UPDATE stu SET sname=’zhangSanSan’, age=’32’, gender=’female’ WHERE sid=’s_1001’;
UPDATE stu SET sname=’liSi’, age=’20’ WHERE age>50 AND gender=’male’;
UPDATE stu SET sname=’wangWu’, age=’30’ WHERE age>60 OR gender=’female’;
UPDATE stu SET gender=’female’ WHERE gender IS NULL
UPDATE stu SET age=age+1 WHERE sname=’zhaoLiu’;
4.3 删除数据
语法：
DELETE FROM 表名 [WHERE 条件]
DELETE FROM stu WHERE sid=’s_1001’003B
DELETE FROM stu WHERE sname=’chenQi’ OR age > 30;
DELETE FROM stu;
语法：
TRUNCATE TABLE 表名
TRUNCATE TABLE stu;
虽然TRUNCATE和DELETE都可以删除表的所有记录，但有原理不同。DELETE的效率没有TRUNCATE高！
TRUNCATE其实属性DDL语句，因为它是先DROP TABLE，再CREATE TABLE。而且TRUNCATE删除的记录是无法回滚的，但DELETE删除的记录是可以回滚的（回滚是事务的知识！）。
5 DCL 5.1 创建用户语法：
CREATE USER 用户名@地址 IDENTIFIED BY \'密码\';
CREATE USER user1@localhost IDENTIFIED BY ‘123’;
CREATE USER user2@’%’ IDENTIFIED BY ‘123’;
5.2 给用户授权
语法：
GRANT 权限1, … , 权限n ON 数据库.* TO 用户名
GRANT CREATE,ALTER,DROP,INSERT,UPDATE,DELETE,SELECT ON mydb1.* TO user1@localhost;
GRANT ALL ON mydb1.* TO user2@localhost;
5.3 撤销授权
语法：
REVOKE权限1, … , 权限n ON 数据库.* FORM 用户名
REVOKE CREATE,ALTER,DROP ON mydb1.* FROM user1@localhost;
5.4 查看用户权限
语法：
SHOW GRANTS FOR 用户名
SHOW GRANTS FOR user1@localhost;
5.5 删除用户
语法：
DROP USER 用户名
DROP USER user1@localhost;
5.6 修改用户密码
语法：
USE mysql;
UPDATE USER SET PASSWORD=PASSWORD(‘密码’) WHERE User=’用户名’ and Host=’IP’;
FLUSH PRIVILEGES;
UPDATE USER SET PASSWORD=PASSWORD(\'1234\') WHERE User=\'user2\' and Host=’localhost’;
FLUSH PRIVILEGES;
数据查询语法（DQL）
DQL就是数据查询语言，数据库执行DQL语句不会对数据进行改变，而是让数据库发送结果集给客户端。
语法：
SELECT selection_list /*要查询的列名称*/
FROM table_list /*要查询的表名称*/
WHERE condition /*行条件*/
GROUP BY grouping_columns /*对结果分组*/
HAVING condition /*分组后的行条件*/
ORDER BY sorting_columns /*对结果分组*/
LIMIT offset_start, row_count /*结果限定*/
创建名：
l 学生表：stu
字段名称
字段类型
说明
sid
char(6)
学生学号
sname
varchar(50)
学生姓名
age
int
学生年龄
gender
varchar(50)
学生性别
CREATE TABLE stu (
sid CHAR(6),
sname VARCHAR(50),
age INT,
gender VARCHAR(50)
);
INSERT INTO stu VALUES(\'S_1001\', \'liuYi\', 35, \'male\');
INSERT INTO stu VALUES(\'S_1002\', \'chenEr\', 15, \'female\');
INSERT INTO stu VALUES(\'S_1003\', \'zhangSan\', 95, \'male\');
INSERT INTO stu VALUES(\'S_1004\', \'liSi\', 65, \'female\');
INSERT INTO stu VALUES(\'S_1005\', \'wangWu\', 55, \'male\');
INSERT INTO stu VALUES(\'S_1006\', \'zhaoLiu\', 75, \'female\');
INSERT INTO stu VALUES(\'S_1007\', \'sunQi\', 25, \'male\');
INSERT INTO stu VALUES(\'S_1008\', \'zhouBa\', 45, \'female\');
INSERT INTO stu VALUES(\'S_1009\', \'wuJiu\', 85, \'male\');
INSERT INTO stu VALUES(\'S_1010\', \'zhengShi\', 5, \'female\');
INSERT INTO stu VALUES(\'S_1011\', \'xxx\', NULL, NULL);
l 雇员表：emp
字段名称
字段类型
说明
empno
int
员工编号
ename
varchar(50)
员工姓名
job
varchar(50)
员工工作
mgr
int
领导编号
hiredate
date
入职日期
sal
decimal(7,2)
月薪
comm
decimal(7,2)
奖金
deptno
int
部分编号
CREATE TABLE emp(
empno INT,
ename VARCHAR(50),
job VARCHAR(50),
mgr INT,
hiredate DATE,
sal DECIMAL(7,2),
comm decimal(7,2),
deptno INT
) ;
INSERT INTO emp values(7369,\'SMITH\',\'CLERK\',7902,\'1980-12-17\',800,NULL,20);
INSERT INTO emp values(7499,\'ALLEN\',\'SALESMAN\',7698,\'1981-02-20\',1600,300,30);
INSERT INTO emp values(7521,\'WARD\',\'SALESMAN\',7698,\'1981-02-22\',1250,500,30);
INSERT INTO emp values(7566,\'JONES\',\'MANAGER\',7839,\'1981-04-02\',2975,NULL,20);
INSERT INTO emp values(7654,\'MARTIN\',\'SALESMAN\',7698,\'1981-09-28\',1250,1400,30);
INSERT INTO emp values(7698,\'BLAKE\',\'MANAGER\',7839,\'1981-05-01\',2850,NULL,30);
INSERT INTO emp values(7782,\'CLARK\',\'MANAGER\',7839,\'1981-06-09\',2450,NULL,10);
INSERT INTO emp values(7788,\'SCOTT\',\'ANALYST\',7566,\'1987-04-19\',3000,NULL,20);
INSERT INTO emp values(7839,\'KING\',\'PRESIDENT\',NULL,\'1981-11-17\',5000,NULL,10);
INSERT INTO emp values(7844,\'TURNER\',\'SALESMAN\',7698,\'1981-09-08\',1500,0,30);
INSERT INTO emp values(7876,\'ADAMS\',\'CLERK\',7788,\'1987-05-23\',1100,NULL,20);
INSERT INTO emp values(7900,\'JAMES\',\'CLERK\',7698,\'1981-12-03\',950,NULL,30);
INSERT INTO emp values(7902,\'FORD\',\'ANALYST\',7566,\'1981-12-03\',3000,NULL,20);
INSERT INTO emp values(7934,\'MILLER\',\'CLERK\',7782,\'1982-01-23\',1300,NULL,10);
l 部分表：dept
字段名称
字段类型
说明
deptno
int
部分编码
dname
varchar(50)
部分名称
loc
varchar(50)
部分所在地点
CREATE TABLE dept(
deptno INT,
dname varchar(14),
loc varchar(13)
);
INSERT INTO dept values(10, \'ACCOUNTING\', \'NEW YORK\');
INSERT INTO dept values(20, \'RESEARCH\', \'DALLAS\');
INSERT INTO dept values(30, \'SALES\', \'CHICAGO\');
INSERT INTO dept values(40, \'OPERATIONS\', \'BOSTON\');
1 基础查询 1.1 查询所有列
SELECT * FROM stu;
1.2 查询指定列SELECT sid, sname, age FROM stu;
2 条件查询 2.1 条件查询介绍条件查询就是在查询时给出WHERE子句，在WHERE子句中可以使用如下运算符及关键字：
l =、!=、<>、<、<=、>、>=；
l BETWEEN…AND；
l IN(set)；
l IS NULL；
l AND；
l OR；
l NOT；
2.2 查询性别为女，并且年龄50的记录SELECT * FROM stu
WHERE gender=\'female\' AND ge<50;
2.3 查询学号为S_1001，或者姓名为liSi的记录SELECT * FROM stu
WHERE sid =\'S_1001\' OR sname=\'liSi\';
2.4 查询学号为S_1001，S_1002，S_1003的记录SELECT * FROM stu
WHERE sid IN (\'S_1001\',\'S_1002\',\'S_1003\');
2.5 查询学号不是S_1001，S_1002，S_1003的记录SELECT * FROM tab_student
WHERE s_number NOT IN (\'S_1001\',\'S_1002\',\'S_1003\');
2.6 查询年龄为null的记录SELECT * FROM stu
WHERE age IS NULL;
2.7 查询年龄在20到40之间的学生记录SELECT *
FROM stu
WHERE age>=20 AND age<=40;
或者
SELECT *
FROM stu
WHERE age BETWEEN 20 AND 40;
2.8 查询性别非男的学生记录SELECT *
FROM stu
WHERE gender!=\'male\';
或者
SELECT *
FROM stu
WHERE gender<>\'male\';
或者
SELECT *
FROM stu
WHERE NOT gender=\'male\';
2.9 查询姓名不为null的学生记录SELECT *
FROM stu
WHERE NOT sname IS NULL;
或者
SELECT *
FROM stu
WHERE sname IS NOT NULL;
3 模糊查询当想查询姓名中包含a字母的学生时就需要使用模糊查询了。模糊查询需要使用关键字LIKE。
3.1 查询姓名由5个字母构成的学生记录SELECT *
FROM stu
WHERE sname LIKE \'_____\';