多表查询
总结
# 1、书写sql语句的时候 select后面先用*占位 之后写完再改
# 2、写较为复杂得sql语句时应该循序渐进，一步一步的分步骤来写，切忌一步到位
# 3、多表查询时，联表操作与子查询会有结合使用得情况
知识点补充
# 但凡是多表查询就有两种思路：1、联表操作 2、子查询
# 查询平均年龄在25岁以上得部门名称
# 联表操作
1 先拿到部门和员工表 拼接之后的结果
2 分析语义 得出需要进行分组
select dep.name from emp inner join dep
on emp.dep_id = dep.id
group by dep.name
having avg(age) > 25
;
"""涉及到多表操作的时候 一定要加上表的前缀"""
# 子查询
select name from dep where id in
(select dep_id from emp group by dep_id
having avg(age) > 25);
# 关键字exists(了解)
只返回布尔值 True False
返回True的时候外层查询语句执行
返回False的时候外层查询语句不再执行
select * from emp where exists
(select id from dep where id>3);
select * from emp where exists
(select id from dep where id>300);
Navicat软件
"""
我们在终端操作MySQL 也没有自动提示也无法保存等等 不方便开发
Navicat内部封装了所有的操作数据库的命令
用户在使用它的时候只需要鼠标即可完成操作 无需书写sql语句
"""
"""
1 MySQL是不区分大小写的
验证码忽略大小写
内部统一转大写或者小写比较即可
upper
lower
2 MySQL建议所有的关键字写大写
3 MySQL中的注释 有两种
--
#
4 在navicat中如何快速的注释和解注释
ctrl + ？ 加注释
ctrl + ？ 基于上述操作再来一次就是解开注释
如果你的navicat版本不一致还有可能是
ctrl + shift + ？解开注释
"""
练习
建表准备 test.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;
# 查询所有的课程的名称以及对应的任课老师姓名
SELECT
course.cname,
teacher.tname
FROM
course
INNER JOIN teacher ON course.teacher_id = teacher.tid;
# 查询平均成绩大于八十分的同学的姓名和平均成绩
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;
# 查询没有选择李平老师课程的学生姓名
# 分步操作
# 1 先找到李平老师教授的课程id
# 2 再找所有报了李平老师课程的学生id
# 3 之后去学生表里面取反 就可以获取到没有报李平老师课程的学生姓名
SELECT
sname
FROM
student
WHERE
sid NOT IN (
SELECT DISTINCT
student_id
FROM
score
WHERE
course_id IN (
SELECT
cid
FROM
course
INNER JOIN teacher ON course.teacher_id = teacher.tid
WHERE
teacher.tname = '李平老师'
));
# 查询没有同时选修物理课程和体育课程的学生姓名
# 1 先查物理和体育课程的id
# 2 再去获取所有选了物理和体育的学生数据
# 3 按照学生分组 利用聚合函数count筛选出只选了一门的学生id
# 4 依旧id获取学生姓名
SELECT
student.sname
FROM
student
INNER JOIN score ON student.sid = score.student_id
WHERE
score.course_id IN (
SELECT
cid AS course_id
FROM
course
WHERE
cname IN ( '物理', '体育' ))
GROUP BY
score.student_id
HAVING
COUNT( score.course_id )= 1;
# 查询挂科超过两门（包括两门）的学生姓名和班级
# 1 先筛选出所有分数小于60的数据
# 2 按照学生分组 对数据进行计数获取大于等于2的数据
SELECT
class.caption,
student.sname
FROM
student
INNER JOIN class ON student.class_id = class.cid
WHERE
sid IN (
SELECT
student_id
FROM
score
WHERE
num < 60
GROUP BY
student_id
HAVING
count( student_id )>= 2
);
python与MySQL——pymysql模块
# 支持python代码操作数据库MySQL
'''pip3 install pymysql'''
import pymysql
# 连接数据库
conn = pymysql.connect(
host='127.0.0.1', # 'ip'
port=3306, # '端口'
user='root', # 'mysql用户名'
password='yumi_0405', # 'mysql密码'
database='day48', # '要连接的库名'
charset='utf8' # 编码千万不要加-
)
# 产生一个游标对象(就是用来帮你执行命令的)
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
"""
cursor=pymysql.cursors.DictCursor将查询结果以字典的形式返回
"""
sql = 'select * from teacher;'
res = cursor.execute(sql)
# print(res) # execute返回的是你当前sql语句所影响的行数 改返回值一般不用
# 获取命令执行的查询结果
# print(cursor.fetchone()) # 只拿一条
# print(cursor.fetchall()) # 拿所有
# print(cursor.fetchmany(2)) # 可以指定拿几条
print(cursor.fetchone())
print(cursor.fetchone()) # 读取数据类似于文件光标的移动
# cursor.scroll(1,'relative') # 相对于光标所在的位置继续往后移动1位
cursor.scroll(1,'absolute') # 相对于数据的开头往后继续移动1位
print(cursor.fetchall())
sql注入
"""fuwuqi
sql注入是一种注入攻击，通过将任意SQL代码插入数据库查询，从而来完全掌控数据库服务器。
利用一些语法的特性 书写一些特点的语句实现固定的语法
MySQL利用的是MySQL的注释语法
select * from user where name='jason' -- jhsadklsajdkla' and password=''
select * from user where name='xxx' or 1=1 -- sakjdkljakldjasl' and password=''
"""
import pymysql
conn = pymysql.connect(
host='127.0.0.1',
port=3306,
user='root',
password='yumi_0405',
db='day48',
charset='utf8'
)
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
user = input('>>>:').strip()
pwd = input('>>>:').strip()
sql = "select * from user where user_name='%s'and pass_word='%s';" % (user, pwd)
if cursor.execute(sql):
print('登陆成功')
cursor.fetchall()
else:
print('用户名密码错误')
# 解决：讲数据的拼接交给execute来做，不要自己拼接
sql = "select * from user where user_name='%s'and pass_word='%s';"
# 自动识别sql里面的%s用后面元组里面的数据替换
if cursor.execute(sql,(user,pwd)):
print('登陆成功')
cursor.fetchall()
else:
print('用户名密码错误')
