#1. 连接查询
join 用于多表中字段之间的联系，语法如下：
... from table1 inner|left|right join table2 on condition
table1:左表；table2:右表。
join按照功能大致分为如下三类：
inner join（内连接,或等值连接）：取得两个表中存在连接匹配关系的记录。
left join（左连接）：取得左表（table1）完全记录，即是右表（table2）并无对应匹配记录。
right join（右连接）：与 left join 相反，取得右表（table2）完全记录，即是左表（table1）并无匹配对应记录。
注意：mysql不支持full join
内 连接
select ... from 表1 inner join 表2 on 连接条件 WHERE ... group by ... HAVING ... ORDER BY ... limit ... (重点)
左 外
select ... from 表1 left [outer] join 表2 on 连接条件 (重点)
右 外
select ... from 表1 right [outer] join 表2 on 连接条件
全
select ... from 表1 full join 表2 on 连接条件 (mysql不支持全连接)
例：连接部门表和员工表
select * from emp a inner join dept b on a.deptno=b.deptno;
+-------+--------+-----------+------+------------+---------+---------+--------+--------+------------+----------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno | deptno | dname | loc |
+-------+--------+-----------+------+------------+---------+---------+--------+--------+------------+----------+
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 | 10 | ACCOUNTING | NEW YORK |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 | 10 | ACCOUNTING | NEW YORK |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 | 10 | ACCOUNTING | NEW YORK |
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 | 20 | RESEARCH | DALLAS |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 | 20 | RESEARCH | DALLAS |
| 7788 | SCOTT | ANALYST | 7566 | 1987-07-13 | 3000.00 | NULL | 20 | 20 | RESEARCH | DALLAS |
| 7876 | ADAMS | CLERK | 7788 | 1987-07-13 | 1100.00 | NULL | 20 | 20 | RESEARCH | DALLAS |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 | 20 | RESEARCH | DALLAS |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 | 30 | SALES | CHICAGO |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 | 30 | SALES | CHICAGO |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 | 30 | SALES | CHICAGO |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 | 30 | SALES | CHICAGO |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 | 30 | SALES | CHICAGO |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 | 30 | SALES | CHICAGO |
+-------+--------+-----------+------+------------+---------+---------+--------+--------+------------+----------+
注意：
1) 内连接是将两张表中所有符合连接条件的数据列入结果，不符合连接条件的结果中没有，例如40号部门
2) 如果连接的两表中有同名的列，列前面要加表名(或表别名)来区分（否则会报歧义错误）
3) inner join ... on 的写法是符合SQL-92标准写法,其实还有一种内连接的写法：
select ... from 表1, 表2 where 连接条件;
例： 还是连接部门表和员工表 select * from emp a, dept b where a.deptno=b.deptno;
例子：采用左外连接员工表和部门表
select * from emp a left join dept b on a.deptno = b.deptno;
+-------+--------+-----------+------+------------+---------+---------+--------+--------+------------+----------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno | deptno | dname | loc |
+-------+--------+-----------+------+------------+---------+---------+--------+--------+------------+----------+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 | 20 | RESEARCH | DALLAS |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 | 30 | SALES | CHICAGO |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 | 30 | SALES | CHICAGO |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 | 20 | RESEARCH | DALLAS |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 | 30 | SALES | CHICAGO |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 | 30 | SALES | CHICAGO |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 | 10 | ACCOUNTING | NEW YORK |
| 7788 | SCOTT | ANALYST | 7566 | 1987-07-13 | 3000.00 | NULL | 20 | 20 | RESEARCH | DALLAS |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 | 10 | ACCOUNTING | NEW YORK |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 | 30 | SALES | CHICAGO |
| 7876 | ADAMS | CLERK | 7788 | 1987-07-13 | 1100.00 | NULL | 20 | 20 | RESEARCH | DALLAS |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 | 30 | SALES | CHICAGO |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 | 20 | RESEARCH | DALLAS |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 | 10 | ACCOUNTING | NEW YORK |
+-------+--------+-----------+------+------------+---------+---------+--------+--------+------------+----------+
例子：采用左外连接部门表和员工表
select * from dept b left join emp a on a.deptno = b.deptno;
+--------+------------+----------+-------+--------+-----------+------+------------+---------+---------+--------+
| deptno | dname | loc | empno | ename | job | mgr | hiredate | sal | comm | deptno |
+--------+------------+----------+-------+--------+-----------+------+------------+---------+---------+--------+
| 10 | ACCOUNTING | NEW YORK | 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 |
| 10 | ACCOUNTING | NEW YORK | 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 |
| 10 | ACCOUNTING | NEW YORK | 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 |
| 20 | RESEARCH | DALLAS | 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 |
| 20 | RESEARCH | DALLAS | 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
| 20 | RESEARCH | DALLAS | 7788 | SCOTT | ANALYST | 7566 | 1987-07-13 | 3000.00 | NULL | 20 |
| 20 | RESEARCH | DALLAS | 7876 | ADAMS | CLERK | 7788 | 1987-07-13 | 1100.00 | NULL | 20 |
| 20 | RESEARCH | DALLAS | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
| 30 | SALES | CHICAGO | 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
| 30 | SALES | CHICAGO | 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
| 30 | SALES | CHICAGO | 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |
| 30 | SALES | CHICAGO | 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 |
| 30 | SALES | CHICAGO | 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 |
| 30 | SALES | CHICAGO | 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 |
| 40 | OPERATIONS | BOSTON | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
+--------+------------+----------+-------+--------+-----------+------+------------+---------+---------+--------+
左外连接，首先将符合连接条件的记录连在一起，作为结果，其次左边表中不符合连接条件的记录也会出现在结果中，只不过它对应的右边的列都是NULL
例子：右外连接
select * from emp a right join dept b on a.deptno = b.deptno;
注意：左外和右外与表的先后次序有关，而内连接与表的先后次序无关
#2. 多表连接
select * from 表1
inner join 表2 on 连接条件
inner join 表3 on 连接条件
...
例子
select * from student a inner join sc b on a.sid = b.sid
inner join course c on b.cid = c.cid
inner join teacher d on c.tid = d.tid
order by a.sid, b.cid;
等价写法：
select * from student a, sc b, course c, teacher d
where a.sid=b.sid and b.cid=c.cid and c.tid=d.tid;
左外多表连接：
select * from student a left join sc b on a.sid = b.sid
left join course c on b.cid = c.cid
left join teacher d on c.tid = d.tid
order by a.sid, b.cid;
注意：左外多表连接要全部使用left join，不能再出现inner join，否则不符合连接条件的将不会出现在结果中。
性能上：连接的表越多，性能越低, 可以把连接查询变成分多次查询
#3. 自连接
一个表自己和自己连接
找到员工的姓名和上级的姓名
select a.empno,a.ename,a.mgr,b.empno,b.ename,b.mgr
from emp a left join emp b on a.mgr=b.empno;
以后经常用于树状结构的数据表示
陕西省
西安
雁塔区
高新区
咸阳
宝鸡
id name parent_id
1 陕西省 null
2 西安 1
3 雁塔区 2
#4. 子查询
把某个select结果当做一个值，或一张表做进一步的查询
使用子查询时要注意以下几点：
（1）子查询是指 嵌套在查询内部的 查询，且必须始终出现在圆括号内。
（2）子查询可以包含多个关键字或条件，如：distinct/ group by / order by / limit / 函数等。
（3）子查询的外层查询可以是：select， insert， update ， set。
（4）子查询可以返回标量、一行、一列或子查询。
情况1：找具有最高工资的员工信息（子查询作为值）
select max(sal) from emp; // 5000
select * from emp where sal = (select max(sal) from emp);
把select max(sal) from emp当成了一个值，代入到主查询当中，代入时需要在子查询的两边加()
情况2： 获取每个部门的平均工资和部门的名称（子查询作为表）
先查询平均工资
(select deptno,avg(sal) from emp group by deptno) a
再把子查询看做临时表，与其它表做表连接
select * from (子查询)a inner join dept b on a.deptno=b.deptno;
练习：
1）查询所有课程成绩都小于等于60分的同学的学号、姓名；
select a.sid, sname from (select sid, max(score) from sc group by sid having max(score)<=60) b inner join student a on a.sid=b.sid;
2）查询所有同学的学号、姓名、选课数、总成绩
(select sid, count(*),sum(score) from sc group by sid) a
select * from (select sid, count(*),sum(score) from sc group by sid) a
inner join student b on a.sid=b.sid;
select a.*,b.sname from (select sid, count(*) 选课数,sum(score) 总成绩 from sc group by sid) a
inner join student b on a.sid=b.sid;
3) 查询学生平均成绩大于80的所有学生的学号、姓名和平均成绩
(select sid, avg(score) 平均成绩 from sc group by sid having(avg(score)>80)) a
select a.*, b.sname from (select sid, avg(score) 平均成绩 from sc group by sid having(avg(score)>80))a
inner join student b on a.sid=b.sid;
4) 查询课程相同且成绩相同的的学生的学号、课程号、学生成绩
(select cid,score from sc group by cid,score having count(*) > 1) a
select * from (select cid,score from sc group by cid,score having count(*) > 1)a
inner join sc b on a.cid=b.cid and a.score=b.score;
5）查询两门以上不及格课程的同学的学号及其平均成绩 ；
select b.sid, avg(score) from ((select sid, count(*) from sc group by sid, score having score <60 and count(*)>=2) b inner join sc a on a.sid=b.sid) group by sid;
6）查询学过1号课程并且也学过编号2号课程的同学的学号、姓名
select c.sid, sname from (select a.sid from sc a, sc b where a.sid=b.sid and a.cid=1 and b.cid=2) c inner join student d on c.sid=d.sid;
7）查询没学过“叶平”老师课的同学的学号、姓名
select student.sid, sname from student where sid not in (select e.sid from (select sid from (select cid, cname from (select tid from teacher where tname='叶平') a inner join course b on a.tid=b.tid) c inner join sc d on c.cid=d.cid) e inner join student f on e.sid=f.sid);
8) 查询每个部门有最高工资的员工的（所有）信息
(select deptno,max(sal) msal from emp group by deptno) a
select b.* from (select deptno,max(sal) msal from emp group by deptno)a
inner join emp b on a.deptno=b.deptno and a.msal=b.sal;
情况3： 将子查询当做一个函数 (了解)
(select max(sal) from emp where deptno =?)
m(deptno) 返回结果是这个deptno下的最大工资
部门编号是一个入参, 最大工资是返回结果
select * from emp e where sal = m(e.deptno); // 伪代码
select * from emp e where sal = (select max(sal) from emp where deptno = e.deptno);
#6. 事务
DDL create alter drop truncate
DML insert update delete select
TCL start transaction, commit, rollback
## 6.1 start transaction 开始事务 (begin)
## 6.2 commit 提交事务
## 6.3 rollback 回滚事务
账户表account
ID balance(余额)
1 50000.0
2 0.0
create table account (
id int primary key,
balance decimal(12,2) not null
);
insert into account(id,balance)values(1,50000.0),(2,0.0);
以下两条sql必须作为一个整体执行, 要么都成功，其中有一条失败，前面成功的也得撤销
update account set balance=balance+10000.0 where id=2; /*2号账户转入10000元*/ 成功
update account set balance=balance-10000.0 where id=1; /*1号账户转出10000元*/
所谓的事务，就是指一个事务内，多条sql语句是作为一个整体执行的。
一个事务内的多条sql是作为一个原子操作，不可以被分割。要么都成功，要么都不成功。
start TRANSACTION;
UPDATE
UPDATE
INSERT
DELETE
如果这个事务内多条sql全部成功 COMMIT(让更改都生效)
如果这个事务内有sql失败了，Rollback（让更改都撤销）
事务内所有更改，在结束之前，对于其它用户来讲都是不可见的。
事务commit提交时，这些更改才会真正生效，其它用户才能看到你的更改。
事务执行中如果出现意外情况，这时候可以执行rollback，可以撤销事务内所有更改，恢复到事务开始的时刻
commit 和rollback都意味着事务结束
事务有四大特性
ACID
A 原子性, 指事务内多条sql是作为一个整体执行
C 一致性, 事务开始前后，整个数据的状态应当一致
I 隔离性, 指事务的隔离级别(未提交读，提交读，可重复读，序列化读)
1) 脏读(读取到了未提交的数据)
客户1 客户2
1 号账户余额 10000.0
begin;
update 1 号账户余额50000.0
select 1 号账户余额 50000.0 脏读
rollback;
select 1 号账户余额 50000.0
2)避免脏读现象, 将隔离级别升级为提交读
查询到的肯定是别人提交后的结果，提交读下不会有脏读, 但会有不可重复读现象:
客户1 更新 客户2查询
1 号账户余额 10000.0
begin;
select ... 10000.0
begin;
update 1 号账户余额50000.0;
commit;
select ... 50000.0
commit;
3) 为了避免不可重复和脏读的现象，可以将隔离级别升级可重复读（mysql默认隔离级别）
4) 幻读 (可以将隔离级别提高为序列化读，即可避免幻读现象)
客户1 新增 客户2查询
原始记录是10条
begin begin;
查询个数 10
insert 1
commit;
查询个数 11
commit;
5) mysql的【可重复读】隔离级别三种现象都可以避免
D 持久性, 事务中做的更改必须在事务结束后永久生效
增删改查(insert update delete select)
CRUD c insert 插入
r select 查询
u update 更新
d delete 删除
# 7. DCL 数据控制语言 (了解)
grant 授权
revoke 回收权限
create user 'user1'@'localhost' identified by 'user1';
登录之后执行use test3;
会报告 Access denied for user 'user1' 含义是用户无权访问.
使用root 给user1授权
grant all on test3.* to 'user1'@'localhost';
all 是代表所有权限：select,insert,update,delete...
test3.* 是权限的范围：test3库中所有对象
to 后面跟的是用户
使用root回收权限
revoke all on test3.* from 'user1'@'localhost';
更细的权限分配
grant select on test3.student to 'user1'@'localhost'; /*只让test1用户能够查询test3.student表*/
492
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
