【MySQL】5.1MySQL高级语句与sql语句

常用查询

对MySQL数据库的查询,除了基本的查询外,有时候需要对查询的结果集进行处理;

例如:只取10条数据、对查询结果进行排序或分组等

一、按关键字排序

ps:类比与windows任务管理器

使用select 语句可以将需要的数据从MySQL数据库中查询出来;另外,还可以通过order by 语句实现排序,并将最终排序后的结果返回给用户;order by 排序语句可以针对单个或多个字段进行排序

(1)语法

select 字段1,字段2,... from 表名 order by 字段1,字段2,...

asc与desc

asc是按照升序进行排序的,是默认的排序方式,可以省略不写;select语句中如果没有指定具体的排序方式,就会默认按asc方式进行排序
desc是按降序方式进行行排列;order by语句前也可以使用where 子句来对查询结果进一步过滤;

模板表:
mysql> select * from class;
+------+--------+-------+----------+--------+
| id   | name   | score | address  | course |
+------+--------+-------+----------+--------+
| 0001 | yiyi   | 78.00 | shanghai | 1      |
| 0002 | lizi   | 92.00 | henan    | 1      |
| 0003 | monor  | 88.00 | nanjing  | 2      |
| 0004 | sisi   | 60.00 | nanjing  | 3      |
| 0005 | wuwu   | 37.00 | shanghai | 3      |
| 0006 | liuliu | 66.00 | henan    | 2      |
| 0007 | qiqi   | 13.00 | hangzhou | 4      |
+------+--------+-------+----------+--------+
7 rows in set (0.00 sec)

mysql> desc class;
+---------+--------------------------+------+-----+---------+-------+
| Field   | Type                     | Null | Key | Default | Extra |
+---------+--------------------------+------+-----+---------+-------+
| id      | int(4) unsigned zerofill | NO   | PRI | NULL    |       |
| name    | char(10)                 | YES  |     | NULL    |       |
| score   | decimal(5,2)             | YES  |     | NULL    |       |
| address | varchar(80)              | YES  |     | NULL    |       |
| course  | char(10)                 | YES  |     | NULL    |       |
+---------+--------------------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

mysql> 
1.按指定字段默认排序
select 字段1,字段2,... from 表名 order by 排序字段;
#按score分数进行排序

mysql> select id,name,score from class order by score;
+------+--------+-------+
| id   | name   | score |
+------+--------+-------+
| 0007 | qiqi   | 13.00 |
| 0005 | wuwu   | 37.00 |
| 0004 | sisi   | 60.00 |
| 0006 | liuliu | 66.00 |
| 0001 | yiyi   | 78.00 |
| 0003 | monor  | 88.00 |
| 0002 | lizi   | 92.00 |
+------+--------+-------+
7 rows in set (0.00 sec)
2.按指定字段降序
select 字段1,字段2,... from 表名 order by 排序字段 desc;
#按score分数进行降序排列

mysql> select id,name,score from class order by score desc;
+------+--------+-------+
| id   | name   | score |
+------+--------+-------+
| 0002 | lizi   | 92.00 |
| 0003 | monor  | 88.00 |
| 0001 | yiyi   | 78.00 |
| 0006 | liuliu | 66.00 |
| 0004 | sisi   | 60.00 |
| 0005 | wuwu   | 37.00 |
| 0007 | qiqi   | 13.00 |
+------+--------+-------+
7 rows in set (0.00 sec)
3.结合where子句进行条件过滤
select 字段1,字段2,... from 表名 where 字段='过滤值' order by 排序字段 desc;
#order by 结合 where 子句过滤排序

mysql> select id,name,score,course from class where course='2' order by score desc;
+------+--------+-------+--------+
| id   | name   | score | course |
+------+--------+-------+--------+
| 0003 | monor  | 88.00 | 2      |
| 0006 | liuliu | 66.00 | 2      |
+------+--------+-------+--------+
2 rows in set (0.00 sec)

4.多字段排序

order by语句也可以使用多个字段来进行排序;当排序的第一个字段有多个相同的记录时,这些记录再按第二个字段进行排序;多个排序字段间用逗号隔开,优先级按先后顺序而定;只用当记录的第一个排序字段相同时,第二个排序字段才会生效

select 字段1,字段2,... from 表名 order by 排序字段1,排序字段2;
#查询学生信息,先按科目升序,再按分数降序

mysql> select id,name,score,course from class order by course,score desc;
+------+--------+-------+--------+
| id   | name   | score | course |
+------+--------+-------+--------+
| 0002 | lizi   | 92.00 | 1      |
| 0001 | yiyi   | 78.00 | 1      |
| 0003 | monor  | 88.00 | 2      |
| 0006 | liuliu | 66.00 | 2      |
| 0004 | sisi   | 60.00 | 3      |
| 0005 | wuwu   | 37.00 | 3      |
| 0007 | qiqi   | 13.00 | 4      |
+------+--------+-------+--------+
7 rows in set (0.00 sec)

总结:按照关键字 或者字段进行 降序排列或者升序排列

二、区间判断及查询不重复记录(去重)

1.区间判断排序(and/or)
and 和 or ———— 且 和 或
#and 同时满足;分数大于60且小于90

mysql> select * from class where score>60 and score<90;
+------+--------+-------+----------+--------+
| id   | name   | score | address  | course |
+------+--------+-------+----------+--------+
| 0001 | yiyi   | 78.00 | shanghai | 1      |
| 0003 | monor  | 88.00 | nanjing  | 2      |
| 0006 | liuliu | 66.00 | henan    | 2      |
+------+--------+-------+----------+--------+
3 rows in set (0.00 sec)


#or 满足其一即可;分数大于90或小于等于60
mysql> select * from class where score>90 or score<=60;
+------+------+-------+----------+--------+
| id   | name | score | address  | course |
+------+------+-------+----------+--------+
| 0002 | lizi | 92.00 | henan    | 1      |
| 0004 | sisi | 60.00 | nanjing  | 3      |
| 0005 | wuwu | 37.00 | shanghai | 3      |
| 0007 | qiqi | 13.00 | hangzhou | 4      |
+------+------+-------+----------+--------+
4 rows in set (0.00 sec)
2.嵌套/多条件查询排序
mysql> select * from class where score>60 or (score>65 and score<95);
+------+--------+-------+----------+--------+
| id   | name   | score | address  | course |
+------+--------+-------+----------+--------+
| 0001 | yiyi   | 78.00 | shanghai | 1      |
| 0002 | lizi   | 92.00 | henan    | 1      |
| 0003 | monor  | 88.00 | nanjing  | 2      |
| 0006 | liuliu | 66.00 | henan    | 2      |
+------+--------+-------+----------+--------+
4 rows in set (0.00 sec)

mysql> select * from class where score>10 and (score>=60 and score<90);
+------+--------+-------+----------+--------+
| id   | name   | score | address  | course |
+------+--------+-------+----------+--------+
| 0001 | yiyi   | 78.00 | shanghai | 1      |
| 0003 | monor  | 88.00 | nanjing  | 2      |
| 0004 | sisi   | 60.00 | nanjing  | 3      |
| 0006 | liuliu | 66.00 | henan    | 2      |
+------+--------+-------+----------+--------+
4 rows in set (0.00 sec)
3.distinct查询不重复记录

只能针对某一字段去重,不能对整个表作用

select distinct 字段 from 表名;
mysql> select distinct course from class;
+--------+
| course |
+--------+
| 1      |
| 2      |
| 3      |
| 4      |
+--------+
4 rows in set (0.00 sec)

三、对结果进行分组(聚合函数)

通过sql查询出来的结果,还可以对其进行分组,使用group by语句来实现;

group by 通常都是结合聚合函数一起使用的,常用的聚合函数包括:计数(COUNT)、 求和(SUM)、求平均数(AVG)、最大值(MAX)、最小值(MIN),group by 分组的时候可以按一个或多个字段对结果进行分组处理。

语法
select 字段1,聚合函数(字段名) from 表名 where 字段名 运算符值 group by 被分组字段名;

SELECT column_name, aggregate_function(column_name)FROM table_name WHERE column_name operator value GROUP BY column_name;

#聚合函数后面括号中的字段要有唯一性(主键或唯一键)

1.分组统计个数
select count(字段1),字段2 from 表名 group by 字段2;

#表示以字段2分组,并以字段1来统计每组的个数
#不管是分组、升序还是降序 都是针对字段进行的

以科目分组,统计相同科目的学生人数(基于name个数进行计数)

mysql> select count(name),score,course from class group by course;
+-------------+-------+--------+
| count(name) | score | course |
+-------------+-------+--------+
|           2 | 78.00 | 1      |
|           2 | 88.00 | 2      |
|           2 | 60.00 | 3      |
|           1 | 13.00 | 4      |
+-------------+-------+--------+
4 rows in set (0.00 sec)
2.结合where语句进行筛选

筛选分数大于等于80的分组,并计算学生个数

mysql> select count(name),score,course from class where score>=80 group by course;
+-------------+-------+--------+
| count(name) | score | course |
+-------------+-------+--------+
|           1 | 92.00 | 1      |
|           1 | 88.00 | 2      |
+-------------+-------+--------+
2 rows in set (0.00 sec)

count(name):计数
>=80    优秀
>=60    及格
<60     不及格
3.结合order by 进行排序

结合order by把计算出的学生个数按成绩降序排列

mysql> select count(name),score,course from class where score>=80 group by course order by score desc;
+-------------+-------+--------+
| count(name) | score | course |
+-------------+-------+--------+
|           1 | 92.00 | 1      |
|           1 | 88.00 | 2      |
+-------------+-------+--------+
2 rows in set (0.00 sec)
统计表中有多少条数据
select count(*) from 表名;
#统计表中有多少条数据
mysql> select count(*) from class;
+----------+
| count(*) |
+----------+
|        7 |
+----------+
1 row in set (0.00 sec)

#统计分数的总和
mysql> select sum(score) from class;
+------------+
| sum(score) |
+------------+
|     434.00 |
+------------+
1 row in set (0.01 sec)

#统计最小分数
mysql> select min(score) from class;
+------------+
| min(score) |
+------------+
|      13.00 |
+------------+
1 row in set (0.00 sec)

#统计最大分数
mysql> select max(score) from class;
+------------+
| max(score) |
+------------+
|      92.00 |
+------------+
1 row in set (0.00 sec)

#统计平均分数
mysql> select avg(score) from class;
+------------+
| avg(score) |
+------------+
|  62.000000 |
+------------+
1 row in set (0.00 sec)

限制结果条目(limit)

limit限制输出的结果记录

在使用mysql 的 select 语句进行查询时,结果集返回的是所有匹配的行(记录);但有时仅需要返回第一行或者某几行时,就要用到 limit子句了

语法
select 字段1,字段2,... from 表名 limit 参数1,参数2;

limit 的第一个参数时位置偏移量(可选参数,可不写),设置MySQL从哪一行开始显示;
如果不设置第一个参数将会默认从第一条记录开始显示;
第一条记录的位置偏移量是0(一般是列名行),第二条是1,以此类推;
第二个参数是设置返回记录行的最大数目;

1.查询所有信息并只显示前3行记录
mysql> select * from class limit 3;
+------+-------+-------+----------+--------+
| id   | name  | score | address  | course |
+------+-------+-------+----------+--------+
| 0001 | yiyi  | 78.00 | shanghai | 1      |
| 0002 | lizi  | 92.00 | henan    | 1      |
| 0003 | monor | 88.00 | nanjing  | 2      |
+------+-------+-------+----------+--------+
3 rows in set (0.00 sec)
2.从第3行开始,往后显示3行
mysql> select * from class limit 2,3;
+------+-------+-------+----------+--------+
| id   | name  | score | address  | course |
+------+-------+-------+----------+--------+
| 0003 | monor | 88.00 | nanjing  | 2      |
| 0004 | sisi  | 60.00 | nanjing  | 3      |
| 0005 | wuwu  | 37.00 | shanghai | 3      |
+------+-------+-------+----------+--------+
3 rows in set (0.00 sec)
3.结合order by 语句,按id升序排列显示前3行
mysql> select * from class order by id limit 3;
+------+-------+-------+----------+--------+
| id   | name  | score | address  | course |
+------+-------+-------+----------+--------+
| 0001 | yiyi  | 78.00 | shanghai | 1      |
| 0002 | lizi  | 92.00 | henan    | 1      |
| 0003 | monor | 88.00 | nanjing  | 2      |
+------+-------+-------+----------+--------+
3 rows in set (0.00 sec)
基础select 小的升阶
1.怎么只输出后3行
mysql> select * from class order by id desc limit 3;
+------+--------+-------+----------+--------+
| id   | name   | score | address  | course |
+------+--------+-------+----------+--------+
| 0007 | qiqi   | 13.00 | hangzhou | 4      |
| 0006 | liuliu | 66.00 | henan    | 2      |
| 0005 | wuwu   | 37.00 | shanghai | 3      |
+------+--------+-------+----------+--------+
3 rows in set (0.00 sec)

#先desc 降序排列,再输出前3行即可
2.怎么对前3行进行降序
第一种方法:

mysql> select * from (select * from class limit 3) as a order by score desc;
+------+-------+-------+----------+--------+
| id   | name  | score | address  | course |
+------+-------+-------+----------+--------+
| 0002 | lizi  | 92.00 | henan    | 1      |
| 0003 | monor | 88.00 | nanjing  | 2      |
| 0001 | yiyi  | 78.00 | shanghai | 1      |
+------+-------+-------+----------+--------+
3 rows in set (0.00 sec)



第二种方法:
可以先限制输出前3行并将数据导入一个新表,再在新表中进行降序

企业中删除数据时,先用select 查看,确认数据后,再换用delete删除,比较安全准确

四、设置别名alias

在MySQL查询时,当表的名字比较长或者表内某些字段比较长时,为了方便书写或者多次使用相同的表,可以给字段或者表设置别名;使用时直接使用别名,简洁明了,增强可读性;

可以给表或表的列设置别名,增强可读性;alias可以简写为as;
别名的设置是临时的,只有临时的显示效果,并不会修改表的结构;

语法

列的别名:select 列名1,列名2 as 别名,... from 表名;

表的别名:select 别名.列名1,别名.列名2,... from 表名 as 别名;

  在使用as后,可用 别名 代替 表名 ,其中as语句是可选的;as之后的别名,主要是为表内的列或者表提供临时的名称,在查询过程中使用,库内实际的表名 或字段名是不会被改变的

设置列名的别名

别名只有临时的显示效果,并不会修改表的结构;

mysql> select id,name as 姓名,score,course 课程 from class;
+------+--------+-------+--------+
| id   | 姓名   | score | 课程   |
+------+--------+-------+--------+
| 0001 | yiyi   | 78.00 | 1      |
| 0002 | lizi   | 92.00 | 1      |
| 0003 | monor  | 88.00 | 2      |
| 0004 | sisi   | 60.00 | 3      |
| 0005 | wuwu   | 37.00 | 3      |
| 0006 | liuliu | 66.00 | 2      |
| 0007 | qiqi   | 13.00 | 4      |
+------+--------+-------+--------+
7 rows in set (0.00 sec)

#alias 别名可以简写为 as;应为是系统默认的值,所以也可以省略不写(列名后面直接跟别名)
设置表名的别名

当多表之间的有相同字段时,可以用别名来区分

mysql> select a.id,a.name,a.score from class as a;
+------+--------+-------+
| id   | name   | score |
+------+--------+-------+
| 0001 | yiyi   | 78.00 |
| 0002 | lizi   | 92.00 |
| 0003 | monor  | 88.00 |
| 0004 | sisi   | 60.00 |
| 0005 | wuwu   | 37.00 |
| 0006 | liuliu | 66.00 |
| 0007 | qiqi   | 13.00 |
+------+--------+-------+
7 rows in set (0.00 sec)
查询表的字段数量

使用场景:

1.对复杂的表进行查询的时候,别名可以缩短查询语句的长度
2.多表查询时,方便通俗易懂,简短sql语句

as 将查询的数据导入新表

create table test as select * from class limit 3;

此处 as 的作用:
1、创建了一个新表test 并定义表结构,插入表数据(与class表相同)
2、但是”约束“没有被完全”复制“过来 #但是如果原表设置了主键,那么附表的:default字段会默认设置一个0
相似:与克隆表复制表结构时相似
create table test (select * from class);
#也可以加入where 语句判断
create table test1 as select * from class where score >=60;
 
在为表设置别名时,要保证别名不能与数据库中的其他表的名称冲突。
列的别名是在结果中有显示的,而表的别名在结果中没有显示,只在执行查询时使用。 
 

mysql> create table test as select * from class limit 3;
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| class            |
| test             |
+------------------+
2 rows in set (0.00 sec)

mysql> select * from test;
+------+-------+-------+----------+--------+
| id   | name  | score | address  | course |
+------+-------+-------+----------+--------+
| 0001 | yiyi  | 78.00 | shanghai | 1      |
| 0002 | lizi  | 92.00 | henan    | 1      |
| 0003 | monor | 88.00 | nanjing  | 2      |
+------+-------+-------+----------+--------+
3 rows in set (0.00 sec)

五、通配符

通配符主要用于替换字符串中的部分字符,通过部分字符的匹配将相关结果查询出来

通常通配符都是跟 like (模糊匹配)一起使用,协同where子句共同完成查询任务;

常用的通配符有两个:% 和 _

%        百分号表示零个、一个或多个字符
_        下划线表示单个字符

查询以xxx开头的记录

查询以l开头的用户

mysql> select * from class where name like 'l%';
+------+--------+-------+---------+--------+
| id   | name   | score | address | course |
+------+--------+-------+---------+--------+
| 0002 | lizi   | 92.00 | henan   | 1      |
| 0006 | liuliu | 66.00 | henan   | 2      |
+------+--------+-------+---------+--------+
2 rows in set (0.00 sec)

mysql> select * from class where name like 'L%';
+------+--------+-------+---------+--------+
| id   | name   | score | address | course |
+------+--------+-------+---------+--------+
| 0002 | lizi   | 92.00 | henan   | 1      |
| 0006 | liuliu | 66.00 | henan   | 2      |
+------+--------+-------+---------+--------+
2 rows in set (0.00 sec)

#对大小写不敏感
查询以xxx结尾的记录

查询以r结尾的记录

mysql> select * from class where name like '%r';
+------+-------+-------+---------+--------+
| id   | name  | score | address | course |
+------+-------+-------+---------+--------+
| 0003 | monor | 88.00 | nanjing | 2      |
+------+-------+-------+---------+--------+
1 row in set (0.00 sec)
查询中间有xxx的记录

查询中间有u的记录

mysql> select * from class where name like '%u%';
+------+--------+-------+----------+--------+
| id   | name   | score | address  | course |
+------+--------+-------+----------+--------+
| 0005 | wuwu   | 37.00 | shanghai | 3      |
| 0006 | liuliu | 66.00 | henan    | 2      |
+------+--------+-------+----------+--------+
2 rows in set (0.00 sec)
查询 xxx 隔xxx个字符后为 xxx 的记录

查询 i 隔一个字符后为 l 的记录

mysql> select * from class where name like '%i_l%';
+------+--------+-------+---------+--------+
| id   | name   | score | address | course |
+------+--------+-------+---------+--------+
| 0006 | liuliu | 66.00 | henan   | 2      |
+------+--------+-------+---------+--------+
1 row in set (0.00 sec)

mysql> select * from class where name like 'l%i%';
+------+--------+-------+---------+--------+
| id   | name   | score | address | course |
+------+--------+-------+---------+--------+
| 0002 | lizi   | 92.00 | henan   | 1      |
| 0006 | liuliu | 66.00 | henan   | 2      |
+------+--------+-------+---------+--------+
2 rows in set (0.00 sec)

总结

1. order by 排序

指定字段默认排序
select 字段1,字段2,... from 表名 order by 排序字段;
指定字段降序
select 字段1,字段2,... from 表名 order by 排序字段 desc;
结合where子句进行条件过滤
select 字段1,字段2,... from 表名 where 字段='过滤值' order by 排序字段 desc;
多字段排序
select 字段1,字段2,... from 表名 order by 排序字段1,排序字段2;

2.区间判断并排序and  or 和去重 distinct

select * from class where 区间1 and 区间2;
select * from class where 区间1 or 区间2;

嵌套/多条件查询排序
select * from class where score>60 or (score>65 and score<95);

distinct查询不重复记录
select distinct 字段 from 表名;

3.对结果进行分组(聚合函数)

聚合函数
select 字段1,聚合函数(字段名) from 表名 where 字段名 运算符值 group by 被分组字段名;
#聚合函数后面括号中的字段要有唯一性(主键或唯一键)

分组统计个数
select count(字段1),字段2 from 表名 group by 字段2;
结合where语句进行筛选
select count(name),score,course from class where score>=80 group by course;
结合order by 进行排序
select count(name),score,course from class where score>=80 group by course order by score desc;

聚合函数:count计数、sum求和、avg求平均数、max最大值、min最小值
select count(*) from 表名;
select sum(score) from class;
select min(score) from class;
select max(score) from class;
select avg(score) from class;

限制结果条目(limit)
限制结果条目(limit)
select 字段1,字段2,... from 表名 limit 参数1,参数2;

按id升序排列显示前3行
select * from class order by id limit 3;

只输出后3行
select * from class order by id desc limit 3;

对前3行进行降序
select * from (select * from class limit 3) as a order by score desc;

4.设置别名alias

别名的设置是临时的,只有临时的显示效果,并不会修改表的结构;

列的别名
select 列名1,列名2 as 别名,... from 表名;
表的别名
select 别名.列名1,别名.列名2,... from 表名 as 别名;

as 将查询的数据导入新表
create table 新表名 as select * from 表名;

5.通配符 % 和 _

% 和 _ 与like和where子句共同完成查询任务;对大小写不敏感

查询以xxx开头的记录
select * from class where name like 'l%';
查询 xxx 隔xxx个字符后为 xxx 的记录
select * from class where name like '%i_l%';

  • 18
    点赞
  • 16
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值