表的约束

最新推荐文章于 2022-10-15 00:49:26 发布

原创最新推荐文章于 2022-10-15 00:49:26 发布 · 252 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#数据库

sql 专栏收录该内容

5 篇文章

订阅专栏

本文详细介绍了数据库的创建、查看、选择、删除和修改操作，涵盖了数据类型、表的创建、修改与约束，如主键、唯一性约束、非空约束和外键。同时讲解了如何使用ALTER和CREATE TABLE语句进行表结构的调整，是数据库初学者和进阶者的实用参考。

数据库的操作

数据库的创建：create database 数据库名 charset utfs8；

查看数据库： show

show databasel;
show create database db;
select database;

选择数据库： use 数据库名；
删除数据库： drop database 数据库名；
修改数据库： alter database db1 charset utf8；

数据类型

在这里插入图片描述

基本数据类型

表table的创建

-- 学号 姓名      性别  出生日期 入学时间  专业     院系     创建时间
-- int varchar  char  date   date    varchar  varchar timestamp
create table t_student(
    sno int,
    sname varchar(40),
    gender char(1),
    birthday date,
    schooltime date,
    major varchar(255),
    department varchar(255),
    createtime timestamp;
)



create table user(
    id int(10) primary key,
    uname varchar(40),
    passwd varchar(40),
    phoneNum char(11) unique,
    birthday datetime,
    address varchar(255),
    createtime timestamp default now()
);

create table person(
	xing varchar(40),
    ming varchar(40),
    birthday datetime,
    primary key(xing, ming)
);

表 table的修改

根据查询语句创建表 : create table 表名： as select * from 另外一个表名
- ```
create table stuo1 as select * from student;
```
添加一列 : alter table 表名 add 列名
- default 如果不输入默认输入 sysdate 今天的日期。
```
alter table student add birthday date default sysdate;
```
删除一列 : alter table 表名 drop 列名
```
alter table student drop column email;
```
修改一列： alter table 表名 modify(要修改的列); modify改的是属性
- ```
alter table student modify(clazz varchar(20));
```
修改列名 : alter table 表名 rename 旧列名 to 新列名
- ```
alter table student rename column birthday to birth;
```
修改表名： rename 旧表名 to 新表名
- ```
rename student to t_s;
```
删除一张表： drop table 表名
- ```
drop table t_S;
```

表 table的约束

约束指的是我们创建的表，别人在插入数据的时候，对数据的约束，而不是对创建人的约束。
主键约束 primary key
- 主键值必须唯一标识表中的每一行，且不能为NULL，即表中不可能存在有相同主键值的两行数据。主键列的数据类型不限，但此列必须是唯一并且非空。
- 主键分为单字段主键和多字段联合主键
  - 单字段主键
```
-- 在定义字段时指定主键
CREATE TABLE <数据表>
(
	<字段名> <数据类型> PRIMARY KEY  
);

-- eg： 在数据库中创建数据表table_example1，主键为id
CREATE TABLE table_example1
(
	id INT(10) PRIMARY KEY,
	name VARCHAR(20)
);



-- 在定义完字段后指定主键
CREATE TABLE <数据表>
(
	<字段名> <数据类型> ,
	PRIMARY KEY(<字段名>)  
);


-- eg：在数据库中创建数据表table_example2，主键为id
CREATE TABLE table_example2
(
	id INT(10),
	name VARCHAR(20),
	PRIMARY KEY(id),
);
```
  - 联合主键
```
-- 在定义完字段后指定主键
CREATE TABLE <数据表>
(
	<字段1> <数据类型>,
	<字段2> <数据类型>,
	PRIMARY KEY(<字段1>,<字段2>)
);

-- eg：创建数据表table_example3，主键约束为id,name的联合主键
CREATE TABLE table_example3
(
	id INT(10),
	name VARCHAR(20),
	PRIMARY KEY(id,name)
);
```
  - 修改属性时添加主键约束
    - ```
    -- 修改表table_example4，将id设为主键
    ALTER TABLE table_example4 add primary key (<字段名>); 
```
- 删除主键
```
  ALTER TABLE <数据表> drop primary key (<字段名>);
   
   -- 一张表中只有一个主键，所以删除主键约束可以不用指定主键名，两行代码效果一样
  ALTER TABLE <数据表> drop primary key; 
```
- 联合主键不能包含不必要的多余字段，当把联合主键的某一字段删除后，如果剩下的字段构成的主键仍然满足唯一性原则，那么这个联合主键是不正确的。这是最小化原则。
  
  每个表都有一个且只有一个主键。主键不接受NULL或重复值。如果主键由两列或更多列组成，则值可能在一列中重复，但主键中所有列的值组合必须是唯一的。
  
  请参阅以下培训(training)表。
  
  training表的主键包含employee_id和course_id列。course_id列中的值是重复的，但employee_id和course_id中的值组合不是重复的。
- 在 MySQL 中，当主键定义为自增长后，这个主键的值就不再需要用户输入数据了，而由数据库系统根据定义自动赋值。每增加一条记录，主键会自动以相同的步长进行增长。
  
  通过给字段添加 AUTO_INCREMENT 属性来实现主键自增长。语法格式如下：
  
  字段名数据类型 AUTO_INCREMENT
  - 默认情况下，AUTO_INCREMENT 的初始值是 1，每新增一条记录，字段值自动加 1。
  - 一个表中只能有一个字段使用 AUTO_INCREMENT 约束，且该字段必须有唯一索引，以避免序号重复（即为主键或主键的一部分）。
  - AUTO_INCREMENT 约束的字段必须具备 NOT NULL 属性。
  - AUTO_INCREMENT 约束的字段只能是整数类型（TINYINT、SMALLINT、INT、BIGINT 等）。
  - AUTO_INCREMENT 约束字段的最大值受该字段的数据类型约束，如果达到上限，AUTO_INCREMENT 就会失效。

唯一性约束 unique

被约定的列中的值可以为空但是不能相同。

格式： 列名 <数据类型> unqiue ;

create table tb_unique01(
	id int(11) primary key,
    name varchar(22) unique,
    location varchar(50)
);

alter table <数据表名> add constraint <唯一的约束名> unique(<列名>) ;

alter table tb_unique01 add constraint unique_t_unique01_location unique(location);

alter table <表名> drop index <唯一约束名>

alter table tb_unique01 drop index unique_t_unique01_location;

非空约束 notnull
- 被约束的列中的值不能为null
  
  格式： 列名 <数据类型> not null；
```
create table tb_null01(
	id int(11) primary key,
    name varchar(22) not null,
    location varchar(50)
);
```
- 将列的属性修改为非空约束。
  
  alter table <数据表名> change column <字段名> <字段名> <数据类型> not null;
```
alter table tb_null01 change column location location varchar(50) not null;
```
- 删除非空约束。
  
  alter table <数据表名> change column <字段名> <字段名> <数据类型> null；
```
alter table tb_null01 change column location location varchar(50) null;
```
外键约束
```
[外键约束](http://c.biancheng.net/view/2441.html)
```
- 在定义外键时，需要遵守下列规则：
  - 主表必须已经存在于数据库中，或者是当前正在创建的表。如果是后一种情况，则主表与从表是同一个表，这样的表就称为参照表，这种结构称为自参照完整性。
  - 必须为主表定义主键。
  - 主键不能包含空值，但允许在外键中出现空值。也就是说，只要外键的每个非空值出现在指定的主键中，这个外键的内容就是正确的。
  - 在主表的表名后面指定列名或列名的组合。这个列或列的组合必须是主表的主键或候唯一性键。
  - 外键中列的数目必须和主表的主键中列的数目相同。
  - 外键中列的数据类型必须和主表主键中对应列的数据类型相同。
- 外键的删除
  - 级联删除
    - 设置为NULL
    - 阻止删除
    - 删除主表数据的时候，要保证这个ID没有被字表所使用
    - - 创建表时设置外键约束：
  constraint <外键名> foreign key (列名， … ) references <主表名> (主键列1, …);
- ```
-- 1位老师对应N个学生， teacher表为主表， student表为外键表， tid为外键
create table teacher(
	tid varchar(40) primary key，
    name varchar(22),
    phonenum varchar(11) unique,
    location varchar(50)
);

create table student(
	sid varchar(40) primary  key,
    name varchar(40),
    tid varchar(40),
    constraint fk_teacher_student_tid foreign key(tid) references teacher(tid) on delete cascade
);

insert into teacher values('a', '岳不群');
insert into teacher values('b', '定先师太');
insert into teacher values('c', '无崖子');

insert into student values('1', '令狐冲', 'a');
insert into student values('2', '岳灵珊', 'a');
insert into student values('3', '依琳', 'b');
insert into student values('4', '星宿大仙', 'c');
-- 错误数据， 因为主表主键没有d
insert into student values('5', '任我行', 'd');
```
- 修改表： alter table <数据表名> add constraint <外键名> foreign key(<列名>) references <主表名> （<列名>）;
- 删除表： alter table <表名> drop foreign key <外键约束名>;

约束总结

-- 查看表中的约束
show create table <数据表名>;

create table table_name(
	列名1 数据类型 （int） primary key auto_increment,  -- 主键自动递增
    列名2 数据类型 not null，							-- 非空约束
    列名3 数据类型 unique，							-- 唯一性约束
    列明4 数据类型 default ‘值’，						-- 取一个默认值
    constraint 索引名 foreign key(外键列) references 主键表（主键列） on delete cascade | on delete set null                         -- 外键约束
)

1. 主键约束
添加： alter table table_name add primary key(字段);
删除： alter table table_name drop primary key; 

2.非空约束
添加：alter table table_name modify 列名 数据类型 not null;
删除：alter table table_name modify 列名 数据类型 null；

3.唯一约束
添加：alter table table_name add unique 约束名（字段）;
删除：alter table table_name drop key 约束名;

4.自动增长
添加：alter table table_name modify 列名 int auto_increment;
删除：alter table table_name modify 列名 int;

5.外键约束
添加： alter table table_name add constraint 约束名 foreign key（外键列） references 主键表（主键列）;

删除：
第一步：删除外键
alter table table_name drop foreign key 约束名;

6. 默认值
添加: alter table table_name alter 列名 set default '值';
删除： alter table table_name alter 列名 drop default;