多表链接测试

表一：product（商品表）
	pid	pname	pprice
	1	手机	2300
	2	电脑	5600
	3	照相机	1200
	4	投影仪	2500
表二：user（用户表）
	uid	uname	uage
	1	李三	20
	2	张四	23
	3	赵五	25
	4	孙六	18
	5	孙悟空	24
表三：order（订单表）
	opid	ouid	onumber(点单号)
	1	1	123123
	1	2	112233
	2	1	234567
	2	5	787878
	3	4	343421
	3	5	909090
	4	2	212112
	4	1	343421
1：查询孙姓用户的个数
2：查询年纪最大的2个用户名
3：查询李三买的商品名
4：查询购买过电脑的用户名
5：查询订单是909090对应的用户名和商品名
6：查询商品表中的最高价格的前两条数据

1：查询孙姓用户的个数

 SELECT COUNT(*) FROM `user` WHERE user.uname LIKE "孙%"

2：查询年纪最大的2个用户名

SELECT u.uname FROM user u ORDER BY u.uage DESC LIMIT 0,2

3：查询李三买的商品名

select p.pname from product p,`user` u,`order` o where u.uid = o.ouid AND p.pid = o.opid and u.uname = "李三"

 4：查询购买过电脑的用户名

select u.uname from product p,`user` u,`order` o where u.uid = o.ouid AND p.pid = o.opid and p.pname = "电脑"

5：查询订单是909090对应的用户名和商品名

select u.uname,p.pname from product p,`user` u,`order` o where u.uid = o.ouid AND p.pid = o.opid and o.onumber = 321312212

6：查询商品表中的最高价格的前两条数据

SELECT * FROM product ORDER BY product.pprice DESC LIMIT 0,2

	celebrity(名人)
sid	sname(作者)	sage	ssex
1	李白	34	男
2	杜甫	24	男
3	白居易	31	男
4	李商隐	40	女
5	苏轼	26	男
6	辛弃疾	22	男

     

	summary(总结)
sid	wid	sassess(名句)
1	1	天生我材必有用
1	2	危乎高哉！
6	8	明月几时有
3	6	乱花渐欲迷人眼
4	3	却话巴山夜雨时
5	7	大江东去
2	5	一览众山小
1	4	举头望明月

	works（代表）
wid	wwork(作品)	wdynasty(朝代)
1	将进酒	唐代
2	蜀道难	唐代
3	夜雨寄北	唐代
4	静夜思	唐代
5	望岳	唐代
6	钱塘湖春行	南宋
7	念奴娇赤壁怀古	北宋
8	水调歌头	唐代

1：查询名人表中性别是男的作者

SELECT c.sname from celebrity c WHERE c.ssex ="男"
2：查询静夜思的作者

SELECT celebrity.sname FROM celebrity INNER JOIN summary on celebrity.sid = summary.sid INNER JOIN works on works.wid = summary.sid WHERE works.wwork = "静夜思"
3：查询李白和杜甫的年纪

SELECT c.sname ,c.sage FROM celebrity c WHERE c.sname in ("李白","杜甫")
4：查询名人表中的男女人数

SELECT c.ssex, COUNT(*) FROM celebrity c GROUP BY c.ssex
5：查询白居易写的作品名称和对应的名句

SELECT celebrity.sname,summary.sassess FROM celebrity INNER JOIN summary on celebrity.sid = summary.sid INNER JOIN works on works.wid = summary.sid WHERE celebrity.sname = "白居易"
6：查询年纪在25到30岁之间的个数

SELECT COUNT(*) FROM celebrity c WHERE c.sage<=30 AND c.sage>=25
7：查询名人表中年纪最小两条的数据信息

SELECT * FROM celebrity ORDER BY sage LIMIT 0,2
8：查询李白的作品名称，名句和年纪

SELECT celebrity.sname,summary.sassess,works.wwork FROM celebrity INNER JOIN summary on celebrity.sid = summary.sid INNER JOIN works on works.wid = summary.sid WHERE celebrity.sname = "李白"
9：将作品为望岳的朝代修改为北宋

UPDATE works SET wdynasty="北宋" WHERE wwork = "望岳"
10：新增名人表中的一个作者王维，年纪25，性别男

INSERT INTO celebrity VALUES(7,"王维",25,"男")

学生信息表S(sno,sname,age,sex), 属性为：学号，学生姓名，年龄和性别
sno	sname	age	sex
1	张三	18	男
2	李四	20	女
3	王五	23	男
4	赵六	22	女

选课信息表SC(sno,cno,scgrade), 属性为学号，课程号和成绩
sno	cno	scgrade
1	1	50
2	1	66
3	1	90
1	2	55
2	2	68
3	2	61
4	2	44
1	3	90
2	3	78
3	3	22
4	3	55

课程信息表C(cno,cname,cteacher), 属性为课程号，课程名称和任课老师
Cno	Cname	Cteacher
1	java	何昊
2	php	李美军
3	android	王超

1：查询SC表中对应何昊老师所授课程的女生的信息

SELECT ss.sname,ss.age,ss.sex from studentcourse s LEFT JOIN course c on s.cno = c.Cno LEFT JOIN student ss on ss.sno = s.sno WHERE c.Cteacher = "何昊" AND ss.sex= "女"

找出没有选修过何老师的课程的所有学生的姓名

SELECT * FROM ( SELECT * FROM student r LEFT JOIN (SELECT DISTINCT(ss.sname) name from studentcourse s LEFT JOIN course c on s.cno = c.Cno LEFT JOIN student ss on ss.sno = s.sno WHERE c.Cteacher = "何昊") e ON r.sname = e.name ) q WHERE q.`name` = null

列出有不及格课程（成绩小于60）的学生的姓名

SELECT DISTINCT(s.sname) FROM student s,studentcourse ss WHERE s.sno=ss.sno AND ss.scgrade<60

多表设计

1. 一对一

一张表的一条记录一定只能与另外一张表的一条记录进行对应，反之亦然。

有时候，为了业务，或者避免一张表中数据量过大，过复杂，在开发中会进行一对一方式来设计表。

2一对多（1方建主表(id为主键字段), 多方建外键字段）7.

指的是一个实体的某个数据与另外一个实体的多个数据有关联关系， 一对多的关系在设计的时候，需要设计表的外键

1.1.    班级表和学生表设计

部门表和员工表设计

1.2.    创建数据库表

constraint 约束 

foreign key就是表与表之间的某种约定的关系，由于这种关系的存在，能够让表与表之间的数据，更加的完整，关连性更强。

foreign key语句的式例：FOREIGN KEY(Sno) REFERENCES Student(Sno)

附：表的外键必须是另一张表的主键

create table class(id int primary key auto_increment,name varchar(20));

create table student(id int primary key auto_increment,name varchar(20),sex varchar(20),class_id int,constraint  foreign key(class_id) references class(id));

 insert into class values(1,'ceshiban');

 insert into class values(2,'kaifa');

 insert into student values(1,'zhangsan','nan',1);

 insert into student values(2,'lisi','nan',2);

insert into student values(3,'jingjing','nan',2);

select * from student where class_id=(select id from class where id=2);

补一个外键的概念（默认是约束): 删除主键信息时，当该主键字段值在外键表中存在时，该记录是不能删除的。---要把外键表是的相关信息删除之后，才能删除。

3、多对多（ 3个表= 2个实体表 + 1个关系表 )

一个实体的数据对应另外一个实体的多个数据，另外实体的数据也同样对应当前实体的多个数据。

一个学生可以有多个老师，一个老师可以教多个学生

解决方案：创建一个中间表，专门用来维护多表之间的对应关系，通常是能够唯一标识出数据的字段（主键）

create table teacher(id int primary key,name varchar(100));

create table student (id int primary key,name varchar(100));

create table teacher_student(teacher_id int,student_id int,constraint foreign key(teacher_id) references teacher(id),constraint foreign key(student_id) references student(id));

 insert into teacher values(1,'梁老师');

 insert into teacher values(2,'李老师');

insert into student values(1,”张三”);

insert into student values(2,”李四”);

insert into teacher_student values(1,1);

insert into teacher_student values(1,2);

insert into teacher_student values(2,1);

insert into teacher_student values(2,2);

查询李老师所教的学生

select id from teacher where name=’李老师’

select student_id from teacher_student where teacher_id=id

select * from student where id in(select student_id from teacher_student where teacher_id =(select id from teacher where name='李老师'));

查询张三的所有老师

select * from teacher where id in(select teacher_id from teacher_student where student_id=(select id from student where name='张三'));

4. 连接查询

1.    初始定义表结构

create table customer(id int primary key auto_increment,name varchar(20),city varchar(20));

create table orders(id int primary key auto_increment,good_name varchar(20),price float(8,2),customer_id int);

insert into customer (name,city) values('李老师','东北');

insert into customer (name,city) values('崔老师','山西');

insert into customer (name,city) values('张老师','内蒙');

insert into customer (name,city) values('闫老师','天津');

 insert into orders(good_name,price,customer_id) values('电脑',59,1);

 insert into orders(good_name,price,customer_id) values('笔记本',88,2);

 insert into orders(good_name,price,customer_id) values('吹风机',99,1);

 insert into orders(good_name,price,customer_id) values('香水',300,3);

 insert into orders(good_name,price,customer_id) values('牛奶',100,6);

2.    交叉查询

交叉查询，又叫笛卡尔积查询，会将左表和右表的信息，做一个乘积将所有信息查询出来，会产生临时表，比较占用内存，生成的记录数=表1 X表2

select * from customer,orders;

select * from customer cross join orders;

3.    内连接查询

内连接，inner join on 查询两张表，设定条件，将两张表中对应的数据查询出来

不会产生笛卡尔积，不会产生临时表，性能高

select * from customer c inner join orders o on c.id=o.customer_id;

select * from customer,orders where customer.id=orders.customer_id;

select * from customer c,orders o where c.id=o.customer_id;

4.    左外连接

左外连接  left join   on 设定条件，将两张表对应的数据查询出来，同时将左表自己没有关联的数据也查询出来

注意：join前面是左，后面是右

select * from customer c left join orders o on c.id=o.customer_id;

5.    右外连接

右外连接 right join  on 设定条件，将两张表对应的数据查询出来，同时将右表自己没有关联的所有数据查询出来

select * from customer c right join orders o on c.id=o.customer_id;

6.    联合查询

select * from customer left join orders on customer.id=orders.customer_id

 having price>20;

 区别

left join (左边为主表，右边有则填充，没有用null)

right join(右边为主表，左边有则填充，没有用null)