概念

数据库发展三个阶段

1. 人工管理阶段
2. 文件系统阶段
3. 数据库系统阶段

常用数据库模型

1. 层次模型
2. 网状模型
3. 关系模型

常用关系型数据库

三大范式

1. 字段不可分割
2. 必须有主键，不能存在不依赖于主键的字段
3. 非主键只依赖主键，而不依赖其他非主键

一 操作数据库

1.1 连接数据库

命令行连接

mysql -uroot -p123456 --连接数据库
update mysql.user set authentication_string=password('123456') where user='root' and Host = 'localost'; --修改用户密码
flush privileges; --刷新权限
 exit; --退出连接
 --sql原本的注释就是--

1.2 操作数据库

操作数据库 > 操作数据库中的表 > 操作数据库中的数据

==mysql中的数据库不区分大小写==

1. 创建数据库

   CREATE DATABASE [IF NOT EXISTS] westos;
   

2. 删除数据库

   DROP DATABASE [IF EXISTS] westos;
   

3. 使用数据库

   --如果你的表名或者字段名是一个特殊字符，就需要带``
   USE `数据库`
   

4. 查看数据库

    SHOW DATABASES; -- 查看所有数据库

5. 可以对照历史记录学习SQL语句

6. 记住关键字

1.3 数据库数据类型

数值

类型	描述	大小	备注
tinyint	十分小的数据	1B
smallint	较小的数据	2B
mediumint	中等大小的数据	3B
int	标准整数	4B	常用
bigint	较大整数	8B
float	浮点数	4B
double	浮点数	4B
decimal	字符串形式浮点数		金融数据计算

字符串

类型	描述	长度	备注
char	固定大小字符串	0~255
varchar	可变字符串	0~65535	常用 string
tinytext	微型文本	2^8-1
text	文本串	2^16-1	保存大文本

时间和日期

类型	格式	描述
date	YYY:MMM:DDD	日期格式
time	HHH:MMM:SSS	时间格式
datastamp	时间戳	1970.1.1到现在的毫秒数
year		年份表示
timestamp	日期+时间 2021-10-12 09:42:33	修改表的时间和日期

使用timestamp作为类型，将默认值设置为CURRENT_TIMESTAMP可以将该字段默认值设置为修改时间

null

未知，没有值

不要使用null运算，否则结果为null

MySQL 中的 varchar 和 char 有什么区别？

char 是一个定长字段,假如申请了char(10)的空间,那么无论实际存储多少内容.该字段都占用 10 个字符,而 varchar 是变长的,也就是说申请的只是最大长度,占用的空间为实际字符长度+1,最后一个字符存储使用了多长的空间.

在检索效率上来讲,char > varchar,因此在使用中,如果确定某个字段的值的长度,可以使用 char,否则应该尽量使用 varchar.例如存储用户 MD5 加密后的密码,则应该使用 char。

1.4 数据库约束（重点）

主键约束

• PRIMARY KEY(字段名)
• 声明一个字段为该表主键

唯一 unique

• 要求该列唯一，可以有一个空值

非负Unsigned:

• 无符号整数
• 声明该列不能声明为负数

零补zerofill:

• 0填充的
• 不足的位数使用0填充，int(3) ,5 --> 005

自增：AUTO_INCREMENT

• 自动在上一条记录的基础上+1（默认）
• 通常用设计唯一的主键index，必须是整数类型
• 可以自定义设计主键自增的起始值和步长
• 可以在高级->自动增量中设置

非空：NULL not null

• 假设设置为not null 如果不给赋值则会报错
• NULL，如果不填写值，默认就是Null

默认： DEFAULT

• 设置默认值

1.5 创建数据库表

-- 创建一个表 
-- 学号 姓名 密码 家庭住址 email

-- 使用英文字符 表名和字段尽量使用``包括起来
-- 字符串使用单引号括起来
-- 所有语句后面加逗号 最后一句不用加
-- PRIMARY KEY主键 一般一个表只有一个主键
-- 字符集和引擎写在语句最后
CREATE TABLE IF NOT EXISTS students(
`id` INT(8) AUTO_INCREMENT COMMENT 'student_id',
`name` VARCHAR(30) NOT NULL COMMENT 'student_name',
`pwd` VARCHAR(30) NOT NULL DEFAULT '123456' COMMENT 'login_pwd',
`adress` VARCHAR(50) DEFAULT NULL COMMENT 'student_adress',
`email` VARCHAR(100) DEFAULT NULL COMMENT 'student_email',
PRIMARY KEY(`id`)
)ENGINE=INNODB CHARSET=utf8

基本格式

CREATE TABLE [IF NOT EXISTS] `table_name`(
	`field` `type` [属性] [索引] [注释],
	`field` `type` [属性] [索引] [注释],
    ......
	`field` `type` [属性] [索引] [注释]
)[表类型][字符集][注释]

常用命令

SHOW CREATE DATABASE school -- 查看创建数据库的语句 
-- CREATE DATABASE `school` /*!40100 DEFAULT CHARACTER SET utf8 */ /*!80016 DEFAULT ENCRYPTION='N' */

SHOW CREATE TABLE students -- 查看创建表的语句

DESC students
DESCRIBE students  -- 查看表结构

1.6 修改删除表

修改表 alter

-- 修改表名 ALTER TABLE 旧表名 RENAME AS新表名
ALTER TABLE students RENAME AS student1;

SHOW DATABASES;
USE school;
SHOW TABLES; 

-- student1
-- 增加表中的字段
-- ALTER TABLE 表名 ADD 字段名 数据类型;
ALTER TABLE student1 ADD age INT(5);

-- ALTER TABLE 表名 ADD 字段名 数据类型 [约束条件] [first] after 字段名;
-- 修改表
-- 修改表的字段约束 modify 修饰 ALTER TABLE 表名 MODIFY 字段名 数据类型;
ALTER TABLE student1 MODIFY age1 VARCHAR(5);  

-- 修改表中的字段名 change 改变 ALTER TABLE 表名 CHANGE 旧字段名 新字段名 数据类型;
ALTER TABLE student1 CHANGE age1 age INT(1); 

-- 删除表的字段
ALTER TABLE student1 DROP age;

字段指定位置

-- 在指定字段后面创建字段
-- ALTER TABLE 表名 ADD 字段名 类型 AFTER 指定字段名;
ALTER TABLE `employee` ADD `sex` VARCHAR(40) AFTER `title`;

-- 移动字段位置到指定字段后面
-- ALTER TABLE 表名 MODIFY 字段名 类型 AFTER 指定字段名 ;
ALTER TABLE `employee` MODIFY `gender` VARCHAR(1) AFTER `selfintro` ;

删除表 drop

-- 删除表（如果表存在）
DROP TABLE IF EXISTS students;

创建和删除都要加上判断以免报错

注意点：

• 字段名使用``包裹
• 注释 -- /**/
• sql关键字大小不敏感
• 所有字符都是用英文

二 MYSQL数据管理

2.1外键（了解）

-- 数据库级别外键
CREATE TABLE `class`(
    `classid` INT(10) NOT NULL COMMENT '班级id',
    `stuNmuber` INT(3) NOT NULL DEFAULT '48' COMMENT '学生数量',
    PRIMARY KEY(`classid`)
)ENGINE=INNODB CHARSET=utf8
-- foreign 异物 外来的 		foreign key 外键	primary 主要的
-- constraint 约束		references 引用 偏好 首选项  
CREATE TABLE IF NOT EXISTS `student`(
    `id` INT(10) NOT NULL AUTO_INCREMENT COMMENT '学生id',
    `name` VARCHAR(40) NOT NULL COMMENT '学生姓名',
    `classid` INT(10) NOT NULL COMMENT '姓名',
    -- 主键设置  PRIMARY KEY(`id`),
    -- 外键设置  KEY `FK_classid` (`classid`), 
    -- 外键连接 引用 约束
    CONSTRAINT `FK_classid`
    FOREIGN KEY (`classid`) REFERENCES `class`(`classid`)
)ENGINE=INNODB CHARSET=utf8

创建表成功后，添加外键约束

-- foreign 异物 外来的 		foreign key 外键	primary 主要的
-- constraint 约束		preferences 引用 偏好 首选项
CREATE TABLE IF NOT EXISTS `student`(
    `id` INT(10) NOT NULL AUTO_INCREMENT COMMENT '学生id',
    `name` VARCHAR(40) NOT NULL COMMENT '学生姓名',
    `classid` INT(10) NOT NULL COMMENT '姓名',
    -- 主键设置
    PRIMARY KEY(`id`))ENGINE=INNODB CHARSET=utf8
    -- 可以创建表的时候不引用外键
    -- 后面使用alter table add引用外键
    ALTER TABLE `student` 
    ADD CONSTRAINT `FK_classid` FOREIGN KEY(`classid`) REFERENCES `class`(`classid`);

以上操作都是数据库级别的外键，一般不建议使用，避免数据库联系过多造成困扰

字符集不同会导致外键不兼容

2.2DML语言 （重点）

书库意义： 数据储存，数据管理

DML语言： 数据库操作语言

• insert
• update
• delete

2.2.1 插入

INSERT

-- 插入数据操作
-- 格式： INSERT INTO `表名`(`字段名`) VALUES ('值1'),('值2')
INSERT INTO `class`(`classid`) VALUES ('123'),('111')		

-- 给一个字段插入多个值
-- 格式： INSERT INTO `表名`(`字段名`,`字段名2`,`字段名3`...) VALUES ('值1','值2','值3'...)
INSERT INTO `class`(`classid`,`stuNmuber`) VALUES ('1231','62')

-- 给一组字段插入一组值
-- 格式： INSERT INTO `表名`(`字段名`,`字段名2`,`字段名3`...) VALUES ('值1','值2','值3'...),('值1','值2','值3'...),('值1','值2','值3'...)
INSERT INTO `class`(`classid`,`stuNmuber`)
 VALUES ('18811','34'),('12111','44'),('2232','45');
 
-- 不加字段名，直接插入一组数据
INSERT INTO `readers`
VALUES('s100','yan','stu','男','university','15855104243');

• 字段和字段之间必须以英文逗号隔开
• 字段可以省略，但是后面的值必须要一一对应
• 可以同时插入多条数据，VALUES后面的值必须要以逗号隔开

2.2.2 修改

UPDATE

-- 修改一条数据
-- UPDATE `表名` SET `字段名`=值 WHERE [条件]
UPDATE `class` SET `stuNmuber`=100 WHERE `classid`=111

-- 修改多条数据只需要以逗号隔开即可
-- UPDATE `表名` SET `字段名`=值,[`字段名`=值,`字段名`=值...] WHERE [条件]
UPDATE `class` SET `stuNmuber`=60,`female`=30 WHERE `classid`=111;

-- 如果不使用WHERE则会改变所有数据库中字段对应的条目 ！！！

SQL操作符，返回布尔值

• 等于 =
• 不等于 <> 或 !=
• 大于 >
• 小于 <
• 大于等于 >=
• 小于等于 <=
• 在某个范围内 BETWEEN ... AND ...
• 且 AND
• 或 OR

-- 通过多个条件定位数据 使用AND作为连接符
UPDATE `class` SET `stuNmuber`=60,`female`=30 WHERE `classid`=123 AND `stuNmuber`=48;

2.2.3删除

DELETE

语法：delete from 表名 [where 条件 ]

TRUNCATE

清空表： TRUNCATE 表名

参与了索引和视图的表不能使用truncate

-- 删除数据操作
-- 删除指定数据
DELETE FROM `class` WHERE `classid`=123;

-- 删除数据（删除所有数据，不要这么写）
DELETE FROM `class`

-- truncate 清空一个数据表，表的结构和索引约束不会变 
-- truncate截断
TRUNCATE TABLE `class`;

DELETE 和 TRUNCATE 的区别

1. 都可以删除数据条目
2. TRUNCATE 重新设置自增列 计数器归零
3. TRUNCATE 不会影响事务

CREATE TABLE `demo`(
    `id` INT(3) NOT NULL AUTO_INCREMENT,
    `col` INT(3) NOT NULL,
    PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

INSERT INTO `demo`(`col`)
  VALUES (1),(2),(3),(4)
  DELETE FROM `demo`		-- 不会清空主键自增
  TRUNCATE TABLE `demo`		-- 会清空主键自增

TRUNCATE删除问题

1. 在INNODB中，自增列会从1开始（存在内存中，断电丢失）
2. 在MYISAM，继续从上一个子增量开始（存在文件中）

三 DQL查询数据

3.1 通用格式

SELECT语法

SELECT [ALL| DISTINCT]
{* | table.* | [table.field1[AS alias1] [,table.field2 [AS alias2] [,...]]}
FROM table_name [AS table_alias]
     [left | right |inner join table_name2] -- 联表查询
     [WHERE ,...] -- 指定条件 筛选结果
     [GROUP BY ...] -- 指定结果按照那几个字段分组
     [HAVING ...] -- 过滤分组记录必须满足的次要条件
     [ORDER BY ...] -- 指定查询记录按一个或多个条件排序
     [LIMIT {[offect,]row_count | row_countOFFECT offect}]; -- 指定查询的记录从哪条到哪条 分页

[]代表可选 {}代表必选

data query language数据查询语言

• 数据库最重要的一部分，最核心的语言
• 所有查询操作都用DQL
• 使用频率极高

3.2 简单查询

-- 查询所有的学生
SELECT * FROM `student`;

-- 查询指定字段
-- SELECT 字段1 [,字段2,字段3] FROM 表名;
SELECT `studentno`,`studentname` FROM `student`;

-- 给查询字段起别名 字段 [AS 别名] 即可 
-- 可以给字段起别名，也可以给表起别名SELECT `studentno` AS 学生学号, `studentname` AS 学生姓名 FROM `student`;
-- 函数 concat(a,b)SELECT CONCAT('姓名：',`studentname`) AS 新名 FROM `student`;

格式：SELECT 字段1,字段2... FROM 表名

3.3 去重和查询表达式

去重 DISTINCT

-- 去重SELECT * FROM `result`; 
-- 查看所有数据
SELECT `studentno` FROM `result`; 
-- 普通查看数据，显示很多重复数据
SELECT DISTINCT `studentno` FROM `result` -- 去重查看数据，将重复值不显示

查询表达式

SELECT VERSION();  -- 查询系统版本SELECT 199-80+90 AS res;  -- 用于计算SELECT @@auto_increment_increment;  -- 查询自增步长-- 查询表达式SELECT `studentno`,`studentresult` AS 原数据 ,`studentresult`+1 AS 加分后 FROM `result`;

查询表达式 SELECT 表达式 FROM 表名

3.4 WHERE条件子句

作用：检索数据中符合条件的值

搜索的条件由一个或多个表达式组成！ 结果是布尔值

逻辑运算符

逻辑运算符	语法	描述
and &&	a and b a&&b	逻辑与
or ||	a or b a || b	逻辑或
NOT !	not a !a	逻辑非

尽量使用英文字母

-- WHERE 条件判断 写在查询语句之后 用于筛选查询结果

-- 筛选出分数在一定范围内的学生
SELECT `studentno`,`studentresult` FROM `result` 
WHERE `studentresult`>=90 AND `studentresult`<=100;

SELECT `studentno`,`studentresult` FROM `result` 
WHERE `studentresult`>=90 && `studentresult`<=100;

-- 模糊查询
SELECT `studentno`,`studentresult` FROM `result` 
WHERE `studentresult` BETWEEN 90 AND 100;

-- not ! 筛选出学号不等于1000的学生
SELECT `studentno`,`studentresult` FROM `result`
WHERE `studentno`!=1000;

SELECT `studentno`,`studentresult` FROM `result`
WHERE NOT `studentno`=1000;

3.5 模糊查询

比较运算符

运算符	语法	描述
IS NULL	a is null	a为null,结果为真
IS NOT NULL	a is not null	a为not null,结果为真
BETWEEN ... AND ...	a between b and c	若a在b和c之间，结果为真
Like	a like b	SQL匹配，a如果可以匹配到B，结果为真
IN	a in (a1,a2,a3...)	假设a在a1 a1 a3...其中某一个值，结果为真

-- 模糊查询

-- LIKE

-- 查询姓张的同学
-- like结合 通配符 %（任意一个字符） _（一个字符）

-- 查询姓张的同学
SELECT `studentno`,`studentname` FROM `student`
WHERE `studentname` LIKE '张%';

-- 查询所有名只有一个字的人
SELECT `studentno`,`studentname` FROM `student`
WHERE `studentname` LIKE '张_';
-- 查询所有名只有两个字的人
SELECT `studentno`,`studentname` FROM `student`
WHERE `studentname` LIKE '张__';

-- 查询名字里有 四 的人
SELECT `studentno`,`studentname` FROM `student`
WHERE `studentname` LIKE '%四%';


-- IN  （具体的一个或多个值）

-- 按学号查询
SELECT `studentno`,`studentname` FROM `student`
WHERE `studentno` IN (1011,1012,1013);

-- 按地域查询
SELECT `studentno`,`studentname`,`address` FROM `student`
WHERE `address` IN ('北京');

-- 查询地址为空的同学
SELECT `studentno`,`studentname`,`address` FROM `student`
WHERE `address` IS NULL OR `address`='';

-- 查询电话号码非空的同学
SELECT `studentno`,`studentname`,`pone` FROM 
WHERE `phone` IS NOT NULL;

3.6 联表查询

七种join理论

-- ====================== 联表查询 ========================
-- 联表查询 即 两个表的交叉数据的一种方式
-- 比如 学生里面有一部分参加了考试
-- 参加考试的人里面有一部分是学生
-- 我们使用联表查询即可查到学生与参加考试的人的交叉数据 即参加考试的学生数
-- 判断条件，student.studentno = result.studentno

-- join on 连接查询
-- where 等值查询

-- INNER JOIN
-- 查询时可以使用别名查询
SELECT r.studentno,subjectno,studentname 
-- 给 result起别名
FROM result AS r
-- join 连接表 给student起别名
INNER JOIN student AS s
-- 条件判断 判断result的学号和student的学号相等
ON r.`studentno` = s.`studentno`;

-- RIGHT JOIN
-- 查询到了没有参加考试的同学 
-- right join 连接查询之后，以右边（后边）的表为基本做查询
-- 查到的数据 如果右边有但是左边没有 则将没有的数据设置为null
SELECT r.`studentno`,`subjectno`,`studentname` 
FROM `result` AS r
RIGHT JOIN `student` AS s
ON r.`studentno` = s.`studentno`;

-- LEFT JOIN
-- 查询到的数据是所有的学生-考试成绩表
-- 没有参加考试的学生 subjectno和studentname显示都是null
SELECT r.`studentno`,`subjectno`,`studentname`,`studentresult`
FROM `result` AS r
LEFT JOIN `student` AS s
ON r.`studentno` = s.`studentno`;

-- 查询所有缺考学生
SELECT r.`studentno`,`subjectno`,`studentname`,`studentresult`
FROM `result` AS r
LEFT JOIN `student` AS s
ON r.`studentno` = s.`studentno`
WHERE `studentname`IS NULL;

ON 和 WHERE作用

on 连接查询 where 等值查询

多个表之间的联表查询

-- 联表查询结果再联表查询
-- 多个表之间联表查询，可以分步进行，每次联系两个表
SELECT r.`studentno`,`subjectno`,`studentname`
FROM `result` AS r
RIGHT JOIN `student` AS s
ON r.`studentno` = s.`studentno`
INNER JOIN `subject` AS sub
ON r.`subjectno` = sub.`subjectno` -- 字段模糊

3.7 自连接

本表与本表连接，将一张表拆成两张一样的表连接

本表：树状结构表

categoryid	pid	categoryname
2	1	信息技术
3	1	软件开发
5	1	美术设计
8	2	办公信息
4	3	数据库
6	3	web开发
7	5	ps技术

父类

categoryid	categoryname
2	信息技术
3	软件开发
5	美术设计

子类

categoryid	pid	categoryname
8	2	办公信息
4	3	数据库
6	3	web开发
7	5	ps技术

以父类为基准，这是一个树状结构

pid为1的为父类，父类的categoryid即分类类名

pid不为1的为子类，其pid与父类的category相等，则属于该父类

父类	子类
信息技术	办公信息
软件开发	数据库
软件开发	web开发
美术设计	ps技术

-- -----------------自连接查询----------------------
-- 自连接
-- 将一张表拆成两张一样的表去查询
-- 使用 `别名.` 可以分别查询一张表
SELECT a.`categoryname`, b.`categoryname`
-- 给一张表起不同的别名
FROM `category` AS a,`category` AS b
-- where+条件控制输出结果
WHERE a.`categoryid`=b.`pid`

3.8 分页排序分组

排序

升序 ASC 降序 DESC

分组

ORDER BY 通过哪个字段排序

分页

LIMIT 起始值（0开始计数）， 单页值数量

如果只有一个参数，就显示从0到这个位置的数据

-- 查询result表，将所有分数信息从上向下排序
-- 升序ASC 降序DESC
SELECT `studentno`,`subjectno`,`studentresult`
FROM `result`
-- 排序
ORDER BY `studentresult` DESC
-- 分页语法 LIMIT 起始值（0开始计数）， 单页值数量
LIMIT 0,10

3.9 子查询

WHERE （计算值）

本质：在where语句中嵌套一个子查询语句

where (select *from)

-- ---------------------子查询-------------------------
-- 查询 数据库结构1考试的前十的同学成绩 包括学号 成绩 科目编号

-- 正常查询
SELECT `studentno`,`studentresult`,s.`subjectno`
FROM `result` AS r
INNER JOIN `subject` AS s
WHERE `subjectname`='数据库结构-1'
ORDER BY `studentresult` ASC
LIMIT 0,10;

-- 子查询
SELECT `subjectno` FROM `subject` WHERE `subjectname` = '数据库结构-1';

SELECT `studentno`,`studentresult`,`subjectno`
FROM `result`
-- 在where条件中写查询语句可以正常运行
WHERE `subjectno`=(SELECT `subjectno` FROM `subject` WHERE `subjectname` = '高等数学-1');

3.A 分组和过滤

GROUP BY 字段名

可以依照字段分组

HAVING 条件

依照条件筛选分组

-- 查询每门科目的 最高值 最低值 和平均值
-- 筛选出平均分大于70分的科目
SELECT `subjectname`,MAX(`studentresult`) AS 最高分,MIN(`studentresult`) AS 最低分,AVG(`studentresult`) AS 平均分
FROM `result` AS r
INNER JOIN `subject` AS s
ON r.`subjectno`=s.`subjectno`
GROUP BY r.`subjectno` -- 依照subjectno分组，否则会将所有分数都平均
HAVING 平均分 >= 70 -- 依照条件筛选分组

四 MYSQL函数

4.1 基本函数

MySQL 函数 | 菜鸟教程 (runoob.com)

-- ------------数字----------------
SELECT ABS(-89); -- 绝对值
SELECT CEILING(9,3) -- 向上取整
SELECT FLOOR(9.3) -- 向下取整
SELECT READ() -- 随机数0~1
SELECT SIGN(10) -- 判断正负数 返回-1或1

-- -------------字符串----------------
SELECT CHAR_LENGTH('发生的发生的发生的发生的飞·1') -- 字符串长度
SELECT CONCAT('i','love','coding')
SELECT INSERT('123456',1,2,'00000') -- 替换字符串
SELECT LOWER('ASDASD') -- 转小写
SELECT UPPER('sdfff') -- 转大写
SELECT INSTR('adfads','f') -- 返回第一次出现查询索引\
SELECT REPLACE()

-- ---------------系统-------------
SELECT USER(); -- 返回当前用户
SELECT SYSTEM_USER(); -- 返回当前用户
SELECT VERSION(); -- 返回当前用户

4.2 聚合函数

function name	description
COUNT	计数
SUM	求和
AVG	平均值
MAX	最大值
MIN	最小值

-- ================聚合函数===================
SELECT COUNT(studentname) FROM student; -- count(字段),忽略null值
SELECT COUNT(*) FROM student; -- count(*) 不会忽略null值
SELECT COUNT(1) FROM student; -- count(1) 不会忽略null值

SELECT SUM(`studentresult`) FROM `result`; -- SUM求总和
SELECT AVG(`studentresult`) FROM `result`; -- AVG求平均分
SELECT MAX(`studentresult`) FROM `result`; 
SELECT MIN(`studentresult`) FROM `result`; 

4.3 数据库级别的MD5加密

MD5主要增强算法的复杂性和不可逆性

CREATE TABLE IF NOT EXISTS `test`(
  `id` INT(4) NOT NULL,
  `nickname` VARCHAR(30) NOT NULL,
  `pwd` VARCHAR(90) NOT NULL
)ENGINE=INNODB DEFAULT CHARSET=utf8

INSERT INTO `test` VALUES (1,'zhangsan','111111'),(2,'lisi','111111'),(3,'wangwu','111111');

UPDATE `test` SET `pwd`=MD5(111111) WHERE id=1; -- 将test表中id为1 的条目 密码加密

INSERT INTO `test` VALUES (4,'zhaoliu',MD5('111111')); -- 在创建数据的时候就可以直接加密

五 事务

5.1 基本了解

要么都成功，要么都失败

将一组SQL放在一个批次中执行

事务原则：ACID原则 原子性、一致性、隔离性、永久性 （脏读）（幻读）

博客：事务ACID理解_dengjili的专栏-CSDN博客_acid

ACID原则

原子性（Atomicity） 原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。 一致性（Consistency） 事务前后数据的完整性必须保持一致。 隔离性（Isolation） 事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。 持久性（Durability） 持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响

• 隔离导致的问题：
  • 脏读：指一个事务读取了另外一个事务未提交的数据。
  • 不可重复读：在一个事务内读取表中的某一行数据，多次读取结果不同。（这个不一定是错误，只是某些场合不对）
  • 幻读：是指在一个事务内读取到了别的事务插入的数据，导致前后读取数量总量不一致。

5.2 事务的应用

-- ================事务================
-- mysql默认开启事务自动提交
SET autocommit = 0; -- 关闭
SET autocommit = 1; -- 开启（默认）

-- 手动处理事务

SET autocommit = 0; -- 关闭事务自动提交
START TRANSACTION; -- 标记一个事务的开始，这之后所有的sql语句都在同一个事务内

-- sql语句操作
INSERT xx;
INSERT xx;

-- 提交（成功） 持久化
COMMIT
-- 回滚 （失败） 回到原来的状态
ROLLBACK
-- 事务结束
SET autocommit = 1; -- 开启自动提交

SAVEPOINT 保存点名 -- 设置事务保存点

使用事务管理实现转账功能

-- 建库建表
CREATE DATABASE IF NOT EXISTS `shop`;
USE shop;
CREATE TABLE IF NOT EXISTS `account`(
  `id` INT(4) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(50) NOT NULL,
  `money` DECIMAL(20) NOT NULL,
  PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
-- 插入基本数据
INSERT INTO `account`(`name`,`money`) VALUES ('zhangsan',1000),('lisi',500);

-- 事务：zhangsan给lisi转200块

-- 事务处理流程
-- 关闭事务管理
SET autocommit = 0;
-- 标记事务
START TRANSACTION;
UPDATE `account` SET `money`=`money`-200 WHERE `id`=1;
UPDATE `account` SET `money`=`money`+200 WHERE `id`=2;

-- 提交
COMMIT;
-- 回滚
ROLLBACK;
-- 开启事务管理
SET autocommit = 1;

六 索引

MySQL官方对索引的定义为：索引（index）是帮助MySQL高效获取数据的数据结构

索引的本质是：数据结构

6.1 索引分类

• 主键索引（primary key）
  • 唯一标识：主键不可重复
• 唯一索引（unique key）
  • 避免重复的列出现，唯一索引可以重复
• 常规索引（index/key）
• 全文索引（FULLTEXT）

-- 索引的使用
-- 1. 在创建表的时候给表增加索引
-- 2. 创建完毕后增加索引

-- 显示所有索引信息
SHOW INDEX FROM student;

-- explain分析SQL执行情况
EXPLAIN SELECT *FROM `student`;  -- 非全文索引
EXPLAIN SELECT *FROM `student` WHERE MATCH(studentname) AGAINST('张'); -- 全文索引

新增索引

-- 使用alter table创建索引
-- 增加一个全文索引
-- ALTER TABLE 表名 ADD 索引类型 索引名(索引字段)
-- 索引名可选，可以使用多个字段联合索引
ALTER TABLE employee ADD FULLTEXT salary(salary);

-- 使用create index创建索引
-- CREATE UNIQUE INDEX 索引名 ON 表名(索引字段);
CREATE UNIQUE INDEX managerid ON department(managerid);

删除索引

-- alter table 表名 drop index 索引名;
ALTER TABLE employee DROP INDEX salary;

-- drop index 索引名 on 表名;
DROP INDEX managerid ON department;

索引在数据量很小的情况下作用不大，但是在数据量很大的情况下查询效果非常明显

6.2 索引原则

1. 索引不是越多越好
2. 不要对经常变动的数据加索引
3. 小数据量表不需要加索引
4. 索引一般加载常用来查询的字段上

索引的数据结构

Hash类型的索引

Btree：innodb默认类型索引

CodingLabs - MySQL索引背后的数据结构及算法原理

七 数据库管理

7.1 用户管理

SQL命令操作

用户表：mysql.user

本质：读这张表

1. 查看所有用户

   -- 将用户当做表查
   SELECT *FROM mysql.`user`;
   

2. 创建用户

   -- CREATE USER `username` IDENTIFIED BY 'password';
   CREATE USER `myuser` IDENTIFIED BY '123456';
   

3. 修改用户密码

   -- 格式： ALTER USER '用户名'@'主机地址' IDENTIFIED BY '新密码';
   ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';
   

4. 修改用户名

   -- 格式： RENAME USER '旧名'@'主机地址' TO '新名'@'主机地址';
   RENAME USER 'myuser'@'localhost' TO 'yan'@'localhost';
   

5. 用户授权

   -- 授予所有权限，除了GRANT 给别的用户授权
   GRANT ALL PRIVILEGES ON *.* TO 'yan'@'localhost';
   

6. 查看用户权限

   -- SHOW GRANTS FOR '用户名'@'主机地址';
   SHOW GRANTS FOR 'yan'@'localhost'; -- 查看指定用户权限
   

7. 撤销权限

   -- REVOKE ALL PRIVILEGES ON *.* FROM '用户名'@'主机地址';
   REVOKE ALL PRIVILEGES ON *.* FROM yan@localhost;
   

8. 删除用户

   -- DROP USER 用户名@主机地址
   DROP USER yan@localhost;
   

-- ====================权限管理=======================

-- 查看所有用户
SELECT *FROM mysql.`user`;

-- 创建用户 
-- CREATE USER `username` IDENTIFIED BY 'password';
CREATE USER `myuser` IDENTIFIED BY '123456';

-- 修改当前用户密码
-- 格式： ALTER USER '用户名'@'主机地址' IDENTIFIED BY '新密码';
ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';
 
-- 修改用户名
-- 格式： RENAME USER '旧名'@'主机地址' TO '新名'@'主机地址';
RENAME USER 'myuser'@'localhost' TO 'yan'@'localhost';

-- 用户授权 所有权限ALL PRIVILEGES 全部权限 ，库.表
-- 授予所有权限，除了GRANT 给别的用户授权
GRANT ALL PRIVILEGES ON *.* TO 'yan'@'localhost';

-- 查看用户权限
-- SHOW GRANTS FOR '用户名'@'主机地址';
SHOW GRANTS FOR 'yan'@'localhost'; -- 查看指定用户权限

-- 撤销权限
-- REVOKE ALL PRIVILEGES ON *.* FROM '用户名'@'主机地址';
REVOKE ALL PRIVILEGES ON *.* FROM yan@localhost;

-- 删除用户
-- DROP USER 用户名@主机地址
DROP USER yan@localhost;

7.2 MySQL备份

MySQL数据库备份的方式

• 直接拷贝物理文件

• 在可视化工具内手动导出

  • 选中要导出的库或者表，右键备份

• 使用命令行导出 mysqldump

  • # 导出单个文件
    # 格式：mysqldump -h主机地址 -u用户名 -p密码 数据库 表 > 输出路径\输出文件名
    mysqldump -hlocalhost -uroot -p123456 school grade > D:\grade.sql
    
    # 导出多个文件
    # 格式：mysqldump -h主机地址 -u用户名 -p密码 数据库 表1 表2 表3 > 输出路径\输出文件名
    mysqldump -hlocalhost -uroot -p123456 school grade student test> D:\grade.sql
    
    # 导出数据库
    # 格式：mysqldump -h主机地址 -u用户名 -p密码 数据库 > 输出路径\输出文件名
    mysqldump -hlocalhost -uroot -p123456 school > D:\school.sql
    
    # 导入数据库
    # 在登陆并且选中数据库的情况下使用 souce 地址
    source D:\school.sql
    

八 视图

8.1基本概念

• 视图是一个虚拟表，是SQL的查询结果，其内容由查询定义。
• 视图包含一系列带有名称的列和行数据，在使用视图时动态生成。
• 视图的数据变化会影响到基表，基表的数据变化也会影响到视图[insert update delete ] ;
• 创建视图需要create view 权限，并且对于查询涉及的列有select权限；
• 使用create or replace 或者 alter修改视图，那么还需要改视图的drop权限。

8.2 创建视图

语法

-- create view 视图名 as 查询语句 [with check option]
CREATE VIEW dep_view AS SELECT deptid, deptname FROM department;

-- 定义视图以后，可以像查询基本表一样查询视图
SELECT *FROM dep_view;

with check option

可选项，强制通过视图修改的数据必须满足查询条件，这样可以确保数据修改以后仍然可以通过视图查看数据；

如果没有where条件语句则没有必要使用

-- 创建视图
CREATE VIEW emp_view AS SELECT *FROM employee WHERE salary > 4000 WITH CHECK OPTION;
-- 更新视图
UPDATE emp_view SET salary = 3900 WHERE salary = 4100;

运行结果

查询：update emp_view set salary = 3900 where salary = 4100
错误代码： 1369
CHECK OPTION failed 'company.emp_view'

这是因为我们修改后的salary变成了3900不满足创建视图时的大于4000

-- 这样就不报错
UPDATE emp_view SET salary = 4200 WHERE salary = 4100;

8.3 操作视图

查看视图结构

DESC emp_view;

查看视图基本信息

SHOW TABLE STATUS LIKE 'emp_view';

查看视图详细信息

SHOW CREATE VIEW emp_view;

查看数据库中所有视图

SELECT *FROM information_schema.`VIEWS`;

修改视图

alter view 视图名 as select语句;
alter view 视图名 as 视图;

create or replace view 视图名 as 查询语句;

视图的更新和表完全相同，使用update语句和delete语句更新和删除数据

删除视图

drop view 视图名;

九 存储过程和触发器

9.1 SQL语法

变量

-- 1. 常量和变量
-- 创建用户变量
SET @myage = 19;
-- 查看变量
SELECT @myage;
-- 显示所有全局变量
-- global全局 session会话变量 local同session
SHOW GLOBAL VARIABLES;

-- 创建局部变量num=10
DECLARE	num INT DEFAULT 10;

条件判断

-- if else
IF ... THEN ...
ELSEIF ... THEN ...
ELSE ...
END IF;

-- case when
CASE ...
  WHEN ... THEN ...
  WHEN ... THEN ...
END CASE;

修改结束符

-- 修改结束符不为分号
DELIMITER ##
SELECT *FROM employee##
DELIMITER ;

循环

-- 循环
-- while
WHILE 表达式 DO
 ...
END WHILE;

-- repeat
REPEAT 
  ...
UNTIL 表达式
END REPEAT;

9.2 存储过程

创建和调用存储过程

#存储过程和函数
CREATE PROCEDURE avg_salary()
  SELECT AVG(salary) FROM employee;

#调用存储过程
CALL avg_salary();

储存过程传参

#指定参数
#销毁存储过程
DROP PROCEDURE filter_salary;
#创建存储过程
CREATE PROCEDURE filter_salary(IN gz INT)
  SELECT *FROM employee WHERE salary>gz;

#调用
CALL filter_salary(3000);

存储过程输出参数

创建存储过程时，添加输出参数，OUT 字段名 类型

调用存储过程时，传递一个变量，存储过程内给out参数赋值

在调用存储过程时，该变量会被赋值

CREATE PROCEDURE show_courses(IN tname CHAR(6),OUT res INT(2))
SET res=(
SELECT COUNT(*) FROM teachers 
INNER JOIN teach_arr 
ON teachers.`teacherid`=teach_arr.`teacherid`
WHERE teachername=tname);

CALL show_courses("吴晨红",@res);
SELECT @res;
+------+
| @res |
+------+
|   16 |
+------+

销毁存储过程

DROP PROCEDURE `show_courses`;

9.3 存储函数

储存函数和储存过程的区别：

1. 储存函数没有输出参数
2. 储存函数不使用call调用
3. 储存函数必须包含return语句

SET GLOBAL log_bin_trust_function_creators=TRUE;

#创建存储函数
CREATE FUNCTION count_student()
RETURNS INT
RETURN (SELECT COUNT(*) FROM students);
Query OK, 0 rows affected,0 warning(0.25 sec)

#调用
SELECT count_student();
+-----------------+
| count_student() |
+-----------------+
|              43 |
+-----------------+
1 row in set (0.02 sec)

带参的存储函数的创建

#创建带参存储函数，不需要使用out in来修饰
CREATE FUNCTION show_stuMsg(stuname VARCHAR(11),cname CHAR(6))
RETURNS INT
RETURN (
SELECT score FROM score 
WHERE studentid = (SELECT studentid FROM students WHERE studentname = stuname)
AND courseid = (SELECT courseid FROM courses WHERE coursename = cname)
)
Query OK, 0 rows affected,0 warning(0.25 sec)

#调用存储函数
SELECT show_stuMsg("张三","数据结构");
+--------------------------------------+
| show_stuMsg("张三","数据结构")       |
+--------------------------------------+
|                                   85 |
+--------------------------------------+
1 row in set (0.02 sec)

销毁函数

DROP FUNCTION `show_stuMsg`;

9.4 触发器

概述

CREATE TRIGGER 触发器 触发时间 触发事件
ON 表名 FOR EACH ROW 触发器动作

实例

CREATE TRIGGER del_teachers 
AFTER DELETE ON teachers FOR EACH ROW
DELETE FROM teach_arr WHERE teacherid = old.teacherid;
Query OK, 0 rows affected,0 warning(0.25 sec)

代码过长，中间有分号，运行会被MySQL检测语句结束，可以使用DELIMITER来更换结束符，防止报错

DELIMITER //
CREATE TRIGGER update_courseid
AFTER UPDATE ON courses FOR EACH ROW
BEGIN
UPDATE score SET courseid = new.courseid WHERE courseid=old.courseid;
UPDATE teach_arr SET courseid = new.courseid WHERE courseid=old.courseid;
END
Query OK, 0 rows affected,0 warning(0.25 sec)

-- 结束更换分号
DELIMITER ;