MySQL 基础（下）

函数

select 函数(参数);

• 字符串函数
  

-- 例如 将企业员工工号，统一为5位数 不足五位数的前面补零 比如：1为00001
updata emp set wrokno =lpad (woerkno, 5, '0');

• 数值函数

-- 通过数据库的函数，生成一个6位数的随机验证码
select lapd(round(rand()*1000000 , 0), 6, '0'); 
-- 生成的数字*1000000再取整数（删除小数位）即可得到

• 日期函数
  

-- 案例：查询所有员工的入职天数，并根据入职天数倒序排序
select name, datediff(curdate(), entrydate) as'entrydays' from emp order by entrydays desc; 

• 流程控制函数
  

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


-- 案例：统计班级各个学院成绩,展示的规则如下：
-- >=85. 显示优秀
-- >=65. 显示合格
-- 否则显示不及格

create  table score(
	id int comment 'ID',
	name varchar(20) comment '姓名',
	math int comment '数学',
	English int comment '英语',
	chinese int comment '语文' 
)comment '学员成绩表';
insert int score (id, name, math, english, chinese) value (1, 'Tom', 67 ,88 , 95),(2, 'rose' , 56, 65, 130), (3, 'jack' , 54, 85, 69);
 

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

约束

• 约束：作用于表中字段上的规则，用于现在存储再表中的数据
  目的：保证数据库数据的正确、有效性和完整性
  

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

案例演示：

create table user(
 	id int primary key auto_increment comment '主键', --自动增长
 	name varchar(10) not null unique comment '姓名',
 	age int check (age >=0 && <= 120 ) comment '年领',
 	status char(1) default '1' comment '状态',
 	gender char(1) comment '性别'
)comment '用户表'

-- 插入数据
insert into user(name, age, status, gender) values ('Tom1', 19, '1', '男'),('Tom2', 28, '0', '男');

insert into user(name, age, status, gender) values ('Tom1', 39, '1', '男');

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

注意：上述两张表，在数据库层面，并未建立外键关联，所以是无法保证数据的一致性和完整性的。
添加外键：

删除外键：

create table dept(
	id int auto_increment 'ID' primary key,
	name varchar(50) not null comment '部门名称'
	
)comment '部门表';
INSERT INTO dept (id, name)values (1, '研发部'), (2, '市场部'), (3, '财务部'), (4, '销售部'), (5, '总经办');


create table (
 	id int auto_increment comment 'ID' primary key, --自动增长
 	name varchar(10) not null comment '姓名',
 	age int comment '年领',
 	job varchar(20) comment '职称',
 	salary int  comment '薪资'
 	entrydate date comment '入职时间'
 	managerid int comment '直属领导'
 	dept_id int comment '部门ID'
)comment '员工表'

INSERT INTO emp (id, name , age, job, salary, entrydate, manangerid, dept_id)VALUES
			(1,'yy', 66, '总裁', 20000, '2001-11-20', null, 5),
			(2,'jd', 26, '项目经理', 15500, '2002-01-20', 1, 1),
			(3,'wfs', 36, '开发', 8000, '2003-08-02', 1, 1),
			(4,'gjy', 48, '开发', 7000, '2021-12-30', 1, 1),
			(5,'zyx', 23, '开发', 5000, '2007-06-10', 3, 1),
			(6,'smq', 20, '程序员', 5600, '2004-11-20', 2, 1);

-- 添加外键
alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id);

--删除外键
alter table emp drop  foreign key fk_emp_dept_id;

更新和删除

create table dept(
	id int auto_increment 'ID' primary key,
	name varchar(50) not null comment '部门名称'
	
)comment '部门表';
INSERT INTO dept (id, name)values (1, '研发部'), (2, '市场部'), (3, '财务部'), (4, '销售部'), (5, '总经办');


create table (
 	id int auto_increment comment 'ID' primary key, --自动增长
 	name varchar(10) not null comment '姓名',
 	age int comment '年领',
 	job varchar(20) comment '职称',
 	salary int  comment '薪资'
 	entrydate date comment '入职时间'
 	managerid int comment '直属领导'
 	dept_id int comment '部门ID'
)comment '员工表'

INSERT INTO emp (id, name , age, job, salary, entrydate, manangerid, dept_id)VALUES
			(1,'yy', 66, '总裁', 20000, '2001-11-20', null, 5),
			(2,'jd', 26, '项目经理', 15500, '2002-01-20', 1, 1),
			(3,'wfs', 36, '开发', 8000, '2003-08-02', 1, 1),
			(4,'gjy', 48, '开发', 7000, '2021-12-30', 1, 1),
			(5,'zyx', 23, '开发', 5000, '2007-06-10', 3, 1),
			(6,'smq', 20, '程序员', 5600, '2004-11-20', 2, 1);

-- 添加外键
alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id);

--删除外键
alter table emp drop  foreign key fk_emp_dept_id;

-- 外键的删除和更行
alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id) on update cascade on delete cascade;
// 删除 父表外键内容后 子表对映外键人员 数据清除

alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id) on update set null on delete set null;
// 删除 父表外键内容后 子表对映外键人员 外键项内容更变为null