day 01 MySQL环境搭建

基本概念

MySQL的安装、卸载、字符设置、常见问题

1. chapter 02 宋红康笔记

第02章_MySQL环境搭建.pdf

MySQL5.7版本：
字符设置细节：如果在创建表后修改字符格式，需要将此表删除，重新创建。否则还是之前为修改的字符集！！！
数据库的默认字符集是拉丁，如果需要存储中文，需要将字符集格式修改为UTf-8，修改后要重启服务

MySQL8.0版本：
新特性：默认的字符集格式为 utf-8 ，不需要修改字符集。

Doc下 启动数据库

习惯将 -p 放到最后 前面 -u -P -h 这三个参数谁在前 谁在后无所谓
-P（大写P） : 这里是端口号 如果 安装两个版本的数据库 可以根据端口号来区分这两个数据库 输入对应的端口号 启动对应的端口
-h ： 数据库是基于 TCP/IP 协议的 如果不想访问别的 就输入对应的ip地址
-u : 账号
-p ：回车 输入DB的密码

基本的select语句（chepter_03)

运算符

算术运算符

1. 算术运算符： + - * / 或者div % 或者mod

   SELECT 100, 100 + 0, 100 - 0, 100 + 50, 100 + 50 * 30, 100 + 35.5, 100 - 35.5
   FROM DUAL;

2. 在SQL中，+没有连接的作用，就表示加法运算。此时，会将字符串转换为数值（隐式转换）

SELECT 100 + ‘1’ # 在Java语言中，结果是：1001。
FROM DUAL;

SELECT 100 + ‘a’ #此时将’a’看做0处理
FROM DUAL;

1. null值参与运算，结果为null

SELECT 100 + NULL
FROM DUAL;

1. 分母如果为0，则结果为null

SELECT 100, 100 *1, 100 1.0, 100 / 1.0, 100 / 2,
100 + 2 5 / 2,100 / 3, 100 DIV 0
FROM DUAL;

1. 取模运算： % mod

运算结果的正负与被模数有关
SELECT 12 % 3,12 % 5, 12 MOD -5,-12 % 5,-12 % -5
FROM DUAL;

1. 结论：

• 一个整数类型的值对整数进行加法和减法操作，结果还是一个整数；
• 一个整数类型的值对浮点数进行加法和减法操作，结果是一个浮点数；
• 加法和减法的优先级相同，进行先加后减操作与进行先减后加操作的结果是一样的；
• 在Java中，+的左右两边如果有字符串，那么表示字符串的拼接。但是在MySQL中+只表示数 值相加。如果遇到非数值类型，先尝试转成数值，如果转失败，就按0计算。（补充：MySQL 中字符串拼接要使用字符串函数CONCAT()实现）

比较运算符

• 最小值运算符
  • 语法格式为：LEAST(值1，值2，…，值n)。

其中，“值n”表示参数列表中有n个值。在有 两个或多个参数的情况下，返回最小值。

• 总结：当参数是整数或者浮点数时，LEAST将返回其中最小的值；

     当参数为字符串时，返回字 母表中顺序最靠前的字符；<br />            当比较值列表中有NULL时，不能判断大小，返回值为NULL。 

• 最大值运算符

  • 语法格式为：GREATEST(值1，值2，…，值n)。

  • 其中，n表示参数列表中有n个值。当有 两个或多个参数时，返回值为最大值。假如任意一个自变量为NULL，则GREATEST()的返回值为NULL。

  • 总结：

    • 当参数中是整数或者浮点数时，GREATEST将返回其中最大的值；
    • 当参数为字符串时， 返回字母表中顺序最靠后的字符；
    • 当比较值列表中有NULL时，不能判断大小，返回值为

• BETWEEN AND运算符

• BETWEEN运算符使用的格式通常为：

  • SELECT D FROM TABLE WHERE C BETWEEN A AND B，
  • 此时，当C大于或等于A，并且C小于或等于B时，结果为1，否则结果为0。

• . IN运算符

  • IN运算符用于判断给定的值是否是IN列表中的一个值，如果是则返回1，否则返回0。如果给 定的值为NULL，或者IN列表中存在NULL，则结果为NULL。

第04章_运算符.pdf

第05章_排序与分页（day02）

1. 排序

如果没有使用排序操作，默认情况下查询返回的数据是按照添加数据的顺序显示的。

SELECT * FROM employees;

基本使用

• 使用 ORDER BY 对查询到的数据进行排序操作。

升序：ASC (ascend)
降序：DESC (descend)
如果在ORDER BY 后没有显式指名排序的方式的话，则默认按照升序排列

• 练习：按照salary从高到低的顺序显示员工信息

  SELECT employee_id,last_name,salary
  FROM employees
  ORDER BY salary DESC;

• 练习：按照salary从低到高的顺序显示员工信息 ```sql SELECT employee_id,last_name,salary FROM employees ORDER BY salary ASC;

SELECT employee_id,last_name,salary FROM employees ORDER BY salary;

<a name="jcoer"></a>
### 列的别名
- **我们可以使用列的别名，进行排序**
```sql
SELECT employee_id,salary,salary * 12 annual_sal
FROM employees
ORDER BY annual_sal;

• 列的别名只能在 ORDER BY 中使用，不能在WHERE中使用。

  如下操作报错！
  SELECT employee_id,salary,salary * 12 annual_sal
  FROM employees
  WHERE annual_sal > 81600;

  
  

• 强调格式：WHERE 需要声明在FROM后，ORDER BY之前。

  SELECT employee_id,salary
  FROM employees
  WHERE department_id IN (50,60,70)
  ORDER BY department_id DESC;

二级排序

• 练习：显示员工信息，按照department_id的降序排列，salary的升序排列

  SELECT employee_id,salary,department_id
  FROM employees
  ORDER BY department_id DESC,salary ASC;

  分页

  mysql使用limit实现数据的分页显示

• 需求1：每页显示20条记录，此时显示第1页

  SELECT employee_id,last_name
  FROM employees
  LIMIT 0,20;

• 需求2：每页显示20条记录，此时显示第2页

SELECT employee_id,last_name
FROM employees
LIMIT 20,20;

• 需求3：每页显示20条记录，此时显示第3页

SELECT employee_id,last_name
FROM employees
LIMIT 40,20;

• 需求：每页显示pageSize条记录，此时显示第pageNo页：
• 公式：LIMIT (pageNo-1) * pageSize,pageSize;

WHERE … ORDER BY …LIMIT 声明顺序如下：

• LIMIT的格式： 严格来说：LIMIT 位置偏移量,条目数

• 结构”LIMIT 0,条目数” 等价于 “LIMIT 条目数”

  SELECT employee_id,last_name,salary
  FROM employees
  WHERE salary > 6000
  ORDER BY salary DESC
  #limit 0,10;
  LIMIT 10;

• 练习：表里有107条数据，我们只想要显示第 32、33 条数据怎么办呢？

  SELECT employee_id,last_name
  FROM employees
  LIMIT 31,2;

MySQL8.0新特性：LIMIT … OFFSET …

练习：表里有107条数据，我们只想要显示第 32、33 条数据怎么办呢？

SELECT employee_id,last_name
FROM employees
LIMIT 2 OFFSET 31;

练习：查询员工表中工资最高的员工信息

SELECT employee_id,last_name,salary
FROM employees
ORDER BY salary DESC
#limit 0,1
LIMIT 1;

了解内容

• LIMIT 可以使用在MySQL、PGSQL、MariaDB、SQLite 等数据库中使用，表示分页。

不能使用在SQL Server、DB2、Oracle！

第06章_多表查询

熟悉常见的几个表

DESC employees; 员工表

DESC departments; 部门表

DESC locations; 位置表

1. 查询员工名为’Abel’的人在哪个城市工作？ ```sql SELECT * FROM employees WHERE last_name = ‘Abel’;

SELECT * FROM departments WHERE department_id = 80;

SELECT * FROM locations WHERE location_id = 2500;

<a name="b3S6j"></a>
## 出现笛卡尔积的错误
- 错误的原因：缺少了多表的连接条件
```sql
#错误的实现方式：每个员工都与每个部门匹配了一遍。
SELECT employee_id,department_name
FROM employees,departments;  #查询出2889条记录
#错误的方式
SELECT employee_id,department_name
FROM employees CROSS JOIN departments;#查询出2889条记录
SELECT *
FROM employees;  #107条记录
SELECT 2889 / 107
FROM DUAL;
SELECT *
FROM departments; # 27条记录

多表查询的正确方式

3. 多表查询的正确方式：需要有连接条件

SELECT employee_id,department_name
FROM employees,departments
#两个表的连接条件
WHERE employees.`department_id` = departments.department_id;

4. 如果查询语句中出现了多个表中都存在的字段，则必须指明此字段所在的表。

SELECT employees.employee_id,departments.department_name,employees.department_id
FROM employees,departments
WHERE employees.`department_id` = departments.department_id;

建议：从sql优化的角度，建议多表查询时，每个字段前都指明其所在的表。

5.可以给表起别名，在SELECT和WHERE中使用表的别名。

SELECT emp.employee_id,dept.department_name,emp.department_id
FROM employees emp,departments dept
WHERE emp.`department_id` = dept.department_id;

如果给表起了别名，一旦在SELECT或WHERE中使用表名的话，则必须使用表的别名，而不能再使用表的原名。

#如下的操作是错误的：
SELECT emp.employee_id,departments.department_name,emp.department_id
FROM employees emp,departments dept
WHERE emp.`department_id` = departments.department_id;

1. 结论：如果有n个表实现多表的查询，则需要至少n-1个连接条件

   #练习：查询员工的employee_id,last_name,department_name,city
   SELECT e.employee_id,e.last_name,d.department_name,l.city,e.department_id,l.location_id
   FROM employees e,departments d,locations l
   WHERE e.`department_id` = d.`department_id`
   AND d.`location_id` = l.`location_id`;

多表查询的分类

• 角度1：等值连接 vs 非等值连接

• 角度2：自连接 vs 非自连接

• 角度3：内连接 vs 外连接

1. 等值连接 vs 非等值连接

• 非等值连接的例子： ```sql SELECT * FROM job_grades;

SELECT e.last_name,e.salary,j.grade_level FROM employees e,job_grades j

where e.salary between j.lowest_sal and j.highest_sal;

WHERE e.salary >= j.lowest_sal AND e.salary <= j.highest_sal;

2. **自连接  vs  非自连接**
SELECT * FROM employees;
- **自连接的例子：**
   - 练习：查询员工id,员工姓名及其管理者的id和姓名
```sql
SELECT emp.employee_id,emp.last_name,mgr.employee_id,mgr.last_name
FROM employees emp ,employees mgr
WHERE emp.`manager_id` = mgr.`employee_id`;

1. 内连接 vs 外连接

• 内连接：合并具有同一列的两个以上的表的行, 结果集中不包含一个表与另一个表不匹配的行（将两个不同的表，相互联系的字段，求出相同的数据）

  SELECT employee_id,department_name
  FROM employees e,departments d
  WHERE e.`department_id` = d.department_id;  #只有106条记录

• 外连接：合并具有同一列的两个以上的表的行, 结果集中除了包含一个表与另一个表匹配的行之外，还查询到了左表 或 右表中不匹配的行。

• 外连接的分类：左外连接、右外连接、满外连接

• 左外连接：两个表在连接过程中除了返回满足连接条件的行以外还返回左表中不满足条件的行，这种连接称为左外连接。

• 右外连接：两个表在连接过程中除了返回满足连接条件的行以外还返回右表中不满足条件的行，这种连接称为右外连接。

  SQL92语法和SQL99语法的区别：

• 练习：查询所有的员工的last_name,department_name信息

  SELECT employee_id,department_name
  FROM employees e,departments d
  WHERE e.`department_id` = d.department_id;   # 需要使用左外连接

• SQL92语法实现内连接：见上，略

• SQL92语法实现外连接：使用 + —————MySQL不支持SQL92语法中外连接的写法！

  #不支持：
  SELECT employee_id,department_name
  FROM employees e,departments d
  WHERE e.`department_id` = d.department_id(+);

• SQL99语法中使用 JOIN …ON 的方式实现多表的查询。这种方式也能解决外连接的问题。MySQL是支持此种方式的。

• SQL99语法如何实现多表的查询。

  • SQL99语法实现内连接： ``sql SELECT last_name,department_name FROM employees e INNER JOIN departments d ON e.department_id= d.department_id`;

SELECT last_name,department_name,city FROM employees e JOIN departments d ON e.department_id = d.department_id JOIN locations l ON d.location_id = l.location_id;

   - **SQL99语法实现外连接**：
      - 练习：查询所有的员工的last_name,department_name信息 
      - 左外连接：
```sql
SELECT last_name,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`;

• 右外连接：

  SELECT last_name,department_name
  FROM employees e RIGHT OUTER JOIN departments d
  ON e.`department_id` = d.`department_id`;

• 满外连接：mysql不支持FULL OUTER JOIN

  SELECT last_name,department_name
  FROM employees e FULL OUTER JOIN departments d
  ON e.`department_id` = d.`department_id`;

UNION 与 UNION ALL

1. UNION 和 UNION ALL的使用

• UNION：会执行去重操作

• UNION ALL:不会执行去重操作

• 结论：如果明确知道合并数据后的结果数据不存在重复数据，或者不需要去除重复的数据，则尽量使用UNION ALL语句，以提高数据查询的效率。

1. 7种JOIN的实现：

• 中图：内连接

  SELECT employee_id,department_name
  FROM employees e JOIN departments d
  ON e.`department_id` = d.`department_id`;

• 左上图：左外连接

  SELECT employee_id,department_name
  FROM employees e LEFT JOIN departments d
  ON e.`department_id` = d.`department_id`;

• 右上图：右外连接

  SELECT employee_id,department_name
  FROM employees e RIGHT JOIN departments d
  ON e.`department_id` = d.`department_id`;

• 左中图：

  SELECT employee_id,department_name
  FROM employees e LEFT JOIN departments d
  ON e.`department_id` = d.`department_id`
  WHERE d.`department_id` IS NULL;

• 右中图：

  SELECT employee_id,department_name
  FROM employees e RIGHT JOIN departments d
  ON e.`department_id` = d.`department_id`
  WHERE e.`department_id` IS NULL;

• 左下图：满外连接 ```sql

  方式1：左上图 UNION ALL 右中图

SELECT employee_id,department_name FROM employees e LEFT JOIN departments d ON e.department_id = d.department_id UNION ALL SELECT employee_id,department_name FROM employees e RIGHT JOIN departments d ON e.department_id = d.department_id WHERE e.department_id IS NULL;

方式2：左中图 UNION ALL 右上图

SELECT employee_id,department_name FROM employees e LEFT JOIN departments d ON e.department_id = d.department_id WHERE d.department_id IS NULL UNION ALL SELECT employee_id,department_name FROM employees e RIGHT JOIN departments d ON e.department_id = d.department_id;

- **右下图：左中图  UNION ALL 右中图**
```sql
SELECT employee_id,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL
UNION ALL
SELECT employee_id,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`department_id` IS NULL;

SQL99语法的新特性1:自然连接

SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
ON e.`department_id` = d.`department_id`
AND e.`manager_id` = d.`manager_id`;

1. NATURAL JOIN : 它会帮你自动查询两张连接表中所有相同的字段，然后进行等值连接。

   SELECT employee_id,last_name,department_name
   FROM employees e NATURAL JOIN departments d;

SQL99语法的新特性2:USING

SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
ON e.department_id = d.department_id;
SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
USING (department_id);

#拓展：
SELECT last_name,job_title,department_name 
FROM employees INNER JOIN departments INNER JOIN jobs 
ON employees.department_id = departments.department_id 
AND employees.job_id = jobs.job_id;

07章_单行函数

数值函数

1. 基本的操作

   SELECT ABS(-123),ABS(32),SIGN(-23),SIGN(43),PI(),CEIL(32.32),CEILING(-43.23),FLOOR(32.32),
   FLOOR(-43.23),MOD(12,5),12 MOD 5,12 % 5
   FROM DUAL;

2. 取随机数

SELECT RAND(),RAND(),RAND(10),RAND(10),RAND(-1),RAND(-1)
FROM DUAL;

四舍五入，截断操作
SELECT ROUND(123.556),ROUND(123.456,0),ROUND(123.456,1),ROUND(123.456,2),
ROUND(123.456,-1),ROUND(153.456,-2)
FROM DUAL;

SELECT TRUNCATE(123.456,0),TRUNCATE(123.496,1),TRUNCATE(129.45,-1)
FROM DUAL;

单行函数可以嵌套
SELECT TRUNCATE(ROUND(123.456,2),0)
FROM DUAL;

角度与弧度的互换

SELECT RADIANS(30),RADIANS(45),RADIANS(60),RADIANS(90),
DEGREES(2*PI()),DEGREES(RADIANS(60))
FROM DUAL;

三角函数
SELECT SIN(RADIANS(30)),DEGREES(ASIN(1)),TAN(RADIANS(45)),DEGREES(ATAN(1))
FROM DUAL;

指数和对数
SELECT POW(2,5),POWER(2,4),EXP(2)
FROM DUAL;

SELECT LN(EXP(2)),LOG(EXP(2)),LOG10(10),LOG2(4)
FROM DUAL;

进制间的转换
SELECT BIN(10),HEX(10),OCT(10),CONV(10,10,8)
FROM DUAL;

2. 字符串函数

SELECT ASCII(‘Abcdfsf’),CHAR_LENGTH(‘hello’),CHAR_LENGTH(‘我们’),
LENGTH(‘hello’),LENGTH(‘我们’)
FROM DUAL;

xxx worked for yyy
SELECT CONCAT(emp.last_name,’ worked for ‘,mgr.last_name) “details”
FROM employees emp JOIN employees mgr
WHERE emp.manager_id = mgr.employee_id;

SELECT CONCAT_WS(‘-‘,’hello’,’world’,’hello’,’beijing’)
FROM DUAL;
#字符串的索引是从1开始的！
SELECT INSERT(‘helloworld’,2,3,’aaaaa’),REPLACE(‘hello’,’lol’,’mmm’)
FROM DUAL;

SELECT UPPER(‘HelLo’),LOWER(‘HelLo’)
FROM DUAL;

SELECT last_name,salary
FROM employees
WHERE LOWER(last_name) = ‘King’;

SELECT LEFT(‘hello’,2),RIGHT(‘hello’,3),RIGHT(‘hello’,13)
FROM DUAL;

LPAD:实现右对齐效果
# RPAD:实现左对齐效果
SELECT employee_id,last_name,LPAD(salary,10,’ ‘)
FROM employees;

SELECT CONCAT(‘—-‘,LTRIM(‘ h el lo ‘),’*‘),
TRIM(‘oo’ FROM ‘ooheollo’)
FROM DUAL;

SELECT REPEAT(‘hello’,4),LENGTH(SPACE(5)),STRCMP(‘abc’,’abe’)
FROM DUAL;

SELECT SUBSTR(‘hello’,2,2),LOCATE(‘lll’,’hello’)
FROM DUAL;

SELECT ELT(2,’a’,’b’,’c’,’d’),FIELD(‘mm’,’gg’,’jj’,’mm’,’dd’,’mm’),
FIND_IN_SET(‘mm’,’gg,mm,jj,dd,mm,gg’)
FROM DUAL;

SELECT employee_id,NULLIF(LENGTH(first_name),LENGTH(last_name)) “compare”
FROM employees;

3. 日期和时间函数

3.1 获取日期、时间
SELECT CURDATE(),CURRENT_DATE(),CURTIME(),NOW(),SYSDATE(),
UTC_DATE(),UTC_TIME()
FROM DUAL;

SELECT CURDATE(),CURDATE() + 0,CURTIME() + 0,NOW() + 0
FROM DUAL;

3.2 日期与时间戳的转换
SELECT UNIX_TIMESTAMP(),UNIX_TIMESTAMP(‘2021-10-01 12:12:32’),
FROM_UNIXTIME(1635173853),FROM_UNIXTIME(1633061552)
FROM DUAL;

3.3 获取月份、星期、星期数、天数等函数
SELECT YEAR(CURDATE()),MONTH(CURDATE()),DAY(CURDATE()),
HOUR(CURTIME()),MINUTE(NOW()),SECOND(SYSDATE())
FROM DUAL;

SELECT MONTHNAME(‘2021-10-26’),DAYNAME(‘2021-10-26’),WEEKDAY(‘2021-10-26’),
QUARTER(CURDATE()),WEEK(CURDATE()),DAYOFYEAR(NOW()),
DAYOFMONTH(NOW()),DAYOFWEEK(NOW())
FROM DUAL;

3.4 日期的操作函数

SELECT EXTRACT(SECOND FROM NOW()),EXTRACT(DAY FROM NOW()),
EXTRACT(HOUR_MINUTE FROM NOW()),EXTRACT(QUARTER FROM ‘2021-05-12’)
FROM DUAL;

3.5 时间和秒钟转换的函数
SELECT TIME_TO_SEC(CURTIME()),
SEC_TO_TIME(83355)
FROM DUAL;

3.6 计算日期和时间的函数

SELECT NOW(),DATE_ADD(NOW(),INTERVAL 1 YEAR),
DATE_ADD(NOW(),INTERVAL -1 YEAR),
DATE_SUB(NOW(),INTERVAL 1 YEAR)
FROM DUAL;

SELECT DATE_ADD(NOW(), INTERVAL 1 DAY) AS col1,DATE_ADD(‘2021-10-21 23:32:12’,INTERVAL 1 SECOND) AS col2,
ADDDATE(‘2021-10-21 23:32:12’,INTERVAL 1 SECOND) AS col3,
DATE_ADD(‘2021-10-21 23:32:12’,INTERVAL ‘1_1’ MINUTE_SECOND) AS col4,
DATE_ADD(NOW(), INTERVAL -1 YEAR) AS col5, #可以是负数
DATE_ADD(NOW(), INTERVAL ‘1_1’ YEAR_MONTH) AS col6 #需要单引号
FROM DUAL;

SELECT ADDTIME(NOW(),20),SUBTIME(NOW(),30),SUBTIME(NOW(),’1:1:3’),DATEDIFF(NOW(),’2021-10-01’),
TIMEDIFF(NOW(),’2021-10-25 22:10:10’),FROM_DAYS(366),TO_DAYS(‘0000-12-25’),
LAST_DAY(NOW()),MAKEDATE(YEAR(NOW()),32),MAKETIME(10,21,23),PERIOD_ADD(20200101010101,10)
FROM DUAL;

3.7 日期的格式化与解析
# 格式化：日期 —-> 字符串
# 解析： 字符串 ——> 日期

此时我们谈的是日期的显式格式化和解析

之前，我们接触过隐式的格式化或解析
SELECT *
FROM employees
WHERE hire_date = ‘1993-01-13’;

格式化：
SELECT DATE_FORMAT(CURDATE(),’%Y-%M-%D’),
DATE_FORMAT(NOW(),’%Y-%m-%d’),TIME_FORMAT(CURTIME(),’%h:%i:%S’),
DATE_FORMAT(NOW(),’%Y-%M-%D %h:%i:%S %W %w %T %r’)
FROM DUAL;

解析：格式化的逆过程
SELECT STR_TO_DATE(‘2021-October-25th 11:37:30 Monday 1’,’%Y-%M-%D %h:%i:%S %W %w’)
FROM DUAL;

SELECT GET_FORMAT(DATE,’USA’)
FROM DUAL;

SELECT DATE_FORMAT(CURDATE(),GET_FORMAT(DATE,’USA’))
FROM DUAL;

4.流程控制函数
#4.1 IF(VALUE,VALUE1,VALUE2)

SELECT last_name,salary,IF(salary >= 6000,’高工资’,’低工资’) “details”
FROM employees;

SELECT last_name,commission_pct,IF(commission_pct IS NOT NULL,commission_pct,0) “details”,
salary 12 (1 + IF(commission_pct IS NOT NULL,commission_pct,0)) “annual_sal”
FROM employees;

4.2 IFNULL(VALUE1,VALUE2):看做是IF(VALUE,VALUE1,VALUE2)的特殊情况
SELECT last_name,commission_pct,IFNULL(commission_pct,0) “details”
FROM employees;

4.3 CASE WHEN … THEN …WHEN … THEN … ELSE … END
# 类似于java的if … else if … else if … else
SELECT last_name,salary,CASE WHEN salary >= 15000 THEN ‘白骨精’
WHEN salary >= 10000 THEN ‘潜力股’
WHEN salary >= 8000 THEN ‘小屌丝’
ELSE ‘草根’ END “details”,department_id
FROM employees;

SELECT last_name,salary,CASE WHEN salary >= 15000 THEN ‘白骨精’
WHEN salary >= 10000 THEN ‘潜力股’
WHEN salary >= 8000 THEN ‘小屌丝’
END “details”
FROM employees;

4.4 CASE … WHEN … THEN … WHEN … THEN … ELSE … END
# 类似于java的swich … case…
/*

练习1
查询部门号为 10,20, 30 的员工信息,
若部门号为 10, 则打印其工资的 1.1 倍,
20 号部门, 则打印其工资的 1.2 倍,
30 号部门,打印其工资的 1.3 倍数,
其他部门,打印其工资的 1.4 倍数

/
SELECT employee_id,last_name,department_id,salary,CASE department_id WHEN 10 THEN salary 1.1
WHEN 20 THEN salary 1.2
WHEN 30 THEN salary 1.3
ELSE salary * 1.4 END “details”
FROM employees;

/*

练习2
查询部门号为 10,20, 30 的员工信息,
若部门号为 10, 则打印其工资的 1.1 倍,
20 号部门, 则打印其工资的 1.2 倍,
30 号部门打印其工资的 1.3 倍数

/
SELECT employee_id,last_name,department_id,salary,CASE department_id WHEN 10 THEN salary 1.1
WHEN 20 THEN salary 1.2
WHEN 30 THEN salary 1.3
END “details”
FROM employees
WHERE department_id IN (10,20,30);

5. 加密与解密的函数
# PASSWORD()在mysql8.0中弃用。
SELECT MD5(‘mysql’),SHA(‘mysql’),MD5(MD5(‘mysql’))
FROM DUAL;

ENCODE()\DECODE() 在mysql8.0中弃用。
/
SELECT ENCODE(‘atguigu’,’mysql’),DECODE(ENCODE(‘atguigu’,’mysql’),’mysql’)
FROM DUAL;
/

6. MySQL信息函数

SELECT VERSION(),CONNECTION_ID(),DATABASE(),SCHEMA(),
USER(),CURRENT_USER(),CHARSET(‘尚硅谷’),COLLATION(‘尚硅谷’)
FROM DUAL;

7. 其他函数
#如果n的值小于或者等于0，则只保留整数部分
SELECT FORMAT(123.125,2),FORMAT(123.125,0),FORMAT(123.125,-2)
FROM DUAL;

SELECT CONV(16, 10, 2), CONV(8888,10,16), CONV(NULL, 10, 2)
FROM DUAL;
#以“192.168.1.100”为例，计算方式为192乘以256的3次方，加上168乘以256的2次方，加上1乘以256，再加上100。
SELECT INET_ATON(‘192.168.1.100’),INET_NTOA(3232235876)
FROM DUAL;

BENCHMARK()用于测试表达式的执行效率
SELECT BENCHMARK(100000,MD5(‘mysql’))
FROM DUAL;
# CONVERT():可以实现字符集的转换
SELECT CHARSET(‘atguigu’),CHARSET(CONVERT(‘atguigu’ USING ‘gbk’))
FROM DUAL;

第08章_聚合函数

1. 常见的几个聚合函数
   1.1 AVG / SUM ：只适用于数值类型的字段（或变量）

SELECT AVG(salary),SUM(salary),AVG(salary) * 107
FROM employees;
如下的操作没有意义
SELECT SUM(last_name),AVG(last_name),SUM(hire_date)
FROM employees;

1.2 MAX / MIN :适用于数值类型、字符串类型、日期时间类型的字段（或变量）

SELECT MAX(salary),MIN(salary)
FROM employees;

SELECT MAX(last_name),MIN(last_name),MAX(hire_date),MIN(hire_date)
FROM employees;

1.3 COUNT：
# ① 作用：计算指定字段在查询结构中出现的个数（不包含NULL值的）

SELECT COUNT(employee_id),COUNT(salary),COUNT(2 salary),COUNT(1),COUNT(2),COUNT()
FROM employees ;

SELECT *
FROM employees;

如果计算表中有多少条记录，如何实现？
#方式1：COUNT(*)
#方式2：COUNT(1)
#方式3：COUNT(具体字段) : 不一定对！

② 注意：计算指定字段出现的个数时，是不计算NULL值的。
SELECT COUNT(commission_pct)
FROM employees;

SELECT commission_pct
FROM employees
WHERE commission_pct IS NOT NULL;

③ 公式：AVG = SUM / COUNT
SELECT AVG(salary),SUM(salary)/COUNT(salary),
AVG(commission_pct),SUM(commission_pct)/COUNT(commission_pct),
SUM(commission_pct) / 107
FROM employees;

需求：查询公司中平均奖金率
#错误的！
SELECT AVG(commission_pct)
FROM employees;

正确的：
SELECT SUM(commission_pct) / COUNT(IFNULL(commission_pct,0)),
AVG(IFNULL(commission_pct,0))
FROM employees;

如何需要统计表中的记录数，使用COUNT()、COUNT(1)、COUNT(具体字段) 哪个效率更高呢？
# 如果使用的是MyISAM 存储引擎，则三者效率相同，都是O(1)
# 如果使用的是InnoDB 存储引擎，则三者效率：COUNT() = COUNT(1)> COUNT(字段)

其他：方差、标准差、中位数

2. GROUP BY 的使用

需求：查询各个部门的平均工资，最高工资
SELECT department_id,AVG(salary),SUM(salary)
FROM employees
GROUP BY department_id

需求：查询各个job_id的平均工资
SELECT job_id,AVG(salary)
FROM employees
GROUP BY job_id;

需求：查询各个department_id,job_id的平均工资
#方式1：
SELECT department_id,job_id,AVG(salary)
FROM employees
GROUP BY department_id,job_id;
#方式2：
SELECT job_id,department_id,AVG(salary)
FROM employees
GROUP BY job_id,department_id;

错误的！
SELECT department_id,job_id,AVG(salary)
FROM employees
GROUP BY department_id;

结论1：SELECT中出现的非组函数的字段必须声明在GROUP BY 中。
# 反之，GROUP BY中声明的字段可以不出现在SELECT中。

结论2：GROUP BY 声明在FROM后面、WHERE后面，ORDER BY 前面、LIMIT前面

结论3：MySQL中GROUP BY中使用WITH ROLLUP

SELECT department_id,AVG(salary)
FROM employees
GROUP BY department_id WITH ROLLUP;

需求：查询各个部门的平均工资，按照平均工资升序排列
SELECT department_id,AVG(salary) avg_sal
FROM employees
GROUP BY department_id
ORDER BY avg_sal ASC;

说明：当使用ROLLUP时，不能同时使用ORDER BY子句进行结果排序，即ROLLUP和ORDER BY是互相排斥的。
#错误的：
SELECT department_id,AVG(salary) avg_sal
FROM employees
GROUP BY department_id WITH ROLLUP
ORDER BY avg_sal ASC;

3. HAVING的使用 (作用：用来过滤数据的)
#练习：查询各个部门中最高工资比10000高的部门信息
#错误的写法：
SELECT department_id,MAX(salary)
FROM employees
WHERE MAX(salary) > 10000
GROUP BY department_id;

要求1：如果过滤条件中使用了聚合函数，则必须使用HAVING来替换WHERE。否则，报错。
#要求2：HAVING 必须声明在 GROUP BY 的后面。

正确的写法：
SELECT department_id,MAX(salary)
FROM employees
GROUP BY department_id
HAVING MAX(salary) > 10000;

要求3：开发中，我们使用HAVING的前提是SQL中使用了GROUP BY。

练习：查询部门id为10,20,30,40这4个部门中最高工资比10000高的部门信息
#方式1：推荐，执行效率高于方式2.
SELECT department_id,MAX(salary)
FROM employees
WHERE department_id IN (10,20,30,40)
GROUP BY department_id
HAVING MAX(salary) > 10000;

方式2：
SELECT department_id,MAX(salary)
FROM employees
GROUP BY department_id
HAVING MAX(salary) > 10000 AND department_id IN (10,20,30,40);

结论：当过滤条件中有聚合函数时，则此过滤条件必须声明在HAVING中。
# 当过滤条件中没有聚合函数时，则此过滤条件声明在WHERE中或HAVING中都可以。但是，建议大家声明在WHERE中。

/
WHERE 与 HAVING 的对比
1. 从适用范围上来讲，HAVING的适用范围更广。
2. 如果过滤条件中没有聚合函数：这种情况下，WHERE的执行效率要高于HAVING
/

4. SQL底层执行原理
#4.1 SELECT 语句的完整结构
/*

sql92语法：
SELECT ….,….,….(存在聚合函数)
FROM …,….,….
WHERE 多表的连接条件 AND 不包含聚合函数的过滤条件
GROUP BY …,….
HAVING 包含聚合函数的过滤条件
ORDER BY ….,…(ASC / DESC )
LIMIT …,….

sql99语法：
SELECT ….,….,….(存在聚合函数)
FROM … (LEFT / RIGHT)JOIN ….ON 多表的连接条件
(LEFT / RIGHT)JOIN … ON ….
WHERE 不包含聚合函数的过滤条件
GROUP BY …,….
HAVING 包含聚合函数的过滤条件
ORDER BY ….,…(ASC / DESC )
LIMIT …,….

*/

4.2 SQL语句的执行过程：
#FROM …,…-> ON -> (LEFT/RIGNT JOIN) -> WHERE -> GROUP BY -> HAVING -> SELECT -> DISTINCT ->
# ORDER BY -> LIMIT

13 约束

重点：非空约束 主键约束 唯一约束 默认约束
开发过程中 一般不会去删除约束 知道怎么添加就行

主键约束（重点）

• 主键约束
  • 关键字：primary key

1. 在CREATE TABLE时添加约束

1.1 一个表中最多只能有一个主键约束

#错误：Multiple primary key defined
CREATE TABLE test3(
id INT PRIMARY KEY, #列级约束
last_name VARCHAR(15) PRIMARY KEY,
salary DECIMAL(10,2),
email VARCHAR(25)
);

1.2 主键约束特征：非空且唯一，用于唯一的标识表中的一条记录

CREATE TABLE test4(
id INT PRIMARY KEY, #列级约束
last_name VARCHAR(15),
salary DECIMAL(10,2),
email VARCHAR(25)
);

1.3   MySQL的主键名总是PRIMARY，就算自己命名了主键约束名也没用。

CREATE TABLE test5(
id INT , 
last_name VARCHAR(15),
salary DECIMAL(10,2),
email VARCHAR(25),
#表级约束
CONSTRAINT pk_test5_id PRIMARY KEY(id)  #没有必要起名字。
);
SELECT * FROM information_schema.table_constraints 
WHERE table_name = 'test5';
INSERT INTO test4(id,last_name,salary,email)
VALUES(1,'Tom',4500,'tom@126.com');
#错误：Duplicate entry '1' for key 'test4.PRIMARY'
INSERT INTO test4(id,last_name,salary,email)
VALUES(1,'Tom',4500,'tom@126.com');
#错误：Column 'id' cannot be null
INSERT INTO test4(id,last_name,salary,email)
VALUES(NULL,'Tom',4500,'tom@126.com');
SELECT * FROM test4;
CREATE TABLE user1(
id INT,
NAME VARCHAR(15),
PASSWORD VARCHAR(25),
PRIMARY KEY (NAME,PASSWORD)
);

 1.4    如果是多列组合的复合主键约束，那么这些列都不允许为空值，并且组合的值不允许重复。

INSERT INTO user1
VALUES(1,'Tom','abc');
INSERT INTO user1
VALUES(1,'Tom1','abc');
#错误：Column 'name' cannot be null
INSERT INTO user1
VALUES(1,NULL,'abc');
SELECT * FROM user1;
#5.2 在ALTER TABLE时添加约束
CREATE TABLE test6(
id INT ,
last_name VARCHAR(15),
salary DECIMAL(10,2),
email VARCHAR(25)
);
DESC test6;
ALTER TABLE test6
ADD PRIMARY KEY (id);

1.5   如何删除主键约束 (在实际开发中，不会去删除表中的主键约束！)

ALTER TABLE test6
DROP PRIMARY KEY;

自增长列:AUTO_INCREMENT

1. 6.1 在CREATE TABLE时添加

   CREATE TABLE test7(
   id INT PRIMARY KEY AUTO_INCREMENT,
   last_name VARCHAR(15) 
   );

2. 开发中，一旦主键作用的字段上声明有AUTO_INCREMENT，则我们在添加数据时，就不要给主键对应的字段去赋值了。 ```sql INSERT INTO test7(last_name) VALUES(‘Tom’);

SELECT * FROM test7;

3. 当我们向主键（含AUTO_INCREMENT）的字段上添加0 或 null时，实际上会自动的往上添加指定的字段的数值
```sql
INSERT INTO test7(id,last_name)
VALUES(0,'Tom');
INSERT INTO test7(id,last_name)
VALUES(NULL,'Tom');
INSERT INTO test7(id,last_name)
VALUES(10,'Tom');
INSERT INTO test7(id,last_name)
VALUES(-10,'Tom');
INSERT INTO test7(last_name)
VALUES('Tom');
SELECT * FROM test7;

1. 在ALTER TABLE 时添加 ```sql CREATE TABLE test8( id INT PRIMARY KEY , last_name VARCHAR(15) );

DESC test8;

ALTER TABLE test8 MODIFY id INT AUTO_INCREMENT;

5. 在ALTER TABLE 时删除
```sql
ALTER TABLE test8
MODIFY id INT ;

1. MySQL 8.0新特性—自增变量的持久化

• 在MySQL 5.7系统中，对于自增主键的分配规则，是由InnoDB数据字典 内部一个 计数器 来决定的，而该计数器只在 内存中维护 ，并不会持久化到磁盘中。

• 重启服务器步骤：

  • 在dos命令窗口下：

             - net stop mysql版本号                   停止服务器运行
             - net start  mysql版本号           运行服务器

    ```sql

    在MySQL 5.7中演示

    CREATE TABLE test9( id INT PRIMARY KEY AUTO_INCREMENT );

INSERT INTO test9 VALUES(0),(0),(0),(0);

SELECT * FROM test9;

DELETE FROM test9 WHERE id = 4;

INSERT INTO test9 VALUES(0);

DELETE FROM test9 WHERE id = 5;

重启服务器

SELECT * FROM test9;

INSERT INTO test9 VALUES(0);

在MySQL 8.0中演示

CREATE TABLE test9( id INT PRIMARY KEY AUTO_INCREMENT );

INSERT INTO test9 VALUES(0),(0),(0),(0);

SELECT * FROM test9;

DELETE FROM test9 WHERE id = 4;

INSERT INTO test9 VALUES(0);

DELETE FROM test9 WHERE id = 5;

重启服务器

SELECT * FROM test9;

INSERT INTO test9 VALUES(0); ```

外键约束

在 java 应用层面干预
1.面试题
怎么解决数据的完整性？
mysql数据库提供了外键约束这个功能，体现数据引用的完整性，但是在实际开发过程中在 应用层面 完成检查数据一致性的逻辑。也就是说，即使你不 用外键约束，也要想办法通过应用层面的附加逻辑，来实现外键约束的功能，确保数据的一致性。
因为外键与级联更新适用于 单 机低并发 ，不适合 分布式 、 高并发集群 ；级联更新是强阻塞，存在数据库 更新风暴 的风险；外键影响 数据库的 插入速度 。

check约束

说明：MySQL8.0支持，MySQL5.7不支持

default约束

chapter_14 视图

视图：

• 虚拟表，最主要是供查询使用的
• 存储select语句的

chapter_15 存储