笔记day05
-
1.导入模块
2.常见内置模块
3.常见第三方模块 -
面向对象
1.面向对象思想
2.类与对象概念
3.代码如何编写类和对象
4.对象独有的数据
5.对象绑定方法
6.动静态方法
7.面向对象三大特性
8.面向对象反射特性
9.元类 -
基础阶段项目
购物车程序
ATM程序
选课系统
ps:脱产班实战课程
本周内容概要
-
软件开发目录规范
-
数据库前戏
-
MySQL简介
-
基本SQL语句
-
基本数据类型
-
约束条件
-
多表查询思路
-
navicat可视化软件
-
多表查询练习题
-
python代码操作MySQL
本周内容详细
软件开发目录规范
当我们项目中的文件特别多的时候 需要精细化管理
'''文件夹的名字可以自定义 但是主要的功能都是为了分类管理'''
bin文件夹
start.py 项目启动文件
conf文件夹
settings.py 项目配置文件
core文件夹
src.py 项目核心文件
lib文件夹
common.py 项目公共文件
log文件夹
log.log 项目日志文件
db文件夹
userinfo.txt 项目数据文件
requirements.txt 项目模块及版本
readme 项目说明书
"""
软件开发目录规范不是让我们去记忆具体的名字 而是给我们传递一种思想
一旦数据较多就应该分类关系
"""
数据库前戏
# 存储数据的阶段
1.直接使用文本文件(文件操作)
文件路径不固定 文件名称不固定 数据格式不固定 兼容性极差
2.软件开发目录规范(json模块)
并没有完全解决上述的问题 兼容性较差
3.数据库服务
解决上述所有的问题 兼容性好
# 数据库服务演变史
1.单机游戏
数据全部保存在本地 无法实现共享
2.联机游戏
数据全部保存在数据库服务器上 可以实现共享
"""
思考:数据全部统一存放在网络上的某个地方 万一丢失后果很严重
为了降低数据丢失的可能性 存储数据的地方会采用集群的措施
"""
数据库服务
数据库分类
关系型数据库
有固定的表结构 并且表与表之间的数据可以建立数据库层面的关系
id name age hobby
常见关系型数据库
MySQL、PostgreSQL、Oracle、MariaDB、sqlite、sql server
非关系型数据库
没有固定的表结构 并且无法建立数据库层面的关系 数据采用K:V键值对存取
{'name':'jason','pwd':123}
{'username':'jasonNB','password':123,'hobby':'read'}
常见非关系型数据库
redis、mongoDB、memcache
"""
虽然数据库服务有很多 但是操作这些服务的方式几乎都是一样的 变化不大
"""
SQL与NoSQL
数据库服务是专门提供给程序来操作数据的 但是编写程序的语言是各式各样的(python、java、c++、php...)
数据库服务针对不同编程语言不太好区分并且消耗资源过大 所以统一规定只要想使用数据库服务就必须采用数据库指定的语言>>>SQL语句 NoSQL语句
SQL语句
专门用来操作关系型数据库
NoSQL语句
专门用来操作非关系型数据库
MySQL简介
学习阶段 我们可以将数据库服务运行在本地
1.MySQL版本问题
5.6:以前使用频率最高
5.7:逐步过渡
8.0:最新的 一般不用(个人使用其实很方便)
ps:无论哪个版本不影响我们入门阶段的学习
2.MySQL下载
https://downloads.mysql.com/archives/community/
由于是本地学习 所以直接上最新版即可 >>>: 比如8.0.18
3.MySQL安装
mac电脑:https://www.cnblogs.com/Dominic-Ji/articles/15402755.html
windows电脑:直接解压 找一个路径存放 D:\mysql-8.0.18-winx64
bin文件夹
mysqld.exe 服务端
mysql.exe 客户端
先切换到bin文件夹下(管理员身份打开cmd)
D:
cd mysql-8.0.18-winx64\bin
mysqld --install
net start mysql
详细参考:https://www.bilibili.com/video/BV1Bt4y1n7j2?spm_id_from=333.999.0.0&vd_source=3fc04d260ce9c015d471e9c05b137ebc
"""
MySQL运维开发相关知识
https://www.cnblogs.com/Dominic-Ji/p/15124625.html
"""
基本概念
库 简单的理解为是文件夹
表 简单的理解为是文件夹里面的文件
记录 简单的理解为文件夹里面的文件里面的一行行内容
1.登录MySQL
切换到bin路径下 然后输入
mysql -uroot -p 管理员登录没有密码直接回车即可
2.SQL语句的结束符是分号
基本SQL语句
库
1.增
create database 数据库名;
2.查
show databases;
show create database 数据库名;
3.改
alter database 数据库名 charset='gbk';
4.删
drop database 数据库名;
表
'''
表必须在库里面 所以操作表之前 得先准备/指定库
1.查看当前所在库
select database();
2.指定数据库
use 数据库名;
'''
1.增
create table 表名(id int,name char(32));
2.查
show tables;
show create table 表名;
desc 表名;
3.改
alter table 表名 rename 新表名;
alter table 表名 change 旧字段名 新字段名 字段类型;
4.删
drop table 表名;
记录
'''
先确保有库和表
'''
1.增
insert into 表名 values(数据值,数据值);
insert into 表名 values(数据值,数据值),(),(),();
insert into 表名(字段顺序) values(数据值,数据值),(),(),();
2.查
select * from 表名;
select 指定字段名 from 表名;
3.改
update 表名 set 字段=新值 where 筛选条件;
4.删
delete from 表名;
delete from 表名 where 筛选条件;
创建表的完整语法
create table 表名(
字段名1 字段类型(数字) 约束条件,
字段名2 字段类型(数字) 约束条件,
字段名3 字段类型(数字) 约束条件
)engine='存储引擎' charset='字符编码';
1.字段名和字段类型是必须的(至少一个)
create table user(id int);
2.数字和约束条件是可选的
3.约束条件可以写多个 空格隔开即可
4.最后一行字段结尾不能有逗号
5.engine和charset一般情况下不用指定 采用默认的即可
字段类型
可以看成是数据类型
1.整型 有负号 不带负号
tinyint 1bytes -128-127 0-255
smallint 2bytes
int 4bytes
bigint 8bytes
ps:整型默认需要有负号(需要一位二进制位存负号)
create table t1(id tinyint);
create table t2(id tinyint unsigned);
如果要存储手机号肯定不能用int(最长只有10位)所以需要是bigint或者字符串
2.浮点型
float
double
decimal
create table t3(id float(255,30)); # 总共255位小数点后面有30位
create table t4(id double(255,30)); # 总共255位小数点后面有30位
create table t5(id decimal(65,30)); # 总共65位小数点后面有30位
精确度: float < double < decimal
3.字符型
char 定长
char(4) 最大存四个字符 超出直接报错 如果不超则默认空格填充至四个字符
优点:整存整取 速度快
缺点:浪费存储空间
varchar 变长
varchar(4) 最大存四个字符 超出直接报错 如果不超有几个存几个
优点:节省存储空间
缺点:存取数据的速度没有char快
ps:char与varchar使用的频率都很高 具体需要结合业务需求
4.枚举与集合
枚举 enum
多选一
create table t6(
id int,
name varchar(32),
gender enum('male','female','others')
);
集合 set
多选多(包含多选一)
create table t7(
id int,
name varchar(32),
hobby set('篮球','足球','双色球','羽毛球')
);
5.日期类型
date 年月日
datetime 年月日时分秒
time 时分秒
year 年份
create table t8(
id int,
name varchar(32),
birthday date,
reg_time datetime,
study_time time,
join_year year
);
约束条件
约束条件相当于是在字段类型的基础之上添加的额外限制
1.unsigned 无负号
2.not null 非空
create table t9(
id int,
name varchar(32) not null
);
3.default 默认值
create table t10(
id int,
name varchar(32) default 'jason'
);
4.unique 唯一值
单列唯一
create table t11(
id int,
name varchar(32) unique
);
多列唯一
create table t12(
id int,
host varchar(32),
port int,
unique(host,port)
);
5.primary key 主键
MySQL5.5版本之后默认规定所有的表都必须有且只有一个主键(InnoDB存储引擎)
并且一般情况下都是id字段作为主键(序号) id nid sid pid
如果表中没有定义主键那么有两种策略
1.没有主键也没有非空且唯一的字段 那么采用隐藏主键建表(隐藏意味着用不到)
使用主键可以加快数据查询 用不到意味着无法加快查询 效率低
2.没有主键但是有非空且唯一的字段 那么自动升级为主键
create table t13(
id int primary key
);
create table t14(
id int,
age int not null unique,
pwd int not null unique,
cid int not null unique
);
6.auto_increment 自增
一张表中只能有一个自增的字段 并且该配置只能用于键(主要就是主键)
create table t15(
id int primary key auto_increment,
name varchar(32)
);
7.foreign key 外键
建立数据与数据之间的关系
一对多
多对多
一对一
外键约束
建立员工表
id name age dep_name dep_desc
1.表结构不清晰 表意不明确
2.数据冗余明显 浪费存储空间
3.数据修改麻烦 牵一发动全身
解决上述问题
id name age
id dep_name dep_desc
拆表之后上述三个问题全部解决 但是有一个致命的缺陷
在员工表中添加一个dep_id的字段
外键字段
记录数据与数据关系的字段
外键关系的判断
'''表关系的判断发展>>>:换位思考'''
以员工表与部门表为例
1.先站在员工表的基础之上
问:一条员工表里面的数据能否对应多条部门表里的数据
答:不可以
2.再站在部门表的基础之上
问:一条部门表里面的数据能否对应多条员工表里的数据
答:可以
换位思考结束得出的结论是一个可以一个不可以 那么关系就是'一对多'
员工表是多 部门表是一(关系只有一对多的叫法 没有多对一)
针对一对多的关系 外键字段建在多的一方
以书籍表与作者表为例
1.先站在数据表的基础之上
问:一条书籍表里面的数据能否对应多条作者表里的数据
答:可以
2.再站在作者表的基础之上
问:一条作者表里面的数据能否对应多条书籍表里的数据
答:可以
换位思考结束得出的结论是两边都可以 那么关系就是'多对多'
针对多对多的关系 外键字段建在第三张关系表中
以用户表与用户详情表为例
1.先站在用户表的基础之上
问:一条用户表里面的数据能否对应多条用户详情表里的数据
答:不可以
2.再站在用户详情表的基础之上
问:一条用户详情表里面的数据能否对应多条用户表里的数据
答:不可以
换位思考结束得出的结论是两边都不可以 那么关系可能有两种
1.一种是压根没有关系(用膝盖都可以想到)
2.'一对一'
针对一对一的关系 外键字段建在任何一方都可以 但是推荐建在查询频率较高的表中
一对多
create table emp(
id int primary key auto_increment,
name varchar(32),
age int,
dep_id int,
foreign key(dep_id) references dep(id)
on update cascade
on delete cascade
);
create table dep(
id int primary key auto_increment,
dep_name varchar(32),
dep_desc varchar(64)
);
一对一
create table user(
id int primary key auto_increment,
name varchar(32),
age int,
detail_id int unique,
foreign key(detail_id) references userDetail(id)
on update cascade
on delete cascade
);
create table userDetail(
id int primary key auto_increment,
addr varchar(32),
phone bigint,
)
多对多
create table book2author(
id int primary key auto_increment,
book_id int,
foreign key(book_id) references book(id)
on update cascade
on delete cascade,
author_id int,
foreign key(author_id) references author(id)
on update cascade
on delete cascade
)
"""
外键虽然可以非常直观的表现出数据之间的关系 但是也会增加数据之间的耦合度
很多大型的项目甚至不会采用外键来建立数据关系 而是直接使用代码建立逻辑意义上的关系(所有自动操作的事情改为自己写SQL执行)
"""
表查询关键字
1.数据准备环节
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);
2.基本关键字
select
控制想要查询的字段名称
from
控制想要查询的表名称
select name from userinfo; 从userinfo表中查询name字段对应的所有数据
查询关键字之where筛选
select 字段名 from 表名 where 筛选条件;
# 1.查询id大于等于3小于等于6的数据
select id,name from emp where id >= 3 and id <= 6;
select * from emp where id between 3 and 6;
# 2.查询薪资是20000或者18000或者17000的数据
select * from emp where salary = 20000 or salary = 18000 or salary = 17000;
select * from emp where salary in (20000,18000,17000); # 简写
# 3.查询员工姓名中包含o字母的员工姓名和薪资
# 在你刚开始接触mysql查询的时候，建议你按照查询的优先级顺序拼写出你的sql语句
"""
先是查哪张表 from emp
再是根据什么条件去查 where name like ‘%o%’
再是对查询出来的数据筛选展示部分 select name,salary
"""
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 * from emp where id not between 3 and 6;
# 6.查询薪资不在20000，18000，17000范围的数据
select * from emp where salary not in (20000,18000,17000);
# 7.查询岗位描述为空的员工名与岗位名 针对null不能用等号，只能用is
select name,post from emp where post_comment = NULL; # 查询为空！
select name,post from emp where post_comment is NULL;
select name,post from emp where post_comment is not NULL;
查询关键字之group by分组
分组:按照指定的条件将单个单个的数据分为一个个整体
eg:按照性别分组 按照国家分组 按照年龄段分组...
聚合函数
专门配合分组一起使用 常见的聚合函数有
max最大值
min最小值
avg平均值
count计数
sum求和
查询关键字之having过滤
having与where的功能其实是一模一样的 都是用来筛选数据
只不过where用于分组之前的初次筛选 而having用于分组之后的二次筛选
统计各部门年龄在30岁以上的员工平均工资，并且保留平均工资大于10000的部门
"""编写SQL语句与编写python代码几乎一致 都有逻辑"""
1.先筛选所有年龄在30岁以上的员工数据
select * from emp where age > 30;
2.将上述符合条件的数据按照部门分组
select post from emp where age > 30 group by post;
3.利用聚合函数avg统计平均薪资
select post,avg(salary) from emp where age > 30 group by post;
4.分组之后还需要筛选数据 所以使用having做二次过滤
select post,avg(salary) from emp where age > 30 group by post
having avg(salary) > 10000;
其他关键字
distinct去重(数据必须一模一样才可以去重)
order by排序(当某个字段值相同无法必出大小 支持填写多个字段)
limit分页(当表中数据特别多的时候无法一次性展示 可以通过limit控制)
regexp正则(如果模糊查询无法满足业务需求 也可以写正则表达式)
多表查询思路
1.子查询
将一条SQL语句的执行结果括号括起来当做另外一条SQL语句的条件
eg:查询jason所在的部门名称
1.先查询jason所在的部门编号
select dep_id from emp where name='jason'; # 200
2.再根据部门编号查询部门名称
select name from dep where id=(select dep_id from emp where name='jason');
2.连表操作(重点)
先将所有需要用到的表按照一些公共的条件拼接到一起形成一张大表 之后基于单表查询即可完成查询需求
inner join 内连接
只拼接两张表中公有的数据项
select * from emp inner join dep on emp.dep_id=dep.id;
left join
以左表为基准 展示左表所有的数据项 没有对应的null填充
select * from emp left join dep on emp.dep_id=dep.id;
right join
以右表为基准 展示右表所有的数据项 没有对应的null填充
select * from emp right join dep on emp.dep_id=dep.id;
union
左右两边的表数据全部展示 没有对应的全部使用null填充
select * from emp left join dep on emp.dep_id=dep.id
union
select * from emp right join dep on emp.dep_id=dep.id;
"""
以后在涉及到多表查询的时候往往是两者混合使用
"""
可视化软件
给用户提供了很多鼠标点点点的快捷操作方式 内部自动封装SQL语句执行
使用这些可视化软件可以提升开发效率
Navicat可以连接很多数据库服务 最主要的就是MySQL 由于其功能强大所以需要收费
我们也可以下载破解版
/*
数据导入：
Navicat Premium Data Transfer
Source Server : localhost
Source Server Type : MySQL
Source Server Version : 50624
Source Host : localhost
Source Database : sqlexam
Target Server Type : MySQL
Target Server Version : 50624
File Encoding : utf-8
Date: 10/21/2016 06:46:46 AM
*/
SET NAMES utf8;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for `class`
-- ----------------------------
DROP TABLE IF EXISTS `class`;
CREATE TABLE `class` (
`cid` int(11) NOT NULL AUTO_INCREMENT,
`caption` varchar(32) NOT NULL,
PRIMARY KEY (`cid`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of `class`
-- ----------------------------
BEGIN;
INSERT INTO `class` VALUES ('1', '三年二班'), ('2', '三年三班'), ('3', '一年二班'), ('4', '二年九班');
COMMIT;
-- ----------------------------
-- Table structure for `course`
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
`cid` int(11) NOT NULL AUTO_INCREMENT,
`cname` varchar(32) NOT NULL,
`teacher_id` int(11) NOT NULL,
PRIMARY KEY (`cid`),
KEY `fk_course_teacher` (`teacher_id`),
CONSTRAINT `fk_course_teacher` FOREIGN KEY (`teacher_id`) REFERENCES `teacher` (`tid`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of `course`
-- ----------------------------
BEGIN;
INSERT INTO `course` VALUES ('1', '生物', '1'), ('2', '物理', '2'), ('3', '体育', '3'), ('4', '美术', '2');
COMMIT;
-- ----------------------------
-- Table structure for `score`
-- ----------------------------
DROP TABLE IF EXISTS `score`;
CREATE TABLE `score` (
`sid` int(11) NOT NULL AUTO_INCREMENT,
`student_id` int(11) NOT NULL,
`course_id` int(11) NOT NULL,
`num` int(11) NOT NULL,
PRIMARY KEY (`sid`),
KEY `fk_score_student` (`student_id`),
KEY `fk_score_course` (`course_id`),
CONSTRAINT `fk_score_course` FOREIGN KEY (`course_id`) REFERENCES `course` (`cid`),
CONSTRAINT `fk_score_student` FOREIGN KEY (`student_id`) REFERENCES `student` (`sid`)
) ENGINE=InnoDB AUTO_INCREMENT=53 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of `score`
-- ----------------------------
BEGIN;
INSERT INTO `score` VALUES ('1', '1', '1', '10'), ('2', '1', '2', '9'), ('5', '1', '4', '66'), ('6', '2', '1', '8'), ('8', '2', '3', '68'), ('9', '2', '4', '99'), ('10', '3', '1', '77'), ('11', '3', '2', '66'), ('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'), ('37', '9', '4', '22'), ('38', '10', '1', '90'), ('39', '10', '2', '77'), ('40', '10', '3', '43'), ('41', '10', '4', '87'), ('42', '11', '1', '90'), ('43', '11', '2', '77'), ('44', '11', '3', '43'), ('45', '11', '4', '87'), ('46', '12', '1', '90'), ('47', '12', '2', '77'), ('48', '12', '3', '43'), ('49', '12', '4', '87'), ('52', '13', '3', '87');
COMMIT;
-- ----------------------------
-- Table structure for `student`
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`sid` int(11) NOT NULL AUTO_INCREMENT,
`gender` char(1) NOT NULL,
`class_id` int(11) NOT NULL,
`sname` varchar(32) NOT NULL,
PRIMARY KEY (`sid`),
KEY `fk_class` (`class_id`),
CONSTRAINT `fk_class` FOREIGN KEY (`class_id`) REFERENCES `class` (`cid`)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of `student`
-- ----------------------------
BEGIN;
INSERT INTO `student` VALUES ('1', '男', '1', '理解'), ('2', '女', '1', '钢蛋'), ('3', '男', '1', '张三'), ('4', '男', '1', '张一'), ('5', '女', '1', '张二'), ('6', '男', '1', '张四'), ('7', '女', '2', '铁锤'), ('8', '男', '2', '李三'), ('9', '男', '2', '李一'), ('10', '女', '2', '李二'), ('11', '男', '2', '李四'), ('12', '女', '3', '如花'), ('13', '男', '3', '刘三'), ('14', '男', '3', '刘一'), ('15', '女', '3', '刘二'), ('16', '男', '3', '刘四');
COMMIT;
-- ----------------------------
-- Table structure for `teacher`
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
`tid` int(11) NOT NULL AUTO_INCREMENT,
`tname` varchar(32) NOT NULL,
PRIMARY KEY (`tid`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of `teacher`
-- ----------------------------
BEGIN;
INSERT INTO `teacher` VALUES ('1', '张磊老师'), ('2', '李平老师'), ('3', '刘海燕老师'), ('4', '朱云海老师'), ('5', '李杰老师');
COMMIT;
SET FOREIGN_KEY_CHECKS = 1;
多表查询练习题
-- 1、查询所有的课程的名称以及对应的任课老师姓名
# 1.先确定需要用到几张表 course teacher
# 2.大致查看表中的数据特征
-- select * from course;
-- select * from teacher;
# 3.思考具体编写思路(课程名称和老师姓名分别取自于两张表)
-- SELECT
-- course.cname,
-- teacher.tname
-- FROM
-- teacher
-- INNER JOIN course ON teacher.tid = course.teacher_id;
-- 4、查询平均成绩大于八十分的同学的姓名和平均成绩
# 1.先确定需要几张表 score student
# 2.大致查看表中数据特征
-- select * from score;
-- select * from student;
# 3.思考具体编写逻辑
# 3.1.先去分数表中查询出所有学生的平均成绩
-- select student_id,avg(num) as avg_num from score group by student_id having avg(num) > 80;
/*给字段起别名防止后续出现普通字段名和函数名混淆*/# 3.2.将student表和上述SQL语句执行之后的表拼接到一起
-- SELECT
-- student.sname,
-- t1.avg_num
-- FROM
-- student
-- INNER JOIN ( SELECT student_id, avg( num ) AS avg_num FROM score GROUP BY student_id HAVING avg( num ) > 80 ) AS t1 ON student.sid = t1.student_id /*一条SQL语句如果想当成表来用 也可以起别名*/
-- 7、查询没有报李平老师课的学生姓名
# 1.先确定需要用到的表 teacher course score student
# 2.大致查看表数据特征
# 3.思考编写逻辑(先查所有报了李平老师课程的学生id 然后去学生表中取反查询即可)
# 3.1.查询李平老师教授的课程编号
-- select cid from course where teacher_id = (select tid from teacher where tname='李平老师');
# 3.2.基于李平老师教授的课程编号去分数表中筛选出所有报了李平老师课程的学生编号
-- select distinct score.student_id from score where course_id in (select cid from course where teacher_id = (select tid from teacher where tname='李平老师'));
# 3.3.根据学生编号取反直接获取学生姓名
-- SELECT
-- student.sname
-- FROM
-- student
-- WHERE
-- sid NOT IN ( SELECT DISTINCT score.student_id FROM score WHERE course_id IN ( SELECT cid FROM course WHERE teacher_id = ( SELECT tid FROM teacher WHERE tname = '李平老师' ) ) )
-- 8、查询没有同时选修物理课程和体育课程的学生姓名(只要报了一门的 两门都报的和两门都没报的都不要)
# 1.先确定需要几张表 student score course
# 2.大致查找各自表中的数据特征
# 3.思考编写逻辑
# 3.1.先根据物理和体育筛选出课程的编号
-- select cid from course where cname in ('物理','体育')
# 3.2.根据课程编号先筛选出所有跟物理 体育相关的学生数据
-- select * from score where course_id in (select cid from course where cname in ('物理','体育'));
# 3.3.基于上述表的结果统计每个学生报了几门课 筛选出一门的
-- select score.student_id from score where course_id in (select cid from course where cname in ('物理','体育'))
-- group by score.student_id
-- having count(score.course_id) = 1
# 3.4.基于学生编号获取学生姓名
-- SELECT
-- sname
-- FROM
-- student
-- WHERE
-- sid IN (
-- SELECT
-- score.student_id
-- FROM
-- score
-- WHERE
-- course_id IN ( SELECT cid FROM course WHERE cname IN ( '物理', '体育' ) )
-- GROUP BY
-- score.student_id
-- HAVING
-- count( score.course_id ) = 1
-- )
-- 9、查询挂科超过两门(包括两门)的学生姓名和班级
# 1.先筛选出所有小于60的数据
-- select * from score where num < 60;
# 2.按照学生编号分组统计每个学生的挂科数目
-- select student_id from score where num < 60 group by student_id
-- having count(course_id) >= 2
# 3.先拼接学生表和班级表(上面的结果先别管)
-- select * from student inner join class on student.class_id = class.cid;
# 4.根据学生编号筛选数据即可
-- SELECT
-- student.sname,
-- class.caption
-- FROM
-- student
-- INNER JOIN class ON student.class_id = class.cid
-- WHERE
-- student.sid IN ( SELECT student_id FROM score WHERE num < 60 GROUP BY student_id HAVING count( course_id ) >= 2 )
python操作MySQL
第三方模块>>>:pymysql pip3 install pymysql
import pymysql
# 1.链接MySQL服务
conn = pymysql.connect(
host='127.0.0.1',
port=3306,
user='root',
password='jason123',
database='zm_day01_2',
charset='utf8mb4'
)
# 2.产生游标对象(执行命令)
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) # 增加配置 自动封装成字典便于后续操作
# 3.编写SQL语句
sql = 'select * from emp'
# 4.执行SQL语句
cursor.execute(sql)
# 5.查看执行结果
res = cursor.fetchall()
print(res)
"""
1.获取SQL语句执行的结果 类似于文件光标的移动
2.获取结果集的方法有三个
res1 = cursor.fetchall() # 获取所有的结果
res1 = cursor.fetchone() # 获取所有的结果
res1 = cursor.fetchmany(3) # 获取指定个数的结果
3.SQL注入
利用特殊符号的组合产生了特殊的含义从而逃避了正常的业务逻辑
解决SQL注入的方式就是避免特殊符号的加入
sql = "select * from userinfo where name=%s and pwd=%s"
cursor.execute(sql,(username, password)) # 自动排查特殊符号
4.提交确认
针对增、删、改的操作需要调用commit二次确认 也可以通过配置自动确认
conn = pymysql.connect(
...
autocommit=True
)
"""
视图
1、什么是视图
视图就是通过查询得到一张虚拟表，然后保存下来，下次直接使用即可
2、为什么要用视图
如果要频繁使用一张虚拟表，可以不用重复查询
3、如何用视图
create view teacher2course as
select * from teacher inner join course on teacher.tid = course.teacher_id;
"""
创建好了之后 验证它的存在navicat验证 cmd终端验证
最后文件验证 得出下面的结论 视图只有表结构数据还是来源于之前的表
delete from teacher2course where id=1;
"""
强调 1、在硬盘中，视图只有表结构文件，没有表数据文件 2、视图通常是用于查询，尽量不要修改视图中的数据
drop view teacher2course;
思考:开发过程中会不会去使用视图？
不会！视图是mysql的功能，如果你的项目里面大量的使用到了视图，那意味着你后期想要扩张某个功能的时候这个功能恰巧又需要对视图进行修改，意味着你需要先在mysql这边将视图先修改一下，然后再去应用程序中修改对应的sql语句，这就涉及到跨部门沟通的问题，所以通常不会使用视图，而是通过重新修改sql语句来扩展功能
触发器
在满足对某张表数据的增、删、改的情况下，自动触发的功能称之为触发器
为何要用触发器
触发器专门针对我们对某一张表数据增insert、删delete、改update的行为，这类行为一旦执行
就会触发触发器的执行，即自动运行另外一段sql代码
创建触发器语法
"""语法结构
create trigger 触发器的名字 before/after insert/update/delete on 表名 for each row
begin
sql语句
end
"""
# 针对插入
create trigger tri_after_insert_t1 after insert on 表名 for each row
begin
sql代码。。。
end
create trigger tri_after_insert_t2 before insert on 表名 for each row
begin
sql代码。。。
end
# 针对删除
create trigger tri_after_delete_t1 after delete on 表名 for each row
begin
sql代码。。。
end
create trigger tri_after_delete_t2 before delete on 表名 for each row
begin
sql代码。。。
end
# 针对修改
create trigger tri_after_update_t1 after update on 表名 for each row
begin
sql代码。。。
end
create trigger tri_after_update_t2 before update on 表名 for each row
begin
sql代码。。。
end
"""
需要注意 在书写sql代码的时候结束符是; 而整个触发器的结束也需要分号;
这就会出现语法冲突 需要我们临时修改结束符号
delimiter $$
delimiter ;
该语法只在当前窗口有效
"""
# 案例
CREATE TABLE cmd (
id INT PRIMARY KEY auto_increment,
USER CHAR (32),
priv CHAR (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
);
delimiter $$ # 将mysql默认的结束符由;换成$$
create trigger tri_after_insert_cmd after insert on cmd for each row
begin
if NEW.success = 'no' then # 新记录都会被MySQL封装成NEW对象
insert into errlog(err_cmd,err_time) values(NEW.cmd,NEW.sub_time);
end if;
end $$
delimiter ; # 结束之后记得再改回来，不然后面结束符就都是$$了
#往表cmd中插入记录，触发触发器，根据IF的条件决定是否插入错误日志
INSERT INTO cmd (
USER,
priv,
cmd,
sub_time,
success
)
VALUES
('kevin','0755','ls -l /etc',NOW(),'yes'),
('kevin','0755','cat /etc/passwd',NOW(),'no'),
('kevin','0755','useradd xxx',NOW(),'no'),
('kevin','0755','ps aux',NOW(),'yes');
# 查询errlog表记录
select * from errlog;
# 删除触发器
drop trigger tri_after_insert_cmd;
事务
什么是事务
开启一个事务可以包含一些sql语句，这些sql语句要么同时成功
要么一个都别想成功，称之为事务的原子性
事务的作用
保证了对数据操作的数据安全性
案例:用交行的卡操作建行ATM机给工商的账户转钱
事务应该具有4个属性：原子性、一致性、隔离性、持久性。这四个属性通常称为ACID特性。
原子性（atomicity）。一个事务是一个不可分割的工作单位，事务中包括的诸操作要么都做，要么都不做。
一致性（consistency）。事务必须是使数据库从一个一致性状态变到另一个一致性状态。一致性与原子性是密切相关的。
隔离性（isolation）。一个事务的执行不能被其他事务干扰。即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。
持久性（durability）。持久性也称永久性（permanence），指一个事务一旦提交，它对数据库中数据的改变就应该是永久性的。接下来的其他操作或故障不应该对其有任何影响。
如何用
# 先介绍事务的三个关键字 再去用表实际展示效果
create table user(
id int primary key auto_increment,
name char(32),
balance int
);
insert into user(name,balance)
values
('jason',1000),
('kevin',1000),
('tank',1000);
# 修改数据之前先开启事务操作
start transaction;
# 修改操作
update user set balance=900 where name='jason'; #买支付100元
update user set balance=1010 where name='kevin'; #中介拿走10元
update user set balance=1090 where name='tank'; #卖家拿到90元
# 回滚到上一个状态
rollback;
# 开启事务之后，只要没有执行commit操作，数据其实都没有真正刷新到硬盘
commit;
"""开启事务检测操作是否完整，不完整主动回滚到上一个状态，如果完整就应该执行commit操作"""
MySQL提供两种事务型存储引擎InnoDB和NDB cluster及第三方XtraDB、PBXT
事务处理中有几个关键词汇会反复出现
事务(transaction)
回退(rollback)
提交(commit)
保留点(savepoint)
为了支持回退部分事务处理，必须能在事务处理块中合适的位置放置占位符，这样如果需要回退可以回退到某个占位符(保留点)
创建占位符可以使用savepoint
savepoint sp01;
回退到占位符地址
rollback to sp01;
# 保留点在执行rollback或者commit之后自动释放
在SQL标准中定义了四种隔离级别，每一种级别都规定了一个事务中所做的修改
InnoDB支持所有隔离级别
set transaction isolation level 级别
1.read uncommitted(未提交读)
事务中的修改即使没有提交，对其他事务也都是可见的，事务可以读取未提交的数据，这一现象也称之为"脏读"
2.read committed(提交读)
大多数数据库系统默认的隔离级别
一个事务从开始直到提交之前所作的任何修改对其他事务都是不可见的，这种级别也叫做"不可重复读"
3.repeatable read(可重复读) # MySQL默认隔离级别
能够解决"脏读"问题，但是无法解决"幻读"
所谓幻读指的是当某个事务在读取某个范围内的记录时另外一个事务又在该范围内插入了新的记录，当之前的事务再次读取该范围的记录会产生幻行，InnoDB和XtraDB通过多版本并发控制(MVCC)及间隙锁策略解决该问题
4.serializable(可串行读)
强制事务串行执行，很少使用该级别
存储过程
存储过程包含了一系列可执行的sql语句，存储过程存放于MySQL中，通过调用它的名字可以执行其内部的一堆sql,类似于python中的自定义函数
基本使用
delimiter $$
create procedure p1()
begin
select * from dep;
end $$
delimiter ;
# 调用
call p1()
函数
注意与存储过程的区别，mysql内置的函数只能在sql语句中使用！
"ps:可以通过help 函数名 查看帮助信息!"
# 1.移除指定字符
Trim、LTrim、RTrim
# 2.大小写转换
Lower、Upper
# 3.获取左右起始指定个数字符
Left、Right
# 4.返回读音相似值(对英文效果)
Soundex
"""
eg:客户表中有一个顾客登记的用户名为J.Lee
但如果这是输入错误真名其实叫J.Lie,可以使用soundex匹配发音类似的
where Soundex(name)=Soundex('J.Lie')
"""
# 5.日期格式:date_format
'''在MySQL中表示时间格式尽量采用2022-11-11形式'''
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');
1.where Date(sub_time) = '2015-03-01'
2.where Year(sub_time)=2016 AND Month(sub_time)=07;
# 更多日期处理相关函数
adddate 增加一个日期
addtime 增加一个时间
datediff 计算两个日期差值
...
流程控制
# 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 ;
END //
delimiter ;
作业
1.判断下列表关系
书籍表与出版社表
客户表与学生表
用户表与用户组表
学生表与班级表
班级表与课程表
老师表与课程表
2.基于表查询关键字数据完成下列题目
1. 查询岗位名以及岗位包含的所有员工名字
2. 查询岗位名以及各岗位内包含的员工个数
3. 查询公司内男员工和女员工的个数
4. 查询岗位名以及各岗位的平均薪资
5. 查询岗位名以及各岗位的最高薪资
6. 查询岗位名以及各岗位的最低薪资
7. 查询男员工与男员工的平均薪资，女员工与女员工的平均薪资
3.基于pymysql完成用户注册登录功能
4.更多表查询练习题(尽量完成三分之一即可)
https://www.cnblogs.com/Dominic-Ji/p/10875493.html
