1 多表设计-外键
要关联两个表,需要使用到外键,即两个表有关联的字段需要使用外键
外键语法:
1.创建表时指定
create table 表名(
字段名 数据类型.....
[constraint] [外键名称] foreign key (外键字段名) reference 主表(字段名))
2.建完表后创建外键
alter table 表名 add constrain 外键名称 foreign key (外键字段名) reference 主表(字段名)
除了查询语句需要掌握以外,其他的语句可以根据需求掌握
2 多表查询
2.1 内连接
查询两张表有交集的数据
1)隐式内连接:select 字段列表 from 表1,表2 where 条件 .....;
2)显式内连接:select 字段列表 from 表1 [inner] join 表2 on 连接条件....;
# 查询员工的姓名,及所属的部门名称
select tb_emp.name,tb_dept.name from tb_emp,tb_dept where tb_emp.dept_id = tb_dept.id;
# 查询员工的姓名,及所属的部门名称
select tb_emp.name,tb_dept.name from tb_emp inner join tb_dept on tb_emp.dept_id = tb_dept.id;
# 当表名过长时可以给表起别名
select e.name,d.name from tb_emp e,tb_dept d where e.dept_id = d.id;
2.2 外连接
包含一整个表独立的内容以及与其他表有交集的部分
左外连接就包括左边表的全部内容,右外连接就包含右边的全部内容(左右针对写sql语句时查找表的先后顺序)
#查询所有员工的姓名以及所属的部门名称(左外连接)
select e.name,d.name from tb_emp e left outer join tb_dept d on e.dept_id = d.id;
#查询部门表所有部门的名称,和对应的员工名称(右外连接)
select d.name,e.name from tb_emp e right outer join tb_dept d on d.id = e.dept_id;
2.3 子查询(嵌套查询)
分为四类:第一类为标量子查询,子查询返回的结果值为单个;第二类为列子查询返回的结果为一列;第三类为行子查询返回的结果为一行;第四类为表子查询返回的结果为多行多列.
#标量子查询
#查询教研部的所有员工的名称
select id from tb_dept where name = '教研部';
select * from tb_emp where tb_emp.dept_id = (select id from tb_dept where name = '教研部');
#查询在'方东白'入职之后的员工信息
select entrydate from tb_emp where name = '方东白';
select * from tb_emp where entrydate >= (select entrydate from tb_emp where name = '方东白');
#列子查询 查询教研部和咨询部的所有员工信息(结果返回一列)
select id from tb_dept where name='教研部' or name = '咨询部';
select * from tb_emp where dept_id in (select id from tb_dept where name='教研部' or name = '咨询部');
#行子查询(返回一行,可以有多列)
#查询与韦一笑的入职日期及职位都相同的员工信息
select entrydate,job from tb_emp where name = '韦一笑';
select * from tb_emp where entrydate = (select entrydate from tb_emp where name = '韦一笑') and job = (select job from tb_emp where name = '韦一笑');
select * from tb_emp where (entrydate,job) = (select entrydate,job from tb_emp where name = '韦一笑') ;
#表子查询
#查询入职日期是"2006-01-01"之后的员工信息,及其部门名称
select e.*,d.name from (select * from tb_emp where entrydate > '2006-01-01') e ,tb_dept d where e.dept_id = d.id;
查询语句举例:
#查询价格低于10菜品的名称,价格及其菜品的分类名称
select * from dish d,category c where d.category_id = c.id and d.price < 10;
#2查询价格为10到50的菜品且状态为起售的菜品,展示菜品的名称价格以及分类
select d.name,d.price,c.name from dish d left outer join category c on d.category_id = c.id where d.status = 1 and d.price >= 10 and d.price <= 50 ;
#查询每个分类下最贵的菜品,展示出分类的名称最贵的菜品的价格
select c.name,max(d.price) as '最贵的菜品' from dish d,category c where d.category_id = c.id group by c.name;
#查询哥各个分类下菜品状态为起售,并且该分类下菜品总数量大于等于3 的分类名称
select c.name,count(*) from category c,dish d where c.id = d.category_id and d.status = 1 group by c.name having count(*)>=3 ;
#查询出"商务套餐A"中包含哪些菜品(展示出套餐名称,价格,包括菜品名称,价格,份数)
select s.name, s.price, d.name, d.price, sd.copies
from setmeal s,
setmeal_dish sd,
dish d
where s.id = sd.setmeal_id
and s.name = '商务套餐A'
and d.id = sd.dish_id;
#查询出低于菜品平均价格的菜品信息
select avg(d.price) from dish d ;
select * from dish d where d.price < (select avg(d.price) from dish d);
3 索引
为表创建索引时可以提高查找效率,
索引语法:
1)创建索引:create [unique] index 索引名 on 表名(字段名....).
2)查看索引:show index from 表名.
3)删除索引:drop index 索引名 on 表名