09_04、查询关键字与Navicat可视化工具的使用

一、查询关键字

1、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);  # 简写


"""
模糊查询
    关键字  
        like
    关键符号
        %:匹配任意个数的任意字符
        _:匹配单个个数的任意字符
    show variables like '%mode%';
"""
# 3.查询姓名中带有字母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.查询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;

2、group by 分组

分组
    将单个单个的个体按照指定的条件分成一个个整体

"""
分组之后默认只能直接获取到分组的依据
其他字段无法再直接获取(可以间接获取)
"""
# 严格模式
set global sql_mode='STRICT_TRANS_TABLES,PAD_CHAR_TO_FULL_LENGTH,only_full_group_by'



# 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,count(id) from emp group by post;
# 5.每个部门的月工资总和
select post,sum(salary) from emp group by post;

"""
可以给字段起别名(as还可以给表起别名)
    select post as '部门',sum(salary) as '总和' from emp group by post;
"""
# 查询分组之后的部门名称和每个部门下所有的员工姓名
"""
group_concat()  获取分组以外的字段数据 并且支持拼接操作
select post,group_concat(name) from emp group by post;
select post,group_concat(name,':',salary) from emp group by post;

concat()  未分组之前使用的拼接功能
select concat(name,':',sex) from emp;

concat_ws()
select concat_ws(':',name,sex,salary,age) from emp;
"""

3、聚合函数

分组之后频繁需要使用的
    max        最大值
    min        最小值
    sum        求和
    count     计数
    avg        平均值

4、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
        ;

5、distinct 去重

去重有一个非常严格的前提条件 数据必须是完全一样
    如果数据带有主键那么肯定无法去重
select distinct age from emp;

6、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);

7、limit 分页

用来限制数据的展示条数
select * from emp limit 5;  # 前五条
select * from emp limit 5,5;  # 起始位置、条数

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

8、regexp 正则表达式

正则表达式
    用一些特殊符号的组合去字符串中筛选出符合条件的数据
       
select * from emp where name regexp '^j.*(n|y)$';
# '^j.*(n|y)$'  j开头 中间无所谓 n或者y结尾

9、多表查询思想

1.子查询

分步解决问题

将一条SQL语句的查询结果用括号括起来当作另外一条SQL语句的查询条件

# 查询egon所在的部门名称

    # 第一步 先获取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')

2.连表查询

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

# 前戏(了解)
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可视化工具

1、Navicat简介

Navicat通过对数据库的封装，方便并简化了数据库的操作和使用，可以充当很多数据库软件的客户端
该软件默认也是收费的，正版不破解免费试用14天

2、安装与使用

略

三、练习题

1、练习题文本

r         : 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;

SQL练习题文件

2、习题

1、 查询所有的课程的名称以及对应的任课老师姓名
4、 查询平均成绩大于八十分的同学的姓名和平均成绩
7、 查询没有报李平老师课的学生姓名
8、 查询没有同时选修物理课程和体育课程的学生姓名
9、 查询挂科超过两门(包括两门)的学生姓名和班级

3、答案

#####################关键字习惯都用大写###############################
# 建议:在书写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 );