数据库学习笔记——DDL

数据库学习笔记——DDL

建立EMPLOYEE数据库：

CREATE TABLE employee(employee_ID int not null,employee_name varchar(20) not null,street varchar(20) not null,city varchar(20) not null,PRIMARY KEY(employee_ID)
);CREATE TABLE company(company_name varchar(30) not null,city varchar(20) not null,PRIMARY KEY(company_name)
);create table manages(employee_ID int not null,manager_ID int,primary key(employee_ID),foreign key(employee_ID) references employee(employee_ID) on delete cascade,foreign key(manager_ID) references employee(employee_ID) on delete set null			
);create table works(employee_ID int not null,company_name varchar(30),salary numeric(8,2) check (salary>3000),  primary key(employee_ID),foreign key(employee_ID) references employee(employee_ID) on delete cascade,foreign key(company_name) references company(company_name) on delete set null		
);

向基本表employee 中增加“性别”属性列，其属性名为sex，数据类型为字符型：

ALTER TABLE employee add sex char(1) comment '性别';

向基本表employee中增加“年龄”属性列，其属性名为age，数据类型为SMALLINT, 并且年龄在15至30之间：

ALTER TABLE employee add age SMALLINT comment '年龄';
ALTER TABLE employee add CONSTRAINT check1 check (age>=15 and age<=30);

将employee中的age年龄的数据类型改为INT型：

ALTER TABLE employee MODIFY age int;

将employee中的age字段改名为emp_age：

ALTER TABLE employee drop check check1;
ALTER TABLE employee change age emp age int;
ALTER TABLE employee add CONSTRAINT check1 check (emp_age>=15 and emp_age<=30);

将employee中的sex列允许为空值的属性更改为不允许为空值：

ALTER TABLE employee change sex sex char(1) not null;

向company表中增加id字段，并添加自增约束：

create table works(employee_ID int not null,company_name varchar(30),salary numeric(8,2) check (salary>3000),  primary key(employee_ID),foreign key(employee_ID) references employee(employee_ID) on delete cascade,CONSTRAINT works_fk foreign key(company_name) references company(company_name) on delete set null		
);# 删除works的外键约束
ALTER TABLE works drop foreign key works_fk;
# 删除company原始主键
ALTER TABLE company drop PRIMARY KEY;
# 主键才能添加自增约束
ALTER TABLE company add company_ID int PRIMARY KEY auto_increment;
# 恢复从表的外键约束
ALTER TABLE works add company_ID int;
ALTER TABLE works add CONSTRAINT works_fk1 FOREIGN KEY (company_ID) references company (company_ID) on delete set null;desc company;
desc works;
# 显示works建表语句
show create TABLE works;

删除employee表中的sex列：

ALTER TABLE employee drop sex;

删除employee表：

drop TABLE works;
drop TABLE manages;
drop TABLE employee;

manages表中的manager_ID字段上无法添加not null约束：

create table manages(employee_ID int not null,manager_ID int,primary key(employee_ID),foreign key(employee_ID) references employee(employee_ID) on delete cascade,foreign key(manager_ID) references employee(employee_ID) on delete set null			
);

当主表中一条记录被删除时，如何处理子表中的外键字段：

• on delete cascade : 删除子表中所有的相关记录

• on delete set null : 将所有相关记录的外键字段值设置为NULL

• on delete no action: 不做任何操作（默认）

manager_ID作为外键字段，被设置为on delete set null，即当主表中一条记录被删除时，manager_ID设置为NULL，与“添加not null约束”冲突，即无法成功添加约束。