一、初识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
事务支持	不支持	支持
数据行锁定	不支持	支持
外键约束	不支持	支持
全文索引	支持	不支持
表空间的大小	较小	较大，约为MYISAM的2倍

常规使用操作：
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 子句（等于某个值，小于或大于某个值，在某个区间内修改…）
操作符：返回布尔值

操作符	含义
=	等于
<>或!=	不等于
>	大于
<	小于
<=	小于等于
>=	大于等于
BETWEEN…AND…	在某个范围内
AND	和
OR	或

例：

--通过多个条件定位数据
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）逻辑运算符

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

注：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）模糊查询：比较运算符

运算符	语法	描述
IS NULL	a is null	如果操作符为NULL，结果为真。
IS NOT NULL	a is not null	如果操作符不为NULL，结果为真。
BETWEEN	a between b and c	若a在b和c之间，则结果为真。
LIKE	a like b	SQL匹配，如果a匹配b，则结果为真。
IN	a in (a1,a2,…)	假设a在a1或a2或…其中的某一个值中，结果为真

例：

--查询姓刘的同学
--与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可省略。

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

例：查询缺考的同学。

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

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

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

子表：

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

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

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

结果表的样式：

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

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.聚合函数（常用）

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

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