数据库应用+SQL优化+Git

1.1 概念

1.1.1 什么是数据库

简而言之，就是存储数据，管理数据的仓库。
常见的数据库分为：

• 关系型数据库， Oracle、MySQL、SQLServer、Access
• 非关系型数据库， MongoDB、Redis、Solr、ElasticSearch、Hive、HBase

1.1.2 关系型和非关系型

早期发展的数据库建立在数据的紧密关系基础之上（如：父子关系、师生关系），我们称其为关系型数据库，也称为传统数据库；现今数据库建立在数据的松散关系基础之上（如：中国人和美国人、中国人和印度人、视频、音频），我们称其为非关系型数据库nosql（not only sql）。业界总在争论nosql能否干掉传统数据库，很多初学者也有这个困惑。以我来看，两者没有矛盾，它们各有特点，根据业务情况互补才是真谛。但总的来说原来关系型数据库一统天下的格局早被打破，领土不断被蚕食，规模一再的缩小，虽然无法全面被替代，但却早已风光不在，沦落到一偶之地，Oracle的衰落就是最好的证明，早期只要是全球大企业无一例外都是部署Oracle，但现在都在去Oracle化，阿里就已经全面排斥Oracle。

既然干不掉，很多传统项目的还是围绕关系型数据库的居多，所以我们先来学习关系型数据库，目前最流行的关系型数据库是MySQL。

1.1.3 关系型数据库

关系型数据库有特定的组织方式，其以行和列的形式存储数据，以便于用户理解。关系型数据库这一系列的行和列被称为表，一组表组成了数据库。用户通过查询来检索数据库中的数据，而查询是一个用于限定数据库中某些区域的执行代码。关系模型可以简单理解为二维表格模型，而一个关系型数据库就是由二维表及其之间的关系组成的一个数据集合。

1.2 Mysql数据库

1） mysql服务端，它来处理具体数据维护，保存磁盘

2） mysql客户端，CRUD新增，修改，删除，查询

1.2.1 MySQL数据存放在哪里？

在MySQL的配置文件my.ini中会进行默认配置

1.2.2 MySQL服务端

mysql-5.5.27-winx64.msi




Mysql数据库默认的编码是latin1等价于iso-8859-1，修改为utf-8



注意：配置完，mysql开始执行，最后一步出错有时仍可以使用，使用SQLyog工具测试，如不行，再执行安装程序，选择remove，删除，然后重新安装。同时注意必须是管理员权限。

1.2.3 MySQL客户端1：DOS窗口

mysql -uroot -proot

语法：mysql.exe执行文件

代表参数

-u 用户名，紧接着写的

-p 密码，紧接着写的

1.2.4 MySQL客户端2：可视化工具

1.3 数据库的结构

1.3.1 数据库结构

1.4 SQL语句

1.4.1 定义

结构化查询语言(Structured Query Language)简称SQL(发音：/ˈes kjuː ˈel/ “S-Q-L”)，是一种特殊目的的编程语言，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统；同时也是数据库脚本文件的扩展名。

SQL 是1986年10 月由美国国家标准局（ANSI）通过的数据库语言美国标准，接着，国际标准化组织（ISO）颁布了SQL正式国际标准。

1.4.2 分类

• DML（Data Manipulation Language）数据操纵语言

如：insert，delete，update，select（插入、删除、修改、检索）简称CRUD操新增Create、查询Retrieve、修改Update、删除Delete

• DDL（Data Definition Language）数据库定义语言

如：create table之类

• DCL（Data Control Language）数据库控制语言

如：grant、deny、revoke等，只有管理员才有相应的权限

• 数据查询语言（DQL:Data Query Language）：

注意：SQL不区分大小写

1.5 数据库常用操作

1.5.1 建库

• 创建数据库，数据库名称：cbg2011

create database cbg2011 DEFAULT CHARACTER SET utf8;

1.5.2 删库

• 删除名称是cbg2011的数据库

drop database cbg2011;

1.5.3 查看所有数据库

• 查看所有数据库

show databases;

1.6 表的常用操作

使用数据库：use cbg2011;

1.6.1 表设计

门店表：tb_door

订单详情表：tb_order_detail

1.6.2 创建表

• 创建tb_door表，有id,door_name,tel字段

create table tb_door(

id int primary key auto_increment,

door_name varchar(100),

tel varchar(50)

);

1.6.3 修改表

• 添加列

alter table tb_door add column see NUMERIC(7,2)

1.6.4 删除表

• 删除名称是tb_door的表

drop table tb_door;

1.6.5 查看所有表

• 查看所有表

show tables;

1.6.6 查看表结构/设计表

• 查看tb_door表结构

desc tb_door;

1.7 表记录的常用操作

1.7.1 插入记录

• 向tb_door表中插入2条记录

insert into tb_door values(null,’永和大王1店’,666);
insert into tb_door values(null,’ 永和大王2店’,888);

1.7.2 查询记录

• 查询tb_door表中的所有记录

SELECT * FROM tb_door;

1.7.3 修改记录

修改tb_door表中id为1的记录

update tb_door set tel=555 where id=1;

1.7.4 删除记录

• 删除tb_door表中id为2的数据

Delete from tb_door where id=2;

1.7.5 排序

• 将tb_door表记录按照tel排序

Select * from tb_door order by tel desc;

1.7.6 记录总数

• 查询tb_door表中的总记录数

Select count(*) from tb_door;

1.8 数据类型

1.8.1 命名规则

• 字段名必须以字母开头，尽量不要使用拼音

• 长度不能超过30个字符（不同数据库，不同版本会有不同）

• 不能使用SQL的保留字，如where,order,group

• 只能使用如下字符a_z、AZ、0~9、$ 等

• Oracle习惯全大写：USER_NAME，mysql习惯全小写：user_name

• 多个单词用下划线隔开，而非java语言的驼峰规则

1.8.2 字符

• char长度固定，不足使用空格填充，最多容纳2000个字符，char(11)存储abc，占11位。查询速度极快但浪费空间

• varchar变长字符串，最多容纳4000个字符，varchar(11)存储abc，只占3位。查询稍慢，但节省空间。Oracle为varchar2

• 大文本: 大量文字（不推荐使用，尽量使用varchar替代）

以utf8编码计算的话，一个汉字在u8下占3个字节

注：不同数据库版本长度限制可能会有不同

1.8.3 数字

• tinyint,int整数类型

• float,double小数类型

• numberic(5,2) decimal(5,2)—也可以表示小数,表示总共5位,其中可以有两位小数

• decimal和numeric表示精确的整数数字

1.8.4 日期

• date 包含年月日

• time时分秒

• datetime包含年月日和时分秒

• timestamp时间戳，不是日期，而是从1970年1月1日到指定日期的毫秒数

1.8.5 图片

• blob 二进制数据，可以存放图片、声音，容量4g。早期有这样的设计。但其缺点非常明显，数据库庞大，备份缓慢，这些内容去备份多份价值不大。同时数据库迁移时过大，迁移时间过久。所以目前主流都不会直接存储这样的数据，而只存储其访问路径，文件则存放在磁盘上。

1.9 字段约束

1.9.1 主键约束

主键约束：如果为一个列添加了主键约束，那么这个列就是主键，主键的特点是唯一且不能为空。通常情况下，每张表都会有主键。

添加主键约束，例如将id设置为主键：

主键自增策略:当主键为数值类型时，为了方便维护，可以设置主键自增策略（auto_increment），设置了主键自增策略后，数据库会在表中保存一个AUTO_INCREMENT变量值，初始值为1，当需要id值，不需要我们指定值，由数据库负责从AUTO_INCREMENT获取一个id值，作为主键值插入到表中。而且每次用完AUTO_INCREMENT值，都会自增1. AUTO_INCREMENT=1

create table abc(

    id int primary key auto_increment

);

insert into abc values(null);

insert into abc values(null);

insert into abc values(null);

select * from abc;

1.9.2 非空约束

非空约束：如果为一个列添加了非空约束，那么这个列的值就不能为空，但可以重复。

添加非空约束，例如为password添加非空约束：

create table user(
    id int primary key auto_increment,
    password varchar(50) not null
);
show tables;
insert into user values(null,null);//不符合非空约束
insert into user values(null,'123');//OK

1.9.3 唯一约束

唯一约束：如果为一个列添加了唯一约束，那么这个列的值就必须是唯一的（即不能重复），但可以为空。

添加唯一约束，例如为username添加唯一约束及非空约束：

create table test(
    id int primary key auto_increment,
        username varchar(50) unique--唯一约束
);
show tables;
insert into test values(null,'lisi');
insert into test values(null,'lisi');--username的值要唯一,重复会报错的
select * from test;

1.10 准备数据

1.10.1 部门表 dept

字段名称	数据类型	是否为空	备注
deptno	int		部门编号，PK主键
ldname	varchar(20)	Y	部门名称
loc	varchar(13)	Y	部门所在地点

CREATE TABLE dept(
  deptno int primary key auto_increment NOT NULL,
  dname VARCHAR(20),
  loc VARCHAR(13)
); 
INSERT INTO dept VALUES(1,'accounting','一区');
INSERT INTO dept VALUES(2,'research','二区');
INSERT INTO dept VALUES(3,'operations','二区');

1.10.2 员工表 emp

字段名称	数据类型	是否为空	备注
empno	int		员工编号，PK主键
ename	varchar(10)	Y	员工名称
job	varchar(10)	Y	职位
mgr	int	Y	上级编号
hiredate	datetime	Y	月工资
sal	double	Y	奖金
comm	NUMERIC(8,2)	Y	奖金
deptno	int	Y	所属部门 FK外键

Mysql:

CREATE TABLE emp(
  empno int primary key auto_increment NOT NULL,
  ename VARCHAR(10),
  job VARCHAR(10),
  mgr int,
  hiredate DATE,
  sal double,
  comm NUMERIC(7,2),
  deptno int
);
INSERT INTO emp VALUES(100,'jack','副总',NULL,'2002-05-03',90000,NULL,1);
INSERT INTO emp VALUES(200,'tony','总监',100,'2015-02-02',10000,2000,2);
INSERT INTO emp VALUES(300,'hana','经理',200,'2017-02-02',8000,1000,2);
INSERT INTO emp VALUES(400,'leo','员工',300,'2019-02-22',3000,200.12,2);
INSERT INTO emp VALUES(500,'liu','员工',300,'2019-03-19',3500,200.58,2);

1.11 基础函数

1.11.1 lower

SELECT 'ABC',LOWER('ABC') from dept; --数据转小写

1.11.2 upper

select upper(dname) from dept --数据转大写

1.11.3 length

select length(dname) from dept --数据的长度

1.11.4 substr

SELECT dname,SUBSTR(dname,1,3) FROM dept; --截取[1,3]

1.11.5 concat

select dname,concat(dname,'123') X from dept --拼接数据

1.11.6 replace

select dname,replace(dname,'a','666') X from dept --把a字符替换成666

1.11.7 ifnull

select ifnull(comm,10) comm from dept2 #判断,如果comm是null,用10替换

1.11.8 round & ceil & floor

round四舍五入，ceil向上取整，floor向下取整

--直接四舍五入取整

select comm,round(comm)  from emp

--四舍五入并保留一位小数

select comm,round(comm,1)  from emp

--ceil向上取整,floor向下取整

select comm,ceil(comm) ,floor(comm)  from emp

1.11.9 uuid

SELECT UUID()

返回uuid：a08528ca-741c-11ea-a9a1-005056c00001

1.11.10 now

select now() -- 年与日 时分秒

select curdate() --年与日

select curtime() --时分秒

1.11.11 year & month & day

--hour()时  minute()分 second()秒

select now(),hour(now()),minute(now()),second(now()) from emp ;

--year()年 month()月 day()日

select now(),year(now()),month(now()),day(now()) from emp ;

1.11.12 转义字符

’作为sql语句符号，内容中出现单撇就会乱套，进行转义即可

select 'ab'cd'  -- 单引号是一个SQL语句的特殊字符

select 'ab\'cd' --数据中有单引号时,用一个\转义变成普通字符

1.12 条件查询

1.12.1 distinct

使用distinct关键字，去除重复的记录行

SELECT loc FROM dept;

SELECT DISTINCT loc FROM dept;

1.12.2 where

注意:where中不能使用列别名!!

select * from emp
select * from emp where 1=1 --类似没条件
select * from emp where 1=0 --条件不成立
select * from emp where empno=100 --唯一条件
select * from emp where ename='tony'  and deptno=2 --相当于两个条件的&关系
select * from emp where ename='tony'  or deptno=1 --相当于两个条件的|关系
select name, sal from emp where sal=1400 or sal=1600 or sal=1800;
-- 或
select name, sal from emp where sal in(1400,1600,1800);
select name, sal from emp where sal not in(1400,1600,1800);

1.12.3 like

通配符%代表0到n个字符，通配符下划线_代表1个字符

select * from emp where ename like 'l%' --以l开头的

select * from emp where ename like '%a' --以a结束的

select * from emp where ename like '%a%' --中间包含a的

select * from emp where ename like 'l__'  --l后面有两个字符的 _代表一个字符位置

1.12.4 null

select * from emp where mgr is null --过滤字段值为空的

select * from emp where mgr is not null --过滤字段值不为空的

1.12.5 between and

SELECT * FROM emp

select * from emp where sal>3000 and sal<10000

select * from emp where sal>=3000 and sal<=10000--等效

select * from emp where sal between 3000 and  10000--等效

1.12.6 limit

分数最高的记录：按分数排序后，limit n，返回前n条。Oracle做的很笨，实现繁琐，后期有介绍，而mysql做的很棒，语法简洁高效。在mysql中，通过limit进行分页查询：

select * from emp limit 2 --列出前两条
select * from emp limit 1,2 --从第二条开始,展示2条记录
select * from emp limit 0,3 --从第一条开始,展示3条记录--前三条

1.12.7 order by

SELECT * FROM emp order by sal #默认升序

SELECT * FROM emp order by sal desc #降序

1.13 统计案例

1.13.1 入职统计

#2015年以前入职的老员工
SELECT * FROM emp WHERE DATE_FORMAT(hiredate,'%Y-%m-%d')<'2015-01-01';
SELECT * FROM emp WHERE YEAR(hiredate)<2015
 
#2019年以后签约的员工，日期进行格式转换后方便比较
SELECT * FROM emp WHERE YEAR(DATE_FORMAT(hiredate,'%Y-%m-%d'))>=2019;

#2015年到2019年入职的员工
SELECT * FROM emp
 WHERE
STR_TO_DATE(hiredate,'%Y-%m-%d')>='2015-01-01'
 AND
STR_TO_DATE(hiredate,'%Y-%m-%d')<='2019-12-31'

1.13.2 年薪统计

公司福利不错13薪，年底双薪，统计员工的年薪=sal13+comm13

SELECT empno,ename,job,sal*13+comm*13  FROM emp;

SELECT empno,ename,job,sal*13+comm*13 as 年薪 FROM emp;--用as给列起个别名

SELECT empno,ename,job,sal*13+comm*13 年薪 FROM emp; --as也可以省略

select ename, sal+comm from emp

select ename, sal , comm, sal+ifnull(comm,0) from emp--用0替换掉null

1.14 聚合 aggregation

根据一列统计结果

1.14.1 count

s

elect count(*) from emp --底层优化了

select count(1) from emp --效果和*一样

select count(comm) from emp  --慢,只统计非NULL的

1.14.2 max / min

select max(sal) from emp --求字段的最大值
select max(sal) sal,max(comm) comm from emp 

select min(sal) min from emp --获取最小值
select min(sal) min,max(sal) max  from emp --最小值最大值
SELECT ename,MAX(sal) FROM emp group by ename --分组

1.14.3 sum / avg

平均值

select count(*) from emp --总记录数

select sum(sal) from emp --求和

select avg(sal) from emp --平均数

1.15 分组 group

用于对查询的结果进行分组统计

group by表示分组, having 子句类似where过滤返回的结果

1.15.1 group by

#每个部门每个岗位的最高薪资和平均薪资，结果中的非聚合列必须出现在分组中，否则业务意义不对
SELECT deptno,MAX(sal),AVG(sal) FROM emp
GROUP BY deptno #按照deptno分组 

SELECT job,MAX(sal),AVG(sal) FROM emp
GROUP BY job #按照job分组

SELECT deptno,job,MAX(sal),AVG(sal) FROM emp
GROUP BY deptno,job #deptno和job都满足的

1.15.2 having

#平均工资小于8000的部门
select deptno, AVG(sal)  from emp 
group by deptno  #按部门分组
having AVG(sal) <8000 #查询条件,类似where,但是group by只能配合having

#deptno出现的次数
SELECT deptno,COUNT(deptno)  FROM emp
GROUP BY deptno #按deptno分组
HAVING COUNT(deptno)>1 #次数多的

1.16 小结

1.16.1 char和varchar有什么区别？

• char为定长字符串，char(n)，n最大为255
• varchar为不定长字符串，varchar(n)，n最大长度为65535
• char(10)和varchar(10)存储abc，那它们有什么差别呢？
• char保存10个字符，abc三个，其它会用空格补齐；而varchar只用abc三个位置。

1.16.2 datetime和timestamp有什么区别？

• 数据库字段提供对日期类型的支持，是所有数据类型中最麻烦的一个，慢慢使用就会体会出来。

• date 是 年与日

• time是 时分秒

• datetime年月日时分秒，存储和显示是一样的

• timestamp时间戳，存储的不是个日期，而是从1970年1月1日到指定日期的毫秒数

1.16.3 中文乱码

如果在dos命令下执行insert插入中文数据，数据又乱码，那现在sqlYog客户端执行下面命令：

set names utf8;

set names gbk;

设置客户端字符集和服务器端相同。如果不知道它到底用的什么编码？怎么办呢？很简单，两个都尝试下，哪个最后操作完成，查询数据库不乱码，就用哪个。

那为何会造成乱码呢？

Mysql数据库默认字符集是lantin1，也就是以后网页中遇到的ISO8859-1，它是英文字符集，不支持存放中文。我们创建库时，可以指定字符集：

create database yhdb charset utf8;

但这样很容易造成服务器和客户端编码集不同，如服务器端utf8，客户端ISO8859-1。mysql和客户端工具都有习惯的默认编码设置，好几个地方，要都统一才可以保证不乱码。

我们只要保证创建数据库时用utf8，使用可视化工具一般就基本正确。

1.16.4 注释

 /* 很多注释内容 */

  # 一行注释内容

 -- 一行注释内容，这个使用较多

1.16.5 主键、外键、唯一索引的区别？

• Primary Key 主键约束，自动创建唯一索引

• Foreign Key 外键约束，外键字段的内容是引用另一表的字段内容，不能瞎写

• Unique Index 唯一索引，唯一值但不是主键

对于约束的好处时，数据库会进行检查，违反约束会报错，操作失败。数据库提供了丰富的约束检查，还有其他约束，但现今弱化关系型数据库的前提下，基本已经很少使用，记住上面三个即可。

1.16.6 drop、delete和truncate之间的区别？

drop删除库或者表，数据和结构定义

delete和truncate只是删除表的数据

delete可以指定where条件，删除满足条件的记录，tuncate删除所有记录

对于自增字段的表，delete不会自增值清零，而truncate是把表记录和定义都删除了，然后重建表的定义，所以自增主键会重头开始计数

1.17 事务 transaction

1.1.1 什么是事务

数据库事务(Database Transaction)，是指作为单个逻辑工作单元执行的一系列操作，要么完全地执行，要么完全地不执行。

简单的说：事务就是将一堆的SQL语句(通常是增删改操作)绑定在一起执行，要么都执行成功，要么都执行失败，即都执行成功才算成功，否则就会恢复到这堆SQL执行之前的状态。

下面以银行转账为例，A转100块到B的账户，这至少需要两条SQL语句：

• 给A的账户减去100元；

update 账户表 set money=money-100 where name=‘A’;

• 给B的账户加上100元。

update 账户表 set money=money+100 where name=‘B’;

如果在第一条SQL语句执行成功后，在执行第二条SQL语句之前，程序被中断了（可能是抛出了某个异常，也可能是其他什么原因），那么B的账户没有加上100元，而A却减去了100元，在现实生活中这肯定是不允许的。

如果在转账过程中加入事务，则整个转账过程中执行的所有SQL语句会在一个事务中，而事务中的所有操作，要么全都成功，要么全都失败，不可能存在成功一半的情况。

也就是说给A的账户减去100元如果成功了，那么给B的账户加上100元的操作也必须是成功的；否则，给A减去100元以及给B加上100元都是失败的。

1.17.1 事务4个特性ACID

一般来说，事务是必须满足4个条件（ACID）：原子性（Atomicity，或称不可分割性）、一致性（Consistency）、隔离性（Isolation，又称独立性）、持久性（Durability）。

• 原子性：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中如果发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。

• 一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。

• 隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。

• 持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

1.17.2 隔离级别

事务隔离分为不同级别，包括

• 读未提交（Read uncommitted） 安全性最差,可能发生并发数据问题,性能最好

• 读提交（read committed） Oracle默认的隔离级别

• 可重复读（repeatable read）MySQL默认的隔离级别,安全性较好,性能一般

• 串行化（Serializable） 表级锁，读写都加锁，效率低下,安全性高,不能并发

1.17.3 查询mysql的隔离级别

在默认情况下，MySQL每执行一条SQL语句，都是一个单独的事务。如果需要在一个事务中包含多条SQL语句，那么需要手动开启事务和结束事务。

• 开启事务：start transaction；

• 结束事务：commit（提交事务）或rollback（回滚事务）。

在执行SQL语句之前，先执行strat transaction，这就开启了一个事务（事务的起点），然后可以去执行多条SQL语句，最后要结束事务，commit表示提交，即事务中的多条SQL语句所做出的影响会持久化到数据库中。或者rollback，表示回滚，即回滚到事务的起点，之前做的所有操作都被撤消了！

SELECT @@tx_isolation;
Repeatable Read（可重读）

MySQL的默认事务隔离级别，它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。

1.17.4 事务处理

• 在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务

• 事务处理可以用来维护数据的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行

• 事务用来管理 insert、update、delete 语句，因为这些操作才会“破坏”数据，查询select语句是不会的

• MySQL默认数据库的事务是开启的，执行SQL后自动提交。

• MySQL的事务也可以改成手动提交，那就有两个步骤：先开启，写完SQL后，再手动提交。
  

1.17.5 提交 commit

#多条语句时，批量执行，事务提交
#有了事务，多步操作就形成了原子性操作，高并发下也不会引起数据错乱
#mysql的事务默认就是开启的  -- 多条语句一起操作时,要么一起成功要么一起失败
BEGIN; #关闭事务的自动提交

INSERT INTO user (id) VALUES(25);#成功
INSERT INTO user (id) VALUES(5);#已经存在5了，会失败
COMMIT; #手动提交事务

1.17.6 回滚 rollback

#多条语句，批量执行，insert插入重复的主键导致失败时，事务回滚
BEGIN;

INSERT INTO user (id) VALUES(15);
INSERT INTO user (id) VALUES(35);#存在了

ROLLBACK;#事务回滚，就不会再提交了

1.18 表关联 association

1.18.1 概念

表table代表了生活中一个主体，如部门表dept，员工表emp。表关联则代表了表之间的关系，如：部门和员工，商品和商品分类，老师和学生，教室和学生。

同时，也要知道，表并不都有关系，它们形成自己的小圈子。如商品和商品详情一圈，部门和员工一圈，出圈就可能没关系了，如商品和员工无关，商品和学生无关。

下面我们讨论表的关系分为四种：

• 一对一 one to one QQ和QQ邮箱，员工和员工编号

• 一对多 one to many 最常见，部门和员工，用户和订单

• 多对一 many to one 一对多反过来，员工和部门，订单和用户

• 多对多 many to many 老师和学生，老师和课程

1.19 考试系统mysql版

1.19.1 表设计

• 外键：由子表出发向主表拖动鼠标，到达主表后松手，PD会自动添加外键字段

• 讲师表和课程表：一对多，两张表。关联关系体现：子表存储主表的主键，称外键

• 课程表和学生表：多对多，三张表。关联关系体现：子表无法存储主表的多条关联信息，只能再创建一张表来存储其信息

• 中间表：存储两张表各自的主键，某一张表的主键无法标识记录的唯一性，两个一起才可以标识唯一，这种主键为多个字段的称为复合主键

1.19.2 创建数据库

1.19.3 创建表

表设计特点：

• 表都以s结束，标识复数

• 字段多以表的首字母作为开头，在多表联查时，方便标识出是哪个表的字段

/*==============================================================*/
/* DBMS name:      MySQL 5.0                                    */
/* Created on:     2020                                         */
/*==============================================================*/

 
drop table if exists courses;
 
drop table if exists scores; 

drop table if exists students; 

drop table if exists teachers;


/*==============================================================*/
/* Table: courses                                               */
/*==============================================================*/
create table courses

(
   cno                  varchar(5) not null,
   cname                varchar(10) not null,
   tno                  varchar(3) not null,
   primary key (cno)
);

 
/*==============================================================*/
/* Table: scores                                                */
/*==============================================================*/
create table scores

(

   sno                  varchar(3) not null,
   cno                  varchar(5) not null,
   degree               numeric(10,1) not null,
   primary key (sno, cno)

);


/*==============================================================*/
/* Table: students                                              */
/*==============================================================*/
create table students

(

   sno                  varchar(3) not null,
   sname                varchar(4) not null,
   ssex                 varchar(2) not null,
   sbirthday            datetime,
   class                varchar(5),
   primary key (sno)

); 

/*==============================================================*/
/* Table: teachers                                              */
/*==============================================================*/
create table teachers

(

   tno                  varchar(3) not null,
   tname                varchar(4),
   tsex                 varchar(2),
   tbirthday            datetime,
   prof                 varchar(6),
   depart               varchar(10),
   primary key (tno)

);

alter table courses add constraint FK_Reference_3 foreign key (tno)
      references teachers (tno) on delete restrict on update restrict;

alter table scores add constraint FK_Reference_1 foreign key (sno)
      references students (sno) on delete restrict on update restrict;

alter table scores add constraint FK_Reference_2 foreign key (cno)
      references courses (cno) on delete restrict on update restrict;

1.19.4 生成建表SQL

1.19.5 插入测试数据

INSERT INTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (108 ,'曾华' ,'男' ,'1977-09-01',95033);