mysql的表单的创建、修改、删除

大耗子 2020年02月23日 322次浏览

UNI:唯一约束 数据不可重复

MUI:复合约束 复合数据不可重复

PRI:主键约束 相当于非空+唯一(一个表只允许一个主键)

创建行约束表:

create table student(
id int not null,
name varchar(100) not null,
phone varchar(20),
class_no int not null);

删除表:drop table student;

插入表:insert into student(id,name,phone,class_no) values(1,'小李子','123485493',1);

修改表:alter table student modify column phone varchar(20) not null;

不写not null 默认为null

删除行约束:alter table student modify column phone varchar(20);

创建唯一约束表:

create table student(
id int unique,
name varchar(100),
phone varchar(20),
class_no int);

唯一约束复合版

create table student(
id int unique,
name varchar(100),
phone varchar(20),
class_no int
constraint uc_phone unique(id,phone)  
);
此时id和phone组成唯一

唯一约束起名字:alter table student add contraint uc_phone unique(phone);

删除唯一约束:alter table student drop index uc_phone;

创建主键约束:

create table student(
id int primary key,
name varchar(100),
phone varchar(20),
class_no int
);

创建复合主键:

create table student(
id int,
name varchar(100),
phone varchar(20),
class_no int,
constraint pk_id primary key(id,phone)
);

添加主键约束:alter table student add primary key(id);

删除主键约束:alter table student drop priamry key;

清空表:delete from student;

自增主键:

create table student(
id int primary key auto_increment,
name varchar(100),
phone varchar(20),
class_no int
);

删除自增:alter table student modify column id int not null;

alter table student modify column id varchar(20) not null;

增加自增:alter table student modify column id int auto_increment;

增加自增主键:alter table student modify column id int primary key auto_increment;

check约束:在MySQL中不起作用,但可以写

create table student(
id int primary key auto_increment,
name varchar(100) not null,
phone varchar(20) unique,
age int check(age>0 and age<100)
);

默认值约束:

create table student(
id int primary key,
name varchar(100) not null unique,
phone varchar(20) default 'c++'
);

修改默认值:

alter table student modify subject varchar(20) default 'java';

删除默认值:

alter table student modify subject varchar(20) default null;

外键约束:一个表的值是参考另一个表的主键,必须是主键

create table class(
class_no int primary key,
class_name varchar(100) not null
);


create table student(
id int primary key,
name varchar(100) not null,
class_no int not null,
constraint fk_stucla foreign key(class_no) 
references class(class_no)
);

(建立外键)

外键使用:

insert class values(1,'c++'); 先添加外键才能使用

insert student values(1,'菜鸡',1);

#### 级联操作

cascade 父表数据变化,子表跟着变 父表数据删除,子表也删除相关数据

no action 子表中有匹配的记录,不允许对父表数据进行操作update/delete操作

set null 父表数据进行update/delete操作 字符匹配列设为null(注意,子表外键不能为not null)

create table class(
class_no int primary key,
class_name varchar(100) not null
);

create table student(
id int primary key,
name varchar(100) not null,
class_no int,
constraint fk_stucla foreign key(class_no)
references class(class_no) 
on delete cascade on update cascade
);

使用:

insert class values(1,'c++'),(2,'c'),(3,'java');

insert student values(1,'菜鸡1',1),(2,'菜鸡2',2),(3,'菜鸡3',3);

update student set class_no=1 where class_no=2;

例子:

create table subject(
subject_no int primary key,
subject_name varchar(100) not null
);

create table class(
class_no int primary key,
class_name varchar(100) not null,
class_addre varchar(100) not null
);

create table student(
id int primary key,
name varchar(100) not null,
sex varchar(2),
date_birth date default '2000-01-01',
class_no int,
subject_no int,
constraint fk_class foreign key(class_no)
references class(class_no) on delete cascade on update cascade,
constraint fk_subject foreign key(subject_no)
references subject(subject_no) on delete cascade on update cascade
)engine=InnoDB default charset=utf8;

删除表:drop table if exists student;
insert subject values(1,'c'),(2,'c++'),(3,'java');
insert class values(1,'21期','一楼'),(2,'22期','二楼'),(3,'23期','三楼');
insert student values(1,'耗子1','男','1998-10-30',1,1),(2,'耗子2','男','1998-10-30',2,2),(3,'耗子3','男','1998-10-30',3,3);