MySQL数据库04

• 表查询关键字之having过滤

having与where的功能是一模一样的，都是对数据进行筛选。

having条件是实现聚合结果层面上的筛选>>>:拿聚合结果完成判断。

where用在分组之前的筛选；havng用在分组之后的筛选。

• 统计每个部门年龄在30岁以上的员工的平均薪资并且保留平均薪资大于10000的部门

1. 先获取每个部门年龄在30岁以上的员工的平均薪资
2. 在过滤出平均薪资大于10000的数据

select post,avg(salary) from emp where age>30 group by post;

select post,avg(salary) from emp 
  where age>30 
  group by post
  having avg(salary) > 10000;

ps：针对聚合函数，如果还需要在其他地方作为条件使用，可以先起别名。

select post,avg(salary) as avg_salary from emp 
      where age>30 
    group by post
    having avg_salary > 10000
    ;

• 表查询关键字之distinct去重

去重的前提是数据必须是一模一样的才可以，如果数据有主键存在，则肯定无法去重。

select distinct age from emp;

• 表查询关键字之order by排序

升序：asc    降序：desc

• 按照薪资高低排序

select * from emp order by salary; >>>: 默认情况下是升序(从小到大)

select * from emp order by salary asc;  >>>:关键字asc,可以省略 

select * from emp order by salary desc;  >>>:降序(从大到小)

• 先按照年龄升序排序，如果年龄相同，则再按照薪资降序排序

select * from emp order by age asc,salary desc;

• 统计各部门年龄在10岁以上的员工平均工资，并且保留平均工资大于1000的部门，并按照从大到小的顺序排序

select post,avg(salary) as avg_salary from emp 
      where age > 10 
        group by post
        having avg_salary > 1000
        order by avg_salary desc;

• 表查询关键字之limit分页

分页即限制展示条数，可以通过限制展示条数来达到一个类似于筛选的效果。

限制最终结果的数据行数。limit只与数字结合使用。

limit 1：只能显示一行数据。

limit 6，5：从第6+1行开始显示5条数据，索引从0开始。

• 限制只展示五条数据

select * from emp limit 5;

• 分页效果展示

select * from emp limit 5,5;

• 查询工资最高的人的详细信息

select * from emp order by salary desc limit 1;

ps：当数据特别多的时候，经常使用limit来限制展示条数，节省资源，防止系统崩溃。

• 表查询关键字之regexp正则筛选

like完成模糊匹配，但是功能有限，可以模糊个数，但是不能模糊类型，正则可以完成类型及个数的模糊匹配。

语法：字段   regexp  '正则表达式'。

MySQL当中的正则方法符合正则的基本使用规则。但是只能支持部分正则语法。

select * from emp where name regexp '^j.*(n|y)$';

多表查询思路

多表查询的思路总共就两种：子查询和连表操作。

• 子查询

子查询：将一条SQL语句的查询结果加括号当做另外一条SQL语句的查询条件。

语法：一条select语句用（）包裹得到的结果作为另一个select语句的条件。

eg：以员工表和部门表为例，查询jason所在的部门名称。

子查询的步骤：

1. 先查jason所在的部门编号
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);
先获取jason的部门编号
select dep_id from emp where name='jason';
将结果加括号作为查询条件
select name from dep where id=(select dep_id from emp where name='jason');

 

• 连表操作

先将多张表拼接到一起，形成一张大表，然后基于单表查询获取数据。

eg：以员工表和部门表为例，查询jason所在的部门名称。

连表查询操作步骤：

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);
笛卡尔积：
select * from emp,dep;  会将所有的数据全部对应一遍，效率太低
select * from emp,dep where emp.dep_id=dep.id;  #

• 多表连接

在连接语法join前面就是左表，之后就是右表。

采用的是left关键词就是左连接，right关键词就是右连接，inner关键词就是内连接。

• 内连接

只连接两张表中有对应关系的数据。

select * from emp inner join dep on emp.dep_id=dep.id;

inner join on 
内连接：
    结果为两张表有对应关系的数据（emp有dep没有，emp没有dep有的记录均不会被虚拟表展示）
    语法：左表 inner join 右表 on 两表有关联的字段的条件，on就是产生对于关系的（连接的依据）
eg：select * from emp inner join dep on emp.dep_id = dep.id;
+----+----------+--------+--------+----+-----------+--------+
| id | name     | salary | dep_id | id | name      | work   |
+----+----------+--------+--------+----+-----------+--------+
|  1 | egon     |      3 |      2 |  2 | 教学部    | 授课   |
|  2 | yanghuhu |      2 |      2 |  2 | 教学部    | 授课   |
|  3 | sanjiang |     10 |      1 |  1 | 市场部    | 销售   |
|  4 | owen     |  88888 |      2 |  2 | 教学部    | 授课   |
|  5 | liujie   |      8 |      1 |  1 | 市场部    | 销售   |
+----+----------+--------+--------+----+-----------+--------+

• 左连接

以左表为基准，展示所有的数据，没有对应项则用NULL填充。

select * from emp left join dep on emp.dep_id=dep.id;

left join on
左连接：在内连接的基础上还保留左表特有的记录
语法：左表 left join 右表 on 两表有关联的字段条件
eg：select emp.name '员工', dep.name '部门', dep.work '职责' from emp left join dep on emp.dep_id = dep.id;
+----------+-----------+--------+
| 员工     | 部门      | 职责   |
+----------+-----------+--------+
| sanjiang | 市场部    | 销售   |
| liujie   | 市场部    | 销售   |
| egon     | 教学部    | 授课   |
| yanghuhu | 教学部    | 授课   |
| owen     | 教学部    | 授课   |
| yingjie  | NULL      | NULL   |
+----------+-----------+--------+

• 右连接

以右表为基准，展示所有的数据，没有对应项则用NULL填充。

select * from emp right join dep on emp.dep_id=dep.id;

right join on
右连接：在内连接的基础上还保留右表特有的记录
语法：左表 right join 右表 on 两表有关联的字段的条件
eg：select * from emp right join dep on emp.dep_id = dep.id;
+------+----------+--------+--------+----+-----------+--------+
| id | name | salary | dep_id | id | name | work |
+------+----------+--------+--------+----+-----------+--------+
| 1 | egon | 3 | 2 | 2 | 教学部 | 授课 |
| 2 | yanghuhu | 2 | 2 | 2 | 教学部 | 授课 |
| 3 | sanjiang | 10 | 1 | 1 | 市场部 | 销售 |
| 4 | owen | 88888 | 2 | 2 | 教学部 | 授课 |
| 5 | liujie | 8 | 1 | 1 | 市场部 | 销售 |
| NULL | NULL | NULL | NULL | 3 | 管理部 | 开车 |
+------+----------+--------+--------+----+-----------+--------+

• 全连接

左右两表数据全部展示，没有对应项则用NULL填充。

select * from emp left join dep on emp.dep_id=dep.id;

select * from emp right join dep on emp.dep_id=dep.id;

select dep.name from emp
inner join dep on emp.dep_id=dep.id
where emp.name='jason'

全连接：在内连接的基础上分别保留着左表及右表特有的记录
语法：mysql没有full join on 语法，但是可以通过去重达到效果
eg：
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;

+------+----------+--------+--------+------+-----------+--------+
| id   | name     | salary | dep_id | id   | name      | work   |
+------+----------+--------+--------+------+-----------+--------+
|    3 | sanjiang |     10 |      1 |    1 | 市场部    | 销售   |
|    5 | liujie   |      8 |      1 |    1 | 市场部    | 销售   |
|    1 | egon     |      3 |      2 |    2 | 教学部    | 授课   |
|    2 | yanghuhu |      2 |      2 |    2 | 教学部    | 授课   |
|    4 | owen     |  88888 |      2 |    2 | 教学部    | 授课   |
|    6 | yingjie  |    1.2 |      0 | NULL | NULL      | NULL   |
| NULL | NULL     |   NULL |   NULL |    3 | 管理部    | 开车   |
+------+----------+--------+--------+------+-----------+--------+

Navicat使用方法

Navicat可以充当很多数据库软件的客户端，提供了图形化界面能够让我们更加快速的操作数据库。【其底层封装了数据库的SQL和NoSQL语句】

1. 安装Nacicat>>>:http://www.navicat.com.cn/
2. 连接数据库，并建库>>>:Navicat的默认端口号是3306。
3. 创建表、设置字段、插入数据 
4. 新建查询

SQL语句注释语法(快捷键与pycharm中的一致 （ctrl+?)！！！