1、数据库定义

1.1.数据库

创建数据库

create database db_school default character set gb2312 default collate gb2312_chinese_ci;   

修改数据库
修改数据库默认字符集

alter database db_school default character set gb2312 default collate gb2312_chinese_ci;

删除数据库

drop database db_school;

1.2.表

创建表

create table tb_student 
(
studentNo char(10) not null unique,
studentName varchar(20) not null,
sex char(2),
birthday date,
native varchar(20),
nation varchar(10),
classNo char(6)
) engine=innodb;
create table tb_student1
(
studentNo char(10) not null unique auto_increment,
studentName varchar(20) not null,
sex char(2),
birthday date,
native varchar(20),
nation varchar(10),
classNo char(6)
) engine=innodb;
create table tb_student2
(
studentNo char(10) not null unique,
studentName varchar(20) not null,
sex char(2),
birthday date,
native varchar(20),
nation varchar(10) default '汉',
classNo char(6)
) engine=innodb;

表的查看
use db_school;show tables;
show tables from db_school;
show tables in db_school;

表结构简单查看
show columns from tb_student2;
desc tb_student2;

表结构详细查看
show create table tb_student2\G

表的重命名
alter table tb_student rename to backup_tb_student;
rename table backup_tb_student to tb_student;

表的删除
drop table tb_student,tb_student1,tb_student2;

1.3.字段

添加字段
alter table tb_student2 add column id int not null unique auto_increment first;
alter table tb_student add column department varchar(16) default '信息学院' after nation;

修改字段名和数据类型
alter table tb_student change column birthday age tinyint null default 18;

删除列的默认值
alter table tb_student alter column department drop default;

修改列的默认值
alter table tb_student alter column department set default '经济学院';

修改列的位置
alter table tb_student modify column department varchar(20) not null after studentName;

删除字段
alter table tb_student2 drop column id;

1.4.数据完整性约束


定义实体完整性


主键约束
实现主键约束有两种方式
列级完整性约束

create table tb_student
(
studentNo char(10) primary key,
studentName varchar(20) not null,
sex char(2) not null,
birthday date,
native varchar(20),
nation varchar(10) default '汉',
classNo char(6)
) engine=innodb;

表级完整性约束

create table tb_student
(
studentNo char(10),
studentName varchar(20) not null,
sex char(2) not null,
birthday date,
native varchar(20),
nation varchar(10) default '汉',
classNo char(6),
primary key(studentNo)
) engine-innodb;

完整性约束命名

create table tb_student
(
studentNo char(10),
studentName varchar(20) not null,
sex char(2) not null,
birthday date,
native varchar(20),
nation varchar(10) default '汉',
classNo char(6),
constraint pk_student primary key(studentNo)
) engine=innodb;

候选键约束

create table tb_class
(
classNo char(6) primary key,
className varchar(20) not null,
department varchar(30) not null,
grade smallint,
classNum tinyint,
constraint uq_class inique(classNum)
) engine=innodb;

定义参照完整性


列级创建外键

create table tb_student
(
studentNo char(10),
studentName varchar(20) not null,
sex char(2) not null,
birthday date,
native varchar(20),
nation varchar(10) default '汉',
classNo char(6) references tb_class(classNo),
constraint pk_student primary key(studentNo)
) engine=innodb;

表级创建外键

create table tb_student
(
studentNo char(10),
studentName varchar(20) not null,
sex char(2) not null,
birthday date,
native varchar(20),
nation varchar(10) default '汉',
classNo char(6),
constraint pk_student primary key(studentNo),
constraint fk_student foreign key(classNo) references tb_class(classNo)
)
engine=innodb;

创建带参照动作的外键

create table tb_student
(
studentNo char(10),
studentName varchar(20) not null,
sex char(2) not null,
birthday date,
native varchar(20),
nation varchar(10) default '汉',
classNo char(6),
constraint pk_student primary key(studentNo),
constraint fk_student foreign key(classNo) references tb_class(classNo)
on update restrict
on delete cascade
)
engine=innodb;

用户定义完整性


非空约束
前面已经很多例子了
check约束

create table tb_course
(
courseNo char(6),
courseName varchar(20) not null,
credit int not null,
courseHour int not null,
term char(2),
priorCourse char(6),
constraint pk_course primary key(courseNo),
constraint fk_course foreign key(priorCourse) references tb_course(courseNo),
constraint ck_course check(credit=courseHour/16)
)
engine=innodb;