准备数据
以下操作将在该表中进行
create table student (
id int unsigned primary key auto_increment,name char(12) not null,gender enum("male","famale") default "male",age tinyint unsigned not null,hoc_group char(12) not null,html tinyint unsigned not null,css tinyint unsigned not null,js tinyint unsigned not null,sanction enum("大处分","小处分","无")
);
insert into student(name,gender,age,hoc_group,html,css,js,sanction) values
("Yunya","male",18,"first",88,93,76,"无"),("Jack",17,"second",92,81,("Bella","famale",72,68,91,"小处分"),("Dairis","third",89,54,43,"大处分"),("Kyle",19,"fifth",31,24,60,("Alice",16,49,23,58,("Ken",33,62,("Jason",21,"fourth",90,("Tom",20,("Fiona",71,45,"无");
查询语法
SELECT DISTINCT(字段名1,字段名2...) FROM 表名
WHERE 条件
GROUP BY 字段名
HAVING 筛选
ORDER BY 字段名 asc/desc
LIMIT 限制条数;
执行顺序
虽然查询的书写语法是上面那样的,但是其内部执行顺序却有些不太一样。
1.通过from 找到将要查询的表
2.where 规定查询条件,在表记录中逐行进行查询并筛选出符合规则的记录
3.将查到的记录进行字段分组group by ,如果没有进行分组,则默认为一组
4.将分组得到的结果进行having 筛选,可使用聚和函数(where 时不可使用聚合函数)
5.执行select 准备打印
6.执行distinct 对打印结果进行去重
7.执行ordery by 对结果进行排序
8.执行limit 对打印结果的条数进行限制
select
select 主要复负责打印相关的工作
全部查询
使用select * from 表名 可拿到该表下全部的数据
以下示例将展示使用全部查询拿到student 表中所有记录
select * from student;
+----+--------+--------+-----+-----------+------+-----+----+-----------+
| id | name | gender | age | hoc_group | html | css | js | sanction |
+----+--------+--------+-----+-----------+------+-----+----+-----------+
| 1 | Yunya | male | 18 | first | 88 | 93 | 76 | 无 |
| 2 | Jack | male | 17 | second | 92 | 81 | 88 | 无 |
| 3 | Bella | famale | 17 | first | 72 | 68 | 91 | 小处分 |
| 4 | Dairis | famale | 18 | third | 89 | 54 | 43 | 大处分 |
| 5 | Kyle | famale | 19 | fifth | 31 | 24 | 60 | 大处分 |
| 6 | Alice | famale | 16 | second | 49 | 23 | 58 | 无 |
| 7 | Ken | male | 16 | third | 33 | 62 | 17 | 大处分 |
| 8 | Jason | male | 21 | fourth | 91 | 92 | 90 | 无 |
| 9 | Tom | male | 20 | fifth | 88 | 72 | 91 | 无 |
| 10 | Fiona | famale | 19 | fourth | 60 | 71 | 45 | 无 |
+----+--------+--------+-----+-----------+------+-----+----+-----------+
字段查询
使用select 字段名1,字段名2 from 表名 可拿到特定字段下相应的数据
以下示例将展示使用字段查询拿到每个学生的HTMLCSSJS 成绩
select name,js from student;
+--------+------+-----+----+
| name | html | css | js |
+--------+------+-----+----+
| Yunya | 88 | 93 | 76 |
| Jack | 92 | 81 | 88 |
| Bella | 72 | 68 | 91 |
| Dairis | 89 | 54 | 43 |
| Kyle | 31 | 24 | 60 |
| Alice | 49 | 23 | 58 |
| Ken | 33 | 62 | 17 |
| Jason | 91 | 92 | 90 |
| Tom | 88 | 72 | 91 |
| Fiona | 60 | 71 | 45 |
+--------+------+-----+----+
as 别名
使用select 字段名1 as 别名1,字段名2 as 别名2 from 表名 可将查询到的记录字段修改一个别名
以下示例将展示修改name 字段为姓名 ,修改gender 字段为性别 ,修改age 字段为年龄 的操作
select name as "姓名",gender as "性别",age as "年龄" from student;
+--------+--------+--------+
| 姓名 | 性别 | 年龄 |
+--------+--------+--------+
| Yunya | male | 18 |
| Jack | male | 17 |
| Bella | famale | 17 |
| Dairis | famale | 18 |
| Kyle | famale | 19 |
| Alice | famale | 16 |
| Ken | male | 16 |
| Jason | male | 21 |
| Tom | male | 20 |
| Fiona | famale | 19 |
+--------+--------+--------+
distinct
使用select distinct(字段名1,字段名2) from 表名 可将查询到的记录做一个取消重复的操作
以下示例将展示使用去重功能来看有多少个小组
select distinct(hoc_group) from student;
+-----------+
| hoc_group |
+-----------+
| first |
| second |
| third |
| fifth |
| fourth |
+-----------+
四则运算
查询结果可进行四则运算,以下示例将展示拿到每个同学三科总分的操作
select name,html+css+js as 总成绩 from student;
+--------+-----------+
| name | 总成绩 |
+--------+-----------+
| Yunya | 257 |
| Jack | 261 |
| Bella | 231 |
| Dairis | 186 |
| Kyle | 115 |
| Alice | 130 |
| Ken | 112 |
| Jason | 273 |
| Tom | 251 |
| Fiona | 176 |
+--------+-----------+
显示格式
使用concat() 可将查询结果与任意字符串进行拼接
使用concat_ws() 可指定连接符进行拼接,第一个参数是连接符
select concat("姓名->",name," ","性别->",gender) from student; # 合并成了一个字符串,注意用的空格分隔开的,不然会黏在一起
+--------------------------------------------------+
| concat("姓名->",gender) |
+--------------------------------------------------+
| 姓名->Yunya 性别->male |
| 姓名->Jack 性别->male |
| 姓名->Bella 性别->famale |
| 姓名->Dairis 性别->famale |
| 姓名->Kyle 性别->famale |
| 姓名->Alice 性别->famale |
| 姓名->Ken 性别->male |
| 姓名->Jason 性别->male |
| 姓名->Tom 性别->male |
| 姓名->Fiona 性别->famale |
+--------------------------------------------------+
select concat_ws("|||",age) from student; # 使用|||为每个字段进行分割
+----------------------------------+
| concat_ws("|||",age) |
+----------------------------------+
| Yunya|||male|||18 |
| Jack|||male|||17 |
| Bella|||famale|||17 |
| Dairis|||famale|||18 |
| Kyle|||famale|||19 |
| Alice|||famale|||16 |
| Ken|||male|||16 |
| Jason|||male|||21 |
| Tom|||male|||20 |
| Fiona|||famale|||19 |
+----------------------------------+
where
where 条件是查询的第一道坎,能有效过滤出我们想要的任意数据
比较运算
使用比较运算符> < >= <= != 进行查询
以下示例将展示使用where 过滤出js 成绩大于80分的同学
select name,js from student where js > 80;
+-------+----+
| name | js |
+-------+----+
| Jack | 88 |
| Bella | 91 |
| Jason | 90 |
| Tom | 91 |
+-------+----+
逻辑运算
使用and or not 可进行逻辑运算与多条件查询
以下示例将展示使用where 多条件查询过滤出各科成绩都大于80分的同学
select name,js from student where html > 80 and css > 80 and js > 80;
+-------+------+-----+----+
| name | html | css | js |
+-------+------+-----+----+
| Jack | 92 | 81 | 88 |
| Jason | 91 | 92 | 90 |
+-------+------+-----+----+
成员运算
in 可以在特定的值中进行获取,如in(80,100) 则代表只取80或者90或者100的这几条记录。
以下示例将展示只取第一组first 以及第二组second 学生的个人信息
select name,hoc_group from student where hoc_group in ("first","second");
+-------+--------+-----+-----------+
| name | gender | age | hoc_group |
+-------+--------+-----+-----------+
| Yunya | male | 18 | first |
| Jack | male | 17 | second |
| Bella | famale | 17 | first |
| Alice | famale | 16 | second |
+-------+--------+-----+-----------+
between and
between and 也是取区间的意思,
以下示例将展示使用between and 过滤出Js 成绩大于等于60并且小于80的同学
select name,js from student where js between 60 and 80;
+-------+----+
| name | js |
+-------+----+
| Yunya | 76 |
| Kyle | 60 |
+-------+----+
like
like 是模糊查询,其中% 代表任意多个字符(类似于贪婪匹配的通配符.* ),_ 代表任意一个字符(类似于非贪婪匹配的通配符.*? )。
以下示例将展示使用like/% 匹配出姓名以k 开头的所有同学的名字
select name from student where name like "k%";
+------+
| name |
+------+
| Kyle |
| Ken |
+------+
以下示例将展示使用like/_ 匹配出姓名以k 开头并整体长度为3的同学的名字
select name from student where name like "k__";
+------+
| name |
+------+
| Ken |
+------+
正则匹配
使用RegExp 可进行正则匹配,以下示例将展示使用正则匹配出名字中带有k 的所有同学姓名
select name from student where name REGEXP "k+";
+------+
| name |
+------+
| Jack |
| Kyle |
| Ken |
+------+
group by
分组行为发生在where 条件之后,我们可以将查询到的记录按照某个相同字段进行归类,一般分组都会配合聚合函数进行使用。
需要注意的是select 语句是排在group by 条件之后的,因此聚合函数也能在select 语句中使用。
基本使用
以下示例将展示对hoc_group 字段进行分组。
我们按照hoc_group 字段进行分组,那么select 查询的字段只能是hoc_group 字段,想要获取组内的其他字段相关信息,需要借助函数来完成
select hoc_group from student group by hoc_group;
+-----------+
| hoc_group |
+-----------+
| fifth |
| first |
| fourth |
| second |
| third |
+-----------+
如果不使用分组,则会产生重复的信息
mysql> select hoc_group from student;
+-----------+
| hoc_group |
+-----------+
| first |
| second |
| first |
| third |
| fifth |
| second |
| third |
| fourth |
| fifth |
| fourth |
+-----------+
group_concat
用什么字段名进行分组,在select 查询时就只能查那个用于分组的字段,查询别的字段会抛出异常,会提示sql_mode 异常。
我们将Js 成绩大于80 分的同学筛选出来并且按照gender 字段进行分组,此外我们还想查看其所有满足条件同学的名字。
以下这样操作会抛出异常。
mysql> select gender,name from student where js > 80 group by gender;
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'school.student.name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
必须借助group_concat() 函数来进行操作才能使我们的需求圆满完成。
select gender,group_concat(name) from student where js > 80 group by gender;
+--------+--------------------+
| gender | group_concat(name) |
+--------+--------------------+
| male | Jack,Jason,Tom |
| famale | Bella |
+--------+--------------------+
分组模式
ONLY_FULL_GROUP_BY 要求select 中的字段是在与group by 中使用的字段
如果group by 是主键或unique not null 时可以在select 中列出其他字段
#查看MySQL 5.7默认的sql_mode如下:
mysql> select @@global.sql_mode;
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
#设置sql_mole如下操作(我们可以去掉ONLY_FULL_GROUP_BY模式):
mysql> set global sql_mode='STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION';
聚合函数
聚合函数可以在where 执行后的所有语句中使用,比如having ,select 等。
聚合函数一般是同分组进行配套使用,以下是常用的聚合函数。
函数名 |
作用 |
|