1.4、第三阶段 MySQL数据库

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数据库，如果存在的话，则不创建，指定字符集为 utf8create 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

selectcid,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_studentgroup 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);

selectyear,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），为表的数据建议一个快速查询的方式。

可以创建表的一个或多个字段的索引，一般索引字段是在查询条件中使用。

思考:

1. 表中的每一个字段都可以创建索引？ 可以
2. 一张表中的索引是否越多越好？不是的，因为索引过多的时候，影响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] 参数名 数据类型(长度),...)
beginselect into 语句;
end &&
delimiter ;

因为在存储过程的body中可能有sql语句需要执行，默认sql语句结束标识符是;一个完整的sql语句也是使用sql结束，为了避免创建完整的存储过程之中出现;可以使用delimiter结束符命令声明临时的sql结束符。

如：定义村相互过程，查询某一个学科的平均分

delimiter &&
create procedure course_avg(cname varchar(20),out avg decimal)
beginselect avg(score) into avgfrom sc natural join coursewhere name=cname;
end &&
delimiter ;

执行存储过程：

--调用存储过程，参数写法：常量，变量	变量以@开头+变量名
call 存储过程名(参数，....)--查询存储过程执行的结果
select @变量名;

call course_avg('语文',@avg);
select @avg

如2：创建存储过程查询不同学科的最高分、最低分、平均分和总分,并直接显示

delimiter &&
create procedure course_avg(cname varchar(20))
beginselect cname,avg(score) avg,max(score) max,min(score) min,sum(score) sumfrom sc natural join coursewhere 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 timeusername 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';