Mysql（笔记）

目录

• 1、初识MySQL
  • 1.1、为什么学习数据库
  • 1.2、什么是数据库
  • 1.3、数据库分类
  • 1.4、MySQL简介
  • 1.5、基础操作
    • 1.连接数据库
    • 2.查看所有数据库
    • 3.切换数据库
    • 4.查看所有表
    • 5.显示库中表的所有信息
    • 6.创建数据库
    • 7.退出连接
    • 8.单行注释
• 2、操作数据库
  • 2.1、操作数据库(了解)
  • 2.2、数据库的列类型
  • 2.3、数据库的字段属性（重点）
  • 2.4、创建数据库表
  • 2.5、数据库表的类型
  • 2.6、修改删除表
• 3、MySQL数据管理
  • 3.1、外键（了解即可）
  • 3.2、DML语言（全部记住）
  • 3.3、添加
  • 3.4、修改
  • 3.5、删除
• 4、DQL查询数据(最重点)
  • 4.1、DQL
  • 4.2、指定查询字段
  • 4.3、where条件子句
  • 4.4、联表查询
  • 4.5、分页和排序
  • 4.6、子查询
  • 4.7、分组过滤
  • 4.8、select 小结
• 5、MySQL函数
  • 5.1、常用函数
  • 5.2、聚合函数（常用）
  • 5.3、数据库机别的MD5加密（扩展）

1、初识MySQL

JavaEE：企业级Java开发 Web

前端(页面：展示，数据！)

后台 (连接点：连接数据库JDBC，连接前端（控制，控制视图跳转，和给前端传递数据)

数据库（存数据，txt，Excel，word）

只会写代码，学好数据库，基本混饭吃；

操作系统，数据结构与算法！当一个不错的程序猿！

离散数学，数字电路，编译原理。+实战经验

1.1、为什么学习数据库

1、岗位需求

2、现在的世界，大数据时代~得数据者得天下。

3、被迫需求：存数据

4、数据库是所有软件体系中最核心的存在 DBA

1.2、什么是数据库

数据库（DB，DataBase）

概念：数据仓库，软件，安装在操作系统（window，linux，mac。。。之上

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

1.3、数据库分类

关系型数据库：Excel（SQL）

• MySQL，Oracle、Sql Server，DB2，SQLite
• 通过表与表之间，行和列之间的关系进行数据的存储

非关系型数据库：{key:value}（NoSQL）Not Only

• Redis，MongDB
• 非关系型数据库，对象存储，通过对象的自身的属性来决定。

DBMS(数据库管理系统)

• 数据库的管理软件，科学有效的管理我们的数据。维护和获取数据；
• MySQL，数据库管理系统

1.4、MySQL简介

MySQL是一个关系型数据库管理系统

1.5、基础操作

1.连接数据库

mysql -u账户名 -p密码

填写你的账户名和密码

2.查看所有数据库

show databases; --注意分号结尾

3.切换数据库

use 数据库名   --切换数据库

4.查看所有表

show tables;   --查看数据库中所有表

5.显示库中表的所有信息

describe 表名; --记得分号喔
desc 表名;    --效果相同

6.创建数据库

create database 数据库名;

7.退出连接

exit;

8.单行注释

--	单行注释（SQL的本来的注释）
/*
sql多行注释
*/
#	sqlyog的注释

数据库xxx语言

DDL 定义

DML 操作

DQL 查询

DCL 控制

2、操作数据库

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

mysql 关键字不区分大小写

2.1、操作数据库(了解)

1、创建数据

CREATE DATABASE [IF NOT EXISTS] learnmysql

2、删除数据库

DROP DATABASE [IF EXISTS] learnmysql

3、使用数据库

--tab 键上面，如果你的表名或者字段名试一个特殊字符，就需要带``
USE `school`

学习思路：

• 对照sqlyog可视化历史记录查看sql
• 固定的语法或关键字必须要强行记住！

2.2、数据库的列类型

数值

• tinyint 十分小的数据 1个字节
• smallint 较小的字节 2个字节
• mediumint 中等大小的数据 3个字节
• int 标准的整数 4个字节（常用）
• bigint 较大的数据 8个字节
• float 单精度浮点数 4个字节
• double 双精度浮点数 8个字节（精度问题！）
• decimal 字符串形式浮点数 （金融计算时一般用这个）

字符串

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

时间日期

java.util.Date

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

null

• 没有值，未知
• 注意，不要使用null进行运算，结果会为null

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

Unsigned:

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

zerofill：

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

自增：

• 通常理解为自增，自动在上一条记录的基础上+1（默认）
• 通常用来设计唯一的主键~index，必须是整数类型
• 可以自定义设计主键自增的起始值和步长

非空：NULL not NULL

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

默认：

• 设置默认的值！
• sex，默认值：男，如果不指定该列的值，则会有默认的值！

拓展：

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

2.4、创建数据库表

--注意：使用英文的(),
--表的名称和字段尽量使用``括起来
--字符串用'单引号'括起来
--所有语句后面加，(英文的),最后一个不用加
--PRIMARY KEY 主键，一般一个表一个
CREATE TABLE IF NOT EXISTS `student`(
`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
`name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
`pwd` VARCHAR(20) NOT NULL DEFAULT '123456' 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] `表名`(
    `字段名` 列类型[属性] [索引] [注释],
	`字段名` 列类型[属性] [索引] [注释],
    ......
    `字段名` 列类型[属性] [索引] [注释]
)[表类型][字符集设置][注释]

常用命令

SHOW CREATE DATABASE school	--查看创建数据库的语句
SHOW CREATE TABLE student	--查看student数据表的定义语句
DESC student	--显示表的结构

2.5、数据库表的类型

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

	MYISAM	INNODE
事务支持（同成功同失败）	不支持	支持
数据行锁定（锁定一行）	不支持 表锁	支持
外键约束	不支持	支持
全文索引	支持	不支持
表空间大小	较小	较大，约为2倍

常规使用操作：

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

在物理空间存在的位置

所有的数据库文件都存在data目录下，一个文件夹对应一个数据库

本质还是文件存储

MySQL引擎在物理文件上的区别

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

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

CHARSET=utf8

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

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

修改：

可以在my.ini中配置默认的编码

character-set-server=utf8

2.6、修改删除表

修改

--修改表 ALTER TABLE 旧表名 RENAME AS 新表明
ALTER TABLE teacher RENAME AS teacher1
 
--增加表的字段 ALTER TABLE 表名 ADD 字段名 列属性
ALTER TABLE teacher1 ADD age INT(11)
 
--修改表的字段（重命名，修改约束！）
--ALTER TABLE表名 MODIFY 字段名 列属性[]
ALTER TABLE teacher1 MODIFY age VARCHAR(11)      --修改约束
--ALTER TABLE表名 CHANGE 旧名字 新名字 列属性[]
ALTER TABLE teacher1 CHANGE age age1 INT(1)      --字段重命名
 
--删除表的字段：ALTER TABLE 表名 DROP 字段名
ALTER TABLE teacher1 DROP age1

删除

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

所有的创建和删除操作尽量加上判断，以免报错~（心烦）

注意点：

• ``所有的字段名，使用这个包裹！
• 注释 -- /**/
• sql 关键字大小写不敏感，建议写小写
• 所有的符号用英文！

3、MySQL数据管理

3.1、外键（了解即可）

方式一、在创建表的时候，增加约束（麻烦）

-- 定义外键key
-- 给这个外键添加约束（执行引用） references 引用
CREATE TABLE `student` (
  `id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
  `name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
  `pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
  `sex` VARCHAR(2) NOT NULL DEFAULT '男' COMMENT '性别',
  `birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
  `gradeid` INT(10) NOT NULL COMMENT '学生的年级',
  `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 `student` (
  `id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
  `name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
  `pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
  `sex` VARCHAR(2) NOT NULL DEFAULT '男' COMMENT '性别',
  `birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
  `gradeid` INT(10) NOT NULL COMMENT '学生的年级',
  `address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
  `email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8
 
-- 创建表的时候没有外键关系
ALTER TABLE `student` 
ADD CONSTRAINT `FK_ gradeid` FOREIGN KEY(`gradeid`) REFERENCES `grade`(`gradeid`);
 
-- ALTER TABLE 表名 ADD CONSTRAINT 约束名 FOREIGN KEY（作为外键的列） REFERENCES 哪个表（哪个字段）

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

最佳实践

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

3.2、DML语言（全部记住）

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

DML语言：数据操作语言

• Insert
• update
• delete

3.3、添加

insert

CREATE TABLE IF NOT EXISTS `student`(
`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
`name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT  '姓名',
`pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
`sex` VARCHAR(2) NOT NULL DEFAULT '男' COMMENT '性别',
`birthday` DATETIME DEFAULT NULL COMMENT '出生年月',
`gradeid` INT(10) NOT NULL COMMENT '学生的年级',
`address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
`email`VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
 
 
 
-- 插入语句（添加）
-- insert into 表名([字段1,字段2,..])values ('值1'),('值2'),...
INSERT INTO `grade`(`gradename`) VALUES ('大四')
 
-- 由于主键自增我们可以省略(如果不写表的字段，他就会一一匹配）
INSERT INTO `grade` VALUES ('大三')
 
-- 一般写插入语句，我们一定要数据和字段一一对应！
 
-- 插入多个字段
INSERT INTO `grade`(`gradename`) VALUES ('大一'),('大二')
 
INSERT INTO `student`(`name`) VALUES ('张三')
 
INSERT INTO `student`(`name`,`pwd`,`sex`) VALUES ('李梅','aaaa','女')
 
INSERT INTO `student`(`name`,`pwd`,`sex`) VALUES ('李梅','aaaa','女'),('肖战','aaada','女')

(遇到问题，在图形界面里改吧。)

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

注意事项：

1. 字段和字段之间使用 英文逗号 隔开
2. 字段是可以省略的，但是后面的值必须要一一对应，不能少
3. 可以同时插入多条数据，VALUES 后面的值，需要使用，隔开即可VALUES (),(),...

3.4、修改

update 修改谁 (条件) set 原来的值 = 新值

-- 修改学员名字，带了简介
UPDATE `student` SET `name` = '大壮' WHERE id = 3;
 
-- 不指定条件的情况下，会改变所有的表
UPDATE `student` SET `name`= '哦吼'
 
-- 语法：
-- UPDATE 表名 set clonum_name = value,[clonum_name = value] where [条件]

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

操作符会返回布尔值

操作符	含义	范围	结果
=	等于	1=2	false
<> 或 !=	不等于	1<>2	true
>	大于	1>2	false
<	小于	1<2	true
>=	大于等于
<=	小于等于
BETWEEN ...and	在某个范围内	[2,5]
AND	和 &&	5>1 and 1>2	false
OR	或 ||	5>1 or 2>1	true

-- 通过多个条件定位数据，无上限！ && || or
UPDATE `student` SET `name`='条件定位' WHERE `pwd` = 'aaaa' AND sex = '女'

语法：UPDATE 表名 set clonum_name = value,[clonum_name = value] where [条件]

注意：

• colnum_name 是数据库的列，尽量带上``
• 条件， 筛选的条件，如果没有指定，则会修改所有的列
• value ，是一个具体的值，也可以是一个变量

UPDATE `student` SET `birthday`= CURRENT_TIME WHERE `pwd` = 'aaaa' AND sex = '女'

3.5、删除

delete 命令

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

-- 删除指定数据
DELETE FROM `student` WHERE id = 3;
 
-- 删除数据 避免这样写，会全部删除
DELETE FROM `student`

TRUNCATE 命令

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

-- 清空表
TRUNCATE `student`

delete 和 TRUNCATE 的区别

• 相同点：都能删除数据，都不会删除表结构
• 不同：
  • TRUNCATE 重新设置 自增列，计数器会归零
  • TRUNCATE 不会影响事务

TRUNCATE 自增会归零

了解：DELETE 删除问题，重启数据库，现象

• InnoDB 自增列会重1开始 (存在内存中的，断电即失)
• MyISAM 继续从上一个自增量开始。(存在文件中的，不会丢失)

4、DQL查询数据(最重点)

4.1、DQL

(Data Query LANGUAGE：数据查询语言)

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

4.2、指定查询字段

-- 查询全部学生   SELECT 字段 FROM 表
SELECT * FROM student
 
SELECT * FROM result
 
-- 查询指定字段
SELECT `studentno`,`studentname` FROM student
 
-- 别名，给结果起一个名字 AS 可以给字段其别名，也可以给表起别名
SELECT `studentno` AS 学号,`studentname` AS 学生姓名 FROM student
 
-- 函数，Concat(a,b) 拼接字符串
     SELECT CONCAT('姓名：',studentname) AS 新名字 FROM student

语法：SELECT 字段,... FROM 表

有的时候，列名字不是那么清楚，我们可以起别名 AS 字段名 as 别名 表名 as 别名

去重 distinct

作用：去除SELECT查询出来的结果中重复的数据，重复数据只显示一条

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

数据库列的表达式：

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

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

select 表达式 from 表

4.3、where条件子句

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

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

逻辑运算符

运算符	语法	描述
and &&	a&&b a and b	逻辑与，两个都为真
or ||	a||b a or b	逻辑或，其中一个真，都真
Not !	not a !a	逻辑非，真为假，假为真

尽量使用英文字母

SELECT studentno,`studentresult` FROM result
 
-- 查询考试成绩在 95-100 分之间的
SELECT studentno,`studentresult` FROM result WHERE studentresult>=95 AND studentresult<=100
 
 
-- and  &&
SELECT studentno,`studentresult` FROM result WHERE studentresult>=95 && studentresult<=100
 
-- 模糊查询（区间）
SELECT studentno,`studentresult` FROM result
WHERE studentresult BETWEEN 95 AND 100
 
-- 除了1000号学生之外的学生成绩
SELECT studentno,`studentresult` FROM result
WHERE studentno != 1000;
 
> 模糊查询：比较运算符
 
| 运算符      | 语法                | 描述                                 |
| ----------- | ------------------- | ------------------------------------ |
| IS NULL     | A is null           | 如果操作符为null，则结果为真         |
| IS NOT NULL | a is not null       | 如果操作符不为not 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...其中的某一个值中 |
 
```sql
-- ==========================  where  ===============================
SELECT studentno,`studentresult` FROM result
 
-- 查询考试成绩在 95-100 分之间的
SELECT studentno,`studentresult` FROM result WHERE studentresult>=95 AND studentresult<=100
 
 
-- and  &&
SELECT studentno,`studentresult` FROM result WHERE studentresult>=95 && studentresult<=100
 
-- 模糊查询（区间）
SELECT studentno,`studentresult` FROM result
WHERE studentresult BETWEEN 95 AND 100
 
-- 除了1000号学生之外的学生成绩
SELECT studentno,`studentresult` FROM result
WHERE studentno != 1000;
 
INSERT INTO `result` (studentno,subjectno,examdate,studentresult) 
VALUES(1001,1,'2021-1-1 13:00:00',50),(1001,2,'2021-1-1 16:00:00',85),(1001,3,'2021-1-1 5:00:00',65),(1001,4,'2021-1-1 17:00:00',77),(1001,5,'2021-1-1 16:01:00',35)
 
INSERT INTO `result` (studentno,subjectno,examdate,studentresult) 
VALUES(1002,1,'2021-1-1 13:00:00',77),(1002,2,'2021-1-1 16:00:00',93),(1002,3,'2021-1-1 5:00:00',98),(1002,4,'2021-1-1 17:00:00',100),(1002,5,'2021-1-1 16:01:00',60)
 
`studentno`
 
SELECT studentno,`studentresult` FROM result WHERE studentresult>=50 AND studentresult<100
SELECT subjectno,`studentresult` FROM result WHERE studentresult>=50 AND studentresult<100
SELECT studentno,`studentresult` FROM result WHERE studentno = 1002
SELECT studentno,`studentresult` FROM result WHERE NOT studentresult = 98
 
 
-- ==========================  模糊查询  ===============================
 
-- 查询姓张的同学
-- 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 ('云南','北京朝阳')

4.4、联表查询

JOIN 对比

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

操作	描述
Inner join	如果表中至少有一个匹配，就返回结果
left join	会从左表中返回所有的值，即使右表中没有匹配
right join	会从右表中返回所有的值，即使左表中没有匹配

-- 查询缺考的同学
SELECT s.studentno,studentname,subjectno,studentresult
FROM student s
LEFT JOIN result AS r
ON s.studentno = r.studentno
WHERE studentresult IS NULL
 
-- 思考题（查询了参加考试的同学信息：学号，学生姓名，科目名称，分数）
/*思路
1.分析需求，分析查询的字段来自哪些表，（连接查询）
2.确定使用哪种连接查询？7种
确定交叉点（这两个表中哪个数据是相同的）
判断的条件：学生表中的studentno = 成绩表中的studentno
*/
 
SELECT s.studentno,studentname,subjectname,studentresult
FROM student s
RIGHT JOIN result r
ON r.studentno = s.studentno
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
 
 
-- 我要查询哪些表，select...
-- 从哪几个表中查 from 表 xxx join 连接的表 on 交叉条件
-- 假设存在一种多张表的查询，慢慢来，先查询两张表，然后再慢慢增加。
 
-- from a left join b
-- from a right join b

自连接

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

父类

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

子类

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

操作：查询父类对应的子类关系

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

-- 查询父子信息：把一张表拆成两个单独的表
SELECT a.`categoryname` AS '父栏目',b.`categoryname` AS '子栏目'
FROM `category` AS a,`category` AS b
WHERE a.`categoryid` = b.`pid`

-- 查询学生所属的年级（学号，学生姓名，年级名称）
SELECT studentno,studentname,gradename
FROM student s
INNER JOIN grade g
ON s.gradeid = g.gradeid
 
-- 查询科目所属的年级(科目id，科目名称，年级名称）
SELECT subjectno,subjectname,gradename
FROM `subject` s
INNER JOIN grade g
ON s.gradeid = g.gradeid
 
-- 查询参加考试的学生信息：学号，学生姓名，科目名，分数
SELECT stu.studentno AS '学号',studentname AS '学生姓名',subjectname AS '科目名',studentresult AS '分数'
FROM `student` stu
INNER JOIN result r
ON stu.studentno = r.studentno
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno

4.5、分页和排序

排序

-- 排序：升序ASC，降序DESC
-- ORDER BY 通过哪个字段排序，怎么排
-- 查询结果根据成绩 降序 排序
SELECT stu.studentno AS '学号',studentname AS '学生姓名',subjectname AS '科目名',studentresult AS '分数'
FROM `student` stu
INNER JOIN result r
ON stu.studentno = r.studentno
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
WHERE subjectname = '高等数学-1'
ORDER BY studentresult DESC

分页

-- 100w条数据
-- 为什么要分页？
-- 缓解数据库压力，给人的体验更好， 瀑布流
 
-- 分页，每页只显示五条数据
-- 语法：limit 起始值，页面的大小
-- 网页应用：当前，总的页数，页面的大小
-- LIMIT 0,2 1~2
SELECT stu.studentno AS '学号',studentname AS '学生姓名',subjectname AS '科目名',studentresult AS '分数'
FROM `student` stu
INNER JOIN result r
ON stu.studentno = r.studentno
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
WHERE subjectname = '高等数学-1'
ORDER BY studentresult DESC
LIMIT 0,2
 
 
-- 第一页 limit 0,5
-- 第二页 limit 5,5
-- 第三页 limit 10,5
-- 第四页 limit 15,5
-- 。。。
-- 第N页 limit 5*(N-1),5      (n-1)*pageSize,pageSize
-- 【pagesize：页面大小】
-- 【(n-1)*pagesize：起始值】
-- 【n：当前页】
-- 【数据总数/页面大小 = 总页数】

语法：limit(查询起始 下表，pagesize)

4.6、子查询

where（值是固定的，这个值是计算出来的）

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

where(select * from)

-- 1. 查询C语言-1的所有考试结果（学号，科目编号，成绩），降序
-- 方式一：使用连接查询
SELECT s.`studentno`,r.`subjectno`,`studentresult` 
FROM student s
INNER JOIN result r
ON s.studentno = r.studentno
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
WHERE subjectname = 'c语言-1'
ORDER BY studentresult DESC
 
 
-- 方式二：使用子查询（有里及外）
SELECT DISTINCT `studentno`,`subjectno`,`studentresult`
FROM `result`
WHERE `subjectno` = (
	SELECT subjectno FROM `subject`
	WHERE `subjectname` = 'c语言-1'
)
ORDER BY studentresult DESC
 
 
-- 在改造(由里及外)
SELECT `studentno`,`studentname` FROM student WHERE studentno IN(
	SELECT studentno FROM result WHERE subjectno = (
		SELECT subjectno FROM `subject` WHERE subjectname = 'c语言-1'
	)
)

4.7、分组过滤

-- 查询不同课程的平均分，最高分，最低分
-- 核心： 根据不同的课程分组
SELECT any_value(`subjectname`),AVG(`studentresult`) AS '平均分',MAX(`studentresult`) AS 最高分,MIN(`studentresult`) AS 最低分
FROM result r
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
GROUP BY r.subjectno  -- 通过什么字段来分组
HAVING 平均分>80

4.8、select 小结

顺序很重要

select 去重 要查询的字段 from 表

xxx join 要连接的表 on 等值判断

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

group by （通过哪个字段分组）

having （过滤后分组的信息，条件和where是一样的，位置不同）

order by （通过哪个字段排序）

limit startindex ，pagesize

5、MySQL函数

5.1、常用函数

-- ======================== 常用函数 ============================
 
-- 数学运算
SELECT ABS(-2)   -- 绝对值
SELECT CEILING(9.7)   -- 向上取整
SELECT FLOOR(9.7)    -- 向下取整
SELECT RAND()     -- 返回一个0~1之间的一个随机数
SELECT SIGN(-10)    -- 判断一个数的符号   0-0   负数返回-1，整数返回1
 
-- 字符串函数
SELECT CHAR_LENGTH('得道者多助，失道者寡助')   -- 字符串长度
SELECT CONCAT('I',' Love',' You')    -- 拼接字符串
SELECT INSERT('你今天睡醒了没有',1,1,'小詹') -- 查询，从某个位置开始替换某个长度
SELECT UPPER('Zjw') -- 转大写
SELECT LOWER('Zjw') -- 转小写
SELECT INSTR('xiaozhan','z')    -- 返回第一次出现的子串的索引
SELECT REPLACE('坚持就是胜利','坚持','努力') -- 替换出现的指定字符串
SELECT SUBSTR('坚持就是胜利',5,2)	-- 返回指定的子字符串(源字符串，截取的位置，截取的长度)
SELECT REVERSE('坚持就是胜利')	-- 反转
 
-- 查询姓 小的同学，改成大
SELECT REPLACE(studentname,'小','大') 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()
SELECT USER()
SELECT VERSION()

5.2、聚合函数（常用）

函数名称	描述
COUNT()	计数
SUM()	求和
AVG()	平均值
MAX()	最大值
MIN()	最小值
...	...

-- ========================= 聚合函数 ========================
-- 都能够统计 表中的数据（想查询一个表中有多少条记录，就用count）
SELECT COUNT(studentname) FROM student;-- count(指定列)，会忽略所有的null值
 
SELECT COUNT(*) FROM student; -- count(*)，不会忽略null值,本质：计算行数
SELECT COUNT(1) FROM result;  -- 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

5.3、数据库机别的MD5加密（扩展）

什么是MD5？

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

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

MD5破解网站的原理，就是找。

-- ==================== 测试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,'zhangsan','123456'),(2,'lsi','123456'),(3,'wangwu','123456')
 
-- 加密
UPDATE testmd5 SET pwd = MD5(pwd) WHERE id!=1
 
-- 插入时就要加密
INSERT INTO testmd5 VALUES(4,'xiaoming',MD5('123456'))
 
-- 如何校验：将用户传递进来的密码，进行md5加密，然后比对加密后的值
SELECT * FROM testmd5 WHERE `name` = 'xiaoming' AND pwd = MD5('123456')