MySQL高级SQL语句

1. MySQL的晋级查询

1.1 按关键字排序

1. 使用ORDER BY语句来实现排序
2. 排序可针对一个或多个字段
3. ASC：升序，默认排序方式
4. DESC：降序
5. ORDER BY的语句结构
   ###语法结构###

SELECT column1, column2, ... FROM table_name ORDER BY column1, column2, ... ASC|DESC;

1.2 用法

1.2.1 升序

mysql> select * from info order by score;
+----+----------+-------+-------+------+
| id | name     | score | hobby | addr |
+----+----------+-------+-------+------+
|  4 | kb       |  8.00 | 2     | NULL |
|  3 | ks       | 25.00 | 1     | NULL |
|  5 | ooxoo    | 55.00 | 3     | NULL |
|  6 | ooxooo   | 66.00 | 3     | NULL |
|  1 | zhangsan | 77.00 | 1     | NULL |
|  2 | lisi     | 88.00 | 2     | NULL |
+----+----------+-------+-------+------+
6 rows in set (0.00 sec)

1.2.2 降序

mysql> select * from info order by score;
+----+----------+-------+-------+------+
| id | name     | score | hobby | addr |
+----+----------+-------+-------+------+
|  4 | kb       |  8.00 | 2     | NULL |
|  3 | ks       | 25.00 | 1     | NULL |
|  5 | ooxoo    | 55.00 | 3     | NULL |
|  6 | ooxooo   | 66.00 | 3     | NULL |
|  1 | zhangsan | 77.00 | 1     | NULL |
|  2 | lisi     | 88.00 | 2     | NULL |
+----+----------+-------+-------+------+
6 rows in set (0.00 sec)

1.2.3 多字段降序排序

mysql> select id,name from info where 3=3 order by score desc,hobby desc;
+----+----------+
| id | name     |
+----+----------+
|  2 | lisi     |
|  1 | zhangsan |
|  6 | ooxooo   |
|  5 | ooxoo    |
|  3 | ks       |
|  4 | kb       |
+----+----------+
6 rows in set (0.00 sec)

1.2.4 对结果进行分组

1. GROUP BY语句来实现分组
2. 结合聚合函数一起使用
3. 以按一个或多个字段对结果进行分组
4. GRO BY的语法结构
   ###语法结构###

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

分组

mysql> select count(name),hobby nfo group by hobby;   ###统计hobby中相同的输出name的个数和hobby
+-------------+-------+
| count(name) | score |
+-------------+-------+
|           2 | 77.00 |
|           2 | 88.00 |
|           2 | 55.00 |
+-------------+-------+
3 rows in set (0.00 sec)

GROUP BY结合ORDER BY

mysql> select count(name),hobby from info group by hobby order by count(name);
+-------------+-------+
| count(name) | hobby |
+-------------+-------+
|           2 | 2     |
|           2 | 3     |
|           2 | 1     |
+-------------+-------+
3 rows in set (0.00 sec)

1.2.5 限制结果条目

1. 回SELECT查询结果的第一行或前几行
2. LIMIT语句限制条目
3. IMIT语法结构
   ###语法结构###

SELECT column1, column2, ... FROM table_name LIMIT [offset,] number  ###返回记录行的最大数目
[offset,] ：位置偏移量，从0开始算

查看前三行

mysql> select * from info limit 3;
+----+----------+-------+-------+------+
| id | name     | score | hobby | addr |
+----+----------+-------+-------+------+
|  1 | zhangsan | 77.00 | 1     | NULL |
|  2 | lisi     | 88.00 | 2     | NULL |
|  3 | ks       | 25.00 | 1     | NULL |
+----+----------+-------+-------+------+
3 rows in set (0.00 sec)

查看2-4行

mysql> select * from info limit 1,3;
+----+------+-------+-------+------+
| id | name | score | hobby | addr |
+----+------+-------+-------+------+
|  2 | lisi | 88.00 | 2     | NULL |
|  3 | ks   | 25.00 | 1     | NULL |
|  4 | kb   |  8.00 | 2     | NULL |
+----+------+-------+-------+------+
3 rows in set (0.00 sec)

1.2.6 设置别名

1.使用AS语句设置别名，关键字AS可省略
2. 设置别名时，保证不能与库中其他表或字段名称冲突
3. 别名的语法结构
###语法结构###

SELECT column_name AS alias_name FROM table_name;

###给name和score设置别名### 方法一

mysql> select name as 姓名,score as 成绩 from info;
+----------+--------+
| 姓名     | 成绩   |
+----------+--------+
| zhangsan |  77.00 |
| lisi     |  88.00 |
| ks       |  25.00 |
| kb       |   8.00 |
| ooxoo    |  55.00 |
| ooxooo   |  66.00 |
+----------+--------+
6 rows in set (0.01 sec)

###给name和score设置别名### 方法二

mysql> select name 姓名,score 成绩 from info;
+----------+--------+
| 姓名     | 成绩   |
+----------+--------+
| zhangsan |  77.00 |
| lisi     |  88.00 |
| ks       |  25.00 |
| kb       |   8.00 |
| ooxoo    |  55.00 |
| ooxooo   |  66.00 |
+----------+--------+
6 rows in set (0.00 sec)

给表设置别名i

mysql> select i.name 姓名,i.score 成绩 from info as i;
+----------+--------+
| 姓名     | 成绩   |
+----------+--------+
| zhangsan |  77.00 |
| lisi     |  88.00 |
| ks       |  25.00 |
| kb       |   8.00 |
| ooxoo    |  55.00 |
| ooxooo   |  66.00 |
+----------+--------+
6 rows in set (0.00 sec)

创建新表，将info表中的分数大于等于80的数据放在新表上

mysql> create table tmp as select * from info where score >= 80;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0
mysql> select * from tmp;
+----+------+-------+-------+------+
| id | name | score | hobby | addr |
+----+------+-------+-------+------+
|  2 | lisi | 88.00 | 2     | NULL |
+----+------+-------+-------+------+
1 row in set (0.00 sec)

注意点：这边只复制数据和表结构，但是表的约束并不会复制过去

1.3 通配符

1. 用于替换字符串的部分字符
2. 通常配合LIKE一起使用，并协同WHERE完成查询
3. 常用的通配符
4. %表示零个、一个或多个
5. _表示单个字符

mysql> select * from info where name like 'l% ; ##匹配l0次或者多次
+----+------+-------+-------+------+
| id | name | score | hobby | addr |
+----+------+-------+-------+------+
|  2 | lisi | 88.00 | 2     | NULL |
+----+------+-------+-------+------+
1 row in set (0.00 sec)

mysql> select * from info where name like '_i_i'; ###_ 任意一个字符
+----+------+-------+-------+------+
| id | name | score | hobby | addr |
+----+------+-------+-------+------+
|  2 | lisi | 88.00 | 2     | NULL |
+----+------+-------+-------+------+
1 row in set (0.00 sec)

1.4 子查询

1. 也称作内查询或者嵌套查询
2. 先于主查询被执行，其结果将作为外层主查询的条件
3. 在增删改查中都可以使用子查询
4. 支持多层嵌套
5. IN语句是用来判断某个值是否在给定的结果集中

准备工作

mysql> create table num (id int (4));  ##创建表
Query OK, 0 rows affected (0.01 sec)
mysql> insert into num values (1),(3),(5),(7);  #插入数据
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0
mysql> select * from num;   ##查看表内容
+------+
| id   |
+------+
|    1 |
|    3 |
|    5 |
|    7 |
+------+
4 rows in set (0.00 sec)

###然后根据刚刚的info表和num表进行多表相连，按照num表的1、3、5、7显示出info表的1、3、5、7行数据###
###后面输出的结果赋予了前面的值###

mysql> select * from info where id in(select id from num);  ##括号里面的id对应外面条件id，对应上的输出结果
+----+----------+-------+-------+------+
| id | name     | score | hobby | addr |
+----+----------+-------+-------+------+
|  1 | zhangsan | 77.00 | 1     | NULL |
|  3 | ks       | 25.00 | 1     | NULL |
|  5 | ooxoo    | 55.00 | 3     | NULL |
+----+----------+-------+-------+------+
3 rows in set (0.00 sec)

多层嵌套，先内括号一步步到外括号

mysql> select * from info where id in(select id from num where name in (select name from num));
+----+----------+-------+-------+------+
| id | name     | score | hobby | addr |
+----+----------+-------+-------+------+
|  1 | zhangsan | 77.00 | 1     | NULL |
|  3 | ks       | 25.00 | 1     | NULL |
|  5 | ooxoo    | 55.00 | 3     | NULL |
+----+----------+-------+-------+------+
3 rows in set (0.00 sec)

注意点：外层的条件要和内存的类型是相同的

1.5 视图

数据库中的虚拟表，这张虚拟表中不包含任何数据，只是做了数据映射

mysql> create view v_score as select * from info where score >=80;   ##创建视图，并且将info表中大于80分的移进视图表中
Query OK, 0 rows affected (0.00 sec)
mysql> select * from v_score;
+----+------+-------+-------+------+
| id | name | score | hobby | addr |
+----+------+-------+-------+------+
|  2 | lisi | 88.00 | 2     | NULL |
+----+------+-------+-------+------+
1 row in set (0.00 sec)
mysql> show table status;   ###查看视图表信息

注意点：视图表是一个虚拟的动态的，当info表中有同学的分数变成80以上时，会自动更新数据

拓展

mysql> select id from (select id,name from info) ;
ERROR 1248 (42000): Every derived table must have its own alias    ##错误1248(42000):每个派生表必须有自己的别名
解决方法，设置别名 
```bash
mysql