SQL之常用函数事务及权限操作

函数介绍

事先提供好的一些功能可以直接使用

函数可以用在SELECT语句及其子句

也可以用在UPDATE,DELETE语句当中。

函数分类

字符串函数、数值函数、日期和时间函数、流程函数、其它函数

字符串函数

concat(s1,s2...sn) 将传入的字符连接成一个字符串，任何字符串与null进行连接结果都是null

insert(str,x,y,instr) 将字符串str从x位置开始，y个字符长的子串替换为指定的字符

LOWER(Str)和UPPER(str) 将字符串转成小写或大写

LEFT(str,x)和RIGHT(str，x) 分别返回字符串最左边的x个字符和最右边的x个字符。如果第二个参数为null，那么不返回任何字符

LPAD（str,n,pad和RPAD(str,n,pad) 用字符串pad对str最左边或最右边进行填充，直接到长度为n个字符长度

LTRIM(str)和RTRIM(str) 去掉字符串当中最左侧和最右侧的空格

TRIM(str) 去掉字符串左右的空格

REPEAT(str,x) 返回str重复x次的结果

REPLACE(str,a,b) 用字符串b替换字符串str中所有出现的字符串a.

SUBSTRING(str,x,y) 返回字符串str中第x位置起y个字符长度的字符

 

数值函数

ABS(x) 返回X的绝对值

CEIL(x) 小数不为零部分上取整，即向上取最近的整数

FLOOR(x) 小数部分下取整，即向下取最近的整数

MOD(X,Y) 返回X/Y的模

RAND() 返回0-1内容的随机值

 

日期和时间函数

CURDATE() 返回当前日期，只包含年月日

CURTIME() 返回当前时间，只包含时分秒

NOW() 返回当前日期和时间，年月日时分秒都包含

UNIX_TIMESTAMP 返回当前日期的时间戳

FROM_UNIXTIME(unixtime) 将一个时间戳转换成日期

WEEK(DATE) 返回当前是一年中的第几周

YEAR(DATE) 返回所给日期是那一年

HOUR(TIME) 返回当前时间的小时

MINUTE(TIME) 返回当前时间的分钟

DATE_FORMAT(date,fmt) 按字符串格式化日期date值

DATE_ADD(date,interval expr type) 计算日期间隔

DATEDIFF(date1,date2) 计算两个日期相差的天数

 

流程函数

IF(value,t,f) 如果value是真，返回t,否则返回f

IFNULL(value1,value2) 如果value1不为空，返回value1否者返回value2

CASE WHEN THEN END

 

其他常用函数

DATABASE() 返回当前数据库名

VERSION() 返回当前数据库版本

USER() 返回当前登陆用户名

PASSWORD(STR) 对str进行加密

MD5() 返回str的MD5值

 

1.什么是事务

不可分割的操作,假设该操作有ABCD四个步骤组成.

若ABCD四个步骤都成功完成,则认为事务成功.

若ABCD中任意一个步骤操作失败,则认为事务失败

 

每条sql语句都是一个事务

事务只对DML语句有效,对于DQL无效

 

2.事务的ACID

原子性（Atomicity） 原子性是指事务包含的所有操作要么全部成功，要么全部失败回滚

 

一致性（Consistency） 一致性是指事务必须使数据库从一个一致性状态变换到另一个一致性状态，也就是说一个事务执行之前和执行之后都必须处于一致性状态。

让数据保持一定上的合理

一个商品出库时，仓库商品数量减1，对应用户的购物车中商品加1

 

隔离性（Isolation）

隔离性是当多个用户并发访问数据库时，比如操作同一张表时，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离。

 

持久性（Durability）

持久性是指一个事务一旦被提交了，就不能再回滚了，已经把数据保存到数据库当中了。

 

3.事务的使用

开启事务 start transaction

 

提交事务 commit

所有语句全部执行完毕，没有发生异常,提交事务，更新到数据库当中。

 

回滚事务 rollback

当遇到一突发情况，撤销执行的sql语句

 

4.执行流程

 

 

5.事务的并发问题

脏读

老板要给程序员发工资，程序员的工资是3.6万/月。但是发工资时老板不小心按错了数字，按成3.9万/月，该钱已经打到程序员的户口，但是事务还没有提交，就在这时，程序员去查看自己这个月的工资，发现比往常多了3千元，以为涨工资了非常高兴。但是老板及时发现了不对，马上回滚差点就提交了的事务，将数字改成3.6万再提交

实际程序员这个月的工资还是3.6万，但是程序员看到的是3.9万。他看到的是老板还没提交事务时的数据。这就是脏读。

解决办法 Read committed！读提交，能解决脏读问题

 

不可重复读

程序员拿着工资卡（卡里当然是只有3.6万），当他买单时（程序员事务开启），收费系统事先检测到他的卡里有3.6万，就在这个时候！！程序员的妻子要把钱全部转出充当家用，并提交。当收费系统准备扣款时，再检测卡里的金额，发现已经没钱了。程序员就会很郁闷，明明卡里是有钱的…

一个事务范围内两个相同的查询却返回了不同数据，这就是不可重复读

解决办法 Repeatable read

 

重复读

程序员拿着工资卡（卡里还是有3.6万），当他买时（事务开启，不允许其他事务的UPDATE修改操作），收费系统事先检测到他的卡里有3.6万。这个时候他的妻子不能转出金额了。接下来收费系统就可以扣款了。

 

幻读

程序员某一天去消费，花了2千元，然后他的妻子去查看他今天的消费记录（，妻子事务开启），看到确实是花了2千元，就在这个时候，程序员花了1万买了一部电脑，即新增INSERT了一条消费记录，并提交。当妻子打印程序员的消费记录清单时（妻子事务提交），发现花了1.2万元，似乎出现了幻觉，这就是幻读。

解决办法 Serializable

但是这种事务隔离级别效率低下，比较耗数据库性能，一般不使用。

 

对应关系

事务隔离级别 脏读 不可重复读 幻读

读未提交（read-uncommitted） 是 是 是

不可重复读（read-committed） 否 是 是

可重复读（repeatable-read） 否 否 是

串行化（serializable） 否 否 否

 

6.事务隔离级别

Read uncommitted 就是一个事务可以读取另一个未提交事务的数据

Read committed 一个事务要等另一个事务提交后才能读取数据

Repeatable read 就是在开始读取数据（事务开启）时，不再允许修改操作

Serializable 在该级别下，事务串行化顺序执行，可以避免脏读、不可重复读与幻读

 

7.查看隔离级别

select @@global.tx_isolation,@@tx_isolation;

 

8.设置隔离级别

全局的 set global transaction isolation level read committed;

当前会话 set session transaction isolation level read committed;

1.什么是权限

限制一个用户能够做什么事情，在MySQL中，可以设置全局权限，指定数据库权限，指定表权限，指定字段权限

 

2.有哪些权限

create 创建数据库、表或索引权限

DROP 除数据库或表权限

ALTER ALTER更改表，比如添加字段、索引等

DELETE 删除数据权限

INDEX 索引权限

INSERT 插入权限

SELECT 查询权限

UPDATE 更新权限

CREATE VIEW 创建视图权限

EXECUTE 执行存储过程权限

 

3.创建用户

create user '用户名'@'localhost' identified by '密码';

 

4.删除用户

DROP USER 用户名称

 

5.分配权限

GRANT 权限 (columns) ON 数据库对象 TO 用户 IDENTIFIED BY "密码" WITH GRANT OPTION

 

6.创建对指定数据库的所有权限

grant ALL PRIVILEGES on 数据库名.* TO gaowei@localhost IDENTIFIED BY '1234' WITH GRANT OPTION;

flush privileges;

 

创建一个超级管理员mylk,密码为1234，拥有所有权限，并能继续授予权限；

grant ALL PRIVILEGES on *.* TO mylk@localhost IDENTIFIED BY '1234' WITH GRANT OPTION;

flush privileges;

 

创建一个gxq用户只能对stu表进行CRUD操作

grant insert,update,select,delete on my_test.stu TO privuser@localhost IDENTIFIED BY '1234'

flush privileges;

 

7.查看权限

show grants 查看指定用户的权限

show grants for root@localhost

 

8.删除权限

REVOKE 权限 ON 数据库对象 FROM 用户；

老九学堂会员社群出品