MySQL笔记-函数,约束

本文标签 : 数据库函数 约束

一、函数

1.字符串函数.

** 实现:**

                            -- 函数演示 --
    -- 语法: select 函数(参数);

-- 1.字符串函数
-- concat(拼接字符串)
select concat('hello','MySQL');

-- lower(转换为小写字母)
select  lower('Hello');

-- upper(转换为大写字母)
select upper('hello');

-- lpad(左边补齐)
select lpad('01',5,'-');

-- rpad(右边补齐)
select rpad('01',5,'-');

-- trim(去除头部和尾部空格,中间空格还在)
select trim(' hello MySQL ');

-- substring(返回当前字符串的前len个字符)
select substring('hello MySQL',1,5);

练习:

-- 1.企业员工的工号,统一为5位数, 目前不足5位数的全部在前面补0.比如: 1号员工的工号应该是00001.
update emp set workno = lpad(workno,5,'0');

2.数值函数.

** 实现:**

-- 2.数值函数
-- ceil (向上取整)
select ceil(1.5);

-- floor(向下取整)
select floor(1.1);

-- mod(求模 7%4)
select mod(3,4);

-- rand(生成 0~1 之间的随机数)
select rand();

-- roune(四舍五入,保留几位小数)
select round(2.345,2);

-- 需求 : 通过数据库的函数,生成一个六位数的随机验证码.(0.01*1000000=10000,所以再加个 lpad 函数,不足 6 位补 0)
select lpad(round(rand()*1000000 , 0),6,'0');

3.日期函数.

** 实现:**

-- 3.日期函数
-- curdate(当前日期)
select curdate();

-- curtime(当前时间)
select curtime();

-- now(当前日期和时间)
select now();

-- year , month , day
select YEAR(now()); -- 当前日期 now ,所属年份 year

select MONTH(now()); -- 当前日期 now ,所属月份 month

select DAY(now()); -- 当前日期 now ,所属日期 day

-- date_add(返回一个日期/时间,加上一个时间间隔后的时间值)
select date_add(now(),INTERVAL 70 DAY );   -- now 当前时间 (INTERVAL 固定写法) ,加上 70day 后的时间/日期.
select date_add(now(),INTERVAL 70 MONTH );
select date_add(now(),INTERVAL 70 YEAR );

-- datediff(两个指定时间之间相差的天数),第一个时间 - 第二个时间
select datediff('2023-4-17','2022-4-17');

-- 需求 : 查询所有员工的入职天数,并根据入职天数倒序排序.
select name,datediff(curdate(),entrydate) as 'entrydays' from emp order by entrydays desc;
-- 时间相减函数datediff( 当前时间函数curdate - 列表名 entrydate) desc降序

**结果: **

4.流程函数

** 实现:**

-- 4.流程函数
-- if(判断是否为 true ,为 true 返回 ok ,不是返回 Error)
select if(true,'ok','Error');

-- ifnull(判断非空,非空返回 ok ,空则返回后面的 default)
select ifnull('ok','default');

select ifnull('','default');  -- 字符串 空,返回空字符串

select ifnull(null ,'default');  -- 返回 null

-- case when then else end
-- 需求:查询 emp 表的员工姓名合工作地址(如果是北京或者上海返回 ---> 一线城市 ,其他返回 ---> 二线城市 )
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,展示及格,
-- 其他,展示不及格.

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

**结果: **

二、约束

1.概述

1. 概念:约束时作用域表中字段上的规则,用于限制存储在表中的数据.

2. 目的:保证数据库中数据的正确,有效性和完整性.

3. 分类:

** 注意 : 约束是作用表中字段上的,可以在创建表/求该表的时候添加约束.**

2.约束演示

**按照需求 完成下列表结构的创建: **

** 实现:**

-- 创建用户表
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 '状态',
    genter char(1) comment '性别'
) comment '用户表';

3.外键约束

** 1.概念 :**

** 外键是用来让两张表的数据之间建立联系,从而保证数据的一致性和完整性. **

** 2. 实现:**

** 实现如下两张关系表的主键和外键约束:**

注意 : 目前上述的两张表,在数据库层面,并未建立连接,所以是无法保证数据的一致性和完整性的.

实现:

-- ---------------------------------- 约束 (外键) --------------------------

-- 准备数据

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 (id, name, age, job,salary, entrydate, managerid, dept_id) VALUES
            (1, '金庸', 66, '总裁',20000, '2000-01-01', null,5),(2, '张无忌', 20, '项目经理',12500, '2005-12-05', 1,1),
            (3, '杨逍', 33, '开发', 8400,'2000-11-03', 2,1),(4, '韦一笑', 48, '开发',11000, '2002-02-05', 2,1),
            (5, '常遇春', 43, '开发',10500, '2004-09-07', 3,1),(6, '小昭', 19, '程序员鼓励师',6600, '2004-10-12', 2,1);

** 添加外键:**

-- 语法:

-- 添加外键:

-- 法1.直接在创建表时添加外键约束
create table 表名(
        字段名 数据类型 , 
        ... ...
        [constraint] [外键名称] foreign key(外键字段名) refences 主表 (主表列名)
);

-- 法2.创建表后添加约束
  alter table 表名 add constraint 外键名称 foreign key(外键字段名) references 主表(主表列名);

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

** 删除外键:**

-- 删除外键
-- 语法 : alter table 表名 drop foreign key 外键名称;

alter table emp drop foreign key dept_id;

3.删除/更新行为:

** no action 和 restrict 为默认行为, set default 基本用不到.**

删除/更新行为的语法及实现:

-- ---------------------------------- 删除/更新行为 -----------------------------

-- 语法 : alter table 表名 add constraint 外键名称 foreign key (外键字段) references 主表名 (主表字段名) on update cascade on DELETE cascade ;

-- 实现 :
-- cascade:

alter table emp add constraint dept_id foreign key (dept_id) references dept (id) on update cascade on delete cascade;

-- set null:

alter table emp add constraint dept_id foreign key (dept_id) references dept (id) on update set null on delete set null ;

三、总结

数据库函数,约束相关笔记.如有不足,还望指出!