SQL:函数以及约束

目录

介绍

函数

字符串函数

数值函数

日期函数

流程函数

约束

总结

---

介绍

说到函数我们都不陌生,在C,C++,java等语言中都有库函数,我们在平时也是经常使用,函数就是一段代码,我们既可以自定义实现,又可以使用库里内置的函数;从来更加简洁方便的完成业务;同样的在SQL中也有一些内置函数来供我们使用;

函数

函数:一段可以直接别另一段程序调用的程序或代码;
注意:要看到函数的结果要使用select +函数;

字符串函数

1>字符串拼接(concat)

使用语句

select concat("hello ","mysql");

结果: 

2>转换为小写(lower)

将字符串中的大写字符转换为小写,原本是小写的不变;
使用语句:

select lower("HellO");

结果: 

3>转换为大写(upper)

使用语句:

select upper("hello");

结果: 

4>使用自定义字符左填充(lpad)

这个函数的参数有三个;分别是填充前的字符串,填充后字符串的长度,填充字符;
注意这里的填充字符可以是一个单个字符,也可以是字符串;

如果是单个字符
使用语句:

select lpad("1",5,'0');

结果:

如果是字符串

使用语句:

select lpad("2",6,'01');

这里我设置的长度为6,只有5个位置可以填充,所以这里是01依次填充,直到填完;
结果:

5>使用自定义字符右填充(rpad)

这里需要注意的是有填充是从最右端依次依次向左填充的:
eg:使用语句:

select rpad("2",6,'01');

 结果是201010;

6>去头去尾的空格(trim)

使用语句:

select trim("  Hello , mysql");

结果: 把头部和尾部的空格去掉了;

7>返回指定字符串长度部分(substring)

使用语句:

select substring("abcdefg",1,5);

结果:"abcde";

注意如果没有写len长度的话,就会取起始位置到字符串的末尾;

数值函数

对x向下取整:取小于x的最大整数;
对x向上取整:取大于x的最小整数;
x模y:x/y的余数;

问题:我们如何返回0~10^x之间整数的随机数呢?

我们可以先使用rand生成一个0~1之间的小数,然后乘上10^x,这时候随机数的整数部分是有x位,但是小数部分还需要处理,我们就可以使用round来保留0位小数;这样就得到了0~10^x之间的随机数整数了; 

日期函数

这里的日期是年-月-日,time是时-分-秒,now就是年-月-日-时-分-秒;

流程函数

第一个if(value,t,f)与C语言中的三元表达式(value!=0?t:f;)很相似;后面的根据需求了解即可;

约束

概念:约束是作用于表中字段上的限制,用于限制存储在表中的数据;
目的:保证表中数据的正确性,有效性和完整性; 

 格式:
1.在创建表时,直接写在字段的数据类型后面;
2.在已经创建好的表中的字段中添加约束需要使用关键字alter;具体后面会有;

现在创建一个用户表,并同时加上约束;(一个字段并不是只能有一个约束);

1>主键(primary)和自增(auto_increment)

设置主键后,该字段的数据就要保持完整性(不为空且唯一);这个很好理解,下面来看一下自增性;

现在我向表中插入三个数据,但是我并没有插入id(id的约束是主键自增),虽然我们没有写,但是因为有自增,所以id不为空,而且是唯一的;下面我们来看看表;
自增的作用就是会自动的根据序列递增式的填充;

注意:如果我们插入失败了一个行数据,虽然没有插入成功,但是已经向数据库申请到了空间,下次插入的时候自增约束的字段并不会按照表格中现有的数据挨着填充;
比如:上表中id已经自动填充到3了,如果我插入一行数据失败了,然后我再插入一行数据成功了,那么新插入的一行数据的id就是5,而不是4;

 2>检查(check)

这个比较简单,只需要注意格式就可以了,格式与where的格式相同;

3>外键(foreign key (从表字段) reference (主表)(主表字段))

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

以这两张表为例,每个员工都有一个所属的部门,部门都有一个id序号,而我们在填员工的部门id时必须得按照部门表中的id来填,也就是员工表中的dept_id的范围约束在了部门表的id取值中,这样就可以保证每个员工的所属部门都是存在的;

现在创建员工表:

 
在没有使用外键之前,尽管两张表在逻辑上有联系,但是在物理上是没有关系的,也就是说我更改部门表中的id员工表没有任何反应,我在员工表中填一个不存在的一个部门id也不会报错;

1>添加外键

使用语句:

 

外键添加后会在字段前出现一个蓝色的小钥匙标志 ;

2>检验是否两个表建立了联系

现在我删除掉部门表中的id为1的部门,在员工表中有好多行的员工在该部门,删除后如果报错了说明两个表之间建立了联系,如果没有报错说明两个表之阿金仍然没有建立物理上的联系;
我删除执行会出现一条报错语句,说明外键发挥了作用;不可以删除或更新主表数据;因为这样会影响到从表的很多数据都需要改变; 下面我们来说说外键的删除和更新行为;

3>删除和更新行为

1>CASCADE(同步)

如果我把部门表中id为1的数据更改为6,那么员工表中的原来dept_id是1的也会变成6;
如果我如果把部门表中id为1的数据删除,那么员工表中的原来dept_id是1的那一行的数据都会被删除;

2>SET NULL 

如果我把部门表中id为1的数据更改为6或者删除,那么员工表中的原来dept_id是1就会全部变成null;

总结

非空约束:NOT NULL
唯一约束:UNIQUE
主键约束:PRIMARY(自增:AUTO_INCREMENT)
默认约束:DEFAULT
检查约束:CHECK
外键约束:FOREIGN KEY

其中最重要的是主键和外键,他们与其他的使用格式不同;要求更严格;
外键的使用使得表与表之间不再是独立的,为以后的多表操作奠定了基础;