计算机二级MySQL笔记（第三章 数据定义）

---

数据库操作

---

创建数据库

create database if not exists db_school
	default character set gb2312
	default collate gb2312_chinese_ci;
-- 创建名为 db_school 的数据库
-- mysql默认对大小写不敏感
-- character set: 指定数据库字符集，默认字符集为latin1，此处为中文字符集gb2312
-- collate: 指定字符集校对规则，默认校对规则为latin1_swedish_ci，此处为中文字符集校对规则gb2312_chinese_ci
-- database可用schema代替
-- default 可省略
-- if not exists 避免重复创建报错，可省略
-- 注意分号‘;’结尾

选择数据库

use db_school;

查看数据库

show databases丨schemas;

修改数据库

alter database db_school
	default character set latin1
	default collate latin1_swedish_ci;
-- 修改 db_school 的数据库的默认字符集和校对规则
-- database 可用 schema 替代

删除数据库

drop database db_school;
-- 删除 db_school 数据库
-- database 可用 schema 替代

---

数据表操作

---

创建表

create table tb_student
(	studentNo char(10) not null unique,
	studentName varchar(20) not null,
	sex char(2),
	birthday data,
	native varchar(20),
	nation varchar(10) default '汉',
	classNo char(6)	
) engine = InnoDB;
-- 创建包含以上字段的 tb_student 表，指定InnoDB存储引擎
-- unique 代表唯一性约束
-- default'汉' 代表nation字段的默认值为‘汉’
--mysql 支持多种存储引擎，可通过 show engines 语句查看支持的存储引擎信息 

查看表

show tables from db_school;
-- 查看db_school数据库中的表信息
-- from 可用 in 代替
-- 如果查看当前数据库中的所有表信息，from db_name 参数可省略

查看表结构

-- 两种方法均可查看表 tb_student 的结构
show columns from tb_student;   -- from 可用 in 代替
describe  tb_student;  --describe 可用 desc 简写代替

-- 查看表的详细结构，输出创建表的详细语句，包括存储引擎和字符编码
show create table tb_student;

修改表

-- 添加字段
-- 向 tb_student 表中添加 int 型字段 id,要求不为空，取值唯一且自动增加，且添加到表的第一个字段
alter table tb_student 
	add column id int not null unnique auto_increment first;

-- 修改字段
-- 将 tb_student表中 birthday字段重命名为 age,并将其数据类型改为 tinyint，允许为空，默认值是18
alter table tb_student
	change column birthday age tinyint null default 18;

-- 将 tb_student表中 department字段的默认值修改为“经济学院”
alter table tb_student
	alter column department set default '经济学院';

-- 将 tb_student表中 department字段的数据类型更改为 varchar(20),取值允许为空，并将此字段移至字段 studentName 之后
alter table tb_student
	modify column department varchar(20) not null after studentName;

-- 注意这里的区别：
-- change column: 可同时修改字段名称和数据类型
-- alter column: 修改或删除字段的默认值
-- modify column: 只能修改数据类型，不可修改名称，也可修改列在表中的位置
-- alter table语句中可同时修改多个字段，只需用逗号分隔

-- 删除字段
-- 删除 tb_student表中字段 id
alter table tb_student drop column id;

重命名表

-- 将 tb_student 表重新命名为 backup_tb_student
rename table tb_student to backup_tb_student;

删除表

drop table tb_student;
-- 同时删除多个表，表与表之间用逗号分隔即可

---

约束

---

表级约束与列级约束

-- 例：在表 test 表中对 column1 字段创建一个主键约束

-- 列级约束:直接在字段后加上约束关键字
create table test
(	column1 char(10) not null primary key,
	column2 int(10),
) engine = InnoDB;

-- 表级约束：在所有字段定义后，添加创建约束的子句（以下两种方式均可）
create table test
(	column1 char(10) not null,
	column2 int(10),
	primary key(column1)
) engine = InnoDB;
------------------------------
create table test
(	column1 char(10) not null,
	column2 int(10),
	constraint PK_column1 primary key(column1)
) engine = InnoDB;

-- 说明：
-- 1、Mysql中创建约束分两种方式：表级约束和列级约束
-- 2、在定义约束时，应尽量给约束命名，以便后续的修改和删除操作。
-- 3、当前的Mysql版本只能给表级约束命名，无法给列级约束命名，因此，推荐使用表级完整性约束中的 'constraint' 创建语法。

创建约束

-- 在表 tb_student 中创建主键约束、候选键约束（唯一性约束）、外键约束、check约束
create table tb_student
(	studentNo char(10), 
	studentName varchar(20) not null,  --非空约束
	score int(5),
	birthday date,
	nation varchar(10) default '汉',   --默认约束
	classNo char(6),
	className varchar(20),
	constraint PK_student primary key(studentNo),  --字段studentNo为主键约束
	constraint UQ_class unique(className),  --字段className为唯一性约束
	constraint CK_score check(score>=0 and score<=100),  --字段score需要满足[0,100]的check约束
	constraint FK_class foreign key (classNo) references tb_class(classNo)   --字段classNo为此表的外键约束，同时也是表tb_class的主键约束
	on update restrict --拒绝修改被参照表（tb_class）中的字段classNo
	on delete cascade  --如果删除被参照表（tb_class）中字段classNo的某个编号时，相应的删除tb_student表中的数据
) engine = InnoDB;

更新约束

-- 删除约束
alter table tb_student drop foreign key FK_class;	--删除外键约束
alter table tb_student drop primary key PK_student;	--删除主键约束
alter table tb_student drop index UQ_class;	--删除候选键约束（唯一性约束）
alter table tb_student drop index CK_score;	--删除check约束

--添加约束
alter table tb_student add constraint PK_student primary key(studentNo);	--添加主键约束
alter table tb_student add constraint FK_class foreign key(classNo) references tb_class(classNo);	--添加外键约束
alter table tb_student add constraint UQ_class unique key(className);	--添加候选键约束

---

课后习题

---

1、请使用命令行客户端创建一个名称为db_sp的数据库

--创建数据库db_sp
create database db_sp
	default character set gb2312
	default collate gb2312_chinese_ci;

2、在数据库db_sp中创建S表、P表、J表和SP表，定义完整性

--切换当前数据库
use db_sp;

--创建S表
create table S
(	SNO CHAR(10),
	SNAME VARCHAR(20) NOT NULL,	--非空约束
	STATUS  INT(5),
	CITY VARCAHR(20),
	CONSTRAINT PK_SNO PRIMARY KEY(SNO),	--主键约束
	CONSTRAINT UQ_SNAME UNIQUE(SNAME),	--候选键约束（唯一性约束）
	CONSTRAINT CK_CITY CHECK(CITY!="London" OR STATUS=20)	--check约束
) ENGINE = InnoDB;

--创建P表
create table P
(	PNO CHAR(10),
	PNAME VARCHAR(20),
	COLOR CHAR(10),
	WEIGHT FLOAT,
	CONSTRAINT PK_PNO PRIMARY KEY(PNO),	--主键约束
	CONSTRAINT CK_COLOR CHECK(COLOR IN ('Red','Yellow','Green','Blue'))	--check约束
) ENGINE = InnoDB;

--创建SP表
create table SP
(	SNO CAHR(10),
	PNO CHAR(10),
	JNO CHAR(10),
	QTY INT(10),
	CONSTRAINT PK_SPNO PRIMARY KEY(SNO,PNO),	--主键约束，多个字段用逗号隔开
	CONSTRAINT FK_SNO FOREIGN KEY(SNO) REFERENCES S(SNO),	--外键约束
	CONSTRAINT FK_PNO FOREIGN KEY(PNO) REFERENCES P(PNO)	--外键约束
) ENGINE = InnoDB;