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 | | discuz | discuz | +---------+--------+ 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 Select_priv: Y Insert_priv: Y Update_priv: Y 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; +------+------+ | id | name | +------+------+ | 1 | abc | +------+------+

7.update db1.t1 set name='aaa' where id=1;//把db1库的t1表id为1的名字改为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;//删除db库的t1表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;//清空t1表内容。表结构留着

10.drop table db1.t1;//库结构、内容全部清空。 11.drop database db1.t1;//删除t1表的内容以及结构 12.drop database db1；//删除数据库

本文转自 虾米的春天 51CTO博客，原文链接：http://blog.51cto.com/lsxme/2061421，如需转载请自行联系原作者