Mysql 5.7 学习

ㅤㅤㅤㅤㅤㅤㅤㅤㅤㅤㅤㅤㅤㅤㅤㅤ632

于 2024-08-18 21:11:53 发布

阅读量1k

点赞数 20

文章标签： mysql 学习数据库

本文链接：https://blog.csdn.net/weixin_64352622/article/details/141305389

版权

1.数据库与表

1.数据库字段数据的基本类型

数值：

数据类型字节数大小
tinyint 1 小
smallint 1 较小

数据类型	字节数	大小
tinyint	1	小
smallint	1	较小

| mediunint | 3 | 中等 |
| int | 4 | 标准的整数 |
| bigint | 8 | 较大的整数 |
| float | 4 | 浮点数 |
| double | 8 | 精度大的浮点数 |

字符串

数据类型字节数大小
char 0~255 字符串固定大小
varchar 0~65535 可变字符串(常用String)
tinytext 2^8-1 微型文本
text 2^16-1 文本串(保存大文本)=

数据类型	字节数	大小
char	0~255	字符串固定大小
varchar	0~65535	可变字符串(常用String)
tinytext	2^8-1	微型文本
text	2^16-1	文本串(保存大文本)=

日期

数据类型	字节数	取值范围	解释	格式	零值
time	3	-838：59：59~ 838：59：59	时间格式	HH:mm:ss	00:00:00
date	4	1000-01-01-9999-12-31	日期格式	YYYY-MM-DD	0000-00-00
datetime	8	1000-01-01 00:00:00~9999-12-31 23:59:59	常用时间格式	YYYY-MM-DD HH:mm:ss	0000-00-00 00:00:00
timestamp	4	1970-01-01 00:00:01~2038-01-19 03:14:07	时间戳	1970.1.1到现在的毫秒数	0000-00-00 00:00:00
year	1	1901~2155	年份	YYYY	0000

1.1引擎的区别(主要MYISAM和INNODB)

	myisam	innodb
事物支持	不支持	支持
数据行锁定	不支持	支持
外键的约束	不支持	支持
全文索引	支持	不支持
表空间的大小	较小	较大(约为2倍)

1.2 常规使用操作

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

1.3 Mysql引擎在物理文件上的区别

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

2.数据库表的创建

还可以调用SHOW CRETE TABLE; – 获取创建表的sql语句

CREATE TABLE IF NOT EXISTS 表名（

IF NOT EXISTS 意思：若表不存在则创建，存在则覆盖

字段 字段类型(字段大小) NOT NULL(注:是否为空) DEFAULT 默认值 COMMENT 字段注释

PRIMARY KEY(字段) 表示该字段不可重

）ENGIN引擎 CHARACTER SET 字符编码 = utf8

具体代码如下:

CREATE TABLE IF NOT EXISTS class（
	`name` varchar(10) NOT NULL DEFAULT `张三` COMMENT `姓名`,
	`id` int(3) NOT NULL AUTO_INCREMENT COMMENT `学号`,
	PRIMARY KEY(`id`)
）ENGINE = InnoDB CHARACTER SET= utf8

3.数据库、表的删除

-- 删除数据库
DROP DATABASE 数据库名;

-- 删除数据库的某个表
DROP TABLE 表名;

2.操作前缀简要意思

ALTER 改变

DROP 剔除

UPDATE 更新

SELECT 选择

MODIFY 修改

INSERT 插入

DELETE 删除

SHOW 展示

3.DDL(数据定义语言)

针对数据库与表
Data Definition Language

1.ALTER(修改)

1.1 表重命名

ALTER TABLE 旧表名 RENAME AS 新表名;
具体实例

-- student重命名为student1
ALTER TABLE student RENAME AS student1;

1.2 字段重命名

ALTER TALBE 表名 CHANGE 旧字段名新字段名新字段属性;
change 也可以修改字段属性
具体实例

-- 将class表的字段name1重命名为name 且该字段属性被修改
ALTER TABLE class CHANGE `name1` `name` VARCHAR(30);

1.3 添加字段

ALTER TABLE 表名 ADD 字段名基本类型(类型大小) [列属性];
具体实例

-- 向表class中添加一个名为 sex 的字段
ALTER TABLE class ADD `sex` varchar(3) NOT NULL COMMENT '学生性别';

1.4 修改字段的约束

ALTER TABLE class MODIFY 字段名新的属性
具体实例

-- 原name字段基本属性:varchar(10),被修改为varchar(30)
ALTER TABLE class MODIFY `name` VARCHAR(30);

1.5 删除表的字段

ALTER TABLE 表名 DROP 字段名
具体实例

-- 删除表class中的sex字段
ALTER TABLE class DROP `sex`;

2.数据库命名规则

在这里插入图片描述

4.DML(数据操纵语言)

针对数据库的数据
Data Manipulation Language

1.INSERT(添加数据)

1.1 有字段添加单条数据

INSERT INTO 表名 (字段名) VALUES(字段值)

-- 添加了一个年龄为10 姓名为张三的数据在表中
INSERT INTO `student` (`age`,`name`) VALUES ('10','张三');

1.2 有字段添加多条数据

INSERT INTO 表名 (字段名) VALUES(字段值1),(字段值2),…

-- 添加多条数据
INSERT INTO `student` (`age`,`name`) VALUES ('20','JACK'),('30','BOB')

1.3 无字段添加数据

INSERT INTO 表名 VALUES (字段值)

-- 如果没有指明字段那么将按照表结构进行匹配,值不匹配则报错

在这里插入图片描述

-- 如上图的表,此时不加字段就会按照表结构顺序进行匹配
INSERT INTO `student` VALUES ('张三','10','翻斗花园');
-- 执行之后就会生成一条数据

在这里插入图片描述

1.4 无字段添加多条数据

INSERT INTO 表名 VALUES (字段值1),(字段值2)…

-- 无字段添加多条数据
-- 注:需要写入表中所有字段的值
INSERT INTO `class` VALUES ('MIKE','1','CHINA'),('JHON','2','CHINA')

2.UPDATE(修改数据)

2.1 SQL部分条件表达式

在这里插入图片描述

2.2 修改数据的某个字段

UPDATE 表名 SET 字段=字段值 WHERE [条件]

-- 将 id为1 的数据的姓名修改为 张三
UPDATE `class` SET `name`='张三' WHERE `id`='1'

2.3 修改数据的多个字段

UPDATE 表名 SET 字段1=字段值1,字段值2=字段值2,… WHERE [条件]

-- 将 id为1 的数据的 姓名和地址修改为 张三和中国
UPDATE `class` SET `name`='张三',`address`='中国' WHERE `id`='1'

2.4 无条件修改数据

UPDATE 表名 SET 字段=字段值,…

-- 将所有数据的姓名和地址都修改为 张三 和 中国
UPDATE `class` SET `name`='张三',`address`='中国'

3.DELETE、TRUNCATE(删除数据)

3.1 DELETE(删除)

DELETE FROM 表名

-- 删除表中所有数据,保留计数器(自增的值)的值
DELETE FROM `class`

3.2 TRUNCATE(截断)

TRUNCATE TABLE 表名

-- 删除表中所有数据,计数器重置
TRUNCATE TABLE `class`

3.3 两者区别

DELETE	TRUNCATE
计数器不重置(自增量为上一条数据+1)	计数器重置(重新设置自增列)
	不会影响事务

DELETE例:

现有一个表有id字段且自增,还有name字段 ,此时添加了2条数据
```
INSERT INTO `class` (`name`) VALUES ('JACK'),('BOB')
```
此时删除该表数据,在添加一条数据
```
-- 删除数据
DELETE FROM `class`
-- 添加数据
INSERT INTO `class` (`name`) VALUES ('MIKE')
```
此时MIKE的id会变为 3 原因 :由于上一条数据的 id为2,计数器变为了3 ,导致后续id值继续自增
TRUNCATE例:

现有一个表有id字段且自增,还有name字段 ,此时添加了2条数据
```
INSERT INTO `class` (`name`) VALUES ('JACK'),('BOB')
```
此时删除该表数据,在添加一条数据
```
-- 删除数据
TRUNCATE TABLE `class`
-- 添加数据
INSERT INTO `class` (`name`) VALUES ('MIKE')
```
此时MIKE的 id会变为1 ,使用 TRUNCATE 删除数据使得计数器重置变为1，重新开始自增

5.DQL(数据查询语言)

DATA QUERY LANGUAGE

SELECT语法结构


SELECT [ALL | DISTINCT]-- alias  别名
{* | TABLE.* | [TABLE.field1,AS alias1],[TABLE.field2,AS alias2]....}
FROM TABLE_Name1 [AS TABLE_ALIAS]
	[LEFT | RIGHT | INNER JOIN TABLE_NAME2]   -- 联表查询方式
	[WHERE 条件] 							     -- 指定结果需要满足的条件
	[GROUP BY ...]							  -- 指定结果按照哪几个字段来分组
	[HAVING]								  -- 过滤分组的记录必须满足的次要条件
	[ORDER BY ...]							  -- 指定查询记录按一个或多个条件排序
	[LIMIT {[offset,] row_count | row_countOFFSET offset}];-- 指定查询的记录从哪条至哪条

注:[ ] 括号代表可选的, { } 括号代表必选得 ,结构不可改变顺序

1. SELECT(查询数据)

1.1 查询单个字段数据

SELECT 字段1 From 表名

具体实例

-- 查询student中所有的姓名
SELECT `name` FROM `student`

1.2 查询多个字段数据

SELECT 字段1,字段2… FROM 表名

具体实例

-- 查询student中所有的学号和姓名
SELECT `id`,`name` FROM `student`

1,3 数据取别名

SELECT 字段 AS 别名 FROM 表名

-- 给'subjectno'取别名'课程编号'
-- 给'studentresult'取别名'课程成绩'
SELECT `subjectno` AS '课程编号',`studentresult` AS '课程成绩'
FROM `result`

取别名前
取别名后

1.4 where 条件字句

运算符	语法	描述
IS NULL	a IS NULL	a为空则真，否则假
IS NOT NULL	a IS NOT NULL	a不为空则真,否则假
BETWEEN…AND	a BETWEEN b AND c	a在b和c之间为真,否则假
LIKE	a LIKE b	SQL匹配 ,a匹配b为真,否则为假
IN	a IN (X1,X2,X3,X4…)	a 在括号范围内则真,否则为假

1.4.1 IS NULL

1.4.2 IS IS NOT NULL

1.4.3 IS BETWEEN…AND

-- 查询成绩在90~100之间学生的姓名
SELECT `name` FROM `student` WHERE `grade` BETWEEN 90 AND 100

1.4.4 LIKE(模式匹配)

LIKE 搭配 % 和 _ 使用可以查询字符

%(代表0到任意一个字符)

_(一个字符) __(两个字符)

[ ] 匹配[ ]中的任意一个字符(若要比较的字符是连续的，则可以使用"-"表达)

[^ ] 不匹配[ ] 中的任意字符

-- 查询姓张的同学

SELECT `name` FROM `student` WHERE `name` LIKE '张%'

-- 查询姓张 但是两个字的姓名的同学
SELECT `name` FROM `student` WHERE `name` LIKE '张_'

-- 查询姓张 但是三个字的姓名的同学
SELECT `name` FROM `student` WHERE `name` LIKE '张__'

-- 查询姓名中有三的同学姓名
SELECT `name` FROM `student` WHERE `name` LIKE '%三%'

1.4.5 IN

-- 查询地址在江西或北京或上海的同学姓名
SELECT `name` FROM `student` WHERE `address` IN ('北京','上海','江西')

1.5 根据条件查询数据

SELECT 字段1,… FROM 表名 where [条件]
具体实例
```
-- 查询学号大于
```

2. DISTINCT(数据去重复)

作用: 去除选取数据中重复的数据，只保留一条该数据
SELECT DISTINCT 字段 FROM 表名

具体实例

-- 去除重复的编号数
SELECT DISTINCT `studentno` FROM `result`

去除前
去除重复后

3.联表查询

将不用表的数据连接到一起

3,1 连接表图

在这里插入图片描述

3.2 INNER(内连接)

作用：连接两个表都匹配的数据
句式: SELECT 字段1，字段2… FROM 表1 INNER JOIN 表二 ON [匹配条件]

具体实例

表grade
表student

-- 查询学习学号+学生姓名+年级	匹配条件grade.gradeid = student.gradeid
-- 字段后面为别名
SELECT `studentno` '学生学号',`studentname` '学生姓名',`gradename` '年级'
FROM `studnet` s INNER JOIN `grade` g
ON s.`gradeid` = g.`gradeid`

结果

3.3 LEFT JOIN(左连接)

作用:保留左表中数据,若右表中没有匹配左表的数据,则填充null
句式: SELECT 字段1,字段2,… FROM 表A LEFT JOIN 表B ON [匹配条件]

具体实例

表grade
表student

-- 左连接
-- 查询学生姓名+年级
SELECT `studentname` '学生姓名',`gradename` '学生年级'
FROM `student` s LEFT JOIN `grade` g
ON s.`gradeid` = g.`gradeid`

结果
注: 由于最后一名同学的gradeid = 0 ,与右表无法匹配,填充null

3.4 RIGHT JOIN(右连接)

作用:保留右表所有数据,若左表中又无法匹配的数据,则填充null
句式：SELECT 字段1,字段2,… FORM 表A RIGHT JOIN 表B ON [匹配条件]

具体实例

表grade和表student和上述的表一样

-- 右连接
-- 查询学生姓名+年级
SELECT `studentname` '学生姓名',`gradename` '学生年级'
FROM `student` s RIGHT JOIN `grade` g
ON s.`gradeid` = g.`gradeid`

结果

3.5 UNION(组合)

作用：组合两个表SELECT出的数据
格式：

SELECT 字段,... FROM TABLE_A
UNION
SELECT 字段,... FROM TABLE_B

注：

UNION 会去除重复数据

UNION ALL 会保留所有数据

使用UNION 组合查询时,只能由一条ORDER BY 语句,且它必须出现在最后一条 SELECT 语句之后

实例:

-- UNION 组合查询
SELECT `studentno`,`studentname` 
FROM `student`
WHERE `studentno` >= 1008
UNION
SELECT `studentno`,`studentname` 
FROM `student`
WHERE `studentno` >= 1011
-- UNION ALL组合查询
SELECT `studentno`,`studentname` 
FROM `student`
WHERE `studentno` >= 1008
UNION ALL
SELECT `studentno`,`studentname` 
FROM `student`
WHERE `studentno` >= 1011

UNION结果
UNION ALL结果

3.6 交叉连接

连接多个表,先连接两个表,在慢慢连接其他表，一个一个连接

3.7 自连接

自己和自己连接
创建一个和自己一模一样(数据相同)的表

4.分页和排序

4.1 分页(LIMIT)

语法：LIMIT OFFSET,PAGESIZE

例:


LIMIT 0,5 -- 从第一条数据到第五条数据,每页5条数据 
LIMIT 5,5

各个数据的值：

OFFSET : 起始值

PAGESIZE：每页的数据量

第一页：0，5

第二页：5，5

第三页：10，5

第 N 页：PAGESIZE*(N-1)，5、

每一页的起始值 = (N-1)*PAGESIZE

总的数据 = PAGESIZE *总页数

总的页数 = [ 总的数据/PAGESIZE ] 结果向上取整
实例：

-- 按高等数学-1的成绩升序排序
SELECT s.`studentno` '学生学号',`studentname` '学生姓名',`subjectname` '课程名字',`studentresult` '学生成绩'
FROM `student` s INNER JOIN `result` r
ON s.`studentno` = r.`studentno`
LEFT JOIN `subject` sub
ON r.`subjectno` = sub.`subjectno`
WHERE `subjectname` = '高等数学-1'
ORDER BY `studentresult` ASC

4.2 升序(DESC)

语法:ORDER BY 字段 ASC
作用：让查询的结果按照该字段数据降序进行排序

实例

-- 按高等数学-1的成绩升序排序
SELECT s.`studentno` '学生学号',`studentname` '学生姓名',`subjectname` '课程名字',`studentresult` '学生成绩'
FROM `student` s INNER JOIN `result` r
ON s.`studentno` = r.`studentno`
LEFT JOIN `subject` sub
ON r.`subjectno` = sub.`subjectno`
WHERE `subjectname` = '高等数学-1'
ORDER BY `studentresult` ASC

4.3 降序(DESC)

语法:ORDER BY 字段 DESC
作用：让查询的结果按照该字段数据降序进行排序

实例

-- 按高等数学-1的成绩降序排序
SELECT s.`studentno` '学生学号',`studentname` '学生姓名',`subjectname` '课程名字',`studentresult` '学生成绩'
FROM `student` s INNER JOIN `result` r
ON s.`studentno` = r.`studentno`
LEFT JOIN `subject` sub
ON r.`subjectno` = sub.`subjectno`
WHERE `subjectname` = '高等数学-1'
ORDER BY `studentresult` DESC

5.子查询

在WHERE 条件中添加新的查询语句
注：子查询只能返回一行数据

实例:

-- 子查询
-- 查询学生的学号，姓名且年级为大二的学生
SELECT `studentno` '学生学号',`studentname` '学生姓名'
FROM `student`
WHERE `gradeid` = (
	SELECT `gradeid` FROM `grade`
	WHERE `gradename` = '大二'
)

6.嵌套查询

在WHERE 条件中添加多个SELECT查询语句
注：嵌套查询可返回多条数据,判断是用范围条件进行判断

实例:

-- 由里向外
-- 嵌套查询
-- 查询学生的学号，姓名，且C语言-1 成绩大于80分的学生
SELECT `studentno` '学生学号',`studentname` '学生姓名' FROM `student` WHERE `studentno` IN (
	SELECT `studentno` FROM `result` WHERE `studentresult` >= '80' AND `subjectno` IN (
		SELECT `subjectno` FROM `subject`
		WHERE `subjectname` = 'C语言-1'
	)
)

6.Mysql函数

6.1 常用函数

6.1.1 字符串相关函数

6.1.1.1 CONCAT(拼接字符串)

格式:SELECT CONCAT(str1,str2,…)
作用:拼接字符串,参数:字符串
实例：

SELECT CONCAT('hello',',','world')  -- 结果显示:  hello,world

SELECT CONCAT('学习','Mysql')		   -- 结果显示:	学习Mysql

注：若参数中有一个为NULL，则返回NULL

SELECT CONCAT('hello',NULL,'world')		-- 结果显示 NULL

6.1.1.2 SUBSTR(截取字符串)

格式:SELECT SUBSTR(str,pos,len)
作用：从字符串str的pos位置开始截取长度为len的字符串
实例：

-- 从字符串的第六个位置开始，截取长度为5的字符串
SELECT SUBSTR('helloWorld',6,5)       -- 结果显示:  World

6.1.1.3 REPLACE(替换字符串)

**格式 **:SELECT REPLACE(str,from_str,to_str)
作用：将字符串str的from_str部分替换为to_str
实例:

-- 将 A字符 串替换成 字符串World
SELECT REPLACE('helloA','A','World')        -- 结果显示:HelloWorld

6.1.2 数字相关函数

6.1.2.1 CEILING(向上取整)

格式：SELECT CEILING(num)
作用:返回该数向上取整的结果
实例

-- 正数取整 
SELECT CEILING(9.1)     -- 结果显示: 10
-- 负数取整
SELECT CEILING(-9.1)	-- 结果显示:  -9

6.1.2.2 FLOOR(向下取整)

格式：SELECT FLOOR(num)
作用:返回该数向下取整的结果
实例

-- 正数取整 
SELECT CEILING(9.8)      -- 结果显示: 9
-- 负数取整
SELECT CEILING(-9.1)	-- 结果显示:  -10

6.1.3 聚合函数

结合表的数据一起使用

6.1.3.1 COUNT()

格式：SELECT
COUNT(字段) 作用: 计算出该字段在该表中不为NULL的记录总数
COUNT(*) 作用: 计算出该表的总行数,NULL值也记录其中
COUNT(1) 作用:计算出该表的总行数,NULL值也记录其中
COUNT(字段) 实例：

-- 计算表student中所有 studentname 不为NULL的记录
SELECT COUNT(`studentname`) FROM `student`

COUNT(*) 实例:
表demo中的数据

SELECT COUNT(*) FROM `demo`     -- 结果显示： 2 (包含了null)

性能: COUNT(*) = COUNT(1) > COUNT(主键字段) > COUNT(字段)

6.1.3.2 SUM()

格式：SELECT SUM(字段) FROM TABLE_A
作用:计算该表中某个字段的总和
实例:

-- 依旧使用demo表
SELECT SUM(`age`) FROM `student`   -- 结果显示: 1

6.1.3.3 AVG()

格式：SELECT AVG(字段) FROM TABLE_A
作用:计算该字段在该表的平均值
实例:

SELECT AVG(`studentresult`) '平均成绩'
FROM `result`

6.1.3.4 MAX()

格式：SELECT MAX(字段) FROM TABLE_A
作用:计算该字段在该表在最大值
实例:

SELECT MAX(`studentresult`) '最高成绩'
FROM `result

6.1.3.5 MIN()

格式：SELECT MIN(字段) FROM TABLE_A
作用:计算该字段在该表在最小值
实例:

SELECT MIN(`studentresult`) '最低成绩'
FROM `result`

6.2 分组过滤

格式:
- 分组:GROUP BY …
- 过滤:HAVING 条件
注：HAVING 必须和GROUP BY 一起使用
使用聚合函数使用先分组在过滤
实例:


-- 查询每一门课程的平均成绩、最高分、最低分
SELECT `subjectname` '课程名字', AVG(`studentresult`) '平均成绩',MAX(`studentresult`) '最高成绩',MIN(`studentresult`) '最低成绩'
FROM `result` r
INNER JOIN `subject` sub
ON r.`subjectno` = sub.`subjectno`
GROUP BY sub.`subjectno`  							-- 分组
HAVING AVG(`studentresult`) > 80				-- 分组后的次要条件

7.事务

要么都成功，要么都失败

7.1 事务原则

ACID(事务管理)

Atomicity( 原子性)
- 指一个事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都失败
Consistency(一致性)
- 事务前后数据的完整性必须保持一致
Isolation (隔离性)
- 多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据干扰，多个并发事务之间要相互隔离
Durability (持久性)
- 指一个事务一旦被提交，它对数据库中的数据的改变是永久性的，接下来即使数据库发生故障也不应该对其具有任何影响
- 事务一旦提交，则不可逆
- 数据没提交，恢复到原始状态
- 数据已经提交，数据为提交后的状态

隔离可能产生的问题

脏读
- 指一个事务读取了另一个事务未提交的数据
不可重复读
- 在一个事务内读取表中的某一行数据，多次读取的结果不同。（可能是场合导致，非错误）
虚读（幻读）
- 指在一个事务内读取到了别的事物插入的数据，导致前后读取不一致

7.2 事务sql语句

1.关闭自动提交(大部分sql软件默认开启自动提交事务)

SET AUTOCOMMIT = 0

2.事务开启

START TRANSACTION

3.执行的sql语句 (此时已经进入事务，若有一条语句执行失败则直接回滚到起始状态)

UPDATE XX…

4.提交事务 (语句执行成功)

COMMIT

5.回滚 (语句执行出现问题)

ROLLBACK

6.开启自动提交事务

SET AUTOCOMMIT = 1

事务中语句拓展

SAVEPOINT 保存点名 – 设置一个事务的保存点

ROLLBACK 保存点 – 回滚到该保存点

RELEASE SAVEPOINT 保存点名字 – 撤销该保存点

实例：
- 表account

-- 向account表中添加2条数据
INSERT INTO `account` (`name`,`money`) VALUES
('张三','1000.00'),
('李四','500.00')

-- 关闭事务自动提交
SET AUTOCOMMIT = 0

-- 开启事务
START TRANSACTION

-- 模拟转账
-- 张三转账给李四
UPDATE `account` SET `money` = `money` - 300 WHERE `name` = '张三'

SELECT * FROM `account`

此时表的数据

在这里插入图片描述

UPDATE `account` SET `money` = `money` + 300 WHERE `name` = '李四'

SELECT * FROM `account`

-- 提交事务
COMMIT 

-- 若事务提交失败则进行回滚
-- 事务回滚  数据恢复到未进行该事务之前
ROLLBACK

-- 开启事务自动提交
SET AUTOCOMMIT = 1

此时表的数据

在这里插入图片描述

COMMIT 提交后的结果
ROLLBACK 回滚的结果

8.索引

索引是帮助MySQL高效获取数据的数据结构。更通俗的说，数据库索引好比是一本书前面的目录，能加快数据库的查询速度

8.1 索引的分类

主键索引(PRIMARY KEY)

唯一的标识,主键是不可重复的，只能一个

-- 添加一个主键索引
ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` )

唯一索引(UNIQUE KEY)
- 索引列的值必须唯一，但允许有空值
```
-- 创建一个唯一索引
-- column(length)字段
```
CREATE UNIQUE INDEX indexName ON table(column(length))

– index_name 索引名
– column_name 列名
– 添加一个唯一索引
ALTER TABLE TABLE_A ADD UNIQUE INDEX index_name (column_name)

普通索引(KEY | INDEX)

普通索引是最基本的索引，它没有任何限制，值可以为空

-- 创建一个普通索引
-- column(length)字段
CREATE INDEX index_name ON table(column(length))

-- index_name 索引名
-- column_name 列名
-- 添加一个普通索引
ALTER TABLE TABLE_A ADD INDEX index_name (column_name)

全文索引(FULL | TEXT)

全文索引主要用来查找文本中的关键字，而不是直接与索引中的值相比较

-- 创建一个全文索引
-- column(length)字段
CREATE FULLTEXT INDEX index_content ON article(content)

-- 添加一个全文索引
-- index_name 索引名
-- column_name 列名
ALTER TABLE TABLE_A ADD FULLTEXT INDEX index_name (column_name)

8.2 索引的原则

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

9.数据库管理

9.1 数据库用户

创建用户

-- 创建用户
-- CREATE USER `用户名`@`主机名`;
CREATE USER `mixcus`@`localhost`

删除用户

-- DROP USER `用户名`@`主机名`;
DROP USER `mixcus`@`127.0.0.1`;

创建用户密码

-- SET PASSWORD FOR 用户 = PASSWORD('密码')
SET PASSWORD FOR `mixcus` = PASSWORD('123456')

修改当前用户密码

SET PASSWORD = PASSWORD('123456')

修改用户名

-- RENAME USER 旧名字 TO 新名字
RENAME USER `mixcus` TO `mixcus1`

添加用户权限

-- 1.创建用户后添加权限
-- GRANT ALL PRIVILEGES ON 库名.表名 TO 用户名
-- * 指所有库、表    	GRANT 授予
-- ALL PRIVILEGES 所有权限
GRANT ALL PRIVILEGES ON *.* TO `mixcus`

撤除用户权限

-- REVOKE ALL PRIVILEGES ON 库名.表名 TO 用户名
-- * 指所有库、表    	REVOKE 移除
REVOKE ALL PRIVILEGES ON *.* TO `mixcus`

查询指定用户权限

-- SHOW GRANTS FOR 用户名
SHOW GRANTS FOR `mixcus`

9.2数据库备份

命令行备份
1.导出单个表

# mysqldump -h主机 -u用户名 -p密码 数据库 表名 > 路径位置/文件名
mysqldump -hlocalhost -uroot -p123456 school student >C:\Users\86151\Desktop\1.sql

2.导出多个表

# mysqldump -h 主机 -u 用户名 -p 密码 数据库 表1 表2 > 路径位置/文件名
mysqldump -hlocalhost -uroot -p123456 school student grade >C:\Users\86151\Desktop\2.sql

3.导出整个数据库

# mysqldump -h 主机 -u 用户名 -p 密码 数据库 > 路径位置/文件名
mysqldump -hlocalhost -uroot -p123456 school>C:\Users\86151\Desktop\3.sql

4.导入数据

-- 未登入Mysql
-- mysql -u用户名 -p密码 库名 < 文件位置
-- 最好登入mysql,登入导入数据,需要进入所在的数据库
-- source 数据所在路径 	
source C:\Users\86151\Desktop\1.sql

10.JDBC

10.1 数据库驱动

下载链接

Maven Repository: mysql » mysql-connector-java (mvnrepository.com)

10.2 JDBC步骤

1.加载驱动

// 调用了Driver的静态代码完成驱动注册
Class.forName("com.mysql.jdbc.Driver");

2.连接数据库(包含创建用户信息)

URL

协议://ip地址:端口号/文件名?参数1&参数2&参数3…


//useUnicode=true				使用Unicode编码,可以使用中文编码
//characterEncoding=utf8		字符编码 utf-8
//useSSL=true					使用数据库安全连接
String url = "jdbc:mysql://localhost:3306/数据库名?		useUnicode=true&characterEncoding=utf8&useSSL=true";

//用户名
String name = "";
//密码
String pwd = "";

//连接数据库,参数:数据库地址,用户名,用户密码
Connection connection = DriverManager.getConnection(url,userName,pwd);

3.创建sql对象,创建sql语句

Statement statement = connection.createStatement();
//sql语句
String sql = "";

4.执行sql语句，获取返回结果集

executeQuery() 执行查询sql语句返回结果集

executUpdate() 执行插入(INSERT)、删(DELETE)、改(UPDATE) ql语句返回受影响的行数


ResultSet resultSet = statement.executeQuery(sql);

//通过循环遍历出结果
while (resultSet.next()){
    System.out.println();
}

5.释放连接


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

6.完整代码:

//1.加载驱动
Class.forName("com.mysql.jdbc.Driver");

//2.1创建用户信息
// url url = "jdbc:mysql://localhost:3306/数据库名?		useUnicode=true&characterEncoding=utf8&useSSL=true"
String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true";
String userName = "root";
String pwd = "123456";

//2.2连接数据库
//获取数库对象,不为空连接成功，否则失败
Connection connection = DriverManager.getConnection(url,userName,pwd);

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

//4.设置sql语句,让sql对象执行
String sql = " SELECT * FROM jdbcstudy";
/*
*  查询 executeQuery()
*  增、删、改  executeUpdate()
* */

//返回一个结果集
ResultSet resultSet = statement.executeQuery(sql);
//遍历获取结果信息,调用next()
while (resultSet.next()){
    System.out.println("id:"+resultSet.getObject("id"));
    System.out.println("name:"+resultSet.getObject("name"));
    System.out.println("email:"+resultSet.getObject("email"));
    System.out.println("birthday:"+resultSet.getObject("birthday"));
}

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

10.3 JDBC 部分类的分析

Connection

Statement

ResultSet

10.4 JDBCUtils(jdbc工具类)

配置文件 comfig.properties

# jdbc config
driver = com.mysql.jdbc.Driver
name = root
pwd = 123456
url = jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true

Utils代码

public class JDBCUtils {

    private static String driver = null;//驱动
    
    private static String name = null;//数据库用户名

    private static String pwd = null;//数据库密码

    private static String url = null;//数据库地址

    static {
        //获取资源文件 并且以流的形式
        //通过反射获取  类的加载器 再通过  文件所在地路径 获取流
        InputStream is = 		 JDBCUtils.class.getClassLoader().getResourceAsStream("config.properties");

        Properties properties = new Properties();

        try {

            properties.load(is);

            //从属性文件中获取数据
            name = properties.getProperty("name");

            pwd = properties.getProperty("pwd");

            url = properties.getProperty("url");

            driver = properties.getProperty("driver");

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

        } catch (Exception e) {
            throw new RuntimeException(e);
        }

    }

    /**
     *
     * @return 服务器连接对象
     */
    public static Connection getConnection() {

        try {
            return DriverManager.getConnection(url,name,pwd);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    /**
     *
     *
     * @param con
     * @param st
     * @param re
     * 释放资源
     */
    public static void release(Connection con,Statement st,ResultSet re){

        try {

            if(con!=null){
                con.close();
            }
            if(st!=null){
                st.close();
            }
            if(re!=null){
                re.close();
            }

        }catch (Exception e){
            e.printStackTrace();
        }
    }

}

10.5 CURD(增删改查)

CURD -SELECT 查询数据

String sql1 = "SELECT * FROM jdbcstudy";
//查询属于Query
//获取查询的结果集
resultSet = statement.executeQuery(sql1);

while (resultSet.next()){
    //获取指定列名的值
    System.out.println("id:"+resultSet.getString("id"));
    System.out.println("name:"+resultSet.getString("name"));
    System.out.println("email:"+resultSet.getString("email"));
    System.out.println("birthday:"+resultSet.getString("birthday"));
}

CURD - INSERT 插入数据

String sql = "INSERT INTO jdbcstudy (id,name,email,birthday) VALUES ('4','赵四','123123@qq.com','2001-01-02')";
// 插入操作属于更新数据,返回受影响行数
int result = statement.executeUpdate(sql);

if(result > 0){
    System.out.println("数据插入成功");
}

CURD - UNDATE 修改数据

String sql = "UPDATE jdbcstudy set name = '张四' Where id = 1";
//修改操作属于更新数据,返回受影响行数
int i = statement.executeUpdate(sql);
if(i > 0){
	System.out.println("数据更新成功");
}

CURD -DELETE 删除数据

String sql = "DELETE FROM jdbcstudy WHERE id = '4'";
//删除操作属于更新数据,返回受影响行数
int result = statement.executeUpdate(sql);
if(result > 0){
    System.out.println("数据删除成功")
}

10.6 SQL注入问题

通过使用字符串的拼接来获取用户的用户名和密码

原理：

String sql = “SELECT * FROM user WHERE name = '”+userName+“’ or ‘1’=‘1’”
+“AND password = '”+password+“’ or ‘1’ = ‘1’”;

通过sql语句or的特点，让其中一个满足即可判定成功

Connection connection = JDBCUtils.getConnection();

String userName ="12312";//用户名

String password ="2312312";//密码

String sql = "SELECT * FROM user WHERE `name` = '"+userName+"' or '1'='1'"
    +"AND `password` = '"+password+"' or '1' = '1'";
//假设sql语句如此，即可sql注入，用户名和密码随意输入也能获取正确的用户名和密码

Statement statement = connection.createStatement();

ResultSet resultSet = statement.executeQuery(sql);

while (resultSet.next()){
    System.out.println("userName: "+resultSet.getString("name")
                       +" password: "+resultSet.getString("password"));
}

10.7 PreparedStatement

PreparedStatement 能够有效防止sql注入问题

预编译的sql对象

使用PreparedStatement实现JDBC步骤

1.创建驱动连接Connection类对象

Connection connection = JDBCUtils.getConnection();

2,创建要执行的sql语句

? 为参数

String sql = "SELECT * FROM student WHERE id = ? "

3.创建PreparedStatement类对象

PreparedStatement preparedStatement = connection.prepareStatement(sql);

4.为sql语句添加参数

preparedStatement.setXXX(index,value)

XXX 参数类型例如：setString(),setInt()…

index 参数位置，即?的位置

value 参数值

// 执行上述语句，得到id为2的学生记录
preparedStatement.setInt(1,2)

5.通过调用preparedStatement的executeUpdate()方法获取结果或别的execute 方法

executeUpdate() 返回受影响的行数

完整代码

public class TestPS {

    public static void main(String[] args) throws Exception {

        Connection connection = JDBCUtils.getConnection();

        Statement statement = connection.createStatement();

        String sql = "INSERT INTO student (`id`,`name`,`age`) VALUES (?,?,?)";

        //获取sql预编译对象
        PreparedStatement preparedStatement = connection.prepareStatement(sql);

        preparedStatement.setInt(1,1);
        preparedStatement.setString(2,"张三");
        preparedStatement.setInt(3,20);

        if(preparedStatement.executeUpdate()>0){

            System.out.println("插入成功");
        }else
            System.out.println("插入失败");


        String querySql = "SELECT * FROM student";//查询结果语句

        ResultSet resultSet = statement.executeQuery(querySql);//结果集

        //打印表信息
        while (resultSet.next()){
            System.out.println("id: "+resultSet.getInt("id")
                +"name: "+resultSet.getString("name")
                +"age: "+resultSet.getInt("age"));
        }

        JDBCUtils.release(connection,statement,preparedStatement,resultSet);

    }

}

使用PreparedStatement实现JDBC步骤

1.创建驱动连接Connection类对象

Connection connection = JDBCUtils.getConnection();

2,创建要执行的sql语句

? 为参数

String sql = "SELECT * FROM student WHERE id = ? "

3.创建PreparedStatement类对象

PreparedStatement preparedStatement = connection.prepareStatement(sql);

4.为sql语句添加参数

preparedStatement.setXXX(index,value)

XXX 参数类型例如：setString(),setInt()…

index 参数位置，即?的位置

value 参数值

// 执行上述语句，得到id为2的学生记录
preparedStatement.setInt(1,2)

5.通过调用preparedStatement的executeUpdate()方法获取结果或别的execute 方法

executeUpdate() 返回受影响的行数

完整代码

public class TestPS {

    public static void main(String[] args) throws Exception {

        Connection connection = JDBCUtils.getConnection();

        Statement statement = connection.createStatement();

        String sql = "INSERT INTO student (`id`,`name`,`age`) VALUES (?,?,?)";

        //获取sql预编译对象
        PreparedStatement preparedStatement = connection.prepareStatement(sql);

        preparedStatement.setInt(1,1);
        preparedStatement.setString(2,"张三");
        preparedStatement.setInt(3,20);

        if(preparedStatement.executeUpdate()>0){

            System.out.println("插入成功");
        }else
            System.out.println("插入失败");


        String querySql = "SELECT * FROM student";//查询结果语句

        ResultSet resultSet = statement.executeQuery(querySql);//结果集

        //打印表信息
        while (resultSet.next()){
            System.out.println("id: "+resultSet.getInt("id")
                +"name: "+resultSet.getString("name")
                +"age: "+resultSet.getInt("age"));
        }

        JDBCUtils.release(connection,statement,preparedStatement,resultSet);

    }

}

ㅤㅤㅤㅤㅤㅤㅤㅤㅤㅤㅤㅤㅤㅤㅤㅤ632

关注

20
点赞
踩
29

收藏

觉得还不错? 一键收藏
0
评论
Mysql 5.7 学习

针对数据库与表1.ALTER(修改)1.1 表重命名ALTER TABLE 旧表名 RENAME AS 新表名;具体实例-- student重命名为student11.2 字段重命名ALTER TALBE 表名 CHANGE 旧字段名新字段名新字段属性;change 也可以修改字段属性具体实例-- 将class表的字段name1重命名为name 且该字段属性被修改1.3 添加字段ALTER TABLE 表名 ADD 字段名基本类型(类型大小) [列属性];具体实例。
复制链接

扫一扫