MYSQL系列文章目录
3.2 学生-课程数据库
3.3.1 模式的定义(创建)与删除
3.3.3 索引的建立与删除
3.4 数据查询
3.5 数据更新
数据定义-目录
3.3.2 数据库表的定义(创建)、删除与修改
1.定义基本表(创建表)
创建了一个模式就建立了一个数据库的命名空间,一个框架。
create table <表名> (<列名><数据类型>[列级完整性约束条件]
[,<列名><数据类型>[列级完整性约束条件]]
…
[,<表级完整性约束条件>]);
如果完整性约束条件涉及该表的多个属性列,则必须定义在表级上,否则既可以定义在列级也可以定义在表级。
[例3.5] 建立一个“学生”表Student(列级约束条件,主键(主码),取唯一值)
代码如下(示例):
/*创建一个Student表*/
create TABLE Student (Sno CHAR(9) PRIMARY KEY,/*列级完整性约束条件,Sno是主码*/
Sname CHAR(20) UNIQUE, /*Sname取唯一值*/
Ssex enum('男','女') not null default '女',/*默认为'女',不能为空*/
Sage SMALLINT,
Sdept CHAR(20)
);
/*创建一个Student表*/
create TABLE Student (Sno CHAR(9),
Sname CHAR(20) UNIQUE, /*Sname取唯一值*/
Ssex enum('男','女') not null default '女',/*默认为'女',不能为空*/
Sage SMALLINT,
Sdept CHAR(20),
constraint sno PRIMARY KEY(Sno)/*表级完整性约束条件,Sno是主码,sno是主码的名称*/
/*constraint sno 可以省略*/
);
[例3.6] 建立一个“课程”表Course(列级约束条件,不能为空值,外键约束条件)
代码如下(示例):
/*创建一个Course表*/
create TABLE Course (Cno CHAR(4) PRIMARY KEY,/*列级完整性约束条件,Cno是主码*/
Cname CHAR(40) NOT NULL, /*列级完整性约束条件,Cname不能取空值*/
Cpno CHAR(4),/*Cpno的含义是先修课*/
Ccredit SMALLINT,
FOREIGN KEY (Cpno) REFERENCES Course(Cno)
/*表级完整性约束条件,Cpno是外码,被参照表是Course,被参照列是Cno*/
);
本例说明参照表和被参照表可以是同一个表。
[例3.7] 建立学生选课表SC(表级约束条件,外键约束条件,外键命名)
代码如下(示例):
/*创建一个SC表*/
create TABLE SC (Sno CHAR(9),
Cno CHAR(4),
Grade SMALLINT,
PRIMARY KEY (Sno,Cno),/*主码由两个属性构成,必须作为表级完整性进行定义*/
constraint sc_fk_1 FOREIGN KEY (Sno) REFERENCES Student(Sno),
/*表级完整性约束条件,sc_fk_1是外键名(建议写为:Sno),Sno是外码,被参照表是Student,被参照列是Sno*/
constraint sc_fk_2 FOREIGN KEY (Cno) REFERENCES Course(Cno)
/*表级完整性约束条件,sc_fk_2是外键名(建议写为:Cno),Cno是外码,被参照表是Course,被参照列是Cno*/
);
创建外键约束条件并赋名字(名称)
- 本页面目录中有关于外键的约束条件的相关内容介绍跳转。
代码如下(示例):
/*创建一个SC表*/
create TABLE SC (Sno CHAR(9),
Cno CHAR(4),
Grade SMALLINT,
PRIMARY KEY (Sno,Cno),/*主码由两个属性构成,必须作为表级完整性进行定义*/
/*constraint sc_fk_1*/ FOREIGN KEY (Sno) REFERENCES Student(Sno),
/*表级完整性约束条件,sc_fk_1是外键名(可以省略,有默认值),Sno是外码,被参照表是Student,被参照列是Sno*/
/*constraint sc_fk_2*/ FOREIGN KEY (Cno) REFERENCES Course(Cno)
/*表级完整性约束条件,sc_fk_2是外键名(可以省略,有默认值),Cno是外码,被参照表是Course,被参照列是Cno*/
);
2.数据类型
数据类型 | 含义 |
---|---|
CHAR(n),CHARACTER(n) | 长度为n的定长字符串。(不能进行算术运算) |
VARCHAR(n) | 最大长度为n的变长字符串 |
CHARACTERVARCHAR(n) | 最大长度为n的变长字符串 |
CLOB | 字符串大对象 |
BLOB | 二进制大对象 |
INT,INTEGER | 长整数(4字节) |
SMALLINT | 短整数(2字节) |
BIGINT | 大整数(8字节) |
NUMERIC(p,d) | 定点数,有p位数字(不包含符号、小数点)组成,小数点后面有d位数字 |
DECIMAL(p,d),DEC(p,d) | 同NUMERIC |
BOOLEAN | 逻辑布尔量 |
DATE | 日期,包含年、月、日,格式为YYYY-MM-DD |
TIME | 时间,包含一日的时、分、秒,格式为HH:MM:SS |
TIMESTAMP | 时间戳类型 |
… | … |
3.模式与表
4.修改基本表(修改表)
0. 修改数据表的名字(名称)
alter table <旧表名> rename to <新表名>;
alter table student rename to students;
1.添加新列、新的列级完整性约束条件
alter table <表名>
[add [column] <新列名><数据类型>[完整性约束条件]];
[例3.8] 向Student表增加“入学时间"列,其数据类型为日期型。
alter table student add s_entrance DATE;
不论基本表中原来是否已有数据,新增加的列一律为空值。
2.添加新的表级完整性约束条件
alter table <表名>
[add <表级完整性约束条件>];
[例3.10] 增加课程名称必须取唯一值的约束条件。增加学生名字必须取唯一值的约束条件。
alter table course add unique(Cname);
alter table student add unique(Sname);
alter table <表名> add [constraint 约束名] unique(<列名>);
alter table student add constraint sname unique(Sname);
3.drop 删除表中的列,如果指定了CASCADE短语,则自动删除引用了该列的其他对象,比如视图;
如果指定了RESTRICT短语,则如果该列被其他对象引用,RDBMS将拒绝删除该列。
alter table <表名>
[drop [column] <列名> [cascade | restrict]];
alter table student drop s_entrance;
4.drop constraint 删除指定的完整性约束条件。
alter table <表名>
[drop constraint <完整约束名> [cascade | restrict]];
alter table student drop constraint sname;
注意:这里是约束名
5.alter column 修改原有的列定义,包括修改列名和数据类型。
[例3.9] 将年龄的数据类型由字符型(假设原来的数据类型是字符型)改为整数。
alter table <表名>
[modify <列名><数据类型>];
alter table student modify Sage int;
alter table <表名>
[alter column <列名><数据类型>];
这个会报错。
alter table student alter COLUMN sage int;/*这个会报错*/
例3.8和3.9合并:添加一条新的列属性,修改某列的数据类型
alter table student add s_entrance DATE;/*在student表中,添加一个列,数据类型为时间*/
alter table student modify Sage smallint;/*在student表中,将Sage的数据库类型修改为smallint*/
desc student;/*显示student表的数据信息*/
5.删除基本表(删除表)
drop table <表名> [restrict | cascade];
cascade | restrict 必须任选其一
cascade(级联删除) 无论如何全部删除
restrict(限制删除) 如果子文件夹有文件就不能执行删除
默认情况为restrict
drop table Student cascade;
drop table Student;
6.清除表中的全部数据
delete from <表名>
delete from student;
添加外键约束条件和删除外键约束条件
如何获取外键约束条件的名称(名字)
select * from information_schema.referential_constraints;/*获取外键约束信息*/
select * from information_schema.key_column_usage where constraint_schema='school';
select * from information_schema.key_column_usage where constraint_schema='<数据库名称>';/*必须加''*/
/*可以获取指定数据库中的所有键约束的列信息,包括主键约束中的主键列,唯一约束中的唯一键列和外键约束中的引用列*/
删除外键约束条件
alter table sc drop constraint sc_fk_2;/*删除sc表中的Cno外键约束条件*/
alter table sc drop foreign key sc_fk_2;/*删除sc表中的Cno外键约束条件*/
添加外键约束条件
alter table <表名> add [constraint 约束名] foreign key(<列名>) references <表名> (<列名>,…);
alter table sc add foreign key (Cno) references course (Cno);/*添加sc表中的Cno外键约束条件*/
alter table sc add constraint sc_fk_2 foreign key (Cno) references course (Cno);/*添加sc表中的Cno外键约束条件*/
constraint sc_fk_2 添加外键约束条件的名称
(外键的名称可以自定义;如果不自定义(默认)外键的名称,则外键的名称有默认值(不是列名))
如果外键的名称默认(不自定义),则索引的名称默认值为列名;
如果外键的名称自定义,则索引名称不为默认值,为外键自定义的名称;
添加主键约束条件和删除主键约束条件
对已有列添加主键约束条件
alter table <表名> add [constraint 约束名] primary key(<列名>,…);
中括号里面的内容可能会报错。
alter table student add constraint sno primary key (Sno);
/*没有添加上约束名,主键添加上了*/
alter table <表名>
[drop constraint <约束名>;
alter table student drop constraint sno;
/*会报错*/
关于自增的添加和删除(自增的字段需为主键且不为CHAR类型)
0. 自增,主键,添加列
alter table student add sno int auto_increment primary key first;/*添加Sno列名,自增,主键*/
1. 修改Sno列名并添加自增(此时的Sno是主键)
alter table student modify Sno int auto_increment;/*修改Sno列名,添加为自增*/
2. 修改Sno列名并删除自增(本质是修改了数据表的结构,没有使用删除功能)
alter table student modify Sno int;/*修改Sno列名,删除自增*/
3.在创建表时,让int类型的主键自增
/*创建一个Student表*/
create TABLE Student (Sno int auto_increment PRIMARY KEY,/*列级完整性约束条件,Sno自增,Sno是主码*/
Sname CHAR(20) UNIQUE, /*Sname取唯一值*/
Ssex enum('男','女') not null default '女',/*默认为'女',不能为空*/
Sage SMALLINT,
Sdept CHAR(20)
);