MySQL Tutorial (part 2)

Posted: April 21, 2013 in Database, MySQL

14. UPDATE command:

UPDATE <Table_Name> SET (Column_Name = ‘value’) WHERE (Condition);

mysql> select * from authors;
+----+-------------------+
| Id | Name              |
+----+-------------------+
| 1  | Jack London       |
| 2  | Honore de Balzac  |
| 3  | Lion Feuchtwanger |
| 4  | Emile Zola        |
| 5  | Truman Capote     |
| 6  | Le Duong Long     |
| 11 | Le Vinh Hien      |
+----+-------------------+
7 rows in set (0.00 sec)
mysql> update authors set Name = 'Le Van Hue' where Id = 11;
Query OK, 1 row affected (0.03 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from authors;
+----+-------------------+
| Id | Name              |
+----+-------------------+
| 1  | Jack London       |
| 2  | Honore de Balzac  |
| 3  | Lion Feuchtwanger |
| 4  | Emile Zola        |
| 5  | Truman Capote     |
| 6  | Le Duong Long     |
| 11 | Le Van Hue        |
+----+-------------------+
7 rows in set (0.00 sec)
mysql>

15. DELETE command:

DELETE FROM <Table_Name> WHERE (Condition);

mysql> select * from authors;
+----+-------------------+
| Id | Name              |
+----+-------------------+
| 1  | Jack London       |
| 2  | Honore de Balzac  |
| 3  | Lion Feuchtwanger |
| 4  | Emile Zola        |
| 5  | Truman Capote     |
| 6  | Le Duong Long     |
| 11 | Le Van Hue        |
+----+-------------------+
7 rows in set (0.00 sec)
mysql> delete from authors where Id = 11;
Query OK, 1 row affected (0.03 sec)
mysql> select * from authors;
+----+-------------------+
| Id | Name              |
+----+-------------------+
| 1  | Jack London       |
| 2  | Honore de Balzac  |
| 3  | Lion Feuchtwanger |
| 4  | Emile Zola        |
| 5  | Truman Capote     |
| 6  | Le Duong Long     |
+----+-------------------+
6 rows in set (0.00 sec)
mysql>

16. WHERE command:

… WHERE (Condition);

This command often accompanies some commands like SELECT, UPDATE, DELETE if you want to receive a result that depends on a condition.

a.With SELECT command:

mysql> select * from authors;
+----+-------------------+
| Id | Name              |
+----+-------------------+
| 1  | Jack London       |
| 2  | Honore de Balzac  |
| 3  | Lion Feuchtwanger |
| 4  | Emile Zola        |
| 5  | Truman Capote     |
| 6  | Le Duong Long     |
+----+-------------------+
6 rows in set (0.02 sec)
mysql> select * from books;
+----+----------+----------------------+
| Id | AuthorId | Title                |
+----+----------+----------------------+
| 1  | 1        | Call of the Wild     |
| 2  | 1        | Martin Eden          |
| 3  | 2        | Old Goriot           |
| 4  | 2        | Cousin Bette         |
| 5  | 3        | Jew Suess            |
| 6  | 4        | Nana                 |
| 7  | 4        | The Belly of Paris   |
| 8  | 5        | In Cold blood        |
| 9  | 5        | Breakfast at Tiffany |
+----+----------+----------------------+
9 rows in set (0.03 sec)
mysql> select authors.Name, books.Title from (authors, books) where (authors.Id=books.AuthorId);
+-------------------+----------------------+
| Name              | Title                |
+-------------------+----------------------+
| Jack London       | Call of the Wild     |
| Jack London       | Martin Eden          |
| Honore de Balzac  | Old Goriot           |
| Honore de Balzac  | Cousin Bette         |
| Lion Feuchtwanger | Jew Suess            |
| Emile Zola        | Nana                 |
| Emile Zola        | The Belly of Paris   |
| Truman Capote     | In Cold blood        |
| Truman Capote     | Breakfast at Tiffany |
+-------------------+----------------------+
9 rows in set (0.00 sec)
mysql>

b. With DELETE command:

See in the 19 section

c. With UPDATE command:

See in the 18 section

17. ORDER BY command:

If you want to arrange an output in an increment or decrement order:
Without arrangement:

mysql> select authors.Name, books.Title from (authors, books) where (authors.Id=books.AuthorId);
+-------------------+----------------------+
| Name              | Title                |
+-------------------+----------------------+
| Jack London       | Call of the Wild     |
| Jack London       | Martin Eden          |
| Honore de Balzac  | Old Goriot           |
| Honore de Balzac  | Cousin Bette         |
| Lion Feuchtwanger | Jew Suess            |
| Emile Zola        | Nana                 |
| Emile Zola        | The Belly of Paris   |
| Truman Capote     | In Cold blood        |
| Truman Capote     | Breakfast at Tiffany |
+-------------------+----------------------+
9 rows in set (0.00 sec)

a.In decrement order:

ORDER BY <Column_name> DESC

mysql> select authors.Name, books.Title from (authors, books) where (authors.Id=books.AuthorId) order by authors.Name DESC;
+-------------------+----------------------+
| Name              | Title                |
+-------------------+----------------------+
| Truman Capote     | In Cold blood        |
| Truman Capote     | Breakfast at Tiffany |
| Lion Feuchtwanger | Jew Suess            |
| Jack London       | Call of the Wild     |
| Jack London       | Martin Eden          |
| Honore de Balzac  | Old Goriot           |
| Honore de Balzac  | Cousin Bette         |
| Emile Zola        | Nana                 |
| Emile Zola        | The Belly of Paris   |
+-------------------+----------------------+
9 rows in set (0.02 sec)
mysql>

b. In increment order:

ORDER BY <Column_Name> ASC;

mysql> select authors.Name, books.Title from (authors, books) where (authors.Id=books.AuthorId) order by authors.Name ASC;
+-------------------+----------------------+
| Name              | Title                |
+-------------------+----------------------+
| Emile Zola        | Nana                 |
| Emile Zola        | The Belly of Paris   |
| Honore de Balzac  | Old Goriot           |
| Honore de Balzac  | Cousin Bette         |
| Jack London       | Call of the Wild     |
| Jack London       | Martin Eden          |
| Lion Feuchtwanger | Jew Suess            |
| Truman Capote     | In Cold blood        |
| Truman Capote     | Breakfast at Tiffany |
+-------------------+----------------------+
9 rows in set (0.00 sec)
mysql>

18. GROUP BY command:

GROUP BY clause is used to combine database records with identical values into a single record.

mysql> select * from authors;
+----+-------------------+
| Id | Name              |
+----+-------------------+
| 1  | Jack London       |
| 2  | Honore de Balzac  |
| 3  | Lion Feuchtwanger |
| 4  | Emile Zola        |
| 5  | Truman Capote     |
| 6  | Le Duong Long     |
| 12 | Jack London       |
| 13 | Emile Zola        |
| 14 | Le Duong Long     |
| 15 | Le Vinh Hien      |
+----+-------------------+
10 rows in set (0.00 sec)
mysql> select * from authors group by Name;
+----+-------------------+
| Id | Name              |
+----+-------------------+
| 4  | Emile Zola        |
| 2  | Honore de Balzac  |
| 1  | Jack London       |
| 6  | Le Duong Long     |
| 15 | Le Vinh Hien      |
| 3  | Lion Feuchtwanger |
| 5  | Truman Capote     |
+----+-------------------+
7 rows in set (0.00 sec)
mysql>

Another example:

mysql> select * from sales;
+----+-------------------+------+
| Id | Name              | sell |
+----+-------------------+------+
| 1  | Le Duong Long     | 12   |
| 2  | Tran Thu Ha       | 2    |
| 3  | Le Vinh Hien      | 20   |
| 4  | Duong Thi Hop     | 30   |
| 5  | Hoang Minh Phuong | 3    |
| 6  | Le Van Hue        | 23   |
| 7  | Tran Thu Ha       | 24   |
| 8  | Duong Thi Hop     | 43   |
| 9  | Hoang Minh Phuong | 39   |
| 10 | Le Duong Long     | 112  |
| 11 | Le Vinh Hien      | 200  |
+----+-------------------+------+
11 rows in set (0.00 sec)
mysql> select Name, sum(sell) from sales group by Name;
+-------------------+-----------+
| Name              | sum(sell) |
+-------------------+-----------+
| Duong Thi Hop     | 73        |
| Hoang Minh Phuong | 42        |
| Le Duong Long     | 124       |
| Le Van Hue        | 23        |
| Le Vinh Hien      | 220       |
| Tran Thu Ha       | 26        |
+-------------------+-----------+
6 rows in set (0.00 sec)
mysql>

19. LIMIT command:

If you want to limit the number of rows which is displayed in output results:

mysql> select * from authors;
+----+-------------------+
| Id | Name              |
+----+-------------------+
| 1  | Jack London       |
| 2  | Honore de Balzac  |
| 3  | Lion Feuchtwanger |
| 4  | Emile Zola        |
| 5  | Truman Capote     |
| 6  | Le Duong Long     |
+----+-------------------+
6 rows in set (0.01 sec)
mysql> select * from authors limit 4;
+----+-------------------+
| Id | Name              |
+----+-------------------+
| 1  | Jack London       |
| 2  | Honore de Balzac  |
| 3  | Lion Feuchtwanger |
| 4  | Emile Zola        |
+----+-------------------+
4 rows in set (0.00 sec)
mysql>

Sometime GROUP BY and HAVING go together as follow:

mysql> select Name, sum(sell) from sales group by Name;
+-------------------+-----------+
| Name              | sum(sell) |
+-------------------+-----------+
| Duong Thi Hop     | 73        |
| Hoang Minh Phuong | 42        |
| Le Duong Long     | 124       |
| Le Van Hue        | 23        |
| Le Vinh Hien      | 220       |
| Tran Thu Ha       | 26        |
+-------------------+-----------+
6 rows in set (0.00 sec)
mysql> select Name, sum(sell) from sales group by Name having sum(sell) > 100;
+---------------+-----------+
| Name          | sum(sell) |
+---------------+-----------+
| Le Duong Long | 124       |
| Le Vinh Hien  | 220       |
+---------------+-----------+
2 rows in set (0.00 sec)
mysql>

20. LIKE command:

mysql> select * from sales;
+----+-------------------+------+
| Id | Name              | sell |
+----+-------------------+------+
| 1  | Le Duong Long     | 12   |
| 2  | Tran Thu Ha       | 2    |
| 3  | Le Vinh Hien      | 20   |
| 4  | Duong Thi Hop     | 30   |
| 5  | Hoang Minh Phuong | 3    |
| 6  | Le Van Hue        | 23   |
| 7  | Tran Thu Ha       | 24   |
| 8  | Duong Thi Hop     | 43   |
| 9  | Hoang Minh Phuong | 39   |
| 10 | Le Duong Long     | 112  |
| 11 | Le Vinh Hien      | 200  |
+----+-------------------+------+
11 rows in set (0.00 sec)

I just want to list out who has name with letter L at the beginning:

mysql> select * from sales where Name like 'L%';
+----+---------------+------+
| Id | Name          | sell |
+----+---------------+------+
| 1  | Le Duong Long | 12   |
| 3  | Le Vinh Hien  | 20   |
| 6  | Le Van Hue    | 23   |
| 10 | Le Duong Long | 112  |
| 11 | Le Vinh Hien  | 200  |
+----+---------------+------+
5 rows in set (0.00 sec)

Other examples:

mysql> select distinct Name from sales where Name like 'H%';
+-------------------+
| Name              |
+-------------------+
| Hoang Minh Phuong |
+-------------------+
1 row in set (0.00 sec)
mysql> select distinct Name from sales where Name like '%H%';
+-------------------+
| Name              |
+-------------------+
| Tran Thu Ha       |
| Le Vinh Hien      |
| Duong Thi Hop     |
| Hoang Minh Phuong |
| Le Van Hue        |
+-------------------+
5 rows in set (0.00 sec)
mysql>

21. AS command:

Display output results are in an another name

mysql> select * from sales;
+----+-------------------+------+
| Id | Name              | sell |
+----+-------------------+------+
| 1  | Le Duong Long     | 12   |
| 2  | Tran Thu Ha       | 2    |
| 3  | Le Vinh Hien      | 20   |
| 4  | Duong Thi Hop     | 30   |
| 5  | Hoang Minh Phuong | 3    |
| 6  | Le Van Hue        | 23   |
| 7  | Tran Thu Ha       | 24   |
| 8  | Duong Thi Hop     | 43   |
| 9  | Hoang Minh Phuong | 39   |
| 10 | Le Duong Long     | 112  |
| 11 | Le Vinh Hien      | 200  |
+----+-------------------+------+
11 rows in set (0.00 sec)
mysql> select Name, sum(sell) as summary from sales group by Name;
+-------------------+--------+
| Name              | sumary |
+-------------------+--------+
| Duong Thi Hop     | 73     |
| Hoang Minh Phuong | 42     |
| Le Duong Long     | 124    |
| Le Van Hue        | 23     |
| Le Vinh Hien      | 220    |
| Tran Thu Ha       | 26     |
+-------------------+--------+
6 rows in set (0.00 sec)
mysql>

22. Change password for an existing user:

a. The first way:

mysql> use mysql;
Database changed
mysql> select Host, User, Password from user;
+-----------+---------+-------------------------------------------+
| Host      | User    | Password                                  |
+-----------+---------+-------------------------------------------+
| localhost | root    | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| localhost | admin   | *3620754A963ECB3D7296097F9DA00C1FA5476B03 |
| localhost | ledlong | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+-----------+---------+-------------------------------------------+
3 rows in set (0.00 sec)
mysql> set password for 'admin'@'localhost' = password('123456');
Query OK, 0 rows affected (0.00 sec)
mysql> select Host, User, Password from user;
+-----------+---------+-------------------------------------------+
| Host      | User    | Password                                  |
+-----------+---------+-------------------------------------------+
| localhost | root    | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| localhost | admin   | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| localhost | ledlong | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+-----------+---------+-------------------------------------------+
3 rows in set (0.00 sec)
mysql>

b. The second way:

mysql> grant all privileges on testdb.* to 'admin'@'localhost' identified by '123abc';
Query OK, 0 rows affected (0.05 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.06 sec)
mysql> select Host, User, Password from user;
+-----------+---------+-------------------------------------------+
| Host      | User    | Password                                  |
+-----------+---------+-------------------------------------------+
| localhost | root    | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| localhost | admin   | *3620754A963ECB3D7296097F9DA00C1FA5476B03 |
| localhost | ledlong | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+-----------+---------+-------------------------------------------+
3 rows in set (0.00 sec)
mysql>

c. The third way:

mysql> select Host, User, Password from user;
+-----------+---------+-------------------------------------------+
| Host      | User    | Password                                  |
+-----------+---------+-------------------------------------------+
| localhost | root    | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| localhost | admin   | *3620754A963ECB3D7296097F9DA00C1FA5476B03 |
| localhost | ledlong | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+-----------+---------+-------------------------------------------+
3 rows in set (0.00 sec)
mysql> update mysql.user set Password = password('123456') where user = 'admin' and host = 'localhost';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> flush privileges;
Query OK, 0 rows affected (0.03 sec)
mysql> select Host, User, Password from user;
+-----------+---------+-------------------------------------------+
| Host      | User    | Password                                  |
+-----------+---------+-------------------------------------------+
| localhost | root    | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| localhost | admin   | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| localhost | ledlong | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+-----------+---------+-------------------------------------------+
3 rows in set (0.00 sec)
mysql>

23. Quit MySQL:

mysql> quit;

24. Backup database:

You can use mysqldump tool to backup database:
If you install mysql server on a Windows machine, you can find out mysqldump tool in C:\Program Files\MySQL\MySQL Server 5.5\bin>
You can backup databases as follow:

C:\Program Files\MySQL\MySQL Server 5.5\bin>mysqldump -u root -p testdb > C:\testdb.sql
Enter password: ******

The above clause will generate testdb.sql file into C partition. This file will contain all of commands which are used to backup for testdb database.
You can backup multiple databases in mysql through following clause:

C:\Program Files\MySQL\MySQL Server 5.5\bin>mysqldump -u root -p --databases testdb mysql > C:\testdb.sql
Enter password: ******

You also can backup all of databases in mysql through following clause:

C:\Program Files\MySQL\MySQL Server 5.5\bin>mysqldump -u root -p --all-databases > C:\testdb.sql
Enter password: ******

Note that: In Windowns operating system, mysql and musqldump are seperated programs, so you cannot backup database directly in mysql program if using mysqldump. In Linux environment, you can perform directly mysqldump command in mysql

25. Backup table:

If you just want to backup a table or some tables in a database in mysql, you can do as follow:

C:\Program Files\MySQL\MySQL Server 5.5\bin>mysqldump.exe -u root -p testdb sales > sales.sql
Enter password: ******

The above clause can help you to backup the table sales from the database testdb and generate to sales.sql file. This file is placed in the same folder with mysqldump tool. Certainly, you can identify where is sales.sql placed.

26. Restore a database:

C:\Program Files\MySQL\MySQL Server 5.5\bin>mysql -u root -p testdb < C:\testdb.sql
Enter password: ******

Please see the attached file for more details: Some commands in MySQL

Reference:
[1] http://zetcode.com/databases/mysqltutorial/

Hanoi, April 21th, 2013
ledlong

Comments

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s