字段约束与函数_字段约束1.1

---

数据类型

命名规则

1. 字段名必须以字母开头，尽量不要使用拼音
2. 长度不能超过30个字符（不同数据库，不同版本会有不同）
3. 不能使用SQL的保留字，如where,order,group
4. 只能使用如下字符az、AZ、0~9、$ 等
5. Oracle习惯全大写：USER_NAME，mysql习惯全小写：user_name
6. 多个单词用下划线隔开，而非java语言的驼峰规则

字符

1. char长度固定，不足使用空格填充，最多容纳2000个字符，char(11)存储abc，占11位。查询速度极快但浪费空间
2. varchar变长字符串，最多容纳4000个字符，varchar(11)存储abc，只占3位。查询稍慢，但节省空间。Oracle为varchar2
3. 大文本: 大量文字（不推荐使用，尽量使用varchar替代） 以utf8编码计算的话，一个汉字在u8下占3个字节
   注：不同数据库版本长度限制可能会有不同

数字

1. tinyint,int整数类型
2. float,double小数类型
3. numeric(5,2) decimal(5,2)——也可以表示小数,表示总共5位,其中可以有两位小数
4. decimal和numeric表示精确的整数数字

日期

1. date 包含 年 月 日
2. time 时 分 秒
3. datetime 包含 年 月 日 和 时 分 秒
4. timestamp时间戳，不是日期，而是从1970年1月1日到指定日期的毫秒数

图片

1. blob 二进制数据，可以存放图片、声音，容量4g。早期有这样的设计。但其缺点非常明显，数据库庞大，备份缓慢，这些内容去备份多份价值不大。同时数据库迁移时过大，迁移时间过久。所以目前主流都不会直接存储这样的数据，而只存储其访问路径，文件则存放在磁盘上。

1、字段约束

1.字段约束的目的是什么？

约束是数据库用来确保数据满足业务规则的手段，对数据做的条件限制。

2 字段约束的类型有几种？

1. 主键约束（PRIMARY KEY）

2. 唯一性约束（UNIQUE)

3. 非空约束（NOT NULL)

4. 检查约束（CHECK)

5. 外键约束（FOREIGN KEY)

这里我们只详细介绍在开发过程中常用到的主键约束、唯一约束以及非空约束。

1.1 主键约束

1. 主键约束：如果为一个列添加了主键约束，那么这个列就是主键，主键的特点是唯一且不能为空。通常情况下，每张表都会有主键。
2. 添加主键约束，例如将id设置为主键：

create table abc(
id int primary key auto_increment
);
insert into abc values(null);
insert into abc values(null);
insert into abc values(null);
select * from abc;

1
2
3
4
5
6
7
8

3. 主键自增策略：当主键为数值类型时，为了方便维护，可以设置主键自增策略（auto_increment），设置了主键自增策略后，数据库会在表中保存一个AUTO_INCREMENT变量值，初始值为1，当需要id值，不需要我们指定值，由数据库负责从AUTO_INCREMENT获取一个id值，作为主键值插入到表中。而且每次用完AUTO_INCREMENT值，都会自增1. AUTO_INCREMENT=1

1.2 非空约束

1. 非空约束：如果为一个列添加了非空约束，那么这个列的值就不能为空，但可以重复。

2. 添加非空约束，例如为password添加非空约束：

create table user(
id int primary key auto_increment,
password varchar(50) not null
);
show tables;
insert into user values(null,null);//不符合非空约束
insert into user values(null,123;);//OK

1
2
3
4
5
6
7
8

1.3 唯一约束

1. 唯一约束：如果为一个列添加了唯一约束，那么这个列的值就必须是唯一的（即不能重复），但可以为空。

2. 添加唯一约束，例如为username添加唯一约束及非空约束：

create table test(
id int primary key auto_increment,
username varchar(50) unique--唯一约束
);
show tables;
insert into test values(null,'lisi');
insert into test values(null,'lisi');--username的值要唯一,重复会报错的
select * from test;

1
2
3
4
5
6
7
8
9

1.4检查约束（CHECK、CK）

1. 检查约束指的是在数据列上设置一些过滤条件，当过滤条件满足的时候才可以进行保存，如果不满足则出现错误。
2. 语法格式：CHECK <表达式>
   <表达式>指的就是 SQL 表达式，用于指定需要检查的限定条件。
3. 若将 CHECK 约束子句置于表中某个列的定义之后，则这种约束也称为基于列的 CHECK 约束。
4. 在更新表数据的时候，系统会检查更新后的数据行是否满足 CHECK 约束中的限定条件。MySQL 可以使用简单的表达式来实现 CHECK 约束，也允许使用复杂的表达式作为限定条件，例如在限定条件中加入子查询。

注意：若将 CHECK 约束子句置于所有列的定义以及主键约束和外键定义之后，则这种约束也称为基于表的 CHECK约束。该约束可以同时对表中多个列设置限定条件。

5. 使用：
   创建表时设置检查约束的语法规则如下：
   CHECK(<检查约束>)
   【实例 1】在 test_db 数据库中创建 tb_emp7 数据表，要求 salary 字段值大于 0 且小于 10000，输入的 SQL 语句和运行结果如下所示。

   mysql> CREATE TABLE tb_emp7
   -> (
   -> id INT(11) PRIMARY KEY,
   -> name VARCHAR(25),
   -> deptId INT(11),
   -> salary FLOAT,
   -> CHECK(salary>0 AND salary<100),
   -> FOREIGN KEY(deptId) REFERENCES tb_dept1(id)
   -> );
   Query OK, 0 rows affected (0.37 sec)
   1
   2
   3
   4
   5
   6
   7
   8
   9
   10

   修改表时设置检查约束的语法规则如下：
   ALTER TABLE tb_emp7 ADD CONSTRAINT <检查约束名> CHECK(<检查约束>)
   【实例 2】修改 tb_dept 数据表，要求 id 字段值大于 0，输入的 SQL 语句和运行结果如下所示。

   mysql> ALTER TABLE tb_emp7
       -> ADD CONSTRAINT check_id
       -> CHECK(id>0);
   Query OK, 0 rows affected (0.19 sec)
   Records: 0  Duplicates: 0  Warnings: 0
   1
   2
   3
   4
   5

   修改表时删除检查约束的语法规则如下：

   ALTER TABLE <数据表名> DROP CONSTRAINT <检查约束名>;
   1

1.5 外键约束（FOREIGN KEY)

MySQL 外键约束（FOREIGN KEY）用来在两个表的数据之间建立链接，它可以是一列或者多列。一个表可以有一个或多个外键。

2、基础函数

lower

SELECT 'ABC',LOWER('ABC') from dept; --数据转小写
1

upper

select upper(dname) from dept --数据转大写
1

length

select length(dname) from dept --数据的长度
1

substr

SELECT dname,SUBSTR(dname,1,3) FROM dept; --截取[1,3]
1

concat

select dname,concat(dname,'123') X from dept --拼接数据
1

replace

select dname,replace(dname,'a','666') X from dept --把a字符替换成666
1

ifnull

select ifnull(comm,10) comm from dept2 #判断,如果comm是null,用10替换
1

round & ceil & floor

round四舍五入，ceil向上取整，floor向下取整

• 直接四舍五入取整

select comm,round(comm) from emp
1

• 四舍五入并保留一位小数

select comm,round(comm,1) from emp
1

• ceil向上取整,floor向下取整

select comm,ceil(comm) ,floor(comm) from emp
1

uuid

SELECT UUID()
返回结果uuid：a6b836ca-1573-11ec-915d-107b44337844
1
2

now

select now() -- 年与日 时分秒 
返回结果now()：2021-09-14 23:52:45

select curdate() --年与日
返回结果curdate()：2021-09-14
select curtime() --时分秒
返回结果curtime()：23:55:37

1
2
3
4
5
6
7
8

year & month & day

• hour()时 minute()分 second()秒

select now(),hour(now()),minute(now()),second(now()) from customer ;
1

• year()年 month()月 day()日

select now(),year(now()),month(now()),day(now()) from customer ;
1

转义字符

 ' 作为sql语句符号，内容中出现单撇就会乱套，进行转义即可

select 'ab'cd' -- 单引号是一个SQL语句的特殊字符

select 'ab\'cd' --数据中有单引号时,用一个\转义变成普通字符
1
2
3
4
5