Mysql--增删改查语句(多表)

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 表名