[大数据]MySQL加强版练习

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