Common SQL statements

1.select Count ( ) from mysql.user;//to find the number of rows for the user table in MySQL Library
Mysql> Select COUNT () from Mysql.user;//count ( in the MyISAM will be very fast, InnoDB engine will be very slow (not to count the number of rows, very time-consuming), allOperations to Minimize
+----------+
| COUNT (*) |
+----------+
| 8 |
+----------+

2.select from mysql.db;
Mysql> select from Mysql.db\g; Find all the contents of the DB table in 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 the DB table for MySQL library field
+---------+
| db |
+---------+
| Test |
| test_% |
+---------+

4.select Db,user from Mysql.db;
Select Db,user from Mysql.db; Two fields can be queried
+---------+--------+
| 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.% ';//like fuzzy matching
Mysql> Select from mysql.db where host like ' 192.168.% ' \g;
2. Row
host:192.168.211.%
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, ' abc ');
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 a 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, ' abc ');//INSERT statement. You can also do some restrictions, the same value, not the same ID, the same number will be reported 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;//changes the DB1 library's T1 table ID 1 to AAA
Mysql> select from Db1.t1;
+------+------+
| id | name |
+------+------+
| 1 | abc |
| 1 | lsx |
| 3 | lshx |
+------+------+
3 rows in Set (0.01 sec)
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;//delete db Library T1 row with table ID 6
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;//Empty the contents of T1 tables. The table structure is kept
10.drop Table db1.t1;//Library structure, contents all empty.
11.drop database db1.t1;//Delete the contents and structure of the T1 table
12.drop database db1;//Deleting databases

Common SQL statements