尚硅谷mysql复习

1.概述：

2.一对一关系：
3.一对多关系：

4.多对多关系：
5.自我引用：

2.mysql的安装：

1.查看mysql的版本：

方法一：mysql --version
方法二：select version();

2.5.7的mysql安装后会有字符集的问题的解决：

1.查看表的创建信息：show create table 表名词；可以发现使用的是latin1字符集
2.查看数据库的创建信息：show create table 数据库名；发现也是使用的拉丁字符集
3.修改mysql中的my.ini配置文件：


BOM头：

BOM（byte-order mark），即字节顺序标记，它是插入到以UTF-8、UTF16或UTF-32编码Unicode文件开头
的特殊标记，用来识别Unicode文件的编码类型
BOM的作用是告诉文本处理程序，这个文件是用哪种Unicode编码方式保存的，
以及字节的顺序是怎样的
BOM的存在有利有弊。有利的一面是，它可以帮助文本处理程序正确地识别Unicode文件的编码方式，
避免乱码或错误的解释。有弊的一面是，它可能会被一些不支持BOM的程序误认为是普通字符，导致出现多
余的字符或者破坏文件格式

4.重启服务；
5：查看编码命令
show variables like ‘character_%’;
show variables like ‘collation_%’;

collation_开头的系统变量是与排序规则相关的，排序规则是一种规定字符如何比较和排序的规则

alter table student charset utf8; #修改表字符编码为UTF8
 alter table student modify name varchar(20) charset utf8; #修改字段字符编码为UTF8，
#alter table student 表示要对student表进行修改。
#•  modify name varchar(20) 表示要将name列的数据类型修改为varchar(20)，
#即可变长字符串，最大长度为20个字符。
alter database 0728db charset utf8; #修改数据库的字符编码为utf8

root用户密码忘记，重置的操作

基本的select语句：

1.注 释

单行注释：#注释文字(MySQL特有的方式)
单行注释：-- 注释文字(--后面必须包含一个空格。) 
多行注释：/* 注释文字         */

2.数据库或者表命名的时候：如果是使用了系统的保留字体，在使用SQL语句的时候，要对该库或者表用（着重号）引起来; #其中order使用``飘号，因为order和系统关键字或系统函数名等预定义标识符重名了 CREATE TABLE order`(
3.数据导入：
在命令行客户端登录mysql，使用source指令导入：
mysql> source d:\mysqldb.sql；只有命令行可以怎样，图形化界面这个命令不行；图像化界面使用运行sql脚本的方式导入；
4.别名不是一定要使用引号，但是当别名有空格的时候必须要用双引号引起来。
5.在SELECT语句中使用关键字DISTINCT去除重复行

SELECT DISTINCT department_id 
FROM   employees;

DISTINCT 其实是对后面所有列名的组合进行去重；你能看到最后的结果是 74 条，因为这 74 个部
门id不同，都有 salary 这个属性值。如果你想要看都有哪些不同的部门（department_id），只需
要写 DISTINCT department_id 即可，后面不需要再加其他的列名了。
6.空值参与运算
所有运算符或列值遇到null值，运算的结果都为null；空值不等于空字符串。一个空字符串的长度是 0，而一个空值的长 度是空。而且，在 MySQL 里面，空值是占用空间的。
7.SQL 中的 SELECT 语法的确提供了这个功能，一般来说我们只从一个表中查询数据，通常不需要增加一个
固定的常数列，但如果我们想整合不同的数据源，用常数列作为这个表的标记，就需要查询常数。
比如说，我们想对 employees 数据表中的员工姓名进行查询，同时增加一列字段 corporation ，这个
字段固定值为“尚硅谷”，可以这样写：

SELECT '尚硅谷' as corporation, last_name FROM employees;

8.显示表结构：

DESCRIBE employees; 
或
DESC employees;

1.select，from之前的语句后跟的是要查询的列（列的名字常以,分割开来，列运行有空格），也就是后面跟是结果集中的列。

运算符：

1.在Java中，+的左右两边如果有字符串，那么表示字符串的拼接。但是在MySQL中+只表示数
值相加。如果遇到非数值类型，先尝试转成数值，如果转失败，就按0计算。（补充：MySQL
中字符串拼接要使用字符串函数CONCAT()实现）
2.在数学运算中，0不能用作除数，在MySQL中，一个数除以0为NULL。
3.．等号运算符
等号运算符（=）判断等号两边的值、字符串或表达式是否相等，如果相等则返回1，不相等则返回
0。
在使用等号运算符时，遵循如下规则：
如果等号两边的值、字符串或表达式都为字符串，则MySQL会按照字符串进行比较，其比较的
是每个字符串中字符的ANSI编码是否相等。
如果等号两边的值都是整数，则MySQL会按照整数来比较两个值的大小。
如果等号两边的值一个是整数，另一个是字符串，则MySQL会将字符串转化为数字进行比较。
如果等号两边的值、字符串或表达式中有一个为NULL，则比较结果为NULL。
对比：SQL中赋值符号使用 :=
使用安全等于运算符时，两边的操作数的值都为NULL时，返回的结果为1而不是NULL，其他
返回结果与等于运算符相同（与普通等于运算符的区别是：在比较NULL==NULL的时候，普通等于运算符返回结果为NULL,但是安全运算符返回的是1）
4.。不等于运算符不能判断NULL值。如果两边的值有任意一个为NULL，
或两边都为NULL，则结果为NULL。
5.
6.XOR异或运算符号：

XOR是逻辑异或运算符，它用来判断两个操作数是否不同。它的语法规则如下：

•  如果两个操作数都是NULL，那么返回NULL。

•  如果一个操作数是NULL，另一个不是NULL，那么返回NULL。

•  如果两个操作数都不是NULL，那么如果它们的逻辑真假值相异，则返回1，否则返回0。

XOR的作用是用来进行条件判断或者数据加密等场景。例如：

•  如果要查询满足A或B条件，但不能同时满足的数据，可以使用 WHERE A XOR B。

•  如果要对明文和密钥进行异或运算，得到密文，可以使用 text XOR key。

•  如果要对两个变量进行交换值，不需要额外的空间，可以使用
x = x XOR y; y = x XOR y; x = x XOR y;。

时间：

MySQL学习笔记：count(1)、count(*)、count（字段）的区别 - Hider1214 - 博客园
【MySQL】连接查询 以及 on、where、Having的区别 - 至安 - 博客园

子查询：

子查询（内查询）在主查询之前一次执行完成。
- 子查询的结果被主查询（外查询）使用 。
- 注意事项
  - 子查询要包含在括号内
  - 将子查询放在比较条件的右侧
  - 单行操作符对应单行子查询，多行操作符对应多行子查询

3. 子查询的分类
角度1：从内查询返回的结果的条目数
   单行子查询  vs  多行子查询

角度2：内查询是否被执行多次
   相关子查询  vs  不相关子查询，一般相关子查询返回的是多行数据，而不相干子查询返回的是一行数据

   
 比如：相关子查询的需求：查询工资大于本部门平均工资的员工信息。
       不相关子查询的需求：查询工资大于本公司平均工资的员工信息。
 
*/
#子查询的编写技巧（或步骤）：① 从里往外写  ② 从外往里写

#4. 单行子查询
#4.1 单行操作符： =  !=  >   >=  <  <= 
子查询返回值的结果为空的时候，子查询不返回任何行，相应的外查询也不返回任何行。
查询某一行为空的条件的写法：where xxx IS NULL而不是xxx=NULL;

SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) = (
			SELECT MIN(avg_sal)
			FROM(
				SELECT AVG(salary) avg_sal
				FROM employees
				GROUP BY department_id
				) t_dept_avg_sal
			);
#上面的t_dept_avg_sal是子查询结果集表的别名
#
SELECT department_id
FROM employees
where salary = (
			SELECT MIN(avg_sal)
			FROM(
				SELECT AVG(salary) avg_sal
				FROM employees
				GROUP BY department_id
				) t_dept_avg_sal
			);
#这个查询和上面那个查询的区别：子查询是样的，都是返回部门平均工资最低的平均工资，下面这个是拿原先中的表的所有行去去和子查询
# 的结果对比，而上面那个是把原先中的行进行分组后，在计算每组的AVG，生成新列AVG(salary)和新的结果集，然后让AVG和子查询的结果比较。
#后面的查询是大于最低部门平均工资的部门的员工他所属的部门ID

1.子查询使用到了外表的数据
2.执行步骤：a.先把外边的要查询的一条数据放进子查询中；b.然后根据子查询的条件决定是否返回该行和返回
什么
c.重复上述步骤

3.在SELECT中，除了GROUP BY 和 LIMIT之外，其他位置都可以声明子查询！

数据类型：

MySQL 数据类型之浮点

代码运行 （MySQL）：

create table t3 (d1 decimal(5,2),d2 double(5,2),d3 float(5,3),d4 double, d5 float,
d6 decimal)
insert into t3
values
(23.432,23.432,23.432,23.432,23.432,23.432)
select * from t3;

运行结果：
23.43(后面变成两位小数) 23.43（后面变成两位小数）23.432 （浮点，后面三位）
23.432（double 不变），23.432（float 不变），23 （decimal 没有设定小数点，
直接没有小数点后面的数值了）

IFNULL(note,'合计总量')：使用 IFNULL 函数，如果 note 字段为 NULL，将其替换为 '合计总量'。
WITH ROLLUP 子句生成包括汇总行的结果，就是新增一行，这一行是统计该列的和，with pollup不能和order
by 同时使用
SELECT IFNULL(note,'合计总量') AS note,COUNT(*)
FROM books
GROUP BY note WITH ROLLUP;

枚举：
约束：

/*
1. 基础知识
1.1 为什么需要约束？ 为了保证数据的完整性！

1.2 什么叫约束？对表中字段的限制。

1.3 约束的分类：

角度1：约束的字段的个数
单列约束 vs 多列约束

角度2：约束的作用范围

列级约束：将此约束声明在对应字段的后面
表级约束：在表中所有字段都声明完，在所有字段的后面声明的约束

角度3：约束的作用（或功能）

① not null (非空约束)
② unique  (唯一性约束)
③ primary key (主键约束)
④ foreign key (外键约束)
⑤ check (检查约束)
⑥ default (默认值约束)

1.4 如何添加/删除约束？

CREATE TABLE时添加约束

ALTER TABLE 时增加约束、删除约束


*/
可以向声明为unique的字段上添加null值。而且可以多次添加null
#4.4 删除唯一性约束
-- 添加唯一性约束的列上也会自动创建唯一索引。
-- 删除唯一约束只能通过删除唯一索引的方式删除。
-- 删除时需要指定唯一索引名，唯一索引名就和唯一约束名一样。
-- 如果创建唯一约束时未指定名称，如果是单列，就默认和列名相同；如果是组合列，那么默认和()中排在第一个的列名相同。也可以自定义唯一性约束名。
# 主键约束特征：非空且唯一，用于唯一的标识表中的一条记录。
#MySQL的主键名总是PRIMARY，就算自己命名了主键约束名也没用。
#如果是多列组合的复合主键约束，那么这些列都不允许为空值，并且组合的值不允许重复。
#开发中，一旦主键作用的字段上声明有AUTO_INCREMENT，则我们在添加数据时，就不要给主键
#对应的字段去赋值了。
#当我们向主键（含AUTO_INCREMENT）的字段上添加0 或 null时，实际上会自动的往上添加指定的字段的数值
#7.4 ###  约束等级

-- `Cascade方式`：在父表上update/delete记录时，同步update/delete掉子表的匹配记录 

-- `Set null方式`：在父表上update/delete记录时，将子表上匹配记录的列设为null，但是要注意子表的外键列不能为not null  

-- `No action方式`：如果子表中有匹配的记录，则不允许对父表对应候选键进行update/delete操作  

-- `Restrict方式`：同no action， 都是立即检查外键约束

-- `Set default方式`（在可视化工具SQLyog中可能显示空白）：父表有变更时，子表将外键列设置成一个默认的值，但Innodb不能识别
结论：对于外键约束，最好是采用: `ON UPDATE CASCADE ON DELETE RESTRICT` 的方式。
# MySQL5.7 不支持CHECK约束，MySQL8.0支持CHECK约束。

视图：

/*
1. 视图的理解

① 视图，可以看做是一个虚拟表，本身是不存储数据的。
  视图的本质，就可以看做是存储起来的SELECT语句
  
② 视图中SELECT语句中涉及到的表，称为基表

③ 针对视图做DML操作，会影响到对应的基表中的数据。反之亦然。

④ 视图本身的删除，不会导致基表中数据的删除。

⑤ 视图的应用场景：针对于小型项目，不推荐使用视图。针对于大型项目，可以考虑使用视图。

⑥ 视图的优点：简化查询; 控制数据的访问


*/

2个@@是系统变量，一个@是用户变量

/*
① 用户变量 ： 会话用户变量 vs 局部变量

② 会话用户变量：使用"@"开头，作用域为当前会话。

③ 局部变量：只能使用在存储过程和存储函数中的。

*/
#1.6 会话用户变量
/*
① 变量的声明和赋值：
#方式1：“=”或“:=”
SET @用户变量 = 值;
SET @用户变量 := 值;

#方式2：“:=” 或 INTO关键字
SELECT @用户变量 := 表达式 [FROM 等子句];
SELECT 表达式 INTO @用户变量  [FROM 等子句];

② 使用
SELECT @变量名
/*
1、局部变量必须满足：
① 使用DECLARE声明 
② 声明并使用在BEGIN ... END 中 （使用在存储过程、函数中）
③ DECLARE的方式声明的局部变量必须声明在BEGIN中的首行的位置。

2、声明格式：
DECLARE 变量名 类型 [default 值];  # 如果没有DEFAULT子句，初始值为NULL

3、赋值：
方式1：
SET 变量名=值;
SET 变量名:=值;

方式2：
SELECT 字段名或表达式 INTO 变量名 FROM 表;

4、使用
SELECT 局部变量名;
*/

mysql远程：

这个输出的意思是，firewalld.service这个服务被屏蔽了，所以无法启动或运行。屏蔽一个服务的目的是阻止它被其他服务或用户启动。如果你想要使用firewalld.service这个服务，你需要先解除屏蔽，然后启用和启动它。后面一个命令的意思是：输出的意思是，firewalld服务没有运行，所以你无法查看它的配置。

数据目录：

1.


（默认大小是12M，，5.7版本表的内容数据存放在“表.ibd”当中了，表.ibd”可以叫做独立表空间。
（8.0后的mysql每个表只有一个表文件，将前面的.frm和ibd文件合并了）

用户权限：

（该用户的权限有限，只能查看该数据库）



(把manager角色赋予给wang5)

(current_role() is a function that returns the name of the primary role in use for the current session;)

配置文件的使用和系统环境变量：

这里的意思是使用前面的启动命令可以读取到配置文件中的哪些组，例子：上面的mysqld启动命令读取的是配置文件中[mysqld]和[server]组中的配置信息。

设置系统变量：

逻辑架构：

（5.7的运行顺序图）

1.Connectors:

第一层：连接层

服务层：

当第二次查询的内容和第一次是一样的，那么可以直接拿取Caches&Buffers中之前缓存的结果。

引擎层：

存储层：

查询缓存（8.0已经抛弃了）：

解析器：

优化器：

执行器：

MySQL8的执行原理：

存储引擎：

索引：

1.为什么要用索引？

索引的优缺点：

概述：

InnODB索引：

（当数据大的时候，存储就会按照页来存储，一个数据页的数据大小默认是16KB；以主键为搜索条件使用二分法查找的条件是主键是按自增的顺序储存在表中的； 上面的最小记录是说的主键的最小记录，也就是第一条记录；多条记录在物理上不一定是连续的，但是在逻辑上是连续的；）

对于使用InnoDB存储引擎的表：
InnoDB使用聚集索引的方式来组织数据，主键的顺序决定了数据在磁盘上的存储顺序。
当你插入新数据时，如果新插入的数据的主键值小于上一个插入的数据，那么在磁盘上，新数据的存储位置将在上一个插入的数据之前。
在链表结构中，上一个插入数据的下一个地址将是新插入数据的地址。

（添加数据的时候一定会保证主键是自增的，如果新添加的数据校园前面的，会让前面的换）

mysql数据结构选择的合理性：

InnoDB的数据存储结构：

页的内部结构：