MySQL基础

初识MySQL

什么是数据库

数据库(DB,DataBase)

概念：数据仓库，软件，安装在操作系统之上，SQL，可以存储大量的数据，500w！

作用：存储数据，管理数据

数据库分类

关系型数据库

• MySQL,Oracle,Sql Server,DB2,SQLlite
• 通过表与表之间，行列之间的关系进行数据的存储，学员信息表，考勤表…

非关系型数据库(NoSQL) not only

• Redis,MongDB
• 对象存储，通过对象的自身的属性来决定

连接数据库

mysql -u rppt -p -- 连接数据库

update mysql.user set authentication_string=password("******")where user = 'root' and Host = 'localhost'; -- 修改用户密码
flush privileges; -- 刷新权限
----------------------------
-- 所有语句都使用; 结尾
show databases; -- 查看所有的数据库

mysql > use school --切换数据据 use 数据库名
Database change;

show tables; -- 查看数据库中所有的表的信息
describe student ; -- 显示数据库中所有的表的信息

create database westos; -- 创建一个数据库

exit -- 退出连接

数据库xxx语言

DDL 定义

DML 操作

DQL 查询

DCL 控制

操作数据库

操作数据库

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

== mysql关键字不区分大小写 ==

1. 创建数据库

   CREATE DATABASE [IF NOT EXISTS] westos; -- 创建数据库westos if判断可用可不用create database shop character set utf8 collate utf8_general_ci -- 字符集，字符校对

2. 删除数据库

   DROP DATABASE IF EXISTS westos;

3. 使用数据库

   -- ``在tab键的上面,如果你的表名或者字段名是一个特殊字符，就需要带``USE `school`;-- 切换数据库-- user是个特殊的字段，在SQLyog中会标为关键字，而在student表中只是我设置的一个普通属性，不需要特殊标识，所以user用``标记SELECT `user` FROM student;

4. 查看数据库

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

学习思路：

• 对照SQLyog可视化的历史记录来查看SQL代码，多敲SQL代码
• 固定的语法或关键字必须要强行记住！多敲就能记住

数据库的属性类型

数值

• tinyint 十分小的数据 1个字节
• smallint 较小的数据 2个字节
• mediumint 中等大小的数据 3个字节
• int 标准的整数 4个字节 常用
• bigint 较大的数据 8个字节
• float 浮点数 4个字节
• double 浮点数 8个字节（这种类型的浮点数都会有精度问题）
• decimal 字符串形式的浮点数 金融计算的时候一般都是使用decimal，这不会丢失精度

int显示长度与零填充有关，不与值大小有关，而varchar的长度与字符串长度和值有关

字符串

• char 字符串固定大小的 0~255
• varchar 可变字符串 0~65535 常用的变量 String
• tinytext 微型文本 2^8 - 1
• text 文本串 2^16 - 1 一般可保存大文本

时间日期

java.util.Data

• data YYYY-MM-DD 日期格式
• time HH:mm:ss 时间格式
• datetime YYYY-MM-DD HH:mm:ss 最常用的时间格式
• timestamp 时间戳 1970.1.1到现在的毫秒数 较为常用
• year 年份表示

null

• 没有值 未知的意思
• ==注意，不要使用NULL进行运算，有NULL的话最后的结果也是NULL，没意义 ==

数据库的字段属性（重点）

Unsigned:

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

zerofill:

• 零填充的
• 不足的位数，使用零来填充，int(3)，5 … 005

自增:（auto_increment）

+ 通常理解为自增,一般使用在主键中
+ 通常用来设计唯一的主键 index，必须是整数类型
+ 可以自定义设计主键自增的起始值和步长

非空：NULL 与 NOT NULL

+ 假设设置为not null：如果不给它赋值，就会报错！
+ 不设置not null：如果不填写值，默认就是NULL

默认：

• 设置默认的值

扩展：

/* 每一个表，都必须存在以下五个字段！未来做项目用的，表示一个记录存在意义id   
主键`version`    
乐观锁is_delete    
伪删除gmt_create   
创建时间gmt_update   
修改时间*/

创建数据库表（重点）

-- 目标：创建一个school数据库-- 创建学生表（列，字段） 使用SQL创建-- 学号int 登录密码varchar(20) 姓名，性别varchar(2)，出生日期(datetime)，家庭住址。email
-- 注意点，使用英文（），表的名称和字段  尽量使用``括起来
-- AUTO_INCREMENT 自增-- 字符串使用单引号''括起来！
-- 所有的语句后面加英文逗号 , (英文)，最后一个不用加
-- primary key 主键，一般一个表只有一个唯一的主键
-- ENGINE=INNODB 设置存储引擎， DEFAULT CHARSET=utf8 设置编码格式
CREATE TABLE IF NOT EXISTS `student`(
    `id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',     
    `password` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',     
    `name` VARCHAR(10) NOT NULL DEFAULT '匿名' COMMENT '名字',     
    `sex` VARCHAR(2) NOT NULL DEFAULT '男' COMMENT '性别',     
    `birthday` DATETIME DEFAULT NULL COMMENT '出生日期',     
    `address` VARCHAR(50) DEFAULT NULL COMMENT '家庭住址',     
    `email` VARCHAR(30) DEFAULT NULL COMMENT '邮件',     
    PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

格式：

CREATE TABLE [IF NOT EXISTS] `表名`(     
    `字段名` 属性类型 [属性] [索引] [注释],     
    `字段名` 属性类型 [属性] [索引] [注释],   
    ......    
    `字段名` 属性类型 [属性] [索引] [注释],    
    primary key(``)-- 设置主键
)[表类型][字符集设置][注释]

常用命令：

SHOW CREATE DATABASE school  -- 查看创建school数据库的语句
-- CREATE DATABASE `school` /*!40100 DEFAULT CHARACTER SET utf8 */
SHOW CREATE TABLE student -- 查看student数据表的定义语句
DESC student -- 显示表的结构，属性

2.5 数据表的类型

-- 关于数据库的引擎
/*
INNODB 默认使用~
MYISAM 早些年使用的
*/

	MYISAM	INNODB
事务支持	不支持	支持
数据行锁定	不支持	支持
外键约束	不支持	支持
全文索引	支持	不支持
表空间大小	较小	较大，约为2倍的MYISAM

常规使用操作：

• MYISAM 节约空间，速度较快
• INNODB 安全性高，事务的处理，多表多用户操作

在物理空间存在的位置:

所有的数据库文件都存在data目录下，一个文件夹就对应一个数据库，本质还是文件的存储！

MYSQL（5版本）引擎在物理文件上的区别：

• INNODB 在数据库中有一个*.frm文件和* .ibd文件，以及上级目录下的ibdata1文件
• MYISAM对应文件：
  • *.frm 表结构的定义文件
  • *.MYD 数据文件（data）
  • *.MYI 索引文件（index）

设置数据库表的字符集编码

MySQL的默认编码是Latin1 不支持中文

charset =utf8

不设置的话，会是mysql默认的字符集编码（不支持中文）

1. 在建表的时候设置字符编码

   ENGINE=InnoDB DEFAULT CHARSET=utf8

2. 在my.ini主配置中设置默认的编码（不推荐）

   character-set-server=utf8

修改删除表

修改：

对表进行修改，不是修改里面的数据（行）

-- 创建表
CREATE TABLE IF NOT EXISTS teacher (
    `tid` INT(5) NOT NULL AUTO_INCREMENT COMMENT 'id', 
    PRIMARY KEY(tid)
)ENGINE=INNODB DEFAULT CHARSET=utf8
-- 修改表名  ALTER TABLE 旧表名 RENAME AS 新表名
ALTER TABLE teacher RENAME AS teacher1
-- 增加表的字段(属性) ALTER TABLE 表名 ADD 字段名（属性名） 数据类型
ALTER TABLE teacher1 ADD `age` INT(3)
-- 修改表的字段（重命名，修改约束！）
ALTER TABLE teacher1 MODIFY age VARCHAR(11) -- 修改约束（数据类型）
ALTER TABLE teacher1 CHANGE age age1 INT(3) -- 字段重命名
-- 删除表的字段(属性):ALTER TABLE 表名 DROP 属性名（字段名）
ALTER TABLE teacher1 DROP age1

删除：

-- 删除表（如果表存在则删除）
DROP TABLE IF EXISTS teacher1

注意点：

• 字段名要用 ``包裹
• 注释： — 和 /**/
• sql关键字大小写不敏感，建议都小写（SQLyog会自动转为大写）
• 所有的符号全部用英文

MySQL数据管理

外键

方式一：创建表的时候就添加外键

CREATE TABLE `grade`(
    `gid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年级id',
    PRIMARY KEY(`gid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

-- 学生表的gradeid字段，要去引用年级表的gradeid
-- 定义外键key
-- 给这个外键添加约束（执行引用） constraint约束 references引用
-- 注意添加表的时候确保这个表之前不在，不要直接在添加表的代码里临时添加数据，添加表属性用alter...add...
CREATE TABLE IF NOT EXISTS `student`(
     `id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
     `gradeid` INT(10) NOT NULL COMMENT '学生的年级',
     PRIMARY KEY(`id`),
     KEY `FK_gradeid`(`gradeid`),
     CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade`(`gid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

方式二：在创建表时没有添加外键，手动临时添加外键约束如下：

-- 创建表的时候没有外键关系,手动修改表，创建外键
ALTER TABLE `student` 
ADD 
CONSTRAINT `FK_gradeid` FOREIGN KEY(`gradeid`) REFERENCES `grade`(`gid`);
-- ALTER TABLE 表名 ADD CONSTRAINT 约束名 FOREIGN KEY(作为外键的属性) REFERENCES 哪个表(哪个字段（属性）);

以上操作都是物理外键，数据库级别的外键，不建议使用，（避免数据库过多造成困扰）

最佳实践

• 数据库就是单纯的表，只用来存数据，只有行（数据）和列（字段，属性）
• 我们想使用多张表的数据，想实现关联（外键）（用程序去实现）

为什么不用外键： (19条消息) 为什么实际开发中不使用外键_王富贵儿。的博客-CSDN博客_实际开发中会用外键吗

DML语言

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

DML语言：数据操作语言**（对数据（记录，行）进行操作）**

• insert
• update
• delete

添加

-- 插入语句（添加）
-- insert into 表名（[字段名1,字段名2,...]）values (值1,值2,值3,...),(值1,值2,值3,...),(值1,值2,值3,...)...
-- 一个括号values()代表一个数据(记录)
INSERT INTO `grade`(gname) VALUES ('大四')
-- 由于主键自增我们可以省略（如果不写表的字段，他就会一一匹配）
INSERT INTO `grade` VALUES('大三') -- 这里会报错，大三对应的是gid（int类型）
-- 插入多个字段
INSERT INTO grade(gname) 
VALUES ('大三'),('大二');
INSERT INTO `student`(`name`) VALUES ('张三')
INSERT INTO `student`(`name`,`password`,`sex`)
VALUES('张三','aaaaaaa','男'),('李四','bbbbbb','男');
-- 没有字段名（属性名）则需要一一对应表中的属性顺序
-- 要全部属性对应的数据都写出来，主键不能一样，外键编号对应在引用表里也必须存在
INSERT INTO `student` VALUES(10,'000000','Tang','男','2021-01-01','湖南','email',2)

语法：insert into 表名（[字段名1,字段名2,...]）values (值1,值2,值3,...),(值1,值2,值3,...),(值1,值2,值3,...)...

一 一对应

注意事项：

1. 字段和字段之间使用英文逗号隔开
2. 字段是可以省略的，但是后面的值必须要一一对应，不能少
3. 可以同时插入多条数据，values后面的值需要使用，隔开即可values(),(),…
4. 没有字段名（属性名）则需要一 一对应表中的属性顺序
   要全部属性对应的数据都写出来，主键不能一样，外键编号对应在引用表里也必须存在

修改

update 修改谁 [条件] set 属性名=新值

-- 修改学生名字
UPDATE `student` SET `name`='狂神' WHERE id=1;
-- 不指定条件的情况下会修改表中所有记录的数据
UPDATE student SET `name`='1号'
-- 修改多个属性，逗号隔开
UPDATE student SET `name`='狂神',email=NULL WHERE id =6;
-- 语法：
-- UPDATE 表名 SET column_name=value,[column_name=value,...] WHERE [条件];

条件：where子句 运算符 id等于某个值，大于某个值，在某个区间修改 ……

UPDATE student SET name=’狂神’,email=’123’ WHERE id BETWEEN 2 AND 5;

操作符会返回 布尔值

操作符	含义	范围	结果
=	等于	5=6	false
<>或!=	不等于	5<>6或5!=6	true
>	大于	5>6	false
<	小于	5<6	true
>=	大于等于	5>=6	false
<=	小于等于	5<=6	true
between…and…	在某个范围内，双闭合区间	[2,5]
and	&& 和	5>1 and 1>2	false
or	|| 或	5>1 or 1>2	true

-- 通过多个条件定位数据
UPDATE student SET `name`='长江7号' WHERE `name`='狂神' AND sex='女'

注意:

• column_name 是数据库的列，尽量带上``
• 筛选的条件，如果没有指定，则会修改所有的列
• value是一个具体的值，也可以是一个变量
• 多个变量的属性之间使用英文逗号隔开

-- CURRENT_TIME 当前时间 （变量）
UPDATE student SET birthday=CURRENT_TIME WHERE id=1
-- or
UPDATE student SET birthday='xxxx-xx-xx' WHERE id=1

删除

delete 命令

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

-- 删除指定数据D
ELETE FROM student WHERE id =1;
-- 删除数据没有条件（避免这样写，会全部清空表）
DELETE FROM student;

truncate命令

作用：完全清空一个数据库表，表的结构和索引约束不会变！

-- 清空student表(会将自增量也清零)
TRUNCATE student

delete与truncate的区别

-- 测试delete 和 truncate区别
CREATE TABLE `test`(
	`id` INT(4) NOT NULL AUTO_INCREMENT,
    `coll` VARCHAR(20) NOT NULL,   
     PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
INSERT INTO test(coll) VALUES ('1'),('2'),('3')DELETE FROM test 
-- delete 不会影响自增
TRUNCATE test  -- 清零自增量

了解即可：delete删除的问题，重启数据库现象:

• INNODB 自增列会重1开始 (存在内存当中，断电即失)
• MYISAM 继续从上一个自增量开始（存在文件中，不会丢失）

DQL查询数据（最重点）

DQL

DQL: Data Query Language 数据查询语言

• 所有的查询操作都用它 Select
• 简单的查询，复杂的查询它都能做
• 数据库中最核心的语言，最重要的语句
• 使用频率最高的语句

以下图片内的语法顺序必须遵守

where的条件不能使用聚集函数，要再having中使用

-- 一般sql语句
==顺序很重要：==

select 去重(distinct) 要查询的字段from表（注意：表和字段可以区别名）==

xxx join 要连接的表 on 判断的连接点

where（具体的值，子查询语句）==

Group By（通过哪个字段来分组）==

Having（过滤分组后的信息，条件语法和where是一样的，位置不同，用在分组中）

order by（通过哪个字段排序）【升序/降序】

limit startindex， pageSize（分页）

业务层面：查询：跨表，跨数据库…

查询指定字段

select 字段,… from 表名

-- 查询全部学生   SELECT * FROM 表名
SELECT * FROM student
-- 查询指定字段
SELECT `studentno`,`studentname` FROM student
-- 别名，给结果起一个别名  用AS（as可以省略）
SELECT `studentno` AS `学号`,`studentname` AS 名字 FROM student
-- 在姓名前加上“姓名:XXX” 函数concat(a,b)
SELECT `studentno`  学号,CONCAT('姓名：',`studentname`)AS 新姓名 FROM student

有的时候列名字不是那么的见名知意，所以有了别名 AS（可以省略） 字段名 as 别名 表名 as 别名

去重 distinct

作用：去除重复的结果数据，重复的数据只显示一条

-- 查询一下有哪些同学参加了考试，成绩
SELECT * FROM result  -- 查询全部的考试成绩
SELECT `studentno` FROM result -- 查询哪些同学参加了考试，只显示学号
-- 发现有重复数据，distinct去重
SELECT DISTINCT `studentno` FROM result

数据库的列（表达式）

SELECT VERSION()  -- 查询系统的版本（函数）
SELECT 100*3-1 AS 计算结果  -- 用来计算（表达式）
SELECT @@auto_increment_increment -- 查询自增量的步长（变量）
-- 学生的考试成绩+1分查看
SELECT `studentno`, `studentresult`+1 AS '提分后' FROM result

数据库中的表达式有：文本值，列，null，函数，计算表达式，系统变量….

select 表达式 from 表

where条件子句

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

逻辑运算符：尽量使用英文字母

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

-- ========where========
SELECT studentNo,StudentResult FROM result; -- 不区分大小写
-- AND 查询考试成绩在90~100分之间
SELECT `studentno`,`studentresult` FROM result
WHERE `studentresult`>=90 AND `studentresult`<=100;
-- &&
SELECT `studentno`,`studentresult` FROM result
WHERE `studentresult`>=90 && `studentresult`<=100;
-- 闭合区间：between...and...
SELECT `studentno`,`studentresult` FROM result
WHERE `studentresult` BETWEEN 90 AND 100;
-- != 除了1001号学生之外的同学的成绩
SELECT `studentno`,`studentresult` FROM result
WHERE studentno!=1001;
-- not 除了1001号学生之外的同学的成绩
SELECT `studentno`,`studentresult` FROM result
WHERE NOT studentno=1001;

模糊查询：比较运算符

运算符	语法	描述
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,a3,…)中某个值，则结果为真

-- =======模糊查询========
-- 查询性唐的同学
-- like结合 %(代表0到任意个字符)  _(一个字符)
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（具体的一个或者多个值(全称)）
-- 查询1001,1002,1003号学生
SELECT `studentno`,`studentname` FROM `student`
WHERE `studentno` IN(1001,1002,1003);

-- 查询在北京的学生
SELECT `studentno`,`studentname` FROM `student`
WHERE `address` IN('湖南','河南洛阳');-- 必须是全称，一一匹配

-- null     not null
-- 查询地址为空的学生null ''
SELECT `studentno`,`studentname` FROM `student`
WHERE `address`=''

-- 查询出生日期的同学 不为空
SELECT `studentno`,`studentname` FROM `student`
WHERE `borndate` IS NOT NULL;

-- 查询没有出生日期的同学  为空
SELECT `studentno`,`studentname` FROM `student`
WHERE `borndate` IS NULL;

联表查询

-- =========联表查询 join==========
SELECT * FROM student
SELECT * FROM result
-- 查询参加了考试的同学（学号，姓名，科目编号，分数）
/* 思路
1.分析需求，分析查询的字段来自哪些表（连接查询）
2.确定使用哪种连接查询？7种
3.确定交叉点（这两个表哪个数据是相同的）
4.判断的条件：学生表中studentno = 成绩表中studentno（这里用别名会方便很多）
*/
-- join on   连接查询
-- where     等值查询
-- inner join
SELECT s.studentno,studentname,subjectno,studentresult
FROM student AS s
INNER JOIN result AS r
WHERE s.studentno = r.studentn
-- right join
SELECT s.`studentno`,`studentname`,`studentname`,`studentresult`
FROM `student` s
RIGHT JOIN `result` r
ON r.`studentno` = s.`studentno`
-- left join
SELECT s.`studentno`,`studentname`,`studentname`,`studentresult`
FROM `student` s
LEFT JOIN `result` r
ON r.`studentno` = s.`studentno`

注意：right join和left join只能用on确定交叉点

• join on 连接查询

  on是在临时表的生成时，不管on后面的条件为不为真，都会返回左表的记录，然后再通过条件进行覆盖条件为真的记录，而不为真的用null来代替

• where 等值查询

  where条件是在临时表生成好后再对临时表进行过滤的条件，这时已经没有left join的含义了，条件不为真的就全部过滤掉

操作	描述
inner join	两个表都要存在的数据，只取交集
left join	左表都有，右表可以没有。在右表中没有的数据，展示的属性数据用null替代或者过滤掉
right join	右表都有，左表可以没有。在左表中没有的数据，展示的属性数据用null替代或者过滤掉

-- 查询缺考的同学 先查出所有同学再通过条件筛选出缺考的学生
SELECT s.studentno,studentname,subjectno,studentresult
FROM student s LEFT JOIN result r
ON s.studentno=r.studentno
WHERE `studentresult` IS NULL;
-- 思考题（查询参加考试的同学的信息：学号，学生姓名，科目名，分数）(三表查询)
/*思路
1.分析需求，分析查询的字段来自哪些表，student，result，subject（连接查询）
2.确定使用左连接找出参加考试同学的信息，然后再通过交集连接把课程名打印出来
3.确定交叉点 studentno，subjectno
*/
SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM `student` s
RIGHT JOIN `result` r
ON r.`studentno` = s.`studentno`
INNER JOIN `subject` sub
ON sub.`subjectno` = r.`subjectno`

SELECT s.`studentno`,`studentname`,`gradename`,`studentresult`
FROM `student` s
LEFT JOIN `result` r
ON r.`studentno` = s.`studentno`
INNER JOIN `grade` sub
ON sub.`gradeid` = s.`gradeid`

-- 查询数据 select ...
-- 从哪几个表中查 from 表名XXX join 连接的表XXX on 交叉的条件
-- 假设存在一种多张表查询，一步一步来，先查询两张表然后再慢慢增加查询
-- from a inner join b
-- from a left join b
-- from a right join b

三表查询的注意事项，关于谁作为左右表的:

左右表就是查询比对的底层表

当第一个表作为左表时，第三个表要有作为左表的相同字段，在第一个表和第二个表查询后，再使用inner join交叉查询

当第一个表作为右表时，第三个表要有作为右表的相同字段，在第一个表和第二个表查询后，再使用inner join交叉查询

自连接

自己的表和自己的表连接，核心：一张表拆为两种一样的表即可

-- ==========自连接============
CREATE TABLE `category`(
   `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '唯一id',
   `pid` INT(10) NOT NULL COMMENT '父级id',
   `name` VARCHAR(50) NOT NULL COMMENT '名称',
   PRIMARY KEY(`id`)
)ENGINE =INNODB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8
INSERT INTO category (id,pid,`name`)
VALUES (2,1,'信息技术'),(3,1,'软件开发'),(4,3,'数据库'),
(5,1,'美术设计'),(6,3,'web开发'),(7,5,'ps技术'),(8,2,'办公信息');

-- 查询父子信息：把一张表看成两个一模一样的表
SELECT a.`categoryName` 父栏目,b.`categoryName` 子栏目
FROM `category` a,
`category` b
WHERE a.`categoryid` = b.`pid`

查询练习：

-- 查询学员所属的年级（学员，学生的姓名，年级名称）
SELECT `studentno`,`studentname`,`gradename`
FROM student s
INNER JOIN grade g
ON s.gradeid=g.gradeid
-- 查询科目所属的年级（科目名称，年级名称）
SELECT `subjectname`,`gradename`
FROM `subject` sub
INNER JOIN grade g
ON sub.gradeid=g.gradeid
-- 查询了参加高等数学-1 考试的同学信息（学号，学生姓名，科目名，分数）
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 `subjectname`='高等数学-1'

分页和排序

排序

-- =======分页 limit和排序 order by=========
-- 排序：升序ASC，降序DESC
-- 语法：order by 通过哪个字段进行排序，怎么排（asc，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 `subjectname`='高等数学-1'
ORDER BY `studentresult` ASC

分页

• 为什么要分页
  • 缓解数据库压力，给人的体验更好，瀑布流（例如抖音，一直往下刷）
  • 分页，每页只显示五条数据
  • 语法：limit 记录下标(数据库第一个数据的下标是0),页面大小
  • limit 0,5 从1开始，展示5个记录
  • limit 0,5 1~5
  • limit 5,5 6~10

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`
ORDER BY `studentresult` ASC
LIMIT 5,5
-- 第一页 limit 0,5   (1-1)*5
-- 第二页 limit 5,5   (2-1)*5
-- 第三页 limit 10,5  (3-1)*5
-- 第n页  limit ~,5   (n-1)*pageSize,pageSize
-- 【pageSize：页面大小】
-- 【(n-1)*pageSize:起始值】
-- 【n: 当前页】
-- 【总页数= 数据总数/页面大小】（向上取整）
-- 思考
-- 查询java-1 课程成绩排名第一的学生，并且分数要大于80的学生信息(学号，姓名，课程名称，分数)
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 `subjectname`='java-1' AND `studentresult`>=60
ORDER BY `studentresult` DESC
LIMIT 0,1

语法：limit（起始下标，pageSize）

子查询

-- ===========where=========
-- 1.查询 高等数学-1 的所有的考试结果（学号，科目编号，成绩）按成绩降序排列
-- 方式一：使用联表查询
SELECT `studentno`,r.`subjectno`,`studentresult`
FROM `result` r
INNER JOIN `subject` sub
ON r.subjectno=sub.subjectno
WHERE `subjectname`='高等数学-1'
ORDER BY `studentresult` DESC

-- 方式二：使用子查询（由里及外）
-- 查询所有学生的学号等信息，然后通过子查询再筛选出高等数学-1的学生信息
SELECT studentno,`subjectno`,`studentresult`
FROM result
WHERE `subjectno`=(
-- 查询高等数学-1的科目编号
SELECT `subjectno` FROM `subject` WHERE `subjectname`='高等数学-1'
)
ORDER BY `studentresult` DESC

-- 练习：
-- 1.分数不小于60分的学生的学号和姓名(使用联表方式)
SELECT DISTINCT s.studentno,studentname
FROM student s
INNER JOIN result r
ON s.studentno=r.studentno
WHERE studentresult >= 60
/*
-- 2.在这个基础上增加一个科目，高等数学-2
SELECT DISTINCT s.studentno,studentname
FROM student s
INNER JOIN result r
ON s.studentno=r.studentno
WHERE studentresult >= 60 AND studentname='高等数学-2'
-- 错误，因为还要再联一个subject表才行，所以用子查询会方便很多
*/

-- 3.正确方式，加一个联表(联了三个表)效率较子查询来说要低一些
SELECT DISTINCT s.studentno,studentname
FROM student s
INNER JOIN result r
ON s.studentno=r.studentno
INNER JOIN `subject` sub
ON r.subjectno=sub.subjectno
WHERE studentresult >= 60 AND subjectname='高等数学-2'

-- 4.子查询方式。在这个基础上增加一个科目，高等数学-2
-- 子查询 查询高等数学-2的编号
SELECT DISTINCT s.`studentno`,`studentname`
FROM `student` s
INNER JOIN `result` r
ON s.`studentno` = r.`studentno`
WHERE `studentresult` > 60 AND subjectno = (
  SELECT subjectno FROM `subject`
  WHERE `subjectname` = '高等数学-1'
)
-- 5.通过子查询再简化，把联表操作去掉
-- 再次改造（由里及外）
SELECT studentno , studentname FROM student WHERE studentno IN(
  SELECT studentno FROM result WHERE studentresult > 60 AND subjectno = (
      SELECT subjectno FROM `subject` WHERE `subjectname` = '高等数学-1'
  )
)
-- 练习：查询java-2 前5名同学的成绩的信息（学号，姓名，分数） 使用子查询
SELECT s.`studentno`,`studentname`,`studentresult`
FROM student s
INNER JOIN result r
ON s.studentno=r.studentno
WHERE r.subjectno = (
SELECT subjectno FROM `subject` WHERE subjectname= 'java-2'
)
ORDER BY `studentresult` DESC
LIMIT 0,5
/*
SELECT studentno ,studentname, FROM student WHERE studentno IN(
  SELECT studentno,studentresult FROM result WHERE subjectno = (
     SELECT subjectno FROM `subject` WHERE subjectname = '高等数学-1'
  )
)*/

分组和过滤

-- 查询不同课程的平均分，最高分，最低分，并且平均分大于80
-- 核心（根据不同的课程分组）
SELECT `subjectname` ,AVG(`studentresult`) 平均分,MAX(`studentresult`) 最高分,MIN(`studentresult`) 最低分
FROM result r
INNER JOIN `subject` sub
ON r.`subjectno` = sub.`subjectno`
GROUP BY `subjectname`
HAVING 平均分 > 80
-- having 用于分组后过滤

MySQL函数

官网：https://dev.mysql.com/doc/refman/5.7/en/functions.html

通用函数（不常用）

-- ===========常用函数==========
-- 数学运算
SELECT ABS(-8)    -- 绝对值
SELECT CEILING(9.4) -- 向上取整
SELECT FLOOR(9.4) -- 向下取整
SELECT RAND() -- 返回一个0~1之间的随机数
SELECT SIGN(0) -- 判断一个数的符号 0返回0 负数返回-1，正数返回 1 
-- 字符串函数
SELECT CHAR_LENGTH('即使再小的帆也能远航') -- 字符串长度
SELECT CONCAT('我','爱','wlh') -- 拼接字符串
SELECT INSERT('我爱编程HelloWord',1,2,'超级热爱') -- 插入，从某个位置开始替换某个长度
SELECT LOWER('KuangShen') -- 全小写
SELECT UPPER('KuangShen') -- 全大写
SELECT INSTR('kuangShen','h')-- 返回第一次出现的字串的索引
SELECT REPLACE('每天都是坚持就能成功','坚持','努力')-- 替换出现的指定字符串
SELECT SUBSTR('每天都是坚持就能成功',5,2) -- 返回指定的字符串（源字符串，截取位置，截取的长度）
SELECT REVERSE('每天都是坚持就能成功') -- 反转
-- 查询姓周的同学，替换为邹
-- 当有名字里有多个周的时候用insert方法
SELECT REPLACE(studentname,'周','邹')FROM student
WHERE studentname LIKE '周%'
SELECT INSERT(studentname,1,1,'邹')FROM student
WHERE studentname LIKE '周%'

-- 时间和日期函数（记住！）
SELECT CURRENT_DATE() -- 获取当前日期
SELECT CURDATE() -- 获取当前日期
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() -- root@localhost
SELECT USER()-- root@localhost
SELECT VERSION() -- 查了MySQL版本号

聚合函数(常用)

函数名称	描述
count()****	计数
sum()	求和
avg()	平均值
max()	最大值
min()	最小值
……	……

-- ==========聚合函数==========
-- 统计表中的数据（想查询一个表中有多少个记录，就使用这个count()）
SELECT COUNT(`address`) FROM student; -- count(字段)，会忽略所有的null值
SELECT COUNT(*) FROM student; -- count(*) 不会忽略null值，本质  计算行数
SELECT COUNT(1) FROM student;-- count(1)  不会忽略null值，本质  计算行数
SELECT SUM(`studentresult`) AS 总分 FROM result
SELECT AVG(studentresult) AS 平均分 FROM result
SELECT MAX(studentresult) AS 最高分 FROM result
SELECT MIN(studentresult) AS 最低分 FROM result

数据库级别的MD5加密(扩展)

什么是MD5？

主要是增强算法复杂度和不可逆性

MD5不可逆，具体的值的MD5是一样的

MD5破解网站的原理，背后有一个字典，MD5加密后的值，加密的前值

password()函数也是一个加密不可逆的加密方式

-- ==========测试加密MD5========
-- 加密后不可逆！！
CREATE TABLE`testmd5`(
    `id` INT(4) NOT NULL,
    `name` VARCHAR(20) NOT NULL,
    `pwd` VARCHAR(50) NOT NULL,
    PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
-- 明文密码（能看见的密码）
INSERT INTO `testmd5` VALUES(1,'Tang','123456'),(2,'Wen','123456'),(3,'xiao','123456')
-- 加密（对id为1的pwd记录加密一次）
UPDATE testmd5 SET pwd=MD5(pwd) WHERE id=1
-- 加密全部的密码
UPDATE testmd5 SET pwd=MD5(pwd)
-- 插入的时候加密！
INSERT INTO testmd5 VALUES(4,'zu',MD5('123456'))
-- 如何校验：将用户传递进来的密码进行md5加密，然后比对加密后的值
-- 当用户要登录查询用户信息时，如何进行验证：用户登录输入的密码加密后， 与注册表里加密后的密码进行比对就ok了
-- 注意加密的次数，对加密后再加密就是加密了两次，与加密一次的值不一样的
SELECT * FROM testmd5 WHERE `name`='Wen' AND pwd=MD5('123456')
SELECT * FROM testmd5 WHERE `name`='Tang' AND pwd=MD5(MD5('123456'))

事务

什么是事务

事务：就是一些增删改查等等操作

目的：设置事务是为了保证一组事务要么都成功，要么都失败

---

1. SQL执行 A给B转账 A 1000 —>200 B 200
2. SQL执行 B收到A的钱 A 800 B 400

---

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

事务原则: ACID原则： 原子性，一致性，隔离性，持久性

可供参考博客链接： (20条消息) 事务ACID理解_dengjili的博客-CSDN博客_事务acid

原子性（Atomicity）

要么都成功，要么都失败

一致性（Consistency）

事务前后的数据完整性要保持一致

持久性（Durability）——事务提交

事务一旦提交则不可逆，被持久化到数据库中

隔离性（Isolation）

事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。

隔离所导致的一些问题

脏读：

指一个事务读取了另一个事务未提交的数据

不可重复读：

在一个事务内读取表中的某一行数据，多次读取结果不同。（这个不一定是错误，只是某些场合不对）

虚读(幻读)

是指在一个事务内读取到了别的事务插入的数据，导致前后读取不一致

操作事务方式

执行事务

-- MySQL是默认开启事务自动提交的
SET autocommit=0 -- 关闭
SET autocommit=1 -- 开启（默认的）
-- 手动处理事务（事务：对数据库进行增删改查等操作）
-- 事务开启
START TRANSACTION -- 标记一个事务的开始，从这个之后的sql都在同一个事务内
-- 用start直接开启手动模式，写完事务就直接commit提交事务，不需要语法设置
INSERT xx
INSERT xx
-- 提交：提交后就是持久化到数据库中(成功)
COMMIT
-- 回滚：回到原来的样子（失败）
ROLLBACK
-- 事务结束`    
SET autocommit=1 -- 开启自动提交


/* 了解
SAVEPOINT 保存点 名 -- 设置一个事务的保存点
ROLLBACK TO SAVEPOINT 保存点 名 -- 回滚到保存点
RELEASE SAVEPOINT 保存点 名 -- 撤销保存点
*/

MySQL 默认开启事务自动提交模式，即除非显式的开启事务（BEGIN 或 START TRANSACTION），否则每条 SOL 语句都会被当做一个单独的事务自动执行。但有些情况下，我们需要关闭事务自动提交来保证数据的一致性。

模拟场景

-- 转账  1.创建银行数据库
CREATE DATABASE shop CHARACTER SET utf8 COLLATE utf8_general_ci
USE shop
-- 创建表
-- DECIMAL(9,2)代表：9位数，其中小数位是两位
CREATE TABLE `account`(
    `id` INT(5) NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(30) NOT NULL,
    `money` DECIMAL(9,2) NOT NULL,
    PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
-- 添加数据
INSERT INTO account(`name`,`money`)
VALUES('A',2000),('B',10000)
-- 模拟转账事务
SET autocommit=0; -- 关闭自动提交
START TRANSACTION -- 开启一个事务（一组事务）
UPDATE account SET money=money-500 WHERE `name`='A' -- A减500
UPDATE account SET money=money+500 WHERE `name`='B' -- B加500
COMMIT;-- 提交事务，就被持久化了
ROLLBACK;-- 回滚，回到事务前的数据
SET autocommit = 1;-- 恢复自动提交

索引

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

提取句子主干，就可以得到索引的本质，索引是数据结构

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

索引的分类

在一个表中，不能设置多个主键索引，可以设置多个唯一键索引

• 主键索引（primary key）
  • 唯一的标识，主键不可重复，只能有一个列作为主键
• 唯一索引（unique key）
  • 避免重复的列数据出现，值唯一，可以重复—>也就是说可以设置多个唯一索引
• 常规索引（key/index）
  • 默认的，index，key关键字来设置
• 全文索引（fulltext）
  • 在特定的数据库引擎下才有，MYISAM
  • 快速定位数据

-- 索引的使用
-- 1.在创建表的时候给字段增加索引（第一种添加索引方式）
-- 2.创建完毕后，增加索引
-- 显示所有的索引信息
SHOW INDEX FROM student
-- 第二种添加索引方式：增加一个全文索引(索引名) 列名
ALTER TABLE school.`student`ADD FULLTEXT INDEX studentname(studentname);
-- explain 分析sql执行的状况
EXPLAIN SELECT * FROM student -- 非全文索引(rows返回你查了多少行的数据，相当于检查了这条SQL的效率)
-- 全文索引，一般用于很多很多数据的时候
EXPLAIN SELECT * FROM student WHERE MATCH(studentname)AGAINST('刘')

添加索引的方式：

1. 在创建表的时候给字段增加索引（第一种添加索引方式）
2. 第二种添加索引方式：增加一个全文索引(索引名) 列名
   ALTER TABLE school.studentADD FULLTEXT INDEX studentname(studentname);
3. 添加索引 命名格式：id 表名 字段名
   CREATE INDEX 索引名 ON 表(字段)

测试索引

插入100 0000条数据

CREATE TABLE `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(3)DEFAULT'0' COMMENT'年龄',
    `create_time`DATETIME DEFAULT CURRENT_TIMESTAMP,
    `update_time`TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY(`id`) 
)ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COMMENT='app用户表'
-- 插入1万条数据
-- 创建函数，还不是执行函数
DELIMITER $$ -- 写函数之前必须要写，标志（类似于c语言的头文件）
CREATE FUNCTION mock_data()
RETURNS INT -- 放回值
BEGIN
   -- 声明变量
   DECLARE num INT DEFAULT 500000;
   DECLARE i INT DEFAULT 0;
   WHILE i<num DO
        -- 插入数据
        INSERT INTO app_user(`name`,`email`,`phone`,`gender`,`password`,`age`)
        VALUES(CONCAT('用户',i),'2640420434@qq.com',
        CONCAT('18',FLOOR(RAND()*899999999+100000000)),
        FLOOR(RAND()*2),UUID(),FLOOR(RAND()*100));
        SET i=i+1;
   END WHILE; 
   RETURN i;-- 注意这里是return 前面是returns    
END;

TRUNCATE app_user;-- 清空表
SELECT mock_data();-- 执行函数
DROP FUNCTION mock_data; -- 删除函数

索引查询比较

-- 查询用户9999数据,当没有索引的时候(总耗时:0.573 sec)
SELECT * FROM app_user WHERE `name`='用户9999';
EXPLAIN SELECT * FROM app_user WHERE `name`='用户9999';
-- 添加索引 命名格式：id_表名_字段名
-- CREATE INDEX 索引名 ON 表(字段)
CREATE INDEX id_app_user_name ON app_user(`name`);
-- 查询用户9999数据,当有索引的时候(总耗时:0.001 sec)
SELECT * FROM app_user WHERE `name`='用户9999';
EXPLAIN SELECT * FROM app_user WHERE `name`='用户9999';

索引原则

• 索引不是越多越好
• 不要对进程变动数据加索引
• 小数据量的表不需要加索引
• 索引一般加在常用来查询的字段上

索引的数据结构

Hash类型的索引

Btree:innodb默认的数据结构

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

用户管理和备份

用户管理

root是最高权限用户

可视化命令操作

SQL 命令操作

用户表：mysql.user表

本质:读这张表进行增删改查

-- 创建用户 CREATE USER 用户名 IDENTIFIED BY '密码'
CREATE USER Tang IDENTIFIED BY '123456'

-- 修改密码（修改当前用户密码）这个时候修改的是root用户（最高权限用户）
SET PASSWORD=PASSWORD('123456')-- 注意：mysql8.0已经不能这样修改密码了

-- 修改密码（修改指定用户密码）
SET PASSWORD FOR Tang=PASSWORD('123456'); -- 第一种方式
ALTER USER 'Tang'@'%' IDENTIFIED BY '456789';-- 第二种方式

-- 重命名 rename user 原来名字 to 新的名字
RENAME USER Tang TO Wen

-- 用户授权grant all privileges(全部的权限) on 库.表 to Wen
-- all privileges 除了给别人授权，其它都能干（也就是不会给grant权限）
GRANT ALL PRIVILEGES ON *.* TO Wen

-- 查询权限
-- 查看指定用户的权限sql语句
SHOW GRANTS FOR Wen 

-- GRANT ALL PRIVILEGES ON *.* TO 'Wen'@'%'
SHOW GRANTS FOR root@localhost

-- GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION
-- 撤销权限 revoke 哪些权限，在哪个库撤销，给谁撤销
REVOKE ALL PRIVILEGES ON *.* FROM Wen

-- 注意：当你只给了指定库的权限时，用上面*.*不能直接撤销，需要指定库指定表
-- 现在要撤销Wen里school库的全部权限
REVOKE ALL PRIVILEGES ON school.* FROM Wen

-- 删除用户
DROP USER Wen

MySQL备份

为什么要备份：

• 保证重要的数据不丢失
• 数据转移

MySQL数据库备份的方式

1. 直接拷贝物理文件
2. 使用可视化工具（SQLyog）手动备份导出

点击你要导出的文件（库或者表）鼠标右键选择备份/导出，选择‘备选表作为sql存储’

1. 使用命令行cmd导出mysqldump命令行

# mysqldump -h主机 -u用户名 -p密码 数据库名 表名 >物理磁盘位置\文件名
mysqldump -hlocalhost -uroot -p123456 school student >D:\a.sql

# mysqldump -h主机 -u用户名 -p密码 数据库名 表1 表2 表3 ... >物理磁盘位置\文件名
mysqldump -hlocalhost -uroot -p123456 school student >D:\b.sql

# mysqldump -h主机 -u用户名 -p密码 数据库名 表名 >物理磁盘位置\文件名
mysqldump -hlocalhost -uroot -p123456 school student >D:\c.sql

# 注意：主机这里可以换，也就是说可以把表dump 到其它主机上面（别的电脑或者服务器上）

# 导入
# 登录的情况下，切换指定的数据库
# source备份文件
source C:\Users\HongHua\Desktop\a.sql
#mysql -u用户名 -p密码 库名<备份文件

假设你要备份数据库，来防止数据丢失，sql文件保存即可

把数据库给朋友：sql文件直接给别人即可

规范

为什么需要设计

当数据库比较复杂的时候我们就需要设计

糟糕的数据库设计：

• 数据冗余，浪费空间
• 数据库插入和删除都会麻烦、异常【屏蔽使用物理外键】
• 程序的性能差

良好的数据库设计：

• 节省内存空间
• 保证数据库的完整性
• 方便我们开发系统

软件开发中，关于数据库的设计

• 分析需求：分析业务和需要处理的数据库的需求
• 概要设计：设计关系图E-R图

设计数据库的步骤：（如个人博客）

• 收集信息，分析需求
  • 用户表（用户登录注销，用户的个人信息，写博客，创建分类）
  • 分类表（文章分类，创建人）
  • 文章表（文章的内容）
  • 评论表
  • 友链表（链接信息）
  • 粉丝表
  • 自定义表（系统信息，某个关键的字，或者一些主字段） key：value
• 标识实体（把需求落地到每个字段）
• 标识实体之间的关系
  • 写博客：user—>blog
  • 创建分类：user—>category
  • 关注：user—>user
  • 友链：links
  • 评论：user-user-blog

三大范式

为什么需要数据规范化？

• 信息重复
• 更新异常
• 插入异常
  • 无法正常显示信息
• 删除异常
  • 丢失有效的信息

第一范式（1NF）

原子性：保证每一列不可再分

第二范式（2NF）

前提：满足第一范式

每张表只描述一件事情

前提：满足第一范式和第二范式

第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关

（规范数据库的设计）

规范性和性能的问题

关联查询的表不得超过三张表

• 考虑商业化的需求和目标（成本，用户体验！）数据库的性能更加重要
• 在规范性能的问题的时候，需要适当的考虑一下 规范性！
• 故意给某些表增加一些冗余的字段（为了从多表查询中变为单表查询）
• 故意增加一些计算列（从大数据量降低为小数据的查询：索引，如计算有多少个记录）