文章目录
- 1.完整性约束:主键 (constraint)
- 2.完整性约束:外键 (constraint)
- 3.完整性约束:用户自定义完整性约束 (check)
1.完整性约束:主键 (constraint)
- 主键能够标识唯一一条记录，主键不能为空，也不能重复
mysql> create table t_emp(empno int primary key,ename varchar(20),esex char(2));
Query OK, 0 rows affected (0.20 sec)
mysql> show tables;
+-----------------+
| Tables_in_study |
+-----------------+
| t_emp |
+-----------------+
1 row in set (0.00 sec)
mysql> desc t_emp;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| empno | int | NO | PRI | NULL | |
| ename | varchar(20) | YES | | NULL | |
| esex | char(2) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> show create table t_emp;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t_emp | CREATE TABLE `t_emp` (
`empno` int NOT NULL,
`ename` varchar(20) DEFAULT NULL,
`esex` char(2) DEFAULT NULL,
PRIMARY KEY (`empno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> insert into t_emp(empno,ename,esex) values(10,'wangji','f');
Query OK, 1 row affected (0.03 sec)
mysql> insert into t_emp(empno,ename,esex) values(10,'wangji','m');
ERROR 1062 (23000): Duplicate entry '10' for key 't_emp.PRIMARY'
mysql> insert into t_emp values(11,'shenlulu','m');
Query OK, 1 row affected (0.04 sec)
mysql> insert into t_emp values(null,'lili','m');
ERROR 1048 (23000): Column 'empno' cannot be null
2.完整性约束:外键 (constraint)
- t_emp表（员工表）的deptno字段的名称不一定与t_dept表（部门表）中的deptno字段的名称一样，通常都是一样的，引用的类型要一样的
mysql> show create table t_emp;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t_emp | CREATE TABLE `t_emp` (
`empno` int NOT NULL,
`deptno` int DEFAULT NULL,
`ename` varchar(20) DEFAULT NULL,
`esex` char(2) DEFAULT NULL,
PRIMARY KEY (`empno`),
KEY `FK_DEPTNO` (`deptno`),
CONSTRAINT `FK_DEPTNO` FOREIGN KEY (`deptno`) REFERENCES `t_dept` (`deptno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> insert into t_dept values(2001,'hr');
Query OK, 1 row affected (0.06 sec)
mysql> insert into t_dept values(2002,'software');
Query OK, 1 row affected (0.05 sec)
mysql> select * from t_dept;
+--------+----------+
| deptno | dname |
+--------+----------+
| 2001 | hr |
| 2002 | software |
+--------+----------+
2 rows in set (0.00 sec)
自动增长字段
mysql> create table t_test1(id int auto_increment primary key, name varchar(20), age int default 20);
Query OK, 0 rows affected (0.20 sec)
mysql> desc t_test1;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| name | varchar(20) | YES | | NULL | |
| age | int | YES | | 20 | |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
mysql> insert into t_test1(name) values('bbb');
Query OK, 1 row affected (0.05 sec)
mysql> insert into t_test1(name,age) values('bbb',null);
Query OK, 1 row affected (0.03 sec)
mysql> select * from t_test1;
+----+------+------+
| id | name | age |
+----+------+------+
| 1 | bbb | 20 |
| 20 | bbb | 20 |
| 21 | bbb | NULL |
+----+------+------+
3 rows in set (0.00 sec)
3.完整性约束:用户自定义完整性约束 (check)
- 主键，外键，用户自定义的约束保存的位置如下
在mysql中，所有主键的约束名称都叫做：PRIMARY（在oracle数据库中是生效的，主键名称设置啥，这里就叫啥）；
所有外键的约束名称设置什么就是什么； - sql语句如下：test.sql
====主键约束====
create table t_emp(empno int not null primary key, ename varchar(20), esex char(2));普通字段也可以设置为not null
/* 等价于 */
create table t_emp(empno int primary key, ename varchar(20), esex char(2));
/* 等价于 */
create table t_emp(empno int, ename varchar(20), esex char(2), primary key (empno));
/* 等价于 */
PK_EMPNO大小写都行，给primary key加上约束
create table t_emp(empno int, ename varchar(20), esex char(2), constraint PK_EMPNO primary key(empno));
/* 等价于 */
create table t_emp(empno int, ename varchar(20), esex char(2));
alter table t_emp add constraint PK_EMPNO primary key(empno);
/* 2个字段联合作为一个主键 */
alter table t_emp add constraint PK_EMPNO primary key(XX1,XX2);
show create table t_emp;
/* 查看它的查询语句 */
insert into t_emp values(1000, 'john', 'm');
insert into t_emp values(1000, 'lily', 'f');
insert into t_emp values(null, 'lily', 'f');
====外键约束====
/* deptno是外键：参照完整性，员工表t_emp的部门号deptno要么是空的，要么是部门表t_dept中存在的记录 */
/* t_dept是父表， t_emp是子表，因为t_emp子表的deptno来自t_dept*/
create table t_emp(empno int, deptno int, ename varchar(20), esex char(2));
alter table t_emp add constraint PK_EMPNO primary key(empno);
create table t_dept(deptno int, dname varchar(20));
alter table t_dept add constraint PK_DEPTNO primary key(deptno);
/* deptno外键引用到了部门表t_dept中的deptno */
alter table t_emp add constraint FK_DEPTNO foreign key(deptno) references t_dept(deptno);
set names gbk;
insert into t_dept values(2001, '人事部');
insert into t_dept values(2002, '技术部');
/* 查看插入的记录 */
select * from t_emp;
insert into t_emp values(1001, 2001, 'john', 'm');
insert into t_emp values(1003, 2003, 'john', 'm');
/* 显示表结构 */
describe table t_dept;
/* 等价于 */
desc table t_dept;
简化版
desc t_dept;
显示所有数据库：show databases;
选定数据库：use dbname;
显示当前数据库中所有表：show tables;
要先选定数据库
显式指定数据库中的所有表：show tables from dbname;
删除表的所有记录
delete from t_emp;
删除t_emp整张表
drop table t_emp;
/* 自动增长的字段 */
/* 自动增长的属性必定是主键 */
/*auto_increment是mysql自己的语法，oracle没有的*/
create table t_test1(id int auto_increment primary key, name varchar(30), age int default 20);
show create table t_test1;/* 查看它的查询语句 */
desc table t_test1;/*查看表结构*/
/* 用户自定义的约束 */
/* 主键不设置会自动增长 */
insert into t_test1 values(null, 'aaa');/*这样的写法是错误的*/
insert into t_test1 values(null, 'aaa', null);/*这样写，age=null*/
insert into t_test1(name) values( 'bbb');/*这样写，age = 20*/
/* 查看插入的记录 */
select * from t_emp;
/* 用户自定义完整性约束 (check) mysql不支持，oracle支持*/
create table t_test2(id int, name varchar(30), age int);
alter table t_test2 add constraint CC_AGE check (age >=18 and age<=60);
alter table t_test2 add constraint CC_NAME check (length(name)>2);
