常用SQL語句

1.select count() from mysql.user;//查找mysql庫的user表的行數

mysql> select count() from mysql.user;//count()在myisam會很快、innoDB引擎就會很慢（不會去統計行數，很耗時），所有的操作盡量減少

+----------+

| count(*) |

| 8 |

2.select from mysql.db;

mysql> select from mysql.db\G; //查找mysql庫的db表所有内容

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; //查找mysql庫的db表為db的字段

+---------+

| db |

| test |

| test_% |

4.select db,user from mysql.db;

select db,user from mysql.db; //可以查詢兩個字段

+---------+--------+

| db | user |

| test | |

| test_% | |

| db1 | user2 |

| discuz | discuz |

| db1 | user1 |

| db1 | user3 |

7 rows in set (0.00 sec)

5.select from mysql.db where host like '192.168.%'; //like模糊比對

mysql> select from mysql.db where host like '192.168.%'\G;

2. row

Host: 192.168.211.%

Db: discuz

User: discuz

Delete_priv: Y

6.insert into db1.t1 values (1, 'abc'); //插入

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; //檢視表的結構

+-------+----------+------+-----+---------+-------+

| 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');//插入語句.也可以做一些限制，一樣的值，不一樣的id，一樣的數字就報沖突

Query OK, 1 row affected (0.21 sec)

select * from db1.t1;