MySQL加强版练习

网络大夫

已于 2022-03-31 18:02:46 修改

阅读量823

点赞数

分类专栏： MySQL java 文章标签：数据库开发

于 2022-03-31 18:00:47 首次发布

本文链接：https://blog.csdn.net/xu_2962466566/article/details/123877627

版权

java 同时被 2 个专栏收录

5 篇文章 0 订阅

订阅专栏

MySQL

2 篇文章 0 订阅

订阅专栏

MySQL系列文章目录

前言

理解数据库三范式。
掌握使用单表查询。
理解多表查询的作用，并掌握内连接查询和左外连接查询的使用。
掌握使用分组函数。
掌握使用分组查询，并注意查询列可以有哪些比较合理。
了解单行函数。
掌握子查询的使用。

一、三范式是理解？

理解：设计数据库中默认遵守的规范
第一范式：每张表中的列的值是唯一的（必须遵守）
第二范式：每张表建议都要有主键（建议遵循）
第三范式：表中不能其他含有其他表中的非主键的值（建议遵循）

二、SQL分类

2.1、数据查询语言（DQL）

其语句，也称为“数据检索语句”，用以从表中获得数据，确定数据怎样在应用程序给出。保留字 SELECT 是 DQL（也是所有 SQL）用得最多的动词，其他 DQL 常用的保留字有 WHERE，ORDER BY，GROUP BY 和 HAVING。这些 DQL 保留字常与其他类型的 SQL 语句一起使用。

2.2、数据定义语言（DDL）

其语句包括动词 CREATE 和 DROP。在数据库中创建新表或删除表（CREAT TABLE 或 DROP TABLE）；为表加入索引等。DDL 包括许多与人数据库目录中获得数据有关的保留字。它也是动作查询的一部分。

2.3、数据操作语言（DML）

其语句包括动词 INSERT，UPDATE 和 DELETE。它们分别用于添加，修改和删除表中的行，Insert / Update / Delete。也称为动作查询语言。

2.4、事务处理语言（TCL）

单表查询

3 SELECT [DISTINCT] * | 字段 [别名] [, 字段 [别名]]
1 FROM 表名称 [别名]
2 [WHERE 条件(S)]
4 [ORDER BY 字段 [ASC|DESC] [, 字段 [ASC|DESC], …]]
5 [LIMIT ?,?];

1.全列和投影查询

一个是查询表中所有列，另一个是查询表中部分的列

2.消除重复

DISTINCT 关键字可以用于一列，也可以用于多列。比如：SELECT distinct job,deptno FROM emp; 只有当 job 和 deptno 相同，才认为是重复的数据

3.算术运算符

对 NUMBER 型数据可以使用算数操作符创建表达式（+ - * /）；
对 DATE 型数据可以使用算数操作符创建表达式（+ -）。

4. 过滤查询

4.1 过滤查询值的注意事项

字符串和日期要用单引号扩起来；
数字类型直接书写；
字符串是大小写不敏感的，日期值是格式大小写敏感的；
字符串若要大小写敏感，需要添加 binary 关键字。（SELECT * FROM dept WHERE BINARY DNAME = ‘sales’）

4.2 空值

用 NULL 表示，注意：

空值是指不可用、未分配的值，也就是没有值；
空值不等于零或空格，也不表示空字符串；
任意类型都可以支持空值，也就是说任何类型的字段都可以允许空值作为值的存在；
包括空值的任何算术表达式都等于空。
使用函数 IFNULL(expr1, expr2)，若 expr1 不是 NULL，IFNULL() 返回 expr1，否则它返回 expr2

4.3 常用算术比较运算符

=，!=，<>，>，>=，<，<=
BETWEEN … AND …：在两值之间（包含开始和结尾，数学中的闭区间）
IN：匹配列出的值；如：IN(1, 2, 3, 4)
LIKE ：匹配字符串模式， _ 、%，LIKE 运算符必须使用通配符才有意义；
- 匹配单个字符：_ 表示 1 个；
- 匹配任意多个字符：% 表示 0个、1个、多个。
IS NULL：是否为空

4.4 逻辑运算符

AND：如果组合的条件都是 true，返回 true；
OR：如果组合的条件之一是 true ，返回 true；
NOT：如果下面的条件是 false，返回 true。
优先级规则：比较运算符 > NOT > AND > OR。

4.5 结果排序

ASC：升序，缺省
DESC：降序
ORDER BY 子句出现在 SELECT 语句后执行；
ORDER BY 可以使用别名，但不能使用加了引号的别名或列名来排序，没有效果。

它的语句能确保被 DML 语句影响的表的所有行及时得以更新。TCL 语句包括 BEGIN TRANSACTION，COMMIT 和 ROLLBACK。

多表查询

1.1多表查询分类

内连接查询
- 隐式内连接查询
- 显示内连接查询
外连接查询
- 左外连接查询
- 右外连接查询
- 全外连接查询

内连接查询

1 隐式内连接

SELECT [DISTINCT] * | 字段 [别名] [, 字段 [别名], …]
FROM 表名称 [别名], [表名称 [别名], …]
[WHERE 条件(S)/消除笛卡尔积连接]
[ORDER BY 排序字段 [ASC|DESC] [, 排序字段 [ASC|DESC], …]];

在 WHERE 子句中写入连接条件；
当多个表中有重名列时，必须在列的名字前加上表名作为前缀或者表的别名（使用别名更简单，性能更高）；
等值连接是连接操作中最常见的一种，通常是在存在主外键约束条件的多表上建立的，连接条件中的两个字段通过等号建立等值关系。

2 显示内连接

SELECT table1.column, table2.column FROM table1 
[INNER] JOIN table2 ON table1.column1 = table2.column2
WHERE 条件

显示内连接查询：查询的结果和隐式内连接一模一样。区别在于：

显示内连接可以看到 [INNER] JOIN；
消除笛卡尔积条件使用写在 ON 子句。

3 外连接查询

3.1左外链接

SELECT table1.column, table2.column FROM table1 
LEFT [OUTER] JOIN table2 ON table1.column1 = table2.column2
WHERE 条件

查询出 JOIN 左边表的全部数据查询出来，JOIN 右边的表不匹配的数据使用 NULL 来填充数据。

3.2 右外链接（）

SELECT table1.column, table2.column FROM table1 
RIGHT [OUTER] JOIN table2 ON table1.column1 = table2.column2
WHERE 条件

查询出 JOIN 右边表的全部数据查询出来，JOIN 左边的表不匹配的数据使用 NULL 来填充数据。

分组函数

COUNT()：查询表中的数据记录；
AVG()：求出平均值；
SUM()：求和；
MAX()：求出最大值；
MIN()：求出最小值。

注意：

统计函数忽略空值，可以使用 IFNULL，因为是 NULL 不会影响汇总值，但会影响汇总数量；
不能在 where 语句中使用分组函数

分组查询

什么是分组函数

分组情况1：按照性别分组，男生一组，女生一组，之后可以统计男生和女生的数量；
分组情况2：按照年龄段分组，80 后一组，90 后一组；
分组情况3：按照籍贯分组，广东一组，湖南一组，江西一组。

分组语法

SELECT [DISTINCT] *|分组字段1 [别名] [, 分组字段2 [别名] ,…] | 统计函数
FROM 表名称 [别名], [表名称 [别名] , …]
[WHERE 条件(s)]
[GROUP BY 分组字段1 [, 分组字段2 ,…]]
[ORDER BY 排序字段 ASC | DESC [, 排序字段 ASC | DESC]];

分组效果：

使用 GROUP BY 子句将表分成小组；
结果集数据按升序排列，如果需要改变排序方式可以使用 ORDER BY 子句。

分组注意事项

SELECT 子句出现的字段，要么在统计函数中，要么不出现在 GROUP BY 子句中，否则不合理（整体与个体）；
在GROUP BY 子句中出现的字段，可以不出现在 SELECT 列表中；
统计函数可以单独使用，SQL 中可以没有 GROUP BY 子句；
在 GROUP BY 子句中，可以按单列进行分组，也可以在多列上进行分组，多列分组就是按照多个字段的组合进行分组，最终的结果也会按照分组字段进行排序显示。

分组限定

不能在 WHERE 子句中对分组限定，限制组须使用 HAVING 子句；
不能在 WHERE 子句中使用统计函数，而在 HAVING 子句可使用统计函数。

单行函数

日期函数

NOW()：获取当前时间；
DAY(date)：获取日期中的天数，范围是从 1 到 31；
HOUR(time)：返回 time 对应的小时数，范围是从 0 到 23；
MINUTE(time)：返回 time 对应的分钟数，范围是从 0 到 59；
MONTH(date)：返回 date 对应的月份，范围时从 1 到 12；
YEAR(date)：返回 date 对应的年份，范围是从 1000 到 9999；
LAST_DAY(date)：获取一个日期或日期时间值，返回该月最后一天对应的值。

日期转为字符串

DATE_FORMAT(date, format)：把日期转换为字符串。其中 format 中常见格式化符号如下：

SELECT DATE_FORMAT(NOW(), '%Y-%m-%d')

子查询

定义和作用

子查询指的就是在一个查询之中嵌套了其他的若干查询。

注意

子查询一般出现在 FROM 和 WHERE 子句中；
子查询要使用圆括号括起来；
将子查询放在比较运算符的右边（增强可读性）；
子查询在主查询前执行一次，主查询使用子查询的结果；但不宜嵌套过多。

多行单列

IN：与列表中的任意一个值相等
ANY：与子查询返回的任意一个值比较
=ANY：此时和 IN 操作符相同
>ANY：大于子查询中最小的数据
<ANY：小于子查询中最大的数据
ALL：与子查询返回的每一个值比较
>ALL：大于子查询中最大的数据
<ALL：小于子查询中最小的数据

练习1

use sqlstrengthen
CREATE TABLE student (
id INT(10) NOT NULL UNIQUE PRIMARY KEY,
name VARCHAR(20) NOT NULL,
sex VARCHAR(4) ,
birth YEAR,
department VARCHAR(20),
address VARCHAR(50)
)

CREATE TABLE score (
id INT(10) NOT NULL UNIQUE PRIMARY KEY AUTO_INCREMENT,
stu_id INT(10) NOT NULL ,
c_name VARCHAR(20) ,
grade INT(10)
)

INSERT INTO student VALUES( 901,'张老大', '男',1995,'计算机系', '北京市海淀区');
INSERT INTO student VALUES( 902,'张老二', '男',1996,'中文系', '北京市昌平区');
INSERT INTO student VALUES( 903,'张三', '女',2000,'中文系', '湖南省永州市');
INSERT INTO student VALUES( 904,'李四', '男',2000,'英语系', '辽宁省阜新市');
INSERT INTO student VALUES( 905,'王五', '女',2001,'英语系', '福建省厦门市');
INSERT INTO student VALUES( 906,'王六', '男',1998,'计算机系', '湖南省衡阳市');

INSERT INTO score VALUES(NULL,901, '计算机',98);
INSERT INTO score VALUES(NULL,901, '英语',80);
INSERT INTO score VALUES(NULL,902, '计算机',65);
INSERT INTO score VALUES(NULL,902, '中文',88);
INSERT INTO score VALUES(NULL,903, '中文',95);
INSERT INTO score VALUES(NULL,904, '计算机',70);
INSERT INTO score VALUES(NULL,904, '英语',92);
INSERT INTO score VALUES(NULL,905, '英语',94);
INSERT INTO score VALUES(NULL,906, '计算机',90);
INSERT INTO score VALUES(NULL,906, '英语',85);

-- 3.查询student表的所有记录
select * from student
-- 4.查询student表的第2条到4条记录 
select * from student where id  between 902 and 904 
-- 5.从student表查询所有学生的学号（id）、姓名（name）和院系（department）的信息
select id,name,department from student
-- 6.从student表中查询计算机系和英语系的学生的信息
select * from student where department = "计算机系" or department = "英语系"
-- 7.从student表中查询年龄18~22岁的学生信息
select *,(YEAR(now())- BIRTH) from student
select *,(YEAR(now())- BIRTH) as age from student HAVING age between 18 and 22
-- 8.从student表中查询每个院系有多少人 
select count(*),department from student GROUP BY department
-- 10.查询李四的考试科目（c_name）和考试成绩（grade）

select stu.name ,sc.c_name,sc.grade from student as stu 
JOIN score as sc ON stu.id = sc.stu_id
where stu.name="李四"
-- 11.用连接的方式查询所有学生的信息和考试信息
select * from student as stu 
JOIN score as sc ON stu.id = sc.stu_id
-- 12.计算每个学生的总成绩
select *,sum(sc.grade) '总成绩' from student as stu 
JOIN score as sc ON stu.id = sc.stu_id
GROUP BY name
-- 13.计算每个考试科目的平均成绩
select sc.c_name,avg(sc.grade) from student as stu 
JOIN score as sc ON stu.id = sc.stu_id
GROUP BY sc.c_name
-- 14.查询计算机成绩低于95的学生信息
select * from student as stu 
JOIN score as sc ON stu.id = sc.stu_id
where stu.department='计算机系' and sc.grade < 95
-- 15.查询同时参加计算机和英语考试的学生的信息(待定)
select * from student 
where id= any(
select stu_id from score 
where stu_id 
in(select stu_id from score where c_name='计算机')
and c_name = "英语"
)
-- 16.将计算机考试成绩按从高到低进行排序
select * from student as stu 
JOIN score as sc ON stu.id = sc.stu_id
where sc.c_name = '计算机'
ORDER BY sc.grade DESC
-- 17.从student表和score表中查询出学生的学号，然后合并查询结果
select *,student.id from  student,score where student.id = score.stu_id

-- 18.查询姓张或者姓王的同学的姓名、院系和考试科目及成绩
select stu.name,stu.department,sc.c_name,sc.grade from student as stu 
JOIN score as sc ON stu.id = sc.stu_id
where stu.name like '张%' or stu.name like '王%'
-- 19.查询都是湖南的学生的姓名、年龄、院系和考试科目及成绩
select stu.name,stu.address,stu.department,sc.c_name,sc.grade from student as stu 
JOIN score as sc ON stu.id = sc.stu_id
where stu.address like '湖南%'

练习2

-- - 询所有员工信息。
SELECT * from emp
-- - 查询每个员工的编号、姓名、职位。
SELECT empno, ename, job from emp
-- - 查询所有部门信息。
select * from dept
select * from emp
select * from salgrade
select * from dept,emp,salgrade
-- - 查询所有有员工的部门编号。
select ename,deptno from emp
-- - 查询有员工的部门和职位。
select ename,job,dname from emp,dept where emp.deptno=dept.deptno
-- - 查询所有员工的年薪。如果为 NULL 则返回第二个参数的值,如果不为 NULL 则返回第一个参数的值
select (sal+(IFNULL(sal,0)))*12  from emp
-- - 查询所有员工的年薪（使用别名）。
select (sal+(IFNULL(sal,0)))*12 年薪 from emp
-- - 查询所有员工的年薪((月薪 + 奖金) * 12) 。
select (sal+(IFNULL(sal,0)))*12 年薪 from emp
-- - 查询有奖金的员工信息。
select * from emp where comm is not null;
-- - 查询公司的老板。
select * from emp where mgr is null;
-- - 查询出基本工资高于 1500 的所有员工信息。
select * from emp where sal>1500
-- - 查询名字叫 SCOTT 的员工所从事的工作。
select * from emp where ename = "SCOTT"
-- - 查询 1981 年入职的员工信息。
select * from emp where YEAR(hiredate) = 1981
-- - 查询年薪小于 3W 的员工。
select ename,(sal+(IFNULL(sal,0)))*12 '年薪' from emp where (sal+(IFNULL(sal,0)))*12 < 30000
-- - 查询所有不是销售人员的员工信息。
select * from emp where not job = "salesman"
-- - 查询工资在 2000-3000 之间的员工信息。
select * from emp where sal  between 2000 and 3000 
-- - 查询 1981 年入职的员工。
select * from emp where YEAR(hiredate) = 1981
-- - 查询工资为 800 或 1600 或 3000 的员工。
select * from emp where sal in (800,1600,3000)
-- - 查询出所有雇员姓名是以 A 开头的全部雇员信息。
select * from emp where ename like "A%"
-- - 查询出雇员姓名第二个字母是 M 的全部雇员信息。
select * from emp where ename like "_M%" 
-- - 查询出雇员姓名任意位置上包含字母 A 的全部雇员信息。
select * from emp where ename like "%A%"
-- - 查询姓名中有 e 或者 a 的员工姓名。
select * from emp where ename like "%e%" or ename like "%a%"
-- - 查询工资在 1500~3000 之间的全部员工信息。
select * from emp where sal between 1500 and 3000
-- - 查询工资不在 2000-3000 之间的员工信息。
select * from emp where sal not between 2000 and 3000
-- - 查询工资不为 800 或 1600 或 3000 的员工。
select * from emp where sal not in (800,1600,3000)
-- - 查询出职位是办事员 (CLERK) 或者是销售人员 (SALESMAN) 的全部信息，且工资在 1000 以上。
select * from emp where (job = 'CLERK' or job = 'SALESMAN') and sal > 1000
-- - 查询所有员工信息，按照工资排序。
select * from emp order by sal
-- - 查询所有员工信息，按照年薪降序排序。
select * from emp order by sal desc
-- - 查询所有员工信息，按照部门和年薪降序排序。
select * from emp,dept WHERE emp.deptno = dept.deptno order by sal desc
-- - 查询员工编号，员工名称，员工所属部门的编号和名称。
select emp.empno, emp.ename, dept.deptno, dept.dname from emp,dept WHERE emp.deptno = dept.deptno
-- - 查询员工的姓名，工资，所在部门的名称，以及工资的等级。
select * from salgrade
select  emp.ename, emp.EMPNO, dept.dname , emp.sal from emp,dept WHERE emp.deptno = dept.deptno 

-- - 查询所有员工每个月的平均工资及总工资。
select avg(sal),sum(sal) from emp

-- - 查询月薪在 2000 以上的员工总人数。
select count(*)from emp where sal>2000
-- - 查询员工最高工资和最低工资差距。
select max(sal) max, min(sal) ,(max(sal)-min(sal))'差距'from emp
-- - 按照职位分组，求出每个职位的最高和最低工资。
select job,max(sal),min(sal) from emp group by job;
-- - 查询出每一个部门员工的平均奖金。
select *,avg(IFNULL(comm,0)) from emp group by deptno;
-- - 查询出每一个部门员工的平均工资。
select *,avg(sal) "平均工资" from emp group by deptno;
-- - 查询各个部门和岗位的平均工资。
select *,avg(sal) from emp GROUP BY deptno,job
-- - 查询部门平均工资高于 2000 的部门及其平均工资。
select deptno,avg(sal) as s from emp GROUP BY deptno having s>2000
-- - 查询在 80，81，82 年各进公司多少人。
select YEAR(hiredate) y, count(empno) from emp
GROUP BY YEAR(hiredate) 
HAVING y between '1980' and '1982'
-- - 查询各个管理人员下员工的平均工资，其中最低工资不能低于 1300，不计算老板。
select mgr,avg(sal),min(sal) as minSal from emp GROUP BY mgr HAVING mgr is not null and minSal > 1300
-- - 查询出工资比 MARTIN 还要高的全部雇员信息。
select * from emp where sal > (select sal from emp where ename = "MARTIN" )
select sal from emp where ename = "MARTIN" 
-- - 查询平均工资高于公司平均工资的部门信息
select avg(sal) from emp 
select deptno,avg(sal) as avgdept from emp group by deptno HAVING avgdept > (select avg(sal) from emp )
-- - 查询工资等于部门经理（职位是 MANAGER）的员工信息。
select * from emp where sal in (select sal from emp WHERE job = 'MANAGER')

-- - 查询出每个部门的编号、名称、部门人数、平均工资。

select emp.deptno,dept.dname,count(emp.EMPNO) from emp,dept where emp.deptno = dept.deptno

select emp.DEPTNO '部门的编号',dept.DNAME '名称',count(emp.EMPNO) "部门人数",avg(emp.sal) '平均工资' from emp,dept 
where emp.deptno = dept.deptno
GROUP BY dept.deptno