MYSQL--第三和第四章笔记

# ORDER BY 排序数据
# 如果没有对表进行排序操作，默认查询是按添加的顺序显示
SELECT * FROM employees;
# 1、排序
# 使用ORDER BY 对数据进行查询操作
# 升序：ASC
# 降序：DESC
# 如果在order by后面没有指明排序方式，默认是升序排列ASC
SELECT * FROM employees ORDER BY salary DESC;
# 可以对列的别名，进行排序，order by列的别名不用加单引号
SELECT department_id AS "部门编号",name AS "名字",
salary AS "月工资",salary * 12 AS "年工资" FROM employees ORDER BY 年工资 DESC;
# 注意：列的别名可以在order by中使用，不能在where中使用,否则会报错！！
# 注意：where要紧挨着from后面写!!
# 正确：SELECT * FROM employees WHERE salary IN (10000,8800) ORDER BY salary DESC;
# 错误：SELECT * FROM employees ORDER BY salary DESC WHERE salary IN (10000,8800);
# 二级排序
# 先按照月工资进行排序，然后再按部门id进行排序
SELECT * FROM employees ORDER BY salary DESC,department_id ASC;
# 其他排序，和二级排序类似，可以类推

# LIMIT 进行分页显示
# LIMIT 偏移量,显示条目数; 从偏移量+1到偏移量+显示条目数指针指向偏移量+1的位置
SELECT * FROM employees LIMIT 0,20;-- 表示从 1到20
# 每页显示pageSize条记录,显示第pageNo页
# LIMIT （pageNo - 1) * pageSize,pageSize;
# SELECT ... FROM ... WHERE ... ORDER BY ... LIMIT...
# 正确：SELECT * FROM employees WHERE salary >= 9000 ORDER BY salary DESC LIMIT 0,20;
# 报错：SELECT * FROM employees WHERE salary >= 9000 LIMIT 0,20 ORDER BY salary;
# 如果LIMIT的偏移量为0;可以写成LIMIT 条目数,省略偏移量0
SELECT * FROM employees WHERE salary >= 9000 ORDER BY salary DESC LIMIT 20;
# mysql8.0新特性：LIMIT ... OFFSET ...
# LIMIT 显示条目数 OFFSET 偏移量;
SELECT * FROM employees WHERE salary >= 7000 ORDER BY salary ASC LIMIT 30 OFFSET 0;
# 扩展：LIMIT可以在MYSQL、PGSQL、MariaDB、SQLite等数据库中使用，表示分页，
# 不能在SQL Sever、DB2、Oracle使用！！

# 多表查询
# 如果把多张表的数据全放在一张表上，
# 容易产生数据冗余，以及IO的次数多影响效率，后期在维护上也很费力，不利于高并发的场景
# 缺少连接条件：
# 笛卡尔积错误:
# SELECT department_id,name,city FROM employees,employees_detailed;
# SELECT department_id,name,city FROM employees CROSS JOIN employees_detailed;
# 错误产生的条件：
# 省略多个表的连接条件（或关联条件）
# 连接条件（或关联条件）无效
# 所有表中的所有行互相连接
-- 为了避免这种情况，可以在where加入有效的连接条件
# 多表查询的正确方式：需要连接条件
# 列的别名不能再where中使用，但表的别名可以再where中使用！！
SELECT * FROM employees AS e,employees_detailed AS ei
WHERE e.location_id = ei.location_id;
# 如果列名是多个表共有的列名,使用该列名时要用表名.列名来区分，建议都加表名！
# 注意：如果在from上给表起了别名，就必须在select和where中用别名，不能用原名！！
SELECT e.name as "名字",e.salary AS "月工资",ei.position AS "职位",et.type AS "状态" ,
e.location_id AS "e中的location",ei.location_id AS "ei中的location"
FROM employees e,employees_detailed ei,employees_type et
WHERE e.location_id = ei.location_id AND ei.age = et.age_detail;
# 如果有n个表，需要至少n-1个连接条件！！！
# 多表查询的分类
# 1、等值连接 vs 非等值连接
# 2、自连接 vs 非自连接
# 3、内连接 vs 外连接
# 等值连接 vs 非等值连接（连接条件是大于、小于之类的。。。。）
# 非等值连接：
SELECT e.name AS "名字",e.salary AS "月工资",es.salary_low AS "等级下限",es.salary_high AS "等级上限",es.salary_grade AS "等级"
FROM employees e,employees_slary es
WHERE e.salary BETWEEN es.salary_low AND es.salary_high;-- WHERE中不能用列别名
# 自连接（自己和自己连接） vs 非自连接
# 自连接：
# SELECT e.name AS "员工名字",em.name AS "员工上级名字",
# e.salary AS"员工月工资",em.salary AS"员工上级月工资",
# e.department_id AS "员工编号",em.department_id AS "员工上级编号",
# e.manager_id AS "员工上级编号",em.manager AS "上级上级编号"
# FROM employees e,employees em
# WHERE e.manager_id = em.department_id;
# 内连接 vs 外连接有点类似集合外连接的关键词“所有”
# 内连接：合并具有同一列的两个以上的表的行，结果集中不包含一个表与另一个表不匹配的行，
# 就是显示符合连接条件的行，不显示不符合连接条件的行（交集）
# 外连接：合并具有同一列的两个以上的表的行，结果集中除了包含一个表与另一个表匹配的行
# 之外，还查询了左表或右表中不匹配的行
# 外连接的分类：左外连接、右外连接、满外连接
# 左外连接：合并具有同一列的两个以上的表的行，结果集中除了包含一个表与另一个表匹配的行
# 之外，还查询了左表
# 前面写的基本都是sql92内连接写法，mysql支持sql92的内连接写法，不支持外连接写法
# 查询所有员工的name和city的信息（“所有”告诉要用外连接！）
# SELECT e.name,ed.city FROM employees e,employees_detailed ed WHERE e.location_id(+) = ed.location_id;报错因为mysql不支持sql92的外连接写法
# sql99的语法中使用join ... ON ...的方式实现多表的查询，mysql支持sql99的内外连接语法
# sql99的内连接写法：
SELECT e.department_id,e.`name`,e.sex,ed.city FROM employees e JOIN employees_detailed ed
ON e.location_id = ed.location_id;-- 两张表
SELECT e.department_id,e.`name`,e.sex,ed.city,et.type FROM employees e JOIN employees_detailed ed
ON e.location_id = ed.location_id JOIN employees_type et ON ed.age = et.age_detail;-- 多张表
# SELECT ... FROM 第一张表 JOIN 第二张表 ON 第一和第二张表的连接条件 JOIN 第三张表 ON 前面的表和第三种表的连接条件 JOIN....ON...类推
# sql99的左外连接写法：
SELECT e.name AS "名字",ed.city AS "城市" FROM employees_detailed ed LEFT JOIN employees e
ON ed.location_id = e.location_id;-- ed的location_id范围比e更大，而ed在左表，故是左外连接
# sql99的右外连接写法:
SELECT e.name AS "名字",ed.city AS "城市" FROM employees e RIGHT JOIN employees_detailed ed
ON ed.location_id = e.location_id;-- ed的location_id范围比e更大，而ed在右表，故是右外连接
# SELECT ... FROM 左表 LEFT /RIGHT JOIN 右表 ON 连接条件..
# 看左表和右表的所对应的连接条件，那个连接条件更“大”,就是哪边连接。
# sql99的满外连接写法：（满外连接在mysql中不支持）
# SELECT e.name AS "名字",ed.city AS "城市" FROM employees e FULL OUTER JOIN employees_detailed ed
# ON ed.location_id = e.location_id; 报错，mysql不支持该写法，但是这是正确的写法

# 用UNION和UNION ALL合并查询结果
# UNION操作符：返回两个查询的结果集的并集，去除重复记录，类似集合
# UNION ALL操作符：返回两个查询的结果集的并集，对于两个结果集的重复部分，不去重！类似集合
# 注意：能用UNION ALL时就不要用UNION，提高数据查询效率，因为UNION是在UNION ALL的基础上去重，
# 7种join的实现：
# 有集合a（左）和集合b（右），这两个集合有交集，集合a和集合b的交集是c
# 求集合c：内连接
SELECT e.department_id,e.name,e.location_id,ed.city,ed.position FROM employees e JOIN employees_detailed ed
ON e.location_id = ed.location_id;
# 求集合a：左外连接
SELECT e.department_id,e.name,e.location_id,ed.city,ed.position FROM employees e LEFT JOIN employees_detailed ed
ON e.location_id = ed.location_id;
# 求集合b：右外连接
SELECT e.department_id,e.name,e.location_id,ed.city,ed.position FROM employees e RIGHT JOIN employees_detailed ed
ON e.location_id = ed.location_id;
# 求集合a-c的部分：看要求来写，用where过滤
SELECT e.department_id,e.name,e.location_id,ed.city,ed.position FROM employees e JOIN employees_detailed ed
ON e.location_id = ed.location_id WHERE e.location_id > 10 OR e.location_id < 0;
# 求集合b-c的部分和上面的类似
# 用UNION ALL求满外连接（a和b的并集，取掉重复的部分！）：用集合a+去掉重复部分的集合b 或集合b+去掉重复部分的集合a
SELECT e.department_id,e.name,e.location_id,ed.city,ed.position FROM employees e RIGHT JOIN employees_detailed ed
ON e.location_id = ed.location_id
UNION ALL-- 改成UNION没有重复的了。。。
SELECT e.department_id,e.name,e.location_id,ed.city,ed.position FROM employees e JOIN employees_detailed ed
ON e.location_id = ed.location_id WHERE e.location_id > 10 OR e.location_id < 0;-- 这个好像有重复的。。。反正意思和这个差不多
# sql99的新特性：自然连接和using连接（扩展）
# 自然连接：我们可以把自然连接理解为sql92中的等值连接，它会帮你自动查询两张连接表中所有相同的列名，然后进行等值连接
SELECT * FROM employees e NATURAL JOIN employees_detailed ed; -- 虽然间接，但不够灵活
# USING连接：using里面放连接条件 USING(共有的连接条件);
SELECT * FROM employees e JOIN employees_detailed ed USING(location_id);

posted @ 2022-05-31 15:47 小k不理解阅读(56) 评论(0) 收藏举报

刷新页面返回顶部

Super Hero

MYSQL--第三和第四章笔记

公告