MySQL学习笔记3

一、函数

字符串函数

使用规则：select 函数

1，concat(s1,s2,s3) 字符串拼接，将s1,s2,s3拼接成一个字符串

select concat('hello', 'mysql');

2，lower(str) 将字符串str全部转为小写

select lower('HELLO');

3，upper(str) 将字符串str全部转为大写

select upper('hello');

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

select lpad('01', 5, '-');

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

select rpad('01', 5, '-');

6，trim(str) 去掉字符串头部和尾部的空格

select trim(' hello mysql  ');

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

select substring('hello mysql',1,5);

8，数据更新例子

# 将所有员工的managerid统一为5位数，在前面补0
update emp set managerid = lpad(managerid, 5, '0');

数值函数

使用规则：select 函数

1，ceil(x) 向上取整

select ceil(1.3);

2，floor(x) 向下取整

select floor(1.3);

3，mod(x,y) 返回x/y的模 求模运算

select mod(12, 5);

4，rand() 返回0~1内的随机数

select rand();

5，round(x,y) 求参数x的四舍五入的值，保留y位小数

select round(2.345, 2);

6，例

# 生成六位数的随机验证码
# 利用rand生成随机数乘1000000取整，保留六位，前面不足六位补0
select lpad(round(rand()*1000000, 0), 6, 0);

日期函数

1，curdate() 返回当前日期

select curdate();

2，curtime() 返回当前时间

select curtime();

3，now() 返回当前日期和时间

select now();

4，year(date) 获取指定date的年份

select year(now());

5，month(date) 获取指定date的月份

select month(now());

6，day(date) 获取指定date的日期

select day(now());

7，date_add(date,INTERVAL expr type) 返回一个日期/时间值加上一个时间间隔expr后的时间值

select date_add(now(), interval 70 day );  # 往后推70天
select date_add(now(), interval 70 month );  # 往后推70个月
select date_add(now(), interval 70 year );  # 往后推70年

8，datediff(date1, date2) 返回起始时间date1和结束时间date2之间的天数

select datediff('2024-07-06', '2021-12-03');

9，例

# 查询所有员工的入职天数，并根据入职天数倒序排序  (给入职天数起别名，方便排序)
select name, datediff(curdate(), entrydate) as 'entrydays' from emp order by entrydays desc;

流程函数

1，if(value, t, f) 如果value为true，则返回t否则返回f

select if(false, 'ok', 'error');  # value为False，所以会返回error

2，ifnull(value1, value2) 如果value1不为空，返回value1，否则返回value2

select ifnull('ok', 'error');   # ''，两个'之间没有元素不代表为空

3，case when[val1] then [res1] ...else[dafault] end 如果val1为true，返回res1，...否则返回default默认值

# 查询emp表的员工姓名和员工职位（职员/销售--->基层员工，其他--->非基层员工）
select
    name,
    case job when '职员' then '基层员工' when '销售' then '基层员工' else '非基层员工' end as '职位'
from emp;

4，case[expr] when [val1] then [res1] ...else[default] end 如果expr的值等于val1，返回res1, ...否则返回default默认值

二、约束

约束 是作用在表中字段上的规则，用于限制存储在表中的数据  多个约束之间使用空格分开
目的 保证数据库中数据的正确，有效性和完整性

# 非空约束 限制该字段的数据不能为null NOT NULL

# 唯一约束 保证该字段的所有数据都是唯一，不重复的 UNIQUE

# 主键约束 主键是一行数据的唯一标识，要求非空且唯一 PRIMARY KEY

# 默认约束 保存数据时，如果未指定该字段的值，则采用默认值 DEFAULT

# 检查约束 保证字段值满足某一个条件 CHECK

# 外键约束 用来让两张表的数据之间建立连接，保证数据的一致性和完整性 FOREIGN KEY

1，外键约束

外键用来让两张表的数据之间建立连接，从而保证数据的一致性和完整性

添加外键规则：

# 1，创建表的时候直接添加外键
create table 表名(
    字段名 数据类型,
    ...
    [constraint][外键名称] foreign key(外键字段名) references 主表(主表列名)
);

# 2，表创建好了之后，增加外键
alter table 表名 add constraint 外键名称 foreign key(外键字段名) references 主表(主列表名);

# 添加外键

alter table emp1 add constraint fk_emp_dept_id foreign key (dept_id) references dept(id);

# 删除外键

alter table emp1 drop foreign key fk_emp_dept_id;

# 删除/更新行为

cascade 当主表中删除/更新对应的记录时，首先检查该记录是否有对应的外键，如果有，则也删除/更新外键在子健中的记录

alter table emp1 add constraint fk_emp_dept_id foreign key (dept_id) references dept(id)
on update cascade on delete cascade ;

set null 当父表中删除对应记录时，首先检查该记录是否有对应的外键，如果有则设置子表中该外键值为null

alter table emp1 add constraint fk_emp_dept_id foreign key (dept_id) references dept(id)
on update set null on delete set null ;