Mysql学习笔记3

一、日期函数

函数	功能
curdate()	返回当前日期
curtime()	返回当前时间
now()	返回当前日期和时间
year(date)	指定date的年份
month(date)	指定date的月份
day(date)	指定date的日期
date_add(date,interval exper type)	返回一个日期/日期加上一个时间间隔exper后的时间值
datediff(date1,date2)（前面的日期较大）	返回起始时间date1和结束时间date2之间的天数

运用方式：select + 函数；

应用实例：

-- 1、返回当前时间和日期
select now();
-- 2、返回特定时间的月份
select month(now());
-- 3、返回70天之后的日期
select date_add(now(),interval 70 day);
--   返回80月之后的日期
select date_add(now(),interval 80 month);
--   返回20年之后的日期
select date_add(now(),interval 20 year);
-- 4、返回两个指定时间之间相差的天数
select datediff('2023-9-1','2023-7-15');
-- 5、查询所有员工的入职天数，并根据入职天数倒序排序
select name,datediff(curdate(),entrydate) as 'entrydays' from user order by entrydays desc;

二、流程函数

.

函数	功能
if(value,t,f)	如果value为true,则返回t,否则返回f
ifnull(value1,value2)	如果value不为空，返回为value1,否则返回valude2
case when[val1] then [res1] …else[default] end	如果val1为true,返回res1,…否则返回default默认值
case[expr] when [val1] then [res1] …else[default] end	如果expr的值等于val1.，返回res1,…否则返回defalut默认值

应用实例：

题目要求

• 案例：统计班级各个学员的成绩，展示规则如下：（表中有姓名，英语，数学，语 文成绩，分别统计各个学员个成绩的等级）
  .>=85,展示优秀
  .>=60,展示及格
  . 否则，展示不合格

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

三、 约束

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

• 目的：保证数据库中数据的重要性、有效性和完整性。

• 分类：

约束	描述	关键字
非空约束	限制该字段的数据不能为null	not null
唯一约束	保证该字段的所有数据都是唯一，不重复的	unique
主键约束	主键是一行数据的唯一标识，要求非空且唯一	primary key
默认约束	保存数据时，如果未指定该字段的值，则采用默认值	default
检查约束	保证字段值满足某一个条件	check
外键约束	用来让两张表的数据之间建立连接，保证数据的一致性的完整性	forelgn key

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

1、常用约束

约束条件	关键字
主键	primary key
自动增长	auto_increment
不为空(不为null)	not null
唯一	unique
逻辑条件	check
默认值	default

应用实例：

创建一个符合规范的表结构（要求以注释形式标注了）
create table user(                                   -- 约束条件(多个存在，之间写不用加,comment后面加标注)
    id int primary key auto_increment comment '主键',-- 主键，并且自动增长
    name varchar(10) not null unique comment '姓名', -- 不为空，并且唯一
    age int check ( age>0&&age<=120 ) comment '年龄',-- 大于0，并且小于等于120(检查约束）
    status char(1) default '1' comment '状态',       -- 如果没有指定该值，默认为1
    gender char(1) comment '性别'                    -- 无约束条件
)comment '用户表';

2、外键约束

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

添加外键的基本方法:
1、
create table 表名（
字段名 数据类型，
…
[constraint][外键名称] foreign key(外键字段名) references 主表(主表列名) )
2、
alter table 表名 add constraint 外键名称 foreign key(外键字段名) references 主表（主表列名）；
删除外键的基本方法:
alter table 表名 drop foreign key 外键名称

应用实例：

• 题目描述 创建一个部门表作主表和员工表作副表（建表要求不展示了），设置外键将部门表中’id’元素和员工表中的‘dept_id’元素联系起来，实现删除主表该元素时，副表中若存在相关联元素，不可直接删除该主表元素。

先创建主表和副表

-- 创建主表
create table dept(
    id int auto_increment comment 'ID' primary key ,
    name varchar(50) not null comment '部门名称'
)comment '部门表';

insert into dept(id, name) values (1,'研发部'),(2,'市场部'),(3,'财务部'),(4,'销售部'),(5,'办公室');

-- 创建副表
create table emp(
    id int auto_increment comment 'ID' primary key ,
    name varchar(50) not null comment '姓名',
    age int comment '年龄',
    job varchar(20) comment '职位',
    salary int comment '薪资',
    entrydate date comment '入职时间',
    managerid int comment '直属领导ID',
    dept_id int comment '部门ID'
)comment '员工表';

insert into emp values (1,'顾夜寒',32,'总裁',1000000,'2000-01-01',null,5),(2,'叶暖暖',25,'项目经理',20000,'2005-12-05',1,1),
                       (3,'冷柠',28,'开发',12500,'2006-11-03',2,1),(4,'韦一笑',48,'开发',15000,'2002-12-05',2,1),
                       (5,'常明',36,'开发',10500,'2004-09-07',3,1),(6,'小昭',20,'后端程序员',10000,'2015-10-12',2,1);

后进行创建及删除外键操作

-- 添加外键
alter table emp add constraint  fk_emp_dept_id3 foreign key (dept_id) references  dept(id);
-- 删除外键
alter table emp drop foreign key fk_emp_dept_id3;

注：可在软件里面自行验证，添加主表外键后，在主表要进行删除操作时，如果有外键与副表相关联，则该操作会报错。

3、删除/更新行为

行为	说明
no action	当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除/更新。（与restrict一致）
restrict	当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除/更新。(与弄action一致)
cascade	当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则也删除/更新外键在子表中的记录(同更新)
set null	当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则设置子表中该外键值为null（要求该外键允许为null）
set default	父表有变更时，子表将外键设为一个默认值（innodb不支持）

运用方式：
alter table 表名 addconstraint 外键名称 foreign key (外键字段) references 主表名（主表字段名）on urdate 行为要求 delete cascade;