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)
