(三)、函数 - (非聚合函数)
函数: 是指一段可以直接被另一段程序调用的程序或代码。
1. 字符串函数
(1).基础知识
concat(s1,s2...sn),将s1 s2 字符串拼接成新的字符串。- lower(str), 将字符串str全部转化为小写。
- upper(str), 将字符串str全部转化为大写。
lpad(str,n,pad), 用字符串pad对str左边进行填充，直到n个字符的长度。- rpad(str,n,pad), 右填充，用字符串pad对str的右边进行填充，达到n个字符串长度。
- trim(str),去掉字符串头部和尾部的空格。
substring(str,start,len),返回从字符串str的start位置起的len个长度的字符串。第一位的位置是1。
(2).字符串语法
select 函数;
(3).示列
1. contact
SELECT concat('hello','mysql'); • 1
2. lower 和 upper
select lower('Hello')
select upper('Hello')
3. lpad 和 rpad
select LPAD('李明',10,'_')
4. substring
select SUBSTRING('abcd',1,2) • 1
5.由于业务需求变更，企业员工的工号，统一为5位数，目前不足5位数的全部在前面补0。比如: 1号员工的工号应该为00001
update emp set WORKNO = LPAD(WORKNO,6,'0') • 1
2.数值函数
(1).基础知识
常见的数值函数如下:
- ceil(x), 向上取整。
- floor(x),向下取整。
- mod(x,y),返回x/y的模，也就是余数。
- rand(),返回0~1内的随机数。
- round(x,y),求参数x的四社五入的值，保留y位小数。
(2).示列
通过数据库函数，生成一个随机六位数的密码。
SELECT LPAD(substring(RAND()*1000000,1,6),6,'0') • 1
3.日期函数
(1).基本知识
- curdate(),返回当前日期。
- curtime(),返回当前时间。
now(),返回当前日期和时间。year(date),获取指定date的年份。month(date),获取指定date的月份。day(date),获取指定date的日期。date_add(date,interval expr type)。返回一个日期/时间值间隔expr后的值。datediff(date1(减数),date2(被减数)),返回起始时间date1和结束时间date之间的天数。
(2).示列
1.返回当前时间
now()
2.返回指定间隔后时间是多少
select date_add(now(), interval 70 day)
3.查询入职时间为多少天
select datediff( '2023-11-08 18:45:17',now()) • 1
4.查询所有员工的入职天数，并根据入职天数倒叙排序
SELECT `NAME`,DATEDIFF(NOW(),ENTRYDATE) FROM emp ORDER BY DATEDIFF(NOW(),ENTRYDATE) DESC • 1
4.流程函数
流程函数也是很常用的一类函数，可以在SQL语句中实现条件筛选，从而提高语句的效率。
(1).基本知识
1.if(value,t,f),如果value为true,则返回t,否则返回f。
2. ifnull(value1,value2),如果value1不为空，返回value1,否则返回value2。
3. case when [val1] then [res1] ... else [default] end, 如果val1为true,返回res1, …否则返回default默认值。
4. case [expr] when [val1] then [res1] ... else [default] en,如果expr的值等于val1,返回res1,否则返回default默认值。
(2).示列
1.查询emp表的员工姓名和工作地址(北京/上海 --->展示为一线城市,其他的为二线城市) 使用4
SELECT `NAME`,WORKADDRESS,( CASE WORKADDRESS ⭐ WHEN '北京' THEN '一线城市' WHEN '上海' THEN '一线城市' ELSE '二线城市' END ) FROM emp;
2.统计班级各个学员的成绩，展示的规则如下: >=85优秀，>=60及格,否则不及格。 使用3
SELECT `NAME`,AGE,CASE ⭐ WHEN AGE>=70 THEN '优秀' WHEN AGE>=60 AND AGE<70 THEN '及格' ELSE '不及格' END FROM emp;
# 也可以设置多条流程函数用于求单科的优秀还是不优秀。⭐ SELECT `NAME`,AGE, (CASE WHEN AGE>=70 THEN '优秀' WHEN AGE>=60 AND AGE<70 THEN '及格' ELSE '不及格'END), (CASE WHEN AGE>=70 THEN '优秀' WHEN AGE>=60 AND AGE<70 THEN '及格' ELSE '不及格'END) FROM emp;
注意
- when 和 then 可以写多个代表着 或 的意思。
(四)、约束
1.约束概述
(1).约束的概念
概念: 约束时作用于表中字段上的规则，用于限制存储在表中的数据。
(2).约束的目的
保证数据库中数据的正确、有效性和完整性。
(3).约束的分类
- 非空约束(Not Null) 限制字段的数据不能为null
- 唯一约束(unique) 保证该字段的所有数据都是唯一、不重复
- 主键约束(primary key) 主键是一行数据的唯一标识，要求非空且唯一
- 默认约束(Default) 保存数据时，如未指定字段则采用默认值
- 检查约束(check_8.0.16版本支持) 保证字段值满足某一个条件
- 外键约束(foreign key) 用来让两张表之间建立联系，保证数据的一致性和完整性。
注意: 约束是作用于表中字段上的，可以创建表/修改表的时候添加约束。
2.约束演示
# 会报错，因为的check约束是8.0.16版本以后才支持的，我们的数据库6.0.13版本的，所以会报错 create table user( id int PRIMARY key auto_increment comment '主键', name varchar(20) not null unique comment '姓名', age int check(age>0 && age<=120) comment '年龄', # 年龄在0~120岁之间 statu char(1) default '1' comment '状态', gender char(1) comment '性别' );
3.外键约束
(1).外键约束的概念
外键用来让两张表的数据之间建立连接，从而保证数据的一致性和完整性。
(2).SQL语句
创建一个新的数据库: 数据库名字叫做itheima
create table dept( id int primary key auto_increment, name varchar(10) ); insert into dept values (null, '研发部'), (null, '市场部'), (null, '财务部'), (null, '销售部'), (null, '总经办'), (null, '人事部'); create table emp( id int primary key auto_increment, name varchar(10), age int, job varchar(10), salary int, entrydate date, managerid int, dept_id int, constraint fk_dept foreign key (dept_id) references dept(id) ); ⭐ insert into emp values (null, '金庸', 66, '总裁', 20000, '2000-01-01', null, 5), (null, '张无忌', 20, '项目经理', 12500, '2005-12-05', 1, 1), (null, '杨逍', 33, '开发', 8400, '2000-11-03', 2, 1), (null, '韦一笑', 48, '开发', 11000, '2002-02-05', 2, 1), (null, '常遇春', 43, '开发', 10500, '2004-09-07', 3, 1), (null, '小昭', 19, '程序员鼓励师', 6600, '2004-10-12', 2, 1), (null, '灭绝', 60, '财务总监', 8500, '2002-09-12', 1, 3), (null, '周芷若', 19, '会计', 4800, '2006-06-02', 7, 3), (null, '丁敏君', 23, '出纳', 5250, '2009-05-13', 7, 3), (null, '赵敏', 20, '市场部总监', 12500, '2004-10-12', 1, 2), (null, '鹿杖客', 56, '职员', 3750, '2006-10-03', 10, 2), (null, '鹤笔翁', 19, '职员', 3750, '2007-05-09', 10, 2), (null, '方东白', 19, '职员', 5500, '2009-02-12', 10, 2), (null, '张三丰', 88, '销售总监', 14000, '2004-10-12', 1, 4), (null, '俞莲舟', 38, '销售', 4600, '2004-10-12', 14, 4), (null, '宋远桥', 40, '销售', 4600, '2004-10-12', 14, 4), (null, '陈友谅', 42, null, 2000, '2011-10-12', 1, null); create table salgrade( grade int, losal int, hisal int ); insert into salgrade values (1, 0, 3000), (2, 3001, 5000), (3, 5001, 8000), (4, 8001, 10000), (5, 10001, 15000), (6, 15001, 20000), (7, 20001, 25000), (8, 25001, 30000);
(3).外键约束的语法
具有外键语法的表称为从表、不具有外键语法的表成为主表。
- 语法
第一种添加外键- 创建表的时候
create table 表名( 字段名 数据类型, ... [constraint] [外键名称] foreign key(外键字段名) references 主表(主表列名) )
第二种添加外键- 创建表之后
alter table 从表表名 add constraint 外键名称 foreign key(外键字段名) references 主表(主表列名)
删除外键
alter table 从表表名 drop foreign key 外键名称。
(4).有外键约束与外键约束情况
- 无外键的情况下
假如两张表数据是相互联系作用的，在没有绑定主外键的情况下，一张表数据的删除将不会影响到另一张表的删除。从而出现了数据不同步的情况。
- 有外键的情况下
我们尝试删除主表中的数据，我们发现提示我们删除不了这个字段，因为在从表中有行使用我们这个字段。
4.外键删除更新行为
(1).删除/更新行为
- not action : 在主表中删除/更新对应记录时，首先
检查该记录是否对应外键，如果有则不允许删除/更新。 - restrict : 当在主表中删除/更新记录时，首先
检查该记录是否有对应外键，如果有则不允许删除/更新。 - cascade(级联) : 当在主表中删除/更新对应记录时，首先
检查记录是否对应外键，如果有，则也删除/更新外键在子表中的记录。 - set null : 当在主表中删除对应记录时，首先
检查该记录是否有对应外键，如果有则设置子表中该外键值为null(这就要求前提是外键允许null) - set deffault 主表有变更时，子表将外键列设置成一个默认的值(
Innodb不支持)
注意: MySQL默认支持 no action 和 restrict 这两种行为机制。
(2).修改删除/更新行为语法
alter table 从表表名 add constraint 外键名称 foreign key (外键字段) references 主表名(主表字段名) on update xxxx on delete xxxx;
(3).级联删除/更新测试
# 先删除外键 alter table emp drop foreign key fk_dept; # 再添加外键 alter table emp add constraint fk_dept foreign key (dept_id) references dept(id) on update CASCADE on delete cascade;
- 我们将主表的编码为2 更改为6.根据级联的特性，所以从表的字段内容也随着改变。
(4). set null 删除/更新测试
# 先删除外键 alter table emp drop foreign key fk_dept; # 再添加外键 alter table emp add constraint fk_dept foreign key (dept_id) references dept(id) on update set NULL on delete set null;
1.我们将我们的主键设置成6更改成8，发现从表数据修改为null
