查询关键字、多表查询、可视化软件Navicat
学习内容总结
今日学习内容总结
查询关键字
查询关键字之having过滤
HAVING 关键字和 WHERE 关键字都可以用来过滤数据，且 HAVING 支持 WHERE 关键字中所有的操作符和语法。
但是 WHERE 和 HAVING 关键字也存在以下几点差异：
1. 一般情况下，WHERE 用于过滤数据行，而 HAVING 用于过滤分组。
2. WHERE 查询条件中不可以使用聚合函数，而 HAVING 查询条件中可以使用聚合函数。
3. WHERE 在数据分组前进行过滤，而 HAVING 在数据分组后进行过滤 。
4. WHERE 针对数据库文件进行过滤，而 HAVING 针对查询结果进行过滤。也就是说，WHERE 根据数据表中的字段直接进行过滤，而 HAVING 是根据前面已经查询出的字段进行过滤。
5. WHERE 查询条件中不可以使用字段别名，而 HAVING 查询条件中可以使用字段别名。
而为了更好的区分where与having，将where说成筛选，having说成过滤。
通过这样的案例来体验having：
# 统计每个部门年龄在30岁以上的员工的平均薪资并且保留平均薪资大于10000的部门
1. 先获取每个部门年龄在30岁以上的员工的平均薪资
mysql> select post, avg(salary) from emp where age > 30 group by post;
+---------+---------------+
| post | avg(salary) |
+---------+---------------+
| teacher | 255450.077500 |
| sale | 2500.240000 |
+---------+---------------+
2 rows in set (0.01 sec)
2. 再过滤出平均薪资大于10000的数据
mysql> select post, avg(salary) from emp
where age > 30 group by post
having avg(salary) > 10000;
+---------+---------------+
| post | avg(salary) |
+---------+---------------+
| teacher | 255450.077500 |
+---------+---------------+
1 row in set (0.00 sec)
# 同时针对聚合函数，我们后续有可能还需要在其他地方作为条件使用，可以先起别名
mysql> select post, avg(salary) as avg_salary from emp
where age > 30
group by post
having avg_salary > 10000;
+---------+---------------+
| post | avg_salary |
+---------+---------------+
| teacher | 255450.077500 |
+---------+---------------+
1 row in set (0.01 sec)
查询关键字之distinct去重
去重的前提：数据必须是一模一样的才可以(如果数据有主键肯定无法去重)
比如我们查询emp表中所有age数据：
mysql> select age from emp;
+-----+
| age |
+-----+
| 18 |
| 78 |
| 81 |
| 73 |
| 28 |
| 18 |
| 18 |
| 48 |
| 48 |
| 38 |
| 18 |
| 18 |
| 28 |
| 28 |
| 18 |
| 18 |
| 18 |
| 18 |
+-----+
18 rows in set (0.00 sec)
去重之后：
mysql> select distinct age from emp;
+-----+
| age |
+-----+
| 18 |
| 78 |
| 81 |
| 73 |
| 28 |
| 48 |
| 38 |
+-----+
7 rows in set (0.01 sec)
查询关键字之order by排序
通过实操体验排序：
1. 通过薪资进行排序
# 默认是升序
mysql> select * from emp order by salary;
+----+--------+--------+-----+------------+--------------------+--------------+------------+--------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
+----+--------+--------+-----+------------+--------------------+--------------+------------+--------+-----------+
| 11 | 西西 | female | 18 | 2011-03-12 | sale | NULL | 1000.37 | 402 | 2 |
| 10 | 呵呵 | female | 38 | 2010-11-01 | sale | NULL | 2000.35 | 402 | 2 |
| 5 | owen | male | 28 | 2012-11-01 | teacher | NULL | 2100.00 | 401 | 1 |
| 9 | 哈哈 | female | 48 | 2015-03-11 | sale | NULL | 3000.13 | 402 | 2 |
| 12 | 乐乐 | female | 18 | 2016-05-13 | sale | NULL | 3000.29 | 402 | 2 |
| 4 | tony | male | 73 | 2014-07-01 | teacher | NULL | 3500.00 | 401 | 1 |
| 13 | 拉拉 | female | 28 | 2017-01-27 | sale | NULL | 4000.33 | 402 | 2 |
| 1 | jason | male | 18 | 2017-03-01 | 张江第一帅形象代言 | NULL | 7300.33 | 401 | 1 |
| 3 | kevin | male | 81 | 2013-03-05 | teacher | NULL | 8300.00 | 401 | 1 |
| 6 | jack | female | 18 | 2011-02-11 | teacher | NULL | 9000.00 | 401 | 1 |
| 8 | sank | male | 48 | 2010-11-11 | teacher | NULL | 10000.00 | 401 | 1 |
| 14 | 僧龙 | male | 28 | 2016-03-11 | operation | NULL | 10000.13 | 403 | 3 |
| 18 | 程咬铁 | female | 18 | 2014-05-12 | operation | NULL | 17000.00 | 403 | 3 |
| 17 | 程咬铜 | male | 18 | 2015-04-11 | operation | NULL | 18000.00 | 403 | 3 |
| 16 | 程咬银 | female | 18 | 2013-03-11 | operation | NULL | 19000.00 | 403 | 3 |
| 15 | 程咬金 | male | 18 | 1997-03-12 | operation | NULL | 20000.00 | 403 | 3 |
| 7 | jenny | male | 18 | 1900-03-01 | teacher | NULL | 30000.00 | 401 | 1 |
| 2 | tom | male | 78 | 2015-03-02 | teacher | NULL | 1000000.31 | 401 | 1 |
+----+--------+--------+-----+------------+--------------------+--------------+------------+--------+-----------+
18 rows in set (0.01 sec)
# 加asc也是升序的意思
select * from emp order by salary asc;
# 加desc是降序
select * from emp order by salary desc;
2. 先按照年龄升序排序，如果年龄相同，则按照薪资降序排序
mysql> select * from emp order by age asc, salary desc;
+----+--------+--------+-----+------------+--------------------+--------------+------------+--------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
+----+--------+--------+-----+------------+--------------------+--------------+------------+--------+-----------+
| 7 | jenny | male | 18 | 1900-03-01 | teacher | NULL | 30000.00 | 401 | 1 |
| 15 | 程咬金 | male | 18 | 1997-03-12 | operation | NULL | 20000.00 | 403 | 3 |
| 16 | 程咬银 | female | 18 | 2013-03-11 | operation | NULL | 19000.00 | 403 | 3 |
| 17 | 程咬铜 | male | 18 | 2015-04-11 | operation | NULL | 18000.00 | 403 | 3 |
| 18 | 程咬铁 | female | 18 | 2014-05-12 | operation | NULL | 17000.00 | 403 | 3 |
| 6 | jack | female | 18 | 2011-02-11 | teacher | NULL | 9000.00 | 401 | 1 |
| 1 | jason | male | 18 | 2017-03-01 | 张江第一帅形象代言 | NULL | 7300.33 | 401 | 1 |
| 12 | 乐乐 | female | 18 | 2016-05-13 | sale | NULL | 3000.29 | 402 | 2 |
| 11 | 西西 | female | 18 | 2011-03-12 | sale | NULL | 1000.37 | 402 | 2 |
| 14 | 僧龙 | male | 28 | 2016-03-11 | operation | NULL | 10000.13 | 403 | 3 |
| 13 | 拉拉 | female | 28 | 2017-01-27 | sale | NULL | 4000.33 | 402 | 2 |
| 5 | owen | male | 28 | 2012-11-01 | teacher | NULL | 2100.00 | 401 | 1 |
| 10 | 呵呵 | female | 38 | 2010-11-01 | sale | NULL | 2000.35 | 402 | 2 |
| 8 | sank | male | 48 | 2010-11-11 | teacher | NULL | 10000.00 | 401 | 1 |
| 9 | 哈哈 | female | 48 | 2015-03-11 | sale | NULL | 3000.13 | 402 | 2 |
| 4 | tony | male | 73 | 2014-07-01 | teacher | NULL | 3500.00 | 401 | 1 |
| 2 | tom | male | 78 | 2015-03-02 | teacher | NULL | 1000000.31 | 401 | 1 |
| 3 | kevin | male | 81 | 2013-03-05 | teacher | NULL | 8300.00 | 401 | 1 |
+----+--------+--------+-----+------------+--------------------+--------------+------------+--------+-----------+
18 rows in set (0.00 sec)
3.统计各部门年龄在10岁以上的员工平均薪资，并且保留平均薪资大于1000的部门按照从大到小的顺序排序。
mysql> select post, avg(salary) as avg_salary from emp
-> where age > 10
-> group by post
-> having avg_salary > 1000
-> order by avg_salary desc;
+--------------------+---------------+
| post | avg_salary |
+--------------------+---------------+
| teacher | 151842.901429 |
| operation | 16800.026000 |
| 张江第一帅形象代言 | 7300.330000 |
| sale | 2600.294000 |
+--------------------+---------------+
4 rows in set (0.00 sec)
使用ORDER BY 关键字应注意以下几个方面：
1. ORDER BY 关键字后可以跟子查询
2. 当排序的字段中存在空值时，ORDER BY 会将该空值作为最小值来对待。
3. ORDER BY 指定多个字段进行排序时，MySQL 会按照字段的顺序从左到右依次进行排序。
查询关键字之limit分页
在mysql中limit关键字用来强制SELECT 语句返回指定的记录数。
分页即限制展示条数，通过实操来体验：
1. 限制展示五条数据：
mysql> select id,name,sex,salary from emp limit 5;
+----+-------+------+------------+
| id | name | sex | salary |
+----+-------+------+------------+
| 1 | jason | male | 7300.33 |
| 2 | tom | male | 1000000.31 |
| 3 | kevin | male | 8300.00 |
| 4 | tony | male | 3500.00 |
| 5 | owen | male | 2100.00 |
+----+-------+------+------------+
5 rows in set (0.00 sec)
2. 分页效果
mysql> select id,name,sex,salary from emp limit 5, 5;
+----+-------+--------+----------+
| id | name | sex | salary |
+----+-------+--------+----------+
| 6 | jack | female | 9000.00 |
| 7 | jenny | male | 30000.00 |
| 8 | sank | male | 10000.00 |
| 9 | 哈哈 | female | 3000.13 |
| 10 | 呵呵 | female | 2000.35 |
+----+-------+--------+----------+
5 rows in set (0.00 sec)
3. 查询工资最高的人的详细信息
mysql> select * from emp order by salary desc limit 1;
+----+------+------+-----+------------+---------+--------------+------------+--------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
+----+------+------+-----+------------+---------+--------------+------------+--------+-----------+
| 2 | tom | male | 78 | 2015-03-02 | teacher | NULL | 1000000.31 | 401 | 1 |
+----+------+------+-----+------------+---------+--------------+------------+--------+-----------+
1 row in set (0.00 sec)
当数据特别多的时候，经常使用limit来限制展示条数，节省资源，防止系统崩溃。
查询关键字之regexp正则
MySQL中正则表达式通常被用来检索或替换符合某个模式的文本内容，根据指定的匹配模式匹配文中符合要求的特殊字符串。
实际操作体验
# 查询名字以 j 为开头，以 n 或 y 结尾的员工的详细信息
mysql> select * from emp where name regexp '^j.*(n|y)$';
+----+-------+------+-----+------------+--------------------+--------------+----------+--------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
+----+-------+------+-----+------------+--------------------+--------------+----------+--------+-----------+
| 1 | jason | male | 18 | 2017-03-01 | 张江第一帅形象代言 | NULL | 7300.33 | 401 | 1 |
| 7 | jenny | male | 18 | 1900-03-01 | teacher | NULL | 30000.00 | 401 | 1 |
+----+-------+------+-----+------------+--------------------+--------------+----------+--------+-----------+
2 rows in set (0.00 sec)
我们目前所讲的是MySQL查询关键字中使用频率较高的一些，其实还有一些关键字目前无需讲解。并且SQL语句里面同样还支持流程控制语法。
多表查询
多表查询思路
多表查询思路有两种：
1. 子查询
相当于是我们生活中解决问题的方式(一步步解决)
将一条SQL语句的查询结果加括号当做另一条SQL语句的查询条件
# 比如以昨天的员工表与部门表为例 查询jason所在部门名称
子查询步骤
1. 先查询jason所在的部门编号
2. 根据部门编号去部门表中查询部门名称
2. 连表操作
先将多张表拼接到一起 形成一张大表 然后基于单表查询获取数据
连表操作
1. 先将员工表和部门表按照某个字段拼接到一起
2. 基于单表查询
演练数据
create table dep(
id int primary key auto_increment,
name varchar(32)
);
create table emp(
id int primary key auto_increment,
name varchar(32),
gender enum('male','female','others') default 'male',
age int,
dep_id int
);
insert into dep values(200,'技术'),(201,'人力资源'),(202,'销售'),(203,'运营'),(205,'安保');
insert into emp(name,age,dep_id) values('jason',18,200),('tony',28,201),('oscar',38,201),('jerry',29,202),('kevin',39,203),('jack',48,204);
实际演练
1. 使用子查询 获取jason所在的部门名称
1.1. 先获取jason的部门编号
select dep_id from emp where name='jason';
1.2. 将结果加括号作为查询条件
select name from dep where id=(select dep_id from emp where name='jason');
2. 使用连表操作 获取jason所在的部门名称
2.1. 笛卡尔积(了解知识)
select * from emp,dep; # 会讲所有的数据全部对应一遍
select * from emp,dep where emp.dep_id=dep.id; # 效率低下
2.2. 连表操作有四个关键字
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;
# 左右两表数据全部展示 没有对应项则用NULL填充
如果要想进行笛卡尔积的消除，往往会使用关联字段。由于多张表之间可能会有重名的字段，所以在进行重名字段访问的时候，前面需要加上表名称。采用“表名称.字段”的方式来进行访问。而一条SQL语句的查询结果，我们也可以看成是一张虚拟表。
可视化软件Navicat
什么是可视化
数据可视化软件可将文本和数字数据转换为可视化的图表，图形和表格。 通过将重要数据引入中央界面，它被用作创建应用程序/系统性能或操作仪表板的方法。
什么是Navicat
Navicat是一个可多重连接的数据库管理工具，它可以连接到MySQL、Oracle、PostgreSQL、SQLite、SQL Server和/或MariaDB数据库，让数据库管理更加方便。Navicat的功能可以满足专业开发人员的所有需求，对数据库服务器的新手来说学习起来也不难。有了极完备的图形用户界面（GUI），Navicat可以简便、安全地创建、组织、访问和共享信息。
Navicat提供三种操作平台：Microsoft Windows、Mac OS X 和Linux。它可以让用户连接到本机或远程服务器、提供一些实用的数据库工具如数据模型、数据传输、数据同步、结构同步、导入、导出、备份、还原、报表创建工具及计划以协助管理数据。
navicat的下载
Navicat 该软件是收费的，但是有很多破解版本，自我百度下载即可(正式版只能使用14天)。针对这种图形化软件，版本越新越好(不同版本图标颜色不一样，但是主题功能是一样的)。
navicat的使用
注意：navicat的使用是为了提高开发的效率，但是不能太过于依赖该软件，其实这些软件的底层还是执行的SQL语句来操作数据库的，只不过是将SQL语句封装到软件内。
使用Navicat第一步首先连接本地MySQL，连接MySQL，输入密码，在点击测试即可，不需要输入HOST，测试成功，会默认填写。然后查询MySQL内，库和表。然后就可以使用了，你可以查看你原来的库，也可以直接利用可视化创建库等操作。
可视化软件内，也可以选择SQL语句编辑，一些代码，可视化软件完成不了时，可以自己使用，可视化软件提供的SQL语句编辑。
多表查询练习
-- 1、查询所有的课程的名称以及对应的任课老师姓名
# 1.先明确需要几张表 course表 teacher表
# 2.大致查找一些表中的数据情况
# 3.既然是多表查询 那么查询思路 子查询 连表操作(复杂的SQL需要两者配合使用)
# 4.编写完成后 使用美化功能 将SQL语句规范化
-- SELECT
-- course.cname,
-- teacher.tname
-- FROM
-- course
-- INNER JOIN teacher ON course.teacher_id = teacher.tid;
-- 4、查询平均成绩大于八十分的同学的姓名和平均成绩
# 1.先明确需要用到几张表 student score
# 2.大致查看一下两张表里面的数据
# 3.先获取平均成绩大于80分的学生信息(按照student_id分组)
-- select score.student_id,avg(num) as avg_num from score group by score.student_id having avg_num>80;
# 4.结果需要从两个表里面的获取 student SQL语句执行之后的虚拟表
-- SELECT
-- student.sname,
-- t1.avg_num
-- FROM
-- student
-- INNER JOIN ( SELECT 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、查询没有报李平老师课的学生姓名
# 此题有两种思路 第一种是正向查询 第二种是反向查询(先查所有报了李平老师课程的学生id 之后取反即可)
# 1.先明确需要用到几张表 四张表
# 2.先查询李平老师的编号
-- select tid from teacher where tname='李平老师'
# 3.再查李平老师教授的课程编号
-- select cid from course where teacher_id=(select tid from teacher where tname='李平老师')
# 4.根据课程编号 去score表中筛选出所有选了课程的学生编号
-- select distinct student_id from score where course_id in (select cid from course where teacher_id=(select tid from teacher where tname='李平老师'));
# 5.根据学生编号去学生表中反向筛选出没有报李平老师课程的学生姓名
-- 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 = '李平老师' ) ) )
-- 8、查询没有同时选修物理课程和体育课程的学生姓名(两门都选了和一门都没选的 都不要 只要选了一门)
# 1.先明确需要用到几张表 三张
# 2.先获取物理课程和体育课程的编号
-- select cid from course where cname in ('物理','体育');
# 3.再去分数表中筛选出选了物理和体育的数据(包含了选了一门和两门 没有选的就已经被排除了)
-- select * from score where course_id in (select cid from course where cname in ('物理','体育'))
# 4.如何剔除选了两门的数据(按照学生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;
# 5.根据上述学生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.先明确需要几张表 三张表
# 2.先去score表中筛选出所有不及格的数据
-- select * from score where num < 60;
# 3.如何筛选每个学生挂科的门数(按照学生id分组 对学科计数即可)
-- select student_id from score where num < 60 group by student_id
-- HAVING count(course_id) >= 2;
# 4.由于最终的结果需要取自两张表 所以应该拼接
-- select student.sname,class.caption from class inner join student on class.cid=student.class_id;
# 5.使用步骤3获取到的学生编号 对步骤4的表结果筛选数据
SELECT
student.sname,
class.caption
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( course_id ) >= 2 );
浙公网安备 33010602011771号