kinaba 中根据索引查询拿数据 sql根据索引查询

MySQL学习笔记

• 基础介绍与配置

• 开启服务+进入数据库管理系统

• 语言部分

• 书写规范

• DQL (Data Query Language) :数据查询语言-select

• 1.基础查询
• 2.条件查询where
• 3.排序查询
• 4.单行函数
• 5.分组查询
• 6.连接查询

• sql92连接
• sql99连接【推荐使用】

• 7.子查询
• 8.分页查询（广泛使用）
• 9.联合查询

• DML(Data Manipulate Language):数据操作语言-insert,update,delete

• 插入：insert
• 修改：update
• 删除：delete

• DDL (Data Define Languge）:数据定义语言-create,drop,alter

• 1.库管理+2.表管理
• 2.数据类型
• 3.常见约束

• TCL (Transaction Control Language）:事务控制语言-commit,rollback
• SQL索引

• 1. B+树 数据结构
• 2. 存储引擎
• 3. 没有显示的创建自己的主键的缺点？
• 4.列的离散性：数据重复度太高，索引失效
• 5. 联合索引（最左匹配原则）
• 6.覆盖索引（返回当前索引例的值和主键的值）
• 7.三星索引
• 8.hashsql
• 2.delete,drop

• 知识点总结

基础介绍与配置

视频链接#狂神说sql

查询数据存地方

开启服务+进入数据库管理系统

1–开启本地mysql服务

1. 计算机管理系统
2. 命令行

net stop/start mysqllin

2–拿着密码进入管理系统

用户名root密码123456

退出：exit /ctrl+c

语言部分

书写规范

1. 要先写好select后，group by后的语句，其他的再补齐。group by后的字段，在select后也要出现
2. 要执行的命令用;号结尾，不加；表示没写完，可以换行
3. F9执行使命时，鼠标选中哪些行，哪些行运行，F12规范书写
4. select查询时’字段‘时有三种方式，手写，SELECT *，手写加鼠标点
5. 关键词大写，其他小写 SELECT ’name‘ from table1；
6. select，order by ,having 后可以使用别名，（就是将原来的a的字段as 成b，然后用b进行having）
7. 由于执行顺序的关系，如果为表起了别名，就不是select原表名
8. where a IS NOT NULL
9. select A from employee e,一般别名不加as 了
10. 
    

DQL (Data Query Language) :数据查询语言-select

1.基础查询

#进阶1：基础查询
#只是查看
USE myemployees;
DESC employees;#查看表的所有字段
SELECT hiredate,last_name FROM employees;
SELECT * FROM employees;


##查常量池，select有种print的感觉
SELECT 100;
SELECT "john";#单双引号
SELECT VERSION();
SELECT 100+80 AS 'sum';
SELECT last_name AS '姓','first_name' AS '名' FROM employees;#加不加单双引号都行，如何要是关键字就要加,
SELECT last_name ,first_name AS 姓, 名 FROM employees;#错误的写法
SELECT last_name ,first_name AS 名 FROM employees;#这个正确，只把名赋值
SELECT last_name*12 AS 名 FROM employees;#也是正确，相当将查询后的结果，进行了处理，再输出（而不是搜索0）
SELECT DISTINCT `department_id` FROM employees;#去掉相同的字段

## 加号的使用
SELECT 100+90;#190 sql中的+只表示加运算
SELECT "100"+90=190#先试图将“”转换int，成功就是int，否则就是0
SELECT "hello"+90;#将字符看成0+90=90
SELECT NULL+0;#只要一方为null，结果就是null，在concat中也一样

##concat拼接使用 contact联系的意思
SELECT CONCAT(last_name ,first_name) AS 姓名 FROM employees;

SELECT `commission_pct` FROM employees;
SELECT IFNULL(`commission_pct`,0) AS 奖金率 FROM employees;
SELECT CONCAT(`commission_pct`,',',IFNULL(`commission_pct`,0)) AS 奖金率 FROM employees;#两列输出变并一列

2.条件查询where

#进阶二：条件查询where   ---and or,like % _ ,between 100 and 200 [100,200],in，is null，is not null
SELECT * FROM employees WHERE salary>12000;
SELECT `last_name`,`manager_id` FROM employees WHERE `department_id`!=90;
SELECT CONCAT(`first_name`,'-',`last_name`) AS 姓名,`salary`,`commission_pct` FROM employees WHERE `salary`>10000 AND `salary`<20000;
SELECT `last_name` FROM employees WHERE last_name LIKE '%a%';#%通配符，表示前面后面有任意个
SELECT `last_name` FROM employees WHERE last_name LIKE '__st%';#一个_表示前面只有一个不确定的东西
SELECT `last_name` FROM employees WHERE last_name LIKE '_\_%';#\表示转义
SELECT `salary`,last_name FROM employees WHERE salary IN (10000,20000);#in列表的内容要兼容 in 就是=的意思，列表中不能写'_a',这种是like的写法
SELECT `last_name`,`commission_pct` FROM employees WHERE `commission_pct` IS NULL;#不能写==null，不能识别

3.排序查询

#进阶三：排序查询order by 字段 asc(默认升序，越下越大)/desc
SELECT `last_name`,`salary` FROM employees ORDER BY salary DESC;
SELECT salary*12 AS 年薪 FROM employees ORDER BY 年薪 DESC;
SELECT LENGTH(`last_name`),`last_name`FROM employees ORDER BY LENGTH(`last_name`) DESC;
SELECT * FROM employees ORDER BY salary ASC,`manager_id` ASC;

4.单行函数

##1.字符函数
/*length(" ")返回字节数  
  concat(a,b)
  upper(last_name),lower(first_name)
  SELECT SUBSTR("1234567",5) AS output;#567
  SELECT SUBSTR("12345678",5,2) AS output;#56指定字符长度
  instr:获取子串第一次出现的索引
  trim
  lpad，rpad
  replace
  */
SELECT CONCAT(UPPER(SUBSTRING(last_name,1,1)),"_",LOWER(SUBSTRING(last_name,2))) AS 姓名 FROM employees; 
SELECT INSTR("ssaaddcc","a") AS output;
SELECT TRIM("  我   ") AS output;
SELECT TRIM("a" FROM "aaa我aaa") AS output;#我
SELECT LPAD("我",10,"*")AS output;#*********我 共填充10个
SELECT LPAD("aaaa",2,"*")AS output;#aa
SELECT REPLACE("aabbbbbaa","aa","ww") AS output;#wwbbbbbww

##2.数学函数
/*
ceil:向上取整，返回大于等于该参数的最小整数SELECT CEIL(1.00);=1
floor：向下取整,返回大于等于该参数的最小整数
round：四舍五入round(-1.7)=-2,不管负号，先round，再负号
round(1.346,2)=1.345保留再round
truncate:截断 truncate(1.699,1)=1.6
mod:取模,与10%3一样
rand:获取随机数，返回0-1之间的小数
*/

##3.日期函数
/*
now：返回当前日期+时间2021-08-20 15:56:07    SELECT NOW(); SELECT CURTIME();SELECT CURDATE();

year:返回年       SELECT YEAR(NOW());
month：返回月
day:返回日
hour:小时
minute:分钟
second：秒
datediff: 返回两日期相差天数

date_format:将日期转换成字符
curdate:返回当前日期
str_to_date:将字符转换成日期  SELECT STR_TO_DATE("1998-3-2",'%Y-%c-%d') AS output;#1998-03-02
datediff:返回两个日期相差的天数
monthname:以英文形式返回月*/
SELECT DATE_FORMAT(`hiredate`,'%m月/%d日/%年')FROM employees;#1992-04-03 00:00:00  04月/03日/年

##4.其他系统函数
/*version 当前数据库服务器的版本      select version();
database 当前打开的数据库
user当前用户
password('字符')：返回该字符的密码形式
md5('字符'):返回该字符的md5加密形式*/

##5.流程控制函数
/*
①if(条件表达式，表达式1，表达式2)：如果条件表达式成立，返回表达式1，否则返回表达式2
②case情况1 类似switch用于等值计算

case 变量或表达式或字段
when 常量1 then 值1
when 常量2 then 值2
...
else 值n
end

③case情况2 类似if else 用于条件
case 
when 条件1 then 值1
when 条件2 then 值2
...
else 值n
end*/
SELECT IF(10<5,"大","小");#小

#case情况1
SELECT salary 原始工资,`department_id`,
CASE `department_id`
WHEN 30 THEN salary*1.1
WHEN 40 THEN salary*1.5
ELSE salary
END AS 新工资
FROM employees;

#case情况2 if else 用于条件
SELECT salary 原始工资,salary,
CASE
WHEN salary>20000 THEN 'A'
WHEN salary>15000 THEN 'B'  #条件就是if else，自然是<20000
ELSE 'C'
END AS 工资级别
FROM employees;

## 6.分组函数
/*以下所有分组函数都满足
1.（用作统计使用，又称聚合函数）,之前上面的操作，如salary*1.5会返回每个人（107个id）的信息，这里聚合函数只
返回一个数
2.sum和avg一般用于处理数值型
max、min、count可以处理任何数据类型
3.以上分组函数都忽略null，（只要与null相运算的都会变成null）
4.都可以搭配distinct使用，实现去重的统计
5. count(字段)：统计该字段非空值的个数  count(*)=107:统计结果集的行数，注意的是count(随便字段)=107都可以为107，会自动补
效率上：
MyISAM存储引擎，count(*)最高
InnoDB存储引擎，count(*)和count(1)效率>count(字段)
6.和分组函数一同查询的字段，要求是group by后出现的字段
*/

/*
1、分类
max 最大值
min 最小值
sum 和
avg 平均值
count 计算个数*/
SELECT SUM(salary),MAX(salary) FROM employees;#691400.00 24000.00
SELECT SUM(DISTINCT salary),SUM(salary) FROM employees;
SELECT COUNT(salary) FROM employees;#105
SELECT COUNT(*) FROM employees;#107,如果salary中有null，则不计算行数，所以(*)靠谱

5.分组查询

1. ##注意：group by后的字段，在select后也要出现
2. 要先写好select后，group by后的语句， 其他的再补齐
3. 3，4，5例题重点

# 分组查询
/*
select 分组函数，分组后的字段
from 表
【where 筛选条件】
group by 分组的字段
【having 分组后的筛选】
【order by 排序列表】

##注意：
1.group by后的字段，在select后也要出现
2.要先写好，select后的，group by后的语句，其他的再补齐
3.可以按多个字段分组，字段之间用逗号隔开
4.可以支持排序
5.having后可以支持别名,（就是将原来的a的字段as 成b，然后用b进行having）
二、特点，两者筛选的数据源不一样

		使用关键字		筛选的表	位置
分组前筛选	where			原始表		group by的前面
分组后筛选	having			分组后的结果	group by 的后面
*/

SELECT AVG(salary) ,job_id FROM employees GROUP BY job_id;
SELECT COUNT(*),location_id FROM employees GROUP BY location_id;

#1查询邮箱中包含a字符的，每个部门的平均工资
SELECT AVG(salary) ,`department_id`FROM employees WHERE `email` LIKE '%a%' GROUP BY `department_id`;

#2.分组后--查询哪个部门的员工个数>2
SELECT COUNT(*),department_id FROM employees GROUP BY department_id HAVING COUNT(*)>2;

#3.查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资

#第一步  查询每个工种有奖金的员工的最高工资
SELECT MAX(salary),job_id 
FROM employees 
WHERE `commission_pct` IS NOT NULL 
GROUP BY department_id

#进一步 >12000的工种编号和最高工资
HAVING MAX(salary)>12000;

#4.查询领导编号>102 的每个领导手下的最低工资>5000的领导编号是哪个，以及其最低工资

##第一步，写好基础
SELECT `manager_id`,MIN(`salary`)
FROM employees
GROUP BY `manager_id`;

##第二步，条件
SELECT `manager_id`,MIN(`salary`)
FROM employees 
WHERE `manager_id`>102
GROUP BY `manager_id`;

##第三步，加条件
SELECT `manager_id`,MIN(`salary`)
FROM employees 
WHERE `manager_id`>102
GROUP BY `manager_id`
HAVING MIN(salary)>5000;

##5.每个部门，每个工种的平均工资
SELECT AVG(`salary`),`manager_id`,`job_id`
FROM employees
GROUP BY `manager_id`,`job_id`;

6.连接查询

当使查询两个表时，一定要加连接条件

sql92连接

按年代分类：
	sql92：
		等值
		非等值
		自连接

		也支持一部分外连接（用于oracle、sqlserver，mysql不支持）

一、含义
当查询中涉及到了多个表的字段，需要使用多表连接
select 字段1，字段2
from 表1，表2,...;

笛卡尔乘积：当查询多个表时，没有添加有效的连接条件，导致多个表所有行实现完全连接
如何解决：添加有效的连接条件
1、等值连接
语法：
	select 查询列表
	from 表1 别名,表2 别名
	where 表1.key=表2.key
	【and 筛选条件】
	【group by 分组字段】
	【having 分组后的筛选】
	【order by 排序字段】

特点：
	① 一般为表起别名
	②多表的顺序可以调换
	③n表连接至少需要n-1个连接条件
	④等值连接的结果是多表的交集部分


2、非等值连接
语法：
	select 查询列表
	from 表1 别名,表2 别名
	where 非等值的连接条件
	【and 筛选条件】
	【group by 分组字段】
	【having 分组后的筛选】
	【order by 排序字段】
3、自连接

语法：
	select 查询列表
	from 表 别名1,表 别名2
	where 等值的连接条件
	【and 筛选条件】
	【group by 分组字段】
	【having 分组后的筛选】
	【order by 排序字段】
*/
#1.等值连接
SELECT NAME,boyName FROM boys,beauty
WHERE beauty.`boyfriend_id` =boys.id;

SELECT last_name,e.job_id,job_title
FROM employees e,jobs j
WHERE e.job_id =j.job_id AND e.`commission_pct` IS NOT NULL;

#查询每个城市的部门个数
SELECT COUNT(*), city
FROM departments, locations
WHERE departments.`location_id`=locations.`location_id`
GROUP BY city;

#2.非等值连接
#查询员工工资和工资级别
SELECT salary,grade_level
FROM `employees`,`job_grades` j
WHERE salary BETWEEN  j.`lowest_sal` AND j.`highest_sal`;


#3.自连接
#(将一个表命名成多个，当成不同的表来使用)
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连接【推荐使用】

==
情况1：
select a,b from 表A，表B，当select后的两个字段，来自不同的表时，要使用连接查询
情况2：如下代码块，经典==

#3.查询各部门中工资比本部门平均工资高的员工的员工号, 姓名和工资
#①查询各部门的平均工资
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id

#②连接①结果集和employees表，进行筛选
SELECT employee_id,last_name,salary,e.department_id
FROM employees e
INNER JOIN (
	SELECT AVG(salary) ag,department_id
	FROM employees
	GROUP BY department_id


) ag_dep
ON e.department_id = ag_dep.department_id
WHERE salary>#3.查询各部门中工资比本部门平均工资高的员工的员工号, 姓名和工资
#①查询各部门的平均工资
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id

#②连接①结果集和employees表，进行筛选
SELECT employee_id,last_name,salary,e.department_id
FROM employees e
INNER JOIN (
	SELECT AVG(salary) ag,department_id
	FROM employees
	GROUP BY department_id


) ag_dep
ON e.department_id = ag_dep.department_id
WHERE salary>ag_dep.ag ; ;#这里ag_dep.ag本来是按部门分组得到的结果，可能是10行，但是salary是107行，不好比较，因此需要连接查询，将employees 和后面的进行拼接，这样再salary>ag_dep.ag ，就可以

1. 内连接，查询两个表的交集
2. 外连接，查询一个表有，一个表没有的，两个表的关系是主从(a的某一行，去遍历b的所有，若没有，则a当前行为null ；若有，就是内连接)，所以 外连接，包含内连接
3. 查询哪个，哪个就是主表，查询哪个部门没有员工，所以部门为主表

sql99【推荐使用】
		内连接
			等值
			非等值
			自连接
		外连接
			左外
			右外
			全外（mysql不支持）
		交叉连接

/*
四、SQL99语法

语法：
select 查询列表
from 表1 别名
连接类型 join 表2 别名 
on 连接条件

[连接类型]有
inner       inner可省略
left [outer][outer]可以省略
right[outer]
full [outer]
cross

后追加
where 筛选条件
group by 分组列表
having 分组后的筛选
order by 排序列表
limit 子句;



1、内连接
分类：
等值连接
非等值连接
自连接

特点：
①表的顺序可以调换
②内连接的结果=多表的交集
③n表连接至少需要n-1个连接条件


2、外连接
语法：
select 查询列表
from 表1 别名
left|right|full【outer】 join 表2 别名 on 连接条件
where 筛选条件
group by 分组列表
having 分组后的筛选
order by 排序列表
limit 子句;

特点：
①查询的结果=主表中所有的行，如果从表和它匹配的将显示匹配行，如果从表没有匹配的则显示null

②left join 左边的就是主表，right join 右边的就是主表

③full join 两边都是主表，由三部分组成，交集，a的不同之处，b的不同之处
一般用于查询除了交集部分的剩余的不匹配的行

3、交叉连接
语法：
select 查询列表
from 表1 别名
cross join 表2 别名;

特点：
类似于笛卡尔乘积
左表11，右表4，输出44

*/

#1、内连接
SELECT last_name, department_name
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`#相当于上面两表已经形成了一个大表，然后再与这个相连
WHERE e.`last_name` LIKE "%e%";


#2、外连接
#左外
SELECT b.name, bo.* 
FROM beauty b
LEFT JOIN boys bo
ON b.`boyfriend_id`=bo.`id`#此时boys表已经扩展成与beauty一样行的表
WHERE bo.id IS NULL;

#右外
SELECT b.name, bo.* 
FROM boys bo
RIGHT JOIN beauty b
ON b.`boyfriend_id`=bo.`id`#此时boys表已经扩展成与beauty一样行的表
WHERE bo.id IS NULL;

#全外

#3、交叉连接

7.子查询

1. 子查询看起来比较复杂，分开步骤写，最后进行整合到()中就好
2. 子查询通常放到where，having后，为重点，其他用的少

一、含义
嵌套在其他语句内部的select语句称为子查询或内查询，
外面的语句可以是insert、update、delete、select等，一般select作为外面语句较多
外面如果为select语句，则此语句称为外查询或主查询

特点：

	1、子查询都放在小括号内
	2、子查询可以放在from后面、select后面、where后面、having后面，但一般放在条件的右侧
	3、子查询优先于主查询执行，主查询使用了子查询的执行结果
	4、子查询根据查询结果的行数不同分为以下两类：
	① 单行子查询
		结果集只有一行
		一般搭配单行操作符使用：> < = <> >= <= 
		非法使用子查询的情况：
		a、子查询的结果为一组值
		b、子查询的结果为空
		
	② 多行子查询
		结果集有多行
		一般搭配多行操作符使用：any、all、in、not in
		in： 属于子查询结果中的任意一个就行
		any和all往往可以用其他查询代替，any：比任意一个怎样，可以用min，max代替

二、分类
1、按出现位置
select后面：
		仅仅支持标量子查询
from后面：
		表子查询
where或having后面：         ☆
		标量子查询  ☆
		列子查询    ☆多行一列
		行子查询      多行多列
exists后面：
		标量子查询
		列子查询
		行子查询
		表子查询

2、按结果集的行列
标量子查询（单行子查询）：结果集为一行一列
列子查询（多行子查询）：结果集为多行一列
行子查询：结果集为多行多列
表子查询：结果集为多行多列
*/

三、示例

1. 标量子查询
WHERE或HAVING后面
1、标量子查询
案例：查询最低工资的员工姓名和工资
①最低工资
SELECT MIN(salary) FROM employees

②查询员工的姓名和工资，要求工资=①
SELECT last_name,salary
FROM employees
WHERE salary=(
	SELECT MIN(salary) FROM employees
);

##1.谁的工资比Abel高？
#-a-查询Abel的工资,单行单列
SELECT salary
FROM employees
WHERE last_name ="Abel";

#-b-查询员工，满足条件
SELECT *
FROM employees
WHERE salary>(
	SELECT salary
	FROM employees
	WHERE last_name ="Abel"
);

##2.括号中的子查询()返回是标量
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);
	
##3.查询最低工资大于50号部门最低工资的   部门id，和其最低工资

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
);

2、列子查询
案例：查询所有是领导的员工姓名
①查询所有员工的 manager_id
SELECT manager_id
FROM employees

②查询姓名，employee_id属于①列表的一个
SELECT last_name
FROM employees
WHERE employee_id IN(
	SELECT manager_id
	FROM employees
);

##1.返回location_id是1400 或1700中的部门中  所有员工姓名

SELECT department_id
FROM departments
WHERE location_id=1400 OR location_id=1700

SELECT `last_name`,department_id
FROM employees
WHERE department_id IN (
	SELECT department_id
	FROM departments
	WHERE location_id=1400 OR location_id=1700
);


3.表子查询,WHERE条件苛刻
SELECT *
FROM employees
WHERE (employee_id,salary)=(
	SELECT MIN(employee_id),MAX(salary)
	FROM employees
);

# 放到select中
#查询员工号=102的部门名
SELECT (
	SELECT department_name
	FROM departments d
	INNER JOIN employees e
	ON d.department = e.department_id
	WHERE e.employee_id=102

) ;




三、FROM后面
/*
将子查询结果充当一张表，要求必须起别名
*/

#案例：查询每个部门的平均工资的工资等级
#①查询每个部门的平均工资
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id


SELECT * FROM job_grades;


#②连接①的结果集和job_grades表，筛选条件平均工资 between lowest_sal and highest_sal

SELECT  ag_dep.*,g.`grade_level`
FROM (
	SELECT AVG(salary) ag,department_id
	FROM employees
	GROUP BY department_id
) ag_dep
INNER JOIN job_grades g
ON ag_dep.ag BETWEEN lowest_sal AND highest_sal;




三、EXISTS后面（相关子查询）

SELECT department_name
FROM departments d
WHERE EXISTS(
	SELECT *
	FROM employees e
	WHERE d.`department_id`=e.`department_id`


);


#案例2：查询没有女朋友的男神信息

#in

SELECT bo.*
FROM boys bo
WHERE bo.id NOT IN(
	SELECT boyfriend_id
	FROM beauty
)

#exists
SELECT bo.*
FROM boys bo
WHERE NOT EXISTS(
	SELECT boyfriend_id
	FROM beauty b
	WHERE bo.`id`=b.`boyfriend_id`

);

8.分页查询（广泛使用）

#进阶8：分页查询 ★
/*

应用场景：当要显示的数据，一页显示不全，需要分页提交sql请求,比如先显示10条信息给客户，再显示10条
语法：
	select 查询列表
	from 表
	【join type join 表2
	on 连接条件
	where 筛选条件
	group by 分组字段
	having 分组后的筛选
	order by 排序的字段】
	limit 【offset,】size;
	
	offset要显示条目的起始索引（起始索引从0开始）
	size 要显示的条目个数
特点：
	①limit语句放在查询语句的最后
	②公式
	要显示的页数 page，每页的条目数size
	
	select 查询列表
	from 表
	limit (page-1)*size,size;
	
	size=10
	page  
	1	0
	2  	10
	3	20
	
*/
#案例1：查询前五条员工信息


SELECT * FROM  employees LIMIT 0,5;
SELECT * FROM  employees LIMIT 5;


#案例2：查询第11条——第25条
SELECT * FROM  employees LIMIT 10,15;


#案例3：有奖金的员工信息，并且工资较高的前10名显示出来
SELECT 
    * 
FROM
    employees 
WHERE commission_pct IS NOT NULL 
ORDER BY salary DESC 
LIMIT 10 ;

9.联合查询

#进阶9：联合查询
/*
union 联合 合并：将多条查询语句的结果合并成一个结果

语法：
查询语句1
union
查询语句2
union
...


应用场景：
要查询的结果来自于多个表，且多个表没有直接的连接关系，但查询的信息一致时

特点：★
1、要求多条查询语句的查询列数是一致的！
2、要求多条查询语句的查询的每一列的类型和顺序最好一致
3、union关键字默认去重(相同字段的信息自动去重)，如果使用union all 可以包含重复项

*/


#引入的案例：查询部门编号>90或邮箱包含a的员工信息

SELECT * FROM employees WHERE email LIKE '%a%' OR department_id>90;

SELECT * FROM employees  WHERE email LIKE '%a%'
UNION
SELECT * FROM employees  WHERE department_id>90;


#案例：查询中国用户中男性的信息以及外国用户中年男性的用户信息

SELECT id,cname FROM t_ca WHERE csex='男'
UNION ALL
SELECT t_id,tname FROM t_ua WHERE tGender='male';

DML(Data Manipulate Language):数据操作语言-insert,update,delete

#DML语言
/*
数据操作语言：
插入：insert
修改：update
删除：delete
*/

插入：insert

#一、插入语句
SELECT * FROM beauty;
#1.插入的值的类型要与列的类型一致或兼容
INSERT INTO beauty(id,NAME,sex,borndate,phone,photo,boyfriend_id)
VALUES(13,'唐艺昕','女','1990-4-23','1898888888',NULL,2);

#2.不可以为null的列必须插入值。可以为null的列如何插入值？
INSERT INTO beauty(id,NAME,sex,borndate,phone,photo,boyfriend_id)
VALUES(13,'唐艺昕','女','1990-4-23','1898888888',NULL,2);

#3.列的顺序是否可以调换
INSERT INTO beauty(NAME,sex,id,phone)
VALUES('蒋欣','女',16,'110');

INSERT INTO beauty
VALUES(18,'张飞','男',NULL,'119',NULL,NULL);

#方式二：
/*
语法：
insert into 表名
set 列名=值,列名=值,...
*/


INSERT INTO beauty
SET id=19,NAME='刘涛',phone='999';


#两种方式大pk ★

#1、方式一支持插入多行,方式二不支持

INSERT INTO beauty
VALUES(23,'唐艺昕1','女','1990-4-23','1898888888',NULL,2)
,(24,'唐艺昕2','女','1990-4-23','1898888888',NULL,2)
,(25,'唐艺昕3','女','1990-4-23','1898888888',NULL,2);

#2、方式一支持子查询，方式二不支持

INSERT INTO beauty(id,NAME,phone)
SELECT id,boyname,'1234567'#将子查询的结果，插入到表中
FROM boys WHERE id<3;

修改：update

#二、修改语句 要加where，不然全部修改

/*

1.修改单表的记录★

语法：
update 表名
set 列=新值,列=新值,...
where 筛选条件;


2.修改多表的记录【补充】

语法：
sql92语法：
update 表1 别名,表2 别名
set 列=值,...
where 连接条件
and 筛选条件;

sql99语法：
update 表1 别名
inner|left|right join 表2 别名
on 连接条件
set 列=值,...
where 筛选条件;
*/


#1.修改单表的记录
UPDATE beauty
SET beauty.`phone`='199999998', boyfriend_id=11
WHERE beauty.`name` LIKE '%唐%';



#2.修改多表的记录

#案例 1：修改张无忌的女朋友的手机号为114

UPDATE boys bo
INNER JOIN beauty b ON bo.`id`=b.`boyfriend_id`#先生成大表，再set
SET b.`phone`='119',bo.`userCP`=1000
WHERE bo.`boyName`='张无忌';

删除：delete

#三、删除语句
/*

方式一：delete
语法：

1、单表的删除【★】
delete from 表名 where 筛选条件

2、多表的删除【补充】

sql92语法：
delete 表1的别名,表2的别名
from 表1 别名,表2 别名
where 连接条件
and 筛选条件;

sql99语法：

delete 表1的别名,表2的别名
from 表1 别名
inner|left|right join 表2 别名 on 连接条件
where 筛选条件;



方式二：truncate
语法：truncate table 表名;

*/

#方式一：delete
#1.单表的删除
#案例：删除手机号以9结尾的女神信息

DELETE FROM beauty WHERE phone LIKE '%9';
SELECT * FROM beauty;


#2.多表的删除

#案例：删除张无忌的女朋友的信息

DELETE b
FROM beauty b
INNER JOIN boys bo ON b.`boyfriend_id` = bo.`id`
WHERE bo.`boyName`='张无忌';


#案例：删除黄晓明的信息以及他女朋友的信息
DELETE b,bo
FROM beauty b
INNER JOIN boys bo ON b.`boyfriend_id`=bo.`id`
WHERE bo.`boyName`='黄晓明';



#方式二：truncate语句

#案例：将魅力值>100的男神信息删除
TRUNCATE TABLE boys ;



#delete pk truncate【面试题★】

/*

1.delete 可以加where 条件，truncate不能加

2.truncate删除，效率高一丢丢
3.假如要删除的表中有自增长列，
如果用delete删除后，再插入数据，自增长列的值从断点开始，比如删前表中数据有5个，全删除后，加insert数据，从6开始
而truncate删除后，再插入数据，自增长列的值从1开始。     比如删前表中数据有5个，全删除后，加insert数据，从1开始
4.truncate删除没有返回值，delete删除有返回值

5.truncate删除不能回滚，delete删除可以回滚.

*/

SELECT * FROM boys;

DELETE FROM boys;
TRUNCATE TABLE boys;
INSERT INTO boys (boyname,usercp)
VALUES('张飞',100),('刘备',100),('关云长',100);

DDL (Data Define Languge）:数据定义语言-create,drop,alter

1.库管理+2.表管理

库不能改名字，表可以

#DDL
/*

数据定义语言

库和表的管理

一、库的管理
创建、修改、删除
二、表的管理
创建、修改、删除

创建： create
修改： alter
删除： drop

*/

#一、库的管理
#1、库的创建
/*
语法：
create database  [if not exists]库名;
*/


#案例：创建库Books

CREATE DATABASE IF NOT EXISTS books ;


#2、库的修改

RENAME DATABASE books TO 新库名;

#更改库的字符集

ALTER DATABASE books CHARACTER SET gbk;


#3、库的删除

DROP DATABASE IF EXISTS books;




#二、表的管理
#1.表的创建 ★

/*
语法：
create table 表名(
	列名 列的类型【(长度) 约束】,
	列名 列的类型【(长度) 约束】,
	列名 列的类型【(长度) 约束】,
	...
	列名 列的类型【(长度) 约束】


)


*/
#案例：创建表Book

CREATE TABLE book(
	id INT,#编号
	bName VARCHAR(20),#图书名
	price DOUBLE,#价格
	authorId  INT,#作者编号
	publishDate DATETIME#出版日期

);


DESC book;

#1.创建表author
CREATE TABLE IF NOT EXISTS author(
	id INT,
	au_name VARCHAR(20),
	nation VARCHAR(10)

)
DESC author;


#2.表的修改

/*
语法
alter table 表名
add|drop|modify|change column 列名 【列类型 约束】;

*/

#①修改列名

ALTER TABLE book CHANGE COLUMN publishdate pubDate DATETIME;


#②修改列的类型或约束
ALTER TABLE book MODIFY COLUMN pubdate TIMESTAMP;

#③添加新列
ALTER TABLE author ADD COLUMN annual DOUBLE; 

#④删除列

ALTER TABLE book_author DROP COLUMN  annual;
#⑤修改表名

ALTER TABLE author RENAME TO book_author;

DESC book;




#3.表的删除

DROP TABLE IF EXISTS book_author;

SHOW TABLES;


#4.表的复制

#4.1.仅仅复制表的结构

CREATE TABLE copy LIKE author;

#4.2.复制表的结构+数据
CREATE TABLE copy2 
SELECT * FROM author;

#4.3.只复制部分数据
CREATE TABLE copy3
SELECT id,au_name
FROM author 
WHERE nation='中国';


#4.4.仅仅复制某些字段

CREATE TABLE copy4 
SELECT id,au_name
FROM author
WHERE 0;#不要数据

2.数据类型

3.常见约束

#常见约束

/*
含义：一种限制，用于限制表中的数据，为了保证表中的数据的准确和可靠性


分类：六大约束
	NOT NULL：非空，用于保证该字段的值不能为空
	比如姓名、学号等
	DEFAULT:默认，用于保证该字段有默认值
	比如性别
	PRIMARY KEY:主键，用于保证该字段的值具有唯一性，并且非空
	比如学号、员工编号等
	UNIQUE:唯一，用于保证该字段的值具有唯一性，可以为空
	比如座位号
	CHECK:检查约束【mysql中不支持】
	比如年龄、性别
	FOREIGN KEY:外键，用于限制两个表的关系，用于保证该字段的值必须来自于主表的关联列的值
		在从表添加外键约束，用于引用主表中某列的值
	比如学生表的专业编号，员工表的部门编号，员工表的工种编号
	

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

约束的添加分类：
	列级约束：
		六大约束语法上都支持，但外键约束没有效果
		
	表级约束：
		
		除了非空、默认，其他的都支持
		
		
主键和唯一的大对比：

		保证唯一性  是否允许为空    一个表中可以有多少个   是否允许组合
	主键	√		×		至多有1个           √，但不推荐
	唯一	√		√		可以有多个          √，但不推荐
外键：
	1、要求在从表设置外键关系
	2、从表的外键列的类型和主表的关联列的类型要求一致或兼容，名称无要求
	3、主表的关联列必须是一个key（一般是主键或唯一）
	4、插入数据时，先插入主表，再插入从表
	删除数据时，先删除从表，再删除主表


*/

CREATE TABLE 表名(
	字段名 字段类型 列级约束,
	字段名 字段类型,
	表级约束

)
CREATE DATABASE students;
#一、创建表时添加约束

#1.添加列级约束
/*
语法：

直接在字段名和类型后面追加 约束类型即可。

只支持：默认、非空、主键、唯一



*/

USE students;
DROP TABLE stuinfo;
CREATE TABLE stuinfo(
	id INT PRIMARY KEY,#主键
	stuName VARCHAR(20) NOT NULL UNIQUE,#非空
	gender CHAR(1) CHECK(gender='男' OR gender ='女'),#检查
	seat INT UNIQUE,#唯一
	age INT DEFAULT  18,#默认约束
	majorId INT REFERENCES major(id)#外键

);


CREATE TABLE major(
	id INT PRIMARY KEY,
	majorName VARCHAR(20)
);

#查看stuinfo中的所有索引，包括主键、外键、唯一
SHOW INDEX FROM stuinfo;


#2.添加表级约束
/*

语法：在各个字段的最下面
 【constraint 约束名】 约束类型(字段名) 
*/

DROP TABLE IF EXISTS stuinfo;
CREATE TABLE stuinfo(
	id INT,
	stuname VARCHAR(20),
	gender CHAR(1),
	seat INT,
	age INT,
	majorid INT,
	
	CONSTRAINT pk PRIMARY KEY(id),#主键
	CONSTRAINT uq UNIQUE(seat),#唯一键
	CONSTRAINT ck CHECK(gender ='男' OR gender  = '女'),#检查
	CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id)#外键
	
);





SHOW INDEX FROM stuinfo;



#通用的写法：★

CREATE TABLE IF NOT EXISTS stuinfo(
	id INT PRIMARY KEY,
	stuname VARCHAR(20),
	sex CHAR(1),
	age INT DEFAULT 18,
	seat INT UNIQUE,
	majorid INT,
	CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id)

);



#二、修改表时添加约束

/*
1、添加列级约束
alter table 表名 modify column 字段名 字段类型 新约束;

2、添加表级约束
alter table 表名 add 【constraint 约束名】 约束类型(字段名) 【外键的引用】;


*/
DROP TABLE IF EXISTS stuinfo;
CREATE TABLE stuinfo(
	id INT,
	stuname VARCHAR(20),
	gender CHAR(1),
	seat INT,
	age INT,
	majorid INT
)
DESC stuinfo;
#1.添加非空约束
ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20)  NOT NULL;
#2.添加默认约束
ALTER TABLE stuinfo MODIFY COLUMN age INT DEFAULT 18;
#3.添加主键
#①列级约束
ALTER TABLE stuinfo MODIFY COLUMN id INT PRIMARY KEY;
#②表级约束
ALTER TABLE stuinfo ADD PRIMARY KEY(id);

#4.添加唯一

#①列级约束
ALTER TABLE stuinfo MODIFY COLUMN seat INT UNIQUE;
#②表级约束
ALTER TABLE stuinfo ADD UNIQUE(seat);


#5.添加外键
ALTER TABLE stuinfo ADD CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id); 

#三、修改表时删除约束

#1.删除非空约束
ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NULL;

#2.删除默认约束
ALTER TABLE stuinfo MODIFY COLUMN age INT ;

#3.删除主键
ALTER TABLE stuinfo DROP PRIMARY KEY;

#4.删除唯一
ALTER TABLE stuinfo DROP INDEX seat;

#5.删除外键
ALTER TABLE stuinfo DROP FOREIGN KEY fk_stuinfo_major;

SHOW INDEX FROM stuinfo;

TCL (Transaction Control Language）:事务控制语言-commit,rollback

SQL索引

树的理解索引视频

1. 索引 (Index)是帮助Mysql高效获取数据的数据结构
2. 在RDBMS中，索引存储在硬盘中。
   innodb存储引擎，支持b+tree，hash数据结构
   索引的特点：1，离散型 2.最左匹配原则

1. B+树 数据结构

b树存储关键字+数据区+指针，b+树存储关键字+指针，所以b+树更宽，更矮。

为啥mysql使用b+tree做索引？

1.基于索引的扫库扫表能力强，因为所有的数据都在叶子结点

2.查找数据更稳定，必须要到叶子结点才能找到datat，而b树结点保存关键字+数据区+指针

3.相对 IO读写次数就降低，b+树存储关键字+指针，一次性读入内存的需要查找的关键字也就越多

2.基于索引排序能力强。比如找年龄在18-22岁的人的信息，先用随机查找找到18，再用顺序查到到22，速度极快

2. 存储引擎

Myisam ，索引的结构和数据分别存到两个文件中

InnoDB

InnoDB ，索引的结构和数据只存到一个文件中.ibd

在lnnoDB引擎中，只有主键是聚集索引，其他的索引都是非聚集索引。

非聚集索引返回当前关键字和主键值，利用主键值，去主键索引查找，再返回行记录

3. 没有显示的创建自己的主键的缺点？

因为主键索引的叶子节点挂载行记录，所以要当自己没有创建主键时，mysql会自动创建隐藏主键 _rowid (int 6byte).
1.造成资源浪费 int 6byte
2.会把行锁变成表锁

4.列的离散性：数据重复度太高，索引失效

5. 联合索引（最左匹配原则）

6.覆盖索引（返回当前索引例的值和主键的值）

1. 通过索引项的信息可以直接返回所查询的列，则该索引称为查询SQL的覆盖索引。
2. 尽量使用覆盖索引，因为它能加快我们的查询速度，少了io操作。
   select * from user where name=dafei 回表索引 （少用）
   select name,id from user where name=dafei name索引为 覆盖索引

7.三星索引

8.hashsql

如何解决hash冲突

1. 再散列法（相同hash函数）， p=H(key) 出现冲突时，则以 p 为基础，再次hash， p1=H§。
2. 多重散列（不同hash函数），当 R1=H1(key1) 发生冲突时，再计算 R2=H2(key1)。
3. 链地址法，将哈希值相同的元素构成一个同义词的单链表,并将单链表的头指针存放在哈希
   表的第i个单元中，查找、插入和删除主要在同义词链表中进行。链表法适用于经常进行插入和删除的情况。
4. 建立公共溢出区，将哈希表分为公共表和溢出表，当溢出发生时，将所有溢出数据统一放到溢出区。

1． 内联接和外连接，自联接有什么区别？
内联接通常是 2 个表存在主外键关系时使用的，
内联接查询有 2 种方式实现，
1 是在 WHERE 子句中指定联接条件
2 是在 FROM 子句中使用 join…on
内联接查询通常不仅仅联接 2 表，可以 3 表甚至更多的表
参与内联接的表的地位是平等的

而外联接中参与联接的表有主从之分。 以主表的每行数据去匹配从表的数据列， 符合条件的
数据将直接返回到结果集中，不符合的用 NULL（空值）填充后再返回到结果集中。

2.delete,drop

delete只删除数据不删除表的结构(定义) ,这个操作会放到rollback segement中,可以回滚操作，即可恢复。
truncate只删除数据不删除表的结构(定义)，不能回滚
drop删除表的结构，将表所占用的空间全部释放。

知识点总结

1.基础查询
SELECT CONCAT(last_name ,first_name) AS 姓名 FROM employees;
SELECT DISTINCT `department_id` FROM employees;#去掉相同的字段
2.条件查询where
SELECT `salary`,last_name FROM employees WHERE salary IN (10000,20000);
3.排序查询
SELECT `last_name`,`salary` FROM employees ORDER BY salary DESC;  ASC
4.单行函数
SELECT salary 原始工资,salary,
CASE
WHEN salary>20000 THEN 'A'
WHEN salary>15000 THEN 'B'  #条件就是if else，自然是<20000
ELSE 'C'
END AS 工资级别
FROM employees;
5.分组查询
#1查询邮箱中包含a字符的，每个部门的平均工资,Where分组前筛选
SELECT AVG(salary) ,`department_id`FROM employees WHERE `email` LIKE '%a%' GROUP BY `department_id`;
#2.分组后--查询哪个部门的员工个数>2，having分组后筛选
SELECT COUNT(*),department_id FROM employees GROUP BY department_id HAVING COUNT(*)>2;
6.连接查询
select 查询列表 from 表1 别名 连接类型 join 表2 别名 on 连接条件。
[连接类型]有
inner-内连接     left ，right， full外连接   cross交叉链接（类似于笛卡尔乘积）
SELECT b.name, bo.* FROM beauty b
LEFT JOIN boys bo ON b.`boyfriend_id`=bo.`id`#此时boys表已经扩展成与beauty一样行的表
WHERE bo.id IS NULL;
7.子查询
SELECT MIN(salary),department_id
FROM employees
GROUP BY department_id
HAVING MIN(salary)>(
	SELECT MIN(salary)
	FROM employees
	WHERE department_id =50
);
8.分页查询（广泛使用）
SELECT * FROM  employees LIMIT 0,5;

9.联合查询（多个表没有直接的连接关系）
union 联合合并：将多条查询语句的结果合并成一个结果，查询列数是一致的
SELECT * FROM employees  WHERE email LIKE '%a%'
UNION SELECT * FROM employees  WHERE department_id>90;
10. 数据操作Data Manipulate Language（insert,delete,update）
INSERT INTO beauty(NAME,sex,id,phone) VALUES('蒋欣','女',16,'110'), ('唐艺昕','女',15,2);
#案例：删除张无忌的女朋友的信息
DELETE b FROM beauty b
INNER JOIN boys bo ON b.`boyfriend_id` = bo.`id`
WHERE bo.`boyName`='张无忌';
UPDATE beauty SET beauty.`phone`='199999998'
UPDATE boys bo INNER JOIN beauty b ON bo.`id`=b.`boyfriend_id`#先生成大表，再set
SET b.`phone`='119',bo.`userCP`=1000
WHERE bo.`boyName`='张无忌';
11.表结构操作Data Define Languge( create,drop,alter)
CREATE TABLE IF NOT EXISTS author(id INT,	au_name VARCHAR(20),)
DROP TABLE IF EXISTS book_author;
alter table 表名add|drop|modify|change column 列名 【列类型 约束】;
ALTER TABLE author ADD COLUMN annual DOUBLE;
ALTER TABLE book_author DROP COLUMN  annual;
ALTER TABLE book CHANGE COLUMN publishdate pubDate DATETIME;
ALTER TABLE author RENAME TO book_author;

12.创建索引的三种方式
alter table 表名 add INDEX 索引名(column_list)
create INDEX 索引名 table_name(column_list)
create TABLE table_name(KEY name(first_name, last_name),)
13.数据库的三大范式
	原子性：即确保每一列不可再分。
	唯一性：在满足第一范式的基础上，考虑第二范式，属性完全依赖于主键。
（订单号，产品号）为联合主键，订单时间只与订单相关，so要拆成两个表
	直接性：在满足第二范式的基础上，保证属性不依赖于其它非主属性，属性直接依赖于主键。
学号为主键，班主任姓名直接依赖，班主任年龄就是间接依赖，so要拆成两个表
14.sql数据类型
字符是指计算机中使用的字母、数字、字和符号。 1个汉字字符存储需要2个字节，1个英文字符存储需要1个字节
	数值型
整型(int)
定点数DECIMAL(size,d) d固定的小数点，
浮点数FOLAT(size,d) double(size,d)
	字符型
较短文本char(size) 保存固定长度的字符串varchar(size) 保存可变长度的字符串size是最大
较长文本 text blob
	日期型
DATE() TIME() YEAR()
15.内联接和外连接，自联接有什么区别？ 
内联接通常是 2 个表存在主外键关系时使用的，  
内联接查询有 2 种方式实现，  
1 是在 WHERE 子句中指定联接条件  
2 是在 FROM 子句中使用 join...on  
内联接查询通常不仅仅联接 2 表，可以 3 表甚至更多的表  
参与内联接的表的地位是平等的,而外联接中参与联接的表有主从之分。 以主表的每行数据去匹配从表的数据列，符合条件的
数据将直接返回到结果集中，不符合的用 NULL（空值）填充后再返回到结果集中。 
16.delete,drop
delete只删除数据不删除表的结构(定义) ,这个操作会放到rollback segement中,可以回滚操作，即可恢复。
truncate只删除数据不删除表的结构(定义)，不能回滚
drop删除表的结构，将表所占用的空间全部释放。