多表查询

多表查询

1 修改MySQL密码

​ a: 关闭mySQL服务
​ b: 打开cmd窗口, 输入mysqld –skip-grant-tables (不要关闭此页面)
​ c: 再打开一个cmd窗口,正常登陆,要输入密码时,回车跳过
​ d: 进入mysql ,use mysql数据库,update user set password = password(‘新密码’) where user = ‘root’
​ f:在任务管理器中结束掉mysqld的进程,再启动mysql服务

2 备份和还原数据库

​ 备份数据库:
​ mysqldump -u root -p 数据库名 > 存储路径(绝对路径)
​ 还原数据库
​ 外部: mysql -u root -p 存储路径< 数据库

​ 内部: use 数据库; source 本地存储路径

3 多表关系

​ 外键 foreign key

添加外键语法:    alter table 表名1 add foreign key(外键名称) references 表名2(主键名称)

4 三种表关系

一对多

​ 在多的一方建立外键 指向一方的主键

clip_image002

多对多

​ 建立中间表 存储外键分别指向其他表的主键

clip_image002-1558490610522

一对一

​ 唯一外键对应

​ 假设是一对多,在多的一方创建外键指向一的一方的主键,将外键设置为unique。
​ 主键对应
​ 将两个表的主键建立对应关系即可。

clip_image002-1558490661152

5 多表查询语法

交叉连接查询: 查询出来的是两张表的每一条记录进行匹配的结果 会出现笛卡尔积
    select * from tb1 cross join tb2;
    select * from tb1 , tb2;

内连接:
    select * from tb1 inner join tb2 on 条件; 显示内连接
    select * from tb1 , tb2 where 条件; 隐式内连接

外连接:
    左外连接 select * from tb1 left outer join tb2 on 条件;    查询左表所有和两张表的共有部分
    右外连接 select * from tb1 right outer join tb2 on 条件;    查询右表所有和两张表的共有部分

6 子查询

子查询:一条select语句的执行依赖于另一条select语句

select * from 表名 in/exists/all/any (select *...)

in(...)            在(...)范围内
exists(...)        若(...)存在查询结果,就执行select语句
all(...)        所有
any(...)        任一

7 练习题

计算企业每年营业额的增长率

1558491166063

思路:

年营业额增长率 = (本年营业额 - 上年营业额) / 上年营业额 * 100%

关键在于如何同时获取本年营业额和上年营业额

1
2
3
-- 将该表获取两次,筛选出本年营业额和上年营业额处于一行的数据
-- 使用左外连接 是由于第一年的营业额没有对应的上年营业额,它的增长率应为0%,为了保证数据的完整性,采用此方法.
SELECT * FROM income i1 LEFT JOIN income i2 ON i1.year = i2.year + 1

1558492004088

round()对数据进行取整

concat()拼接数据

ifnull() 对数据为null时,进行处理

1
SELECT i1.*,IFNULL(CONCAT(ROUND((i1.zz -i2.zz)/i2.zz*100),'%'),'0%') AS 增长率 FROM income i1 LEFT JOIN income i2 ON i1.year = i2.year + 1

1558492510616

8 事务

事务: 指的是逻辑上的一组操作,组成这组操作的各个逻辑单元,要么全都成功,要么全都失败。

事务特性:
    原子性/一致性/隔离性/持久性
    原子性: 组成事务的各个逻辑单元不可分割。
    一致性:事务执行的前后,数据完整性保持一致
    隔离性:事务执行过程中,应不受其他事务的干扰
    持久性:事务一旦结束,数据就持久化到数据库中。

如果不考虑事务的隔离级别 在进行操作时有可能出现问题:
    脏读/不可重复读/虚读

事务隔离级别的设置: 
set session isolation transaction level 事务隔离级别
查询事务级别:
    select @@tx_isolation;

事务可以设置的隔离级别:
    read uncommitted    读未提交
    read committed        读已提交
    repeatable read        可重复读
    serializable        串行化

​ 行列转置

利用聚合函数group by

max(case sname when’英语’ then score else 0 end)as “英语”