MySQL基础（二）

SQL指令

• 本篇总结MySQL关系模型，先从较为简单和常用的指令开始

常见指令

• SQL 对大小写不敏感：SELECT与 select是相同的

• 多条语句时，每条 SQL 语句的末端使用分号；

• 命令行中每句结尾必须使用;， 使用--或#表示单行注释

• 使用下面的数据学习基本MySQL指令：

  +----+--------------+---------------------------+-------+---------+
  | id | name         | url                       | alexa | country |
  +----+--------------+---------------------------+-------+---------+
  | 1  | Google       | https://www.google.cm/    | 1     | USA     |
  | 2  | 淘宝         | https://www.taobao.com/   | 13    | CN      |
  | 3  | 菜鸟教程      | http://www.runoob.com/    | 4689  | CN      |
  | 4  | 微博         | http://weibo.com/         | 20    | CN      |
  | 5  | Facebook     | https://www.facebook.com/ | 3     | USA     |
  +----+--------------+---------------------------+-------+---------+
  

• 就不具体说命令结构了，没劲，直接看例子

  -- ----------------------------数据操作CURD-------------------------
  -- 创建（Create）、更新（Update）、检索（Retrieve）和删除（Delete）
  -- SELECT - 从数据库中提取数据
  SELECT name,country FROM Websites;
  SELECT DISTINCT country FROM Websites;	// 去掉 "country" 列重复值
  select id,name,country from websites where id=1;
  
  -- UPDATE - 更新数据库中的数据
  UPDATE Websites SET alexa='5000', country='USA' WHERE name='roy';
  
  -- DELETE - 从数据库中删除数据
  DELETE FROM Websites WHERE name='百度' AND country='CN';
  
  -- INSERT INTO - 向数据库中插入新数据
  -- 可以不指定字段，按顺序
  INSERT INTO Websites (name, url, country)
  VALUES ('stackoverflow', 'http://stackoverflow.com/', 'IND'),
   	   ('stackoverflow', 'http://stackoverflow.com/', 'IND');
  
  
  -- --------------------------库表操作-------------------------
  -- CREATE DATABASE - 创建新数据库
  CREATE DATABASE IF NOT EXISTS mydbname DEFAULT CHARSET utf8 COLLATE utf8_general_ci;
  -- utf8：而不是utf-8；
  -- collate：校对集（数据比较），告诉mysql怎么比较varchar text等这些字符类型的列，
  	-- 因此COLLATE会影响到ORDER BY语句的顺序，会影响到WHERE条件中大于小于号筛选出来的结果
  	-- 会影响**DISTINCT**、**GROUP BY**、**HAVING**语句的查询结果。
  -- 会在data目录下建立对应文件夹
  
  drop database <数据库名>; 删除数据库
  -- 注意备份，删除不可逆，这是常识
  
  -- 修改数据库
  alter database exercise charset gbk; 
  -- 数据库名字不能修改，也不建议修改校对集
  
  -- 创建新表
  create table if not exists exercise.websites (id int, name varchar(10), url char(35), alexa int, country varchar(10))charset utf8;
  -- 注意指定数据库
  -- 或者进入数据库：use databasename，就不加exercise.
  -- 此时数据库目录下会生成编译后的结构文件：website.frm，即这个表的数据结构，后面详细解释
  -- 后面会说到数值类型的问题
  
  ALTER TABLE - 变更数据表（结构上的改变）
  alter table websites charset gbk;
  -- add column
  alter table websites add column new_column varchar(1) after id;
  -- 位置可以是：first/after
  alter table websites modify url varchar(66) after id;
  -- modify 修改字段属性（类型、位置等）
  alter table websites change url path char(55) first;
  -- change 更改字段名...
  alter table websites drop alexa;	-- 删除字段
  
  -- DROP TABLE - 删除表
  drop table websites;
  
  CREATE INDEX -- 创建索引（搜索键）
  DROP INDEX -- 删除索引
  rename table websites to my_websites; -- 重命名数据表
  

  如果使用中文创建数据库需要使用：set names gbk;指明当前环境字符集
  当然了，最好别用中文

  

• 查看表结构（描述创建信息），主要是desc和show create命令

• 匹配模式包括 %（可以匹配多个字符）， _（只能匹配一个字符），属于查询操作

  -- 查找所有数据库
  show databases;
  show databases like [pattern];	// 模糊查询
  show databases like 'exercise_';
  show databases like 'exercise%';	// 如果要匹配 _ 需要使用 \ 转义
  -- 查找数据表
  show tables;
  show tables like [pattern];
  -- 查看表结构
  DESC websites;	// 小写也行
  describe websites;
  show columns from websites;
  

  -- 查看创建数据库的语句
  show create database exercise;
  -- 查看创建数据表的语句
  show create table websites\g;
  show create table websites\G;
  

• SQL执行时MySQL会进行优化，所以和创建时的语句并不完全一样

• 这部分是基础指令，在下一篇会详细介绍数据库操作

字符集

-- 查看所有字符集
show character set;

-- 查看服务器默认的对外处理的字符集
show variables like 'character_set%';

-- 修改服务器认为的客户端的字符集
set character_set_client = gbk;	-- 解决服务器中文乱码

-- 修改服务器传过来的字符集
set character_set_results = gbk;-- 解决客户端显示中文乱码的问题（让服务器传gbk）

校对集

• 校对集：数据比较的方式

• 三种格式

  • _bin：二进制按位比较，区分大小写
  • _cs：case sensitive
  • _ci：case insensitive，不区分大小写

• 查看支持的校对集：show collation;

• 默认校对集：utf8_general_ci，utf8不支持_cs，使用utf8_bin和utf8_ci比较

  -- 创建表
  create table collate_bin(name char(1))charset utf8 collate utf8_bin;
  create table collate_ci(name char(1))charset utf8 collate utf8_general_ci;
  -- 插入数据
  insert into collate_bin values('a'),('A'),('b'),('B');
  insert into collate_ci values('a'),('A'),('b'),('B');
  -- 查找
  select * from collate_bin order by name;	-- 必须使用order by才能体现
  select * from collate_ci order by name;
  

• 必须在有数据之前就定义好校对集，数据生成后修改无效！

Web乱码问题

• 浏览器、服务器、数据库都有自己的编码规则，如何统一？大致了解一下
  
  • 浏览器header设置字符集
  • 服务器通过之前提到的命令设置字符集
  • PHP可改变操作系统文件字符集

数据类型（字段类型）

• 来个图直接看看：
  
• 包括：整数、小数、字符串、日期时间，记住没？

整数型

• tinyint

  使用一个字节存储，表示的状态最多为256种

• smallint

  两个字节存储

• mediumint

  三个字节存储

• int

  标准整型，4字节

• bigint

  8个字节存储（很少）

  create table my_int(int_1 tinyint, int_2 tinyint unsigned);
  -- 1 是在限定显示的最小宽度，对表示的范围没有任何影响，如果数据宽度不够，会用zerofill填充，宽度超出按原形显示
  create table my_int(int_1 tinyint(1));
  -- 使用zerofill填充会将类型自动变为unsigned，负数前面加0会变成式子
  create table my_int(int_1 tinyint(2) zerofill);
  

  

  SQL中的数值型全部默认有符号，很多时候需要使用无符号数值类型，扩大可使用范围；
  尽量结合需求给定合适的数值类型，杀鸡勿用牛刀！

小数型

• 浮点型：会丢失精度
  • float：4字节，7位精度（小数）
  • double：8字节，15位精度

  -- float(M,D) M表示总长度，D表示小数部分长度，M-D为整数部分长度
  -- 直接使用float表示没有限制
  -- 使用float(10)则是限定显示宽度
  create table my_float(f1 float, f2 float(10,2), f3 float(10,5))charset utf8;
  -- 可以使用科学计数法
  -- 66666, 1234.12, 12345.12346
  insert into my_float values(66666, 1234.123, 12345.123456);
  -- 超出范围浮点数会四舍五入，如果因为系统进位导致整数部分超出长度则是允许的！
  -- 12345700， 100000000.00（已超出8位整数）， 100000.00000（最多超一位）
  insert into my_float values(12345678.12345678, 99999999.999, 3e12);
  

• 定点型：精度固定
  • 4字节，整数和小数分开计算
  • 变长，M最大是60，D最大是30
  • 适合计算精确值

  -- 整数部分不能超出长度，小数部分随意
  create table my_decimal(f1 float(5,2), d1 decimal(5,2))charset utf8;
  -- 结果相同  999.99
  insert into my_decimal values(999.999, 999.999);
  

日期时间型

• datatime：格式：YYYY-mm-dd HH:ii:ss 范围：1000——9999年，注意符号

• date：就是datetime中的日期部分

• time：时间；可以使用负数，表示时间段

• timestamp：时间戳。常用

• year：可以是2位或4位，2位的范围在1970——2069（99年）
  

  create table my_date(d1 date, d2 datetime, d3 time, d4 timestamp, d5 year)charset utf8;
  -- 
  insert into my_date values('2020-03-29', '2020-03-29  16:45:32', '16:45:32',
                             '2020-03-29  16:45:32', 2021);
  -- 时间可以为负数，表示过去一段时间
  insert into my_date values('2020-03-29', '2020-03-29  16:45:32', '-16:45:32',
                             '2020-03-29  16:45:32', 2021);
                             -- -2表示两天前，自动变为 -64:45:32
  insert into my_date values('2020-03-29', '2020-03-29  16:45:32', 
                             '-2 16:45:32', '2020-03-29  16:45:32', 2021);
  -- year可以使用2位或4位
  -- 2069
  insert into my_date values('2020-03-29', '2020-03-29  16:45:32', 
                             '-2 16:45:32', '2020-03-29  16:45:32', 69);
  -- 1970
  insert into my_date values('2020-03-29', '2020-03-29  16:45:32', 
                             '-2 16:45:32', '2020-03-29  16:45:32', 70);
                             
  -- 更新数据，时间戳会自动改变；基于原时间增加（相当于写入时便开启了计时器）
  update my_date set d1='2025-12-31' where d5=1970;
  

• PHP中有date()函数处理时间戳，功能强大！所以不使用DB中的格式，时间都用时间戳（整型）

字符串类型

• char：定长字符串，在定义的时候就确定了最终的长度

  • 使用方式：char(L)：表示在utf8环境下需要L*3个字节，最大可为255字节
  • utf8中三个字节存储一个汉字，一个字节存储一个英文（包括符号）
  • 这里一个字符可以认为是一个汉字，当然，如果变为gbk就是两个字节一个字符

• varchar：变长字符串，分配空间时按照指定分配，但实际根据具体数据而定

  • 最大可为65535个字节，一般会多出1到2个字节来确定存储的实际长度
  • 因此永远不可能达到最大长度，或者说最多65533/65534个
  • varchar(10)：实际长度可能为10*3+1=31字节

  即char和varchar在数据超过给定范围时都不能写入！

  char：磁盘空间比较浪费，但效率高，因为没不需要动态（身份证，电话号码等）

  varchar：节省空间（姓名等）

  可以说Length决定了汉字个数！字符是一个宽泛的概念，一个汉字，一个字母，一个数字都可以叫一个字符；

  create table my_varchar_u(name varchar(21845))charset utf8;	-- 21845*3=65535
  -- gbk一个字符（汉字）两个字节
  create table my_varchar_g(name varchar(32767))charset gbk;	-- 32767*2=65535
  

  

• 一般varchar分配不超过255个字符，再多则使用text

• 如果有一个字段允许为空，那么系统会从整个记录中保留一个字节来存储null；不允许为空即insert时必须传值

  -- 若想释放null所占用的字节，必须保证所有字段不为空
  -- 加上个tinyint正好65535个字节！也就是说65535字节是mysql记录的最大长度了
  -- 21844*3+2=65534
  create table my_varchar_u1(name varchar(21844) not null,
                             age tinyint not null)charset utf8;
  -- 32766*2+2=65534
  create table my_varchar_g1(name varchar(21844) not null,
                            age tinyint not null)charset gbk;
  

• text

  • 通常超过255个字符都用文本字符串存储，根据存储的数据格式分为text和blob
  • text：存储文字（实际上存储资源路径，效率高）
  • blob：存储二进制数据（一般不用）

  text文本字符串存储的内容不占用记录长度，额外存储！

  -- 21841*3+2=65525+10=65535
  -- text还是要占用10个字节记录长度的！
  create table my_text(name text not null,
                      hobby varchar(21841) not null)charset utf8;
  

• enum

  • 事先将所有可能出现的结果都设计好，实际存储的必须出现在其中，选项量最大65536个

  -- 创建枚举表：字段gender
  create table my_enum(gender enum('男','女','保密'))charset utf8;
  insert into my_enum values('男'),('保密');	-- 插入多条有效数据
  

  • 规范数据格式（无需用户输入）
  • 节省存储空间：类似于单选框，将数据按序号转换存取，但也因此效率会下降

  -- 如果是字符串+0则会显示转换后的数字
  select gender+0, gender from my_enum;
  -- 因此可以使用数值插入
  insert into my_enum values(1),(2);
  

  

• set

  • 类似枚举，但存储的是二进制数字

  create table my_set(girls set('a','b','c','d','e','f'))charset utf8;
  

  • 集合中每一个元素都对应一个二进制位

  -- （貌似不行，只能插入a）
  insert into my_set values('a, c, e'); -- 一行可以插入多个数据
  insert into my_set values('a'),('c'),('e'); -- 插入三行
  -- 可以使用对应二进制数值插入
  insert into my_set values(1);	-- a
  -- 集合插入是没有顺序的
  insert into my_set values(63);	-- a,b,c,d,e,f	即111111
  select girls+0, girls from my_set;
  

  

• 集合在本质上就是用整型存数据的！强大在于规范数据和节省空间，同样，对于PHP来说增加了维护成本，无法判断在数据库的存储形式！

列属性

• 真正约束字段的是数据类型，但是太单一，于是有了列属性，也叫完整性约束
• 包括空属性、列描述、默认值、主键、外键

空属性

• NULL or NOT NULL

• 如果不指定，字段默认为NULL（可以不传值）

• 真实开发尽量不让数据字段为NULL，空数据没有意义，不能参与运算

  -- 创建班级表
  create table my_class(name varchar(10) not null, 
  	room varchar(10) null)charset utf8;-- 可以为空
  

列描述

• comment，字段注释，让代码更易读

  create table my_teacher(name varchar(10) not null comment '老师姓名',
  	age tinyint(2) not null comment '老师年龄')charset utf8;
  

默认值

• default，不传值时使用

  -- 下面创建表的语句在DOS执行不能成功设置enum！看来是命令行的问题！
  create table my_default(name varchar(10) not null,
                         age tinyint unsigned default 18,
                         gender enum('男','女','保密') default '男')charset utf8;
  insert into my_default values('Roy', 22, default);
  

• 默认值很多时候可以带来方便！

主键

• primary key：一张表中只能有一个字段定义为主键，可以约束该字段里面的数据不重复

  -- 添加主键
  create table my_prim(id int(2) primary key comment '身份证号',
                      name varchar(10) not null comment '姓名')charset utf8;
  -- id自动设置为not null
  

• 复合主键

  create table my_prim1(stuId char(10) comment '学号',
                       classId int(6) comment '课程号',
                      primary key(stuId,classId))charset utf8;
  -- 学号和课程号不能同时重复
  -- 即一个学号可以选多个课，这个课可以被多个学号选，但是不能一个学号多次选相同的课！
  -- 注意，出现两个PRI只能说明是复合主键
  

  • 一般用在多对多关系中，但是这种表会拆开stu和class，新建一个关系表
    

• 额外追加主键

  create table my_prim2(id int(2)  comment '身份证号',
                      name varchar(10) not null comment '姓名')charset utf8;
  alter table my_prim2 modify id int(8) primary key comment '学号';
  alter table my_prim2 add primary key(id);	-- 特殊属性，可以使用add
  

• 删除主键

  alter table my_prim drop primary key;
  -- 更改主键
  alter table my_prim add primary key(id);
  -- 先删除，后更改，因为唯一
  

• 逻辑主键：实际情况下基本不使用真实的数据字段作为主键（学号、课程号），大部分使用逻辑性的字段，与业务没有关系；例如id

  create table my_stu(id int(8) primary key auto_increment comment '自增主键')charset utf8;
  -- 任何一个字段要做自增长必须本身是一个索引，且类型是整型，和主键搭配！
  -- Error: Incorrect table definition; there can be only one auto column and it must be defined as a key（必须和主键搭配）
  insert into my_stu values();	-- 1
  insert into my_stu values(5);	-- 5
  insert into my_stu values();	-- 6
  -- 如果设置自增长字段，则自增长失效，会从设置的地方再开始自增
  -- 可查看 show create table my_stu;
  -- 自增长偏移量为1，默认从1开始，因为所有系统的变现都是由内部的变量控制的
  show variables like 'auto_increment%';
  -- 可以修改，但都是会话级修改
  set auto_increment_increment=5;
  

  

• 这里有个常见问题，就是删除记录之后increment会默认继续跟着最大的记录值自增，这个别改，会破坏索引

  -- 删除自增长
  alter table my_stu modify id int(8);	-- 即重定义一下
  

唯一键

• unique，往往有不止一个字段需要约束为不重复，但只能有一个主键不够使啊

  create table my_uniq(id int primary key auto_increment,
                       name varchar(10) not null,
                      number int  unique comment '学号')charset utf8;
  

  
• 添加唯一键

  create table my_uniq1(id int,
                       name varchar(10) not null,
                      number varchar(8) comment '学号',
                       unique key(number) not null)charset utf8;
  -- 不为空，又唯一，傻傻分不清楚！但本质不是主键
  show create table my_uniq1;
  

  
• 追加唯一键 add unique

  alter table my_uniq1 add unique key(name);
  -- 唯一键允许为空
  insert into my_uniq1 values(1,'roy',null);
  -- 删除唯一键，不能直接drop unique key，因为有多个
  alter table my_uniq1 drop index number;	-- 唯一字段可以作为索引（索引不一定是数值哦）
  

索引

• 索引（Index）是系统根据算法（B+树），根据已有的数据建立一个文件，能够实现快速的匹配，找到表中的数据
  • 所有的数据类型都可被索引，可以人为给字段添加索引
  • 索引一旦创建，数据库会自动管理和维护
  • 索引文件可能会占用较大空间
  • 可以极大提升查询效率
    • 查询优化器基于索引，评估多种数据检索方法，选择最有效的查询计划
    • 能够加速连接、排序、分组等操作
    • 可以约束数据，保证唯一性
• 数据库中索引的形式类似图书目录，键值即目录标题 ，指针相当于页码
  • 指定的字段会生成键（非叶节点），指针记录（叶节点）指向数据位置
• 索引的分类：
  • 主键索引（默认）：primary key，随表创建，数据唯一，一个字段，非空
  • 唯一索引：unique key，数据唯一，可在多个字段建立，可空
  • 全文索引：fulltext index，对于中文作用不大，InnoDB也不支持，放弃吧
  • 普通索引：index，数据可重复，可空
• 创建原则
  • 经常更新的表，要限制索引数目，很少使用的索引及时删除drop index xxx，维护索引也是开销啊
  • 数据量大，更新少，查询多，可以建立多个索引提高效率（给查询优化器多方选择）
  • 经常需要排序、分组、联合操作的字段一定要建立索引；即join、order by、where判断
  • 在视图上创建索引
  • 避免在包含大量重复值的字段上建立索引
  • InnoDB引擎支持的索引最长键值为767字节
• 创建方法
  • 如果表已经建好了

    create [unique | spatial]index index_name on table_name(index_column_name);
    -- 例如
    create unique index stuNameIndex on students(stuName(3));
    

    • 可以使用index_column_name(length)，来限制编制索引时使用的字符长度，对于学生名字，一般前3个字符就能很好区分了嘛，减少索引文件占用的空间
    • 也可使用alter table students add primary key(sno), add index tname_index(tname)
  • 建表时索引

    create table if not exists teachers(tno char(6) not null comment '教师编号',
    	tname char(8) not null comment '教师姓名',
    	major char(10) not null comment '专业',
    	prof char(10) not null comment '职称',
    	department char(16) not null comment '所在院系部门',
    	primary key(tno),
    	unique index tname_index(tname),
    	index dep_index(department(5)));
    

    
• 删除索引
  • 可以用drop语句或者alter语句

    drop index tname_index on teachers;
    alter table teachers drop primary key|drop index tname_index|drop foreign key xxx;
    

关系

• 将实体与实体的联系反映到最终表的设计上叫做关系，表之间的关系分为三种：
  • 一对一
  • 一对多
  • 多对多
• 所有关系指的都是表与表之间的记录，后面会介绍到使用外键关联

一对一

• 一张表中的一条记录只能对应另外一张表中的一条记录
• 例如：将学生表中的常用数据和不常用数据分开，放在两张表中存储，使用关系字段相联系

一对多

• 一张表中的一条记录对应另外一张表的多条记录，此时一般在多端使用外键

多对多

• 一张表中的多条记录对应另一张表的多条记录
• 例如：老师和学生表，由于在任何一张表中增加关系字段都需要存储多个值，所以需要建立中间表，将多对多转化为了一对多关系

数据库设计

• 数据库设计是指构造优化的逻辑模式和物理结构，据此建立应用系统

基本过程

• 一般分为四个阶段
  • 需求分析
  • 概念设计
  • 逻辑设计
  • 物理设计
• 需求分析
  • 了解用户的数据需求和处理需求，按格式整理成数据说明书
  • 包括数据库所涉及的数据、数据特征、数据量和使用频率的估计
  • 对字段名及类型、保密要求、完整性约束等定义
• 概念设计
  • 对第一步的结果进行抽象与综合
  • 与DBMS无关，是面向现实场景的数据模型，应极易为用户所理解
  • 可以吸收用户参与和评议
  • 常用的方法是实体关系（E-R）图，每个实体或联系将来映射成一张表
• 逻辑设计
  • 将上一步的概念模型变为DBMS所接受的逻辑模型，概念视图变为逻辑视图
  • 主要是加上一些限制
• 物理设计
  • 设计数库的存储形式和存储路径
    
• 完成了以上步骤就可用MySQL建库建表了
  • 很抽象吧，一般也很少能走全这个流程，还是看例子

教务管理数据库

• 以教务管理数据库为例，一步步设计符合范式的数据库

范式（Normal Format）

• 是离散数学中的知识，为了解决数据的存储与优化问题，主要是减少数据冗余（凡是能够通过关系找到的数据不重复存储！）
• 是一种分层结构的规范，总共分为六层
• 由于数据库也需要解决存储空间的问题，因此引用范式来指导设计，但也需要解决效率的问题，因此不能严格的按照范式设计。也就是说范式具有指导意义但不是强制规范（空间和效率的权衡）
• 一般情况下数据库只需要考虑前三种范式
• 1NF
• 要求字段数据具有原子性，这是根据实际需求来说的，如果将数据取出之后不需再进行拆分，即满足了原子性

• 2NF

• 在存在复合主键的表中，不允许出现部分依赖问题，且满足第一范式

• 部分依赖是指表的字段不是由整个主键一起确定的

  老师	性别	代课时间（天）	开始	结束	班级	教室
  卫庄	M	30	2020-04-06	2020-05-06	N1	129
  盖聂	M	15	2020-04-10	2020-04-25	N2	410
  卫庄	M	18	2020-04-12	2020-04-30	N3	406

• 如上表所示，讲师和班级作为复合主键，但是性别只依赖讲师，教室只依赖班级，不符合第二范式（应该依赖整个 主键）

• 解决方案：

  • 将性别与讲师单独成表，将班级与教室单独成表（还是使用复合主键，但消去了部分依赖的字段）
  • 取消复合主键，使用逻辑主键：

  ID	老师	性别	代课时间（天）	开始	结束	班级	教室
  1	卫庄	M	30	2020-04-06	2020-05-06	N1	129
  2	盖聂	M	15	2020-04-10	2020-04-25	N2	410
  3	卫庄	M	18	2020-04-12	2020-04-30	N3	406

  逻辑主键没有实际意义，依赖于业务主键，但是可以保证唯一性且自增长，也提高了安全性
  在这里起到了取消复合主键的作用

• 3NF

• 不存在传递依赖问题

• 传递依赖即存在字段不直接依赖主键，而是通过某个非主键字段，最终实现依赖主键

• 如上表所示，性别依赖讲师，讲师依赖主键；教室依赖班级，班级依赖主键

• 解决方案：将传递依赖的字段使用单独的表存储

  ID	姓名	性别
  1	卫庄	M
  2	盖聂	M

  ID	班级	教室
  1	N1	129
  2	N2	410
  3	N3	406

  上面表格中，业务主键是姓名和班级，但仍要使用逻辑主键确保唯一性

  ID	讲师ID	代课时间（天）	开始	结束	班级ID
  1	1	30	2020-04-06	2020-05-06	1
  2	2	15	2020-04-10	2020-04-25	2
  3	1	18	2020-04-12	2020-04-30	3

逆规范化

• 有时为了保证效率，我们不保存其他表的主键，而是直接保存想要的数据

  ID	讲师ID	代课时间（天）	开始	结束	班级ID
  1	卫庄	30	2020-04-06	2020-05-06	N1
  2	盖聂	15	2020-04-10	2020-04-25	N2
  3	卫庄	18	2020-04-12	2020-04-30	N3

• 磁盘利用率与效率的对抗

• 接下来按照规范对教学数据库进行设计（老师来了老师来了，快坐好了！！！）

  • 需求分析，得到所需字段、类型及属性，这里只是演示一下，这个并不复杂
    • 学生：学号、姓名、性别、生日、入学成绩、电话、邮箱
    • 教师：教师号、姓名、专业、职称、院系
  • 教务管理系统E-R图：
    
  • (m,n)表示多对多关系，教师与课程之间、学生和课程之间需要建表连系
  • 每个实体（方框）按道理来说都要建表

    -- 此时在数据库安装目录下的data文件夹中已有teaching文件夹
    create database if not exists teaching charset gb2312 collate gb2312_chinese_ci;
    -- 默认的存储引擎是InnoDB，是具有提交、回滚、崩溃恢复能力的事务安全存储引擎
    

    -- 学生信息表
    create table if not exists student(
    	studentno char(10) not null comment '学号',
    	sname char(10) not null comment '学生姓名',
    	sex enum('男','女') default '男' comment '性别',
    	birthdate date not null comment '出生日期',
    	entrance int(3) null comment '入学成绩',
    	phone varchar(12) not null comment '电话',
    	Email varchar(20) not null comment '电子邮箱',
    	primary key(studentno));
    

    -- 课程信息表
    create table if not exists course(
    	courseno char(6) not null comment '课程号',
    	cname char(6) not null comment '课程名',
    	type char(8) not null comment '类别',
    	period int(2) not null comment '总学时',
    	exp int(2) not null comment '实验学时',
    	term int(2) not null comment '开课学期',
    	primary key(courseno));
    	-- show create table course\G;
    

    -- 学生成绩表
    -- studentno和couserno作为联合主键
    create table if not exists score(
    	studentno char(11) not null,
    	couserno char(6) not null,
    	daily float(3,1) default 0 comment '平时成绩',
    	final float(3,1) default 0 comment '期末成绩',
    	primary key(studentno,couserno));
    

    -- 教师信息表
    create table if not exists teacher(
    	teacherno char(6) not null comment '教师编号',
    	tname char(8) not null comment '教师姓名',
    	major char(10) not null comment '专业',
    	prof char(10) not null comment '职称',
    	department char(16) not null comment '所在院系部门',
    	primary key(teacherno));
    

    -- 教师-课程表
    create table if not exists teach_course(
    	teacherno char(6) not null,
    	courseno char(6) not null,
    	primary key(teacherno, courseno));
    

  • 对照E-R图，分析表之间的关系
    • 从：学生（学号，姓名，性别，电话，课程号，课程名称，成绩），教师（......）这些基本信息入手（假设各在一张表，需求分析时会写在一块），再按规范拆表
    • 多对多建立中间表，
    • 直到各表之间满足第三范式，即原子性、非部分依赖、非传递依赖

小结

• 表的关系和设计范式都是指导我们设计表的，这里完善一下理论，关键在与实践相结合，平时多加思考！
• 下一篇着重介绍MySQL的查询语法，sqlboy必备