MYSQL<五>
时间：2021-07-01 10:21:17
帮助过：10人阅读
< 60
)
-- 有课程成绩的同学编号
AND studentid IN (SELECT studentid FROM scoreinfo);
-- 写法2、正向思路：考虑在成绩信息表中依据学生编号进行分组，获取组的最小成绩大于等于60的同学
SELECT studentid AS 学生编号, studentname AS 学生姓名
FROM studentinfo
WHERE studentid IN (
SELECT studentid
FROM scoreinfo
GROUP BY studentid
HAVING MIN(score) >= 60
);
SELECT * FROM scoreinfo;
-- 使用比较运算符的子查询
-- 需求：列出不小于选修了课程编号2的学生编号的学生信息
-- 错误代码： 1242 Subquery returns more than 1 row
SELECT *
FROM studentinfo
WHERE studentid >= (SELECT studentid FROM scoreinfo WHERE courseid = 2);
-- 正确写法：虽然没有大于等于某个范围的关键字或是符号，但是可以大于等于这个范围中最大的那个数据值
SELECT *
FROM studentinfo
WHERE studentid >= (SELECT MAX(studentid) FROM scoreinfo WHERE courseid = 2);
-- 使用EXISTS关键字的子查询：如果子查询查询到了结果，外层的查询才执行；否则的话，外层的查询不执行
-- 需求：有课程成绩大于等于90分的同学就列出全部的学生信息
SELECT *
FROM studentinfo
WHERE EXISTS (SELECT * FROM scoreinfo WHERE score >= 90);
-- 需求：有课程成绩大于90分的同学就列出全部的学生信息
SELECT *
FROM studentinfo
WHERE EXISTS (SELECT * FROM scoreinfo WHERE score > 90);
-- 使用ANY 或 SOME 的子查询
-- 需求：列出编号大于任一个选修了课程编号2的学生编号的学生信息
SELECT *
FROM studentinfo
WHERE studentid >= ANY(SELECT studentid FROM scoreinfo WHERE courseid = 2);
-- 上句等价于下句：列出编号大于选修了课程编号2的学生编号最小那个编号的学生信息
SELECT *
FROM studentinfo
WHERE studentid >= (SELECT MIN(studentid) FROM scoreinfo WHERE courseid = 2);
-- 关键字SOME 和 关键字ANY 是同义词
SELECT *
FROM studentinfo
WHERE studentid >= SOME(SELECT studentid FROM scoreinfo WHERE courseid = 2);
-- 使用ALL的子查询
-- 需求：列出学生编号不小于所有选修了课程编号2的学生编号的学生信息
SELECT *
FROM studentinfo
WHERE studentid >= ALL(SELECT studentid FROM scoreinfo WHERE courseid = 2);
-- 上句可以理解为：大于等于查询出课程编号2的学生学号这个范围中最大的那个数据值
SELECT *
FROM studentinfo
WHERE studentid >= (SELECT MAX(studentid) FROM scoreinfo WHERE courseid = 2);
-- 结合常见的场景：客户信息 和 订单信息来看看子查询的使用
-- 客户信息表
CREATE TABLE customers
(
customerid INT AUTO_INCREMENT PRIMARY KEY,
customername VARCHAR(10) NOT NULL
);
INSERT INTO customers VALUES(NULL, ‘张三‘), (NULL, ‘李四‘), (NULL, ‘王五‘), (NULL, ‘赵六‘);
-- 订单信息表
CREATE TABLE orders
(
orderid INT AUTO_INCREMENT PRIMARY KEY,
customerid INT NOT NULL,
-- 金额
amount DECIMAL(10, 2) NOT NULL,
-- 订单类型：现金、信用卡
ordertype VARCHAR(10) NOT NULL
);
INSERT INTO orders VALUES(NULL, 1, 22.25, ‘现金‘), (NULL, 2, 11.75, ‘信用卡‘), (NULL, 2, 5.00, ‘信用卡‘), (NULL, 2, 8.00, ‘现金‘),
(NULL, 3, 9.33, ‘信用卡‘), (NULL, 3, 10.11, ‘信用卡‘);
SELECT * FROM customers;
SELECT * FROM orders;
-- 1、独立子查询作为数据源（包含数据的集合）的使用
-- 需求：以客户为单位，统计每个客户的现金消费总数
-- 1）筛选在分组后做
-- 错误代码： 1054 Unknown column ‘ordertype‘ in ‘having clause‘
-- 分析：因为按照customerid进行分组形成的每个组中会有不同的ordertype
SELECT customerid AS 客户编号, SUM(amount) AS 现金消费总数
FROM orders
GROUP BY customerid
HAVING ordertype = ‘现金‘;
-- 2）筛选在分组前做，考虑使用子查询
-- 错误代码： 1248 Every derived table must have its own alias
SELECT *
FROM (
SELECT customerid AS 客户编号, SUM(amount) AS 现金消费总数
FROM orders
WHERE ordertype = ‘现金‘
GROUP BY customerid
);
-- 正确写法：
SELECT *
FROM (
SELECT customerid AS 客户编号, SUM(amount) AS 现金消费总数
FROM orders
WHERE ordertype = ‘现金‘
GROUP BY customerid
) AS temp;
-- 不使用星号通配符，使用具体字段
-- 错误代码： 1054 Unknown column ‘customerid‘ in ‘field list‘
SELECT customerid AS 客户编号, SUM(amount) AS 现金消费总数
FROM (
SELECT customerid AS 客户编号, SUM(amount) AS 现金消费总数
FROM orders
WHERE ordertype = ‘现金‘
GROUP BY customerid
) AS temp;
-- 正确写法：
-- 写法1
SELECT 客户编号, 现金消费总数
FROM (
SELECT customerid AS 客户编号, SUM(amount) AS 现金消费总数
FROM orders
WHERE ordertype = ‘现金‘
GROUP BY customerid
) AS temp;
-- 写法2
SELECT temp.客户编号, temp.现金消费总数
FROM (
SELECT customerid AS 客户编号, SUM(amount) AS 现金消费总数
FROM orders
WHERE ordertype = ‘现金‘
GROUP BY customerid
) AS temp;
-- 2、关联子查询
-- 1）在查询条件中使用关联子查询
-- 需求：查询出消费总计小于20元的客户信息
SELECT * FROM orders;
-- 写法1、使用独立子查询
SELECT *
FROM customers
WHERE customerid IN
(
SELECT customerid
FROM orders
GROUP BY customerid
HAVING SUM(amount) < 20
);
-- 写法2、使用关联子查询
-- 【使用关联子查询时，子查询不再像独立子查询那样可以独立执行，也不再像独立子查询那样先执行】
SELECT *
FROM customers AS c
WHERE (
SELECT customerid
FROM orders AS o
WHERE o.customerid = c.customerid
GROUP BY o.customerid
HAVING SUM(o.amount) < 20
);
-- 可以理解为：分步操作，在查询条件中的关联子查询相当于遍历外层表中关联字段的每一行记录
-- 在这里，查询条件中的关联子查询使用的关联字段customerid相当于遍历外层的customers表中的customerid这个关联字段的每一行记录
-- 因为关联子查询中涉及到使用外层表中的关联字段，所以关联子查询就无法独立执行
-- 第1行记录
SELECT customerid
FROM orders AS o
WHERE o.customerid = 1
GROUP BY o.`customerid`
HAVING SUM(o.`amount`) < 20;
-- 第2行记录
SELECT customerid
FROM orders AS o
WHERE o.customerid = 2
GROUP BY o.`customerid`
HAVING SUM(o.`amount`) < 20;
-- 第3行记录（查询到了满足条件的记录，所以留下来）
SELECT customerid
FROM orders AS o
WHERE o.customerid = 3
GROUP BY o.`customerid`
HAVING SUM(o.`amount`) < 20;
-- 第4行记录
SELECT customerid
FROM orders AS o
WHERE o.customerid = 4
GROUP BY o.`customerid`
HAVING SUM(o.`amount`) < 20;
-- 2）关联子查询作为一个计算的列
-- 需求：列出客户姓名以及客户的个人消费数量
-- 分析：单个的获取客户姓名 或是 单个的获取客户的个人消费数量都好办，但是要在一个查询结果中出现这两者有些麻烦，借助于关联子查询
SELECT * FROM orders;
SELECT * FROM customers;
SELECT customerid AS 客户编号, COUNT(*) AS 客户的个人消费数量
FROM orders
GROUP BY customerid;
SELECT
customername AS 客户姓名,
(
SELECT COUNT(o.orderid) AS 消费数量
FROM orders AS o
WHERE o.customerid = c.customerid
) AS 个人消费数量
FROM customers AS c;
-- 可以理解为：分步操作，在列中的关联子查询相当于遍历外层表中关联字段的每一行记录
-- 在这里，查询条件中的关联子查询使用的关联字段customerid相当于遍历外层的customers表中的customerid这个关联字段的每一行记录
-- 因为关联子查询中涉及到使用外层表中的关联字段，所以关联子查询就无法独立执行
-- 第1行记录
SELECT COUNT(o.orderid) AS 消费数量
FROM orders AS o
WHERE o.customerid = 1
-- 第2行记录
SELECT COUNT(o.orderid) AS 消费数量
FROM orders AS o
WHERE o.customerid = 2
-- 第3行记录
SELECT COUNT(o.orderid) AS 消费数量
FROM orders AS o
WHERE o.customerid = 3
-- 第4行记录
SELECT COUNT(o.orderid) AS 消费数量
FROM orders AS o
WHERE o.customerid = 4
-- 子查询的总结：
-- 1、独立子查询：可以独立执行，且先执行
-- 2、关联子查询：不可以独立执行，且伴随着外层查询的操作，每一次都被调用
-- ########## 04、表的连接 ##########
-- 常用的场景：部门 和 员工 是 一对多的关系，部门是一，员工是多
-- 为了体现这种一对多的关系，在员工信息表中放置部门编号这个字段
-- 部门表
CREATE TABLE dept
(
deptid INT AUTO_INCREMENT PRIMARY KEY,
deptname VARCHAR(20) UNIQUE NOT NULL
);
-- 员工表
CREATE TABLE employee
(
employeeid INT AUTO_INCREMENT PRIMARY KEY,
employeename VARCHAR(10) NOT NULL,
deptid INT NULL
);
-- 模拟数据
INSERT INTO dept VALUES(NULL, ‘开发部‘), (NULL, ‘销售部‘), (NULL, ‘总经办‘), (NULL, ‘人事部‘);
INSERT INTO employee VALUES(NULL, ‘刘备‘, 1), (NULL, ‘张飞‘, 1), (NULL, ‘关羽‘, 1),
(NULL, ‘曹操‘, 2), (NULL, ‘荀彧‘, 2), (NULL, ‘郭嘉‘, 2),
(NULL, ‘孙权‘, 3), (NULL, ‘周瑜‘, 3), (NULL, ‘陆逊‘, 3),
(NULL, ‘孟获‘, NULL), (NULL, ‘于吉‘, 5);
SELECT * FROM dept;
SELECT * FROM employee;
-- 1、交叉连接（CROSS JOIN）：获取笛卡尔积（笛卡儿积）
-- 交叉连接：不加任何限制条件，把两个实体（分别有m条记录 和 n条记录）进行连接，形成m*n条记录的新的集合
-- 写法1
SELECT * FROM dept, employee;
-- 写法2、显式使用CROSS JOIN关键字（推荐写法）
SELECT * FROM dept CROSS JOIN employee;
-- 2、内连接（INNER JOIN）：在交叉连接上进行条件限制（筛选）
-- 需求：查询出员工信息及其对应的所属部门的名称
-- 分析：上述取得的笛卡尔积没有任何条件的限制，很多数据是没用意义的
-- 我们需要根据员工信息表中的部门唯一标识-----部门编号，到部门信息表中获取部门的相应信息出来
-- 也就是员工信息表中的部门编号应该和部门信息表中的部门编号对应起来
-- 考虑使用INNER JOIN关键字 和 WHERE子句
SELECT *
FROM dept
INNER JOIN employee
WHERE dept.`deptid` = employee.`deptid`;
-- 改进
SELECT employee.`employeeid` AS 员工编号, employee.`employeename` AS 员工姓名, dept.`deptname` AS 部门名称
FROM dept
INNER JOIN employee
WHERE dept.`deptid` = employee.`deptid`;
-- 可以理解为：首先取得交叉连接的笛卡尔积，再针对笛卡尔积使用条件限制，就得到了内连接的结果
SELECT employee.`employeeid` AS 员工编号, employee.`employeename` AS 员工姓名, dept.`deptname` AS 部门名称
FROM dept
CROSS JOIN employee
WHERE dept.`deptid` = employee.`deptid`;
-- 上述写法等价于
SELECT employee.`employeeid` AS 员工编号, employee.`employeename` AS 员工姓名, dept.`deptname` AS 部门名称
FROM dept, employee
WHERE dept.`deptid` = employee.`deptid`;
-- 对于连接的对应关系的描述，SQL中提供了 ON 关键字
SELECT employee.`employeeid` AS 员工编号, employee.`employeename` AS 员工姓名, dept.`deptname` AS 部门名称
FROM dept
INNER JOIN employee
ON dept.`deptid` = employee.`deptid`;
-- 3、外连接（OUTER JOIN）
-- 理解：这里的外，是针对作为重点的那个集合而言的
-- 1）左外连接/左连接（LEFT OUTER JOIN/LEFT JOIN）：
-- 以左边的表为主，右边的表中能匹配的上的数据列出来，匹配不上的显示为NULL值
-- 2）右外连接/右连接（RIGHT OUTER JOIN/RIGHT JOIN）：
-- 以右边的表为主，左边的表中能匹配的上的数据列出来，匹配不上的显示为NULL值
-- 需求：列出公司全部的部门以及部门的员工信息（多行显示）
-- 分析：重点在全部的部门，另外要列出的是全部部门中每个部门对应的员工的信息
-- 隐含的意思，哪怕这个部门没有员工，也要列出这个部门来，没有员工时，员工信息就显示为NULL值
-- 但是，员工没有部门的，不考虑
SELECT *
FROM dept
LEFT OUTER JOIN employee
ON dept.`deptid` = employee.`deptid`;
-- 写法1、使用LEFT OUTER JOIN
SELECT dept.`deptname` AS 部门名称, employee.`employeeid` AS 员工编号, employee.`employeename` AS 员工姓名
FROM dept
LEFT OUTER JOIN employee
ON dept.`deptid` = employee.`deptid`;
-- 写法2、使用LEFT JOIN
SELECT dept.`deptname` AS 部门名称, employee.`employeeid` AS 员工编号, employee.`employeename` AS 员工姓名
FROM dept
LEFT JOIN employee
ON dept.`deptid` = employee.`deptid`;
-- 需求：列出公司全部的员工以及员工的部门信息
-- 分析：重点在全部的员工上，另外要列出的是全部员工中每个员工对应的部门的信息
-- 隐含的意思，哪怕这个员工没有部门，也要列出这个员工来，没有部门时，部门信息就显示为NULL值
-- 写法1、使用左连接写法
SELECT employee.`employeeid` AS 员工编号, employee.`employeename` AS 员工姓名, dept.`deptname` AS 部门名称
FROM employee
LEFT JOIN dept
ON employee.`deptid` = dept.`deptid`;
-- 写法2、使用右连接写法
SELECT employee.`employeeid` AS 员工编号, employee.`employeename` AS 员工姓名, dept.`deptname` AS 部门名称
FROM dept
RIGHT OUTER JOIN employee
ON employee.`deptid` = dept.`deptid`;
-- 简化
SELECT employee.`employeeid` AS 员工编号, employee.`employeename` AS 员工姓名, dept.`deptname` AS 部门名称
FROM dept
RIGHT JOIN employee
ON employee.`deptid` = dept.`deptid`;
-- 4、自连接（内连接的变体）
-- 需求：设计公司的组织架构信息表（这种设计很重要：务必掌握）
-- 蔡氏国际
-- 蔡氏中国
-- 蔡氏安徽
-- 蔡氏北京
-- 蔡氏上海
-- 蔡氏美国
-- 蔡氏纽约
-- 蔡氏德州
-- 蔡氏日本
-- 蔡氏关东
-- 蔡氏关西
-- 考虑树形结构来进行描述，描述树结构上的节点的位置，需要描述其当前的节点和它的父节点，这样才能确定它的位置
-- 公司组织结构信息表
CREATE TABLE companyinfo
(
-- 组织编号
companyid INT AUTO_INCREMENT PRIMARY KEY,
-- 组织名称
companyname VARCHAR(10) NOT NULL,
-- 组织父编号
companypid INT NOT NULL
);
-- 模拟数据
INSERT INTO companyinfo VALUES(NULL, ‘蔡氏国际‘, 0),
(NULL, ‘蔡氏中国‘, 1), (NULL, ‘蔡氏美国‘, 1), (NULL, ‘蔡氏日本‘, 1),
(NULL, ‘蔡氏安徽‘, 2), (NULL, ‘蔡氏北京‘, 2), (NULL, ‘蔡氏上海‘, 2),
(NULL, ‘蔡氏纽约‘, 3), (NULL, ‘蔡氏德州‘, 3),
(NULL, ‘蔡氏关东‘, 4), (NULL, ‘蔡氏关西‘, 4);
SELECT * FROM companyinfo;
-- 需求：根据给定的组织名称查看其组织编号及其下一级
-- 思路：首先按给定的组织名称获取其组织编号，再拿着这个组织编号做companypid父编号在表中查询，得到其下一级
-- 分步操作如下：
-- step1、首先按给定的组织名称获取其组织编号
SELECT companyid AS 组织编号, companyname AS 组织名称
FROM companyinfo
WHERE companyname = ‘蔡氏中国‘;
-- step2、拿着这个组织编号做companypid父编号在表中查询，得到其下一级
SELECT companyid AS 下一级组织编号, companyname AS 下一级组织名称
FROM companyinfo
WHERE companypid = 2;
-- 不使用分步操作，使用自连接，将组织信息表集合1的编号 和 组织信息表集合2的父编号 进行匹配
-- 也就是把左表的编号作为右表的父编号进行匹配
SELECT *
FROM companyinfo AS c1
INNER JOIN companyinfo AS c2
ON c1.`companyid` = c2.`companypid`;
-- 考虑到需求要根据给定的组织名称查看其组织编号及其下一级
-- 错误代码： 1052 Column ‘companyname‘ in where clause is ambiguous companyname列在WHERE子句中有歧义（因为有多个companyname字段）
SELECT *
FROM companyinfo AS c1
INNER JOIN companyinfo AS c2
ON c1.`companyid` = c2.`companypid`
WHERE companyname = ‘蔡氏中国‘;
-- 正确写法1、筛选条件中使用 ON 和 WHERE
SELECT c1.`companyid` AS 组织编号, c1.`companyname` AS 组织名称, c2.`companyname` AS 下一级组织名称
FROM companyinfo AS c1
INNER JOIN companyinfo AS c2
ON c1.`companyid` = c2.`companypid`
WHERE c1.companyname = ‘蔡氏中国‘;
-- 正确写法2、筛选条件中使用 ON
SELECT c1.`companyid` AS 组织编号, c1.`companyname` AS 组织名称, c2.`companyname` AS 下一级组织名称
FROM companyinfo AS c1
INNER JOIN companyinfo AS c2
ON c1.`companyid` = c2.`companypid` AND c1.companyname = ‘蔡氏中国‘;
-- 正确写法2、筛选条件中使用 WHERE
SELECT c1.`companyid` AS 组织编号, c1.`companyname` AS 组织名称, c2.`companyname` AS 下一级组织名称
FROM companyinfo AS c1
INNER JOIN companyinfo AS c2
WHERE c1.`companyid` = c2.`companypid` AND c1.companyname = ‘蔡氏中国‘;
-- 需求：查看一级组织（一级子公司）及其下一级组织（多行显示）
SELECT c1.`companyid` AS 组织编号, c1.`companyname` AS 一级组织名称, c2.`companyname` AS 下一级组织名称
FROM companyinfo AS c1
INNER JOIN companyinfo AS c2
WHERE c1.`companyid` = c2.`companypid` AND c1.`companypid` = 1;
-- 需求：查看一级组织（一级子公司）及其下一级组织（单行显示）
SELECT temp.组织编号, temp.一级组织名称, GROUP_CONCAT(temp.下一级组织名称) AS 下一级组织名称
FROM
(
SELECT c1.`companyid` AS 组织编号, c1.`companyname` AS 一级组织名称, c2.`companyname` AS 下一级组织名称
FROM companyinfo AS c1
INNER JOIN companyinfo AS c2
WHERE c1.`companyid` = c2.`companypid` AND c1.`companypid` = 1
) AS temp
GROUP BY temp.组织编号, temp.一级组织名称;
MYSQL<五>
标签：查看 primary 个学生 com 步骤 mount 组织结构 用法 rem
