MySQL（三）

MySQL查询

一、基本查询数据

1、全表查询、查询部分字段、查询⼀个表里面所有的数据条数（2种）

2、条件过滤：并且(AND)、或者(OR)、包含(IN)、范围检查(BETWEEN AND)

3、条件过滤：否定结果(NOT)、匹配任意字符(%)、以什么开头(^)、以什么结束($) 

 

二、对结果信息排序

1、限制：limit

当遇到数据量很大的数据时，千万不能全量查询，这样会把数据库的服务器搞死，此时需要限制表的查询条数：limit

以testdev数据库里的user表为例：

查询user表中的所有内容，将limit设置为1：

select * from user limit 1;

2、排序： select * from user order by [排序的字段名];

默认是升序：asc

降序：desc

a、对于数字型的排序是按照数字的大小进行排序：

将user表中的age进行升序排序，两种方式：

第一种方式：

select * from user order by age;

第二种方式：

select * from user order by age asc;

b、对于字符串型的排序是按照ASCLL码进行排序：

 三、聚合函数

以testdev数据库里的user表为例：

1、查询总数(count)：

查询user表中所有的数据条数：

select count(1) from user;

2、总和(sum)：

查询user表中的所有age之和：

select sum(age) from user;

3、平均(averge)：

查询user表中的所有age的平均值：

select avg(age) from user;

4、最大(max)：

查询user表中所有age中的最大年龄：

select max(age) from user;

5、最小(min)：

查询user表中所有age中的最小年龄：

select min(age) from user;

6、去重(distinct)

查询user表中无重复的name:

select distinct name from user;

7、过滤(having)

以testdev数据库里的person表为例：

通过having来过滤group by字句的结果信息。

7-1、查询person表中，不同性别的的人数：

select sex,count(sex) from person group by sex;

7-2、查询person表中，不同性别的人数并且过滤出总人数>2，请使用两种表示方法：

第一种：

select sex,count(sex) from person group by sex having count(sex)>2;

第二种：

select sex,count(sex) 总数 from person group by sex having 总数>2;

四、表关联查询

在工作中，很多的时候，需要获取到具体的数据需要关联好几个表，才能够查询以及获取到想要的数据， 这时就需要将几个表关联起来，进行查询。

1、内连接

inner join，又叫内连接的部分，主要是获取两个表中字段匹配关系的表，查询关联字段共同拥有的数据。

以goods表、shop表、city表为例:

goods表:

skuid:商品的唯一标识

shop表：

city表：

a、在goods表和shop表中筛选出相同的数据内容：

select * from goods inner join shop on goods.id=shop.good_id;

b、在goods表和shop表中筛选出相同数据内容+再将goods表中的id=1筛选出来：

select * from goods inner join shop on goods.id=shop.good_id where goods.id=1;

c、筛选出goods表中的name和shop表中的name和level，用两种方式表示出来：

第一种方式：

select goods.name,shop.name,shop.level from goods inner join shop on goods.id=shop.good_id;

第二种方式：

select g.name,s.name,s.level from goods g inner join shop s on g.id=s.good_id;

d、在goods表、shop表和city表中筛选出相同的数据内容：

select * from goods inner join shop on goods.id=shop.good_id inner join city on shop.id=city.shop_id;

e、筛选出goods表中的address和city表中的name，用两种方式表示出来：

第一种方式：

select goods.address,city.name from goods inner join shop on goods.id=shop.good_id inner join city on shop.id=city.shop_id;

第二种方式：

select g.address,c.name from goods g inner join shop s on g.id=s.good_id inner join city c on s.id=c.shop_id;

2、外连接：左连接+右连接

2-1、左连接

定义：获取左表所有记录，获取左边数据表所有符合要求的字段数据信息。

左连接的逻辑：

a、先完成内连接的逻辑（即table1和table2公共的部分）；

b、再查询出左表除公共部分以外所有的数据。

以testdev数据库的goods表和shop表为例：

那么，goods表左连接shop表的操作命令为：

select * from goods g left join shop s on g.id=s.good_id;

2-2、右连接

定义：获取右表所有记录的信息，获取右边数据表所有的数据信息。

右连接的逻辑：和左连接的逻辑类似。

以testdev数据库的goods表和shop表为例：

那么，goods表右连接shop表的操作命令：

select * from goods g right join shop s on g.id=s.good_id;

3、子查询

定义：当一个查询是另一个查询的条件时，称之为子查询。

子查询的逻辑：里面的SQL执行的结果是，是外面SQL执行的结果是输入。

以testdev数据库的goods表和shop表为例：

那么，查询goods表skuid="g001"在shop表的所有商品的name和level的操作命令：

mysql> select s.name,s.level from shop s where good_id in
    -> (select id from goods where skuid="g001");

🧨相当于:select s.name,s.level from shop s where good_id in (1);

🎨能子查询的必然可以内查询：

select s.name,s.level from goods g inner join shop s on g.id=s.good_id where g.skuid="g001";

五、MySQL索引

在MySQL中，创建MySQL的索引主要是为了提高MySQL查询的效率，但是添加太多的索引也是会降低更新表的速度。

以testdev数据库的userIndex表为例：

1、创建新表时添加索引：

2、给某一表的字段更新索引：

以testdev数据库的user表为例：

3、给本身已经创建好的表增加索引：

和给某一表的字段更新索引所使用的命令是一样的。

4、给某一个表删除索引：

六、实战练习

1、聚合函数实战：

a、在employees表中查询出不同gender的总人数：

select gender,count(gender) from employees group by gender;

b、在salaries表中，将salary的平均值从大到小排序，查询出salary的平均值大于10000的不同emp_no编号的员工并给其限制到5，用两种方式表示出来：

第一种方式：

select emp_no,avg(salary) from salaries group by emp_no having avg(salary)>10000 order by avg(salary) desc limit 5;

第二种方式：

select emp_no,avg(salary) avg from salaries group by emp_no having avg>=100000 order by avg desc limit 5;

c、在titles表中去重title字段：

 select distinct title from titles;

d、在salaries表中查看有关salary的sum、avg、max和min，用两种方式表示出来：

第一种方式：

select sum(salary),avg(salary),max(salary),min(salary) from salaries;

第二种方式：

select sum(salary) 总和 ,avg(salary) 平均薪资,max(salary) 最大薪资,min(salary) 最小薪资 from salaries;

e、将salaries表中salary的最大值和salary的最小值进行相加减：

mysql> select(
    -> select max(salary) from salaries)
    -> -
    -> select min(salary) from salaries)
    -> );

2、条件过滤实战：

a、过滤出salaries表中的salary在30000到40000之间的salary不包含40000，并将salary进行降序排序，限制到5：

select * from salaries where salary between 30000 and 40000 and salary!=40000 order by salary desc limit 5;

3、表的内连接实战：

a、筛选出employees表中的first_name、last_name和salaries表中salary，并通过salaries表中salary进行降序排序，限制到5：

select e.first_name,e.last_name,s.salary from employees e inner join salaries s on e.emp_no=s.emp_no order by salary desc limit 5