MySQL入门学习二

函数

字符串函数

1. CONCAT(S1,S2,S3,…,Sn); 字符串拼接，将S1,S2,S3,…,Sn字符串拼接起来

select concat('Hello', 'Mysql');
-- 结果：HelloMysql

2. LOWER(str); 将字符串str全部转换成小写

select lower('Hello');
-- 结果：hello

3. UPPER(str); 将字符串str全部转换成大写

select lower('Hello');
-- 结果：HELLO

4. LPAD(str); 左填充，用字符串pad对str的左边进行填充，达到n个字符串长度

select lpad('01', 5 ,'-');
-- 结果：---01

5. RPAD(str); 右填充，用字符串pad对str的右边进行填充，达到n个字符串长度

select rpad('01', 5, '-');
-- 结果：01---

6. TRIM(str); 去掉字符串头部和尾部的空格

select trim(' Hel lo ');
-- 结果:Hel lo

7. SUBSTRING(str,start,len); 返回从字符串str从start位置起的len个长度的字符串

--start：指定子串的起始位置。起始位置从1开始计数。
--length：可选参数，指定要提取的子串的长度。如果省略此参数，则返回从起始位置到字符串末尾的所有字符。
select substring('Hello', 1, 3);
-- 结果：Hel

数值函数

8. CEIL(x); 向上取整

select ceil(1.5);
-- 结果:2
select ceil(1.1);
-- 结果:2

9. FLOOR(x); 向下取整

select floor(1.9);
-- 结果:1
select floor(1.1);
-- 结果:1

10. MOD(x,y); 返回x/y的模，取余

select mod(1,3);
-- 结果:1
select mod(0,3);
-- 结果:0
select mod(1,0);
-- 结果:NULL

11. RAND(); 返回0~1内的随机数

select rand();
-- 结果:0.5064212516941328

12. ROUND(x,y); 求参数x的四舍五入的值，保留y位小数

select round(2.365, 2);
-- 结果:2.37
select round(2.364, 2);
-- 结果:2.36

13. 实例

--通过函数，生成一个六位数的随机验证码
select rpad(round(rand()*1000000,0), 6, '0');
-- 结果:192550

日期函数

14. CURDATE(); 返回当前日期

select curdate();
-- 结果：2023-07-17 

15. CURTIME(); 返回当前时间

select curtime();
-- 结果：10:50:03

16. NOW(); 返回当前日期和时间

select now();
-- 结果：2023-07-17 10:50:37

17. YEAR(date); 获取指定date的年份

select year(now());
-- 结果：2023

18. MONTH(date); 获取指定date的月份

select month(now());
-- 结果：7

19. DAY(date); 获取指定date的日期

select day(now());
-- 结果:17

20. DATE_ADD(date,INTERVAL expr type); 返回一个日期/时间值加上一个时间间隔expr后的时间值

--INTERVAL：固定值
--expr：时间值
-- type：时间单位 DAY MONTH YEAR HOUR MINUTE SECOND
select date_add(now(), INTERVAL 70 DAY);
-- 结果：2023-08-16 11:30:23

21. DATEDIFF(date1, date2); 返回起始时间date1和结束时间date2之间的天数， date1-date2

select datediff('2023-07-01', '2023-08-01');
-- 结果：-31
select datediff('2023-08-01', '2023-07-01');
-- 结果：31

流程函数

22. IF(value, t, f) 如果value为true，则返回t， 否则返回f

select if(1=1, 'ok', 'error');
-- 结果：ok

23. IFNULL(value1, value2) 如果value1不为空，返回value1，否则返回value2

select ifnull(null, 2);
-- 结果：2
select ifnull('', 2);
-- 结果返回value1：

24. CASE WHEN [val1] THEN [res1] …ELSE [default] END 如果val1为true，返回res1，…否则返回default默认值

--查询score表中的学生姓名和成绩等级
select name, 
(case 
when math>=85 then '优秀' 
when math >= 60 then '及格' 
else '不及格' end) as '数学';
from score;

25. CASE [expr] WHEN [val1] THEN [res1] …ELSE [default] END 如果expr的值等于val1，返回res1， …否则返回default默认值

--查询temp表中的员工姓名和工作地址(北京/上海----》一线城市，其他----》二线城市)
select name, 
(case workaddress 
when '北京' then '一线城市' 
when '上海' then '一线城市' 
else '二线城市' end) as '工作地址';
from temp;

约束

非空约束

--语法
--第一种
create table 表名{
	字段名 数据类型 NOT NULL COMMENT '',
	......
};
--第二种
ALTER TABLE 表名 ADD COLUMN 字段名 数据类型 NOT NULL COMMENT '';

唯一约束

--语法
--第一种
create table 表名{
	字段名 数据类型 UNIQUE COMMENT '',
	......
};
--第二种
ALTER TABLE 表名 ADD COLUMN 字段名 数据类型 UNIQUE COMMENT '';

主键约束

--语法
--第一种
create table 表名{
	字段名 数据类型 NOT NULL AUTO_INCREMENT COMMENT '',
	......,
	PRIMARY KEY(字段名)
};
--第二种
ALTER TABLE 表名 ADD PRIMARY KEY(字段名);

默认约束

--语法
--第一种
create table 表名{
	字段名 数据类型 DEFAULT NULL COMMENT '',
	......
};
--第二种
ALTER TABLE 表名 ADD COLUMN 字段名 数据类型 DEFAULT NULL COMMENT '';

检查约束

--语法
--第一种
create table 表名{
	字段名 数据类型 CHECK(字段名满足条件) COMMENT '',
	......
};

外键约束

1. 概念：让两张表的数据之间建立连接，从而保证数据的一致性和完整性
2. 添加外键

--语法
--第一种
create table 表名{
	字段名 数据类型,
	......
	[CONSTRAINT] [外键名称] FOREIGN KEY(外键字段名) REFERENCES 主表(主表字段名)
};
--第二种
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY(外键字段名) REFERENCES 主表(主表字段名);

3. 删除外键

--语法
ALTER TABLE 表名 DROP FOREIGN KEY(外键名称);

4. 删除/更新行为

行为	说明
NO ACTION	当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除/更新，(与RESTRICT一致)
RESTRICT	当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除/更新，(与NO ACTION一致)
CASCADE	当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则也删除/更新外键在子表中的记录
SET NULL	当在父表中删除对应记录时，首先检查该记录是否有对应外键，如果有则设置子表中该外键值为null(这就要求该外键允许取null)
NO ACTION	父表有变更时，子表将外键列设置成一个默认的值(innodb不支持)

--语法  CASCADE对应上述表格中的行为
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY(外键字段名) REFERENCES 主表名(主表字段名) ON UPDATE CASCADE ON DELETE CASCADE;