SQL数据库--DQL-函数-多表查询与设计

最新推荐文章于 2024-08-02 15:49:51 发布
YB_HAO
最新推荐文章于 2024-08-02 15:49:51 发布
阅读量98
点赞数
文章标签：数据库 sql database
本文链接：https://blog.csdn.net/YB_HAO/article/details/125172006
版权
学习目标：

-- 准备数据
create table dept
(
    id   int auto_increment comment 'ID' primary key,
    name varchar(50) not null comment '部门名称'
) comment '部门表';


create table emp
(
    id        int auto_increment comment 'ID' primary key,
    name      varchar(50) not null comment '姓名',
    age       tinyint comment '年龄',
    job       varchar(20) comment '职位',
    salary    int comment '薪资',
    entrydate date comment '入职时间',
    managerid int comment '直属领导ID',
    dept_id   int comment '部门ID'
) comment '员工表';

INSERT INTO dept (id, name)
VALUES (1, '研发部'),
       (2, '市场部'),
       (3, '财务部'),
       (4, '销售部'),
       (5, '总经办'),
       (6, '人事部');
INSERT INTO emp (id, name, age, job, salary, entrydate, managerid, dept_id)
VALUES (1, '金庸', 66, '总裁', 20000, '2000-01-01', null, 5),
       (2, '张无忌', 20, '项目经理', 12500, '2005-12-05', 1, 1),
       (3, '杨逍', 33, '开发', 8400, '2000-11-03', 2, 1),
       (4, '韦一笑', 48, '开发', 11000, '2002-02-05', 2, 1),
       (5, '常遇春', 43, '开发', 10500, '2004-09-07', 3, 1),
       (6, '小昭', 19, '程序员鼓励师', 6600, '2004-10-12', 2, 1),
       (7, '灭绝', 60, '财务总监', 8500, '2002-09-12', 1, 3),
       (8, '周芷若', 19, '会计', 48000, '2006-06-02', 7, 3),
       (9, '丁敏君', 23, '出纳', 5500, '2009-05-13', 7, 3),
       (10, '赵敏', 20, '市场部总监', 12500, '2004-10-12', 1, 2),
       (11, '鹿杖客', 56, '职员', 3750, '2006-10-03', 10, 2),
       (12, '鹤笔翁', 19, '职员', 3750, '2007-05-09', 10, 2),
       (13, '方东白', 19, '职员', 5500, '2009-02-12', 10, 2),
       (14, '张三丰', 88, '销售总监', 14000, '2004-10-12', 1, 4),
       (15, '俞莲舟', 38, '销售', 4600, '2004-11-12', 14, 4),
       (16, '宋远桥', 40, '销售', 4600, '2004-10-12', 14, 4),
       (17, '陈友谅', 42, null, 2000, '2011-10-12', 1, null);

SELECT NAME
FROM emp
WHERE NAME = '杨逍';
SELECT age
FROM emp
WHERE age < 20;
-- 查询null值用is 或者is not
SELECT job
FROM emp
WHERE job IS NULL;
-- 不等于的使用
SELECT age
FROM emp
WHERE age != 60;
-- 同类型范围并且使用BETWEEN
SELECT age
FROM emp
WHERE age BETWEEN 15 AND 25;
-- 并且的使用
SELECT salary, age
FROM emp
WHERE salary >= 6000
  AND age >= 25;
-- 或者的使用
SELECT age
FROM emp
WHERE age IN (19, 20, 60);
-- 占位符使用
SELECT name
FROM emp
WHERE name LIKE '%东%';

--  聚合函数实例-----------------------

-- 统计该企业的员工数量
SELECT COUNT(id)
FROM emp;-- 一列的中不能含有空值;

SELECT COUNT(*)
FROM emp; -- 推荐使用*,
SELECT COUNT(1)
FROM emp;

-- 统计该企业员工的平均薪资
SELECT AVG(salary)
FROM emp;

-- 企业员工的最高薪资
SELECT MAX(salary)
FROM emp;

-- 最低薪资
SELECT MAX(salary)
FROM emp;

-- 薪资之和
SELECT SUM(salary)
FROM emp;

-- 分组查询------------------------------
-- 根据分组,统计各个职位员工的数量 ---group by ,count
SELECT job, COUNT(*)
FROM emp
GROUP BY job;
-- 分组之后查询返回的字段为聚合函数字段,分组字段.

-- 根据职位分组,统计平均年龄
SELECT job, AVG(age)
FROM emp
GROUP BY job;

-- 现车寻你年龄小于45的员工,并对结果根据 职位 job 分组,获取员工数量大于等于2,要求年龄大于45的不参与分组
-- have 过滤
SELECT job, COUNT(age)
FROM emp
WHERE age < 45
GROUP BY job
HAVING COUNT(age) >= 2;
-- where 是在分组前进行操作,having是对分组过后进行过滤,
-- 且where之后不能使用聚合函数,having可以;

-- 排序查询-------------------------------
-- SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式(ASC:升序 DESC:降序),字段2 排序方式;
-- 1.更具年龄对公司的员工进行排序;
SELECT age
FROM emp
ORDER BY age;
-- ASC可以省略,是默认排序方式

-- 根据入职时间,进行降序排序
SELECT entrydate
FROM emp
ORDER BY entrydate DESC;

-- 根据年龄进行升序排序,如果年龄相同更具入职时间进行降序排序
SELECT age , entrydate
FROM emp
ORDER BY age, entrydate DESC;


-- 分页查询(limit)----------------
-- SELECT 字段列表 FROM 表名 LIMIT 起始索引 ,结束索引;
-- 查询第一页员工数据 (起始索引(页数-1)*每页条数:查询的开始位置((是实际展示的位置-1),默认从0开始,结束索引表示要查询得条数)
SELECT *
FROM emp
LIMIT 0 ,5;-- 仅仅在查询第一页的时候可以省略其实索引
SELECT *
FROM emp
LIMIT 5 ,5;
SELECT *
FROM emp
LIMIT 10 ,5;
-- 分页查询是数据库查询的方言,不同的数据库分页查询语言不同,MYSQL中是LIMIT;


-- 函数--------------------------------------------------------------------------------------------
-- 什么是函数?
-- 函数:是指一段可以倍直接调用的程序代码
-- 字符串函数-------------------------------------------------
-- CHAR_LENGTH(S) 字符串长度
-- 查询每个员工名字的长度
SELECT CHAR_LENGTH(name)
FROM emp;
-- CONCAT_WS ()字符串拼接
SELECT CONCAT(name, ':', salary)
FROM emp;

-- LEFT 左侧获取指定的字符串你长度(RIGHT)
SELECT LEFT(name, 2)
FROM emp;
SELECT RIGHT(name, 1)
FROM emp;

-- SUBSTRING:截取字符串长度(SUBSTRING(被截取内容,截取的开始位置,截取长度))
SELECT SUBSTRING(name, 1, 2)
FROM emp;
-- 数值函数-------------------------------------------
-- CEIL(X)  向上取整
SELECT CEIL(1.5);
-- FLOOR(X) 向下取整
SELECT FLOOR(2.5);
-- RAND:获取随机数
SELECT RAND();
-- ROUND(数值,保留位数): 四舍五入
SELECT ROUND(1.56, 1) ;
-- 日期函数---------------------------
-- NOW()当前日期
SELECT NOW();
-- CURDATE()---当前日期
SELECT CURDATE();
-- CURTIME---当前时间
SELECT CURTIME();

-- YEAR .MONTH. DAY :当前年 月 日
SELECT YEAR(NOW());
SELECT MONTH(NOW());
SELECT DAY(NOW());

-- DATEDIFF :获取两个日期之间的差
SELECT DATEDIFF(NOW(), '1998-09-13');


-- 流程控制函数----------------------------
-- 查询员工的姓名和年龄,如果年龄大于60岁,显示老人,否则显示年轻人
-- IF(条件判断,如果真返回的值,如果假返回的值)
SELECT NAME, IF(age >= 60, '老年人', '年轻人')
FROM emp;

-- 查询院女工的姓名及职位,如果单位职位为null,则显示未分配
-- IFNULL(如果不是空返回的值,如果是空返回的值)
SELECT name, IFNULL(job, '未分配')
FROM emp;

-- 查询员工的姓名及年龄,如果年龄大于40显示老年人,如果年龄大于40小于60显示中年人,
-- 否则显示年轻人 (CASE 的执行规律:如果第一个条件执行之后成立不会再往后执行)
SELECT name, (CASE WHEN age >= 60 THEN '老年人' WHEN age > 40 THEN '中年人' ELSE '年轻人' END)
FROM emp;

-- 案例1:计算表中的每个员工的入职天数
SELECT name, DATEDIFF(NOW(), entrydate)
FROM emp;
create table score
(
    id      int comment 'ID',
    name    varchar(20) comment '姓名',
    math    int comment '数学',
    english int comment '英语',
    chinese int comment '语文'
) comment '学员成绩表';
insert into score(id, name, math, english, chinese)
VALUES (1, 'Tom', 67, 88, 95),
       (2, 'Rose', 23, 66, 90),
       (3, 'Jack', 56, 98, 76);


-- 根据分数转换规则,完成学生的分数登记转化,
-- 分数大于等于85,优秀
-- 分数小于85,大于60,及格
-- 分数小于60,不及格;

SELECT id,
       name,
       (CASE WHEN math >= 85 THEN '优秀' WHEN math > 60 THEN '及格' ELSE '不及格' END)       ,
       (CASE WHEN english >= 85 THEN '优秀' WHEN english > 60 THEN '及格' ELSE '不及格' END) ,
       (CASE WHEN chinese >= 85 THEN '优秀' WHEN chinese > 60 THEN '及格' ELSE '不及格' END)
FROM score;


-- ============多表设计与查询=============
-- ==多表关系== -->概述: 关系分为 一对多 一对一 多对多
--  ===一对多-->案例:
-- 部门与员工的关系 一个部门多个员工
-- (查看表的关系 CSA+U;)
-- === 多对多-->一个学生可以选择多门课程,一门课程也可以供多个学生选择
-- 实现: 建立第三章中间表,中间表至少有两个外键,分别关联两方的主键
-- ==一对一 用户和用户详情表的关系
-- >实现:在任意一方加入外键,关联另外一方的主键,并且设置外键为唯一的(UNIQUE)

-- ==多表查询概述==-->
SELECT *
FROM emp,
     dept;
-- 现象分析:上诉代码查询时,会吧所有的情况组合一次,做一次笛卡尔乘积.
-- 因此在过程中应该删除无用的笛卡尔积.
SELECT *
FROM dept,
     emp
WHERE emp.dept_id = dept.id;
-- 通过连接的外键的值相等消除无效的笛卡尔积

-- 多表查询的分类
-- 连接查询: 内连接 相当于查询AB交集 的部分 ;外连接-->左连接 :查询左表及交集部分,右连接:查询右表及相交部分.


-- ======>内连接
-- 查询交集部分的数据:
-- 语法:
-- 隐式内连接 :SELECT 字段表 FROM 表一,表二 WHERE 条件;
-- 查询每一个员工的部门名称
SELECT E.name, D.name
FROM emp E,
     dept D
WHERE E.dept_id = D.id;
-- 表名比较长可以选择给表名取个别名,取别名需要在FROM后的表名后直接输入别名,且取了之后只能用别名调用
-- 显示内连接 SELECT 字段列表 FROM  表一  INNER JOIN 表二 ON 连接条件;
SELECT E.name, D.name
FROM emp E
         INNER JOIN dept D
                    on D.id = E.dept_id;

-- =======>外连接
-- 左外连接: SELECT 字段列表 表一 LEFT [OUTER] JOIN 表二 ON 条件:
SELECT E.name, D.name
FROM emp E
         LEFT JOIN dept D ON E.dept_id = D.id;
-- 右外连接:SELECT 字段列表 表一 RIGHT [OUTER]JOIN 表二 ON
SELECT D.name, E.name
FROM emp E
         RIGHT JOIN dept D ON E.dept_id = D.ID;


-- =======>自连接查询
-- SELECT 字段列表 表A 别名 JOIN 表A  ON 条件  ;(必须要取别名)
-- 查询所有员工及其所属领导的名字
SELECT E.name, L.name
FROM emp E,
     emp L
WHERE E.managerid = L.id;
-- 查询所有员工及其领导的名字,没有领导也要查出
SELECT E.name, L.name
FROM emp E
         LEFT JOIN emp L ON E.managerid = L.id;

-- ====子查询 ;
-- SELECT  FROM T1 WHERE  COLUMN1 = (SELECT  COLUMN1 FROM T2);
-- ===标量子查询 返回结果为单个值(数字\字符串\日期等)
-- 查询销售部的所有员工信息
-- SELCT 拆分写法-->
-- 1 查询部门ID
SELECT id
FROM dept
WHERE name = '销售部';
-- 2 根据部门的id,查询员工的信息
SELECT *
FROM emp
WHERE dept_id = 4;

-- 查询方东白入职之后的员工信息;
SELECT *
FROM emp
WHERE entrydate >= (SELECT entrydate FROM emp WHERE name = '方东白');

-- ===列子查询
-- 返回的结果是一列(可以是多行)
-- 查询销售部和市场部的所有员工信息


-- == 行子查询
-- 返回的结果是一行 (可以是多列)
-- 查询与鹿杖客的工资相同的员工及员工;
SELECT *
FROM emp
WHERE (salary, job) = (SELECT salary, job FROM emp WHERE name = '鹿杖客');

-- == 表子查询

-- 查询 2006年-01-01之后的员工信息及部门信息;
SELECT *
FROM (SELECT * FROM emp WHERE entrydate >= '2006-01-01') E
         LEFT JOIN dept ON E.dept_id = dept.id;


-- ============================ 多表查询练习 ==========================
create table salgrade
(
    id    int primary key auto_increment,
    grade int,
    losal int,
    hisal int
) comment '薪资等级表';

insert into salgrade(grade, losal, hisal)
values (1, 0, 3000),
       (2, 3001, 5000),
       (3, 5001, 8000),
       (4, 8001, 10000),
       (5, 10001, 15000),
       (6, 15001, 20000),
       (7, 20001, 25000),
       (8, 25001, 30000);

-- 1. 查询员工的姓名、年龄、职位、部门名称信息。 （隐式内连接）
SELECT E.name, E.age, E.job, D.name
FROM emp E,
     dept D
WHERE E.dept_id = D.ID;

-- 2. 查询年龄小于30岁的员工姓名、年龄、职位、部门信息。 （显式内连接）
SELECT E.name, E.job, E.age, D.name
FROM emp E
         JOIN dept D ON E.dept_id = D.id
WHERE age < 30;
-- 3. 查询所有年龄大于40岁的员工, 及其归属的部门名称; 如果员工没有分配部门, 也需要展示出来。
SELECT E.name, E.age, E.job, D.name
FROM emp E
         LEFT JOIN dept D ON E.dept_id = D.id
WHERE E.age > 40;
-- 4. 查询所有员工的 姓名、薪资、薪资等级
SELECT E.name, E.salary, S.grade, S.hisal, S.losal
FROM emp E,
     salgrade S
where E.salary BETWEEN S.losal AND S.hisal;
-- 5. 查询 "研发部" 员工的平均工资。
SELECT AVG(salary)
FROM emp
WHERE id = (SELECT id FROM dept D WHERE name = '研发部');
-- 6. 查询工资比 "灭绝" 高的员工信息。
SELECT salary
FROM emp
WHERE name = '灭绝';
SELECT *
FROM emp E
WHERE salary > (SELECT salary
                FROM emp
                WHERE name = '灭绝'
);
-- 7. 查询比平均薪资高的员工信息。
SELECT *
FROM emp E
WHERE salary > (SELECT AVG(salary) FROM emp);

-- 8. 查询低于本部门平均工资的员工信息
SELECT *
FROM emp
WHERE salary < (SELECT AVG(salary) FROM emp WHERE dept_id = dept_id);