mysql学习笔记

Durro.

已于 2023-02-18 20:23:26 修改

阅读量269

点赞数

文章标签：数据库

于 2022-12-22 22:50:00 首次发布

本文链接：https://blog.csdn.net/tastebud/article/details/128408621

版权

DQL数据查询语言

DQL-分页查询

DQL的执行顺序

DCL数据控制语言

其主要功能分为用户管理和权限控制。

用户管理

在mysql数据库里有张user表，里面保存的是可以控制访问此mysql服务的用户。此表由Host和User这两个字段构成主键，所以我们要指定唯一一个用户，需要这两个信息。

例子：

创建用户xzt，可以在任意主机访问该数据库，密码123456

CREATE user 'xzt'@'%' IDENTIFIED by '123456'

修改用户xzt的访问密码为123478

ALTER user 'xzt'@'%' IDENTIFIED WITH mysql_native_password by '123478'

删除xzt@%用户

DROP user 'xzt'@'%'

这类SQL开发人员使用得比较少，主要是DBA使用。

权限控制

MySQL中定义了许多权限，常用的权限如下：

例子：

查询用户xzt的权限

SHOW GRANTS FOR 'xzt'@'%'

授予用户xzt对数据库exam所有表的全部权限

GRANT ALL ON exam.* TO 'xzt'@'%'

撤销用户xzt对数据库exam所有表的全部权限

REVOKE ALL ON exam.* FROM 'xzt'@'%'

函数

字符串函数

MySQL中内置了许多字符串函数，常用的如下：

例子：

由于业务需求变更，员工工号统一为五位数，不足五位数的全部补0，如1号员工的工号应该为：00001

UPDATE emp SET workno = LPAD(workno,5,'0')

数值函数

常见的数值函数如下：

例子：

随机生成六位验证码

SELECT LPAD(ROUND(RAND()*1000000,0),6,'0')

日期函数

常见的日期函数如下：

例子：

查询所有员工的入职天数，并根据入职天数倒叙排序

SELECT name,DATEDIFF(CURDATE(),entrydate) AS 'entrydays' FROM emp ORDER BY entrydays DESC

流程函数

流程函数可以在SQL中进行条件筛选，从而提高语句查询效率

例子：

查询emp表中的员工姓名和工作地址，工作地址要求北京和上海显示为一线城市，其他城市显示为二线城市

SELECT
	name,(CASE workaddress
	WHEN '北京' THEN
		'一线城市'
	WHEN '上海' THEN
	  '一线城市'
	ELSE
		'二线城市'
END) AS '工作地址'
FROM emp

查询学生表中每位学生的语数英成绩，分数>=85显示优秀，>=60显示及格，否则显示不及格

SELECT
	id,
	name,
	(CASE WHEN math >= 85 THEN '优秀' WHEN math >= 60 THEN '及格' ELSE '不及格' END) '数学',
	(CASE WHEN english >= 85 THEN '优秀' WHEN math >= 60 THEN '及格' ELSE '不及格' END) '英语',
	(CASE WHEN chinese >= 85 THEN '优秀' WHEN math >= 60 THEN '及格' ELSE '不及格' END) '语文'
FROM score;

事务

事务是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。

如上图例子所示，我们需要把三个操作给封装在一个事务中，这样才会保证数据的一致性和完整性。而mysql中每条sql默认是一个事务，它执行完后就会自动提交。我们可以执行

SELECT @@autocommit

来查看当前事务的提交方式，为1表示自动提交，为0表示手动提交。我们可以执行

SET @@autocommit = 0

把当前事务改为手动提交。这样我们在要执行的sql最后面加上

COMMIT
ROLLBACK

就可以提交事务和回滚事务。回滚事务是指的当有异常发生时，已经执行过的sql都会恢复到原来状态。其实我们也可以不修改事务提交方式，一开始直接使用

START TRANSACTION

或

BEGIN

即可开启事务，接下来同样在最后加上事务提交和事务回滚即可。

并发事务问题

如下图所示，脏读就是事务A正在访问一个数据，而此时事务B也访问了这个数据，但是事务A还没完，后面还对这个数据作了修改，这就导致了数据的不一致。

如下图所示，不可重复读的现象也与脏读差不多，就是两个事务都在访问同一个数据，事务A一开始查询这个数据值为1，可能紧接着事务B就把这个值改为了2，而事务A再查这个数据值就为2了，因为事务是一致性的，我都没动过它，怎么会突然变了呢？

如下图所示，幻读是在解决了不可重复度的前提下产生的问题。事务A想插入一条数据，它先查询有没有这条数据，查询到没有它就准备插入了，可是这时，事务B先一步插入了这条数据，然后事务A再插入就会报错，这时事务A就再查询一次这个数据发现没有啊（此时已解决不可重复读，所以两次查询的结果是一样的），明明没有为什么还插入不了呢？这就是幻读。

事务隔离级别

set后面表示的作用范围，session表示当前会话用这个隔离级别，global表示全局都用这个隔离级别。

存储引擎

存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式。存储引擎是基于表的，而不是基于库的，所以存储引擎也可被称为表类型。

我们可以通过

SHOW CREATE TABLE course

查看course表的建表语句

CREATE TABLE `course` (
  `Cno` varchar(4) COLLATE utf8mb4_general_ci NOT NULL,
  `Cname` varchar(40) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `Cpno` varchar(4) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `Ccredit` smallint DEFAULT NULL,
  PRIMARY KEY (`Cno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci

我们可以看到我们建表时，默认就给我们设置了InnoDB作为该表的存储引擎。