MySQL高阶语句

MySQL高阶语句

排序

按关键字排序

order by 语句来实现排序

asc 按照升序进行排序（默认）

desc 按照降序方式进行排序

order by 前面可以使用where子句对查询进行进一步的过滤

mysql> select * from ky29;
+------+-----------+-------+------------+--------+
| id   | name      | score | address    | hobbid |
+------+-----------+-------+------------+--------+
|    6 | hanmeimei | 10.00 | nanjing    |      3 |
|    5 | jiaoshou  | 98.00 | laowo      |      3 |
|    7 | lilei     | 11.00 | nanjing    |      5 |
|    3 | lisi      | 60.00 | shanghai   |      4 |
|    1 | liuyi     | 80.00 | beijing    |      2 |
|    4 | tianqi    | 99.00 | hangzhou   |      5 |
|    2 | wangwu    | 90.00 | shengzheng |      2 |
+------+-----------+-------+------------+--------+
7 rows in set (0.00 sec)

select name,score from ky29 where address=‘nanjing’ order by score desc；

只显示姓名和分数，并且提取南京地区的分数并从高往低排序

mysql> select name,score from ky29  where address='nanjing' order by score desc;
+-----------+-------+
| name      | score |
+-----------+-------+
| lilei     | 11.00 |
| hanmeimei | 10.00 |
+-----------+-------+
2 rows in set (0.00 sec)

select id,name,hobbid from ky29 order by hobbid desc,id desc;

多列排序

mysql> select id,name,hobbid from ky29 order by hobbid desc,id desc;  
+------+-----------+--------+
| id   | name      | hobbid |
+------+-----------+--------+
|    7 | lilei     |      5 |
|    4 | tianqi    |      5 |
|    3 | lisi      |      4 |
|    6 | hanmeimei |      3 |
|    5 | jiaoshou  |      3 |
|    2 | wangwu    |      2 |
|    1 | liuyi     |      2 |
+------+-----------+--------+
7 rows in set (0.00 sec)

区间判断

区间判断查询 不重复记录

and/or 且/或

distinct 查询不重复记录

select * from ky29 where score >60 and score <=90;

删选大于60小于等于90的分数

mysql> select * from ky29 where score >60 and score <=90;
+------+--------+-------+------------+--------+
| id   | name   | score | address    | hobbid |
+------+--------+-------+------------+--------+
|    1 | liuyi  | 80.00 | beijing    |      2 |
|    2 | wangwu | 90.00 | shengzheng |      2 |
+------+--------+-------+------------+--------+
2 rows in set (0.00 sec)

select * from ky29 where score >60 or score <=90;

删选大于60或者小于等于90的分数

mysql> select * from ky29 where score >60 or score <=90;
+------+-----------+-------+------------+--------+
| id   | name      | score | address    | hobbid |
+------+-----------+-------+------------+--------+
|    6 | hanmeimei | 10.00 | nanjing    |      3 |
|    5 | jiaoshou  | 98.00 | laowo      |      3 |
|    7 | lilei     | 11.00 | nanjing    |      5 |
|    3 | lisi      | 60.00 | shanghai   |      4 |
|    1 | liuyi     | 80.00 | beijing    |      2 |
|    4 | tianqi    | 99.00 | hangzhou   |      5 |
|    2 | wangwu    | 90.00 | shengzheng |      2 |
+------+-----------+-------+------------+--------+
7 rows in set (0.00 sec)

select * from ky29 where score >60 or （score >60 and score <90);

筛选出大于60或者大于60小于90分的数据

mysql> select * from ky29 where score >60 or (score >60 and score <90);
+------+----------+-------+------------+--------+
| id   | name     | score | address    | hobbid |
+------+----------+-------+------------+--------+
|    5 | jiaoshou | 98.00 | laowo      |      3 |
|    1 | liuyi    | 80.00 | beijing    |      2 |
|    4 | tianqi   | 99.00 | hangzhou   |      5 |
|    2 | wangwu   | 90.00 | shengzheng |      2 |
+------+----------+-------+------------+--------+
4 rows in set (0.00 sec)

select * from ky29 where score >60 and (score >60 and score <90);

筛选出大于60且大于60小于90分的数据

mysql> select * from ky29 where score >60 and (score >60 and score <90);
+------+-------+-------+---------+--------+
| id   | name  | score | address | hobbid |
+------+-------+-------+---------+--------+
|    1 | liuyi | 80.00 | beijing |      2 |
+------+-------+-------+---------+--------+
1 row in set (0.00 sec)

select distinct hobbid from ky29;

查询不重复的hobbid

mysql> select distinct hobbid from ky29;    
+--------+
| hobbid |
+--------+
|      3 |
|      5 |
|      4 |
|      2 |
+--------+
4 rows in set (0.00 sec)

对结果进行分组

对结果进行分组

通过SQL语句查询出来的结果 进行分组

group by 语句来实现

count（计数）、求和（sum）求平均数（avg）最大值（max）最小值（min）

group by分组可以按一个或多个字段对结果进行分组

select count(name),hobbid from ky29 group by hobbid;

按照hobbid相同的分组，计算相同分数的学生的个数(就是指hobbid为2的人有几个，为3的人有几个)

mysql> select count(name),hobbid from ky29 group by hobbid;
+-------------+--------+
| count(name) | hobbid |
+-------------+--------+
|           2 |      2 |
|           2 |      3 |
|           1 |      4 |
|           2 |      5 |
+-------------+--------+
4 rows in set (0.00 sec)

select count(*) from ky29;

查看ky29表里面一共有多少行数据

mysql> select count(*) from ky29;
+----------+
| count(*) |
+----------+
|        7 |
+----------+
1 row in set (0.00 sec)

select count(name),score,hobbid from ky29 where score>=80 group by hobbid order by count(nat(name) asc;

以hobbid进行分组，筛选出分数大于等于80的人，并升序显示

mysql> select count(name),score,hobbid from ky29 where score>=80 group by hobbid order by countt(name) asc;
+-------------+-------+--------+
| count(name) | score | hobbid |
+-------------+-------+--------+
|           1 | 99.00 |      5 |
|           1 | 98.00 |      3 |
|           2 | 80.00 |      2 |
+-------------+-------+--------+
3 rows in set (0.00 sec)

限制结果条目

限制结果条目（limit）

limit 限制输出的结果记录（显示你想要看到的行数）

select * from ky29 limit 4,3

显示ky29表里面第四行往下的三行内容

mysql> select * from ky29 limit 4,3 ;
+------+--------+-------+------------+--------+
| id   | name   | score | address    | hobbid |
+------+--------+-------+------------+--------+
|    1 | liuyi  | 80.00 | beijing    |      2 |
|    4 | tianqi | 99.00 | hangzhou   |      5 |
|    2 | wangwu | 90.00 | shengzheng |      2 |
+------+--------+-------+------------+--------+
3 rows in set (0.00 sec)

select id,name from ky29 order by id limit 4;

按照id排序并只显示前四行

mysql> select id,name from ky29 order by id limit 4;
+------+--------+
| id   | name   |
+------+--------+
|    1 | liuyi  |
|    2 | wangwu |
|    3 | lisi   |
|    4 | tianqi |
+------+--------+
4 rows in set (0.00 sec)

设置别名

设置别名 as

列：字段列设置别名，主要是为了简洁明了 增强可读性

在企业中多个表查询字段相同时需要使用到别名来确定是哪一张表的数据

表：表设置别名，主要也是为了简洁明了 增强可读性

select name as 姓名,score as 成绩 from ky29；

将ky29表里面的name和score设置成中文别名

mysql> select name as 姓名,score as 成绩 from ky29;
+-----------+--------+
| 姓名      | 成绩   |
+-----------+--------+
| hanmeimei |  10.00 |
| jiaoshou  |  98.00 |
| lilei     |  11.00 |
| lisi      |  60.00 |
| liuyi     |  80.00 |
| tianqi    |  99.00 |
| wangwu    |  90.00 |
+-----------+--------+
7 rows in set (0.00 sec)

select count(*) as 数量 from ky29;（as可以不需要）

查询ky29表里面的数据条数，并且用数量别名显示

mysql> select count(*) as 数量 from ky29;
+--------+
| 数量   |
+--------+
|      7 |
+--------+
1 row in set (0.00 sec)

create table t1 as select * from ky29；

用ky29里面的数据来创建一个ky30表，这里的as单纯作为连接语句用

mysql> create table t1 as select * from ky29;
Query OK, 7 rows affected (0.02 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql> select * from t1;
+------+-----------+-------+------------+--------+
| id   | name      | score | address    | hobbid |
+------+-----------+-------+------------+--------+
|    6 | hanmeimei | 10.00 | nanjing    |      3 |
|    5 | jiaoshou  | 98.00 | laowo      |      3 |
|    7 | lilei     | 11.00 | nanjing    |      5 |
|    3 | lisi      | 60.00 | shanghai   |      4 |
|    1 | liuyi     | 80.00 | beijing    |      2 |
|    4 | tianqi    | 99.00 | hangzhou   |      5 |
|    2 | wangwu    | 90.00 | shengzheng |      2 |
+------+-----------+-------+------------+--------+
7 rows in set (0.00 sec)

create table t2 (select * from ky29 where score >=80);

用ky29表里面分数大于等于80的数据来创建一个t2的表

mysql> create table t2 (select * from ky29 where score >=80);
Query OK, 4 rows affected (0.02 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from t2;
+------+----------+-------+------------+--------+
| id   | name     | score | address    | hobbid |
+------+----------+-------+------------+--------+
|    5 | jiaoshou | 98.00 | laowo      |      3 |
|    1 | liuyi    | 80.00 | beijing    |      2 |
|    4 | tianqi   | 99.00 | hangzhou   |      5 |
|    2 | wangwu   | 90.00 | shengzheng |      2 |
+------+----------+-------+------------+--------+
4 rows in set (0.00 sec)

通配符

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

通常通配符都是跟LIKE一起使用的，并协同WHERE

%：表示零个、一个或多个字符

_：表示单个字符

select id,name from ky29 where name like ‘l%’

显示name以l为开头的内容

mysql> select id,name from ky29 where name like 'l%';
+------+-------+
| id   | name  |
+------+-------+
|    7 | lilei |
|    3 | lisi  |
|    1 | liuyi |
+------+-------+
3 rows in set (0.01 sec)

select id,name from ky29 where name like ‘%g%’;

查询名字中间有g的数据

mysql> select id,name from ky29 where name like '%g%';
+------+--------+
| id   | name   |
+------+--------+
|    2 | wangwu |
+------+--------+
1 row in set (0.00 sec)