mathum

博客园 首页 新随笔 联系 订阅 管理
Mysql学习笔记

1、初识数据库

略

1.3 数据库分类

关系型数据库：（SQL）

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

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

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

DBMS（Database Management System 数据库管理系统）

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

1.4 MySQL简介

MySQL是一个关系型数据库管理系统

前世：瑞典MySQL AB公司

今生：属于Oracle旗下产品

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

1.7 连接数据库

命令行连接！

mysql -u root -p --连接数据库
update mysql.user set authentication_string=password('123456') where user='root' and Host = 'localhost';  --修改用户密码
flush privileges; --刷新权限

-------------------------------------------------------------------------------------

--所有的语句都以分号（;）结尾
show databases; --查看所有数据库
use school; --切换数据库， use db_name
show tables; --查看数据库中所有的表
describe student; --显示数据库中所有的表的信息

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

exit; --退出连接

-- 单行注释（SQL）
/*
SQL的多行注释
SQL的多行注释
SQL的多行注释
*/

DDL：数据库定义语言

DML：数据库操作语言

DQL：数据库查询语言

DCL：数据库控制语言

2、操作数据库

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

mysql关键字不区分大小写

2.1 操作数据库（了解）

1、创建数据库

CREATE DATABASE [IF NOT EXISTS] westo

2、删除数据库

DROP DATABASE [IF EXISTS] westo

3、使用数据库

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

4、查看数据库

SHOW DATABASES

2.2 数据库的列类型

1、数值

图片

2、字符串

图片

3、日期和时间型数值类型

图片

4、NULL值

  • 理解为 "没有值" 或 "未知值"
  • 不要用NULL进行算术运算 , 结果仍为NULL

2.3 数据库的字段属性（重点）

UnSigned

  • 无符号的
  • 声明该数据列不允许负数

ZEROFULL

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

Auto_InCrement

  • 自动增长的，每添加一条数据，自动在上一个记录上加1（默认）
  • 通常用于设置主键，且为整数类型
  • 可定义起始值和步长
    • 当前表设置步长（AUTO_INCREMENT=100），只影响当前表
    • SET@@auto_increment_increment=5，影响所有使用自增的表（全局）

NULL 和 NOT NULL

  • 默认为NULL，即没有插入该列的数值
  • 如果没有设置为NOT NULL，则该列必须有值

DEFAULT

  • 用于设置默认值
  • 例如，性别字段，默认为“男”，否则为“女”；若无指定该列的值，则默认为“男”的值

拓展：

/*每一个表，都必须存在以下五个字段
id 主键
`version` 乐观锁
is_delete 伪删除
gmt_create 创建时间
gmt_update 修改时间
*/

2.4 创建数据库表

CREATE TABLE IF NOT EXISTS `student`(
  `id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
  `name` VARCHAR(30) NOT NULL DEFAULT'匿名' COMMENT '姓名',
  `password` VARCHAR(20) NOT NULL DEFAULT'123456' COMMENT'密码',
  `sex` VARCHAR(2) NOT NULL DEFAULT'男' COMMENT'性别',
  `birth` DATETIME DEFAULT NULL COMMENT'出生日期',
  `address` VARCHAR(100) DEFAULT NULL COMMENT'家庭住址',
  `email` VARCHAR(50) DEFAULT NULL COMMENT'邮箱',
  PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET = utf8`student`

格式：

CREATE TABLE [IF NOT EXISTS] `表名`(
  `字段名` 列类型 [属性] [索引] [注释],
  `字段名` 列类型 [属性] [索引] [注释],
  ......
  `字段名` 列类型 [属性] [索引] [注释]
)ENGINE = MyISAM (or InnoDB)

-- 查看mysql所支持的引擎类型 (表类型)
SHOW ENGINES;

常用命令：

SHOW CREATE DATABASE school -- 查看数据库的语句
SHOW CREATE TABLE student -- 查看表的语句
DESC student -- 显示表的结构

2.5 数据表的类型

/*
INNODB 默认使用
MYISAM 早些年使用
*/
MYISAM INNODB
事务支持 不支持 支持
数据行锁定 不支持 支持
外键 不支持 支持
全文索引 支持 不支持
表空间的大小 较小 较大，约为2倍

常规使用操作：

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

在物理空间的位置：

所有的数据库文件都存在data目录下，本质还是文件的存储。

MySQL殷勤在物理文件上的区别：

  • InnoDB在数据库表中只有一个 *.frm 文件，以及上级目录下的 lidata1 文件
  • MYISAM 对应文件（了解）
    • *.frm 表结构的定义文件
    • *.MYD 数据文件（data）
    • *.MYI 索引文件（index）

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

CHARSET = utf8

MySQL的默认编码是Latin1，不支持中文

可以在my.ini中配置默认的编码：

character-set-server=utf8

2.6 修改和删除表

ALTER TABLE student RENAME AS stu -- 修改表名
ALTER TABLE stu ADD score INT(2) -- 增加表的子段
-- 修改表的字段
ALTER TABLE stu MODIFY `password` VARCHAR(10) -- 修改约束
ALTER TABLE stu CHANGE `password` psw INT(1) -- 字段重命名

-- 删除表的字段
ALTER TABLE stu DROP psw

-- 删除表
DROP TABLE IF EXISTS stu

所有的创建和删除操作尽量加上判断，提高安全性，以免报错

注意：

  • `` 字段名，使用这个包裹
  • 注释 -- /**/
  • sql关键字大小写不敏感，建议小写
  • 所有符号用英文

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

-- 定义外键Key
-- 给这个外键添加约束（执行引用）
CREATE TABLE IF NOT EXISTS `student`(
  `id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
  `name` VARCHAR(30) NOT NULL DEFAULT'匿名' COMMENT '`grade`姓名',
  `password` VARCHAR(20) NOT NULL DEFAULT'123456' COMMENT'密码',
  `sex` VARCHAR(2) NOT NULL DEFAULT'男' COMMENT'性别',
  `birth` 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 `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 '`grade`姓名',
  `password` VARCHAR(20) NOT NULL DEFAULT'123456' COMMENT'密码',
  `sex` VARCHAR(2) NOT NULL DEFAULT'男' COMMENT'性别',
  `birth` 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`);

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

最佳实践：

  • 数据库就是单纯的表，只用来存数据，只有行和列
  • 要想使用多张表中的外键，use程序（应用层）

3.2 DML语言（记住）

添加insert

公式：

INSERT INTO `表名`(字段1,字段2,字段3,...) VALUES('值1','值2','值3'),('值1','值2','值3')...

INSERT INTO `student`(`name`) VALUES ('ma');
INSERT INTO `student`(`name`) VALUES ('ma'),('li');
INSERT INTO `student`(`name`,`password`) VALUES ('ma','123123'),('li','666666');

字段可以省略，后面的值要一一对应。

INSERT INTO `student` 
VALUES (9,'po','123','女','1997-01-01','xdu','');

INSERT INTO `student` 
VALUES (9,'po','123','女','1997-01-01','xdu',''),
(7,'jo','456','女','1997-02-02','xdu','123@qq.com');

修改update

-- 修改具体一条数据
UPDATE `student` SET `name` = 'mathum' WHERE id = 1;

-- 不指定条件的情况下，会改动所有数据的值
UPDATE `student` SET `name` = '长江7号';

-- 修改多个属性
UPDATE `student` SET `name` = 'mathum',`email` = '23333@qq.com' WHERE id = 1;

-- 选择修改
UPDATE `student` SET `name` = 'lemoure' WHERE id < 3;

-- 通过多个条件定位
UPDATE `student` SET `name` = 'limengru' WHERE `name` = '长江7号' AND `password` = '123';

-- 通过函数
UPDATE `student` SET `birthday`=CURRENT_TIME WHERE `name` = '长江7号' AND `sex` = '男';

操作符：

操作符 含义 范围 结果
= 等于 5 = 6 false
<> 不等于 5 <> 6 true
>
<
<=
>=
BETWEEN ... AND ... 在某个范围内 [5,6]
AND 5>1 AND 1>2 false
OR 5>1 or 1>2 true

删除delete

delete命令

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

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

truncate命令

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

TRUNCATE `student`;

delete 和 truncate 的区别

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

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

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

4、DQL查询数据（most important）

4.1 DQL

Data Query Language：数据库查询语句

4.2 指定查询字段

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

有的时候，列名字不是那么的见名知意，可以使用 AS 起别名

去重 distinct

-- 查询全部的考试成绩
SELECT * 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 逻辑非 
-- and
SELECT studentno,`studentresult` FROM result
WHERE studentresult >= 95 AND studentresult <= 100

-- 模糊查询
SELECT studentno,`studentresult` FROM result
WHERE studentresult BETWEEN 80 AND 100

-- 除了学号为1000以外学生的成绩
SELECT studentno,`studentresult` FROM result
WHERE studentno != 1000

-- NOT
SELECT studentno,`studentresult` FROM result
WHERE NOT studentno = 1000

模糊查询：比较运算符

运算符 语法 描述
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,a3...) 假设a在a1,a2...中，结果为真 
-- 查询姓张的同学
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 '%慧%'

-- 查询1001,1002,1003成绩
SELECT `studentno`,`studentname` FROM `student`
WHERE studentno IN (1001,1002,1003)

-- 查询在南京的学生
SELECT `studentno`,`studentname` FROM `student`
WHERE `address` IN('江苏南京')

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

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

4.4 联表查询

img

-- inner join
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 AS s
RIGHT JOIN result r
ON s.studentno = r.studentno

-- Left Join
SELECT s.studentno,studentname,subjectno,studentresult
FROM student AS s
LEFT JOIN result r
ON s.studentno = r.studentno

-- 查询缺考的同学
SELECT s.studentno,studentname,subjectno,studentresult
FROM student AS s
LEFT JOIN result r
ON s.studentno = r.studentno
WHERE studentresult IS NULL
操作 描述
Inner Join 如果表中至少有一个匹配，就返回
Left Join 会从左表返回所有的值，即使右表中没有匹配
Right Join 会从右表返回所有的值，即使左表中没有匹配

多表查询

/*
1.分析需求，分析查询的字段来自哪些表
2.确定使用哪些连接查询？7种
确定交叉点（这两个表中哪个数据是相同的）
以及判断的条件
*/
SELECT s.studentno,studentname,subjectname,studentresult
FROM student s
RIGHT JOIN result r
ON r.studentno = s.studentno
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno

自连接

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

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

4.5 分页和排序

排序 order by

分页 limit （在语句块的最后，顺序不能改变）

-- 排序：升序 ASC ，降序 DESC
-- limit 起始值，页面的大小
SELECT s.`studentno`,`studentname`,`studentresult`
FROM `student` s
LEFT JOIN `result` r
ON s.`studentno` = r.`studentno`
WHERE `studentresult` IS NOT NULL
ORDER BY `studentresult` ASC
LIMIT 1,5

各个语句顺序

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 {[offset,]row_count | row_countOFFSET offset}];
   -- 指定查询的记录从哪条至哪条

4.6 子查询

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

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

-- 1、查询 高等数学-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`,`subjectno`,`studentresult`
FROM `result`
WHERE `subjectno` = 
(
SELECT `subjectno` FROM `subject` 
WHERE `subjectname` = '高等数学-1'
)
ORDER BY `studentresult` ASC
-- 查询所有高等数学-1的学生学号
SELECT `subjectno` FROM `subject` WHERE `subjectname` = '高等数学-1'


-- 分数不小于80分的学号和姓名
SELECT DISTINCT s.`studentno`,`studentname`
FROM student s
INNER JOIN `result` r
ON s.`studentno` = r.`studentno`
WHERE `studentresult`>=80

-- 在这个基础上增加一个科目，高等数学-1
SELECT DISTINCT s.`studentno`,`studentname`
FROM student s
INNER JOIN `result` r
ON s.`studentno` = r.`studentno`
WHERE `studentresult`>=80 AND `subjectno` = 
(
SELECT `subjectno` FROM `subject`
WHERE `subjectname` = '高等数学-1'
)

4.7 分组和过滤

-- 查询不同课程的平均分，最高分，最低分
SELECT `subjectname`,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

5、MySQL函数

官网：https://dev.mysql.com/doc/refman/5.7/en/sql-function-reference.html

5.1 常用函数

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

-- 字符串函数
SELECT CHAR_LENGTH('业精于勤荒于嬉') -- 字符串长度
SELECT CONCAT('我','爱','你') -- 拼接
SELECT INSERT('我爱编程helloworld',2,1,'超级热爱') -- 查询，从某个位置开始替换长度
SELECT LOWER('MATHUM') -- 小写
SELECT UPPER('mathum') -- 大写
SELECT INSTR('mathum','h') -- 第一次出现的位置
SELECT REPLACE('坚持就能成功','坚持','努力') -- 替换
SELECT SUBSTR('坚持就能成功',5,2) -- 从第几个位置开始，截取长度
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(`borndate`) FROM `student` -- 会忽略所有的null值
SELECT COUNT(*) FROM `student` -- 不会忽略null值，本质 计算行数
SELECT COUNT(1) FROM `student` -- 不会忽略所有的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`


-- 查询不同课程的平均分，最高分，最低分
SELECT `subjectname`,AVG(`studentresult`) AS 平均分,MAX(`studentresult`),MIN(`studentresult`)
FROM `result` r
INNER JOIN `subject` sub
ON r.`subjectno` = sub.`subjectno`

-- 查询不同课程的平均分，最高分，最低分，平均分大于80
SELECT `subjectname`,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

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

一、MD5简介

MD5即Message-Digest Algorithm 5（信息-摘要算法5），用于确保信息传输完整一致。是计算机广泛使用的杂凑算法之一（又译摘要算法、哈希算法），主流编程语言普遍已有MD5实现。将数据（如汉字）运算为另一固定长度值，是杂凑算法的基础原理，MD5的前身有MD2、MD3和MD4。不可逆。

二、实现数据加密

新建一个表 testmd5

 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,'kuangshen','123456'),(2,'qinjiang','456789')

如果我们要对pwd这一列数据进行加密，语法是：

 update testmd5 set pwd = md5(pwd);

如果单独对某个用户(如kuangshen)的密码加密：

 INSERT INTO testmd5 VALUES(3,'kuangshen2','123456')
 update testmd5 set pwd = md5(pwd) where name = 'kuangshen2';

插入新的数据自动加密

 INSERT INTO testmd5 VALUES(4,'kuangshen3',md5('123456'));

查询登录用户信息（md5对比使用，查看用户输入加密后的密码进行比对）

 SELECT * FROM testmd5 WHERE `name`='kuangshen' AND pwd=MD5('123456');

6、事务

6.1 什么是事务

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

原子性（Atomicity）

要么都成功，要么都失败

一致性（Consistency）

事务前后的数据完整性要保持一致

持久性（Durability）

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

隔离性（Isolation）

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

隔离性的问题：

脏读：一个事务读取了另一个没有提交的事务。

不可重读读：在同一个事务内，重复读取表中的数据，表数据发生了改变。

虚读（幻读）：在一个事务内，读取到了别人插入的数据，导致前后读出来结果不一致。

执行事务

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

-- 手动处理事务
SET autocommit = 0 -- 关闭
-- 事务开启
START TRANSACTION -- 标记一个事务的开始，从这个之后的sql都在同一个事务内
-- 提交：持久化
COMMIT
-- 回滚：回到原来的样子
ROLLBACK
-- 事务结束
SET autocommit = 1 -- 开启

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

例子：

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`)
VALUES ('A',2000.00),('B',10000.00)

SET autocommit = 0;
START TRANSACTION
UPDATE `account` SET money = money - 500 WHERE `name` = 'A'
UPDATE `account` SET money = money + 500 WHERE `name` = 'B'
COMMIT;
ROLLBACK;
SET autocommit = 1;

7、索引

MySQL官方对索引的定义为：索引（Index）是帮助MySQL搞笑获取数据的数据结构。

7.1 索引的分类

  • 主键索引（PRIMARY KEY）
    • 唯一的标识，主键不可重复，只有一个列作为主键
  • 唯一索引（UNIQUE KEY）
    • 避免重复的列出现，可以重复，多个列都可以标识为唯一索引
  • 常规索引（KEY / INDEX）
    • 默认的
  • 全文索引（FullText）
    • 在特定的数据库引擎下才有，MyISAM
    • 快速定位数据
    • 适合大型数据集

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 CURRENT_TIMESTAMP,
`update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='app用户表'

-- 批量插入100w条数据
DROP FUNCTION IF EXISTS mock_data;
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`, `email`, `phone`, `gender`, `password`, `age`)
   VALUES(CONCAT('用户', i), '24736743@qq.com', CONCAT('18', FLOOR(RAND()*(999999999-100000000)+100000000)),FLOOR(RAND()*2),UUID(), FLOOR(RAND()*100));
  SET i = i + 1;
END WHILE;
RETURN i;
END;
SELECT mock_data();

-- 查询
SELECT * FROM app_user WHERE name = '用户9999'; -- 查看耗时
EXPLAIN SELECT * FROM app_user WHERE name = '用户9999'

-- 建立索引
CREATE INDEX idx_app_user_name ON app_user(name);

-- 查询
SELECT * FROM app_user WHERE name = '用户9999';

7.3 索引原则

  • 索引不是越多越好
  • 不要对经常变动的数据加索引
  • 小数据量的表建议不要加索引
  • 索引一般应加在常用来查询的字段上

8、权限管理和备份

8.1 用户管理

SQL命令

mysql.user 表

-- 创建用户
CREATE USER lemoure IDENTIFIED BY '123456'

-- 修改密码（修改当前用户密码）
SET PASSWORD = PASSWORD('111111')
-- 修改密码（修改指定用户密码）
SET PASSWORD FOR lemoure = PASSWORD('111111')

-- 重命名
RENAME USER lemoure TO li

-- 用户授权， 库.表
GRANT ALL PRIVILEGES ON *.* TO li

-- 查看权限
SHOW GRANT FOR li -- 查看指定用户的权限
SHOW GRANT FOR root@localhost

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

-- 删除用户
DROP USER li

8.2 MySQL备份

方式：

  • 直接拷贝物理文件（data文件夹）
  • 在SQLyog这种可视化工具中手动导出
  • cmd mysqldump
    • mysqldump -hlocalhost -uroot -p123456 school student > d:/a.sql
# mysqldump -h 主机 -u 用户名 -p 密码 数据库 表名 > 物理磁盘位置
mysqldump -hlocalhost -uroot -p123456 school student > d:/a.sql

# 导出多张表
mysqldump -h 主机 -u 用户名 -p 密码 数据库 表名1 表名2 表名3 > 物理磁盘位置

# 导出数据库
mysqldump -h 主机 -u 用户名 -p 密码 数据库 > 物理磁盘位置

# 导入
登录的情况下：source  d:/ a.sql
不登录：mysql -u 用户名 -p 密码 库名< 物理磁盘位置

9、规范数据库设计

9.1 为什么要设计？

糟糕的数据库设计 :

  • 数据冗余，存储空间浪费
  • 数据更新和插入的异常
  • 程序性能差

良好的数据库设计 :

  • 节省数据的存储空间
  • 能够保证数据的完整性
  • 方便进行数据库应用系统的开发

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

  • 需求分析阶段：分析客户的业务和数据处理需求
  • 概要设计阶段：设计数据库的E-R模型图 , 确认需求信息的正确和完整。

设计数据库步骤

  • 收集信息

    • 与该系统有关人员进行交流 , 座谈 , 充分了解用户需求 , 理解数据库需要完成的任务。

  • 标识实体[Entity]

    • 标识数据库要管理的关键对象或实体，实体一般是名词

  • 标识每个实体需要存储的详细信息[Attribute]

  • 标识实体之间的关系[Relationship]

9.2 三大范式

问题 : 为什么需要数据规范化?

不合规范的表设计会导致的问题：

  • 信息重复

  • 更新异常

  • 插入异常

    • 无法正确表示信息

  • 删除异常

    • 丢失有效信息

三大范式

第一范式 (1st NF)

第一范式的目标是确保每列的原子性，如果每列都是不可再分的最小数据单元，则满足第一范式。

第二范式(2nd NF)

第二范式（2NF）是在第一范式（1NF）的基础上建立起来的，即满足第二范式（2NF）必须先满足第一范式（1NF）。

第二范式要求每张表只描述一件事情。

第三范式(3rd NF)

如果一个关系满足第二范式，并且除了主键以外的其他列都不传递依赖于主键列，则满足第三范式。

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

规范化和性能的问题

阿里规范：关联查询的表不得超过三张

  • 考虑商业化的需求 , 数据库性能比规范化数据库更重要

  • 在规范性能问题的时候，需要适当的考虑一下规范性。

  • 故意给某些表增加一些冗余的字段（从多表查询中变为单表查询）

  • 故意增加一些计算列（从大数据量降低为小数据量的查询）

10、JDBC

10.1 数据库驱动

程序通过 数据库驱动 和数据库打交道！

10.2 JDBC

Java Database Connectivity，（Java操作数据库）规范

java.sql

javax.sql

还需要导入数据库驱动包 mysql-connector-java-5.1.47

10.3 第一个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)
VALUES(1,'zhansan','123456','zs@sina.com','1980-12-04'),
(2,'lisi','123456','lisi@sina.com','1981-12-04'),
(3,'wangwu','123456','wangwu@sina.com','1979-12-04')

测试代码

package com.kuang;

import java.sql.*;

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

        //2.用户信息和url
        // useUnicode=true&characterEncoding=utf8&useSSL=true
        String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true";
        String username = "root";
        String password = "123456";
        //3.连接成功
        Connection connection = DriverManager.getConnection(url,username,password);
        //4.执行sql的对象
        Statement statement = connection.createStatement();
        //5.执行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("pwd=" + resultSet.getObject("PASSWORD"));
            System.out.println("email=" + resultSet.getObject("email"));
            System.out.println("birth=" + resultSet.getObject("birthday"));
            System.out.println("========================================");
        }
        //6.释放连接
        resultSet.close();
        statement.close();
        connection.close();
    }
}

步骤总结：

1.加载驱动

2.连接数据库 DriverManager

3.获得执行sql的对象 Statement

4.获得返回的结果集

5.释放连接

DriverManager

// DriverManager.registerDriver(new com.mysql.jdbc.Driver());
Class.forName("com.mysql.jdbc.Driver"); //固定写法
Connection connection = DriverManager.getConnection(url,username,password);

//connection 代表数据库

//事务回滚
//事务提交
//数据库设置自动提交
connection.rollback();
connection.commit();
connection.setAutoCommit();

URL

String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true";

// mysql --3306
//jdbc:mysql://主机地址:端口号/数据库名?参数1..
// oracle --1521
//jdbc:oracle:thin:@localhost:1521:sid

Statement

String sql = "SELECT * FROM `users`"; // 编写sql

statement.executeQuery();//查询操作返回 resultset
statement.execute();
statement.executeUpdate(); //更新、插入、删除，返回一个受影响的行数

ResultSet 查询的结果集：封装了所有的查询结果

获得指定的数据类型

resultSet.getObject(); //在不知道列类型的情况下使用
resultSet.getString();
resultSet.getInt();
resultSet.getDate();
...

遍历，指针

// [1,1,1,1,1,1,1,1,1,1,1,1,1]
resultSet.beforeFirst();//移动到最前面
resultSet.afterLast();//移动到最后面
resultSet.next();//移动到下一个
resultSet.previous();//移动到前一行
resultSet.absolute();//移动到指定行

释放资源

resultSet.close();
statement.close();
connection.close();

10.4 statement对象

JDBC中的statement对象用于向数据库发送sql语句。

代码实现

1、db.properties

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true
username=root
password=123456

2、JdbcUtils.java

package lesson2;

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

            //驱动只需要加载一次
            Class.forName(driver);

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

    //释放连接
    public static void release(Connection conn, Statement st, ResultSet rs){
        if (rs != null){
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (st != null){
            try {
                st.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (conn != null){
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

}

3、测试类

package lesson2;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class Test {
    public static void main(String[] args) {
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;

        try {
            conn= JdbcUtils.getConnection();//获取数据库连接
            st = conn.createStatement();//获得SQL的执行对象
            String sql = "INSERT INTO   users(`id`,`NAME`,`PASSWORD`,`email`,`birthday`)\n" +
                    "VALUES (4,'mathum','123456','1@qq.com','2020-1-1')";
            int i = st.executeUpdate(sql);
            if (i>0){
                System.out.println("插入成功！！");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,rs);
        }
    }
}

4、查询

public class TestQuery {
    public static void main(String[] args) {
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;

        try {
            conn= JdbcUtils.getConnection();//获取数据库连接
            st = conn.createStatement();//获得SQL的执行对象
            String sql = "select * from users where id = 1";
            rs = st.executeQuery(sql);

            while(rs.next()){
                System.out.println(rs.getString("NAME"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,rs);
        }
    }
}

10.5 PreparedStatement对象

可以防止sql注入，并且效率更高

1、测试类--插入

package lesson2;


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


public class TestInsert {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement st = null;

        try {
            conn = JdbcUtils.getConnection();

            //使用?占位符代替参数
            String sql = "insert into users(`id`,`NAME`,`PASSWORD`,`email`,`birthday`) values(?,?,?,?,?)";

            st = conn.prepareStatement(sql);//预编译SQL，先写SQL，然后不执行

            //手动给参数赋值
            st.setInt(1,5);
            st.setString(2,"masiyu");
            st.setString(3,"123456");
            st.setString(4,"2@qq.com");
            //注意点：sql.Date   数据库  java.sql.Date()
            //       util.Date  Java    new Date().getTime()
            st.setDate(5,new java.sql.Date(new java.util.Date().getTime()));

            int i = st.executeUpdate();
            if(i>0){
                System.out.println("插入成功!!");
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
        finally {
            JdbcUtils.release(conn,st,null);
        }
    }
}

10.6 IDEA连接数据库

右侧Datebase -> '+'号 -> 输入信息 ->OK

选择数据库：

image-20210313100656092

查看信息：

image-20210313100736600

修改数据：

image-20210313100832544

编写sql代码：

image-20210313101000150

10.8 事务

1、开启事务 conn.setAutoCommit(false);

2、一组业务执行完毕，提交事务

3、可以在catch语句中显示的定义 回滚语句，但默认失败就会回滚

package lesson3;

import lesson2.JdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class TestTransaction {
    public static void main(String[] args) {
        Connection conn =null;
        PreparedStatement st = null;
        ResultSet rs = null;

        try {
            conn = JdbcUtils.getConnection();

            //关闭自动提交，自动回开启事务
            conn.setAutoCommit(false);

            String sql1 = "update account set money = money - 100 where name = 'a'";
            st = conn.prepareStatement(sql1);
            st.executeUpdate();

            // int x = 1/0; 测试回滚

            String sql2 = "update account set money = money + 100 where name = 'b'";
            st = conn.prepareStatement(sql2);
            st.executeUpdate();

            conn.commit();
            System.out.println("成功！！");

        } catch (SQLException e) {
            try {
                conn.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            e.printStackTrace();
        }
        finally {
            JdbcUtils.release(conn,st,rs);
        }
    }
}

10.9 数据库连接池

数据库连接 --- 执行完毕 --- 释放 （十分浪费资源）

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

posted on 2021-03-13 11:00  mathum  阅读(102)  评论(0)  编辑  收藏  举报