MySQL(三)

MySQL(三)

自增特性

'''
自增属性存在一个特性：不会回退
当一条记录被删除后，再添加记录，自增会从上一条记录那里继续执行
可以通过truncate重置主键实现重新计数自增
'''

外键简介

# 外键是干啥的？
	就是用来记录表之间的关系的
eg:
    表一：存储了用户信息(id账号，密码等等)
    表二：存储了用户的购物车信息(用户id,商品，数量，价格等等)
    怎么查用户的购物车？
    先根据用户信息查找到用户id，再去购物车表根据用户id查记录
    
这里，这里的用户id就是购物车表外键，通过用户表里的用户id才能找到用户的购物车里有什么东西

# 拆表
	有时候我们常常会遇到一个问题，信息太多了，一个表几十个字段，这时候我们就可以把表按照功能进行拆分，通过外键关联起来
   优势:
    	1.表字段少了，更简洁明了
		2.减少表数据重复
        3.提高表的扩展性，让表能更灵活增添字段

外键关系

# 四种表关系
	1.一对多
    2.多对多
    3.一对一
    4.没有关系
    
    """
    关系的判断记住四字口诀:换位思考
    """
# 一对多关系
	上述购物车例子就是一个一对多关系
    一个用户只能有一个用户id
    但是用户可以有多条购物记录
    一条购物记录只能有一个操作的用户，即只能有一个用户id
    
    '''关系表达只能用一对多 不能用多对一'''
    一对多关系 外键字段建在"多"的一方(购物车表)

建立外键的SQL语句

'''
要遵循类似现有服务端再有客户端的逻辑
先建立普通字段，再建立外键字段
'''
create table user(
    id int primary key auto_increment comment '编号',
    username varchar(32) comment '用户名'
);
create table shop_car(
    id int primary key auto_increment comment '编号',
    shop_info varchar(32) comment '购物记录',
    user_id int comment '用户id',
    foreign key(user_id) references user(id)
);
'''
定义外键语法:
	foreign key(外键字段名) references 被关联的表名(被关联的表字段)
'''

# 外键的约束效果
	1.创建表的是由，应先创建被关联表(否者会报错)
    2.插入数据的时候，也要先插入被关联表
    	外键字段只能是被关联表有的值
    3.修改数据，删除数据的时候会出现障碍
    	只有通过级联的方式才能实现数据更新删除
        
# 级联更新级联删除
	create table user(
        id int primary key auto_increment comment '编号',
        username varchar(32) comment '用户名'
    );
    create table shop_car(
        id int primary key auto_increment comment '编号',
        shop_info varchar(32) comment '购物记录',
        user_id int comment '用户id',
        foreign key(user_id) references user(id)
        on update cascade  
  		on delete cascade  
    );
    
"""
在实际工作中外键也可能不会使用 因为外键会消耗额外的资源
并且会增加表的复杂度 
表很多的情况下 我们也可以通过SQL语句的形式建立逻辑意义上的表关系
"""

创建外键

外键直接更新删除障碍

级联更新

多对多关系

#  什么是多对多关系？
eg:
    一本书可以有多个作者
    一个作者可以写多本书
    这就是'多对多'关系
    
# 创建多对多关系的表
	因为两个表之间的字段互相存在多个对应关系，所以需要第三张表来记录量表之间的关系
    
   eg:
    # 创建作者表
    create table author(
        id int primary key auto_increment,
        name varchar(32)
    );
    # 创建数表
    create table book(
        id int primary key auto_increment,
        book_name varchar(32)
    );
    # 创建作者-书关系表
    create table author_book(
        id int primary key auto_increment,
        author_id int,
        book_id int,
        foreign key(author_id) references author(id)  # 针对author表的外键
        on update cascade   # 级联更新
        on delete cascade,  # 级联删除
        foreign key(book_id) references book(id)  # 针对book表的外键
        on update cascade  # 级联更新
        on delete cascade  # 级联删除
    );
    
# 针对多对多表关系 
  两张基表内的数据没有在第三张表内绑定关系的情况下随意新增修改删除

一对一关系

# 什么是一对一关系？
eg:
    一个人
    他的身份证
    这个人只能对应他的身份证
    他的身份证只能对应他这个人
    (造假证除外)
# 代码创建一对一关系
eg:
    # 创建商品简略表
    create table shop(
        id int primary key auto_increment,
        name varchar(32),
        price double
    );
    # 创建商品详情表
    create table shop_details(
        id int primary key auto_increment,
        details varchar(32),
        shop_id int,
        foreign key(shop_id) references shop(id) 
        on update cascade   # 级联更新
        on delete cascade	# 级联删除
    );

表关系总结

# 一对多
	一个表的某个字段值在另一个表里可以有一条以上关于此字段值的记录
# 多对多
	两个表某个字段值都可以在对方表里找到一条以上记录
# 一对一
	一个表的某个字段值在另一个表里只有一条关于此字段值的记录

表查询关键字

# 数据准备
create table emp(
  id int primary key auto_increment,
  name varchar(20) 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 emp(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

最基础也是必不可少的查询关键字
select：指定要查询的字段
from：从哪里查

查询关键字之where筛选

# 作用
	指定条件，筛选需要的数据
# 绝对查询
	关键字：'='
    效果：查询条件等于我们想要的条件的数据
    eg:
        # 查询id=3的数据
        select * from emp where id = 3;
# 范围查询
	关键字：算数运算符、between...and...、and、or、not、in...
    效果：划定范围，查询符合这个范围的数据
    eg:
        # 查询id在3到5之间的数据
        select * from emp where id >= 3 and id <= 5;
        select * from emp where id between 3 and 5;
        # 查询id在3-6或者大于10的数据
        select * from emp where id >= 3 and id <= 6 or id > 10;
       # 查询id不在3-6的数据
    	select * from emp where id <= 3 and id >= 6;
        select * from emp where id not between 3 and 6;
       # 查询id为3,6,9的数据
    	select * from emp where id in (3,6,9);
       # 查询岗位描述为空的数据
    	select * from emp where post_comment is null;
        # 这里不能写'post_comment=null'
        
# 模糊查询
	关键字：like、%、_
    作用：查询包含关键字的数据，%匹配任意字符,_匹配单个字符
    eg:
        # 查询名字带字母a的数据
        select * from emp where name like '%a%';
        # '%a%'的意思就是可能是a，a开头结尾，中间有a的数据
        # 查询名字四个字的数据
        select * from emp where name like '____';
        select * from emp where char_length(name)=4;

查询关键字之group by分组

# 什么是分组？
	就是按照指定的条件把单条的数据组织成一个整体
    
# 为什么要分组？
	可以快速统计数据
    
# 分组查询
	select post from emp group by post;
    # 查询结果根据post字段分组
    
      """
  mysql5.7及以上版本默认自带sql_mode=only_full_group_by
  该模式要求分组之后默认只可以直接获取分组的依据不能直接获取其他字段
  
  原因是分组的目的就是按照分组的条件来管理诸多数据 最小单位应该是分组的依据而不是单个单个的数据
  
  如果是MySQL5.6及以下版本 需要自己手动添加
  """
        
# 聚合函数
	专门用于分组之后的数据统计
  	max				统计最大值
    min				统计最小值
    sum				统计求和
    count			统计计数
    avg				统计平均值
    
   # 1.统计每个部门的最高薪资
      select post,max(salary) from emp group by post;
    # 2.统计每个部门的平均薪资
        select post,avg(salary) from emp group by post;
    # 3.统计每个部门的员工人数
        select post,count(id) from emp group by post;
    # 4.统计每个部门的月工资开销
        select post,sum(salary) from emp group by post;
    # 5.统计每个部门最小的年龄数
        select post,min(age) from emp group by post; 
        
    """间接获取分组以外其他字段的数据"""
    # 1.统计每个部门下所有员工的姓名
        select post,group_concat(name) from emp group by post;
    # 2.统计每个部门下所有员工的姓名和年龄
        select post,group_concat(name,age) from emp group by post;
      select post,group_concat(name,'|',age) from emp group by post;
    """字段起别名"""
        select post,group_concat(name) as '姓名' from emp group by post; 
      select id as '序号',name as '姓名' from emp;
      # as关键字也可以不写 但是语义不明确 建议加上
      select id '序号',name '姓名' from emp;

作业

1.判断表关系
	# 班级表
    cid	caption
    # 学生表
    sid sname gender class_id
    # 老师表
    tid	tname
    # 课程表
    cid	cname	teacher_id
    # 成绩表
    sid	student_id course_id number
    '''
    一个班级有多个学生
    	班级表和学生表一对多
   	一个班级有多个老师，一个老师能带多个班级
   		班级表和老师表多对多
   	一个老师能带多门课程，一门课程一般情况下只能一个老师上
   		老师表和课程表一对多
   	一个学生只能有一张成绩单，一张成绩单只能写一张学生的成绩
   		学生表和成绩表一对一
    '''
2.分组练习题
  1. 查询岗位名以及岗位包含的所有员工名字
    select post_comment,group_concat(name) from emp group by post_comment;
  2. 查询岗位名以及各岗位内包含的员工个数
	select post_comment,count(name) from emp group by post_comment;
  3. 查询公司内男员工和女员工的个数
	select sex,count(id) from emp group by sex;
  4. 查询岗位名以及各岗位的平均薪资
	select post_comment,avg(salary) from emp group by post_comment;
  5. 查询岗位名以及各岗位的最高薪资
select post_comment,max(salary) from emp group by post_comment;
  6. 查询岗位名以及各岗位的最低薪资
    select post_comment,min(salary) from emp group by post_comment;
  7. 查询男员工与男员工的平均薪资，女员工与女员工的平均薪资
	select sex,avg(salary) from emp group by sex;
3.整理今日内容及博客、录音、单词