Tutorial to understand SQL language

First read SQL Statements: DDL, DML and DCL tutorial to understand this tutorial deeply. In this tutorial, we will learn how to create database, tables and how to select/insert/delete/update data in tables.

Creating and Deleting Databases

1) Creating a database
mysql> CREATE database 134a;
Query OK, 1 row affected (0.00 sec)

2) Deleting a database
mysql> DROP database 134a;
Query OK, 0 rows affected (0.00 sec)

Creating a Table

3) After we have created the database we use the USE statement to
change the current database;
mysql> USE 134a;
Database changed

4) Creating a table in the database is achieved with the CREATE table
statement
mysql> CREATE TABLE president (
-> last_name varchar(15) not null,
-> first_name varchar(15) not null,
-> state varchar(2) not null,
-> city varchar(20) not null,
-> birth date
-> death date null
not null default '0000-00-00',
-> );
Query OK, 0 rows affected (0.00 sec)

Examining the Results

5) To see what tables are present in the database use the SHOW tables:
mysql> SHOW tables;
+------------------------+
| Tables_in_134a |
+------------------------+
| president
|
+------------------------+
1 row in set (0.00 sec)

6) The command DESCRIBE can be used to view the structure of a table
mysql> DESCRIBE president;

Inserting / Retrieving Data into / from Tables

7) To insert new rows into an existing table use the INSERT command:
mysql> INSERT INTO president values ('Washington','George','VA','Westmoreland County','17320212','17991214');
Query OK, 1 row affected (0.00 sec)

8) With the SELECT command we can retrieve previously inserted rows:
mysql> SELECT * FROM president;

Selecting Specific Rows and Columns

9) Selecting rows by using the WHERE clause in the SELECT command
mysql> SELECT * FROM president WHERE state="VA";

10) Selecting specific columns by listing their names
mysql> SELECT state, first_name, last_name FROM president;

Deleting and Updating Rows

11) Deleting selected rows from a table using the DELETE command
mysql> DELETE FROM president WHERE first_name="George";
Query OK, 1 row affected (0.00 sec)

12) To modify or update entries in the table use the UPDATE command
mysql> UPDATE president SET state="CA" WHERE first_name="George";
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0


Loading a Database from a File

13) Loading a your data from a file into a table.
Assuming we have a file named "president_db" in the current directory,
with multiple INSERT commands in it, we can use the LOAD DATA command to
insert the data into the table president.

mysql> LOAD DATA LOCAL INFILE 'president_db' INTO TABLE president;
Query OK, 45 rows affected (0.01 sec)
Records: 45 Deleted: 0 Skipped: 0 Warnings: 0
Note, that any ascii file that contains a valid sequence of MySql
commands on separate lines can be read in from the command line as:
>mysql -u USERNAME -p < MY_Mysql_FILE


More on SELECT

A general form of SELECT is:
SELECT what to select
FROM table(s)
WHERE condition that the data must satisfy;
Comparison operators are: < ; <= ; = ; != or <> ; >= ; >
Logical operators are: AND ; OR ; NOT
Comparison operator for special value NULL: IS

14) The following MySQL query will return all the fields for the
presidents whose state field is "NY";
mysql> SELECT * FROM president WHERE state="NY";

15) We can limit the values of the returned fields as it is shown bellow:
mysql> SELECT last_name, first_name FROM president WHERE state="NY";

16) The following entry SELECT will return the last name and
birth date of presidents who are still alive
Note: The comparison operator will not work in this case:
mysql> SELECT * FROM president WHERE death = NULL;
Empty set (0.00 sec)
mysql> SELECT last_name, birth FROM president WHERE death is NULL;

17) This command will select the presidents who were born in the
18th century
mysql> SELECT last_name, birth FROM president WHERE birth<"1800-01-01";

18) The following command will select the president who was born first
mysql> SELECT last_name, birth from president ORDER BY birth ASC LIMIT 1;

19) The following query will return the names of fist 5 states (in
descending order) in which the greatest number of presidents have been
born
mysql> SELECT state, count(*) AS times FROM president GROUP BY state
-> ORDER BY times DESC LIMIT 5;

20) The following query will select presidents who have been born
in the last 60 years
mysql> SELECT * FROM president WHERE(YEAR(now())- YEAR(birth)) < 60;
Useful function to retrieve parts of dates are: YEAR(), MONTH(), DAYOFMONTH(),TO_DAY().

21) The following query will sort presidents who have died by their
age and list the first 10 in descending order.
mysql> SELECT last_name, birth, death, FLOOR((TO_DAYS(death) - TO_DAYS(birth))/365) AS age
-> FROM president
-> WHERE death is not NULL ORDER BY age DESC LIMIT 10;

Working with Multiple Tables

22) Often it is useful to separate data in conceptually distinct groups and store them in separate tables. Assuming we have a table that contains students' personal information, and we have another table that contains test scores of students. We can create a common field in each table, say "ssn" and work with the two tables together as follows:

SELECT last_name, address, test_date, score FROM test, student WHERE test.ssn = student.ssn;


Related Tutorials:-

★ ACID properties of Database

★ SQL Statements : DDL, DML and DCL

★ Difference between delete, truncate and drop command in SQL