MYSQL-必知必会

数据库数据

数据资源

李玉婷MYSQL

https://www.aliyundrive.com/s/9FULa87P98x
Navicat 导入

| 外部SQL 文件导入 |

| :———————————————————-: |
| 外部SQL文件导入 |

表数据字段解释

mysql> desc employees;
+----------------+--------------+------+-----+---------+----------------+
| Field          | Type         | Null | Key | Default | Extra          |
+----------------+--------------+------+-----+---------+----------------+
| employee_id    | int(6)       | NO   | PRI | NULL    | auto_increment |  员工编号
| first_name     | varchar(20)  | YES  |     | NULL    |                |  名
| last_name      | varchar(25)  | YES  |     | NULL    |                |  姓
| email          | varchar(25)  | YES  |     | NULL    |                |  邮箱
| phone_number   | varchar(20)  | YES  |     | NULL    |                |  电话号码
| job_id         | varchar(10)  | YES  | MUL | NULL    |                |  工种编号
| salary         | double(10,2) | YES  |     | NULL    |                |  月薪
| commission_pct | double(4,2)  | YES  |     | NULL    |                |  奖金率
| manager_id     | int(6)       | YES  |     | NULL    |                |  上级领导编号
| department_id  | int(4)       | YES  | MUL | NULL    |                |  部门编号
| hiredate       | datetime     | YES  |     | NULL    |                |  入职日期
+----------------+--------------+------+-----+---------+----------------+

mysql> desc departments;
+-----------------+------------+------+-----+---------+----------------+
| Field           | Type       | Null | Key | Default | Extra          |
+-----------------+------------+------+-----+---------+----------------+
| department_id   | int(4)     | NO   | PRI | NULL    | auto_increment |  部门编号
| department_name | varchar(3) | YES  |     | NULL    |                |  部门名称
| manager_id      | int(6)     | YES  |     | NULL    |                |  部门领导的编号
| location_id     | int(4)     | YES  | MUL | NULL    |                |  位置编号
+-----------------+------------+------+-----+---------+----------------+
4 rows in set (0.03 sec)

mysql> desc locations;
+----------------+-------------+------+-----+---------+----------------+
| Field          | Type        | Null | Key | Default | Extra          |  位置表
+----------------+-------------+------+-----+---------+----------------+
| location_id    | int(11)     | NO   | PRI | NULL    | auto_increment |  位置编号
| street_address | varchar(40) | YES  |     | NULL    |                |  街道
| postal_code    | varchar(12) | YES  |     | NULL    |                |  邮编
| city           | varchar(30) | YES  |     | NULL    |                |  城市
| state_province | varchar(25) | YES  |     | NULL    |                |  州/省
| country_id     | varchar(2)  | YES  |     | NULL    |                |  国家编号
+----------------+-------------+------+-----+---------+----------------+
6 rows in set (0.05 sec)

mysql> desc jobs;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| job_id     | varchar(10) | NO   | PRI | NULL    |       |  工种编号
| job_title  | varchar(35) | YES  |     | NULL    |       |  工种名称
| min_salary | int(6)      | YES  |     | NULL    |       |  最低工资
| max_salary | int(6)      | YES  |     | NULL    |       |  最高工资
+------------+-------------+------+-----+---------+-------+
4 rows in set (0.07 sec)

SQL查询

基础

注释

1
2
3

#  注释
-- 注释
/* 注释 */

单表查询

-- 1. 查询列表可以是: 表中的字段、常量值、表达式、函数
-- 2. 查询的结果是一个虚拟的表格

# 选中要操作的数据库
use database-name;

# 查询表中的单个字段
select last_name from employees;

# 查询表中的多个字段
select first_name,last_name from employees;

# 查询表中的所有字段
SELECT
	employee_id,
	first_name,
	last_name,
	email,
	phone_number,
	job_id,
	salary,
	commission_pct 
FROM
	employees;

别名

字段别名

SELECT
	first_name AS "姓" 
FROM
	employees;
	
	
/*
注意:
	as 可以省略

起别名的好处:
	①: 便于理解
	②: 如果要查询的字段由重名的情况,使用别名可以区分开来
	
省略 As:
	SELECT
		first_name 姓 
	FROM
		employees;
*/

去重【 DISTINCT 】

-- 查询员工表中涉及到的所有部门的编号
-- 为去重显示所有数据

SELECT 
	department_id 
FROM
	departments;

-- 去重
SELECT DISTINCT
	department_id 
FROM
	departments;

拼接函数【concat(str1,str2,···)】

SELECT
	concat( first_name, last_name ) 
FROM
	employees;

条件查询

分类

表达式

按条件表达式筛选:
	
	简单条件运算符: > < = != <> >= <=
	
按逻辑表达式筛选:
	
	逻辑运算符: 
		&& 与  and : 两个条件都为 true，结果为 true,反之为 false 
		|| 或  or : 只要由一个条件为 true，结果为 true,反之为 false
    ！ 非  not : 如果连接的条件本身为 false, 结果为 true(取反)
	
	模糊查询:
		
		like
		between ... and ... 
		in 
		is null

where 条件

`小于`	`大于`

`BETWEEN...AND`	`> <`

LIKE

模糊查询

IN

In

IS Null

`Is Null`

试问: 
	select * from employees; 
	和 
	select * from employees where commission_pct like '%%' and last_name like '%%'; 
	结果是否一样?并说明原因
	
	答: 不一样， commission_pct  字段有 null 值

数据库常见概念

DB：数据库,存储数据的容器
DBMS：数据库管理系统,又称为数据库软件或数据库产品,用于创建或管理 DB
SQL：结构化查询语言,用于和数据库通信的语言,不是某个数据库软件特有的,而是几乎所有的主流数据库软件通用的语言

数据库存储数据的特点

数据存储到表中,然后表再放到库中
一个库可以又多张表,每张表具有唯一的表明用来表识自己
表中有一个或多个列,列又被称为”字段”,* 相当于 java 中的属性
表中的每一行数据, 相当于 java 中的对象

MYSQL的优点

开源、免费、成本低
性能好、移植性也好
体积小,便于安装

排序查询

SELECT
	* 
FROM
	employees 
ORDER BY
	salary ASC;

SELECT
	* 
FROM
	employees 
ORDER BY
	salary ASC;

查询员工的姓名和部门号和年薪,按年薪降序按姓名升序

`asc`

选择工资不再 8000 到 17000 的员工的姓名和工资,按工资降序

`desc`

查询邮箱中包含 “e” 的员工信息,并先按邮箱的字节数降序,再按部门号升序

模糊查询与排序

常见函数

字符函数

length():获取字节数
concat(): 字符拼接
upper、lower：大小写相关

substr、substring

substr：截取操作索引从 1 开始

1 2	SELECT substr( "我是谁,我在哪", 3, 4 ); // 谁,我在

substring：函数从特定位置开始的字符串返回一个给定长度的子字符串

1 2	SELECT substring( "我是谁", 3 ); // 谁

instr：返回字符串中第一次出现的索引,如果找不到返回 0

1 2	SELECT instr( "我困了", "困" ) AS out_put; // 2

trim: 去除空格

lpad | rpad

-- lpad 用指定的字符实现左填充指定长度
SELECT
	lpad( '数据测试', 10, '*' );
	
SELECT
	rpad( '数据测试', 10, '*' );

replace 替换

1 2	SELECT REPLACE ( "我是数据测试", "数据测试", "xxxxx" );

数学函数

round 四舍五入

SELECT
	round( 10.88 ); // 11 

SELECT
	round( 10.28 ); // 10

ceil 向上取整,返回 >= 该参数的最小整数

1 2	SELECT ceil(- 1.05 ); // -1

floor 向下取整，返回<= 该参数的最大整数

1 2	SELECT floor(- 1.05 ); // -2

mod 取余

1 2	SELECT MOD ( 10,- 3 ); // 1

truncate 截断指定小数位

1 2	SELECT TRUNCATE ( 1.66999, 2 ); // 1.66

日期函数

now 返回当前系统日期 + 时间

1 2	SELECT now(); // 2021-07-31 20:44:23

curdate：返回当前系统日期

1
2
3

-- curdate 返回当前系统日期,不包含时间
SELECT
	curdate(); // 2021-07-31

curtime：返回当前时间,不包含日期

1 2	SELECT curtime(); // 20:47:18

可以获取指定的部分、年、月、日

1 2	SELECT year(now()) 年; SELECT month(now()) 月;

str_to_date: 将日期格式的字符转换为指定格式的日期

1 2	SELECT str_to_date( "9-13-1999", "%m-%d-%Y" ); // 1999-09-13

date_format：将日期转换成字符

SELECT
	date_format(
	"2021/8/1",
	"%Y年%m月%d日"); // 2021年08月01日

序号	格式符	功能
1	%Y	四位的年份
2	%y	2 位的年份
3	%m	月份(01,02,···)
4	%c	月份(1,2,···)
5	%d	日(01,02,···)
6	%H	小时(24小时制)
7	%h	小时(12小时制)
8	%i	分钟(00,01…59)
9	%s	秒(00,01,…59)

使用练习

-- 查询有奖金的员工的员工名和入职如期(xx月/xx日 xx年)
SELECT
	last_name,
	date_format( hiredate, "%m月/%d日 %y年" ) 入职日期 
FROM
	employees 
WHERE
	commission_pct IS NOT NULL;

流程控制

IF

SELECT
	last_name,
	commission_pct,
IF
	( commission_pct IS NULL, "没奖金,0+", "有奖金500+" ) 备注 
FROM
	employees;

case 函数

`case`

case 要判断的字段或表达式
	when 常量1 then 要显示的值1或语句1
	when 常量2 then 要显示的值2或语句2
	...
	else 要显示的值 n 或语句 n;
	end
	
练习:
-- 查询员工的工资,要求 
	-- 部门号 = 30，显示工资为 1.1 倍
	-- 部门号 = 40，显示工资为 1.2 倍
	-- 部门号 = 50，显示工资为 1.3 倍

case 
	when 条件1 then 要显示的值1或语句1
	when 条件2 then 要显示的值2或语句2
	...
	else 要显示的值 n 或语句 n;
	end

`when..then`

练习掌握

-- 查询员工名、姓名、工资、以及工资提高百分之 20% 后的结果

SELECT
	last_name,
	salary AS "原始工资",
	salary * 1.2 AS "new salary" 
FROM
	employees;
	
-- 将员工的姓名按首字母排序,并写出姓名的长度(length)
SELECT
	concat( first_name, last_name ) "nameInfo",
	length(
	concat( first_name, last_name )) AS "length" 
FROM
	employees 
ORDER BY
	nameInfo;

分组函数

功能: 用作统计使用,又称为聚合函数或统计函数或组函数
分类:sum 求和、avg 平均值、max 最大值、min 最小值、count 计算个数
特点:
1. sum、avg 一般用于处理数值型，max、min、count 可以处理任何类型
2. 以上分组函数都忽略null 值
3. 可以和 distinct 搭配实现去重运算
4. count：一般使用 count(*) 用作统计行数
5. 和分组函数一同查询的字段要求是group by 后的字段

练习掌握

-- 查询员工表中的最大入职时间和最小入职时间相差的天数(difference)
SELECT
	max( hiredate ),
	min( hiredate ),
	datediff(
		max( hiredate ),
	min( hiredate )) AS difference 
FROM
	employees;
	
	
	
-- 查询部门编号为 90 的员工个数
SELECT
	count( department_id ) 
FROM
	employees 
WHERE
	department_id = 90;

分组查询

分组查询练习

-- 查询每个部门的平均工资
SELECT
	avg( salary ),
	department_id 
FROM
	employees 
GROUP BY
	department_id;

	
-- 查询每个工种的最高工资
SELECT
	max( salary ),
	job_id 
FROM
	employees 
GROUP BY
	job_id;

-- 查询每个位置上的部门个数
SELECT
	count(location_id ),
	location_id 
FROM
	departments 
GROUP BY
	location_id;

-- 查询邮箱中包含 a 字符的,每个部门的平均工资
SELECT
	avg( department_id ),
	department_id 
FROM
	employees 
WHERE
	email LIKE "%a%" 
GROUP BY
	department_id;

	
-- 查询有奖金的每个领导手下员工的最高工资
SELECT
	max( salary ),
	manager_id 
FROM
	employees 
WHERE
	commission_pct IS NOT NULL 
GROUP BY
	manager_id;

添加分组后筛选

-- 查询那个部门的员工个数 > 2
SELECT
	count(*),
	department_id 
FROM
	employees 
GROUP BY
	department_id 
HAVING
	count(*)> 2;

-- 查询每个工种有奖金的员工的最高工资 > 12000 的工种编号和最高工资
SELECT
	max( salary ),
	job_id 
FROM
	employees 
WHERE
	commission_pct IS NOT NULL 
GROUP BY
	job_id 
HAVING
	max( salary )> 12000;

-- 查询领导编号 > 102 的每个领导手下的最低工资 > 5000 的领导编号是那个,以及其最低工资
SELECT
	min( salary ) "最低工资",
	manager_id 
FROM
	employees 
WHERE
	manager_id > 102 
GROUP BY
	manager_id 
HAVING
	min( salary )> 5000;

分组查询中的筛选条件分为两类

	数据源	位置	关键字
分组`前`筛选	原始表	`group by` 子句的前面	`where`
分组`后`筛选	分组后的结果集	`group by` 子句的后面	`having`

分组函数的条件肯定是放在 having 子句中
能用分组前筛选的,就优先考虑使用分组前筛选

-- 按员工姓名的长度分组,查询每一组的员工个数,筛选员工个数 > 5 的有那些
SELECT
	count(*),
	length( last_name ) 
FROM
	employees 
GROUP BY
	length( last_name ) 
HAVING
	count(*)> 5;

按多个字段分组

-- 查询每个部门每个工种的员工的平均工资
SELECT
	avg( salary ),
	department_id,
	job_id 
FROM
	employees 
GROUP BY
	department_id,
	job_id;

-- 查询每个部门每个工种的员工的平均工资,并且按平均工资的高低显示
SELECT
	avg( salary ),
	department_id,
	job_id 
FROM
	employees 
GROUP BY
	department_id,
	job_id 
ORDER BY
	avg( salary ) DESC;

-- 查询各 job_id 的员工工资的最大值，最小值，平均值，总和，并按 job_id 升序
SELECT
	max( salary ),
	min( salary ),
	avg( salary ),
	sum( salary ),
	job_id 
FROM
	employees 
GROUP BY
	job_id 
ORDER BY
	job_id ASC;


-- 查询员工最高工资和最低工资的差距(difference)
SELECT
	max( salary ),
	min( salary ),
	max( salary )- min( salary ) AS "DIFFERENCE" 
FROM
	employees;


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

SELECT
	count(*),
	avg( salary ) avgSalary,
	department_id 
FROM
	employees 
GROUP BY
	department_id 
ORDER BY
	avgSalary DESC;

-- 查询具有各个 job_id 的员工人数
SELECT
	count(*),
	job_id 
FROM
	employees 
WHERE
	job_id IS NOT NULL 
GROUP BY
	job_id;

多表查询 SQL92

等值连接

# 查询女神名对应的男神名
SELECT
	`name`,
	boyName 
FROM
	beauty bg,
	boys bys 
WHERE
	bg.boyfriend_id = bys.id;

-- 查询员工名和对应的部门名
SELECT
	last_name,
	department_name 
FROM
	employees,
	departments 
WHERE
	employees.department_id = departments.department_id;

-- 查询员工名,工种号,工种名
SELECT
	last_name "员工名",
	e.job_id "工种号",
	job_title "工种名" 
FROM
	employees e,
	jobs j 
WHERE
	e.job_id = j.job_id;

-- 查询有奖金的员工名,部门名
SELECT
	last_name,
	department_name,
	commission_pct 
FROM
	employees e,
	departments d 
WHERE
	e.department_id = d.department_id 
	AND commission_pct IS NOT NULL;

-- 查询城市名中第二个字符为 "o"的部门名和城市名
SELECT
	department_name,
	city 
FROM
	departments d,
	locations l 
WHERE
	d.location_id = l.location_id 
	AND city LIKE "_o%";

自连接


# 查询员工名和上级的名称
SELECT
	e.employee_id,
	e.last_name,
	m.employee_id,
	m.last_name 
FROM
	employees e,
	employees m 
WHERE
	e.manager_id = m.employee_id;

多表查询 SQL99

等值连接

select 查询列表
from 表1 别名 [连接类型]
join 表2 别名
on 连接条件
where 筛选条件
group by 分组
having 筛选条件
order by 排序列表

分类:
	内连接: inner *
	外连接: 
		左外 left [outer] *
		右外 right [outer] *
		全外: full [outer]
	交叉连接: cross

# 查询员工名,部门名
SELECT
	last_name,
	department_name 
FROM
	employees e
	INNER JOIN departments d ON e.department_id = d.department_id;

# 查询名字中包含 e 的员工名和工种名 添加筛选
SELECT
	last_name,
	job_title 
FROM
	employees e
	INNER JOIN jobs ON e.job_id = jobs.job_id 
WHERE
	last_name LIKE "%e%";

# 查询部门个数 > 3 的城市名和部门个数(添加分组 + 筛选)
SELECT
	count(*),
	city 
FROM
	departments d
	INNER JOIN locations l ON d.location_id = l.location_id 
GROUP BY
	city 
HAVING
	count(*)> 3;

# 查询那个部门的员工个数 > 3 的部门名和员工个数,并按个数降序排序
SELECT
	count(*),
	department_name 
FROM
	employees e
	INNER JOIN departments d ON e.department_id = d.department_id 
GROUP BY
	department_name 
HAVING
	count(*)> 3 
ORDER BY
	count(*) DESC;

# 查询员工名,部门名,工种名,并按部门名降序
SELECT
	last_name,
	department_name,
	job_title 
FROM
	employees e
	INNER JOIN departments d ON e.department_id = d.department_id
	INNER JOIN jobs j ON e.job_id = j.job_id 
ORDER BY
	department_name DESC;

左连接

应用场景: 用于查询一个表中有,另一个表中没有的记录

特点: 
 		1. 外连接的查询结果为主表中的所有记录
 				如果从表中有和它匹配的,则显示匹配的值
 				如果从表中没有和它匹配的,则显示 null
 				
 				外连接查询结果 = 内连接结果 + 主表中有而从表中没有的记录
 		2. 左外连接 left 左边的是主表
 			 右外连接 right join 右边的是主表

# 查询男朋友,不再男神表的女神名
SELECT
	b.NAME 
FROM
	beauty b
	LEFT OUTER JOIN boys bo ON b.boyfriend_id = bo.id 
WHERE
	bo.id IS NULL;
	
# 查询那个部门没有员工
SELECT
	d.*,
	e.employee_id 
FROM
	departments d
	LEFT OUTER JOIN employees e ON d.department_id = e.department_id 
WHERE
	e.email IS NULL;

子查询

基本含义

含义: 出现再其他语句中的select 语句,称为子查询或内查询,外部的查询语句,称为主查询或外查询

分类

按子查询出现的位置: 
		select 后面: 仅仅支持标量子查询
		from 后面: 支持表子查询
		where或having 后面[*]:
				标量子查询
				列子查询
				
				行子查询
		 exists 后面(相关子查询)
		 
按结果集的行列数不同:
		标量子查询(结果集只有一行一列)
		列子查询(结果集只有一行多列)
		行子查询(结果集有一行多列)
		表子查询(结果集一般为多行多列)

where或having 后面[*]:

标量子查询(单行子查询)
列子查询(多行子查询)
行子查询(多行多列)

子查询放在小括号内
子查询一般放在条件的右侧
标量子查询,一般搭配着单行操作符使用 > < >= <= <>
列子查询,一般搭配着多行操作符使用IN、ANY / SOME、ALL

# 查询谁的工资比 Abe1 高
#  先查询 Abel的工资
SELECT
	salary 
FROM
	employees 
WHERE
	last_name = "Abel";
# 再查询谁大于 Abel的工资
SELECT
	* 
FROM
	employees 
WHERE
	salary > ( SELECT salary FROM employees WHERE last_name = "Abel" );

# 返回 job_id 与 141 号(员工编号: employee_id )员工相同,salary 比 143 号员工多的员工 姓名 job_id和工资
job_id: select job_id from employees where employee_id =141;
salary: select salary from employees where employee_id=143;

select 
	last_name,
	job_id,
  salary 
from
	employees 
where 
	job_id = (select job_id from employees where employee_id =141) 
and 
		salary>(select salary from employees where employee_id=143);

# 返回公司工资最少的员工的 last_name job_id salary
SELECT min( salary ) FROM employees;

SELECT
	last_name,
	job_id,
	salary 
FROM
	employees 
WHERE
	salary =(
	SELECT
		min( salary ) 
	FROM
	employees 
	);

# 查询最低工资大于 50号部门最低工资的 部门id个其最低工资

# 查询 50 号的最低工资
select min(salary) from employees where department_id=50;
# 在查询 每个部门的最低工资
select min(salary),department_id from employees group by department_id;

select min(salary),department_id from employees group by department_id having min(salary)>
(
select min(salary) from employees where department_id=50
)

列子查询【多行子查询】

返回多行

使用多行比较操作符

操作符	含义
`IN/NOT IN` **	等于列表中的`任意一个`
`ANY	SOME`
`ALL`	和子查询返回的`所有`值比较

练习实操

# 返回 location_id 是 1400 或 1700 的部门中的所有员工姓名
SELECT
	department_id 
FROM
	departments 
WHERE
	location_id IN ( 1400, 1700 );
	
	
SELECT
	last_name 
FROM
	employees 
WHERE
	department_id IN (
	SELECT
		department_id 
	FROM
		departments 
WHERE
	location_id IN ( 1400, 1700 ));

# 返回其他部门中比 job_id 为 "IT_PROG" 部门任一工资低的员工的: 工号 姓名 job_id 以及 salary
# 查询部门
SELECT DISTINCT
	salary 
FROM
	employees 
WHERE
	job_id = "IT_PROG";
SELECT
	employee_id,
	last_name,
	job_id,
	salary 
FROM
	employees 
WHERE
	salary < ANY ( SELECT DISTINCT salary FROM employees WHERE job_id = "IT_PROG" );

select 后面的子查询

# 查询每个部门的员工个数
select d.*, 
	(select count(*) from employees e where e.department_id = d.department_id) 个数 
from 
	departments d;

子查询练习夯实

-- 查询和 Zlotkey 相同部门的员工的姓名和工资;
/*
分析:
    1. 查询 last_name = "Zlotkey" 的部门
    2. 查询部门号 = 1 的姓名和工资
*/

select last_name, salary
from employees
where department_id = (
    select department_id
    from employees
    where last_name = "Zlotkey"
);

-- 查询工资比公司平均工资高的员工的员工号,姓名、和工资
select employee_id, last_name, salary
from employees
where salary > (select avg(salary) from employees);


-- 查询各部门中工资比本部门平均工资高的员工号、姓名和工资

/*
分析:
    1. 查询各个部门的工资
    2. 联合 employees
*/
--  1. 查询各个部门的工资
-- 2. 将查询的结果集联合
select employee_id, last_name, salary
from employees e
         inner join (select avg(salary) ag, department_id from employees group by department_id) agT
                    on e.department_id = agT.department_id
where salary > ag;

-- 查询和姓名中包含字母 u 的员工在相同部门的员工工号和姓名

select employee_id, e.last_name
from employees e
         inner join (select distinct department_id, last_name from employees where last_name like "%u%") ld
                    on e.department_id = ld.department_id;

-- -----------------------------------------------
select last_name, employee_id
from employees
where department_id in (select distinct department_id from employees where last_name like "%u%");

-- 查询在部门的 localtion_id 为 1700 的部门工作的员工的员工号

select employee_id, e.department_id, dl.location_id
from employees e
         inner join (select distinct department_id, d.location_id
                     from departments d
                              inner join locations l on d.location_id = l.location_id
                     where d.location_id = 1700) dl on e.department_id = dl.department_id;

-- -----------------------------------------------------------------------------------

select employee_id
from employees
where department_id = any (select distinct department_id from departments where location_id = 1700);


-- 查询管理者是 K_ing 的员工姓名和工资

select last_name, salary, manager_id
from employees
where manager_id = (select manager_id from employees where last_name = "K_ing" and manager_id is not null);


-- 查询工资最高的员工的姓名，要求 first_name 和 last_name 显示为一列,列名为 姓.名

select concat(first_name, last_name) "姓.名"
from employees
where salary = (select max(salary) from employees);

分页查询

基础语法

select 查询列表
from 表
【
  join type join 表2
  on 连接条件
  where 筛选条件
  group by 分组字段
  having 分组后的筛选
  order by 排序的字段
 】
 limit offset,size
 
 offset: 要显示条目的起始索引【索引从 0 开始】
 size: 要显示的条目个数

offset 计算方式

第一页: 0 10
第二页: 1 20
第三页: 2 30
...

page = 第 page 页;
每页显示 10 条数据
offset = (page - 1) * size


-- 查询前 10 条员工信息
select * from employees  limit 0,10;

-- 查询 11 条到 25 条数据
select * from employees limit 10,15;

-- 查询: 有奖金的员工信息,并且工资较高的前 10 名显示出来
select * from employees where commission_pct is not null order by salary desc limit 0,10;

增删改

插入表数据

-- 创建测试表 
create table testTable
(
    test_id   int not null primary key, -- 主键
    test_name varchar(10) -- 名称
) char set "utf8"; -- 字符集

show tables;
desc testTable;

insert into testTable
values (1001, "name1001");
insert into testTable
set test_id=1002,
    test_name="name1002";

select * from testTable;

数据修改

1. 修改单表
update  table-name set key1=value1,key2=value2,··· where 筛选条件

2. 修改多表记录 【级联】
update 表1 别名
inner | left | right join 表2 别名
on 连接条件
set 列=值,...
where 筛选条件

删除语句

方式一【单表删除】:
  delete from table-name where 筛选条件 
  
方式二:
	truncate table table-name;
	
	
sql99【删除多表】:
	delete 表1别名，表2别名 from 表1 别名 inner | left | right join 表2 别名 on 连接条件
	where 筛选条件;

delete 和 truncate 的区别

delete 可以加where 条件,truncate 不能加
truncate 删除,效率高一丢丢
加入要删除的表中有自增长列，如果用delete 删除后，再插入数据，自增长列的值从断点开始，而truncate 删除后，再插入数据，自增长列的值从 1 开始

DDL 语言

表和库的管理:
	库: 创建、修改、删除
	表: 创建、修改、删除
	
	创建: create
	修改: alter
	删除: drop

数据库的管理

库的创建

create database  if not exists database-name;


Eg.
	-- 创建数据库 books
  create database  if not exists books;
	-- 查看创建的数据库
  show databases;
	-- 删除该数据库
  drop database  books;

库的修改

1	rename database books to 新库名【8.0 已废弃】

更改库的字符集

1 2	-- 设置 bkks 的字符集为 gbk; alter database books character set gbk;

库的删除

1 2	-- if exists 可以容错，如果不存在不会报错 drop database if exists books;

表的管理

表的创建

create table if not exists tableName
(
 列名 数据类型 约束,
 ...
) charaset set "utf8";

表的修改

修改列名+数据类型

1 2	# 修改列名+数据类型 alter table testtable change column testId testIID int;

修改列的数据类型

1 2	# 修改列的数据类型 alter table testtable modify column testIID int(10);

添加新列

# 添加新列
    
    -- 添加在首行
   		alter table testtable add addTest int first;
    -- 添加在指定位置后面
    	alter table testtable add addTest_enter int after addTest;
    -- 添加在末尾
    	alter table  testtable add endTest int;

删除列

1 2	# 删除列 alter table testtable drop column testColumn;

修改表名

1 2	# 修改表名 alter table test_table rename to testtable;

表的删除

1	drop table if exists tableName;

表的复制

1 2	# 语法； create table tableName like 要拷贝表结构的表源 create table copyTestTable like testtable;

1 2	-- 拷贝表以及数据 create table copy01 select * from testtable;

1 2	-- 只复制部分数据 create table copy2 select addTest_enter from testtable;

1	create table copy3 select addTest_enter from testtable where 0>1;

数据类型

数据类型	tinyint	smallint	meddiumint	int/integer	bigint
所占字节数	1	2	3	4	8

特点
1. 如果不设置无符号还是有符号，默认是有符号,如果想设置无符号,需要添加unsigned 关键字
2. 如果插入的数值超出了整型的范围，会报out of range 异常,并且插入临界值
3. 如果不设置长度,会有默认的长度
长度代表了显示的最大宽度，如果不够用会用0 在左边填充,但必须搭配zerofill 使用

数据类型	float	double
所占字节数	4	8

数据类型	DEC(M,D) \| DECIMAL(M,D)
所占字节数	M+2

特点:
1. M: 整数部位 + 小数部位,D: 小数部位，如果超过范围，则插入临界值
2. M 和D 都可以省略,如果是decimal，则M 默认为10，D 默认为0,如果是float 和double，则会根据插入的数值的精度来决定精度
3. 定点型的精确度较高，如果要求插入数值的精度

较短的文本： char、varchar
较长的文本: text、blob（较大的二进制,如图片等）

数据类型	date	datetime不受时区影响	timestamp受时区影响	time	year
所占字节数	4	8	4	3	1

分类
- date：只保存日期
- time：只保存时间
- year：只保存年
- datetime: 保存日期 + 时间
- timestamp: 保存日期 + 时间

常见约束

约束分类与添加时机

约束分类

not null 非空,用于保证该字段的值不能为空
default 默认 用于保证该字段具有默认值
primary key 主键,用于保证该字段的值具有唯一性,并且非空
unique 唯一 用于保证该字段的值具有唯一性，可以为空
check 检查约束【mysql 中不支持】
foreign key 外键 用于限制两个表的关系，用于保证该字段的值必须来自于主表的关联列的值，在从表添加外键约束，用于引用主表中某列的值

添加时机

添加约束的时机:
	1. 创建表时
	2. 修改表时

约束的添加分类:
	列级约束: 六大约束语法上都支持,但外键约束没有效果
	表级约束: 除了非空、默认、其他都支持

创建表时添加约束

create table addConstraint
(
    id      int primary key,                              # 主键约束
    stuName varchar(10) not null,                         # 非空约束
    gender  varchar(10) check (testCheckk in ("男", "女")), # 检查约束
    seat    varchar(10) unique,                           # 唯一约束
    age     int default 18,                               # 默认约束
    majorId int references major (id)                     # 外键约束
);

create table major
(
    id        int primary key, # 主键约束
    majorName varchar(20)
);


列级约束: 只支 持默认、非空、主键、唯一

创建表时添加表级约束

# 表级约束不支持非空和默认
create table stuinfo
(
    id      int,
    stuname varchar(10),
    gender  char(1),
    seat    int,
    age     int,
    majorid int,
    constraint pk_id primary key (id),                                      # 主键约束
    constraint uq_seat unique (seat),# 唯一约束
    constraint ck_gender check ( gender in ("男", "女") ),                    # 检查约束
    constraint fk_stuinfo_major foreign key (majorid) references major (id) # 外键约束
);

通用写法

create table stuinfo
(
    id      int primary key,
    stuname varchar(10) not null,
    gender  char(1) check ( gender in ("男", "女") ),
    seat    int unique,
    age     int default 18,
    majorid int,
    constraint fk_当前表_引用表 foreign key (majorid) references major (id) # 外键约束
);

主键和唯一的区别

	保证唯一性	是否允许为空	一个表中有多少个	是否允许组合
主键	✔	X	`至多`有`1` 个	✔，但不推荐
唯一	✔	✔	可以有多个	✔，但不推荐

外键特点
1. 要求在从表设置外键关系
2. 从表的外键列的类型和主表的关联列的类型要求一致或兼容，名称无要求
3. 主表的关联列必须是一个key（一般是主键或唯一）
4. 插入数据时，先插入主表，再插入从表，删除数据时，先删除从表，再删除主表

修改表时添加约束


drop table stuinfo;

create table stuinfo
(
    id      int,
    stuname varchar(10),
    seat    int,
    age     int,
    majorid int
);

-- 修改表时添加非空约束
alter table stuinfo modify column stuname varchar(10) not null;

-- 添加默认约束
alter table stuinfo modify column age int default 18;

-- 添加主键(列、表级)
alter table stuinfo modify column id int primary key ;
alter table stuinfo add primary key (id);

-- 唯一约束
alter table stuinfo modify column seat int unique ;
alter table stuinfo add unique (seat);

-- 外键约束
alter table stuinfo add constraint fk_stuinfo_major foreign key (majorid) references major(id);

修改表时删除非空约束

# 删除非空约束
alter table stuinfo modify column stuname varchar(10) null;

# 删除默认约束
alter table stuinfo modify column age int;

# 删除主键
alter table stuinfo drop primary key;

# 删除唯一
alter table stuinfo drop index seat;
# 获取要删除的索引名
show index from stuinfo;


# 删除外键
alter  table stuinfo drop foreign key fk_stuinfo_major;

练习掌握

约束练习

use myemployees;


# 向表 emp2 的 id 列中添加 primary key 约束(my_emp_id_pk)
drop table if exists emp2;
create table if not exists emp2
(
    id      int,
    strName varchar(10),
    constraint my_emp_id_pk primary key (id) # 主键添加名称
);

# 方式一: 默认名称
alter table emp2
    add primary key (id);
# 方式二: 默认名称
alter table emp2
    modify column id int primary key;
# 查看添加的主键信息
show index from emp2;


# 向表 dept2 的 id 列中添加 primary key 约束 (my_dept_id_pk)
create table if not exists dept2
(
    id      int,
    strName varchar(10),
    constraint my_dept_id_pk primary key (id)
);
# 方式一
alter table dept2
    add primary key (id);
# 方式二
alter table dept2
    modify column id int primary key;


# 向表 emp2 中添加列 dept_id，并在其中定义 foreign key 约束,与之相关联的列时 dept2表中的 id 列
alter table emp2
    add column dept_id int;
alter table emp2
    add constraint fk_emp2_dept2 foreign key (dept_id) references dept2 (id);

标识列

基本定义

1
2
3

标识列: 又称为自增长列

含义: 可以不用手动插入值,系统提供默认值的序列值

创建表时添加标识列

create table IdentityColumn
(
    id    int primary key auto_increment, # 创建标识列
    names varchar(10)
);

# 插入测试数据
insert into identitycolumn(names) values ("testValue");

修改标识列的步进值

1
2
3

# 查看并修改标识步进值
show variables like '%auto_incremnt%';
set auto_increment_increment = 3;

特点
1. 标识列必须和主键搭配吗? 不一定,但要求是一个 key
2. 一个表可以有几个标识列? 至多一个!
3. 标识列的类型只能是数值型
4. 标识列可以通过 set auto_increment = 3；设置步长也可以通过手动插入值，设置起始值

修改表时设置标识列

1	alter table tab_identify modify column id int primary key auto_increment;

修改表时删除标识列

1	alter table tab_identify modify column id int primary key;

事务

基本概念

1	一个或一组 sql 语句组成一个执行单元,这个执行单元要么全部执行,要么全部不执行

事务的ACID 属性
1. 原子性(Atomicity): 原子性是指事物是一个不可分割的工作单位、事物的操作要么都发生、要么都不发生
2. 一致性(Consistency): 事物必须使数据库从一个一致性状态变换到另外一个一致性状态
3. 隔离性(Isolation): 事物的隔离性是指一个事物的执行不能被其他事物干扰，即一个事物内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的哥哥十五之间不能互相干扰
4. 持久性(Durability): 持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来的其他操作和数据库故障不应该对其有任何影响
事务的创建
- 隐式事务: 事务没有明显的开启和结束的标记、比如:insert、update、delete 语句
- 显示事务: 事务具有明显的开启和结束的标记，前提: 必须先设置自动提交功能为禁用

关闭事务


show variables like 'autocommit';

set autocommit = 0;

事务使用步骤

步骤一: 开启事务
  set autocommit = 0
  start transaction; [可选的]

步骤二: 编写事务的 sql 语句(select,insert,update,delete)
  语句1;
  语句2;
  ...
 
步骤三: 结束事务
  commit; # 提交事务
  rollback; # 回滚事务

视图

基本含义: 虚拟表，和普通表一样使用
应用场景:
- 多个地方用到同样的查询结果
- 该查询结果使用的sql 语句比较复杂

视图创建语法

1
2
3

create view 视图名
as
查询语句;

视图修改

1	alter view viewName as 查询语句; \| create or replace view viewName as 查询语句

视图删除

1	drop view viewName1,viewName2,···

查看视图结构

1	desc viewName; \| show create view viewName;

具备以下特点的视图不允许更新
1. 包含关键字的sql 语句: 分组函数、distinct、group by、having、union、union all
2. 常量试图
3. select 中包含子查询 join from 一个不能更新的视图 where 子句中的子查询引用了from 子句中的表

视图和表的对比

1
2
3

区别:
 创建关键字不一样 view/table
 都可以进行增删改查(视图一搬不进行增删改)

变量

系统变量

全局变量

会话变量

说明: 变量由系统提供,不是用户定义，属于服务器层面

1. 查看所有系统变量
	show global | session variables;
2. 查看满足条件的部分系统变量
	show global | session variables like '%char%';
3. 查看指定的某个系统变量的值
	select @@global | session.系统变量名;
4. 为某个系统变量赋值
	set global | session 系统变量名 = 值; | set @@global | session.系统变量名 = 值;
	
	全局变量作用域: 服务器每次启动将会为所有的全局变量赋初始值
	
	会话变量作用域: 仅仅针对当前会话(连接)有效

自定义变量

用户变量

用户变量作用域: 针对于当前会话(连接)有效，同于会话变量的作用域

# 声明并初始化
set @用户变量名 = value; | set @用户变量名 := 值; | select @用户变量名 := 值;

局部变量

局部变量作用域: 仅仅在 begin end 中间有效

# 声明
declare 变量名 类型;
# 声明并赋值
declare 变量名 类型 default 值;应用在 begin end 中的第一句话

存储过程和函数

存储过程

含义: 一组预先编译好的SQL 语句的集合，理解成批处理语句
优点:
1. 提高代码的重用性
2. 简化操作
3. 减少了编译次数并且减少了和数据库服务器的连接次数，提高了效率

创建语法

create procedure 存储过程名(参数列表) 
begin
	存储过程体(一组合法的SQL语句)
end;

注意: 
1. 参数列表包含三部分:
		参数模式 参数名 参数类型
举例:
in stuname varchar(20)
参数模式:
	in: 该参数可以作为输出，也就是该参数需要调用方传入值
	out: 该参数可以作为输出，也就是该参数可以作为返回值
	inout: 该参数既可以作为输出又可以作为输入，也就是该参数既需要传入值，又可以返回值
	
2. 如果存储过程体仅仅只有一句话，begin  end 可以省略
存储过程提中的每条 SQL 语句的结尾要求必须添加分号
存储过程的结尾可以使用 delimiter 重新设置

语法: 
		delimiter 结束标记
		Eg. delimiter $

delimiter $
create procedure myp1()
begin
    insert into admin(username, password)
    values ('tom', '1111'),
           ('tom',  '2222'),
           ('Shift','3333'),
           ('Wolf', '4444'),
           ('jake', '5555');
end $;

# 空参的存储过程
call myp1() $;

疑问: 为什么可视化界面创建存储过程无法调用


delimiter $
create procedure myp2(in girlName varchar(10))
begin
    select *
    from boys
             inner join beauty on boys.id = beauty.boyfriend_id
    where name = girlName;
end $;

call myp2('赵敏') $;

delimiter $
create procedure myp3(in username varchar(10), in password varchar(10))
begin
    declare result int default 0; # 声明并初始化
    select count(*)
    into result # 赋值
    from admin
    where admin.username = username
      and admin.password = password;
    select if(result > 0, '成功', '失败'); # 使用变量
end $;

call myp3('join', '8888') $;

# 根据【传入】女神名 返回【传出】对应的男神名
delimiter $
create procedure myp4(in girlName varchar(10), out bodyname varchar(10))
begin
    select boyName
    into bodyname
    from boys
             inner join beauty on boys.id = beauty.boyfriend_id
    where name = girlName;
end $;

# 调用
# 用户自定义变量

call myp4('Angelababy', @bName) $;
select @bName$;

delimiter $
create procedure myp5(in girlname varchar(10), out boyname varchar(10), out usercp int)
begin
    select boys.boyName, boys.userCP
    into boyname,usercp # 变量初始化以及赋值
    from boys
             inner join beauty on boys.id = beauty.boyfriend_id
    where beauty.name = girlname;
end $;
# 调用时可以直接声明在调用，也可以直接调用
call myp5('小昭', @bName, @bCp);
# 查询
select @bName, @bCp $;

# 创建带 inout 模式参数的存储过程
# 传入 a 和 b 两个值,最终 a 和 b 都翻倍并返回

delimiter $
create procedure myp5(inout a int, inout b int)
begin
    # 局部变量
    set a = a * 2;
    set b = b * 2;
end $;

# 自定义变量，名称无所谓
set @num1 = 10 $;
set @num2 = 20 $;

# 传入变量
call myp5(@num1, @num2) $;

数据库数据

SQL查询

基础

注释

单表查询

别名

去重 【 DISTINCT 】

拼接函数 【**concat(str1,str2,···)**】

条件查询

分类

where 条件

数据库常见概念

数据库存储数据的特点

MYSQL的优点

排序查询

常见函数

字符函数

数学函数

日期函数

流程控制

分组函数

分组查询

分组查询练习

添加分组后筛选

按多个字段分组

多表查询 SQL92

等值连接

自连接

多表查询 SQL99

等值连接

左连接

子查询

基本含义

where或having 后面[*]:

列子查询【多行子查询】

select 后面的子查询

子查询练习夯实

分页查询

增删改

插入表数据

数据修改

删除语句

delete 和 truncate 的区别

DDL 语言

数据库的管理

表的管理

常见约束

约束分类与添加时机

练习掌握

标识列

事务

视图

变量

存储过程和函数

存储过程

函数

流程控制结构

顺序结构

分支结构

循环结构

去重【 DISTINCT 】

拼接函数【concat(str1,str2,···)】