MySQL学习

文章目录

1.初识MySQL
- 1.1为什么要学习数据库
- 1.2什么是数据库
- 1.3 数据库分类
- 1.4 连接数据库
- 1.5 注释
- 1.6 数据库语言分类
2. 操作数据库
- 2.1 操作数据库
- 2.2 数据库的列类型
- 2.3数据库的字段属性
- 2.4 创建数据库表
- 2.5 数据表的类型
- 2.6 Alter ,Drop
3. MySQL的数据管理
- 3.1 外键(了解即可)
3.2DML语言（重点）
4.DQL(Data Query LANGUAGE) 数据库查询语言
- 4.1select 完整语法
- 4.2指定查询字段
- 4.3 数据库中的列(表达式)
- 4.4 where条件子句
- 4.5模糊查询操作符详解
- 4.6联表查询
- - 自连接（父子，权限）
- 4.7分页和排序
- 4.8子查询
- 4.9分组和过滤
5.MySQL函数
- 5.1常用函数
- 5.2聚合函数
- 5.3数据库级别的MD5加密
6.事务
- 6.1什么事事务
7.索引
- 7.1索引的分类
- 7.2测试索引
- 7.3索引原则
8.权限管理和数据库备份
- 8.1用户管理
- 8.2MySQL备份
9.设计数据库和三大范式
10.JDBC
- SQL注入
- 10.5PreparedStatement对象
- 10.6使用IDEA连接数据库
- 10.7事务
- 10.8数据库连接池

1.初识MySQL

1.1为什么要学习数据库

岗位需求
大数据时代
存数据
数据库是所有软件体系中最核心的存在、

1.2什么是数据库

数据库（DB DataBase）
概念：数据仓库，安装在操作系统之上
作用: 存储数据，管理数据

1.3 数据库分类

关系型数据库：(SQL)
MySQL，Oracle，Sql Server ,DB2,SQLlite
通过表和表之间行和列之间的关系进行数据存储，学员信息表，等等

非关系型数据库：(NoSQL)
Redis, MongDB
非关系型数据库，对象存储，通过对象的自身属性来决定

DBMS(数据库管理系统)

数据库的管理软件
MySQL 数据库管理系统

1.4 连接数据库

mysql -u root -p 连接数据库

修改密码：

update mysql.user set authentication_string=password('123456') where user='root' and Host='localhost'

show databases;-- 查看所有的数据库
use shcool --切换数据库
show tables --查看所有的表
describe student --显示数据库中所有表的信息
exit；-- 退出连接

1.5 注释

– 单行注释

/* */多行注释

1.6 数据库语言分类

数据库xxx语言 CRUD 增删改查
DDL 定义
DML 操作
DQL 查询
DCL 控制

2. 操作数据库

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

2.1 操作数据库

创建数据库
CREATE DATABASE [IF NOT EXISTS] (test)
删除数据库
DROP DATABASE [IF EXISTS] ·test·
使用数据库
USE school --如果表的名字或者字段是一个特殊字符，就需要带 ·· (Tab键上面)
4.查看数据库
SHOW DATABASES；
5. 查看数据表、
show databases

2.2 数据库的列类型

2.1.1 数值

1.tinyint 1个字节
2.smallint 2个字节
3.mediumint 3个字节
4.int 4个字节
5.bigint 8个字节

6.float 浮点数 4个字节
7.double 浮点数 8个字节
8.decimal 字符串形式的浮点数金融计算的时候，一般使用 decimal

2.2.2 字符串

1.char 字符串固定大小的 0~255
2.varchar 可变字符串 0~65535 常用的string
3.tinytext 微型文本 2^8-1
4.text 文本串 2^16-1

2.2.3 时间日期

1.data YYYY-MM-DD
2.time HH:mm:ss
3.datatime YYYY-MM-DD HH:mm:ss
4.timestamp 时间戳 1970.1.1到现在的毫秒数！也较为常用
5.year 年份表示

2.2.4 null

没有值，未知

2.3数据库的字段属性

Unsigned：
无符号的整数
声明了该列不能声明为负数

Zerofill：
0填充

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

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

默认：
设置默认的值

拓展：
每一个表都必须存在以下5个字段！未来做项目用的，表示一个记录存在意义
id 主键
·version· 乐观锁
is_delete 伪删除
gmt_create 创建时间
gmt_update 修改时间

2.4 创建数据库表

CREATE TABLE `mon`(
	`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
	`pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
	PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

常用命令

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

删除数据表

DROP TABLE teach

2.5 数据表的类型

关于数据库引擎：
InnoDB 默认使用
MyISAM 早些年使用的
在这里插入图片描述
常规使用操作：
MyISAM 节约空间速度较快
INNODB 安全性高，事务的处理，多表多用户操作。

在物理空间中存在的位置
所有的数据库文件都存在data目录下，
本质还是文件的存储！

MySQL引擎在物理文件上的区别
InnoDB在数据库表中只有一个*.frm文件，以及上级目录下的ibdata1文件
2.MyISAM对应的文件
*.frm 表结构的定义文件
*.MYD 数据文件(data)
*.MYI 索引文件(index)

设置数据表的字符集编码

charset=utf8

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

2.6 Alter ,Drop

注意：
什么叫做约束？

约束，就是要求数据需要满足什么条件的一种“规定”。
主要有如下几种约束：

主键约束：形式： primary key ( 字段名);
含义(作用)：使该设定字段的值可以用于“唯一确定一行数据”，其实就是“主键”的意思。
唯一约束：形式： unique key ( 字段名);
含义(作用)：使该设定字段的值具有“唯一性”，自然也是可区分的。
外键约束：形式： foreign key ( 字段名) references 其他表名(对应其他表中的字段名) ;
含义(作用)：使该设定字段的值，必须在其给定的对应表中的对应字段中已经有该值了。

4.非空约束：形式： not null，其实就是设定一个字段时写的那个“not null”属性。
这个约束只能写在字段属性上；

5.默认约束：形式： default XX值；其实就是设定一个字段时写的那个“default 默认值”属性
这个约束只能写在字段属性上；

6.检查约束：形式： check(某种判断语句)，比如：create table tab1 (age tinyint,check (age>=0 and age <100) /这就是检查约束/)

其实，主键约束，唯一约束，外键约束，只是“同一件事情的2个不同角度的说法”，他们同时也称为“主键索引”，“唯一索引”，“外键索引”。
原文链接

修改表

-- 修改表名
ALTER TABLE teacher RENAME AS teach
-- 增加表的字段
ALTER TABLE teach ADD age INT(11)
-- 修改表的字段
ALTER TABLE teach MODIFY age VARCHAR(11)  -- 修改约束   不能重命名
ALTER TABLE teach CHANGE age age VARCHAR(8)  -- 字段重命名 可以修改约束
ALTER TABLE result CHANGE studentno studentno INT(5) NOT NULL

RENAME TABLE
    tbl_name TO new_tbl_name
    [, tbl_name2 TO new_tbl_name2] ...

-- 带中括号的可以省略，没带的不能省略
ALTER TABLE res RENAME [TO] result
RENAME TABLE res TO result  -- 这两个语句是相等的，修改表名

-- RENAME TABLE, unlike ALTER TABLE, can rename multiple tables within a single statement:
RENAME TABLE old_table1 TO new_table1,
             old_table2 TO new_table2,
             old_table3 TO new_table3;

-- 删除数据表的字段
ALTER TABLE teach DROP age

modify和change的区别：
其实就是一句话：两者都能修该表的定义，modify只能改字段数据类型完整约束，不能改字段名，但是change可以。
删除表

3. MySQL的数据管理

3.1 外键(了解即可)

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

CREATE TABLE `grade`(
	`gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年纪id',
	`gradename` VARCHAR(50) NOT NULL COMMENT '年纪name',
	PRIMARY KEY(`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8


-- 学生表的gradeid字段 要引用年级表的gradeid
-- 定义外键key  KEY `FK_gradeid` (`gradeid`),
-- 给这个外键添加约束（执行引用） CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade`(`gradeid`)
CREATE TABLE `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(3) NOT NULL DEFAULT '男' COMMENT '性别',
    `gradeid` INT(10) NOT NULL COMMENT '学生的年级',
    PRIMARY KEY(`id`),
    KEY `FK_gradeid` (`gradeid`),
    CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade`(`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

删除有外键关系的表的时候，必须要删除引用别人的表，再删除被引用的表。。。

方式二：创建表的时候没有外键关系，

ALTER TABLE `student` -- alter table 表 add constraint 约束名 foreign key(作为外键的列) references 哪个表（那个字段）
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY(`gradeid`) REFERENCES `grade`(`gradeid`)

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

最佳实践：
数据库就是单纯的表，只用来存数据，只有行（数据）和列（字段）
我们想使用多张表的数据，想使用外键（程序去实现），即逻辑外键

3.2DML语言（重点）

数据库意义：数据存储，数据管理
DML语言：数据操作语言

3.2.1 insert

-- 插入数据（添加）
-- insert into 表名([字段名1，字段名2，字段名3，...]) values('值1')，('值2'),('值3',...)

INSERT INTO `grade`(`gradename`) VALUES('大四')

-- 一般写插入语句，我们一定要将数据和字段一一对应！
INSERT INTO `grade`(`gradename`) VALUES('大三'),('大一')

INSERT INTO `student`(`name`,`pwd`,`sex`,`gradeid`) VALUES('张三','123456','男','3'),('里斯','2342','女','3')

注意事项：
1，字段和字段之间使用英文逗号隔开
2，字段是可以省略的，但是后面的值必须要意义对应，不能少
3，可以同时插入多条数据，values后面的值，需要使用英文逗号隔开即可

3.2.2 update

-- 修改学员的名字
UPDATE `student` SET `name`='张三' WHERE id=1
-- 不指定条件的情况下，会修改所有的表
UPDATE `student` SET `name`='zhanghaixia'
-- 修改多个属性，逗号隔开
UPDATE `student` SET `name`='haha',`pwd`='2342234' WHERE id=1

条件：where 子句运算符 id等于某个值，大于某个值，在某个区间内修改
在这里插入图片描述

3.2.3 delete

DELETE FROM `student`
-- 
TRUNCATE `student`

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

delete删除的问题,重启数据库,现象。。。
innodb 自增列会重1开始（存在内存中的，断电即失）
MyISAM 继续从上一个自增量开始（存在文件中的,不会丢失）

4.DQL(Data Query LANGUAGE) 数据库查询语言

特点：1.所有的查询操作都用它 Select
2.简单的查询，复杂的查询他都能做
3.数据库中最核心的语言，最重要的语句
4.使用频率最高的语句

数据表准备

CREATE TABLE `student`(
	`studentno` INT(4) NOT NULL COMMENT '学号',
    `loginpwd` VARCHAR(20) DEFAULT NULL,
    `studentname` VARCHAR(20) DEFAULT NULL COMMENT '学生姓名',
    `sex` TINYINT(1) DEFAULT NULL COMMENT '性别，0或1',
    `gradeid` INT(11) DEFAULT NULL COMMENT '年级编号',
    `phone` VARCHAR(50) NOT NULL COMMENT '联系电话，允许为空',
    `address` VARCHAR(255) NOT NULL COMMENT '地址，允许为空',
    `borndate` DATETIME DEFAULT NULL COMMENT '出生时间',
    `email` VARCHAR (50) NOT NULL COMMENT '邮箱账号允许为空',
    `identitycard` VARCHAR(18) DEFAULT NULL COMMENT '身份证号',
    PRIMARY KEY (`studentno`),
    UNIQUE KEY `identitycard`(`identitycard`),
    KEY `email` (`email`)
)ENGINE=MYISAM DEFAULT CHARSET=utf8;

CREATE TABLE `grade` (
  `gradeid` int(11) NOT NULL AUTO_INCREMENT COMMENT '年级编号',
  `gradename` varchar(50) NOT NULL COMMENT '年级名称',
  PRIMARY KEY (`gradeid`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8

CREATE TABLE `subject` (
  `subjectno` int(11) NOT NULL AUTO_INCREMENT COMMENT '课程编号',
  `subjectname` varchar(50) DEFAULT NULL COMMENT '课程名称',
  `classhour` int(4) DEFAULT NULL COMMENT '学时',
  `gradeid` int(4) DEFAULT NULL COMMENT '年级编号',
  PRIMARY KEY (`subjectno`)
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8

CREATE TABLE `result` (
  `studentno` int(10) DEFAULT NULL,
  `subjectno` int(4) NOT NULL COMMENT '课程编号',
  `examdate` datetime NOT NULL COMMENT '考试日期',
  `studentresult` int(4) NOT NULL COMMENT '考试成绩',
  KEY `subjectno` (`subjectno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

4.1select 完整语法

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}];
-- 指定查询的记录从那一条到哪一条

SQL的执行顺序：

–第一步：执行FROM
–第二步：WHERE条件过滤
–第三步：GROUP BY分组
–第四步：执行SELECT投影列
–第五步：HAVING条件过滤
–第六步：执行ORDER BY 排序

4.2指定查询字段

-- 查询全部的学生  select 字段 from 表
SELECT * FROM student
-- 查询指定字段
SELECT `studentno`,`studentname` FROM student

-- 别名，给结果起一个名字 AS 可以字段其别名，也可以给表起别名
SELECT `studentno` AS 学号,`studentname` AS 学生姓名 FROM student AS zhang

-- 函数

SELECT CONCAT('姓名: ',studentname) AS 新名字 FROM student

SELECT * FROM result  -- 查询全部的考试成绩
-- 
SELECT DISTINCT `studentno` FROM result ORDER BY studentno ASC -- 发现重复数据，去重,只显示一条

4.3 数据库中的列(表达式)

SELECT VERSION() -- 查询系统版本  函数被

SELECT 100*8-324 AS 计算结果 -- 用来计算  表达式

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

-- 学员考试成绩 + 1分查看
SELECT `studentno` AS '学号',`studentresult` AS '提分前',`studentresult` +10 AS '提分后' FROM result

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

4.4 where条件子句

作用：检索数据中符合条件的值
搜素的条件有一个或多个表达式组成！结果是一个布尔值
在这里插入图片描述
注意：比较符号尽量使用英文符号。

模糊查询

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

查询范围内数据

SELECT `studentno`,`studentresult` FROM result
WHERE studentresult>=80 AND studentresult <=100

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

4.5模糊查询操作符详解

在这里插入图片描述 `-- =模糊查询======
– 查询
– 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 ‘%阿道%’

– 查询具体的一个或多个值
SELECT studentno,studentname FROM student
WHERE studentno IN (1001,1002,1003);

– 查询在在北京的学生

SELECT studentno,studentname FROM student
WHERE address IN (‘北京朝阳’,‘山东青岛’);

– 查询地址为空的同学 null ‘’

SELECT studentno,studentname FROM student
WHERE address=’’ OR address IS NULL
`

4.6联表查询

MySQL七种join理论

-- ==============联表查询=============
-- 查询参加了考试的同学（学号，姓名，科目编号，分数）
SELECT * FROM student
SELECT * FROM result

/*
1.分析需求，分析查询的字段来自哪些表
2.确定使用哪种连接查询？ 7种
确定交叉点（这两个表中那个数据是相同的）
判断的条件：学生表中的studentno=成绩表中的studentno
*/

-- join on 连接查询
-- where 等值查询
SELECT s.studentno,studentname,subjectno,studentresult
FROM student AS s
INNER JOIN result AS r
WHERE s.studentno=r.studentno 

SELECT s.studentno,studentname,subjectno,studentresult
FROM student AS s
RIGHT 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

-- 查询缺考的同学
SELECT s.studentno,studentname,subjectno,studentresult
FROM student AS s
LEFT JOIN result r
ON s.studentno=r.studentno
WHERE studentresult IS NULL

-- 查询参加了考试的同学的信息 学号，学生姓名，科目名，分数
SELECT s.studentno,studentname,subjectname,studentresult
FROM student AS s
INNER JOIN result r
ON r.studentno = s.studentno
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno

-- 查询参加了数据库结构-3 考试的同学的信息
SELECT s.studentno,studentname,subjectname,studentresult
FROM student AS s
INNER JOIN result AS r
ON s.`studentno` = r.`studentno`
INNER JOIN `subject` AS sub
ON r.`subjectno` = sub.`subjectno`
WHERE subjectname = '数据库结构-3'

自连接（父子，权限）

CREATE TABLE `category`(
  `categoryid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主题id',
  `pid` INT(10) NOT NULL COMMENT '父id',
  `categoryname` VARCHAR(50) NOT NULL COMMENT '主题名字',
  PRIMARY KEY (`categoryid`)
)ENGINE=INNODB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8

INSERT INTO `category`(`categoryid`,`pid`,`categoryname`)
VALUES('2','1','信息技术'),
('3','1','软件开发'),
('4','3','数据库'),
('5','1','美术设计'),
('6','3','web开发'),
('7','5','ps技术'),
('8','2','办公信息');

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

父类：

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

子类：

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

SELECT a.`categoryname` AS '父栏目',b.`categoryname` AS '子栏目' 
FROM `category` AS a,`category` AS b
WHERE a.`categoryid` = b.`pid`

– 经典权限表结构

结果：
在这里插入图片描述

4.7分页和排序

排序：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='高等数学-3'
ORDER BY studentresult ASC

– 分页：为什么要分页？
– 缓解数据库压力，给人的体验更好
– 语法： limit(查询的起始下标，pageSize)

LIMIT 3,5
-- 查询 高等数学-3 课程成绩排名前十的学生，并且分数要大于等于70分
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='高等数学-3' AND studentresult>=70
ORDER BY studentresult DESC

4.8子查询

where (这个值是计算出来的)
本质：在where语句中嵌套一个子查询
where（select*form）


-- 查询参加了 高等数学-3 考试且成绩大于等于80的学生的学号和姓名


SELECT s.studentno,studentname
FROM student AS s
INNER JOIN result AS r
ON s.`studentno`=r.`studentno`
WHERE studentresult>=80 AND `subjectno`= (
	SELECT subjectno FROM `subject`
	WHERE subjectname='高等数学-3'
)


SELECT studentno,studentname FROM student WHERE studentno IN(
  SELECT studentno FROM result WHERE studentresult> 80 AND subjectno=(
    SELECT subjectno FROM `subject` WHERE `subjectname` = '高等数学-3'
  )
)

4.9分组和过滤

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

any_value() 会选择被分到同一组的数据里第一条数据的指定列值作为返回数据

5.MySQL函数

mysql官网

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('asdfasdfasdfasdf') -- 返回字符串的长度
SELECT CONCAT('das','最近爱上') -- 拼接
SELECT INSERT('张海峡',1,1,'哈')  -- 从某个位置开始替换某个长度
SELECT LOWER('SDFA')
SELECT UPPER('fasdfasd')
SELECT INSTR('zhanghaixia','h')  -- 返回指定字符第一次出现的位置
SELECT REPLACE('zhanghaixia','hai','hahah')  -- 替换指定字符串的出现项
SELECT SUBSTR('zhanghaixia',4,6) -- 返回指定的子字符串（源字符串，起始位置，截取的长度）
SELECT REVERSE('zhanghaixia')  -- 反转

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 MINUTE(NOW())
SELECT SECOND(NOW())

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

5.2聚合函数

在这里插入图片描述

-- ===============聚合函数===============
-- 都能够统计表中的数据
SELECT COUNT(studentname) FROM student  --  Conut(字段)，会忽略所有的null值  指定列
SELECT COUNT(*) FROM student -- 不会忽略null值   本质，计算行数
SELECT COUNT(1) FROM student -- 不会忽略null值
 
 
SELECT SUM(`Studentresult`) AS 总和 FROM result
SELECT AVG(`Studentresult`) 平均分 总和 FROM result
SELECT MAX(`Studentresult`) 最高分 总和 FROM result
SELECT MIN(`Studentresult`) 最低分  总和 FROM result

5.3数据库级别的MD5加密

什么是MD5？
1.主要增强算法复杂度和不可逆性
2.MD5不可逆，具体的值的md5是一样的
3.MD5破解网站原理，背后有一个字典，md5加密后的值加密前的值

CREATE TABLE `testmd5`(
	`id` INT(10) NOT NULL,
	`name` VARCHAR(20) NOT NULL,
	`pwd` VARCHAR(50) NOT NULL,
	PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

@[TOC](这里写自定义目录标题)
INSERT INTO testmd5 VALUES(1,'zhanghaixia','123456'),(2,'zhangsan','234234'),(3,'lisi','23424234')

UPDATE testmd5 SET pwd=MD5(pwd) WHERE id = 1

INSERT INTO testmd5 VALUES(4,'wangmazi',MD5('123456'))

-- 如何校验：将用户传递进来的密码，进行MD5加密，然后比对加密后的值
SELECT * FROM testmd5 WHERE `name`='wangmazi' AND pwd=MD5('123456')

6.事务

6.1什么事事务

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

1.SQL执行 A给B 转账 A1000 —> 200 B200

事务ACID

原子性(Atomicity)：针对同一个事务

一致性(Consistency)：针对一个事务操作前与操作后的状态一致

持久性(Durability)：表示事务结束后的数据不随着外界原因导致数据丢失。事务一旦提交，则不可逆。

隔离性(Isolation)：针对多个用户同时操作，主要是排除其他事务对本次事务的影响。
隔离所导致的一些问题

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


-- 手动处理事务
SET autocommit = 0  -- 关闭自动提交
-- 事务开启
START TRANSACTION  -- 标记一个事务的开始，从这个sql之后的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;
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'),('B','10000');


-- 模拟转账 ：事务
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)：避免重复的列出现，可以重复，多个列都可以标识为唯一索引

3 常规索引(KEY/INDEX)：默认的，index，key关键字来设置

全文索引(FullText)：在特定的数据库引擎下才有，MyISAM
快速定位数据

基础语法：

SHOW INDEX FROM student
-- 增加一个全文索引  索引名（列名）
ALTER TABLE `student` ADD FULLTEXT INDEX `studentname`(`studentname`)、
-- Explain 分析sql执行的状况
EXPLAIN SELECT * FROM student

EXPLAIN SELECT * FROM student WHERE MATCH(studentname) AGAINST('张')

mysql优化-看懂explain

7.2测试索引

CREATE TABLE `app_user1` (
`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 NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT = 'app用户表'

-- 插入100万数据.
DELIMITER $$  -- 写函数之前必须要写，标志,分隔符
CREATE FUNCTION mock_data4()
RETURNS INT
BEGIN
	DECLARE num INT DEFAULT 1000000;
	DECLARE i INT DEFAULT 0;
	WHILE i<num DO
		INSERT INTO `app_user1`(`name`,`email`,`phone`,`gender`,`password`,`age`)
		VALUES(CONCAT('用户',i),'19224305@qq.com','123456789',FLOOR(RAND()*2),UUID(),FLOOR(RAND()*100));
		SET i=i+1;
	END WHILE;
	RETURN i;
END;

SELECT mock_data4(); -- 执行此函数 生成一百万条数据

-- CREATE INDEX 索引名 on(表) 字段
CREATE INDEX id_app_user1_name ON app_user1(`name`)

未加索引

在这里插入图片描述

加了索引之后：SELECT * FROM app_user1 WHERE name = ‘用户99998’;，搜素过程
在这里插入图片描述

索引在用户很小的时候，用处不大，大是在大数据的时候，区别十分明显

7.3索引原则

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

索引的数据结构：

1.Hash类的索引
2.Btree INNODB的默认数据结构

8.权限管理和数据库备份

8.1用户管理

SQLyog可视化管理
用户表mysql.user

-- CREATE INDEX 索引名 on(表) 字段
CREATE INDEX id_app_user1_name ON app_user1(`name`)


EXPLAIN SELECT * FROM app_user1 WHERE `name` = '用户99998';

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

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

-- 修改指定用户的密码
SET PASSWORD FOR zhang=PASSWORD('12345678');

-- 重命名

RENAME USER zhang TO hai

-- 用户授权  all privileges 全部的权限，除了给被人授权都可以
GRANT ALL PRIVILEGES ON *.* TO hai

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

-- 撤销权限  revoke 哪些权限，在那个库撤销
REVOKE ALL PRIVILEGES ON *.* FROM hai

-- 删除用户
DROP USER hai

8.2MySQL备份

方式：1直接拷贝物理文件
2.可视化工具中手动导出
3.使用命令行导出mysqldumb
在这里插入图片描述

9.设计数据库和三大范式

10.JDBC

使用JDBC连接数据库六个步骤：

import java.sql.*;

public class first_jdbc {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        //Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver'
        // . The driver is automatically registered via the SPI and manual loading of
        // the driver class is generally unnecessary.
        //1.加载驱动
        //DriverManager.registerDriver(new com.mysql.cj.jdbc.Driver());
        Class.forName("com.mysql.cj.jdbc.Driver");

        //2.用户信息和url
        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();

        //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("PASSWORD="+resultSet.getObject("PASSWORD"));
            System.out.println("email="+resultSet.getObject("email"));
            System.out.println("birthday="+resultSet.getObject("birthday"));
        }

        //关闭资源
        resultSet.close();
        statement.close();
        connection.close();
    }
}

定义工具类

//这是一个配置文件，需要确保它被放在src目录下
driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true
username=root
password=123456

package 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 {
            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 (IOException | ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    //返回一个Connection 代表数据库
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(url,username,password);
    }


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

使用定义的工具类执行插入语句

import 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) throws SQLException {
        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,'zhang','123456','zs@sina.com','1980-12-04')";
           int i = st.executeUpdate(sql);
           if (i>0){
               System.out.println("插入成功");
           }
        } catch (SQLException throwable) {
            throwable.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,null);
        }
    }
}

SQL注入

sql存在漏洞，会被攻击到时数据泄露，SQL会被拼接

10.5PreparedStatement对象

PreparedStatement可以防止SQL注入，

10.6使用IDEA连接数据库

10.7事务

要么都成功，要么都失败
ACID原则
原子性
一致性
持久性：
隔离性：多个进程互不干扰

隔离性的问题：
脏读：一个事务读取了另一个没有提交的事务。
不可重复读：在同一个事务之内，重复读取表中的数据，表数据发生了改变
虚读：在同一个事务内，读取到了别人插入的数据，导致前后读出来的数据结果不一致

10.8数据库连接池

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

池化技术：准备一些预先的资源，过来就连接预先准备好的
最小连接数：
最大连接数：
等待超时

编写连接池，实现一个接口 DataSource

开源数据实现
DBCP
C3P0
Druid
使用这些数据库连接池之后，在项目开发中就不需要编写连接数据库的代码了

结论：
无论使用什么数据资源，本质还是一样的，DataSouce接口不会变

posted @ 2021-02-28 19:19 木有呂朋友阅读(32) 评论(0) 编辑收藏举报

刷新页面返回顶部

木有呂朋友