0407

附录：

〇、视频课链接

书：Mysql 必知必会

【狂神说Java】MySQL最新教程通俗易懂

【MySQL最新教程通俗易懂】 的 笔记：来自狂神说公众号的文章—MySQL系列，和视频课的内容稍有偏差，
还有另外一个笔记：MySQL基础.md，这两个笔记结合着看。

CSDN上的一个笔记：《数据库系统概论》第五版 +学习笔记总目录

还有Redis：【狂神说Java】Redis最新超详细版教程通俗易懂

一、初识MySQL

1.为什么学习数据库？

答：数据库是几乎软件体系中最核心的一个存在。

2.什么是数据库？

答：
概念 : 长期存放在计算机内,有组织,可共享的大量数据的集合,是一个数据 “仓库”
作用 : 保存,并能安全管理数据(如:增删改查等),减少冗余…

3.数据库总览（分类）☆☆☆

• 关系型数据库（SQL）
  MySQL，Oracle，SQL Server，DB2…
  关系型数据库通过外键关联来建立表与表之间的关系。
• 非关系型数据库（NOSQL）—Not Only SQL
  Redis，MongoDB，…
  非关系型数据库通常指数据以对象的形式存储在数据库中，而对象之间的关系通过每个对象自身的属性来决定。

4.什么是DBMS

答：
数据库管理系统 ( DataBase Management System)，
数据库管理软件 , 科学组织和存储数据 , 高效地获取和维护数据。

5.安装MySQL

在Windows上安装mysql5.7 64位 （百度云里有保存包装包）

安装步骤：
1、下载后得到zip压缩包（用这个安装比较干净，哈哈，好像是说卸载的时候好卸载，能卸载干净）

2、解压到自己想要安装到的目录，本人解压到的是D:\mysql-5.7.19-winx64\mysql-5.7.19-winx64

3、添加环境变量：我的电脑->属性->高级->环境变量
选择PATH,在其后面添加: 你的mysql 安装文件下面的bin文件夹的路径

4、编辑 my.ini 文件
在mysql 安装文件下添加一个my.ini 文件（可以先新建一个txt文件，然后后缀改成.ini，再用记事本打开编辑），注意替换路径位置

[mysqld]
basedir=D:\mysql-5.7.19-winx64\mysql-5.7.19-winx64\
datadir=D:\mysql-5.7.19-winx64\mysql-5.7.19-winx64\data\
port=3306
skip-grant-tables

5、启动管理员模式下的CMD，

并将路径切换至**mysql下的bin目录（D:\mysql-5.7.19-winx64\mysql-5.7.19-winx64\bin）**下，

然后输入mysqld –install (安装mysql)

6、再输入 mysqld --initialize-insecure --user=mysql 初始化数据文件，初始化之后，在mysql安装文件下会生成一个data文件夹：

7、然后再次启动mysql ；
然后用命令 mysql –u root –p 进入mysql管理界面（密码可为空）

8、进入界面后更改root密码（用户名是root，密码改为123456）

update mysql.user set authentication_string=password('123456') where user='root' and Host = 'localhost';

这里记得加最后面的英文分号。
修改完密码之后，就进入mysql的状态下了：

9、刷新权限

flush privileges;

10、修改 my.ini文件删除（注释掉）最后一句skip-grant-tables

11、先退出mysql服务；
然后关闭重启mysql即可正常使用；

net stop mysql
net start mysql

12、最后再连接数据库（命令行方式连接数据库）：

6.安装可视化工具SQLyog

安装包在百度云里：

安装步骤：

剩下的都是傻瓜式安装，注意修改下安装路径，我把它安装在D盘：D:\SQLyog

安装好之后新建一个连接：（可视化方式连接数据库）

然后就可以进行可视化操作了。

7.用 SQLyog 创建数据库 和 创建表

①创建数据库school

这个操作对应的命令行指令是：

CREATE DATABASE `school`CHARACTER SET utf8 COLLATE utf8_general_ci; 

②创建表

这步操作对应的命令行指令巨长，所以说用可视化的软件操作起来更方便，但一些基本的指令还是要会，不知道怎么写就去历史记录查看每一步可视化操作对应的代码。

③查看表（打开表）

④小结：

每一个SQLyog的执行操作，本质上就是对应了一个sql，可在历史记录中查看。

⑤注释 + 运行单行代码

注释用两个杠-- 注释内容，注意--后面要加个空格，然后再写注释内容。（见下图）

运行单行代码：

8.用命令行连接数据库

因为在上面的5.安装MySQL中添加了环境变量，所以可以在任意目录下连接数据库。

• 在DOS命令行窗口输入连接数据库语句：

mysql -u root -p

然后输入密码就可成功连接数据库。

几个基本的数据库操作命令

//安装mysql的时候的操作：
update user set password=password('123456')where user='root'; 修改密码
flush privileges; 刷新数据库
//对数据库的一些基本操作：
show databases; 显示所有数据库
use school;打开数据库school
show tables; 显示数据库school中所有的表
describe student; 显示表school数据库中student表的列信息
describe teacher; 显示表school数据库中teacher表的列信息

create database name; 创建数据库

exit; 退出Mysql
? 命令关键词 : 寻求帮助
-- 表示注释

注意：
如果是在DOS命令窗口进行数据库的操作，就要输入如下指令，一定要全大写字母，否则很多指令就是无效指令。
可以先在SQLyog中输入上面的小写形式的代码，然后再在历史记录中把全大写的写法复制到DOS命令窗口中，因为SQLyog自动把所有小写转换成大写。
（这里最好是直接复制过去，自己看着手写一遍，但有的符号格式就是不对，直接复制就不会出错，更保险）

SELECT VERSION(); -- 查询数据库版本
SHOW DATABASES;-- 显示所有数据库
USE `school`;  -- 打开数据库school，这个符号不是单引号，是tab键上面的那个符号``
SHOW TABLES ;-- 显示数据库school中所有的表
DESCRIBE student; -- 显示表school数据库中student表的列信息
DESCRIBE teacher; -- 显示表school数据库中teacher表的列信息

9.数据库的基本知识

数据库类似于Excel表，一个数据库类似于一个类class，
表的每一列，即列属性，是类中的成员变量/成员属性，数据库中叫字段；
表的每一行，即行属性，是类的一个具体的对象。

10.结构化查询语句分类

二、操作数据库和数据表（创建、删除、使用、显示、修改）

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

2.1 命令行操作数据库（创建数据库、删除数据库、使用数据库、显示数据库）☆☆☆

（了解）

创建数据库 :  create database [if not exists] 数据库名;
例如：CREATE DATABASE IF NOT EXISTS animals

删除数据库 : drop database [if exists] 数据库名;
例如：DROP DATABASE IF EXISTS animals

显示所有数据库：show databases; 
例如：SHOW DATABASES;-- 显示所有数据库

使用数据库：use 数据库名;
例如：USE `school` -- 使用数据库school
注意：如果表名或字段名是一个特殊字符，就需要带 ``（tab键上面的）

显示数据库school中所有的表：show tables; 
显示表school数据库中student表的列信息：describe student; 
显示表school数据库中teacher表的列信息：describe teacher; 

学习思路：
对照SQLyog可视化软件的历史记录查看sql；
固定的语法或关键字必须要记住。

2.2 数据字段 的 类型 和 属性（重要）

1.列名：

• 也叫列属性，类似于类的成员属性，也叫字段。

2.数据类型（重要）：

①数值类型（int 和 decimal） 和 字符串（varchar）：

数值：
int ：整型数据
decimal：存储金额（金额必须是精确值，而float和double都不能精确表示一个小数，decimal的底层是一个字符串，所以它能精确表示一个小数），

语法：DECIMAL(M,D)
其中，M是最大位数（精度），范围是1到65。可不指定，默认值是10；D是小数点右边的位数（小数位），范围是0到30，并且不能大于M，可不指定，默认值是0。
例如：字段 salary DECIMAL(5,2)，能够存储具有五位数字和两位小数的任何值，因此可以存储在salary列中的值的范围是从-999.99到999.99。

示例：DECIMAL(10,2)表示数据最大长度为10，小数点后有两位。

CREATE DATABASE `shop` CHARACTER SET utf8 COLLATE utf8_general_ci; 
CREATE TABLE `account` (
  `id` INT(4) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(20) NOT NULL,
  `money` DECIMAL(10,2) NOT NULL, -- VARCHAR(30)
  PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

INSERT INTO `account`
VALUES(12,'小王',1000),(16,'小张',800)

结果：

字符串：
varchar ：存储字符串
text：保存大文本

②日期和时间型数值类型（datetime） 和 NULL值：

datetime：YYYY-MM-DD HH:mm:ss
timestamp：时间戳

3.长度：

• 该数据的长度

4.默认：

• 用于设置默认值；
• 例如：性别字段,默认为"男" , 否则为 “女” ; 若无指定该列的值 , 则默认值为"男"的值

5.主键：

• 一个表中只能有一个主键。

6.非空（NOT NULL）：

• 如果设置为NOT NULL , 则该列必须有值。

7.Unsigned：

• 无符号，表明该数据列不允许出现负数；

8.自增（Auto_InCrement）：

• 自动增长的 , 每添加一条数据 , 默认是自动在上一个记录数上加 1(默认)；
• 通常用于设置主键 , 且为整数类型（int）；
• 可以自定义自增量（高级—自动增量）
• 

9.Zerofill：

• 用0填充，不足位数的用零来填充；
• 例如：int(3)，输入一个5，就会显示005。

10.更新：

• 后面会讲。

11.注释：

• 给字段值/列名加个注释。

2.3 每一个表都必须存在以下五个字段

以后会讲，未来做项目用的，表示一个纪录存在的意义。

2.4 创建数据表（CREATE TABLE）☆☆☆

2.4.1 看个例子

1.表名还有数据字段的属性都用飘``括起来；
2.comment表示注释，注释内容用单引号’‘括起来；
3.default表示默认值，默认值也用单引号’'括起来；
4.PRIMARY KEY表示主键，一般把主键的设置放在最后，且主键只有一个；
5.ENGINE是引擎（具体见本节的2.4.3 补充），CHARSET是字符集。

CREATE TABLE IF NOT EXISTS `student`(
  `id` INT(3) NOT NULL AUTO_INCREMENT COMMENT '学号', -- 设置自增加
  `name` VARCHAR(20)  NOT NULL DEFAULT '匿名' COMMENT '姓名',
  `pwd` VARCHAR(10)  NOT NULL DEFAULT '123456' COMMENT '密码',
  `sex` VARCHAR(2) NOT NULL DEFAULT '男' COMMENT '性别',
  `birthday` DATETIME DEFAULT NULL COMMENT '生日', -- 数据类型是日期和时间型数据类型
  PRIMARY KEY(`id`) -- 设置主键
)ENGINE=INNODB DEFAULT CHARSET=utf8

注意：创建表之前 , 一定要先选择数据库，例如下面先选择数据库school，然后选中创建表的语句，然后点击单句运行按钮。

2.4.2 创建表的格式：

CREATE TABLE [IF NOT EXISTS] ``(
`字段名` 列类型 [属性] [索引] [注释],
`字段名` 列类型 [属性] [索引] [注释],
...
`字段名` 列类型 [属性] [索引] [注释],
PRIMARY KEY (`字段名`)
)[表类型][字符集设置][注释]

如果上面的写不出来，有个偷懒的操作，就是先用SQLyog进行可视化操作，然后再用下面的指令来查看具体的指令是怎么写的，初学阶段不推荐这样做。

-- 查看创建数据库school的语句
SHOW CREATE DATABASE school;

-- 查看创建数据表student的语句
SHOW CREATE TABLE student;

-- 显示表结构
DESC student; 
这个效果和前面说过的是一样的：
describe student; 显示表school数据库中student表的列信息
describe teacher; 显示表school数据库中teacher表的列信息

2.4.3 补充：数据表的 引擎(类型) 和 字符集

创建表时写了一个ENGINE = InnoDB，这个是数据表的引擎的意思，也就是数据表的类型的意思；还写了个DEFAULT CHARSET=utf8，这个是数据表的字符集，在哪里设置这两个参数呢？

一、首先说引擎
引擎表示数据表的类型，MySQL的数据表的类型 : MyISAM , InnoDB , HEAP , BOB , CSV等…

-- 查看mysql所支持的引擎类型 (表类型)
SHOW ENGINES;

常见的 MyISAM 与 InnoDB 类型：
InnoDB 是默认使用；MyISAM 是早些年使用的。

常规使用操作：

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

数据表的类型不同，对应的底层文件也不同。
数据表的存储位置：

• MySQL数据表以文件方式存放在磁盘中，包括表文件 , 数据文件 , 以及数据库的选项文件；
• 位置 : Mysql安装目录\data下（D:\mysql-5.7.19-winx64\mysql-5.7.19-winx64\data）存放数据表 。data目录下的不同的文件夹名对应不同的数据库 , 每个文件夹下的各个文件名对应不同的数据表。
• *.frm —表结构定义文件；
• *.MYD —数据文件（data）；
• *.MYI —索引文件（index）；
• InnoDB类型数据表只有一个 *.frm文件 , 以及上一级目录的ibdata1文件；
• MyISAM类型数据表就包括上面三个文件 。
  

二、然后说设置数据库表字符集编码：
我们可为数据库,数据表,数据列设定不同的字符集，有两种设定方法。
方式1：

• 创建表时通过命令来设置 , 如 : CREATE TABLE 表名()CHARSET = utf8;
  如果创建时不设置字符集的话，会使用mysql默认的字符集编码（不支持中文）；

方式2：

• 可以在MySQL数据库配置文件 my.ini 中直接设定：character-set-server=utf8；
• 不推荐，因为如果别的电脑没配置这个，你的程序就没法运行；
• 

2.5 修改表、删除表

2.5.1 修改数据表（ALTER TABLE）☆☆☆

修改表名 :ALTER TABLE 旧表名 RENAME AS 新表名
例如：ALTER TABLE student RENAME AS students

添加字段 : ALTER TABLE 表名 ADD 字段名 列属性[属性]
例如：ALTER TABLE teacher ADD `id` INT(5)

修改字段 :
ALTER TABLE 表名 MODIFY 字段名 列类型[属性]
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 列属性[属性]
例如：
ALTER TABLE teacher ADD `id` INT(5)
ALTER TABLE teacher CHANGE `age` `age1` INT(3)

删除字段 :  ALTER TABLE 表名 DROP 字段名

解释：

• MODIFY — 修改约束（修改某个字段的属性，或者说修改某个列属性），不能重命名；
• CHANGE — 重命名（修改某个字段名，或者说修改某个列名），重命名的同时后面要加上新字段名的属性，即修改约束，所以CHANGE关键字可以实现重命名+修改约束。

注意：
一般不用这两个关键词来修改，都是直接在表里修改：

2.5.2 删除数据表（DROP TABLE）☆☆☆

删除表：
DROP TABLE [IF EXISTS] 表名
例如：
现在数据库school中创建了一个数据表animal：
CREATE TABLE IF NOT EXISTS `animal`(
 `name` VARCHAR(20) NOT NULL DEFAULT 'null' COMMENT '动物名称'

)ENGINE=INNODB DEFAULT CHARSET=utf8
然后删除表：
DROP TABLE IF EXISTS animal

注意：
所有的创建和删除操作尽量加上判断（IF EXISTS），以免报错。

三、MySQL数据管理

3.1 数据库级别的外键（了解即可）

有两种方式去使用外键：

1. 创建表的时候增加约束（麻烦，比较复杂）；

-- 年级表
CREATE TABLE `grade` (
  `id` INT(30) NOT NULL AUTO_INCREMENT COMMENT '年级id',
  `name` VARCHAR(50) NOT NULL COMMENT '年级名称',
  PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

-- 先删除之前的学生表
DROP TABLE `student`;

-- 学生表 id_grade 字段 需要引用年级表的 id字段
-- 定义外键key
-- 给这个外键添加约束（执行引用） references 引用
CREATE TABLE IF NOT EXISTS `student` (
  `id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
  `name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
  `sex` VARCHAR(2) NOT NULL DEFAULT '女' COMMENT '性别',
  `birthday` DATETIME NOT NULL COMMENT '出生日期',
  `address` VARCHAR(100) DEFAULT NULL COMMENT '家庭地址',
  `email` VARCHAR(30) DEFAULT NULL COMMENT '邮箱',
  `id_grade` INT(30) NOT NULL COMMENT '年级id',
  PRIMARY KEY(`id`),
  KEY `fk_id_grade`(`id_grade`),
  CONSTRAINT `fk_id_grade` FOREIGN KEY (`id_grade`) REFERENCES `grade` (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8

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

-- 年级表
CREATE TABLE `grade` (
  `id` INT(30) NOT NULL AUTO_INCREMENT COMMENT '年级id',
  `name` VARCHAR(50) NOT NULL COMMENT '年级名称',
  PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8


-- 学生表
CREATE TABLE IF NOT EXISTS `student` (
  `id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
  `name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
  `sex` VARCHAR(2) NOT NULL DEFAULT '女' COMMENT '性别',
  `birthday` DATETIME NOT NULL COMMENT '出生日期',
  `address` VARCHAR(100) DEFAULT NULL COMMENT '家庭地址',
  `email` VARCHAR(30) DEFAULT NULL COMMENT '邮箱',
  `id_grade` INT(30) NOT NULL COMMENT '年级id',
  PRIMARY KEY(`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8

-- DROP TABLE `grade`;
-- DROP TABLE `student`;

-- 创建表的时候没有外键关系
ALTER TABLE `student` 
ADD CONSTRAINT `fk_id_grade` FOREIGN KEY (`id_grade`) REFERENCES `grade` (`id`);

-- ALTER TABLE 表 ADD CONSTRAINT 约束名 FOREIGN KEY (作为外键的列) REFERENCES 哪个表 (哪个字段)

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

最佳实践：

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

3.2 DML语言（增、删、改—重要！全部记住）☆☆☆

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

DML语言：数据库操作语言

• 增：insert
• 删：delete
• 改：update

3.3 添加 — INSERT INTO ☆☆☆

1. 插入操作的格式 和 注意事项

-- 插入一条数据：
INSERT INTO `表名`(`字段1`,`字段2`) 
VALUES('值1','值2');

-- 插入多条数据：
INSERT INTO `表名`(`字段1`,`字段2`) 
VALUES('值1-1','值1-2'),('值2-1','值2-2'),('值3-1','值3-2');

注意：

1. 字段和字段之间用逗号隔开；
2. 有默认值的字段可以不赋值，但有默认值的字段必须赋值；
3. 各个字段(字段1,字段2) 和VALUES后面赋的值(‘值1’,‘值2’)必须一一对应；
4. 可以插入多条数据，每条数据用()括起来，多条数据之间用逗号隔开；
5. 如果不写字段值，就要把给所有字段赋值，一个都不能少。

2. 先创建两个表：

-- grade表
CREATE TABLE `grade` (
  `id` INT(30) NOT NULL AUTO_INCREMENT COMMENT '年级id',
  `name` VARCHAR(50) NOT NULL COMMENT '年级名称',
  PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

-- student表
CREATE TABLE IF NOT EXISTS `student` (
  `id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
  `name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
  `sex` VARCHAR(2) NOT NULL DEFAULT '女' COMMENT '性别',
  `birthday` DATETIME NOT NULL COMMENT '出生日期',
  `address` VARCHAR(100) DEFAULT NULL COMMENT '家庭地址',
  `email` VARCHAR(30) DEFAULT NULL COMMENT '邮箱',
  `id_grade` INT(30) NOT NULL COMMENT '年级id',
  PRIMARY KEY(`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8

3. 对表grade进行插入操作：

（插入一条数据）

-- 标准的插入语句：INSERT INTO `表名`(`字段1`,`字段2`) VALUES('值1','值2');
INSERT INTO `grade`(`id`,`name`)
VALUES('123','大一');
-- 因为`id`字段是自增加，所以可以省略，只插入`name`字段的值
INSERT INTO `grade`(`name`)
VALUES('大二');

结果：

（插入多条数据）

-- 标准的插入语句：INSERT INTO `表名`(`字段1`,`字段2`) 
		-- VALUES('值1-1','值1-2'),('值2-1','值2-2'),('值3-1','值3-2');
INSERT INTO `grade`(`id`,`name`)
VALUES('11','研二'),('15','研三'),('19','研一');
-- 因为`id`字段是自增加，所以可以省略，只插入`name`字段的值
INSERT INTO `grade`(`name`)
VALUES('博一'),('博三');
-- 如果不写字段值，就要把给所有字段赋值：
INSERT INTO `grade`
VALUES('200','博6'),('250','博5');

结果：

注意：有默认值的字段可以不用赋值，但没有默认值的数据必须赋值！！！

4. 对表student进行插入操作：

（插入一条、多条数据）

-- 标准的插入语句：INSERT INTO `表名`(`字段1`,`字段2`) VALUES('值1','值2');
-- 注意：插入一条数据，有默认值的字段可以不用赋值，但没有默认值的必须要赋值
INSERT INTO `student`(`name`,`birthday`,`id_grade`)
VALUES('aaa','1998-02-06 12:26:05','2017');
-- 插入多条数据：
INSERT INTO `student`(`name`,`birthday`,`id_grade`)
VALUES('abc','1996-05-09 12:26:05','2021'),
      ('bbb','1995-02-28 12:26:05','2020'),
      ('ccc','1994-12-20 12:26:05','2019');
INSERT INTO `student`(`id`,`name`,`sex`,`birthday`,`address`,`email`,`id_grade`)
VALUES('121','乐乐','男','1995-02-28 16:20:00','西北大学','17966@qq.com','2020');
-- 如果不写字段值，就要把给所有字段赋值：
INSERT INTO `student`
VALUES('156','萌萌','女','1994-12-20 16:20:00','西北大学','82628@qq.com','2017');

结果：

3.4 修改 — UPDATE ☆☆☆

语法：

UPDATE `表名` SET `字段1/列属性1`=value1,`字段2/列属性2`=value2,... WHERE condition;

1. 可以修改多个字段值，中间用英文逗号隔开；
2. value是修改后的值，也可以是个变量（一般是当前的时间）；
3. condition是筛选条件，可以有多个删选条件；必须加筛选条件，否则会把该表中所有数据的某个列属性都修改了，这是很危险的！
   例如：UPDATE student SET name = ‘xyz’会把该表中每条数据的name列属性都修改成’xyz’

示例：

-- 修改一个字段值：
-- 把（`id`字段值等于2的）数据的`name`字段内容改为'xyz'
UPDATE `student` SET `name` = 'xyz' WHERE `id` = 2;

-- 修改多个字段值：
-- 把（`name`字段值等于'ccc'的）数据的`name`字段内容改为'ttt',`address`的字段内容'大雁塔'
UPDATE `student` SET `name`='ttt',`address`='大雁塔' WHERE `name`='ccc';

-- 注意：如果不加where条件，就会把该表中每条数据的列属性都修改了，这是很危险的！
-- 例如：UPDATE `student` SET `name` = 'xyz'会把该表中每条数据的`name`列属性都修改成'xyz'

-- 修改多个字段值，并且有多个筛选条件：
UPDATE `student` SET `name`='毕业生',`address`='西北大学（已毕业）' 
WHERE `address`='西北大学' AND `id_grade`<='2017'

结果：

补充：where后面的筛选条件

3.5 删除 — DELETE FROM ☆☆☆

语法：

DELETE FROM `表名` WHERE condition;

condition依然是筛选条件；
注意：如果不指定筛选条件，则会删除该表的所有列数据，这他妈就是删库跑路啊，一定注意，别这么干！！！

示例：

-- 直接清空整个数据表student：
DELETE FROM `student`

-- 把student表中`id_grade`字段值大于2020的数据删除：
DELETE FROM `student` WHERE `id_grade`>'2020'

清空数据库表 ：TRUNCATE命令
作用：用于完全清空表数据 , 但表结构 , 索引 , 约束等不变 ;

语法：

TRUNCATE [TABLE] table_name;

示例：

-- 清空年级表(两种写法都可以)
TRUNCATE `grade`
TRUNCATE TABLE `test`

DELETE FROM 和 TRUNCATE 的区别

相同点：都能删除数据，且都不会改变表的结构、索引、约束等。
不同点：

• 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('row1'),('row2'),('row3');

-- 删除表数据(不带where条件的delete)
DELETE FROM test;
-- 结论:如不指定Where则删除该表的所有列数据,自增当前值依然从原来基础上进行,会记录日志.

-- 删除表数据(truncate)
TRUNCATE TABLE test;
-- 结论:truncate删除数据,自增当前值会恢复到初始值重新开始;不会记录日志.

补充：（了解即可）delete 删除的问题
使用DELETE清空不同引擎的数据库表数据，重启数据库服务后

• InnoDB : 自增列从初始值重新开始 (因为是存储在内存中,断电即失)
• MyISAM : 自增列依然从上一个自增数据基础上开始 (存在文件中,不会丢失)

四、DQL查询数据（最重点！！！）

DQL（Database Query Language 数据查询语言）
关键词：SELETE
数据库中最核心的语言，最重要的语句！！！

SELECT语法 --> 27.SELECT小结

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

注意 :

• ①[ ] 括号代表可选的 , { }括号代表必选的；
• ②上面的各个可选部分是有顺序的，不能打乱顺序：

小目录：

• 16 &17：SELECT [去重-DISTINCT] 要查询的字段 FROM 数据表A （表和字段可用AS取别名）
• 20：xxx(INNER) JOIN 要连接的表B ON 交叉条件 – 连接查询
• 18 & 19 & 23：WHERE 条件 – 筛选条件（如果有多个筛选条件，就在后面加AND关键字，不要加逗号,）模糊查询（关键字LIKE和关键字IN）> 或者子查询语句）
• 25：[GROUP BY …] – 通过哪个字段来分组
• 25：[HAVING] – 过滤分组后的信息，条件和where是一样的，区别就是先后位置不同
• 22：ORDER BY – （通过哪个字段排序）[升序ASC / 降序DESC]
• 22：LIMIT startIndex, pageSize – 分页

上面的可选部分顺序很重要；
HAVING 和 WHERE的区别就在于先后位置不同。

预备工作：16-20的数据库代码：

-- 第一部分：
CREATE DATABASE IF NOT EXISTS `school`;
-- 创建一个school数据库
USE `school`;-- 创建学生表
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 `grade`;
CREATE TABLE `grade`(
  `gradeid` INT(11) NOT NULL AUTO_INCREMENT COMMENT '年级编号',
  `gradename` VARCHAR(50) NOT NULL COMMENT '年级名称',
    PRIMARY KEY (`gradeid`)
) ENGINE=INNODB AUTO_INCREMENT = 6 DEFAULT CHARSET = utf8;

-- 创建科目表
DROP TABLE IF EXISTS `subject`;
CREATE TABLE `subject`(
    `subjectno`INT(11) NOT NULL AUTO_INCREMENT COMMENT '课程编号',
    `subjectname` VARCHAR(50) DEFAULT NULL COMMENT '课程名称',
    `classhour` INT(4) DEFAULT NULL COMMENT '学时',
    `gradeid` INT(4) DEFAULT 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;

-- 第三部分：
-- 插入学生数据 其余自行添加 这里只添加了2行
INSERT INTO `student` (`studentno`,`loginpwd`,`studentname`,`sex`,`gradeid`,`phone`,`address`,`borndate`,`email`,`identitycard`)
VALUES
(1000,'123456','张伟',0,2,'13800001234','北京朝阳','1980-1-1','text123@qq.com','123456198001011234'),
(1001,'123456','赵强',1,3,'13800002222','广东深圳','1990-1-1','text111@qq.com','123456199001011233');

-- 插入成绩数据  这里仅插入了一组，其余自行添加
INSERT INTO `result`(`studentno`,`subjectno`,`examdate`,`studentresult`)
VALUES
(1000,1,'2013-11-11 16:00:00',85),
(1000,2,'2013-11-12 16:00:00',70),
(1000,3,'2013-11-11 09:00:00',68),
(1000,4,'2013-11-13 16:00:00',98),
(1000,5,'2013-11-14 16:00:00',58);

-- 插入年级数据
INSERT INTO `grade` (`gradeid`,`gradename`) 
VALUES(1,'大一'),(2,'大二'),(3,'大三'),(4,'大四'),(5,'预科班');


-- 第四部分：
-- 插入科目数据
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);

UPDATE student SET `studentname`='张强强' WHERE `studentno`=1001
UPDATE student SET `studentname`='张伟强' WHERE `studentno`=1000

INSERT INTO `student` (`studentno`,`loginpwd`,`studentname`,`sex`,`gradeid`,`phone`,`address`,`borndate`,`email`,`identitycard`)
VALUES
(1002,'123456','刘杰',0,2,'1381234','','1986-1-1','tex3@qq.com','123001011234'),
(1003,'123456','乐乐',0,2,'1380001234','','','','')

UPDATE student SET `address`=NULL WHERE `studentname`='乐乐'

16. 查询某个字段select 和 给字段起别名as

1.查询某个数据表的所有字段/某些指定字段：关键字SELECT
2.给某个字段或者数据表起别名：关键字AS

示例：

-- ①查询某个数据表的所有字段（用*作为通配符）
SELECT * FROM `student`

-- ②查询某个数据表的指定字段
SELECT `studentno`,`studentname` FROM `student`

-- ③给字段或数据表加个别名
-- 注意：数据表的别名不要加单引号：SELECT `studentno` as '学号',`studentname` as '姓名' FROM `student` as '表1'
-- 下面两种写法都可以：
SELECT `studentno` AS '学号',`studentname` AS '姓名' FROM `student` AS 表1
SELECT `studentno` AS 学号,`studentname` AS 姓名 FROM `student` AS 表1

-- ④调用函数CONCAT()来拼接字符串：
SELECT CONCAT('学号：',`studentno`) ,CONCAT('姓名：',`studentname`) FROM `student`

⑤调用函数CONCAT()来拼接字符串 + 重命名字段：
SELECT CONCAT('学号：',`studentno`) AS 学号信息,CONCAT('姓名：',`studentname`) AS 姓名信息 FROM `student`

结果：
①查询某个数据表的所有字段（用*作为通配符）

②查询某个数据表的指定字段

③给字段或数据表加个别名

④调用函数CONCAT()来拼接字符串：

⑤调用函数CONCAT()来拼接字符串 + 重命名字段

17. 去重复distinct 及 数据库中的表达式

作用 : 去掉SELECT查询返回的记录结果中重复的记录 ( 返回所有列的值都相同 ) , 只返回一条。

示例：通过(学号) 字段查看哪些同学参加了考试并去除重复项

-- ①先查看存储考试成绩的数据表result：（查看所有字段）
SELECT * FROM result;

-- ②查看哪些同学参加了考试：（查看学号studentno字段）
SELECT studentno FROM result; 

-- ②用 DISTINCT 去除重复项：
SELECT DISTINCT studentno FROM result; 

结果：
①

②

③

数据库中的表达式：
一般由文本值 , 列值 , NULL , 函数和操作符等组成。

-- selcet查询中可以使用表达式

-- ①查询版本号（函数）
SELECT VERSION()

-- ②用来计算（表达式）
SELECT 100*3-1 AS 计算结果

-- ③查询自增步长（变量）
SELECT @@auto_increment_increment

-- ④查询数据表result的studentresult（学生成绩）字段
SELECT `studentresult` FROM `result`
-- ⑤给所有成绩加一分：
SELECT `studentresult`+1 AS 全体加一分 FROM `result`

①

②

③

④

⑤

18. where子句之逻辑运算符

作用：查询时的筛选条件。

逻辑操作符：

示例：

-- 满足条件的查询(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;

-- 使用NOT
SELECT studentno,studentresult
FROM result
WHERE NOT studentno=1000;

19. 模糊查询操作符☆☆☆

比较操作符：

补充：
关键字LIKE结合使用的通配符 :

• % (代表0到任意个字符) ；
• _ (表示一个字符)；
• __ (表示两个字符)。

关键词IN

注意：

• 数值数据类型的记录之间才能进行算术运算 ;
• 相同数据类型的数据之间才能进行比较 ;

示例：

-- 模糊查询 between and \ like \ in \ null

-- =============================================
-- LIKE
-- =============================================
-- 查询姓张的同学的学号及姓名
-- 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 '%伟%'

-- 查询姓名中含有 强 字的
SELECT `studentno`,`studentname` FROM student
WHERE `studentname` LIKE '%强%'

-- 查询姓名中含有特殊字符的需要使用转义符号 '\'
-- 自定义转义符关键字: ESCAPE ':'

-- =============================================
-- IN
-- =============================================
-- 查询学号为1000,1001,1002的学生姓名
SELECT `studentno`,`studentname` FROM student
WHERE `studentno` IN (1000,1001,1002) -- 

-- 查询地址在'北京','北京朝阳','南京','广东深圳'的学生
SELECT `studentno`,`studentname` FROM student
WHERE `address` IN ('北京','北京朝阳','南京','广东深圳')

-- =============================================
-- NULL 空
-- =============================================
-- 查询出生日期没有填写的同学
-- 不能直接写=NULL , 这是代表错误的 , 用 is null
SELECT studentname FROM student
WHERE BornDate IS NULL;

-- 查询出生日期填写的同学
SELECT `studentno`,`studentname` FROM student
WHERE `borndate` IS NOT NULL

-- 查询没有写家庭住址的同学(空字符串不等于null)
SELECT * FROM student
SELECT `studentno`,`studentname` FROM student
WHERE `address`='' -- 结果有两条
SELECT `studentno`,`studentname` FROM student
WHERE`address` IS NULL -- 结果是空

注意：
IS NULL这块，空字符串并不等于NULL；
判断是否是空字符串，要用=''

结果：

20. 联表查询

连接查询 join on
等值查询 where

连接查询：
如需要多张数据表的数据进行查询，则可通过连接运算符实现多个查询。

七种Join对比：

连接查询的思路：
1.分析需求，分析查询的字段来自哪些表；（连接查询）
2.确定使用哪种连接查询（7种）；
3.确定交叉点（两个表中哪个数据是相同的）；
4.判断的条件：数据表A的cc字段 = 数据表B的cc字段

示例：（注意区分等值查询和连接查询的写法）

-- 查询参加了考试的同学信息(学号,学生姓名,科目编号,分数)
SELECT * FROM student;
SELECT * FROM result;

/*思路:
(1)分析需求,确定查询的列来源于两个表,student result,连接查询
(2)确定使用哪种连接查询?(内连接inner join)
(3)交叉点：两个表都有 studentno 字段
(4)判断的条件：r.studentno = s.studentno
*/
SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult`
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno
-- 上面的s.`studentno`也可以写成s.studentno

-- 右连接(也可实现)
SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult`
FROM student s
RIGHT JOIN result r
ON r.studentno = s.studentno

-- 左连接 (查询了所有同学,不考试的也会查出来)
SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult`
FROM student s
LEFT JOIN result r
ON r.studentno = s.studentno

-- 等值连接
SELECT s.studentno,studentname,subjectno,StudentResult
FROM student s , result r
WHERE r.studentno = s.studentno




-- 查一下缺考的同学(左连接应用场景)以左表为基准，即student 表中有的，即使右表（result）中没有，也要输出；
SELECT s.studentno,studentname,subjectno,StudentResult
FROM student s
LEFT JOIN result r
ON r.studentno = s.studentno
WHERE StudentResult IS NULL -- 最后再加一个条件：学生成绩为空，这是在右表中没有的


-- 思考题:查询参加了考试的同学信息(学号,学生姓名,科目名,分数)
/*思路:
(1)分析需求,确定查询的列来源于三个表,student result subject,连接查询
(2)确定使用哪种连接查询?(内连接inner join)
(3)交叉点：这几个表两两之间的共有字段是什么？
(4)判断的条件：r.studentno = s.studentno
*/
SELECT s.studentno,studentname,subjectname,StudentResult
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno
-- 以上找到表student和表result共有的部分
INNER JOIN `subject` sub
ON sub.subjectno = r.subjectno
-- 再将表student和标result共有的部分 和 表subject进行连接查询，找到共有的部分

总结：
1.假如要查询的字段是两个表共有的，那么就要给两个表起个别名，然后用一个表的别名.公有字段，例如下面的**s.studentno** ，两个表都有studentno字段，SELECT 后就要写成s.studentno或者r.studentno，否则会提示ambiguous（模棱两可）；

2.假设存在多张表查询，慢慢来，先查询两张表，再慢慢往后查（类似上面示例中最后的思考题）。

– INNER JOIN的格式和例子：

SELECT 要查询的字段们
FROM 表A 表A的别名
INNER JOIN 表B 表B的别名
ON 交叉条件

SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult`
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno

– 等值连接的格式和例子：

SELECT 要查询的字段们
FROM 表A 表A的别名, 表B 表B的别名
WHERE 交叉条件

SELECT s.studentno,studentname,subjectno,StudentResult
FROM student s , result r
WHERE r.studentno = s.studentno

练习题：

-- 查询学员所属的年级（学号、学生姓名、年级名称）
SELECT `studentno`,`studentname`,`gradename`
FROM `student` s
INNER JOIN `grade` g
ON s.`gradeid` = g.`gradeid`

-- 查询科目所属的年级（科目名称、年级名称）
SELECT `gradename`,`subjectname`
FROM `grade` g
INNER JOIN `subject` s
ON g.`gradeid` = s.`gradeid`

-- 查询参加 高等数学-2 考试的同学信息：学号，学生姓名、科目名、分数
SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM `student` s
INNER JOIN `subject` sub 
ON s.`gradeid` = sub.`gradeid`
INNER JOIN `result` r
ON sub.`subjectno` = r.`subjectno`
WHERE `subjectname` = '高等数学-2'
-- 最后再加一个判断条件：科目名称为 '高等数学-2'

21. 自连接

自连接：自己的表和自己的表连接，核心是一张表拆为两张一样的表，父类和子类。

-- 创建表
-- unsigned 无符号
-- auto_increment=9 自增的起始值
DROP TABLE IF EXISTS `category` ;
CREATE TABLE `category` (
  `category_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主题id',
  `pid` INT(10) NOT NULL COMMENT '父id',
  `category_name` VARCHAR(50) NOT NULL COMMENT '主题名字',
  PRIMARY KEY (`category_id`)
) ENGINE=INNODB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;

-- 插入值
INSERT INTO `category`(`category_id`,`pid`,`category_name`)
VALUES('2','1','信息技术'),
('3','1','软件开发'),
('4','3','数据库'),
('5','1','美术设计'),
('6','3','web开发'),
('7','5','ps技术'),
('8','2','办公信息');

SELECT * FROM `category`;

创建的表如下：

将原来的表分成两个一样的表：
父类：

子类：

操作：查询父类对应的子类关系（父类的categoryid 等于 子类的pid）

等值查询

-- 查询父子信息，把一张表看为两个一模一样的表
SELECT a.`category_name` AS '父栏目',b.`category_name` AS '子栏目'
FROM `category` AS a, `category` AS b
WHERE a.`category_id`=b.`pid`;

结果：

22. 分页limit 和 排序order by

排序 ORDER BY

排序：一般是先查询，然后再排序。
排序的语法：

ORDER BY 通过哪个字段排序 排序规则

其中，排序规则有升序和降序两种，升序是ASC，降序是DESC。

示例：上面练习题的最后一题：（查询参加 高等数学-2 考试的同学信息：学号，学生姓名、科目名、分数）
升序：

降序：

分页LIMIT

为什么要分页？
假如数据有100w条，就有必要分页，除了缓解数据库压力，还可以给人更好的体验。当然也有不分页的，叫做瀑布流，比如抖音、百度上的图片，一直往下拉刷不完。

现在对一组数据分页，每页只显示五条数据：

-- 分页的语法：
LIMIT 起始值，页面的大小

LIMIT 0,5    -- 1~5 从第1条数据开始（下标为0），显示第1-5条信息
LIMIT 1,5    -- 2~6 从第2条数据开始（下标为0），显示第2-6条信息
LIMIT 5,5    -- 6-10 从第6条数据开始（下标为0），显示第6-10条信息

分页涉及到的概念：当前页、总页数，每页的大小pageSize

推导一下：
– 第一页 LIMIT 0,5 (1-1)*5
– 第二页 LIMIT 5,5 (2-1)*5
– 第三页 LIMIT 10,5 (3-1)*5
– 第N页 LIMIT 10,5 (n-1)*pageSize,pageSize

pageSize表示页面大小；(n-1)*pageSize表示起始值；n表示当前页；总页数 = (数据总数%页面大小==0)? (数据总数/页面大小) : (数据总数/页面大小 + 1)。

示例：

** 上面练习题的最后一题：（查询参加 高等数学-2 考试的同学信息：学号，学生姓名、科目名、分数）
SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM `student` s
INNER JOIN `subject` sub 
ON s.`gradeid` = sub.`gradeid`
INNER JOIN `result` r
ON sub.`subjectno` = r.`subjectno`
WHERE `subjectname` = '高等数学-2' -- 最后再加一个判断条件：科目名称为 '高等数学-2'
ORDER BY `student_result` DESC -- 降序排序
LIMIT 0,5; -- 分页，每页显示五条信息，

思考题：

-- 思考题：
-- 查询科目高等数学-2，课程成绩排名前十的学生，并且分数要大于60的学生信息（学号，姓名，课程名称，分数）
SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
-- 这样写也可以： SELECT s.`student_no`,s.`student_name`,sub.`subject_name`,res.`student_result`
FROM `student` s
INNER JOIN `subject` sub
ON sub.`gradeid` = s.`gradeid`
INNER JOIN `result` r
ON r.`studentno` = s.`studentno`
-- where条件：
WHERE `subjectname` = '高等数学-2' AND `studentresult` > 60
-- 排序：
ORDER BY `studentresult` DESC
-- 这样写也可以： ORDER BY res.`student_result`
-- 分页：因为是前10名，所以就把每页的大小设置为10，这样就可以显示出前10名
LIMIT 0,10
-- 这样写也可以： LIMIT 0,10;
-- 语法： limit (查询起始下标,页面大小)

如果有多个筛选条件，就在WHERE后面加个AND，不要用逗号,

23. 子查询 和 嵌套查询

之前用where的时候，后面是一个确定的值或者范围，例如上面的思考题中的

-- where条件：
WHERE `subjectname` = '高等数学-2' AND `studentresult` > 60

其实，在where后面还可以跟一个子查询语句，这就是子查询。

子查询的本质就是在where语句中嵌套一个子查询语句；
嵌套查询可由多个子查询组成,求解的方式是由里及外；
子查询返回的结果一般都是集合,故而建议使用IN关键字。

说到底，
子查询和嵌套查询就是不用像连接查询那样连接多个表，
而是把某个表的查询工作放到一个WHERE条件中（示例1的方法二和示例2的方法二），
或者把所个表的查询工作放到一个IN中（示例2的方法三）。

示例1：

-- 查询 '高等数学-2' 的所有考试结果(学号,科目编号,成绩),并且成绩降序排列
-- 方法一:使用连接查询
SELECT r.`studentno`,r.`subjectno`,r.`studentresult`
FROM `result` r
INNER JOIN `subject` sub
ON sub.`subjectno` = r.`subjectno`
WHERE `subjectname` = '高等数学-2'
ORDER BY `studentresult` DESC

-- 方法二:使用子查询(一层嵌套)
SELECT `studentno`,`subjectno`,`studentresult`
FROM `result`
WHERE `subjectno` = (
	SELECT `subjectno` FROM `subject`
	WHERE `subjectname` = '高等数学-2'
)
ORDER BY `studentresult` DESC

结果：

示例2：

-- 查询课程为 高等数学-2 且分数不小于80分的学生的学号和姓名
-- 方法一:使用连接查询
SELECT DISTINCT s.`studentno`,`studentname`
FROM `student` s
INNER JOIN `result` r
ON r.`studentno` = s.`studentno`
INNER JOIN `subject` sub
ON sub.`subjectno` = r.`subjectno`
WHERE `studentresult` >= 50 AND `subjectname` = '高等数学-2'

-- 方法二:使用连接查询+子查询(一层嵌套)
SELECT DISTINCT s.`studentno`,`studentname`
FROM `student` s
INNER JOIN `result` r
ON r.`studentno` = s.`studentno`
WHERE `studentresult` >= 50 
AND `subjectno` = (
	SELECT `subjectno` FROM `subject`
	WHERE `subjectname` = '高等数学-2'
)

-- 方法三:使用子查询（两层嵌套）
SELECT DISTINCT `studentno`,`studentname`
FROM `student`
WHERE `studentno` IN (
	SELECT `studentno` FROM `result`
	WHERE `studentresult` >= 50 
	AND `subjectno` = (
		SELECT `subjectno` FROM `subject`
		WHERE `subjectname` = '高等数学-2'
		)
)

结果：

练习题目：
查 C语言-1 的前5名学生的成绩信息(学号,姓名,分数)

-- 练习题：查 C语言-1 的前5名学生的成绩信息(学号,姓名,分数)
-- 方式一：连接查询
SELECT s.`studentno`,`studentname`,`studentresult`
FROM `student` s
INNER JOIN `result` r
ON r.`studentno` = s.`studentno`
INNER JOIN `subject` sub
ON sub.`subjectno` = r.`subjectno`
WHERE `subjectname` = 'C语言-1'
ORDER BY `studentresult` DESC
LIMIT 0,5;

-- 方式二：连接查询 + 子查询（一层嵌套）
SELECT s.`studentno`,`studentname`,`studentresult`
FROM `student` s
INNER JOIN `result` r
ON r.`studentno` = s.`studentno`
WHERE `subjectno` = (
	SELECT `subjectno` FROM `subject`
	WHERE `subjectname` = 'C语言-1'
)
ORDER BY `studentresult` DESC
LIMIT 0,5;

-- 方式三：子查询（两层嵌套）
-- 这个写不出来，要查三个字段，而这三个字段来自两个表，最少要用一次连表查询
SELECT `studentno`,`studentname`,`studentresult`
FROM `result`
WHERE `studentno`

总结：

1. 看个人喜好，习惯用纯连接查询（方式一）也行，习惯用连接查询+子查询（方式二）也可以，也可以只用子查询（方式三）：
   方式一就是直接把所有表全连接到一起，比较简单，但要注意避免出现ambiguous，即必须明确要查询的字段确保不出现歧义；
   方式二就是把个别表的查询放入where中，相对更适合自己；
   方式三的话感觉要求更高，像上面的练习题，要查询来自两个表的三个字段，用方式三好像实现不了（不确定，可能是自己太菜）。
2. 用where的子查询效率比用join的连接查询效率要更高一些，因为不需要连表；
3. 个人推荐用方式二，哈哈，具体看自己的习惯。

五、MySQL函数

24. 常用函数（不常用）

MySQL 2.7 参考手册：点这里，官方手册，可以翻译成中文。

数据函数：

 SELECT ABS(-8);  /*绝对值*/
 SELECT CEILING(9.4); /*向上取整*/
 SELECT FLOOR(9.4);   /*向下取整*/
 SELECT RAND();  /*随机数,返回一个0-1之间的随机数*/
 SELECT SIGN(0); /*符号函数: 负数返回-1,正数返回1,0返回0*/

字符串函数：

 SELECT CHAR_LENGTH('狂神说坚持就能成功'); /*返回字符串包含的字符数*/
 SELECT CONCAT('我','爱','程序');  /*合并字符串,参数可以有多个*/
 SELECT INSERT('我爱编程helloworld',1,2,'超级热爱');  /*替换字符串,从某个位置开始替换某个长度*/
 SELECT LOWER('KuangShen'); /*小写*/
 SELECT UPPER('KuangShen'); /*大写*/
 SELECT LEFT('hello,world',5);   /*从左边截取*/
 SELECT RIGHT('hello,world',5);  /*从右边截取*/
 SELECT REPLACE('狂神说坚持就能成功','坚持','努力');  /*替换字符串*/
 SELECT SUBSTR('狂神说坚持就能成功',4,6); /*截取字符串,开始和长度*/
 SELECT REVERSE('狂神说坚持就能成功'); /*反转
 
 -- 查询带 伟 的同学,改成 尾
SELECT REPLACE(`studentname`,'伟','尾') AS '新名字'
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 VERSION();  /*版本*/
 SELECT USER();     /*用户*/

25. 聚合函数（常用）

MySQL 2.7 参考手册：点这里，官方手册，可以翻译成中文。

COUNT()函数有以下几种用法：

SELECT COUNT(`studentname`) FROM `student` -- 4
SELECT COUNT(`email`) FROM `student` -- 4
SELECT COUNT(*) FROM `student` -- 4
SELECT COUNT(1) FROM `student` -- 4

• count(字段) 统计该字段在表中出现的次数，不会统计字段为null 的记录；
• count(*) 会统计字段为null 的记录；
• count(1) 会统计字段为null 的记录 ；
• 从含义上讲，count(1) 与 count(*) 都表示对全部数据行的查询。

下面它们之间的一些对比：
1）在表没有主键时，count(1)比count(*)快
2）有主键时，主键作为计算条件，count(主键)效率最高；
3）若表格只有一个字段，则count(*)效率较高。

其他几个函数：
计算 result表 中的 studentresult字段 的总和、平均分、最高分、最低分：

SELECT SUM(`studentresult`) AS '总和' FROM `result`
SELECT AVG(`studentresult`) AS '平均分' FROM `result`
SELECT MAX(`studentresult`) AS '最高分' FROM `result`
SELECT MIN(`studentresult`) AS '最低分' FROM `result`

分组和过滤

上面知道怎么怎么求 result表 中的 studentresult字段 的平均分、最高分、最低分，但这是所有科目的成绩加在一起之后算出来的，现在要求各个科目的平均分、最高分、最低分，就要用到分组和过滤了。

 -- 查询不同课程的平均分,最高分,最低分
 -- 前提:根据不同的课程进行分组
SELECT 
`subjectname` AS '课程名称',
SUM(`studentresult`) AS '总分',
AVG(`studentresult`) AS '平均分', 
MAX(`studentresult`) AS '最高分', 
MIN(`studentresult`) AS '最低分'
FROM `result` r
INNER JOIN `subject` sub
ON sub.`subjectno` = r.`subjectno`
-- GROUP BY r.`subjectno` -- 通过课程编号来分组
GROUP BY `subjectname` -- 通过课程名称来分组
-- HAVING AVG(`studentresult`) > 50 -- 这样写不会出错
HAVING 平均分 > 50 -- 也可以这样写，但下面的方式就不行
-- HAVING '平均分' > 50 -- 不知道为什么加了单引号结果就不对，所以最保险的就是用第一种方式写

分组和筛选的格式：

WHERE 字段 -- 指定结果需满足的条件
GROUP BY 字段  -- 指定结果按照哪几个字段来分组
HAVING 次要筛选条件 -- 过滤分组的记录必须满足的次要条件

注意：
1.筛选条件where必须写在group by前面；
2.要是在分组后面还要加筛选条件，就要使用HAVING…
因为having是从前面筛选的字段再筛选，而where是从数据表中的字段直接进行的筛选的。

26.拓展之 数据库级别的MD5加密

1.MD5简介

什么是MD5?
MD5即Message-Digest Algorithm 5（信息-摘要算法5），用于确保信息传输完整一致，是计算机广泛使用的杂凑算法之一（又译摘要算法、哈希算法），主流编程语言普遍已有MD5实现。将数据（如汉字）运算为另一固定长度值，是杂凑算法的基础原理，MD5的前身有MD2、MD3和MD4。

MD5的作用：主要增加算法复杂度和不可逆性。

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`(`id`,`name`,`pwd`)
VALUES
(1,'小王','123456'),
(2,'小张','123456'),
(3,'小刘','123456'),
(5,'小赵','123456')

SELECT * FROM `testmd5`

-- 给单条数据（`id` = 1）加密：
UPDATE `testmd5` SET `pwd`=MD5(`pwd`) WHERE `id` = 1
-- 给所有数据加密：
UPDATE `testmd5` SET `pwd`=MD5(`pwd`)

-- 插入数据的同时也完成加密：
INSERT INTO `testmd5`(`id`,`name`,`pwd`)
VALUES(10,'小李',MD5('123456'))

-- 如何校验：将用户传递进来的密码进行md5加密，然后对比加密后的值
SELECT * FROM `testmd5` WHERE `pwd` = MD5('123456')
SELECT * FROM `testmd5` WHERE `pwd` = MD5('123456') AND `name`='小张'

注意：id = 1的数据被加密了两次，所以它的pwd字段和其他的不一样

课件截图：

27. SELECT小结

对应第四章刚开始的SELECT语法。

第四章主要讲了DQL（Database Query Language）数据库查询语言，关键字就是SELECT，后面跟了很多个可选项，总结下来就是：

• 16 &17：SELECT [去重-DISTINCT] 要查询的字段 FROM 数据表A （表和字段可用AS取别名）
• 20：xxx(INNER) JOIN 要连接的表B ON 交叉条件 – 连接查询
• 18 & 19 & 23：WHERE 条件 – 筛选条件（如果有多个筛选条件，就在后面加AND关键字，不要加逗号,）模糊查询（关键字LIKE和关键字IN）> 或者子查询语句）
• 25：[GROUP BY …] – 通过哪个字段来分组
• 25：[HAVING] – 过滤分组后的信息，条件和where是一样的，区别就是先后位置不同
• 22：ORDER BY – （通过哪个字段排序）[升序ASC / 降序DESC]
• 22：LIMIT startIndex, pageSize – 分页

上面的可选部分顺序很重要；
HAVING 和 WHERE的区别就在于先后位置不同。

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

内置函数小结：

只试了第一部分：数值函数，剩下的没试；
此外还有**24.常用函数（不常用） 和 25. 聚合函数（常用）**也是MySQL函数。

 -- ================ 内置函数 ================
 -- 数值函数
SELECT ABS(-10.9) -- 10.9 
SELECT FORMAT(12.3,6) -- 格式化小数点后的位数：12.300000
SELECT CEIL(0.01) -- 向上取整：1
SELECT FLOOR(1.009) -- 向下取整：1
SELECT ROUND(4.4) -- 四舍五入：4
SELECT ROUND(4.5) -- 四舍五入：5
SELECT MOD(10,3) -- 取模：1
SELECT PI() -- 3.141593
SELECT POW(2,3) -- 指数：8
SELECT SQRT(8) -- 算术平方根：2.8284271247461903
SELECT RAND() -- 随机数：0.43626770883451005
SELECT TRUNCATE(123.456789,3) -- 截取小数点后3位：123.456
 
 -- 时间日期函数
 now(), current_timestamp();     -- 当前日期时间
 current_date();                    -- 当前日期
 current_time();                    -- 当前时间
 date('yyyy-mm-dd hh:ii:ss');    -- 获取日期部分
 time('yyyy-mm-dd hh:ii:ss');    -- 获取时间部分
 date_format('yyyy-mm-dd hh:ii:ss', '%d %y %a %d %m %b %j');    -- 格式化时间
 unix_timestamp();                -- 获得unix时间戳
 from_unixtime();                -- 从时间戳获得时间
 
 -- 字符串函数
 length(string)            -- string长度，字节
 char_length(string)        -- string的字符个数
 substring(str, position [,length])        -- 从str的position开始,取length个字符
 replace(str ,search_str ,replace_str)    -- 在str中用replace_str替换search_str
 instr(string ,substring)    -- 返回substring首次在string中出现的位置
 concat(string [,...])    -- 连接字串
 charset(str)            -- 返回字串字符集
 lcase(string)            -- 转换成小写
 left(string, length)    -- 从string2中的左边起取length个字符
 load_file(file_name)    -- 从文件读取内容
 locate(substring, string [,start_position])    -- 同instr,但可指定开始位置
 lpad(string, length, pad)    -- 重复用pad加在string开头,直到字串长度为length
 ltrim(string)            -- 去除前端空格
 repeat(string, count)    -- 重复count次
 rpad(string, length, pad)    --在str后用pad补充,直到长度为length
 rtrim(string)            -- 去除后端空格
 strcmp(string1 ,string2)    -- 逐字符比较两字串大小
 
 -- 聚合函数
 count()
 sum();
 max();
 min();
 avg();
 group_concat()
 
 -- 其他常用函数
 md5();
 default();

六、事务

28.事务ACID原则、脏读、不可重复读、幻读

第六章的内容见：数据库学习笔记2