# 创建表
create table zz(
-> id int primary key auto_increment,
-> name varchar(32)
-> );
Query OK, 0 rows affected (0.02 sec)
# 填充内容
mysql> insert into zz(name) values('owen'),('tom'),('tony');
# 再增加一个名字
mysql> insert into zz(name) values('gavin');
mysql> select * from zz; # id=4
+----+-------+
| id | name |
+----+-------+
| 1 | owen |
| 2 | tom |
| 3 | tony |
| 4 | gavin |
+----+-------+
# 删除id=4的记录
mysql> delete from zz where id=4;
# 再次添加
mysql> insert into zz(name) values('gavin');
select * from zz;
+----+-------+
| id | name |
+----+-------+
| 1 | owen |
| 2 | tom |
| 3 | tony |
| 5 | gavin |
+----+-------+
'''
自增不会随着数据的删除而后退，只会从原来在哪就直接在哪开始增加
'''
delete from zz;
mysql> insert into zz(name) values('owen'),('tom'),('tony');
mysql> select * from zz;
+----+------+
| id | name |
+----+------+
| 4 | owen |
| 5 | tom |
| 6 | tony |
+----+------+
mysql> truncate zz; # 删除数据并重置主键值
mysql> insert into zz(name) values('owen'),('tom'),('tony');
mysql> select * from zz;
+----+------+
| id | name |
+----+------+
| 1 | owen |
| 2 | tom |
| 3 | tony |
+----+------+
'''
使用delete删除表格内容 还是无法重置主键，如果想要重置主键值 使用关键字 truncate
'''
# 外键前戏
创建一个员工表
id name age dep_name dep_desc
1 owen 20 外交部 搞外交
2 tom 19 教育部 教学
3 avin 21 外交部 搞外交
4 Gavin 24 教育部 教学
# 表格缺陷
1、表的重点不清晰
因为这是员工表，你也可以说这是部门表
2、表中的部门名和职责的数据都一直在重复
浪费存储空间
3、表的扩展性极差
耦合度太高， 不利于维护
# 解决措施
将表格分为两个
员工表
id name age
部门表
id dep_name dep_desc
# 解决了上面的三个问题，但是有一个致命问题，部门和员工的关系怎么绑定
措施：
在员工表中增加一个部门编号字段与部门表中的主键字段对应
"""
这个字段就是外键字段
外键字段就是用来记录表与表之间的数据的关系
"""
# 关系种类
1、一对多
2、多对多
3、一对一
4、没有关系
"""
关系的判断四字口诀: 换位思考
"""
# 一对多时
1、以员工表与不部门表为例
员工表角度:
一个员工可以对应多个部门
不能(比较现实，总不能一个人好几个部门职位)
部门表角度:
一个部门能否对应多个员工
可以
所以关系是"一对多" 部门是"一" 员工为"多"
'''
关系表达也只能是一对多， 不能用多对一
'''
一对多的关系中，外键字段在"多"的一方
'''
当表中有外键字段，那么就先编写普通字段，最后在考虑外键字段
'''
# 普通
create table dep(
id int primary key auto_increment comment '编号',
dep_name varchar(32) comment '部门名称',
dep_desc varchar(32) comment '部门职责'
);
# 外键
create table emp(
id int primary key auto_increment comment '编号',
name varchar(32) comment '姓名',
age int comment '年龄',
dep_id int comment '部门编号',
# dep_id 为外键字段，参考dep中的id
foreign key(dep_id) references dep(id)
);
# foreign key的约束效果
1、在创建表的时候，应该先创建被关联表(没有外键字段的表)
2、填入数据的时候，应该先填入被关联表(没有外键字段的表)
因为外键字段填入的值,只能是被关联表已经存在的值
3、修改、删除被关联表数据都会出现障碍
update dep set id=200 where id=2;
delect from dep where id=2;
# 级联更新 级联删除
# 普通
create table dep1(
id int primary key auto_increment comment '编号',
dep_name varchar(32) comment '部门名称',
dep_desc varchar(32) comment '部门职责'
);
# 外键
create table emp1(
id int primary key auto_increment comment '编号',
name varchar(32) comment '姓名',
age int comment '年龄',
dep_id int comment '部门编号',
foreign key(dep_id) references dep1(id)
on update cascade # 级联更新
on delete cascade # 级联删除
);
"""
在实际工作中外键也可能不会使用 因为外键会消耗额外的资源
并且会增加表的复杂度
表很多的情况下 我们也可以通过SQL语句的形式建立逻辑意义上的表关系
"""
# 用图书馆与作者为例
1.先站在图书表的角度
一本书籍能否对应多名作者
可以
2.再站在作者表的角度
一名作者能否对应多本书籍
可以
换位思考之后两边都可以 那么就是"多对多"关系
create table book(
id int primary key auto_increment comment'编号',
title varchar(32) comment'书名',
author_id int comment'作者ID',
foreign key (author_id) references author(id)
on update cascade # 级联更新
on delete cascade # 级联删除
);
create table author(
id int primary key auto_increment,
name varchar(32),
book_id int,
foreign key(book_id) references book(id)
on update cascade # 级联更新
on delete cascade # 级联删除
);
# 要是只创建两个表，这两个表又是多对多的格式，所以先创建那个都会报错，那么我们就需要创建第三个表专门存储关系
create table book(
id int primary key auto_increment,
title varchar(32)
);
create table author(
id int primary key auto_increment,
name varchar(32)
);
create table bwitha(
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 # 级联删除
);
# 多对多表关系
两张基表内的数据没有在第三张表内绑定关系的情况下随意新增修改删除
# 针对用户信息表，里面的数据可以分为两类
1、热数据:
经常使用的数据: 电话、签名、地区
2、冷数据:
不经常使用的数据: 年级、星座、学校
为了降低资源并 降低数据库压力，将表一分为二
1、用户表
存使用频率较高的数据字段
2、用户详情表
存使用频率较低的数据字段
# 表与表之间的关系
1、用户表角度
一个用户数据能否对应多个用户详情数据
肯定是不能的
2、用户详情表
一个用户详情数据能否对应多个用户表
也肯定是不能的
# 两边都不可以，所以有两种可能
'没有关系'
'一对一的关系'
那肯定是后者 ，那么一对一关系外键字段建在任意一方都可以，推荐建在使用查询频率比较高的一方
create table user(
id int primary key auto_increment,
name varchar(32),
gender enum('male','female','others'),
user_detail_id int unique, # 详细表的编号必须为独一无二
foreign key(user_detail_id) references userdetail(id)
on update cascade
on delete cascade
);
create table userdetail(
id int primary key auto_increment,
phone bigint,
age int
);
# 用户表和用户详细表只能一对一，有了值之后，在想往里面添加值就会直接报错
# 数据准备
create table gjz(
id int primary key auto_increment,
name varchar(32) not null,
sex enum('male','female') not null default 'male', #大部分是男的
age int(3) unsigned not null default 28,
hire_date date not null,
post varchar(50),
post_comment varchar(100),
salary double(15,2),
office int, #一个部门一个屋子
depart_id int
);
#插入记录
#三个部门：教学，销售，运营
insert into gjz(name,sex,age,hire_date,post,salary,office,depart_id) values
('jason','male',18,'20170301','张江第一帅形象代言',7300.33,401,1), #以下是教学部
('tom','male',78,'20150302','teacher',1000000.31,401,1),
('kevin','male',81,'20130305','teacher',8300,401,1),
('tony','male',73,'20140701','teacher',3500,401,1),
('owen','male',28,'20121101','teacher',2100,401,1),
('jack','female',18,'20110211','teacher',9000,401,1),
('jenny','male',18,'19000301','teacher',30000,401,1),
('sank','male',48,'20101111','teacher',10000,401,1),
('哈哈','female',48,'20150311','sale',3000.13,402,2),#以下是销售部门
('呵呵','female',38,'20101101','sale',2000.35,402,2),
('西西','female',18,'20110312','sale',1000.37,402,2),
('乐乐','female',18,'20160513','sale',3000.29,402,2),
('拉拉','female',28,'20170127','sale',4000.33,402,2),
('僧龙','male',28,'20160311','operation',10000.13,403,3), #以下是运营部门
('程咬金','male',18,'19970312','operation',20000,403,3),
('程咬银','female',18,'20130311','operation',19000,403,3),
('程咬铜','male',18,'20150411','operation',18000,403,3),
('程咬铁','female',18,'20140512','operation',17000,403,3);
select用于指定查询的字段
from用于指定查询的表
eg:
select id,name from mysql.user;
# 查看id 和name 从user里面
# 查询id大于等于4 小于等于8的数据
select * from emp where id>=4 and id<=8;
select * from emp where id between 4 and 8;
# 查询薪资是20000或者18000或者17000的数据
select * from emp where salary=20000 or salary=19000 or salary=18000;
select * from emp where salary in (20000,19000,18000);
# 查询id小于4或者大于8的数据
select * from emp where id not between 4 and 8;
# 查询薪资不在20000,19000,18000的数据
select * from emp where salary not in (20000,19000,18000);
# 查询岗位描述为空的数据
'''针对null只能用is不能用等号'''
select * from emp where post_comment is null;
# 查询员工姓名中包含字母o的员工姓名和薪资
"""
查询条件如果不是很明确的情况下 我们统一称之为'模糊查询'
关键字
like:开启模糊查询的关键字
关键符号
%:匹配任意个数的任意字符
_:匹配单个个数的任意字符
"""
select name,salary from emp where name like '%o%';
# 查询员工姓名是由四个字符组成的数据
select * from emp where name like '____';
select * from emp where char_length(name)=4;
# 什么是分组
按照指定的条件将单个单个的个体组织成一个个整体
eg:按照性别分组 按照部门分组 按照年龄分组 按照国家分组...
# 为什么需要分组
分组的好处在于可以快速统计出某些数据
eg:最大薪资 平均年龄 最小年龄 总人数
# 如何分组
'''按照部分分组'''
select * from gjz group by post;
"""
mysql5.7及以上版本默认自带sql_mode=only_full_group_by
该模式要求分组之后默认只可以直接获取分组的依据不能直接获取其他字段
原因是分组的目的就是按照分组的条件来管理诸多数据 最小单位应该是分组的依据而不是单个单个的数据
如果是MySQL5.6及以下版本 需要自己手动添加，在配置文件中增加
"""
# 聚合函数
专门用于分组之后的数据统计
max 统计最大值
min 统计最小值
sum 统计求和
count 统计计数
avg 统计平均值
是否需要分组 我们可以在题目或者需求中发现
# 1.统计每个部门的最高薪资
select post,max(salary) from gjz group by post;
+-----------------------------+-------------+
| post | max(salary) |
+-----------------------------+-------------+
| operation | 20000.00 |
| sale | 4000.33 |
| teacher | 1000000.31 |
| 张江第一帅形象代言 | 7300.33 |
+-----------------------------+-------------+
# 2.统计每个部门的平均薪资
select post,avg(salary) from gjz group by post;
+-----------------------------+---------------+
| post | avg(salary) |
+-----------------------------+---------------+
| operation | 16800.026000 |
| sale | 2600.294000 |
| teacher | 151842.901429 |
| 张江第一帅形象代言 | 7300.330000 |
+-----------------------------+---------------+
# 3.统计每个部门的员工人数
select post,count(id) from gjz group by post;
+-----------------------------+-----------+
| post | count(id) |
+-----------------------------+-----------+
| operation | 5 |
| sale | 5 |
| teacher | 7 |
| 张江第一帅形象代言 | 1 |
+-----------------------------+-----------+
# 4.统计每个部门的月工资开销
select post,sum(salary) from gjz group by post;
+-----------------------------+-------------+
| post | sum(salary) |
+-----------------------------+-------------+
| operation | 84000.13 |
| sale | 13001.47 |
| teacher | 1062900.31 |
| 张江第一帅形象代言 | 7300.33 |
+-----------------------------+-------------+
# 5.统计每个部门最小的年龄数
select post,min(age) from gjz group by post;
+-----------------------------+----------+
| post | min(age) |
+-----------------------------+----------+
| operation | 18 |
| sale | 18 |
| teacher | 18 |
| 张江第一帅形象代言 | 18 |
+-----------------------------+----------+
"""间接获取分组以外其他字段的数据"""
# 1.统计每个部门下所有员工的姓名(roup_concat)
select post,group_concat(name) from gjz group by post;
+-----------------------------+------------------------------------------------+
| post | group_concat(name) |
+-----------------------------+------------------------------------------------+
| operation | 程咬铁,程咬铜,程咬银,程咬金,僧龙 |
| sale | 拉拉,乐乐,西西,呵呵,哈哈 |
| teacher | sank,jenny,jack,owen,tony,kevin,tom |
| 张江第一帅形象代言 | jason |
+-----------------------------+------------------------------------------------+
# 2.统计每个部门下所有员工的姓名和年龄(roup_concat)
select post,group_concat(name,age) from gjz group by post;
select post,group_concat(name,'|',age) from gjz group by post;
+-----------------------------+---------------------------------------------------------------+
| post | group_concat(name,'|',age) |
+-----------------------------+---------------------------------------------------------------+
| operation | 程咬铁|18,程咬铜|18,程咬银|18,程咬金|18,僧龙|28 |
| sale | 拉拉|28,乐乐|18,西西|18,呵呵|38,哈哈|48 |
| teacher | sank|48,jenny|18,jack|18,owen|28,tony|73,kevin|81,tom|78 |
| 张江第一帅形象代言 | jason|18 |
+-----------------------------+---------------------------------------------------------------
"""字段起别名"""
select post,group_concat(name) as '姓名' from gjz group by post;
+-----------------------------+------------------------------------------------+
| post | 姓名 |
+-----------------------------+------------------------------------------------+
| operation | 程咬铁,程咬铜,程咬银,程咬金,僧龙 |
| sale | 拉拉,乐乐,西西,呵呵,哈哈 |
| teacher | sank,jenny,jack,owen,tony,kevin,tom |
| 张江第一帅形象代言 | jason |
+-----------------------------+------------------------------------------------+
select id as '序号',name as '姓名' from gjz;
# as关键字也可以不写 但是语义不明确 建议加上
select id '序号',name '姓名' from gjz;
