MySQL DQL一般查询操作

一、查询语句 ---select

1.创建学生成绩表t_exam

-- 1.创建表    
create table t_exam(
        id int primary key auto_increment,
        name varchar(20) not null,
        chinese double,
        math double,
        english double
    );
mysql> desc t_exam;
+---------+-------------+------+-----+---------+----------------+
| Field   | Type        | Null | Key | Default | Extra          |
+---------+-------------+------+-----+---------+----------------+
| id      | int(11)     | NO   | PRI | NULL    | auto_increment |
| name    | varchar(20) | NO   |     | NULL    |                |
| chinese | double      | YES  |     | NULL    |                |
| math    | double      | YES  |     | NULL    |                |
| english | double      | YES  |     | NULL    |                |
+---------+-------------+------+-----+---------+----------------+
5 rows in set (0.08 sec)

-- 2.插入数据
    insert into t_exam values(null,'王晶',85,76,70);
    insert into t_exam values(null,'周星星',70,75,70);
    insert into t_exam values(null,'沈腾',90,65,95);
    insert into t_exam values(null,'杨威',97,50,50);
    insert into t_exam values(null,'李连杰',90,89,80);
    insert into t_exam values(null,'乔布斯',90,67,65);

mysql> select * from t_exam;
+----+--------+---------+------+---------+
| id | name   | chinese | math | english |
+----+--------+---------+------+---------+
|  1 | 王晶   |      85 |   76 |      70 |
|  2 | 周星星 |      70 |   75 |      70 |
|  3 | 沈腾   |      90 |   65 |      95 |
|  4 | 杨威   |      97 |   50 |      50 |
|  5 | 李连杰 |      90 |   89 |      80 |
|  6 | 乔布斯 |      90 |   67 |      65 |
+----+--------+---------+------+---------+
6 rows in set (0.01 sec)

2.select查询

--  1、查询表中所有学生的姓名和对应的英语成绩。
       select name,english from exam;

--  2、过滤表中重复数据。
mysql> select distinct chinese from t_exam;
+---------+
| chinese |
+---------+
|      85 |
|      70 |
|      90 |
|      97 |
+---------+

-- 3. 统计每个学生的总分。
mysql>select *,chinese+math+english as total from t_exam;
+----+--------+---------+------+---------+-------+
| id | name   | chinese | math | english | total |
+----+--------+---------+------+---------+-------+
|  1 | 王晶   |      85 |   76 |      70 |   231 |
|  2 | 周星星 |      70 |   75 |      70 |   215 |
|  3 | 沈腾   |      90 |   65 |      95 |   250 |
|  4 | 杨威   |      97 |   50 |      50 |   197 |
|  5 | 李连杰 |      90 |   89 |      80 |   259 |
|  6 | 乔布斯 |      90 |   67 |      65 |   222 |
+----+--------+---------+------+---------+-------+

 rows in set (0.00 sec)

   3.select查询带where子句

--  1、查询英语成绩大于78分的同学
mysql> select * from t_exam where english>78;
+----+--------+---------+------+---------+
| id | name   | chinese | math | english |
+----+--------+---------+------+---------+
|  3 | 沈腾   |      90 |   65 |      95 |
|  5 | 李连杰 |      90 |   89 |      80 |
+----+--------+---------+------+---------+
2 rows in set (0.00 sec)

 --  2、查询总分大于240分的所有同学
mysql> select * from t_exam where chinese+math+english>240;
+----+--------+---------+------+---------+
| id | name   | chinese | math | english |
+----+--------+---------+------+---------+
|  3 | 沈腾   |      90 |   65 |      95 |
|  5 | 李连杰 |      90 |   89 |      80 |
+----+--------+---------+------+---------+
2 rows in set (0.00 sec)

--   3、查询数学成绩在 80－90之间的同学。
mysql> select * from t_exam where math>=80 and math<=90;
+----+--------+---------+------+---------+
| id | name   | chinese | math | english |
+----+--------+---------+------+---------+
|  5 | 李连杰 |      90 |   89 |      80 |
+----+--------+---------+------+---------+
1 row in set (0.00 sec)

--   4、查询数学英语分数为70,75,80,85的同学。
mysql> select * from t_exam where english in (70,75,80,85) ;
+----+--------+---------+------+---------+
| id | name   | chinese | math | english |
+----+--------+---------+------+---------+
|  1 | 王晶   |      85 |   76 |      70 |
|  2 | 周星星 |      70 |   75 |      70 |
|  5 | 李连杰 |      90 |   89 |      80 |
+----+--------+---------+------+---------+
3 rows in set (0.00 sec)

--    5、查询所有姓王的学生成绩。
mysql> select * from t_exam where name like '王%';
+----+--------+---------+------+---------+
| id | name   | chinese | math | english |
+----+--------+---------+------+---------+
|  1 | 王晶   |      85 |   76 |      70 |
|  9 | 王晶红 |      70 |   80 |      90 |
| 10 | 王晶亮 |      70 |   80 |      90 |
| 11 | 王多多 |      70 |   80 |      90 |
+----+--------+---------+------+---------+
4 rows in set (0.00 sec)

mysql> select * from t_exam where name like '王晶%';
+----+--------+---------+------+---------+
| id | name   | chinese | math | english |
+----+--------+---------+------+---------+
|  1 | 王晶   |      85 |   76 |      70 |
|  9 | 王晶红 |      70 |   80 |      90 |
| 10 | 王晶亮 |      70 |   80 |      90 |
+----+--------+---------+------+---------+
3 rows in set (0.00 sec)

--   6、查询所有姓王两个字的学生成绩。
mysql> select * from t_exam where name like '王_';
+----+------+---------+------+---------+
| id | name | chinese | math | english |
+----+------+---------+------+---------+
|  1 | 王晶 |      85 |   76 |      70 |
+----+------+---------+------+---------+
1 row in set (0.00 sec)

 4.select查询使用（函数）

--  1.使用count（函数）统计数学成绩大于或等于80的学生有多少个？

mysql> select count(math) from t_exam where math>=80;
+-----------+
| count(math) |
+-----------+
|         6 |
+-----------+
1 row in set (0.00 sec)

-- 1.1统计总分大于240的人数有多少？
mysql>select count(id) from t_exam where chinese+math+english>240;
+-----------+
| count(id) |
+-----------+
|         2 |
+-----------+
1 row in set (0.00 sec)


--   2.使用sum函数 统计一个班级语文、英语、数学各科的总成绩
mysql> select sum(math),sum(english),sum(chinese) from t_exam;
+-----------+--------------+--------------+
| sum(math) | sum(english) | sum(chinese) |
+-----------+--------------+--------------+
|       822 |          880 |          872 |
+-----------+--------------+--------------+
1 row in set (0.00 sec)

-- 2.1统计一个班级语文、英语、数学的成绩总和
mysql> select sum(math+english+chinese) from t_exam;
+---------------------------+
| sum(math+english+chinese) |
+---------------------------+
|                      2574 |
+---------------------------+
1 row in set (0.00 sec)

 -- 3.使用avg函数 求一个班级数学平均分？
mysql> select avg(math) from t_exam;
+-------------------+
| avg(math)         |
+-------------------+
| 74.72727272727273 |
+-------------------+
1 row in set (0.06 sec)

--    3.1、求一个班级总分平均分
IFNULL(expr1,expr2)，含义是：如果第一个参数不为空，则返回第一个参数，否则返回第二个参数。

select avg(ifnull(chinese,0))+avg(ifnull(math,0))+avg(ifnull(english,0)) from t_exam;

+-------------------------------------------------------------------+
| avg(ifnull(chinese,0))+avg(ifnull(math,0))+avg(ifnull(english,0)) |
+-------------------------------------------------------------------+
|                                                               234 |
+-------------------------------------------------------------------+
1 row in set (0.00 sec)

 -- 4.使用max，min函数,求数学最高分
mysql>  select *,max(math) from t_exam;
+----+------+---------+------+---------+-----------+
| id | name | chinese | math | english | max(math) |
+----+------+---------+------+---------+-----------+
|  1 | 王晶 |      85 |   76 |      70 |        89 |
+----+------+---------+------+---------+-----------+
1 row in set (0.05 sec)

-- 5、求班级总分最低分
mysql>    select *,min(chinese+math+english) from t_exam;
+----+------+---------+------+---------+---------------------------+
| id | name | chinese | math | english | min(chinese+math+english) |
+----+------+---------+------+---------+---------------------------+
|  1 | 王晶 |      85 |   76 |      70 |                       197 |
+----+------+---------+------+---------+---------------------------+
1 row in set (0.00 sec)

5.select查询使用排序order by

  

-- 1.使用order by  排序

-- 1.1 对数学成绩排序
mysql> select * from t_exam order by math desc;
+----+--------+---------+------+---------+
| id | name   | chinese | math | english |
+----+--------+---------+------+---------+
|  5 | 李连杰 |      90 |   89 |      80 |
|  7 | 周润发 |      70 |   80 |      90 |
|  8 | 李白   |      70 |   80 |      90 |
|  9 | 王晶红 |      70 |   80 |      90 |
| 10 | 王晶亮 |      70 |   80 |      90 |
| 11 | 王多多 |      70 |   80 |      90 |
|  1 | 王晶   |      85 |   76 |      70 |
|  2 | 周星星 |      70 |   75 |      70 |
|  6 | 乔布斯 |      90 |   67 |      65 |
|  3 | 沈腾   |      90 |   65 |      95 |
|  4 | 杨威   |      97 |   50 |      50 |
+----+--------+---------+------+---------+
11 rows in set (0.05 sec)


-- 1.2 对姓王的学生成绩排序输出
mysql> select * from t_exam where name like '王%' order by english desc;
+----+--------+---------+------+---------+
| id | name   | chinese | math | english |
+----+--------+---------+------+---------+
|  9 | 王晶红 |      70 |   80 |      90 |
| 10 | 王晶亮 |      70 |   80 |      90 |
| 11 | 王多多 |      70 |   80 |      90 |
|  1 | 王晶   |      85 |   76 |      70 |
+----+--------+---------+------+---------+
4 rows in set (0.00 sec)

-- 1.3 对总分排序按从高到低的顺序输出
mysql> select *,chinese+math+english as 总分 from t_exam order by 总分 desc;
+----+--------+---------+------+---------+------+
| id | name   | chinese | math | english | 总分 |
+----+--------+---------+------+---------+------+
|  5 | 李连杰 |      90 |   89 |      80 |  259 |
|  3 | 沈腾   |      90 |   65 |      95 |  250 |
|  7 | 周润发 |      70 |   80 |      90 |  240 |
|  8 | 李白   |      70 |   80 |      90 |  240 |
|  9 | 王晶红 |      70 |   80 |      90 |  240 |
| 10 | 王晶亮 |      70 |   80 |      90 |  240 |
| 11 | 王多多 |      70 |   80 |      90 |  240 |
|  1 | 王晶   |      85 |   76 |      70 |  231 |
|  6 | 乔布斯 |      90 |   67 |      65 |  222 |
|  2 | 周星星 |      70 |   75 |      70 |  215 |
|  4 | 杨威   |      97 |   50 |      50 |  197 |
+----+--------+---------+------+---------+------+
11 rows in set (0.00 sec)

6.select查询使用分组group by和用having对分组结果进行二次筛选

-- 1.使用GROUP BY语句,查询结果只显示每一组的一条记录,其它数据不显示：
mysql> select * from t_exam group by class_id;
+----+--------+---------+------+---------+----------+
| id | name   | chinese | math | english | class_id |
+----+--------+---------+------+---------+----------+
|  1 | 王晶   |      85 |   76 |      70 |        1 |
|  4 | 杨威   |      97 |   50 |      50 |        2 |
|  5 | 李连杰 |      90 |   89 |      80 |        3 |
|  8 | 李白   |      70 |   80 |      90 |        6 |
|  9 | 王晶红 |      70 |   80 |      90 |        5 |
+----+--------+---------+------+---------+----------+
5 rows in set (0.00 sec)
     
 
-- 2、分组查询配合GROUP_CONCAT()来使用，可以看到每个组中的详细信息,同一组中其它成员信息都会显示：
 mysql> select *,group_concat(name) from t_exam group by class_id;
+----+--------+---------+------+---------+----------+----------------------+
| id | name   | chinese | math | english | class_id | group_concat(name)   |
+----+--------+---------+------+---------+----------+----------------------+
|  1 | 王晶   |      85 |   76 |      70 |        1 | 王晶,周星星,沈腾     |
|  4 | 杨威   |      97 |   50 |      50 |        2 | 杨威                 |
|  5 | 李连杰 |      90 |   89 |      80 |        3 | 李连杰,乔布斯,周润发 |
|  9 | 王晶红 |      70 |   80 |      90 |        5 | 王晶红,王晶亮,王多多 |
|  8 | 李白   |      70 |   80 |      90 |        6 | 李白                 |
+----+--------+---------+------+---------+----------+----------------------+
5 rows in set (0.13 sec)

-- 3.使用HAVING 对分组结果进行二次筛选
--     3.1 WHERE 子句作用于表和视图，HAVING 子句作用于组。
--     3.2 WHERE 在分组和聚集计算之前选取输入行（因此，它控制哪些行进入聚集计算）， 
--     3.3 HAVING 在分组和聚集之后选取分组的行，HAVING 子句总是包含聚集函数。
--     3.4 筛选过程1.where，2.紧接着group by分组，再次筛选则用2.having
  
+----+--------+---------+------+---------+----------+----------------------+
| id | name   | chinese | math | english | class_id | group_concat(name)   |
+----+--------+---------+------+---------+----------+----------------------+
|  1 | 王晶   |      85 |   76 |      70 |        1 | 王晶,周星星,沈腾     |
|  4 | 杨威   |      97 |   50 |      50 |        2 | 杨威                 |
|  5 | 李连杰 |      90 |   89 |      80 |        3 | 李连杰,乔布斯,周润发 |
|  9 | 王晶红 |      70 |   80 |      90 |        5 | 王晶红,王晶亮,王多多 |
|  8 | 李白   |      70 |   80 |      90 |        6 | 李白                 |
+----+--------+---------+------+---------+----------+----------------------+
5 rows in set (0.00 sec)

-- HAVING 对分组结果进行二次筛选，每组的数学成绩和大于200的
mysql> select *,group_concat(name) from t_exam group by class_id having sum(math
)>230;
+----+--------+---------+------+---------+----------+----------------------+
| id | name   | chinese | math | english | class_id | group_concat(name)   |
+----+--------+---------+------+---------+----------+----------------------+
|  5 | 李连杰 |      90 |   89 |      80 |        3 | 李连杰,乔布斯,周润发 |
|  9 | 王晶红 |      70 |   80 |      90 |        5 | 王晶红,王晶亮,王多多 |
+----+--------+---------+------+---------+----------+----------------------+
2 rows in set (0.00 sec)