MySQL笔记

1、什么是Mysql?

1、1 简介

MySQL是一个关系型数据库系统，由瑞典MySQL AB 公司开发，属于Oracle 旗下产品。MySQL 是最流行的关系型数据库系统之一，在 WEB 应用方面，MySQL是最好的RDBMS(Relational Database Management System，关系数据库管理系统) 应用软件之一。

MySQL是一种关系型数据库管理系统，关系数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。

MySQL所使用的 SQL 语言是用于访问数据库的最常用标准化语言。MySQL 软件采用了双授权政策，分为社区版和商业版，由于其体积小、速度快、总体拥有成本低，尤其是开放源码这一特点，一般中小型网站的开发都选择 MySQL 作为网站数据库。

1、2 sql语句结构

结构化查询语言包含6个部分：

1、数据查询语言（DQL）：其语句，也称为“数据检索语句”，用以从表中获得数据，确定数据怎样在应用程序给出。保留字SELECT是DQL（也是所有SQL）用得最多的动词，其他DQL常用的保留字有WHERE，ORDER BY，GROUP BY和HAVING。这些DQL保留字常与其它类型的SQL语句一起使用。 [4]

2、数据库操作语言（DML：Data Manipulation Language）：其语句包括动词INSERT、UPDATE和DELETE。它们分别用于添加、修改和删除。

3、事务控制语言（TCL）：它的语句能确保被DML语句影响的表的所有行及时得以更新。包括COMMIT（提交）命令、SAVEPOINT（保存点）命令、ROLLBACK（回滚）命令。

4、数据控制语言（DCL）：它的语句通过GRANT或REVOKE实现权限控制，确定单个用户和用户组对数据库对象的访问。某些RDBMS可用GRANT或REVOKE控制对表单个列的访问。

5、数据定义语言（DDL）：其语句包括动词CREATE,ALTER和DROP。在数据库中创建新表或修改、删除表（CREAT TABLE 或 DROP TABLE）；为表加入索引等。

6、指针控制语言（CCL）：它的语句，像DECLARE CURSOR，FETCH INTO和UPDATE WHERE CURRENT用于对一个或多个表单独行的操作。

2、操作数据库

2、1 数据库操作命令(了解)

1、创建数据库

CREATE DATABASE [IF EXISTS] `school`;

2、删除数据库

DROP DATABASE [IF EXISTS] `school`;

3、使用数据库

USE `school`;

2、2 数据库的列类型

数值

• tinyint 十分小的数据 1个字节
• smallin 较小的数据 2个字节
• mediumint 中等大小的数据 3个字节
• int 标准的整数 4个字节 常用的 int
• bigint 较大的整数 8个字节
• float 浮点数 4个字节
• double 浮点数 8个字节（精度问题！）
• decimal 字符串形式的浮点数 金融计算的时候，一般使用decimal

字符串

• char 字符串固定大小的 0~255
• varchar 可变字符串 0~65535 常用的变量 String
• tinytext 微型文本 2^8-1
• text 文本串 2^16-1 保存大文本

时间日期

java.util.Date

• date YYYY-MM-DD 日期格式

• time HH: mm : hh 时间格式

• datatime YYYY-MM-DD HH: mm : hh 最常用的时间格式

• timestamp 时间戳， 1970.1.1到现在的毫秒数！ 也较为常用

• year 年份表示

  null

• 没有值，位置

• 注意，不要使用Null进行运算，结果为null

2、3 数据库的字段属性（重点）

Unsigned:

• 无符号整数
• 声明了该列不能声明为负数

zerofill:

• 0填充的
• 不足的位数，使用0来填充， int(3) , 5 - - - 005

自增：auto_increment

• 通常理解为自增，自动在上一条记录的基础上 + 1 默认
• 通常用来设计唯一的主键 index，必须时整数类型
• 可以自定义设计主键自增的起始值和步长

非空: null not null

• 假设设置为not null,如果不给他赋值，就会报错
• null,如果不填写值，默认就是null

默认：

• 设置默认的值！
• sex,默认值为男，如果不指定该列的值，就会使用默认的值！

2、4 创建数据库表（重点）

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

-- 注意点，使用英文(),表的名称 和 字段 尽量使用 `` 括起来
-- AUTO_INCREMENT 自增
--  字符串使用 单引号括起来！
-- 所有的语句后面加 ， (英文的)，最后一行不加
-- PRIMARY KEY 主键，一般一个表只有一个唯一的主键
CREATE TABLE IF NOT EXISTS `student`(
	`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
	`name` VARCHAR(20) NOT NULL DEFAULT '匿名' COMMENT '姓名',
	`pwd` VARCHAR(32) NOT NULL DEFAULT '123456' COMMENT '密码',
	`sex` VARCHAR(2) NOT NULL  DEFAULT '男' COMMENT '性别',
	`birthday` DATETIME DEFAULT NULL COMMENT '生日',
	`address` VARCHAR(32) DEFAULT NULL COMMENT '地址',
	`email` VARCHAR(32) DEFAULT NULL COMMENT '邮箱',
	PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

格式

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

常用命令

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

2、5 数据表的类型

-- 关于数据库引擎
/*
INNODB 默认使用
MYISAM 早些年使用的
*/

	MYISM	INNODB
事务支持	不支持	支持
数据行锁定	不支持	支持
外键约束	不支持	支持
全文索引	支持	不支持
表空间的大小	较小	较大，约2倍

常规使用操作：

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

在物理空间存在的位置

​ 所有的数据库文件都存在data目录下

​ 本质还是文件的存储！

设置字符集编码

CHARSET=utf8

不设置的话，会是mysql默认的字符集编码(不支持中文！)

mysql的默认编码是Latin1,不支持中文

在my.ini重可以自己配置默认的编码，建议在表中设置(CHARSET=utf8)

character-set-server=utf8

2、6 修改删除表

修改

-- 修改表名  ALTER TABLE 旧表名 RENAME AS 新表名
ALTER TABLE teacher RENAME AS teacher1
-- 添加表的字段 ALTER TABLE 表名 ADD 字段 列属性
ALTER TABLE teacher1 ADD age INT(3)


-- 修改表的字段（重命名，修改约束！）
--  ALTER TABLE 表名 MODIFY 字段名 [列属性]
ALTER TABLE teacher1 MODIFY age VARCHAR(32) -- 修改约束
--  ALTER TABLE 表名 CHANGE 旧字段名 新字段名 列属性
ALTER TABLE teacher1 CHANGE age age1 INT(3) -- 字段重命名


-- 删除表的字段： ALTER TABLE 表名 DROP 字段名 
ALTER TABLE teacher1 DROP age1 

删除

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

所有的创建和删除操作尽量加上判断(IF EXISTS)，以免报错!

注意点：

• `` 字段名，表名，使用这个包裹！
• 注释 – ，/**/
• 所有符号用英文！

3、MySQL数据管理

3、1 外键（了解即可）

方式一、在创建表的时候，增加约束（比较麻烦）

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 COLLATE=utf8_general_ci;


-- 学生表的 grideid 字段 要引用年级表的 gradeid
-- 定义外键key
-- 给这个外键添加约束 (执行引用) references 引用
CREATE TABLE IF NOT EXISTS `student`(
	`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
	`name` VARCHAR(20) NOT NULL DEFAULT '匿名' COMMENT '姓名',
	`pwd` VARCHAR(32) NOT NULL DEFAULT '123456' COMMENT '密码',
	`sex` VARCHAR(2) NOT NULL  DEFAULT '男' COMMENT '性别',
	`gradeid` INT(10) NOT NULL COMMENT'年级id',
	`birthday` DATETIME DEFAULT NULL COMMENT '生日',
	`address` VARCHAR(32) DEFAULT NULL COMMENT '地址',
	`email` VARCHAR(32) DEFAULT NULL COMMENT '邮箱',
	PRIMARY KEY(`id`),
	KEY `FK_gradeid`(`gradeid`),
	CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade`(`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

删除有外键关系的表的时候，必须要先删除引用别人的表(从表)，再删除被引用的表！

方式二、创建表成功后，再添加外键

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 collate=utf8_general_ci;


-- 注意：既使没有在表内定义外键，但是要在表中定义，有主表的字段
create table IF NOT EXISTS `student`(
	`id` int(4) not null auto_increment comment '学号',
	`name` varchar(20) not null default '匿名' comment '姓名',
	`pwd` varchar(32) not null default '123456' comment '密码',
	`sex` varchar(2) not null  default '男' comment '性别',
    `gradeid` INT(10) NOT NULL COMMENT '年级id',
	`birthday` datetime default null comment '生日',
	`address` varchar(32) default null comment '地址',
	`email` varchar(32) default null comment '邮箱',
	PRIMARY KEY(`id`)
)engine=innodb default charset=utf8 collate=utf8_general_ci;

-- 创建表的时候没有外键关系
-- ALETER TABLE 表 ADD CONSTRAINT 约束名 FOREIGN KEY (作为外键的列) REFERENCES 那个表(那个字段)
alter table `student` 
add CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade`(`gradeid`);

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

最佳实践

• 数据库就是单纯的表，只用来存数据，只有行（数据）和列（字段）
• 我们想使用多张表的数据，想使用外键（程序去实现）
• 【强制】不得使用外键与级联，一切外键概念必须在应用层解决

3、2 DML语言（全部记住）

数据库意义：存储数据

• insert
• update
• delete

3、3 添加

insert

-- 插入语句(添加)
-- INSERT INTO 表名（[字段1],[字段二]...） VALUES('值1','值2')，('值1','值2')...;
INSERT INTO `grade`(`gradename`) VALUES('大一');

-- 由于主键自增我们可以省略(如果不写表的字段，他就会一一匹配)
INSERT INTO `grade` VALUES('大四');

-- 一般写插入语句，我们一定要数据和字段一一对应！


-- 插入多个值
INSERT INTO `grade`(`gradename`)
VALUES ('大二'),('大三');

-- 插入多个字段多个值
INSERT INTO `student`(`id`,`name`,`sex`)
VALUES ('001','张三','男'),('002','李四','男');

语法：insert into 表名（[字段1],[字段二]…） values(‘值1’,‘值2’)，(‘值1’,‘值2’)…;

注意事项：

1. 字段和字段之间使用英文逗号隔开
2. 字段是可以省略的，但必须与表的字段顺序一一对应
3. 可以同时插入多条数据，VALUES后面的值，需要使用，隔开即可 values (),(),…

3、4 修改

update 修改谁 (条件) set 原来的值 = 新值

-- 修改学员名字
UPDATE `student` SET `name`='大哥' WHERE `id`=1;

-- 如果不指定条件，会改动所有表！
UPDATE `student` SET `name`='大哥';

-- 修改多个属性用 ， 隔开
UPDATE `student` SET `name`='大哥',`pwd`='54648' WHERE `id`=1;

-- 通过多个条件定位数据
UPDATE `student` SET `name`='大哥',`pwd`='54648' WHERE `id`=1 AND `sex`='男';
-- 语法：
-- UPDATE 表名 SET colnum_name=value WHERE [条件];

条件：where子句 运算符 id 等于某个值，大于某个值，小于某个值，在某个区间修改…

如：where id =1 where id >1 where id <=5 between…and… …and… …or…

语法：UPDATE 表名 SET colnum_name=value, [colnum_name=value]… WHERE [条件…];

注意：

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

3、5 删除

delete 命令

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

-- 删除数据（避免这样写，会全部删除）
DELETE FROM `student`

-- 删除指定数据
DELETE FROM `student` WHERE `id` = 1;

TRUNCATE 命令

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

-- 清空 student 表
TRUNCATE `student`

delete 和 truncate 区别

• 相同点：都可以删除数据，都不会删除表结构
• 不同点：
  • TRUNCATE 重新设置 自增列 计数器会归零
  • TRUNCATE 不会影响事务

-- 测试delte和truncate区别
create table `test`(
	`id` int(4) not null auto_increment,
    `coll` varchar(20) not null,
    PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=uft8;

insert into `test`(`coll`) values('1'),('2'),('3')

delete from `test`; -- 不会影响自增

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

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

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

4、DQL查询数据（重点）

4、1 DQL

(Data Query Language:数据查询语言)

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

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 on {等值判断}] --联合查询
	[WHERE ...] -- 指定结果满足的条件
	[GROUP BY ...] -- 指定结果按照那几个字段来分组
	[HAVING] -- 过滤分组的记录必须满足的次要条件
	[ORDER BY ...] -- 指定查询记录按一个或多个条件排序
	[LIMIT {[offset,]row_count | row_countOFFSET offset}]; -- 指定查询的记录从那条到那条
	
-- 注意：[]代表可选，{}代表必选	

4、2 指定查询字段

-- 查询全部的学生   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

语法：select 字段，… from 表

有时候，列名不是那么的见名知意。我们起别名 AS 字段名 as 别名 表名 as 别名

去重 distinct

作用：去除select查询出来的结果中重复的数据，重复的数据只显示一条

-- 查询一下有那些同学参加了考试
select * from `result`   -- 查询了所有的考试成绩
select `studentNo` from `result` -- 查询有那些同学参加了考试
select distinct `studentNo` from `result` --发现重复数据，去重

数据库的列（表达式）

select version() -- 查看mysql版本
select 78+3 as 计算结果  -- 用来计算  (表达式)
select @@auto_increment_increment -- 查询自增步长 (变量)

-- 学员考试成绩全部 +1 分 查看
select `studentNo`,`studentResult`+1 AS '提分后' from result

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

select 表达式 from 表

4、3 where 条件子句

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

搜索的条件由一个或则多个表达式组成，返回值为布尔值。

逻辑运算符

运算符	语法	描述
and &&	a and b a&&b	逻辑与
or ||	a or b a||b	逻辑或
Not !	not a !a	逻辑非

-- ============where========= 
-- 查询考试成绩在95-100之间
select `studentNo`,`studentResult` from `result` 
where `studentResult`>=95 and `studentResult`<=100

-- 模糊查询（区间）
select `studentNo`,`studentResult` from `result` 
where `studentResult` between 95 and 100

-- 除了1000号学生以外的同学的成绩
select `studentNo`,`studentResult` from `result` 
where `studentNo` != 1000 

模糊查询 ：比较运算符

运算符	语法	描述
IS NULL	a is null	如果操作符为null,结果为真
IS NOT NULL	a is not null	如果操作符为not null,结果为真
BETWEEN	a between b and c	若a在b和c之间，则结果为真
Like	a like b	sql匹配，如果a匹配b,则结果为真
In	a in (a1,a2,a3…)	假设a在a1,或则a2…,则结果为真

-- ============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 '%嘉%'


-- =============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 `birthday` is not null

-- 查询没有出生日期的同学
select `studentNo`,`studentName` from `student`
where `birthday` is null

4、4 联表查询

Join 对比

-- ========联表查询==========

-- 查询参加考试的学生(学号，姓名，课程号，课程成绩)
/*
1.分析需求，分析查询的数据来自那些表，（连接查询）
2.确定使用那种查询方式？7种
  确定交叉点（这两个表中那个数据是相同的）
  判断的条件 ： 学生表的 studentNo = 成绩表的 studentNo
*/

-- join(连接表) on(条件)  连接查询
-- where    等值查询
-- 效果一样

-- INNER JOIN
select s.studentNo,studentName,subjectNo,studentResult
from student as s 
INNER JOIN result as r
on s.studentNo = r.studentNo

-- RIGHT JOIN
select s.studentNo,studentName,subjectNo,studentResult
from student as s 
RIGHT JOIN result as r
on s.studentNo = r.studentNo

-- LEFT JOIN
select s.studentNo,studentName,subjectNo,studentResult
from student as s 
LEFT JOIN result as r
on s.studentNo = r.studentNo

-- 查询缺考的同学
select s.studentNo,studentName,subjectNo,studentResult
from student as s 
LEFT JOIN result as r
on s.studentNo = r.studentNo
where studentResult is null

-- 查询参加考试的学生(学号，姓名，课程名称，课程成绩)
/*
1.分析需求，分析查询的数据来自那些表，student,subject,result（连接查询）
2.确定使用那种查询方式？7种
  确定交叉点（这两个表中那个数据是相同的）
  判断的条件 ： 学生表的 studentNo = 成绩表的 studentNo
*/
select s.studentNo,studentName,subjectName,StudentResult
from student as s
right join result r
on s.studentNo=r.studentNo
inner join subject as sub
on s.SubjectNo=sub.SubjectNo

-- 我要查询那些数据  select ...
-- 从那几个表中查 from 表  XXX join 连接的表
-- 假设存在一种多张表查询，慢慢来，先查询两张表，然后再慢慢查询，学会拆分表
-- 如上面是三表查询，先是查询两张表后得到一张表的结果，再与剩下的表联表查询

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

自连接

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

父类

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

子类

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

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

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

-- 查询父子信息，把一张表拆分为两张表来查询
select a.categoryName as '父栏目',b.categoryName as '子栏目'
from category as a,category as b
where a.categoryid=b.pid;


-- ===========练习============

-- 查询学员所属的年级(学号，学生的姓名，年纪名称)
select sutdentNo,studentName,grideName
from student as s
inner join grade as g
on s.gradeid=g.gradeid

-- 查询科目所属的年级 (科目名称，年级名称)
-- 因为查询的字段没有重复的，所以可以省略s和g的前缀
select subjecName,GradeName
from subject as s
inner join Grade as g
on s.gradeid=g.gradeid;

-- 查询参加 数据库结构 考试的学生(学号，姓名，课程名称，课程成绩)
select s.studentNo,studeName,subjectName,StudentResult
from student as s
inner join result as r
on s.studentNo=r.studentNo
inner join Subject as sub
on s.SubjectNo=sub.SubjectNO
where SubjectName='数据库结构-1';

4、5 分页与排序

排序

-- 排序 ：升序 ： ASC   ,降序： DESC
-- 语法 ：order by 通过那个关键字排序，怎么排
-- 查询的结果根据 成绩降序 排序
select s.studentNo,studeName,subjectName,StudentResult
from student as s
inner join result as r
on s.studentNo=r.studentNo
inner join Subject as sub
on s.SubjectNo=sub.SubjectNO
where SubjectName='数据库结构-1'
order by studentResult DESC;

分页

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

-- 分页，每页只显示五条数据
-- 语法：limit起始值，页面的大小
-- 页面应用：当前页，页面大小
-- limit 0,5   1-5
-- limit 1,5   2-6
-- limit 6,5   
select s.studentNo,studeName,subjectName,StudentResult
from student as s
inner join result as r
on s.studentNo=r.studentNo
inner join Subject as sub
on s.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
-- 第三页 limit 10,5	(3-1)*5
-- 第N页  limit 0,5	 (n-1)*pageSize
-- 【pageSize:页面大小】
-- 【（n-1）*pageSize:起始值】
-- 【 n:当前页 】
-- 【 数据总数/页面大小 = 总页数 】

语法：LIMIT (查询起始下表，pageSize)

-- 练习
-- 查询 Java第一学年 课程成绩排名前十的学生，并且分数要大于80的学生信息(学号，姓名，课程名称，分数)
select s.studentNo,studentName,SubjectName,StudentResult
from student as s
inner join result r
on s.studentNo=r.studentNo
inner join Subject as sub
on s.SubjectNo=sub.SubjectNo
where StudentResult > 80 and SubjectName='Java第一学年'
order by StudentResult DESC
limit 0,10;

-- 注意 limit 语句都是在语句的最后，order by在它之前

4、6 子查询

where (这个值是计算出来的)

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

-- ============= where ============
-- 1、查询 数据库结构-1 的所有考试结果（学号，科目编号，成绩），降序排列
-- 方式一： 使用连接查询
select studentNo,SubjectNo,studentResult
from student as r
inner join subject as sub
on r.subjectNo=sub.subjectNo
where subjectName='数据库结构-1'
order by studentResult DESC

-- 方式二： 使用子查询
select studentNo,SubjectNo,studentResult
from result
where subjectNo=(
	select subjectNo from subject
    where subjectName='数据库结构-1'
)
order by studentResult DESC

-- 查询课程为 高等数学-2 且分数不小于 80 的同学的学号和姓名
select s.studentNO,studentName
from student as s
inner join result as r
on s.studentNO=r.studentNO
inner join subject as sub
on sub.subjectNo=r.subjectNo
where subjectName='高等数学-2 '
and studentResult >= 80

-- 分数不小于80的学生的学号和姓名
select s.studentNo,studentName
from student as s
inner join result as r
on s.studentNo=r.studentNo
where studentResult >= 80

--在这个基础上增加一个科目， 高等数学-2
-- 查询 高等数学-2 的编号
select s.studentNo,studentName
from student as s
inner join result as r
on s.studentNo=r.studentNo
where studentResult >= 80
and subjectNo=(
 	select subjectNo
    from subject 
    where subjectName='高等数学-2 '
)

-- 再改造(由里到外)
select s.studentNo,studentName from student
where studentNo in(
	select studenNo from result where studentResult >=80
    and subjectNo = (
    	select subjectNo from subject
        where subjectName='高等数学-2 ''
    )
)

4、7 分组和过滤

-- 查询不同课程的平均分，最高分，最低分，平均分大于80
-- 核心： （根据不同的课程分组）
SELECT SubjecName,AVG(studentResult) as 平均分,MAX(studentResult),MIN（studentResult）
from result r
inner join subject sub
on r.subjectNo=sub.subjectNo
GROUP BY r.subjectNo -- 通过什么字段来分组
HAVING 平均分 > 80  -- 分组过滤 (不能使用where，都是和group by组合使用，放在它之后的)

5、 MySQL函数

5、1 常用函数

-- =========常用函数==========
-- 数学运算
SELECT ABS(-8) --绝对值
SELECT CELLING(9.4) --向上取整
SELECT FLOOR(9.4) --向下取整
SELECT RAND() --返回一个0-1之间的随机数
SELECT SIGN() --判断一个数的符号

-- 字符串函数
SELECT CHAR_LENGTH('DDSAF')  -- 字符串长度
SELECT CONCAT('我','爱','你们') -- 拼接字符串
SELECT INSERT('大家伙',1,2,'布什')  -- 查询，替换
SELECT LOWER()  -- 转小写
SELECT UPPER()  -- 转大写
SELECT INSERT('大饥荒','大') --定位第一次出现的子串的下标
SELECT REPLACE()  --  替换出现的指定字符串
SELECT SUBSTR() -- 返回指定的子字符串
SELECT REVERSE() --反转字符串

-- 查询姓周的同学，名字 舟
select REPLACE(studentName,'周','舟') from student where studentName like '周%'

-- 时间和日期函数 (重要)
SELECT CURRENT_DATE() --获取当前日期
SELECT CURDATE() -- 获取当前日期
SELECT NOW() -- 获取当前的时间
SELECT LOCALTIME() --本 地时间
SELECT SYSDATE() -- 系统时间

SELECT YEAR(NOW())
SELECT MONTH(NOW())
SELECT DAY(NOW())
SELECT HOUR(NOW())
SELECT MINUTE(NOW())
SELECT SECOND(NOW())

-- 系统
SELECT SYSTEM_USER()
SELECT USER()
SELECT VERSION()

5、2 聚合函数(常用)

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

-- ============聚合函数==============
-- 统计表中的数据
SELECT COUNT(studentname) from student; -- COUNT(字段)，会忽略所有的null的值
SELECT COUNT(*) from student; -- COUNT(*),不会忽略null
SELECT COUNT(1) from student; -- 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=============
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','15476876'),(2,'lisi','84546')

-- 加密
UPDATE testmd5 set pwd=MD5(pwd) where id = 1

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

-- 插入的时候加密
insert into testmd5 values(4,'xiaiomin',MD5('123456'))

-- 如何校验:将用户传递过来的密码，进行md5加密，然后对比加密后的值
select * from testmd5 where `name`='xiaomin' and pwd MD5('123456')

6、事务

6、1 什么是事务？

要么都成功，要么都失败

列如转账的过程就是事务

将一组sql放在一个批次中去执行

事务原则：ACID原则 原子性，一致性，隔离性，持久性 （脏读，幻读…）

原子性（Atomicity）

要么都成功，要么都失败

一致性（Consistency）

事务前后的数据完整性要保证一直，1000

持久性（Durability） – 事务提交

事务一旦提交则不可逆，被持久化到数据库中！

隔离性（Isolation）

事物的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其它的事务操作所干扰，事务之间要相互隔离。

隔离所导致的一些问题

脏读：

指一个事务读取了另一个事务未提交的数据。

不可重复度：

在一个事务内读取表中的某一行数据，多次读取结果不同。（这个不一定是错误，只是某些场合不对）

虚读(幻读)：

是指在一个事务内读取到了别的事务插入的数据，导致前后读取不一致。

推荐博客：https://www.cnblogs.com/techflow/p/12147933.html

6、2 实例

执行事务

-- =============事务==============

-- mysql 是默认开启事务自动提交的
SET autocommit = 0 -- 关闭
SET autocommit = 1 -- 开启（默认的）

-- 手动处理事务
set autocommit = 0 -- 关闭自动提交

-- 事务开启
START TRANSACTION -- 标记一个事务的开始，从这个之后的sql都在同一个事务内

INSERT XX
INSERT XX

-- 提交：持久化（成功！）
COMMIT
-- 回滚： 回到原来的样子（失败！）
RALLBACK

-- 事务结束
SET autocommit = 1 --开启自动提交

-- 了解
SAVEPOINT  保存点名 -- 设置一个事务的保存点
ROLLBACK TO SAVEPOINT 保存点名 -- 回滚到保存点
RELEASE SAVEPOINT 保存点名  -- 撤销保存点

模拟场景

-- 转账
CREATE DATABASE `shop`  CHARACTER SET utf8 COLLATE utf8_general_ci

USE shop

CREATE TABLE `acount` (
	`id` INT(3) NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(20) NOT NULL,
	`money` DECIMAL(9,2) NOT NULL,
	PRIMARY KEY (`id`)
)ENGINE = INNODB DEFAULT CHARSET=utf8

INSERT INTO `acount`(`name`,`money`)
VALUES ('用户1',2000.00),('用户2',10000.00)

-- 模拟转账

SET autocommit = 0; -- 关闭自动提交
START TRANSACTION -- 开启事务（一组事务）

UPDATE `acount` SET  `money` = `money`-500 WHERE `name` = '用户1'  -- 减500
UPDATE `acount` SET  `money` = `money`+500 WHERE `name` = '用户2'  -- 加500

COMMIT; -- 提交事务，就被持久化了
ROLLBACK; -- 回滚

SET autocommit = 1; -- 恢复默认值

7、索引

MySQL官方对索引的定义：索引（Index）是帮助MySQL高效获取数据的数据结构。提取句子主干，就可以得到索引的本质：索引是数据结构

7、1 索引的分类

在一个表中，主键索引只能有一个，唯一索引可以有多个

• 主键索引 (PRIMARY KEY)
  • 唯一标识，主键不可重复，只能有一个列作为主键
• 唯一索引 (UNIQUE KEY)
  • 避免重复的列出现，唯一索引可以重复，多个列都可以标识为 唯一索引
• 常规索引 (KEY/INDEX）
  • 默认的，index key 关键字来设置
• 全文索引 (FullText)
  • 在特定的数据库引擎下才有，MyISAM
  • 快速定位数据

基础语法：

-- 索引的使用
-- 1、在创建表的时候给字段增加索引
-- 2、创建完毕后，增加索引

-- 显示所有的所有信息

SHOW INDEX FROM student

-- 增加一个索引 (索引名) 列名
ALTER TABLE `student` ADD INDEX `name`(`name`)

-- EXPLAIN 分析sql执行的状况
EXPLAIN SELECT * FROM student  -- 非全文索引

EXPLAIN SELECT * FROM student WHERE MATCH(`name`) AGAINST('大') -- 全文索引

7、2 测试索引

CREATE TABLE `app_user`(
	`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(50) DEFAULT '' COMMENT'用户昵称',
	`email` VARCHAR(50) NOT NULL COMMENT '用户邮箱',
	`phone` VARCHAR(20) DEFAULT'' COMMENT '手机号',
	`gender` TINYINT(4) UNSIGNED DEFAULT '0' COMMENT '性别(0:男，1：女)',
	`password` VARCHAR(100) NOT NULL COMMENT '密码',
	`age` TINYINT(4) DEFAULT '0' COMMENT '年龄',
	`create_time` DATETIME DEFAULT NULL,
	`update_time` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COMMENT='app用户表';


-- 插入100万条数据
DELIMITER $$ --写函数之前必写
CREATE FUNCTION mock_date()
RETURNS INT
BEGIN
   DECLARE num INT DEFAULT 1000000;
   DECLARE i INT DEFAULT 0;
   
   WHILE i<num DO
	-- 插入语句
	INSERT INTO app_user(`name`,`email`,`phone`,`gender`,`password`,`age`)
	VALUES(CONCAT('用户',i),'105489@qq.com',CONCAT('1584',i),0,'58468486',15);
	SET i = i+1;
   END WHILE;
   RETURN i;
END;

INSERT INTO app_user(`name`,`email`,`phone`,`gender`,`password`,`age`)

VALUES(CONCAT('用户'),'105489@qq.com',CONCAT('1584'),0,'58468486',15)


SELECT mock_date(); -- 查询数据总数

SELECT * FROM app_user WHERE `name` = '用户9999' -- 执行耗时   : 0.897 sec

EXPLAIN SELECT * FROM app_user WHERE `name` = '用户9999' -- 查询sql执行过程

-- 后期加入索引
-- id _表名 _字段名
-- create index 索引名 on 表（字段）
CREATE INDEX id_app_user_name ON app_user(`name`); -- 加入索引后  执行耗时   : 0.002 sec

索引在小数据量的时候，用处不大， 但是在大数据的时候，区别是分明显。

7、3 索引原则

• 索引不是越多越好
• 不要对进程变动数据加索引
• 小数据量的表不需要索引
• 索引一般加在常用来查询的字段上！

参考博客：http://blog.codinglabs.org/articles/theory-of-mysql-index.html

索引的数据结构

Hash类型的索引

Btree: InnoDB的默认数据结构

8、权限管理和备份

8、1 用户管理

SQL命令操作

用户表：mysql.user

本质：读这张表增删改查

-- 创建用户  create user 用户名 IDENTIFIED BY '密码'
create user tianyi IDENTIFIED BY '123456'

-- 修改密码（修改当前用户密码）
SET PASSWORD = PASSWORD('8492849')

-- 修改密码（修改制定用户密码）
SET PASSWORD FOR tianyi2 = PASSWORD('12121212')

ALTER user 'tianyi2'@'%' IDENTIFIED by '121212'

-- 重命名   RENAME user 原名 to 新名
RENAME user tianyi to tianyi2

-- 用不授权 ALL PRIVILEGES 全部权限，库 表
-- ALL PRIVILEGES 除了给别人授权，其他都能干
GRANT ALL PRIVILEGES on *.* TO tianyi2

-- 查看权限
SHOW GRANTS FOR tianyi2 -- 查看指定用户的权限 
SHOW GRANTS FOR root@localhost 

-- 撤销权限 REVOKE那些权限，在那个库撤销，给谁撤销 
REVOKE ALL PRIVILEGES ON *.* FROM tianyi2 

-- 删除用户 
DROP user tianyi2

8、2 MySQL备份

为什么要备份：

• 保证重要的数据不丢失
• 数据转移

MySQL 数据库备份的方式：

• 直接拷贝物理文件
• 在SQLYON可视化工具中手动导出
• 使用命令导出 mysqldump 命令行使用

# 以下操作在命令行中执行

# 备份导出一个表
#  mysqldump -h 主机名 -u 用户名 -p 密码 数据库 表名 >物理磁盘位置 /文件名
mysqldump -hlocalhost -uroot -p12345678 mybatis student >/Users/dada/Desktop/a.sql

# 备份导出多个个表
# mysqldump -h 主机名 -u 用户名 -p 密码 数据库 表名1 表名2 >物理磁盘位置 /文件名
mysqldump -hlocalhost -uroot -p12345678 mybatis student teacher >/Users/dada/Desktop/a.sql 

# 备份导出数据库
# mysqldump -h 主机名 -u 用户名 -p 密码 数据库 >物理磁盘位置 /文件名
mysqldump -hlocalhost -uroot -p12345678 mybatis >/Users/dada/Desktop/a.sql 

# 导入备份文件
# 命令行登录的情况下，切换到指定数据库
# source 备份文件
source /Users/dada/Desktop/a.sql 

# 没登录的情况下
mysql -u用户名 -p密码 库名< 备份文件

假设你要备份数据，防止数据丢失。

把数据库给别人！sql文件给别人就可以！

9、数据库的规约，三大范式

9、1 为什么需要设计

当数据库比较复杂的时候，我们就需要设计了

糟糕的数据库设计：

• 数据冗余，浪费空间
• 数据库插入和删除都会麻烦、异常【屏蔽使用物理外键】
• 程序的性能差

良好的数据库设计：

• 节省内存空间
• 保证数据库的完整性
• 方便我们开发系统

软件开发中，关于数据库的设计

• 分析需求：分析业务和需要处理的数据库的需求
• 概要设计：设计关系图 E-R图

设计数据库的步骤：（个人博客）

• 收集信息，分析需求
  • 用户表（用户登录注销，用户的个人信息，写博客，创建分类）
  • 分类表（文章分类，谁创建的）
  • 文章表（文章的信息）
  • 友链表（友链信息）
  • 自定义表（系统信息，某个关键字，或则一些主字段）key : value
• 标识实体（把需求落地到每个字段）
• 标识实体之间的关系（E-R图）
  • 写博客：User ->blog
  • 创建分类：User ->category
  • 关注：User ->user
  • 友链：links
  • 评论：user-user-blog

9、2 三大范式

为什么需要数据规范化？

• 信息重复
• 更新异常
• 插入异常
  • 无法显示信息
• 删除异常
  • 丢失有效的信息

三大范式

第一范式（1NF）

原子性：保证每一列不可再分

第二范式（2NF）

前提：满足第一范式

每张表只描述一件事情

第三范式（3NF）

前提：满足第一范式和第二范式

第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。

（规范数据库的设计）

规范性和性能的问题

关联查询的表不得超过三张表

• 考虑商业化的需求和目标，（成本，用户体验！）数据库的性能更加重要
• 在规范性能的问题的时候，需要适当地考虑一下 规范性！
• 故意给某些表增加一些冗余的字段（从多表查询变为单表查询）
• 故意增加一些计算列（从大数据量降低为小数据量的查询：索引）

参考：https://www.cnblogs.com/wsg25/p/9615100.html

10、JDBC

SQL注入的问题

sql存在漏洞，会被攻击导致数据泄露。原理：sql会被拼接 or ；

-- 一般通过这样的方式来读取所有数据库表中的信息
SELECT fieldlist FROM table WHERE field = 'anything' OR 'x'='x';

参考：https://www.cnblogs.com/myseries/p/10821372.html

数据库连接池

数据库连接 – 执行完毕 – 释放

连接–释放 十分浪费系统资源

池化技术：准备一些预先的资源，过来就连接预先准备好的

------ 开门 --业务员：等待 – 服务 –

常用连接数 10个

最小连接数：10

最多连接数：100 业务最高承载上限