• 查询关键字

• 多表查询的思想

  1.子查询
  2.连表操作

• Navicat可视化软件

  鼠标点点点完成数据库的操作(内部其实还是SQL语句)

• 多表查询练习题(重要)

  稍微有点难度  需要花时间

• python操作MySQL

内容详细

查询关键字之having过滤

功能上having与where是一模一样的
但是使用位置上有所不同
    where在分组之前使用
    having在分组之后使用

1.统计各部门年龄在30岁以上的员工平均工资，并且保留平均工资大于10000的部门
# 1.先筛选出所有30岁以上的员工
    select * from emp where age>30;
# 2.然后再按照部门分组
    ‘‘‘SQL语句的查询结构我们也可以直接看成是一张表‘‘‘
    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 age from emp;

查询关键字之order by排序

select * from emp order by salary;  # 默认是升序select * from emp order by salary asc;  # 升序关键字 可以不写select * from emp order by salary desc;  # 降序# 排序也可以指定多个字段select * from emp order by age desc,salary asc;# 统计各部门年龄在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,5;  # 起始位置、条数

# 查询工资最高的人的详细信息
    # 先按照工资排序 然后限制展示条数
select * from emp order by salary desc limit 1;

查询关键字之regexp正则

正则表达式
    用一些特殊符号的组合去字符串中筛选出符合条件的数据

select * from emp where name regexp ‘^j.*(n|y)$‘;
# ‘^j.*(n|y)$‘  j开头 中间无所谓 n或者y结尾

多表查询思想

1.子查询
    分步解决问题
    将一条SQL语句的查询结果用括号括起来当作另外一条SQL语句的查询条件

2.连表操作
    先将所有需要用到的表拼接到一起(一张表)
    然后就是转换成单表查询

前期表准备

#建表
create table dep(
id int primary key auto_increment,
name varchar(20) 
);

create table emp(
id int primary key auto_increment,
name varchar(20),
sex enum(‘male‘,‘female‘) not null default ‘male‘,
age int,
dep_id int
);

#插入数据
insert into dep values
(200,‘技术‘),
(201,‘人力资源‘),
(202,‘销售‘),
(203,‘运营‘);

insert into emp(name,sex,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);

子查询

# 查询jason所在的部门名称
    # 第一步 先获取jason所在的部门id
    select dep_id from emp where name=‘jason‘;
    # 第二步 根据id号去部门表中筛选
    select * from dep where id = 200;
    # 完整句式
    select * from dep where id=(select dep_id from emp where name=‘jason‘);

连表操作

# 前戏(了解)select * from emp,dep;# 基于上表筛选数据(了解)    ‘‘‘为了避免字段冲突 可以在字段名前面加表名明确‘‘‘select * from emp,dep where emp.dep_id=dep.id;########################掌握############################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 全连接 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;

Navicat可视化软件

可以充当很多数据库软件的客户端 封装了很多快捷方法该软件默认也是收费的 需要破解正版不破解免费试用14天破解版(老版本):https://pan.baidu.com/s/1bpo5mqj1.下载与安装2.使用方法  创建库 表 记录        注意主键     外键字段    逆向数据库到模型    转储SQL文件    查询

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;

多表查询题

1、 查询所有的课程的名称以及对应的任课老师姓名4、 查询平均成绩大于八十分的同学的姓名和平均成绩7、 查询没有报李平老师课的学生姓名8、 查询没有同时选修物理课程和体育课程的学生姓名9、 查询挂科超过两门(包括两门)的学生姓名和班级#####################关键字习惯都用大写################################ 建议:在书写SQL语句的时候一定不要想着一次性成功 写一点看一点再写一点  慢慢拼凑起来-- 1、 查询所有的课程的名称以及对应的任课老师姓名# 1.先明确需要的表  course表 teacher表--  select * from course;--     select * from teacher;# 2.连表操作 明确字段-- SELECT--  course.cname,--     teacher.tname-- FROM--  course--    INNER JOIN teacher ON course.teacher_id = teacher.tid;-- 4、 查询平均成绩大于八十分的同学的姓名和平均成绩# 1.先查看成绩表 --     select * from score;# 2.求所有学生的平均成绩--    select score.student_id,avg(num) from score group by score.student_id;# 3.筛选出大于80分--    select score.student_id,avg(num) as ‘avg_num‘ from score group by score.student_id having avg(num)>80--     ;# 4.学生表与上述查询出来的表连接-- SELECT--  student.sname,--    t1.avg_num -- FROM--    student--   INNER JOIN ( SELECT score.student_id, avg( num ) AS ‘avg_num‘ FROM score GROUP BY score.student_id HAVING avg( num )> 80 ) AS t1 ON student.sid = t1.student_id;-- 7、 查询没有报李平老师课的学生姓名# 1.正向思路:课下可以尝试一下# 2.反向思路:先找所有报了李平老师课程的学生 再取反# 1.先查询李平老师教授的课程id号-- select tid from teacher WHERE tname=‘李平老师‘;-- select cid from course where teacher_id in (select tid from teacher WHERE tname=‘李平老师‘);# 2.去成绩表中筛选出所有报了李平老师课程的学生id号-- select distinct student_id from score where course_id in (select cid from course where teacher_id in (select tid from teacher WHERE tname=‘李平老师‘)); # 3.去学生表中 取反获取没有报李平老师课程的学生姓名-- 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 = ‘李平老师‘ )));-- 8、 查询没有同时选修物理课程和体育课程的学生姓名(只要报了一门的 两门和都不报都不要)# 1.先查询物理 和 体育课程的id号--  select cid from course where cname in (‘物理‘,‘体育‘);# 2.去成绩表中先筛选出所有报了课程的数据(报了一门 报了两门)-- select * from score where course_id in (select cid from course where cname in (‘物理‘,‘体育‘));# 3.按照学生id分组 统计每个学生报了的课程数目-- 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);-- 9、 查询挂科超过两门(包括两门)的学生姓名和班级# 1.先去成绩表中 筛选出分数小于60分的数据-- select * from score where num<60;# 2.按照学生id分组 然后统计个数-- select student_id from score where num<60 group by student_id--  having count(num) >= 2-- ;# 3.将班级表与学生表拼接起来SELECT    class.caption,  student.sname FROM  class   INNER JOIN student ON class.cid = student.class_id WHERE    student.sid IN ( SELECT student_id FROM score WHERE num < 60 GROUP BY student_id HAVING count( num ) >= 2 );

mysql基础4