MySQL基本使用

发表于 | 分类于 |

数据库(DB, DataBase)，作用：存储数据，管理数据。

数据库分类

  • 关系型数据库
    • MySQL、Oracl、SqlServer、DB2、SQLlite
    • 通过表和表之间，行和列之间的关系进行数据的存储
  • 非关系型数据库
    • Redis，MongDB
    • 对象存储，通过对象的自身属性来决定

DBMS数据库管理系统

  • 数据库的管理软件，科学有效的管理我们的数据

MySQL简介

MySQL是一个DBMS。前世：瑞典MySQL AB公司；今生：属于Oracle旗下产品。5.7稳定；

安装建议：使用压缩包安装

MySQL安装

  • 解压安装包

  • 将解压后的包放到安装目录中

  • 添加环境变量

    • 将mysql的bin目录放在path中

    • 设置mysql配置文件

      • 在mysql中新建my.ini文件

        1
        2
        3
        4
        5
        6
        [mysql]
        basedir=D:\Environment\mysql-5.7.19\
        #不需要新建data
        datadir=D:\Environment\mysql-5.7.19\data\
        port=3306
        skip-grant-tables

    • 启动管理员模式下的CMD，运行所有的命令

      • 通过cd进入mysql的bin目录，然后mysqld-install
      • 初始化数据文件：mysqld - -initialize-insecure - -user=mysql
      • 然后再次启动mysql：net start mysql
      • 用命令：mysql -u root -p 进入mysql管理界面(密码可为空)
      • 进入界面后更改root密码：update mysql.user set authentication_string=password(‘123456’) where user=‘root’ and Host = ‘localhost’;

    • 修改my.ini最后一句skip-grant-tables

    • 重启mysql即可正常使用

      • net stop mysql
      • net start mysql

基本的命令行操作

连接

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
mysql -u root -p123456 -- 连接数据库

update mysql.user set authentication_string=password('123456') where user='root' and Host = 'localhost'; --修改用户密码
---------------------------------------------
show databases; -- 查看所有数据库

use databsename; -- 切换数据库 use 数据库名

show tables; -- 查看数据库中所有的表

describe tablename; -- 显示表的信息

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

exit; -- 退出连接

-- 单行注释

/*
多行注释
*/

操作数据库

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

操作数据库

创建数据库

1
2
3
CREATE DATABASE databasename;

CREATE DATABASE [IF NOT EXISTS] databasename;

删除数据库

1
2
3
DROP DATABASE databasename;

DROP DATABASE [IF EXISTS] databasename;

使用数据库

1
2
3
USE databasename;-- 如果你的表名或字段名是一个特殊的字符，就需要带票号

USE `databasename`;

查看数据库

1
SHOW DATABASES;

数据库的列类型

数值

  • tinyint 十分小的数据 1个字节
  • smallint 较小的数据 2个字节
  • mediumint 中等大小的数据 3个字节
  • int 标准的整数 4个字节
  • 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:ss，时间格式
  • datetime YYYY-MM-DD HH:mm:ss 最常用的时间格式
  • timestamp 时间戳，1970.1.1到现在的毫秒数！
  • year 年份表示

null

  • 没有值，未知
  • 注意，不要使用NULL进行计算，结果为NULL

数据库的字段属性

  • Unsigned：无符号的整数；声明了该列不能声明为符数
  • zerofill：0填充的，不足的位数，使用0来填充，int(3)，5，005
  • 自增：自动在上一条记录的基础上+1(默认)，通常来设计唯一的主键index，必须是整数类型；可以自定义设计主键自增的起始和步长
  • 非空NULL，NOT NULL：假设设置为NOT NULL，如果不给它赋值，就会报错! NULL，如果不填写值，默认就是null
  • 默认DEFAULT：设置默认的值！，即不赋值时，默认的值

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

创建数据库表

1
2
3
4
5
6
7
8
9
10
CREATE TABLE IF NOT EXISTS `student`(
	`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
    `name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
    `pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
    `sex` VARCHAR(2) NOT NULL DEFAULT '女' COMMENT '性别',
    `birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
    `address` VARCHAR DEFAULT NULL COMMENT '家庭地址',
    `email` VARCHAR DEFAULT NULL COMMENT '邮箱',
    PRIMARY KEY(`id`)
)ENGINE = INNODB DEFAULT CHARSET=utf8;
1
2
3
4
5
6
CREATE TABLE [IF NOT EXISTS] `tablename`(
	`字段名` 字段类型 [属性] [索引] [注释],
    `字段名` 字段类型 [属性] [索引] [注释],
    ...
    `字段名` 字段类型 [属性] [索引] [注释]
)[表类型] [字符集设置] [注释]
1
2
3
SHOW CREATE DATABASE 数据库名; -- 查看创建数据库语句
SHOW CREATE TABLE 表名; -- 查看表的定义语句
DESC 表名; -- 显示表的结构

数据表的类型

INNODB 默认使用

MYISAM 早些年使用

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

常规使用操作：

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

在物理空间存在的位置

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

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

  • INNODB 在数据库表中只有一个*.frm文件，以及上一级目录下的ibdata1文件
  • MYISAM 对应文件
    • *.frm 表结构的定义文件
    • *.MYD 数据文件 (data)
    • *.MYI 索引文件(index)

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

1
CHARSET=utf8

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

MYSQL默认的是Latin1，不支持中文

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

1
character-set-server=utf8

但是建议在建表的时候设置uft8。

修改和删除

修改

1
2
3
4
5
6
7
8
9
ALTER TABLE 表名 RENAME AS 新表名; -- 修改表名

ALTER TABLE 表名 ADD 字段名 属性; -- 增加表的字段

ALTER TABLE 表名 MODIFY 字段名 新的字段类型; -- 修改字段属性和约束

ALTER TABLE 表名 CHANGE 字段名 新的字段名 新字段类型; -- 修改字段名及其其属性

ALTER TABLE 表名 DROP 字段名; -- 删除字段

删除

1
DROP TABLE [IF EXISTS] 表名; -- 删除数据表

注意：

  • ``字段名，使用飘号包裹
  • 注释使用 - - / /
  • SQL关键字大小写不敏感
  • 所有的符号全部使用英文

MySQL数据管理

外键

1
2
3
4
5
CREATE TABLE IF NOT EXISTS `grade`(
	`gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年级id',
    `gradename` VARCHAR(50) NOT NULL COMMENT '年级名称',
    PRIMARY KEY(`gradedid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8;
1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE TABLE IF NOT EXISTS `student`(
	`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
    `name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
    `pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
    `sex` VARCHAR(2) NOT NULL DEFAULT '女' COMMENT '性别',
    `birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
    `gradeid` INT(10) NOT NULL COMMENT '学生的年纪'，
    `address` VARCHAR DEFAULT NULL COMMENT '家庭地址',
    `email` VARCHAR DEFAULT NULL COMMENT '邮箱',
    PRIMARY KEY(`id`),
    KEY `FK_gradeid`(`gradeid`), 
    CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade`(`gradeid`)
)ENGINE = INNODB DEFAULT CHARSET=utf8;

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

创建表的时候没有外键关系时，添加外键

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

ALTER TABLE 表名 ADD CONSTRAINT 约束名 FOREIGN KEY (作为外键的列)
REFERENCES 其它表明(关联的字段);

以上的操作都是物理外键，数据库级别的外键，不建议使用！

最佳实践

  • 数据库就是单纯的表，只用来存数据，只有行与列
  • 我们想使用多张表的数据，想使用外键(用程序去实现)

DML

数据操作语言：

  • INSERT
  • UPDATE
  • DELETE

添加

1
2
3
4
5
-- 插入语句
INSERT INTO 表名 (`字段1`, `字段2`, `字段3`,...)
VALUES('值1', '值2', '值3',...);

-- 如果不写表的字段，默认一一对应,插入多行数据可以使用括号和逗号隔开

修改

1
UPDATE 表名 SET `字段1`='值1', `字段2`='值2' WHERE 条件;

条件：WHERE子句 运算符

操作符 含义 范围 结果
= 等于 5=6 false
<>或!= 不等于 5<>6 true
>
<
<=
>=
BETWEEN…AND 在某个范围内 [2,5]
AND 且 5>1AND1>2 false
OR 或 5>1OR1>2 true

删除

1
DELETE FROM `表名` WHERE 条件;
1
TRUNCATE TABLE `表名`;

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

DELETE和TRUNCATE的相同与区别：

  • 相同点：都能删除数据，但不会删除表结构
  • 不同点：
    • TRUNCATE 重新设置 自增列 计数器会归零
    • TRUNCATE 不会影响事务
    • DELETE 不会影响自增
      • 重启数据库，现象：InnoDB自增列会从1开始(存在内存当中，断电即失)；MyISAM继续上一个自增量开始(存在文件中，不会丢失)

DQL查询数据

DQL

Data Query Language：数据查询语言

指定查询字段

1
2
3
4
5
6
7
8
9
10
11
12
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`;

去重查询

1
2
3
4
5
6
SELECT * FROM `result`;

SELECT `StudentNo` FROM `result`;

-- 取出SELECT查询出来的结果中重复的数据，重复的数据只显示一条
SELECT DISTINCT `StudentNo` FROM `result`;
1
2
3
4
5
SELECT VERSION(); -- 查询系统版本

SELECT 100*3-1 AS 计算结果; -- 用来计算

SELECT @@auto_increment_increment -- 查询自增的步长
1
2
-- 学员考试成绩+1分查询
SELECT `StudentNo`, `StudentResult`+1 AS '加一分后' FROM `result`;

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

1
SELECT 表达式 FROM 表;

WHERE条件子句

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

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

逻辑运算符

运算符 语法 描述
AND a AND b 逻辑与
OR a OR b 逻辑或
NOT NOT a 逻辑非 
1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT `studentNo`, `StudentResult` FROM result;

-- AND
SELECT `studentNo`, `StudentResult` FROM result 
WHERE StudentResult >= 95 AND StudentResult <= 100;

-- BETWEEN AND
SELECT `studentNo`, `StudentResult` FROM result 
WHERE StudentResult BETWEEN 95 AND 100;

-- NOT
SELECT `studentNo`, `StudentResult` FROM result 
WHERE NOT `studentNo` = 1000;

模糊查询

运算符 语法 描述
IS NULL
IS NOT NULL
BETWEEN…AND…
LIKE a LIKE b SQL匹配，如果a匹配b，则结果为真
IN a IN (a1, a2, a3,…) 假设a在a1，或者a2…其中的某一个值，则结果为真 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- 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
SELECT `studentNo`, `StudentName` FROM `student`
WHERE `studentNo` IN (1000, 1001, 1002);

-- NULL NOT NULL
SELECT `studentNo`, `StudentName` FROM `student`
WHERE `address`='' OR address IS NULL;

联表查询

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
SELECT * FROM `student`
SELECT * FROM `result`

/*
分析需求，分析查询到字段来自那些表
确定使用哪种连接查询？7种
确定交叉点
判断条件：学生表中的studentNo = 成绩表 studentNo
*/

SELECT s.`studentNo`, s.`studentName`, r.`SubjectNo`, r.`StudentResult` 
FROM `student` AS s
INNER JOIN `result` AS r
WHERE s.`studenNo` = r.`studentNo`;

SELECT s.`studentNo`, s.`studentName`, r.`SubjectNo`, r.`StudentResult` 
FROM `student` AS s
RIGHT JOIN `result` AS r
ON s.`studenNo` = r.`studentNo`;

自连接

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

1
2
3
SELECT a.`categoryName` AS '父栏目', b.`categoryName` AS '子栏目'
FROM `category` AS a, `category` AS b
WHERE a.`categoryid` = b.`pid`;
1
2
3
4
SELECT `studentNo`, `studentname`, `GradeName`
FROM `student` AS s
INNER JOIN `grade` AS g
ON s.`GradeID` = g.`GradeID`;

分页和排序

LIMIT和ORDER BY

1
2
3
4
5
6
7
8
9
-- ORDER BY ASC 升序 DESC 降序
SELECT s.`StudentNo`, `StudentName`, `SubjectName`, `StudentResult`
FROM `student` s
INNER JOIN `result` r
ON s.`StudentNo` = r.`StudentNo`
INNER JOIN `subject` sub
ON r.`SubjectNo` = sub.`SubjectNo`
WHERE subjectName = '数据库结构-1'
ORDER BY `StudentResult` ASC;

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

1
2
3
4
5
6
7
8
9
10
11
SELECT s.`StudentNo`, `StudentName`, `SubjectName`, `StudentResult`
FROM `student` s
INNER JOIN `result` r
ON s.`StudentNo` = r.`StudentNo`
INNER JOIN `subject` sub
ON r.`SubjectNo` = sub.`SubjectNo`
WHERE subjectName = '数据库结构-1'
ORDER BY `StudentResult` ASC
LIMIT 0,5 -- 1~5 起始值~页面大小
-- (n-1) * pageSize
-- 【pageSize：页面大小, (n-1)*pageSize：起始值, n：当前页, 数据总数/页面大小=总页数】
1
2
3
4
5
6
7
8
9
10
SELECT `StudentNo`, `studentName`, `SubjectName`, `StudentResult`
FROM `student` AS 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;

子查询

WHERE 这个值是计算出来的

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

1
2
3
4
5
6
7
-- 使用连接查询
SELECT `StudentNo`, r.`SubjectNo`, `StudentResult`
FROM `result` r
INNER JOIN `subject` sub
ON r.`SubjectNo` = sub.`SubjectNo`
WHERE `SubjectName` = '数据库结构-1'
ORDER BY `studentResult` DESC
1
2
3
4
5
6
7
8
-- 使用子查询
SELECT `StudentNo`, `SubjectNo`, `StudentResult`
FROM `result`
WHERE `StudentNo` = (
	SELECT `StudentNo`
    FROM `subject`
    WHERE `SubjectName` = '数据库结构-1'
);
1
2
3
4
5
SELECT DISTINCT s.`StudentNo`, `StudentName`
FROM `student` s
INNER JOIN `result` r
ON r.`StudentNo` = s.`StudentNo`
WHERE `StudentResult` >= 80;
1
2
3
4
5
6
7
8
9
10
SELECT DISTINCT s.`StudentNo`, `StudentName`
FROM `student` s
INNER JOIN `result` r
ON r.`StudentNo` = s.`StudentNo`
WHERE `StudentResult` >= 80 
AND `SubjectNo` = (
	SELECT `SubjectNo`
    FROM `Subject`
    WHERE `SubjectName` = '高等数学-2'
);

MySQL函数

常用函数

1
2
3
4
5
6
7
8
9
SELECT ABS(-8) -- 绝对值

SELECT CEILING(9.4) -- 向上取整

SELECT FLOOR(9.4) -- 向下取整

SELECT RAND() -- 返回一个0~1之间的随机数

SELECT SIGN(10) -- 符号函数
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SELECT CHAR_LENGTH('abcd') -- 字符串长度

SELECT CONCAT('a', 'b', 'cd') -- 拼接字符串

SELECT INSERT('abcdefg',1,2, 'zxc') -- 从某个位置开始替换某个长度

SELECT LOWER('ABC') -- 转小写

SELECT UPPER('abc') -- 转大写

SELECT INSTR('abcdfg', 'd') -- 返回第一次出现的字串的索引

SELECT REPLACE('abcdefg', 'cdef', 'opqr') -- 替换出现的指定字符串

SELECT SUBSTR('abcdefgh',4,3) -- 返回指定的子字符串

SELECT REVERES('ABCDEFGHI') -- 反转
1
2
3
4
5
6
7
8
9
10
11
12
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())
1
2
SELECT USER()
SELECT VERSION()

聚合函数

函数名称 描述
COUNT() 计数
SUM() 求和
AVG() 平均值
MAX() 最大值
MIN() 最小值
… … 
1
2
3
4
5
6
7
8
SELECT COUNT(studentname)
FROM student; -- COUNT(指定列)

SELECT COUNT(*)
FROM student; -- COUNT(*)

SELECT COUNT(1)
FROM student; -- COUNT(1)

这三个的区别：COUNT(指定列)会忽略所有的NULL值；COUNT(*)和COUNT(1)不会忽略所有null值；

如果列为主键，COUNT(列明)效率优于COUNT(1)；

如果列为不为主键，COUNT(列明)效率优于COUNT(1)；

如果表中存在主键，count(主键列名)效率最优 ；

如果表中只有一列，则count(*)效率最优；

如果表有多列，且不存在主键，则count(1)效率优于count(*)

分组和过滤

1
2
3
4
SELECT SUM(`StudentResult`) AS 总和 FROM result;
SELECT AVG(`StudentResult`) AS 平均分 FROM result;
SELECT MAX(`StudentResult`) AS 最高分 FROM result;
SELECT MIN(`StudentResult`) AS 最低分 FROM result;
1
2
3
4
5
6
SELECT SubjectName, AVG(`StudentResult`), MAX(`StudentResult`), MIN(`StudentResult`)
FROM `result` r
INNER JOIN `subject` sub
ON r.`SubjectNo` = sub.`SubjectNo`
GROUP BY r.`SubjectNo`; -- 通过什么字段来分组
HAVING AVG(`StudentResult`) > 80;

数据库级别MD5加密

MD5

增强算法复杂度和不可逆。

MD5 破解网站的原理，背后有一个字典，MD5加密后的值 加密前的值

1
2
3
4
5
6
CREATE TABLE `testmd5`(
	`id` INT(4),
    `name` VARCHAR(20) NOT NULL,
    `pwd` VARCHAR(50) NOT NULL,
    PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8;
1
2
3
4
5
-- 明文密码
INSERT INTO `testmd5` VALUES
(1, 'ZHANGSAN', '123456'),
(2, 'LISI', '123456'),
(3, 'wangwu', '123456');
1
2
3
4
5
-- 加密
UPDATE `testmd5` SET `pwd` = MD5(pwd)
WHERE `id` = 1;

UPDATE `testmd5` SET `pwd` = MD5(pwd)
1
2
3
4
5
-- 插入时加密
INSERT INTO `testmd5` VALUES
(4, 'lsn', MD5('123456'));

-- 如何校验呢？将用户传递进来的密码，进行md5加密，然后对比加密后的值

SELECT回顾

1
2
3
4
5
6
7
8
9
10
SELECT [ALL | DISTINCT]
字段1, 字段2,...
FROM 表1
[LEFT | RIGHT | INNER JOIN 表2]
ON 连接条件
WHERE [具体的值|子查询语句]
[GROUP BY 字段] -- 通过字段来分组
[HAVING ]-- 过滤分组的记录
[ORDER BY]-- 指定查询记录按一个或多个条件
[LIMIT]-- 指定查询的记录从哪里到哪里

事务

什么是事务：要么都成功要么都失败

事务原则：ACID

  • 原子性：事务不可分隔，事务中的操作要么都发生，要么都不发生
  • 一致性：事务前后数据的完整性保持一致
  • 隔离性：多个用户并发访问操作数据库时，每个并发事务之间要相互隔离
  • 持久性：一旦事务被提交，它对数据库中的数据的改变就是永久的

事务的隔离级别：

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

不可重复读：在一个事务内读取表中的某一行数据，多次读取结果不同。是因为在查询间隔中，数据被另外的一个事物修改并提交了。

幻读：是指一个事务内读取了别的事物插入的数据，导致前后读取不一致

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
CREATE DATABASE `shop` CHARACTER SET uft8 COLLATE uft_8_general_ci;

USER `shop`;

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

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;

索引

索引是帮助MySQL高效获取数据的数据结构。

索引的分类

索引的分类：

  • 主键索引(PRIMARY KEY)
    • 唯一的标识，主键不可重复，只能有一个列作为主键
  • 唯一索引(UNIQUE KEY)
    • 避免重复的列出现，唯一索引可以重复，多个列都可以标识为唯一索引
  • 常规索引(KEY，INDEX)
    • 默认的，index。key关键字来设置
  • 全文索引(FULLTEXT)
    • 在特定的数据库才有，MyISAM
1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 索引的使用
-- 在创建表的时候给字段增加索引
-- 创建完毕后，增加索引

-- 显示所有的索引信息
SHOW INDEX FROM 表名;

-- 增加一个全文索引
ALTER TABLE 数据库.表名 ADD FULLTEXT INDEX `studentName`(`studentName`);

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

SELECT * FROM `student` MATCH(studentName) AGAINST (`刘`);

测试索引

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
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 '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=uft8mb4 COMMENT='用户表';

-- 插入100万数据
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), '[email protected]', CONCAT('19', RAND()*((999999999-100000000)+100000000), FLOOR(RAND()*2), UUID(), FLOOR(RAND()*100));
		SET i = i+1;
	END WHILE;
END; 
SELECT mock_data();                                                                        
1
2
3
4
5
6
7
8
9
SELECT * FROM app_user WHERE `name` = '用户9999'; -- 0.993sec

EXPLAIN SELECT * FROM app_user WHERE `name` = '用户9999';

CREATE INDEX 索引名 ON 表(字段);-- 格式

CREATE INDEX id_app_user_name ON app_user(`name`);
SELECT * FROM app_user WHERE `name` = '用户9999'; -- 0.001sec
EXPLAIN SELECT * FROM app_user WHERE `name` = '用户9999';

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

索引原则

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

索引的数据结构

Hash类型的索引

Btree：INNODB

权限管理与数据库备份

用户管理

本质就是对用户表进行增删改查

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
CREATE USER 用户名 IDENTIFIED BY '123456' -- 创建用户

-- 修改当前用户密码
SET PASSWORD = PASSWORD('123456');

-- 修改指定密码
SET PASSWORD FOR 用户名 = PASSWORD('123456');

-- 重命名
RENAME USER 用户名 TO 新用户名

-- 用户授权 ALL PRIVILEGES 全部的权限
GRANT ALL PRIVILEGES ON *.* TO 用户名;

SHOW GRANTS FOR 用户名 -- 查看指定用户的权限

SHOW GRANTS FOR root@localhost;

-- 撤销权限 REVOKE 哪些权限 哪个库撤销，给谁撤销
REVOKE ALL PRIVILEGES ON *.* FROM 用户名;

MySQL备份

保证重要的数据丢失；数据转移A ——> B

MySQL备份方式：

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

导出数据库

1
2
3
4
5
mysqldump -hlocalhost -uroot -p123456 数据库名 表明 > D:/a.sql

mysqldump -h主机 -u用户名 -p密码 数据库名 表明 > 物理磁盘位置/文件名

mysqldump -h主机 -u用户名 -p密码 数据库名 > 物理磁盘位置/文件名

导入数据库

1
2
3
# 登录的情况下
source 备份所在的物理位置
source d:/a.sql

规范数据库设计

为什么需要设计

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

糟糕的数据库设计：

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

良好的数据库设计：

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

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

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

设计数据库的步骤：(个人博客)

  • 收集信息，分析需求
    • 用户表
    • 分类表
    • 文章表
    • 友链表
    • 自定义表

数据库的三大范式

为什么需要数据规范

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

三大范式

第一范式1NF

要求数据库的每一列都是不可分隔的原子数据项

第二范式2NF

前提：满足第一范式

第二范式需要确保数据库中的每一列都和主键相关，而不能只与主键的某一部分相关

第三范式3NF

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

规范性和性能的问题

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

  • 考虑商业化的需求和目标 数据库的性能更加重要
  • 在规范性能的问题的时候，需要适当的考虑一下规范性

JDBC

数据库驱动

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

JDBC

SUN为了简化开发人员的操作，提供了一个规范JDBC。

  • java.sql
  • javax.sql

还需要导入数据库驱动包

JDBC程序

1
2
3
4
5
6
7
CREATE TABLE `users`(
	`id` INT(20) NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT '用户ID',
	`name` VARCHAR(40) DEFAULT NULL COMMENT '用户名字',
	`PASSWORD` VARCHAR(40) DEFAULT '123456' COMMENT '用户密码',
	`email` VARCHAR(60) DEFAULT NULL COMMENT '邮箱地址',
	`birthday` DATE COMMENT '生日'
)ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT='用户表';
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
package cn.xiaohupao.jdbc;

import java.sql.*;

/**
 * @Author: xiaohupao
 * @Date: 2021/6/24 12:18
 */
public class Jdbc01 {

    public static void main(String[] args) throws ClassNotFoundException, SQLException {

        //加载驱动
        //final Class<?> aClass = Class.forName("com.mysql.jdbc.Driver");

        //连接数据库用户信息
        String url = "jdbc:mysql://localhost:3306/new_sql?useUnicode=true&characterEncoding=utf8&useSSL=false";
        String username = "root";
        String password = "123456";
        //连接成功，数据库对象
        final Connection connection = DriverManager.getConnection(url, username, password);

        //执行sql对象的statement
        final Statement statement = connection.createStatement();

        //执行SQL的对象去执行SQL
        String sql = "SELECT * FROM USERS";

        final 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("birthday:" + resultSet.getObject("birthday"));
        }

        //释放连接
        resultSet.close();
        statement.close();
        connection.close();
    }
}

JDBC中对象解释

URL

1
2
3
4
5
6
7
String url = "jdbc:mysql://localhost:3306/new_sql?useUnicode=true&characterEncoding=utf8&useSSL=false";

mysql -- 3306
jdbc:mysql://主机地址:端口号/数据库名?参数1&参数2&参数3

oralce -- 1521
jdbc:oracle:thin:@localhost:1521:sid

connection

数据库连接

1
2
3
4
connection
提交
回滚
自动提交

statement

执行SQL的对象

1
2
3
4
statement.executeQuery(); 查询操作
statement.execute(); 任何SQL
statement.executeUpdate();  更新、插入，删除，返回一个受影响的行数
statement.executeBath(); 批处理执行

ResultSet

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

1
2
3
4
resultSet.getObject();
resultSet.getString();
...

遍历

1
2
3
4
5
resultSet.beforeFirst();
resultSet.afterLast();
resultSet.next(); 移动到下一个数据
resultSet.previous();
resultSet.absolute(row);

释放资源

1
2
3
resultSet.close();
statement.close();
connection.close();

Statement对象

JDBC中的Statement对象用于向数据库发送SQL语句，完成操作。

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
54
55
56
57
58
59
60
61
62
63
package cn.xiaohupao.jdbc.utils;

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

/**
 * @Author: xiaohupao
 * @Date: 2021/6/24 13:13
 */
public class JdbcUtil {
    private static String driver;
    private static String url;
    private static String username;
    private static String password;

    static {
        try (InputStream resourceAsStream = JdbcUtil.class.getClassLoader().getResourceAsStream("jbdc.properties")){
            Properties properties = new Properties();
            properties.load(resourceAsStream);

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

            Class.forName(driver);

        } catch (IOException | ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    //获取连接

    public static Connection getConnection(){
        try {
            return DriverManager.getConnection(url, username, password);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }

    //释放连接资源

    public static void close(ResultSet resultSet, Statement statement, Connection connection){
        try {
            if (resultSet != null){
                resultSet.close();
            }
            if (statement != null){
                statement.close();
            }
            if (connection != null){
                connection.close();
            }
        } catch (SQLException throwables) {
            throwables.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
52
53
54
55
56
57
58
package cn.xiaohupao.jdbc;

import cn.xiaohupao.jdbc.utils.JdbcUtil;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;

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

/**
 * @Author: xiaohupao
 * @Date: 2021/6/24 13:35
 */
public class JdbcTest {

    private Connection connection;
    private Statement statement;
    private ResultSet resultSet;

    @Before
    public void init() throws SQLException {
        connection = JdbcUtil.getConnection();
        statement = connection.createStatement();
    }

    @Test
    public void testInsert(){
        String insert = "INSERT INTO USERS VALUES (null, 'wk', '123123', '[email protected]', '1998-10-25')";
        try {
            final int i = statement.executeUpdate(insert);
            if (i > 0){
                System.out.println("插入成功");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
	
    @Test
    public void testQuery(){
        String query = "SELECT * FROM USERS WHERE `id` = 1";
        try {
            final ResultSet resultSet = statement.executeQuery(query);
            if (resultSet.next()){
                System.out.println("name: " + resultSet.getString("name"));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
    @After
    public void after(){
        JdbcUtil.close(resultSet, statement, connection);
    }
}

SQL注入

SQL存在漏洞，会被攻击导致数据泄露

SQL会被拼接

PreparedStatement对象

PreparedStatement可以防止SQL注入，效率更高！

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
package cn.xiaohupao.jdbc;

import cn.xiaohupao.jdbc.utils.JdbcUtil;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;

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

/**
 * @Author: xiaohupao
 * @Date: 2021/6/25 9:04
 */
public class JdbcPre {

    private Connection connection;

    @Before
    public void init(){

        connection = JdbcUtil.getConnection();
    }

    @Test
    public void Inserttest() throws SQLException {
        String sql = "INSERT INTO USERS(`id`, `name`, `PASSWORD`, `email`, `birthday`) VALUES (?, ?, ? ,? ,?)";

        final PreparedStatement preparedStatement = connection.prepareStatement(sql);

        preparedStatement.setObject(1,null);
        preparedStatement.setString(2, "wk1");
        preparedStatement.setString(3,"9876543");
        preparedStatement.setString(4, "[email protected]");
        preparedStatement.setDate(5, new Date(new java.util.Date().getTime()));

        final int i = preparedStatement.executeUpdate();

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

    @After
    public void after(){
        JdbcUtil.close(null,null,connection);
    }
}

PreparedStatement防止SQL注入的本质，把传递进来的参数当作字符，假设其中存在转义字符，就直接忽略，比如说引号就会被忽略掉

事务

要么都成功，要么都失败

原子性、一致性、隔离性、持久性

隔离性事务级别：脏读，不可重复读，幻读

开启事务

1
connection.setAutoCommit(false);

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

可以在catch语句中定义回滚语句

数据库连接池

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

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

最小连接数

开源数据源实现：

DBCP

C3P0

Druid

使用数据库连接后，我们就不需要编写连接数据库代码了

无论什么使用什么数据源，DataSource接口不会变，方法就不会变。

Donate comment here