第一章
1、MySql字符集介绍
ASSCII，unicode，UTF-16，UTF-8
2、MySql搜索引擎
MYISAM、INNODB、MEMORY
MYISAM：不支持事务，不支持外键，但访问速度快，对事物完整性要求不高或一SELECT和INSERT为主的的应用采用这个引擎
INNODB：支持事务和外键，提供了提交，回滚和崩溃恢复能力的事务安全。对比MYISAM，INNODB的写的效率要差一些，同时要占用更多的磁盘空间来保存数据和索引
MEMORY：该引擎将数据保存到内存当中，访问速度快，默认使用Hash索引；一旦数据库服务器关闭，数据就会消失；但是数据表结构依然会存在，因为该引擎会在磁盘上创建.frm文件保存数据表结构
第二章：SQL
1、SQL功能划分
DDL(数据定义语言)：用来定义数据库对象；创建库，表，列等
DML(数据操作语言)：用来操作数据库表中的记录
DQL(数据查询语言)：用来查询数据
DCL(数据控制语言)：用来定义访问权限和安全权限
2、SQL数据类型
MySQL支持所有标准SQL数值数据类型
MySQL支持多种类型，大致可以分为三类：数值类型、字符串类型、日期和时间类型
在MySQL中，字符串类型和日期类型都要用单引号括起来。‘Myxq’，’2020-01-02‘
常用的数据类型：
- double：浮点型，例如double(5, 2)
- char：固定长度字符串类型；char(10)
- varchar：可变长度符号类型
- text：字符串类型
- blob：二进制类型
- date：日期类型，格式为：yyyy-MM-dd；
- time：时间类型，格式为：hh:mm:ss
- datetime：日期时间类型 ，格式为：yyyy-MM-dd hh:mm:ss
3、DDL(数据定义语言)
-
创建数据库
create database 数据库名称 character set utf8;
-
修改数据库
-
创建学生表
CREATE TABLE 表名(
列名1 列的类型 [约束],
列名2 列名类型 [约束],
…
列名N 列类型 [约束]
);
-
添加一列
ALTER TABLE 表名 ADD 列名 数据类型;
-
查看表的字段信息
DESC 表名
-
修改一个表的字段类型
ALTER TABLE 表名 MODIFY 字段名 数据类型;
-
删除一列
ALTER TABLE 表名 DROP 字段名;
-
修改表名
RENAME TABLE 原始表名 TO 新表名;
-
查看表的创建细节
SHOW CREATE TABLE 表名;
-
修改表的字符集为gbk
ALTER TABLE 表名 character set gbk;
-
修改表的列名
ALTER TABLE 表名 CHANGE 原始列名 新列名 数据类型;
-
删除表
DROP TABLE 表名;
4、DML(数据操作语言)
查询表中的所有数据：SELECT (*) FROM 表名;
DML是对表中的数据进行增、删、改的操作
-
插入操作
-
INSERT INTO 表名 (列名1, 列名2…) VALUES(列值1, 列值2…);
-
注意事项：
1、列名与列值的类型、个数、顺序要一一对应
2、值不要超出列定义的长度
3、插入的日期和字符一样，都要用单引号括起来
-
批量插入：INSERT INTO 表名 (列名1, 列名2…) VALUES(列值1, 列值2…),
(列值1, 列值2…),…;
-
-
更新操作
UPDATE 表名 SET 列名1=列值1, 列名2=列值2, … WHERE 列名=列值
-
删除操作
-
DELETE FROM 表名 [WHERE 列名=值]
-
TRUNCATE TABLE 表名
-
DELETE与TURNCATE的区别
-
DELETE删除表中的数据，表结构还在；删除后的数据可以找回
-
TRUNCATE删除是把表直接DROP掉，然后再创建一个同样的新表，删除的数据不能找回。执行速度比DELETE快。
-
-
5、DQL(数据查询语言)
-
查询所有列
SELECT * FROM 表名;
-
结果集
- 数据库执行DQL语句不会对数据进行改变，而是让数据库发送结果集给客户端。
- 结果集
- 通过查询语句查询出来的数据以表的形式展示我们称这个表为虚拟结果表。存放在内存中。
- 查询返回的结果集是一张虚拟表
-
查询指定列的数据
SELECT 列名1, 列名2,… FROM 表名
-
条件查询
条件查询就是在查询时给出WHERE，在WHERE子句中可以使用一些运算符和关键字；
-
模糊查询
根据指定的关键字进行查询
使用LIKE关键字后跟通配符
通配符：1、_：任意一个字符
2、%：任意0~n个字符
-
字段控制查询
-
去除重复记录：
SELECT DISTINCT name FROM student;
-
把查询字段的结果进行运算，必须都要是数据类型
-
*SELECT , 字段1+字段2 FROM 表名;
-
列有很多记录的值为NULL，
因为任何东西与NULL相加结果还是NULL，所以运算结果可能会出现NULL
下面使用了把NULL转换为数值0的函数IFNULL
*SELECT , age+IFNULL(score, 0) FROM student;
-
对查询结果起别名
-
在上面查询中出现列名为sex+IFNULL(score, 0)，这很不美观，现在我们给这一列一个别名
-
*SELECT , sex+IFNULL(score, 0) AS total FROM student;
-
省略AS
*SELECT , sex+IFNULL(score, 0) total FROM student;
-
-
-
-
排序
ORDER BY
**ASC：**升序(默认值)
**DESC：**降序
-
聚合函数
对查询结果进行统计
- COUNT()：统计指定列不为NULL的记录行数；
- **MAX()：**计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算；
- **MIN()：**计算指定列的最小值，如果指定列是字符串，那么按照字符串排序；
- **SUM()：**计算指定列的数值和，如果指定列类型不为数值类型，那么计算结果为0；
- **AVG()：**计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为0；
-
分组查询
- 将查询结果按照1个或多个字段进行分组，字段相同的为一组
- 注意事项
- SELECT gender FROM employee GROUP BY gender;
- 根据gender字段来分组，gender字段的全部值只有两个('男’和’女‘)，所以分为了两组
- 当 GROUP BY单独使用时，只显示出每组的第一条记录
- 所以GROUP BY单独使用时意义不大
-
分组注意事项：在使用分组时，select后面直接跟的字段一般都出现在GROUP BY
-
GROUP BY + group_concat()
- **group_concat(字段名)**可以作为一个输出字段来使用
- 表示分组之后，根据分组结果，使用**group_concat()**来放置每一组的某字段的值的集
- SELECT gender, GROUP_CONCAT(name) FROM employee GROUP BY gender
-
group by + having
- 用来分组查询后指定一些条件来输出查询结果
- having作用和where一样，但having只能用于group by
- having与where的区别
- having是在分组后对数据进行过滤
- where是在分组前对数据进行过滤
- having后面可以使用分组函数(统计函数)
- where后面不可以使用分组函数
- WHERE是对分组前记录的条件，如果某行记录没有满足WHERE子句的条件，那么这行记录不会参加分组；而HAVING是对分组后数据的约束。
-
书写顺序
-
执行顺序
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-3ieMJKrh-1624192483304)(E:\学习笔记\DataBase\images\执行顺序.png)]
-
LIMIT
-
从哪一行开始查，总共要查几行
-
Limit 参数1, 参数2
-
-
参数1：从哪一行开始查
- 一共要查几行
-
角标从0开始
-
格式：SELECT * FROM 表名 limit 0,3;
-
分页思路
-
第三章：数据的完整性
1、概念及分类
**什么是数据的完整性：**保证用户输入的数据保存到数据库中是正确的
**如何添加数据完整性：**在创建表时给表中添加约束
完整性分类：实体完整性、域完整性、引用完整性(参照完整性)
2、实体完整性
1、什么是实体完整性
表中的一行(一条记录)代表一个实体(entity)
2、实体完整性的作用
标识每一行数据不重复。行级约束
3、约束类型
- 主键约束(primary key)
- 唯一约束(unique)
- 自动增长列(auto_increment)
4、主键约束
1、特点
- 每个表要有一个主键
- 数据唯一，且不能为null
2、添加方式
-
CREATE TABLE 表名(字段名1 数据类型 primary key, 字段2 数据类型)
-
CREATE TABLE 表名(字段1 数据类型, 字段2 数据类型, primary key(主键1, 主键2))
-
联合主键：两个字段数据同时相同时，才违反联合主键约束。
-
先创建表
再去修改表，添加主键
ALTER TABLE student ADD CONSTRAINT PRIMARY KEY(id);
5、唯一约束
1、特点
- 指定列的数据不能重复
- 可以为空值
2、格式
CREATE TABLE 表名(字段名1 数据类型, 字段2 数据类型 UNIQUE);
6、自动增长列
1、特点
- 指定列的数据自动增长
- 即使数据删除，还是从删除的序号继续往下
2、格式
CREATE TABLE 表名(字段名1 数据类型 PRIMARY KEY AUTO_INCREMENT, 字段2 数据类型 UNIQUE);
3、域完整性
1、使用
- 限制此单元格的数据正确，不对照此列的其他单元格比较
- 域代表当前单元格
2、域完整性约束
-
数据类型：数值类型、日期类型、字符串类型
-
非空约束(not null)：CREATE TABLE 表名(字段名1 数据类型 PRIMARY KEY AUTO_INCREMENT, 字段2 数据类型 UNIQUE not null);
-
默认值约束(default)：CREATE TABLE 表名(字段名1 数据类型 PRIMARY KEY AUTO_INCREMENT, 字段2 数据类型 UNIQUE not all default ‘男’);
插入的时候，deafult当中的值直接给value
-
-
4、参照完整性
-
什么是参照完整性
- 是指表与表之间的一种对应关系
- 通常情况下可以通过设置两表之间的主键外键关系，或者编写两表的触发器来实现
- 有对应参照完整性的两张表格，在对他们进行数据插入、更新、删除的过程中，系统都会将被修改的表格与另一张表格进行对照，从而阻止一些不正确的数据操作。
-
数据库的主键和外键类型一定要一致
-
两个表的引擎必须要是InnoDB类型
-
设置参照完整性后，外键当中的内值，必须是主键当中的内容
-
一个表设置当中的字段设置为主键，设置主键的为主表
CREATE TABLE student(sid int PRIMARY KEY, name varchar(50) not null, sex varchar(10) default ‘男’);
-
创建表时，设置外键，设置外键的为子表
CREATE TABLE score(
sid int,
score double,
CONSTRAINT fk_stu_score_sid FOREGIN KEY(sid) REFERENCES student(sid))
第四章：多表查询
1、表之间关系
一对一、一对多、多对多
2、多表查询
1、合并结果集
-
合并结果集就是把两个select语句的查询结果合并到一起
-
合并结果集的两种方式
- **UNION：**合并时去除重复记录
- **UNION ALL：**合并时不去除重复记录
-
格式：
SELECT * FROM 表一 UNION SELECT * FROM 表二
**SELECT * FROM 表一 UNION ALL SELECT * FROM 表二 **
-
注意事项
被合并的两个结果：列数、列类型必须相同
2、连接查询
-
连接查询也叫跨表查询，需要关联多个表进行查询
-
笛卡尔积
-
同时查询两个表，出现的就是笛卡尔集结果
-
查询时给表起别名
SELECT * FROM stu st, score sc;
-
多表联查，如何保证数据正确
-
在查询时要把主键和外键保持一致
-
主表当中的数据参照子表当中的数据
-
-
根据连接方式分类
-
内连接
-
等值连接
-
两个表同时出现的id号(值)才显示
-
SELECT FROM stu st INNER JOIN score sc ON st.id=sc.id
-
与多表联查约束主外键是一样，知识写法改变了
-
ON后面只写主外键
-
如果还有条件直接在后面写WHERE
SELECT st.name, sc.score, sc.km FROM stu st INNER JOIN score sc ON st.id=sc.sid WHERE score>=70;
-
多表联查后面还有条件直接就写AND
-
-
多表连接
-
建立学生，分数，科目表
-
使用99连接法
-
使用内联查询
-
-
非等值连接
-
自连接
- 自己连接自己，起别名
-
-
-
外连接
-
左外连接(左连接)
- 两表满足条件相同的数据查出来，如果左边表当中有不同的数据，也把左边表当中的数据查出来
- 左边表当中的数据全部查出，右边表当中，只查出满足条件的内容
- 格式：[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-u2WElTqR-1624192483309)(E:\学习笔记\DataBase\images\左外连接例子.png)]
-
右外连接(右连接)
-
右连接会把右表当中的数据全部查出，左表当中只查出满足条件的数据
-
使用右连接，就把右表当中的数据全部查出，左表查出满足条件的数据。
-
格式：
-
-
全连接
- 全连接会把两个表中的数据全部查出。内联运算结束之后，左侧表中不匹配右侧表中任何元组的元组被添加上空值并添加到结果中；右表操作类似
- MySQL不支持全连接，可使用UNION连接左右外连接的查询结果
- 格式：SELECT st.* , sc.* FROM student st FULL JOIN score sc ON st.id = sc.sid;
-
-
自然连接
- 连接查询会产生无用笛卡尔集，我们通常使用主外键关系等式来去除它。
- 而自然连接无需你去给出主外键等式，它会自动找到这一等式也就是不用去写条件
- 要求：两张连接的表中列名称和类型完全一致的作为条件会去除相同的列
- 格式：SELECT * FROM student NATURAL JOIN teacher;*
第五章：子查询
1、定义
一个SELECT语句中包含另一个完整的SELECT语句。或两个以上SELECT，那么就是子查询语句。
2、出现的位置
- WHERE后，把SELECT查询出来的结果当作另一个SELECT条件值
- FROM后，把查询出的结果当作一个新表。
第六章：常用函数
1.函数介绍
事先提供好的一些功能可以直接使用
函数可以用SELECT语句及其子句
也可以用在UPDATE、DELETE语句当中。
2、函数分类
字符串函数、数值函数、日期和时间函数、流程函数、其他函数
3、字符串函数
-
concat(s1, s2…sn)
-
传入的字符串连接成一个字符串
-
任何字符串与null进行连接结果都是null
-
-
insert(str, x, y, instr)：将字符串str从x位置开始，y个字符长的子串替换为指定的字符
-
**LOWER(Str)和UPPER(Str)：**将字符串转成小写或大写
-
LEFT(str, x)和RIGHT(str, x)
-
分别返回字符串最左边的x个字符和最右边的x个字符
-
如果第二个参数为null，那么不返回任何字符
-
-
LPAD(str, n, pad)和RPAD(str, n, pad)：用字符串pad对str最左边或最右边进行填充，直接到长度为n个字符长度
-
**LTRIM(str)和RTRIM(str)：**去掉字符串当中最左侧和最右侧的空格
-
**TRIM(str)：**去掉字符串左右的空格
-
REPEAT(str, x)：返回str重复x次的结果
-
REPLACE(str, a, b)：用字符串b替换字符串str中所有出现的字符串a
-
SUBSTRING(str, x, y)：返回字符串str中第x位置起y个字符长度的字符串
4、数值函数
-
ABS(x)：返回X的绝对值
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-jkwn7r3B-1624192483317)(http://image.onemechanic.top/ABS(X)].png)
-
**CEIL(x)：**小数不为零部分上取整，即向上取最近的整数
-
**FLOOR(x)：**小数部分下取整，即向下去最近的整数
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-R1dvnuRS-1624192483319)(e:%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0\DataBase\images\Floor.png)]
-
MOD(x, y)：返回X/Y的模
-
**RAND()：**返回0-1内容的随机值
5、日期和时间函数
-
**CURDATE()：**返回当前日期，只包含年月日
-
**CURTIME()：**返回当前时间，只包含时分秒
-
**NOW()：**返回当前日期和时间，年月日时分秒都包含
-
**UNIX_TIMESTAMP()：**返回当前日期的时间戳
-
**FROM_UNIXTIME(unixtime)：**将一个时间戳换成日期
-
**WEEK(DATE)：**返回当前是一年中的第几周
-
**YEAR(DATE)：**返回所给日期是哪一年
-
**HOUR(TIME)：**返回当前时间的小时
-
**MINUTE(TIME)：**返回当前时间的分钟
-
DATE_FORMAT(date, fmt)：按字符串格式化日期date值
-
**DATE_ADD(date, interval expr type)：**计算时间间隔
-
**DATEDIFF(date1, date2)：**计算两个日期相差的天数
6、流程函数
-
IF(value, t, f)：如果value是真，返回t，否则返回f
-
IFNULL(value1, value2)：如果value1不为空，返回value1，否则返回value2
-
CASE WHEN THEN END*
7、其他函数
-
**DATABASE()：**返回当前数据库名
- **VERSION()：**返回当前数据版本
- **USER()：**返回当前登录用户名
- PASSWORD(STR)：对STR进行加密
- MD5(str)：返回STR的MD5值
第七章：事务
1、什么是事务
-
不可分割的操作，假设该操作有ABCD四个步骤组成：
- 若ABCD四个步骤都成功完成，则认为事务成功。
- 若ABCD中任意一个步骤操作失败，则认为十五失败
-
每条SQL语句都是一个事务
-
事务只对DML语句有效，对于DQL语句无效
2、事务的ACID
1、原子性(Atomicity)
原子性是指事务包含的所有操作要么全部成功，要么全部失败回滚
2、一致性(Consistency)
- 一致性是指事务必须是数据库从一个一致性状态变换到另一个一致性状态，也就是说一个事务执行之前和执行之后都必须处于一致性状态
- 让数据保持一定上的合理
- 一个商品出库时，仓库商品数量减1，对应用户的购物车中商品加1
3、隔离性(Isolation)
隔离性是当多个用户并发访问数据库时，比如操作同一张表时，数据库为每一个用户开启事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离
4、持久性(Durability)
持久性是指一个事务一旦被提交了，就不能在回滚了，已经把数据保存到数据库中了。
3、事务的使用
-
提交事务：
START TRANSACTION
-
提交事务：
COMMIT
所有语句全部执行完毕，没有发生异常，提交事务，更新到数据库中
-
回滚事务：
ROLLBACK
当遇到一突发情况，撤销执行的SQL语句
4、执行流程
5、事务的并发问题
1、脏读
老板要给程序员发工资，程序员的工资是3.6万/月。但是发工资时老板不小心按错了数字，按成3.9万/月，该钱已经打到程序员的户口，但是事务还没有提交，就在这时，程序员去查看自己这个月的工资，发现比往常多了3千元，以为涨工资了非常高兴。但是老板及时发现了不对，马上回滚差点就提交了的事务，将数字改成3.6万再提交
实际程序员这个月的工资还是3.6万，但是程序员看到的是3.9万。他看到的是老板还没提交事务时的数据。这就是脏读。
解决办法：Read Committed 读已提交，能解决脏读问题。
2、不可重复读
程序员拿着工资卡（卡里当然是只有3.6万），当他买单时（程序员事务开启），收费系统事先检测到他的卡里有3.6万，就在这个时候！！程序员的妻子要把钱全部转出充当家用，并提交。当收费系统准备扣款时，再检测卡里的金额，发现已经没钱了。程序员就会很郁闷，明明卡里是有钱的…
一个事务范围内两个相同的查询却返回了不同数据，这就是不可重复读
解决办法：Repeatable Read
3、重复读
程序员拿着工资卡（卡里还是有3.6万），当他买时（事务开启，不允许其他事务的UPDATE修改操作），收费系统事先检测到他的卡里有3.6万。这个时候他的妻子不能转出金额了。接下来收费系统就可以扣款了。
4、幻读
程序员某一天去消费，花了2千元，然后他的妻子去查看他今天的消费记录（，妻子事务开启），看到确实是花了2千元，就在这个时候，程序员花了1万买了一部电脑，即新增INSERT了一条消费记录，并提交。当妻子打印程序员的消费记录清单时（妻子事务提交），发现花了1.2万元，似乎出现了幻觉，这就是幻读。
解决办法：Serializable，但是这种事务隔离级别效率低下，比较耗数据库性能，一般不使用
5、对应关系
| 事务隔离级别 | 脏读 | 不可重复读 | 幻读 |
|---|---|---|---|
| 读未提交(read-uncommitted) | 是 | 是 | 是 |
| 读已提交(read-committed) | 否 | 是 | 是 |
| 可重复读(repeatable-read) | 否 | 否 | 是 |
| 串行化(serializable) | 否 | 否 | 否 |
6、事务隔离级别
1、Read Uncommitted
就是一个事务可以读取另一事务未提交的数据
2、Read Committed
一个事务要等另一个事务提交后才能读取数据
3、Repeatable Read
就是在开始读取数据（事务开启）时，不在允许有修改操作
4、Serializable
在该级别下，事务串行化顺序执行，可以避免脏读，不可重复读与幻读
7、查看隔离级别
select @@global.tx_isolation,@@tx_isolation;
8、设置隔离级别
-- 全局
set global transaction isolation level read committed;
-- 当前会话
set session transaction isolation level read committed;
第八章：权限管理
1、什么权限
权限是一个用户能够做什么事情，在MySQL中，可以设置全局权限，指定数据库权限，指定表权限，指定字段权限
2、权限的种类
- **create：**创建数据库、表或索引权限
- **DROP：**删除数据库或表权限
- ALTER： ALTER更改表，比如添加字段、索引等
- **DELETE：**删除数据权限
- **INDEX：**索引权限
- **INSERT：**插入权限
- **SELECT：**查询权限
- **UPDATE：**更新权限
- **CREATE VIEW：**创建视图权限
- **EXECUTE：**执行存储过程权限
3、创建用户
create user '用户名' @'localhost' identified by '密码';
4、删除用户
DROP USER 用户名称
5、分配权限
GRANT 权限(columns) ON 数据库对象 TO 用户 IDENTIFIED BY '密码' WITH GrANT OPTION
6、创建对指定数据库的所有权限
grant ALL PRIVILEGES ON 数据库名.* TO gaowei@localhost IDENTIFIED BY '1234' WITH GRANT OPTION;
flush privileges
创建一个超级管理员mylk，密码为1234，拥有所有权限，并能继续授予权限
grant ALL PRIVILEGES ON *.* TO mylk@localhost IDENTIFIED BY '1234' WITH GRANT OPTION;
flush privileges;
7、创建一个gxq用户只能对stu表进行CRUD操作
grant insert, update, select, delete on my_test.stu TO gxq@localhost IDENTIFIED BY '1234';
flush privileges;
8、查看权限
show grants
-- 查看指定用户的权限
show grants for root@localhost
9、删除权限
REVOKE 权限 ON 数据库对象 FROM 用户;
第九章
1、什么是视图
- 视图是一个虚拟表，其内容有查询定义。
- 同真实表一样，视图包括一系列带有名称的列和行数据。
- 行和列数据来自定义视图的查询所引用的表，并且在引用试图时动态生成。
- 简单的说视图就是select结果组成的表
2、视图的特性
- 视图时对若干张基本表的引用，一张虚表。查询语句执行的结果
- 不存储具体的数据（基本表数据发生了改变，视图也会跟着改变）
- 可以跟基本表一样，进行增删改查操作(增删改查有条件限制)
3、视图的作用
- 安全性
- 创建一个视图，定义好该视图所操作的数据。之后将用户权限与视图绑定
- 这样的方式是使用到了一个特性：GRANT语句可以针对视图进行授予权限
- 查询性能提高
- 提高了数据的独立性
4、创建视图
CReATE [ALGORITHM]={UNDEFINED|MERGE|TEMPTABLE}
VIEW 视图名 [(属性清单)]
AS SELECT 语句
[WITH [CASCADED|LOCAL] CHECK OPTION];
-
ALGORITHM参数：
- **MERGE：**处理方式替换式，可以进行更新真实表中的数据
- **TEMPTABLE：**具化式，由于数据存储在临时表中，所以不可以进行更新操作
- UNDEFINED：
- 没有定义ALGORITHM参数
- mysql更倾向于选择替换方式。因为它更加有效
-
**WITH CHECK OPTION：**更新数据时不能插入或更新不符合视图限制条件的记录
-
LOCAL和CASCADED：为可选参数，决定了检查测试的范围，默认值为CASCADED
5、修改视图
CREATE OR REPlACE VIEW 视图名 AS SELECT [...] FROM [...]
6、删除视图
DROP VIEW 视图名称;
7、视图机制
- **替换式：**操作视图时，视图名直接被视图定义给替换掉
- 具化式：
- mysql先得到了视图执行的结果，该结果形成一个中间结果暂时存在内存中。
- 外面的SELECT语句就调用了这些中间结果(临时表)
- 替换式与具化式区别
- 替换式，将试图公式替换后，当成一个整体sql进行处理了
- 具体化式，先处理视图结果，后处理外面的查询需求。
8、视图不可更新部分
- 聚合函数
- DISTINCT关键字
- GROUP BY子句
- HAVING子句
- UNION运算符
- FROM子句中包含多个表
- SELECT语句中引用了不可更新视图
- 只要视图当中的数据不是来自于基表，就不能直接修改
第十章：存储过程
1、什么是存储过程
- 一组可编程的函数，是为了完成特定功能的SQL语句集
- 经编译创建并保存在数据库中，用户可通过指定存储过程的名字并给定参数（需要时）来调用执行
- 存储过程就是具有名字的一段代码，用来完成一个特定的功能
- 创建的存储过程保存在数据库的数据字典中
2、为什么要用存储过程
- 将重复性很高的一些操作，封装到一个存储过程中，简化了对这些SQL的调用
- 批量处理
- 统一接口，确保数据安全
- 相对于ORACLE数据库来说，MySQL的存储过程相对功能较弱，使用较少。
3、存储过程的创建和调用
-
DELIMITER $$
- 它与存储过程语法无关
- DELIMITER语句将标准分隔符-----分号（;）更改为：$$
- 因为我们想将存储过程作为整体传递给服务器
- 而不是让MYSQL工具一次解释每一个语句
- 告诉MYSQL解释器，该段命令是否已经结束了，MYSQL是否可以执行了。默认情况下，DELIMITER是分号（；）。在命令行客户端中，如果有一行命令以分号结束，那么回车之后，MYSQL将会执行该命令。但有时候，不希望MYSQL这么做。在为可能输入较多的语句，且语句中包含有分号。使用DELIMITER $$，这样只有当**$$**出现之后，MYSQL解释器才会执行这段语句。
-
创建存储过程
CREATE PROCEDURE 名称() BEGIN 语句 END $$ -
调用存储过程
call 名称 ();
4、删除存储过程
drop procedure 名称
5、存储过程变量
-
在存储过程中声明一个变量
-
使用DECLARE语句
-
DECLARE变量名 数据类型(大小) DEFAULT 默认值；
-
可以声明一个名为total_sale的变量，数据类型为INT，默认值为0
DECLARE total_sale INT DEFAULT 0; -
声明共享相同数据类型的两个或多个变量
DECLARE x, y INT DEFAULT 0;
-
-
分配变量值
-
要为变量分配一个值，可以使用SET语句
SET total_count = 10; -
使用SELECT INTO语句将查询的结果分配到一个变量
SELECT COUNT(*) INTO total_products FROM products;
-
-
变量范围
如果在存储过程中声明一个变量，那么当达到存储过程的END语句时，它将超出范围，因此在其它代码块中无法访问
6、存储过程参数
-
三种类型
-
**IN：**表示调用者向过程传入值（传入值可以是字面量或变量）
-
**OUT：**表示过程向调用者传值
-
INOUT： INOUT参数是IN和参数OUT参数的组合
-
-
定义参数
create produce name(模式, 参数名称 数据类型(大小))
7、存储过程语句
-
IF语句
IF expression THEN statements; END IF;IF expression THEN statements; ELSE else-statements; END IF; -
CASE语句
CASE case_expression WHEN when_expression_1 THEN commands WHEN when_expression_2 THEN commands ... ELSE commands END CASE; -
循环
WHILE expression DO statements END WHILEREPEAT statements; UnTIL expression END REPEAT
8、查看存储过程
-
查看所有存储过程
SHOW PROCEDURE STATUS; -
查看指定数据库中的存储过程
SHOW PROCEDURE STATUS WHERE db='My_test4'; -
查看指定存储过程源代码
SHOW CREATE PROCEDURE 存储过程名
第十一章：自定义函数
1、自定义函数
2、随机产一个部门编号
第十二章：索引
1、什么是索引
- 索引适用于快速找出在某个列中有一特定值的行
- 不适用索引，MySQL必须从第一条记录开始读完整个表，直到找出相关的行
- 表越大，查询数据所花费的时间越多
- 如果表中查询的列有一个索引，MySQL能够快速到达一个位置去搜索数据文件
- 而不必查看所有数据，那么将会节省很大一部分空间
2、索引的优势与劣势
1、优势
- 类似大学图书馆建书目索引，提高数据检索效率，降低数据量IO成本
- 通过索引对数据进行排序，降低数据排序的成本，降低了CPU的消耗
2、劣势
- 实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，索引索引列也是要占空间的
- 虽然索引大大提高了查询速度，同时确会降低更新表的速度，如对表进行INSERT、UPDATE、ELETE
3、索引的分类
1、单值索引
即一个索引只包含单个列，一个表可以有多个单列索引
2、唯一索引
索引列的值必须唯一，但允许有空值
3、复合索引
一个索引包含多个列
INDEX Multildx(id, name, age)
4、全文索引
只有在MyISAN引擎上才能使用，只能在CHAR，VARCHAR，TEXT类型字段上使用全文索引
4、索引操作
-
创建索引
CREATE INDEX 索引名称 ON table (column[, column]...);CREATE INDEX salary_index ON emp(salary) -
删除索引
DROP INDEX 索引名称 ON 表名 -
查看索引
SHOW index FROM 表名;- **Table：**表名
- **Non_unique：**如果索引不能包括重复词，则为0。如果可以，则为1。
- **Key_name：**索引的名称
- **Seq_in_index：**索引中的序列号，从1开始
- **Column_name：**列名称
- **Collation：**列以什么方式存储在索引中。在MySQL中，有值’A’（升序）或NULL（无分类）。
- Cardinality：
- 索引中唯一值的数目的估计值
- 过运行ANALYZE TABLE或myisamchk -a可以更新。基数根据被存储为整数的统计数据来计数，所以即使对小型表，该值也没有必要是精确的。基数越大，当进行联合时，MySQL使用该索引的机会越大。
- Sub_part：如果列只是被部分地编入索引，则为被编入索引地字符的数目。如果整列被编入索引，则为NULL。
- Packed：指示关键字如何被压缩。如果没有被压缩，则为NULL。
- NULL：如果列含有NULL，则含有YES。如果没有，则该列含有NO。
- Index_type：用过的索引方法（BTREE、FULLTEXT、HASH、RTREE）。
- **Comment：**索引备注信息
-
自动创建索引
- 在表上定义了主键时，会自动创建一个对应的唯一索引
- 在表上定义了一个外键时，会自动创建一个普通索引
5、EXPLAIN
用来查看索引是否正在被使用，并且输出其使用的索引的信息
- id： SElECT识别符。这是SELECT的查询序列号，也就是一条语句中，该select是第几次出现。在本次语句中，select就只有一个，所以是1。
- select_type：所使用的SELECT查询类型，SIMPLE表示为简单的SELECT，不适用UNION或子查询，就为简单的SELECT。
- **table：**数据表的名字。他们按被读取的先后顺序排列。
- **type：**指定本数据表和其他数据表之间的关联关系，该表中所有符合检索值的记录都会被取出来和从上一个表中取出来的记录做联合
- **key：**实际选用的索引
- **possible_keys：**MySQL在搜索数据记录时可以选用的各个索引，该表中就只有一个索引，year_publication
- key_len：显示了mysql使用索引的长度(也就是使用的索引个数)，当key字段的值为null时，索引的长度就为null。注意，key_len的值可以告诉你在联合索引中mysql会真正使用了哪些索引。这里就使用了一个索引，所以为1。
- **ref：**给出关联关系中另一个数据表中数据列的名字。常量（const），这里使用的时1990，就是常量。
- **rows：**MySQL在执行这个查询时语句会从这个数据表李读出的数据行的个数。
- **extra：**提供了与关联操作有关的信息，没有则什么都不写。
6、索引结构
会先对数据进行排序
1、btree索引
- B+树索引
- B+树是一个平衡的多叉树，从根节点到每个叶子节点的高度差不超过1，而且同层级的节点间有指针相互链接。
2、hash索引
- 哈希索引就是采用一定的哈希算法，把键值换算成新的哈希值，检索时不需要类似B+树那样从根节点到叶子节点逐级查找，只需一次哈希算法即可立刻定位到相应的位置，速度非常快。
- hash索引结构的特殊性，其检索效率非常高，索引的检索可以一次定位，不像B+ Tree索引需要从根节点到枝节点，最后才能访问到叶节点这样多次的IO访问，所以Hash索引的查询效率要远高于B+ Tree索引。
7、哪些情况需要创建索引
- 主键自动建立唯一索引
- 频繁作为查询条件的字段应该创建索引
- 查询中与其他表关联的字段，外键关系建立索引
- 频繁更新的字段不适合建立索引，因为每次更新不单单是更新了记录还会更新索引
- WHERE条件里用不到的字段不创建索引
- 查询中排序的字段，排序的字段若通过索引去访问将大大提高排序速度
- 查询中统计或者分组字段
8、那些情况不需要创建索引
- 表记录太少
- 经常增删改的表
- 如果某个数据列包含许多重复的内容，为他建立索引就没有太大的实际效果。
3533
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
