数据库知识
数据库
day37 数据库基本知识
1.数据库概念
#数据库
# 很多功能如果只是通过操作文件来改变数据是非常繁琐的
# 程序员需要做很多事情
# 对于多台机器或者多个进程操作用一份数据
# 程序员自己解决并发和安全问题比较麻烦
# 自己处理一些数据备份，容错的措施
# C/S架构的 操作数据文件的一个管理软件
# 1.帮助我们解决并发问题
# 2.能够帮助我们用更简单更快速的方式完成数据的增删改查
# 3.能够给我们提供一些容错、高可用的机制
# 4.权限的认证
# 数据库管理系统 —— 专门用来管理数据文件，帮助用户更简洁的操作数据的软件
# DBMS
# 数据 data
# 文件
# 文件夹 -- 数据库database db
# 数据库管理员 —— DBA
# 数据库管理系统
关系型数据库
# sql server
# oracle 收费、比较严谨、安全性比较高
# 国企 事业单位
# 银行 金融行业
# mysql 开源的
# 小公司
# 互联网公司
# sqllite
非关系型数据库 #【查快递，一个快递单号，后面跟了很多信息】
# redis
# mongodb
2.数据库基本命令
#!/usr/bin/env python
# -*- coding:utf-8 -*-
# 安装mysql遇到的问题
# 操作系统的问题
# 缺失dll文件
# 安装路径
# 不能有空格
# 不能有中文
# 不能带着有转义的特殊字符开头的文件夹名
# 安装之后发现配置有问题
# 再修改配置往往不能生效
# 卸载之后重装
# mysqld remove
# 把所有的配置、环境变量修改到正确的样子
# 重启计算机 - 清空注册表
# 再重新安装
****************************************分割线******************************
# mysql的CS架构
# mysqld install 安装数据库服务
# net start mysql 启动数据库的server端
# 停止server net stop mysql
# 客户端可以是python代码也可以是一个程序
# mysql.exe是一个客户端
# mysql -u用户名 -p密码
# mysql中的用户和权限
# 在安装数据库之后，有一个最高权限的用户root
# mysql 192.168.12.87 eva/123
# mysql -h192.168.12.87 -uroot -p123
# 我们的mysql客户端不仅可以连接本地的数据库
# 也可以连接网络上的某一个数据库的server端
# select user();
查看当前用户是谁
# set password = password('密码')
设置密码
# 创建用户
create user 's21'@'192.168.12.%' identified by '123';
mysql -us21 -p123 -h192.168.12.87
# 授权
grant all on day37.* to 's21'@'192.168.12.%';
# 授权并创建用户
grant all on day37.* to 'alex'@'%' identified by '123';
# 查看文件夹
# show databases;
# 创建文件夹
# create database day37;
# 库 表 数据
DDL数据库定义语言 ；创建库、创建表
DML数据库操纵语句；存数据，删除数据,修改数据,查看
DCL控制权限；grant/revoke
# 库
create database 数据库名; # 创建库
show databases; # 查看当前有多少个数据库
select database();# 查看当前使用的数据库
use 数据库的名字; # 切换到这个数据库(文件夹)下
# 表操作
查看当前文件夹中有多少张表
# show tables;
创建表
# create table student(id int,name char(4));
删除表
# drop table student;
查看表结构
# desc 表名;
# 操作表中的数据
数据的增加
# insert into student values (1,'alex');
# insert into student values (2,'wusir');
数据的查看
# select * from student;
修改数据
# update 表 set 字段名=值
# update student set name = 'yuan';
# update student set name = 'wusir' where id=2;
删除数据
# delete from 表名字;
# delete from student where id=1;
# 把创建用户，给用户授权，登陆一下同桌的mysql server，给同桌开一个账号
# 操作库 表 数据
day 38 数据库数据类型
1.内容回顾
#!/usr/bin/env python
# -*- coding:utf-8 -*-
# 数据库
# 关系型数据库 ：mysql oracle sqlserver sqllite
# 非关系型数据库 ：mongodb redis
# sql ：
# ddl 定义语言
# 创建用户
# create user '用户名'@'%' 表示网络可以通讯的所有ip地址都可以使用这个用户名
# create user '用户名'@'192.168.12.%' 表示192.168.12.0网段的用户可以使用这个用户名
# create user '用户名'@'192.168.12.87' 表示只有一个ip地址可以使用这个用户名
# 创建库
# create database day38;
# 创建表
# create table 表名(字段名 数据类型(长度)，字段名 数据类型(长度)，)
# dml 操作语言
# 数据的
# 增 insert into
# 删 delete from
# 改 update
# 查 select
# select user(); 查看当前用户
# select database(); 查看当前所在的数据库
# show
# show databases: 查看当前的数据库有哪些
# show tables；查看当前的库中有哪些表
# desc 表名；查看表结构
# use 库名；切换到这个库下
# dcl 控制语言
# 给用户授权
# grant select on 库名.* to '用户名'@'ip地址/段' identified by '密码'
# grant select,insert
# grant all
2.今日内容
# mysql的表的操作
# 创建方式 存储 —— 存储引擎
# 基础数据类型
# 表的约束
# 创建表
# 查看表结构
# 修改表
# 删除表
3.习题讲解
#!/usr/bin/env python
# -*- coding:utf-8 -*-
# 有一个文件，这个文件中有20001行数据，开启一个线程池，为每100行创建一个任务，打印这100行数据。
from copy import copy
def print_line(lines):
print(lines)
from concurrent.futures import ThreadPoolExecutor
tp = ThreadPoolExecutor(20) # 起一个线程池
with open('file',encoding='utf-8') as f:
lines = []
for line in f:
if len(lines) == 100:
tp.submit(print_line,copy(lines)) #读是一个线程，打印是20个线程，读的时候实现并发
lines.clear()
lines.append(line)
if lines:
tp.submit(print_line, copy(lines))
============================== 分割线======================================
# 1. print 和 文件的读 写 都是io操作,
# 这个题主要就是起线程在一个线程读文件的时候，利用这个线程读取文件的时间交给另一个线程来进行打印操作
# 2. 使用线程可以有效的规避掉io操作的时间，提高程序的的效率
# 3.解耦程序的功能
# 4.默认参数是列表
4.表的存储引擎
# 表的存储方式
# 存储方式1：MyISAM 5.5以下 默认存储方式
存储的文件个数：表结构、表中的数据、索引
支持 表级锁
不支持 行级锁 不支持事务 不支持外键
# 存储方式2：InnoDB 5.6以上 默认存储方式
适合处理并发
存储的文件个数：表结构、表中的数据
支持 行级锁、支持表级锁
【在修改一行数据的时候，别的用户可以获取】
支持 事务
支持 外键
# 存储方式3： MEMORY 内存
存储的文件个数：表结构
优势 ：增删改查都很快
劣势 ：重启数据消失、容量有限
# 索引 - 数据库的目录
===============================分割线===================================
# 查看配置项:
show variables like '%engine%';
# 创建表
create table t1 (id int,name char(4));
create table t2 (id int,name char(4)) engine=myisam;
create table t3 (id int,name char(4)) engine=memory;
# 查看表的结构
show create table 表名; 能够看到和这张表相关的所有信息
desc 表名 只能查看表的字段的基础信息
# describe 表名
面试题
# 公司用什么数据库 ： mysql
# 版本是什么 ：5.6
# 都用这个版本么 ：不是都用这个版本
# 存储引擎 ：innodb
# 为什么要用这个存储引擎：（它是默认引擎）
# 支持事务 支持外键 支持行级锁
# 能够更好的处理并发的修改问题
5.数据类型--数字
# 整数 int
# create table t4 (id1 int(4),id2 int(11));
int默认是有符号的
它能表示的数字的范围不被宽度约束
它只能约束数字的显示宽度
# create table t5 (id1 int unsigned,id2 int);
# 小数 float
# create table t6 (f1 float(5,2),d1 double(5,2));
# create table t7 (f1 float,d1 double);
# create table t8 (d1 decimal,d2 decimal(25,20));
6.数据类型--时间
# 类型
year 年
date 年月日
time 时分秒
datetime、timestamp 年月日时分秒
# create table t9(
# y year,d date,
# dt datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
# ts timestamp);
7.数据类型--字符串
# char(15) 定长的单位
# alex alex
# alex
# varchar(15) 变长的单位
# alex alex4
# 哪一个存储方式好？
# varchar ：节省空间、存取效率相对低
# char ：浪费空间，存取效率相对高 长度变化小的
# 手机号码、身份证号 char
# 用户名、密码 char
# 评论 微博 说说 微信状态 varchar
# create table t11 (name1 char(5),name2 varchar(5));
8.其他数据类型--ENUM和SET类型
# enum 枚举 set 集合
create table t12(name char(12),gender enum('male','female'),hobby set('抽烟','喝酒','烫头','洗脚'));
# insert into t12 values('alex','不详','抽烟,喝酒,洗脚,洗脚,按摩');
ENUM只允许从值集合中选取单个值，而不能一次取多个值。
set类型可以允许值集合中任意选择1或多个元素进行组合。对超出范围的内容将不允许注入，而对重复的值将进行自动去重。
day39 约束、表操作、数据操作
1.内容回顾
#!/usr/bin/env python
# -*- coding:utf-8 -*-
查看引擎：show create table s1
# 存储引擎
# myisam ：适合做读 插入数据比较频繁的，对修改和删除涉及少的，不支持事务、行级锁和外键。有表级锁
# 索引和数据分开存储的，mysql5.5以下默认的存储引擎
# 3个文件
# innodb ：适合并发比较高的，对事务一致性要求高的，
# 相对更适应频繁的修改和删除操作，有行级锁，外键且支持事务
# 索引和数据是存在一起的，mysql5.6以上默认的存储引擎
# 2个文件
# memory ：数据存在内存中，表结构存在硬盘上，查询速度快，重启数据丢失
# 数据类型
# 数字类型 ：int，float(5,2)
# 字符串类型 ：char(10)，varchar(10) 10 最大不超过是个字符
# 时间类型：datetime，date，time
# enum和set：enum(单选项1,单选项2)，set(多选项1，多选项2)
# create table 表名(
# 字段名 数据类型(宽度/选项)，
# 字段名 数据类型(宽度/选项)，
# )
# 创建库
# create database day39
# 查看有哪些库
# show databases;
# 查看当前所在的数据库
# select database();
# 切换库
# use 库名
# mysql支持的函数
# database(),user(),now(),concat(),password()
# create table staff (
# id int,
# name char(12),
# gender enum('male','female'),
# hire_date date,
# salary float(8,2),
# hobby set('唱','跳','rap','打篮球'))
2.今日内容
# 操作表
# 约束
# 修改表结构
# 删除表
# drop table 表名;
# 操作数据
# 插入数据
# 修改数据
# 删除数据
# 查找数据
# 单表查
# 多表查
3.约束
#!/usr/bin/env python
# -*- coding:utf-8 -*-
# 约束
# unsigned 设置某一个数字无符号 【约束 int 】
# not null 某一个字段不能为空 【严格模式会影响非空设置的效果，非空可能不生效】
# default 给某个字段设置默认值 【一般与enum（）结合】
# unique 设置某一个字段不能重复
# 联合唯一
# unique（字段1，字段2）
# auto_increment 设置某一个int类型的字段 自动增加
#前提：是数字且 唯一unique
# auto_increment自带not null效果
# 设置条件 int unique
# primary key 设置某一个字段非空且不能重复
# 约束力相当于 not null + unique
# 一张表只能由一个主键
# 联合主键 primary（字段1，字段2）
# foreign key 外键
# references
# 级联删除和更新 on update cas
# 外键关联的那张表的字段必须是unique
# not null
# default
# create table t2(
# id int not null,
# name char(12) not null,
# age int default 18,
# gender enum('male','female') not null default 'male'
# )
# unique 设置某一个字段不能重复
# create table t3(
# id int unique,
# username char(12) unique,
# password char(18)
# );
# 联合唯一
# create table t4(
# id int,
# ip char(15),
# server char(10),
# port int,
# unique(ip,port)
# );
# 自增
# auto_increment 自带not null 效果
# 自增字段 必须是数字 且 必须是唯一的
# create table t5(
# id int unique auto_increment,
# username char(10),
# password char(18)
# )
# insert into t5(username,password) values('alex','alex3714')
# primary key 主键 = not null + unique
# 一张表只能设置一个主键
# 一张表最好设置一个主键
# 约束这个字段 非空（not null） 且 唯一（unique）
# create table t6(
# id int not null unique, # 你指定的第一个非空且唯一的字段会被定义成主键
# name char(12) not null unique
# )
# create table t7(
# id int primary key, # 你指定的第一个非空且唯一的字段会被定义成主键
# name char(12) not null unique
# )
# 联合主键
# create table t4(
# id int,
# ip char(15),
# server char(10),
# port int,
# primary key(ip,port)
# );
# 外键 foreign key 涉及到两张表
# 员工表
# create table staff(
# id int primary key auto_increment,
# age int,
# gender enum('male','female'),
# salary float(8,2),
# hire_date date,
# post_id int,
# foreign key(post_id) references post(pid)
# )
# 部门表
# pid postname post_comment post_phone
# create table post(
# pid int primary key,
# postname char(10) not null unique,
# comment varchar(255),
# phone_num char(11)
# )
# update post set pid=2 where pid = 1;
# delete from post where pid = 1;
# 级联删除和级联更新
# create table staff2(
# id int primary key auto_increment,
# age int,
# gender enum('male','female'),
# salary float(8,2),
# hire_date date,
# post_id int,
# foreign key(post_id) references post(pid) on update cascade on delete set null
# )
4.修改表
#!/usr/bin/env python
# -*- coding:utf-8 -*-
# 创建项目之前
# 项目开发、运行过程中
# alter table 表名 add 添加字段
# alter table 表名 drop 删除字段
# alter table 表名 modify 旧字段名字 的类型 宽度 约束
# alter table 表名 change 旧字段名字 新段名字 的类型 宽度 约束
# alter table 表名 add 字段名 数据类型(宽度) 约束 first/after name
# alter table 表名 drop 字段名
# alter table 表名 modify name varchar(12) not null
# alter table 表名 change name new_name varchar(12) not null
# id name age
# alter table 表名 modify age int not null after id;
# alter table 表名 modify age int not null first;
# 删除表
# drop table 表名
5.表关系
# -*- coding:utf-8 -*-
# 两张表中的数据之间的关系
# 多对一 foreign key 永远是在多的那张表中设置外键
# 多个学生都是同一个班级的
# 学生表 关联 班级表
# 学生是多 班级是一
# 一对一 foreign key +unique
# 后出现的后一张表中的数据 作为外键，并且要约束这个外键是唯一的
【被关联的和关联的都要约束unique】
# 客户关系表 ： 手机号码 招生老师 上次联系的时间 备注信息
# 学生表 ：姓名 入学日期 缴费日期 结业
# 多对多 产生第三张表，把两个关联关系的字段作为第三张表的外键
# 书
# 作者
# 出版社
# 书
6.数据修改
#!/usr/bin/env python
# -*- coding:utf-8 -*-
# 增加 insert
# 删除 delete
# 修改 update
# 查询 select
# 增加 insert
# insert into 表名 values (值....)
# 所有的在这个表中的字段都需要按照顺序被填写在这里
# insert into 表名(字段名，字段名。。。) values (值....)
# 所有在字段位置填写了名字的字段和后面的值必须是一一对应
# insert into 表名(字段名，字段名。。。) values (值....),(值....),(值....)
# 所有在字段位置填写了名字的字段和后面的值必须是一一对应
# value单数 values复数
# 一次性写入一行数据 一次性写入多行数据
# t1 id,name,age
# insert into t1 value (1,'alex',83)
# insert into t1 values (1,'alex',83),(2,'wusir',74)
# insert into t1(name,age) value ('alex',83)
# insert into t1(name,age) values ('alex',83),('wusir',74)
# 第一个角度
# 写入一行内容还是写入多行
# insert into 表名 values (值....)
# insert into 表名 values (值....)，(值....)，(值....)
# 第二个角度
# 是把这一行所有的内容都写入
# insert into 表名 values (值....)
# 指定字段写入
# insert into 表名(字段1，字段2) values (值1，值2)
# 删除 delete
# delete from 表 where 条件;
# 更新 update
# update 表 set 字段=新的值 where 条件；
# 查询
# select语句
# select * from 表
# select 字段,字段.. from 表
# select distinct 字段,字段.. from 表 # 按照查出来的字段去重
# select 字段*5 from 表 # 按照查出来的字段去重
# select 字段 as 新名字,字段 as 新名字 from 表 # 按照查出来的字段去重
# select 字段 新名字 from 表 # 按照查出来的字段去重
7.作业
# 1.默写
# 创建库
# create database day39
# 查看有哪些库
# show databases;
# 查看当前所在的数据库
# select database();
# 切换库
# use 库名
# 2.建表
# 把表与表之间的关系 约束 都加上 再完成博客上的建表联系
# 3.修改表结构的sql语句敲一遍
# 4.select语句后面的练习题
# 1 查出所有员工的名字，薪资,格式为
# <名字:egon> <薪资:3000>
# 2 查出所有的岗位（去掉重复）
# 3 查出所有员工名字，以及他们的年薪,年薪的字段名为annual_year
====================================================================
答案
4-1. select concat('名字:',emp_name,'薪资:',salary) from employee;
4-2.select distinct post from employee;
4-3.select concat('名字：',emp_name,'annual_year:',salary*12) from employee;
一张表关联2个外键
create table score(sid int primary key auto_increment,student_id int ,course_id int,number int
,foreign key(student_id) references student(sid))
,foreign key(course_id) references course(cid));
多对多例子
=====================多对多=====================
#'多对多：一个作者可以写多本书，一本书也可以有多个作者，双向的一对多，即多对多'
关联
create table book(
id int primary key auto_increment,
name varchar(20),
press_id int not null);
insert into book(name,press_id) values
('九阳神功',1),
('九阴真经',2),
('九阴白骨爪',2),
('独孤九剑',3),
('降龙十巴掌',2),
('葵花宝典',3);
create table author(
id int primary key auto_increment,
name varchar(20)
);
#插入四个作者，id依次排开
insert into author(name) values('egon'),('alex'),('yuanhao'),('wpq');
==========第三张表=========做关联---------
create table author2book(
id int not null unique auto_increment,
author_id int not null,
book_id int not null,
foreign key(author_id) references author(id)
on delete cascade on update cascade,
foreign key(book_id) references book(id)
on delete cascade on update cascade,
primary key(author_id,book_id)
);
insert into author2book(author_id,book_id) values
(1,1),
(1,2),
(1,3),
(1,4),
(1,5),
(1,6),
(2,1),
(2,6),
(3,4),
(3,5),
(3,6),
(4,1);
#每个作者与自己的代表作如下
egon:
九阳神功
九阴真经
九阴白骨爪
独孤九剑
降龙十巴掌
葵花宝典
alex:
九阳神功
葵花宝典
yuanhao:
独孤九剑
降龙十巴掌
葵花宝典
wpq:
九阳神功
========================显示结果
mysql> select * from author2book;
+----+-----------+---------+
| id | author_id | book_id |
+----+-----------+---------+
| 1 | 1 | 1 |
| 2 | 1 | 2 |
| 3 | 1 | 3 |
| 4 | 1 | 4 |
| 5 | 1 | 5 |
| 6 | 1 | 6 |
| 7 | 2 | 1 |
| 8 | 2 | 6 |
| 9 | 3 | 4 |
| 10 | 3 | 5 |
| 11 | 3 | 6 |
| 12 | 4 | 1 |
+----+-----------+---------+
day40 单表查询、多表查询
【重点】分组、聚合、pymysql、单表查询（顺序）、多表查询
1.内容回顾
#!/usr/bin/env python
# -*- coding:utf-8 -*-
# 约束
# unsigned 整数类型
# not null 非空（严格模式会影响非空设置的效果）
# default 设置默认值
# unique 唯一
# 联合唯一 unique(字段1，字段2)
# auto_increment 自增
# 前提：必须是数字 且唯一
# 自带：非空 自增效果
# primary key 主键
# 一张表只能由一个主键，并且最好有一个主键
# 约束作用：非空且唯一
# 联合主键：
# primary key(字段1，字段2)
# foreign key 外键
# foreign key(本表字段-外键名) references 外表名(外表字段)；
# 外键关联的那张表中的字段必须 unique
# 级联操作：on update cascade on delete cascade
# 表与表之间的关系
# 多对一
# foreign key(多) references 表(一)
# 多个学生对应一个班级
# 多个员工对应一个部门
# 多本书对应一个作者
# 多个商品对应一个店铺、订单
# 一对一
# 后一 类型 unique
# foreign key(后一) references 表(先一)
# 一个客户对应一个学生 学生表有外键
# 一个商品 有一个商品详情 详情页中有外键
# 多对多
# 第三张表
# foreign key(外键名1) references 表1(主键)
# foreign key(外键名2) references 表2(主键)
# 多个学生对应一个班级，多个班级对应一个学生
# 多个员工对应一个部门，多个部门对应一个员工
# 多本书对应一个作者，多个作者对应一本书
# 多个商品对应一个店铺、订单，多个店铺对应一个商品
# 一个学生学习多门课程，一门课程被多个学上学习
# 数据的操作
# 增
# insert into 表 values (一条数据),(另一条数据);
# insert into 表(字段1，字段2) values (值1,值2),(值1,值2);
# 删
# delete from 表 where 条件;
# 改
# update 表 set 字段=值 where 条件
# 查
# 单表查
# select语句
# select * from 表
# select 字段 from 表
# select 字段，字段2 from 表
# select 字段 as 新名字 from 表
# select 字段 新名字 from 表
# select distince 字段 from 表； 去重
# select 字段 *10 from 表
2.默写讲解
# book ：id name price author_id
# author:aid name birthday gender
# 作者与书 一对多
# create table author(
# aid primary key auto_increment,
# name char(12) not null,
# birthday date,
# gender enum('male','female') default 'male'
# )
# create table book(
# id int primary key,
# name char(12) not null,
# price float(5,2)
# author_id int,
# foreign key(author_id) references author(aid)
# )
# 作者与书一对一
# create table author(
# aid primary key auto_increment,
# name char(12) not null,
# birthday date,
# gender enum('male','female') default 'male'
# )
# create table book(
# id int primary key,
# name char(12) not null,
# price float(5,2)
# author_id int unique,
# foreign key(author_id) references author(aid)
# )
# 作者与书多对多
# create table author(
# aid primary key auto_increment,
# name char(12) not null,
# birthday date,
# gender enum('male','female') default 'male'
# )
# create table book(
# id int primary key,
# name char(12) not null,
# price float(5,2)
# )
# create table book_author(
# id int primary key auto_increment,
# book_id int not null,
# author_id int not null,
# foreign key(book_id) references book(id),
# foreign key(author_id) references author(aid),
# );
3.where 语句
where字句中可以使用：
\1. 比较运算符：> < >= <= <> !=
\2. between 80 and 100 值在80到100之间
\3. in(80,90,100) 值是80或90或100 not in
\4. like 'e%'
通配符可以是%或_，
%表示任意多字符
_表示一个字符
\5. 逻辑运算符：在多个条件直接可以使用逻辑运算符 and or not
\6.对于值是null where 条件 is null ，is not null；
#!/usr/bin/env python
# -*- coding:utf-8 -*-
# 比较运算 > < = >= <= != <>
# 范围筛选
# 多选一 字段名 in (值1,值2，值3)
# 20000,30000,3000,19000,18000,17000
# select * from employee where salary in (20000,30000,3000,19000,18000,17000)
# 在一个模糊的范围里 between 10000 and 20000
# 在一个数值区间 1w-2w之间的所有人的名字
# select emp_name from employee where salary between 10000 and 20000;
# 字符串的模糊查询 like
# 通配符 % 匹配任意长度的任意内容
# 通配符 _ 匹配一个字符长度的任意内容
# 正则匹配 regexp 更加细粒度的匹配的时候
# select * from 表 where 字段 regexp 正则表达式
# select * from employee where emp_name regexp '^j[a-z]{5}'
# 逻辑运算 - 条件的拼接
# 与 and
# 或 or
# 非 not
# select * from employee where salary not in (20000,30000,3000,19000,18000,17000)
# 身份运算 - 关于null is null /is not null
# 查看岗位描述不为NULL的员工信息
# select * from employee where post_comment is not null;
# 查看岗位是teacher且名字是jin开头的员工姓名、年薪
#select emp_name,salary*12 from employee where post='teacher' and emp_name like 'jin%'
#select emp_name,salary*12 from employee where post='teacher' and emp_name regexp '^jin.*'
4.分组、聚合
#!/usr/bin/env python
# -*- coding:utf-8 -*-
# 分组 group by
# select * from employee group by post
# 会把在group by后面的这个字段，也就是post字段中的每一个不同的项都保留下来
# 并且把值是这一项的的所有行归为一组
# 聚合【聚合函数】 把很多行的同一个字段进行一些统计，最终的到一个结果
# count(字段) 统计这个字段有多少项
# sum(字段) 统计这个字段对应的数值的和
# avg(字段) 统计这个字段对应的数值的平均值
# min(字段)
# max(字段)
# 分组聚合
# 求各个部门的人数
# select count(*) from employee group by post
# 求公司里 男生 和女生的人数
# select count(id) from employee group by sex
# 求各部门的平均薪资
# 求各部门的平均年龄
# 求各部门年龄最小的
# select post,min(age) from employee group by post
# 求各部门年龄最大的
# 求各部门薪资最高的
# 求各部门薪资最低的
# 求最晚入职的
# 求最早入职的
# 求各部门最晚入职的
# 求各部门最早入职的
# 求部门的最高薪资或者求公司的最高薪资都可以通过聚合函数取到
# 但是要得到对应的人，就必须通过多表查询
# 总是根据会重复的项来进行分组
# 分组总是和聚合函数一起用 最大 最小 平均 求和 有多少项
5.having、orderby、limit
#！！！执行优先级从高到低：where > group by > having
#1. Where 发生在分组group by之前，因而Where中可以有任意字段，但是绝对不能使用聚合函数。
#2. Having发生在分组group by之后，因而Having中可以使用分组的字段，无法直接取到其他字段,可以使用聚合函数
#!/usr/bin/env python
# -*- coding:utf-8 -*-
# having 条件 # 过滤 组
# 部门人数大于3的部门
# select post from employee group by post having count(*) > 3
# 1.执行顺序 总是先执行where 再执行group by分组
# 所以相关先分组 之后再根据分组做某些条件筛选的时候 where都用不上
# 2.只能用having来完成
# 平均薪资大于10000的部门
# select post from employee group by post having avg(salary) > 10000
# select * from employee having age>18
# order by
# order by 某一个字段 asc; 默认是升序asc 从小到大
# order by 某一个字段 desc; 指定降序排列desc 从大到小
# order by 第一个字段 asc,第二个字段 desc;
# 指定先根据第一个字段升序排列，在第一个字段相同的情况下，再根据第二个字段排列
# limit
# 取前n个 limit n == limit 0,n
# 考试成绩的前三名
# 入职时间最晚的前三个
# 分页 limit m,n 从m+1开始取n个
# 员工展示的网页
# 18个员工
# 每一页展示5个员工
# limit n offset m == limit m,n 从m+1开始取n个
6.单表查询顺序
# select distinct 需要显示的列 from 表 |
# where 条件 |
# group by 分组 |
# having 过滤组条件 |
# order by 排序
# limit 前n 条
1.找到表:from
2.拿着where指定的约束条件，去文件/表中取出一条条记录
3.将取出的一条条记录进行分组group by，如果没有group by，则整体作为一组
4.执行select（去重）
5.将分组的结果进行having过滤
6.将结果按条件排序：order by
7.限制结果的显示条数 limit
7.pymysql模块
============================== 基本用法 =================================
import pymysql
# conn = pymysql.connect(host='127.0.0.1', user='root', password="123",
# database='day40')
# cur = conn.cursor() # 数据库操作符 游标
# # cur.execute('insert into employee(emp_name,sex,age,hire_date) '
# # 'values ("郭凯丰","male",40,20190808)')
# # cur.execute('delete from employee where id = 18')
# conn.commit()
# conn.close()
============================== 基本用法 =================================
# conn = pymysql.connect(host='127.0.0.1', user='root', password="123",
# database='day40')
# cur = conn.cursor(pymysql.cursors.DictCursor) # 数据库操作符 游标
# cur.execute('select * from employee '
# 'where id > 10')
# ret = cur.fetchone()
# print(ret['emp_name'])
# # ret = cur.fetchmany(5)
# ret = cur.fetchall()
# print(ret)
# conn.close()
# select * from employee where id > 10
# sql注入风险
8.多表查询
#!/usr/bin/env python
# -*- coding:utf-8 -*-
# 多表查询
# 两张表是怎么连在一起的
# select * from emp,department; # 交叉连接：不适用任何匹配条件。生成笛卡尔积
# 连表查询
# 把两张表连在一起查
# 内链接 inner join 两张表条件不匹配的项不会出现再结果中
# select * from emp inner join department on emp.dep_id = department.id;
# 外连接
# 左外连接 left join 永远显示全量的左表中的数据 ，没有就为null
# 本质就是：在内连接的基础上增加左边有右边没有的结果
# select * from emp left join department on emp.dep_id = department.id;
# 右外连接 right join 永远显示全量的右表中的数据，没有就为null
# select * from emp right join department on emp.dep_id = department.id;
# 全外连接
全外连接：在内连接的基础上增加左边有右边没有的和右边有左边没有的结果
# select * from emp left join department on emp.dep_id = department.id
# union
# select * from department right join emp on emp.dep_id = department.id;
9.子查询
# 子查询
# 找技术部门的所有人的姓名
# 先找到部门表技术部门的部门id
# select id from department where name = '技术';
# 再找emp表中部门id = 200
# select name from emp where dep_id = (select id from department where name = '技术');
# 找到技术部门和销售部门所有人的姓名
# 先找到技术部门和销售部门的的部门id
# select id from department where name = '技术' or name='销售'
# 找到emp表中部门id = 200或者202的人名
# select name from emp where dep_id in (select id from department where name = '技术' or name='销售');
# select emp.name from emp inner join department on emp.dep_id = department.id where department.name in ('技术','销售');
# 连接的语法
# select 字段 from 表1 xxx join 表2 on 表1.字段 = 表2.字段;
# 常用
# 内链接
# 左外链接
# 找技术部门的所有人的姓名
# select * from emp inner join department on emp.dep_id = department.id;
# +----+-----------+--------+------+--------+------+--------------+
# | id | name | sex | age | dep_id | id | name |
# +----+-----------+--------+------+--------+------+--------------+
# | 1 | egon | male | 18 | 200 | 200 | 技术 |
# | 2 | alex | female | 48 | 201 | 201 | 人力资源 |
# | 3 | wupeiqi | male | 38 | 201 | 201 | 人力资源 |
# | 4 | yuanhao | female | 28 | 202 | 202 | 销售 |
# | 5 | liwenzhou | male | 18 | 200 | 200 | 技术 |
# +----+-----------+--------+------+--------+------+--------------+
# select * from emp inner join department on emp.dep_id = department.id where department.name = '技术'
# select emp.name from emp inner join department d on emp.dep_id = d.id where d.name = '技术'
# 找出年龄大于25岁的员工以及员工所在的部门名称
# select emp.name,d.name from emp inner join department as d on emp.dep_id = d.id where age>25;
# 根据age的升序顺序来连表查询emp和department
# select * from emp inner join department as d on emp.dep_id = d.id order by age;
# 优先使用连表查询，因为连表查询的效率高
# 练习
# 查询平均年龄在25岁以上的部门名
# 部门名 department表
# select name from department where id in (
# select dep_id from emp group by dep_id having avg(age)>25
# );
# 员工表
# select dep_id,avg(age) from emp group by dep_id;
# select dep_id from emp group by dep_id having avg(age)>25;
# 查看不足1人的部门名(子查询得到的是有人的部门id)
# 查emp表中有哪些部门id
# select dep_id from emp group by dep_id;
# 再看department表中
# select * from department where id not in (???)
# select * from department where id not in (select dep_id from emp group by dep_id);
# 查询大于所有人平均年龄的员工名与年龄
# select * from emp where age>(select avg(age) from emp);
# 查询大于部门内平均年龄的员工名、年龄
# select dep_id,avg(age) from emp group by dep_id;
# select * from emp inner join (select dep_id,avg(age) avg_age from emp group by dep_id) as d
# on emp.dep_id = d.dep_id where emp.age > d.avg_age;
10.day40作业
1.单表查询基础的语法和顺序背下来
2.继续把下午的小练习做完
3.多表查询的连表和子查询的语法的整理
4.课上的所有sql语句敲一遍
5.根据博客上的要求完成 ：查询每个部门最新入职的那位员工
select * from employee where hire_date in (select max(hire_date) from employee group by office);
6.记忆一下 pymysql的用法
day41 索引原理、事物
1.内容回顾
#!/usr/bin/env python
# -*- coding:utf-8 -*-
# 单表查询
# select (distinct)
# from
# where
# 比较运算符 > < = != <> >= <=
# 逻辑运算符 and or not
# 身份运算符 is is not --> null
# 范围
# 多选一 ：in (12,34,6) / in ('技术','销售')
# 数字的范围：between a and b
# 字符串的范围
# like % _
# regexp 正则
# group by 分组
# 聚合函数 count sum avg min max
# having 过滤组
# 在groupby之后执行的
# having中可以使用聚合函数
# having 后面的条件要么是select的字段，要么是分组的字段
# order by
# 排序 asc（默认，从小到大）； desc（从大到小）
# limit (offset)
# limit n
# limit m,n == limit n offset m
# 多表查询
# 连表查询 更高效
# 内连接 inner join
# 外连接 left join/rignt join
# 子查询
# 在查询一个结果的时候，依赖一个条件也是一个sql语句
2.习题讲解
#!/usr/bin/env python
# -*- coding:utf-8 -*-
# 用户表 user
# create table user(
# uid int primary key auto_increment,
# username char(12)
# )
# 订单表 orders
# create table orders(
# oid int primary key auto_increment,
# otime datetime,
# payment float(10,2),
# uid int,
# foreign key(uid) references user(uid)
# )
# insert into user(username) values ('alex'),('太白');
# insert into orders(otime,payment,uid) values (20190524093800,1320,1),(20180524093800,2000,2);
# 查订单表，按照支付时间从早到晚排序
# 从小到大
# select * from orders order by otime;
# 查询每个用户购买的订单数
# select uid,count(*) from orders group by uid; uid和订单数
# select * from user inner join (select uid,count(*) from orders group by uid) as o on user.uid = o.uid;
# select username,count_order from user inner join (select uid,count(*) as count_order from orders group by uid) as o on user.uid = o.uid;
# 查购买的订单总额最高的用户名
# 查购买的订单总额最高的uid
# select uid,sum(payment) from orders group by uid;
# select uid,sum(payment) as sum_pay from orders group by uid order by sum_pay desc;
# select uid,sum(payment) as sum_pay from orders group by uid order by sum_pay desc limit 1;
# select username from user inner join (select uid,sum(payment) as sum_pay from orders group by uid order by sum_pay desc limit 1) pay on user.uid = pay.uid;
# 删除订单编号为3的订单信息
# delete from orders where oid = 2;
# 订单4的支付金额修改成400
# update orders set payment=400 where oid=4;
3.今日内容
# 索引原理
# 正确的使用索引
# 数据库的备份，表的备份
# 事务和锁
# sql注入和pymsql
4.索引原理
数据库的存储方式
索引的种类
聚集索引和辅助索引
#!/usr/bin/env python
# -*- coding:utf-8 -*-
# 什么是索引 -- 目录
# 就是建立起的一个在存储表阶段
# 就有的一个存储结构能在查询的时候加速
# 索引的重要性
# 读写比例 ： 10：1
# 读（查询）的速度就至关重要了
# 索引的原理
# block 磁盘预读原理
# for line in f
# 4096个字节
# 读硬盘的io操作的时间非常的长，比CPU执行指令的时间长很多
# 尽量的减少IO次数才是读写数据的主要要解决的问题
# 数据库的存储方式
# 新的数据结构 —— 树
# 平衡树 balance tree - b树
# 在b树的基础上进行了改良 - b+树
# 1.分支节点和根节点都不再存储实际的数据了
# 让分支和根节点能存储更多的索引的信息
# 就降低了树的高度
# 所有的实际数据都存储在叶子节点中
# 2.在叶子节点之间加入了双向的链式结构
# 方便在查询中的范围条件
# mysql当中所有的b+树索引的高度都基本控制在3层
# 1.io操作的次数非常稳定 【通过索引，io基本稳定在三次左右，就能查到了，回表也就5次】
# 2.有利于通过范围查询
# 什么会影响索引的效率 —— 树的高度
# 1.对哪一列创建索引，选择尽量短的列做索引
# 2.对区分度高的列建索引，重复率超过了10%那么不适合创建索引
# 聚集索引和辅助索引
# 在innodb中 聚集索引和辅助索引并存的
# 聚集索引 - 主键 更快
# 数据直接存储在树结构的叶子节点
# 辅助索引 - 除了主键之外所有的索引都是辅助索引 稍慢
# 数据不直接存储在树中
# 在myisam中 只有辅助索引，没有聚集索引
# 索引的种类
# primary key 主键 聚集索引 约束的作用：非空 + 唯一
# 联合主键
# unique 自带索引 辅助索引 约束的作用：唯一
# 联合唯一
# index 辅助索引 没有约束作用
# 联合索引
# 看一下如何创建索引、创建索引之后的变化
# create index 索引名字 on 表(字段)
# DROP INDEX 索引名 ON 表名字;
# 索引是如何发挥作用的
# select * from 表 where id = xxxxx
# 在id字段没有索引的时候，效率低
# 在id字段有索引的之后，效率高
5.正确使用索引
索引不生效的原因？
#!/usr/bin/env python
# -*- coding:utf-8 -*-
# 以email为条件查询
# 不添加索引的时候肯定慢
# 查询的字段不是索引字段，也慢
# id作为条件的时候
# 如果不加索引，速度慢
# 加了索引，速度快
'索引不生效的原因？
# 要查询的数据的范围大
# > < >= <= !=
# between and
# select * from 表 order by age limit 0，5
# select * from 表 where id between 1000000 and 1000005;
# like
# 结果的范围大 索引不生效
# 如果 abc% 索引生效，%abc索引就不生效
# 如果一列内容的区分度不高，索引也不生效
# name列
# 索引列不能在条件中参与计算
# select * from s1 where id*10 = 1000000; 索引不生效
# 对两列内容进行条件查询
# and and条件两端的内容，优先选择一个有索引的，并且树形结构更好的，来进行查询
# 两个条件都成立才能完成where条件，先完成范围小的缩小后面条件的压力
# select * from s1 where id =1000000 and email = 'eva1000000@oldboy';
# or or条件的，不会进行优化，只是根据条件从左到右依次筛选
# 条件中带有or的要想命中索引，这些条件中所有的列都是索引列
# select * from s1 where id =1000000 or email = 'eva1000000@oldboy';
# 联合索引 # create index ind_mix on s1(id,name,email);
# select * from s1 where id =1000000 and email = 'eva1000000@oldboy';
# 在联合索引中如果使用了or条件索引就不能生效
# select * from s1 where id =1000000 or email = 'eva1000000@oldboy';
# 最左前缀原则 ：在联合索引中，条件必须含有在创建索引的时候的第一个索引列
# select * from s1 where id =1000000; 能命中索引
# select * from s1 where email = 'eva1000000@oldboy'; 不能命中索引
# (a,b,c,d)
# a,b
# a,c
# a
# a,d
# a,b,d
# a,c,d
# a,b,c,d
# 在整个条件中，从开始出现模糊匹配的那一刻，索引就失效了
# select * from s1 where id >1000000 and email = 'eva1000001@oldboy';
# select * from s1 where id =1000000 and email like 'eva%';
# 什么时候用联合索引
# 只对 a 对abc 条件进行索引
# 而不会对b，对c进行单列的索引
# 单列索引
# 选择一个区分度高的列建立索引，条件中的列不要参与计算，条件的范围尽量小，使用and作为条件的连接符
# 使用or来连接多个条件
# 在满上上述条件的基础上
# 对or相关的所有列分别创建索引
# 覆盖索引
# 如果我们使用索引作为条件查询，查询完毕之后，不需要回表查，覆盖索引
# explain select id from s1 where id = 1000000;
# explain select count(id) from s1 where id > 1000000;
# 合并索引
# 对两个字段分别创建索引，由于sql的条件让两个索引同时生效了，那么这个时候这两个索引就成为了合并索引
# 执行计划 : 如果你想在执行sql之前就知道sql语句的执行情况，那么可以使用执行计划
# 情况1：
# 30000000条数据
# sql 20s
# explain sql --> 并不会真正的执行sql，而是会给你列出一个执行计划
# 情况2：
# 20条数据 --> 30000000
# explain sql
# 原理和概念
# b树
# b+树
# 聚集索引 - innodb
# 辅助索引 - innodb myisam
# SQL索引的创建（单个、联合）、删除
# 索引的命中：范围，条件的字段是否参与计算(不能用函数)，列的区分度(长度)，条件and/or，联合索引的最左前缀问题
# 一些名词
# 覆盖索引
# 合并索引
# explain执行计划
# 建表、使用sql语句的时候注意的
# char 代替 varchar
# 连表 代替 子查询
# 创建表的时候 固定长度的字段放在前面
6.数据备份与事物
#!/usr/bin/env python
# -*- coding:utf-8 -*-
# mysqldump -uroot -p123 day40 > D:\code\s21day41\day40.sql
# mysqldump -uroot -p123 --databases new_db > D:\code\s21day41\db.sql
# begin; # 开启事务
# select * from emp where id = 1 for update; # 查询id值，for update添加行锁；
# update emp set salary=10000 where id = 1; # 完成更新
# commit; # 提交事务
7.sql注入
#!/usr/bin/env python
# -*- coding:utf-8 -*-
# create table userinfo(
# id int primary key auto_increment,
# name char(12) unique not null,
# password char(18) not null
# )
#
# insert into userinfo(name,password) values('alex','alex3714')
# 输入用户
# 输入密码
#
# 用户名和密码到数据库里查询数据
# 如果能查到数据 说明用户名和密码正确
# 如果查不到，说明用户名和密码不对
# username = input('user >>>')
# password = input('passwd >>>')
# sql = "select * from userinfo where name = '%s' and password = '%s'"%(username,password)
# print(sql)
# -- 注释掉--之后的sql语句
# select * from userinfo where name = 'alex' ;-- and password = '792164987034';
# select * from userinfo where name = 219879 or 1=1 ;-- and password = 792164987034;
# select * from userinfo where name = '219879' or 1=1 ;-- and password = '792164987034';
import pymysql
conn = pymysql.connect(host = '127.0.0.1',user = 'root', password = '123',database='day41')
cur = conn.cursor()
username = input('user >>>')
password = input('passwd >>>')
sql = "select * from userinfo where name = %s and password = %s"
cur.execute(sql,(username,password))
print(cur.fetchone())
cur.close()
conn.close()
8.作业
作业 https://www.cnblogs.com/Eva-J/articles/9688383.html
1.初级需求
1、查询男生、女生的人数；
select gender,count(gender) from student group by gender;
2、查询姓“张”的学生名单；
select sname from student where sname like'张%';
3、课程平均分从高到低显示
select avg(num) from score group by course_id order by avg(num) desc;
4、查询有课程成绩小于60分的同学的学号、姓名；
select sid,sname from student where sid in (select sid from score where num<60);
5、查询至少有一门课与学号为1的同学所学课程相同的同学的学号和姓名；
select sid,sname from student where sid in (select student_id from score where course_id in (select course_id from score where student_id=1))；
6、查询出只选修了一门课程的全部学生的学号和姓名；
select sid,sname from student where sid in (select student_id from score group by student_id having count(course_id)=1);
7、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分；
select course_id,max(num),min(num) from score where num group by course_id;
8、查询课程编号“2”的成绩比课程编号“1”课程低的所有同学的学号、姓名；
9、查询“生物”课程比“物理”课程成绩高的所有学生的学号；
10、查询平均成绩大于60分的同学的学号和平均成绩;
11、查询所有同学的学号、姓名、选课数、总成绩；
12、查询姓“李”的老师的个数；
13、查询没学过“张磊老师”课的同学的学号、姓名；
14、查询学过“1”并且也学过编号“2”课程的同学的学号、姓名；
15、查询学过“李平老师”所教的所有课的同学的学号、姓名；
2.高级需求
1、查询没有学全所有课的同学的学号、姓名；
2、查询和“002”号的同学学习的课程完全相同的其他同学学号和姓名；
3、删除学习“叶平”老师课的SC表记录；
4、向SC表中插入一些记录，这些记录要求符合以下条件：①没有上过编号“002”课程的同学学号；②插入“002”号课程的平均成绩；
5、按平均成绩从低到高显示所有学生的“语文”、“数学”、“英语”三门的课程成绩，按如下形式显示： 学生ID,语文,数学,英语,有效课程数,有效平均分；
6、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分；
7、按各科平均成绩从低到高和及格率的百分数从高到低顺序；
8、查询各科成绩前三名的记录:(不考虑成绩并列情况)
9、查询每门课程被选修的学生数；
10、查询同名同姓学生名单，并统计同名人数；
11、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列；
12、查询平均成绩大于85的所有学生的学号、姓名和平均成绩；
13、查询课程名称为“数学”，且分数低于60的学生姓名和分数；
14、查询课程编号为003且课程成绩在80分以上的学生的学号和姓名；
15、求选了课程的学生人数
16、查询选修“杨艳”老师所授课程的学生中，成绩最高的学生姓名及其成绩；
17、查询各个课程及相应的选修人数；
18、查询不同课程但成绩相同的学生的学号、课程号、学生成绩；
19、查询每门课程成绩最好的前两名；
20、检索至少选修两门课程的学生学号；
21、查询全部学生都选修的课程的课程号和课程名；
22、查询没学过“叶平”老师讲授的任一门课程的学生姓名；
23、查询两门以上不及格课程的同学的学号及其平均成绩；
24、检索“004”课程分数小于60，按分数降序排列的同学学号；
25、删除“002”同学的“001”课程的成绩；
