0906~0910
0906(周一)内容概要
-
约束条件之主键
-
约束条件之自增
-
约束条件之外键
重在理解 -
查询关键字
根据指定条件筛选出符合的数据
0906内容详细
约束条件之主键
# 主键 primary key
1.单从约束层面上来讲 相当于not null unique >>> 非空且唯一
验证:
create table t1(id int primary key);
creare table t2(id int not null unique);
2.但是主键还是InnoDB存储引擎组织数据表的依据
# 1.InnoDB规定了一个表必须要有且只有一个主键
# 2.如果你不指定主键则会采用隐藏的字段作为主键
# 3.当表中没有主键但是有非空且唯一的字段则自动升级为主键(自上而下第一个)
"""主键是可以加快查询速度的 隐藏意味着无法使用主键即速度无法提升"""
create table t2(
id int,
name char(16),
age int not null unique,
addr char(16) not null unique
);
"""结论:
在创建表的时候一般都需要有一个id字段(uid sid pid...)
并且改字段应该设置为表的主键字段
"""
# 了解
主键可以单列主键也可以联合主键 但是联合主键使用频率很低
create table t3(
id int,
name char(16),
primary key(id,name)
);
约束条件之自增
create table t4(
id int primary key,
name char(16)
);
# 自增 auto_increment
'''配合主键一起使用'''
create table t5(
id int primary key auto_increment,
name char(16)
);
##########################################################
以后创建表 主键字段的固定写法如下
id int primary key auto_increment
##########################################################
补充
1.自增的特性不会因为delete操作而重置/回退
delete from # 只删除数据
2.如果真的想重置需要清空表数据和表结构
truncate # 即删除数据又重置主键
约束条件之外键
# 前戏
'''定义一张员工表为例'''
id name age dep_name dep_desc
1.表结构不清晰 可以忽略
2.表数据重复 可以忽略
3.数据扩展性极差 不能忽略
'''解决上述三个问题:拆表'''
id name age
id dep_name dep_desc
'''拆表解决了三个问题但是员工和部门没有了关系'''
在员工表里面添加一个部门编号字典dep_id(外键)
############################################################
外键:记录表与表之间数据关系的字段
############################################################
表关系的种类
一对多关系
多对多关系
一对一关系
没有关系
如何判断表关系(重要)
一对多关系
"""判断表关系遵循 换位思考 的原则"""
eg:员工表与部门表
1.先站在员工表的层面
问:一个员工能否对应多个部门
答:不可以
2.再站在部门表的层面
问:一个部门能否对应多个员工
答:可以
结论:一个可以 一个不可以那么员工表与部门表就是"一对多"
"一对多"表关系外键字段建在多的一方(本例中员工是多)
# 1.先写基本字段类型与约束条件
# 2.之后再考虑外键
create table emp(
id int primary key auto_increment,
name char(16),
age int,
dep_id int,
foreign key(dep_id) references dep(id)
);
create table dep(
id int primary key auto_increment,
dep_name char(16),
dep_desc char(32)
);
外键字段的特性
1.在创建表的时候一定要先创建被关联表(自身没有外键字段的表)
2.在插入数据的时候也是先插入被关联表再插入关联表
只能够填写被关联字段中出现的值
3.被关联表中的数据无法自由删除和修改
4.级联更新 级联删除
create table emp(
id int primary key auto_increment,
name char(16),
age int,
dep_id int,
foreign key(dep_id) references dep(id)
on update cascade # 级联更新
on delete cascade # 级联删除
);
# 演示
create table dep1(
id int primary key auto_increment,
dep_name char(10),
dep_comment char(60)
);
create table emp1(
id int primary key auto_increment,
name char(16),
gender enum('male','female') not null default 'male',
dep_id int,
foreign key(dep_id) references dep1(id)
on update cascade
on delete cascade
);
insert into dep1(dep_name,dep_comment) values
('sb教学部','sb辅导学生学习，教授python课程'),
('外交部','驻上海形象大使'),
('nb技术部','nb技术能力有限部门');
insert into emp1(name,gender,dep_id) values
('jason','male',1),
('tom','male',2),
('tony','male',1),
('kevin','male',1),
('jim','female',3);
多对多关系
# 以书籍表与作者表
1.先站在书籍表
问:一本书能否对应多个作者
答:可以
2.在站在作者表
问:一名作者能否对应多本书
答:可以
结论:两边都可以 那么表关系就是"多对多"
# SQL(报错)
create table book(
id int primary key auto_increment,
name char(16),
price int,
author_id int,
foregin key(author_id) references author(id)
on update cascade
on delete cascade
);
create table author(
id int primary key auto_increment,
name char(16),
age int,
book_id int,
foreign key(book_id) references book(id)
on update cascade
on delete cascade
);
"""针对多对多的表关系 外键字段需要建在第三张关系表中"""
create table book(
id int primary key auto_increment,
name char(16),
price int
);
create table author(
id int primary key auto_increment,
name char(16),
age int
);
create table book2author(
id int primary key auto_increment,
author_id int,
book_id int,
foregin key(author_id) references author(id)
on update cascade
on delete cascade,
foreign key(book_id) references book(id)
on update cascade
on delete cascade
)
一对一关系
# 以客户表与学生表
# 以用户名与用户详情表
以用户表与用户详情表
1.先站在用户表层面
问:一个用户能否对应多个用户详情
答:不可以
2.再站在用户详情表层面
问:一个用户详情能否对应多个用户
答:不可以
结论:两边都不可以，那么表关系要么是没有关系要么是"一对一"关系
"""
一对一表关系
外键建在任意一方都可以 但是推荐建在查询频率较高的表中
"""
create table user(
id int primary key auto_increment,
name varchar(16),
detail_id int unique,
foreign key(detail_id) references user_detail(id)
on update cascade
on delete cascade
);
create table user_detail(
id int primary key auto_increment,
phone bigint,
addr varchar(16)
);
修改表相关SQL语句
1. 修改表名
ALTER TABLE 表名
RENAME 新表名;
2. 增加字段
ALTER TABLE 表名
ADD 字段名 数据类型 [完整性约束条件…],
ADD 字段名 数据类型 [完整性约束条件…];
ALTER TABLE 表名
ADD 字段名 数据类型 [完整性约束条件…] FIRST;
ALTER TABLE 表名
ADD 字段名 数据类型 [完整性约束条件…] AFTER 字段名;
3. 删除字段
ALTER TABLE 表名
DROP 字段名;
4. 修改字段 # modify只能改字段数据类型完整约束，不能改字段名，但是change可以！
ALTER TABLE 表名
MODIFY 字段名 数据类型 [完整性约束条件…];
ALTER TABLE 表名
CHANGE 旧字段名 新字段名 旧数据类型 [完整性约束条件…];
ALTER TABLE 表名
CHANGE 旧字段名 新字段名 新数据类型 [完整性约束条件…];
0907(周二)内容概要
主体:查询数据为主
-
单表查询关键字
where group by having distinct orderby limit regexp ... -
多表查询两种策略
子查询 联表操作
0907内容详细
数据准备
"""
涉及到使用命令操作的时候最好在命令结束之后检查一下
涉及到表字段较多窗口展示错乱的情况可以使用\G分行展示
"""
create table emp(
id int primary key auto_increment,
name varchar(20) not null,
gender 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,gender,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);
查询关键字之where筛选
"""书写sql语句可以先写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.模糊查询
"""
关键字
like
关键符号
%:匹配任意个数的任意字符
_:匹配单个个数的任意字符
"""
# 查询员工姓名中包含o字母的员工姓名和薪资
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.查询薪资不在20000，18000，17000范围的数据
select * from emp where salary not in (20000,18000,17000);
# 6.查询岗位描述为空的员工名与岗位名 针对null不能用等号，只能用is
select name,post from emp where post_comment = NULL;
select name,post from emp where post_comment is NULL;
查询关键字之group by分组
"""
分组:按照指定的条件将单个单个的个体分成一个个整体
eg:
按照性别将人分为男性和女性
按照部门将员工分为多个部门
按照年龄将人分为各个年龄段
ps:在查询题目中需不需要分组其实有关键字 最为典型的就是
每个部门 每个国家 每个省份
"""
# 1.按照部门分组
select post from emp group by post;
"""
注意:分组之后默认只可以直接获取到分组的依据 无法再获取内部单个个体数据
如果想要获取需要借助于一些方法
set global sql_mode="strict_trans_tables,only_full_group_by";
"""
聚合函数
# 主要用于分组之后的数据处理
max 最大值
min 最小值
avg 平均值
sum 求和
count 计数
# 1.获取每个部门的最高工资
select post,max(salary) from emp group by post;
# 2.获取每个部门的最低薪资
select post,min(salary) from emp group by post;
# 3.获取每个部门的平均薪资
select post,avg(salary) from emp group by post;
# 4.获取每个部门的薪资总和
select post,sum(salary) from emp group by post;
# 5.获取每个部门的员工人数
select post,count(id) from emp group by post; # 重点理解计数内字段
查询关键字之having过滤
"""
having与where功能其实都是筛选数据 只不过
where用于分组之前的筛选
having用于分组之后的过滤
"""
1.统计各部门年龄在30岁以上的员工平均工资，并且保留平均工资大于10000的部门
# 1.统计各部门平均工资
select post,avg(salary) from emp group by post;
# 2.在分组之前先对数据进行筛选
select post,avg(salary) from emp where age > 30 group by post;
# 3.分组之后还要对数据进行过滤
select post,avg(salary) from emp
where age > 30
group by post
having avg(salary) > 10000;
查询关键字之distinct去重
'''去重的前提是数据必须一模一样才可以'''
select distinct post from emp;
查询关键字之order by排序
select * from emp order by salary; # 默认是升序
select * from emp order by salary asc; # asc是升序关键字 不写则默认
select * from emp order by salary desc; # 降序
select * from emp order by age asc,salary desc;
# 统计各部门年龄在10岁以上的员工平均工资，并且保留平均工资大于1000的部门，然后对平均工资进行排序
select post,avg(salary) from emp
where age > 10
group by post
having avg(salary) > 1000
order by avg(salary);
查询关键字之limit分页
select * from emp limit 5; # 一个数字表示条数
select * from emp limit 5,10; # 第一个数字表示起始位置 第二个数字表示条数
# 1.查询工资最高的人的详细信息
先按照工资降序排序 然后使用limit限制取第一条
select * from emp order by salary desc;
select * from emp limit 1;
查询关键字之regexp正则(了解)
"""
正则:使用一些特殊符号的组合去字符串中筛选出符合条件的数据(爬虫再讲)
"""
select * from emp where name regexp '^j.*(n|y)$';
# 查询姓名是以字母j开头 n或者y结尾的数据
0908(周三)内容概要
-
补充知识
-
多表查询理论
-
可视化软件navicat
-
多表查询练习题(较难)
课上认真听 课下一定要吃透
0908内容详细
补充知识
# 1.group_concat()方法 用于分组之后
获取除分组以外其他字段数据 本质可以理解为是拼接操作
select post,group_concat(name) from emp group by post;
select post,group_concat(name,':',salary) from emp group by post;
# 2.concat()方法 用于分组之前
select post,group_concat(name,':',salary) from emp group by post;
# 3.concat_ws()方法 用于分组之前 多个字段相同分隔符情况
select concat_ws('|',name,age,salary,gender) from emp;
# 4.as语法
1.可以给查询出来的字段名起别名(可以省略但是不推荐省略)
select id as '序号',name as '姓名' from emp;
2.还可以给表名起别名(主要用在多表查询中)
select * from emp as t1 where t1.id > 5;
'''将emp表名起别名为t1 之后使用t1替代emp'''
总结:as左边是字段则是字段别名 左边是表名则是表别名
多表查询理论
多表查询:所需数据来源于多张表数据的组合
#建表
create table dep(
id int primary key auto_increment,
name varchar(20)
);
create table emp(
id int primary key auto_increment,
name varchar(20),
gender enum('male','female') not null default 'male',
age int,
dep_id int
);
#插入数据
insert into dep values
(200,'技术'),
(201,'人力资源'),
(202,'销售'),
(203,'运营');
insert into emp(name,gender,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);
#############################################################
SQL语句查询出来的结果其实也可以看成是一张表
涉及到多表可能会出现字段名冲突需要在字段名前面加上表名作限制
#############################################################
# 查询各员工姓名及对应的部门名称
员工姓名在emp表
部门名称在dep表
# 推导过程1
select * from emp,dep; # 笛卡尔积
# 推导过程2
select * from emp,dep where emp.dep_id=dep.id;
# 推导过程3
select emp.name,dep.name from emp,dep where emp.dep_id=dep.id;
多表查询之联表
"""先将多张表拼接成一张大表 然后在基于单表查询完成"""
# 在MySQL中拼接表有专门的关键字
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填充
right join 右链接
select * from emp right 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 right join dep on emp.dep_id=dep.id;
多表查询之子查询
"""将一张表的查询结果括号括起来当成另外一条SQL语句的条件"""
子查询即日常生活中解决问题的思路>>>:分步操作
# 1.查询部门是技术或者人力资源的员工信息
方法1:连表操作
select emp.name,emp.age,dep.name from emp inner join dep on emp.dep_id=dep.id where dep.name in ('技术','人力资源');
方式2:子查询
1.先查询技术和人力资源id号
select id from dep where name in ('技术','人力资源');
2.再去员工表里面根据部门id号筛选出员工数据
select * from emp where dep_id in (select id from dep where name in ('技术','人力资源'));
总结
#####################################################
涉及到多表查询只有两种方法
1.联表操作
2.子查询
并且很多复杂的查询甚至需要两者的结合
#####################################################
可视化软件之Navicat
在工作中有时候需要更加快速的完成一些基本操作 可视化软件可以大大提升工作效率 Navicat是一款可以操作多种数据库的软件 内部其实就是封装了相应的SQL语句
Navicat软件也是需要收费的 但是我们可以使用破解版本
破解地址:https://defcon.cn/214.html
老版本破解:http://www.ddooo.com/softdown/59996.htm
免费试用14天
基本使用
有些功能如果没有你也可以直接修改软件自动生成的SQL语句
1.链接
2.创建
3.外键
4.查询
5.SQL文件
多表查询数据准备
/*
数据导入：
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;
0909(周四)内容概要
- 多表查询练习题(有难度)
- python代码操作MySQL
- 基于python代码和MySQL实现用户注册登录
0909内容详细
多表查询练习题(有难度)
1、查询所有的课程的名称以及对应的任课老师姓名
2、查询平均成绩大于八十分的同学的姓名和平均成绩
3、查询没有报李平老师课的学生姓名
4、查询没有同时选修物理课程和体育课程的学生姓名
5、查询挂科超过两门(包括两门)的学生姓名和班级
# 在编写较为复杂的SQL查询时不要想着一口气写完
# 写一点查一点看一点再写
# 任何复杂的SQL都是慢慢拼凑出来的
-- 在编写复杂SQL的时候可以先写中文思路 之后再拼凑SQL
-- 1、查询所有的课程的名称以及对应的任课老师姓名
# 1.该题涉及到几张表 课程表与老师表
# 2.需要的数据在两张表中 需要利用多表查询 思考确定为连表操作
# 3.最后确定select后需要的字段名称
-- SELECT
-- course.cname,
-- teacher.tname
-- FROM
-- course
-- INNER JOIN teacher ON course.teacher_id = teacher.tid;
-- 2、查询平均成绩大于八十分的同学的姓名和平均成绩
# 1.先明确需要用到几张表 学生表 成绩表
# 2.分析题意可知先求解分数表中平均成绩大于80分的学生id号
# 3.由于最终的需求是学生姓名和第二步中的平均成绩 所以此处应该采用连表操作更合适
# 求每个学生的平均成绩 按照学生id分组再利用聚合函数avg
-- select student_id,avg(num) from score group by student_id having avg(num)>80;
# 将上述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;
-- 3、查询没有报李平老师课的学生姓名
# 1.该题需要使用四张表
# 2.求报了李平老师课程的学生id
# 3.再去学生表中取反操作获取没有报李平老师课程的学生姓名
# 步骤1:查询李平老师id号
-- select tid from teacher where tname='李平老师'
# 步骤2:根据老师id号筛选出课程id号
-- select cid from course where teacher_id=(select tid from teacher where tname='李平老师');
# 步骤3:根据课程id去分数表中筛选除对应的学生id号
-- select distinct student_id from score where course_id in (select cid from course where teacher_id=(select tid from teacher where tname='李平老师'));
# 步骤4:去学生表中依据学生id号取反
-- SELECT
-- sname
-- FROM
-- student
-- WHERE
-- sid NOT IN (
-- SELECT DISTINCT
-- student_id
-- FROM
-- score
-- WHERE
-- course_id IN (
-- SELECT
-- cid
-- FROM
-- course
-- WHERE
-- teacher_id = (
-- SELECT
-- tid
-- FROM
-- teacher
-- WHERE
-- tname = '李平老师'
-- )
-- )
-- );
-- 4、查询没有同时选修物理课程和体育课程的学生姓名(只要报了一门的 两门都报和都不报的都不要)
# 1.该题涉及到的表有三张表
# 步骤1:先获取物理和体育课程的id号
-- select cid from course where cname in ('物理','体育');
# 步骤2:先根据课程id筛选出所有报了物理和体育的学生id(两门都报了 只报一门的)
-- select * from score where course_id in (select cid from course where cname in ('物理','体育'));
# 步骤3:按照学生id分组 统计分组下课程数量 筛选出数量为1的即可
-- select student_id from score where course_id in (select cid from course where cname in ('物理','体育')) group by student_id HAVING count(course_id)=1;
# 步骤4:根据学生id去学生表中筛选出学生姓名
-- SELECT
-- sname
-- FROM
-- student
-- WHERE
-- sid IN (
-- SELECT
-- student_id
-- FROM
-- score
-- WHERE
-- course_id IN (
-- SELECT
-- cid
-- FROM
-- course
-- WHERE
-- cname IN ('物理', '体育')
-- )
-- GROUP BY
-- student_id
-- HAVING
-- count(course_id) = 1
-- );
--
-- 5、查询挂科超过两门(包括两门)的学生姓名和班级
# 该题涉及到三张表
# 步骤1:去score表中筛选出挂科超过两门的学生
# 1.先筛选出所有num小于60分的数据
-- select * from score where num < 60;
# 2.按照学生id分组统计每个学生挂科的次数筛选出两门及以上的
-- select student_id from score where num < 60 group by student_id having count(course_id) >= 2;
# 3.根据学生id获取对应的学生姓名和班级号
-- select sname,class_id from student where sid in (select student_id from score where num < 60 group by student_id having count(course_id) >= 2)
# 4.将步骤3的表和class连起来
SELECT
class.caption,
t1.sname
FROM
class
INNER JOIN (
SELECT
sname,
class_id
FROM
student
WHERE
sid IN (
SELECT
student_id
FROM
score
WHERE
num < 60
GROUP BY
student_id
HAVING
count(course_id) >= 2
)
) AS t1 ON class.cid = t1.class_id;
python操作MySQL
# python代码操作MySQL需要借助于第三方模块
第三方模块
本质也是模块只不过是网络上其他人写的
如果我们想要使用第三方模块需要基于网络先下载
'''python如何下载模块'''
pip3 # 环境变量 python解释器文件夹下的scripts目录
'''下载模块的基本语句'''
1.方式1:cmd终端
pip3 install pymysql
2.方式2:pycharm终端
pip3 install pymysql
3.方式3:pycharm快捷方式
settings>>>...
'''远程仓库'''
pip3下载模块的时候默认都是从国外的仓库下载模块数据
下载的过程有时候会非常的慢，我们可以切换到国内的仓库
"""
（1）阿里云 http://mirrors.aliyun.com/pypi/simple/
（2）豆瓣 http://pypi.douban.com/simple/
（3）清华大学 https://pypi.tuna.tsinghua.edu.cn/simple/
（4）中国科学技术大学 http://pypi.mirrors.ustc.edu.cn/simple/
（5）华中科技大学http://pypi.hustunique.com/
"""
1.命令临时切换
pip3 install 模块名 -i 仓库地址
2.pycharm更改仓库地址
settings 下载模块界面下方点击manage...
3.永久更改
需要修改python解释器内置的配置文件
'''pip3下载模块报错'''
1.报错信息里面含有timeout关键字
原因:当前计算机网络不稳定
措施:多执行几次或者更换网络
2.报错信息里面含有warning警告版本过低
原因:pip3工具版本过低需要更新
措施:直接拷贝提示的更新命令更新即可
3.报错信息里面没有任何关键字就是一堆红色字体
原因:可能是即将下载的模块对计算机环境有要求
措施:下载之前需要先准备好环境(百度搜一下)
pymysql模块
import pymysql
# 创建连接对象
conn = pymysql.connect(
host='127.0.0.1',
port=3306,
user='root',
password='123',
database='db6',
charset='utf8'
)
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) # 生成游标对象 等待用户输入命令
# 自定义sql语句
sql = 'select * from teacher'
# 执行sql语句
cursor.execute(sql)
# 获取执行的结果
res = cursor.fetchall()
print(res)
0910(周五)内容概要
- pymysql其他操作
- SQL注入
- 基于pymysql实现用户注册登录
- 事务
- 用户管理
- 索引
- 其他辅助知识补充
0910内容详细
pymysql其他操作
import pymysql
conn = pymysql.connect(
host='127.0.0.1',
port=3306,
user='root',
password='123', # 支持简写passwd
database='db6', # 支持简写db
charset='utf8',
autocommit=True # 自动确认
)
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
# 默认可以执行查询操作
# sql = 'select * from teacher'
# 默认无法执行插入操作
sql = 'insert into userinfo(name,password) values("tony",222)'
# 默认无法执行修改操作
# sql = 'update userinfo set name="jasonNB" where id=1'
# 默认无法执行删除操作
# sql = 'delete from userinfo where id=4'
'''pymysql模块针对增删改 需要二次确认'''
affect_rows = cursor.execute(sql)
# conn.commit() # 二次确认 提交
# print(affect_rows) # 返回值表示的是执行sql语句之后影响的数据行数
# print(cursor.fetchall()) # 从结果中获取所有的数据
# print(cursor.fetchone()) # 从结果中获取一条数据
# print(cursor.fetchmany(2)) # 从结构中指定获取几条数据
# print(cursor.fetchone())
# cursor.scroll(2, 'relative') # 相对于当前位置左右移动 正数往右 负数往左
# cursor.scroll(2,'absolute') # 相对于起始位置左右移动 正数往右 负数往左
# print(cursor.fetchone())
SQL注入
import pymysql
# 1.先获取用户名和密码
username = input('username>>>:').strip()
password = input('password>>>:').strip()
# 2.链接MySQL
conn = pymysql.connect(
host='127.0.0.1',
port=3306,
user='root',
password='123', # 支持简写passwd
database='db6', # 支持简写db
charset='utf8',
autocommit=True # 自动确认
)
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
# 3.拼接查询sql语句
sql = "select * from userinfo where name=%s and password=%s"
print(sql)
'''pymysql模块针对增删改 需要二次确认'''
# affect_rows = cursor.execute(sql, (username, password))
# 如果想一次性插入多条数据 可以使用下面的方法
affect_rows = cursor.executemany(sql, [('jason', 123), ('jason1', 123), ('jason2', 321), ('jason3', 222)])
res = cursor.fetchall()
if res:
print(res, '登录成功')
else:
print('用户名或密码错误')
"""
现象1
只需要用户名正确即可
select * from userinfo where name='jasonNB' -- ajsdkjaskldjklasd' and password=''
现象2
用户名密码都不需要也可以
select * from userinfo where name='xxx' or 1=1 -- jkasdjaksldklsajd' and password=''
"""
#############################################################
利用一些特殊符号和特殊语法的形式拼接出违背常理的语句
如何解决上述现象
在SQL注入中涉及到关键性的数据不要自己手动拼接
交由固定的方法拼接(方法会自动过滤掉特殊符号)
#############################################################
sql = "select * from userinfo where name=%s and password=%s"
'''pymysql模块针对增删改 需要二次确认'''
affect_rows = cursor.execute(sql, (username, password))
用户管理
1.创建用户
create user 用户名 identified by '密码';
"""修改密码"""
set password for 用户名 = Password('新密码');
set password = Password('新密码'); # 针对当前登录用户
2.重命名
rename user 新用户名 to 旧用户名;
3.删除用户
drop user 用户名;
4.查看用户访问权限
show grants for 用户名;
5.授予访问权限
grant select on db1.* to 用户名;
# 授予用户对db1数据库下所有表使用select权限
6.撤销权限
revoke select on db1.* from 用户名;
"""
整个服务器
grant all/revoke all
整个数据库
on db.*
特定的表
on db.t1
"""
事务
四大特性:ACID
A:原子性
一个事务是一个完整的整体 不能分割 要么同时成功要么同时失败
C:一致性
一致性是指事务必须使数据库从一个一致性状态变换到另一个一致性状态
I:独立性
事务与事务彼此不干扰 相互独立
D:持久性
事务一旦被提交了，那么对数据库中的数据的改变就是永久性的
create table user(
id int primary key auto_increment,
name char(32),
balance int
);
insert into user(name,balance)
values
('jason',1000),
('kevin',1000),
('tony',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='tony'; #卖家拿到90元
# 回滚到上一个状态
rollback;
# 确认事务没有问题
commit; # 确认之后就无法再回退
视图
# 1、什么是视图
视图就是通过查询得到一张虚拟表，然后保存下来，下次直接使用即可
# 2、为什么要用视图
如果要频繁使用一张虚拟表，可以不用重复查询
# 3、如何用视图
create view teacher2course as
select * from teacher inner join course on teacher.tid = course.teacher_id;
'''视图虽然好用但是不推荐使用!!!'''
触发器
在满足对某张表数据的增、删、改的情况下，自动触发的功能称之为触发器
# 为何要用触发器
触发器专门针对我们对某一张表数据增insert(前后)、删delete(前后)、改update(前后)的行为，这类行为一旦执行就会触发触发器的执行，即自动运行另外一段sql代码
"""
语法结构
create trigger 触发器的名字 before/after insert/update/delete on 表名 for each row
begin
sql语句
end
针对触发器的名字有一个小习惯
tri_before_insert_t1
触发器简写_之前或之后_操作_表名
"""
# 案例
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
('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');
# 查询errlog表记录
select * from errlog;
# 删除触发器
drop trigger tri_after_insert_cmd;
存储过程
# 相对于python中的自定义函数
delimiter $$
create procedure p1()
begin
select * from cmd;
end $$
delimiter ;
# 调用
call p1()
函数
# 相对于pythpn中的内置方法
"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条件语句
if i = 1 THEN
SELECT 1;
ELSEIF i = 2 THEN
SELECT 2;
ELSE
SELECT 7;
END IF;
# while循环语句
SET num = 0 ;
WHILE num < 10 DO
SELECT
num ;
SET num = num + 1 ;
END WHILE ;
索引
索引就是一种数据结构，类似于书的目录。
意味着以后在查数据应该先找目录再找数据，而不是用翻页的方式查询数据
索引在MySQL中也叫做"键"，是存储引擎用于快速找到记录的一种数据结构
主键 primary key
除了可以加快查询之外还有其他的功能
唯一键 unique
除了可以加快查询之外还有其他的功能
索引键 index key
除了可以加快查询之外没有其他的功能
外键 foreign key
跟索引半毛钱关系都没有 也不存在提升查询速度一说
索引的影响
* 在表中有大量数据的前提下,创建索引速度会很慢
* 在索引创建完毕后,对表的查询性能会大幅度提升,但是写的性能会降低
'''
有一张表按照name字段查询数据 速度很慢 如何解决?
将name字段制作索引
小结论:可以简单的认为按照什么字段查询慢就把改字段制作成索引
是不是一遇到查询比较慢，就把对应的字段做成索引?
不对!!!
'''
聚集索引(primary key)
叶子结点放的一条条完整的记录
辅助索引(unique,index)
叶子结点存放的是辅助索引字段对应的那条记录的主键的值
# 覆盖索引
只在辅助索引的叶子节点中就已经找到了所有我们想要的数据
select name from user where name='jason';
# 非覆盖索引
虽然查询的时候命中了索引字段name，但是要查的是age字段，所以还需要利用主键才去查找
select age from user where name='jason';
实操练习
#1. 准备表
create table s1(
id int,
name varchar(20),
gender char(6),
email varchar(50)
);
#2. 创建存储过程，实现批量插入记录
delimiter $$ #声明存储过程的结束符号为$$
create procedure auto_insert1()
BEGIN
declare i int default 1;
while(i<3000000)do
insert into s1 values(i,'jason','male',concat('jason',i,'@oldboy'));
set i=i+1;
end while;
END$$ #$$结束
delimiter ; #重新声明分号为结束符号
#3. 查看存储过程
show create procedure auto_insert1\G
#4. 调用存储过程
call auto_insert1();
# 表没有任何索引的情况下
select * from s1 where id=30000;
# 避免打印带来的时间损耗
select count(id) from s1 where id = 30000;
select count(id) from s1 where id = 1;
# 给id做一个主键
alter table s1 add primary key(id); # 速度很慢
select count(id) from s1 where id = 1; # 速度相较于未建索引之前两者差着数量级
select count(id) from s1 where name = 'jason' # 速度仍然很慢
"""
范围问题
"""
# 并不是加了索引，以后查询的时候按照这个字段速度就一定快
select count(id) from s1 where id > 1; # 速度相较于id = 1慢了很多
select count(id) from s1 where id >1 and id < 3;
select count(id) from s1 where id > 1 and id < 10000;
select count(id) from s1 where id != 3;
alter table s1 drop primary key; # 删除主键 单独再来研究name字段
select count(id) from s1 where name = 'jason'; # 又慢了
create index idx_name on s1(name); # 给s1表的name字段创建索引
select count(id) from s1 where name = 'jason' # 仍然很慢！！！
"""
再来看b+树的原理，数据需要区分度比较高，而我们这张表全是jason，根本无法区分
那这个树其实就建成了“一根棍子”
"""
select count(id) from s1 where name = 'xxx';
# 这个会很快，我就是一根棍，第一个不匹配直接不需要再往下走了
select count(id) from s1 where name like 'xxx';
select count(id) from s1 where name like 'xxx%';
select count(id) from s1 where name like '%xxx'; # 慢 最左匹配特性
# 区分度低的字段不能建索引
drop index idx_name on s1;
# 给id字段建普通的索引
create index idx_id on s1(id);
select count(id) from s1 where id = 3; # 快了
select count(id) from s1 where id*12 = 3; # 慢了 索引的字段一定不要参与计算
drop index idx_id on s1;
select count(id) from s1 where name='jason' and gender = 'male' and id = 3 and email = 'xxx';
# 针对上面这种连续多个and的操作，mysql会从左到右先找区分度比较高的索引字段，先将整体范围降下来再去比较其他条件
create index idx_name on s1(name);
select count(id) from s1 where name='jason' and gender = 'male' and id = 3 and email = 'xxx'; # 并没有加速
drop index idx_name on s1;
# 给name，gender这种区分度不高的字段加上索引并不难加快查询速度
create index idx_id on s1(id);
select count(id) from s1 where name='jason' and gender = 'male' and id = 3 and email = 'xxx'; # 快了 先通过id已经讲数据快速锁定成了一条了
select count(id) from s1 where name='jason' and gender = 'male' and id > 3 and email = 'xxx'; # 慢了 基于id查出来的数据仍然很多，然后还要去比较其他字段
drop index idx_id on s1
create index idx_email on s1(email);
select count(id) from s1 where name='jason' and gender = 'male' and id > 3 and email = 'xxx'; # 快 通过email字段一剑封喉
"""联合索引"""
select count(id) from s1 where name='jason' and gender = 'male' and id > 3 and email = 'xxx';
# 如果上述四个字段区分度都很高，那给谁建都能加速查询
# 给email加然而不用email字段
select count(id) from s1 where name='jason' and gender = 'male' and id > 3;
# 给name加然而不用name字段
select count(id) from s1 where gender = 'male' and id > 3;
# 给gender加然而不用gender字段
select count(id) from s1 where id > 3;
# 带来的问题是所有的字段都建了索引然而都没有用到，还需要花费四次建立的时间
create index idx_all on s1(email,name,gender,id); # 最左匹配原则，区分度高的往左放
select count(id) from s1 where name='jason' and gender = 'male' and id > 3 and email = 'xxx'; # 速度变快
