SQL之数据查询语言.

1. 介绍

select

show

2.select 语句的应用

2.1 select单独使用的情况***

mysql> select @@basedir;#mysql安装目录

mysql> select @@port;#mysql端口号

mysql> select @@innodb_flush_log_at_trx_commit;#日志刷新策略

mysql> show variables like 'innodb%';#模糊查看innodb开头的配置

mysql> select database();#查看当前库名

mysql> select now();#查看当前系统时间

mysql> select @@server_id;#查看本实例id号，群集中不能重复(群集中唯一标识一台MySQL服务器）

2.2 select 通用语法(单表) *****

select  显示的列名（多列逗号分开）    *号代表所有的列

from  表名（多个表逗号分开）   

where 过滤条件的列

group by  分组的列

having   分组后的过滤聚合函数（聚合函数：sum求和，avg平均数，max最大值，min最小值，count计数)

order by 排序的列   （升序：ASC，降序：DESC）

limit  显示前几行

2.3 学习环境的说明

world数据库

city   城市表

country  国家表

countrylanguage   国家的语言

city表结构：

mysql>desc city;

+-------------+----------+------+-----+---------+----------------+

| Field       | Type     | Null | Key | Default | Extra          |

+-------------+----------+------+-----+---------+----------------+

| ID          | int(11)  | NO   | PRI | NULL    | auto_increment |

| Name        | char(35) | NO   |     |         |                |

| CountryCode | char(3)  | NO   | MUL |         |                |

| District    | char(20) | NO   |     |         |                |

| Population  | int(11)  | NO   |     | 0       |                |

+-------------+----------+------+-----+---------+----------------+

5 rows in set (0.00 sec)

ID  :  城市序号(1-...)

name  :  城市名字

countrycode :   国家代码,例如:CHN,USA

district    :   区域: 中国 省  美国 洲

population  :   人口数

如何熟悉数据库业务?

快速和研发人员打好关系

找到领导要ER图

DESC

show create table

select * from city limit 5;

mysql>select * from city limit 5;

+----+----------------+-------------+---------------+------------+

| ID | Name           | CountryCode | District      | Population |

+----+----------------+-------------+---------------+------------+

|  1 | Kabul          | AFG         | Kabol         |    1780000 |

|  2 | Qandahar       | AFG         | Qandahar      |     237500 |

|  3 | Herat          | AFG         | Herat         |     186800 |

|  4 | Mazar-e-Sharif | AFG         | Balkh         |     127800 |

|  5 | Amsterdam      | NLD         | Noord-Holland |     731200 |

2.4 SELECT 配合 FROM 子句使用

-- select  列,列,列  from  表

--- 例子:

(1) 查询表中所有的信息(生产中几乎是没有这种需求的)

USE world ;

SELECT  id,NAME ,countrycode ,district,population   FROM  city;

或者:

SELECT  *   FROM city;

(2) 查询表中 name和population的值

SELECT  NAME ,population   FROM  city;

2.5  SELECT 配合  WHERE 子句使用

-- select  列,列,列  from  表  where 过滤条件

-- where等值条件查询  *****

例子:

查询中国所有的城市名和人口数

select name,population from city where countrycode='CHN';

-- where 配合比较判断查询(> < >= <=)  *****

例子:

世界上小于100人的城市名和人口数

select name,population from city where population<100;

-- where 配合 逻辑连接符(and  or)

例子:

(1) 查询中国人口数量大于800w的城市名和人口

select name,population from city where countrycode='CHN' and population>8000000;

(2) 查询中国或美国的城市名和人口数

select name,population from city where countrycode='CHN' or countrycode='USA';

(3) 查询人口数量在500w到600w之间的城市名和人口数

select name,population from city where population>=5000000 and population<=6000000;

mysql>select name,population from city where population>=5000000 and population<=6000000;

+----------------+------------+

| name           | population |

+----------------+------------+

| Rio de Janeiro |    5598953 |

| Tianjin        |    5286800 |

| Kinshasa       |    5064000 |

| Lahore         |    5063499 |

+----------------+------------+

4 rows in set (0.00 sec)

或者:

select name,population from city where population between 5000000 and 6000000;

-- where 配合 like 子句 模糊查询 *****

例子:

查询一下contrycode中带有CH开头,城市信息

select name,countrycode from city where countrycode like 'CH%';

注意:不要出现类似于 %CH%,前后都有百分号的语句,因为不走索引,性能极差

如果业务中有大量需求,我们用"Elasticsearch"来替代

-- where 配合 in 语句

例子:

查询中国或美国的城市信息.

select name,population from city where countrycode in ('CHN','USA');

2.5.2 GROUP BY

将某列中有共同条件的数据行,分成一组,然后在进行聚合函数(sum,avg,count,max,min)操作.

例子:

(1) 统计每个国家,城市的个数

select countrycode,count(name) from city group by countrycode;

group by 按城市分组后，mysql就会把相同国家的城市进行计数

(2) 统计每个国家的总人口数.

select countrycode,sum(population) from city group by countrycode;

分组之后加条件，使用having，不能使用where

mysql>select countrycode,sum(population) from city group by countrycode having cnountrycode='CHN';

+-------------+-----------------+

| countrycode | sum(population) |

+-------------+-----------------+

| CHN         |       175953614 |

+-------------+-----------------+

(3) 统计每个 国家 省 的个数(distinct 去除重复)

select countrycode,count(distinct district) from city group by countrycode;

mysql>select countrycode,count(distinct district) from city group by countrycode

 having countrycode='CHN';

+-------------+--------------------------+

| countrycode | count(distinct district) |

+-------------+--------------------------+

| CHN         |                       31 |

+-------------+--------------------------+

1 row in set (0.00 sec)

(4) 统计中国 每个省的总人口数

select district as 省,sum(population) as  总人口 from city where countrycode='CHN' group by district;

 as：别名

mysql>select district as 省,sum(population) as 总人口

    -> from city

    -> where countrycode='CHN' group by district;

+----------------+-----------+

| 省             | 总人口    |

+----------------+-----------+

| Anhui          |   5141136 |

| Chongqing      |   6351600 |

| Fujian         |   3575650 |

| Gansu          |   2462631 |

| Guangdong      |   9510263 |

| Guangxi        |   2925142 |

mysql>select sum(population) as 全世界总人口

    -> from city;

+--------------------+

| 全世界总人口       |

+--------------------+

|         1429559884 |

+--------------------+

1 row in set (0.01 sec)

(5) 统计中国 每个省城市的个数

select district as 省,count(name) as 城市个数 from city where countrycode='CHN' group by district;

mysql>select district as 省,count(name) as 城市数量

    -> from city

    -> where countrycode='CHN'

    -> group by district;

+----------------+--------------+

| 省             | 城市数量     |

+----------------+--------------+

| Anhui          |           16 |

| Chongqing      |            1 |

| Fujian         |           12 |

| Gansu          |            7 |

| Guangdong      |           20 |

(6) 统计中国 每个省城市的名字列表GROUP_CONCAT() #列转行

select district,group_concat(name) from city where countrycode='CHN' group by district;

(7) 小扩展(拼接，自定义分隔符)

anhui : hefei,huaian ....

SELECT CONCAT(district,":" ,GROUP_CONCAT(NAME))     FROM  city

WHERE countrycode='CHN'

GROUP BY district ;

2.7 SELECT 配合 ORDER BY 子句

例子:

统计所有国家的总人口数量,

将总人口数大于5000w的过滤出来,

并且按照从大到小顺序排列

select countrycode,sum(population) from city group by countrycode having sum(population)>50000000

order by sum(population) desc;

注：默认为升序，asc ; 降序为desc

2.8 SELECT 配合 LIMIT  子句

例子:

统计所有国家的总人口数量,

将总人口数大于5000w的过滤出来,

并且按照从大到小顺序排列,只显示前三名

mysql>select countrycode,sum(population) from city group by countrycode having sum(population)>50000000 order by sum(population) desc limit 3;

+-------------+-----------------+

| countrycode | sum(population) |

+-------------+-----------------+

| CHN         |       175953614 |

| IND         |       123298526 |

| BRA         |        85876862 |

+-------------+-----------------+

3 rows in set (0.01 sec)

LIMIT M,N     :跳过M行,显示一共N行

LIMIT Y OFFSET X: 跳过X行,显示一共Y行

2.9 练习题:

(1) 统计中国每个省的总人口数，只打印总人口数小于100w的

select district,sum(population) from city where countrycode='CHN'

group by district having sum(population)<1000000;

(2) 查看中国所有的城市，并按人口数进行排序(从大到小)

select name,population from city where countrycode='CHN' order by population desc;

(3) 统计中国各个省的总人口数量，按照总人口从大到小排序

select district,sum(population) from city where countrycode='CHN'

group by district order by sum(population) desc;

(4) 统计中国,每个省的总人口,找出总人口大于500w的,

并按总人口从大到小排序,只显示前三名

select district,sum(population) from city where countrycode='CHN'

group by district having sum(population)>5000000

order by sum(population) desc

limit 3 ;

2.10 把中国每个省城市个数大于10的列出前3名

select district,count(name) from city where countrycode='CHN'

group by district having count(name)>10 order by count(name) desc limit 3;

2.11 union 和 union all

作用: 多个结果集合并查询的功能

需求: 查询中国或者美国的城市信息

SELECT * FROM city WHERE countrycode='CHN' OR countrycode='USA';

改写为:

SELECT * FROM city WHERE countrycode='CHN'

UNION ALL

SELECT * FROM city WHERE countrycode='USA';

面试题: union 和 union all 的区别 ?

union all   ：不做去重复

union ：会做去重操作

3.2 多表连接例子

-- 1. 统计zhang3,学习了几门课

select student.sname,count(sc.score) from student inner join sc

on student.sno=sc.sno

where student.sname='zhang3';

-- 2. 查询zhang3,学习的课程名称有哪些?

select student.sname,course.cname from student  join sc

on student.sno=sc.sno

join course on sc.cno=course.cno

where student.sname='zhang3';

-- 3. 查询laoli老师教的学生名和个数.

select teacher.tname,group_concat(student.sname),count(student.sname)

from teacher join course  on teacher.tno=course.tno

join sc on course.cno=sc.cno

join student on sc.sno=student.sno

where teacher.tname='laoli';

-- 4. 查询laoli所教课程的平均分数

select course.cname,avg(sc.score)

from teacher join course  on teacher.tno=course.tno

join sc on course.cno=sc.cno

where teacher.tname='laoli'

group by course.cname;

-- 5. 每位老师所教课程的平均分,并按平均分降序排列

select teacher.tname,avg(sc.score)

from teacher join course  on teacher.tno=course.tno

join sc on course.cno=sc.cno

group by teacher.tname

order by avg(sc.score) desc;

-- 6. 查询laoli所教的不及格的学生姓名

select student.sname,sc.score from

teacher join course on teacher.tno=course.tno

join sc on course.cno=sc.cno

join student on sc.sno=student.sno

where teacher.tname='laoli' and sc.score<60;

-- 7. 查询所有老师所教学生不及格的信息(扩展)

第一种方法：

select teacher.tname,student.sname,sc.score from

teacher join course on teacher.tno=course.tno

join sc on course.cno=sc.cno

join student on sc.sno=student.sno

where sc.score<60;

第二种方法：

SELECT teacher.tname,GROUP_CONCAT(CONCAT(student.sname,":",sc.score))

FROM teacher

JOIN course

ON teacher.tno=course.tno

JOIN sc

ON course.cno=sc.cno

JOIN student

ON sc.sno=student.sno

WHERE sc.score<60

GROUP BY teacher.tname;

-- 8.别名应用

表别名 :

SELECT t.tname,GROUP_CONCAT(CONCAT(st.sname,":",sc.score))

FROM teacher as t

JOIN course as c

ON t.tno=c.tno

JOIN sc

ON c.cno=sc.cno

JOIN student as st

ON sc.sno=st.sno

WHERE sc.score<60

GROUP BY t.tname;

表别名是全局调用的.

列别名:

SELECT t.tname as 讲师,GROUP_CONCAT(CONCAT(st.sname,":",sc.score)) as 学生成绩 FROM teacher as t JOIN course as c ON t.tno=c.tno JOIN sc ON c.cno=sc.cno JOIN student as st ON sc.sno=st.sno WHERE sc.score<60 GROUP BY t.tname;

列别名可以被  having 和 order by 调用

9. 语句嵌套(自连接)

create database hehe default charset utf8mb4 collate utf8mb4_bin;

use hehe

create table t1 (id int,name varchar(20));

insert into t1 values (1,'zhangsan'),(2,'lisi'),(3,'zhangsan'),(4,'wangwu'),(5,"zhangsan"),(6,'lisi');

要求：查找重复两次及以上的人员，按id号从大到小排列。

select t1.name,id from t1

join (select name from t1 group by name having count(name)>1) as  t2

on t1.name=t2.name order by id desc;

创建员工表，名称为emp

mysql>create table emp (id int,name char(10),age int,sex char(2),jg varchar(64),salary int,ssid int);

Query OK, 0 rows affected (0.00 sec)

insert into emp

-> values(101,'张津瑞',19,'男','山西',20000,105);

查询员工的姓名和对应的上司名称

select yg.员工姓名,ss.员工姓名

from emp as yg,emp as ss

where yg.员工ID=ss.员工ID

mysql>select yg.name as 员工,ss.name as 上司 from emp as yg,emp as ss where yg.ssid=ss.id;