Mysql学习笔记（更新至4.12 权限管理和备份）
Mysql
01 使用CMD连接数据库
-- 在控制台连接数据库（需将位置切换到mysql所在地址）
mysql -u root -p
password:12345
-- 修改mysql账户密码及权限，安装配置完后慎用
update mysql.user set authentication_string = password('12345') where user = 'root' and Host = 'localhost';-- 修改用户密码
flush privileges; -- 刷新权限
--------------------------------------------------
-- 所有语句需要使用;作为结尾
show databases; -- 查看所有数据库
use test01; -- 切换数据库 use+数据库名
show tables; --查看数据库中所有的表
describe `user`; --显示某张表的信息
create database test01; -- 创建一个数据库
exit; -- 退出连接
-- 单行注释
-- 下面是多行注释
/*
hello
*/
- 数据库xxx语言 CRUD 增删改查
- DDL 定义
- DML 操作
- DQL 查询
- DCL 控制
02 操作数据库
操作数据库>操作数据库中的表>操作数据库中表的数据
mysql关键字不区分大小写！！
2.1 操作数据库
- 创建数据库
CREATE DATABASE test01; -- 创建数据库01
-- 但问题是如果test01存在，系统会报错
-- 所以一般会加入一个判断条件
CREATE DATABASE IF NOT EXISTS test01;
- 删除数据库
DROP DATABASE IF EXISTS test01;
- 使用数据库
-- tab键上方有一个``，如果数据库的表名或者字段名是一个特殊字符，就需要添加
USE `test01`;
- 查询数据库
SHOW DATABASES; -- 查看所有数据库
2.2 数据库列数据类型
数字
2.3 数据库字段属性（重要）
-
unsigned：
- 无符号的整数
- 声明了该列不能填充负数
-
zerofill：
- 0填充的
- 不足位数在前面用0来填充，例如int(3),5-->005
-
自增
- 通常理解为自增，自动在上一条记录的基础上+1（默认）
- 通常用来设计唯一的主键~index，必须是整数类型
- 可以自定义自增步长
- Navicat中Mysql自增设置在选项中
- 非空 NULL not NULL
- 假如设置为not null ，如果不给他赋值就会报错！
- NULL，如果不填写值，默认就是NULL！
- 默认：
- 设置默认的值
- 例如 sex，默认值为男性，如果没有特别指定该列的值，则会有默认的值！
- 需要注意，在Navicat中，默认值设置，自增，无符号以及填充零都在下方
- 拓展
/*每一个表都必须存在以下五个字段，未来做项目都有用，表示一个记录存在的意义
id 主键
version 乐观锁
is_delete 伪删除
gmt_create 创建时间
gmt_update 更新时间
*/
2.4 创建数据库表（重点）
-- 目标：创建一个school数据库
-- 创建学生表（列，字段） 使用SQL语句创建
-- 学号int 登陆密码varchar(20) 姓名 性别varchar(2) 出生日期(datetime) 家庭住址 email
-- 注意点，使用英文()，表的名称和字段尽可能使用``括起来
-- AUTO_INCREMENT 自增
-- 字符串使用单引号括起来；
-- 所有的语句后面加,(英文的),最后一个不用加
-- PRIMARY KEY 主键，一般一个表只有一个唯一的主键
CREATE TABLE IF NOT EXISTS `studentinformation`(
`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
`name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT'姓名',
`pwd` VARCHAR(20) NOT NULL DEFAULT '12345' COMMENT'密码',
`sex` VARCHAR(2) NOT NULL DEFAULT'男' COMMENT'性别',
`birthday` DATETIME DEFAULT NULL COMMENT'出生日期',
`address` VARCHAR(100) DEFAULT NULL COMMENT'家庭地址',
`email` VARCHAR(50) DEFAULT NULL COMMENT'电子邮箱',
PRIMARY KEY(`id`)
)ENGINE = INNODB DEFAULT CHARSET=utf8
格式
CREATE TABLE [IF NOT EXISTS] `表名`(
`字段名` 列类型 [属性] [索引][注释],
`字段名` 列类型 [属性] [索引][注释],
·······
`字段名` 列类型 [属性] [索引][注释]
)[表类型][字符集设置][注释]
2.5 数据表的类型
-- 关于数据库引擎
/*
INNODB 默认使用~
MYISAM 早些年使用
*/
| MYISAM | INNODB | |
|---|---|---|
| 事务支持 | 不支持 | 支持 |
| 数据行锁定（同步） | 不支持 | 支持 |
| 外键约束 | 不支持 | 支持 |
| 全文索引 | 支持 | 不支持 |
| 表空间大小 | 较小 | 较大，约为前者2倍 |
常规使用操作：
- MYISAM 节约空间，速度较快
- INNODB 安全性高，事务的处理，多表多用户操作
在物理空间的位置
所有的数据库文件都存在data目录下
所以数据库的本质还是文件的存储
Mysql引擎在物理文件上的区别
- InnoDB在数据库表中只有一个*.frm文件，以及上级目录下的ibdata1文件
- MYISAM对应文件
- *.frm 表结构的定义文件
- *.MYD 数据文件（data）
- *.MYI 索引文件（index）
- 这里有必要提到的是，MySQL8开始删除了原来的frm文件，并采用 Serialized Dictionary Information (SDI), 是MySQL8.0重新设计数据词典后引入的新产物,并开始已经统一使用InnoDB存储引擎来存储表的元数据信息。SDI信息源记录保存在ibd文件中。
如何可以查看表结构信息，官方提供了一个工具叫做ibd2sdi，在安装目录下可以找到，可以离线的将ibd文件中的冗余存储的sdi信息提取出来，并以json的格式输出到终端。可参考一篇CSDN博客。https://blog.csdn.net/philipslu/article/details/102499234
设置数据库表的字符集编码
CHARSET = utf8
也可以采取在数据库安装目录下新建一个ini文件的方式进行默认设置。不如不单独设置，系统会默认为一个latin字符集，我的ini文件格式如下：
#设置3306端口
port=3306
#设置mysql的安装目录 ----------是你的文件路径-------------
basedir=E:\WorkappPackage\Mysql\mysql-8.0.32-winx64
#设置mysql数据库的数据的存放目录 ---------是你的文件路径data文件夹自行创建
#datadir=E:\WorkappPackage\Mysql\mysql-8.0.32-winx64\data
#允许最大连接数
max_connections=200
#允许连接失败的次数。
max_connect_errors=10
#服务端使用的字符集默认为utf8mb4
character-set-server=utf8mb4
#创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
#默认使用“mysql_native_password”插件认证
#mysql_native_password
default_authentication_plugin=mysql_native_password
[mysql]
#设置mysql客户端默认字符集
default-character-set=utf8mb4
[client]
#设置mysql客户端连接服务端时默认使用的端口
port=3306
default-character-set=utf8mb4
- 前面有关于文件路径以及data保存路径的设置需要根据实际情况进行调整。后面的端口设置为系统默认的3306。其他相关内容可参考文章：https://blog.csdn.net/weixin_42820850/article/details/129108669
2.6 修改删除表
修改
-- 修改表名：ALTER TABLE 旧表名 RENAME AS 新表名
ALTER TABLE test01 Rename AS test02
-- 增加表的字段：ALTER TABLE 表名 ADD 字段名 列属性
ALTER TABLE test02 ADD age INT(10)
-- 修改表的字段（重命名，修改约束）
-- ALTER TABLE 表名 MODIFY 字段名 列属性[]
ALTER TABLE test02 MODIFY age VARCHAR(10)
-- ALTER TABLE 表名 CHANGE 旧名字 新名字 列属性[]
ALTER TABLE test01 CHANGE age age1 INT(1) -- 字段重命名
-- 删除表的字段
ALTER TABLE test01 DROP age1
- 可以观察到，所有对于表的修改都是以ALTER开头的
删除
-- 删除表(加入判断条件，如果存在再删除)
DROP TABLE IF EXISTS test01
- 需要注意，所有的创建和删除尽量加上判断，以免出现报错
03 MySQL数据管理
3.1 数据库级别外键（了解）
方式一：在创建表的时候，增加外键语句和关联语句等约束（比较复杂）
CREATE TABLE IF NOT EXISTS `studentinformation`(
`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
`name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT'姓名',
`pwd` VARCHAR(20) NOT NULL DEFAULT '12345' COMMENT'密码',
`sex` VARCHAR(2) NOT NULL DEFAULT'男' COMMENT'性别',
`birthday` DATETIME DEFAULT NULL COMMENT'出生日期',
`gradeid` INT(3) NOT NULL COMMENT'年级id',
`address` VARCHAR(100) DEFAULT NULL COMMENT'家庭地址',
`email` VARCHAR(50) DEFAULT NULL COMMENT'电子邮箱',
PRIMARY KEY(`id`),
KEY `FK_gradeid`(`gradeid`), -- 外键名称设置
CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade`(`gradeid`) -- 关联约束设置
)ENGINE = INNODB DEFAULT CHARSET=utf8
CREATE TABLE IF NOT EXISTS `grade`(
`gradeid` INT(3) NOT NULL COMMENT'年级id',
`gradename` VARCHAR(10) NOT NULL COMMENT'年级名称',
PRIMARY KEY(`gradeid`)
)ENGINE = INNODB DEFAULT CHARSET=utf8
- 运行结果如下
- 可以看到此时由于外键关联关系的存在，原表在删除以及对某些字段内容进行修改时都收到了约束
- 删除时需要先删除引用的表，然后再回来删除被引用的表
方法二：正如上面所示，可以在外键部分手动添加，这里不做展开，与上面运行结果类似即可
方法三：
CREATE TABLE IF NOT EXISTS `grade`(
`gradeid` INT(3) NOT NULL COMMENT'年级id',
`gradename` VARCHAR(10) NOT NULL COMMENT'年级名称',
PRIMARY KEY(`gradeid`)
)ENGINE = INNODB DEFAULT CHARSET=utf8
-- 外部创建外键约束
CREATE TABLE IF NOT EXISTS `studentinformation`(
`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
`name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT'姓名',
`pwd` VARCHAR(20) NOT NULL DEFAULT '12345' COMMENT'密码',
`sex` VARCHAR(2) NOT NULL DEFAULT'男' COMMENT'性别',
`birthday` DATETIME DEFAULT NULL COMMENT'出生日期',
`gradeid` INT(3) NOT NULL COMMENT'年级id',
`address` VARCHAR(100) DEFAULT NULL COMMENT'家庭地址',
`email` VARCHAR(50) DEFAULT NULL COMMENT'电子邮箱',
PRIMARY KEY(`id`)
)ENGINE = INNODB DEFAULT CHARSET=utf8
-- 创建表的时候没有外键关系
-- 外部添加外键约束
ALTER TABLE `studentinformation`
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade`(`gradeid`)
-- 结构
ALTER TABLE 表
ADD CONSTRAINT 约束名 FOREIGN KEY(`作为外键的列`)
- 但需要注意的是，以上的操作均为物理外键，是在数据库级别进行操作的外键，一般不推荐使用。（避免数据库过多造成困扰）
最佳实践
- 数据库就是单纯的表，只用来存数据，只有行（数据）和列（字段）
- 如果想使用多张表的关联操作，即使用外键（在程序中实现）
参考文章：https://www.cnblogs.com/rjzheng/p/9907304.html
3.2 DML语言（需要全部记住）
数据库意义：数据存储、数据管理
DML语言：数据操作语言
- INSERT
- UPDATE
- DELETE
3.3 添加INSERT
格式
-- 插入语句（添加）
-- INSERT INTO 表名 (`字段名1`,`字段名2`,`字段名3`) VALUES('第一条数据字段1数据','第一条数据字段2数据','第一条数据字段3数据'),('第二条数据字段1数据','第二条数据字段2数据','第二条数据字段3数据')····
-- 例：
INSERT INTO `grade`(`gradename`)VALUES('大一')
-- 需注意的是，跟在表名后面的括号内容可以选择表中的部分字段，但是需要观察表结构，那些非空且没有默认值的或者没设置自增的字段必须包括在内，否则会报错。还需注意，选定了括号内容后，VALUES后面的括号内的结构需与前面保持一致。如果想一次性添加多条数据，需要在括号中间用“,”隔开。
-- 错误示例：
INSERT INTO `grade` VALUES('大二')
-- 此处在表名`grade`后面没有括号，因此Mysql就会默认格式为全部字段。依照上面所述，数据库会对VALUES后面数据进行匹配。因此会报错。
-- 正确示例：
INSERT INTO `grade` VALUES(2,'大二')
-- 插入多条数据
INSERT INTO `grade`(`gradename`)
VALUES ('大三'),('大四')
- 再次强调，必须注意标点符号的中英文格式！
注意事项：
- 字段和字段之间用英文逗号隔开
- 字段是可以省略的，但是后面括号内的值需要一一对应，不能少
- 可以同时插入多条数据，VALUES后面的值，需要使用隔开即可
3.4 修改UPDATE
UPDATE 修改谁 set 原来的值 = 新值 WHERE 条件
-- 修改某条数据的某一个字段信息的格式
UPDATE `studentinformation1` SET `pwd`= 11111 WHERE `id` = 2200935
-- 需要注意，如果不加入判断条件，就会修改整张表的全部该字段信息
UPDATE `studentinformation1` SET `address`='XX大学XX校区'
-- 如果想要一次修改某条数据的多个字段信息，则需要在不同字段之间用逗号隔开
UPDATE `studentinformation1` SET `name` = 'wutong',`pwd` = 'wt677099' WHERE `id`= 2200936
-- 语法：
-- UPDATE 表名 SET column_name1 = value1,column_name2 = value2······WHERE 条件
条件：WHERE 子句 运算符 id等于某个值，大于某个值，在某个区间内修改.....
注意，WHERE后的判断语句会返回一个布尔值
| 操作符 | 含义 | 示例 | 结果 |
|---|---|---|---|
| = | 等于 | 5=6 | false |
| <>或!= | 不等于 | 5<>6 | true |
| > | 大于 | 5>6 | false |
| < | 小于 | 5<6 | true |
| <= | 小于等于 | 5<=6 | true |
| >= | 大于等于 | 5>=6 | false |
| BETWEEN...AND... | 在范围内 | BETWEEN 2 AND 5 | [2,5] |
| AND | 与 | 3>1 AND 3>5 | false |
| OR | 或 | 3>1 OR 3>5 | true |
-- 多个条件查询数据示例：
UPDATE `studentinformation1` SET `name` = 'ZhangSan' WHERE `id`<5 AND `pwd` = 12347
注意：
- column_name是数据库的列，尽量带上``
- 条件，筛选的条件，如果没有指定，则会修改所有的列
- value，是一个具体的值，也可以是一个变量
- 多个设置属性之间，使用英文逗号隔开
- 在编写条件时一定要注意参数的属性
3.5 删除DELETE
DELETE命令
语法：DELETE FROM表名 [where 条件]
-- 删除数据（避免这样写，会把数据全部干掉，而且会有遗留问题）
DELETE FROM `test02`
-- 删除指定数据
DELETE FROM `test02` WHERE `id`= 1
TRUNCATE命令
- 作用：完全清空一个数据库表，表的结构和索引约束不会变！而且不影响事务（后面再讲）
-- 清空 test02表
TRUNCATE TABLE `test02`
DELETE 和 TRUNCATE 的区别
-
相同点：都能删除数据，都不会删除表结构
-
不同（在InnoDB引擎下）：
-
TRUNCATE 重新设置自增列计数器，即下次再插入数据时会从1开始，而DELETE会从上次的自增终点开始
- 观察DELETE操作后使用INSERT插入数据
- 观察TRUNCATE操作后使用INSERT插入数据
- 测试源码如下：
CREATE TABLE IF NOT EXISTS `test02`( `id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '序号', `name` VARCHAR(10) NOT NULL, PRIMARY KEY(`id`) ) ENGINE = InnoDB DEFAULT CHARSET = utf8 INSERT INTO `test02`(`name`) VALUES('张三'),('李四'),('王五') DELETE FROM `test02` WHERE `id`= 2 DELETE FROM `test02` TRUNCATE TABLE `test02` -
TRUNCATE 不会影响事务
-
了解内容：DELETE删除的问题，重启数据库现象
- InnoDB 自增列会从1开始（存在内存当中，断点即失）
- MyISAM 继续从上一个自增量开始（存在文件中的，不会丢失）
04 DQL查询数据（最重点）
4.0 预备
设计一个案例数据库，之后操作可能都围绕这个
CREATE DATABASE `studentmanage`;
USE `studentmanage`;
DROP TABLE IF EXISTS `grade`;
CREATE TABLE `grade`(
`GradeID` INT(11) NOT NULL AUTO_INCREMENT COMMENT'年级编号',
`GradeName` VARCHAR(50) NOT NULL COMMENT'年级名称',
PRIMARY KEY(`GradeID`)
)ENGINE = InnoDB DEFAULT CHARSET = utf8;
INSERT INTO `grade`(`GradeID`,`GradeName`) VALUES(1,'大一'),(2,'大二'),(3,'大三'),(4,'大四'),(5,'预科');
drop table if exists `student`;
create table `student`(
`studentno` int(4) not null comment '学号',
`loginpwd` varchar(20) default null,
`studentname` varchar(20) default null comment '学生姓名',
`sex` tinyint(1) default null comment '性别，0或1',
`gradeid` int(11) default null comment '年级编号',
`phone` varchar(50) not null comment '联系电话，允许为空',
`address` varchar(255) not null comment '地址，允许为空',
`borndate` datetime default null comment '出生时间',
`email` varchar (50) not null comment '邮箱账号允许为空',
`identitycard` varchar(18) default null comment '身份证号',
primary key (`studentno`),
unique key `identitycard`(`identitycard`),
key `email` (`email`)
)engine=myisam default charset=utf8;
DROP TABLE IF EXISTS `subject`;
CREATE TABLE `subject`(
`subjectno`INT(11) NOT NULL auto_increment COMMENT '课程编号',
`subjectname` VARCHAR(50) DEFAULT NOT NULL COMMENT '课程名称',
`classhour` INT(4) DEFAULT NOT NULL COMMENT '学时',
`gradeid` INT(4) DEFAULT NOT NULL COMMENT '年级编号',
PRIMARY KEY (`subjectno`)
)ENGINE = InnoDB AUTO_INCREMENT = 19 DEFAULT CHARSET = utf8;
DROP TABLE IF EXISTS `result`;
CREATE TABLE `result`(
`studentno` INT(4) NOT NULL COMMENT '学号',
`subjectno` INT(4) NOT NULL COMMENT '课程编号',
`examdate` DATETIME NOT NULL COMMENT '考试日期',
`studentresult` INT(4) NOT NULL COMMENT '考试成绩',
KEY `subjectno` (`subjectno`)
)ENGINE = InnoDB DEFAULT CHARSET = utf8;
INSERT INTO `subject`(`subjectno`,`subjectname`,`classhour`,`gradeid`)VALUES
(1,'高等数学-1',110,1),
(2,'高等数学-2',110,2),
(3,'高等数学-3',100,3),
(4,'高等数学-4',130,4),
(5,'C语言-1',110,1),
(6,'C语言-2',110,2),
(7,'C语言-3',100,3),
(8,'C语言-4',130,4),
(9,'Java程序设计-1',110,1),
(10,'Java程序设计-2',110,2),
(11,'Java程序设计-3',100,3),
(12,'Java程序设计-4',130,4),
(13,'数据库结构-1',110,1),
(14,'数据库结构-2',110,2),
(15,'数据库结构-3',100,3),
(16,'数据库结构-4',130,4),
(17,'C#基础',130,1);
INSERT INTO `student`
VALUES(1000,'zhangsan123','张三',1,1,13800004196,'十舍111','1999-01-01','zhangsan@qq.com','123456198001011234'),
(1001,'lisi123','李四',1,2,13700002826,'十舍115','1999-03-12','lisi@qq.com','879997879979979'),
(1002,'wangwu123','王五',1,3,15500003231,'十舍223','1999-05-04','wangwu@qq.com','29565236989454'),
(1003,'laoliu123','老六',1,5,13300009825,'十舍342','1999-06-11','laoliu@qq.com','98898216546546'),
(1004,'xiaomei123','小美',0,2,13200006032,'八舍102','1999-08-15','xiaomei@qq.com','32165468979797'),
(1005,'xiaonuan123','小暖',0,4,13500007472,'八舍238','1999-10-21','xiaonuan@qq.com','87651351654998'),
(1006,'xiaoqian123','小倩',0,1,15100004312,'八舍313','1999-11-09','xiaoqian@qq.com','32165489756544'),
(1007,'xiaohanhan123','小憨憨',1,3,17100005169,'十舍250','1999-02-28','xiaohanhan@qq.com','87979789987781');
-- 学生成绩这部分数据根据功能需要自行继续添加
INSERT INTO `result`
VALUES(1000,1,'2017-12-26',91),
(1000,3,'2018-12-17',86),
(1000,5,'2017-12-15',93),
(1000,7,'2018-12-22',91),
(1000,9,'2017-12-19',82),
(1000,10,'2017-06-08',72),
(1000,15,'2018-12-11',83),
(1000,16,'2019-06-25',77),
(1000,17,'2017-12-23',64),
(1001,1,'2017-12-26',93),
(1002,3,'2018-12-17',79),
(1003,5,'2017-12-15',80),
(1004,7,'2018-12-22',73),
(1005,9,'2017-12-19',69),
(1006,10,'2017-06-08',81),
(1002,15,'2018-12-11',61),
(1004,16,'2019-06-25',86),
(1006,17,'2017-12-23',91);
4.1 DQL简介
- Data Query Language，缩写DQL，直翻就是数据查询语言
- 数据库中所有的查询语句都用 SELECT
- 这是数据库操作中最核心最重要的部分
- SELECT语句众多，简单复杂查询都可以实现
- 是数据库操作中使用频率最高的语句
4.2 SELECT初识
-- 查询某张表中的全部信息
SELECT * FROM student
-- 查询某张表中指定字段的信息 SELECT 字段名1，字段2...FROM 表名
SELECT `studentno`,`studentname` FROM student
-- 查询某张表中指定字段的信息，并将部分字段名替换掉，使用AS语句
SELECT `studentno` AS 学号,`studentname` AS 学生姓名 FROM student
-- 利用CONCAT(A,B)函数合并两个字段的信息并展示
SELECT CONCAT(`studentno`,`studentname`) AS 姓名 FROM student
有时候，列名字不是那么的易于理解，可以使用AS取别名帮助理解
去重 DISTINCT
作用：去除SELECT查询出来的结果中重复的数据，重复的数据只显示一条。
-- 查询有哪些同学参加了考试
SELECT * FROM student
SELECT `studentno` FROM student
SELECT DISTINCT `studentno` AS 学号 FROM student
数据库的列（表达式）
-- 查询数据库版本（函数）
SELECT VERSION();
-- 计算结果（表达式）
SELECT 100*3-1 AS 计算结果
-- 查询某个变量的大小
SELECT @@AUTO_INCREMENT_INCREMENT
-- 对数据库原数据进行操作
SELECT `studentno` AS 学号,`studentResult`+1 AS 修正成绩 FROM result
数据库中的表达式：文本值，列，NULL，函数，计算表达式，系统变量......
SELECT 表达式 FROM 表
4.3 WHERE条件子句
作用：检索数据中符合条件的值（前面其实已经使用过了，就相当于if）
逻辑运算符
| 运算符 | 语法 | 描述 |
|---|---|---|
| AND && | a and b a&&b | 只有a和b均为真，结果为真 |
| OR || | a or b a||b | a和b只要有一个为真，结果为真 |
| NOT ! | NOT a=b a!=b | a不等于b则为真 |
-
完整的逻辑运算符可以参考Mysql 8.0版本的Referrence，下载地址如下https://dev.mysql.com/doc/refman/8.0/en/non-typed-operators.html
-
相关内容在第12章的Table12.1中
-
此外，在sql语句中推荐使用左边的，尽量少使用符号
SELECT `studentno` AS 学号,`studentresult` AS 修正成绩 FROM result WHERE `studentresult` BETWEEN 80 AND 90;
SELECT `studentno` AS 学号,`studentresult` AS 修正成绩 FROM result WHERE `studentresult` >=85 AND `studentresult`<=95;
SELECT `studentno` AS 学号,`studentresult` AS 修正成绩 FROM result WHERE `studentresult` >=85 OR `studentresult`<=75;
SELECT `studentno` AS 学号,`studentresult` AS 修正成绩 FROM result WHERE NOT `studentno`=1000;
SELECT `studentno` AS 学号,`studentresult` AS 修正成绩 FROM result WHERE NOT `Borndate` = NULL;
模糊查询：比较运算符
| 运算符 | 语法 | 描述 |
|---|---|---|
| IS NULL | a IS NULL | 如果操作符为null，则返回真 |
| IS NOT NULL | a IS NOT NULL | 如果操作符为非null，则返回真 |
| BETWEEN | a BETWEEN b AND c | 如果a在b和c之间，则返回真 |
| LIKE | a LIKE b | SQL匹配，如果a匹配b，则返回真 |
| IN | a IN (a1,a2,a3...) | 假设a在a1或者a2...其中的某一个值中，则返回真 |
-- =================================================模糊查询=================================================
-- ===================================LIKE===================================
-- 使用LIKE运算符进行SQL匹配，需要注意 % 和 _ 的使用
-- % 指之后的全部字符，而 _ 只代指一个字符
-- 查询学生姓名中姓小的
SELECT `studentno` AS 学号,`studentname` AS 学生姓名 FROM student WHERE `studentname` LIKE '小%';
-- 查询学生姓名中叫小X
SELECT `studentno` AS 学号,`studentname` AS 学生姓名 FROM student WHERE `studentname` LIKE '小_';
-- 查询学生姓名是两个字的
SELECT `studentno` AS 学号,`studentname` AS 学生姓名 FROM student WHERE `studentname` LIKE '__';
-- 查询学生姓名中间带憨字的
SELECT `studentno` AS 学号,`studentname` AS 学生姓名 FROM student WHERE `studentname` LIKE '%憨%';
-- 查询住在十舍的学生
SELECT `studentno` AS 学号,`studentname` AS 学生姓名 FROM student WHERE `address` LIKE '十舍%';
-- ====================================IN====================================
-- 关于IN的使用个人人为更倾向于多精确搜索，即给出某个值的可能取值然后去进行精确匹配，即一定要和所给值完全匹配才可
-- 在IN的语句中，%和_不好使
-- 查询学生学号为1001,1002和1003三人的姓名
SELECT `studentno` AS 学号,`studentname` AS 学生姓名 FROM student WHERE `studentno` IN (1001,1002,1003);
-- 查询学生年级在大一、大二和大三的姓名
SELECT `studentno` AS 学号 ,`studentname` AS 学生姓名 FROM student WHERE `gradeid` IN (1,2,3);
-- ====================================NULL & NOT NULL====================================
-- 正如前面介绍，NULL和NOT NULL是用来看操作符是否为空
-- 查询email为空的学生
SELECT `studentno` AS 学号,`studentname` AS 学生姓名 FROM student WHERE `email` IS NULL;
-- 查询borndate不为空的学生
SELECT `studentno` AS 学号,`studentname` AS 学生姓名 FROM student WHERE `borndate` IS NOT NULL;
4.4 联表查询
-- ===========================================JOIN 二表查询===========================================
-- 从学生表和成绩表中联查学生学号，姓名，考试科目代号以及考试结果
/*思路
1、需要分析查询字段分别来自哪些数据表
2、分析数据表中有哪些交叉字段
3、分析采取哪种交叉方式
FROM A JOIN B ON 条件
·······WHERE 条件 针对等值查询，即表中对应字段内容一致
*/
SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult`
FROM student AS s
INNER JOIN result AS r
WHERE s.studentno = r.studentno
SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult`
FROM student s
INNER JOIN result r
WHERE s.studentno = r.studentno
SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult`
FROM student s
LEFT JOIN result r
ON s.studentno = r.studentno
SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult`
FROM student s
RIGHT JOIN result r
ON s.studentno = r.studentno
| 操作 | 描述 |
|---|---|
| INNER JOIN | 如果表中至少有一个匹配，则返回行 |
| LEFT JOIN | 会从左表中返回SELECT中所有左表有的值，然后从右表中找出能符合ON条件的 |
| RIGHT JOIN | 会从右表中返回SELECT中所有右表有的值，然后从左表中找出能符合ON条件的 |
-- ===========================================JOIN 多表查询===========================================
-- 从学生表、科目表和成绩表中查询学生学号、姓名，考试科目名称和考试成绩
SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM student s
INNER JOIN result r
ON s.studentno = r.studentno
INNER JOIN subject sub
ON r.subjectno = sub.subjectno
-- 要查询数据库中的哪些字段都放在SELECT中
-- 相关字段关联的表中查 FROM 表XXX JOIN 连接的表 ON 交叉条件
-- 从两表联查开始逐渐增加，需要注意INNER RIGHT 和 LEFT的区别。
- Mysql提供了七种JOIN方式，具体构型以及如何编写SQL语句如下图：
参考文献：https://blog.csdn.net/cold___play/article/details/103202486
自连接
自己的表和自己的表相连接，核心：梳理内部表的关系，将一张表按照内部关系拆分开来即可
-- 新建一张表category，用于自连接分析，运行结果如下图所示
DROP TABLE IF EXISTS `category`;
CREATE TABLE `category`(
`categoryid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主题id',
`pid` INT(10) NOT NULL COMMENT '父id',
`categoryname` VARCHAR(50) NOT NULL COMMENT '主题名字',
PRIMARY KEY (`categoryid`)
) ENGINE=INNODB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;
INSERT INTO `category` (`categoryid`, `pid`, `categoryname`)
VALUES ('2','1','信息技术'),
('3','1','软件开发'),
('5','1','美术设计'),
('4','3','数据库'),
('8','2','办公信息'),
('6','3','web开发'),
('7','5','ps技术');
父类
| Categoryid | CategoryName |
|---|---|
| 2 | 信息技术 |
| 3 | 软件开发 |
| 5 | 美术设计 |
子类表
| Categoryid | Pid | CategoryName |
|---|---|---|
| 8 | 2 | 办公信息 |
| 4 | 3 | 数据库 |
| 6 | 3 | web开发 |
| 7 | 5 | ps技术 |
操作输出结果
| 父类项目 | 子类项目 |
|---|---|
| 软件开发 | 数据库 |
| 软件开发 | web开发 |
| 美术设计 | ps技术 |
| 信息技术 | 办公信息 |
-- 自连接实际上就是 FROM A a1 INNER JOIN A a2
-- 需要注意在自连接时一定要对前后两张表区别命名
-- 输出正确的关键在于理清表内部关系
SELECT c1.`categoryname` AS '父类项目',c2.`categoryname` AS '子类项目'
FROM category c1
INNER JOIN category c2
WHERE c1.`categoryid` = c2.`pid`
-- 联查学生学号、学生姓名和所在年级名称
-- 关联数据表 student和grade
SELECT `studentno` AS '学号',`studentname` AS '学生姓名' ,`GradeName` AS '年级名称'
FROM `student` s
INNER JOIN `grade` g
ON s.`gradeid`=g.`gradeid`
-- 联查课程名称和所在年级名称
-- 关联数据表grade和subject
SELECT `gradename` AS '学期名称',`subjectname` AS '科目名称'
FROM `grade` g
INNER JOIN `subject` sub
ON g.gradeid = sub.gradeid
4.5 排序及分页
ORDER BY 排序需要使用的语句
-
ASC ：升序 DESC ：降序
-
查询结果按照成绩排列
-- =======================ORDER BY=======================
-- DESC降序排列
SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM student s
INNER JOIN result r
ON s.studentno = r.studentno
INNER JOIN subject sub
ON r.subjectno = sub.subjectno
WHERE r.subjectno = 1
ORDER BY studentresult DESC
-- ASC升序排列
SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM student s
INNER JOIN result r
ON s.studentno = r.studentno
INNER JOIN subject sub
ON r.subjectno = sub.subjectno
WHERE r.subjectno = 1
ORDER BY studentresult ASC
分页
- 分页作用：缓解数据库压力，给人体验更好
- 现在网页中一些图片网站采取瀑布流的方式
-- 分页，每页只显示5条数据
-- 语法：LIMIT 当前起始数据位置，页面大小
SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM student s
INNER JOIN result r
ON s.studentno = r.studentno
INNER JOIN subject sub
ON r.subjectno = sub.subjectno
WHERE r.subjectno = 1
ORDER BY studentresult ASC
LIMIT 0,5
-- 第一页 LIMIT 0,5 起始位计算(1-1)*5
-- 第二页 LIMIT 5,5 起始位计算(2-1)*5
-- 第三页 LIMIT 10,5 起始位计算(3-1)*5
-- ································
-- 第n页 LIMIT (n-1)*5,5 起始位计算(n-1)*5
-- 格式总结
-- 【pagesize:页面大小】
-- 【(n-1)*pagesize:起始值】
-- 【n:当前页】
-- 【数据总数/页面大小 = 总页数】
-- 查询 JAVA第一学年 课程总成绩排名前5，而且分数要大于80分的同学（学号，姓名，课程名称，分数）
SELECT s.`studentno` AS 学号 ,`studentname` AS 姓名 ,`subjectname` AS 课程名称 ,`studentresult` AS 分数
FROM student s
INNER JOIN result r
ON s.studentno = r.studentno
INNER JOIN subject sub
ON r.subjectno = sub.subjectno
WHERE r.subjectno = 1 AND r.studentresult >= 80
ORDER BY studentresult DESC
LIMIT 0,5
4.6 子查询&嵌套查询
where（这个值是计算出来的）
本质：==在where语句中嵌套一个子查询语句
-- =================where=================
-- 1、查询高等数学-1的所有考试结果（学号，科目编号，成绩），降序排列
-- 方式一：采取联表查询方式
SELECT s.`studentno` AS 学号,r.`subjectno` AS 科目编号,`studentresult` AS 成绩
FROM student s
INNER JOIN result r
ON s.studentno = r.studentno
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
WHERE sub.subjectname = '高等数学-1'
ORDER BY studentresult DESC
-- 方式二：采取子表查询方式
SELECT `studentno` AS 学号,`subjectno` AS 科目编号,`studentresult` AS 成绩
FROM result
WHERE subjectno = (SELECT `subjectno` FROM `subject`
WHERE `subjectname` = '高等数学-1')
ORDER BY studentresult DESC
-- 对方法二做分解实际上可以分为2步
-- 首先观察问题可发现，所需要查询的字段都在result表中
-- 但是需要做高等数学-1的匹配，因此需要调用subject表
- 需要在注意的是，子查询这部分使用的前提是，读取的数据不包含该表信息，但该表的信息参与对输出结果的约束。比如上面这个例子可以发现，查询的学号，科目编号，成绩均可以在一张表中查到，只不过查询条件要从高等数学-1先查到学科编号 ，再查到其他信息，因此可以使用子查询。
- 如下问题针对跨表信息就不能使用子查询
-- 2、查询高等数学-1的所有考试结果（姓名，科目编号，成绩），降序排列
SELECT s.`studentname` AS 学号,`subjectno` AS 科目编号,`studentresult` AS 成绩
FROM result r
INNER JOIN student s
WHERE subjectno = (SELECT `subjectno` FROM `subject`
WHERE `subjectname` = '高等数学-1') AND s.studentno = r.studentno
ORDER BY studentresult DESC
- 可以看到，由于需要查询学生姓名，因此单独一张result表就不够了，需要使用INNER JOIN查询student表
-- 3、查询高等数学-1分数不少于90的学生学号和姓名
SELECT `studentno` AS 学号, `studentname` AS 姓名
FROM student
WHERE studentno IN (
SELECT studentno FROM result
WHERE studentresult >= 90 && `subjectno` = (
SELECT subjectno FROM `subject`
WHERE subjectname = '高等数学-1'
)
)
- 以上就是嵌套查询，实际上就是不断的在WHERE语句中使用较为复杂的SELECT语句查询相关的信息。
- 可以这么理解，使用INNER JOIN就是相当于把若干张表的信息先统一放在一个大池子里，然后再使用WHERE语句查找符合条件的信息；然而使用子查询和嵌套查询相当于在一个池子里找信息，但条件约束需要借用旁边池子里的信息。
-- 4、查询高等数学-1前3名的信息（学号，姓名，分数）
SELECT s.studentno AS 学号, studentname AS 姓名, studentresult AS 考试分数
FROM student s
INNER JOIN result r
WHERE r.subjectno = (SELECT subjectno FROM `subject` WHERE subjectname = '高等数学-1') AND s.studentno = r.studentno
ORDER BY studentresult DESC
LIMIT 0,3
05 Mysql函数
官方参考文档：https://dev.mysql.com/doc/refman/8.0/en/
5.1 常用函数（不常用）
-- ================================常用函数===================================
-- 数学运算
SELECT ABS(-10) -- 取绝对值
SELECT CEILING(8.1) -- 向上取整
SELECT FLOOR(8.1) -- 向下取整
SELECT RAND() -- 随机数
SELECT SIGN(0) -- 取数字符号，>0 1; <0 -1; =0 0
-- 字符串函数
SELECT CHAR_LENGTH('我叫XX，在东北大学读研') -- 字符串长度
SELECT CONCAT('我叫XX','，','在东北大学读研') -- 拼接字符串
SELECT INSERT('我叫XX，在东北大学读研',11,2,'信息科学与工程学院读研') -- 查询位置，从起始位置替换部分字符串为新内容
SELECT LOWER('WuTong') -- 字符串全变为小写字母
SELECT UPPER('WuTong') -- 字符串全变为大写字母
SELECT INSTR('WuTong','T') -- 确定字符串中某个字符的位置
SELECT REPLACE('锄禾日当午','当午','傍晚') -- 替换字符串中某部分字段的内容
SELECT SUBSTR('我爱我的家乡南昌' FROM 3 FOR 6) -- 读取字符串中某一段字符
-- 时间和日期函数
SELECT CURRENT_DATE()
SELECT CURDATE() -- 前两个是查看当前日期
SELECT CURRENT_TIME()
SELECT CURTIME() -- 这两个是查看当前时间
SELECT NOW() -- 获取当前时间（年月日+小时分钟秒）
SELECT LOCALTIME() -- 本地时间
SELECT SYSDATE() -- 系统时间
SELECT YEAR(NOW())
SELECT MONTH(NOW())
SELECT DAY(NOW())
SELECT HOUR(NOW())
SELECT MINUTE(NOW())
SELECT SECOND(NOW())
-- 系统
SELECT SYSTEM_USER()
SELECT USER()
SELECT VERSION()
5.2 聚合函数（常用）
| 函数名称 | 描述 |
|---|---|
| COUNT() | 计数 |
| SUM() | 求和 |
| AVG() | 平均值 |
| MAX() | 最大值 |
| MIN() | 最小值 |
| ········· | ········· |
-- ================================ 聚合函数 ================================
-- 统计数据库中有多少条满足要求的数据
SELECT COUNT( DISTINCT `studentno`) FROM result; -- 使用COUNT(字段名)，会忽略所有NULL
SELECT COUNT(*) AS 学生数量 FROM student; -- 使用COUNT(*)或COUNT(1)，不会忽略所有NULL
SELECT COUNT(1) AS 学生数量 FROM student;
-- 使用COUNT(*)实际上是对所有数据进行遍历，只要是满足条件的数据均会计数；但COUNT(1)是把满足条件的数据填1，然后计数，不会遍历
-- 详细内容可参考博客：https://blog.csdn.net/iFuMI/article/details/77920767
-- 求某个科目的分数总和
SELECT SUM(`studentresult`)
FROM result
WHERE `subjectno` = 1;
-- 求某个科目的平均值
SELECT AVG(`studentresult`)
FROM result
WHERE `subjectno` = 1;
-- 求某个科目的分数最高
SELECT MAX(`studentresult`)
FROM result
WHERE `subjectno` = 1;
-- 求某个科目的分数总和
SELECT MIN(`studentresult`)
FROM result
WHERE `subjectno` = 1;
-- 练习1：查找数据表中每科目的最高分最低分以及平均分
SELECT s.`subjectname` AS 学科名称, MAX(`studentresult`) AS 最高分 , MIN(`studentresult`) AS 最低分 , AVG(`studentresult`) AS 平均分
FROM result r
INNER JOIN `subject` s
WHERE s.subjectno = r.subjectno
GROUP BY r.subjectno;
4.7 分组和过滤（补充知识）
- 根据前面的SELECT语句结构图可以看到，在WHERE和ORDER BY中间还有两个关键词—GROUP BY 和HAVING ，这两个关键词分别对应分组和过滤
- 分组的语法为 GROUP BY + 字段名 ， 即根据表中某一个字段的信息对所有数据进行分组
- 过滤的语法为HAVING + 条件 ， HAVING 的作用和WHERE以及ON类似，均是添加条件。但不同的是，HAVING针对的是在上面分组后进行的一些操作，即出现位置以及约束目标不相同
-- 结合5.2聚合函数的练习1，衍生出一个练习
-- 练习2：：查找数据表中平均分超过80的每科目最高分最低分以及平均分
SELECT s.`subjectname` AS 学科名称, MAX(`studentresult`) AS 最高分 , MIN(`studentresult`) AS 最低分 , AVG(`studentresult`) AS 平均分
FROM result r
INNER JOIN `subject` s
WHERE s.subjectno = r.subjectno
GROUP BY r.subjectno
HAVING 平均分 > 80;
- 可以看到，在HAVING语句处，可以使用AVG(
studentresult)，也可以使用 平均分。说明此时对于AVG(studentresult)而言结果是已经计算完毕了，在这里仅对处理完的数据进行一个筛选操作。
5.3 数据库级别的MD5加密（拓展）
- 什么是MD5
- 主要增强算法复杂度和不可逆性
- https://baike.baidu.com/item/MD5/212708
- MD5不可逆，但具体值的MD5是可逆的
-- ======================== MD5测试 ========================
CREATE TABLE `TestMD5`(
`id` INT(4) NOT NULL,
`name` VARCHAR(10) NOT NULL,
`pwd` VARCHAR(50) NOT NULL,
PRIMARY KEY (`id`)
)
-- 明文数据插入
INSERT INTO TestMD5 VALUES(1,'张三','12345'),(2,'李四','12346'),(3,'王五','12347')
-- 更新密码至MD5()加密
UPDATE TestMD5 SET pwd = MD5(pwd)
-- 插入时即进行加密
INSERT INTO TestMD5 VALUES(4,'李明',MD5('12348'))
-- 二次加密
UPDATE TestMD5 SET pwd = MD5(pwd)
-- 查询匹配
SELECT * FROM TestMD5 WHERE `name` = '张三' AND pwd = MD5(MD5('12345'))
5.4 SELECT总结
- 首先再次回看SELECT的语法
SELECT [ALL| DISTINCT ] -- 这里是注明针对后面提取的字段是全部提取还是提取不同
{*|table.*|[table.field1[as alias1],table.field1[as alias1],........]} -- 选择要提取哪些字段信息 as后面可取别名
FROM table_name[as table_alias]
[left|right|inner join table_name2 [as table_alias2]] -- 决定是否需要联表查询
[WHERE ....] -- 指定查询结果需要满足的条件
[GROUP BY....] -- 指定结果按照哪几个字段分组
[HAVING....] -- 过滤分组的记录必须满足的次要条件
[ORDER BY....] -- 指定查询出的记录按一个或多个条件排序
[LIMIT {[offset,]row_count|row_countOFFSET offset} ] -- 指定查询记录从第几条到第几条
- 首先必须强调，SELECT语句在使用时顺序很重要，关键词之间的位置不能搞错弄乱
- xxx join 连接的表 需要使用on等值判断
- where（具体的值，子查询语句）
- group by（通过哪个字段来分组）
- having（过滤分组后的信息，条件和where是一样的，只不过位置不同）
- order by （通过哪个字段排序）[升序ASC/降序DESC]
- limit stationdex,pagesize
- 实际上SELECT语句远比这个还复杂，因为这个结构还只是针对单库查询，没涉及跨库等更复杂内容。
06 事务（很重要，后期继续学习深化）
要么都成功，要么都失败
————————————
1、SQL执行
2、SQL执行
————————————
将一组SQL放在一个批次中执行
事务的原则：ACID（非常重要）：原子性，一致性，隔离性，持久性
参考博客：https://blog.csdn.net/dengjili/article/details/82468576
原子性（Atomicity）
事务是一个整体，包含在事务内的SQL语句要么都成功执行（提交），要么都失败
一致性（Consistency）
事务前后的数据完整性要保证一致
持久性（Durability）——事务提交
事务一旦提交则不可逆，被持久化到数据库中
隔离性（Isolation）
事务的隔离性事多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事物的操作数据所干扰，即事务之间要相互隔离。
隔离带来的一些问题
脏读：
指一个事务读取了另一个事务尚未提交的数据
不可重复读：
在一个事务内读取表中的某一行数据，多次读取结果不同。（不一定是错误，但在某些场合不对）
虚读（幻读）：
是指在一个事务内读取到了别的事务插入的数据，导致前后读取不一致
执行事务
-- ================================ 事务 ================================
-- mysql是默认开启事务自动提交的
SET autocommit = 0 /*关闭*/
SET autocommit = 1 /*开启*/
-- 手动处理事务
SET autocommit = 0 -- 关闭自动提交
-- 事务开启
START TRANSACTION -- 标记一个事务的开始，从这个之后的sql都在同一个事务内
-- 提交：持久化（成功！）
COMMIT
-- 回滚：回到事务开始前的初始设置（失败！）
ROLLBACK
-- 事务结束
SET autocommit = 1 -- 开启自动提交
-- 了解
SAVEPOINT 保存点名 --设置一个事务的保存点
ROLLBACK TO SAVEPOINT 保存点名 -- 回滚到保存点
RELEASE SAVEPOINT 保存点名 -- 删除保存点
案例：银行转账
CREATE DATABASE IF NOT EXISTS `banktest` CHARACTER SET utf8 COLLATE utf8_general_ci
USE `banktest`
CREATE TABLE `account`(
`id` int(4) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(10) NOT NULL,
`money` DECIMAL(9,2) NOT NULL,
PRIMARY KEY(`id`)
)ENGINE = INNODB DEFAULT CHARSET = utf8
INSERT INTO `account`(`name`,`money`) VALUES ('张三',2000.00),('李四',10000.00)
UPDATE `account` SET money = 2000 WHERE `name` = '张三';
UPDATE `account` SET money = 10000 WHERE `name` = '李四';
SET autocommit = 0
START TRANSACTION
UPDATE `account` SET money = money - 500 WHERE `name` = '张三';
UPDATE `account` SET money = money + 500 WHERE `name` = '李四'
COMMIT;
ROLLBACK;
SET autocommit = 1
-- 有趣的是，数据库中关于事务的提交和回滚类似于JAVA中的
/*
try{
}catch(){
}
*/
-- 即在try成功时正常提交运行，一旦try尝试失败即可进入catch捕获错误并进行其他操作，比如回滚到try之前的状态
07 索引
MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。提取句子主干，就可以得到索引的本质：索引是数据结构。
7.1 索引的分类
在一个表中，主键索引只能有一个，唯一索引可以有多个
- 主键索引（PRIMARY KEY）
- 唯一的标识，主键不可重复，只能有一个列为主键
- 唯一索引（UNIQUE KEY）
- 避免重复的列出现，唯一索引可以重复，多个列都可以标识为唯一索引
- 常规索引（KEY/INDEX）
- 默认的，index。KEY关键字来设置
- 全文索引（FULLTEXT）
- 在特定的数据库引擎下才有，MyISAM（不过貌似现在InnoDB也有）
-- 索引的创建方式
-- 方式一：在创建表的时候给字段增加索引
-- 方式二：创建完毕后，增加索引
USE `studentmanage`
-- 显示所有索引信息
SHOW INDEX FROM student
-- 增加一个全文索引（索引名） 列名（方式二）
ALTER TABLE studentmanage.student ADD FULLTEXT INDEX `studentName`(`studentname`)
-- EXPLAIN分析SQL执行情况
EXPLAIN SELECT * FROM student
-- 此处需要注意的是，由于数据库容量过小，因此无法查询到真实情况。具体情况看后面案例
EXPLAIN SELECT * FROM student WHERE MATCH(studentName) AGAINST('小');
7.2 测试索引
-- =========================================索引测试=========================================
-- 创建新表
CREATE TABLE IF NOT EXISTS `app_user`(
`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(50) DEFAULT'' COMMENT'用户昵称',
`email` VARCHAR(50) NOT NULL COMMENT'用户邮箱',
`phone` VARCHAR(20) DEFAULT'' COMMENT'手机号',
`gender` TINYINT(4) UNSIGNED DEFAULT '0'COMMENT '性别（0:男;1:女）',
`password` VARCHAR(100) NOT NULL COMMENT '密码',
`age` TINYINT(4) DEFAULT'0' COMMENT '年龄',
`create_time` DATETIME DEFAULT CURRENT_TIMESTAMP,
`update_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT = 'app用户表'
-- 函数开始的标志，必须要写
DELIMITER $$
-- 函数头，注意返回值
CREATE FUNCTION mock_data ()
RETURNS INT
-- 这一条选加，针对部分人可能出现的报错情况：this function has none of deterministic, no sql, or reads sql data in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable
READS SQL DATA
-- 函数体开始
BEGIN
DECLARE num INT DEFAULT 1000000;
DECLARE i INT DEFAULT 0;
WHILE i<num DO
INSERT INTO `app_user`(`name`,`email`,`phone`,`gender`,`password`,`age`)VALUES(CONCAT('用户',i),'19224305@qq.com',CONCAT('18',FLOOR(RAND()*999999999)),FLOOR(RAND()*2),UUID(),FLOOR(RAND()*100));
SET i=i+1;
END WHILE;
RETURN i;
END;
SELECT mock_data() -- 执行此函数 生成一百万条数据
-- 在编写循环体前可以现在外面编写SELECT语句，然后再copy回去
INSERT INTO `app_user`(`name`,`email`,`phone`,`gender`,`password`,`age`)VALUES(CONCAT('用户','1'),'19224305@qq.com',CONCAT('18',FLOOR(RAND()*999999999)),FLOOR(RAND()*2),UUID(),FLOOR(RAND()*100));
-- 测试数据生成完后，在无索引情况下进行信息查找，可以看到时间明显增多
SELECT * FROM `app_user` WHERE `name` = '用户9999' -- 用时0.566
SELECT * FROM `app_user` WHERE `name` = '用户9999' -- 用时0.610\
-- 检查此时代码运行情况，结果如下图所示
EXPLAIN SELECT * FROM `app_user` WHERE `name` = '用户9999'
-- 创建新的数据库索引
-- 方法三：采取CREATE方式创建数据库
CREATE INDEX id_app_user_name ON app_user(`name`)
-- 在有index情况下检索信息
SELECT * FROM `app_user` WHERE `name` = '用户9999' -- 用时0.024
-- 检查此时代码运行情况，结果如下图所示
EXPLAIN SELECT * FROM `app_user` WHERE `name` = '用户9999'
- 在没有index情况下的检索情况
- 在有index情况下的数据检索
- 索引在小数据情况下作用并不明显，用处不大；但是在大数据量时，非常有用
7.3 索引原则
- 1、索引并不是越多越好！
- 2、不应当对经常变化的数据添加索引！
- 3、小数据量的表没必要加索引！
- 4、索引一般加在常用来查询的字段上！
相关好文推荐：http://blog.codinglabs.org/articles/theory-of-mysql-index.html
08 权限管理和备份
8.1 用户管理
- 一般情况都是以root用户登陆sql
- 可以在mysql.user表中查看现在全部的用户信息，也或者可以在上面的用户中查看当前数据库的用户信息
-- ====================== 创建新用户以及进行更名改密码权限授予等操作 ======================
-- 创建新用户（注意，@后面是主机，即HOST里面的内容，建议不要不填）
CREATE USER WuTong2@localhost IDENTIFIED BY '1234567'
-- 修改密码（注意，在mysql8.0版本中，已经取消了原有的PASSWORD函数，加密方式不再使用mysql_native_password，换成了caching_sha2_password）
-- 原SQL语句为：SET PASSWORD FOR WuTong2@localhost = PASSWORD('12345')
SET PASSWORD FOR WuTong2@localhost = '12345'
-- 修改完新的密码后，必须使用flush privileges语句刷新权限表，新的密码之后才会生效
FLUSH PRIVILEGES
-- 修改用户名称（注意两点，修改的名字也需要带上主机地址，此外针对主机不是localhost的无法修改名称）
RENAME USER WuTong2@localhost TO WuTong222@localhost
-- 授予权限（主机不是localhost的无法授予权限）
-- 还有一点，授予全部权限并不是权限和root相同，root还有一个Grant_priv权限是ALL PRIVILEGES授予不了的
GRANT ALL PRIVILEGES ON *.* TO WuTong222@localhost
-- 展示当前用户权限
SHOW GRANTS FOR WuTong222@localhost
-- 权限列表
GRANT SELECT,
INSERT,
UPDATE,
DELETE,
CREATE,
DROP,
RELOAD,
SHUTDOWN,
PROCESS,
FILE,
REFERENCES,
INDEX,
ALTER,
SHOW DATABASES,
SUPER,
CREATE TEMPORARY TABLES,
LOCK TABLES,
EXECUTE,
REPLICATION SLAVE,
REPLICATION CLIENT,
CREATE VIEW,
SHOW VIEW,
CREATE ROUTINE,
ALTER ROUTINE,
CREATE USER,
EVENT,
TRIGGER,
CREATE TABLESPACE,
CREATE ROLE,
DROP ROLE
-- 移除该用户的全部权限
REVOKE ALL PRIVILEGES ON *.* FROM WuTong222@localhost
-- 删除该用户
DROP USER WuTong222@localhost
8.2 数据库备份
-
为什么要数据库备份
- 保护重要数据不丢失
- 数据转移
-
如何进行数据库备份
- 直接拷贝物理文件
- 在Navicat或Sqlyog这种可视化工具中手动导出
- 使用命令行导出mysqldump命令行使用
-
使用转储sql文件进行备份（针对数据库和数据表均可）
- 也可以使用命令行进行导出
/*
前提：必须在环境变量中正确配置mysql
格式：mysqldump -h(主机名) -u(用户名) -p(密码) (库名) (表名1) (表名2)······> (保存地址)(文件名)
*/
-- 第一种直接把密码放在里面，运行后mysql会提示这种不安全
mysqldump -hlocalhost -uroot -p12345 student grade1 > D:/·····/a.sql
-- 第二种运行后mysql会要求输入密码，成功后不会报上面的提示
mysqldump -hlocalhost -uroot -p12345 student grade1 > D:/·····/a.sql
-- 例子：
mysqldump -hlocalhost -uroot -p student grade1 > D:/DataBackup/grade1.sql
- 除了导出，也可以使用命令行导入表
-- 第一步 登陆进入mysql
mysql -uroot -p
-- 第二步 指定数据库
use student
-- 第三步 指定源文件
source D:/DataBackup/grade.sql
