1.初识MySQL
1.1数据库分类
关系数据库:(SQL)
- MySQL,Oracle,Sql Server,DB2,SQLlite
- 通过表和表之间,行和列之间的关系进行数据存储,存储结构是由行和列的表组成
非关系型数据库:(NoSQL) Not Only
- Redis,MongDB
- 非关系型数据库,对象存储,一个对象的自身的属性来决定
DBMS(数据库管理系统)
- 数据库的管理软件,科学有效的管理我们的数据。维护和获取数据
- MySQL,数据库管理系统
1.2MySQL简介
-
MySQL是一个关系型数据库管理系统
-
由瑞典MySQL AB 公司开发,属于 Oracle 旗下产品
-
MySQL是最好的 RDBMS (Relational Database Management System,关系数据库管理系统) 应用软件之一。
-
开放源码,中小型,大型网站
-
官网:https://www.mysql.com/
-
常用:5.7(稳定)8.0
1.3MySQL安装(解压包类型安装)
2.命令输出
sc delete mmysql:清空服务
1.4安装可视化软件SQLyog
1.新建一个数据库
2.创建学生表
1.5连接数据库
命令行连接
mysql -uroot -p123456 --连接数据库
UPDATE mysql.`user` SET authentication_string=PASSWORD('123456') WHERE USER='root' AND HOST='localhost'; -- 修改用户密码
flush privilenges; -- 刷新权限
-------------------------------------------
-- 所有的语句都使用分号结尾
show databases; --查看所以数据库
use schhool; --切换数据库
show tables; -- 查看数据库中所有表
describe student; --显示数据库中所有的信息
create database westos; --创建数据库westos
exit --退出连接
-- 单行注释
/* (多行注释)
edfw
edf
*/
2.操作数据库
操作数据库 --> 操作数据库中的表 > 操作表中的数据
2.1操作数据库
[]: 代表可选
1.创建数据库
CREATE DATABASE [IF NOT EXISTS] westos; -- 如果没有这个数据库就创建
2.删除数据库
DROP DATABASE [IF EXISTS] westos; --如果有这个数据库就删除
3.使用数据库
-- tab键上面,如果你的表名是一个特殊字符,就需要带``
USE 'schhool';
4.查看数据库
SHOW DATABASES --查看所以打的数据库
2.2,数据库的列类型
数值
- tinyint 十分小的数据 1个字节
- smallint 较小的数据 2个字节
- mediumint 中等 3
- int 标准的整数 4
- bigint 较大 8
- float 浮点数 4
- double 浮点数 8
- decimal 字符串形式的浮点数 金融计算的时候 ,一般使用
字符串
-
char 字符串固定大小 0-255
-
varchar 可变字符串 0-65535 常用的 String
-
tinytext 微型文本 2^8-1
-
text 文本串 2^16-1 保存大文本
时间日期
-
date YYYY-MM-DD 日期格式
-
time HH:mm:ss 最常用的时间格式
-
timestamp 时间戳 1970.1.1 到现在的毫秒数!
-
year 年份表示
null
- 没有值,未知
- 注意,不要使用这个类型进行运算,结果为null
2.3数据库的字段属性(重点)
Unsigned:
- 无符号整数
- 声明了该列不能声明为负数
zerofill:
- 不足的位数使用0来填充,int(3),5 —>005
自增:
- 自动在上一条记录基础上+1(默认)
- 通常用来设计唯一的主键~ index,必须是整数类型
- 可以自定义设计主键自增订单起始值和步长
非空 NULL not null
- 假设设置为not null,如果不给赋值 ,就会报错
- NULL,如果不填,默认为null
默认:
- 设置默认的值
- sex,默认值为男,如果不指定该列的值,则会有默认的值!
做项目用,每一个表,都必须存在以下五个字段!
id
主键
`versi` on
乐观锁
is_delete
伪删除
gmt_ create
创建时间
gmt update 修改时间
2.4,创建数据库表(重点)
-- 目标:创建一个school数据库
-- 创建学生表(列,字段) 使用SQL 创建
-- 学号int 登陆密码varchar(20) 姓名,性别varchar(2),出生日期(datatime),家庭住址(address),email
-- 注意点,使用英文(),表的名称 和 字段 尽量使用 `` 括起来
-- AUTO_INCREMENT 自增
-- 字符串使用 单引号括起来
-- 所有的语句后面加,(英文的),最后一个不加
-- PRIMARY KEY 主键,一般应该表中只有一个唯一的主键!
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(100) DEFAULT NULL COMMENT'家庭地址',
`email` VARCHAR(50) DEFAULT NULL COMMENT'邮箱',
PRIMARY KEY('id')
)ENGINE = INNODB DEFAULT CHARSET=utf8
格式:
CREATE TABLE IF NOT EXISTS `表名`(
`字段名` 列属性 [属性] [索引] [注释],
`字段名` 列属性 [属性] [索引] [注释],
......
`字段名` 列属性 [属性] [索引] [注释],
)[表类型][字符集设置][注释]
常用命令
show create database school -- 查看创建数据库的语句
show sreate table student -- 查看student数据表的定义语句
desc student -- 显示表的结构
2.5、数据库的类型
-- 关于数据库引擎
/*
INNODB 默认使用
MYISAM 早些年使用的
*/
MYISAM | INNODB | |
---|---|---|
事务支持 | 不支持 | 支持 |
数据行锁定 | 不支持 | 支持 |
外键约束 | 不支持 | 支持 |
全文索引 | 支持 | 不支持 |
表空间大小 | 较小 | 较大,约为2倍 |
常规使用操作:
- MYISAM 节约空间,速度较快
- INNODB 安全性高,事务的处理,多表多用户操作
在物理空间存在的位置
所有的数据库外键都在data目录下,一个文件夹就对应一个数据库,本质还是文件的存储
MySQL引擎在物理文件上的区别
- InnoDB 在数据库表中只有一个*.frm 文件,以及上级目录下的 ibdata1文件
- MYISAM 对应文件
- *.frm 表结构的定义文件
- *.MYD 数据文件(data)
- *.MYI 索引文件(index)
设置数据库表的字符集编码
CHARSET=utf8(创建表时修改)
默认为Latin1,不支持中文
2.在my.ini 中配置默认的编码(不推荐,如果被其他人使用但是没有配置配置文件就会出错)
character-set-server=utf8
2.6、修改删除表
修改
--修改表名 ALTER TABLE 旧表名 RENAME AS 新表名
ALTER TABLE teacher RENAME AS teacher1 -- 将表名修改成teacher1
--增加表的字段 ALTER TABLE 表名 ADD 字段名 列属性
ALTER TABLE teacher1 ADD age INT(11)
--修改表的字段 (重命名,修改约束)
-- ALTER TABLE 表名 MODIFY 字段名 列属性[]
ALTER TABLE teacher1 MODIFY age VARCHAR(11) -- 修改约束,将age的字段类型修改为varchar
-- ALTER TABLE 表名 CHANGE 旧名字 新名字 列属性[]
ALTER TABLE teacher1 CHANGE age age1 INT(1) -- 字段重命名,将age重命名为age1且类型更改为int
--删除表的字段
ALTER TABLE teacher1 DROP age1
删除表
DROP TABLE IF EXISTS teacher1 -- 如果表存在,就删除表
所有的创建和删除操作尽量加上判断,以免报错
注意点:
- `` 字段名,使用这个包裹
- 注释 – /**/
- sql关键字不敏感,建议小写
- 所有符号用英文
3、MySQL的数据管理
3.1、外键(了解)
方式一,在创建表的时候增加约束
添加外键:
KEY `FK_ gradeid` ( `gradeidi` ), -- 定义外键
-- 关联两表中的班级id字段
CONSTRAINT `FK_ gradeid` FOREIGN KEY ( `gradeid` ) REFERENCES ` grade` ( `gradeid` )
方式二:创建表后添加外键
ALTER TABLE `student`
ADD CONSTRAINT `FK_gradeid` FORETGN KEY(`gradeid`) REFERENCES `grade` (`gradeid`)
---ALTER TABLE 表 ADD CONSTRAINT 约束名 FORETGN KEY(作为外键的列) REFERENCES 哪个表(哪个字段)
以上操作都是物理外键,数据库级别的外键,我们不建议使用,(避免数据库过多造成困扰)
最佳实现
- 数据库只存储数据
- 我们想使用多张表的数据,想使用外键(程序代码去实现)
3.2、DML语言
**数据库意义:**数据存储,数据管理
DML语言:数据操作语言
- Insert(添加)
- update(修改)
- delete(删除)
3.3、添加
-- 插入语句(添加)
-- insert into 表名([字段名1,字段名2,字段名3])values('值1','值2','值3')
-- 由于主键自增我们可以省略
INSERT INTO `studio`(`name`)VALUES('打啊');
-- 一次可以插入多个name属性,即插入多条数据
-- 一个括号代表一条数据,一条数据中可以插入多条属性值
-- 英文逗号隔开
INSERT INTO `studio`(`name`)VALUES('打啊'),('z'),('u');
3.4、修改
-- 修改语句
-- 修改多条属性值时,只需要将各属性之间用逗号隔开
-- 如果不加where判断语句,那么会默认修改所以的数据,如果是修改名字,那么所以录入的名字都会被修改
UPDATE `studio` SET `name`='小邹',`age`=3 WHERE id=1;
-- 语法
-- UPDATE `表名` SET `colnum_name`=value,[`colnum_name`=value] WHERE id=1;
where条件运算符
操作符会返回布尔值
操作符 | 含义 | 范围 | 结果 |
---|---|---|---|
= | 等于 | 5=6 | false |
<>或者!= | 不等于 | 5<>6 | true |
> | |||
< | |||
<= | |||
>= | |||
BETWEEN … and … | 在某个范围内 | [2,5] | |
AND | && | ||
OR | || |
注意:
- colnum_name 是数据库的列,尽量带上
- value也可以是一个变量
3.5、删除
delete命令
-- 删除数据
-- 要加条件,不然会全部删除
DELETE FROM `studio` WHERE id=1;
TRUNCATE命令
作用:完全清空一个数据库表,表的结构和索引约束不会变!
-- 清空 student表
TRUNCATE TABLE`student`
delete的TRUNCATE区别
- 相同点:都能删除数据,都不会删除表结构
- 不同:
- TRUNCATE 重新设置 自增列 计数器会归零
- TRUNCATE 不会影响事务
DELETE删除的问题
,重启数据库,现象:
- InnoDB 自增列会从1开始(存在内存当中,断电即失)
Mysql8之前的版本自增数是取当数据个数的值+1, 8及之后会存储自增值,使用时自接拿(应该不是和Myisam一样的文件存储) - MyISAM 继续从上一个自增量开始 (存在文件中,不会丢失)
4、DQL查询数据(最重点)
4.1、DQL
(Data Query LANGUAGE:数据查询语言)
- 所有的查询操作都用它 Select
- 简单的查询,复杂的查询它都能做
- 数据库中最核心的语言,最重要的语句
- 使用频率最高的语句
注意:上面的顺序不能上下更改
4.2、指定查询字段
-- 查询全部学生 SELECT 字段 FROM 表
SELECT * FROM student
-- 查询指定字段
SELECT `StudentNO`,`name` FROM student
-- 别名,给结果起一个名字 AS 可以给字段起别名,也可以给表起别名
SELECT `StudentNO` AS 学号,`name` AS 学生姓名 FROM student AS s
-- 函数 Concat(a,b)
SELECT CONCAT('姓名:',name) AS 新名字 FROM student
语法:SELECT 字段,… FROM 表
去重 distinct
作用:去除SELECT查询出来的结果中重复的数据,只显示一条
-- 查询一下有哪些同学参加了考试,成绩
SELECT * FROM result -- 查询全部的考试成绩
SELECT `StudentNO` FROM result --查询有哪些同学参加了考试
SELECT DISTINCT `StudentNO` FROM result --将重复的查询结果去除并返回不重复结果
数据库的列
SELECT VERSION() -- 查询系统版本(函数)
SELECT 100*3-1 AS 查询结果 -- 用来计算(表达式)
SELECT @@auto_increment_increment -- 查询自增的步长(变量)
-- 学员考试成绩加1分后查看
SELECT `StudentNO`,`StudentResult`+1 AS '提分后' FROM result
数据库中的表达式:文本值,列,Null,函数,计算表达式,系统变量…
select 表达式
from 表
4.3、where条件子句
作用:检索数据中符合条件
的值
逻辑运算符
运算符 | 语法 | 描述 |
---|---|---|
and && | a and b a&&b | 逻辑与,两个都为真,结果为真 |
or || | a or b a||b | 逻辑或,其中一个为真,则结果为真 |
Not ! | not a ! a | 逻辑非,真为假,假为真 |
模糊查询:比较运算符
运算符 | 语法 | 描述 |
---|---|---|
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…其中的某一个值中,结果为真 |
-- ===================== 模糊查询 =======================
-- 查询姓刘的同学
-- 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 `BornDdte` IN NOT NULL
-- 查询没有有出生日期的同学 为空
SELECT `StudentNo`,`StudentName` FROM `Student`
WHERE `BornDdte` IN NULL
4.4、联表查询
join
join联表查询的语法:
SELECT 表别名1.属性1,属性2,属性3 -- 属性代表需要查询的全部栏目,如:name,age,sex。
FROM 表名 AS 表别名1
INNER JOIN 表名 AS 表别名2
ON 表别名1.属性1 = 表别名2.属性1 -- 如果两张表中都有属性1,如:都有name属性
注意:两表的共有属性在写 SELECT 时需要指明使用哪个表的属性,如:共有属性为 属性1
那么,使用格式为:
SELECT 表别名1.属性1
注意:使用的是 left join 时,别名使用最好是以左边表为基准的表的别名
-- C,D,E,F 是要从两张表中查询的属性,如A表中有:C,D,E两个属性,B表中有:C,F两个属性,C就是连接点
SELECT a.C,D,E,F
--left 和right 是相对连接时两个表的位置比如:
-- A表 是在左边 B表 在右边
FROM A AS a LEFT JOIN B AS b
-- 也可以用 join on 是固定语法,代表连接查询,
-- 等值查询 与 连接查询 作用相同
ON a.c = b.c -- 两表中相同的属性
-- 这里也可以用 where a.c=b.c ,这属于 等值查询
和ON配合使用前提下:
操作 | 描述 |
---|---|
Inner join | 两个表中有相同的属性(如:上面的C),且存在相同的属性值(如:两表的C属性下面都有一个叫张三的,那么代表匹配成功),就代表是查询的目标之一 |
left join | 如果A表的C属性下面有张三这一条属性值,但是B中没有张三这一条属性值,也会返回这一行,即:两表中有相同属性C时,以A表中的C属性下面的全部数据为基准,前提:A表在左边 |
right join | 两表中有相同属性C时,以A表中的C属性下面的全部数据为基准,前提:A表在右边 |
例:二表联表查询
-- 查询缺考的同学
SELECT s.studentNo,studentName,SubjectNo,StudentResult -- 列举,需要查询的属性
FROM student s -- 表明处于左边的表
LEFT JOIN result r
ON s.studentNo = r.studentNo
例:三表查询
-- 查询参加考试的同学信息:学号,学生姓名,科目名,分数
-- 分析需求:student表:学号,学生姓名,分数。result:分数,科目名。subject:科目名,分数...
-- 先student与result联表查询,并且用偏向result表的join方式联表
SELECT s.studentNO,studentName,SubjectNo,StudentResult
FROM student s
RICHT JOIN result r
ON r.studentNO = s.studentNO
-- 然后让 result 与 subject联表
INNER JOIN subject sub
ON r.SubjectNo = sub.SubjectNo
4.5、分页和排序
排序 ORDER
-- 排序 :升序 ASC,降序 DESC
-- ORDER BY 通过那个字段排序,怎么排
-- 查询的结果根据 成绩 降序 排序
SELECT s.studentNO,studentName,SubjectNo,StudentResult
FROM student s
RICHT JOIN result r
ON r.studentNO = s.studentNO
-- 然后让 result 与 subject联表
INNER JOIN subject sub
ON r.SubjectNo = sub.SubjectNo
ORDER BY StudentResult ASC -- 成绩升序排序
分页 LIMIT
SELECT s.studentNO,studentName,SubjectNo,StudentResult
FROM student s
RICHT JOIN result r
ON r.studentNO = s.studentNO
-- 然后让 result 与 subject联表
INNER JOIN subject sub
ON r.SubjectNo = sub.SubjectNo
LIMIT 0,5 -- 显示数据库查询结果的从第一条到第五条的数据,0:代表初始的位置,5:代表每次显示的条数
-- 公式:(第n页 - 1) X pageSize(每次显示的条数) 就是分页的方法
4.6、子查询
子查询:在where里面嵌套查询
-- 查询课程为 高等数学-2 且分数不小于 80 的同学的学号和姓名
-- 先用 IN 关键字来筛选符合条件的学生,IN 的括号内查询分数大于 80 的,然后利用子查询,查询科目为 高等数学-2的
SELECT StudentNo,StudentName FROM student WHERE StudentNo IN (
SELECT StudentNo FROM result WHERE StudentResult > 80 AND SubjectNo = (
SELECT SubjectNo FROM `subject` WHERE `SubjectName` = '高等数学-2'
)
)
4.7、分组和过滤
-- 查询不同课程的平均分,最高分,最低分
SELECT SubjectName,AVC(StudentResult) AS 平均分,MAX(StudentResult),MIN(StudentResult)
FROM result r
INNER JOIN `subject` sub
ON r.`subject` = sub.`subject`
GROUP BY r.subjectNo
HAVING 平均分 > 80 -- 只能在GROUP下面写,即分组后才能写过滤条件,即获得结果后对结果再次进行过滤
5、MySQL函数
官网:https://dev.mysql.com/doc/refman/5.7/en/sql-function-reference.html
5.1、常用函数
等等,具体看官方文档
5.2、聚合函数
函数名称 | 描述 |
---|---|
COUNT() | 计数 |
SUM() | 求和 |
AVG() | 平均值 |
MAX() | 最大值 |
MIN() | 最小值 |
… | … |
-- 统计表中数据
SELECT COUNT(`BornDate`) FROM student; -- Count(字段),会忽略所有的null值
SELECT COUNT(*) FROM student; -- 不会忽略null值,本质是计算行数
SELECT COUNT(1) FROM student; -- 不会忽略null值,本质是计算行数
-- 查询不同课程的平均分,最高分,最低分
SELECT SubjectName,AVG(StudentResult) AS 平均分,MAX(StudentResult),MIN(StudentResult)
FROM result r
INNER JOIN `subject` sub
ON r.`subject` = sub.`subject`
GROUP BY r.subjectNo
HAVING 平均分 > 80
5.3、数据库级别的MD5加密
6.事务
6.1、什么是事务
事务原则:ACID原则 原子性,一致性,隔离性,持久性
参考博客连接:https://blog.csdn.net/dengjili/article/details/82468576
原子性:(Atomicity)
原子性是指事务是一个不可分割的工作单位,事务中的操作要么都发生,要么都不发生。
一致性(Consistency)
事务前后数据的完整性必须保持一致,如:转账前后两个账户的总金额不会发生变化
持久性(Durability)
持久性是指一个事务一旦被提交,它对数据库中数据的改变就是永久性的,接下来即使数据库发生故障也不应该对其有任何影响
隔离性(Isolation)
事务的隔离性是多个用户并发访问数据库时,数据库为每一个用户开启的事务,不能被其他事务的操作数据所干扰,多个并发事务之间要相互隔离。
事务的隔离级别
脏读:
指一个事务读取了另外一个事务未提交的数据。
不可重复读:
在一个事务内读取表中的某一行数据,多次读取结果不同。(这个不一定是错误,只是某些场合不对)
虚读(幻读)
是指在一个事务内读取到了别的事务插入的数据,导致前后读取不一致。
(一般是行影响,多了一行)
执行事务
-- ======================= 事务 ====================
-- mysql 是默认开启事务自动提交的
SET autocommit = 0 -- 关闭
SET autocommit = 1 -- 开启
-- 手动处理事务
SET autocommit = 0 --关闭自动提交
-- 事务开启
START TRANSACTION -- 标记一个事务的开始,从这个之后的 SQL 都在同一个事务内
INSERT XX
INSERT XX
-- 提交:持久化(成功!)
COMMIT
-- 回滚:回到原来的样子(失败!)
ROLLBACK
-- 事务结束
SET autocommit = 1 -- 开启自动提交
-- 了解
SAVEPOINT 保存点名 -- 设置一个事务的保存点
ROLLBACK TO SAVEPOINT 保存点名 -- 回滚到保存点
RELEASE SAVEPOINT 保存点名 -- 撤销保存点
模拟场景
-- 模拟转账
CREATE DATABASE shop CHARACTER SET utf8 COLLATE utf8_general_ci -- 创建数据库 shop 并且编码方式为 utf-8
USE shop -- 使用数据库 shop
-- 创建 account 表
CREATE TABLE `account`(
`id` INT(3) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(30) NOT NULL,
`money` DECIMAL(9,2) NOT NULL, -- DECIMAL:字符串形式的浮点数
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' -- A减500
UPDATE account SET money = money + 500 WHERE `name` = 'B' -- B加500
COMMIT; -- y=提交事务,并且持久化
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 school.student ADD FULLTEXT `studentName`(`studentName`);
-- EXPLAIN 分析sql执行的状况
EXPLAIN SELECT * FROM student; -- 非全文索引
EXPLAIN SELECT * FROM student WHERE MATCH(studentName) ACAINST('刘');
7.2、索引原则
- 索引不是越多越好
- 不要对经常变动的数据加索引
- 小数据量的表不需要加索引
- 索引一般加在常用来查询的字段上
索引的数据结构
详细文章:
http://blog.codinglabs.org/articles/theory-of-mysql-index.html
B+Tree数据结构:
MyISAM,InnoDB都是采用此数据结构 (且带顺序访问指针) ,但是MyISAM采用非聚集(即:索引到的主键下面的data存放的是目标数据是地址),而InnoDB是将全部数据与主键聚集在一起,(即:索引到的主键下面的data存放的是目标数据)
B-Tree数据结构
关于两者区别,及结构可以看链接中的文章,
索引调优
InnoDB的主键最好采用自增的列,这样可以充分利用B+Tree的特性,等待下次插入数据时不需要移动庞大的数据,而是直接插入在尾部。
InnoDB索引实现
可以看出InnoDB必须存在主键,从左到右数值增大。
MyISAM索引实现
MyISAM引擎使用B+Tree作为索引结构,叶节点的data域存放的是数据记录的地址。下图是MyISAM索引的原理图:
8、权限管理和备份
8.1、用户管理
8.2、MySQL备份
为什么要备份:
- 保证数据不丢失
- 数据转移
MySQL数据库备份方式:
- 直接拷贝物理文件
- 在Sqlyog这种可视化工具中手动导出
9、规范数据库设计
9.2、三大范式
第一范式(1NF)
原子性: 保证每一列不可再分
第二范式(2NF)
前提:满足第二范式
每张表只描述一件事情
第三范式(3NF)
前提:满足第一范式 和 第二范式
第三范式需要确保数据表中的每一列数据都和主键直接相关,而不能间接相关。
(规范数据库的设计)
规范性 和 性能的问题
关联查询的表不得超过三张
- 考虑商业化的需求和目标,(成本,用户体验!)数据库的性能更加重要
- 在规范性能的问题的时候,需要适当的考虑一下 规范性!
- 故意给某些表增加一些冗余的字段。(从多表查询中变为单表查询)
- 故意增加一些计算列(从大数据量降低为小数据量的查询:索引)
10、JDBC
数据库驱动
应用程序需要利用驱动来操作数据库,而JDBK就是应用程序与驱动打交道的。
JDBC
SUN公司为了简化开发人员的(对数据库的统一)操作,提供了一个(java操作数据库的)规范,俗称 JDBC
这些规范的实现由具体的厂商去做
使用到的java包
java.sql
javax.sql
导入的数据库驱动包
10.1、第一个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,'zou','123456','zou@','2000-10-02'),(2,'fei','123456','fei@','1000-01-20'),(3,'ming','123456','ming@','2000-02-10');
2.新建java项目
3、导入数据库驱动
- 在项目下新建lib目录,将jar包复制到lib目录下,并右键选择 Add as Libray 导入到项目中
4、编写测试代码
import java.sql.*;
//我的第一个JDBC程序
public class test01 {
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 connection = DriverManager.getConnection(url, username, password); // 驱动管理.去获得连接()
// 4. 执行SQL的对象 Statement 执行SQL的对象
Statement statement = connection.createStatement(); // 创建一个执行SQL的对象
// 5. 执行SQL的对象 去 执行SQL, 可能存在结果,查看返回结果
String sql = "SELECT * FROM users";
// 执行sql并返回结果集,Q:想要查询用,Query,更新: U (删除和插入也在更新里面),返回的结 果集以链表形式存储
ResultSet resultSet = statement.executeQuery(sql);
//循环取出链表中的值
while (resultSet.next()){ // 如果还有值
// 可以通过 列名 取相应列的数据,如果不知道列的类型,可以使用Object
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();
}
}
结果:
id=1
name=zou
pwd=123456
email=zou@
birth=2000-10-02
==============================
id=2
name=fei
pwd=123456
email=fei@
birth=1000-01-20
==============================
id=3
name=ming
pwd=123456
email=ming@
birth=2000-02-10
==============================
Process finished with exit code 0
步骤总结:
1、加载驱动
2、连接数据库 DriverManager
3、获得执行sql的对象 Statement(这个对象不安全)
4、获得返回的结果集
5、释放连接
DriverManager
// DriverManager.registerDriver(new com.mysql.jdbc.Driver());之前的写法,意思是注册了一个驱动,但是由于Driver()的源码就是注册一个驱动,那么整行代码本质上注册了两次,故弃用。
Class.forName("com.mysql.jdbc.Driver");//现在注册且加载驱动的固定写法
Connection connection = DriverManager.getConnection(url, username, password);
// connection 代表数据库
// 数据库设置自动提交
// 事务提交
// 事务回滚
connection.rollback(); // 回滚相关
connection.commit(); // 事务提交相关
connection.setAutoCommit(); //事务自动提交相关 参数为false是关闭自动提交事务,且开启事务(此时需要手动提交事务,即调用commit()函数)
URL
String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true";
// mysql 默认端口号为: 3306
//jdbc:mysql://主机地址:端口号/数据库名?参数1&参数2&参数3
// oralce 默认端口号为: 1521
//jdbc:oracle:thin:@localhost:1521:sid
Statement 执行SQL 的对象 PrepareStatement 执行SQL 的对象
String sql = "SELECT * FROM users"; // 编写SQL语句
statement.executeQuery(); // 查询操作,返回 ResultSet
statement.execute(); // 执行任何SQL
statement.executeUpdate(); // 更新、插入、删除。都是用这个,返回一个受影响的行数
ResultSet 查询的结果集: 封装了所有的查询结果
获得指定的数据类型
resultSet.getObject(); //不知道列类型时使用
//知道列类型时使用
resultSet.getString();
resultSet.getInt();
resultSet.getFloat();
resultSet.getDate();
...
遍历,指针
resultSet.beforeFirst(); // 移动到最前面
resultSet.afterLast(); // 移动到最后面
resultSet.next(); // 移动到下一个数据
resultSet.previous(); // 移动到前一行
resultSet.absolute(row); // 移动到指定行
释放资源
// 6. 释放连接
resultSet.close();
statement.close();
connection.close();
10.2、statement对象
CRUD操作-create
使用executeUpdate(String sql)方法完成数据添加,示例操作:
Statement st = conn.createStatement();
String sql = "insert into user(。。。.) values(...)";
int num = st.executeUpdate(sql);
if(num>0){
System.out.println("插入成功!!!");
}
CRUD操作 - delete
Statement st = conn.createStatement();
String sql = "delete from user where id=1";
int num = st.executeUpdate(sql);
if(num>0){
System.out.println("插入成功!!!");
}
CRUD操作 - update
Statement st = conn.createStatement();
String sql = "update user set name='' where name=''";
int num = st.executeUpdate(sql);
if(num>0){
System.out.println("修改成功!!!");
}
CRUD操作 - read
Statement st = conn.createStatement();
String sql = "select * from user where id=1";
int num = st.executeUpdate(sql);
if(num>0){
//根据获取列的数据类型,分别调用rs的相应方法映射到java对象中
}
完整代码实现:
-
Properties(Java.util.Properties),该类主要用于读取Java的配置文件
-
JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");
- 得到 JdbcUtils类 的Class对象,通过Class的 getClassLoader() 方法获得加载 JdbcUtils类 的类加载器对象 ClassLoader ,然后通过ClassLoader的 getResourceAsStream方法 从加载路径取得文件的输入流(会通过当前的ClassLoader的findResource方法查找指定文件)
- 详情链接:https://www.cnblogs.com/yadongliang/p/7920053.html
第一步:编写java类型能够读取的配置文件 db.properties ,文件中存放
driver
、url
、username
、password
的数据库连接需要的参数
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true
username=root
password=123456
第二步:写关于JDBC驱动的工具类(将需要用到的重复操作集中,减少耦合性)
package lesson01.utils;
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 {
// 获得配置文件 db.properties 的输入流
InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");
// 读取配置文件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 (Exception e) {
e.printStackTrace();
}
}
// 获取连接
public static Connection getConnection() throws SQLException {
return DriverManager.getConnection(url,username,password);
}
// 释放连接资源,Connection:代表数据库, Statement:执行SQL的对象, ResultSet:返回的结果集
public static void release(Connection conn, Statement st, ResultSet rs) {
if (rs != null){
try {
rs.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if (st != null){
try {
st.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if (conn != null){
try {
conn.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
}
第3.1步,编写测试类,测试插入数据操作
package lesson01;
import lesson01.utils.JdbcUtils;
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 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`)" +
"VALUES(4,'zou4','123456','1160653906@qq.com','2020-10-10')";
int i = st.executeUpdate(sql);
if (i > 0)
System.out.println("插入成功!");
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(conn,st,rs);
}
}
}
3.2、删除数据
将添加数据
String sql = "INSERT INTO users(id,`NAME`,`PASSWORD`,`email`,`birthday`)" +
"VALUES(4,'zou4','123456','1160653906@qq.com','2020-10-10')";
的数据库语句,改为删除的数据库语句即可:
String sql = "DELETE FROM users WHERE id = 4";
3.3、更改数据
同理、将 sql 语句改为:
String sql = "UPDATE users SET `NAME` = 'zoufeiming' WHERE id = 1";
3.4、查询数据
//package lesson01;
//
//import lesson01.utils.JdbcUtils;
//
//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 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`)" +
// "VALUES(4,'zou4','123456','1160653906@qq.com','2020-10-10')";
//
// int i = st.executeUpdate(sql);
// if (i > 0)
// System.out.println("插入成功!");
// } catch (SQLException e) {
// e.printStackTrace();
// }finally {
// JdbcUtils.release(conn,st,rs);
// }
// }
//
//}
package lesson01;
import lesson01.utils.JdbcUtils;
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 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.3、SQL注入
1、什么是SQL注入
SQL注入即是指web应用程序对用户输入数据的合法性没有判断或过滤不严,攻击者可以在web应用程序中事先定义好的查询语句的结尾上添加额外的SQL语句,在管理员不知情的情况下实现非法操作,以此来实现欺骗数据库服务器执行非授权的任意查询,从而进一步得到相应的数据信息。
2、SQL注入的代码实现
import lesson01.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class SQL_injection {
public static void main(String[] args) {
login(" 'or '2=2"," 'or '1=1");
}
//登陆
public static void login(String username,String password){
Connection conn = null;
Statement st = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection(); // 获得数据库连接
st = conn.createStatement(); // 获得SQL的执行对象
String sql = "select * from users where `NAME`='"+username+"' AND `PASSWORD` = '"+password+"'";
rs = st.executeQuery(sql); // 查询完会返回结果集
// 循环输出想要的结果
while (rs.next()){
System.out.println(rs.getString("NAME"));
System.out.println(rs.getString("password"));
System.out.println("=================");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(conn,st,rs);
}
}
}
可以看出,本应该写成用户名和密码的参数,被写成了额外的SQL语句,从而使所有的数据库信息暴露
login(" 'or '2=2"," 'or '1=1");
上面代码结果:包含了数据库中全部的信息,因为 用了 or 语句且 2=2 恒成立。
zou
123456
=================
fei
123456
=================
ming
123456
=================
zou4
123456
=================
4、解决办法:PreparedStatement,可以对需要查询的语句先行检查,然后在决定是否查询和返回值。
10.4、PreparedStatement对象
安全的,防止SQL注入的,效率相比 Statement 类更高的
把传递进来的参数当做字符
假设其中存在转义字符,就直接忽略, ’ 会被直接转义
10.4.1、插入数据
package lesson01;
import lesson01.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Date;
public class TestInsert_P {
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); // id
st.setString(2,"zou5");
st.setString(3,"123456");
st.setString(4,"1160653906@qq.com");
//java.sql.Date 和 util.Date ,new Date().getTime():获得当前时间年月日(时间戳)
st.setDate(5,new java.sql.Date(new Date().getTime()));
//执行
int i = st.executeUpdate();
if (i > 0){
System.out.println("插入成功");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(conn,st,null);
}
}
}
10.4.2、删除数据
与上面代码的区别:
String sql = "insert into users(id,`NAME`,`PASSWORD`,`email`,`birthday`) values(?,?,?,?,?)";
变成:
String sql = "delete from users where id = ?";
st.setInt(1,5); // id
st.setString(2,"zou5");
st.setString(3,"123456");
st.setString(4,"1160653906@qq.com");
//java.sql.Date 和 util.Date ,new Date().getTime():获得当前时间年月日(时间戳)
st.setDate(5,new java.sql.Date(new Date().getTime()));
变成:
st.setInt(1,4); // id
10.4.3、更新数据
与上面代码的区别:
String sql = "insert into users(id,`NAME`,`PASSWORD`,`email`,`birthday`) values(?,?,?,?,?)";
变成:
String sql = "update users set `NAME`=? where id=?;";
st.setInt(1,5); // id
st.setString(2,"zou5");
st.setString(3,"123456");
st.setString(4,"1160653906@qq.com");
//java.sql.Date 和 util.Date ,new Date().getTime():获得当前时间年月日(时间戳)
st.setDate(5,new java.sql.Date(new Date().getTime()));
变成:
st.setString(1,"zouzou");
st.setInt(2,1); // id
10.4.4、查询数据
package lesson01;
import lesson01.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class TestInsert_P {
public static void main(String[] args) {
Connection conn = null;
PreparedStatement st = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection();
String sql = "select * from users where id = ?";
st = conn.prepareStatement(sql); // 预编译SQL,先写sql,然后不执行
st.setInt(1,2);
//执行
rs = st.executeQuery();
if (rs.next()){
System.out.println(rs.getString("NAME"));
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(conn,st,rs);
}
}
}
10.8、事务
ACID原则
原子性:要么全部完成,要么都不完成
一致性:总数不变
隔离性:多个进程互不干扰
持久性:一旦提交不可逆,持久化到数据库中了
隔离性的问题:
脏读:一个事务读取了另一个事务没有提交的事务
不可重复的:在同一事务中,重复读取表中的数据,表数据发送了改变
虚读(幻读):在一个事务内,读取到了别人插入的数据,导致前后读出来的结果不一致
例:
import lesson01.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class test02 {
public static void main(String[] args) throws SQLException {
Connection conn = null;
PreparedStatement st = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection();
//关闭自动提交,开启事务
conn.setAutoCommit(false);
String sql1 = "update action set money = money - 100 where name ='A'";
st = conn.prepareStatement(sql1);
st.executeUpdate();
String sql2 = "update action set money = money + 100 where name ='B'";
st = conn.prepareStatement(sql2);
st.executeUpdate();
//业务完毕,提交事务
conn.commit();
System.out.println("成功!");
} catch (SQLException e) {
conn.rollback();//如果失败,则回滚,注:可以不用显示定义,默认会回滚的
e.printStackTrace();
}finally {
JdbcUtils.release(conn,st,rs);
}
}
}
10.9、数据库连接池
连接 – 释放 十分浪费资源
池化技术:准备一些预先的资源,过来就连接预先准备好的
有最小,最大连接数,以及等待连接数
等待超时的时间。
本质上:编写连接池,实现一个接口 DataSource
开源数据源实现
DBCP
C3P0
Druid:阿里巴巴
使用这些连接池之后,我们在项目开发中就不需要编写连接数据库的代码了