MYSQL数据库高级SQL语句详解
在对 MySQL 数据库的增、删、改、查操作有一定了解之后,就可以学习一些 SQL 语句的高级使用方法。SQL
语句的熟练使用,在平时的运维工作中可以提供不小的帮助,尤其是在一些规模较小的公司,运维身兼数职,可能会有不少数据库的相关工作。本章将从不
同的方面出发介绍 SQL 语句的高级运用方法。
一:MyAQL进阶查询
1.1:按关键字排序
使用ORDERBY语句来实现排序
排序可针对一个或多个字段
ASC:升序,默认排序方式 【升序是从小到大】
DESC:降序 【降序是从大到小】
ORDER BY的语法结构
ORDER BY后面跟字段名
SELECT column1, column2,....FROM table_name ORDER BY column1,column2,...ASC|DESC;
1.11:按单字短排序
- 我们这边新建一个数据库tt
#创建tt数据库
mysql> cerate databases tt;
#切换到tt库
mysql> use tt;
#创建表结构tt
mysql> create table tt (id int(10) not null primary key auto_increment,name char(20) not null,score decimal(5,2),adddress varchar(40)default '未知')engine=innodb;
#查看表结构
mysql> desc tt;
+---------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+----------------+
| id | int(10) | NO | PRI | NULL | auto_increment |
| name | char(20) | NO | | NULL | |
| score | decimal(5,2) | YES | | NULL | |
| address | varchar(40) | YES | | 未知 | |
+---------+--------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)
#插入数据记录
mysql> insert into tt (name,score,address) values ('wangwu',70,'beijing'),('lisi',90,'nanjing');
mysql> insert into tt (name,score,address) values ('zhangsan',80,'beijing'),('zhaoliu',60,'nanjing');
#查看数据记录
mysql> select * from tt;
+----+----------+-------+---------+
| id | name | score | address |
+----+----------+-------+---------+
| 1 | wangwu | 70.00 | beijing |
| 2 | lisi | 90.00 | nanjing |
| 3 | zhangsan | 80.00 | beijing |
| 4 | zhaoliu | 60.00 | nanjing |
+----+----------+-------+---------+
4 rows in set (0.00 sec)
- 筛选分数大于70分的
mysql> select name,score from tt where score>=70;
+----------+-------+
| name | score |
+----------+-------+
| wangwu | 70.00 |
| lisi | 90.00 |
| zhangsan | 80.00 |
+----------+-------+
3 rows in set (0.00 sec)
#筛选大于70分的进行升序排序 【对score字段排序】
mysql> select name,score from tt where score>=70 order by score;
+----------+-------+
| name | score |
+----------+-------+
| wangwu | 70.00 |
| zhangsan | 80.00 |
| lisi | 90.00 |
+----------+-------+
3 rows in set (0.00 sec)
#进行降序排序
mysql> select name,score from tt where score>=70 order by score desc;
+----------+-------+
| name | score |
+----------+-------+
| lisi | 90.00 |
| zhangsan | 80.00 |
| wangwu | 70.00 |
+----------+-------+
3 rows in set (0.00 sec)
【SELECT 语句中如果没有指定具体的排序方式,则默认按 ASC 方式进行排序。DESC 是按降序方式进行排列。当然 ORDER BY 前面也可以使用 WHERE 子句对查询结果进一步过滤.】
1.12:按多字段排序
- 这边我们再次插入新的数据内容
mysql> insert into tt (name,score,address) values ('tianqi',80,'beijing'),,('shuaige',70,'suzhou');
#查询数据记录
mysql> select * from tt;
+----+----------+-------+---------+
| id | name | score | address |
+----+----------+-------+---------+
| 1 | wangwu | 70.00 | beijing |
| 2 | lisi | 90.00 | nanjing |
| 3 | zhangsan | 80.00 | beijing |
| 4 | zhaoliu | 60.00 | nanjing |
| 5 | tianqi | 80.00 | beijing |
| 6 | shuaige | 70.00 | suzhou |
+----+----------+-------+---------+
6 rows in set (0.00 sec)
#单字段匹配【降序】
mysql> select id,name,score from tt where score>=70 order by score desc;
+----+----------+-------+
| id | name | score |
+----+----------+-------+
| 2 | lisi | 90.00 |
| 3 | zhangsan | 80.00 |
| 5 | tianqi | 80.00 |
| 1 | wangwu | 70.00 |
| 6 | shuaige | 70.00 |
+----+----------+-------+
5 rows in set (0.00 sec)
#修改lisi成绩为80分
mysql> update tt set score=80 where id=2;
mysql> select id,name,score from tt where score>=70 order by score desc;
+----+----------+-------+
| id | name | score |
+----+----------+-------+
| 2 | lisi | 80.00 |
| 3 | zhangsan | 80.00 |
| 5 | tianqi | 80.00 |
| 1 | wangwu | 70.00 |
| 6 | shuaige | 70.00 |
+----+----------+-------+
5 rows in set (0.00 sec)
#进行多段排序
mysql> select id,name,score from tt where score>=70 order by score desc,id desc;
+----+----------+-------+
| id | name | score |
+----+----------+-------+
| 5 | tianqi | 80.00 |
| 3 | zhangsan | 80.00 |
| 2 | lisi | 80.00 |
| 6 | shuaige | 70.00 |
| 1 | wangwu | 70.00 |
+----+----------+-------+
5 rows in set (0.00 sec)
#这边把id字段也做了一个排序
1.2:对结果进行分组
通过 SQL 查询出来的结果,还可以对其进行分组,使用 GROUP BY 语句来实现。
GROUP BY 从字面上看,是以 BY 后面的内容对查询出的数据进行分组,就是将一个“数据集”划分成若干个“小区域”,然后针对这些个“小区域”进行数据处理。
-
使用GROUP BY语句来实现分组
-
通常结合聚合函数一起使用
-
可以按一个或多个字段对结果进行分组
-
GROUP BY的语法结构
SELECT column_name, aggregate_function(column_name)FROM table_name WHERE column_name operator value GROUP BY column_name;
1.21:常用的聚合函数包括:
- count (字段名称) 计数 函数
-
count (*) 技术
-
sum (*) 求和
-
avg (*) 平均值
-
max (*) 最大
-
min (*) 最小值
-
1.22:GROUP BY分组
- 统计70跟80分的人数
#查询所有数据记录
mysql> select * from tt;
+----+----------+-------+---------+
| id | name | score | address |
+----+----------+-------+---------+
| 1 | wangwu | 70.00 | beijing |
| 2 | lisi | 80.00 | nanjing |
| 3 | zhangsan | 80.00 | beijing |
| 4 | zhaoliu | 60.00 | nanjing |
| 5 | tianqi | 80.00 | beijing |
| 6 | shuaige | 70.00 | suzhou |
+----+----------+-------+---------+
6 rows in set (0.00 sec)
#进行分组
mysql> select count(name),score from tt where score >=70 group by score;
+-------------+-------+
| count(name) | score |
+-------------+-------+
| 2 | 70.00 |
| 3 | 80.00 |
+-------------+-------+
2 rows in set (0.00 sec)
- 求班级的学生的平均成绩
avg (*) 平均值
mysql> select avg(score) from tt;
+------------+
| avg(score) |
+------------+
| 73.333333 |
+------------+
1 row in set (0.00 sec)
1.23:GROUP BY集合ORDER BY
#分组降序
mysql> select count(name),score from tt where score >=70 group by score order by score desc;
+-------------+-------+
| count(name) | score |
+-------------+-------+
| 3 | 80.00 |
| 2 | 70.00 |
+-------------+-------+
2 rows in set (0.00 sec)
#分组升序
mysql> select count(name),score from tt where score >=70 group by score order by score;
+-------------+-------+
| count(name) | score |
+-------------+-------+
| 2 | 70.00 |
| 3 | 80.00 |
+-------------+-------+
2 rows in set (0.01 sec)
1.3:限制结果条目
在使用 MySQL SELECT 语句进行查询时,结果集返回的是所有匹配的记录。有时候仅需要返回第一行或者前几行,这时候就需要用到 LIMIT 子句。
LIMIT 的第一个参数是位置偏移量(可选参数),是设置 MySQL 从哪一行开始显示。如果不设定第一个参数,将会从表中的第一条记录开始显示。需要注意的是,第一条记录的位置偏移量是 0,第二条是 1,以此类推。第二个参数是设置返回记录行的最大数目。
-
只返回SELECT查询结果的第一行或前几行
-
使用LIMIT语句限制条目
-
LIMIT语法结构
SELECT column1,column2,...FROM table_name LIMIT[offset,] number;
number:返回记录行的最大数目
[offset,]:位置偏移量,从0开始
- 查看前三行的记录
mysql> select * from tt limit 3;
+----+----------+-------+---------+
| id | name | score | address |
+----+----------+-------+---------+
| 1 | wangwu | 70.00 | beijing |
| 2 | lisi | 80.00 | nanjing |
| 3 | zhangsan | 80.00 | beijing |
+----+----------+-------+---------+
3 rows in set (0.00 sec)
- 查看3到5行的记录
#2代表索引 往下数3行
mysql> select * from tt limit 2,3;
+----+----------+-------+---------+
| id | name | score | address |
+----+----------+-------+---------+
| 3 | zhangsan | 80.00 | beijing |
| 4 | zhaoliu | 60.00 | nanjing |
| 5 | tianqi | 80.00 | beijing |
+----+----------+-------+---------+
3 rows in set (0.00 sec)
- 列出班级前三名
- 在插入一些数据记录
mysql> insert into tt (name,score,address) values ('tom',87,'shanghai'),('shuaige',76,'hangzhou');
#查询数据记录
mysql> select * from tt;
+----+----------+-------+----------+
| id | name | score | address |
+----+----------+-------+----------+
| 1 | wangwu | 70.00 | beijing |
| 2 | lisi | 80.00 | nanjing |
| 3 | zhangsan | 80.00 | beijing |
| 4 | zhaoliu | 60.00 | nanjing |
| 5 | tianqi | 80.00 | beijing |
| 6 | shuaige | 70.00 | suzhou |
| 7 | tom | 87.00 | shanghai |
| 8 | shuaige | 76.00 | hangzhou |
+----+----------+-------+----------+
8 rows in set (0.00 sec)
#列出前三名 先排序
mysql> select * from tt order by score desc limit 3;
+----+----------+-------+----------+
| id | name | score | address |
+----+----------+-------+----------+
| 7 | tom | 87.00 | shanghai |
| 2 | lisi | 80.00 | nanjing |
| 3 | zhangsan | 80.00 | beijing |
+----+----------+-------+----------+
3 rows in set (0.00 sec)
方法2: 只要条件成立就行
mysql> select * from tt where 1=1 order by score desc limit 3;
+----+----------+-------+----------+
| id | name | score | address |
+----+----------+-------+----------+
| 7 | tom | 87.00 | shanghai |
| 2 | lisi | 80.00 | nanjing |
| 3 | zhangsan | 80.00 | beijing |
+----+----------+-------+----------+
3 rows in set (0.00 sec)
1.4:设置别名
在 MySQL 查询时,当表的名字比较长或者表内某些字段比较长时,为了方便书写或者多次使用相同的表,可以给字段列或表设置别名。使用的时候直接使用别名,简洁明了,增 强可读性。
- 使用AS语句设置别名,关键字AS可省略
- 设置别名时,保证不能与库中其他表或字段名称冲突
- 别名的语法结构
SELECT column_name AS alias_name FROM table_name;
SELECT column_name(s)FROM table_name ASalias_name;
1.41:AS的用法
示例
mysql> select count(*) from tt;
+----------+
| count(*) |
+----------+
| 8 |
+----------+
1 row in set (0.00 sec)
mysql> select count(*) as 数量 from tt;
+--------+
| 数量 |
+--------+
| 8 |
+--------+
1 row in set (0.00 sec)
- 多表相连
#这边我们只有tt一张表 我们在创建一张表命名为gg
create table gg (id int(5) not null primary key auto_increment,gg_name varchar(20) not null);
#显示所有表
mysql> show tables;
+--------------+
| Tables_in_tt |
+--------------+
| gg |
| tt |
+--------------+
2 rows in set (0.00 sec)
#查看表结构
mysql> desc gg;
+---------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+----------------+
| id | int(5) | NO | PRI | NULL | auto_increment |
| gg_name | varchar(20) | NO | | NULL | |
+---------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
#插入数据
mysql> insert into gg (gg_name) values ('小当家'),('小神龙'),('小福贵');;
#查看gg表数据记录
mysql> select * from gg;
+----+-----------+
| id | gg_name |
+----+-----------+
| 1 | 小当家 |
| 2 | 小神龙 |
| 3 | 小福贵 |
+----+-----------+
3 rows in set (0.00 sec)
#为了对应我们给tt表增加一列
mysql> alter table tt add column hobby int(3) not null;
#查看表结构
mysql> desc tt;
+---------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+----------------+
| id | int(10) | NO | PRI | NULL | auto_increment |
| name | char(20) | NO | | NULL | |
| score | decimal(5,2) | YES | | NULL | |
| address | varchar(40) | YES | | 未知 | |
| hobby | int(3) | NO | | NULL | |
+---------+--------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
#查询表数据记录
mysql> select * from tt;
+----+----------+-------+----------+-------+
| id | name | score | address | hobby |
+----+----------+-------+----------+-------+
| 1 | wangwu | 70.00 | beijing | 0 |
| 2 | lisi | 80.00 | nanjing | 0 |
| 3 | zhangsan | 80.00 | beijing | 0 |
| 4 | zhaoliu | 60.00 | nanjing | 0 |
| 5 | tianqi | 80.00 | beijing | 0 |
| 6 | shuaige | 70.00 | suzhou | 0 |
| 7