MySql基础

最新推荐文章于 2024-05-20 08:59:13 发布
最新推荐文章于 2024-05-20 08:59:13 发布
阅读量983 收藏
点赞数 1
分类专栏： mysql 文章标签： 数据库 mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/Al_tair/article/details/123944894
版权

MySql基础

MySql基础

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

数据库的初识

1.1数据库是什么？

数据库: 英文DataBase 简称DB

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

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

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

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

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

1.3SQL是什么？

SQL 结构化查询语言

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

1.4三者有什么关系？

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

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

常用数据类型

数据类型图

image-20220325200013952

image-20220325192334220

代码示例

# 文本类型
# 注意区别字符和字节的区别
# 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

# 操作表emp
CREATE 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 # 默认2200
	resume 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,job
INSERT INTO yyy(id,name,job) SELECT eid,ename,ejob FROM xxx
update
UPDATE emp SET salary  = 2000; -- 把所有员工的工资改成2000
UPDATE 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语句

image-20220327154839507

注意：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
字符串函数

image-20220327203355592

SELECT CHARSET(`name`) FROM emp; -- 返回字符串的字符集
SELECT CONCAT(`name`,'job is',job) FROM emp; -- 连接字符串
# dual 亚元表，系统表，可以用来作为测试表
SELECT INSTR('woshiluoniansheng','sheng') FROM dual; -- 返回substring 在string中出现的位置，没有则返回0
SELECT 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;  -- 去掉前后的空格
数学函数

image-20220328154559847

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

image-20220328163915061

SELECT CURRENT_DATE() FROM dual; -- 当前日期 2022-03-28
SELECT CURRENT_TIME() FROM dual; -- 当前时间 16:41:27
SELECT CURRENT_TIMESTAMP() FROM dual; -- 当前时间戳 2022-03-28 16:42:18
SELECT DATE(CURRENT_TIMESTAMP()) FROM dual; -- 显示日期 2022-03-28
SELECT NOW() FROM dual; -- 当前日期和时间 2022-03-28 16:50:55
SELECT 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
流程控制函数

image-20220328190030824

# 说明
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的部门号和它的平均工资 注意题目：平均工资低于2000
SELECT deptno,AVG(sal) AS avgSal FROM emp GROUP BY deptno HAVING avgSal < 2000

注意细节

  1. 排列顺序：GROUP BY 子句出现在 WHERE 子句之后，ORDER BY 和 HAVING子句之前
  2. NULL 的行会单独分为一组
  3. 大多数 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 table
			WHERE ... -- 行过滤
			GROUP BY column -- 分组查询
			HAVING ... -- 分组过滤
			ORDER BY column -- 按字段排序 ASC DESC
			limit start,rows; -- 分页查询
多表查询

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

# 查询雇员名，雇员工资以及部门的名字（分别来自emp和dept表）
# 注意：如果需要指定某个表的列名，可以采用表名.列名的方式进行查询
SELECT emp.ename,emp.sal,dept.dname FROM emp,dept
							WHERE 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 emp	
				WHERE job IN (
                       SELECT DISTINCT job 
                    		   FROM emp 
                                WHERE deptno = 10
                 ) AND deptno != 10;
                 
 # 显示工资比部门30的所有员工的工资高的员工姓名，工资和部门号
 # 注意 ALL的使用
 SELECT ename,sal,deptno FROM emp
					WHERE sal > ALL(
							SELECT sal FROM emp 
                        		WHERE deptno = 30
					)
					
 # 显示工资比部门30的其中一个员工的工资高的员工姓名，工资和部门号
 # 注意 ANY的使用
 SELECT ename,sal,deptno FROM emp
					WHERE 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 UNION
SELECT ename,sal,job FROM emp WHERE job = 'MANAGER'
连接
自连接

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

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

image-20220330185145767

SELECT worker.ename AS '职员名',boss.ename  AS '上级名'FROM emp worker,emp boss
													WHERE worker.mgr = boss.empno;
外连接
  • 左外连接 (LEFT JOIN … ON) 左边的表必须完全显示
  • 右外连接 (RIGHT JOIN… ON) 右边的表必须完全显示

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

stu表

idname
1Jack
2Marry
3Tom
4Steven

exam表

idgrade
185
268
598

左外连接

idnamegrade
1Jack85
2Marry68
3TomNULL
4StevenNULL
SELECT stu.id,`name`,grade FROM stu LEFT JOIN exam ON stu.id = exam.id

右外连接

idnamegrade
1Jack85
2Marry68
NULLNULL98
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

班级表（主表）

idclass_nameaddress
1java杭州
2大数据义乌

学生表（外键所在表）

idnameclass_id
31Steven1
30Jack2
32Marry3（添加不成功）

注意细节

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

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

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

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

自增长

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

格式：字段名 整型 PRIMARY KEY AUTO_INCREMENT

image-20220402164050350

# 创建表
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原理

image-20220402190741727

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); -- 普通索引添加方式2
ALTER TABLE emp ADD ADD PRIMARY KEY id_index (id); -- 主键索引

删除索引

# 根据索引名来删除
DROP INDEX id_index ON emp;
# 删除主键索引
ALTER TABLE emp	DROP 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; 也是开启事务
# 设置保存点m
SAVEPOINT m;
# 进行dml操作
INSERT INTO stu VALUES(32,'marry',1);
SELECT *FROM stu
# 设置保存点n
SAVEPOINT n;
# 进行dml操作
INSERT INTO stu VALUES(33,'tom',1);
# 回退事务m
ROLLBACK TO m
# 回退事务n
ROLLBACK TO n

image-20220403142400675

注意细节

  • 如果不开始事务的时候，默认dml操作是自动提交的，不能进行回滚

  • 如果开始事务，你没有创建保存点，但是你可以执行rollback（回退所有事务），默认就是回退到你事务开始的状态

  • 如果从12：00回退到10：00，将删除10：00~12：00的保存点

  • mysql的事务操作必须使用InnoDB存储引擎，MyISAM不支持

事务的隔离级别

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

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

image-20220403145137550

# 设置事务隔离级别为读未提交
# 注意事务隔离一定要在事务开启之后谈隔离，不然就没有意义了
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存储引擎

    • 支持事务
    • 支持外键

image-20220403170505081

应用场景
  1. 不需要处理事务，只进行基本的CRUD操作，选择MYISAM
  2. 需要事务处理，选择InnoDB
  3. 不需要存储起来，就是短时间的实时状态（比如：用户状态：在线/离线等）,操作频繁，选择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 语句。

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

权限管理

权限表

image-20220403203427992

用户的管理

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

创建用户

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

查询用户

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

修改用户

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

删除用户

# 删除用户  用户名 @ 登录的IP
DROP 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'

注意细节

  1. 在创建用户的时候，如果不指定host，则为%，%表示所有IP都有连接权限
  2. 在删除用户的时候，如果host不是%，必须要具体明确指定
罗念笙
关注
专栏目录
MySql基础知识总结（SQL优化篇）
学Java，找哪吒
07-12 4万+
1、建表语句 + 联合索引 CREATE TABLE `student` ( `id` int(10) NOT NULL, `name` varchar(20) NOT NULL, `age` int(10) NOT NULL, `sex` int(11) DEFAULT NULL, `address` varchar(100) DEFAULT NULL, `phone` varchar(100) DEFAULT NULL, `create_time` timestamp .
MySQL基础PDF版
05-16
2. **MySQL基础** - **DBMS**：数据库管理系统（DataBase Management System），如MySQL，用于科学地组织和存储数据，高效地获取和维护数据。 - **SQL语句分类**：主要包括DDL（数据定义语言）、DML（数据操作语言...
MYSQL 练习2
南州博客
07-23 236
外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-bkVyU2jy-1690082268184)(C:/Users/26481/AppData/Roaming/Typora/typora-user-images/image-20230723105409125.png)]数据库在数据库中根据以下图示创建表，表结构如下，并插入以下数据，完成下面的。
【MySQL基础】MySQL基本操作详解
小杨的博客
10-29 3万+
大家好，我是小杨！今天我将详细的为大家介绍MySQL数据库的基本操作，希望大家能够从中收获多多！
MySQL基础知识总结
徒挽的博客
11-03 3519
MySQL基础知识总结
MySQL基础
最新发布
qq_53149124的博客
05-20 3012
用户自定义变量通常以一个”@“开始，作用域只针对于当前会话有效。创建变量和更新变量SET @<变量名>=<值>;SET @<变量名>:=<值>;SELECT @<变量名>:=<值>;查看变量值SELECT @<变量名>;删除变量#删除变量只需要给变量设置为NULL值就行SET @<变量名>=NULL;CREATE FUNCTION <函数名>([<参数列表>]) RETURNS <返回值类型> [<characteristics>]BEGIN#其它计算或SQL操作...
Mysql基础增删改查语句
热门推荐
weixin_53818758的博客
07-29 8万+
最基本必会的增删改查
MYSQL基础及性能优化
不才不才不不才的博客
07-23 1万+
如果user中的权限为Y,则不检查后面的db等的权限。读锁，当前session只能读本表，其他操作（写本表、或读写其他表）都报错，其他session对本表的写操作会阻塞。表示索引中使用的字节数，根据这个值可以判断索引的使用情况，特别是组合索引的时候，判断所有的索引字段是否被使用到。事务A多次读取同一个表中的数据，事务B在A读取的过程新增或删除了数据，导致A多次读到的数据记录数不一致。如果一定要使用，请使用覆盖索引。事务A多次读取同一数据，事务B在A读取的过程中对数据进行了修改，导致A多次读到的数据不一致。.
MySQL 基础知识（十）之 MySQL 架构
zjs246813的博客
02-19 3371
当 MySQL 接收到一个查询请求时，它首先会检查查询缓存中是否已经存在与该查询请求相同的查询结果。如果查询缓存中没有该查询结果，MySQL 会执行查询语句并将结果存储在查询缓存中，以便下次相同的查询可以直接从缓存中获取结果。我们一般不会使用查询缓存，因为一旦查询涉及的表发生改变，该缓存会失效，会被查询缓存清除。MySQL 执行器是 MySQL 服务器中的一个组件，它负责执行优化器生成的执行计划，并返回查询结果。，检查“解析树”中的元素是否在数据库中有对应的实体，并验证操作的合法性。《高性能MySQL》
Mysql基础（入门）
weixin_53150440的博客
09-03 7849
Mysql入门（一看就会）
MySQL基础命令
m0_55785131的博客
04-13 4293
1.登录MYSQL系统命令 打开DOS命令框shengfen，以管理员的身份运行 命令1：mysql -u usernae -p password 命令2：mysql -u username -p password -h 需要连接的mysql主机名(localhost本地主机名)或是mysql的ip地址（默认为：127.0.0.1）-P 端口号（默认：3306端口） 使用其中任意一个就OK,输入命令后DOS命令框得到mysql&gt;就说明已经进入了mysql系统 2. 查看mysql当中的.
康师傅mysql基础pdf
01-28
康师傅mysql基础pdf可能是一份教程或教材，涵盖了MySQL的基础知识，包括安装、配置、基本操作以及一些核心概念。在这里，我们将深入探讨MySQL的一些关键点。 1. **安装与配置**: MySQL的安装通常涉及下载安装包，...
超详细的MySQL基础入门教程 从0讲到数据库操作 共22页.pdf
08-21
2 连接 MYSQL 1 2.1 命令行 1 2.2 Navicate 2 2.3 例 1：连接到本机上的 MYSQL 2 2.4 例 2：连接到远程主机上的 MYSQL 3 2.5 退出 MYSQL 命令： exit （回车） 4 3 修改密码 4 3.1 例 1：给 root 加个密码 ab12 。 4...
mysql mysql基础介绍
09-24
MySQL 基础介绍 MySQL 是一种流行的开源关系型数据库管理系统（RDBMS），它使用 SQL（Structured Query Language）作为其操作语言。MySQL 具有高性能、可靠性和易于使用的特点，支持大量的并发用户连接，可以处理...
数据库练习
Al_tair的博客
04-09 3769
数据库练习雇员系统表雇员表的结构相关练习 大家好，我是小笙，我们学习完基础Mysql，我们接下来需要进行一些练习，巩固一下基础！ 雇员系统表 # 部门表 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
MySQL笔记--MySQL排序查询(6)
Al_tair的博客
06-03 3168
MySQL排序查询进阶三：排序查询案例1：查询员工信息，要求工资从高到低排序案例2：查询部门编号&gt;=90的员工信息，按入职时间的先后顺序进行排序案例3：按年薪的高低显示员工信息和年薪【按别名升序】案例4：按姓名的长度显示员工的姓名和工资和字节长度【按函数倒序】案例5：查询员工信息，要求先按工资降序，再按员工编号降序测试题1.查询员工的姓名和部门号和年薪，按年薪降序，按姓名升序2.选择工资不在8000到17000的员工的姓名和工资，按工资降序3.查询邮箱中包含‘e’的员工信息，并先按邮箱的字节数降序，再
MySQL笔记--MySQL常见函数(7)
Al_tair的博客
06-07 1530
MySQL常见函数和分组函数进阶4：常见函数基础知识概念优点调用（现成函数）分类单行函数字符函数1.length:获取参数值的字节个数2.concat: 拼接字符串3.upper,lower：转换成大写字母；转换成小写字母案例1：将姓变大写，名变小写，然后拼接4.substr,substring截取从指定索引处后面所有的字符截取从指定索引处指定字符长度的字符案例1;姓名首字符大写，其他字符小写然后用’-‘拼接，显示出来5.instr：返回子串第一次出现的索引，如果找不到则返回06.trim：去掉前后空格；可
MySQL笔记--MySQL分组函数和分组查询(8)
Al_tair的博客
06-17 1159
我们已经学习完排序查询，接下来我们涉足常见函数，跟上我的步伐！
MySQL基础操作与管理指南
“mysql基础知识小结，包括启动、关闭、登录MySQL的方法，以及SQL语言的五大类别：DQL、DML、TPL、DCL、DDL，还有密码管理和root密码找回。” 在MySQL的基础知识中，了解如何正确操作MySQL服务器至关重要。首先，...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

罗念笙

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值