MySQL通俗易懂知识点_$query = 'update `student` set `age`=26 where `id`

MySQL

概念

DB: DateBase 数据库，储存数据
DBMS：数据库管理系统，维护和获取数据

关系型数据库（SQL）
MySQL,Oracle,Sql Server,DB2，SQLIte…
通过表和表之间，行与列之间进行数据的存储
百度百科：关系型数据库，是指采用了关系模型来组织数据的数据库，其以行和列的形式存储数据，以便于用户理解，关系型数据库这一系列的行和列被称为表，一组表组成了数据库。用户通过查询来检索数据库中的数据，而查询是一个用于限定数据库中某些区域的执行代码。关系模型可以简单理解为二维表格模型，而一个关系型数据库就是由二维表及其之间的关系组成的一个数据组织。

非关系型数据库（NoSQL）Not Only
Redis,MogoDB…
非关系型数据库，对象存储，通过对象自身的属性来决定

基本的命令行操作

mysql -uroot -p123456 --链接数据库 或者输完-p后敲一下回车，再输入密码，******

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; -- 查看表中的信息 student为表名
create database westos; --创建一个数据库
exit; --退出

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

操作数据库（了解

MySQL关键字不区分大小写
[ ]中内容可选

1. 创建数据库

CREATE DATABASE [IF NOT EXISTS] westos;
1

2. 删除数据库

DROP DATABASE [IF EXISTS] westos
1

3. 使用数据库

USE ‘school’ --``这个符号可以用来修饰我们的表名或特殊字符
1

4. 查看所有数据库

SHOW DATABASES --
1

列的数据类型

1. 数值
   tinyint 1个字节
   smallint 2个字节
   mudiumint 3个字节
   int 4个字节
   bigint 8个字节
   float 4个字节
   double 8个字节
   decimal 字符串形式的浮点数 用于金融计算使用

2. 字符串
   char 字符串固定大小的 0~255
   varchar 可变字符串 0~65535 相当于String类型
   tinytest 微型文本 2^8 - 1
   test 文本串 2^16 - 1 保存大文本

3. 时间和日期
   data YYYY-MM-DD，日期格式
   time HH:mm:ss 时间格式
   datetime YYYY-MM-DD HH:mm:ss 最常用的时间格式
   timestap 时间戳，初始时间为1970.1.1到现在的毫秒数
   year 年份表示

4. null
   没有值
   不要运用NULL进行运算

数据库的字段属性（重要

1. Unsigned
   无符号的整数，声明了该列不能声明为负数
2. zerofill
   0填充，不足的位数自动写零
3. 自增
   通常理解为自动在上一条的基础上 + 1
   通常用来设计唯一的主键 ~index，必须是整数类型
   可以自定义设计主键自增的起始值和步长
4. 非空 not null
   设置为非空，不给初始值会报错
5. 默认
   初始默认值

创建数据库表

CREATE TABLE IF NOT EXISTS `student`(
`name` VARCHAR(5) NOT NULL DEFAULT'匿名' COMMENT'姓名',
`age` INT(3) NOT NULL DEFAULT'0' COMMENT'年龄',
`id` INT(6) NOT NULL AUTO_INCREMENT  COMMENT'学号',
`address` VARCHAR(33) DEFAULT NULL COMMENT'地址',
`birthday` DATETIME DEFAULT NULL COMMENT'生日',
PRIMARY KEY(`id`)
)ENGINE = INNODB DEFAULT CHARSET=utf8

--出现的问题 default charset = utf8 没有-
--varchar类型的引用时英文字符单引号
--所有的语句后面加,最后一句不用加
--primary key 主键，一般一个表只有唯一的主键
--注意写注释
1
2
3
4
5
6
7
8
9
10
11
12
13
14

sql语法中什么类型的值需要用单引号‘’修饰？
1）char varchar text blob date datetime boolean 要加单引号
2）int float double 不用
3）时间型数据也需要单引号修饰。

常用命令

show create database school --显示创建数据库的语句
show create table student --显示创建student数据表的语句
desc student --显示表的结构
1
2
3

MyISAM和InnoDB区别（了解

MySAM 早些年使用，节省空间，速度较快
InnoDB 默认使用，安全性高，能进行事物的处理，多表多用户操作

在物理空间的位置

所有数据库文件都在data目录下，一个文件夹对应一个数据库，本质是文件的存储
InnoDB在数据库表中只有一个*.frm文件，以及上级目录下的ibdata1文件

MyISAM对应，*.frm , *.MYD(数据文件，data） , *.MYI(索引文件，index）

另：字符集编码utf-8，若不设置，不支持中文

修改删除表

ALTER TABLE student RENAME AS student1 -- 修改表名 alter table 旧表名 rename as 新表名

ALTER TABLE student1 ADD gpa INT(3) -- 增加字段 alter table 表名 add 字段名 类型

ALTER TABLE student1 MODIFY age VARCHAR(3) -- 修改数据类型 alter table 表名 modify 字段名 类型

ALTER TABLE student1 CHANGE age age1 INT(3) -- 修改字段名，同时可以改变类型 alter table 表名 change 旧字段名 新字段名 类型

-- modify用于修改类型约束
--change用于改变名字也可以改变约束 但是要同时出新的名字和类型

ALTER TABLE student DROP gpa -- 删除字段 alter 表名 drop 字段名

DROP TABLE [if exists]student -- 删除表
1
2
3
4
5
6
7
8
9
10
11
12
13
14

总结：
所有创建和删除尽可能加上 if exists 防止报错
字段注意用上 ``

MySQL数据管理

外键（看看就行

相当于学生的年级对年级数据库中数据的引用（有点绕嘴。。。

CREATE TABLE `grade` (
`gradeid` VARCHAR(33)    COMMENT'年级'
)ENGINE=INNODB  DEFAULT CHARSET = utf8

ALTER TABLE `student` ADD `gradeid` VARCHAR(33) 

ALTER TABLE `student` 
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY(`gradeid`) REFERENCES `grade`(`gradeid`);
1
2
3
4
5
6
7
8

以上操作时物理外键，数据库级别的外键，不建议使用，因为数据过多会产生麻烦

DML语言（全都记住！！！

1. 插入

INSERT INTO `student` (`name`) VALUE('小王'),('小贾') -- 不同行的值用括号括起来，并且用逗号隔开

INSERT INTO `student`(`age`) VALUE(22),(21) -- 这样插入不会在小王和小贾的值中做修改，而是产生新的两行

INSERT INTO `student`(`name`,`age`,`address`,`gradeid`) VALUE('小王',22,'天津市','机电171')

INSERT INTO `student`(`name`,`age`,`id`,`address`,`birthday`,`gradeid`) VALUE('小张',33,170788,'深圳市','1888-11-11 22:33:33','会计162')

-- insert into 表名(类型) value(一一对应的值)
-- 各个类型和对应的值用逗号隔开
-- 若你不想写类型，插值要全部给值进行插入，且要按照表格的顺序
INSERT INTO `student` VALUE('小张',33,170788,'深圳市','1888-11-11 22:33:33','会计162')
1
2
3
4
5
6
7
8
9
10
11
12

2. 修改

UPDATE `student` SET `age`=22 WHERE id = 1

UPDATE `student` SET `address`='天津市',`birthday`='1111-11-11 11:33:33',`gradeid`='大三' WHERE id = 1

-- update 表名 set 要求改的属性（逗号隔开） [where条件]

-- 注：<> 是 ！= 不等于号
UPDATE `student` SET `gradeid`='二十一世纪' WHERE id BETWEEN 1 AND 5
-- between ... and ... 是全闭区间

-- 多个条件定位 无数量上限
UPDATE `student` SET `address` = '中国' WHERE `gradeid` = '二十一世纪' AND id = 1
-- and = &&

UPDATE `student` SET `name` = 'dream' WHERE `gradeid`='二十世纪' OR id < 5
-- or = ||

UPDATE `student` SET `name`='小王' 
-- 不写条件就会全部修改，赶紧跑路吧。。。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

3. 删除

-- delete from 表名 where 条件 不写条件全删，赶紧跑路。。。
DELETE FROM `student` WHERE id = 1
DELETE FROM `student` WHERE id > 3 AND `gradeid`='二十一世纪'
1
2
3

-- 完全清空一个数据库表，但是表的结构和索引约束不变
truncate 'student'
1
2

delete 和 truncate 的区别
相同点：都能删除数据
不同点：truncate 重新设置自增列，计数器会归零。
truncate不会影响事务

delete删除的问题（了解
重启数据库后
InnoDB 自增列会从1开始（存在内存中，断电即失
MyISAM 继续从上一个自增开始（存在文件中，不会丢失

DQL查询数据（最重点

Data Query Language:数据查询语言
数据库中最核心的语言

1. 基础数据

/*创建顾客表*/
CREATE TABLE IF NOT EXISTS customer(
     c_id CHAR(6) PRIMARY KEY,
     NAME VARCHAR(30)NOT NULL,
     location VARCHAR(30),
     salary DECIMAL(8,2)

)ENGINE = INNODB DEFAULT CHARSET = utf8;
/*创建银行表*/
CREATE TABLE IF NOT EXISTS bank(
    b_id CHAR(5) PRIMARY KEY,
    bank_name CHAR(30) NOT NULL
)ENGINE = INNODB DEFAULT CHARSET = utf8;
/*创建存款表(注意外键的代码使用)*/
CREATE TABLE IF NOT EXISTS deposite(
    d_id INT(10) AUTO_INCREMENT PRIMARY KEY,
    c_id CHAR(6),
    b_id CHAR(5),
    dep_date DATE,
    amount DECIMAL(8,2),
    CONSTRAINT FK_c_id FOREIGN KEY(c_id) REFERENCES customer(c_id)
)ENGINE = INNODB DEFAULT CHARSET = utf8;
/*插入数据*/
INSERT INTO customer 
VALUES('101001','孙杨','广州',1234),
('101002','郭海','南京',3526),
('101003','卢江','苏州',6892),
('101004','郭惠','济南',3492);
INSERT INTO bank
VALUES('B0001','工商银行'),
('B0002','建设银行'),
('B0003','中国银行');
INSERT INTO bank
VALUES('B0004','农业银行');
INSERT INTO deposite
VALUES(1,'101001','B0001','2011-04-05',42526),
(2,'101002','B0003','2012-07-15',66500),
(3,'101003','B0002','2010-11-24',42366),
(4,'101004','B0004','2008-03-31',62362),
(5,'101001','B0003','2002-02-07',56346),
(6,'101002','B0001','2004-09-23',353626),
(7,'101003','B0004','2003-12-14',36236),
(8,'101004','B0002','2007-04-21',26267),
(9,'101001','B0002','2011-02-11',435456),
(10,'101002','B0004','2012-05-13',234626),
(11,'101003','B0003','2001-01-24',26243),
(12,'101004','B0001','2009-08-23',45671);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47

2. 简单查询

-- 查询表中所有的数据 
-- select * from 表名
SELECT * FROM `customer`

-- 查询指定列的数据
-- select 列名（用逗号隔开） from 表名
SELECT `name`,`location` FROM  `customer`

-- 将查询出来的列名字改个好看的 用as 同样 表名也可以改
SELECT `name` AS 姓名,`location` AS 家乡 FROM  `customer` AS c

-- 将姓名加上一些修饰语或者前缀 利用concat
SELECT CONCAT('姓名：',`name`) AS 有前缀的姓名 FROM `customer`
SELECT CONCAT('姓名：',NAME) AS 有前缀的姓名,CONCAT('家','乡','是',`location`) AS 家乡 FROM `customer` -- 多点字符串也行

-- 查询版本
SELECT VERSION()

-- 用来简单加减乘除计算
SELECT 3*3+3-1 AS 结果

-- 查询自增的步长
SELECT @@auto_increment_increment 

-- 查询出来的值也可进行修改 将存款加1
SELECT `amount`+1 AS '存款+1' FROM`deposite`
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

3. 去重distinct

-- 筛选出所有有存款的数据
select * from deposite

-- 选出b_id列的数据
select `b_id` from deposite

-- 取出重复的数据,重复的数据只显示一条
select distinct `b_id` from deposite
1
2
3
4
5
6
7
8

4. where 条件

-- and = && ,or = || 
SELECT *FROM `deposite` WHERE `amount` BETWEEN 50000 AND 100000

SELECT * FROM `deposite` WHERE `amount`<50000 || `amount` > 100000

SELECT * FROM `deposite` WHERE `amount`> 100000 && `amount` <300000

SELECT * FROM `deposite` WHERE `amount` >= 330000
1
2
3
4
5
6
7
8

5. 模糊查询（重点

本质上也是比较运算符的运用

数据库信息由于涉及隐私，不便公布，若仅供学习请私聊索取
1

like 相关
-- 查询表中 姓王两个字的人
SELECT `id`,`name`,`birthday` FROM`class` WHERE `name` LIKE '王_'
-- 查询表中 姓王的人
SELECT`id`,`name`,`sex`,`QQ` FROM `class` WHERE `name` LIKE '王%'
-- 查询表中带有明字的人
SELECT `id`,`name`,`sex` FROM `class` WHERE `name` LIKE '%明%'
-- 查询地点中包含城市的人
SELECT`name` FROM `class` WHERE `location` LIKE '%城市'
-- 注： _表示一个字符 %表示任意个字符0~n 这两个符号只在like这里用
1
2
3
4
5
6
7
8
9
10

in 相关，用于查询具体的值
-- 查询位置在'小城镇','中小城市'的人
SELECT `name`,`id`,`location` FROM `class` WHERE `location` IN ('小城镇','中小城市')
-- 查询位置在'农村'的人
SELECT `name`,`location` FROM `class` WHERE `location` IN('农村')
 -- 查询学号为170788,170777,170799,170766的人
SELECT `name`,`id` FROM `class` WHERE `id` IN (170788,170777,170799,170766) 
-- 查询学号在170780与170790之间且位置在农村的同学  多个条件注意运用逻辑链接符
SELECT `name`,`location`,`id` FROM`class` WHERE `id` BETWEEN 170780 AND 170790 AND `location` IN ('农村')
1
2
3
4
5
6
7
8
9

not null 相关
-- 查询生日不为空的且在1999-1-1之后出生的同学 注：日期写上单引号
SELECT `name`,`birthday` FROM`class` WHERE `birthday` IS NOT NULL AND `birthday`>'1999-1-1'
1
2
3

6. 联表查询（重难点

JOIN

-- 左表查询本班学生重修情况 会返回所有左表中的数据也就是class中的
-- 并将重修情况对应上class中的同学
-- on多个条件用and连接不用逗号
SELECT c.`id`,c.`name`,`KCM` 
FROM`class` AS c
LEFT JOIN `chongxiu` AS cx
ON c.`id` = cx.`id` AND c.`name` = cx.`name`

-- 若将left改为right，则为右表查询
-- 此时会将右表中数据，也就是chongxiu中的数据全部返回
-- 并且对应上class中的id和name
SELECT c.`id`,c.`name`,`KCM` 
FROM`class` AS c
RIGHT JOIN `chongxiu` AS cx
ON c.`id` = cx.`id` AND c.`name` = cx.`name`

-- Inner join查询
-- 返回的是两者的交集，即返回class中chongxiu的同学
SELECT c.`id`,c.`name`,`KCM` 
FROM`class` AS c
INNER JOIN `chongxiu` AS cx
ON c.`id` = cx.`id` AND c.`name` = cx.`name`


-- 三联表查询
-- 查询同学的成绩，左表查询
-- 再inner join chongxiu表中，返回本班同学的重修科目和gpa
SELECT c.`name`,`location`,c.`id`,`gpa`,`KCM`
FROM `class` AS c
LEFT JOIN `bigclass` AS bc
ON c.`id` = bc.`id` AND c.`name` = bc.`name`
INNER JOIN `chongxiu` AS cx
ON c.`id` = cx.`id` AND c.`name` = cx.`name`

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34

练习
-- 左表查询以class为基准，返回gpa>3.3的同学
SELECT c.`name`,`location`,c.`id`,`gpa` 
FROM `class` AS c
LEFT JOIN `bigclass` AS bc
ON c.`id` = bc.`id` AND c.`name` = bc.`name`
WHERE gpa > 3.3
1
2
3
4
5
6
7

总结：

1. 先要明确查哪些数据，select…
2. 要从哪个表中查 from 表名 xxxx join 连接的表名 on 交叉的条件，也就是都有的属性信息，不要把on丢了
3. 若有多表查询，先查询两个表，再慢慢增加

自连接（了解

自己的表和自己的表连接，核心：一张表拆为两张一模一样的表（简称我连我自己。。。后文有自我理解，我觉得稍容易接受一点儿

基础数据
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技术');
1
2
3
4
5
6
7
8
9
10

自连接前的表格

父类

子类

操作代码
SELECT a.`categoryname` AS '父栏目',b.`categoryname` AS '子栏目' -- 两列数据，一列为父栏目，一列为子栏目，它们的数据源均为categoryname
FROM `category` AS a ,`category` AS b -- 在一个表中查，自连接
WHERE a.`categoryid` = b.`pid` -- 父类自身的id = 子类的父id
1
2
3
4

自连接后

我自己的理解

-- 再加上一条categoryid列，便于理解，此时这样id为父类id
SELECT a.`categoryname` AS '父栏目',b.`categoryname` AS '子栏目',a.`categoryid`
FROM `category` AS a ,`category` AS b 
-- 这一步，相当于，把category这个表变成两个一模一样的表
-- 也就是前文所说的拆成两个表，其本质上也就是在category表拿不同的数据而已
-- a拿出父类数据，也就是父类的名字，b拿出子类数据，也就是子类名字
WHERE a.`categoryid` = b.`pid`
-- 这里是它们的对应关系，拿出数据后你怎么让关联起来？
-- a是父类，有自己的id，b是子类有父类id，有自己的id
-- 让子类的父类id 等于父类的id 就是让儿子找到自己的爸爸 这样就联系起来了
-- 那么这个where条件就是，软件开发：“我爸爸是3。” 这里有2，3，5的父亲，
-- 让3父亲与软件开发关联起来，就相当于 子类的父id = 父id
1
2
3
4
5
6
7
8
9
10
11
12

总结：自连接有局限，仅限于在同一列拆分出数据，分成不同栏目

7. 分页和排序

排序

• 升序 ASC 降序 DESC

升序排列
SELECT `id`,`name`,`class` FROM `bigclass`
ORDER BY `gpa` ASC

降序排列
SELECT c.`id`,c.`name`,`gpa` 
FROM `class` AS c
LEFT JOIN `bigclass` AS b
ON c.`id` = b.`id` AND c.`name` = b.`name`
ORDER BY `gpa` DESC
1
2
3
4
5
6
7
8
9
10

分页

• Limit x,y
• x表示分页起始的数据，y表示为每页装的数据数量
• 第一页为 0-4
• 第二页为 5-9
• 第n 页为 （n-1）*页面大小 - （n-1）*页面大小 +4

-- 从第一个数据开始（第一个数据的索引为0），每页装5个
SELECT c.`id`,c.`name`,`gpa` 
FROM `class` AS c
LEFT JOIN `bigclass` AS b
ON c.`id` = b.`id` AND c.`name` = b.`name`
ORDER BY `gpa` DESC
LIMIT 0,5

-- 查出年级前10名且绩点大于3
SELECT`id`,`name`,`class`,`gpa`
FROM`bigclass`
WHERE `gpa`>3 
ORDER BY `gpa` DESC
LIMIT 0,9
1
2
3
4
5
6
7
8
9
10
11
12
13
14

8. 子查询（从里向外

• 子查询的好处，不用使用JOIN依赖连表
• 局限：所查询的表中的数据，需要依赖外部条件的时候，表中的数据必须和外部表的数据有所联系
• 也就是说，class中要限定gpa条件，用id和bigclass沟通，那么bigclass中必须有id，也就是说，两者必须有公共的属性

-- 查询班级中绩点在3以上的同学
SELECT `id`,`name`
FROM`class`
WHERE `id` IN( 
SELECT`id` FROM `bigclass` WHERE `gpa`>3)
1
2
3
4
5

9. 分组

-- 通过location来分组，来统计来自不同地区的数量，用having来划分条件
-- having是用于修饰分组的次级条件
SELECT COUNT(`name`),`location` 
FROM  `class`
GROUP BY `location`
HAVING `location` IN('中小城市','农村')

-- 通过性别来分组，并统计各组人数
SELECT `sex`,COUNT(`sex`)
FROM`class`
GROUP BY `sex`
1
2
3
4
5
6
7
8
9
10
11

10. 总结

顺序很重要
SELECT 去重 要查询的字段 from表名
xxx JOIN 要连接的表 ON 等值条件
WHERE 条件
GROUP BY 通过哪些字段分组
HAVING 条件和where一样，只是位置不同
ORDER BY 排序 DESC ASC
LIMIT 分页起始数据，当前页的数据数量
1
2
3
4
5
6
7
8

MySQL函数

1. 常用函数（并不常用

• 数学运算
• select abs(-3) – 返回绝对值
• select ceiling(3.3) – 向上取整
• select floor(3.3) – 向下取整
• select rand() – 返回0~1之间的随机数
• select sign() – 判断数的符号 0为0 负数为-1 整数为1
• 字符串
• select char_length(‘字符串’) – 返回字符串长度
• select concat(‘w’,‘ang’) – 拼接字符串
• select lower(‘ABCD’） – 返回小写
• select upper(‘abcd’) – 返回大写
• select instr(‘wang’,‘a’) – 返回a第一次出现的位置
• select replace(‘wang’,‘a’,‘y’） – 把a替换为y
• select substr(‘wang’,1,2) – 从第1个字符开始截取，截2个
• select reverse(‘wang’) – 反转gnaw
• 时间和日期函数
• select current_data() – 获取当前日期
• select now() --获取当前时间
• select sysdate() – 获取系统时间
• 系统
• select user() – 获取当前用户
• select version() – 获取当前版本

2. 聚合函数（常用

1. select count()

SELECT COUNT(1) FROM`class` -- 本质上返回所有行数 于count（*）区别不大，不会忽略null
SELECT COUNT(*) FROM`class` -- 本质上返会所有行数
SELECT COUNT(`id`) FROM `class` -- 查询固定列有内容的行数（会忽略null
1
2
3

2. select sum() select avg() select max() select min()

SELECT SUM(`id`)FROM`class`-- 求该列的总和
SELECT AVG(`id`)FROM class -- 求该列的平均数
SELECT MAX(`id`)FROM class -- 查该列的最大值
SELECT MIN(`id`)FROM class -- 查该列的最小值
1
2
3
4

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

• MD5：MD5信息摘要算法（英语：MD5 Message-Digest Algorithm），一种被广泛使用的密码散列函数，可以产生出一个128位（16字节）的散列值（hash value），用于确保信息传输完整一致。MD5算法无法防止碰撞（collision），因此不适用于安全性认证
• MD5不可逆，具体值的MD5是一样德

-- 把范围内的QQ变成MD5散列码
UPDATE `class` SET `QQ`=MD5(`QQ`) WHERE `id`<170788 
1
2

事务

1. 什么是事务？(理解

• 要么都成功，要么都失败

A给B转100元钱
要么转成功要么转失败，
不能出现A转100元而B没有收到的情况
1
2
3

• 事务原则：ACID （原子性，一致性，隔离性，持久性），（脏读，幻读…)
• 原子性：A给B转钱，要么成功要么失败，不可能只成功一件事
• 一致性：A,B两人的钱总和不变，不可能出现越转总和越大的情况
• 持久性：若事务没有提交，恢复到原状（转钱的时候，服务器宕机了）；若事务已经提交，则为完成事务后的状态（转完钱的时候，服务器宕机了）
• 隔离性：A给B转钱和C给B转钱不会互相影响

2.执行事务

创建测试数据
CREATE DATABASE shop CHARACTER SET utf8 COLLATE utf8_general_ci

USE shop

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

INSERT INTO`account`(`name`,`money`) VALUE('A',2000.00),('B',3000.00)
1
2
3
4
5
6
7
8
9
10
11
12
13

 模拟转账事务，一行一行执行

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

UPDATE `account` SET `money` = `money` - 300 WHERE `id` = 1 -- A给B转钱300
UPDATE `account` SET `money` = `money` + 300 WHERE `id` = 2

COMMIT; -- 提交事务，事务提交成功，就被持久化了
ROLLBACK; -- 回滚，滚到该事务提交前的状态

SET autocommit = 1; -- 恢复默认自动提交的状态
1
2
3
4
5
6
7
8
9
10
11
12

3. 总结

SET autocommit = 1; -- 开启自动提交，这也是数据库的默认状态
SET autocommit = 0; -- 关闭默认提交

COMMIT -- 提交，提交之后数据就持久化了

ROLLBACK -- 回滚，回到执行事务前的样子

SAVEPOINT -- 设置保存点 了解即可
1
2
3
4
5
6
7
8

索引

• MySQL官方对索引的定义：索引是帮助MySQL高效获取数据的数据结构。

1. 索引的分类

• 主键索引（PRIMARY KEY) :唯一的标识，主键不可重复，只能由一个列作为主键。
• 唯一索引（UNIQE KEY) :避免重复的列出现，唯一索引可以重复，多个列都可以标识为唯一索引
• 常规索引（KEY / INDEX) :默认的，用index或key来设置
• 全文索引（FullTest) :快速定位数据

2. 基础语法

SHOW INDEX FROM 表名 -- 显示所有索引

ALTER TABLE school.`class` ADD FULLTEXT INDEX `studentName`(`name`) -- 添加全文索引 索引名（列名）
1
2
3

3. 测试索引

创建表格
CREATE TABLE `app_user` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(50) DEFAULT '',
`eamil` varchar(50) NOT NULL,
`phone` varchar(20) DEFAULT '',
`gender` tinyint(4) unsigned DEFAULT '0',
`password` varchar(100) NOT NULL DEFAULT '',
`age` tinyint(4) DEFAULT NULL,
`create_time` datetime DEFAULT CURRENT_TIMESTAMP,
`update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1
2
3
4
5
6
7
8
9
10
11
12
13

创建一百万条数据
DELIMITER $$ -- 写函数之前必须要写，标志
CREATE FUNCTION mock_data ()
RETURNS INT
BEGIN
DECLARE num INT DEFAULT 1000000;
DECLARE i INT DEFAULT 0;
WHILE i<num DO
INSERT INTO `app_user`(`name`,`eamil`,`phone`,`gender`)VALUES(CONCAT('用户',i),'19224305@qq.com','123456789',FLOOR(RAND()*2));
SET i=i+1;
END WHILE;
RETURN i;
END;

SELECT mock_data() -- 执行此函数 生成一百万条数据
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

测试
SELECT * FROM`app_user` WHERE `name` = '用户9999' -- 耗时0.404s

 -- 创建索引 create index 索引名 on 表名（字段）
CREATE INDEX id_app_user_name ON `app_user`(`name`)

SELECT * FROM`app_user` WHERE `name` = '用户9999' -- 耗时0s 提高效率
1
2
3
4
5
6
7

• 总结：索引在小数据量的时候，用处不大，在数据大的时候，明显提升效率

4. 索引原则

• 索引不是越多越好
• 不要对经常变动的数据加索引
• 小数据量的表不需要加索引
• 索引一般加在常用来查询的字段上
• InnoDB:默认数据结构 Btree
• 索引是Hash类型

权限管理

1. 用户管理

SQL用户管理命令行操作

-- 创建名为 xiaowang 密码为 123456 的用户
CREATE USER xiaowang IDENTIFIED BY '123456' 

-- 设置当前账户密码为 123456
SET PASSWORD = PASSWORD('123456') 

-- 设置小王账户密码为123456789
SET PASSWORD FOR xiaowang = PASSWORD('123456789') 

-- 给xiaowang用户重命名
RENAME USER xiaowang TO dawang

-- 授予用户所有的权限 库.表 但是没有赋予他能给别人授权的权限
GRANT ALL PRIVILEGES ON *.* TO xiaowang

-- 查询权限
SHOW GRANTS FOR xiaowang
SHOW GRANTS FOR root@localhost -- 需要加上@localhost

-- 撤销权限
REVOKE ALL PRIVILEGES ON *.* FROM xiaowang

-- 删除用户
DROP USER xiaowang
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

2. MySQL备份

• MySQL数据库备份的方式

1. 直接拷贝硬盘物理文件
2. 在SQLyog等可视化工具中手动导出，右键想导出的表或库，选择备份或导出
3. 使用 命令行导出 mysqldump

mysqldump -hlocalhost(本机） -u用户名 -p密码 库 表1 表2... >硬盘位置具体文件
mysqldump -hlocalhost -uroot -p123456 school class >d:\a.sql
1
2

4. 使用命令行导入

在登陆的情况下
mysql -uroot -p123456 -- 登录
use school -- 选择库
source d:/a.sql -- 选择导入的文件，并导入

在不登陆的情况下，<为导入
mysql -u用户名 -p密码 库名<文件位置
1
2
3
4
5
6
7

规范数据库设计

1. 三大范式（了解

• 第一范式
  

• 第二范式
  

• 第三范式
  
  总结：第一范式的关键词是列的原子性》第二范式的关键词是不能包含部分依赖》第三范式的关键词是不能包含传递依赖。

• 性能和规范性的问题

1. 阿里有规定查询的表不能联表查询超过3张
2. 考虑商业化的需求和目标，数据库的性能更加重要
3. 规律性需要适当考虑
4. 有时会故意增加冗余字段（从多表查询变为单表查询），故意增加一些计算列，以提高用户体验

JDBC（重点

1. JDBC是啥？

• Java数据库连接，（Java Database Connectivity，简称JDBC）是Java语言中用来规范客户端程序如何来访问数据库的应用程序接口，提供了诸如查询和更新数据库中数据的方法。JDBC也是Sun Microsystems的商标。我们通常说的JDBC是面向关系型数据库的。

2. 创建一个JDBC程序

创建数据库，并导入数据
CREATE DATABASE jdbcStudy CHARACTER SET utf8 COLLATE utf8_general_ci;

USE jdbcStudy;

CREATE TABLE users(
	id INT PRIMARY KEY,
	NAME VARCHAR(40),
	PASSWORD VARCHAR(40),
	email VARCHAR(60),
	birthday DATE
);

INSERT INTO users(id,NAME,PASSWORD,email,birthday)
VALUE(1,'zhangsan','123456','zs@sina.com','1980-12-04'),
(2,'lisi','123456','ls@sina.com','1998-12-12'),
(3,'wangwu','123456','ww@sina.com','1998-12-23');
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

1. 创建一个项目
2. 导入数据库驱动（导包切莫忘记Add as library
   
3. 编写测试代码

import java.sql.*;

public class jdbcFirst {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        // 1.加载驱动
        Class.forName("com.mysql.jdbc.Driver"); // 固定写法

        // 2.用户信息和url
        String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8";
        String username = "root";
        String password = "123456";

        // 3.连接成功，数据库对象,Connection代表数据库
        Connection connection = DriverManager.getConnection(url,username,password);

        // 4.执行SQL对象，statement执行sql对象
        Statement statement = connection.createStatement();

        // 5.用SQL对象去执行SQL，可能存在结果，返回结果
        String sql = "SELECT * FROM users";

        ResultSet resultSet = statement.executeQuery(sql);

        while (resultSet.next()){
            System.out.println("id=" + resultSet.getObject("id"));
            System.out.println("name=" + resultSet.getObject("name"));
            System.out.println("birthday=" + resultSet.getObject("birthday"));
            System.out.println("===");
        }
        // 6.释放连接
        resultSet.close();
        statement.close();
        connection.close();
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

总结：

1. 加载驱动
   Class.forName(“com.mysql.jdbc.Driver”); // 固定写法
2. String url = “jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8”; //mysql 默认端口号为3306，url格式"jdbc:mysql://主机地址：端口号/数据库名？参数1&参数2&参数3"。
   useSSL=true：就是使用JDBC跟你的数据库连接的时候，你的JDBC版本与MySQL版本不兼容，MySQL的版本更高一些，在连接语句后加上“useSSL=‘true’” ，就可以连接到数据库了
3. Connection connection = DriverManager.getConnection(url,username,password);
   connection 代表数据库，它能执行数据库级别的操作
   // 事务提交 connection.commit();
   // 事务回滚 connection.rollback();
   // 事务自动提交 connection.setAutoCommit();
4. Statement statement = connection.createStatement();
   statement 是执行sql的对象
   String sql = “SELECT * FROM users”; // 需要执行的sql
   statement.executeQuery(sql); // 执行查询操作返回 ResultSet
   statement.execute(sql); // 执行任何sql
   statement.executeUpdate(); // 更新、插入、删除，都用这个
5. 释放资源
   resultSet.close();
   statement.close();
   connection.close();

3. statement对象

• jdbc中的statement对象用于向数据库发送SQL语句，向完成对数据库的增删改查，只需要通过statement对象向数据库发送增删改查的SQL语句即可。
• 测试增添

配置文件 db.properties
driver = com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8
username=root
password=123456
1
2
3
4
5

加载驱动 获取连接 释放资源
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

public class jdbcUtils {

    private static String driver = null;
    private static String url = null;
    private static String username = null;
    private static String password = null;

    static {
        try {
            InputStream in = jdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");
            Properties properties = new Properties();
            properties.load(in);

            driver = properties.getProperty("driver");
            url = properties.getProperty("url");
            username = properties.getProperty("username");
            password = properties.getProperty("password");

           // 1. 驱动只用加载一次
            Class.forName(driver);

        } catch (IOException | ClassNotFoundException e) {
            e.printStackTrace();
        }
    }
    // 获取连接
    public static Connection getConnection() throws SQLException {
        Connection connection = DriverManager.getConnection(url, username, password);
        return connection;
    }

    // 释放资源
    public static void release(Connection c, Statement s, ResultSet r) throws SQLException {
        if (c != null) {
            c.close();
        }

        if (s != null) {
            s.close();
        }

        if (r != null) {
            r.close();
        }
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52

测试增添
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class TestInsert {
    public static void main(String[] args)  {
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        try {
            // 获取数据库连接
            connection = jdbcUtils.getConnection();

            // 获取SQL执行对象
            statement = connection.createStatement();
            String sql = "INSERT INTO `users`(id,`name`,`password`,`email`,`birthday`) \n" +
                    "VALUE(4,'xiaowang','123456','374072213','1998-12-23')";
            int i = statement.executeUpdate(sql);
            if(i > 0){
                System.out.println("插入成功");
            }else {
                System.out.println("插入失败");
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                jdbcUtils.release(connection,statement,resultSet);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38

测试查询
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class TestQuery {
    public static void main(String[] args) {
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;

        try {
            // 获取数据库连接
            connection = jdbcUtils.getConnection();

            // 获取Statemnet对象
            statement = connection.createStatement();
            String sql = "SELECT * FROM `users`";
            resultSet = statement.executeQuery(sql);

            while (resultSet.next()){
                System.out.println("id= " + resultSet.getObject("id"));
                System.out.println("name= " + resultSet.getObject("name"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                jdbcUtils.release(connection,statement,resultSet);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

4. SQL注入问题

• SQL注入即是指web应用程序对用户输入数据的合法性没有判断或过滤不严，攻击者可以在web应用程序中事先定义好的查询语句的结尾上添加额外的SQL语句，在管理员不知情的情况下实现非法操作，以此来实现欺骗数据库服务器执行非授权的任意查询，从而进一步得到相应的数据信息。

SQL注入测试代码
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class SqlZhuRu {
    public static void main(String[] args) {
        //正常登录
        // login("wangwu","123456");

        //SQL注入
        login("' or '1=1","' or '1=1");
    }

    public static void login(String username,String password){
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;

        try {
            connection = jdbcUtils.getConnection();

            statement = connection.createStatement();

            //正常登录语句
            //SELECT * FROM `users` WHERE `name` = 'wangwu' AND `password` = '123456'
            //SQL注入语句
            // SELECT * FROM `users` WHERE `name` = '' or '1=1' and 'password' = '' or '1=1';
            String sql = "SELECT * FROM `users` WHERE `name` = '" + username + "' AND `password` = '" + password + "'";


            resultSet = statement.executeQuery(sql);

            while (resultSet.next()){
            System.out.println("id=" + resultSet.getObject("id"));
            System.out.println("name=" + resultSet.getObject("name"));
            System.out.println("birthday=" + resultSet.getObject("birthday"));
            System.out.println("===");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                jdbcUtils.release(connection,statement,resultSet);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51

5. PrepareStatement

• PrepareStatement 可以防止SQL注入，效率更高

测试PreparmentStatement插入代码

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class TestPrepareStatement {
    public static void main(String[] args) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;

        try {
            //获取数据库
            connection = jdbcUtils.getConnection();

            //写SQL语句，用？占位符
            String sql = "INSERT INTO `users`(`id`,`name`,`password`,`email`,`birthday`) values(?,?,?,?,?)";

            //预编译，先写好sql
            preparedStatement = connection.prepareStatement(sql);

            preparedStatement.setInt(1,4);
            preparedStatement.setString(2,"dawang");
            preparedStatement.setString(3,"123456");
            preparedStatement.setString(4,"374072213@qq.com");
            preparedStatement.setDate(5,new Date(new java.util.Date().getTime()) );

            int i = preparedStatement.executeUpdate();

            if(i > 0){
                System.out.println("插入成功！");
            }


        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                jdbcUtils.release(connection,preparedStatement,null);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46

6. JDBC操作事务

public class TestShiWu {
    public static void main(String[] args) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;

        try {

            connection =  jdbcUtils.getConnection();
            //关闭自动提交，启动事务
            connection.setAutoCommit(false);

            String sql = "update account set money = money - 300 where id = 1 ";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.executeUpdate();

            String sq2 = "update account set money = money + 300 where id = 2 ";
            preparedStatement = connection.prepareStatement(sq2);
            preparedStatement.executeUpdate();

            connection.commit();


        } catch (SQLException e) {
            e.printStackTrace();
            try {
                connection.rollback(); // 失败回滚事务，不写也行，默认失败回滚
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        }finally {
            try {
                jdbcUtils.release(connection,preparedStatement,null);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38

7. 数据库连接池

• 数据库连接池负责分配、管理和释放数据库连接，它允许应用程序重复使用一个现有的数据库连接，而不是再重新建立一个，避免了重复“重建连接-释放资源”的麻烦。

1. DBCP

dbcp_properties

#连接设置
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=false
username=root
password=123456

#初始化连接
initialSize=10

#最大连接数量
maxActive=50

#最大空弦连接
maxIdle=20

#最小空闲连接
minIdle=5

#超时等待
maxWait=60000

#JDBC驱动建立连接时附带的连接属性属性的格式必须为这样：[属性名=property;]
#注意：“user” 与 “password” 两个属性会被明确地传递，因此这里不需要包含他们。
connectionProperties=useUnicode=true;characterEncoding=gbk

#指定由连接池所创建的连接的自动提交（auto-commit）状态。
defaultAutoCommit=true

#driver default 指定由连接池所创建的连接的事务级别（TransactionIsolation）。
#可用值为下列之一：（详情可见javadoc。）NONE,READ_UNCOMMITTED, READ_COMMITTED, REPEATABLE_READ, SERIALIZABLE
defaultTransactionIsolation=READ_UNCOMMITTED
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

JdbcUtils_JDCP.class

import org.apache.commons.dbcp.BasicDataSourceFactory;

import javax.sql.DataSource;
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

public class JdbcUtils_JDCP {

    private static DataSource dataSource = null;
    static {
        try {
            // 这里要会写
            InputStream in = JdbcUtils_JDCP.class.getClassLoader().getResourceAsStream("dbcp.properties");
            Properties properties = new Properties();
            properties.load(in);

            //创建数据源,工厂模式-->创建对象
            try {
                dataSource = BasicDataSourceFactory.createDataSource(properties);
            } catch (Exception e) {
                e.printStackTrace();
            }


        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    // 获取连接
    public static Connection getConnection() throws SQLException {

        return dataSource.getConnection();
    }

    // 释放资源
    public static void release(Connection c, Statement s, ResultSet r) throws SQLException {
        if (c != null) {
            c.close();
        }

        if (s != null) {
            s.close();
        }

        if (r != null) {
            r.close();
        }
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53