9/6
今日考题
1.说说存储引擎
InnoDB
5.5版本之后的默认存储引擎
数据安全性高
MyISAM
5.5之前的版本默认存储引擎
安全性较于InnoDB低
但是存取速度优于InnoDB
memory
数据直接存储在内存里
blackhole
数据写进去直接消失
2.创建表的完整语法及注意事项
create table 表名(
字段名 字段类型(宽度) 约束条件,
字段名 字段类型(宽度) 约束条件,
字段名 字段类型(宽度) 约束条件
)
# 字段名和字段类型必须 后面两个可以没有
# 约束条件可以多个
# 最后别多加逗号
3.所学基本数据类型及约束条件(不要仅仅列出做好做些细节或注意点描述)
# 整型
tinyint
smallint
int
bigint
从上到下数据存储从小到大
默认都带正负可以通过nosigned去掉符号
# 浮点型
float
double
decimal
从上到下精确度由低到高
# 字符类型
char # 会自动填充成你要的宽度
varchar
两者超出规定宽度都报错
char存储速度快费空间
varchar省空间速度慢一点
# 时间类型
date # 年月日
time # 时分秒
datetime # 年月日时分秒
year # 年
# 枚举
enum # 多选一
不在选择范围会报错
# 集合
set # 多选 也可以选一
不在选择范围会报错
复习巩固
- 锻炼自我总结分析话语的能力
- 存储引擎
# 查看存储引擎
show engines;
# 常见引擎
InnoDB
blackhole
MyISAM
memory
# 文件结构
InnoDB两个文件
MyISAM三个文件
blackhole一个文件
memory一个文件
- 创建表完整语法
create table 表名(
字段名 字段类型(宽度) 约束条件,
字段名 字段类型(宽度) 约束条件,
字段名 字段类型(宽度) 约束条件
);
'''三个要点'''
- MySQL常用数据类型
整型
tinyint smallint int bigint
浮点型
float double decimal
字符类型
char varchar
时间类型
date time datetime year
枚举与集合
enum set
- 严格模式
# 查看严格模式
show variables like '%mode%';
# 修改严格模式
set global ......
# 退出客户端重新进入就生效了
- 约束条件
# 字段类型和约束条件的关系
字段类型规定了存储数据的类型
约束条件约束的则是字段类型的补充限制
# 约束条件
unsigned
zerofill
not null
default
unique
内容概要
-
约束条件之主键
-
约束条件之自增
-
约束条件之外键
重在理解 -
查询关键字
根据指定条件筛选出符合的数据
详细讲解
约束条件之主键
# 主键 primary key
1.单从约束条件来看 相当于not null + unique >>> 非空且唯一
验证: create table t1(id int primary key);
desc t1;
2.但是主键还是InnoDB存储引擎组织数据表的依据
2.1InnoDB规定了一个表必须要有且只有一个主键
2.2如果不指定则会采用隐藏字段作为主键
2.3当表内没有主键但是有非空且唯一的字段则自动升级为主键
'''
主键能够加快查询速度的
隐藏意味着无法使用主键
导致速度无法提升
'''
create table t2(
id int,
name char(16),
age int not null unique,
gender char(8) not null unique
);
desc t2;
'在创建表的时候一般都要一个id字段'
'并且id字段设置为表的主键字段'
# 主键可以单列主键 还可以联合主键 但是很少用
create table mix(
id int,
name char(16),
primary key(id,name)
);
约束条件之自增
通过上面的主键还是有个问题
如果数据量很大 那id后面就每次要看一遍才能写
# 这样就顺理成章引入自增的概念 auto_increment
一般自增配合主键使用
create table t3(
id int primary key auto_increment,
name char(16)
);
insert into t3(name) values('leo'),('ace');
##############################################
所以直接记好id这部分主键字段固定写法
id int primary key auto_increment
##############################################
补充
1.自增的特性不会因为delete操作重置或者说回退
delete from # 只删除数据
'''这个特性实际上是优点
比如说你快递订单不能因为前面的单删了就要把自己这单号码改了把
如果改了之后就很难区分'''
2.如果真的想要重置清空表数据和表结构
truncate # 即删除数据而且重置主键
约束条件之外键
# 前戏
'''先定义一张学生表为例'''
| id | stu | age | academy学院 | aca_desc学院介绍 |
|---|---|---|---|---|
| 1 | leo | 18 | python | 数据分析 |
| 2 | ace | 18 | python | 数据分析 |
| 3 | jason | 20 | java | 后端开发 |
这样缺点就很多
1.表结构不清晰
2.表数据重复
3.数据扩展性极差
'''用一个很简单的方法就解决了三个问题:拆表'''
拆成学生表和学院表
| id | stu | age |
|---|---|---|
| 1 | leo | 18 |
| 2 | ace | 18 |
| 3 | jason | 20 |
| id | academy | aca_desc |
| ---- | ------- | -------- |
| 1 | python | 数据分析 |
| 2 | java | 后端开发 |
'''但是这样学生和学院之间的联系就断了'''
所以就顺理成章的引入外键的概念
| id | stu | age | aca_id |
################################################
外键:记录表与表之间数据关系
################################################
表关系的种类
一对多关系
多对多关系
一对一关系
没有关系
一对多关系
# 一对多
专业术语中没有多对一一说
多对一和一对多是一样的
'''判断表关系遵循 换位思考 原则'''
以刚才的学生表为例
1.先站在学生表的层面
Q 一个学生能有多个学院嘛?
A 不能
2.在站在学院层面
Q 一个学院能有多个学生嘛?
A 可以
结论:一个可以一个不可以两个表之间的关系就是'''一对多'''
这种情况表关系的外键字段建在多的一方(本案例就是学生表里)
# 创建表关系不要急 先写字段类型和约束条件 最后加关系
create table stu(
id int primary key auto_increment,
stu char(8),
age int,
aca_id int,
foreign key(aca_id) references academy(id)
# 外键名字叫aca_id 关联 academy表里的id
);
create table academy(
id int primary key auto_increment,
academy char(8),
aca_desc char(16)
);
外键字段特性
'''1.创建表的时候一定要先创建被关联表(即自身没有外键字段的表)'''
2.插入数据也一定要先插入被关联表再插入关联表
而且只能填写被关联字段中出现的值
3.被关联表中的数据无法自由删除和修改
4.对于这些种种限制的优化就要引入
级联更新 级联删除
create table stu(
id int primary key auto_increment,
stu char(8),
age int,
aca_id int,
foreign key(aca_id) references academy(id)
on update cascade # 这个相当于写给外键的约束条件
on delete cascade # 比较长才换行 不需要逗号
);
'''这样创建好之后前面的大部分问题就解决了'''
多对多关系
# 以学生和选修课为例
1.先站在学生角度
Q 一个学生能选多门课嘛?
A 可以
2.再站在作者角度
Q 一门选修课能有多个学生选嘛?
A 可以
结论: 两个都可以就是'多对多关系'
# 但是多对多关系如果像之前那样创建表格哪个先创都不行了呀
'''所以针对多对多关系 外键字段需要建在第三个表里'''
create table stu(
id int primary key auto_increment,
stu char(8),
age int
);
create table selected(
id int primary key auto_increment,
selected char(8),
desc_lesson char(16)
);
create table stu_select(
id int primary key auto_increment,
stu_id int,
selected_id int,
foreign key(stu_id) references selected(id)
on update cascade
on delete cascade,
foreign key(selected_id) references stu(id)
on update cascade
on delete cascade
);
'''这样就解决了多对多的创表问题'''
一对一关系
# 以班长和班级为例
1.站在班长的角度
Q 一个班长能对应多个班级嘛
A 不能
2.站在班级的角度
Q 一个班级能对应多个班长嘛
A 不能
结论: 两个都不能那自然就是'一对一关系' 也可能没关系没关系一眼就看穿了也不用多说
# 这种关系外键可以建在任意一方 但是推荐建在查询频率高的那张表
create table stu_sel(
id int primary key auto_increment,
name varchar(8),
class_id int unique, # 通过这里unique和一对多关系区分开
foreign key(class_id) references class(id)
on update cascade
on delete cascade
);
create table class(
id int primary key auto_increment,
num int,
adv varchar(16)
);
修改表相关SQL语句
1.修改表名
alter table 表名 rename 新表名;
2.增加字段
alter table 表名 add 字段名 数据类型 约束条件，add 字段名 数据类型 约束条件;
# 指定位置
alter table 表名 add 字段名 数据类型 约束条件 after 字段名;
3.删除字段
alter table 表名 drop 字段名;
4.修改字段
alter table 表名 change 旧字段名 新字段名 新数据类型 约束条件;
配套练习
# 判断下列表关系 必须完整写出判断流程 并且书写sql语句并插入数据
书籍表与出版社表
书籍能对应多个出版社嘛? NO
出版社能出多本书嘛? YES
一对多关系
create table book(
id int primary key auto_increment,
name char(8),
price int
pub_id int,
foreign key(pub_id) references publish(id)
on updata cascade
on delete cascade
);
create table publish(
id int primary key auto_increment,
name char(8),
add char(16)
);
老师表与课程表
老师能不能上多门课 NO
一门课能不能有多个老师 NO
一对一关系
create table teacher(
id int primary key auto_increment,
name varchar(8),
age int,
cl_id int unique,
foreign key(cl_id) references class(id)
on update cascade
on delete cascade
);
create table class(
id int primary key auto_increment,
class_name varchar(8)
);
学生表与班级表
学生能不能有多个班级 NO
班级能不能有多个学生 YES
一对多关系
create table student(
id int primary key auto_increment,
name char(8),
age int,
cl_id int,
foreign key(cl_id) references class(id)
on update cascade
on delete cascade
);
create table class(
id int primary key auto_increment,
class_name char(8)
);
书籍表与作者表
一本书能不能有多个作者 YES
一个作者能不能有多本书 YES
多对多关系
create table book(
id int primary key auto_increment,
name char(8),
price int
);
create table author(
id int primary key auto_increment,
name char(8),
age int
);
create table book_author(
id int primary key auto_increment,
author_id int,
book_id int,
foreign key(author_id) references author(id)
on update cascade
on delete cascade,
foreign key(book_id) references book(id)
on update cascade
on delete cascade
)
作者表与作者详情表
作者能不能有多个作者详情 NO
作者详情能不能有多个作者 NO
一对一关系
create table author(
id int primary key auto_increment,
name varchar(8),
detail_id int unique,
foreign key(detail_id) references author_detail(id)
on update cascade
on delete cascade
);
create table author_detail(
id int primary key auto_increment,
phone bigint,
addr varchar(8)
);
