MySQL Tutorials : Working/Getting Started With MySQL

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.

Example:

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:

• host:localhost
• user_name:subu
• password:subu
• database:sample_db

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.

mysql>QUIT

Bye

[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:

mysql>select now();

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:

mysql>select now(),

->user(),

->version()

->;

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.

mysql>select now(),

->user(),

->\c

mysql>

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:

mysql>select database();

To make the sample_db as the current database in use, issue the command:

mysql>use sample_db

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:

mysql>select 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:

    mysql>SHOW DATABASES;

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,

            specialization varchar(6) 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

i.e. 

mysql>DESC student;

or 

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 

mysql>SHOW TABLES;

Note: 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);

or

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.

Syntax is:

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 Patra','cse','1983-6-3');

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:

mysql>select <fields-to-select>

 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 TABLE table_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.

or

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:

Syntax is:

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

[anand soft@localhost anandsoft]$mysqldump -u subu -psubu sample_db>sample.sql

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:

[anand soft@localhost anandsoft]$mysqldump -d -u subu -psubu sample_db>sample1.sql

you can take back up of any specific table  from a database by following way

[anand soft@localhost anandsoft]$mysqldump  -u subu -psubu database_name table_name>file_name

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,)