Note: All the keywords are written with
To the Server
As mentioned earlier, MySQL operates in client/server
architecture. The client application needs to connect to
database server, before manipulating the data.
For our examples, we will be using Telnet application
to connect to the database server, and manipulate the database.
In the examples provided, we are using Windows OS with Telnet
client application. However, please note that there are
other ways to connect to the database server. Initially,
you need to call the telnet application by issuing "telnet"
command from your DOS prompt. Now the telnet window pops
up as shown below.
After clicking on the "Remote System", the
following window will come where you can give the IP address
of the server for connection
Then click the Connect button which will
show the following window.
In the above window enter your user name, and then enter
the password when prompted. The following window will be shown
with Linux shell prompt (if the server is a Linux system) if
both user name and password are correct. Please note that the
prompt depends on the username, and will vary from user to user.
Now you are connected with the Linux server.
b. Connecting to the MySQL server
Before you work with MySQL, ensure that you have a user name
and password with appropriate permissions for connecting to
and accessing the MySQL database.
The GRANT and REVOKE commands allow system administrators
to create users and grant and revoke rights to MySQL users at
four privilege levels:
Global level: The global privileges apply to all databases
on a given server. These privileges are stored in the mysql.user
table. REVOKE ALL ON *.* will revoke only global privileges.
Database level: Database privileges apply to all tables in
a given database. These privileges are stored in the mysql.db
and mysql.host tables. REVOKE ALL ON db.* will revoke only database
Table level: Table privileges apply to all columns in a given
table. These privileges are stored in the mysql.tables_priv
table. REVOKE ALL ON db.table will revoke only table privileges.
Column level: Column privileges apply to single columns in
a given table. These privileges are stored in the mysql.columns_priv
table. When using REVOKE you must specify the same columns that
mysql> GRANT ALL PRIVILEGES ON *.* TO name1@localhost
IDENTIFIED BY 'pass' WITH GRANT OPTION;
mysql> GRANT ALL PRIVILEGES ON *.* TO name1@"%"
IDENTIFIED BY 'pass' WITH GRANT OPTION;
The above commands will provide the user "name1"
with superuser permissions. The user can connect from anywhere.
If you give a grant for a users that doesn't exists, that
user is created.
After getting the user_name, password you can connect to
the database server.
To connect to the server invoke the mysql program from your
Syntax of the command is:
% mysql <options>
% indicates the shell prompts
mysql is the client program
<options> include the following:
-h host_name -u user_name -p password
-u user_name -p (if host is localhost)
In our example, we have the following information:
Given the above, to connect to the database server use the
[anand soft@localhost anandsoft]$ mysql -u subu -p
then enter your password subu at the password prompt.
The following screenshot depicts the above example:
Now you are connected to the database server.
The connection can be terminated by giving QUIT at the
[anand soft@localhost anandsoft]$
c. Issuing Queries
After you are connected to the server you are ready to issue
In MySQL the keywords and functions can be in uppercase or
But the database name and table name must be in proper case
as in Unix system the files and directives are case sensitive.
To enter a query in mysql, just type it ,end it with a semicolon(;)
and press enter. The semicolon tells mysql that the query is
complete. You can also use '\g' to terminate queries.
Examples and results of some simple query is given below:
Note: When you invoke a function in query, there must
be no space between function name and following parenthesis
As mysql waits for the semicolon before sending the query
to the server, you don't need to enter it on a single line.
You can spread a query over several lines if you want:
Note: the prompt changes from 'mysql' to '->'after
you enter the first line of query.
If you have begun typing in a multiple-line query and decide
you don't want to execute it , type '\c' to clear(cancel) it.
Note: The prompt changes back to mysql> to indicate
that mysql is ready for new query.'c' is always lowercase
The first step in database management, is to create a database.
The following steps are demonstrated using a database sample_db:
1.Creating(initializing) the database.
2.Creating the tables within the database
3.Interacting with the tables by inserting, retrieving, modifying,
or deleting data.
After connection to the server issue the following query
to create database by name sample_db
Now, a database by name sample_db is created, but still not
in use. You need to issue USE <database-name> command
to perform any operations on the database. SELECT DATABASE()
command can be used to view the database in use as shown below:
To make the sample_db as the current database in use, issue
Note: Use is one of the few statements that require
no terminating semicolon, although you can give if you want.
After you issue the use statement, sample_db is the default
The other way to make a database current is to name it on
command line during connection to the server as follows:
% mysql -u subu -p sample_db
The available databases could be viewed by issuing the command:
You can remove it by the following query:
mysql>drop database sample_db;
The command will permanently remove the database.
f. Creating Tables
The create table
statement allows you to create a table within the current database.
Syntax for creating table:
- table_name indicates the name you want to give the table.
- column_specs provides the specifications for the columns
in the table, as well as indexes (if you have any)
Each column specification in the create table statement consists
of the column name ,the type (like varchar, int, date, etc.),
and possibly some column attributes.
Note: A table must have at least one column. You cannot
create a table without specifying any column name.
Now we can create a table having name student and four fields
having name as roll_no, name, specialization, dob(date of birth).
The CREATE TABLE statement for the student table look like
mysql>CREATE TABLE student
UNSIGNED not null auto_increment
primary key ,
dob date NOT NULL);
In the above insert statement :
INT signifies that the column holds integers (value
with no fractional part)
UNSIGNED disallows negative numbers.
NOT NULL means that the column value must be filled
in. (No student can be without a roll number)
AUTO_INCREMENT works like this: if the value for
the roll_no column is missing (or NULL) when you create
a new student table record,MySQL automatically generates
a unique number that is one greater than the maximum value
currently in the column.
means each value in the column must be unique. This
prevents us for using the roll number twice by mistake,
which is desirable property for student roll number. (Not
only that ,but MySQL requires every AUTO_INCREMENT column
have a unique index)
VARCHAR(n) means the column contains variable-length
character values, with a maximum length of n characters.
Column type DATE holds the value in the format "YYYY-MM-DD"(for
After creating a table you can see the structure of that
table by DESC statement or SHOW COLUMNS FROM table_name
mysql>SHOW COLUMNS FROM student;
Note: if you happen to forget the name of any
tables inside your database, you can see it by giving
the following query
Note: You can create primary key by combining
two or more fields during table creation by the using the following
CREATE TABLE table_name (col1_name type NOT NULL,col2_name
type NOT NULL,.....,primary key(col1,col2))
The two fields combining which you want to make a primary
key cannot be NULL.
Here type signifies data type of the field.
Inserting Data into the Table
The insert into
statement allows you to insert data into a table.
Syntax for insertion is:
>table_name indicates the name of the table.
>value1,value2.... are the number of values same as the
number of columns in the table_name specified.
If you want to insert values into few fields instead of whole
record, you can achieve this by the following query:
mysql>insert into table_name set col1=value1,col2=value2,col3=value3...
Note:Any column not named in the set clause is assigned a
Another method of loading records into a table is to read
the data values directly from a file .You can load records using
load data statement
The load data statement acts as a bulk loader that reads
data from a file.
local infile filename
Note:By default, the load data statement assumes that column
values are separated by tabs and that lines end with new lines
.It also assumes that the values are present in that columns
are stored in the table. "filename" should present in the
user home directory.
Now you can insert some data into the student table using
the above described INSERT statement.
h. Retrieving Information from a Table
statement allows you to retrieve and display information from
The general form of
that data must satisfy>;
You can see the contents of student table as shown below
by the following query:
* from student;
Here * signifies all. You can also retrieve specific field
those you want.
Suppose you want to see only roll number and the name of
students. The following query does this
mysql>SELECT roll_no,name from student;
Editing and Deleting Records
Changing some of the field values, or even deleting some
records is part of any database maintenance. Two frequently
used commands for doing the same are UPDATE and DELETE
The DELETE statement has this form:
DELETE FROM <table_name> WHERE <records to delete>
The WHERE clause specifies which records to be deleted. It's
optional but if you leave it out, all records are deleted from
the table specified.
i.e. "DELETE FROM <table_name>" will delete
all records from table table_name.
Now, suppose you want to delete records of those student
who don't have date of birth, then you can issue the following
mysql>DELETE FROM student WHERE dob="0000-00-00";
After the execution of above
you can see the contents by giving the above SELECT statement
* from student;
TO modify existing records, use UPDATE which has this form:
SET which columns to change WHERE which records to update.
Here also the WHERE clause is optional ,if you don't specify
one, every records in the table is updated.
i.e. UPDATE table_name SET which columns to change
for example you can change the specialization of a
student whose roll number is 20,to etc from cse.
The following query fulfills the above change:
mysql>UPDATE student SET specialization="etc"
After the execution of above query the contents of the table
j. Altering the Structure of Tables
Using ALTER statement you can add fields to a existing
The general form of ALTER statement is:
ALTER TABLEtable_name ADD (column specs);
Suppose you want to add another field as marks to the student
table for storing students mark. Then the query becomes
mysql>ALTER TABLE student add marks int(3);
Then the table structure becomes:
Using ALTER statement you can change the data type
of a column and the name of an existing table.
Syntax for changing the data types of a column.
ALTER TABLE table_name MODIFY column_name type.
ALTER TABLE table_name CHANGE column_name new column_name
Note:The difference between MODIFY and CHANGE is that,
in case of CHANGE you can change name of column which
is not possible by using MODIFY that's why change takes two
Syntax for changing the table name:
ALTER TABLE table_name
rename as new_table_name
Using ALTER statement you can remove a column from a table:
ALTER TABLE table_name
drop column col_name;
Suppose you want to drop field marks, then you can
give the following query:
mysql>ALTER TABLE student DROP COLUMN marks;
Then the table structure becomes:
The difference between DROP and DELETE table is that, after
statement the contents of table are removed but the structure
remains same, but in case of DROP statement both the contents
and structure are removed.
Syntax for DROP statement is:
mysql>DROP TABLE table_name;
During issuing query if you put a single quote( ' ) or double
quote( " ) inside a query you must have to end somewhere
with single quote or double quote otherwise an error will be
thrown (as shown below) because mysql will think as receiving
a string until the quote ends with another quote .Anything
inside that two quote is treated as string.
with NULL value
When the value of a field is NULL you cannot compare in the
same way as doing for NOT NULL value, if you do so you will
not get the desired result.
For NULL value comparison you may follow the following procedure:
mysql>SELECT * FROM table_name WHERE field_name is NULL ;
up a Database
You can take a backup of your database in a text file
by using the mysqldump command from shell prompt as given below
After the execution of the above command sample.sql file
will contain the structure as well as the data insertion statements
done on sample_db database.
Note:sample.sql file is stored in the user home directory,i.e.
the user name under which you logged in to the server(not MySQL
server). For example if your username is anandsoft ,in Linux
system the file sample.sql will be stored in the directory /home/anandsoft/
you can only take the structure of the tables by giving
the following command:
The mysqldump command is a very useful, and frequently used
for taking a backup of an existing database. Another use of
this command is when you want to transfer the database from
a local server to a remote server effortlessly. For example,
you have created a database on your local server and tested
the program. Now you want to upload the same to an Internet
server. Take an sql dump of the local database by using the
mysqldump command and paste the file contents on the
remote server. (Alternatively, you can also recreate the database
by specifying the dump file name,)