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'),...
注意事项：
- 字段和字段之间使用 英文逗号 隔开
- 字段是可以省略的，但是后面的值必须要一一对应，不能少
- 可以同时插入多条数据，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')