root数据库技术
一、数据库理论
1 什么是数据库技术
数据库技术主要研究如何组织、存储数据，并如何高效地提取和处理数据。
2 什么是SQL
SQL（Structured Query Language）结构化查询语言
SQL是操作数据库的命令集，也是功能齐全的数据库语言。
SQL只需要掌握“怎么用”或“做什么”，不需要了解"如何做"或“怎么做”。
SQL在不同的数据库软件中，有一小部分是不相同的(编程部分)，但大多数通用的。
SQL分类：DDL数据描述语言（负责创建和管理资源）、DML数据库操纵语言（负责管理数据）、DTL数据库事务语言(管理事务）、DQL数据查询语言、DCL数据库控制语言（管理数据库运行环境的）。DDL数据定义语言：用来定义数据库对象 – 库，表，列等
创建数据库：create database
查看数据库：show databases
修改数据库的编码格式：alter database 数据库名 charactset 编码格式
删除数据库：drop database 数据库名
切换数据库：use 数据库名
DML数据库操纵语言：用来定义数据库记录（数据）的增删改
增删改：insert into 表名 values
DCL数据控制语言：用来定义访问权限和安全级别
1、授权（grant）：grant create session to scott
2、回滚（rollback）
3、提交（commit）
4、新建用户（create user）
DQL数据查询语言：用来查询记录（数据查询）
数据查询语言DQL基本结构是由SELECT子句，FROM子句，WHERE子句组成的查询块：
SELECT <字段名表> FROM <表或视图名> WHERE <查询条件>
3 DB+DBMS+DBS
3.1 Data数据
数据是客观事物的符号表示，数据也是数据库中存储的最基本的单位。
数据分类：文字、图形、图像、音频、视频。
3.2 数据库的定义
数据库： 长期存储在计算内、有组织、可共享的数据集合。数据库中的数据是按一定的数据模型组织、描述和存储。具有较少的冗余、较高的数据独立性和易扩展性以及数据的完整性，并为多个用户共享。
3.3 DBMS
DBMS: 应用于应用软件与数据存储之间的一层数据管理软件，是基础软件（如,Access）也可以是个大型复杂的软件系统（
Oracle, MySQL，SQLServer）。DBMS的用途： 科学地组织、存储数据，并高效的提取和维护数据。
DBMS的主要功能：数据库定义、数据库操作、数据库的保护（安全性）和维护。
DBMS的优点：
- 较少的数据冗余
- 程序与数据相互对立
- 保证数据的安全、可靠、正确
- 数据可以并发使用并能同时保证一致性
- 数据易维护、易扩展
3.4 DBS数据库系统
DBS由数据库、数据库管理系统、应用系统、系统管理员和用户组成的，经历三个阶段：网状模型、层次模型、关系模型（重点）。
数据和信息的关系：
信息是数据经过加工后提取出有用的知识，通过某种数据形式表示的。
信息的层次：从低到高，从客观到信息化， 存在三个世界：现实世界、信息世界、数据世界。
4 范式
范式： 关系模型满足的条件, 主要用于设计关系的一种规范。
4.1 第一范式
1NF: 关系中的每一个属性都不可分隔。即没有重复的列。
4.2 第二范式
2NF: 关系中非主键属性完整依赖于主键。即存在主键字段或属性
4.3 第三范式
3NF: 非主键属性之间不存在依赖关系。即存在外键字段时，只需要另外关系的主键。如：在员工表只需要部门表的部门编号（主键）即可，不需要将部门的名称存放在员工表中。部门编号在员工表中是一个外键。
二、MySQL数据库
MySQL数据库是一个关系型数据库管理系统， 目前是属于Oracle公司的。也是一个轻量级数据库。目前主要版本集中5.5/5.6/5.7和8.0， 最新的版本8+。
MySQL数据库可以安装在Window、Mac、Linux等操作系统上。
MySQL5.6官方文档：https://dev.mysql.com/doc/refman/5.6/en/
1 安装注意事项
- 安装路径： 尽量安装到D盘，默认安装到C盘
- 版本选择： Server Only 只有服务没有其它的工具。
- 配置中Root用户的口令： root
2 MySQL数据库操作
进入mysql的交互环境：
按Win 键 + mysql ，选择 MySQL 5.6 Command Line
进入命令行窗口时，输入root用户的口令， 正常情况下进入MySQL交互环境。
操作以下的sql语句：
use mysql;
select user,host from user;
【重要的说明】mysql数据库管理系统中，包含了很多的数据库和用户，每一个数据库下存在多个关系表、视图、函数或存储过程等资源。
三、mysql数据库的操作
1 MySQL数据库命令
1.1 查看所有数据库
show databases;
1.2 打开数据库
use <数据库名>;
1.3查看当前所在的库
select database();
1.4 查看当前库下的表
show tables;
1.5 查看表结构
desc <表名>;
1.6 查看表内容
select 属性1,属性2 ... from <表名或关系表> --查看表所列出的属性的列
select * from <表名>； --查看表的所有列
1.7 查看当前的时间
select now(); --显示时间和日期
select current_date; --只显示日期
select current_time; --只显示时间
select current_timestemp; --显示时间和日期
1.8 查看当前字符集
show variables like 'character%'; --正常情况下字符集是 utf8
2 管理数据库
2.1 创建数据库
create database [if not exists] <数据库名> [charset utf8]; --语法 [如果不存在] [指定字符集]
--创建studb数据库，如果存在的话，则不创建，指定字符集为 utf8
create database if not exists studb charset utf8;
2.2 删除数据库
drop database [if exists] <数据库>; --如果<数据库>存在，则删除
--删除bookdb数据库
drop database bookdb;
2.3 查看创建数据库的语句
show create database <数据库>;
3 管理表
表的创建、修改、删除以及重命名。
3.1 创建表
create table [if not exists] 表名
(字段名 数据类型[(数据长度)] [约束] [comment '注释信息'], ...)
engine=InnoDB charset=utf8; --引擎 字符集
3.2 数据类型
数值类型：整数类型
int``integer、小数类型fioat、精确小数类型decimal(长度,小数位)。字符类型：固定长度的字符类型
char（长度）、可变长的字符类型varchar（长度）。日期类型：日期
date、时间time、日期时间timestamp。文本类型：普通文本
text, 、大文本clob。二进制类型：
blob，如音频或视频的媒体文件、电子合同等。
3.3 字段约束
主键约束：
primary key主键约束包含唯一约束和非空约束。外键约束：
foreign key外键引用另一个表的主键字段。唯一约束：
unique字段的值在所有记录中是唯一的。非空约束：
not null字段值是不能为null。默认值：
default字段值如果没有提供时，使用默认值。自增：
mysql特定的功能（序列），auto_increment只配合primary key主键使用，而且数据类型是integer。
3.4 表的引擎
show engines; --查看mysql数据库存在哪些引擎
3.5 实战
任务1: 创建班级表
班级表的名称：
tb_class班级表的字段： 编号
cls_id、名称name、最大人数max_persons编号是自增的、主键； 名称是唯一的，不能重复。 最大人数的默认是100人
create table tb_class (
cls_id integer primary key auto_increment,
name varchar(20) unique,
max_person int default 100
);
任务2: 创建学生表
表名：
tb_stu字段名： 编号
stu_id, 姓名name, 性别sex， 出生日期 birthday编号是主键且自增、性别的默认值为
男, 出生日期不能为空
create table tb_stu (
stu_id integer primary key auto_increment,
name varchar(20),
sex char(1) default '男',
birthday date not null
);
任务3: 创建老师表
表名：
tb_teacher字段名： 编号
tid, 姓名name, 工龄work_years编号主键且自增，姓名不能为空，工龄默认为3年
create table tb_teacher(
tid integer primary key auto_increment,
name varchar(20) not null,
work_years int default 3
);
任务4: 创建课程表
表名：
tb_course字段： 编号
cid, 名称name, 带课老师编号tid编号主键且自增，名称唯一，老师编号外键（引用老师表
tb_teacher的主键字段）
-- 字段级外键约束
create table tb_course
(
cid integer primary key auto_increment,
name varchar(20) unique,
tid integer references tb_teacher(tid)
);
-- 表级外键约束
create table tb_course
(
cid integer primary key auto_increment,
name varchar(20) unique,
tid integer,
constraint tid_fk foreign key(tid) references tb_teacher(tid)
);
注意：表级的方式添加外键约束
constraint [约束名] <约束类型>(字段) references <引用表名>(引用字段)
任务5: 创建成绩表
表名：
tb_score字段： 学生编号
sid, 课程编号cid, 成绩score学生编号是外键（引用学生表的编号）, 外键约束名定为
tb_score_sid_fk课程编号是外键（引用课程表的编号）,外键约束名定为
tb_score_cid_fk成绩的默认为 0.0
create table tb_score
(
sid integer,
cid integer,
score float default 0.0,
constraint tb_score_sid_fk foreign key (sid) references tb_stu(stu_id),
constraint tb_score_cid_fk foreign key (cid) references tb_course(cid)
);
3.6 修改表（日期：3.22）
1 修改表名
alter table 表名 rename [to] 新表名; --语法
--如, 修改`tb_stu`表名为`tb_student`;
alter table tb_stu rename tb_student;
2 修改字段名
--语法
alter table 表名
change 原字段名 新字段名 数据类型[(长度)] [约束];
--如，修改`tb_student`表中的`name`字段名为`sname`
alter table tb_student
change name sname varchar(20);
3 修改字段
一 般用于修改字段的类型和长度、以及非空约束和默认值。
--语法
alter table 表名
modify 字段名 数据类型[(长度)] [约束] [comment '注释'];
--如1：修改`tb_teacher`表的`name`字段的数据长度为30并且默认值为`老师`。
alter table tb_teacher
modify name varchar(30) default '老师';
--如2: 修改`tb_class`表的`name`字段长度为40且非空，同时修改`max_person`字段的默认为120
alter table tb_class
modify name varchar(40) not null,
modify max_person int default 120;
4 删除字段
不能直接删除主键、外键和唯一约束的字段。
--语法
alter table 表名
drop [column] 字段名;
--如1： 删除`tb_score`表的`score`字段。
alter table tb_score
drop score;
--如2： 尝试删除`tb_score`表的`cid`字段（外键约束的字段）应该先删除外键约束再删除字段。
alter table tb_score
drop foreign key tb_score_cid_fk;
5 增加字段
--语法
alter table 表名
add [column] 新字段名 数据类型[(数据长度)] [约束];
--如：向`tb_score`表添加新的字段`cid`和`score`。
alter table tb_score
add column cid integer,
add column score float;
6 增加外键约束
--语法
alter table 表名
add constraint 约束名 foreign key (字段名) references 引用的表名(引用字段);
--如： 向`tb_score`表的`cid`字段添加外键约束`tb_score_cid_fk`并引用`tb_course`表的`cid`主键字段。
alter table tb_score
add constraint tb_score_cid_fk foreign key (cid) references tb_course(cid);
7 查指定表的所有约束
information_schema.table_constraints字典表
select table_name,constraint_name,constraint_type --表名、约束名、约束类型
from information_schema.table_constraints
where table_name='tb_score';
3.7 删除表
--语法
drop table [if exists] <表名>;
--如：删除tb_course表
drop table tb_course;
4 操作表的数据
DML数据库操纵语言：insret、update、delete等语句
4.1 插入数据
--语法1：单条数据插入
insert into 表名[(字段名1, 字段名2,...字段名n)]
values (字段1值, 字段2值,...字段n值);
--语法2：多条数据插入
insert into 表名[(字段名1, 字段名2,...字段名n)]
values
(字段1值, 字段2值,...字段n值),
(字段1值, 字段2值,...字段n值),
(字段1值, 字段2值,...字段n值),
...
(字段1值, 字段2值,...字段n值)
;
任务1：班级表插入一条数据
班级名
Testing-2021,最大人数50
insert into tb_class(max_person, name)
values (50, 'Testing-2021');
任务1： 班级表批量插入
insert into tb_class(name, max_person) values
('Testing-2022-01', 50),
('Testing-2022-02', 45),
('Testing-2022-03', 80);
4.2 更新数据
更新数据是指修改表中某一条记录或元组中对应属性的值，支持同时修改多个属性值。当然可以通过选择操作（条件选择），选择符合条件的元组或记录。
--语法
update 表名 set
字段1=值 [, 字段2=值...]
[where 条件表达式];
条件表达式
- 等值条件 =， 如
name='disen'- 关系条件 ：> 大于, < 小于, >= 大于等于, <= 小于等于, != 不等于
- 非空条件 not null 非空, is null 为空
- 包含条件 in (条件值，条件2,…) ， 条件值之间是"或"的关系
- 逻辑关系： and 且的条件， or 或的条件
- 区间条件： between 开始值 and 结束值， 是闭合区间 [开始值, 结束值]
- 模糊条件： like 匹配字符
- 匹配字符的通配符： % 任意个数任意字符， _ 任意一个字符
任务1: 将低于50人的班级更改为100人的容量
update tb_class
set max_person=100
where max_person < 50;
任务2: 将名字中包含江的老师等级更改为特级
update tb_teacher
set level='特级'
where name like '%江%';
4.4 删除数据（不加条件是清空表内容）
--语法：
delete from 表名
[where 条件表达式];
【注意】如果不加where条件时，表示清空表数据。
【扩展】事务操作
begin 开启事务
commit 提交事务
rollback 回滚事务，取消任务开始到当前的所有操作。
任务1：开启事务并清空成绩表，查看数据之后回滚事务
begin;
delete from tb_score;
select * from tb_score;
rollback;
任务2：删除等级为null的老师记录
delete from tb_teacher
where level is null;
5 查询数据
查询数据是
mysql最主要的功能之一，查询语句涉及的面比较广。涉及选择、投影和连接等操作。
5.1 select 语句
select 语句，主要是选择哪些字段，在select语句中，可以使用一些表达式显示运算之后的结果。
语法：
select [all|distinct] 字段名或表达式 [as 别名] [,字段名或表达式...]
from 表名 [as 别名]
[where 条件表达式]
[group by 字段或表达式]
[having 分组条件表达式]
[order by 排序字段 [ASC|DESC]]
[limit 分页表达式]
[for update]
select 中的表达式，可以进行算术计算，也可以调用mysql函数。
【说明】having子句用于聚合统计之后的数据作为条件，如显示总成绩高于290的课程或学生。
5.2 条件语句
where子句，设定选择记录的条件。
如：查看学号大于等于2且成绩等于100分的课程号和成绩信息, 可以去重
select distinct cid, score
from tb_score
where sid>=2 and score=100;
5.3 排序语句
order by 子句，可以针对某一列的值进行排序(行记录的先后顺序)
如：按成绩高低进行排序，显示所有的学生不同课程的成绩信息
select sid,cid, score
from tb_score
order by score desc;
【说明】DESC/desc 表示从高到低的降序，ASC/asc 表示从低到高的升序, 默认asc
5.4 分页语句
limit子句，格式： limit 开始记录序号,每一页显示的记录数
记忆的格式： limit offset, page_size
第一条记录的序号： 0
如每页显示3条记录
第一页的limit的写法： limit 0,3;
第二页的limit写法： limit 3, 3
第三页的limit写法： limit 6, 3
总结：查看第n页的limit写法
- limit (n-1)*page_size, page_size
如2：按每页3条记录，查看第2页的成绩表数据
select * from tb_score limit 3,3;
5.5 分组统计语句
group by 子句
针对某一个字段的值进行分组，分组之后的数据可以进行聚合统计。
聚合统计需要mysql的函数：
- sum()求和
- avg()求平均
- max() 最大值
- min() 最小值
- count() 统计行数
任务1：统计每一位学生的总成绩
select sid, sum(score) as total
from tb_score
group by sid;
【注意】在select子句中，没有 使用聚合函数的列必须要放在group by 子句中。
任务2： 统计每门课程的考试人数和平均成绩
【扩展】四舍五入的函数: round(数，小数位)， 默认小数位为0
select
cid,
count(1) as persons,
round(avg(score), 1) as avg_score
from tb_score
group by cid
order by avg_score desc;
【扩展】
mysqldump -u用户名 -p密码 数据库名>导出路径和文件名
source命令，在mysql交互环境中执行sql文件中的有效的语句。
如：
将taiyang数据库导出成sql文件的sql语句：mysqldump -uroot -proot taiyang>d:\taiyang.sql
将创建表的所有的sql语句复制到d:\init.sql文件中, 导入文件需要在mysql命令行（交互环境）执行命令： source d:/init.sql
5.6 多表连接语句
简单地理解多个表之间相互联系（关联），通过连接的
sql语句，将多表之间的数据显示在一起。【注意】多表连接时，必须存在连接条件，否则会出现笛卡尔乘积(n*m)。
1 等值连接
语法：
select [ [表名.]字段或表达式 [as 别名], ...]
from 表名1 [as 别名1], 表名2 [as 别名2] [, 表名3 ...]
where 表名1.字段名=表名2.字段名
[and 表名2或1.字段名=表名3.字段名]
[and 表名1或2或3.字段名=表名4.字段名....]
【总结】如果存在n张关系表进行连接，必须存在n-1的等值连接条件。
任务1: 查看所有学生的所有课程的成绩，显示学生名、课程名和成绩并按每位学生的成绩进行排序：
select s.sname as '姓名', c.name as '课程', sc.score as '成绩'
from tb_score sc,tb_student s,tb_course c
where sc.sid=s.stu_id
and sc.cid=c.cid
order by sid DESC, score DESC;
2 join连接
join连接表等值连接更方便使用，而且支持外连接。即等值连接相当于join的内连接。
语法：
select 字段或表达式 [,...]
from 表名1
[natural|left|right] join 表名2 on (连接条件1[,连接条件2])
[join 表名3 on (连接条件2) ]
[join ...]
[where 条件表达式]
【说明】
natural自然连接，从两个表中找到相同的字段名作为默认的连接条件- left 左外连接, 查询的结果除了内连接（两个集合交集）之外，还包含左边表的其它数据。
- right 右外连接，查询的结果除了内连接之外，还包含右边表的其它数据。
- left和right都可能存在 null空数据。
3 union联合
将多个select语句的查询合并到一起，但是select选择的字段尽量保持一致。默认显示是第一个select语句的字段名。
一般在分表的数据查看情况下使用，分表指按某一种分类将数据分开存储，避免一张表存放过多的数据。分表或分库是数据库的优化手段，解决查询效率问题。
语法：
select ... from ... [where... order ..]
union [all | distinct ]
select ... from ... [where ... order ..]
如： 将成绩表的每位学生的平均成绩和总成绩的统计数据合并
select sid,sum(score) total from tb_score group by sid
union
select sid,round(avg(score)) avg from tb_score group by sid;
5.7 子查询语句
子查询：select语句中包含select语句。
子查询应用的场景： 复杂的sql查询语句需要从不同的select语句查询结果中组合的。
子查询可以使用的位置： where条件中，join连接, update 更新，insert插入数据等。
1 where中应用
语法：
select ... from ...
where 字段或表达式 [=|in|>=|<=|>|<| !=](select 字段或表达式 from ...);
任务1: 查询高于最低平均成绩的学生及其成绩，显示学生姓名、课程名和成绩
select min(avg) as min
from
(select sid,avg(score) as avg from tb_score group by sid) A;
【注意】如果from 后跟上子查询时，必须给定别名，表示从某一个表中查询数据。join的子查询也需要一个表的别名。
2 join中应用
语法：
[left | right] join (select ...) 别名 on (连接条件)
使用join方式完成where应用中的任务2
select c.*, if(A.cnt is null,0,A.cnt) as cnt
from tb_class c
natural left join
(select
cls_id,
count(1) as cnt
from tb_student
group by cls_id) A
where cnt < 2 or cnt is null;
【扩展】mysql存在一个分支函数 if(条件, 为true值，为false的值)
3 update中应用
update更新语句，在set子句对子查询的结果赋值给某一个字段。
语法：
update 表名,(select ... ) 别名 set 字段1=别名.字段名 [,字段2=别名.字段名 ...]
【注意】更新字段值时一般是单行结果的子询中某一列的值。
任务1：将班级最大人数低于平均数时，更改为平均数。
update tb_class,
(select round(avg(max_person)) avg from tb_class) a
set max_person=a.avg
where max_person < a.avg;
【注意】子查询可以理解为一个虚拟的表，不能直接在set中使用子查询作为某一个字段的值。
4 insert中应用
插入数据时，可以使用子查询，将查询到的数据插入到某一个表中。
同插入数据使用子查询的方式，create table也可以使用子查询。
语法：
insert into 表[(字段1, ....)]
select ...
【注意】向表的哪些字段插入数据时，在select子句中必须有对应的字段值。
【扩展】create table使用子查询的语法
create table 表名 [as]
select ...
【问题】创建表的字段只有select中选择的字段而且不存在约束。
任务1：根据班级将成绩表分成多分，即创建不同班级的成绩表；
-- 新的成绩表与原成绩表的结构保持不变
create table tb_cls1_score as
select * from tb_score where score=0;
任务2：继任务1，将不同班级学生的成绩分别插入指定的表
-- 将班级1的学生考试成绩插入到 tb_cls1_score
insert into tb_cls1_score
select sc.*
from tb_score sc join tb_student s on (sc.sid=s.stu_id)
where s.cls_id=1;
6 mysql内置函数
6.1 字符串函数
针对字符串的运算提供的内置函数
1 ascii函数
将一个左边字符转化为ascii值，asscii中的字符只占一个字节。
select ascii('a'); -- 查看字符 a的ascii的十进制的数值
select char(97); -- 将数值转化为ascii中的字符,如97转化为a字符
select oct(n); --将n的数值转化为8进制的字符串
select hex(n) --将n的数值转化为16进制的字符串
select bin(11); -- 查看11的二进制的字符串
2 字符长度
select char_length('狄哥'); -- 显示字符个数 【重点】
select length('狄哥'); --显示字节个数
-- 显示字符所占的字节长度，一个英文在utf8字符集中占1个字节，一个中文在utf8中占3个字节。
-- 常用的字符集：gbk/gb2312 (英文占1字节，中文占2字节), unicode每个字符占2字节
-- 设计表的字段时，使用varchar(length) length表示是字符的个数【面试】
3 字符串拼接
-- 字符串拼接 【重点】
select concat('hi', ',', 'good');
select name, concat(work_years,'年') as work from tb_teacher;
4 查找字符串位置
instr(str, substr); --从str中查找substr第一次出现的位置
-- 显示的位置是从1开始的。
select instr('hi,disen', 'disen') as position;
5 大小写转换
-- low() 转小写
-- upper() 转大写
6 删除空白
-- trim() 删除字符串两边的空白
-- ltrim() 删除字符串左边的空白
-- rtrim() 删除字符串右边的空白
select trim(' disen ') a,
ltrim(' **disen** ') as b,
rtrim(' disen ');
7 替换
-- replace(str, oldstr, newstr) 从str中将oldstr替换成newstr
select replace('hi,disen', 'disen', 'jack') as rt;
8 字符串截取
-- substr(str, pos, len) 从pos开始截取子len长的子字符串，len默认为最后的位置
select substr('hi,disen',4);
select substr('hi,disen,very good!', 4, 5);
6.2 数值函数
1 绝对值函数
abs() -- 绝对值
select abs(-90);
2 小数取整函数
ceil() -- 上行取整
floor() -- 下行取整
select ceil(15.5), floor(15.5);
3 四舍五入函数
round(n, 小数位数) --四舍五入
select round(13.4456, 3);
4 次幂函数
pow(m, n)/power() -- m的n次幂
select pow(3, 2);
5 开方函数
sqrt(n) --开平方根的函数
select sqrt(9);
6 取余函数
mod(number, m) --计算 number对m的取余
-- 12 % 8 => 4
select mod(12, 8);
7 取整
select 10 div 3;
8 截断函数
truncate(x, d) -- 保存x中d位小数，如果d是负数时，表示截断整数部分内容。
select truncate(12.56, 1); 结果是12.5
select truncate(1987, -3); 结果是1000
6.3 日期函数
1 日期相关的函数
now() -- 当前日期时间
current_date() -- 当前的日期
year(date) -- 从时间数据中提取年
month(date) -- 提取月份
day(date) -- 提取日
date(timestamp) -- 从日期时间中提取日期
week(date) -- 显示date是一年中第几周 【重点】， 从0开始
weekofyear(date) -- 显示date是一年中的第几周，从1开始
yearweek(date) -- 返回 date中年和一年中的第几周
dayofyear(date) -- 一年中的第几天 （1-366）
dayofweek(date) -- 一周内的星期几的索引 ，星期日是1
dayname(date) -- 显示星期几
dayofmonth(date) -- 一月中的第几天索引，从1开始，范围（0-31）
select dayofmonth(current_date);
2 时间相关函数
current_time() -- 当前时间
hour(time) -- 取小时
minute(time) -- 取分钟
second(time) -- 取秒
time(timestamp) -- 从日期时间信息中，提取时间
3 日期时间转换相关的函数
timestamp(e1) -- 将e1日期转化日期时间
timestamp(e1, e2) -- 将e2的时间加到e1得到新的日期时间
time_format(time, format_str) -- 按format_str格式转化time字符串
date_format(date, format_str) -- 按format_str格式转化date字符串
str_to_date(str, format) -- 将format格式的str字符串转化为日期时间。
select timestamp('2022-03-12');
select timestamp('2022-03-24 09:10:10', '13:15:25');
select char_length(time_format(now(), '%H/%i/%s'));
select time_format('12:15:18', '%i:%s');
select date_format(now(), '%m/%d/%y');
-- 日期或时间计算时，不能按默认的YYYY-dd-mm格式转化的认为不合法的日期或时间
select datediff(now(), '2012年10月12日');
select str_to_date('2012/10/12','%Y/%m/%d');
select str_to_date('10月12日 12年','%m月%d日 %y年');
select datediff(now(), str_to_date('2012年10月12日','%Y年%m月%d日'));
4 时间间隔相关的函数
datediff(d1, d2) -- 计算d1-d2的相隔的天数
timediff(e1, e2) -- 返回 e1-e2相差的时间，结果是时间格式。
adddate(date, INTERVAL n UNIT), adddate(date, days)
-- UNIT时间单位： YEAR, MONTH, DAY, HOUR, MINUTE, SECOND
-- n表示，可以是数值，也可以是字符串，如果字符串存在day，最后出现秒，unit则为day_second
addtime(time, add_time)
subdate(date, INTERVAL n UNIT)
subtime(time, sub_time)
select datediff(now(), '2022-01-12');
select timediff(now(), '2022-01-12 12:10:10');
select adddate(now(), interval 25 day);
select adddate(now(), interval '25 10' day_hour);
select subdate(now(), interval 40 day);
重要的日期格式的字符串：
%Y-%m-%d %H:%i:%s
6.4 分支函数
1 if函数
if(条件表达式, 结果为true的值, 结果为false的值), 用于select子句，where子句。
任务1
存在以下A表：
year month amount
1991 1 1.1
1991 2 1.2
1992 1 2.1
1992 2 2.2
写出sql语句，输出以下结果：
year m1 m2
1991 1.1 1.2
1992 2.1 2.2
sql语句：
create table a(year int, month int, amount float);
insert into a values
(1991, 1, 1.1),
(1991, 2, 1.2),
(1992, 1, 2.1),
(1992, 2, 2.2);
select
year,
max(round(if(month=1, amount, 0),1)) m1,
max(round(if(month=2, amount, 0),1)) m2
from a
group by year;
2 case语句
语法：
select ...,
case [字段名或表达式] when 条件或值 then 条件为true结果
when 条件2或值2 then 条件2为true结果2
...
else 以上条件都不为true时的结果n end 别名
from ...
如1：
select case 1+1 when 1 then '错误'
when 2 then '正确'
else '系统出问题了' end result;
【注意】如果when中是单个值时，表示值和 case后表达式是等值判断
如2：
select case when 1>2 then '1大于2'
when 1=2 then '1等于2'
else '1小于2' end result;
【注意】when中关系表达式，计算关系表达式的结果，case后不需要字段或表达式。
6.5 加密函数
加密函数，即将明文转化为密文，更好地保护数据。
【扩展】加密算法分类： 对称加密（AES, DES）和非对称加密(RSA， 公钥与私钥，如ssl证书采用RSA算法)
md5(str) -- 通过md5算法，将str明文转化为32长度的密文，且不可逆的。
select md5('123');
select md5('disen');
sha1(str) -- 通过sha1算法，将str明文转化为密文，密文的长度一般为40位， 不可逆的。
select sha1('disen');
password(str) -- 将str转化为密文， 一般用于口令加密。
-- 对称加密与解密
aes_encrypt(str, key) -- str明文
aes_decrypt(en_str, key) -- en_str 加密后的密文
hex(str) --将str内容转化16进制内容
unhex(16进制的str) -- 取消16进制转换的内容
select hex('disen');
select unhex('646973656E');
select hex(aes_encrypt('disen', 'abc123'));
select aes_decrypt(unhex('EB0BD931B06891CBDEEB3A90A1BDDCA0'), 'abc123');
7、视图
视图是一张虚拟的表，是一种高级的子查询且存在名称。即视图是一个select查询语句。
视图的数据取决于是select语句，一般存储复杂的且频繁使用（查询）的select语句
7.1、视图创建的语法：
create or replace view 视图名 as select 语句;
如1： 创建视图，存储查询每门课程的平均分数和课程名的数据的sql
create or replace view avg_sc as
select cid,ceil(avg(score)) avg_score from sc group by cid;
视图创建成功之后， 可以通过 show tables查看。
从视图中查询数据时，类似于从表中查询数据：
select * from avg_sc;
【注意】视图一般用于查询，不能像表一样插入、修改和删除数据。但是表的数据发生变化之后，会影响视图查询的结果。因此，可以把视图查看是一个子查询。
7.2、删除视图：
drop view 视图名; eg:drop view avg_sc; --删除视图 avg_sc
查看stu数据库下所有的视图：
select table_name
from information_schema.views
where table_schema='stu';
8、索引
索引是数据库的一种数据结构（b+树、hash），为表的数据建议一个快速查询的方式。
可以创建表的一个或多个字段的索引，一般索引字段是在查询条件中使用。
思考:
- 表中的每一个字段都可以创建索引？ 可以
- 一张表中的索引是否越多越好？不是的，因为索引过多的时候，影响DML语句效率。
索引分类： 普通索引、主键索引、唯一索引、联合索引、全文索引、分区索引
- 主键索引， 一般是创建主键约束时自动创建的索引 - 唯一索引，字段存在唯一约束时也会自动创建唯一索引，也可以手动创建唯一索引 - 联合索引，也称之为组合索引或集簇索引，由多个字段联合创建的索引。
索引创建的语法：
create [online | offline] [unique|fulltext| spatial ]
index 索引名 [ using { btree | hash }]
on 表名(字段名[(长度)] [asc|desc] [, ...] )
【说明】online 表示启用索引， offline关闭索引
如1: 为学生表的name字段创建一个唯一索引
create unique index name_unique using hash on student(name desc);
索引删除的语法:
drop index 索引名 on 表名;
如2: 删除name_unique索引
drop index name_unique on student;
如3: 创建成绩表sc的sid和cid两个字段的组合索引
create index sid_cid_index using btree on sc(sid asc, cid desc);
检查sql语句是否使用索引：
explain select * from sc where sid='01' and cid='01';
【扩展】查看表中的所有索引
select i.index_id,
i.name,
i.type,
t.name
from information_schema.innodb_sys_indexes i
join information_schema.innodb_sys_tables t on (i.table_id=t.table_id)
where t.name like 'stu/%';
9、事务
事务是一系列操作的组合，事务具有ACID的特性。
ACID
Atomicity (原子性)： 事务内发生的所有操作要么全成功，要么全失败。
Consistency(一致性): 事务开始和结束时，数据保持一致。
Isolation(隔离性): 事务之间互不影响
Durability(持久性): 数据一旦保存，将永久性存在，不受系统影响。
事务的隔离级别
read uncommitted 读未提交, 可以读取另一个事务中未提交的数据
【脏读】【不可重复读】【幻读】
read committed 读已提交， 可以读取另一个事务中已提交的数据
【不可重复读】【幻读】
repeatable read 可重复读，事务开启之后，数据不会发生变化。
【幻读】
serializable 串行化，一个事务结束之后，另一个事务才能开始。存在锁的概念。
查看当前事务的隔离级别：
show vairalbes like '%isolation%';
设置当前的会话的事务隔离级别
set session transaction isolation level <隔离级别名称>;
【扩展】事务的还原点
savepoint 名称；
--取消还原点之后的所有操作
rollback to 还原点
【扩展】自动提交事务
当开启事务之后，执行了
DDL语句时，事务会自动提交。
begin;
delete from login where name='disen';
create table abc(name varcher(20));
--以下提交或回滚事务没有任何意义，因为上一行发生了DDL语句，自动提交了事务
commit;
10、用户和权限
10.1：查看当前系统用户
select user,host,password from mysql.user;
【说明】mysql.user表存储系统的所有用户信息。user字段表示用户名，host表示当前用户登录的主句ip地址。如localhost表示只能在本地进行登录进入到mysql环境中。如果用户需要在10.36.174.22主机上登录mysql系统，需要更改host字段值。理解为host即是可登录mysql系统的ip白名单。
10.2：创建用户
语法：
create user '用户名'@'主机地址' identified [with mysql_native_password] by '口令';
【注意】with mysql_native_password 是mysql8.0向下加密兼容
如1：创建任何主机可连接的sdd用户，口令sdd
任意主机：%
use mysql;
create user 'sdd'@'%' identified by 'sdd';
10.3：修改用户口令
语法：
set password [for user]=password('口令')|old_password('口令')|'口令'
user部分包含用户名和主机的
如：将sdd用户的口令修改为sdd888
set password for sdd=password('sdd888');
语法2：修改用户口令过期和口令
--口令失效
alter user '用户名'@'主机' password expire;
--修改口令
alter user '用户名'@'主机' identified by '口令';
10.4：删除用户
语法：
drop user '用户名'@'主机' 或 用户名;
如：删除disen用户
drop user disen;
10.5：用户授权
语法：
grant 权限 on [table|function|procedure] <*|*.*|db.*|db.table>
to 用户; --数据库.表|函数|存储过程
--刷新权限
flush privileges;
如1：授予disen用户操作所有数据库的权限
grant all on * to disen;
如2：授予disen用户管理stu数据库的所有权限
grant all privileges on stu.* to disen;
如3：创建jack用户操并授予查询stu数据库所有表权限
create user 'jack'@'%' identified by 'jack';
grant select on stu.* to jack;
如4：授予jack用户向stu.student表中插入数据的权限
grant insert on stu.student to jack;
如5：授予jack用户刷新权限
grant reload on *.* to jack;
10.6：撤销权限
语法：
revoke [all privileges] [权限名]
on [table|function|procedure] <*|db.*|db.table> from 用户
如1：撤销jack用户向stu.student插入权限
revoke insert on stu.student from jack;
如2：撤销jack查询stu库的所有权限
revoke select on stu.* from jack;
11、mysql编程
11.1 函数编程
语法：
create function 函数名([参数 数据类型[长度],...])
[deterministic] [comment '说明']
returns [数据类型(长度)]
return 返回的函数结果数据;
如：定义aes加密函数，实现aes加密
create function aes(str1 varchar(20)) returns varchar(50)
return hex(aes_encrypt(str1,'123%^@*'));
--使用aes函数
select ase('disen');
如：实现aes加密和解密算法
--删除函数
drop function aes;
create function aes(str1 varchar(50),flag int) returns varchar (50)
return if(flag=0,hex(aes_encrypt(str1,'123%^@*')),if(flag=1,
aes_decrypt(unhex(str1),'123%^@*'),'无效的内容'));
将以下的aes函数中的判断尝试修改为case表达式：
drop function aes;
create function aes(str1 varchar(50),flag int) returns varchar (50)
return case flag when 0 then hex(aes_encrypt(str1,'123%^@*'))
when 1 then aes_decrypt(unhex(str1),'123%^@*'),
else '无效的内容' end;
11.2 存储过程
语法：
delimiter &&
create procedure 存储过程名([in|out|inout] 参数名 数据类型(长度),...)
begin
select into 语句;
end &&
delimiter ;
因为在存储过程的body中可能有sql语句需要执行，默认sql语句结束标识符是;一个完整的sql语句也是使用sql结束，为了避免创建完整的存储过程之中出现;可以使用delimiter结束符命令声明临时的sql结束符。
如：定义村相互过程，查询某一个学科的平均分
delimiter &&
create procedure course_avg(cname varchar(20),out avg decimal)
begin
select avg(score) into avg
from sc natural join course
where name=cname;
end &&
delimiter ;
执行存储过程：
--调用存储过程，参数写法：常量，变量 变量以@开头+变量名
call 存储过程名(参数，....)
--查询存储过程执行的结果
select @变量名;
call course_avg('语文',@avg);
select @avg
如2：创建存储过程查询不同学科的最高分、最低分、平均分和总分,并直接显示
delimiter &&
create procedure course_avg(cname varchar(20))
begin
select cname,avg(score) avg,
max(score) max,
min(score) min,
sum(score) sum
from sc natural join course
where name=cname;
end &&
delimiter ;
删除存储过程：
drop procedure 存储过程名;
11.3 触发器
触发器：监听对数据的操作事件，如当删除数据时，如果存在删除事件监听的触发器，则会执行触发器。在触发器，可以对操作前或后的数据进行处理（备份、验证）。
创建触发器语法：
create trigger 触发器名 <before|after> <insert|update|delete>
on 表名 for each row
trigger_body
【重要说明】在触发器的body中，存在两个特殊的对象OLD、NEW 分别代表是原行数据和新行数据。如针对before delete事件触发器来说，OLD表示即将删除的数据(元组)。如针对before insert事件触发器来说，NEW代表是即将插入的数据（元组）。
如1：备份一个空的成绩表，当成绩表的数据被删除时，将原数据插入到备份的成绩表
--创建一个空的成绩表
create table sc_copy as
select * from sc where 1!=1;
--创建触发器
create trigger watch_sc_delete before delete on sc for each row
insert into sc_copy values(OLD.sid,old.cid,OLD.score);
--尝试删除一条记录
begin;
delete from sc where sid='07' and cid='03';
select * from sc_copy;
rollback;
如2：创建一个用户登录表，表中存在账号和口令的字段，并插入2条记录，同时创建登录表的更新后的触发器，在触发器，将更改前后的口令保存到一个用户更新日志表中（字段：更新时间，账户，原口令，新口令）
--创建登录表 login
create table login(
id int primary key auto_increment,
name varchar(20) unique,
pwd varchar(20) notnull
);
--创建变更登录用户口令日志表
create table update_pwd_log(
id int primary key auto_increment,
update_time time
username varchar(20),
old_pwd varchar(50),
new_pwd varchar(50)
);
--插入两位登录的用户
insert into login(name,pwd) values
('disen','123'),
('jack','888');
--创建触发器
create trigger watch_update_pwd after update on login for each row
insert into update_pwd_log(update_time, username, old_pwd, new_pwd)
values (current_timestamp(), OLD.name, OLD.pwd, NEW.pwd);
-- 变更disen用户的口令为666
update login set pwd='666' where name='disen';
