常用函数
1 字符串函数
- concat函数
concat(s1,s2…sn)将传入的字符连接成一个字符串；
任何字符串与null进行连接结果都是null。
select concat('a','b','c');
select concat('a','b',null);
- insert函数
insert(str,x,y,instr) 将字符串str从x位置开始，y个字符长的子串替换为指定的字符。
select insert('abcdefg',3,2,'**');
- lower和upper函数
lower(str)和upper(str)将字符串转成小写或大写。
select lower('ABCD');
slect upper('ahdbSFsnuj');
- left和right函数
left(str,x)和right(str,x)分别返回字符串最左边的x个字符和最右边的x个字符；如果第二个参数为null，那么不返回任何字符。
SELECT LEFT('1234567890',3)
SELECT RIGHT('1234567890',3);
- lpad和rpad函数
lpad(str,n,pad)和rpad(str,n,pad)用字符串pad对str最左边或最右边进行填充，直到长度为n个字符长度。
select lpad('my',5,123456);
select rpad('my',6,123456);
- ltrim、rtrim和trim函数
ltrim(str)和rtrim(str) 去掉字符串当中最左侧和最右侧的空格；
trim(str) 去掉字符串左右的空格。
SELECT LTRIM(' 12345 ');
SELECT RTRIM(' 12345 ');
SELECT TRIM(' 12345 ');
- repeat函数
repeat(str,x)重复字符串x次
select repeat('123',4);
- replace函数
replace(str,a,b) 用b替换a
select replace('abcdefg','c','**');
- substring函数
substring(str,x,y) 返回字符串str中第x位置起y个字符长度的字符。
select substr('abcdefg',3,2);
2 数值函数
- abs函数
abs(x) 返回x的绝对值
SELECT ABS(-1);
- ceil函数
ceil(x) 小数不为零部分上取整，即向上取最近的整数
SELECT CEIL(1.2);
- floor函数
floor(x) 向下取整
SELECT FLOOR(1.9);
- mod函数
mod(x,y) 返回x/y的模
SELECT MOD(5,3);
【区分取模mod、取余rem】
a mod b = c, a rem b = d
取模运算和取余运算在第一步不同：
取余运算在取d的值时，向0方向舍入；
而取模运算在计算c的值时，向负无穷大方向舍入。
因此，当a和b符号一致时，取模运算和取余运算所得的值一致，因此结果一致。但是当符号不一致的时候，结果不一样。取模运算结果的符号和b一致，取余运算结果的符号和a一致。（在上面的mod运算实际上是取余，并非真正的取模）
- rand函数
rand() 返回0-1内容的随机值
SELECT RAND();
返回0-10内容的随机值
select ceil(rand()*10);
3 日期和时间函数
- curdate
curdate() 返回当前日期，年月日
SELECT CURDATE();
- curtime
curtime() 返回当前时间，时分秒
SELECT CURTIME();
- now
now() 返回当前日期和时间，年月日时分秒
SELECT NOW();
- unix_timestamp
unix_timestamp 返回当前日期的时间戳
SELECT UNIX_TIMESTAMP();
- from_unixtime
from_unixtime(unixtime) 将一个时间戳转换成日期
SELECT FROM_UNIXTIME(1565273819);
- week
week(date) 返回当前是一年中的第几周
SELECT WEEK(NOW());
或
SELECT WEEK(CURDATE());
或
SELECT WEEK('2019-08-08');
或
SELECT WEEK('20190808');
或
SELECT WEEK(20190808);
- year
year(date) 返回所给日期是那一年
SELECT YEAR(now());
其他写法，格式同week
- hour
hour(time) 返回当前时间的小时
SELECT HOUR(now());
- minute
minute(time) 返回当前时间的分钟
SELECT MINUTE(now());
- date_format
date_format(date,fmt) 按字符串格式化日期date值
select date_format(now(),'%M,%D,%Y');
| 格式 | 描述 |
|---|---|
| %a | 缩写星期名 |
| %b | 缩写月名 |
| %c | 月，数值 |
| %D | 带有英文前缀的月中的天 |
| %d | 月的天，数值(00-31) |
| %e | 月的天，数值(0-31) |
| %f | 微秒 |
| %H | 小时 (00-23) |
| %h | 小时 (01-12) |
| %I | 小时 (01-12) |
| %i | 分钟，数值(00-59) |
| %j | 年的天 (001-366) |
| %k | 小时 (0-23) |
| %l | 小时 (1-12) |
| %M | 月名 |
| %m | 月，数值(00-12) |
| %p | AM 或 PM |
| %r | 时间，12-小时（hh:mm:ss AM 或 PM） |
| %S | 秒(00-59) |
| %s | 秒(00-59) |
| %T | 时间, 24-小时 (hh:mm:ss) |
| %U | 周 (00-53) 星期日是一周的第一天 |
| %u | 周 (00-53) 星期一是一周的第一天 |
| %V | 周 (01-53) 星期日是一周的第一天，与 %X 使用 |
| %v | 周 (01-53) 星期一是一周的第一天，与 %x 使用 |
| %W | 星期名 |
| %w | 周的天 （0=星期日, 6=星期六） |
| %X | 年，其中的星期日是周的第一天，4 位，与 %V 使用 |
| %x | 年，其中的星期一是周的第一天，4 位，与 %v 使用 |
| %Y | 年，4 位 |
| %y | 年，2 位 |
- date_add
date_add(date,interval expr type) 加减日期
select date_add(now(),interval 3 year);
select date_add(now(),interval -3 year);
- datediff
datediff(date1,date2) 计算两个日期相差的天数
select datediff(now(),'2019-01-01');
4 流程函数
- if
if(value,t,f) 如果value是真，返回t，否则返回f
select if(2>3,'true','false');
select if((select salary from emp where ename = 'zx')>5000,'经理','员工');
- ifnull
ifnull(value1,value2) 如果value1不为空，返回value1，否则返回value2
SELECT sname, IFNULL(score,0) score from students ;
- case when then end
select case when 2>3 then '对' else '错' end;
5 其他常用函数
- database
database() 返回当前数据库名
select database();
- version
version() 返回当前数据库版本
select version();
- user
user() 返回当前登录用户名
select user();
- password
password(str) 对str进行加密
SELECT PASSWORD('abc');
-- mysql8.0版本移除了password()
- MD5
MD5() 加密返回str的MD5值
SELECT MD5('abc');
事务
一组不可分割的操作，每条sql语句都是一个事务，只对DML语句有效，对DQL无效。
1 事务的ACID:
- 原子性（Atomicity）
整个事务中的所有操作，要么全部完成，要么全部不完成，不可能停滞在中间某个环节。 - 一致性（Consistency）
指事务必须使数据库从一个一致性状态变换到另一个一致性的状态。 - 隔离性（Isolation）
隔离状态执行事务，使它们好像是系统在给定时间内执行的唯一操作。 - 持久性（Durability）
在事务完成以后，该事务对数据库所作的更改便持久的保存在数据库之中，并不会被回滚。
2 事务的使用：
start transaction[mysql默认开启事务]——提交事务——回滚事务
- 开启事务
start transaction;
- 查看表
- 进行操作
update students set score = score + 12
where sname = 'zs';
update students set score = score - 5
where sname = 'ls';
- 刷新再看表，发现没变
- 提交事务，刷新看表
commit;
- 回滚事务 rollback
-- 开启事务
start transaction;
-- 添加操作
update students set score = score + 12
where sname = 'zs';
update students set score = score - 5
where sname = 'ls';
-- 回滚事务
rollback;
回滚以后添加的操作取消
3 事务的并发问题
- 脏读：看到还没提交事务时的数据
- 不可重复读：一个事务范围内两个相同的查询却返回了不同数据
- 重复读：开启事务后，不允许其他事务的修改操作
- 幻读：查看数据后，其他事务进行修改操作，提交事务后数据出错。
4 事务隔离级别：
| 名称 | 特征 | 导致 |
|---|---|---|
| 读未提交 read uncommitted | 一个事务可以读取另一个未提交的事务的数据 | 【脏读】【不可重复读】【幻读】 |
| 不可重复读 read commmitte | 一个事务要等另一个事务提交后才能读取数据 | 【不可重复读】【幻读】 |
| 可重复读 repeatable read | 当使用可重复读隔离级别时，在事务执行期间会锁定该事务以任何方式引用的所有行 | 【幻读】【默认】 |
| 串行化 serializable | 【效率低下】 |
- 查看隔离级别
show variables like '%isolation%';
- 设置隔离级别
-- 全局
set global transaction isolation level read committed;
权限
限制一个用户能够做什么事情，在mysql中，可以设置全局权限，指定数据库权限，指定表权限，指定字段权限。
1 权限分类
| 名称 | 操作 |
|---|---|
| create | 创建数据库、表或索引权限 |
| drop | 删除数据库或表权限 |
| alter | 更改表，比如添加字段、索引等 |
| delete | 删除数据权限 |
| index | 索引权限 |
| insert | 插入权限 |
| select | 查询权限 |
| update | 更新权限 |
| create view | 创建视图权限 |
| execute | 执行存储过程权限 |
- index
http://c.biancheng.net/view/2605.html - create view
http://c.biancheng.net/view/2584.html - excute
https://blog.csdn.net/chinawangfei/article/details/52233276
2 权限操作
- 创建用户
create user '用户名'@'localhost' identified by '密码';
- 删除用户
drop user 'myxq'@'localhost';
- 分配权限
grant 权限(columns) on 数据库对象 to 用户 identified by "密码" with grant option
- 创建超级管理员，拥有所有权限，并能继续授予权限
grant ALL PRIVILEGES on *.* TO mylk@localhost IDENTIFIED BY '1234' WITH GRANT OPTION;
-- *.* 指所有数据库中的所有表
flush privileges;<br>
- 创建对指定数据库的所有权限
grant ALL PRIVILEGES on 数据库名.* TO zhangsan@localhost IDENTIFIED BY '1234' WITH GRANT OPTION;
flush privileges;
- 创建一个gxq用户只能对stu表进行crud操作
grant insert,uodate,select,delete on my_test.stu TO privuser@localhost IDENTIFIED BY '1234' WITH GRANT OPTION;
flush privileges;
- 查看权限
show grants
- 查看指定用户权限
show grants for root@localhost
- 删除权限
revoke 权限 on 数据库对象 from 用户;
1070
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
