第3章 SQL素材定义语句
3.0 初始数据库
3.1 MySQL 概述
️MySQL的数据库构成
️
系统数据库
- 指MySQL安装配置结束后，系统自动创建的一些数据库
- information schema，元数据库，保存了MySOL服务器所有数据库的信息
- mysql，主要存储数据库的用户、权限设置等控制和管理信息
- performance_schema，存储数据库服务器的性能参数，用于监控服务器的资源消耗、资源等待等情况
- sys，数据来自performance_schema，目标是把performance schema的复杂度降低
用户数据库
- 指用户根据实际需求手动创建的数据库
️存储引擎
- 用于指明如何存储数据，如何为存储的数据建立索引，以及如何更新、查询素材的机制
- MySOL数据库提供了多种存储引擎
- MySOL提供了插件式的存储引擎，存储引擎是基于表的
- MySOL常用的存储引擎有InnoDB存储引擎以及MyISAM存储引擎
- 查看存储引擎的方法
- SHOW ENGINES; 或 SHOW ENGINES \G
- (show engines 或 show engines\g)
- SHOW variables like '%%storage_engine%';
️InnoDB存储引擎
- MySQL的数据库引擎之一
- 为处理巨大数据量时所需的最大性能而设计
- 是事务(Transaction)安全的，并且支持外键(foreignkey)
- 支持OLTP，适用于执行大量更新操作(增、删、改)的基本表
- 帮助全文搜索
- InnoDB表空间分为共享表空间和独立表空间。一个表空间只能属于一个数据库
️MyISAM存储引擎
- MySQL的数据库默认引擎之一
- 基于传统的ISAM（有索引的顺序访问方法)类型，是存储记录和资料的标准技巧
- 具有检查和修复表格的大多数器具
- 表格可以被压缩，支持全文搜索
- 适用于执行大量查询操作的表
- 不是事务安全的，不支持外键
️Memory存储引擎
- 将表中的数据存放在内存中
- 适用于存储临时数据的临时表
- 默认使用哈希(hash)索引
️字符集
- 默认情况下，MySQL使用的字符集为Iatin1(西欧ISO88591字符集的别名)
- MySOL服务器可以支持多种字符集，在同一台服务器、同一个数据库甚至同一个表的不同字段都可以使用相同的字符集
- MySOL包括字符集和校验规则两个概念，字符集和校验规则是一对多的关系，两个不同的字符集不能有相同的校验规则，每个字符集有一个默认的校验规则
- 目前，MySOL选择UTF-8(Unicode)
️字符集的设置
- MySOL字符集和校验规则有4个级别的默认设置
- 服务器级(在MySOL服务启动时确定)
- 数据库级(在创建数据库时指定)
- 表级(在创建表时指定)
- 列级(在创建表时指定)
- SHOW variables like 'character_set%';
- SHOW variables like 'collation%';
3.2 SQL内容定义语句
️SQL的数据定义语句
️SQL语句格式备注
- 用[ ]括起来的内容表示是可选的;
- [，…..n]表示重复前面的内容;
- 用<>括起来表示在实际编写语句时，用相应的内容替代；
- 用{}括起来表示是必选的;
- 类似A|B的格式，表示A和B只能选择一个，不能同时都选。
️创建数据库
命令格式为：
CREATE{DATABASE | SCHEMA} create {database | schema}
[IF NOT EXISTS] <database_name> [if not exists]
[ [DEFAULT] CHARACTER SET charset_name] [ [default] character set charset_name]
[ [DEFAULT] COLLATE collation_name] [ [default] collate collation_name]
️例：使用CREATE DATABASE创建一个Test1数据库
CREATE DATABASE Test1;
CREATE DATABASE IF NOT EXISTS Test1;
CREATE DATABASE Test1 CHARACTER SET UTF8MB4;
️使用数据库
命令格式为:
SHOW DATABASES;#查看当前所有的数据库
SELECT DATABASE（）;#查看当前正在使用的数据库
SHOW CREATE DATABASE <database_name>;
#查看指定数据库的创建信息
USE <database_name>;#启用/切换数据库
️修改数据库
命令格式为：
ALTER{DATABASE | SCHEMA} <database_name>alter{database| schema} <database_name>
[[DEFAULT]CHARACTER SET charset_name] [[default] character set charset_name]
[[DEFAULT]COLLATE collation_name]; [[default] collate collation_name]
说明:该命令用于更改数据库的全局特性，用户必须有数据库修改权限，才可以应用该命令修改数据库
️删除数据库
命令格式为：
DROP DATABASE [IF EXISTS] <database_name>;
否存在，则删除不存在的数据库时，体系会提示出错。就是说明:若不使用IF EXISTS判断指定的数据库
️教学管理系统的功能描述
- 每个学院包含多个专业，每个专业包含多个班级，每个班级有多名学生
- 每个学院有多位教师
- 每名学生要学习多门课程，每门课程也会有多名学生选修
- 每位教师每个学期要以班级为单位讲授多门课程，每门课程也会有多位教师讲授
️教学管理系统的基本表
学院表(Department)，学院的主要信息
专业表(Major)，专业的基本信息
学生表(Students)，学生的基本信息
教师表(Teachers)，教师的基本信息
课程表(Courses)，课程的基本信息
选课表(Reports)，学生的选课信息
授课表(Tutors)，教师的授课信息
️教学管理系统
- 学院表(Department)
- 专业表（Major)
- 学生表（Students)
教师表（Teachers)
课程表（Courses）
选课表（Reports）
授课表（Tutors）
️创建基本表
命令格式为：
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] <table_name>
(create [temporary] table [if not exists] <table_name>
(<列名><数据类型> [<列级完整性约束条件>]
[,...n]
[,<表级完整性的约束条件>|
);
<列级完整性约束条件>::=
[NOT NULL | NULL] [DEFAULT<default_value>]
[AUTO_INCREMENT] [{UNIQUE | PRIMARY} KEY] [auto_increment] [{unique
[COMMENT'字段注释'] [属性外键约束]
<表级完整性约束条件>::=
[{UNIQUE | PRIMARY} KEY (属性列1 [,...n])]
[属性外键约束]
[索引定义]
️MYSQL支持的数据类型
tinyint smallint integer bigint float double
char varchar date time year datetime
enum set
️例题 创建学生基本表Students
CREATE TABLE Students
(Sno CHAR(12) PRIMARY KEY,
Sname VARCHAR(4) NOT NULL,
Sgender ENUM('男'，'女'） DEFAULT'男'，
Sage INT,
Dno CHAR(2),
Sclass CHAR(10)
Mno CHAR(4)
);
️例题 创建选课表Reports
CREATE TABLEReports
(Sno CHAR(12),
Cno CHAR(8),
Racademicyear YEAR,
Rterm CHAR(1),
Grade TINYINT,
PRIMARY KEY(Sno,Cno)
);
️修改基本表
- SQL语言用ALTER TABLE语句修改基本表，其一般格式为：
ALTER [IGNORE] TABLE <table_name>
<更新说明>;
- 向已有的表中添加列
ALTER TABLE<表名>
ADD [COLUMN] <新列名><数据类型>[列级完整性约束条件]
[FIRST|AFTER<列名>];
- 修改现有表中的列
ALTER TABLE<表名>
MODIFY [COLUMN]<列名><数据类型>[DEFAULT 默认值]
[FIRST|AFTER<列名>];
- 删除现有表中的列
ALTER TABLE<表名>
DROP [COLUMN]<列名>
- 重命名现有表中的列
ALTER TABLE<表名>
CHANGE[COLUMN]<列名><新列名><数据类型>
[FIRST|AFTER 列名];
例：将基础表Students中的字段Sage改为Sbirth,即出生日期，类型改为DATE。
ALTER TABLE Students
CHANGE COLUMN Sage Sbirth DATE;
例：在基本表Courses中增加一个新的字段Pre_Cno,即前序课程编号，数据类型为CHAR(8)
ALTER TABLE Courses
ADD Pre_Cno CHAR(8);
例：将基本表Students中的字段Dno,即学院编号，设置为外键，参考表为Department.
ALTER TABLE Students
ADD CONSTRAINT FK_Stu_Dno FOREIGN KEY(Dno)
REFERENCES Department(Dno);
删除基本表
- DROP TABLE[IF EXISTS] <表名>;
- 注意：
- 在MySQL中，当一张数据表没有与其他任何数据表形成关联关系时，可以将当前数据表直接删除
- 数据和结构都被删除
- 所有正在运行的相关事务被提交
- 所有相关索引被删除。
- 因此执行删除基本表的操作一定要格外小心，
索引
- 索引是对数据库表中一个或多个列的值进行排序的逻辑结构;
- 每个索引都有一个特定的索引码与表中的记录关联;
- 使用索引的目的:能够快速访问表中的记录，提高查询速度；
- 不同的存储引擎定义了每一个表的最大索引数量和最大索引长度；
- MySQL有两种索引方式:B树(B-tree)索引和哈希(Hash)索引;
- 何时采用索引由DBMS确定。
索引的分类
- 聚簇索引
- 每个表只能有一个聚簇索引;
- 一致的。所以，应该在创建任何非聚簇索引之前创建聚簇索引。就是聚簇索引改变数据的物理排序方式，使得数据行的物理顺序和索引中的键值顺序
- 非聚簇索引
- 如果创建索引时没有指定索引类型，默认情况下为非聚簇索引;
- 每个表最多可以创建64个非聚簇索引(InnoDB中);
- 包含在同一个索引中的列的最大数目为16;
- 最好在唯一值较多的列上创建非聚簇索引。
- 普通索引
- 不附加任何限制条件的索引。
- 唯一性索引
- 运用UNIQUE参数设置;
- 限制该索引的值必须是唯一的;
- 主键是一种特殊的唯一性索引。
- 全文索引
- 使用FULLTEXT参数设置;
- 只能创建在CHAR、VARCHAR或TEXT类型的字段上;
- 默认情况下，不区分大小写。
- 单列索引
- 在单个字段上创建索引。
- 多列索引
- 在多个字段上创建一个索引;
- 只有查询条件中使用了这些字段中第一个字段时，索引才会被采用;
- 空间索引
- 使用SPATIAL参数设置;
- 只能创建在空间数据类型上;
- 只有MyISAM存储引擎拥护空间检索，且索引字段不能为空值。
使用索引的准则
- 一般情况下，应在经常被查询的列上创建索引，以便提高查询速度。但是，索引将占用磁盘空间，并降低执行数据记录的速度。
- 创建索引的列
- 主关键字所在的列
- 外码所在的列或在连接查询中经常采用的列
- 按关键字的范围值进行搜索的列
- 按关键字的排序顺序访问的列
- 不使用索引的列
- 在查询中很少涉及的列
- 包含较少的唯一值
- 更新性能比查询性能更关键的列
建立索引
- 直接创建索引
- 在创建表的时候创建索引，其语句格式为:
CREATE TABLE <table_name>
(<列名><数据类型>[<列级完整性约束条件>]
[,....n]
INDEX |<索引名>|(列名 [ASC |DESC][,….n])
);
- 在已存在的表上创建索引，其语句格式为:
CREATE [UNIQUE] INDEX<索引名>
ON<表名>(<列名> [ASC |DESC]
[,...n]);
- 修改表结构时创建索引，其语句格式为:
ALTER TABLE <table_name>
ADD INDEX |<索引名>|(列名 [ASC|DESC] [,….n]);
- 间接创建索引
- 在创建主键约束时，系统自动创建了一个唯一性的聚簇索引。
- 在创建唯一性键约束时，系统自动创建了一个唯一性的非聚簇索引
- 注意:主键约束或唯一性键约束的优先级高于使用CREATEINDEX语句创建的索引
删除索引
DROP INDEX <索引名> ON <表名>;
浙公网安备 33010602011771号