数据分析学习—MySQL常用函数与约束的语法及案例

数据分析小歪

已于 2024-08-20 22:33:53 修改

阅读量322

点赞数 20

文章标签： mysql android 数据库

于 2024-08-20 22:30:22 首次发布

本文链接：https://blog.csdn.net/weixin_74355754/article/details/141330293

版权

函数与约束

chap1 函数

1 .1字符串函数

MySQL中内置了很多字符串函数，常用的几个如下：

在这里插入图片描述

语法：seletc 函数()

1.2数值函数

常见的数值函数如下：

外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传

1.3日期函数

常见的日期函数如下：

外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传

示例：

#date_add 年月日
select date_add(now(), interval 70 day );
select date_add(now(), interval 70 month );
select date_add(now(), interval 70 year );

#datediff
select  datediff('2024-05-13','2024-08-15');

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

案例结果展示：

外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传

1.4流程函数

流程函数也是很常用的一类函数，可以在SQL语句中实现条件筛选，从而提高语句的效率。

外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传

-- case when then else end
-- 案例1需求：查询emp表的员工姓名和工作地址（北京/上海--->一线城市, 其他 ----> 二线城市）
SELECT
    employee.name,
    CASE
        WHEN employee.address LIKE '%北京%' THEN '一线城市'
        WHEN employee.address LIKE '%上海%' THEN '一线城市'
        ELSE '二线城市'
    END AS city_type
FROM
    employee;

-- --------------------------------分割线------------------------------------------------------
-- 案例2：统计班级各个学员的成绩，展示的规则如下：
-- >=85, 优秀
-- >=60, 合格
-- 否则, 不合格

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, '张三', 90, 85, 88),
(2, '李四', 88, 92, 86),
(3, '王五', 75, 80, 82),
(4, '赵六', 95, 90, 92),
(5, '孙七', 68, 72, 75),
(6, '周八', 82, 88, 85),
(7, '吴九', 98, 95, 97),
(8, '郑十', 78, 85, 80),
(9, '钱十一', 65, 70, 72),
(10, '陈十二', 87, 91, 89);

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

案例1结果展示：

外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传

案例2结果展示：
在这里插入图片描述

chap2 约束

2.1概述

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

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

3.约束：

在这里插入图片描述

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

2.2约束演示

案例：根据需求，完成表结构的创建

在这里插入图片描述

示例：

CREATE TABLE user (
    id INT AUTO_INCREMENT PRIMARY KEY COMMENT 'ID主键',
    name VARCHAR(10) NOT NULL UNIQUE COMMENT '姓名',
    age INT CHECK (age > 0 AND age <= 120) COMMENT '年龄',
    status CHAR(1) DEFAULT '1' COMMENT '状态',
    gender CHAR(1) COMMENT '性别'
) COMMENT='用户表';

在这里插入图片描述

2.3外键约束

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

在这里插入图片描述

其中，dept_id就是员工表的外键，其关联着部门表的主键。具有外键的表就是子表，关联的表就是父表。

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

为了避免注意当中提到的问题，所以我们需要进行外键关联

语法：
- 添加外键
  ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY(外键字段名) REFERENCES主表（主表列名）：
- 删除外键
  ALTER TABLE 表名 DROP FOREIGN KEY 外键名称

示例：

-- ---------------------------------------约束（外键）-----------------------------------
-- 准备数据
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 primary key comment 'ID',
    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);

-- 添加外键
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;

关联成功后删除父表的数据就会出现以下红色报错语段：ava.sql.SQLException: Cannot delete or update a parent row: a foreign key constraint fails

在这里插入图片描述

解除关联关系后，则可以删除父表的数据：

在这里插入图片描述

2.4 外键删除更新行为

删除更新行为

其中 NO ACTION 和 RESTRICT 为默认行为。
语法ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段) REFERENCES 主表名（主表字段名）ON UPDATE CASCADE(或者其他行为) ON DELETE CASCADE或者其他行为) 。

示例：

alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id);
alter table emp add constraint `fk_emp_dept_id` foreign key (dept_id) references dept(id) ON UPDATE  set null  on DELETE  set null;