Note: All the keywords are written with UPPERCASE letters.
a. Connecting 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 privileges.
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 were granted.
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 shell prompt.
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 command:
[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 mysql prompt.
[anand soft@localhost anandsoft]$
c. Issuing Queries
After you are connected to the server you are ready to issue queries.
In MySQL the keywords and functions can be in uppercase or lowercase.
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
d. Creating a Database
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
mysql>create database 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 the command:
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 database:
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:
e. Removing a Database
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:
mysql>create table table_name(column_specs);
- 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 this
mysql>CREATE TABLE student
roll_no int UNSIGNED not null auto_increment primary key ,
name varchar(20)not null,
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.
primary key 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 example,"1983-10-24")
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
You can create primary key by combining two or more fields during table creation by the using the following query:
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.
g. Inserting Data into the Table
The insert into statement allows you to insert data into a table.
Syntax for insertion is:
mysql>insert into table_name values(value1,value2,....);
>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(col1,col2,col3) values(value1,value2,value3);
mysql>insert into table_name set col1=value1,col2=value2,col3=value3...
Note:Any column not named in the set clause is assigned a default value
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.
mysql>load data local infile filename into tabletable_name;
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.
mysql>insert into student values('11','Subhransu
mysql>insert into student(roll_no,name,specialization) values('12','Sudhansu Patra','etc');
mysql>insert into student set name='Suvransu',specialization='ee';
mysql>insert into student values('14','Jonny','etc','1982-6-2');
mysql>insert into student values('15','Missy','ee','1981-5-4');
mysql>insert into student values('16','Jenny','cse','1982-5-7');
mysql>insert into student values('17','Billy','etc','1984-5-4');
mysql>insert into student values('18','Kyle','cse','1983-7-6');
mysql>insert into student values('19','Nathan','ee','1982-2-5');
mysql>insert into student values('20','Abby','cse','1984-9-8');
h. Retrieving Information from a Table
The select statement allows you to retrieve and display information from your table.
The general form of select is:
from <table or tables>
where <conditions that data must satisfy>;
You can see the contents of student table as shown below by the following query:
mysql>select * 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;
i. 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 statements (respectively).
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 command:
mysql>DELETE FROM student WHERE dob="0000-00-00";
After the execution of above delete statement you can see the contents by giving the above SELECT statement as below
mysql>select * from student;
TO modify existing records, use UPDATE which has this form:
UPDATE table_name 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" where roll_no="20";
After the execution of above query the contents of the table becomes:
j. Altering the Structure of Tables
Using ALTER statement you can add fields to a existing table.
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 type.
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 names.
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:
k. Dropping a Table
The difference between DROP and DELETE table is that, after executing delete 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.
l. Working 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 ;
m. Backing 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,)