文章详情

短信预约信息系统项目管理师 报名、考试、查分时间动态提醒

请输入下面的图形验证码

提交验证

短信预约提醒成功

Mysql进阶

2015-09-13 09:43

关注

Mysql进阶

目的:使得数据更准确,更完整。

约束的分类:

  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、删除主键约束

该列(column)不光设置为主键(primary key),还有自增长(auto_increment),句式:

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

如果没有设置为自增长(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:默认情况下,如果主表的被参考列的值被引用,那么就不能轻易的被删除和修改。

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

C:如果在建立外键时,指定了“置空”策略,那么可以做到主表的记录被修改或删除时,从表的对应字段变为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 主表名(主表被参考的列名);
阅读原文内容投诉

免责声明:

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

软考中级精品资料免费领

  • 历年真题答案解析
  • 备考技巧名师总结
  • 高频考点精准押题
  • 2024年上半年信息系统项目管理师第二批次真题及答案解析(完整版)

    难度     813人已做
    查看
  • 【考后总结】2024年5月26日信息系统项目管理师第2批次考情分析

    难度     354人已做
    查看
  • 【考后总结】2024年5月25日信息系统项目管理师第1批次考情分析

    难度     318人已做
    查看
  • 2024年上半年软考高项第一、二批次真题考点汇总(完整版)

    难度     435人已做
    查看
  • 2024年上半年系统架构设计师考试综合知识真题

    难度     224人已做
    查看

相关文章

发现更多好内容

猜你喜欢

AI推送时光机
位置:首页-资讯-数据库
咦!没有更多了?去看看其它编程学习网 内容吧
首页课程
资料下载
问答资讯