连接查询、视图、备份恢复和过程与函数

回顾

增删改

-- 增
insert into 表 (列1, 列2 ...) values (值1, 值2 ...),(值1, 值2 ...);

-- 改
update 表 set 列1=新值1, 列2=新值2 ... where 条件

-- 删
delete from 表 where 条件
truncate table 表

查

-- 假设一张员工 emp 表，员工编号 empno, 员工姓名 ename，员工工资 sal，部门编号 deptno

-- 查询全部
select * from emp;

-- 查询某些列
select empno, sal from emp;

-- 查询工资 >=2000 并 <=3000 的员工
select * from emp where sal>=2000 and sal<=3000;
select * from emp where sal between 2000 and 3000;

-- 查询编号等于 1 或 2 或 3 的员工
select * from emp where empno in (1,2,3);
select * from emp where empno=1 or empno=2 or empno=3;

-- 查询姓名以 sm 开头的员工(默认的表不区分值的大小写)
select * from emp where ename like 'sm%';

-- 查询工资为 null 的员工
select * from emp where sal is null;

-- 计算所有员工的工资总和
select sum(sal) from emp;

-- 计算所有员工的人数
select count(*) from emp;

-- 查询人数 >= 6 人 的部门编号
select count(*),deptno from emp group by deptno having count(*)>=6;

-- 查询工资排名前 3 的员工
select * from emp order by sal desc limit 3;

约束有哪几种，作用及语法分别是什么

• primary key
• not null
• unique
• constraint 约束名 foreign key(外键列名) references 表名(主键列名)

表关系有哪几种？如何用主外键描述这几种表关系

• 一对一， 外键建在任意一方，外键+唯一约束
• 多对一，外键建在多的一方
• 多对多，引入中间表，中间表的两个外键分别引用剩余两表的主键

今日内容

1. 连接查询（*）

内联

select ... from 表1, 表2 where 1与2的连接条件;

select ... from 表1, 表2, 表3 where 1与2的连接条件 and 2与3的连接条件;

select ... from 表1 inner join 表2 on 1与2的连接条件;

select ... from 表1 inner join 表2 on 1与2的连接条件 inner join 表3 on 与3的连接条件;

外联

select ... from 表1 left join 表2 on 1与2的连接条件;

select ... from 表1 left join 表2 on 1与2的连接条件 left join 表3 on 与3的连接条件;

子查询

• 情况1： 就是一个值

• select * from 表 where 列 = (子查询)
  

• 情况2： 就是多个值

• select * from 表 where 列 in (子查询)
  

• 情况3：子查询如果是多行多列，可以视为一张临时表，可以与其它表做表连接

• select * from 真实表 e1，(子查询) e2 where 连接条件; // 表别名必须取
  

• 第二种情况的另外解法：SELECT o.* FROM orderlist o, USER u WHERE o.uid=u.id AND u.name IN(‘张三’,‘李四’);

• 第三种情况的另外解法：SELECT u., o. FROM orderlist o, USER u WHERE o.uid=u.id AND o.id > 4;

自联

• 把一张表拆为两张表来看

11. 查询商品类别的名称，以及此类别下商品的个数

11. /* category 
       product 

*/
SELECT * FROM category c, 
  (SELECT cid,COUNT(*) FROM product GROUP BY cid) b
WHERE
   c.id=b.cid;

12. 查询用户张三能够看到的商品信息，
    以及该商品的类别名称，
    以及这个用户所有的订单编号

    SELECT u.*,p.name,c.name,o.number FROM
    USER u,
    us_pro up,
    product p,
    

category c,
orderlist o
WHERE u.id=up.uid AND up.pid=p.id AND p.cid=c.id AND o.uid=u.id AND u.name=‘张三’;
```

2. 视图（了解）

虚拟表 - 并不真正存储数据，查询视图其实是执行了视图对应的查询语句而已

3. 备份恢复（了解）

D:\mysql-5.7.30-winx64\bin>mysqldump -uroot -p db2 > d:\db2.sql
Enter password: ****

D:\mysql-5.7.30-winx64\bin>mysql -uroot -p db5 < d:\db2.sql
Enter password: ****

4. 过程与函数（了解）

• procedure 没有返回值，通过输出参数返回结果
  • 重用 sql 代码
  • 减少网络传输，提高效率
  • 不通用

delimiter $
create procedure 名字(参数列表) 
begin
      sql 语句
end$
delimiter ;

定义变量并赋值

declare 变量名 类型 [default 默认值];

set 变量名 = 值;

select 列 into 变量名 from 表;

条件判断

if 条件1 then sql1
elseif 条件2 then sql2
...
else sqln
end if;

参数传递

create procedure(输入参数名 类型, out 输出参数名 类型) 
begin
	set 输出参数名 = 值; -- 返回方法1
    select 值 into 输出参数名 from 表 -- 返回方法2
end;

• function 必须有返回值，利用返回值返回结果