對數據的操作
插入數據
insert into tb_name (col1,col2,...) values|value ('STRING', NUM,...);
insert into tb_name (col1,col2,...) values|value ('STRING', NUM,...)， ('STRING', NUM,...)，...;
在指定字段插入數據
mysql> insert into student (name,gender) value ('Li','M'),('Yang','F');
mysql> select * from student;
+------+------+------+--------+--------+
| name | age | high | gender | lesson |
+------+------+------+--------+--------+
| Li | NULL | NULL | M | NULL |
| Yang | NULL | NULL | F | NULL |
+------+------+------+--------+--------+
不指定字段，使用默認字段
mysql> insert into student value ('Zhang',26,162,'M','food');
Query OK, 1 row affected (0.00 sec)
mysql> select * from student;
+-------+------+------+--------+--------+
| name | age | high | gender | lesson |
+-------+------+------+--------+--------+
| Li | NULL | NULL | M | NULL |
| Yang | NULL | NULL | F | NULL |
| Zhang | 26 | 162 | M | food |
+-------+------+------+--------+--------+
修改數據
update tb_name set column=value WHERE
mysql> update student set high=178 where name='yang';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from student;
+-------+------+------+--------+--------+
| name | age | high | gender | lesson |
+-------+------+------+--------+--------+
| Li | NULL | NULL | M | NULL |
| Yang | NULL | 178 | F | NULL |
| Zhang | 26 | 162 | M | food |
+-------+------+------+--------+--------+
刪除數據
delete from tb_name where CONDITION;
mysql> delete from student where name='Li';
選擇
SELECT 字段 FROM tb_name WHERE CONDITION
*: 所有字段
不指定WHERE：表示顯示所有行；
mysql> select name,high from student where lesson='food';
+-------+------+
| name | high |
+-------+------+
| Zhang | 162 |
創建用戶
create user 'username'@'host' [identified by 'password'];
刪除用戶
drop user 'username'@'host';
HOST:
IP：
HOSTNAME:
NETWORK：
通配符
_:匹配任意單個字符， 172.16.0._
%:匹配任意字符；
DCL:
授權用戶
grant pri1,pri2,... on db_name.tb_name to 'username'@'host' [identified by 'password'];不存在的話直接創建并授權
取消授權
revoke pri1,pri2,... on db_name.tb_name from 'username'@'host';
查看用戶的授權
show grants for 'username'@'host';
ALL PRIVILEGES
mysql> create user 'jerry'@'%';
mysql> show grants for 'jerry'@'$';
ERROR 1141 (42000): There is no such grant defined for user 'jerry' on host '$'
mysql> create user 'tom'@'%' identified by 'tom';
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for 'tom'@'%';
+---------------------------------------------------------------------------+
| Grants for tom@% |
+---------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'tom'@'%' IDENTIFIED BY PASSWORD '675bd1463e544441' |
+---------------------------------------------------------------------------+
select User,Host,Password from user;
+-------+--------------+------------------+
| User | Host | Password |
+-------+--------------+------------------+
| root | localhost | 565491d704013245 |
| root | hiyang.com | 565491d704013245 |
| root | 127.0.0.1 | |
| | localhost | |
| | hiyang.com | |
| jerry | % | |
| tom | % | 675bd1463e544441 |
| root | 192.168.8.40 | 565491d704013245 |
+-------+--------------+------------------+
