一,函数
一,字符串函数
练习:
由于业务需求变更,企业员工的工号,统为5位数,目前不足5位数的全部在前面补0。比如: 1号员工的工号应该为00001:
update emp set workno = Lpad(workno, 5,'0');
二,数值函数
练习:通过数据库的函数随机生成一个六位数的验证码:
select lpad(round(rand()*100000,0),6,0);
三,日期函数
练习:查询所有员工的入职天数,并根据入职天数倒序排序。
select name, datediff(curdate(), entrydate) as ' entrydays' from emp order by entrydays desc;
四,流程函数
流程函数也是很常用的一类函数,可以在SQL语句中实现条件筛选,从而提高语句的效率。
练习:查iemp表的员工姓名和工作地址(北京/上海---->一线城市,其他---->- 二线城市)
select name
( case workaddress when '北京' then '一线城市' when '上海' then '一线城市' else 二线城市' end ) as '工作地址' from emp;
五,练习
create table score(
id int comment 'ID',
name varchar(20) comment '姓名',
math int comment '数学',
english int comment '英语',
chinese int comment '语文'
)comment '学员成绩表';
insert into score(id,name,math,english,chinese)VALUES(1,'Tom',67,88,95),
(2,'Rose',23,66,90),(3,'jack',56,98,76);
统计班级各个学员的成绩,展示的规则如下:
>=85,展示优秀
>= 60,展示及格
否则,展示不及格
from score;
select
id,
name ,
(case when math >= 85 then '优秀' when math >=60 then ' 及格'
else '不及格' end ) ' 数学',
(case when english >= 85 then '优秀' when english >=60 then ' 及格' else '不及格' end ) ' 英语',
(case when chinese >= 85 then '优秀' when chinese >=60 then ' 及格' else ' 不及格' end ) '语文'
from score;
二,约束
1.分类:
练习
create table user(
id int primary key auto_ increment comment '主键'
name varchar(10) not null unique comment '姓名' ,
age int check ( age > 0 && age <= 120 ) comment '年龄'
status char(1) default '1' comment '状态 ',
gender char(1) comment ' 性别'
) comment '用户表';
2.外键约束
(1)概念:外键用来让两张表的数据之间建立连接,从而保证数据的一致性和完整性。
(2)添加外键语法:
CREATE TABLE 表名(
字段名 数据类型,
...
[CONSTRAINT] [外键名称] FOREIGN KEY (外键字段名) REFERENCES 主表(主表列名)
);
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN(外键字段名)REFERENCES 主表(主表列名);
例:alter table emp add constraint fk_ emp_ dept_ id foreign key (dept_ id) references dept(id);
(3)删除外键语法:
ALTER TABLE 表名DROP FOREIGN KEY(外键名称);
例:alter table emp drop foreign key fk_ emp_ dept_ id;
3.外键的删除和更新行为:
行为NO ACTION 当在父表中删除/更新对应记录时,首先检查该记录是否有对应外键,如果有则不允许删除/更新。(与 RESTRICT一致)RESTRICT当在父表中删除/更新对应记录时,首先检查该记录是否有对应外键,如果有则不允许删除/更新。(与 NO ACTION - -致)CASCADE当在父表中删除/更新对应记录时,首先检查该记录是否有对应外键,如果有,则也删除/更新外键在子表中的记录。SET NULL当在父表中删除对应记录时,首先检查该记录是否有对应外键,如果有则设置子表中该外键值为null (这就要求该外键允许取null)。SET DEFAULT父表有变更时,子表将外键列设置成一个默认的值(Innodb不支持)
操作方法:
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FORIGN KEY (外键字段) REFERENCES 主表名(主表字段名) ON UPDATE CASCADE ON DELETE CASCADE;