对数据的操作
插入数据
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 |
+-------+--------------+------------------+