MySQL

1、初始MySQL

JavaEE：企业级Java开发 web

前端（页面：展示，数据）

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

数据库（存数据：Txt，Excel，Word）

1.1、为什么学习数据库

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

1.2、什么是数据库（DB，DataBase）

概念：数据仓库，软件，安装在操作系统（window，Linux，mac等）之上！SQL，可以存储大量的数据（500万）

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

1.3、数据库分类

关系型数据库：（SQL）

• MySQL，Oracle，SQL server，DB2，SQLlite
• 通过表和表之间，行和列之间的关系进行数据的存储

非关系型数据库：（NoSQL） not only

• Redis，MongDB
• 对象存储，通过对象的自身的属性来决定

DBMS（数据库管理系统）

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

1.4、MySQL简介

MySQL是一个关系型数据库管理系统，由瑞典MySQL AB 公司开发，属于Oracle旗下产品

MySQL是最好的 RDBMS (Relational Database Management System，关系数据库管理系统) 应用软件之一

开源的数据库软件

体积小、速度快、总体拥有成本低，招人成本低

中小型或大型网站，集群

1.5、安装MySQL

1.6、安装SQLyog

1、安装

2、注册

3、打开连接数据库

4、新建一个数据库

每一个sqlyog的执行操作，本质就是对应了一个sql，可以在软件的历史记录中查看

5、新建一张表

6、查看表

7、添加记录（刷新保存）

1.7、连接数据库

命令行连接

mysql -uroot -p123456 --连接数据库

update mysql.user set authentication_string=password('123456') where user='root' and host='localhost';  --修改用户密码

flush privileges; --刷新权限

---------------------------------------------
--所有的语句都使用分号(;)结尾
show databases; --查看所有的数据库 
use school --切换数据库   use 数据库名

show tables; --查看数据库中所有的表
describe student; --显示数据库中所有表的信息

create database westos; --创建一个数据库

exit; --退出连接

DLL 数据库定义语言

DML 数据库操作语言

DQL 数据库查询语言

DCL 数据库控制语言

2、操作数据库

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

2.1、操作数据库

MySQL关键字不区分大小写

1、创建数据库

CREATE DATABASE [IF NOT EXISTS] westos

2、删除数据库

DROP DATABASE [IF EXISTS] hello

3、使用数据库

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

SELECT `user` FROM student --student中的user属性

4、查看数据库

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

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 ，如果不给它赋值，就会报错!
• NUII，如果不填写值，默认就是null!

默认：

• 设置默认的值!

• 例如 ： sex，默认值为男，如果不指定该列的值，则会有默认的值!

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

• id 主键
• 'version ’ 乐观锁
• is_delete 伪删除
• gmt_create 创建时间
• gmt_update 修改时间

2.4、创建数据库表

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

-- 注意使用英文的() ``
-- auto_increment自增
-- 字符串使用单括号括起来
-- 所有的语句后面加,(英文的)，最后一个不用加
-- primary key 主键（一个表只有一个）
CREATE TABLE IF NOT EXISTS `student1`(
  `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	INNODB
事务支持	不支持	支持
数据行锁定	不支持 （表锁）	支持
外键约束	不支持	支持
全文索引	支持	不支持
表空间的大小	较小	较大，约为两倍

常规使用操作：

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

在物理空间存在的位置：

• 所有的数据库文件都存在data目录下，一个文件夹就对应一个数据库
• 本质还是文件的存储

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

• *.frm 表结构的定义文件
• *.MYD 数据文件(data)
• *.MYI 索引文件( index)

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

CHARSET=utf8

• 不设置的话，会是mysql默认的字符集编码~(不支持中文! )
• MySQL的默认编码是Latin1，不支持中文
• 在my.ini中配置默认的编码

character-set-server=utf8

2.6、修改删除表

修改

-- 修改表名 ALTER TABLE 旧表名 RENAME AS 新表名
ALTER TABLE student RENAME AS student2
-- 添加表的字段 ALTER TABLE 表名 ADD 字段名 列属性
ALTER TABLE student1 ADD age INT(11)
-- 修改表的字段 （重命名，修改约束）
ALTER TABLE student2 MODIFY age VARCHAR(11) -- 修改约束
ALTER TABLE student2 CHANGE age age1 INT(1) -- 字段重命名
-- 删除表的字段
ALTER TABLE student2 DROP age1

删除

-- 删除表(如果表存在再删除)
DROP TABLE IF EXISTS student2

3、MySQL的数据管理

3.1、外键

方式一、在创建表的时候，增加约束(麻烦，比较复杂)

CREATE TABLE `gradeid`(
  `greadid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年级',
  `gradename` VARCHAR(50) NOT NULL COMMENT '年纪名称',
  PRIMARY KEY(`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

--学生表的 gradeid字段要去引用年级表的gradeid
--定义外键key
--给这个外键添加约束(执行引用)  references引用


CREATE TABLE IF NOT EXISTS `student1`(
  `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 IF NOT EXISTS `student1`(
  `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 into 表名([字段名1,字段2,字段3])values('值1'),('值2'),('值3'),....
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 ('李斯','aaaaa','女'),
VALUES ('王五','aaaaa','男'),

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

3.4、修改

-- 修改学员名字，带了简介
UPDATE `student` SET `name`='戴玫孜' WHERE id = 1;

-- 不指定条件的情况下，会改动所有的表
UPDATE `student` SET `name`='戴玫孜'

-- 修改多个属性,逗号隔开
UPDATE `student` SET `name`='dmz',`email`='490229306@qq.com' WHERE id = 1;

条件：where子句运算符

操作符会返回布尔值

操作符	含义	范围	结果
=	等于	5=6	false
<>或！=	不等于	5<>6	true
>
<
<=
>=
BETWEEN…and…	在某个范围内	[2,5]
AND	我和你&&	5>1and1>2	false
OR	我或你||	5>1or1>2	true

-- 通过多个条件定位数据 trim标签可以删除多余的逗号
UPDATE `student` SET `name`='长江7号' WHERE `name`='狂神44' AND sex='女'

语法:

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

注意：

• colnum_name是数据库的列，尽量带上``
• 条件，筛选的条件，如果没有指定，则会修改所有的列
• value，是一个具体的值，也可以是一个变量
• 多个设置的属性之间，使用英文逗号隔开

3.5、删除

delete命令

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

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

TRUNCATE命令

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

-- 清空student表
TRUNCATE `student`

delete和TRUNCATE的区别

• 相同点:都能删除数据，都不会删除表结构

• 不同:

  ​ TRUNCATE重新设置自增列 计数器会归零

  ​ TRUNCATE不会影响事务

-- 测试delete 和 truncate 的区别
CREATE TABLE `text`(
  `id` INT(4) NOT NULL AUTO_INCREMENT,
  `coll` VARCHAR(20) NOT NULL,
  PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

INSERT INTO `text` (`coll`) VALUES('1'),('2'),('3')

DELETE FROM `text`  -- 不会影响自增

TRUNCATE TABLE `text` -- 自增会归零

了解即可:DELETE删除的问题，重启数据库，现象

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

4、DQL查询数据（最重点）

---

4.1、DQL

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

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

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);

查询：

-- 查询全部的学生  SELECT 字段 FROM 表 
SELECT * FROM student
-- 查询指定字段
SELECT `studentNo`, `studentName ` FROM student
-- 别名，给结果起一个名字 AS 可以给字段起别名，也可以给表起别名
SELECT `StudentNo` AS 学号,`studentName ` AS 学生姓名 FROM student AS s
-- 函数 Concat (a，b )
SELECT CONCAT ( '姓名: ', studentName) As 新名字 FROM student

4.2、去重及据库的表达式

去重 distinct

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

数据库的列（表达式）

-- 查询系统版本（函数）
SELECT VERSION()
-- 用来计算（表达式）
SELECT 100*3-1 AS 计算结果
-- 查询自增的步长（变量）
SELECT @@auto_increment_increment

-- 学员考试成绩  + 1分查看
SELECT `studentno`,`studentresult`+1 AS '加一分后' FROM result

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

4.3、where条件子句

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

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

逻辑运算符

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

尽量使用英文字母

-- ==================== 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

模糊查询：比较运算符

运算符	语法	描述
lS NULL	a is null	如果操作符为NUIl,结果为真
lS 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,a3.	假设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 `studentno` IN (1001,1002,1003);

-- 查询在北京的学生
SELECT `studentno`,`studentname` FROM `student`
WHERE `address` IN （'北京'）;

-- === null       not null ===

-- 查询地址为空的学生 null  ''
SELECT `studentno`,`studentname` FROM `student`
WHERE `address`=''   OR  `address` IS NULL

-- 查询有出生日期的同学  不为空
SELECT `studentno`,`studentname` FROM `student`
WHERE `borndate` IS NOT NULL

4.4、联表查询

-- ===================== 联表查询 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 r
ON s.studentno = r.studentno

-- left join
SELECT s.studentno,studentname,subjectno,studentresult
FROM student s
LEFT JOIN result r
ON s.studentno = r.studentno

-- 查询缺考的同学
SELECT s.studentno,studentname,subjectno,studentresult
FROM student s
LEFT JOIN result r
ON s.studentno = r.studentno
WHERE studentresult IS NULL

-- --思考题（查询了参加考试的同学信息:学号，学生姓名，科目名，分数)/*思路
/*
1．分析需求，分析查询的字段来自哪些表，student、result、subject(连接查询)
2．确定使用哪种连接查询?   7种
确定交叉点(这两个表中哪个数据是相同的)
判断的条件:学生表的中 studentNo =成绩表 studentNo
*/
SELECT s.studentno,`studentname`,`subjectname`,`studentresult`
FROM student s
RIGHT JOIN result r
ON s.`studentno` = r.`studentno`
INNER JOIN SUBJECT sub
ON r.subjectno = sub.subjectno


-- 查询哪些数据
-- 从哪几个表中查 from 表 XXX join 连接的表 on 交叉条件
-- 假设存在一种多张表查询，先查询两张表然后再慢慢增加

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

自连接

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

父类：

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

子类：

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

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

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

-- ================ 自连接 ==================

CREATE TABLE `school`.`category`(
`categoryid` INT(3) NOT NULL COMMENT 'id', 
`pid` INT(3) NOT NULL COMMENT '父id 没有父则为1',
`categoryname` VARCHAR(10) NOT NULL COMMENT '种类名字',
 PRIMARY KEY (`categoryid`)
) ENGINE=INNODB CHARSET=utf8 COLLATE=utf8_general_ci; 

INSERT INTO `school`.`category` (`categoryid`, `pid`, `categoryname`) VALUES (2, 1, 信息技术);
INSERT INTO `school`.`CATEGOrY` (`categoryid`, `pid`, `categoryname`) VALUES (3, 1, 软件开发);
INSERT INTO `school`.`category` (`categoryid`, `PId`, `categoryname`) VALUES (5, 1, 美术设计);
INSERT INTO `School`.`category` (`categoryid`, `pid`, `categorynamE`) VALUES (4, 3, 数据库); 
INSERT INTO `school`.`category` (`CATEgoryid`, `pid`, `categoryname`) VALUES (8, 2, 办公信息);
INSERT INTO `school`.`category` (`categoryid`, `pid`, `CAtegoryname`) VALUES (6, 3, web开发); 
INSERT INTO `SCHool`.`category` (`categoryid`, `pid`, `categoryname`) VALUES (7, 5, ps技术);

-- 查询父子信息

SELECT a.`categoryname` AS 'father',b.`categoryname` AS 'son'
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`

-- 查询科目所属的年级（科目名称，年级名称）
SELECT `subjectname`,`gradename`
FROM `subject` sub
INNER JOIN `grade`  g
ON sub.`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'

4.5、分页和排序

排序

-- ========================= 分页 limit 和排序 order by =====================

-- 排序 ： 升序 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` DESC

分页

-- 为什么要分页？
-- 缓解数据库压力，给人的体验更好，瀑布流

-- 分页，每页只显示五条数据
-- 语法：limit  起始值，页面大小
-- limit 0,5    1~5
-- limit 1,5    2~6
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` DESC
LIMIT 5,5

-- 第一页 limit 0,5   (1-1)*5
-- 第二页 limit 5,5   (2-1)*5
-- 第N页  limit x,5   (n-1)*pagesize
-- 【pagesize:页面大小】
-- 【(n-l) * pageSize:起始值】
-- 【n :当前页】
-- 【数据总数/页面大小=总页数】

语法：limit（查询起始下标，pagesize）

综合练习：

-- 思考:
-- 查询 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

4.6、子查询

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

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

-- ================= where ===================

-- 1、查询数据库结构-1的所有考试结果（学号，科目编号，成绩），降序排列
-- 方式一：使用连接查询
SELECT `studentno`,r.`subjectno`,`studentresult`
FROM result r
INNER JOIN `subject` sub
ON r.`studentno` = sub.`subjectno`
WHERE `subjectname` = '数据库结构-1'
ORDER BY `studentresult` DESC

-- 方式二：使用子查询(由里及外)
SELECT `studentno`,r.`subjectno`,`studentresult`
FROM result r
WHERE `studentno` = (
SELECT `subjectno` FROM `subject`
WHERE `subjectname` = '数据库结构-1')
ORDER BY `studentresult` DESC

/*
查询所有数据库结构-1的学生学号
select `subjectno` from `subject` where `subjectname` = '数据库结构-1'
*/

-- 查询课程为 高等数学-2 且分数不小于 80 的同学的学号和姓名
SELECT s.`studentno`,`studentname`
FROM `student` s
INNER JOIN result r
ON s.`studentno` = r.`studentno`
INNER JOIN `subject` sub
ON r.`subjectno` = sub.`subjectno`
WHERE `subjectname` = '高等数学-2' AND `studentresult` >= 80

-- 分数不小于80分的学生的学号和姓名
SELECT DISTINCT s.`studentno`,`studentname`
FROM `student` s
RIGHT JOIN `result` r
ON s.`studentno` = r.`studentno`
WHERE r.`studentresult` >= 80
-- 增加一个科目：高等数学-2
-- 查询 高等数学-2的编号
SELECT DISTINCT s.`studentno`,`studentname`
FROM `student` s
RIGHT JOIN `result` r
ON s.`studentno` = r.`studentno`
WHERE r.`studentresult` >= 80 AND `subjectno` = (
  SELECT `subjectno` 
  FROM `subject`
  WHERE `subjectname` = '高等数学-2'  
)

-- 再改造(由里及外)
SELECT `studentno`,`studentname` FROM `student` WHERE `studentno` IN(
   SELECT `studentno` FROM `result` WHERE `studentresult` >80 AND `subjectno` =(
         SELECT `subjectno` FROM `subject` WHERE `subjectname` = '高等数学-2'    
   )

)

-- 练习：查询 C语言-1 前五名同学的成绩的信息（学号，姓名，分数）
-- 使用子查询
SELECT s.`studentno`,`studentname`,`studentresult`
FROM `student` s
INNER JOIN `result` r
ON s.`studentno` = r.`studentno`
INNER JOIN `subject` sub
ON sub.`subjectno` = r.`subjectno` AND `subjectname` = 'C语言-1'
ORDER BY `studentresult` DESC
LIMIT 3

4.7、分组过滤

-- 查询不同课程的平均分，最高分，最低分，平均分大于80
-- 思路：根据不同课程分组
SELECT `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

5、MySQL函数

官网：https://dev.mysql.com/doc/refman/5.7/en/func-op-summary-ref.html

5.1、常用函数

-- ================== 常用函数 ==================

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

-- 字符串函数
SELECT CHAR_LENGTH ('即使再小的帆也能远航')-- 字符串长度
SELECT CONCAT ('我'，'想'，'睡觉') -- 拼接字符串
SELECT INSERT ('我爱Java', 1,2，'超级热爱') -- 查询，从某个位置开始替换某个长度
SELECT LOWER('ABcd' )-- 小写字母
SELECT UPPER('ABcd')-- 大写字母
SELECT INSTR ('iubfyuerbqvyubeqv', 'y') -- 返回第一次出现的子串的索引
SELECT REPLACE ('开开心心玩玩耍耍','开开心心', '吃吃喝喝')-- 替换出现的指定字符串
SELECT SUBSTR ('我想吃烤串',2,6) -- 返回指定的子字符串(源字符串,截取的位置，截取的长度)
SELECT REVERSE('123456789') -- 反转

-- 查询姓 王 的同学，改为 戴
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(`studentno`) 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` varhar(50) NOT NULL,
   PRIMARY KEY(`id`)
)ENGINE=INNODB  DEFAULT CHARSET=utf8

-- 明文密码
INSERT INTO testmd5 VALUES
(1,'bili','123456')
(1,'bob','123456')
(1,'helen','123456')

-- 加密
UPDATE testmd5 SET pwd=MD5(pwd) WHERE id=1

UPDATE testmd5 SET pwd=MD5(pwd) -- 加密全部的密码

-- 插入的时候加密
INSERT INTO testmd5 VALUES(4,'bob',MD5(123456))

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

5.4、select语法

/*
select'  (去重)  要查询的字段
from表 (注意:表和字段可以取别名)
xxx join 要连接的表 
on 等值判断
where(具体的值，子查询语句)
Group By(通过哪个字段来分组)
Having(过滤分组后的信息，条件和where是—样的，位置不同)
Order By .. (通过哪个字段排序) 
Limit  
*/