Mysql进阶

  • Z时代
  • 2024-01-10
  • 分类：综合

database

一、约束

目的：使得数据更准确，更完整。

约束的分类：

  1. 键约束

    • 主键约束

    • 唯一键约束

    • 外键约束

  2. 非空约束

  3. 默认值约束

  4. 自增约束

  5. 检查约束（mysql暂时没有支持）

二、主键约束

（一）概述

  1. 关键字 ：primary key

  2. 特点：增加主键约束的列（字段）的值必须是非空 + 唯一的，一个表只有一个主键约束

  3. 作用：保证表中不会出现两条无法区分的记录

  4. 要求：每一张表都必须有主键约束

  5. 分类

    • 单列主键约束

    • 复合主键约束

（二）使用主键约束

1、创建主键约束

（1）在建表时指定主键约束

create table 【数据库名.】表名称(
	字段1 数据类型 primary key,
	字段2 数据类型,
	....
);

create table 【数据库名.】表名称(
	字段1 数据类型,
	字段2 数据类型,
	....,
	primary key(字段1)
);

例如：

create table dept(
	id int primary key,
	name varchar(20),
	description varchar(100)
);

或

create table dept(
	id int ,
	name varchar(20),
	description varchar(100),
	primary key(id)
);

mysql> desc dept;
+-------------+--------------+------+-----+---------+-------+
| Field       | Type         | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| id          | int(11)      | NO   | PRI | NULL    |       |
| name        | varchar(20)  | YES  |     | NULL    |       |
| description | varchar(100) | YES  |     | NULL    |       |
+-------------+--------------+------+-----+---------+-------+

insert into dept values(1,"财务部","发钱的");
insert into dept values(1,"财务部","发钱的");
mysql> insert into dept values(1,"财务部","发钱的");
ERROR 1062 (23000): Duplicate entry "1" for key "PRIMARY"

（2）建表后指定主键约束

create table dept(
	id int,
	name varchar(20),
	description varchar(100)
);

修改表结构：

alter table dept add primary key(id);

2、删除主键约束

1、该列（column）不光设置为主键（primary key），还有自增长（auto_increment），句式：

alter table 表名 modify 字段名 类型, drop primary key;

2、如果没有设置为自增长（auto_increment），那么可以直接删除主键（primary key ），句式：

alter table +表名+drop primary key;

例如：

alter table dept drop primary key;

3、复合主键

（1）在建表时指定主键约束

create table [数据库.]表名称(
	字段1 数据类型,
	字段2 数据类型,
	字段3 数据类型,
	...,
	primary key(字段列表)
);

说明：复合主键不能在列后面加，需要单独指定

（2）建表后指定主键约束

alter table [数据库].表名称 add primary key(字段列表);

例如：

create table stu(
	sid int primary key,  #学号
	sname varchar(20) 	#姓名
);
create table course(
	cid int primary key,		#课程编号
	cname varchar(20)		#课程名称
);
create table score(
	sid int,		#学号
	cid int,		#课程编号
	score int		#对应的成绩
);

insert into stu values(1,"张三"),(2,"李四");
insert into course values(1001,"java"),(1002,"mysql");
insert into score values(1,1001,89),(1,1002,90),(2,1001,56),(2,1002,69);

mysql> select * from stu;
+-----+-------+
| sid | sname |
+-----+-------+
|   1 | 张三  |
|   2 | 李四  |
+-----+-------+
2 rows in set (0.00 sec)
mysql> select * from course;
+------+-------+
| cid  | cname |
+------+-------+
| 1001 | java  |
| 1002 | mysql |
+------+-------+
2 rows in set (0.00 sec)
mysql> select * from score;
+------+------+-------+
| sid  | cid  | score |
+------+------+-------+
|    1 | 1001 |    89 |
|    1 | 1002 |    90 |
|    2 | 1001 |    56 |
|    2 | 1002 |    69 |
+------+------+-------+
4 rows in set (0.00 sec)

alter table score add primary key(sid,cid);

create table score(
	sid int,		#学号
	cid int,		#课程编号
	score int,		#对应的成绩
	primary key(sid,cid)
);

变通成如下这种：

create table score(
	id int,			#没有业务意义，只是唯一标记一行
	sid int,		#学号
	cid int,		#课程编号
	score int,		#对应的成绩
	primary key(id)
);

三、唯一键约束

（一）概述

  1. 关键字：unique key

  2. 特点：指定了唯一键的列的值必须唯一，不能重复

  3. 作用：给主键以外的列，限定唯一性

  4. 唯一键分类

    • 单列的唯一

    • 复合唯一

唯一键和主键的区别：

  • 主键不能为空，唯一键可以为空

  • 主键约束，一个表只能有一个，而唯一键可以有很多个

（二）使用唯一键

1、如何创建/指定唯一键

（1）在建表时

create table [数据库名.]表名称(
	字段1 数据类型 primary key,
	字段2 数据类型 [unique key],
	字段3 数据类型 [unique key],
	...
);

或

create table [数据库名.]表名称(
	字段1 数据类型  primary key,
	字段2 数据类型 ,
	字段3 数据类型 ,
	...,
	unique key(字段2),  #分别唯一
	unique key(字段3)
);

或

create table [数据库名.]表名称(
	字段1 数据类型  primary key,
	字段2 数据类型 ,
	字段3 数据类型 ,
	...,
	unique key(字段列表)  #复合唯一
);

举例：

create table emp(
	eid int primary key,  			#员工编号
	ename varchar(20),   			#姓名
	cardid varchar(18)  unique key,		#身份证号
	tel varchar(11) unique key
);

插入数据

insert into emp values(1,"张三","123456789123456789","12345678912");
insert into emp values(2,"李四","123456789123456788","12345678912");

mysql> insert into emp values(2,"李四","123456789123456788","12345678912");
ERROR 1062 (23000): Duplicate entry "12345678912" for key "tel"

（2）在建表后

修改表结构：

alter table [数据库名.]表名称 add unique key(字段名);
alter table [数据库名.]表名称 add unique key(字段列表);  #复合唯一

2、删除唯一键

修改表结构：

alter table emp drop unique key;  #错误的
alter table emp drop unique key(cardid);  #错误的
alter table emp drop index cardid;

索引：index

  • 作用：为了提高查询效率，而设置索引

我们的键约束（主键、唯一键、外键），都会自动创建索引。

  • 因为既然你建立键约束，那么该列的值一定很关键，那么在实际中肯定经常用他们的值来查询。

  • 因此，为了提高查询效率，会自动在这些列上增加索引。

alter table [数据库名.]表名称 drop index 索引名;

如果不知道索引名，可以通过如下的语句查询：

show index from 表名称;

四、非空和默认值约束

1、如何指定非空约束

（1）建表时

create table emp(
	eid int primary key,  				#员工编号
	ename varchar(20) not null,  			#姓名
	cardid varchar(18)  unique key not null ,	#身份证号
	tel varchar(11) unique key not null,
	gender char not null default "男"
);

insert into emp values(1,"张三","111","10086","女");
insert into emp values(2,"李四","111","女");  				#错误的，原因是值的数量和列的数量不匹配
insert into emp(eid,ename,cardid,gender) values(2,"李四","111","女");   #错误的  因为tel设置非空，但是又没指定默认值
insert into emp(eid,ename,cardid,tel) values(2,"李四","222","10010"); 
insert into emp values(3,"王五","3333","10011",default);

（2）建表后

create table [数据库名.]表名称(
	字段1 数据类型 primary key,
	字段2 数据类型 [unique key] [not null] [default 默认值],
	字段2 数据类型 [unique key] [not null] [default 默认值],
	...
);

create table emp(
	eid int primary key,  			#员工编号
	ename varchar(20) not null,  		#姓名
	cardid varchar(18)  unique key  ,	#身份证号
	tel varchar(11) unique key ,
	gender char 
);

alter table emp modify cardid varchar(18) unique key not null;
alter table emp modify tel varchar(11) not null;
alter table emp modify gender char not null default "男";

2、如何去掉非空和默认值约束

alter table emp modify gender char ;

五、自增约束

1、关键字：auto_increment

2、特点：

  1. 一个表只能有一个自增列

  2. 自增列必须是整型的

  3. 自增列必须是键列，例如：主键，唯一键

3、如何指定自增

create table emp(
	eid int primary key auto_increment,
	ename varchar(20) not null
);

insert into emp values(2,"张三");
insert into emp(ename)values("李四");
insert into emp values(0,"王五");
insert into emp values(null,"赵六");

六、外键约束

外键约束不是必须的，而且现在很多大的公司，数据量比较大时，不建议在数据库层面设计外键

因为他觉得这样效率低，把这个数据的约束挪到代码层面去判断。

（一）概述

1、关键字：foreign key

2、特点：

  • 约束的是两张表的关系

    • 需要两张表，或者一张表虚拟成两张表

  • 两张表分为主表（父表）和从表（子表）

    • 外键的建立/指定是在从表（子表）上建立。

  • 被参考的表称为主表，主表的被参考列必须是主键或唯一键

  • 一个表可以有多个外键

（二）如何指定外键

1、在建表时指定外键

要求：

（1）建表的顺序

先建主表，再建从表

从表的语法：

create table 【数据库名.】表名称(
	字段1 数据类型 primary key,
	字段2 数据类型 [unique key] [not null] [default 默认值],
	字段3 数据类型 [unique key] [not null] [default 默认值],
	...,
	foreign key(从表的外键列) references 主表名(主表被参考的列名)
);

（2）删表的顺序

先删从表，再删除主表

（3）添加/修改从表数据

添加/修改从表记录时，引用主表的列的值必须是存在的。

例如：添加/修改员工表时，员工所在部门的值必须引用部门表的部门编号，保证该部门编号是存在的。

（4）删除/修改主表记录

A：默认情况下，如果主表的被参考列的值被引用，那么就不能轻易的被删除和修改。

  • 例如：2号部门被员工引用了，那么这个2号部门就不能被删除，并且2这个编号值不能被修改。

    • foreign key(从表的外键列) references 主表名(主表被参考的列名) 【on update restrict/no action】 【on delete restrict/no action】

B：如果在建立外键时，指定了“级联”策略，那么可以做到级联修改和删除

  • foreign key(从表的外键列) references 主表名(主表被参考的列名) 【on update cascade】 【on delete cascade】

C:如果在建立外键时，指定了“置空”策略，那么可以做到主表的记录被修改或删除时，从表的对应字段变为NULL

  • foreign key(从表的外键列) references 主表名(主表被参考的列名) 【on update set null】 【on delete set null】

#部门表：主表
create table dept(
	did int primary key,			#部门编号
	dname varchar(20) not null unique key,  #部门名称
	description varchar(100)		#部门简介
);
insert into dept values(1,"财务部","发钱的"),(2,"后勤部","发礼物的");

#职位表：主表
create table job(
	jid int primary key,		#职位编号
	title varchar(20) not null , 	#职位名称
	description varchar(100)	#职位简介
);
insert into job values(1,"会计","算钱的"),(2,"助理","修电脑的");

#员工表：从表
create table emp(
	eid int primary key,		#员工编号
	ename varchar(20) not null, 	#员工姓名
	deptid int,    			#所在的部门编号   deptid可以取名did
	jobid int,    			#职位编号
	foreign key(deptid) references dept(did),
	foreign key(jobid) references job(jid) on update set null on delete set null
);
insert into emp values(1,"张三",1,1),(2,"李四",1,1),(3,"王五",2,2),(4,"赵六",2,2);

2、建表后指定外键

alter table 从表名称 add foreign key(从表的字段) references 主表名(主表被参考的列名);

以上是 Mysql进阶 的全部内容， 来源链接： utcz.com/z/532832.html

回到顶部