Please read this tutorial
SQL Statements : DDL, DML and DCL 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 Databases1) 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 Table3) 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 Results5) 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 Tables7) 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 Columns9) 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 Rows11) 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 File13) 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 SELECTA 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 Tables22) 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;
For more tutorials related to SQL, See the SQL label(Above). Please Share and comment to improve this blog. Thanks to all.. :)
