数据库

8.1、初始数据库

8.1.1、数据的演变

在python学习的过程中，我们一开始的数据是存放在文件中的，和都是按照个人习惯去定制的。后来为了更好的记录数据，有了一条软件开发目录规范。

Foo/
|-- core/
|   |-- core.py
|
|-- api/
|   |-- api.py
|
|-- db/
|   |-- db_handle.py
|
|-- lib/
|   |-- common.py
|
|-- conf/
|   |-- settings.py
|
|-- run.py
|-- setup.py
|-- requirements.txt
|-- README

但这也只是对我们本地的数据做一些操作，真正要联网统一管数据，需要用到数据库。

8.1.2、数据库

  1. 基本概念：数据库的本质是一款基于网络通信的应用程序

  2. 数据库大致分为两种：

    • 关系型数据库：MySQL、Oracle、db2、access、sql server
    • 非关系型数据库：Redis、mongodb、memcache

  3. 关系型数据库和费关系型数据库的区别

    name  password  hobby
jason 123       read
egon  123       eat
tank  123       paly

    • 关系型：

      • 数据间有彼此的关系或者约束

      • 存储数据的表现形式通常是以表格存储

        • 字段名：name password hobby
        • 字段：每个字段名所包含的那一列数据
        • 数据(表单)：除去字段名的所有数据
        • 每个字段都可以设定不同的数据类型限制

    • 非关系型：

      • 存储数据通常是以kv键值对的形式存储。

  4. 重要概念

    库 -------> 文件夹

    表 --------> 文件

    记录 ------> 文件内一行行的数据

8.1.3、MySQL

  1. 基本原理

    任何基于网络通信的应用程序底层都是用socket

    • 服务端

      • 基于socket通信
      • 收发消息
      • SQL语句

    • 客户端

      • 基于socket通信
      • 收发消息
      • SQL语句

    MySQL能支持其他编程语言当成客户端来操作，用的是统一的SQL语句

  2. 重要概念介绍

    • 数据库 == 文件夹
    • 表 == 文件
    • 数据 == 文件内的一行行数据
    • 表头 == 表格内的第一行数据
    • 字段 == 第一行数据的元素

  3. 安装

  4. 启动

    MySQL要在终端上启动，要先启动一个服务端，然后我们在客户端对数据进行操作

    • 先切换到MySQL锁在阿德目录下，然后输入mysqld即可启动MySQL服务端

    • 保留原来的cmd窗口，再重新打开一个cmd窗口运行客户端

      """
常见软件的默认端口号
	MySQL  3306
	redis  6379
	mongodb 27017
	django  8000
	flask   5000
	...
	

MySQL第一次以管理员身份进入是没有密码的 直接回车即可

客户端连接服务端完整命令
	mysql -h 127.0.0.1 -P 3306 -uroot -p
"""

  5. sql基本语句

    MySQL的sql语句都是以；作为结束的标志

    • 查看所有的库名：show databases;
    • 连接服务端的命令简写：mysql -urrot -p
    • 如果不想让服务端执行自己写的错误命令：\c
    • 客户端退出：exit quit
    • 如果输入mysql也连接到服务端。这这是游客模式，不是管理员模式，功能阉割。

  6. 环境变量的配置及系统服务制作

    小知识点补充

    • 如何查看具体进程

      • 查看全部进程：tasklist
      • 查看具体进程：tasklist | findstr mysqld

    • 如何杀死具体进程（必须是管理员cmd窗口下才能成功）

      • taskkill/F/PID 号

环境变量配置

每次启动mysql服务端都要在mysql的bin目录下才能启动，这样的方式明显很麻烦，我们可以通过把mysql文件所在的路径添加到系统的环境变量中。

这样虽然不用到特定的目录下启动服务，但还是要启动两个cmd窗口

最优解是吧mysql服务端制作成系统服务（开机自启）

  • 查看当前计算机运行的进程数

    • 在cmd窗口中输入server.msc

  • 把mysqld制作成系统服务

    • mysqld --install

  • 移除mysqld系统服务

    • mysqld --remove

  1. 关于密码

    • 设置密码指令

      • mysqladmin -uroot -p原密码 password 新密码
      • 直接终端修改即可，不需要进入客户端

    • 破译密码

      • 先关闭mysql服务端

        • mysql获取账号密码的校验可以看做一个装饰器，装饰在客户端访问的功能上
        • mysql --skip-grant-tables

      • 直接以无密码的形式连接

        • mysql -uroot -p 直接回车

      • 修改当前用户密码

        • update mysql user set password =password(123456) where user =“root” and host =”localhost“

      • 立刻休书刷新到硬盘中

        • flush privileges

      • 关闭当前服务器，以正常授权形式启动

    • mysql的密码存储是密文形式。如果忘记了，就必须重置。

  2. 统一编码

    mysql在主文件下有一个ini后缀的配置文件

    这个配置文件就在程序刚开始运行时先加载，在启动

    但一般情况下不会再这个自带的ini文件内对mysql配置信息进修改，通常是自己写一个ini文件让程序运行前加载。

    my.ini

    # 统一编码
[mysqld]
character-set-server=utf8
collation-server=utf8_general_ci
[client]
default-character-set=utf8
[mysql]
user="root"
password=123456
default-character-set=utf8

8.2、数据库基础

8.2.1、基本sql语句

  1. 针对库的增删查改（文件夹）

    # 增加数据库
create database db2;
create database db2 charset = 'gbk';
# 查询数据库
show databases; # 查所有
show create database db2; # 查询指定的数据库
# 修改数据库属性
alter database db2 charset = ‘utf8’;
# 删除数据库
drop database db2

  2. 针对表的增删查改（文件）

    我们要修改表 / 文件时，要先找到这个表 / 文件所在的数据库 / 文件夹。

    # 查看当前所在的库的名字
select database();
# 切换库
use db2;
# 增加表
create table t1(id int,name char(4)) # 内涵字段名和字段的数据类型
# 查询表
show tables # 查询当前库所有的表
show create table t1;# 查询当前库指	定名称的表
# 这种语句查出来的结果是select语句的形式
describe t1； # 支持简写 desc t1；
# 这种语句的查询结果是以表格的形式
# 修改表
alter table t1 modify name char(16) # 修改表某一字段的数据类型
# 删除表
drop table t1;
'''
可以用绝对路径操作不同库的表进行操作
create table db1.t1(id int);
'''

  3. 针对数据的增删查改（一行行的数据）

    # 对数据的操作一定要先有库，有表，最后才能操作

# 增加数据
insert into t2 values(1,'hz'),(2,'egon');

# 查询数据
select * from t2；

# 修改数据
update t2 set name=‘hzdsb’ where id= 1；

# 删除数据
delete from t2 where id = 1；

# 清空表数据
delete from t2；

8.2.2、存储引擎

不同的存储引擎针对不同的对数据的处理机制

  1. mysql主要的存储引擎

    • innodb

      • 是mysql5.5之后默认的存储引擎，存储的数据更加安全

    • myisam

      • 是mysql5.5版本之前的默认存储引擎，速度快

    • memory

      • 内存引擎，断电丢失数据

    • blockhole

      • 无论存储什么数据，都会立即消失

  2. 用代码测试不同的引擎的区别

    # 查看所有的存储引擎
show engines;

    image-20200806141610299

  3. 不同的存储引擎在存储表的时候 差异点

    • create table t1(id int) engine=innodb;

    • create table t2(id int) engine=myisam;

    • create table t3(id int) engine=memory;

    • create table t4(id int) engine=blockhole;

      image-20200806143149124

    • 结论

      对每个表添加一个数据可以看到除了blockhole引擎，其他表的数据都还在，这里要注意，memory的数据只是存储在内存中，重启一下服务器数据就会丢失。

  4. 创建表的完整语法

    create table 表名(
	字段1 类型(宽度 可写可不写) 约束条件,
    字段2 类型(宽度 可写可不写) 约束条件,
    字段3 类型(宽度 可写可不写) 约束条件
)

    注意

    • 在同一张表中的字段名不能重复

    • 宽度和约束条件是可选的，有默认值，字段名和字段类型时必须写的，约束条件可以写多个

    • 最后一行不能有逗号

    • 针对不同版本对数据宽度的限定情况

      • 5.6版本默认没有开启严格模式，如果设定char(1)但是给了多个字符，会自动截取第一个
      • 5.7版本及之后的版本默认开启了严格模式，存多了会报错。

    • 约束条件举例：create table t6(id int,name char not null);

      • not null表示这个字段的数据不插入null

    • 宽度和约束条件的关系

      • 宽度：限制数据的长度
      • 约束条件：在宽度的基础上添加额外的约束

  5. 严格模式

    • 查看严格模式

      • show variables like “%mode”;

    • 模糊查找

      • 关键字like
      • % ： 匹配任意多个字符串
      • __：匹配任意单个字符串

    • 就该严格模式

      • set session 只在当前窗口有效
      • set global 全局有效
      • set global sql_mode = ‘STRICT_TRANS_TABLES’；
      • 修改完成之后重新进入服务端即可

8.2.3、数据类型

8.2.3.1、整型

  1. 分类

    • tinyint 、smallint、medumint、int、bigint

  2. 举例

  • 年龄、等级、ID号

  1. 详细

    # 不同的整型类型最大的区别就是能够取到的范围，如果超出这个范围只会存最大范围（非严格模式）
# 以上的5种整型类型能取得范围依次增大，所以我们通常用int能够应付大部分情况

# 例子：tinyint
如果我们创建了一个tinyint类型的字段，它的范围是-128~127，超出这个范围就会报错

特殊约束，unsigned约束可以让该字段的范围只有正数，会把负数全部带到正数中，也就是说，将负数补位到正数。
create table t6(id tinyint unsigned);
# 这里的id的范围不再是-128~127 而是0~255了

# 例子：
id int(8)
# 如果数字没超过8位，默认用空格填充至8位（显式时会自动去除空格）
# 如果超出了8位，只存前8位，但还是要遵守该类型的最大范围
# 如果超出了最大范围，用最大值代替
# 我们可以用zerofill约束让空格变成0显示出来
create table t7(id int(8) zerofill);

8.2.3.2、浮点型

  1. 分类

    • float、double、decimal

  2. 举例

    • 身高、体重、薪资

  3. 详细

    # 储存限制（不写的默认值）
create tabble t10(id float(255,30));
create table t11(id double(255,30));
create table t12(ud decimal(65,30));

# 插入数据验证区别
insert into t10 values(1.111111111111111111111111111111)
insert into t11 values(1.111111111111111111111111111111)
insert into t12 values(1.111111111111111111111111111111)

    结果：image-20200806151523124

  4. 总结：

    • 精度从小到大：float<double<decimal
    • 应用场景不同用不同的精度

8.2.3.3、字符类型

  1. 类型

    • char定长

      • char(4)数据超过了4个字符直接报错，不够4个用空格补全

    • varchar变长

      • varchar(4)数据超过了4个数据直接报错，不够有几个存几个

  2. 举例验证区别

    可以用length关键字来分析内含几个字符

    修改sql_mode = “STRICT_TRANS_TABLE,PAD_CHAR_FULL_LENGTH”;

    再重启服务后，这个修改能然mysql不作任何剔除操作

    得到的结果是，通那样只插入一个字符的数据，varchar的长度是1，char的长度是4

  3. 对比优缺点

    • char

      • 优点：读取简单，因为我们取的都是最大长度
      • 缺点：浪费空间（因为会自动补全）

    • varchar

      • 优点：节省空间（不会自动补全）
      • 缺点：读取麻烦，存取都需要报头来限定长度

    • 总结：两者各有好处，都有用处

8.2.3.4、日期类型

  1. 分类

    • date：年月日 2020-8-6
    • datetime：年月日时分秒 2020-8-6 11:11:11
    • time：时分秒 11:11:11
    • year：年 2020

  2. 详细

    create table student(
    now_year year,
    now_date date,
    now_datetime datetime,
    now_time time
);

insert into student values('2020','2020-8-6','2020-8-6 11:11:11','11:11:11');

8.2.3.5、枚举和集合类型

  1. 概念

    • 枚举（enum）：多选一
    • 集合（set）:多选多

  2. 具体使用

    create table teacher(
    id int,
    name char(16),
    gender        enum('male','female','others'),
    hobby set('read','play','sing')
);
insert into teacher valus(1,'alex','male','read,play,sing');

  3. 枚举是只能在给定的选项中选一个，集合是在给定的选项中选择多个，此时在插入多个选项时，选项全部在一个引号中，中间用逗号隔开。

8.2.3、约束条件

8.2.3.1、default默认值

  1. 知识点补充：在插入数据时可以指定字段插入

    create table t1(
	id int,
    name char(16)
);
insert into t1(name,id) values('lz',1);

  2. default的使用

    # 创建一张表 内含一个默认字段
create table t2(
	id int,
    gender enum('male','female','others') default 'male',# 这里用什么类型都可以，enum只是举例
)
# 只对这个表id字段插入如数据可以插入成功则验证默认值生效
insert into t2(id) values(1);
insert into t2 values(1,'male');

8.2.3.2、unique唯一

  1. 单列唯一

    # 创建一张表 id字段不能重复
create table t3(
	id int unique,
    name char(16)
)
# 尝试对这个表的id值插入重复数据
insert into t3 values(1,'lz'),(1,'xz');
ERROR 1062 (23000): Duplicate entry '1' for key 'id'
# 报错提示不能重复添加id等于1的数据

  2. 联合唯一

    # 联合唯一的本质是，每一条自己是可以重复的，单恋合在一起就是唯一的
# 例如ip+port  各自是可以重复的，但是ip+port合在一起指定的就是一个唯一的应用程序

# 创建一个ip+port表
create table t4(
	ip int,
    port int,
    unique(ip,port)
)
# 尝试向表中插入相同的数据
insert into t4 values(1,2),(1,2);
ERROR 1062 (23000): Duplicate entry '1-2' for key 'ip'
    
# 尝试向表内插入单列相同，其他列不同的数据
insert into t4 values(1,2),(1,3);
# 操作成功

8.2.3.3、primary key 主键

  1. 主键的基本使用

    # 蛋蛋从约束条件来看，primary key的效果相当于not null + unique
# 非空且唯一
# 创建一个含有主键的表
create table t5(
	id int priamry key,
    name char(16)
);
# 向表中插入数据验证主键的特性
insert into t5 values(1,'lz');
insert into t5 values(null,'xz');  # 报错，主键不能为空

insert into t5 values(1,'lz'); # 报错，主键不能重复

  2. 主键的特性

    • 特性：主键是innodb存储引擎组织数据的依据

      innodb存储引擎在创建表的时候必须有主见，我们在不写主键的时候，mysql检索我们创建的字段，会把一个设置里not null + unique约束的字段升级成主键。如果没有这种字段，mysql会自己创建一个隐藏的主键，这种情况下就无法实现主键高效率的特性了。

    • 目的：是为了索引的方便，类似于数据目录，提高了我们检索数据的效率。

  3. 具体示例

    • 单个字段主键

      create table t5(
	id int primary key,
    name char(16)
);
# 就将id字段设置成主键

    • 联合主键(多个字段连起来的主键，本质上还是一个主键)

      create table t6(
	ip int,
    port int,
    name char(16),
    primary key(ip,port)
);
# 就将ip和port设置成一个联合主键

  4. 总结：主键是一种提升检索效率的结构，我们应该在建表时为唯一不重复的字段设置成主键

8.2.3.4、auto_increment自增

  1. 特性：顾名思义，就是会自动增加的字段

  2. 具体示例

    # 当我们设置一些编号是依次增加的时候，我们人为地每次去输入就会显得多余，这个时候用auto_increment约束就可以让字段自动增加1
# 注意：自增约束只能设置给主键

# 创建爱你一个id自增的表
create table t6(
	id int primary key auto_increment,
    name char(16)
);
# 只对name字段插入数据验证id是否会自增
insert into t6(name) values('lz'),('xz');
# 我们也可以指定id插入，这之后的数据会在我们这条数据上自增。
# 删除数据不会影响后面的自增，这样设置的机制有利于我们查找那些数据被删除

  3. 补充

     delete from t6 # 后面可以跟条件，不跟默认清空整张表，但是自增计数不会清空
    
 truncate t6 # 清空表数据并且清空自增计数

8.2.4、表与表之间的关系

  • 引子：

    我们再定一张学生表的时候，会定义许多字段

    学生：姓名、年龄、班级、课程、成绩、排名......

    定义一个如此庞大的表会出现以下几个问题

    • 表的组织结构不清晰
    • 浪费硬盘空间
    • 数据的可扩展性性很差（重要）

    这种合在一起写就像是在Python中把所有的代码都写在一个py文件中，似然都可以实现，但是可读性和可扩展性都非常差，所以我们根据种类。把不同的字段类型分成不同的表，这里可以吧学生表分成学生基本信息表，学生成绩表

8.2.4.1、外键

  1. 定义：用来帮助我们建立表和表之间的关系的
  2. foreign key

8.2.4.2、表关系

  • 表与表之间只有四种关系

    • 一对多
    • 多对多
    • 一对一
    • 无关系

  1. 一对多关系

    在刚开始学习表关系的时候，我们要站在不同的表上考虑和对方的关系。

    • 举例：学生表和班级表

      • 学生角度：有个学生不能再多个班级里
      • 班级角度：一个班级可以有多个学生
      • 结论：学生表和班级表是一对多的关系

    • 代码实现

      # 代码中外检写在那张表上？
# 写在“多”的那张表上，也就是必须要后创建的表
# 班级里可以有0~无数个学生，但是学生必须有一个班级，所以要先创建班级表

create table class(
	cls_id int primary key,
    class_name char(16)
);
# 这是班级表
create table student(
	id int primary key auto_increment,
    name char(16),
    class_id int,
    foreign key(class_id) references class(cls_id)
)
# 这是学生表

# 先班级表中插入数据，再向学生表中插入数据
insert into class values(1,'1班'),(2,'2班'),(3,'3班');

insert into student(name,class_id) values('jason',1),('egon',1),('tank','2'),('alex',3);

# 这里的班级必须要把班级内的同学全都删了才能删除班级，这样会显得操作非常繁琐，而且数据之间不能通不更新同步删除

# 设置级联更新，级联删除

create table class(
	cls_id int primary key,
    class_name varchar(16)
);
# 这是班级表

create table student(
	id int primary key auto_increment,
    name varchar(16),
    class_id int,
    foreign key(class_id) references class(cls_id)
    on update cascade
    on delete cascade
 );
# 这是学生表

  2. 多对多关系

    • 举例：图书表和作者表

      • 图书角度：一本书可以有多个作者
      • 作者角度：一个作者可以写多本图书
      • 结论：图书表和作者表是多对多关系

    • 重点：在多对多关系中，多对多的外检必须再添加一个表，在这个表中存放图书表和作者表的关系。

    • 代码实现

      # 创建图书表
create table book(
	id int primary key auto_increment,
    name varchar(16),
    price int
);
# 创建作者表
create table author(
	id int primary key auto_increment,
    name varchar(16),
    age int 
);
# 创建两张表的关系表
create table book2author(
	id int primary key auto_increment,
    book_id int,
    author_id int,
    foreign key(book_id) references book(id)
    on update cascade
    on delete cascade
    foreign key(author_id) references author(id)
    on update cascade
    on delete cascade
);
# 在book表中插入数据
insert into book values(1,'python',100),(2,'java',99),(3,'php',69);
# 在author表中插入数据
insert into author values(1,'alex',73),(2,'egon',64),(3,'jason',45);
# 在book2author表中插入数据
insert into book2author(book_id,author_id) values(1,1),(2,1),(3,1),(2,3);

  3. 一对一关系

    • 示例：作者表和作者详情表

      • 一个作者只能对应一个详情表
      • 一个详情表只能对应一个作者
      • 结论：作者表和作者详情表是一对一关系

    • 重点：一对一关系的外键写在哪里都可以，不过通常写在查询频率较高的表中

    • 代码实现

      # 创建作者详情表
create table authot_detail(
	id int primary key auto_increment,
    phone int,
    addr varchar(32)
);
# 创建作者表
create table author(
	id int primary key auto_increment,
    name varchar(16),
    age int,
    author_detail_id int unique,
    foreign key(author_detail_id) references author_detail(id)
    on update cascade # 同步更新
    on delete cascade # 同步删除
);
# 在作者详情表插入数据
inisert into author_detail values(1,123,'xxx'),(2,123,'xxx'),(3,123,'xxx');
# 在作者表中插入数据
insert into author values(1,'alex',45,2),(2,'egon',1),(3,'jason',3);

8.2.4.3、修改表

# mysql对大小是不敏感的
"""
1 修改表名
	alter table 表名 rename 新表名；

2 添加字段
	alter table 表名 add 字段名 字段类型（宽度） 约束条件,
	alter table 表名 add 字段名 字段类型（宽度） 约束条件,
	alter table 表名 add 字段名 字段类型（宽度） 约束条件
	
3 删除字段
	alter table 表名 drop 字段名；
	
4 修改字段
	alter table 表名 modify 字段名 字段类型（宽度） 约束条件；
	
	alter table 表名 change 旧表名 新字段名 字段类型（宽度）约束条件；
"""

8.2.4.4、复制表

"""
我们sql语句查询的结果其实也就是一张虚拟表
"""
create table 表名 select * from 旧表;不能复制主键，外键....
create table new_class select * from class where id >3;

8.3、mysql进阶

8.3.1、select查询扩展

  • 查询格式

    • 筛选内容用逗号隔开，筛选条件用空格隔开

  • 前期表准备

    create table emp(
  id int not null unique 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);

    补充：如果把字符编码全部修改成utf8还是不能插入中文或者显示空格，将编码改回GBK即可

  1. 几个重要关键字的执行顺序

    • 书写顺序

      • select id ,name from emp where id > 3;

    • 执行顺序

      • from where select

  2. where筛选条件

    # where是我们拿到整个表数据最先筛选的操作

# 1 查询id大于等于3小于等于6的数据
select id from emp where 3<id<6;
select id from emp where id >=3 and id <=6;
select id from emp where id between 3 and 6;

# 2 查询薪资是20000或者18000或者17000的数据
select * from emp where salary in (20000,18000,17000);

# 3 查询员工名字中带有o的员工的姓名和薪资
"""
补充：模糊查询
		like
			% 匹配任意多个字符
			_ 匹配任意单个字符
"""
select name,salary from emp where name like '%o%';

# 4 查询员工名字是由四个字符组成的名字和薪资
select name,salary from emp where name like '____';
select name,salary from emp where char_length(name) = 4;

# 5 查询id小于3或者大于6 的数据
select id from emp where id<3 or id >6;

# 6 查询薪资不在20000,18000,17000范围的数据
select name,salary from emp where salary not in (20000,18000,17000);

# 7 查询岗位描述为空的员工姓名和岗位名，针对NULL，不用=号，用is
select name,post from emp where post_comment is null;

  3. group by分组

    • 分组实际应用那个场景

      • 男女分组
      • 薪资分组
      • 国家分组
      • ....

    • 注意分组的坑：

      • 要把sql_mode 设置成“STRICT_TRANS_TABLES,ONLY_FULL_GROUP_BY”模式

    • 聚合函数

      """
max() 最大值
min() 最小值
sum() 求和
avg() 平均值
count() 计数
"""

    • 代码测试

      """
分组后，最小可操作单位是组，不是组内的单个数据
"""
# 前期操作
set global sql_mode = 'strict_trans_tables,only_full_group_by'
# 这句代码可以让我们只能拿到分组，不拿分组内的数据

# 1 获取每个部门最高薪资
# as 可以起别名
select post as '部门', max(salary) as '最高薪资' from emp group by post;

# 2 获取每个部门的最低薪资
select post as '部门', min(salary) as '最低薪资' from emp group by post;

# 3 获取每个部门的平均薪资
select post as '部门', avg(salary) as '平均薪资' from emp group by post;

# 4 获取每个部门的工资总和
select post as '部门', sum(salary) as '薪资总和' from emp group by post;

# 5 获取每个部门的总人数
select post as '部门', count(id) as '总人数' from emp group by post
# 注意 count后面最好用id，其他的也行，但是绝对不能有null

# 6 获取分组后的部门名称和每个部门下左右的员工姓名
# group_concat 不单单可以支持获取分组后的其他字段的集合，还支持拼接操作
select post as '部门',group_concat(name) as '名字' from emp group by post; # 单纯地显示单个数据
select post as '部门'，group_concat(name,'SB') as '名字' from emp group by post; # 使用了拼接
select post as '部门'，group_concat(name,':',salary) as '薪资' from emp group by post;

# 7 查询每个人的年薪 12薪
select name,salary*12 from emp;

    • 补充

      # concat 是在不分组的时候使用,仅仅只是用于拼接
select concat('name:'，name),concat('sal:',salary) from emp;

# as 语法不仅可以给字段起别名，还可以给表起别名
select * from emp as t1;
select t1.id,t1.name from emp as t1;

    • 分组注意事项

      """
关键字where和group by同时出现的时候，group by必须在where后面

where筛选条件内不能出现聚合函数
where max(salary) 错误示范
"""

# 统计各部门年龄在30岁以上的员工的平均薪资
# 第一步：
	select * from emp where age>30;
# 第二部：
	select post,avg(salary) from group by post;
# 第三部：
	select post, avg(salary) from where age>30 group by post;

  4. having分组之后的筛选条件

    • having的语法是跟where一致的，不过having是在分组后的过滤操作，且可以用聚合函数

      # 统计各部门年龄在30岁以上的员工的平均工资，且保留平均工资大于10000的部门
select post,avg(salary) from emp where age>30 group by post having avg(salary)>10000;

  5. distinct去重

    • 去重的关键是重的定义

    • 但数据完全一致是，才可以进行去重操作

    • 结论：我们在去重是不能有主键

      select distinct id,age from emp; # 错误
select distinct age from emp;    # 正确

  6. order by 排序

    """
order by 排序默认是升序 asc(可以不写)
desc 降序
order by 可以有多重排序
"""
# 先用age降序，如果age相同，再用salary升序

select * from emp order by age desc order by, salary asc; 

# 统计各部门年龄在10岁以上的员工的平均薪资并且保留平均薪资大于1000的部门。然后对平均薪资进行降序排序
select post avg(salary) from emp where age>10 group by post having avg(salary)>1000 order by avg(salary) desc;

  7. limit 限制数据的展示条数

    # 当我们玉带数据过多的情况下，通常采用分页处理
select * from emp limit 3; # 只显示3条数据

select * from emp limit 0,5; 
# 从第0个位置开始，展示5条数据

select * from emp limit 10,6;
# 从第10个位置开始，展示6条数据

    • limit总结

      • 第一个数字是数据展示的起始位置
      • 第二个数据是数据展示的条数

  8. 正则表达式

    • 和之前学的正则是一样的使用

      select * from emp name regexp '^j.*(n|y)$';
# 筛选出以j开头，以n或者y结尾的员工名字

  9. 多表操作

    前期表准备

    #建表
create table dep(
id int,
name varchar(20) 
) charset GBK;

create table emp(
id int primary key auto_increment,
name varchar(20),
sex enum('male','female') not null default 'male',
age int,
dep_id int
);

#插入数据
insert into dep values
(200,'技术'),
(201,'人力资源'),
(202,'销售'),
(203,'运营');

insert into emp(name,sex,age,dep_id) values
('jason','male',18,200),
('egon','female',48,201),
('kevin','male',18,201),
('nick','male',28,202),
('owen','male',18,203),
('jerry','female',18,204);

    • 连表操作

      select * from dep,emp;
# 得到的结果是dep的每个数据和emp数据的意义对应，笛卡尔积

# 上面的这种方法结果是不合理的，我们可以下面这种方法
select * from emp,dep where emp.dep_id = dep.id

# 还有更加智能的操作
"""
	inner join 内连接
	left join 左连接
	rigth join 右连接
	union 全连接
"""
# inner join 内连接
select * from emp inner join dep on emp.dep_id = dep.id;
# 只拼接两张表共有的部分

# left join 左连接
select * from emp left join dep on emp.dep_id = dep.id;
# 左边表显示全部，没有的就用null补上

# rigth join 右连接
select * from emp rigth join dep on emp.dep_id = dep.id;
# 右边表显示全部，没有的就用null补上

# union 全连接
select * from emp left join dep on emp.dep_id = dep.id;
union
select * from emp rigth join dep on emp.dep_id = dep.id;
# 左边右边表都保留，没有的用null补全

    • 子查询

      """
子查询就是我们平时解决问题的思路
	分步骤解决问题
		第一步
		第二步
		......
"""
# 查询部门是技术部或者人力资源部的员工信息
	# 第一步：先获取部门表中技术部和人力资源部的id号
	# 第二步：再去员工表里面被选出对应员工信息

# 第一步：
	select id from dep where name = '技术部' or name = '人力资源部';
    ======> 200,201
# 第二步：
	select * from emp where dep_id in(200,201);
# 第三步：
	select * from emp where dep_id in (select id from dep where name = '技术部' or name = '人力资源部');

# 等以后熟练了以后，可以直接跳过第一二步。

    • 总结

      # 表查询结果可以直接作为其他表的查询条件
# 也可以通过起别名的方式把它作为一张虚拟表跟其他表相关联
"""
多表查询就两种方式
	先拼接在查询
	子查询一步一步来
"""

8.3.2、navicat使用

  • 可视化界面操作数据库
  • 创建库（文件夹）、创建表（文件）、写字段都是些常规操作，如果想进一步了解数据库，可以去看模型，也可以手写sql语句。

8.3.3、pymysql模块

  1. 定义：pymysql是python中通过python语句对数据库进行操作

  2. 安装：pip3 install pymysql

  3. 基本使用

    import pymysql

# 连接数据库
conn = pymysql.connect(
	host = '127.0.0.1', # 服务端ip地址
    port = 3306, # 数据库的默认端口
    user = 'root', # 用户名
    password = '123456', # 密码
    database = 'day47', # 操作的库
    charset = 'utf8' # 指定编码，千万注意不能加-
)

cursor = conn.cursor() # 产生一个游标，就相当于我们已经连接mysql正在准备输入指令，通常不使用
cursor = conn.cursor(cursor=pymysql.cursor.Dictcursor) # 通常使用这一种，获取数据更加准确

sql = 'select * from teacher;' # sql语句
res = cursor.execute(sql) # 执行sql语句
# execute 返回的是当前sql语句得到的数据的行数，通常不用
# print(cursor.fetchone()) # 得到一条数据
# print(cursor.fetchall()) # 得到全部数据
# print(cursor.fetchmany(n)) # 得到n条数据

# 游标(光标)的移动
cursor.scrool(1,'relative') # 相对于当前位置光标位置，光标向后移动一位
cursor.scroll(1,'absolute') # 相对于数据开头位置，光标向后移动一个位置

  4. 针对pymysql的增删查改

    import pymysql 

conn = pymysql.connect(
	host = '127.0.0.1',
    port = 3306,
    user = 'root',
    password = '123456',
    database = 'day47',
    charset = 'utf8',
    autocommit = True # 后面的增、删、改操作就不用再写commit操作了
) # 连接数据库

cursor = conn.cursor(cursor = pymysql.cursor.Dictcursor)

# 增
sql = "insert into student values(17,'男')"
rows = cursor.execute(sql) # 一次只能添加一条数据
rows = cursor.executemany(sql,[(),(),()...]) # 一次添加多条数据
conn.commit() # 确认操作

# 改
sql = "update student set sname = 'hhhzzz' where sid = 1"
rows = cursor.execute(sql)
conn.commit() # 确认操作

# 删
sql = "delete from student where sid = 2"
rows = cursor.execute(sql)
conn.commit() # 确认操作

"""
在涉及对文件增、删、改时，需要二次确认
"""

  5. pymysql实现存储过程演示

    # 定义存储过程
delimiter $$
create proceduce p1(
	in n int, # in:只进不出，n不能返回除去
    in m int,
    out res int # 该参数可以返回
	# in锁对应的变量时供我们下面的sql语句使用的，out对应的变量相当于一个标识，这个变量修改了，标识我们执行了存储过程
)
begin
	select tname from teacher where tid >n and tid < m;
    set res = 666;
end $$
delimiter ;

# 针对形参res 不能直接传数据，应该传一个变量名

"""
定义变量
	set res = 10;
查看变量对应的数据
	select res;
"""

# python代码实现过程
import pymysql 

conn = pymysql.connect(
	host = '127.0.0.1',
    port = 3306,
    user = 'root',
    password = '123456',
    database = 'day47',
    charset = 'utf8',
    autocommit = True
)
cursor = conn.cursor(cursor=pymysql.cursors.Dictcursor)

# 调用存储过程
cursor.callproc('p1',(1,5,10))
print(cursor.fetchall())

8.3.4、sql注入

当我们需要让用户去写一些sql代码时，比如用户输入的信息去数据库搜索时，如果用户输入的是一些操作数据库的代码，就会造成sql注入问题。

  • 示例

    import pymysql

conn = pymysql.connect(
	host = '127.0.0.1',
    port = 3306,
    user = 'root',
    password = '123456',
    database = 'day47',
    charset = 'utf8',
    autocommit = True
)

cursor = conn.cursor(cursor = pymysql.cursors.Dictcursor)

username = input('>>>>>：')
password = input('>>>>>：')
sql = 'select * from user where name = %s password = %s' %(useranme,password)

rows = cursor.execute(sql)
if rows:
    print('登录成功')
    print(cursor.fetchall())
else:
    print('密码错误')
    
# 如果我们在输入用户名时输入--就相当于mysql中的注释，直接把密码搜索注释掉了，就不用验证了
"""
利用一些语法的特性，书写一些特点的语句实现固定的语法
MySQL利用的是MySQL的注释语法

select * from user where name='jason' --fsijgnnfjfkdkhdjkdjjsnhbdjkmsjd and password=''

select * from user where name='xxx' or 1=1 -- adjdnfj and password=''
"""

  • 总结：利用一些语法的特性，书写一些有特点的语句固定语法。MySQL利用的是注释语法

  • sql注入的解决方案

    # 在日常生活中很多软件在注册的时候不能输入含有特殊的字符
# 因为怕你构造出特定的语句入侵数据库 不安全

# 敏感的数据不要自己做拼接，交给execute帮你做拼接即可
# 结合数据库完成一个用户登录功能

import pymysql 

conn = pymysql.connect(
	host = '127.0.0.1',
    port = 3306,
    user = 'root',
    password = '123456',
    database = 'day47',
    charset = 'utf8',
    autocommit = True
)

cursor = conn.cursor(cursor= pymysql.cursors.Dictcursor)

username = input('>>>>>:')
password = input('>>>>>:')

sql = 'select * from user where name=%s password=%s'
# 不要手动拼接数据，先用占位符之后将需要拼接的数据交给execute方法即可
print(sql)
rows = cursor.execute(sql,(username,password)) # 自动识别sql里面的%s，用后面元组里面的数据补充

if rows:
    print('登录成功')
    print(cursor.fetchall())
else:
    print('密码错误')

8.3.5、基于所学写作业

# 前期表准备
create table emp(
  id int not null unique 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);

# 1 查询岗位名以及岗位包含的所有员工名字
select post,group_concat(name) from emp group by post;

# 2 查询岗位名以及各岗位内包含的员工个数
select post,count(id) from emp group by post; 

# 3 查询公司内男员工和女员工的个数
select sex,count(id) from emp group by sex;

# 4 查询岗位名以及各岗位的平均薪资
select post,avg(salary) from emp group by post;

# 5 查询岗位名以及各岗位的最高薪资
select post,max(salary) from emp group by post;

# 6 查询岗位名以及各岗位的最低薪资
select post,min(salary) from emp group by post;

# 7 查询男员工与男员工的平均薪资，女员工与女员工的平均薪资
select sex,avg(salary) from emp group by sex;

8.4、数据库最终章

8.4.1、视图

  1. 什么是视图

    • 视图是通过查询得到的一张虚拟表，将其保存下来
    • 所以，视图也是一张表

  2. 为什么要有视图

    • 把常用的查询表操作保存下来或者拼表操作，可以减少代码

  3. 如何操作

    • create view 视图名 as 虚拟表的查询sql语句

  4. 注意事项

    """
1 创建视图在硬盘上只会有表结构，没有表数据（数据来源于原来查询到的表）
2 视图通常只用于查询 不要修改里面的数据
3 视图的使用频率不高，创建多了会影响我们判断表与表之间的结构
"""

  5. 总结：了解即可，基本不用

8.4.2、触发器

  1. 定义：在满足对表数据进行增、删、改情况下，自动触发的功能

  2. 作用：使用触发器可以帮组我们实行监控、日志等等

  3. 基本语法结构

    # 基本语法
delimiter $$
create trigger 触发器的名字(要见名知意) before/after  insert/update/delete on 表名 for each row
begin
	sql语句
end
delimiter ;

"""
befter	after
inseret update delete
2对3 6种情况
增前 增后 删前 删后 改前 改后
"""

# 举例
delimiter $$
create trigger tri_after_insert_t1 after insert on t1 for each row
begin
	sql语句
end $$
delimiter ;

"""
######### 补充 #########
修改mysql结束条件
delimiter $$ # 表示接下来的mysql造作结束符号改为$$ 关闭窗口就该回来了
"""

# 删除触发器
drop trigger 触发器名字;

  4. 示例

    create table cmd(
	id int primary key auto_increment,
    user char(32),
    priv chahr(10),
    cmd char(64),
    sub_time datetime, # 提交时间呢
    success enum('yes','no') # 0代表执行失败
);

create table errlog(
	id int primary key auto_increment,
    err_cmd char(64),
    err_time datetime
);

"""
当cmd表中的记录success字段是no时，那么就出发触发器去执行errlog表中插入数
NEW指代的是一条条数据对象
"""
delimiter $$ 
create trigger tri_insert cmd_after insert on cmd for each row
begin
	if NEW.success = 'no' then
    insert into errlog(err_cmd,err_time) values(NEW.cmd,NEW.sub_time);
	end if;
end $$
delimiter ;

# 朝cmd表中插入数据
insert into cmd(
	user,
    priv,
    cmd,
    sub_time,
    success
)
values
	('jason','0755','ls -l /etc',NOW(),'yes'),
    ('jason','0755','cat /etc/passwd',NOW(),'no'),
    ('jason','0755','useradd xxx',NOW(),'no'),
    ('jason','0755','ps aux',NOW(),'yes');

8.4.3、事务

  1. 定义：事务为多条sql语句共同执行，只有共同成功或者失败

  2. 事物的四大特性：ACID

    • A：原子性

      • 一个事物是不可分割的单位，事务包含着诸多操作，要么同时成功，要么同时失败

    • C：一致性

      • 事务必须是使数据库从一个一致性的状态变到另外一个一致性的状态
      • 一致性和原子性密切相关

    • I：隔离性

      • 一个事务的执行不能被其他事务干扰，并发执行的事务之间也是互补干扰的

    • D：持久性/永久性

      • 指一个事务一旦执行成功，那么它对数据库中的数据的修改是永久的
      • 接下来的其他操作和故障不会对他有影响

  3. 如何使用事务

    # 事务相关的关键字
# 1 开启事务
start transaction;
# 开启事务再确认和回滚之前都属于事务代码
# 2 回滚操作（当事物中间出现一个错误，回到事务执行之前的状态）
rollbck
# 3 确认（确认后就没法回滚了）
commit

8.4.4、存储过程

  1. 定义：存储过程类似于python中的自定义函数

  2. 基本使用

    # 创建存储过程
delimiter $$
create proceduce 存储过程的名字(形参1,形参2...)
begin
	sql语句
end $$
delimiter ;

# 调用
call 存储过程名字();

  3. 存储过程mysql演示

    delimiter $$
create proceduce p1(
	in n int, # 只进不出  m不能返回出去
    in m int,
    out res int, # 该形参可以返回出去
    # in所对应的变量时供我们下面sql语句使用的，out对应的变量相当于一个标识，这个变量修改了，表示我们执行了存储过程
)
begin
	select tname from teacher where tid>n and tid<m;
    set res = 666;  # 将res变量修改 用来标识当前的存储过程代码确实执行了
end $$
delimiter ;

# 针对形参res 不能直接传数据，应该传入一个变量名

# 定义变量
set res = 10;
# 查看变量对应的值
select res;

  4. python中的mysql存储过程

    import pymysql


conn = pymysql.connect(
    host = '127.0.0.1',
    port = 3306,
    user = 'root',
    passwd = '123456',
    db = 'day48',
    charset = 'utf8',
    autocommit = True
)
cursor = conn.cursor(pymysql.cursors.DictCursor)
# 调用存储过程
cursor.callproc('p1',(1,5,10))
"""
在pymysql内部，变量的存储形式
@_p1_0=1
@_p1_1=5
@_p1_2=10
"""
# print(cursor.fetchall())
cursor.execute('select @_p1_2;')
p5. rint(cursor.fetchall())

  5. 三种开发模式

    • 第一种

      • 应用程序：程序员写代码开发
      • MySQL：提前写好存储过程，供程序调用
      • 优点：开发效率，执行效率高
      • 缺点：需要考虑沟通问题，存储过程可扩展性差

    • 第二种

      • 应用程序、MySQL：都是程序员写
      • 优点：可扩展性高
      • 缺点：开发效率低、sql语句很繁琐，还要考虑优化

    • 第三种

      • 应用程序：只写代码，用别人写好的操作mysql的python框架直接操作
      • 优点：开发效率高
      • 缺点：语句的可扩展性差，可能会出现效率低下的情况

    • 总结：第一种基本不用，基本都是第三种，出现效率问题就自己解决。

8.4.5、函数

  1. 定义：函数和存储过程是对应的，存储过程就是python中的自定义函数，而函数就类似于python中的内置函数

  2. 简单举例：如果我们想当时存储时间，可以自己手动添加，也可以使用NOW()

    ('jason','0755','ls -l /etc',NOW(),'yes')

CREATE TABLE blog (
    id INT PRIMARY KEY auto_increment,
    NAME CHAR (32),
    sub_time datetime
);

INSERT INTO blog (NAME, sub_time)
VALUES
    ('第1篇','2015-03-01 11:31:21'),
    ('第2篇','2015-03-11 16:31:21'),
    ('第3篇','2016-07-01 10:21:31'),
    ('第4篇','2016-07-22 09:23:21'),
    ('第5篇','2016-07-23 10:11:11'),
    ('第6篇','2016-07-25 11:21:31'),
    ('第7篇','2017-03-01 15:33:21'),
    ('第8篇','2017-03-01 17:32:21'),
    ('第9篇','2017-03-01 18:31:21');

select date_format(sub_time,'%Y-%m'),count(id) from blog group by date_format(sub_time,'%Y-%m');

8.4.6、流程控制

  1. 定义：就是mysql版本的if多分支和while判断

  2. 具体示例

    # if判断
delimiter //
CREATE PROCEDURE proc_if ()
BEGIN
    declare i int default 0;
    if i = 1 THEN
        SELECT 1;
    ELSEIF i = 2 THEN
        SELECT 2;
    ELSE
        SELECT 7;
    END IF;
END //
delimiter ;
# while循环
delimiter //
CREATE PROCEDURE proc_while ()
BEGIN
    DECLARE num INT ;
    SET num = 0 ;
    WHILE num < 10 DO
        SELECT
            num ;
        SET num = num + 1 ;
    END WHILE ;

8.4.7、索引

  1. 定义：索引就是一种数据结构，类似于书的目录。如：

    • 找拼音
    • 找偏旁
    • 或者我们提前知道了页码直接翻过去
    • 或者一页一页找

    这些不同的去书中找字的办法就是索引，在计算机中就是用不同的办法去硬盘中找数据，这就是索引，不同的索引给了我们对不同应用场景的需求实现

  2. 分类

    • primary key （主键）
    • unique key （uni键）
    • index key

  3. 注意：

    • foreign key不是用来加速查询的，只是用来建立表与表之间关系的
    • 上面的三种key，前两种不仅可以家属查询，还有约束条件，比如主键是非空且唯一，uni键是唯一的，index只是用来加速查询的

  4. 本质：索引是为了让我们缩下想查找数据的范围，减少搜索时间，提高效率，也就是如果我们记录下一条索引，以后再继续照着挑数据的时候就会非常快

  5. 优缺点

    索引虽然能给我们在搜索特定数据的时候变得非常快速，但是也有缺点

    • 当表中有大量数据的时候，创建索引会非常慢

      • 一维索引的创建本质上需要用最原始的方法去搜索数据，索引是拍出了其他错误路线上直接帮我们找到数据

    • 在索引创建完毕后，搜索的性能提高了，创建时性能会降低

      • 因为在索引创建完毕后，对标的结构也定死了，如果我们要添加数据，相当于破坏了表数据的排列，索引就必须重新创建

8.4.8、b+树

  1. 定义：把从用户到数据看作一棵树，用户是树根，找到树叶上的数据需要经历多次对数据路径的判断，每个枝条放的是虚拟数据，用来帮我们区分数据在哪个分支上。
  2. 具体定义：在分支上的数据，相当于一个个索引，他告诉我们这个分支派生出去的子分支哪里可能有我们的数据。所以我们用id字段作为索引，因为id是int类型，相比与其他类型他能在分支上存储的越多。
  3. 结论：当分支上的数据越多，说明在一个分支上对数据路径分析越详细，我们就可以通过越少的分支得到数据。走的路越少，我们得到数据的速度就越快

8.4.9、 聚集索引(primary key)

聚焦索引指的就是主键

innodb 只有两个文件 直接将主键存放到idb表中

MyIsam 三个文件 单独将索引存放到一个文件中

8.4.10、辅助索引

我们查询的时候不是一直用id(主键)，也可能用到其他字段，这时候就要用到辅助索引，我们从辅助索引到最后去拿到真正的数据

  1. 覆盖索引

    • 如果我们在辅助索引的中途就找到了数据，就直接返回结果了

      select name from user where name = 'jason';

  2. 非覆盖索引

    • 还是需要用到主键

      select age from uer where name = 'jason';

8.5、终极作业

8.5.1、 表准备

# 创建班级表
create table class(
	cid int primary key auto_increment,
	caption char(32)
);
# 插入数据
insert into class(caption) values('三年二班'),('三年三班'),('一年二班'),('二年九班');
===================================================================================================
# 创建学生表
create table student(
	sid int primary key auto_increment,
	gender char(32),
	class_id int ,
	sname char(32),
	foreign key(class_id) references class(cid)
	on update cascade
	on delete cascade
);
# 插入数据
insert into student(gender,class_id,sname) values('男',1,'李杰'),('女',1,'钢蛋'),('男',1,'张三'),('男',1,'张一'),('女',1,'张二'),('男',1,'张四'),('女',2,'铁锤'),('男',2,'李三'),('男',2,'李一'),('女',2,'李二'),('男',2,'李四'),('女',3,'如花'),('男',3,'刘三'),('男',3,'刘一'),('女',3,'刘二'),('男',3,'刘四');
===================================================================================================
# 创建分数表
create table score(
	sid int primary key auto_increment,
	sutdent_id int,
	course_id int,
	num int,
	foreign key(sutdent_id) references student(sid)
	on update cascade
	on delete cascade,
	foreign key(course_id) references course(cid)
	on update cascade
	on delete cascade
);
# 插入数据
insert into score values(1,1,1,10),(2,1,2,9),(3,2,1,65),(4,5,4,87),(5,1,4,66),(6,2,1,8),(8,2,3,68),(9,2,4,99),(10,3,1,77),(11,3,4,99),(12,3,3,87),(13,3,4,99),(14,4,1,79),(15,4,2,11),(16,4,3,67),(17,4,4,100),(18,5,1,79),(19,5,2,11),(20,5,3,67),(21,5,4,100),(22,6,1,9),(23,6,2,100),(24,6,3,67),(25,6,4,100),(26,7,1,9),(27,7,2,100),(28,7,3,67),(29,7,4,88),(30,8,1,9),(31,8,2,100),(32,8,3,67),(33,8,4,88),(34,9,1,91),(35,9,2,88),(36,9,3,67);
===================================================================================================
# 创建课程表
create table course(
	cid int primary key auto_increment,
	cname char(32),
	teacher_id int,
	foreign key(teacher_id) references teacher(tid)
	on update cascade
	on delete cascade
);
# 插入数据
insert into course(cname,teacher_id) values('生物',1),('物理',2),('体育',3),('美术',2);
===================================================================================================
# 创建教师表
create table teacher(
	tid int primary key auto_increment,
	tname char(32)
);
# 插入数据
insert into teacher(tname) values('张磊老师'),('李平老师'),('刘海燕老师'),('朱云海老师'),('李杰老师')

8.5.2、题目

# 1 查询所有的课程的名称以及对应的任课老师姓名
SELECT
	course.cname,
	teacher.tname 
FROM
	course
	INNER JOIN teacher ON course.teacher_id = teacher.tid;
============================================================
select course,cname,teacher,tname from course inner join teacher on course.teacher_id = teacher.tid;
    
# 2 查询平均成绩大于八十分的同学的姓名和平均成绩
SELECT
	student.sname,
	t1.avg_num 
FROM
	student
	INNER JOIN ( SELECT sutdent_id, avg( num ) AS avg_num FROM score GROUP BY score.sutdent_id HAVING avg( num )> 80 ) AS t1 ON t1.sutdent_id = student.sid;
============================================================
select student.sname,t1.avg_num from student inner join
(select sutdent_id,avg(num) as avg_num from score group by score.sutdent_id having avg(num)>80) as t1 on t1.sutdent_id=student.sid;


# 3 查询没有报李平老师课的学生姓名
SELECT
	student.sname 
FROM
	student 
WHERE
	sid IN (
	SELECT DISTINCT
		score.sid 
	FROM
		score
		INNER JOIN ( SELECT course.cid FROM course INNER JOIN teacher ON course.teacher_id = teacher.tid WHERE teacher.tname = '李平老师' ) AS course2teacher ON score.course_id = course2teacher.cid 
	);
============================================================
select student.sname from student where sid in 
(select distinct score.sid from score inner join 
(select course.cid from course inner join teacher on course.teacher_id=teacher.tid where teacher.tname='李平老师') as course2teacher on score.course_id=course2teacher.cid);
    
# 4 查询没有同时选修物理课程和体育课程的学生姓名(只选择一门)
SELECT
	* 
FROM
	student 
WHERE
	student.sid IN (
	SELECT
		score.sutdent_id 
	FROM
		score 
	WHERE
		score.course_id IN (
		SELECT
			course.cid 
		FROM
			course 
		WHERE
		course.cname IN ( '物理', '体育' )) 
	GROUP BY
		score.sutdent_id 
	HAVING
		count( score.sid )= 1 
	);
============================================================
select * from student where student.sid in 
(select score.sutdent_id from score where score.course_id in
(select course.cid from course where course.cname in ('物理','体育')) group by score.sutdent_id having count(score.sid)=1);

    
# 5 查询挂科超过两门(包括两门)的学生姓名和班级
SELECT
	class.caption,
	t1.sname 
FROM
	class
	INNER JOIN (
	SELECT
		* 
	FROM
		student 
	WHERE
	sid IN ( SELECT score.sutdent_id FROM score WHERE score.num < 60 GROUP BY score.sutdent_id HAVING count( score.sid )>= 2 )) AS t1 ON class.cid = t1.class_id;
============================================================
select class.caption,t1.sname from class inner join
(select * from student where sid in
(select score.sutdent_id from score where score.num<60 group by score.sutdent_id having count(score.sid)>=2)) as t1 on class.cid=t1.class_id;
posted @ 2020-08-25 16:14  今天捡到一百块钱  阅读(101)  评论(0)  编辑  收藏  举报