1.定义数据库
1.1 创建数据库:创建数据库是在系统磁盘上划分一块区域用于数据的存储和管理。
# 基本语法:
create {database | schema} [if not exists] db_name
default character set = charset_name
default collate = collation_name
# db_name:数据库名
# if not exists:创建数据库前进行判断数据库是否已存在,不存在则创建
# character set:指定数据库字符集()character
# charset_name:字符集名称,简体中文字符集:gb2312
# collate:指定字符集的校对规则
# collation_name:校对规则名称,简体中文字符集的校对规则为gb2312_chinese_ci
# 示例:创建一个名为db_school的数据库
> create database db_school
> default character set gb2312
> default collate gb2312_chinese_ci;
1.2 选择与查看数据库
# 选择数据库
> use db_name;
# 查看数据库
show {database | schemas};
# 数据库系统表介绍:
# mysql:用户访问权限
# information_schema:保存关于mysql服务器所维护的所有他数据库的信息,如:数据库名,数据库的表,表字段的数据类型与访问权限。
# performance_schema:主要用于收集数据库服务器性能参数等
# test:用户利用该数据库进行测试工作
1.3 修改数据库
# 修改数据库
# mysql中,数据库的默认字符集为latin1,默认校对规则为lation1_swedish_ei。使用ALTER DATABASE或ALTER SCHEMA修改数据库的默认字符集和默认校对规则。
alter {database | schema} [db_name]
default character set = charset_name
default collate = collation_name
# alter {DATABASE | SCHEMA}:修改数据库的全局特性,这些特性存储在数据库目录中的db.opt文件中。
# 使用alter {DATABASE | SCHEMA}命令时,用户必须具有相应的权限。
# 数据库名称可以被省略,表示修改当前数据库
# 示例:
>alter DATABASE db_school
>default character set gb2312
>default collate gb2312_chinese_ci;
1.4 删除数据库
# 删除数据库是将已创建的数据库文件夹从磁盘空间上清除
> drop { database | schema } [if not exists] [db_name];
1.5 退出数据库
> \q | exit
1.6 查看当前登录的用户
> select user();
1.7 删除用户
> drop user username;
1.8 显示所有的数据库
> show databases;
2.定义表
2.1 数值类型
# BIT():位字段类型,范围为1-64,默认为1
# TINYINT():很小的整数,带符号范围为-128-127,无符号范围为0-255
# BOOL.BOOLEAN():TINYINT(1)的同义词
# SMALLINT():小的整数,带符号范围为-32768-32767,无符号的范围为0-65535
# MEDIUMINT():中等大小的整数,带符号范围为-8388608-8388607,无符号范围为0-16777215
# INT():普通大小的整数,带符号范围为-2147483648-2147483647,无符号范围为0-4294967295
# INTEGER():INT的同义词
# BIGINT():大整数,带符号范围为-9223372036854775808-9223372036854775807,无符号范围为0-18446744073709551615
# DOUBLE(M,D):普通大小的浮点数,M是小数总位数,D是小数点后面的位数
# DECIMAL(M,[D]):压缩的浮点数,M是小数位数(精度)的总数,D是小数点(标度)后面的位数,
# DEC():DECIMAL同义词
# unsigned不允许负值。
# zerofill:当插入mysql中该字段的值的长度小于定义的长度时,会在数值前面补全相应数据的0。
2.2 日期和时间类型
# DATE:日期型,范围:1000-01-01~9999-12-31
# DATETIME:日期和时间组合,范围:1000-01-01 00:00:00~9999-12-31 23:59:59
# TIMESTAMP():时间戳,范围:1970-01-01 00:00:00到2037年
# TIME:时间型,范围:-838:59:59~838:59:59
# YEAR(2|4):两位或四位格式的年,默认是四位格式,四位格式范围:19012155,两位格式范围:7069
# mysql时间格式显示为:YYYY-MM-DD HH:MM:SS,同时允许使用字符串或数字为类型分配值
2.3 字符串类型
# CHAR()/CHARACTER():固定长度的字符数据类型,用于保存以文本格式存储的信息,范围:0~255
# VARCHAR():可变长的字符数据类型,用于保存以文本格式存储的信息,范围:0~65535
# TINYTEXT():最大长度为255(28-1)字符的TEXT列
# TEXT():最大长度为255(216-1)字符的TEXT列
2.4 创建表
# 基本语法:
CREATE TABLE tb_name(
字段名1 数据类型 [列级完整性条件] [默认值]
字段名2 数据类型 [列级完整性条件] [默认值]
)[ENGINE=引擎类型];
# 示例:创建tb_student表
create table tb_student(
studentNo CHAR(10) NOT NULL UNIQUE, # 学号
studentName VARCHAR(20) NOT NULL, # 姓名
sex CHAR(2), # 性别
birthday DATE, # 出生日期
native VARCHAR(10), # 籍贯
classNo CHAR(6), # 所属班级
)ENGINE=InnoDB;
# 约束条件
# NULL:允许在插入记录时不给出该列的值。
# NOT NULL:插入或更新数据时,该列必须要有值
# AUTO_INCREMEN:自增长约束,初始值为1,只要整型列才能设置此属性,每个表只能设置一个自增长约束。
# DEFAULT:向数据表中插入数据时,如果没有明确给出某个字段所对应的值,则允许为此字段指定一个值。
# ENGINE=InnoDB:为新建表指定存储引擎。
# 存储引擎:如何存储数据,如何为存储的数据建立索引和如何更新、查询数据等技术的实现方法。Oracle、SQLserver等数据库只要一种存储引擎,而MYSQL提供了多种存储引擎(核心),
# SHOW ENGINES:查看系统所支持的引擎类型和默认引擎,InnoDB是默认引擎。
2.5 查看表
# 查看当前数据库所有数据表的名称;
> show tables;
# 查看非当前数据库的表的名称
> show tables { from | in } db_name;
# 查看数据表的基本结构
# 方法一:
> SHOW COLUMNS {FROM | IN} tb_name [{FROM | IN} db_name];
# 方法二(常用):
> {DESCRIBE | DESC} tb_name;
# 查看数据库的详细结构()原始SQL:
> show create table tb_name;
2.6 修改表--添加字段
alter table tb_name add [collmn] 新字段名 数据类型 [约束条件] [first | after 已有字段名]
# [约束条件]:用于字段字段不为空,字段的默认值,主键,候选键等约束
# [FIRST | AFTER 已有字段名]:指定新增字段在表中的位置;
# FIRST表示将新添加的字段设置为表的第一个字段;
# AFTER 已有字段名表示将新添加的字段设置为数据表的最后一列;
# 示例:向表中添加一个INT型字段id,要求不能为NULL,取值唯一且自动增加,并将字段添加到表的第一个字段
alter table tb_student add column id INT NOT NULL UNIQUE AUTO_INCREMENT FIRST;
# 示例:向表中添加一个VARCHAR(16)型字段department,要求设置其默认值为'信息学院',并将字段添加到nation列之后
alter table tb_student add colum department varchar(16) default "信息学院" AFTER nation;
2.7 修改表--修改字段
ALTER TABLE tb_name CHANGE [COLUMN] 原字段名 新字段名 数据类型 [约束条件];
# CHANGE [COLUMN]:可同时修改表中指定列的名称和数据类型。可同时添加多个CHANGE [COLUMN],用逗号隔开。
# # 示例:将表中字段birthday重命名为age,并将数据类型修改为TINYINT,允许为NULL,默认值为18
alter table tb_student change column birthday age TINYINT NULL DEFAULT 18;
ALTER TABLE tb_name ALTER [COLUMN] 字段名 {SET | DROP} DEFAULT;
# ALTER [COLUMN]:可以修改或删除表中指定列的默认值
# 示例:将表中tb_student的字段department的默认值删除
alter table tb_student alter column department drop default;
ALTER TABLE tb_name MODIFY [COLUMN] 字段名 数据类型 [约束条件] [FIRST | AFTER 已有字段名];
# MODIFY [COLUMN]:修改指定列的数据类型
# [FIRST | AFTER 已有字段名]:修改指定列在表中的位置
# 示例:将表中的字段department的数据类型修改为VARCHAR(20),取值不能为空,并将字段移到字段studentName之后
alter table tb_student MODIFY COLUMN department VARCHAR(20) NOT NULL AFIER studentName;
2.8 修改表--删除字段
alter table tb_name DROP [COLUMN] 字段名;
# 示例:删除表中id字段
alter table tb_student drop column id;
2.9 重命名表
alter table 原表名 rename to 新表名;
# 示例:将tb_student表名修改为backup_tb_student。
alter table tb_student rename to beckup_to_student;
RENAME TABLE 原表名1 TO 新表名1,原表名2 TO 新表名2,.....;
# 示例:将backup_tb_student表名修改为tb_student。
rename table backup_tb_student to tb_student;
2.10 删除表
drop table [if exists] 表1,表2,.....;
# 示例:删除表tb_student;
drop table tb_student;
3.数据完整性约束
关系模型的完整性约束是对关系的某种约束条件。其主要是为了保障数据的正确性和一致性,防止数据库存在不符合语义的,不正确的数据。
3.1 定义实体完整性
实体完整性规则指关系的主属性不能取空值,即主键和候选键在关系中所对应的属性不能为空。
- 主键约束:主键是表中某一列或某些列构成的一个组合。其中,由多个列组合而成的主键又称为复合主键。
# 主键约束条件:
# 每一个表只能定义一个主键。
# 主键的值,也称为键值,必须能唯一标识表中的每一行记录,且不能为NULL。
# 复合主键不能包含不必要的多余列。
# 一个列名在复合主键的列表中只能出现一次。
# 示例:
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),
# PRIMARY KEY(studentNo) # 确定主键(方法二)
)ENGINE=InnDB;
- 完整性约束的命名:命名完整性约束的方法是,在各种完整性约束的定义说明之前加上关键字CONSTRAINT和该约束的名字。
CONSTRAINT<指定的约束名>
{
|PRIMARY KEY(主键字段列表)
|UNIQUE(候选键字段列表)
|FOREIGN KEY(外键字段列表) REFERENCES tb_被参照关系(主键字段列表)
|CHECK(约束条件表达式)
};
# 示例:
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=InnDB;
- 候选键约束:候选键的值必须唯一,且不能为NULL。
# 定义在列级的完整性约束
CREATE TABLE tb_class(
classNo CHAR(6) PRIMARY KEY,
className VARCHAR(20) NOT NULL UNIQUE,
department VARCHAR(30) NOT NULL,
grade SMALLINT,
classNum TINYINT
)ENGINE=InnDB;
# 定义在表级的完整性约束
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 UNIQUE(className)
)ENGINE=InnDB;
# mysql中PRIMARY KEY与UNIQUE之间的区别:
# - 一个表只能创建一个PRIMARY KEY,但可以定义多个UNIQUE.
# - 定义为PRIMARY KEY的列不允许有空值,但UNIQUE的字段允许。
# - 定义约束时,系统会自动产生索引。
3.2 定义参照完整性
外键是一个表中的一个或一组属性,外键对应另一张表的主键。主要作用是保证数据引用的完整性,一致性。外键所属的表称为参照关系,相关联的主键的表称为被参照关系。参照完整性规则定义的是外键与主键之间引用的规则,即外键的取值或者为空,或者等于被参照关系中某个主键的值。
# 以列级完整性约束定义外键
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 '汉',
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;
- RESTRICT:限制策略,即当要删除或修改被参照表中被参照列上且在外键中出现的值时,系统拒绝对被参照表的删除或修改操作。
- CASCADE:级联策略,即当被参照表中删除或修改记录中,自动删除或修改参照表中匹配的记录。
- SET NULL:置空策略,即当从被参照表中删除或修改记录时,设置参照表中与之对应的外键列的值为NULL。
- NO ACTION:表示不采取实施策略,即当一个相关的外键值在被参照表中时,删除或修改被参照表中键值的动作不被允许。
- SET DEFAULT:默认值策略,即当从被参照表中删除或修改记录行,设置参照表中与之对应的外键列的值为默认值。
# 定义外键时定义相关的参照动作
create table tb_student(
stuudentNo 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, # 不允许修改classNo
ON DELETE CASCADE # 级联删除
)ENGINE=InnoDB;
3.3 用户定义的完整性
- 设置非空约束:非空约束指字段的值不能为空,非空约束的定义可以使用CREATE TABLE或ALTER TABLE语句。,在列后面加上NOT NULL。
- check约束:CHECK约束是在创建表(TABLE)或修改表(ALTER TABLE)的同时,根据用户的实际完整性要求来定义的。列级CHECK约束定义的是单个字段需要满足的要求。表级CHECK约束定义的是表中多个字段之间应满足的条件。
# 示例:创建表,自定义约束:每16个课时对应1学分
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) # 自定义约束:每16个课时对应1学分。
);
# 示例:创建成绩表,要求成绩取值只能在0-100之间
create table tb_score(
studentNo char(10), # 学号
courseNo char(6), # 课程号
score FLOAT check(score>=0 and score<=100) , # 成绩,自定义约束
CONSTRAINT PK_score PRIMARY KEY(studentNo, courseNo),
CONSTRAINT FK_score1 FOREING KEY(studentNo) REFERENCES tb_student(studentNo),
CONSTRAINT FK_score2 FOREIGN KEY (courseNo) REFERENCES tb_course(courseNo),
);
4.更新完整性约束
4.1 删除外键约束
alter table (表名) drop foreing key (外键约束);
4.2 删除主键约束
drop table (表名) drop primary key;
4.3 删除候选键约束
# 如果没有给候选键命名,使用DROP INDEX子句删除的是定义候选键的字段名
alter table 表名 drop index 候选键字段名;
# 如果使用了CONSTRAINT子句给候选键命名,使用DROP INDEX子句删除的是约束名
alter table 表名 drop index 约束名;
4.4 添加主键约束
alter table 表名 add [CONSTRAINT 约束名] PRIMARY KEY (主键字段);
# []内容可省略
4.5 添加外键约束
alter table 表名 add [CONSTRAINT 约束名] foreing key (外键字段名) references 被参照表(主键字段);
4.6 添加候选键约束
alter table 表名 add [CONSTRAINT 约束名] UNIQUE KEY (字段名);