一、初识MySQL

javaEE：企业级java开发，主要是做Web。
Web分为前端（页面：展示数据库数据）、后台（连接点：连接数据库JDBC，连接前端（控制视图跳转、给前端传递数据））、数据库（存数据）。
必须要学操作系统、数据结构与算法。学好离散数学、数字电路、体系结构、编译原理就更好了，再有实战经验就是优秀的程序猿了。

1.为什么要学习数据库

①岗位需求
②现在的世界是大数据时代，得数据者得天下。
③被迫需求：存数据。
④数据库是所有软件体系中最核心得存在。相应岗位：DBA数据库管理员。

2.什么是数据库

数据库（DB，DateBase）
概念：数据仓库，是个软件，安装在操作系统（linux，windows，mac···）之上。用SQL语句执行，可以储存大量的数据。500万条以下的数据可以直接存储，500万条以上需要进行索引的优化。
作用：存储数据、管理数据。

3.数据库分类

1）关系型数据库：SQL
主要有MySQL、Oracle、Sql Server、DB2、SQLlite
通过表和表之间，行和列之间的关系进行数据存储。
2）非关系型数据库：NoSQL（Not Only）
主要有：Redis，MongoDB
以对象存储，通过对象的自身属性来决定。
3）DBMS（数据库管理系统）
数据库的管理软件，科学有效的管理数据，维护和获取数据。

image

MySQL，数据库管理系统！

4.MySQL简介

MySQL是一个关系型数据库管理系统。
前身：属于瑞典的MySQL AB公司，如今：属于Oracle旗下产品。
MySQL是最好的RDBMS（Relational Database Management System，关系型数据库管理系统）应用软件之一。
是开源的数据库软件，具有体积小，速度快，总体拥有成本低的特点。
适用于中小型或大型网站，可以做集群。
官网：https://www.mysql.com 版本：5.7（稳定）、8.0
官网下载地址：https://dev.mysql.com/downloads/mysql/
安装建议：Ⅰ尽量不要使用.exe安装，会往注册表里走，不好卸载
Ⅱ尽可能使用压缩包安装

5.安装MySQL

MySQL 5.7下载地址：https://dev.mysql.com/downloads/mysql/5.7.html
1）操作步骤：
①下载后得到zip压缩包。
②解压到要安装的目录，本人解压到的是D:\MySQL\mysql-5.7.32。

image

③配置环境变量：Ⅰ右击我的电脑——>属性——>高级系统设置——>环境变量

image

image

Ⅱ选择PATH——>新建——>添加mysql安装文件下面的bin文件夹地址

image

image

④在D:\MySQL\mysql-5.7.32下新建并编辑文件my.ini文件，注意文件路径。

image

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

image

⑤启动管理员模式下的CMD，并将路径切换至mysql下的bin目录，然后输入

mysqld -install

（安装mysql）。
⑥再输入

mysqld --initialize-insecure --user=mysql

初始化数据文件。

image

⑦然后输入

net start mysql

启动mysql，并用命令

mysql -u root -p

进入mysql的管理界面（密码可为空）

image

⑧进入界面后修改root密码，密码改为123456。

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

⑨最后输入

flush privileges;

刷新权限。

image

⑩修改my.ini文件，删除最后一句skip-grant-tables（可在前面添加#注释掉）。

image

⑪重启mysql即可正常使用，分别输入

exit
net stop mysql
net start mysql

image

⑫进入管理界面，输入密码，出现以下结果就安装好了。

image

2）安装时可能遇到的问题
①缺少组件.dill
②命令可能输入错误。
③mysql命令行与windows命令行弄混。
④net命令输入不存在，环境变量PATH中与系统盘相关的可能被删除。
安装失败，可以输入

sc delete mysql

清空服务，重新安装。

6.安装SQLyog

1）简单安装，将语言设置为Chinese(Simplified)。

image

image

image

image

image

2）打开，新建联系，名称为localhost，连接数据库。

image

image

image

3）右击root@localhost，点击刷新对象浏览器。
4）右击root@localhost，创建一个数据库school，设置基字符集为utf8，数据库排序规则为utf8_general_ci。

image

注：每一个sqlyog的执行操作，本质就对应了一个sql，可以再软件的历史记录中查看。
5）新建一张表student，右击表，选择创建表，设置表名称为student，引擎为InnoDB，字符集为utf8，核对为utf8_general_ci。

image

6）填写三个列名id，name，age，并填写后面内容，最后点击保存。

image

主键：唯一标识，不能重复。
非空：必须要有值，否则就报错。
Zerofill：零填充。
7）查看表：右击student，选择打开表。

image

8）尝试添加多条记录，刷新即可保存。

image

7.连接数据库

1）命令行连接

mysql -u root -p123456  --连接数据库
update mysql.user set authentication_string=password('123456') where user='root' and Host = 'localhost';  ---修改用户密码
flush privileges;  --刷新权限
show databases;  --查看所有数据库
use school  --切换数据库：use 数据库名  显示Database changed即切换成功
show tables;  --查看数据库中的所有表
describe student;  --显示数据库中表的信息：describe 表名
create database westos;  --创建一个数据库westos
exit;  --退出连接

image

image

--：单行注释 /* */：多行注释
2）数据库的语言 核心：CRUD增删改查
DDL定义，DML操作，DQL查询，DCL控制

二、操作数据库

操作数据库——>操作数据库中的表——>操作数据库中表的数据
注：所有语句都要用;结尾。

1.操作数据库

例：创建数据库westos并使用。
注：以后所有命令带[]表示可选，带{}表示必选。
1）创建数据库

CREATE DATABASE [IF NOT EXISTS] westos;

2）删除数据库

DROP DATABASE [IF EXISTS] westos;

3）使用数据库

USE `westos`
--tab键的上面，如果表名或者字段名是一个特殊字符

4）查看数据库

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

对比SQLyog的可视化操作：

image

学习思路：
①对照sqlyog可视化历史记录查看sql语句。
②固定的语法或关键字必须强行记住。

2.数据库的列类型

1）数值
①tinyint 十分小的数据 1个字符
②smallint 较小的数据 2个字节
③mediumint 中等大小的数据 3个字节
④int 标准的整数 4个字节 常用的int
⑤bigint 较大的数据 8个字节
⑥float 浮点数 4个字节
⑦double 浮点数 8个字节
⑧decimal 字符串形式的浮点数 在金融计算的时候使用
2）字符串
①char 固定大小的字符串 0-255
②varchar 可变的字符串 0-65535 常用的String
③tinytext 微型文本 2的8次方-1
④text 文本串 2的16次方-1 保存大文本
3）时间日期 对应Java中的java.util.Date
①date 日期格式：YYYY-MM-DD
②time 时间格式：HH:mm:ss
③datetime 最常用的时间格式：YYYY-MM-DD HH:mm:ss
④timestamp 时间戳 较为常用
⑤year 年份表示
4）null：没有值，未知。
注：不要使用NULL进行运算，没有意义，结果仍为NULL。

3.数据库的字段属性

1）Unsigned：无符号的整数，声明了该列不能为负数。
2）zerofill：0填充的。不足的位数，使用0来填充。
例：int(3)，5——>005
3）自增：通常理解为自增，自动在上一条记录的基础上+1（默认）。
通常用来设置唯一的主键index，必须是整数类型。
可以自定义设置主键自增的起始值和步长。

image

4）非空：假设设置为not null，如果不给它赋值就会报错。
NULL，如果不填写值，默认就是null。
5）默认：设置默认的值。如果不指定该列的值，则会有默认的值。
6）拓展：每一个表都必须在以下五个字段。未来做项目用的，表示一个记录存在意义。
①id 主键
②version 乐观锁
③is_delete 伪删除
④gmt_create 创建时间
⑤gmt_update 修改时间

4.创建数据表

目标：创建一个school数据库。
创建学生表（列，字段），使用SQL语句创建。
学号int，登录密码varchar(20)，姓名，性别varchar(2)，出生日期（datetime），家庭住址，邮箱（email）。

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

注意点：Ⅰ使用英文()，表的名称和字段尽量使用``括起来。
ⅡAUTO_INCREMENT 自增。
Ⅲ字符串的使用：用单引号''括起来。
Ⅳ所有的语句后面加英文的,，最后一个不用加。
ⅤPRIMARY KEY主键，一般一个表只有唯一的主键。
格式：

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

常用命令：
例：查看创建数据库和表的语句并显示。

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

5.数据库类型

关于数据库引擎：INNODB 默认使用、MYISAM 早些年使用的。

常规使用操作：
MYISAM：节约空间，速度较快。
INNODB：安全性高，事务的处理，多表多用户操作。
在物理空间存在的位置：
所有的数据库文件都存在data目录下，一个文件就对应一个数据库。
本质还是文件的存储！
MySQL引擎在物理文件上的区别：
InnoDB：在数据表中只有一个.frm文件，以及上级目录下的ibdata1文件。
MYISAM对应文件：
①.frm 表结构的定义文件
②.MYD 数据文件（data）
③.MYI 索引文件（index）
设置数据库的字符集编码：

CHARSET=utf8

MySQL的默认编码是Latinl，不支持中文。
不设置成utf8的话，会是mysql默认的字符集编码。
在my.ini中配置默认的编码：
charcicter-set-server=utf8

6.修改删除表

1）修改
例：

--修改表名：ALTER TABLE 旧表名 RENAME AS 新表名
ALTER TABLE teacher RENAME AS teacher1
--增加表的字段：ALTER TABLE 表名 ADD 字段名 列属性
ALTER TABLE teacher1 ADD age INT(11)
--修改表的字段（重命名、修改约束）
--修改约束：ALTER TABLE 表名 字段名 列属性
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

2）删除
例：

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

注：所有的创建和删除操作尽量加上判断，以免报错。
注意点：Ⅰ字段名使用``包裹。
Ⅱ注释用--或/ * * /，不要用#。
Ⅲsql关键字大小写不敏感，建议写小写。
Ⅳ所有的符号全部用英文。

三、MySQL数据管理

1.外键

例：学生的grade列引用年级表的id（约束）。

image

方式一：在创建表的时候，增加约束（比较复杂）。

CREATE TABLE `grade` (
  `gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年级id',
  `gradename` VARCHAR(50) NOT NULL COMMENT '年级名称',
  PRIMARY KEY (`gradeid`)
)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 '姓名',
  `gradeid` INT(10) NOT NULL COMMENT '年级',
  PRIMARY KEY (`id`),
  --定义外键key
  KEY `FK_gradeid` (`gradeid`),
  --给此外键添加约束（执行引用），用references引用
  CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade`(`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

image

注：删除有外键关系的表的时候，必须要先删除引用别人的表（从表），再删除被引用的表（主表）。
可视化里查看外键：右击表——>改变表——>选择3个外部键

image

方式二：创建表成功后，添加外键约束。

--创建表的时候没有外键关系
--ALTER TABLE 表` ADD CONSTRAINT 约束名 FOREIGN KEY(作为外键的列) REFERENCES 被引用的表(其字段);
ALTER TABLE `student` ADD CONSTRAINT `FK_gradeid` FOREIGN KEY(`gradeid`) REFERENCES `grade`(`gradeid`);

image

以上的操作都是物理外键，数据库级别的外键，不建议使用。（避免数据库过多造成困扰）
最佳实践：
①数据库就是单纯的表，只用来存数据，只有行（数据）和列（字段）。
②想使用多张表的数据，想使用外键（用程序去实现）。

2.DML语言

数据库的意义：数据存储、数据管理。
DML语言：数据操作语言
Insert、update、delete

3.添加（insert）

语法：

insert into 表名([字段名1,字段名2,…]) values[('值1',…),('值2',…),……]

例：

INSERT INTO `student`(`name`,`pwd`,`sex`)
VALUES('张三','aaaa','男'),('李四','bbbb','女')

注：由于主键自增可以省略。（但如果不写表的字段，它会一一匹配。）
一般写插入语句，一定要使数据和字段一一对应。
注意事项：
①字段和字段直接使用英文逗号,隔开。
②字段是可以省略的，但后面的值必须要是一一对应的，不能缺少。
③可以同时插入多条数据，VALUS后面的值，使用,隔开。

4.修改（update）

语法：

UPDATE 表名 set colnum_name = value,[colnum_name = value,…] where [条件]

例：

UPDATE `student` SET `name`='王五',`email`='2585801995@qq.com' WHERE id = 1;

注：①不指定条件的情况下，会改动整个表。
②修改多个属性用逗号,隔开。
条件：where 子句（等于某个值，小于或大于某个值，在某个区间内修改…）
操作符：返回布尔值

例：

--通过多个条件定位数据
UPDATE `student` SET name='长江' WHERE `name`='张三' AND `sex`='男'

注意：①column_name是数据库的列，尽量带上``。
②作为筛选的条件吗，如果没有指定，则会修改所有的列。
③value是一个具体的值，也可以是一个变量。
例：

UPDATE `student` SET `birthday`=CUPPENT_TIME WHERE `name`='张三' AND `sex`='男'

④多个设置的属性之间，使用英文逗号,隔开。

5.删除（delete、truncate）

1）delete命令
语法：

delete from 表名 [where条件]

例：

DELETE FROM `student` WHERE id=1;

注：若无指定的条件，会全部删除，避免这样写。
2）truncate命令
作用：完全清空一个数据表，表的结构和表的索引不会变。
例：

--清空student表
TRUNCATE `student`
TRUNCATE TABLE `student`

2）delete和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('1'),('2'),('3')
DELETE FROM `test` --不会影响自增
TRUNCATE TEBLE `test` --自增会归零

DELETE删除的问题：
InnoDB：自增列会重新从1开始（存在内存当中的，断电即失）。
MyISAM：继续从上一个自增量继续（存在文件中的，不会丢失）。

四、DQL查询数据

1.DQL（Data Query Language）：数据查询语言

所有的查询操作都要用它 Select。
简单的查询，复杂的查询它都能做。
是数据库中最核心的语言，最重要的语句；
也是使用频率最高的语句。
school测试数据库及grade、result、student、subject表的创建及添加数据的代码，可下载使用：https://share.weiyun.com/4QiyVQfd，也可以直接复制下方。

-- 创建一个school数据库
create database if not exists `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);

2.指定查询字段

1）语法：

SELECT 字段1,字段2,… FROM 表

例：

--查询学术表的全部信息
SELECT * FROM student
--查询表中的指定字段
SELECT `StudentNo`,`StudentName` FROM student
--用AS给结果取别名
SELECT `StudentNo`AS 学号,`StudentName` AS 学生姓名 FROM student AS 学生表
--运用函数 Concat('姓名',Student) AS 新名字 FROM student

有的时候，列名字不是那么见名知意，可以用AS起别名：字段名/表名 as 别名。
2）去重：distinct
作用：去除SELECT查询出来的结果中重复的数据，重复的数据只显示一条。
例：查询参加考试的同学及其成绩。

SELECT * FROM result --查询全部的考试成绩
SELECT `StudentNo` FROM result --查询参加考试的同学
SELECT DISTINCT `StudentNo` FROM result --查询参加考试的同学，去除重复的数据

3）数据库的列（表达式）
例：

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

数据库中的表达式：文本值、列、NULL、函数、计算表达式、系统变量······
语法：

select 表达式 form 表

3.where 条件子句

作用：检索数据中符合条件的值。
搜索的条件由一个或多个表达式组成，结果为布尔值。
1）逻辑运算符

注：or也可以用两个|来表示，尽量使用英文字母而不是符号。
例：

--查询考试成绩在95-100之间的学生学号
SELECT StudentNo,`StudentResult` FORM result
WHERE StudentResult>=95 AND StudentResult<=100
--模糊查询（区间）
SELECT StudentNo,`StudentResult` FORM result
WHERE StudentResult BETWEEN 95 AND 100
--除了1000号学生之外的同学成绩
SELECT StudentNo,`StudentResult` FORM result
WHERE NOT StudentNo=1000

2）模糊查询：比较运算符

例：

--查询姓刘的同学
--与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 StudentName IN (1001,1002,1003);
--查询地址为空的同学
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE address=' ' OR address IS NULL
--查询没有出生日期的同学
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE `BornDate` IS NOT NULL

4.联表查询

1）JOIN对比

image

百度搜索七种join理论

image

例：查询参加了考试的同学（学号、姓名、科目编号、分数）。
思路：Ⅰ分析需求，分析查询的字段来自哪些表（连接查询）。
Ⅱ确定使用哪种查询。共7种
确定交叉点（student和result这两个表中哪个数据是相同的）。
判断的条件：学生表中的StudentNo = 成绩表中的StudentNo。

--Inner Join
SELECT s.StudentNo,StudentName,SubjectNo,StudentResult
FORM student s
INNER JOIN result r
ON s.StudentNo = r.StudentNo
--Right Join
SELECT s.StudentNo,StudentName,SubjectNo,StudentResult
FORM student s
RIGHT JOIN result r
ON s.StudentNo = r.StudentNo
--Left Join
SELECT s.StudentNo,StudentName,SubjectNo,StudentResult
FORM student s
LEFT JOIN result r
ON s.StudentNo = r.StudentNo

注：有时使用AS可省略。

例：查询缺考的同学。

SELECT s.StudentNo,StudentName,SubjectNo,StudentResult
FORM student s
LEFT JOIN result r
ON s.StudentNo = r.StudentNo
WHERE StudentResult IS NULL

on与where：
join (连接的表) on (判断的条件)：连接查询
where：等值查询

思考题：查询了参加考试的同学信息（学号、学生姓名、科目名、分数）。

SELECT s.StudentNo,StudentName,SubjectName,`StudentResult`
FROM student
RIGHT JOIN result r
ON r.SttudentNo = s.StudentNo
INNER JOIN `subject` sub
ON r.SubjectNo = sub.SubjectNo

总结：Ⅰ要查询哪些数据。select…
Ⅱ从哪几个表中查。FROM 表 XXX Join 连接的表 on 交叉条件
Ⅲ假设存在一种多张表查询，先查询两张表然后再慢慢增加。
2）自连接
school数据库中的分类表category的创建和导入数据的代码，可下载使用：https://share.weiyun.com/Wp0LerVu，也可以直接复制下方。

CREATE TABLE `category` ( 
  `categoryId` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主题id', 
  `pid`  INT(10) NOT NULL COMMENT  '父id', 
  `categoryName`  VARCHAR(50) NOT NULL COMMENT '主题名字', 
  PRIMARY KEY (`categoryId`) 
) ENGINE=INNODB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8; 

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

image

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

子表：

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

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

结果表的样式：

3）联表查询练习
①查询科目所属的年级（科目名称、年级名称）。

SELECT `SubjectName`,`GradeName`
FROM `subject` sub
INNER JOIN `grade` g
ON sub.`GradeId` = g.`GradeId`

②查询学员所属的年级（学号、学生姓名、年级名称）。

SELECT StudentNo,StudentName,`GradeName`
FROM student s
INNER JOIN `grade` g
ON s.`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'

5.分页和排序

1）排序
方式：升序ASC，降序DESC。
语法：

ORDER BY 通过哪个字段排序,排序方式

例：以上例思考题改为基础，将查询的信息按成绩排序。

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

1）分页
分页的原因：缓解数据库的压力，给用户的体验更好。用瀑布流的话可以不分页。
语法：

limit 起始值,页面的大小

例：以上例思考题改为基础，页面大小为5，显示第一页的内容。

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,5

网页应用显示：当前页,总的页数,页面的大小
公式：ⅠpageSize：页面大小
Ⅱ(n-1)*pageSize：初始值
Ⅲn：当前页 = 数据总数/页面大小

思考题：查询JAVA第一学年课程成绩排名前十，并且分数要大于80的学生信息（学号、姓名、课程名称、分数）。

SELECT s.`StudentNo`,`StudentName`,`SubjectName`,`StudentResult`
FROM student s
INNER JOIN `result` r
ON s.StudentNo = r.StudentNo
INNER JOIN `subject` sub
ON sub.`SubjectNo` = r.`SubjectNo`
WHERE SubjectName = 'JAVA第一学年' AND StudentResult>=80
ORDER BY StudentResult DESC
LIMIT 0,10

6.子查询

where（这个值是计算出来的）
本质：在where语句中嵌套一个子查询语句。
例：查询数据库结构-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

方式二：使用子查询（由里及外）

SELECT `StudentNo`,r.`SubjectNo`,`StudentResult`
FROM `result` 
WHERE SubjectNo = (
  SELECT SubjectNo FROM `subject`
  WHERE SubjectName = '数据库结构-1'
)
ORDER BY StudentResult DESC

例：查询课程为高等数学-2且分数不小于80的同学的学号和姓名。
方式一：

SELECT s.StudentNo,StudentName
FORM student s
INNER JOIN result r
ON s.StuentNo = r.StuentNo
INNER JOIN `subject` sub
ON r.`SubjectNo` = sub.`SubjectNo`
WHERE `StudentName` = '高等数学-2' AND StudentResult>=80

方式二：

SELECT s.`StudentNo`,`StudentName`
FORM student s
INNER JOIN result r
ON r.StuentNo = s.StuentNo
WHERE `StudentResult`>=80 AND `SubjectNo` = (
  SELECT SubjectNo FROM `subject`
  WHERE `SubjectName` = '高等数学-2'                                                                                                                                                                    
)
--进一步改变
SELECT s.StudentNo,StudentName FORM student WHERE StudentNo IN (
  SELECT StudentNo FROM result WHERE StudentResult>=80 AND SubjectNo = （
    SELECT SubjectNo FROM `subject` WHERE `SubjectName` = '高等数学-2'
  ）
)

练习：查询C语言-1课程中前5名同学的成绩信息（学号、姓名、分数）。
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 {[offest.] row_count | row_countOFFEST offset}]
  --指定查询的记录从哪条至哪条

注：[]括号代表可选的,{}括号代表必选的。

五、MySQL函数

MySQL 5.7官方参考手册：https://dev.mysql.com/doc/refman/5.7/en/
SQL函数和运算符操作：https://dev.mysql.com/doc/refman/5.7/en/sql-function-reference.html

1.常用函数（不常用）

1）数学运算

SELECT ABS() --绝对值
SELECT CEILING() --向上取整
SELECT FLOOR() --向下取整
SELECT RAND() --返回一个0-1之间的随机数
SELECT SIGN() --判断一个数的符号：0返回0，负数返回-1，正数返回1

2）字符串函数

SELECT GHAR_LENGTH(str) --字符串长度
SELECT CONCAT(str,str,…) --拼接字符串
SELECT INSERT(str,初始位置,替换长度,替代的str) --从某个位置开始替换某个长度
SELECT LOWER(str) --转化成小写字母
SELECT UPPER(str) --转化成大写字母
SELECT INSTR(str,子str) --返回第一次出现的子字符串的索引
SELECT PEPLACE(str,出现的str,替换的str) --替换出现的指定字符串
SELECT SUBSTR(str,截取位置，截取长度) --返回指定的子字符串
SELECT REVERSE(str) --反转字符串

例：查询姓周的同学，并将姓改成邹。

SELECT REPLACE(StudentName,'周','邹') FROM student
WHERE StudentName LIKE '周%'

3）时间和日期函数

--获取当前日期的三种方式
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())

4）系统

--获取用户的两种方式
SELECT SYSTEM_USER() 
SELECT USER()
SELECT VERSION() --获取版本号

2.聚合函数（常用）

1）count()：使用其可查询一个表中有多少记录
例：查询student表中的所有记录

SELECT COUNT(`BornDate`) FROM student; --Count(字段)，会忽略此字段中所有的null值的
SELECT COUNT(*) FROM student;
SELECT COUNT(1) FROM student;
-- Count(*)和Count(1)，不会忽略含有null值的，本质上都是计算行数

2）总和、平均、最大、最小四种函数
例：分别求学生成绩的总和、平均分、最高分和最低分。

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

补充：四、7.分组和过滤

例：查询不同课程的平均分、最高分、最低分，平均分要大于80分。

SELECT SubjectName,AVG(StudentResult) AS 平均分,MAX(StudentResult) AS 最高分,
MIN(StudentResult) AS 最低分
FORM result r
INNER JOIN `subject` sub
ON r.`SubjectNo` = sub.`SubjectNo`
GROUP BY r.SubjectNo --指定结果通过什么字段来分组
HAVING 平均分>=80 --使用分组需指定结果满足的条件，用HAVING而不是WHERE

3.数据库级别的MD5加密（扩展）

什么是MD5？
MD5信息摘要算法，一种被广泛使用的密码散列函数，可以产生出一个128位（16字节）的散列值，用于确保信息传输完整一致。
MD5由MD4、MD3、MD2改进而来，主要增强算法复杂度和不可逆性。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,'lisi','123456'),(1,'wangwu','123456')
--加密全部的密码（可以用WHERE只加密一部分）
UPDATE testmd5 SET pwd=MD5(pwd)
--在插入的时候就加密
INSERT INTO testmd5 VALUES(4,'xiaoming',MD5('123456'))
--如何校验：将用户传递进来的密码，进行md5加密，然后比对加密后的值
SELECT * FROM testmd5 WHERE `name`='xiaoming' AND pwd=MD5('123456')

补充：四、8.Select小结

注：顺序很重要！
①select 去重 要查询的字段 from 表
注意：表和字段可以取别名。
②XXX join 要连接的表 on 等值判断
③where（具体的值，子查询语句）
④Group By（通过哪个字段来分组）
⑤Having（过滤分组后的信息，条件和where是一样的，位置不同）
⑥Order By…（通过哪个字段排序：升序\降序）
⑦Limit startindex,pagesize
业务层面：查询：跨表、跨数据库…

参考博客https://www.jianshu.com/p/57988412e89f
参考博客https://www.jianshu.com/p/ce9e583398ce
参考博客https://www.jianshu.com/p/07a95a940267
参考博客https://www.jianshu.com/p/030d4ba2417e
参考博客https://www.jianshu.com/p/cf0d5a28eaf9
Ping开源：https://www.jianshu.com/u/50ea99bfc2b2