1. 首页首页
  2. 编程笔记编程笔记

数据库中多表连接查询和子查询的区别

编程笔记 • • 阅读 12

在Student表和score表上进行查询。Student表和score表的定义如下表所示： score表数据如下： 1.查询操作： 1）查询李五一的考试科目和考试成绩 2）查询所有学生的信息和考试信息 3）计算每个学生的总成绩（需显示学生姓名） 4)

在Student表和score表上进行查询。Student表和score表的定义如下表所示：

 

score表数据如下：

1.查询操作：

1）查询李五一的考试科目和考试成绩

2）查询所有学生的信息和考试信息

3）计算每个学生的总成绩（需显示学生姓名）

4) 计算计算机成绩低于95分的学生的信息

5）查询同时参加计算机和英语考试的学生的信息

6）从student表和score表中查询学生的学号，然后合并查询结果

7）查询姓张或姓王同学的姓名、院系、考试科目和成绩

8) 查询都是湖南的同学的姓名、年龄、院系、考试科目和成绩。

1）查询李五一的考试科目和考试成绩
SELECT a.Name,b.Cname,b.Grade 
FROM Student AS a
JOIN score AS b 
ON a.Num=b.Num
WHERE a.Name='李五一';

2）查询所有学生的信息和考试信息
SELECT * FROM Student AS a
JOIN score AS b
ON a.Num=b.Num;

3）计算每个学生的总成绩（需显示学生姓名）
SELECT a.Name, SUM(Grade)
FROM Student AS a
JOIN score AS b
ON a.Num=b.Num
GROUP BY a.Num;

4) 计算计算机成绩低于95分的学生的信息
SELECT a.Num,a.Name,a.Dept,a.Address,a.Birthday ,b.Grade,b.Cname
FROM Student AS a
JOIN score AS b
ON a.Num=b.Num
WHERE b.Grade<95 AND b.Cname='计算机';

5）查询同时参加计算机和英语考试的学生的信息
SELECT a.Num,a.Name,a.Dept,a.Address,a.Birthday 
FROM Student AS a
JOIN score AS b
ON a.Num=b.Num
JOIN score AS c
ON b.Num=c.Num
WHERE (b.Cname='计算机' AND c.Cname='英语') ;

6）从student表和score表中查询学生的学号，然后合并查询结果
SELECT Num FROM Student UNION SELECT Num FROM score;

7）查询姓张或姓王同学的姓名、院系、考试科目和成绩
SELECT a.Name,a.Dept,b.Cname,b.Grade
FROM student AS a
JOIN score AS b
ON a.Num=b.Num
WHERE a.name LIKE '张%' OR  a.Name LIKE '王%';

8) 查询都是湖南的同学的姓名、年龄、院系、考试科目和成绩
SELECT a.Address,a.Name,(DATE_FORMAT(NOW(),'%Y')-a.Birthday) AS age,a.Dept,b.Cname,b.Grade
FROM Student AS a
JOIN score AS b
ON a.Num=b.Num
WHERE a.Address LIKE '湖南省%';

2.利用子查询实现第一题中的4） 5） 7） 8）

4）计算计算机成绩低于95分的学生的信息
SELECT Num,Name,Dept,Address,Birthday FROM Student 
WHERE Num IN
(SELECT Num FROM score WHERE Cname='计算机' AND Grade<95);

5）查询同时参加计算机和英语考试的学生的信息
SELECT Num,Name,Dept,Address,Birthday FROM Student
WHERE Num IN
(SELECT Num FROM score WHERE Cname='英语')
AND Num IN
(SELECT Num FROM score WHERE Cname='计算机');

7）查询姓张或姓王同学的姓名、院系、考试科目和成绩
SELECT a.Name,a.Dept,b.Cname,b.Grade
FROM (SELECT Num,Name,Dept FROM Student
WHERE Name LIKE '张%' OR  Name LIKE '王%') AS a
JOIN score AS b
ON a.Num=b.Num;

8) 查询都是湖南的同学的姓名、年龄、院系、考试科目和成绩
SELECT a.Address,a.Name,(DATE_FORMAT(NOW(),'%Y')-a.Birthday) AS age,a.Dept,b.Cname,b.Grade
FROM (SELECT Num,Name,Dept,Birthday,Address FROM Student
WHERE Address LIKE '湖南省%') AS a
JOIN score AS b
ON a.Num=b.Num;

3.学生选课关系数据库C ,S,SC表分别如下：

1)检索不选修任何课程的学生的学号

2)查询选课两门课的学生的姓名

3)查询学习课程号为'C2',成绩为第一名的学生的姓名

4)查询选修C2课程成绩大于该课平均成绩的学生的姓名学号成绩

1)检索不选修任何课程的学生的学号
SELECT * FROM S WHERE SNO NOT IN(
SELECT DISTINCT SNO FROM SC);

2)查询选课两门课的学生的姓名
SELECT SN
FROM S
WHERE SNO IN 
(SELECT SNO
FROM SC
GROUP BY SNO
HAVING COUNT(*)=2);

3)查询学习课程号为'C2',成绩为第一名的学生的姓名
SELECT SN
FROM S
WHERE SNO=
(SELECT SNO
FROM SC
WHERE CNO='C2'
ORDER BY SCORE DESC LIMIT 1);

4)查询选修C2课程成绩大于该课平均成绩的学生的姓名学号成绩
SELECT SN,S.SNO,SCORE
FROM S,SC
WHERE  CNO='C2' AND SCORE>(
SELECT AVG(SCORE)
FROM SC
WHERE CNO='C2'
HAVING S.SNO=SC.SNO);

多表连接源码：

-- 创建省份表
CREATE TABLE provinces(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
proName VARCHAR(10) NOT NULL UNIQUE
);
INSERT provinces(proName) VALUES('北京'),
('上海'),
('深圳');

-- 管理员admin id username email proId
CREATE TABLE admin(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR(20) NOT NULL UNIQUE,
email VARCHAR(50) NOT NULL DEFAULT '382771946@qq.com',
proId TINYINT UNSIGNED NOT NULL
);
INSERT admin(username,proId) VALUES('king',1);
INSERT admin(username,proId) VALUES('queen',2);

-- 商品分类cate id cateName cateDesc
CREATE TABLE cate(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
cateName VARCHAR(50) NOT NULL UNIQUE,
cateDesc VARCHAR(100) NOT NULL DEFAULT ''
);
INSERT cate(cateName) VALUES('母婴');
INSERT cate(cateName) VALUES('服装');
INSERT cate(cateName) VALUES('电子');

-- 商品表products id productName, price,cateId
CREATE TABLE products(
id INT UNSIGNED AUTO_INCREMENT KEY,
productName VARCHAR(50) NOT NULL UNIQUE,
price FLOAT(8,2) NOT NULL DEFAULT 0,
cateId TINYINT UNSIGNED NOT NULL,
adminId TINYINT UNSIGNED NOT NULL
);
INSERT products(productName,price,cateId,adminId)
VALUES('iphone9',9888,3,1),
('adidas',388,2,2),
('nike',888,2,2),
('奶瓶',288,1,1);

-- 练习1：查询products id productName price --- cate cateName
SELECT p.id, p.productName, p.price, c.cateName 
FROM products AS p 
JOIN cate AS c
ON p.cateId = c.id;


-- 练习2：查询管理员 id username email -- provinces proName
SELECT a.id,a.username,a.email,pro.proName
FROM admin AS a
JOIN provinces AS pro
ON a.proId=pro.id;


-- 练习3：查询 products id productName price
-- cate cateName
-- admin username email 
SELECT p.id,p.productName,p.price,c.cateName,a.username,a.email
FROM products AS p
JOIN cate AS c
ON p.cateId=c.id
JOIN admin AS a
ON  p.adminId=a.id;

-- 练习4：查询 价格低于1000元商品的products id productName price
-- cate cateName
-- admin username email 按价格的降序排列，并且只显示前两条
SELECT p.id,p.productName,p.price,c.cateName,a.username,a.email
FROM products AS p
JOIN cate AS c
ON p.cateId=c.id
JOIN admin AS a
ON  p.adminId=a.id
WHERE p.price < 1000
ORDER BY p.price DESC
LIMIT 2 ;


-- 练习5：查询products id productName price
-- cate cateName
-- admin username email
-- provinces proName
SELECT p.id,p.productName,p.price,c.cateName,a.username,a.email,pro.proName
FROM products AS p
JOIN cate AS c
ON p.cateId=c.id
JOIN admin AS a
ON  p.adminId=a.id
JOIN provinces AS pro
ON a.proId=pro.id;

连接查询源码：

dep表
+----+-----------------+-----------------------------+
| id | depName         | depDesc                     |
+----+-----------------+-----------------------------+
|  1 | 大数部          | 大学数学基础课              |
|  2 | 信计部          | 信息与计算科学学科          |
|  3 | 应用数学部      | 数学建模与科学计算          |
|  4 | 光科            | 物理光信息                  |
+----+-----------------+-----------------------------+
emp表
+----+----------+-----+--------+--------+-------+
| id | username | age | sex    | addr   |  depId|
+----+----------+-----+--------+--------+-------+
|  1 | king     |  24 | 保密   | 北京   |     1 |
|  2 | queen    |  25 | 保密   | 北京   |     2 |
|  3 | imooc    |  26 | 保密   | 北京   |     1 |
|  4 | lily     |  27 | 保密   | 北京   |     1 |
|  5 | rose     |  28 | 保密   | 北京   |     3 |
|  6 | john     |  29 | 保密   | 北京   |     3 |
+----+----------+-----+--------+--------+-------+


CREATE TABLE emp(
id INT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR(20) NOT NULL UNIQUE COMMENT '编号',
age TINYINT UNSIGNED NOT NULL DEFAULT 18  COMMENT '年龄',
sex ENUM('男','女','保密') NOT NULL DEFAULT '保密' COMMENT '性别',
addr VARCHAR(20) NOT NULL DEFAULT '北京',
depId TINYINT UNSIGNED NOT NULL COMMENT '部门对应的编号'
)ENGINE=INNODB CHARSET=UTF8;

INSERT emp(username,age,depId) VALUES('king',24,1),
('queen',25,2),
('imooc',26,1),
('lily',27,1),
('rose',28,3),
('john',29,3);



CREATE TABLE dep(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
depName VARCHAR(50) NOT NULL UNIQUE,
depDesc VARCHAR(100) NOT NULL DEFAULT ''
)ENGINE=INNODB CHARSET=UTF8;
INSERT dep(depName,depDesc) VALUES('大数部','大学数学基础课'),
('信计部','信息与计算科学学科'),
('应用数学部','数学建模与科学计算'),
('光科','物理光信息');


-- 做笛卡尔积
SELECT emp.id,emp.username,emp.age,dep.id,dep.depName,dep.depDesc FROM emp,dep;

--内连接
SELECT e.id,e.username,e.age,d.depName
FROM emp AS e
INNER JOIN dep AS d
ON e.depId=d.id;



-- 测试左外连接
INSERT emp(username,age,depId)VALUES('测试用户',39,7);

SELECT e.id,e.username,e.age,d.depName,d.depDesc
FROM emp AS e
LEFT OUTER JOIN dep AS d
ON e.depId=d.id;

-- 测试右外连接
SELECT e.id,e.username,e.age,d.depName,d.depDesc
FROM emp AS e
RIGHT JOIN dep AS d
ON e.depId=d.id;

子查询源码：

CREATE TABLE S(
  SNO CHAR(6) PRIMARY KEY,
  SN VARCHAR(20) NOT NULL,
  AGE TINYINT UNSIGNED NOT NULL,
  DEPT VARCHAR(20) 
);
INSERT S VALUES('S1','李立勇',20,'CS'),
               ('S2','刘蓝',23,'IS'),
               ('S3','周小花',18,'MA'),
               ('S4','张立伟',19,'IS'),
               ('S5','王世明',19,'IS'),
               ('S6','陈思思',19,'MS');

CREATE TABLE C(
  CNO VARCHAR(6) PRIMARY KEY,
  CN VARCHAR(10) NOT NULL,
  CPNO VARCHAR(6) 
);
INSERT C VALUES('C1','数据库','C2'),
               ('C2','离散数学',NULL),
               ('C3','操作系统','C4'),
               ('C4','数据结构','C2');

CREATE TABLE SC(
  SNO CHAR(6),
  CNO VARCHAR(6),
  SCORE INT   NOT NULL,
  CONSTRAINT S_C_P PRIMARY KEY(SNO,CNO),
  CONSTRAINT S_F FOREIGN KEY(SNO) REFERENCES S(SNO),
  CONSTRAINT C_F FOREIGN KEY(CNO) REFERENCES C(CNO)
  );
INSERT SC VALUES('S1','C1',85),
                ('S1','C2',90),
                ('S1','C3',89),
                ('S1','C4',88),
                ('S2','C2',78),
                ('S2','C3',85),
                ('S3','C2',68),
                ('S3','C3',78),
                ('S3','C4',75),
                ('S4','C1',69),
                ('S4','C2',82),
                ('S4','C4',73),
                ('S5','C1',92),
                ('S5','C4',86);
-- 查询与“张立伟”在同一个系学习的学生
SELECT SNO,SN,DEPT
FROM S WHERE DEPT=
(SELECT DEPT FROM S WHERE SN='张立伟');

SELECT S1.SNO,S1.SN,S1.DEPT
FROM S AS S1
JOIN S AS S2
ON S1.DEPT=S2.DEPT
WHERE S2.SN='张立伟';

-- 查询选修了课程名为“数据库”的学生学号和姓名 
SELECT SNO,SN 
FROM S 
WHERE SNO IN 
(SELECT SNO 
  FROM SC 
  WHERE CNO IN
  (SELECT CNO 
    FROM C
    WHERE CN='数据库')
);

SELECT S.SNO,S.SN FROM S
JOIN SC
ON SC.SNO=S.SNO
JOIN C
ON C.CNO=SC.CNO
WHERE C.CN='数据库';

-- 找出每个学生超过他选修课程平均成绩的课程号
SELECT SNO,CNO FROM SC AS X
WHERE SCORE>=(
  SELECT AVG(SCORE)
  FROM SC AS Y 
  GROUP BY Y.SNO 
  HAVING Y.SNO=X.SNO);

-- 查询选修了C1课程的学生的学号姓名
SELECT SNO,SN 
FROM S WHERE EXISTS
(SELECT * FROM SC 
  WHERE SC.SNO=S.SNO AND CNO='C1');

SELECT S.SNO,S.SN
FROM S 
JOIN SC
ON S.SNO=SC.SNO
WHERE SC.CNO='C1';

-- 查询没有选修1号课程的学生姓名
SELECT SNO,SN 
FROM S WHERE NOT EXISTS
(SELECT * FROM SC 
  WHERE SC.SNO=S.SNO AND CNO='C1');



-- 其它
-- 创建一个user1表,id username
CREATE TABLE user1(
    id int UNSIGNED AUTO_INCREMENT KEY,
    username VARCHAR(20)
)SELECT id,username FROM emp;

-- 将stu表中id=3的用户名写入到user1表中
INSERT user1(username) SELECT username FROM stu where id=3;

-- 创建象user1一样的表user2
CREATE TABLE user2 LIKE user1;
INSERT user2(username) SELECT username FROM stu;

-- 将stu表中的tiancai用户名添加到user2表中
INSERT user2 SET username=(SELECT username FROM stu WHERE id=5);

-- 去掉字段的重复值
SELECT DISTINCT(username) FROM user2;

-- 将user1和user2数据合并到一起
--去重
SELECT username FROM user1
UNION
SELECT username FROM user2;
--不去重
SELECT username FROM user1
UNION ALL
SELECT username FROM user2;

知秋君
知秋君

知秋君

版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请关注公众号“全栈程序员社区”联系小编举报，一经查实，本站将立刻删除。
上一篇 2024-08-07 08:02
下一篇 2024-08-07 07:36

相关推荐