9/9
今日考题
1.在一列展示中想获取多个字段数据有哪些操作方法
# 通过关键字concat()
select age,group_concat(name,'|',salary) from emp group by age;
select concat(name,'|',salary) from emp;
select concat_ws('|',name,age,salary) from emp;
2.多表查询有几种方法,具体都是怎么做的
多表查询方法就两种
1.联表法
将两张表通过关键字inner join内链接(left join左链接,right join右链接,union全链接)关键字组合成一张表
然后通过表名点字段名筛选获取想要的数据
# 即把SQL语句查询出来的结果当成一张表使用
2.子查询
先去一张表中索引到与另一张表有关的数据
通过索引到的数据再去另一张表中查询所需数据
# 即把SQL语句查询出来的结果当成一个条件使用
'''复杂情况下可能出现两种方法一起使用'''
3.你接触过操作数据库的可视化软件吗,该软件有何特点及主要功能
Navicat数据可视化软件,用于很多数据库特别是mysql的可视化
软件帮助用户可以通过简单的鼠标操作就能完成一些基本操作,不容易出错,也提高效率
功能:SQL语句能实现的简单操作软件里都可以实现
少有的一些无法直接通过操作软件写入的需求也可以通过在软件里打开SQL语句自行添加
复习巩固
- 补充知识
1.分组前拼接数据操作
concat()
2.分组后拼接数据
group_concat()
3.分组前多个相同连接符
concat_ws()
4.起别名as
可以给字段起别名
也可以给表起别名
- 多表查询
- 可视化软件
navicat是一款可以充当很多数据库软件的客户端软件
内容概要
- 多表查询练习题(难)
- python代码操作MySQL
- 基于python代码和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.查询所有课程名称以及对应的任课老师姓名
# 先思考用上几张表
# 需要的数据在两张表中就需要多表操作
# 而这边所需数据在两个表中所以联表操作更方便
SELECT
course.cname,
teacher.tname
FROM
course
INNER JOIN teacher ON course.teacher_id = teacher.tid;
2.查询平均成绩大于80分的同学姓名和平均成绩
# 要用的表格是stuent和score两张表
# 查询的数据出现在两张表里 用联表操作会好点
SELECT
student.sname,
AVG( score.num )
FROM
student
INNER JOIN score ON student.sid = score.student_id
GROUP BY
score.student_id
HAVING
AVG( score.num )> 80;
3.查询没有报李平老师课的学生姓名
# 由于结果只要一个学生姓名子查询会好一些
# 先找李平老师id
-- select tid
-- from teacher
-- WHERE tname in ('李平老师');
# 再找到对应的课程id
-- select cid from course
-- where teacher_id in (select tid
-- from teacher
-- WHERE tname in ('李平老师'));
# 再找到学生表里有这个课程编号的学生id
-- select DISTINCT student_id from score # 这个老师有两节课所以拿到学生id重复加去重就行
-- where course_id in (select cid from course
-- where teacher_id in (select tid
-- from teacher
-- WHERE tname in ('李平老师')));
# 最后通过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 IN (
SELECT
tid
FROM
teacher
WHERE
tname IN ( '李平老师' ))));
4.查询只选了物理和体育其中一门的学生姓名
# 先子查询找到物理课和体育课的id
-- SELECT cid from course
-- WHERE cname in ('物理','体育');
# 先用课程id找到所有报了物理和体育的
-- SELECT * FROM score
-- WHERE course_id in (SELECT cid from course
-- WHERE cname in ('物理','体育'));
# 这就这张表就是已经把两个都没选的筛选出去了
# 按学生id分组 统计课程数量 然后筛选出数量为1的
-- select student_id
-- from (SELECT * FROM score
-- WHERE course_id in (SELECT cid from course
-- WHERE cname in ('物理','体育'))) as p_p
-- GROUP BY student_id where
-- having count(student_id)=1;
select student_id from score
where course_id in ((SELECT cid from course
WHERE cname in ('物理','体育'))
group by student_id
having count(studenet_id)=1;
# 最后通过找到的id获取名字
1.SELECT
sname
FROM
student
WHERE
sid IN (
SELECT
student_id
FROM
(
SELECT
*
FROM
score
WHERE
course_id IN (
SELECT
cid
FROM
course
WHERE
cname IN ( '物理', '体育' ))) AS p_p
# 这边这个表太长了起个别名p_p
GROUP BY
student_id
HAVING
count( student_id )= 1
);
2.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( student_id )= 1
);
5.查询挂科超过两门(包括两门)的学生姓名和班级
# 最终数据在两张表里所以联表一定用得上
# 先去筛选出所有挂科的数据
-- select * from score
-- where num < 60;
-- # 按学生分组计数出超过两次的拿到学生id
-- select student_id from score
-- where num <60
-- GROUP BY student_id
-- HAVING COUNT(student_id)>=2;
# 通过学生id找到学生表里的学生名字和班级id
-- select class_id,sname from student
-- where sid IN (select student_id from score
-- where num <60
-- GROUP BY student_id
-- HAVING COUNT(student_id)>=2);
# 把上一步的表和班级表联起来再选取想要的数据
SELECT
class.caption,
fail.sname
FROM
class
INNER JOIN (
SELECT
class_id,
sname
FROM
student
WHERE
sid IN ( SELECT student_id FROM score WHERE num < 60 GROUP BY student_id HAVING COUNT( student_id )>= 2 )) AS fail
ON class.cid = fail.class_id;
python代码操作MySQL
# python操作MySQL需要借助第三方模块
第三方模块 本质就是一些大佬写出来的发到网上给我们白嫖
想要使用就要基于网络下载
'''如何下载模块'''
1.把python解释器中scripts目录加到环境变量
2.下载方法
2.1用cmd终端输入
pip3 install pymysql
2.2用pycharm终端
点击pycharm左下角的Terminal
同样输入pip3 install pymysql
2.3用pycharm里快捷键
file > settings > project打开详情 > interpreter > + 输入想要的模块名 > 左下角install
# 远程仓库
pip3下载模块默认是从国外的仓库下载
有时会非常的慢 这时候就可以切到国内仓库
国内仓库具体可以百度
阿里云 https://mirrors.aliyun.com/pypi/simple/
清华大学 https://pypi.tuna.tsinghua.edu.cn/simple/
豆瓣(douban) https://pypi.douban.com/simple/
中国科学技术大学 https://pypi.mirrors.ustc.edu.cn/simple/
'''更改仓库'''
1.通过终端指令临时切换
pip 模块名 -i 仓库地址
2.pycharm里更换仓库地址
file > settings > project打开详情 > interpreter > + > 左下manage > 添加想要的仓库地址
3.永久更改
需要修改配置文件(不推荐)
# 下载模块报错
1.报错信息里面含有timeout关键字
原因:当前计算机网络不稳定
措施:多执行几次或者更换网络
2.报错信息里面含有warning警告版本过低
原因:pip3工具版本过低需要更新
措施:直接拷贝提示的更新命令更新即可
3.报错信息里面没有任何关键字就是一堆红色字体
原因:可能是即将下载的模块对计算机环境有要求
措施:下载之前需要先准备好环境(百度搜一下)
pymysql模块
import pymysql
import pymysql
# 创建连接对象
conn = pymysql.connect(
host='127.0.0.1',
port=3306,
user='root',
password='123',
database='practise',
charset='utf8'
)
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) # 生成游标对象 等待用户输入命令
# 自定义sql语句
sql = 'select * from teacher' # 执行sql语句
cursor.execute(sql) # 获取执行的结果
info_dict = cursor.fetchall()
print(info_dict)
练习题网址