mysql入门(二)——函数与约束

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
云数据库 RDS MySQL,高可用系列 2核4GB
简介: mysql入门(二)——函数与约束

mysql中的函数

之前我们介绍过聚合函数,在MySQL中,聚合函数是用于对多行数据进行计算和统计的函数。它们将多行数据作为输入,并返回单个值作为输出。聚合函数可以对数据进行汇总、计数、求和、平均值、最大值、最小值等操作,用于生成有关数据集的摘要信息。

函数: 是指一段可以最直接被另一段程序调用的程序或代码,在mysql中早已经在内部写好了

字符串函数

我们这里介绍常用的几种字符串函数

image.png

例子:

1updata 表名 set 字段 = 函数();//使用函数更新emp表中的字段

由于业务需求变更,企业员工的工号统一号为5位数,目前不足5位数的全部在前面补0.比如:1号员工的工号应该为00001;

1update emp set workno = lpad (workno,5,'0');


字符串函数的基本应用

数据清洗和格式化:字符串函数可用于去除多余的空格、修剪字符串、删除特定字符,以及格式化字符串的大小写等。例如,使用函数如TRIM、UPPER、LOWER和REPLACE可以清理和规范化输入数据。

字符串拼接和连接:使用函数如CONCAT和CONCAT_WS可以将多个字符串连接成一个字符串,常用于生成完整的文本内容或动态生成SQL查询语句。

字符串分割和提取:函数如SUBSTRING、LEFT、RIGHT和MID用于提取字符串的子串,常用于从文本中提取特定的信息或截取部分字符串。

字符串比较和匹配:使用函数如LIKE、INSTR和STRCMP可以进行字符串的模糊匹配、查找特定子串,或者比较字符串的大小或顺序。

字符串长度和计数:函数如LENGTH和CHAR_LENGTH用于计算字符串的长度,常用于验证输入长度或限

向左填充5位数,不足5位数向前补0;


数值函数

image.png

案例:通过数据库的·函数,生成一个六位数的随机验证码。先使用rand乘以1000000 再进行取整;


1 select rand()*1000000;

因为rand是生成0-1直接的随机数,所以可能会出现0。016563这种数字,导致乘之后不满足6位数的要求,这时候我们可以对其进行补零;

1select lpad(round(rand()*1000000,0),6,‘0’);


日期函数

日期函数在sql语句中实现对日期类型的查询计算

image.png

流程函数

流程控制函数在sql中实现条件筛选,从而提高语句的效率

image.png

注意:ifnull中的条件是如果为null值,并非是为空串,如果是空串,也会返回空串, 其中第二个函数常用于范围判断,返回true或false的情况,第四个常用于固定当某值等于的条件的时候返回


举例: 查询emp员工表中的员工姓名和工作地址(如果是北京上海就返回一线城市,如果是其他则返回二线城市)


1 select  name  case workaddress when '北京' then '一线城市' when '上海' then '一线城市'  else '二线城市' end from emp;


案例:统计函数各个学院的成绩,展示的规则如下:

如果成绩>=80;展示优秀

如果成绩>=60;展示及格

否则;展示不及格


原表格:

image.png

select id,name,
    (case when english <=80 then '及格'when english <= 60 then '及格' else '不及格' end) as '英语成绩'from student;

image.png


约束

约束就是限制,束缚,它是用来限制表结构中的数据的,是用于定义和强制表中数据的完整性和规则的规范。它们用于确保表中的数据满足特定的条件,以维护数据的一致性和有效性。


概述

概念: 是用于作用表中字段的规则,用于限制储存在表中的结构。

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

分类:

image.png

唯一约束:比如用户创建的用户名,身份证我们就可以加上唯一约束。在很多规范中要求要有主键约束;默认约束:假设我们现在有个状态值,未设置就为1,这时候就可以使用默认约束。一旦出现外键约束,就标志着至少有两张表。对于一个字段可以添加多个约束条件,多个约束使用空格进行隔开。检查约束中有多个条件的时候可以使用&&进行连接。主键可以不给,会进行自动增长。


约束的演示

案例:更具需求,完成表结构的创建

(自动增长:在mysql数据库中:auto_increment)

image.png

create table  user(
    id int primary key auto_increment,
    name varchar(10) not null unique ,
    age int check ( age>0&&age<=120 ),
    status char(1) default '1',
    gender char(1) 
)

外键约束

外键约束是让两张表来建立数据连接的,从而保证数据的一致性和完整性。

image.png我们注意观察员工表中的dep_id和部门表中的id(主键),我们就说dep_id是部门表的外键,通过外键进行将两张表连接起来。我们一般叫部门表是主表(父表),员工表是从表(子表)注意:以上的两张表,在数据库上没有建立外键关联, 所以无法保证数据的一致性和完整性,即我在改变一个表中的数据时候,另外一张表中的数据并不会跟着改变。

image.png

我们观察以上两张表,他们是通过id进行关联的,所以我们可以通过为两张表依靠id进行相连,创建外键约束。

建立外键关联:

语法:

1.在 创建表结构的时候就进行添加

create table 表名(
  字段名 数据类型
  ...
  ...
  [constraint][外键名称] foreign key (外键字段名) references 主表(主表列名)

2.表创建好之后进行关联

alter table 表名 add constraint 外键名称 foreign key (外键字段名) references 主表(主表列名);

我们现在给上边两个表添加外键约束

alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept (id);  

添加了外键之后,我们就可以保证数据的完整性,所以我们在删除数据的时候,他会进行判断,父表中的数据是否与字表中的数据存在关联情况,如果关联则不能删除,很容易理解,比如我删除了父表,那字表中的那么多数据怎么办,现在两张表已经建立了链接。

删除外键

alter table 表名 drop foreign key 外键名称;

外键的删除/更新行为

image.png

注意:后两个是默认情况

语法:

alter table 表名 add constraint 外键名称 foreign key (外键字段名) references 主表(主表列名)  on update cascade on delete cascade;

就是在上边添加外键的语句后边加上on update与on delete ,代表在更新和删除的时候进行的操作。


为什么要采取外键约束

建立表之间的关系: 当存在多个表之间的关联关系时,使用外键约束可以确保关联的完整性。例如,一个订单表和一个顾客表,可以使用外键约束将订单表中的顾客ID列与顾客表中的主键关联起来,以确保订单表中的顾客ID值必须存在于顾客表中。


维护引用完整性: 外键约束可以确保在插入或更新数据时,引用关系的完整性得到维护。它可以防止在关联表中引用不存在的数据,从而保持数据的一致性。


防止意外删除或修改关联数据: 外键约束可以防止意外删除或修改关联表中的数据。当一个表有外键约束关联到其他表时,如果尝试删除或修改被关联的数据,则会触发外键约束,阻止操作或根据定义的操作行为执行相应的操作。


简化查询和维护操作: 外键约束可以简化查询和维护操作。通过外键约束,可以轻松地执行联接查询、级联删除和更新等操作,而不需要手动处理数据的引用关系。


提高数据完整性和一致性: 外键约束有助于提高数据的完整性和一致性。它可以强制执行数据的关联关系和依赖性,防止不一致或不合法的数据插入或更新。



image.pngimage.pngimage.png

相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
2月前
|
JSON 关系型数据库 MySQL
Mysql(5)—函数
MySQL提供了许多内置的函数以帮助用户进行数据操作和分析。这些函数可以分为几类,包括聚合函数、字符串函数、数值函数、日期和时间函数、控制流函数等。
80 1
Mysql(5)—函数
|
1月前
|
NoSQL 关系型数据库 MySQL
2024Mysql And Redis基础与进阶操作系列(4-2)作者——LJS[含MySQL非空、唯一性、PRIMARY KEY、自增列/自增约束举例说明等详解步骤及常见报错问题对应的解决方法]
24MySQL非空、唯一性、PRIMARY KEY、自增列/自增约束举例说明等详解步骤及常见报错问题对应的解决方法(4-2) 学不会你来砍我!!!
|
3月前
|
关系型数据库 MySQL 数据库
MySQL基本操作入门指南
MySQL基本操作入门指南
100 0
|
1月前
|
关系型数据库 MySQL Serverless
MySQL函数
最常用的MySQL函数,包括聚合函数,字符串函数,日期时间函数,控制流函数等
35 1
|
2月前
|
存储 SQL 关系型数据库
MySQL 存储函数及调用
MySQL 存储函数及调用
130 3
|
2月前
|
Ubuntu 关系型数据库 MySQL
ubuntu使用aliyun源+mysql删除有外键约束的数据+查看特定目录的大小
ubuntu使用aliyun源+mysql删除有外键约束的数据+查看特定目录的大小
38 4
|
1月前
|
SQL NoSQL 关系型数据库
|
2月前
|
缓存 关系型数据库 MySQL
MySQL 满足条件函数中使用查询最大值函数
MySQL 满足条件函数中使用查询最大值函数
138 1
|
2月前
|
关系型数据库 MySQL 数据库
mysql中tonumber函数使用要注意什么
在处理这类转换操作时,考虑周全,利用提供的高性能云服务器资源,可以进一步提升数据库处理效率,确保数据操作的稳定性和安全性,尤其是在处理大量数据转换和运算密集型应用时。
105 0
|
2月前
|
关系型数据库 MySQL 数据处理
企业级应用 mysql 日期函数变量,干货已整理
本文详细介绍了如何在MySQL8.0中使用DATE_FORMAT函数进行日期格式的转换,包括当日、昨日及不同时间段的数据获取,并提供了实际的ETL应用场景和注意事项,有助于提升数据处理的灵活性和一致性。
43 0