mysql知识点

最新推荐文章于 2023-06-06 10:27:30 发布

孟哈哈

最新推荐文章于 2023-06-06 10:27:30 发布

阅读量563

点赞数

分类专栏： SQL 文章标签： mysql

本文链接：https://blog.csdn.net/Meyolo_/article/details/114849293

版权

SQL 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

MYSQL

1.概念讲解

2.数据库基本属性

2.1 数据库列类型

数值：

tinyint 比较小的数据 1个字节
smallint 小的数据 2个字节
midiumint 中等的数据 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 时间格式
datetime YYYY-MM-DD HH：mm：ss 日期时间格式较为常用
timestramp 1970.1.1到现在时间经过的毫秒数**
year 年份

null

尽量不使用NULL进行计算，结果仍为NULL（无意义）

2.2 数据库的字段属性

Unsigned：

无符号整数
声明该列不能为负数

zerofill：

0填充
数据不足的位数使用0进行填充 int（2） 3 003

自增：

通常是在上一条记录加1
通常设置为唯一的主键例如：index id
可以自定义起始值和步长

非空 NULL not null

如果设置为 not null ，则不赋值就会报错
NULL，如果未填写值，默认就是Null

拓展：阿里巴巴规范，之后做项目，实际开发中每一个表都要有一下5个字段

id 主键

version 乐观锁

is_delete 伪删除

gmt_create 创建时间

gmt_update 修改时间

3.创建数据库表

3.1 创建school表

CREATE TABLE IF NOT EXISTS `school` (
  `id` INT 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] `表名`(
 `字段名` 列类型 [属性][索引][注释],
 `字段名` 列类型 [属性][索引][注释],
  ......
  字段名` 列类型 [属性][索引][注释],
)[引擎][字符集][注释]

3.2 查看建表语句

常用命令

show create database myl   --查看创建数据库的语句

show create table tb_myl;  --查看创建数据库表的语句

desc tb_myl  --显示表的结构

数据表的类型

-- 关于数据库引擎
INNODB 默认使用
MYISAM 早期使用

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

常规使用操作

MYISAM 节约空间，速度较快。
INNODB 支持事务，事务处理，夺表多用户操作。

物理空间存在的位置： data\

所有的数据库文件都存放在mysql安装目录下的data文件夹下，

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

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

本质上数据库表还是文件。

设置数据库默认字符集编码

在mysql安装目下的 my.ini中修改编码方式（以后都是这个编码方式）
语句加 charset=utf8

3.3修改删除表

修改

/*修改表名    LTER TABLE 原表名 RENAME AS 新表名	  */
ALTER TABLE teacher RENAME AS teacher1	

/* 增加表的字段     ALTER TABLE 表名 ADD 字段名 列类型        */
ALTER TABLE teacher1 ADD age INT(11) 

/*   修改表的字段 (重命名   ALTER TABLE 表名 MODIFY 字段名 列类型
                 约束)   ALTER TABLE 表名 CHANGE 原字段名 新字段名 列类型   */
ALTER TABLE teacher1 MODIFY age VARCHAR(11)     /*修改约束*/

ALTER TABLE teacher1 CHANGE age age1 INT(2) 	/*字段重命名*/

/*删除表的字段        ALTER TABLE 表名 DROP 字段名*/
ALTER TABLE teacher1 DROP age1

删除

/*删除表*/
DROP TABLE IF EXISTS teacher1

注意：

所有的创建和删除操作，尽量加上判断（IF EXISTS）
`` 字段名使用这个符号包裹
sql关键字大小写都可以，建议小写
所有的符号都用英文

4.Mysql数据管理

4.1 外键（了解）

/*外键：
           创建年级表  
           创建学生表  外键（gradeid）
           
        1.在创建表语句时设置外键
        2.在已有创建表上设置外键
 
*/
CREATE TABLE `grade`(
	`gradeid` INT(4) NOT NULL,
	`gradename` VARCHAR(20) NOT NULL,
	PRIMARY KEY (`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
/*student表增加字段  gradeid */
ALTER TABLE student ADD gradeid INT(4) 

/*  2.在已有创建表上设置外键*/
ALTER TABLE `student`
	ADD CONSTRAINT `FK_gradeid` FOREIGN KEY(`gradeid`) REFERENCES `grade`(`gradeid`);

注意：

删除带有外键的关系表时，必须先删除从表（引用的表），再删除主表（被引用的表）

上面所述都是物理外键，数据库级别的外键（不建议使用），避免数据库过多造成问题。

最佳实践：

数据库就是只存取数据，只有行和列。
想使用多张表的数据，通过程序去实现即可。

4.2 DML语言

数据库操作语言

INSERT

/*   给数据库表插入数据
	格式：insert into 表名([字段名1,字段名2,字段名3]) values('值1'),('值2')......
         1.插入数据时，字段名与插入数据一一对应
         2.当插入数据为全部字段时，可以省略字段名的书写
         3.一次可以插入多条数据，一行完整的数据用,隔开
         4.当ID为自增时，可以用NULL占位

       */
INSERT INTO `student`(`name`,`pwd`) VALUES('张三','54321')

INSERT INTO `student` VALUES(2,'李四','654321','男','2000-09-04','咸阳','1025483534@qq.com') 

INSERT INTO `student`
	VALUES(3,'李四','654321','男','2000-09-04','咸阳','1025483534@qq.com'),
	      (NULL,'王五','23456','男','2020-01-01','西安','205382553@qq.com')

语法：

insert into 表名([字段名1,字段名2,字段名3]) values(‘值1’),(‘值2’)…

UPDATE

/*    UPDATE 更新数据
	语法： UPDATE 表名 set 字段名的值=新值 where 条件
	1. 根据条件去修改数据
	2.不指定条件下会修改所有的数据！（删库跑路）
	3.修改多个数据时，使用逗号,隔开
	4.条件中value可以是一个值，也可以是一个变量（例如 ）

	*/

UPDATE `student` SET `name`='孟永龙' WHERE id = 1

UPDATE `student` SET `name`='孟永龙' 

UPDATE `student` SET `pwd`='11111',sex='男' WHERE id =2

UPDATE `student` SET `pwd`='11111',sex='男',birthday=CURRENT_TIME WHERE id =2

UPDATE `student` SET `name`='张三',sex='女' WHERE `id` BETWEEN 2 AND 5

where 条件的取值操作符（返回值为 boolean）

操作符	含义	范围	结果
=	等于	2=3	false
<>或!=	不等于	2<>3	true
>
<
>=
<=
Between…And…	范围之间	[2,5]
And	与	4>3 And 3<2	false
OR	或	2>1 OR 1<1	true

语法：

update 表名 set 字段名=值,[字段名=值,…] where 条件

DELETE

语法：delete from 表名 where 条件

/*     DELETE:删除数据
	语法：delete from 表名 where 条件
	1.根据条件去删除
	2.如果未指定条件，则会清空表
	清空表：
	delete from 表名

*/
CREATE TABLE `stn`(
	`id` INT(4),
	`name` VARCHAR(20)
)

DELETE FROM `student` WHERE `pwd`='23456'

清空表的两种操作：
DELETE FROM 表名
TRUNCATE 表名

相同点：都会清空数据，不改变表结构和索引

不同点：
TRUNCATE 重新设置自增序列，计数器归零
TRUNCATE 不会影响事务

关于delete的问题（重启Mysql服务，现象）：

InnoDB: 自增列会从1开始，（断电即失） mysql8.0修复此现象
MyISAM: 继续从上一个自增列开始，（存在文件中，不会丢失）

5.DQL查询数据

5.1 指定查询字段


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

语法：

SELECT 字段… FROM 表名

我们可以通过 AS 关键字给字段名或者表名起别名

数据库中的列：

去重

/*        去重：
		去掉查询结果中相同的数据项
*/		
-- 查询哪些同学参加了考试，成绩
	-- 查询全部的考试成绩
SELECT * FROM `result`
	-- 查询有哪些同学参加了考试
SELECT `studentno` FROM `result`
	-- 有重复数据，去重
SELECT DISTINCT `studentno` FROM `result`

数据库中的列

/*  数据库中的列（表达式）*/
	-- 查询系统版本
SELECT VERSION()
	-- 实现计算
SELECT 50*2-2 AS 计算结果
	-- 查询自增的步长
SELECT @@auto_increment_increment
	-- 数据项 加1 
SELECT `studentno`,`studentresult`+1 AS 加分后 FROM `result`

5.2 where条件字句

where 逻辑语句

/*  where条件字句    */
SELECT `studentno`,`studentresult` FROM `result`
-- 查询成绩在50 - 90之间   AND    &&     BETWEEN...AND...
SELECT `studentno`,`studentresult` FROM `result` 
	WHERE `studentresult`>=60 AND `studentresult`<=90 

SELECT `studentno`,`studentresult` FROM `result` 
	WHERE `studentresult`>=60 && `studentresult`<=90 	

SELECT `studentno`,`studentresult` FROM `result` 
	WHERE `studentresult` BETWEEN 60 AND 90	
-- 查询成绩不等于85的成绩   !=  not
SELECT`studentno`,`studentresult` FROM `result`
	WHERE `studentresult`!= 85

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

where 模糊查询

模糊查询：比较运算符

运算符	语法	描述
IS NULL	A is null	操作符为null,结果为真
IS NOT NULL	A is not null	操作符为not 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,A3…中，结果为真

--   like查询
-- 查询姓张的同学       like  占位符（% 表示0到多个字符，_  表示一个字符）
SELECT * FROM `student` 
	WHERE `studentname` LIKE '张%'
SELECT * FROM `student` 
	WHERE `studentname` LIKE '张_'
-- 查询名字中含有 三 的同学
SELECT * FROM `student`
	WHERE `studentname` LIKE '%三%'
    
-- in查询          in(多个查询的条件，注意：这里不能用占位符 % _)
-- 查询1001,1002,1003号学生
SELECT * FROM `student`
	WHERE `studentno` IN(1001,1002)
-- 查询在西安的学生      
SELECT * FROM `student` 
	WHERE `address` IN('西安','北京朝阳')

--  null   not null
-- 查询地址为空的学生    这里是空字符串 不是 null
SELECT * FROM `student` 
	WHERE `address` ='' OR `address` IS NULL
-- 查询有出生日期的同学，不为null
SELECT * FROM `student`
	WHERE `borndate` IS NOT NULL
-- 查询出生日期为空的同学    null
SELECT * FROM `student`
	WHERE `borndate` IS NULL

5.3 联表查询

联表查询：

/*
    查询思路：
	1.分析需求，分析所需查询的字段来自于哪几个表
	2.确定使用哪种连接查询  7种
	3.确定交叉点 （这两个表中哪个数据是相同的）
	4.利用相同字段形成连接条件
	注意：  查询结果中的字段如果是两个表的相同字段，必须指定哪个表中的字段。
		否则报错：Column 'studentno' in field list is ambiguous
*/
-- 查询参加了考试的同学（学号，姓名，科目编号，分数）	
SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult`
FROM student AS s
INNER JOIN result AS r
WHERE s.studentno = r.studentno	

-- Right Join
SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult`
FROM student AS s
RIGHT JOIN result AS r
ON s.studentno = r.studentno

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

操作	描述
inner join	如果表中至少有一个匹配，就返回行
left join	从左表中返回所有的值，即使右表中没有（null填充）
right join	从右表中返回所有的值，即使左表中没有（null填充）

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

/*
	查询参加考试同学的信息  （学号，学生姓名，科目名，分数）
	所需查询的表  student、result、subject   分步查询
	条件 student.sno = result.sno result.subjectno=subject.subjectno
*/

SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM student s
RIGHT JOIN result r
ON s.studentno = r.studentno
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno

自连接

自己和自己连接，一张表拆分成两张表

父类

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

子类

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

操作：查询父类对应的子类数据

父类	子类
信息技术	办公信息
软件开发	数据库
软件开发	web开发
美术设计	ps技术

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','办公信息');
-- 查询父子信息：把一张拆分成两张一模一样的表
SELECT a.`categoryName` AS '父列',b.`categoryName` AS '子列'
FROM `category` AS a,`category` AS b
WHERE a.categoryid = b.pid

5.4 分页和排序

排序

-- 排序 :   升序 ASC   降序：DESC
-- ORDER BY  通过哪个字段排序，排序规则
-- 根据成绩升序排序
SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM student s
RIGHT JOIN result r
ON s.studentno = r.studentno
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
ORDER BY studentresult ASC

分页

-- 分页
-- 语法  limit（放在语句最后） 起始下标，页面大小
SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM student s
RIGHT JOIN result r
ON s.studentno = r.studentno
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
ORDER BY studentresult ASC
LIMIT 0,3 

/*
    第一页  limit 0,3  （1-1）*3
    第二页  limit 3,3  （2-1）*3
    第三页  limit 6,3  （3-1）*3
    第n页   limit (n-1)*pagesize,pagesize
    总页数= 数据总数/页面大小
*/

语法：limit 查询起始下标，页面大小

5.5 子查询

where：条件需要计算出

格式：在where语句中嵌套查询语句

-- 查询 高等数学-1 的所有考试结果（学号，科目编号，成绩），降序排列

-- 方法一：连接查询
SELECT `studentno`,r.`subjectno`,`studentresult`
FROM `result` r
INNER JOIN `subject` sub
ON r.`subjectno` = sub.`subjectno`
WHERE `subjectname` = '高等数学-1'
ORDER BY `studentresult` DESC

-- 方法二： 子查询
SELECT r.`studentno`,`subjectno`,`studentresult`
FROM `result` r
WHERE `subjectno` =(
	SELECT `subjectno` FROM `subject`
	 WHERE `subjectname` = '高等数学-1'
)
ORDER BY `studentresult` DESC

-- 查询所有 高等数学-1 的科目编号
SELECT `subjectno` FROM `subject` WHERE `subjectname` = '高等数学-1'

-- 查询 科目为高等数学-1 且分数不小于80分的学生学号和姓名

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

-- 联表查询
SELECT s.`studentno`,`studentname`
FROM `student` s
INNER JOIN `result` r
ON s.`studentno` = r.`studentno`
INNER JOIN `subject` sub
ON sub.`subjectno` = r.`subjectno`
WHERE `subjectname`='高等数学-1' AND `studentresult`>=80


-- 子查询改造
SELECT s.`studentno`,`studentname` FROM `student` s WHERE `studentno` IN (
	SELECT `studentno` FROM `result` r WHERE `studentresult`>=80 AND `subjectno`= (
		SELECT `subjectno` FROM `subject` WHERE `subjectname` = '高等数学-1'	
	)
)

5.6 分组和过滤



-- 查询不同课程的平均分，最高分，最低分，平均分>70
-- 核心；根据不同的课程分组
SELECT `subjectname`,AVG(`studentresult`) AS 平均分,MAX(`studentresult`) AS 最高分,MIN(`studentresult`) AS 最低分
FROM `result` r
INNER JOIN `subject` sub
ON r.`subjectno` = sub.`subjectno`
GROUP BY `subjectname`
HAVING 平均分>70

5.7 MD5加密

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

INSERT INTO `MD5test` VALUES(1,'张三','12345'),(2,'李四','23456'),(3,'王五','34567')
--   已有数据加密
UPDATE  `MD5test` SET pwd=MD5(pwd) WHERE id =1
UPDATE  `MD5test` SET pwd=MD5(pwd)

-- 插入数据时加密
INSERT INTO `MD5test` VALUES(4,'孟哈哈',MD5('111111'))

-- 校验：将用户传递过来的密码进行加密，比对加密后的值
SELECT * FROM `MD5test` WHERE `name` ='孟哈哈' AND `pwd`=MD5('111111')

-- 通常来说，解密现在通过在线解密网站进行数据库查找，得到对应的密码

6.函数

6.1 常用函数


-- 常用函数  官方文档查询即可（了解）
-- 查询姓张的同学  替换姓为 孟
SELECT REPLACE(studentname,'张','孟') FROM `student`
WHERE `studentname` LIKE '张%'

-- 时间和日期函数（重点记忆）

SELECT CURRENT_DATE()   -- 获取当前日期
SELECT CURDATE() -- 获取当前日期
SELECT NOW() -- 获取当前日期和时间
SELECT LOCALTIME()  -- 获取本地时间
SELECT SYSDATE() -- 获取系统时间
SELECT SYSTEM_USER() -- 获取当前用户
SELECT USER() -- 获取当前用户
SELECT VERSION() -- 获取当前版本

6.2 聚合函数（重点）

函数	描述
COUNT（）	计数
SUM（）	和
AVG（）	平均数
MAX（）	最大值
MIN（）	最小值

-- 统计表中数据的格式
SELECT COUNT(`studentno`) FROM `student`       -- 会忽略所有的Null值
SELECT COUNT(*) FROM `student`	-- 不会忽略所有的Null值   本质：计算行数
SELECT COUNT(1) FROM `student`	-- 不会忽略所欲的Null值   

SELECT SUM(`studentresult`) AS 总和 FROM `result`
SELECT MAX(`studentresult`) AS 最大数 FROM `result`
SELECT MIN(`studentresult`) AS 最小数 FROM `result`
SELECT AVG(`studentresult`) AS 平均分 FROM `result`

7.select小结

7.1注意：顺序很重要

select 去重查询字段 from 表名（表名和字段可以通过 as取别名）

xxx join 连接的表名 on 等值判断

where （具体的值，子查询语句）

group by（通过哪个字段来分组）

having （过滤分组后的信息，和where条件相同，位置不同而已）

order by （通过哪个字段排序，升序或者降序）

limit startindex，pagesize （起始数据号，页面大小）

8.事务（ACID）

8.1 概念

要么都成功，要么都失败

例子：

银行转账:

A 1000元 B 200元 C:800元 D：400 E：300元

A ——>B 200元： A：1000-200=800 B：200+200=400

C——>B 100元： C：800-100=700 B：200+100=300

D——>B 200元

原子性：说明转账是要实时同步的

一致性：说明这里的金额总数是不变的起始：1000+200=1200 最终：800+400=1200

隔离性：说明如果有其他用户也给B转账是互不影响的

持久性：说明事务一旦提交，数据库则会立即更新，如果未提交，则保留原始数据

脏读：说明了C给B转账100时，读取到的数据为B 200 然后 B：200+100=300

不可重复读：说明A B C之间在完成事务，提交事务的时候（已经提交），D突然向B转了200元，则结果会和预期不一样

虚读（幻读）：说明提交事务的时候，有一个事务插入了D数据，导致数据前后不一致

四个基本原则

1.原子性（atomicity）:

一个事务是一个不可分割的工作单位，事务中包括的操作要么都做，要么都不做。

2.一致性（consistency）：

事务必须是使数据库从一个一致性状态变到另一个一致性状态。一致性与原子性是密切相关的。

3.隔离性（isolation）：

一个事务的执行不能被其他事务干扰。即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。

4.持久性（durability）：

持久性也称永久性（permanence），指一个事务一旦提交，它对数据库中数据的改变就应该是永久性的。接下来的其他操作或故障不应该对其有任何影响。

隔离所导致的一些问题

脏读：

指一个事务读取了另外一个事务未提交的数据。

不可重复读：

在一个事务内读取表中的某一行数据，多次读取结果不同。（不一定是错误，只是场合不对）。

虚读（幻读）：

指在一个事务内读取到了其他事务插入的数据，导致前后读取不一致（通常是多了一行数据）。

8.2 语法


-- mysql是默认开启事务提交的
SET autocommit = 0;   -- 关闭事务提交
SET autocommit = 1;	-- 开启事务提交

-- 手动处理事务需先关闭自动提交
SET autocommit = 0;
-- 事务开启
START TRANSACTION -- 标记一个事务的开始，从这个语句开始，后面的SQL语句都在同一个事务内


-- 提交
COMMIT;
-- 回滚
ROLLBACK;

-- 事务结束记得开启自动提交
SET autocommit = 1;

-- 保存点
SAVEPOINT 保存点名;  -- 设置一个保存点（存档） 
ROLLBACK TO SAVEPOINT 保存点名  -- 回滚到保存点
RELEASE SAVEPOINT 保存点名 -- 删除保存点

8.3 转账测试

--   测试
CREATE TABLE `acount`(
	`id` INT(4) 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 `acount`(`name`,`money`) VALUES('A','400.00'),('B','1000.00')

-- 模拟转账事务
-- 关闭自动提交
SET autocommit = 0;
START TRANSACTION;        -- 开启一个事务

UPDATE `acount` SET `money`=`money`-200 WHERE `name`='A'
UPDATE `acount` SET `money`=`money`+200 WHERE `name`='B'

COMMIT;   -- 提交
ROLLBACK;   -- 假设有语句执行出错，事务回滚到原来的数据

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

9.索引

9.1 索引的分类

在一个表中，主键索引只能有一个，唯一索引可以有多个

主键索引（PRIMARY KEY）
- 唯一标识，主键索引只能有一个，只能有一个列作为主键
唯一索引（UNIQUE KEY）
- 避免重复的列出现，唯一索引可以重复，多个列都可以标识为唯一索引
常规索引（KEY/INDEX）
- 默认，index key来设置
全文索引（FULLTEXT）
- 在特定的数据库引擎下才有 MyISAM
- 快速定位数据

9.2 语法

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('张')

9.3 测试索引

-- 测试
CREATE TABLE `app_user` (
`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(50) DEFAULT'' COMMENT'用户昵称',
`email` VARCHAR(50) NOT NULL COMMENT'用户邮箱',
`phone` VARCHAR(20) DEFAULT'' COMMENT'手机号',
`gender` TINYINT(4) UNSIGNED DEFAULT '0'COMMENT '性别（0：男;1:女）',
`password` VARCHAR(100) NOT NULL COMMENT '密码',
`age` TINYINT(4) DEFAULT'0'  COMMENT '年龄',
`create_time` DATETIME DEFAULT CURRENT_TIMESTAMP,
`update_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT = 'app用户表'


-- 插入100万数据.
DELIMITER $$
-- 写函数之前必须要写，标志
CREATE FUNCTION inserts_data ()
RETURNS INT DETERMINISTIC    -- 确定性函数用关键词Deterministic标识,表示函数的返回值完全由输入参数决定。
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),'1025483534@qq.com', CONCAT('13', FLOOR(RAND()*(999999999-100000000)+100000000)),FLOOR(RAND()*2),UUID(), FLOOR(RAND()*100));
		SET i=i+1;
	END WHILE;
	RETURN i;
END;

SELECT inserts_data() -- 执行此函数 生成一百万条数据    1 min 48 sec

SELECT * FROM app_user WHERE `name`='用户10000'  -- 1.169 sec
SELECT * FROM app_user WHERE `name`='用户10000'  -- 1.337 sec
SELECT * FROM app_user WHERE `name`='用户10000'  -- 1.238 sec

EXPLAIN	SELECT * FROM app_user WHERE `name`='用户10000'  -- rows = 992803

-- 创建索引，再进行查询
-- 语法规范： 索引名（id_表名_字段名）
-- CREATE INDEX 索引名 on 表（字段）

CREATE INDEX id_app_user_name ON app_user(`name`)

SELECT * FROM app_user WHERE `name`='用户10000'  -- 0.003 sec
SELECT * FROM app_user WHERE `name`='用户10001'  -- 0.001 sec

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-bMddkIgP-1615810632690)(C:\Users\10254\AppData\Roaming\Typora\typora-user-images\image-20201113183506633.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-2noigPz7-1615810632693)(C:\Users\10254\AppData\Roaming\Typora\typora-user-images\image-20201113183030885.png)]

这里相当于键值对，创建索引后则会给每一行元组的主键创建一个 key：value ，要查value的值，只需定位到key即可

9.4 索引原则

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

索引的数据结构

Hash类型的索引

BTREE：InnoDB默认的数据结构

阅读博客：http://blog.codinglabs.org/articles/theory-of-mysql-index.html

10.权限管理和备份

10.1 权限管理

-- 创建一个新用户
CREATE USER menghaha IDENTIFIED BY '11111'  

-- 修改指定用户密码
ALTER USER 'menghaha' IDENTIFIED BY '123';

-- 重命名 RENAME USER 原来名字 TO 新名字
RENAME USER menghaha TO mengha

-- 用户授权 ALL privileges 全部的授权 
--   除了 授权给其他人
GRANT ALL PRIVILEGES ON *.* TO mengha

-- 查看指定用户权限
SHOW GRANTS FOR mengha
SHOW GRANTS FOR root@localhost
-- ROOT用户权限（最高权限）
GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION

10.2 备份

方式：

直接拷贝物理文件（data）
在可视化工具（SQLyog）手动导出
- [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-LLIB3akn-1615810632697)(C:\Users\10254\AppData\Roaming\Typora\typora-user-images\image-20201113212459626.png)]

使用mysqldump命令行导出

导出：

# mysqldump -h主机 -u用户名 -p密码 数据库名 表名 > 导出路径
mysqldump -hlocalhost -uroot -proot school acount >D:/a.sql

# mysqldump -h主机 -u用户名 -p密码 数据库名 > 导出路径
mysqldump -hlocalhost -uroot -proot school >D:/b.sql

导入：
# 登录的情况下，切换到指定数据库
# source 备份文件

mysql -uroot -proot
mysql> use 数据库名
mysql> source D:/a.sql
#未登录
mysql -u用户名 -p密码 数据库名 <D:/a.sql

：InnoDB默认的数据结构

阅读博客：http://blog.codinglabs.org/articles/theory-of-mysql-index.html

10.权限管理和备份

10.1 权限管理

-- 创建一个新用户
CREATE USER menghaha IDENTIFIED BY '11111'  

-- 修改指定用户密码
ALTER USER 'menghaha' IDENTIFIED BY '123';

-- 重命名 RENAME USER 原来名字 TO 新名字
RENAME USER menghaha TO mengha

-- 用户授权 ALL privileges 全部的授权 
--   除了 授权给其他人
GRANT ALL PRIVILEGES ON *.* TO mengha

-- 查看指定用户权限
SHOW GRANTS FOR mengha
SHOW GRANTS FOR root@localhost
-- ROOT用户权限（最高权限）
GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION

10.2 备份

方式：

直接拷贝物理文件（data）
在可视化工具（SQLyog）手动导出
- [外链图片转存中…(img-LLIB3akn-1615810632697)]

使用mysqldump命令行导出

导出：

# mysqldump -h主机 -u用户名 -p密码 数据库名 表名 > 导出路径
mysqldump -hlocalhost -uroot -proot school acount >D:/a.sql

# mysqldump -h主机 -u用户名 -p密码 数据库名 > 导出路径
mysqldump -hlocalhost -uroot -proot school >D:/b.sql

导入：
# 登录的情况下，切换到指定数据库
# source 备份文件

mysql -uroot -proot
mysql> use 数据库名
mysql> source D:/a.sql
#未登录
mysql -u用户名 -p密码 数据库名 <D:/a.sql

孟哈哈

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql知识点

MYSQL1.概念讲解2.数据库基本属性2.1 数据库列类型数值：tinyint 比较小的数据 1个字节smallint 小的数据 2个字节midiumint 中等的数据 3个字节int 一般的数据 4个字节（常用）bigint 较大的数据 8个字节float
复制链接

扫一扫