1. insert
我们先创建一个表结构，这部分操作我们使用这张表完成我们的操作：
mysql> create table student(
-> id int primary key auto_increment,
-> name varchar(20) not null,
-> qq varchar(20) unique
-> );
Query OK, 0 rows affected (0.04 sec)
示例演示：
-- 单行插入
mysql> insert into student values(100, '张三', '11111');
Query OK, 1 row affected (0.00 sec)
mysql> insert into student values(101, '李四', NULL);
Query OK, 1 row affected (0.01 sec)
-- 多行插入
mysql> insert into student(name,qq) values('王五', '22222'),('赵六',' ');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from student;
+-----+--------+-------+
| id | name | qq |
+-----+--------+-------+
| 100 | 张三 | 11111 |
| 101 | 李四 | NULL |
| 102 | 王五 | 22222 |
| 103 | 赵六 | |
+-----+--------+-------+
4 rows in set (0.00 sec)
-- 插入冲突更新
mysql> insert into student(id,name,qq) values(102,'周七', '33333');
ERROR 1062 (23000): Duplicate entry '102' for key 'student.PRIMARY'
mysql> insert into student(id,name,qq) values(102,'周七', '33333') on duplicate key update name = '周七', qq = '33333';
Query OK, 2 rows affected (0.00 sec)
mysql> select * from student;
+-----+--------+-------+
| id | name | qq |
+-----+--------+-------+
| 100 | 张三 | 11111 |
| 101 | 李四 | NULL |
| 102 | 周七 | 33333 |
| 103 | 赵六 | |
+-----+--------+-------+
4 rows in set (0.00 sec)
-- 替换
mysql> replace into student (id, name) VALUES (100, '田八');
Query OK, 2 rows affected (0.00 sec)
mysql> select * from student;
+-----+--------+-------+
| id | name | qq |
+-----+--------+-------+
| 100 | 田八 | NULL |
| 101 | 李四 | NULL |
| 102 | 周七 | 33333 |
| 103 | 赵六 | |
+-----+--------+-------+
4 rows in set (0.00 sec)
2. update
-- 将张三同学的数学成绩变更为 100 分
mysql> update exam_result set math = 100 where name = '张三';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from exam_result;
+----+--------+---------+------+---------+
| id | name | chinese | math | english |
+----+--------+---------+------+---------+
| 2 | 张三 | 67 | 100 | 56 |
| 3 | 李四 | 87 | 78 | 77 |
| 4 | 王五 | 88 | 98 | 90 |
| 5 | 赵六 | 82 | 84 | 67 |
| 6 | 田八 | 70 | 73 | 78 |
| 7 | 刘九 | 75 | 65 | 30 |
+----+--------+---------+------+---------+
6 rows in set (0.00 sec)
-- 将张三同学的数学成绩变更为 60 分，语文成绩变更为 70 分
mysql> update exam_result set math = 60,chinese = 70 where name = '张三';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from exam_result;
+----+--------+---------+------+---------+
| id | name | chinese | math | english |
+----+--------+---------+------+---------+
| 2 | 张三 | 70 | 60 | 56 |
| 3 | 李四 | 87 | 78 | 77 |
| 4 | 王五 | 88 | 98 | 90 |
| 5 | 赵六 | 82 | 84 | 67 |
| 6 | 田八 | 70 | 73 | 78 |
| 7 | 刘九 | 75 | 65 | 30 |
+----+--------+---------+------+---------+
6 rows in set (0.00 sec)
-- 将总成绩倒数前三的 3 位同学的数学成绩加上 30 分
mysql> update exam_result set math = math + 30 order by chinese + math + english limit 3;
Query OK, 3 rows affected (0.01 sec)
Rows matched: 3 Changed: 3 Warnings: 0
mysql> select * from exam_result;
+----+--------+---------+------+---------+
| id | name | chinese | math | english |
+----+--------+---------+------+---------+
| 2 | 张三 | 70 | 90 | 56 |
| 3 | 李四 | 87 | 78 | 77 |
| 4 | 王五 | 88 | 98 | 90 |
| 5 | 赵六 | 82 | 84 | 67 |
| 6 | 田八 | 70 | 103 | 78 |
| 7 | 刘九 | 75 | 95 | 30 |
+----+--------+---------+------+---------+
6 rows in set (0.00 sec)
-- 将所有同学的语文成绩更新为原来的 2 倍
mysql> update exam_result set chinese = chinese * 2;
Query OK, 6 rows affected (0.00 sec)
Rows matched: 6 Changed: 6 Warnings: 0
mysql> select * from exam_result;
+----+--------+---------+------+---------+
| id | name | chinese | math | english |
+----+--------+---------+------+---------+
| 2 | 张三 | 140 | 90 | 56 |
| 3 | 李四 | 174 | 78 | 77 |
| 4 | 王五 | 176 | 98 | 90 |
| 5 | 赵六 | 164 | 84 | 67 |
| 6 | 田八 | 140 | 103 | 78 |
| 7 | 刘九 | 150 | 95 | 30 |
+----+--------+---------+------+---------+
6 rows in set (0.00 sec)3. delete
-- 删除张三同学的考试成绩
mysql> delete from exam_result where name = '张三';
Query OK, 1 row affected (0.00 sec)
mysql> select * from exam_result;
+----+--------+---------+------+---------+
| id | name | chinese | math | english |
+----+--------+---------+------+---------+
| 3 | 李四 | 174 | 78 | 77 |
| 4 | 王五 | 176 | 98 | 90 |
| 5 | 赵六 | 164 | 84 | 67 |
| 6 | 田八 | 140 | 103 | 78 |
| 7 | 刘九 | 150 | 95 | 30 |
+----+--------+---------+------+---------+
5 rows in set (0.00 sec)
-- 删除整张表
delete from for_delete;
Query OK, 3 rows affected (0.00 sec)
SELECT * FROM for_delete;
Empty set (0.00 sec)
-- 截断整表数据，注意影响行数是 0，所以实际上没有对数据真正操作
truncate for_truncate;
Query OK, 0 rows affected (0.10 sec)
SELECT * FROM for_truncate;
Empty set (0.00 sec)
-- delete 和 truncate 的其中一个区别是truncate会清空auto_increment。4. select
这部分使用下面的表进行操作：
mysql> create table exam_result(
-> id int primary key auto_increment,
-> name varchar(20) not null,
-> chinese float default 0,
-> math float default 0,
-> english float default 0
-> );
Query OK, 0 rows affected (0.03 sec)
mysql> insert into exam_result(name,chinese,math,english) values('张三',67,98,56),
-> ('李四',87,78,77);
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> insert into exam_result(name,chinese,math,english) values('王五', 88, 98, 90), ('赵六', 82, 84, 67),('田八', 70, 73, 78),('刘九', 75, 65, 30);
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0
4.1 基本查询
4.1.1 简单查询
-- 全列查询
mysql> select * from exam_result;
+----+--------+---------+------+---------+
| id | name | chinese | math | english |
+----+--------+---------+------+---------+
| 2 | 张三 | 67 | 98 | 56 |
| 3 | 李四 | 87 | 78 | 77 |
| 4 | 王五 | 88 | 98 | 90 |
| 5 | 赵六 | 82 | 84 | 67 |
| 6 | 田八 | 70 | 73 | 78 |
| 7 | 刘九 | 75 | 65 | 30 |
+----+--------+---------+------+---------+
6 rows in set (0.00 sec)
-- 查询部分列
mysql> select id,name,chinese from exam_result;
+----+--------+---------+
| id | name | chinese |
+----+--------+---------+
| 2 | 张三 | 67 |
| 3 | 李四 | 87 |
| 4 | 王五 | 88 |
| 5 | 赵六 | 82 |
| 6 | 田八 | 70 |
| 7 | 刘九 | 75 |
+----+--------+---------+
6 rows in set (0.00 sec)
-- 查询字段为表达式
mysql> select id,name,chinese+math+english from exam_result;
+----+--------+----------------------+
| id | name | chinese+math+english |
+----+--------+----------------------+
| 2 | 张三 | 221 |
| 3 | 李四 | 242 |
| 4 | 王五 | 276 |
| 5 | 赵六 | 233 |
| 6 | 田八 | 221 |
| 7 | 刘九 | 170 |
+----+--------+----------------------+
6 rows in set (0.00 sec)
-- 为查询列指定别名
mysql> select id,name,chinese+math+english 总分 from exam_result;
+----+--------+--------+
| id | name | 总分 |
+----+--------+--------+
| 2 | 张三 | 221 |
| 3 | 李四 | 242 |
| 4 | 王五 | 276 |
| 5 | 赵六 | 233 |
| 6 | 田八 | 221 |
| 7 | 刘九 | 170 |
+----+--------+--------+
6 rows in set (0.00 sec)
-- 去重查询
mysql> select distinct math from exam_result;
+------+
| math |
+------+
| 98 |
| 78 |
| 84 |
| 73 |
| 65 |
+------+
5 rows in set (0.01 sec)
4.1.2 where
比较运算符：
| 运算符 | 说明 |
|---|---|
| >, >=,<,<= | 大于，大于等于，小于，小于等于 |
| = | 等于，NULL 不安全，例如 NULL = NULL 的结果是 NULL |
| <=> | 等于，NULL 安全，例如 NULL NULL 的结果是 TRUE(1) |
| !=, <> | 不等于 |
| BETWEEN a0 AND a1 | 范围匹配，[a0, a1]，如果 a0 <= values <=a1，返回 TRUE(1) |
| IN (option, ...) | 如果是 option 中的任意一个，返回 TRUE(1) |
| IS NULL | 是 NULL |
| IS NOT NULL | 不是 NULL |
| LIKE | 模糊匹配。% 表示任意多个（包括 0 个）任意字符；_ 表示任意一个字符 |
逻辑运算符：
| 运算符 | 说明 |
|---|---|
| AND | 多个条件必须都为 TRUE(1)，结果才是 TRUE(1) |
| OR | 任意一个条件为 TRUE(1), 结果为 TRUE(1) |
| NOT | 条件为 TRUE(1)，结果为 FALSE(0) |
示例演示：
-- 英语不及格的同学及英语成绩 ( < 60 )
mysql> select name,english from exam_result where english < 60;
+--------+---------+
| name | english |
+--------+---------+
| 张三 | 56 |
| 刘九 | 30 |
+--------+---------+
2 rows in set (0.00 sec)
-- 语文成绩在 [80, 90] 分的同学及语文成绩
mysql> select name,chinese from exam_result where chinese between 80 and 90;
+--------+---------+
| name | chinese |
+--------+---------+
| 李四 | 87 |
| 王五 | 88 |
| 赵六 | 82 |
+--------+---------+
3 rows in set (0.00 sec)
mysql> select name,chinese from exam_result where chinese >= 80 and chinese <= 90;
+--------+---------+
| name | chinese |
+--------+---------+
| 李四 | 87 |
| 王五 | 88 |
| 赵六 | 82 |
+--------+---------+
3 rows in set (0.00 sec)
-- 数学成绩是 58 或者 59 或者 98 或者 99 分的同学及数学成绩
mysql> select name,math from exam_result where math = 58 or math = 59 or math = 98 or math = 99;
+--------+------+
| name | math |
+--------+------+
| 张三 | 98 |
| 王五 | 98 |
+--------+------+
2 rows in set (0.00 sec)
mysql> select name,math from exam_result where math in(58,59,98,99);
+--------+------+
| name | math |
+--------+------+
| 张三 | 98 |
| 王五 | 98 |
+--------+------+
2 rows in set (0.01 sec)
-- 姓张的同学 及 赵某同学
mysql> select name from exam_result where name like '张%' or name like '赵_';
+--------+
| name |
+--------+
| 张三 |
| 赵六 |
+--------+
2 rows in set (0.00 sec)
-- 语文成绩好于英语成绩的同学
mysql> select name,chinese,english from exam_result where chinese > english;
+--------+---------+---------+
| name | chinese | english |
+--------+---------+---------+
| 张三 | 67 | 56 |
| 李四 | 87 | 77 |
| 赵六 | 82 | 67 |
| 刘九 | 75 | 30 |
+--------+---------+---------+
4 rows in set (0.00 sec)
-- 总分在 200 分以下的同学
mysql> select name,chinese+math+english from exam_result where chinese + math + english < 200;
+--------+----------------------+
| name | chinese+math+english |
+--------+----------------------+
| 刘九 | 170 |
+--------+----------------------+
1 row in set (0.00 sec)
-- 语文成绩 > 80 并且不姓张的同学
mysql> select name,chinese from exam_result where chinese > 80 and name not like '张%';
+--------+---------+
| name | chinese |
+--------+---------+
| 李四 | 87 |
| 王五 | 88 |
| 赵六 | 82 |
+--------+---------+
3 rows in set (0.00 sec)
-- 张某同学，否则要求总成绩 > 200 并且 语文成绩 < 数学成绩 并且 英语成绩 > 80
mysql> select * from exam_result where name like '张%' or (chinese + math + english > 200 and chinese < math and english >80);
+----+--------+---------+------+---------+
| id | name | chinese | math | english |
+----+--------+---------+------+---------+
| 2 | 张三 | 67 | 98 | 56 |
| 4 | 王五 | 88 | 98 | 90 |
+----+--------+---------+------+---------+
2 rows in set (0.00 sec)
4.1.3 order by
-- 同学及数学成绩，按数学成绩升序显示
mysql> select name,math from exam_result order by math;
+--------+------+
| name | math |
+--------+------+
| 刘九 | 65 |
| 田八 | 73 |
| 李四 | 78 |
| 赵六 | 84 |
| 张三 | 98 |
| 王五 | 98 |
+--------+------+
6 rows in set (0.01 sec)
-- 同学及总分，按总分排序显示
mysql> select name,chinese + math + english 总分 from exam_result order by 总分;
+--------+--------+
| name | 总分 |
+--------+--------+
| 刘九 | 170 |
| 张三 | 221 |
| 田八 | 221 |
| 赵六 | 233 |
| 李四 | 242 |
| 王五 | 276 |
+--------+--------+
6 rows in set (0.00 sec)
-- 查询同学各门成绩，依次按 数学降序，英语升序，语文升序的方式显示
mysql> select * from exam_result order by math desc,english,chinese;
+----+--------+---------+------+---------+
| id | name | chinese | math | english |
+----+--------+---------+------+---------+
| 2 | 张三 | 67 | 98 | 56 |
| 4 | 王五 | 88 | 98 | 90 |
| 5 | 赵六 | 82 | 84 | 67 |
| 3 | 李四 | 87 | 78 | 77 |
| 6 | 田八 | 70 | 73 | 78 |
| 7 | 刘九 | 75 | 65 | 30 |
+----+--------+---------+------+---------+
6 rows in set (0.00 sec)
-- 查询姓张的同学或者姓刘的同学数学成绩，结果按数学成绩由高到低显示
mysql> select * from exam_result where name like '张%' or name like '刘%' order by math desc;
+----+--------+---------+------+---------+
| id | name | chinese | math | english |
+----+--------+---------+------+---------+
| 2 | 张三 | 67 | 98 | 56 |
| 7 | 刘九 | 75 | 65 | 30 |
+----+--------+---------+------+---------+
2 rows in set (0.00 sec)4.1.4 limit offset
对查询结果进行分页处理：
mysql> select * from exam_result limit 3 offset 0;
+----+--------+---------+------+---------+
| id | name | chinese | math | english |
+----+--------+---------+------+---------+
| 2 | 张三 | 67 | 98 | 56 |
| 3 | 李四 | 87 | 78 | 77 |
| 4 | 王五 | 88 | 98 | 90 |
+----+--------+---------+------+---------+
3 rows in set (0.00 sec)
mysql> select * from exam_result limit 3 offset 3;
+----+--------+---------+------+---------+
| id | name | chinese | math | english |
+----+--------+---------+------+---------+
| 5 | 赵六 | 82 | 84 | 67 |
| 6 | 田八 | 70 | 73 | 78 |
| 7 | 刘九 | 75 | 65 | 30 |
+----+--------+---------+------+---------+
3 rows in set (0.00 sec)
mysql> select * from exam_result limit 3 offset 6;
Empty set (0.00 sec)
4.1.5 insert into
mysql> insert into son_table select * from exam_result;
Query OK, 5 rows affected (0.01 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> select * from son_table;
+----+--------+---------+------+---------+
| id | name | chinese | math | english |
+----+--------+---------+------+---------+
| 3 | 李四 | 174 | 78 | 77 |
| 4 | 王五 | 176 | 98 | 90 |
| 5 | 赵六 | 164 | 84 | 67 |
| 6 | 田八 | 140 | 103 | 78 |
| 7 | 刘九 | 150 | 95 | 30 |
+----+--------+---------+------+---------+
5 rows in set (0.00 sec)4.1.6 group by
在select中使用group by 子句可以对指定列进行分组查询
-- 显示每个部门的平均工资和最高工资
select deptno,avg(sal),max(sal) from EMP group by deptno;
-- 每个部门的每种岗位的平均工资和最低工资
select avg(sal),min(sal),job, deptno from EMP group by deptno, job;
4.2 复合查询
4.2.1 多表连接
接下来我们使用下面两张表来进行查询：
• 显示雇员名、雇员工资以及所在部门的名字
select EMP.ename, EMP.sal, DEPT.dname from EMP, DEPT where EMP.deptno = DEPT.deptno;
• 显示部门号为10的部门名，员工名和工资
select ename, sal,dname from EMP, DEPT where EMP.deptno = DEPT.deptno and DEPT.deptno = 10;
• 显示各个员工的姓名，工资，及工资级别
select ename, sal, grade from EMP, SALGRADE where EMP.sal between losal and hisal;4.2.2 自连接
• 显示员工FORD的上级领导的编号和姓名
-- 使用的子查询
select empno,ename from emp where emp.empno = (select mgr from emp where ename = 'FORD');
-- 使用多表查询（自查询）
--from emp leader, emp worker，给自己的表起别名，因为要先做笛卡尔积，所以别名可以先识别
select leader.empno,leader.ename from emp leader, emp worker where
leader.empno = worker.mgr and worker.ename='FORD';
4.2.3 子查询
• 显示SMITH同一部门的员工
select * from EMP WHERE deptno = (select deptno from EMP where ename = 'smith');• 查询和10号部门的工作岗位相同的雇员的名字，岗位，工资，部门号，但是不包含10自己的
select ename,job,sal,deptno from emp where
job in (select distinct job from emp where deptno = 10) and deptno<>10;
• 显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号
select ename, sal, deptno from EMP
where sal > all(select sal from EMP where deptno = 30);• 显示工资比部门30的任意员工的工资高的员工的姓名、工资和部门号（包含自己部门的员工）
select ename, sal, deptno from EMP
where sal > any(select sal from EMP where deptno = 30);• 查询和SMITH的部门和岗位完全相同的所有雇员，不含SMITH本人
select ename from EMP
where (deptno, job) = (select deptno, job from EMP where ename = 'SMITH') and ename <> 'SMITH';• 显示每个高于自己部门平均工资的员工的姓名、部门、工资、平均工资
select ename, deptno, sal, format(asal,2) from EMP,
(select avg(sal) asal, deptno dt from EMP group by deptno) tmp
where EMP.sal > tmp.asal and EMP.deptno = tmp.dt;
• 查找每个部门工资最高的人的姓名、工资、部门、最高工资
select EMP.ename, EMP.sal, EMP.deptno, ms from EMP,
(select max(sal) ms, deptno from EMP group by deptno) tmp
where EMP.deptno=tmp.deptno and EMP.sal=tmp.ms;
4.2.4 合并查询
• 将工资大于2500或职位是MANAGER的人找出来
select ename, sal, job from EMP
where sal > 2500
union
select ename, sal, job from EMP
where job = 'MANAGER';--去掉了重复记录
• 将工资大于25000或职位是MANAGER的人找出来
select ename, sal, job from EMP
where sal > 2500
union all
select ename, sal, job from EMP
where job = 'MANAGER';
4.3 内外连接
4.3.1 内连接
内连接实际上就是利用where子句对两种表形成的笛卡儿积进行筛选。
• 显示SMITH的名字和部门名称
-- 用前面的写法
select ename, dname from EMP, DEPT
where EMP.deptno = DEPT.deptno and ename = 'SMITH';
-- 用标准的内连接写法
select ename, dname from EMP inner
join DEPT on EMP.deptno = DEPT.deptno and ename = 'SMITH';
4.3.2 外连接
左外连接
如果联合查询，左侧的表完全显示我们就说是左外连接
• 查询所有学生的成绩，如果这个学生没有成绩，也要将学生的个人信息显示出来
select * from stu left join exam on stu.id = exam.id; 右外连接
如果联合查询，右侧的表完全显示我们就说是右外连接。
• 对stu表和exam表联合查询，把所有的成绩都显示出来，即使这个成绩没有学生与它对应，也要显示出来
select * from stu right join exam on stu.id = exam.id;