mysql数据库（四）单表查询

单表查询

一、单表查询

记录的查询语法如下：
SELECT DISTINCT(去重) 字段1,字段2… FROM 表名
WHERE 筛选条件
GROUP BY 分组
HAVING 分组筛选
ORDER BY 排序
LIMIT 限制显示条数

这些关键词的执行顺序是：from(选出记录)、where、group by、having、select(根据指定字段保留记录内容)、distinct、order by、limit

1.1 简单查询

#常用的简单查询语句
select * from t1;
select name,age from t1;

#对查询的出版社去重
select distinct publish from t1;

#带运算和as的查询
#as的作用是将字段重起一个名字
select name,score*100 as Score from t2;

#带函数的查询
#concat函数用于连接字符串
select concat("名字:",name) as student_name,concat("分数:",score) as student_score from t3;
#concat_ws函数也用于字符串拼接，不过第一个参数为分隔符
select concat_ws(":",name,score) as student from t3;

#case的分支语句
select ( 
		case 
           when score>85 then '优秀' 
           when score>60 then '合格' 
           else '不合格' 
           end
          ) as new_score 
       from t3;
          

1.2where

where用于对记录进行初步筛选。

#单条件查询
select score from t1 where name='张三';
        
#多条件查询
 select score_Math,score_English from t1 where name='张三';

#between and表示区间[a,b]
select name from t1 where score between 60 and 100;

#not表示取反
select name from t1 where score not between 60 and 100;
    
#is null表示判断字段是否为空，需要注意的是''不是null
select name form t1 where score is null;
select name form t1 where score is not null;

#in表示处于某个范围
#查询成绩为100、90、80分的学生姓名
select name from t1 where score=100 or score=90 or score=80;
select name from t1 where score in [100,90,80];

#like表示模糊查询，_表示任意字符，%表示任何数量的字符
#匹配成绩在80至89的学生姓名
select name from t1 where score like '8_';
#匹配姓张的学生成绩
select score from t1 where name like '张%';

#regexp表示正则表达式，正则表达式的内容不再介绍了，不了解的可以参考下面的链接
#匹配姓张的学生成绩
select score from t1 where name regexp '张.+';

正则表达式

1.3group by

group by用于对数据进行分组，分组的目的是将数据中某一字段的相同内容进行归类，并对归类后的数据进行一系列的操作。例如要计算每个部门的平均薪资就需要先对部门进行分组，然后对薪资字段使用avg函数聚合。

需要注意的是mysql默认的是ONLY_FULL_GROUP_BY模式的分组，简单来说就是分组后查询出来的字段中只能有一个明确的值，如果有多个就会报错。例如对部门进行分组，查询部门和员工两个字段，由于分组后的一个部门含有多个员工，这样的分组查询结果会报错。

+----+--------+--------+------------+
| id | name   | salary | department |
+----+--------+--------+------------+
|  1 | 王五   |   9000 | IT         |
|  2 | 张三   |  10000 | IT         |
|  3 | 李四   |   6000 | 销售       |
|  4 | 赵六   |   6500 | 销售       |
+----+--------+--------+------------+

#对部门进行分组，查询部门和员工两个字段
select department,name from t1 group by department;

which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

#只对部门进行分组
select department from t1 group by department;
+------------+
| department |
+------------+
| IT         |
| 销售       |
+------------+

#使用group_concat对部门进行分组并查询部门和员工信息
select department,group_concat(name) from t1 group by department;
+------------+--------------------+
| department | group_concat(name) |
+------------+--------------------+
| IT         | 王五,张三           |
| 销售       | 李四,赵六           |
+------------+--------------------+

#使用聚合函数max、min、avg、count、sum
#求每个部门的平均薪资
select department,avg(salary) from t1 group by department;
+------------+-------------+
| department | avg(salary) |
+------------+-------------+
| IT         |   9500.0000 |
| 销售       |   6250.0000  |
+------------+-------------+

#求每个部门的员工数
select department,count(name) from t1 group by department;
+------------+-------------+
| department | count(name) |
+------------+-------------+
| IT         |           2 |
| 销售       |           2  |
+------------+-------------+

#补充：聚合函数也可以不跟group by，直接使用
#求表中员工的薪资总和
mysql> select sum(salary) from t1;
+-------------+
| sum(salary) |
+-------------+
|       31500 |
+-------------+

1.4having

having用于对分组结果进行筛选。

+----+--------+--------+------------+
| id | name   | salary | department |
+----+--------+--------+------------+
|  1 | 王五   |   9000 | IT         |
|  2 | 张三   |  10000 | IT         |
|  3 | 李四   |   6000 | 销售       |
|  4 | 赵六   |   6500 | 销售       |
+----+--------+--------+------------+

#求出平均薪资大于9000的部门及其薪资
select department,avg(salary) from t1 group by department having avg(salary)>9000;
+------------+-------------+
| department | avg(salary) |
+------------+-------------+
| IT         |   9500.0000 |
+------------+-------------+

1.5order by

order by用于排序，其中asc表示升序，desc表示降序。(默认为升序)

+----+-------+-------+
| id | num   | score |
+----+-------+-------+
|  1 | 10101 |    90 |
|  2 | 10102 |    88 |
|  3 | 10103 |    90 |
+----+-------+-------+

#对表中数据按成绩降序排列，如果成绩一样则按学号升序排列
select * from t1 order by score desc,num asc;
+----+-------+-------+
| id | num   | score |
+----+-------+-------+
|  1 | 10101 |    90 |
|  3 | 10103 |    90 |
|  2 | 10102 |    88 |
+----+-------+-------+

1.6limit

limit用于限制显示的记录数。

+----+-------+-------+
| id | num   | score |
+----+-------+-------+
|  1 | 10101 |    90 |
|  2 | 10102 |    88 |
|  3 | 10103 |    90 |
|  4 | 10104 |    70 |
|  5 | 10105 |   100 |
|  6 | 10106 |    79 |
+----+-------+-------+

# 查询学号为10101的同学成绩
select * from t1 limit 1;
+----+-------+-------+
| id | num   | score |
+----+-------+-------+
|  1 | 10101 |    90 |
+----+-------+-------+

# 查询成绩的第二第三名
#limit 1,2表示从第一条记录开始向后显示两条记录(记录从0开始计数)
select * from t1 order by score desc limit 1,2;
+----+-------+-------+
| id | num   | score |
+----+-------+-------+
|  1 | 10101 |    90 |
|  3 | 10103 |    90 |
+----+-------+-------+