Commonly used SQL statements
1.select count () from mysql.user;// finds the number of rows in the user table of the mysql library
Mysql > select count () from mysql.user;//count () will be fast in myisam, innoDB engine will be very slow (will not count rows, very time-consuming), and all operations will be reduced as much as possible
+-+
| | count (*) |
+-+
| | 8 |
+-+
2.select from mysql.db
Mysql > select from mysql.db\ G; / / find all the contents of the db table of the mysql library
1. Row
Host:%
Db: test
User:
Select_priv: Y
Insert_priv: Y
Update_priv: Y
3.select db from mysql.db
Select db from mysql.db; / / find fields whose db table of the mysql library is db
+-+
| | db |
+-+
| | test |
| | test_% |
+-+
4.select db,user from mysql.db
Select db,user from mysql.db; / / can query two fields
+-+ +
| | db | user |
+-+ +
| | test |
| | test_% |
| | db1 | user2 |
| | discuz | discuz |
| | db1 | user1 |
| | db1 | user3 |
| | discuz | discuz |
+-+ +
7 rows in set (0.00 sec)
5.select from mysql.db where host like '192.168% matching; / / like fuzzy matching
Mysql > select from mysql.db where host like '192.168%'\ G
2. Row
Host: 192.168.211.1%
Db: discuz
User: discuz
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
6.insert into db1.t1 values (1, 'abc'); / / insert
Mysql > insert db1.t1 values (1)
Query OK, 1 row affected (0.11 sec)
Mysql > select * from db1.t1
+-+ +
| | id | name |
+-+ +
| | 1 | abc |
+-+ +
1 row in set (0.01 sec)
Desc db1.t1; / / View the structure of the table
+-+ +
| | Field | Type | Null | Key | Default | Extra | |
+-+ +
| | id | int (4) | YES | | NULL |
| | name | char (40) | YES | | NULL |
+-+ +
2 rows in set (0.00 sec)
Mysql > insert into db1.t1 values (1); / / insert statements. You can also make some restrictions, the same value, different id, the same number is reported to conflict
Query OK, 1 row affected (0.21 sec)
Select * from db1.t1
+-+ +
| | id | name |
+-+ +
| | 1 | abc |
+-+ +
7.update db1.t1 set name='aaa' where id=1;// changed the name of the T1 table id of the db1 library to aaa
Mysql > select from db1.t1
+-+ +
| | id | name |
+-+ +
| | 1 | abc |
| | 1 | lsx |
| | 3 | lshx |
+-+ +
3 rows in set (0.01sec)
Mysql > update db1.t1 set name='lsx01' where id=1
Query OK, 2 rows affected (0.55 sec)
Rows matched: 2 Changed: 2 Warnings: 0
Mysql > select from db1.t1
+-+ +
| | id | name |
+-+ +
| | 1 | lsx01 |
| | 1 | lsx01 |
8.delete from db1.t1 where id=6;// deletes the row with id 6 in the T1 table of the db library
Mysql > select from db1.t1
+-+ +
| | id | name |
+-+ +
| | 1 | abc |
| | 111 | lsx |
| | 111 | lshx |
+-+ +
3 rows in set (0.00 sec)
Mysql > delete from db1.t1 where id=111
Query OK, 2 rows affected (0.28 sec)
Mysql > select from db1.t1
+-+ +
| | id | name |
+-+ +
| | 1 | abc |
+-+ +
1 row in set (0.00 sec)
9.truncate table db1.t1;// clears the contents of the T1 table. Keep the table structure
The structure and contents of the 10.drop table db1.t1;// library are all emptied.
11.drop database db1.t1;// deletes the content and structure of the T1 table
12.drop database db1;// deletes the database
