DAY01-mysql加强

作者: 建国同学 | 来源:发表于2020-04-20 23:54 被阅读0次

一、 mysql的进一步介绍

三范式

1NF:字段不可分，每个字段是原子级别的。
不满足第一范式（1NF）的数据库就不是关系数据库
2NF：在1NF基础上建立，要求数据库表中的每个实体即每个记录行可以被唯一的区分，这个唯一属性即关键字---主键
3NF：在2NF基础上建立，要求一个关系表中不包含其他表中包含的非关键字信息，即不存在传递依赖关系。

SQL分类

SQL语言按照实现的功能不同，主要分为3类：数据操纵语言（DML），数据定义语言（DDL）,数据控制语言（DCL）。

1.数据操纵语言（DML）：主要用来处理数据库中的数据内容。允许用户对数据库中的数据进行查询，插入，更新和删除等操作

2.数据定义语言（DDL）:是一组SQL命令，用于创建和定义数据库对象，并且将对这些对象的定义保存到数据字典中。通过DDL语句可以创建数据库对象，修改数据库对象和删除数据库对象等。

3.数据控制语言（DCL）：数据控制语言用于修改数据库结构的操作权限。DCL语句主要有2种：
按六类说明：

DDL数据定义语言：create、drop
DML数据操作语言：insert、delete、update
DCL数据控制语言：grant、revoke
DQL数据查询语言：select、where、order by、group by、having
TPL数据处理语言：确保被DML影响的表的所有行及时得以更新，begin transaction、commit、rollback
CCL指针控制语言：declare cursor，fetch into和update where current 用于对一个或多个表单独的操作。

SQL语句规范
编写SQL语句时需要遵循一定的规则：

1.SQL关键字、对象名、和列名不区分大小写。
2.字符值和日期值要区分大小写。
3.在应用程序中如果SQL语句文本很长，可以将语句分布到多行上，并且可以通过使用跳格和缩进提高代码的可读性
4.SQL*Plus中的SQL语句以分号（;）结束。

二、单表查询

语法：
SELECT [DISTINCT] * | 字段 [别名] [,字段 [别名]]
FROM 表名称 [别名]
[WHERE 条件(S)]
[ORDER BY 字段 [ASC|DESC] [,字段 [ASC|DESC],…]];

消除重复（DISTINCT）

DISTINCT 关键字可以用于一列，也可以用于多列。
只有当job 和 deptno中的一条数据重复，才认为是重复的数据。
SELECT DISTINCT DEPTNO,JOB FROM emp

image.png

算术运算符

对 NUMBER 型数据使用（+ - * /）算数操作符创建表达式；
对 DATE 型数据使用（+ -）算数操作符创建表达式。
-- 查询所有员工的年薪（使用别名) SELECT SAL*12 AS SALOFYEAR FROM emp

过滤查询（WHERE ）

注意：
字符串和日期要用单引号扩起来
字符串是大小写不敏感的，日期值是格式大小写敏感的；

字符串大小写敏感（binary ）

演示：
SELECT * FROM dept WHERE BINARY DNAME = 'sales'

比较运算符

= & != & <> & > & >= & < & <=
BETWEEN ... AND ...：在两值之间，闭区间包含
-- 查询工资在 2000-3000 之间的员工信息 SELECT * FROM emp WHERE SAL BETWEEN 2000 AND 3000
IN：匹配列出的值；如：IN(1, 2, 3, 4)
-- 查询工资为 800 或 1600 或 3000 的员工 SELECT * FROM emp WHERE SAL IN (800,1600,3000)
LIKE ：匹配字符串模式如：_ （占位符一个字符）、%（通配符多个字符）
-- 根据关键字模糊查询(员工名称和职位)根据sal 值查询 sal=800
SELECT * FROM emp WHERE (ENAME LIKE '%S%' OR JOB LIKE '%C%') AND SAL = 800 注意括号
IS NULL：是否为空

空值判断

空值不等于零或空格，也不表示空字符串，也就是没有值
任何类型的字段都可以允许空值作为值的存在
包括空值的任何算术表达式都等于空
函数 IFNULL(expr1, expr2)，若 expr1 不是 NULL，IFNULL() 返回expr1，否则它返回expr2
-- 查询所有员工的年薪((月薪 + 奖金) * 12)
SELECT (SAL + IFNULL(COMM,0)) * 12 FROM emp

逻辑运算符

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

例子：
SELECT * FROM emp WHERE NOT (SAL = 800 OR SAL = 1600 OR SAL = 3000)
OR条件是独立的需要加括号后再进行操作

结果排序

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

三、多表查询

why use it

需要查询的数据分散在多张表中，只有联合多张表才能查询出期望的数据

消除笛卡尔积

其意思：在数学中，假设集合 A = {a, b}，集合 B = {0, 1, 2}，则两个集合的笛卡尔积为 {(a, 0), (a, 1), (a, 2), (b, 0), (b, 1), (b, 2)}。
解决方案：在WHERE加入有效的连接条件 ----> 等值连接，注意：连接 n 张表，至少需要n - 1个连接条件。

多表查询分类

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

多表查询

四、内连接查询

隐式内连接查询

SELECT [DISTINCT] * | 字段 [别名] [,字段 [别名] ,…]
FROM 表名称 [别名], [表名称 [别名] ,…]
[WHERE 条件(S)/等值连接]
[ORDER BY 排序字段 [ASC|DESC] [,排序字段 [ASC|DESC] ,…]];

显式内连接查询（常用）

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

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

-- 查询员工编号，员工名称，员工所属部门的编号和名称
-- 隐式内连接查询
SELECT e.EMPNO, e.ENAME, e.DEPTNO, d.DNAME 
FROM emp e,dept d 
WHERE e.DEPTNO = d.DEPTNO
-- 显式内连接查询
SELECT e.EMPNO, e.ENAME, e.DEPTNO, d.DNAME 
FROM emp e JOIN dept d 
ON e.DEPTNO = d.DEPTNO

五、外连接查询

解决什么：

连表查询时候，查询的某个条件为空导致整条没有查出来

左外连接查询（ LEFT [OUTER] JOIN）

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

右外连接查询（RIGHT [OUTER] JOIN）

SELECT table1.column, table2.column
FROM table1 RIGHT [OUTER] JOIN table2 ON(table1.column1 = table2.column2)
WHERE 条件
SELECT emp.EMPNO, emp.ENAME, emp.SAL, dept.DNAME FROM dept RIGHT JOIN emp ON emp.DEPTNO = dept.DEPTNO

查询的结果：没有的值就是null

六、分组函数

函数分类

单行函数：将每条数据进行独立的计算，然后每条数据得到一条结果；
多行函数：多条数据同时计算，最终得到一条结果数据。也成为聚集函数、分组函数，主要用于完成一些统计功能等等。

多行函数（聚合函数）

多行函数也称聚合函数（aggregate function）？

聚合函数对一组值执行计算并返回单一的值。

聚合函数有什么特点？
除了 COUNT 以外，聚合函数忽略空值。
聚合函数经常与 SELECT 语句的 GROUP BY 子句一同使用。
所有聚合函数都具有确定性。任何时候用一组给定的输入值调用它们时，都返回相同的值。
标量函数：只能对单个的数字或值进行计算。主要包括字符函数、日期/时间函数、数值函数和转换函数这四类。

COUNT()：查询表中的数据记录；
AVG()：求出平均值；
SUM()：求和；
MAX()：求出最大值；
MIN()：求出最小值。
$\color{#FF0000}{注意：统计函数忽略空值，可以使用 IFNULL，因为是NULL不会影响汇总值，但会影响汇总数量；不能在where语句中使用分组函数。 }$
-- 查询员工最高工资和最低工资差距
SELECT MAX(SAL) - MIN(SAL) FROM emp

七、分组查询（GROUP BY）

把表分组，再进行操作

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

-- 按照职位分组，求出每个职位的最高和最低工资
SELECT JOB,MAX(SAL) max, MIN(SAL) min FROM emp GROUP BY JOB ORDER BY SAL
$\color{#FF0000}{注意 :}$

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

分组限定（HAVING）

不能在WHERE子句中对分组限定，限制组须使用HAVING子句；
不能在WHERE子句中使用统计函数，而在HAVING子句可使用统计函数。
$\color{#FF0000}{HAVING后一般需要起个别名来操作，注意：空值判断不能这样使用 != null ,语法错误}$
-- 查询各个管理人员下员工的平均工资，其中最低工资不能低于1300，不计算老板
SELECT MGR, AVG(SAL), MIN(SAL) AS minsal FROM emp GROUP BY MGR HAVING MGR IS NOT NULL AND minsal >= 1300

八、单行函数

获取日期时间中某个段

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

本地时间：
SELECT YEAR(NOW()), MONTH(NOW()), DAY(NOW()), HOUR(NOW()), MINUTE(NOW()), SECOND(NOW())

不带分割符的时间：
SELECT LAST_INSERT_ID(NOW()) 例子：20200419184521

日期转换为字符串（DATE_FORMAT）

DATE_FORMAT(date, format)：把日期转换为字符串。

日期转换为字符串
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d')

九、子查询

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

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

分类

按查询结果分类：
单行单列：只包含一个字段的查询，返回的查询结果也只包含一行数据；
多行单列：只包含了一个字段，但返回的查询结果可能多行或者零行；
多行多列：包含多个字段的返回，查询结果可能是单行或者多行，好比是一张表。

单行单列

一般用于 WHERE 之后的子查询，子查询结果是一行一列记录。
使用单行记录比较运算符：=、>、>=、<、<=、<>。(单行单列才能用)

-- 查询出工资比 MARTIN 还要高的全部雇员信息
SELECT * FROM emp WHERE sal > (SELECT sal FROM emp WHERE ename = 'MARTIN')

多行单列

一般也用于 WHERE 子句中，子查询结果只有一列，但是有多行。
IN：与列表中的任意一个值相等

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

ALL：与子查询返回的每一个值比较
>ALL：大于子查询中最大的数据
<ALL：小于子查询中最小的数据

-- 查询工资等于部门经理（职位是 MANAGER）的员工信息。
SELECT * FROM emp WHERE SAL IN (SELECT SAL FROM emp WHERE JOB = 'MANAGER')

多行多列

子查询的结果是多行多列，一般会把子查询返回的结果当成一个临时表，接着在临时表上继续查询或者连接查询；
注意：多行多列的子查询返回的结果必须要设置一个临时表名。

-- 查询出每个部门的编号、名称、部门人数、平均工资部门名称
SELECT e.DEPTNO, d.DNAME, COUNT(*), AVG(e.SAL), e.ENAME FROM emp e JOIN dept d ON e.DEPTNO = d.DEPTNO GROUP BY e.DEPTNO