MySQL基础- 函数 和 约束

MySQL函数

函数是指一段可以直接被另一段程序调用的程序或代码
MySQL有许多内置函数，可以直接调用

字符串函数

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

concat：拼接字符串
lower：将字符串转小写
upper：将字符串转大写
lpad：左填充（中间的参数代表返回字符串的总长度）
rpad：右填充（中间的参数代表返回字符串的总长度）
trim：去掉头尾的空格
substring：截取字符串（包头不包尾）

函数演示

-- concat
select concat('hello','One','Mysql');
-- lower
select lower('HELLO');
-- upper
select upper('hello');
-- lpad
select lpad('one',5,'-');-- --one
-- rpad
select rpad('two',10,'-');-- two-------
-- trim
select trim (' one ');
-- substring
select substring('wxxyangyuying',4,12);

• 练习
  

use onesql;
update emp set workno=lpad(workno,5,'0');

数值函数

常见的数值函数如下：

ceil：向上取整
floor：向下取整
mod（x，y）：返回x/y的余数
rand：返回0-1以内的随机数
round（x，y）求参数x四舍五入的值，保留y位小数

函数演示：

-- ceil
select ceil(2.5);-- 3
-- floor
select floor(2.5);-- 2
-- mod
select mod(5,4);-- 1
-- rand
select rand();-- 0.9962585717404807
-- round
select round(3.527,2);-- 3.53
select round(3.527,1);-- 3.5

练习：

select lpad(round(rand(),6)*1000000,6,'0');

日期函数

常见的日期函数

curdate：返回当前日期
curtime：返回当前时间
now：返回当前日期和时间
year（date）：获取指定date的年份
month（date）：获取指定date的月份
day（date）：获取指定date的日期
date_add（date，interval expr type）：返回一个日期/时间值加上一个时间间隔expr后的时间值
datediff（date1，date2）：返回起始时间date1和结束时间date2之间的天数，这里求取时间差数时是用date1-date2

注意：日期时间类型用’‘（字符串）包裹，例如’2023-05-23‘

函数演示：

-- curdate
select curdate();-- 2023-05-23
-- curtime
select curtime();-- 18:17:06
-- now
select now();-- 2023-05-23 18:17:23
-- year
select year(now());-- 2023
-- month
select month(now());-- 5
-- day
select day(now());-- 23
-- date_add
select date_add(now(),interval 20 day );-- 2023-06-12 18:20:34
select date_add(now(),interval -20 day );-- 2023-05-03 18:20:48
select date_add(now(),interval 20 hour );-- 2023-05-24 14:21:10
select date_add(now(),interval 20 day_hour );-- 2023-05-24 14:21:27
select date_add(now(),interval 20 minute );-- 2023-05-23 18:41:48
select date_add(now(),interval 20 year );-- 2043-05-23 18:22:07
select date_add(now(),interval 20 month );-- 2025-01-23 18:22:26
select date_add(now(),interval 20 year_month );-- 2025-01-23 18:22:37
-- datediff
select datediff(now(),'2023-09-21');-- -121
select datediff('2023-09-21',now());-- 121

use onesql;
select name,datediff(now(),entryDate) as entryd from emp order by entryd desc;
# 张三,8543
# 李123,8543
# 张四,8512
# 李32,8510
# 李四,8483
# 李21,8483
# 李1,8452
# 李92,8422
# 李3,8391
# 李4s,8361
# 李5,8330
# 李61,8299
# 李12,8299
# 李19,8288
# 李67,8269
# 李7,8238

流程函数

流程控制函数时很常用的一类函数，可以在SQL语句中实现条件筛选，提高语句的效率
常用的流程控制函数：

if（value，t，f）：如果value为true，返回t，否则返回f；
ifnull（value1，value2）：如果value1不为空（null），返回value1，否则返回value2；
case when [val1] then [res1] when[val2] then[res2] ... else [default] end：如果val1为true，返回res1，如果val2为true，返回res2…否则返回default默认值
case [expr] when [val1] then [res1] when [val2] then [res2] ...else [default] end：如果expr的值等于val1，返回res1，如果expr的值等于val2，返回res2…否则返回default默认值

函数演示：

-- if
select if(true,'ok','error');-- ok
select if(false,'ok','error');-- error
-- ifnull
select ifnull('one','two');-- one
select ifnull('','two');-- （一个空串，并不会返回two，因为’‘不为null）
select ifnull(null,'two');-- two
-- case when [val1] then [res1] when[val2] then[res2] ... else [default] end
select case when true then 'a' when true then 'b' else 'c' end;-- a
select case when false then 'a' when true then 'b' else 'c' end;-- b
select case when false then 'a' when false then 'b' else 'c' end;-- c
-- case [expr] when [val1] then [res1] when [val2] then [res2] ...else [default] end
select case 1 when 1 then 'one'when 2 then 'two' else 'three' end;-- one
select case 2 when 1 then 'one'when 2 then 'two' else 'three' end;-- two
select case 10 when 1 then 'one'when 2 then 'two' else 'three' end;-- three

-- 需求：查询emp员工表中的姓名和工作地址，（如果地址为北京，上海，返回一线城市，其余返回二线城市）
-- 这是使用case when [val1] then [res1] when[val2] then[res2] ... else [default] end的方式
select name,
    case when workAddress='北京' then '一线城市'
        when workAddress='上海' then '一线城市' else '二线城市' end from emp;
-- 还可以使用case [expr] when [val1] then [res1] when [val2] then [res2] ...else [default] end的方式来写
select name,
       case workAddress
           when '北京' then '一线城市' when '上海' then '一线城市' else '二线城市' end from emp;
# 张三,一线城市
# 张四,一线城市
# 李四,二线城市
# 李1,二线城市
# 李92,二线城市
# 李3,二线城市
# 李4s,二线城市
# 李5,二线城市
# 李61,一线城市
# 李67,一线城市
# 李7,一线城市
# 李12,一线城市
# 李21,二线城市
# 李123,二线城市
# 李32,一线城市
# 李19,一线城市

练习：

create table score(
    id int comment 'ID',
    name varchar(20) comment '姓名',
    math int comment '数学',
    english int comment 'english',
    chinese int comment 'chinese'
)comment '成绩表';
insert into score values (1,'tom',67,88,93),
                         (2,'rose',69,59,99),
                         (3,'jack',88,89,90);

select name,
       case when math>=85 then '优秀' when math>=60 then '及格' else '不合格' end as '数学',
       case when chinese>=85 then '优秀' when chinese>=60 then '及格' else '不合格'end as '语文',
       case when english>=85 then '优秀' when english>=60 then '及格' else '不合格'end as '英语'
from score;

小结

MySQL约束

约束的概述

概念：约束是作用于表中字段上的规则，用于限制存储在表中的数据

约束的目的：保证数据库中数据的正确，有效和完整性，一致性。

• 常见的约束分类：

not null：非空约束：限制字段的数据不能存储null值

unique：唯一约束：限制字段的所有数据都是唯一的，不能重复

primary key：主键约束：主键是一行数据的唯一标识，要求非空且唯一

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

check：检查约束（在MySQL的8.0.16版本之后）保证字段值满足一个条件或多个条件

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

注意：约束是作用于表中字段上的，可以在创建表/修改表的时候添加约束

约束的演示（语法）

案例：
创建下列表格，按照约束条件

这里id需求中的自动增长涉及到
mysql关键字中的AUTO_INCREMENT -- auto_increment

-- 约束演示
use onesql;
create table user(
     -- 在数据类型后就可以写约束了
    id int primary key auto_increment comment 'ID',
    name varchar(10) not null unique comment '姓名',
    age int check ( age<=120&&age>0 ) comment '年龄',
    status char(1) default '1' comment '状态',
    gender char(1) comment '性别'
)comment '用户表';

-- 验证
-- 这里添加时没有添加id，但是在表中会自动的递增添加id，从1开始
insert into user(name, age, status,gender) values ('tom',20,0,'a');
insert into user(name, age, status,gender) values ('tom1',20,0,'a');
insert into user(name, age, status,gender) values ('tom2',20,0,'a');

使用datagrip的图形化页面创建表更方便

外键约束

概念：让两张表的数据建立连接，从而保证数据的一致性和完整性

例如：将dept_id与id建立外键约束后
这里的dept_id就是员工表的一个外键，它关联的就是部门表的主键id，员工表dapt_id为5就代表着在部门表中id为5，对应的name字段为总经办

在这种主外键的关系中，我们把部门表（主键）称为父表，把员工表（外键）称为子表，即具有外键的表为子表，外键所关联的表称为父表

语法：
1.添加外键：

-- 有两种情况
-- 1.在创建表的时候直接添加
CREATE TABLE 表名(
	字段名 数据类型
	...
CONSTRAINT [外键名称] FOREIGN KEY (外键字段名) REFERENCES 主表(主键名)
);

create table 表名(
	字段名 数据类型
	...
constraint [外键名称] foreign key (外键字段名) references 主表(主键名)
);

-- 2.表结构创建完成后额外添加
ALTER TABLE 表名（需要添加外键的表） ADD CONSTRAINT 外键名称
（这里的的名称可以自定义） FOREIGN KEY(外键字段名) REFERENCES 主表(主键名)

alter table 表名 add constraint 外键名称 foreign key(外键字段名) 
references 主表(主键名)

2.删除外键

ALTER TABLE 表名（有外键的表） DROP FOREIGN KEY 外键名称;
alter table 表名 drop foreign key 外键名称;

演示：

user表
id 。 。 。 。 
1,tom1,20,0,a,1
2,tom2,20,0,a,2
3,tom3,20,0,a,3
score表
id 。 。 。 。 dept_id
1,tom,67,88,93,1
2,rose,69,59,99,2
3,jack,88,89,90,3


alter table score add constraint fk_emp_id foreign key (dept_id) references user(id);

alter table score drop foreign key fk_emp_id;

外键删除/更新数据时的行为

外键的删除和更新行为：

no action：在父表中删除/更新对应记录时，会先检查该记录是否有外键，如果有就不允许删除和更新（默认行为）

restrict：与no action一样

cascade：当在父表中删除/更新对应记录时，首先检查该记录是否有外键，如果有，也会删除/更新外键在子表中的记录（与父表是一样的变化）

set null：当在父表中删除对应记录时，首先检查该记录是否有对应外键，如果有则设置子表中该外键值为null（所以要求该外键允许取null）

set default：父表有变更时，子表将外键设置成一个默认的值（lnnodb不支持）

在创建外键时指定外键约束的行为语法：

ALTER TABLE 表名（需要添加外键的表） ADD CONSTRAINT 外键名称
（这里的的名称可以自定义） FOREIGN KEY(外键字段名) REFERENCES 主表(主键名)ON 
UPDATE CASADE ON DELETE CASADE;

alter table 表名 add constraint 外键名称 foreign key(外键字段名) 
references 主表(主键名) on update casade on delete casade/no action

ON UPDATE后面的是更新数据时的行为
ON DELETE后面是在删除数据时的行为

alter table score add constraint fk_emp_id foreign key (dept_id) references user(id)
    on update cascade on delete cascade ;

datagrip的图形化界面更方便

小结