嵌套查询
查询方式：
单表查询
多表查询：
嵌套查询
Select * from 表名where 列名=（sql语句）and 列名=（单表查询）
查询30岁以上的学生信息
SELECT * FROM stu WHERE s_age>30
查询年龄大于赵敏的学生信息
SELECT * FROM stu WHERE s_age>(
SELECT s_age FROM stu WHERE s_name='赵敏'
)
查询班级名称是05031的所有学生信息
05031->b_id->stu.*
SELECT * FROM stu WHERE b_id=(
SELECT b_id FROM banji WHERE b_name='05031'
)
查询年龄大于20岁学生所在班级信息
SELECT * FROM banji WHERE b_id IN(
SELECT b_id FROM stu WHERE s_age>20
)
等值查询
左外连接
右外连接
班级表和学生表
CREATE DATABASE emq
USE emq
CREATE TABLE banji(
b_id INT PRIMARY KEY AUTO_INCREMENT,
b_name V ARCHAR(10),
b_tel V ARCHAR(18)
)ENGINE=INNODB
CREATE TABLE stu(
s_id INT PRIMARY KEY AUTO_INCREMENT,
s_name V ARCHAR(10),
s_sex ENUM('男','女')DEFAULT'男',
s_age INT,
s_address V ARCHAR(20),
b_id INT,
FOREIGN KEY(b_id) REFERENCES banji(b_id)
)ENGINE=INNODB
INSERT INTO banji V ALUES (1,'20150608001','951357456') INSERT INTO banji V ALUES ('unll','20150608002','632145987') INSERT INTO banji V ALUES ('unll','20150608003','852314697') SELECT * FROM banji
INSERT INTO stu V ALUES (1,'懒洋洋','男',20,'羊村',1) INSERT INTO stu V ALUES ('unll','喜洋洋','男',21,'羊村',1) INSERT INTO stu V ALUES ('unll','慢羊羊','男',30,'羊村',1) INSERT INTO stu V ALUES ('unll','美羊羊','女',21,'羊村',2) INSERT INTO stu V ALUES ('unll','胖羊羊','女',22,'羊村',2) INSERT INTO stu V ALUES ('unll','灰太狼','男',25,'狼堡',3) INSERT INTO stu V ALUES ('unll','红太狼','女',22,'狼堡',3) INSERT INTO stu V ALUES ('unll','小灰灰','男',19,'狼堡',3) SELECT * FROM stu
1查询班里年纪最大的学生信息；
Max(s_age)->s_age->stu*
Select * from stu where s_age=(
Select max(s_age) from stu)
select s_name,s_age from stu
(select s_id,max(age) age from banji group by s_id) b where a.s_id=b.b_id
2查出年龄最大和最小的学生的所有信息
Max(s_age) or min(s_age)->s_age->stu*
Select * from stu where s_age=(
Select max(s_age) from stu) or s_age=(
Selsct min(s_age) from stu)
3查出表中年龄大于平均年龄的信息
Avg(s_age)->s_age->比较
Select * from stu
where s_age>(
Select avg(s_age) from stu)
4查出03051班所有男生信息
03051->b_id and s_sex=’男’
Select * from stu where s_sex=’男’ and b_id=(
Select b_id from banji where b_name=’03051’
)
5查询张三所在班级的名称
张三->b_id->b_name
SELECT b_name FROM banji
WHERE b_id=(SELECT b_id FROM stu WHERE s_name='张三')
1:部门表：dept(d_id,d_name)
CREATE TABLE dept(
d_id INT PRIMARY KEY AUTO_INCREMENT,
d_name V ARCHAR(50)
)
2:职工表：emp（e_id,d_id,e_name,e_sex,e_birthday,e_dangyuan,e_in_date,e_jg）：入职时间和出生日期精确到年月日
CREATE TABLE emp(
e_id INT PRIMARY KEY AUTO_INCREMENT,
d_id INT,
e_name V ARCHAR(10),
e_sex V ARCHAR(10),
e_birthday V ARCHAR(10),
e_dangyuan V ARCHAR(10),
e_in_date date,
e_jg V ARCHAR(50)
)
1查询所有教学部职工的基本信息
教学部->d_id->em(*)
SELECT * FROM emq WHERE d_id=
(SELECT d_id FROM dept WHERE d_name='教学部')
2查询人事部最早入职的员工信息
人事部->d_id->min(e_in_date)
Select * from emp e_in_date=(
Select min(e_in_date) from emp where d_id=(
Select d_id from dept where d_name=’人事部’)) and d_id=(
select d_id from dept where d_name=’人事部’)
3查询所有男职工的基本信息
Select * from emp where e_sex=’男’
4查询所有女职工的姓名，出生年月和所在部门编号
Select e_name,e_birthday,d_id from emp where e_sex=’女’
5询所有入职时间在2000年以后的女职工的职工编号，姓名，入职时间
Select e_id,e_name,e_in_date from emp where e_sex=’女’and e_in_date>=2000-01-01
6查询所有人事部和市场部员工编号，姓名和部门编号
Select e_id,e_name,d_id from from emp where d_id=(
Select d_id from dept where d_name=’市场部’) or d_id=
(Select d_id from dept where d_name=’人事部’)
7查询所有不姓李的,并且不在人事部的员工基本信息
Select * from emp
where e_name not like ’李%’ and
d_id !=(Select d_id from dept where d_name=’人事部’ )
8：将人事部年龄大于55岁的员工调到后勤部
UPDATE emp SET d_id=(select d_id from dept where d_name=’后勤部’) WHERE e_id IN(
SELECT * FROM
(SELECT e_id FROM emp WHERE d_id=(select d_id from dept where d_name=’人事部’)
AND e_age>55) xx
)
练习：
学生表stu：学号s_id，姓名s_name，性别s_sex，年龄s_age，所在系s_dept 课程表course：课程编号c_id，课程名称c_name，课程学分
选课表sc：学号，课程号，成绩x_mark 不添加外键
1：查询与刘晨在同一个系的学生信息
select * from stu where s_dept in
(select s_dept from stu where s_name='刘晨')
2：查询选修了课程名为‘信息系统’的学生学号和姓名
select s_id, s_name from stu where s_id in
(select s_id from sc where c_id in
(select c_id from course where c_name='信息系统'))
3：查询选修课程编号为1和2的学生学号和姓名
select s_idfrom stu where s_id in (select sno from sc
where c_id='1')and s_id in (select s_id from sc where c_id='2')
4：查询比刘晨年龄小的学生信息
Select * from stu s_age<(
Select s_age from stu where s_name=’刘晨’)
5：查询其他系中比信息系中刘晨年龄小的学生姓名和年龄
Select s_name,s_age from stu where s_age Select s_age from stu where s_dept=’信息系’)and s_dept<>’信息系’and s_name=’刘晨’ 6：查询每个学生的最高成绩 Select * from sc A where x_mark =( Select max(x_mark) from sc where s_id=A.s_id) 7：查询选修了全部课程的学生学号； Select s_id from stu where not exists ( Select * from course where not exists ( Select * from sc where s_id = stu.s_id and c_id = course.c_id)) 8：查询选课人数不少于2人的课程号及选课人数； select course.课程名,STUDENT.学号,STUDENT.姓 名,STUDENT.性别,sc.成绩 from (select sc.课程号,Count(sc.学号) FROM sc Group by sc.课 程号having Count(sc.学号)>2)tbl JOIN course ON TBL.课程号=course.课程号 JOIN SC ON TBL.课程号=SC.课程号 JOIN STUDENT ON SC.学号= STUDENT.学号 1：查询与杜丹东在同一个系的其他学生信息 select * from stu where s_dept in (select s_dept from stu where s_name='杜丹') and s_name<>’杜丹’ 2：查询选修了课程名为‘java’的学生学号和姓名 select s_id,s_name from sc where c_name='java' 3：查询比田鹏年龄小的学生基本信息 select * from stu where s_age< (select s_age from stu where s_name='田鹏') 4：查询其他系中比计算机系中杜丹东年龄小的学生姓名和年龄 Select s_name,s_age from stu where s_age Select s_age from stu where s_dept=’计算机系’)and s_dept<>’计算机系’and s_name=’杜丹’ 5查询每个学生的最高成绩 Select * from sc A where x_mark =( Select max(x_mark) from sc where s_id=A.s_id) 6：查询选修了全部课程的学生学号； select s_id from stu where not exists (select * from course where not exists ( select * from sc where s_id=stu.s_id and c_id=course.c_id)) 7：查询选课人数不少于2人的课程号及选课人数； select distinct s_id from sc as scx where not exists ( select * from sc as scy where scy.s_id=’2’and not exists (select * from sc scz where scz.s_id = scx.s_id and scz.c_id = scy.c_id 8：查询选修课程编号为1和2的学生学号 select s_id from stu where s_id in (select s_id from sc where c_id=1) and s_id in (select s_id from sc where c_id=2) 9得到每门课的选修人数 select 课程信息表.课程名称,count(distinct 成绩表.学号) from 成绩表join 课程信息表on 成绩表.课程编号=课程信息表.课程编group by 课程信息表.课程名称 10查询门门课程都及格的学生的学号 11查询平均分不及格的学生学号和姓名 12查询哪门课程有大于30名学生选择。 13得到每门课的选修人数 14找出数据库课程不及格的女生姓名。 商品销售数据库 Article(商品号char(4)，商品名char(16)，单价Numeric(8,2)，库存量int) Customer(顾客号char (4)，顾客名char (8)，性别char(2)，年龄int) OrderItem(顾客号char(4)，商品号char(4)，数量int, 日期date) 1检索定购商品号为‘0001’的顾客号和顾客名。 Select distinct c_id,c_name from orderitem where a_id=’0001’ 2检索定购商品号为‘0001’或‘0002’的顾客号。 Select distinct c_id from orderitem where a_id=’0001’ or a_id=’0002’ 3检索定购商品号为‘0001’和‘0002’的顾客号。 Select c_id from orderitem where a_id=’0001’ and a_id in( Select c_id from orderitem where a_id=’0002’) 4检索订购的每种商品的次数。 Select count( distinct c_id) from orderitem select count(c_id) from orderitem 5检索顾客张三订购商品的总数量 Select sum(o_shuliang) from orderitem, customer where c_name=’张三’6检索年龄在30至40岁的顾客所购买的商品名及商品单价。 Select a_id,a_danji from customer,article,orseritem where customer.c_id = orderitem.o_id and article.a_id= orderitem.o_id and c_age between 30 and 40