MySql基础

文档中心

MySql基础

MySql基础
- 数据库的初识
- - 1.1数据库是什么？
  - 1.2数据库管理系统是什么？
  - 1.3SQL是什么？
  - 1.4三者有什么关系？
- 常用数据类型
- - 数据类型图
  - 代码示例
- 数据库的操作
- - 三种注释方式
  - 创建和切换数据库
  - 查看和删除数据库
  - 创建和删除表
  - 修改表
  - 数据操作CRUD
  - - insert
    - update
    - delete
    - select
    - - 过滤
      - where语句
        
        通配符
      - 排序
      - 函数
      - 统计函数
        
        字符串函数
        
        数学函数
        
        时间日期
        
        流程控制函数
        
        加密函数（扩展）
      - 分组查询
      - 分页查询
      - 语句顺序
      - 多表查询
      - 子查询
      - 单行子查询
        
        多行子查询
        
        多列子查询
      - 组合查询
      - 连接
      - 自连接
        
        外连接
  - 约束
  - - 主键（primary key）
    - 非空（not null）
    - 唯一（unique）
    - 外键（foreign key）
    - check约束
    - 自增长
  - 索引
  - - 索引机制（简述）
    - 索引的类型
  - 事务
  - - 事务的基本操作
    - 事务的隔离级别
    - 事务的ACID特性
  - 存储引擎
  - - 存储引擎特点
    - 应用场景
  - 视图
  - - 视图的操作
  - 存储过程
- 权限管理
- - 用户的管理
  - 授权

MySql基础

大家好，我是小笙！经过了一周的数据库学习，我开始掌握了一些数据库基础，还需继续努力，以下是我个人的笔记！（部分学习自SQL）

数据库的初识

1.1数据库是什么？

数据库: 英文DataBase 简称DB

数据库，顾名思义就是存储数据的仓库，实际上就是一些文件，用来存储特定格式的数据！

1.2数据库管理系统是什么？

数据库管理系统：英文DataBaseManagement 简称 DBMS

数据库管理系统是专门用来管理数据库中的数据，可以对数据库中的数据进行增删改查

常见的数据库管理系统： MySql Sqlserver Oracle等等

1.3SQL是什么？

SQL 结构化查询语言

我们可以通过学习SQL语言对数据库管理系统进行操作来实现对数据库中的数据增删改查，并且SQL在不同的数据库管理系统中普遍使用

1.4三者有什么关系？

我们编写SQL数据库语言，通过数据管理系统实现对数据库中的数据增删改查

简述：DBMS ----- 执行 —> SQL ---- 操作 —> DB

常用数据类型

数据类型图

MySql基础

代码示例

# 文本类型# 注意区别字符和字节的区别# size 都指代字符（数字或者汉字）CHAR(size) -- 固定字符串 最大存储 = 255字符（无论数字还是中文都是一个字符）VARCHAR(size) -- 可变长度字符串 最大存储 = 65532字节（还有3字节用来记录该字符串长度）# 如果VARCHAR 不够用，可以考虑使用 MEDIUMTEXT 或者 TEXT 或者 LONGTEXT# 日期类型CREATE TABLE `date`(birthday DATE, job_time DATETIME,login_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP  # 默认当前时间戳ON UPDATE CURRENT_TIMESTAMP # 更新时间戳)# 插入语句INSERT INTO `date`(birthday,job_time) VALUES('2022-11-11','2022-11-11 10:10:10');

数据库的操作

三种注释方式

# 注释-- 快捷键：ctrl + / ; 取消注释： ctrl + shift + / /*多行注释*/

创建和切换数据库

# 关键字create [if not exists]如果存在该数据库则不会再创建create database[if not exists]db_name[DEFAULT]character set:utf8 #默认字符集utf-8[DEFAULT]collate:utf8_general_ci #默认不区分大小写；utf8_bin区分大小写# 代码示例# 解说：创建了一个字符集为utf8的区分大小写的数据库CREATE DATABASE test CHARACTER SET utf8 COLLATE utf8_bin# 创建数据库，表的名字的时候为了规避关键字可以使用反引号来解决CREATE DATABASE `CREATE`# 切换成test数据库USE test

查看和删除数据库

# 显示数据库SHOW DATABASES# 删除数据库Drop DATABASE xxx

创建和删除表

# 创建表CREATE TABLE person(   #  列名  字段类型 field1 dataType,    field1 dataType,    field1 dataType,)CHARACTER SET 字符集 COLLATE 校对规则 ENGINE 引擎CHARACTER SET 字符集：默认为所在数据库字符集COLLATE 校队规则：默认为所在数据库校对规则# 代码示例CREATE TABLE `person` (  # 无符号整形`id` INT UNSIGNED, `name` VARCHAR(255),`pwd`  VARCHAR(16),`birthday` DATE)# 删除表DROP TABLE person# 查看test表的结构desc test;

修改表

# 修改表名 RENAME TABLE test to new_test;# 修改test表的字符集ALTER TABLE test CHARACTER SET utf8;# 添加列ALTER TABLE test ADD col VARCHAR(10);# 修改列ALTER TABLE test MODIFY salary DOUBLE;# 删除列中image列ALTER TABLE test DROP image;# 修改列名ALTER TABLE test CHANGE `name` another_name VARCHAR(30);

数据操作CRUD

# 操作表empCREATE TABLE emp(id INT, NOT NULL`name` VARCHAR(20),sex CHAR(1),birthday DATE,entry_date DATE,job VARCHAR(20),salary FLOAT, NOT NULL DEFAULT 2200 # 默认2200resume TEXT)CHARACTER SET utf8

insert

# 注意细节 # 1.'123' 可以放入到INT类型的数据里# 2.列出的数据位置必须与列的排列顺序要一致# 3.字符和日期类型插入的数据需要单引号或者双引号引上INSERT INTO emp   # 添加全部数据VALUES(1,'罗念笙','男','2001-03-18','2022-03-26','XXX',20000,'加油');INSERT INTO emp (id,`name`,sex) # 添加部分数据VALUES(2,'陈勇军','男');# 复制表 将xxx表中的eid,ename,ejob的数据复制到yyy表中id,name,jobINSERT INTO yyy(id,name,job) SELECT eid,ename,ejob FROM xxx

update

UPDATE emp SET salary  = 2000; -- 把所有员工的工资改成2000UPDATE emp SET salary  = 2000 WHERE id = 1; -- 把id=1的员工的工资改成2000 WHERE是条件语句

delete

DELETE FROM emp; -- 把所有员工记录删除DELETE FROM emp WHERE id = 1; -- 把id=1的员工记录删除

select

# 基本语法# DISTINCT(可选)：去重 ; * 查找所有列  ;  column指的是列名SELECT [DISTINCT] *|{column1,column2,column3...} FROM emp;# 给列取别名 （可以分别对多个列名进行设置不同的别名）# 格式：SELECT 列名 as 别名 FROM 表名;

过滤

不进行过滤的数据非常大，导致通过网络传输了多余的数据，从而浪费了网络带宽。因此尽量使用 SQL 语句来过滤不必要的数据，而不是传输所有的数据到客户端中然后由客户端进行过滤。

where语句

MySql基础

注意：between small and big 范围前面小于后面

通配符

通配符也是用在过滤语句中，但它只能用于文本字段。

% 匹配 >=0 个任意字符

_ 匹配 ==1 个任意字符

# 使用 Like 来进行通配符匹配SELECT *FROM emp WHERE `name` LIKE '罗%'; -- 以罗开头的任意字符

排序

使用order by语句进行排序（位置位于SELECT语句结尾）

ASC[默认升序]
DESC[降序]

#  显示的列 根据哪个列来排序# 格式：SELECT 列名或者别名 FROM  表名 WHERE... ORDER BY 列名或者别名 ASC或者DESC

函数

统计函数

MySql的主要函数

函数	说明
AVG()	返回某列的平均值
COUNT()	返回某列的行数
MAX()	返回某列的最大值
MIN()	返回某列的最小值
SUM()	返回某列值之和

COUNT（行数）

# 查询所有记录的个数SELECT COUNT(*) FROM emp# 查询该列的所有行数（注意：排除null）SELECT COUNT(`name`) FROM emp

SUM（求和）

# 查询该列所有值之和SELECT SUM(salary) FROM emp

AVG（平均值）

# 查询该列所有值的平均值SELECT AVG(salary) FROM emp

MAX（最大值）

# 查询该列所有值的最大值SELECT MAX(salary) FROM emp# 查询该列所有值的最小值SELECT MIN(salary) FROM emp

字符串函数

MySql基础

SELECT CHARSET(`name`) FROM emp; -- 返回字符串的字符集SELECT CONCAT(`name`,'job is',job) FROM emp; -- 连接字符串# dual 亚元表，系统表，可以用来作为测试表SELECT INSTR('woshiluoniansheng','sheng') FROM dual; -- 返回substring 在string中出现的位置，没有则返回0SELECT UCASE(`name`) FROM emp; -- 转换成大写SELECT LCASE(`name`) FROM emp; -- 转换成小写SELECT LEFT(`name`,2) FROM emp; -- 从String2中的左边起取length个字符SELECT LEFT(`name`,2) FROM emp; -- 从String2中的右边起取length个字符SELECT LENGTH(`name`) FROM emp; -- string长度[按照占用字节]SELECT REPLACE(`name`,'lns','zlr') FROM emp; -- 用replace_str替换search_str字符串SELECT STRCMP('lns','zlr') FROM dual; -- 比较字符串SELECT SUBSTRING(`name`,1,2) FROM emp; -- 从str的position开始[从1开始]，取length个字符SELECT LTRIM(' LNS ') FROM dual;  -- 去掉前面的空格SELECT RTRIM(' LNS ') FROM dual;  -- 去掉后面的空格SELECT TRIM(' LNS ') FROM dual;  -- 去掉前后的空格

数学函数

MySql基础

SELECT ABS(-10) FROM dual; -- 10SELECT CEILING(-1.1) FROM dual; -- -1SELECT FLOOR(1.1) FROM dual; -- 1SELECT FORMAT(78.2356,2) FROM dual; -- 78.24SELECT RAND() FROM dual; -- 返回随机数 范围[0,1.0]SELECT RAND(3) FROM dual; -- 返回随机数 范围[0,1.0] 但是seed = 3不变，该随机数也就不变了

时间日期

MySql基础

SELECT CURRENT_DATE() FROM dual; -- 当前日期 2022-03-28SELECT CURRENT_TIME() FROM dual; -- 当前时间 16:41:27SELECT CURRENT_TIMESTAMP() FROM dual; -- 当前时间戳 2022-03-28 16:42:18SELECT DATE(CURRENT_TIMESTAMP()) FROM dual; -- 显示日期 2022-03-28SELECT NOW() FROM dual; -- 当前日期和时间 2022-03-28 16:50:55SELECT DATE_ADD(CURRENT_TIMESTAMP(),INTERVAL 10 MINUTE) FROM dual; -- 2022-03-28 16:59:34 十分钟以后SELECT DATEDIFF(NOW(),'2001-3-18') FROM dual; -- 我活了7680天SELECT UNIX_TIMESTAMP FROM dual; -- 返回的是1970-1-1 到现在的秒数# 意义：开发中，可以存放一个整数，然后表示，通过FROM_UNIXTIME转换SELECT FROM_UNIXTIME(1618483408,'%Y-%m-%d %H:%i:%s') FROM dual;  -- 2021-04-15 18:43:28

流程控制函数

MySql基础

# 说明IF(expr1,expr2,expr3); -- 等价于java的三元运算IFNULL(expr1,expr2); -- 等价于如果expr1不为NULL返回expr1,反之返回expr2

加密函数（扩展）

# md5加密SELECT MD5('LUO12345') FROM dual; -- 70e4a6f2316f83d10718b2d251ec8c58 32位十六进制密文# PASSWORD(str)加密 MYSQL数据库密码就是用这个函数加密的SELECT PASSWORD('LUO12345') FROM dual;  -- D0D23440C6FBE6CD05E079658CE805F8A3589D1C

雇员系统表

# 部门表CREATE TABLE dept( deptno MEDIUMINT   UNSIGNED  NOT NULL  DEFAULT 0, dname VARCHAR(20)  NOT NULL  DEFAULT "",loc VARCHAR(13) NOT NULL DEFAULT ""); INSERT INTO dept VALUES(10, 'ACCOUNTING', 'NEW YORK'), (20, 'RESEARCH', 'DALLAS'), (30, 'SALES', 'CHICAGO'), (40, 'OPERATIONS', 'BOSTON');# 创建表EMP雇员CREATE TABLE emp(empno  MEDIUMINT UNSIGNED  NOT NULL  DEFAULT 0,  -- 编号ename VARCHAR(20) NOT NULL DEFAULT "",  -- 名字job VARCHAR(9) NOT NULL DEFAULT "", -- 工作mgr MEDIUMINT UNSIGNED , -- 上级编号hiredate DATE NOT NULL, -- 入职时间sal DECIMAL(7,2)  NOT NULL, -- 薪水comm DECIMAL(7,2) , -- 红利deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0  -- 部门编号); INSERT INTO emp VALUES(7369, 'SMITH', 'CLERK', 7902, '1990-12-17', 800.00,NULL , 20), (7499, 'ALLEN', 'SALESMAN', 7698, '1991-2-20', 1600.00, 300.00, 30),  (7521, 'WARD', 'SALESMAN', 7698, '1991-2-22', 1250.00, 500.00, 30),  (7566, 'JONES', 'MANAGER', 7839, '1991-4-2', 2975.00,NULL,20),  (7654, 'MARTIN', 'SALESMAN', 7698, '1991-9-28',1250.00,1400.00,30),  (7698, 'BLAKE','MANAGER', 7839,'1991-5-1', 2850.00,NULL,30),  (7782, 'CLARK','MANAGER', 7839, '1991-6-9',2450.00,NULL,10),  (7788, 'SCOTT','ANALYST',7566, '1997-4-19',3000.00,NULL,20),  (7839, 'KING','PRESIDENT',NULL,'1991-11-17',5000.00,NULL,10),  (7844, 'TURNER', 'SALESMAN',7698, '1991-9-8', 1500.00, NULL,30),  (7900, 'JAMES','CLERK',7698, '1991-12-3',950.00,NULL,30),  (7902, 'FORD', 'ANALYST',7566,'1991-12-3',3000.00, NULL,20),  (7934,'MILLER','CLERK',7782,'1992-1-23', 1300.00, NULL,10);# 工资级别表CREATE TABLE salgrade(grade MEDIUMINT UNSIGNED NOT NULL DEFAULT 0, -- 工资级别losal DECIMAL(17,2)  NOT NULL, -- 该级别的最低工资hisal DECIMAL(17,2)  NOT NULL -- 该级别的最高工资);INSERT INTO salgrade VALUES (1,700,1200);INSERT INTO salgrade VALUES (2,1201,1400);INSERT INTO salgrade VALUES (3,1401,2000);INSERT INTO salgrade VALUES (4,2001,3000);INSERT INTO salgrade VALUES (5,3001,9999);

分组查询

分组就是把具有相同的数据值的行放在同一组中。

# 显示每个部门的平均工资和最高工资 注意题目：每个部门SELECT deptno,AVG(sal) AS avgSal, MAX(sal) AS maxSal FROM emp GROUP BY deptno# WHERE 过滤行，HAVING 过滤分组，行过滤应当先于分组过滤 这就是为什么这里用 HAVING# 显示平均工资低于2000的部门号和它的平均工资 注意题目：平均工资低于2000SELECT deptno,AVG(sal) AS avgSal FROM emp GROUP BY deptno HAVING avgSal < 2000

注意细节

排列顺序：GROUP BY 子句出现在 WHERE 子句之后，ORDER BY 和 HAVING子句之前
NULL 的行会单独分为一组
大多数 SQL 实现不支持 GROUP BY 列具有可变长度的数据类型

分页查询

# 基本语法：SELECT ... LIMIT start,rows # 限制返回的行数。可以有两个参数，第一个参数start为起始行，从 0 开始；第二个参数rows为返回的总行数。SELECT * FROM 表名 ORDER BY 列名 LIMIT 每页显示记录数*（第n页数-1）,每页显示的记录数

语句顺序

总结顺序：where -> group by -> having -> order by -> limit

SELECT column1,column2,column3... FROM tableWHERE ... -- 行过滤GROUP BY column -- 分组查询HAVING ... -- 分组过滤ORDER BY column -- 按字段排序 ASC DESClimit start,rows; -- 分页查询

多表查询

顾名思义：查询两张及两张以上的表

# 查询雇员名，雇员工资以及部门的名字（分别来自emp和dept表）# 注意：如果需要指定某个表的列名，可以采用表名.列名的方式进行查询SELECT emp.ename,emp.sal,dept.dname FROM emp,deptWHERE emp.deptno = dept.deptno

子查询

子查询也叫嵌套查询，嵌入在其他sql语句中的查询语句

单行子查询

# 显示与'SMITH'同一个部门的员工SELECT *FROM emp WHERE deptno = (  SELECT deptno FROM emp WHERE ename = 'SMITH' ) AND ename != 'SMITH';

多行子查询

# 查询部门号为10的工作相同的雇员的名字，岗位，工资，部门号，但是不包括该部门号的员工SELECT ename,job,sal,deptno FROM empWHERE job IN (  SELECT DISTINCT job   FROM emp     WHERE deptno = 10   ) AND deptno != 10;    # 显示工资比部门30的所有员工的工资高的员工姓名，工资和部门号 # 注意 ALL的使用 SELECT ename,sal,deptno FROM empWHERE sal > ALL(SELECT sal FROM emp    WHERE deptno = 30) # 显示工资比部门30的其中一个员工的工资高的员工姓名，工资和部门号 # 注意 ANY的使用 SELECT ename,sal,deptno FROM empWHERE sal > ANY(SELECT sal FROM emp    WHERE deptno = 30)

多列子查询

查询返回多个列数据的子查询

# 查询与'ALLEN'的部门和岗位完全相同的所有雇员（不包含'ALLEN'本人）SELECT *FROM emp  WHERE (deptno,job) = (SELECT deptno,job FROM emp WHERE ename = 'ALLEN') AND ename != 'ALLEN';

组合查询

使用 UNION 来组合两个查询，如果第一个查询返回 M 行，第二个查询返回 N 行，那么组合查询的结果一般为 M+N 行；默认会去除相同行，如果需要保留相同行，使用 UNION ALL

注意：每个查询必须包含相同的列、表达式和聚集函数；只能包含一个 ORDER BY 子句，并且必须位于语句的最后。

# 合并工资大于2500和职位是经理的员工表SELECT ename,sal,job FROM emp WHERE sal > 2500 UNIONSELECT ename,sal,job FROM emp WHERE job = 'MANAGER'

连接

自连接

自连接可以看成内连接的一种，只是连接的表是自身而已。

可以理解为把一张表当成两张表来查询（员工和上级都在同一张表）

MySql基础

SELECT worker.ename AS '职员名',boss.ename  AS '上级名'FROM emp worker,emp bossWHERE worker.mgr = boss.empno;

外连接

左外连接 (LEFT JOIN … ON) 左边的表必须完全显示
右外连接 (RIGHT JOIN… ON) 右边的表必须完全显示

不管是左外连接还是右外连接，本质就是区分主表和次表

stu表

id	name
1	Jack
2	Marry
3	Tom
4	Steven

exam表

id	grade
1	85
2	68
5	98

左外连接

id	name	grade
1	Jack	85
2	Marry	68
3	Tom	NULL
4	Steven	NULL

SELECT stu.id,`name`,grade FROM stu LEFT JOIN exam ON stu.id = exam.id

右外连接

id	name	grade
1	Jack	85
2	Marry	68
NULL	NULL	98

SELECT stu.id,`name`,grade FROM stu RIGHT JOIN exam ON stu.id = exam.id

约束

用于确保数据库的数据满足特定的商业规则

包括：not null ，unique，primary key，foreign key，check

主键（primary key）

概念：用于唯一的标识表行的数据，当定义主键约束时，该列数据值不能重复

格式：字段名字段类型 primary key

注意细节

主键不能重复也不能为NULL的数据

一张表最多只有一个主键，但可以是复合主键（复合主键是指把多个字段设置为主键）

  SELECT TABLE `table`( id INT PRIMARY KEY, -- 主键   `name` VARCHAR(32), ) SELECT TABLE `table`( id INT,   `name` VARCHAR(32),  email VARCHAR(32),  PRIMARY KEY(id,`name`), -- 复合主键 )

非空（not null）

如果在列上定义了not null约束，则该列字段数值不能为空

格式：字段名字段类型 NOT NULL

唯一（unique）

当定义了唯一约束后，该列字段数值不能重复（但是可以有多个字段值为NULL）

格式：字段名字段类型 UNIQUE

外键（foreign key）

如果我们把如下的表中的学生表里的class_id（依附于班级表里的id）将其作为外键，如果要添加学生就要先判断先学生中的class_id是否存在于班级表中的id里，如果没有则添加不成功

CREATE TABLE `class`( -- 主表id INT PRIMARY KEY,    `name` VARCHAR(32) NOT NULL DEFAULT '',     address VARCHAR(32));INSERT INTO `class` VALUES(1,'java','杭州');INSERT INTO `class` VALUES(2,'大数据','义乌');CREATE TABLE stu( -- 外键所在表 id INT PRIMARY KEY,     `name`VARCHAR(32),     class_id INT ,FOREIGN KEY(class_id) REFERENCES `class`(id) -- 外键用法)INSERT INTO stu VALUES(31,'Steven',1);INSERT INTO stu VALUES(30,'Jack',2);/*    Cannot add or update a child row:     a foreign key constraint fails 外键约束失败*/INSERT INTO stu VALUES(32,'Marry',3); -- error

班级表（主表）

id	class_name	address
1	java	杭州
2	大数据	义乌

学生表（外键所在表）

id	name	class_id
31	Steven	1
30	Jack	2
32	Marry	3（添加不成功）

注意细节

外键字段的类型必须和主表字段类型一样（长度可以不同）
外键字段的值必须在主表字段中出现，或者为null，否则无法添加该外键字段数据
添加数据主表要先于外键所在表；删除数据外键所在表要先于主表

check约束

用于强制数据必须满足的条件

注意：目前mysql5.7不支持check，但是oracle，sqlserver和mysql8.0均支持

格式：字段名字段类型 CHECK (约束条件)

自增长

整型数据从1自增长（自增长修饰的一般都是整数型）

格式：字段名整型 PRIMARY KEY AUTO_INCREMENT

MySql基础

# 创建表CREATE TABLE test(id INT PRIMARY KEY AUTO_INCREMENT,`name` VARCHAR(32))# 插入表(两种形式)INSERT INTO test VALUES(NULL,'jack');INSERT INTO test(name) VALUES('marry');

注意细节

自增长要么和主键要么和唯一配合
可以修改默认的自增长的开始值
```
ALTER TABLE test AUTO_INCREMENT = 100
```

索引

索引机制（简述）

索引底层就是B+Tree原理

MySql基础

B Tree 指的是 Balance Tree，也就是平衡树。平衡树是一颗查找树，并且所有叶子节点位于同一层。

B+ Tree 是基于 B Tree 和叶子节点顺序访问指针进行实现，它具有 B Tree 的平衡性，并且通过顺序访问指针来提高区间查询的性能。

索引的类型

主键索引，主键自动的为主索引（PRIMARY KEY）添加主键就默认添加了索引
唯一索引（UNIQUE）
普通索引（INDEX）（最常用）
全文索引（FULL TEXT）(关键字搜索)

开发中全文索引一般使用的是Solr和ElasticSearch而不用MyISAM（Mysql自带的）

创建索引

# 创建索引CREATE UNIQUE INDEX id_index ON emp(deptno); -- 唯一索引CREATE INDEX id_index ON emp(deptno); -- 普通索引ALTER TABLE emp ADD INDEX id_index (id); -- 普通索引添加方式2ALTER TABLE emp ADD ADD PRIMARY KEY id_index (id); -- 主键索引

删除索引

# 根据索引名来删除DROP INDEX id_index ON emp;# 删除主键索引ALTER TABLE empDROP PRIMARY KEY

查询索引

# 查询表是否有索引SHOW INDEXES FROM emp; -- 方式一SHOW INDEXES FROM emp; -- 方式二SHOW KEYS FROM emp; -- 方式三DESC emp; -- 方式四

索引的应用场景

比较频繁查询的字段应该创建索引；更新比较频繁的字段不适合建立索引
唯一性太差的字段不适合单独创建索引（比如性别）

事务

事务由一组dml（update,insert,delete）语句组成，用于保证数据的一致性

当执行事务操作时，mysql会在表上加锁，防止其他用户更改表的数据

事务的基本操作

start transaction 开始一个事务（默认在开始事务处设置一个保存点）
savepoint 设置保存点
rollback to 回退事务
rollback 回退所有事务
commit 提交事务，不能再回退了

不能回退 SELECT 语句，回退 SELECT 语句也没意义；也不能回退 CREATE 和 DROP 语句

回退事务操作

# 开启事务START TRANSACTION; -- 或者 SET autocommit = off; 也是开启事务# 设置保存点mSAVEPOINT m;# 进行dml操作INSERT INTO stu VALUES(32,'marry',1);SELECT *FROM stu# 设置保存点nSAVEPOINT n;# 进行dml操作INSERT INTO stu VALUES(33,'tom',1);# 回退事务mROLLBACK TO m# 回退事务nROLLBACK TO n

MySql基础

注意细节

如果不开始事务的时候，默认dml操作是自动提交的，不能进行回滚
如果开始事务，你没有创建保存点，但是你可以执行rollback（回退所有事务），默认就是回退到你事务开始的状态
如果从12：00回退到10：00，将删除10：00~12：00的保存点
mysql的事务操作必须使用InnoDB存储引擎，MyISAM不支持

事务的隔离级别

概念：多个连接开启各自事务操作数据库中的数据时，数据库系统要负责隔离操作，以保证各个连接在获取数据时的准确性

种类	概述
脏读	当一个事务读取另一个事务尚未提交的修改数据（dml操作），称作脏读
不可重复读	由于其他提交事务所做的修改或者删除影响了当下事务的查询
幻读	由于其他提交事务所做的插入操作影响了当下事务的查询

MySql基础

# 设置事务隔离级别为读未提交# 注意事务隔离一定要在事务开启之后谈隔离，不然就没有意义了SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;# 查询事务隔离级别SELECT @@tx_isolation;

事务的ACID特性

原子性（Atomicity）：事务时一个不可分割的工作单位，事务中的操作要么都发生要么都不发生
一致性（Consistency）：事务必须使数据库从一个一致性状态转换成另外一个一致性状态
隔离性（Isolation）：多个用户访问数据库时，数据库为每一个用户开启事务，不能被其他事务的操作所影响
持久性（Durability）：一个事务一旦被提交，它对数据库中的数据改变是永久的，不能进行回滚

存储引擎

mysql数据库的表类型是由存储引擎决定

主要包括六种，分别是：

非事务安全性型：CSV，Memory，ARCHIVE，MRG_MYISAM，MYISAM

事务安全型：Inno`DB

# 查看所有的存储引擎SHOW ENGINE;# 修改表的存储引擎ALTER TABLE `表名` ENGINE = 存储引擎；

存储引擎特点

memory 存储引擎

数据存储在内存中【关闭数据库服务，数据丢失，但是表的结构还在】

执行数度很快

默认支持索引（hash表）
InnoDB存储引擎
- 支持事务
- 支持外键

MySql基础

应用场景

不需要处理事务，只进行基本的CRUD操作，选择MYISAM
需要事务处理，选择InnoDB
不需要存储起来，就是短时间的实时状态（比如：用户状态：在线/离线等）,操作频繁，选择memory

视图

视图是虚拟的表，本身不包含数据，其数据都来自对应的真实的表(基表)，也就不能对其进行索引操作。

视图特点

视图可以修改基表的数据，基表数据的改变会影响视图（因为视图的数据来自基表）
```
UPDATE view1 SET ename = '程序员' WHERE empno = 7499;
```
通过只给用户访问视图的权限，保证数据的安全性
简化复杂的 SQL 操作，比如复杂的连接
视图可以再使用视图（可以嵌套）

视图的操作

# 创建视图CREATE VIEW 视图名 AS SELECT ...CREATE VIEW view1 AS SELECT empno,ename FROM emp;  -- 显示empno，ename在emp基表中的数据# 更改视图数据ALTER VIEW 视图名 AS SELECT ...# 显示视图SHOW CREATE VIEW 视图名# 删除视图DROP VIEW 视图名1，视图名2

存储过程

存储过程可以看成是对一系列 SQL 操作的批处理。

使用存储过程的好处:

代码封装，保证了一定的安全性；
代码复用；
由于是预先编译，因此具有很高的性能。

命令行中创建存储过程需要自定义分隔符，因为命令行是以 ; 为结束符，而存储过程中也包含了分号，因此会错误把这部分分号当成是结束符，造成语法错误。

包含 in、out 和 inout 三种参数。

给变量赋值都需要用 select into 语句。

每次只能给一个变量赋值，不支持集合的操作。

权限管理

权限表

MySql基础

用户的管理

不同的数据库用户，登录到DBMS后，根据相应的权限，可以操作的数据库和数据对象都不一样

创建用户

#  创建用户 用户名 @ 登录的IP   password('123456')加密后的密码CREATE USER 'lns'@'localhost' IDENTIFIED BY '123456'

查询用户

# 查询所有用户权限SELECT *FROM mysql.user;

修改用户

# 修改r密码SET PASSWORD FOR 'root'@'localhost' = PASSWORD('1234567')

删除用户

# 删除用户  用户名 @ 登录的IPDROP USER 'lns'@'localhost'

授权

给用户授权

GRANT 权限列表 on 库.表名 to '用户名'@'登录IP' [IDENTIFIED BY '密码'] -- 密码可添加，如果存在该对象，则修改该表的密码# 权限列表GRANT SELECT ON ... -- 开放查询权限GRANT SELECT,DELETE,UPDATE,CREATE ON ... -- 开放多个权限GRANT ALL ON ... -- 开放所有权限# 库.对象名 改成 *.* 代表所有数据库中的表（表，视图，存储过程）# 库.对象名 改成 库.* 代表该库中的所有表

回收授权

GRANT 和 REVOKE 可在几个层次上控制访问权限:

整个服务器，使用 GRANT ALL 和 REVOKE ALL；
整个数据库，使用 ON database.*；
特定的表，使用 ON database.table；
特定的列；
特定的存储过程。

REVOKE 权限列表 ON 库.对象名 FROM '用户名'@'登录IP'

注意细节

在创建用户的时候，如果不指定host，则为%，%表示所有IP都有连接权限
在删除用户的时候，如果host不是%，必须要具体明确指定

MySql基础

MySql基础

MySql基础

数据库的初识

1.1数据库是什么？

1.2数据库管理系统是什么？

1.3SQL是什么？

1.4三者有什么关系？

常用数据类型

数据类型图

代码示例

数据库的操作

三种注释方式

创建和切换数据库

查看和删除数据库

创建和删除表

修改表

数据操作CRUD

insert

update

delete

select

过滤

where语句

通配符

排序

函数

统计函数

字符串函数

数学函数

时间日期

流程控制函数

加密函数（扩展）

分组查询

分页查询

语句顺序

多表查询

子查询

单行子查询

多行子查询

多列子查询

组合查询

连接

自连接

外连接

约束

主键（primary key）

非空（not null）

唯一（unique）

外键（foreign key）

check约束

自增长

索引

索引机制（简述）

索引的类型

事务

事务的基本操作

事务的隔离级别

事务的ACID特性

存储引擎

存储引擎特点

应用场景

视图

视图的操作

存储过程

权限管理

用户的管理

授权

相关问题

公告

标签