MySQL8

Install

1、安装 docker-compose

# 下载文件
sudo curl -L "https://github.com/docker/compose/releases/download/1.29.2/docker-compose-$(uname -s)-$(uname -m)" -o /usr/local/bin/docker-compose
# 设置权限
sudo chmod +x /usr/local/bin/docker-compose
# 设置软链接
sudo ln -s /usr/local/bin/docker-compose /usr/bin/docker-compose
# 检测是否成功
docker-compose --version

2、构建文件

# 新建 data、conf、init 文件夹用于挂载
mkdir -r /usr/local/mysql8docker/{data,conf,init}
# 编写 docker-compose.yml 文件
cd /usr/local/mysql8docker
vim docker-compose.yml

version: "3"

services:
  db:
  image: mysql:latest
  container_name: mysql8
  restart: always
  command: --default-authentication-plugin=mysql_native_password
  environment:
    TZ: Asia/Shanghai
    MYSQL_ROOT_PASSWORD: root
    MYSQL_DATABASE: test
  ports:
    - 3306:3306
  volumes:
    - "./data:/var/lib/mysql"
    - "./conf:/etc/mysql/conf.d"
    - "./init:/docker-entrypoint-initdb.d"

3、运行容器

# 构建容器并后台运行
docker-compose up -d

# 查看容器运行情况
docker container ls

# 以可交互终端 bash 进入 mysql8 容器
docker exec -it mysql8 bash

# 登录 root 用于并指定密码为 root
mysql -uroot -proot

4、配置 mysql

# 显示 mysql 字符集
show variables like 'character_%';
# 显示 mysql 校对集
show variables like 'collation_%';
# 新建 reajason 用户，% 指定所有 ip 可连接，设置密码为 123456
create user reajason@'%' identified by '123456';
# 将所有权限授予 reajason
grant all privileges on *.* to 'reajason';
# 刷新权限
flush privileges;

# root 用户
grant all privileges on *.* to 'root'@'%' identified with mysql_native_password by 'root';

Select

1、简单使用

# 使用别名，表明和列名都可使用别名，使用 AS 关键字
SELECT
	employee_id,
	first_name ,
	last_name ,
	salary * 1.05 AS "totalSalary"
FROM
	employees;

# 表使用别名
SELECT
	e.employee_id ,
	e.email
FROM
	employees e;

# 去重 DISTINCT 关键字，可以对多列去重，多列全不同才去掉
SELECT
	DISTINCT e.department_id
FROM
	employees e;

# 空值参与运算也为空值（NULL）
SELECT
	e.employee_id ,
	e.salary * (1 + e.commission_pct) * 12 "年工资"
FROM
	employees e;

# 使用反引号，规避名称为关键字的情况
SELECT * FROM `order`;

# 显示表结构，显示表列名、数据类型、键约束等信息
DESCRIBE employees;
DESC employees;

2、使用 where

# 1.查询员工12个月的工资总和，并起别名为ANNUAL SALARY
SELECT
	e.employee_id ,
	e.salary * 12 AS "ANNUAL SALARY"
FROM
	employees e;
# 2.查询employees表中去除重复的job_id以后的数据
SELECT
	DISTINCT e.job_id
FROM
	employees e;
# 3.查询工资大于12000的员工姓名和工资
SELECT
	e.first_name ,
	e.last_name,
	e.salary
FROM
	employees e
WHERE
	e.salary > 12000;
# 4.查询员工号为176的员工的姓名和部门号
SELECT
	e.last_name,
	e.department_id
FROM
	employees e
WHERE
	e.employee_id = 176;
# 5.显示表 departments 的结构，并查询其中的全部数据
DESC departments;

SELECT * FROM departments;

3、运算符

NULL 参与运算结果为 NULL

1
2
3

# 运算符 +、-、*、/（DIV）、%（MOD）
SELECT 2 DIV 1 FROM DUAL;
SELECT -1 MOD 8 FROM DUAL;

1 2	`# 比较运算符 =、<=>（安全等于）、<>（不等于）、!=、>、<、>=、<= # = NULL 不会有结果，而 <=> NULL 则会输出为 NULL 的值`

# IS NULL（为 NULL）、IS NOT NULL（不为 NULL）、ISNULL()（为 NULL）
SELECT NULL IS NULL FROM DUAL;
# LEAST(value1,value2,...)--获取最小、GREATEST(value1,value2,...)--获取最大

# BETWEEN ... AND，包括边界值
# 1、查询工资 6000 到 8000 的的员工信息（不能调换两个值的位置）
SELECT * FROM employees e WHERE salary BETWEEN 6000 AND 8000;
SELECT * FROM employees e WHERE salary>=6000 AND salary<=8000;
# 2、查询工资低于 6000 以及 高于 8000 的员工信息
SELECT * FROM employees e WHERE salary NOT BETWEEN 6000 AND 8000;
SELECT * FROM employees e WHERE salary<6000 OR salary>8000;

# IN(set)、NOT IN(set)


# LIKE 模糊查询，% 匹配任意字符、_ 匹配单个字符

# REGEXP 正则表达式

1	`# 逻辑运算符 NOT、AND、OR、XOR`

1	`# 位运算符 &、\|、^、~、>>、<<`

# 1.选择工资不在5000到12000的员工的姓名和工资
SELECT e.last_name, e.salary FROM employees e WHERE e.salary NOT BETWEEN 5000 AND 12000;
# 2.选择在20或50号部门工作的员工姓名和部门号
SELECT e.last_name ,e.department_id FROM employees e WHERE e.department_id IN (20,50);
# 3.选择公司中没有管理者的员工姓名及job_id
SELECT e.last_name ,e.job_id FROM employees e WHERE e.manager_id IS NULL;
# 4.选择公司中有奖金的员工姓名，工资和奖金级别
SELECT e.last_name ,e.salary ,e.commission_pct FROM employees e WHERE e.commission_pct IS NOT NULL;
# 5.选择员工姓名的第三个字母是a的员工姓名
SELECT e.last_name FROM employees e WHERE e.last_name LIKE '__a%';
# 6.选择姓名中有字母a和k的员工姓名
SELECT e.last_name FROM employees e WHERE e.last_name LIKE '%a%k%'OR  e.last_name LIKE '%k%a%';
# 7.显示出表 employees 表中 first_name 以 'e'结尾的员工信息
SELECT * FROM employees e WHERE e.first_name LIKE '%e';
# 8.显示出表 employees 部门编号在 80-100 之间的姓名、工种
SELECT e.last_name,e.job_id FROM employees e WHERE department_id BETWEEN 80 AND 100;
# 9.显示出表 employees 的 manager_id 是 100,101,110 的员工姓名、工资、管理者id
SELECT e.last_name ,e.salary ,e.manager_id FROM employees e WHERE e.manager_id IN (100,101,110);

Order

不使用 ORDER BY 时默认排序为数据添加的顺序

使用 ORDER BY 不指定排序规则默认为升序(ASC)，指定降序使用 DESC

列别名可以在 ORDER BY 中使用不能在 WHERE 使用

可以指定多个排序列，从前往后进行排序

1
2
3

# 指定以 salary 排序
SELECT * FROM employees e WHERE e.salary > 5000 ORDER BY e.salary;
SELECT * FROM employees e WHERE e.salary > 5000 ORDER BY e.salary DESC;

# 查询员工的姓名和部门号和年薪，按年薪降序,按姓名升序显示
SELECT e.last_name,e.salary * 12 annual_sal FROM employees e ORDER BY e.salary DESC, e.last_name;

# 查询邮箱中包含 e 的员工信息，并先按邮箱的字节数降序，再按部门号升序
SELECT * FROM employees e WHERE e.email LIKE '%e%' ORDER BY LENGTH(e.email) DESC, e.department_id;

Limit

实现分页，LIMIT(offset, size)

# 每页 20 条记录，显示第 1、2 页
SELECT e.employee_id, e.last_name FROM employees e LIMIT 0, 20;
SELECT e.employee_id, e.last_name FROM employees e LIMIT 20, 20;

# 显示前 10 条数据
SELECT e.employee_id, e.last_name,e.salary FROM employees e 
ORDER BY e.salary DESC 
LIMIT 10;

# MySQL8 LIMIT ... OFFSET ...
# 获取 21、22 位置的数据
LIMIT 2 OFFSET 20;

1
2

# 选择工资不在 8000 到 17000 的员工的姓名和工资，按工资降序，显示第21到40位置的数据
SELECT e.last_name,e.salary FROM employees e WHERE e.salary NOT BETWEEN 8000 AND 17000 ORDER BY e.salary DESC LIMIT 20,20;

Join

# 当使用 , 连接两表产生笛卡尔积，使用 WHERE 条件可获取正确需要的数据
SELECT * FROM employees e,departments d;

SELECT * FROM employees e,departments d WHERE e.department_id = d.department_id;

# 自连接

# 内连接，... JOIN ... ON ...
# 左外连接，保留左表的所有行，右表没数据的使用 NULL 填充，... LEFT JOIN ...ON ...
# 右外连接，反之，... LEFT JOIN ...ON ...

UNION 返回两个查询的结果集并集，去除重复记录

UNION ALL 返回两个查询的结果集并集，不去重

1	`# 自然连接，... NATUAL JOIN ...，自动查询两张表相同字段进行等值连接`

# 1.显示所有员工的姓名，部门号和部门名称。
SELECT e.last_name ,e.department_id,d.department_name 
FROM employees e LEFT JOIN departments d 
ON e.department_id = d.department_id;

# 2.查询90号部门员工的job_id和90号部门的location_id
SELECT e.job_id, d.location_id FROM employees e , departments d 
WHERE e.department_id = d.department_id 
AND e.department_id = 90;

# 3.选择所有有奖金的员工的 last_name , department_name , location_id , city
SELECT e.last_name , d.department_name, l.location_id, l.city 
FROM employees e LEFT JOIN departments d ON  e.department_id = d.department_id 
LEFT JOIN locations l ON d.location_id = l.location_id
WHERE e.commission_pct IS NOT NULL;

# 4.选择city在Toronto工作的员工的 last_name , job_id , department_id , department_name
SELECT e.last_name , e.job_id, d.department_id, d.department_name 
FROM employees e ,departments d, locations l 
WHERE e.department_id = d.department_id 
AND d.location_id = l.location_id
AND l.city = 'Toronto';

# 5.查询员工所在的部门名称、部门地址、姓名、工作、工资，其中员工所在部门的部门名称为’Executive’
SELECT d.department_name,l.street_address , e.last_name, e.job_id, e.salary 
FROM employees e ,departments d, locations l 
WHERE d.location_id = l.location_id 
AND e.department_id = d.department_id 
AND d.department_name ='Executive';

# 6.选择指定员工的姓名，员工号，以及他的管理者的姓名和员工号，结果类似于下面的格式
SELECT e.last_name, e.employee_id AS "EMP#", m.last_name, m.employee_id AS "Mgr#"
FROM employees e LEFT JOIN employees m 
ON e.manager_id = m.employee_id;

# 7.查询哪些部门没有员工
SELECT d.* FROM departments d LEFT JOIN employees e 
ON d.department_id = e.department_id 
WHERE e.department_id IS NULL;

# 8. 查询哪个城市没有部门
SELECT l.location_id, l.street_address 
FROM departments d RIGHT JOIN locations l 
ON d.location_id = l.location_id 
WHERE d.location_id IS NULL;

# 9. 查询部门名为 Sales 或 IT 的员工信息
SELECT e.*,d.department_name FROM employees e, departments d 
WHERE e.department_id = d.department_id 
AND d.department_name IN ('Sales', 'IT');

Func

数值函数：

/*
ABS(X) 求绝对值
CELING(X) 向上取整
FLOOR(X) 向下取整
RADN(X) X 为随机数种子的随机数
RAND() 0-1 随机数
ROUND(X) 四舍五入，取整
ROUND(X,Y) 保留 Y 位小数，四舍五入
TRUNCATE(X,Y) 保留 Y 位小数，截断
三角函数
指数对数
进制转换
*/

字符串函数：

/*
ASCII(C) 获取 c 的 ascii 码值
CHAR_LENGTH(S) 获取字符长度
LENGTH(S) 获取字节数（和编码有关）
CONCAT(s1,s2...) 字符串拼接
CONCAT_WS(sep,s1,s2...) 使用 sep 分隔符连接字符串
INSERT(str,index,length,reolacestr) 字符索引 1 开始
REPLACE(str,a,b) 字符串替换
UPPLER(S) 转大写
LOWER(S) 转小写
LEFT(S,n) 取左边 n 个
RIGHT(S,n) 取右边 n 个
LPAD(S,length,pad) 扩展 s 长度，左边使用 pad 补充
RPAD(S,length,pad) 扩展 s 长度，右边使用 pad 补充
TRIM(S) 去除字符左右的空白字符
REPEAT(S,n) 重复 n 次
SUBSTR(S,index,length) 取子串
LOCATE(substr,S) 查找子串
*/

日期时间函数：

/*
CURDATE() 当前日期，年月日
CURTIME() 当前时间，时分秒
NOW() 当前系统时间
UTC_DATE()
UTC_TIME()
UNIX_TIMESTAMP() 时间戳
UNIX_TIMESTAMP(DATE) 将 date 转为时间戳
FROM_UNIXTIME(TIMESTAMP) 将时间戳转为时间
YEAR(DATE)/MONTH(DATE)/DAY(DATE)
HOUR(TIME)/MINUTE(TIME)/SECOND(TIME)
TIME_TO_SEC(TIME) 时间转为秒钟
SEC_TO_TIME(SECONDS) 秒转时间
ADDDATE(DATE,INTERVAL expr type) 增加日期
SUBDATE(DATE, INTERVAL expr type) 减少日期
ADDTIME(time1,time2)
SUBTIME(time1,time2)
DATEDIFF(date1,date2)
TIMEDIFF(time1,time2)
DATE_FORMAT(date,format) %Y-%m-%d %H:%i:%s
TIME_FORMAT(time,format)
STR_TO_DATE(str,format)
*/

流程控制处理函数：

/*
如果 value 为 true，则 value1，反之 value2
IF(value,value1,value2) 

如果 value1 不为空返回 value1，某则返回 value2
IFNULL(value1,value2) 

CASE 
WHEN condition1 THEN result1
WHEN condition2 THEN result2
ELSE result
END

CASE expr
WHEN value1 THEN result1
WHEN value2 THEN result2
ELSE result
END
*/

加密与解密函数：

/*
PASSWORD(str)  
MD5(str) 
SHA(str) 
ENCODE(value,seed) 以 seed 作为加密密码加密 value
DECODE(value,seed) 以 seed 作为加密密码解密 value
*/

MySQL 相关函数：

/*
VERSION() 返回 mysql 版本号
CONNECTION_ID() 返回 mysql 连接数
DATABASE() 返回当前所在数据库
USER() 返回当前连接用户
CHARSET(value) 返回 value 字符集
COLLATION(value) 返回 value 校对规则
*/

# 1.显示系统时间(注：日期+时间)
SELECT NOW() FROM DUAL;

# 2.查询员工号，姓名，工资，以及工资提高百分之20%后的结果（new salary）
SELECT e.employee_id,e.last_name, e.salary, 
e.salary * 1.2 "new salary" 
FROM employees e;

# 3.将员工的姓名按首字母排序，并写出姓名的长度（length）
SELECT e.last_name, LENGTH(e.last_name) FROM employees e 
ORDER BY e.last_name DESC;

# 4.查询员工id,last_name,salary，并作为一个列输出，别名为OUT_PUT
SELECT CONCAT(employee_id, ',' , last_name , ',', salary) OUT_PUT FROM employees e;

# 5.查询公司各员工工作的年数、工作的天数，并按工作年数的降序排序
SELECT e.hire_date, YEAR(NOW()) - YEAR(e.hire_date) years, 
DATEDIFF(NOW(),e.hire_date) 
FROM employees e
ORDER BY years DESC;

# 6.查询员工姓名，hire_date , department_id，满足以下条件：雇用时间在1997年之后，department_id 为80 或 90 或110, commission_pct不为空
SELECT e.employee_id,e.hire_date,e.department_id 
FROM employees e
WHERE e.commission_pct IS NOT NULL
AND e.department_id in (80, 90, 100)
AND YEAR(e.hire_date) >= 1997;

# 7.查询公司中入职超过10000天的员工姓名、入职时间
SELECT e.employee_id ,e.hire_date FROM employees e 
WHERE DATEDIFF(NOW(),e.hire_date) > 10000;

# 8、使用 case when
SELECT e.last_name,e.job_id,
(CASE e.job_id WHEN 'AD_PRES' THEN 'A'
WHEN 'ST_MAN' THEN 'B'
WHEN 'IT_PROG' THEN 'C'
WHEN 'SA_REP' THEN 'D'
WHEN 'ST_CLERK' THEN 'E'
ELSE 'F'
END) Grade
FROM employees e;

聚合函数：

/*  不能在 WHERE 使用聚合函数，因为 WHERE 的数据只有一行没有多行
AVG() 对数值型使用，求平均
SUM() 对数值型使用，求和
MIN() 对任意类型，取最小
MAX() 对任意类型，取最大
COUNT(*) 返回记录总数，包括 NULL 行
COUNT(ecpr) 返回 expr 不为 NULL 行
*/

Group

GROUP BY 分组，和聚合函数结合使用

可以对多个列进行分组，分组的列必须列出来，不分组的列需要使用聚合函数来使得分组后的数据进行处理

WITH ROLLUP 在分组之后会增加一条记录，计算查询记录总和，不可与 ORDER BY 同时出现

HAVING 分组后过滤，必须在 GROUP BY 后使用

SELECT 格式与执行顺序：

#（1）from：从哪些表中筛选
#（2）on：关联多表查询时，去除笛卡尔积
#（3）where：从表中筛选的条件
#（4）group by：分组依据
#（5）having：在统计结果中再次筛选
#（6）order by：排序
#（7）limit：分页

# 执行顺序：FROM -> WHERE -> GROUP BY -> HAVING -> SELECT 的字段 -> DISTINCT -> ORDER BY -> LIMIT

#2.查询公司员工工资的最大值，最小值，平均值，总和
SELECT MAX(e.salary),MIN(e.salary),AVG(e.salary),SUM(e.salary) FROM employees e;

#3.查询各job_id的员工工资的最大值，最小值，平均值，总和
SELECT e.job_id, MAX(e.salary),MIN(e.salary),AVG(e.salary),SUM(e.salary) 
FROM employees e GROUP BY e.job_id;

#4.选择具有各个job_id的员工人数
SELECT e.job_id, COUNT(*)
FROM employees e GROUP BY e.job_id;

# 5.查询员工最高工资和最低工资的差距（DIFFERENCE）
SELECT MAX(e.salary) - MIN(e.salary) DIFFERENCE 
FROM employees e;

# 6.查询各个管理者手下员工的最低工资，其中最低工资不能低于6000，没有管理者的员工不计算在内
SELECT e.manager_id, MIN(e.salary) minS FROM employees e 
WHERE e.manager_id IS NOT NULL
GROUP BY e.manager_id
HAVING minS > 6000;

# 7.查询所有部门的名字，location_id，员工数量和平均工资，并按平均工资降序
SELECT d.department_name,d.location_id, COUNT(e.employee_id) counts, AVG(e.salary) avgS
FROM employees e, departments d
WHERE e.department_id = d.department_id 
GROUP BY d.department_name,d.location_id
ORDER BY avgS;

# 8.查询每个工种、每个部门的部门名、工种名和最低工资
SELECT d.department_name,e.job_id,MIN(e.salary)
FROM departments d LEFT JOIN employees e
ON e.department_id = d.department_id
GROUP BY d.department_name,e.job_id

Subquery

子查询要包含在括号内

将子查询放在比较条件的右侧

单行操作符对应单行子查询，多行操作符对应多行子查询

ANY 与子查询某一个值进行比较

ALL 与子查询所有值进行比较

EXISTS 子查询满足条件返回 true

NOT EXISTS 子查询不满足条件，返回 true

#1.查询和Zlotkey相同部门的员工姓名和工资
SELECT * FROM employees e WHERE e.department_id = (
	SELECT e.department_id FROM employees e WHERE e.last_name = 'Zlotkey'
);

#2.查询工资比公司平均工资高的员工的员工号，姓名和工资。
SELECT e.employee_id,e.last_name ,e.salary 
FROM employees e 
WHERE e.salary > (
	SELECT AVG(e.salary) FROM employees e
);

#3.选择工资大于所有JOB_ID = 'SA_MAN'的员工的工资的员工的last_name, job_id, salary
SELECT e.last_name ,e.job_id ,e.salary 
FROM employees e 
WHERE e.salary > ALL(
	SELECT e.salary 
	FROM employees e 
	WHERE e.job_id = 'SA_MAN'
);

#4.查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名
SELECT * FROM employees e
WHERE e.department_id IN (
	SELECT e.department_id 
	FROM employees e 
	WHERE e.last_name LIKE '%u%'
);

#5.查询在部门的location_id为1700的部门工作的员工的员工号
SELECT e.employee_id 
FROM employees e 
WHERE e.department_id IN (
	SELECT d.department_id 
	FROM departments d 
	WHERE d.location_id = 1700
);


#6.查询管理者是King的员工姓名和工资
SELECT e.last_name, e.salary 
FROM employees e 
WHERE e.manager_id IN (
	SELECT e.employee_id 
	FROM employees e 
	WHERE e.last_name = 'King'
);


#7.查询工资最低的员工信息: last_name, salary
SELECT e.last_name, e.salary 
FROM employees e 
WHERE e.salary = (
	SELECT MIN(salary) 
	FROM employees
);

#8.查询平均工资最低的部门信息
SELECT * FROM departments d WHERE d.department_id = (
	SELECT e.department_id FROM employees e 
	GROUP BY e.department_id 
	ORDER BY AVG(e.salary) LIMIT 1
);

#9.查询平均工资最低的部门信息和该部门的平均工资（相关子查询）

SELECT d.*,t.avgS
	FROM departments d,(
		SELECT e.department_id,AVG(e.salary) avgS FROM employees e 
		GROUP BY e.department_id 
		ORDER BY AVG(e.salary) LIMIT 1
		) t WHERE d.department_id = t.department_id;


#10.查询平均工资最高的 job 信息
SELECT * FROM jobs j WHERE j.job_id = (
	SELECT e.job_id 
	FROM employees e 
	GROUP BY e.job_id 
	ORDER BY AVG(e.salary) DESC 
	LIMIT 1
);

#11.查询平均工资高于公司平均工资的部门有哪些?
SELECT e.department_id FROM employees e
WHERE e.department_id IS NOT NULL
GROUP BY e.department_id 
HAVING AVG(e.salary) > (SELECT AVG(e.salary) FROM employees e);

#12.查询出公司中所有 manager 的详细信息
SELECT DISTINCT e.* 
FROM employees e ,employees m 
WHERE e.employee_id = m.manager_id; 

SELECT * FROM employees e WHERE EXISTS (
	SELECT * FROM employees e2 
	WHERE e2.manager_id = e.employee_id
);

#13.各个部门中 最高工资中最低的那个部门的 最低工资是多少?
SELECT MIN(e.salary) FROM employees e WHERE e.department_id = (
	SELECT e.department_id FROM employees e 
	GROUP BY e.department_id 
	ORDER BY MAX(e.salary) LIMIT 1
);


#14.查询平均工资最高的部门的 manager 的详细信息: last_name, department_id, email, salary
SELECT e.last_name ,e.department_id ,e.email ,e.salary 
FROM employees e 
WHERE e.employee_id IN (
	SELECT e.manager_id FROM  employees e 
	WHERE e.department_id = (
		SELECT e.department_id 
		FROM employees e 
		GROUP BY e.department_id 
		ORDER BY AVG(e.salary) DESC 
		LIMIT 1
));


#15. 查询部门的部门号，其中不包括job_id是"ST_CLERK"的部门号
SELECT * FROM departments d 
WHERE d.department_id NOT IN (
	SELECT e.department_id 
	FROM employees e 
	WHERE e.job_id = 'ST_CLERK'	
);


#16. 选择所有没有管理者的员工的last_name
SELECT e.last_name FROM employees e WHERE e.manager_id IS NULL;

SELECT e.last_name
FROM employees e
WHERE NOT EXISTS (
	SELECT * FROM employees e1
	WHERE e.manager_id = e1.employee_id
);

#17．查询员工号、姓名、雇用时间、工资，其中员工的管理者为 'De Haan'
SELECT e.employee_id ,e.last_name ,e.hire_date ,e.salary 
FROM employees e 
WHERE e.manager_id = (
	SELECT e.employee_id 
	FROM employees e 
	WHERE e.last_name = 'De Haan'
);

#18.查询各部门中工资比本部门平均工资高的员工的员工号, 姓名和工资（相关子查询）
SELECT e.employee_id ,e.last_name ,e.salary 
FROM employees e 
WHERE e.salary > (
			SELECT AVG(e1.salary) FROM employees e1 
			WHERE e1.department_id = e.department_id 
			GROUP BY e1.department_id
);


#19.查询每个部门下的部门人数大于 5 的部门名称（相关子查询）
SELECT * FROM departments d WHERE 5 < (
	SELECT COUNT(*) 
	FROM employees e 
	WHERE e.department_id =d.department_id
);

#20.查询每个国家下的部门个数大于 2 的国家编号（相关子查询）
SELECT * FROM countries c , locations l 
WHERE c.country_id = l.country_id 
AND 2 < (
	SELECT COUNT(*) 
	FROM departments d 
	WHERE d.location_id = l.location_id 
);

DATABASE

创建数据库：

# 以默认方式创建数据库
CREATE DATABASE mytest;

# 查看数据库创建语句
SHOW CREATE DATABASE mytest;

# 创建数据库，存在则不创建，指定字符集和校对规则
CREATE DATABASE IF NOT EXISTS mytest2 
CHARACTER SET 'utf8mb4' 
COLLATE 'utf8mb4_0900_ai_ci';

# 切换数据库
USE mytest;

# 查看数据库下的表
SHOW TABLES;
SHOW TABLES FROM mysql;

# 查看当前所在数据库
SELECT DATABASE() FROM DUAL;

修改数据库：

1	`ALTER DATABASE mytest2 CHARACTER SET 'utf8';`

删除数据库：

1	`DROP DATABASE IF EXISTS mytest2;`

TABLE

创建表：

/*
CREATE TABLE [IF NOT EXISTS] 表名(
字段1, 数据类型 [约束条件] [默认值],
字段2, 数据类型 [约束条件] [默认值],
字段3, 数据类型 [约束条件] [默认值],
……
[表约束条件]
);
*/

# 创建表
CREATE TABLE emp (
    emp_id INT,
    emp_name VARCHAR(20),
    salary DOUBLE,
    birthday DATE
);

# 查看表结构
DESC emp;

# 使用 AS 基于查询创建表和插入查询到的数据
CREATE TABLE dept80
AS
SELECT employee_id, last_name, salary*12 ANNSAL, hire_date
FROM employees
WHERE department_id = 80;

修改表：

# 添加列
ALTER TABLE emp 
ADD phone_num VARCHAR(14) AFTER emp_name;

# 修改列
/*
ALTER TABLE 表名 
MODIFY 【COLUMN】 字段名1 字段类型 【
DEFAULT 默认值】
【FIRST|AFTER 字段名2】;
*/

ALTER TABLE dept80
MODIFY salary double(9,2) default 1000;

ALTER TABLE dept80
CHANGE department_name dept_name varchar(15);

# 删除列
ALTER TABLE emp
DROP COLUMN phone_num;

# 重命名表
RENAME TABLE emp
TO myemp;

# 删除表
DROP TABLE IF EXISTS emp;

# 清空表 TRUNCATE 不可回滚，DELETE 有事务可回滚
TRUNCATE TABLE emp;
DELETE FROM emp;

#1. 创建数据库test01_office,指明字符集为utf8。并在此数据库下执行下述操作
CREATE DATABASE test01_office CHARACTER SET 'utf8';
USE test01_office;
#2. 创建表dept01
/*
字段 类型
id INT(7)
NAME VARCHAR(25)
*/
CREATE TABLE dept01 (
 id INT(7),
 name VARCHAR(25)
);

#3. 将表departments中的数据插入新表dept02中
CREATE TABLE dept02
AS
SELECT * FROM atguigudb.departments;

#4. 创建表emp01
/*
字段 类型
id INT(7)
first_name VARCHAR (25)
last_name VARCHAR(25)
dept_id INT(7)
*/
CREATE TABLE emp01 (
	id INT(7),
	first_name VARCHAR(25),
	last_name VARCHAR(25),
	dept_id INT(7)
);

DESC emp01;
#5. 将列last_name的长度增加到50
ALTER TABLE emp01
MODIFY last_name VARCHAR(50);

#6. 根据表employees创建emp02
CREATE TABLE emp02
AS
SELECT * FROM atguigudb.employees;

#7. 删除表emp01
DROP TABLE emp01;

#8. 将表emp02重命名为emp01
RENAME TABLE emp02 TO emp01;

#9.在表dept02和emp01中添加新列test_column，并检查所作的操作
ALTER TABLE dept02 
ADD test_column VARCHAR(1);
ALTER TABLE emp01 
ADD test_column VARCHAR(1);
DESC dept02;
DESC emp01;

#10.直接删除表emp01中的列 department_id
ALTER TABLE emp01
DROP COLUMN department_id;

RECORD

插入数据：

/*
INSERT INTO 表名(column1 [, column2, …, columnn])
VALUES
(value1 [,value2, …, valuen]),
(value1 [,value2, …, valuen]),
……
(value1 [,value2, …, valuen]);
*/

/* 使用查询的数据插入表中
INSERT INTO 目标表名
(tar_column1 [, tar_column2, …, tar_columnn])
SELECT
(src_column1 [, src_column2, …, src_columnn])
FROM 源表名
[WHERE condition]
*/

更新数据：

/*
UPDATE table_name
SET column1=value1, column2=value2, … , column=valuen
[WHERE condition]
*/

删除数据：

1
2
3

/*
DELETE FROM table_name [WHERE <condition>];
*/

MySQL8计算列：

# 使用其他列计算而来
CREATE TABLE tb1(
id INT,
a INT,
b INT,
c INT GENERATED ALWAYS AS (a + b) VIRTUAL
);

# 1、创建数据库test01_library
CREATE DATABASE test01_library;
USE test01_library;
# 2、创建表 books
CREATE TABLE books(
	id INT,
	name VARCHAR(50),
	authors VARCHAR(100),
	price FLOAT,
	pubdate YEAR,
	note VARCHAR(100),
	num INT
);

DESC books;
SELECT * FROM books;
# 3、向books表中插入记录
# 1）不指定字段名称，插入第一条记录
INSERT INTO books VALUES(1, 'Tal of AAA', 'Dickes', 23, 1995, 'novel', 11);
# 2）指定所有字段名称，插入第二记录
INSERT INTO books(id, name, authors, price, pubdate, note, num)
VALUES (2, 'EmmaT', 'Jane lura', 35, 1993, 'joke', 22),
	(3, 'Story of Jane', 'Jane Tim', 40, 2001, 'novel', 0);
# 3）同时插入多条记录（剩下的所有记录）
INSERT INTO books
VALUES (4, 'Lovey Day', 'George Byron', 20, 2005, 'novel', 30),
	(5, 'Old land', 'Honore Blade', 30, 2010, 'law', 0),
	(6, 'The Battle', 'Upton Sara', 30, 1999, 'medicine', 40),
	(7, 'Rose Hood', 'Richard haggard', 28, 2008, 'cartoon', 28);
# 4、将小说类型(novel)的书的价格都增加5
UPDATE books b SET b.price = b.price + 5 WHERE b.note = 'novel';

# 5、将名称为EmmaT的书的价格改为40，并将说明改为drama
UPDATE books b SET b.price = 40, b.note = 'drama' WHERE b.name = 'EmmaT';
# 6、删除库存为0的记录
DELETE FROM books WHERE num = 0;
# 7、统计书名中包含a字母的书
SELECT * FROM books b WHERE b.name LIKE '%a%';

# 8、统计书名中包含a字母的书的数量和库存总量
SELECT COUNT(*),SUM(num) FROM books b WHERE b.name LIKE '%a%';

# 9、找出“novel”类型的书，按照价格降序排列
SELECT * FROM books b WHERE b.note = 'novel' ORDER BY b.price DESC;

# 10、查询图书信息，按照库存量降序排列，如果库存量相同的按照note升序排列
SELECT * FROM books b ORDER BY b.num DESC, b.note;

# 11、按照note分类统计书的数量
SELECT b.note ,COUNT(*) FROM books b GROUP BY b.note;

# 12、按照note分类统计书的库存量，显示库存量超过30本的
SELECT b.note ,SUM(b.num) sums FROM books b 
GROUP BY b.note
HAVING sums >= 30;

# 13、查询所有图书，每页显示5本，显示第二页
SELECT * FROM books b LIMIT 5,5;

# 14、按照note分类统计书的库存量，显示库存量最多的
SELECT b.note FROM books b 
GROUP BY b.note
ORDER BY SUM(b.num)
LIMIT 1;

# 15、查询书名达到10个字符的书，不包括里面的空格
SELECT * FROM books b WHERE CHAR_LENGTH(REPLACE(b.name,' ','')) >= 10;

# 16、查询书名和类型，其中note值为novel显示小说，law显示法律，medicine显示医药，cartoon显示卡通，joke显示笑话
SELECT b.name, b.note,(CASE b.note 
WHEN 'novel' THEN '小说'
WHEN 'law' THEN '法律'
WHEN 'medicine' THEN '医药'
WHEN 'cartoon' THEN '卡通'
WHEN 'joke' THEN '笑话'
ELSE '其他'
END) zhname
FROM books b;

# 17、查询书名、库存，其中num值超过30本的，显示滞销，大于0并低于10的，显示畅销，为0的显示需要无货
SELECT b.name, b.num,(CASE 
WHEN b.num >= 30 THEN '滞销'
WHEN b.num < 10 AND b.num > 0 THEN '畅销'
WHEN b.num = 0 THEN '无货'
ELSE '正常'
END) status
FROM books b;

# 18、统计每一种note的库存量，并合计总量
SELECT b.note, SUM(b.num) FROM books b GROUP BY b.note WITH ROLLUP;

# 19、统计每一种note的数量，并合计总量
SELECT b.note,COUNT(1) FROM books b GROUP BY b.note WITH ROLLUP;

# 20、统计库存量前三名的图书
SELECT * FROM books b ORDER BY sums DESC  LIMIT 3;

# 21、找出最早出版的一本书
SELECT * FROM books b ORDER BY b.pubdate LIMIT 1;

# 22、找出novel中价格最高的一本书
SELECT * FROM books b WHERE b.note = 'novel' ORDER BY b.price DESC LIMIT 1;

# 23、找出书名中字数最多的一本书，不含空格
SELECT * FROM books ORDER BY CHAR_LENGTH(REPLACE(name,' ','')) DESC LIMIT 0,1;

Type

整型：

TINYINT（1字节）、SMALLINT（2字节）、MEDIUMINT（3字节）、INT/INTEGER（4字节）、BIGINT（8字节）

# 其中的 5 表示显示宽度，搭配 ZEROFILL 零填充才能看到效果，MySQL 8.0.17 不再有显示宽度
# UNSIGNED 设置无符号
CREATE TABLE test_int(
	f1 INT(5),
    f2 INT(5) ZEROFILL
);

浮点型：

FLOAT（4字节）、DOUBLE（8字节）、REAL（默认就是 DOUBLE）

表示精度不精准，不应使用 = 比较浮点型

定点数：

DECIMAL(M,D)，M 位有效位，小数点后 D 位。使用字符串存储。

位类型：

BIT(M)

日期时间类型：

YEAR（年）、TIME（时分秒）、DATE（年月日）、DATETIME（年月日时分秒）、TIMESTAMP（年月日时分秒）

DATE：标准格式为：YYYY-MM-DD

TIME：标准格式：D HH-MM-SS、HH-MM-SS，D 会转为小时与 HH 相加

DATETIME（8个字节）：标准格式：YYYY-MM-DD HH:MM:SS

TIMESTAMP（4字节）：标准格式：YYYY-MM-DD HH:MM:SS，存储范围为 “1970-01-01 00:00:01 UTC”到“2038-01-19 03:14:07 UTC”，存储的数据时区不同显示不同。

尽量使用 DATETIME，注册时间等常用于计算的存储使用时间戳

文本字符串类型：

CHAR(M)、VARCHAR(M)、TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT、ENUM、SET

VARCHAR(20)：4.0版本以下指20字节，5.0版本以上指20字符，存储字节为字符串实际长+1，InnoDB 建议使用 VARCHAR

ENUM：season ENUM('春','夏','秋','冬',unknow')，只能选择一个

SET：s SET ('A', 'B', 'C')，可多选存储

二进制：

BINARY、VARBINARY、TIBYBLOB、BLOB、MEDIUMBLOB、LONGBLOB

BLOG 和 TEXT 执行大量更新和删除操作时，会产生空洞现象，为提高性能应定期使用 OPTIMIZE TABLE 进行碎片处理。尽量分离到单独的表中，减少主表的碎片。

JSON 类型：

CREATE TABLE test_json(
js json
);

INSERT INTO test_json (js)
VALUES ('{"name":"songhk", "age":18, "address":{"province":"beijing",
"city":"beijing"}}');

SELECT js -> '$.name' AS NAME,js -> '$.age' AS age ,js -> '$.address.province'
AS province, js -> '$.address.city' AS city
FROM test_json;

Constraint

实体完整性、域完整性、引用完整性、用户自定义完整性

# 列级约束，约束在列之后
CREATE TABLE mytest(
	id int NOT NULL
);

# 表级约束，在所有字段之后
CREATE TABLE mytest(
	id int NOT NULL,
    last_name VARCHAR(25),
    email VARCHAR(25)
    CONSTRAINT uk_mytest_email UNIQUE(email) 
);

NOT NULL 非空约束，规定某个字段不能为空
UNIQUE 唯一约束，规定某个字段在整个表中是唯一的
- 定义 UNIQUE 约束时会自动创建一个唯一性索引
- 删除唯一约束，只能通过删除唯一约束来删除

# 设置 name 和 pwd 组合唯一
CREATE TABLE mytest(
	id INT,
    name VARCHAR(25),
    pwd VARCHAR25),
    CONSTRAINT uk_mytest_name_pwd UNIQUE(`name`, pwd) # 指定索引名
    # UNIQUE KEY(`name`, pwd)  # 索引自动命名为第一个列名
);

# 查看表索引
SHOW INDEX FROM mytest;

# 删除唯一性约束
ALTER TABLE mytest 
DROP INDEX uk_mytest_name_pwd;

PRIMARY KEY 主键(非空且唯一)约束，只能有一个主键约束

# 列级约束
id INT PRIMARY KEY

# 表级约束，索引自动命名为列名
PRIMARY KEY(id)

AUTO_INCREMENT 自增，一个表只能有一个自增列，必须是主键列、整数类型

/*
MySQL5.7 自增下一个值保存在内存中，重启服务则清空了缓存
MySQL8 则是保存在重做日志中
*/

FOREIGN KEY 外键约束，限定表的引用完整性
- 外键必须是主表的主键或唯一约束
- 表可以创建多个外键约束
- 外键有性能开销，推荐应用层解决引用完整性而不是使用外键

# 表级约束
CONSTRANT fk_dept_id FOREIGN KEY(dept_id) REFERENCE department(dept_id)

# 约束等级
# Cascade 主表删除对应数据时，从表相关数据也删除
# Set null 主表删除对应数据时，从表的外键设为 null 而不删除记录

# 删除外键约束
SELECT * FROM information_schema.table_constraints WHERE table_name = '表名称';#查看某个
表的约束名
ALTER TABLE 从表名 DROP FOREIGN KEY 外键约束名;

CHECK 检查约束，检查值的范围，5.7 不支持，8 支持

1	`salary DECIMAL(10,2) CHECK(salary > 2000)`

DEFAULT 默认值约束

View

视图可以看作是一个虚拟表，修改视图数据，基表数据也会修改，视图数据删除，基表不会删除。

视图更新需要视图的行和基表一一对应关系，以下情况不能更新：

视图定义时指定 ALGORITHM=TEMPTABLE，不支持插入和删除操作
视图中不包含基表中所有被定义为非空又未指定默认值的列，视图将不支持INSERT操作
视图查询语句使用 join，不支持插入和删除操作
查询语句使用数学表达式或子查询，不h支持插入和更新哪些列
视图列中使用 DISTINCT、聚合函数、GROUP BY、HASVING、UNION 等，不支持插入、更新和删除操作
通常视图就是为了方便查询，更新修改数据都是在基表上操作

/*
CREATE [OR REPLACE]
[ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
VIEW 视图名称 [(字段列表)]
AS 查询语句
[WITH [CASCADED|LOCAL] CHECK OPTION]
*/

# 查看视图
DESC 视图名称;

# 删除视图
DROP VIEW IF EXISTS 视图名称;

Procedure&Function

存储过程是一段程序化sql，对底层表进行操作

/*
CREATE PROCEDURE 存储过程名(IN|OUT|INOUT 参数名 参数类型,...)
[characteristics ...]
BEGIN
存储过程体
END
*/

# 调用：CALL 存储过程名(实参列表)

MySQL

#Notes

MySQL8

https://reajason.vercel.app/2021/11/18/MySQL8Learning/

作者

ReaJason

发布于

2021年11月18日

许可协议

Tomcat 上一篇

CoreJava Volume Ⅰ 下一篇