MYSQL高级用法 mysql高级查询语句总结

这里写目录标题

• 一，mysql进阶查询

• 1，按关键字排序

• （1）按单字段排序
• （2）按多字段排序

• 2，按结果进行分组

• （1）group by 分组
• （2）group by 结合order by

• 3,限制结果条目

• 二，MYSQL存储过程

• 1， 通配符
• 2，子查询
• 3，NULL值
• 4，正则表达式
• 5，连接查询
• 6，字符串函数

• 三，存储过程简介

• 1，创建存储过程
• 2， 删除存储过程
• 3，修改存储过程

一，mysql进阶查询

1，按关键字排序

（1）按单字段排序

• 使用DRDER NY语句来实现排序
• 排序可针对一个或者多个字段
• ASC；升序，默认排序方式
• DESC；降序
• DRDER BY 的语法结构

select column1,column2... from 库名 order by column1,column2,... asc|desc;

mysql> create database lihuiuhi;
mysql> create table test(xuehao char(10) not null,nianling int(3) not null,xingming char(36) not null,chengji varchar(3) not null);
mysql> insert into test values(201001,17,'zhangsan',60),(201002,18,'lisi',70),(201003,19,'wangwu',80),(201004,18,'zhaoliu',88),(201005,17,'lilei',55);

默认升序
mysql> select chengji from test order by chengji;
降序
mysql> select chengji from test order by chengji desc;
加入条件的排序
mysql> select xingming,chengji from test where chengji>=60 order by chengji desc;
多条件的排序
mysql> select * from test order by nianling desc,chengji desc;

（2）按多字段排序

按照年龄

2，按结果进行分组

（1）group by 分组

• 使用GROUP BY 语句来实现分组
• 通常结合聚合函数一起使用
• 可以按一个或多个字段对结果进行分组
• group by 的语法结构
  例如·：

select column_name,aggregate_function(column_name) from table_name where column_name operator value GROUP BY column_name;

mysql> select count(xingming),chengji from test where chengji>=60 group by nianling;
+-----------------+---------+
| count(xingming) | chengji |
+-----------------+---------+
|               1 | 60      |
|               2 | 70      |
|               1 | 80      |
+-----------------+---------+
3 rows in set (0.01 sec)

mysql> select count(xingming),chengji from test where chengji>=50 group by nianling order by count(xingming) desc;
+-----------------+---------+
| count(xingming) | chengji |
+-----------------+---------+
|               2 | 70      |
|               2 | 60      |
|               1 | 80      |
+-----------------+---------+
3 rows in set (0.00 sec)

（2）group by 结合order by

3,限制结果条目

• 只返回select查询结果的第一行做着前几行
• 使用Limit语句限制条目
• Limit语法结构
  例如：

select column1,column2,... from table_name [offset,] number;

mysql> select * from test;
+--------+----------+----------+---------+
| xuehao | nianling | xingming | chengji |
+--------+----------+----------+---------+
| 201001 |       17 | zhangsan | 60      |
| 201002 |       18 | lisi     | 70      |
| 201003 |       19 | wangwu   | 80      |
| 201004 |       18 | zhaoliu  | 88      |
| 201005 |       17 | lilei    | 55      |
| 201006 |       18 | lili     | 90      |
+--------+----------+----------+---------+
6 rows in set (0.00 sec)

前三个
mysql> select * from test limit 3;
+--------+----------+----------+---------+
| xuehao | nianling | xingming | chengji |
+--------+----------+----------+---------+
| 201001 |       17 | zhangsan | 60      |
| 201002 |       18 | lisi     | 70      |
| 201003 |       19 | wangwu   | 80      |
+--------+----------+----------+---------+
3 rows in set (0.00 sec)

第一个数字：位置偏移量是从零开始，第二个数字：返回记录行的最大数目
第三到第五
mysql> select * from test limit 2,3;
+--------+----------+----------+---------+
| xuehao | nianling | xingming | chengji |
+--------+----------+----------+---------+
| 201003 |       19 | wangwu   | 80      |
| 201004 |       18 | zhaoliu  | 88      |
| 201005 |       17 | lilei    | 55      |
+--------+----------+----------+---------+
3 rows in set (0.00 sec)

二，MYSQL存储过程

1， 通配符

• 通配符%的用法

• 用于替换字符串中的部分字符
• 通常配合LIKE一起使用，并协同WHERE完成查询
• 常用通配符
  %表示零个，一个或多个， _表示单个字符
  例如：

mysql> select xingming,chengji from test where xingming like '%n';
+----------+---------+
| xingming | chengji |
+----------+---------+
| zhangsan | 60      |
+----------+---------+
1 row in set (0.00 sec)

mysql> select * from test where xingming like 'z%';
mysql> select * from test where xingming like 'zhang_an';
mysql> select * from test where xingming like 'l_s_';

两者结合
mysql> select * from test where xingming like '%sa_';

2，子查询

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

mysql> select xuehao as 学号,xingming as 姓名,chengji as 成绩 from test where chengji in (select chengji from test where chengji>=60);
+--------+----------+--------+
| 学号   | 姓名     | 成绩   |
+--------+----------+--------+
| 201001 | zhangsan | 60     |
| 201002 | lisi     | 70     |
| 201003 | wangwu   | 80     |
| 201004 | zhaoliu  | 88     |
| 201006 | lili     | 90     |
+--------+----------+--------+
5 rows in set (0.00 sec)
降序：
mysql> select xuehao as 学号,xingming as 姓名,chengji as 成绩 from test where chengji in (select chengji from test where chengji>=60 ) order by chengji desc;

mysql> create table source as select * from test;
mysql> delete from source;
mysql> select * from source;
Empty set (0.00 sec)

mysql> insert into source select * from test where chengji in (select chengji from test where chengji>=80);
mysql> select * from source;
+--------+----------+----------+---------+
| xuehao | nianling | xingming | chengji |
+--------+----------+----------+---------+
| 201003 |       19 | wangwu   | 80      |
| 201004 |       18 | zhaoliu  | 88      |
| 201006 |       18 | lili     | 90      |
+--------+----------+----------+---------+
3 rows in set (0.00 sec)

修改:
mysql> alter table source add column num int(3);	#添加一列num
mysql> desc source;			#查表的结构
mysql> update source set num=101 where chengji in (select chengji from test where chengji >=80);
mysql> select * from source;
+--------+----------+----------+---------+------+
| xuehao | nianling | xingming | chengji | num  |
+--------+----------+----------+---------+------+
| 201003 |       19 | wangwu   |      80 |  101 |
| 201004 |       18 | zhaoliu  |      88 |  101 |
| 201006 |       18 | lili     |      90 |  101 |
+--------+----------+----------+---------+------+
3 rows in set (0.00 sec)

mysql> select * from (select * from test where chengji>=75)as a;
mysql> select * from (select * from test where chengji>=75)a;
mysql> select * from (select * from test where chengji>=75)a order by chengji desc;
mysql> delete from test where chengji in(select chengji from (select * from test where chengji >=75)a);
mysql> select * from test;
+--------+----------+----------+---------+
| xuehao | nianling | xingming | chengji |
+--------+----------+----------+---------+
| 201001 |       17 | zhangsan |      60 |
| 201002 |       18 | lisi     |      70 |
| 201005 |       17 | tianqi   |      55 |
+--------+----------+----------+---------+
3 rows in set (0.00 sec)

mysql> insert into source values(201008,18,'zhangsan',89,102);
后面条件为真则执行前面的
mysql> select count(num) from source where exists(select num from source where xingming='zhangsan');

###子查询的用法

3，NULL值

• 表示缺失的值
• 与数字0或者空白是不同的
• 使用IS NULL或IS NOT NULL进行判断
• NULL值和空值的区别

• 空值长度为0，不占空间，NULL值的长度为NULL，占用空间
• IS NULL无法判断空值
• 控制使用“=”或者“<>”来处理
• COUNT（）计算时，NULL会忽略，空值会加入计算

插入空值
mysql> alter table chengji add column class varchar(25);
mysql> select * from chengji;
mysql> insert into chengji values(6,20,'liuliu',83,null,3);
mysql> select * from chengji;
+----+-----+----------+--------+------+-------+
| id | age | name     | scrore | num  | class |
+----+-----+----------+--------+------+-------+
|  1 |  17 | zhangsan |   60.0 | NULL | NULL  |
|  2 |  18 | lisi     |   70.0 | NULL | NULL  |
|  3 |  18 | wangwu   |   80.0 | NULL | NULL  |
|  4 |  17 | zhaoliu  |   95.0 | NULL | NULL  |
|  5 |  19 | tianqi   |   55.0 | NULL | NULL  |
|  6 |  20 | liuliu   |   83.0 | NULL | 3     |
+----+-----+----------+--------+------+-------+
6 rows in set (0.00 sec)

null用法
mysql> select * from chengji where class is null;
+----+-----+----------+--------+------+-------+
| id | age | name     | scrore | num  | class |
+----+-----+----------+--------+------+-------+
|  1 |  17 | zhangsan |   60.0 | NULL | NULL  |
|  2 |  18 | lisi     |   70.0 | NULL | NULL  |
|  3 |  18 | wangwu   |   80.0 | NULL | NULL  |
|  4 |  17 | zhaoliu  |   95.0 | NULL | NULL  |
|  5 |  19 | tianqi   |   55.0 | NULL | NULL  |
+----+-----+----------+--------+------+-------+
5 rows in set (0.01 sec)

mysql> select * from chengji where class is not null;
+----+-----+--------+--------+------+-------+
| id | age | name   | scrore | num  | class |
+----+-----+--------+--------+------+-------+
|  6 |  20 | liuliu |   83.0 | NULL | 3     |
+----+-----+--------+--------+------+-------+
1 row in set (0.00 sec)

4，正则表达式

• 根据指定的匹配模式匹配记录中符合要求的特殊字符
• 使用regexp关键字指定匹配模式
• 常用匹配模式

• ^匹配开始字符
• ￥匹配结束字符
• . 匹配任意单个字符
• *匹配任意个前面字符
• +匹配前面字符至少一次
• pi|p2 匹配p1或p2

内连接：找两个表中的公共部分
外连接：分为左连接和右连接

mysql> select * from test where xingming regexp '^z';		以什么开头
mysql> select * from test where xingming regexp 'i$';		以什么结尾
mysql> select * from test where xingming regexp 'zha';		
mysql> select * from test where xingming regexp 'zha.gsan';	匹配单个字符
mysql> select * from test where xingming regexp '^[z|l]';	以z或l开头
mysql> select * from test where xingming regexp '^[^z|l]';	不以z或l开头
mysql> select * from test where xingming regexp 'e{3,5}';
mysql> select * from test where xingming regexp 'ee*';
mysql> select * from test where xingming regexp '^[d-f]';

5，连接查询

mysql> create table chengji(id int(3) not null primary key auto_increment,age int(3) not null,name varchar(64) not null,scrore decimal(4,1));
mysql> insert into chengji(age,name,scrore) values(17,'zhangsan',60),(18,'lisi',70),(18,'wangwu',80),(17,'zhaoliu',95),(19,'tianqi',55);
mysql> select * from chengji;
+----+-----+----------+--------+
| id | age | name     | scrore |
+----+-----+----------+--------+
|  1 |  17 | zhangsan |   60.0 |
|  2 |  18 | lisi     |   70.0 |
|  3 |  18 | wangwu   |   80.0 |
|  4 |  17 | zhaoliu  |   95.0 |
|  5 |  19 | tianqi   |   55.0 |
+----+-----+----------+--------+
5 rows in set (0.00 sec)

mysql> create table sushe(id int(3) not null primary key,num int(3) not null);
mysql> insert into sushe values(1,305),(2,306),(3,307),(8,308),(9,309),(10,310);
mysql> select * from sushe;
+----+-----+
| id | num |
+----+-----+
|  1 | 305 |
|  2 | 306 |
|  3 | 307 |
|  8 | 308 |
|  9 | 309 |
| 10 | 310 |
+----+-----+
6 rows in set (0.00 sec)

内连接
mysql> select * from chengji c inner join sushe s on c.id=s.id;
+----+-----+----------+--------+----+-----+
| id | age | name     | scrore | id | num |
+----+-----+----------+--------+----+-----+
|  1 |  17 | zhangsan |   60.0 |  1 | 305 |
|  2 |  18 | lisi     |   70.0 |  2 | 306 |
|  3 |  18 | wangwu   |   80.0 |  3 | 307 |
+----+-----+----------+--------+----+-----+
3 rows in set (0.00 sec)

左连接
mysql> select * from chengji c left join sushe s on c.id=s.id;
+----+-----+----------+--------+------+------+
| id | age | name     | scrore | id   | num  |
+----+-----+----------+--------+------+------+
|  1 |  17 | zhangsan |   60.0 |    1 |  305 |
|  2 |  18 | lisi     |   70.0 |    2 |  306 |
|  3 |  18 | wangwu   |   80.0 |    3 |  307 |
|  4 |  17 | zhaoliu  |   95.0 | NULL | NULL |
|  5 |  19 | tianqi   |   55.0 | NULL | NULL |
+----+-----+----------+--------+------+------+
5 rows in set (0.00 sec)

右连接
mysql> select * from chengji c right join sushe s on c.id=s.id;
+------+------+----------+--------+----+-----+
| id   | age  | name     | scrore | id | num |
+------+------+----------+--------+----+-----+
|    1 |   17 | zhangsan |   60.0 |  1 | 305 |
|    2 |   18 | lisi     |   70.0 |  2 | 306 |
|    3 |   18 | wangwu   |   80.0 |  3 | 307 |
| NULL | NULL | NULL     |   NULL |  8 | 308 |
| NULL | NULL | NULL     |   NULL |  9 | 309 |
| NULL | NULL | NULL     |   NULL | 10 | 310 |
+------+------+----------+--------+----+-----+
6 rows in set (0.00 sec)

6，字符串函数

• 常用的字符串函数

• length(x)返回字符串x的长度
• trim() 返回去除指定格式的值
• concat(x,y)将提供的参数x和y拼接成一个字符串
• upper(x)将字符串x的所有字母变成大写字母
• lower(x)将字符串x的所有字母变成小写字母
• left(x.y）返回字符串x的前y个字符
• right(x,y）返回字符串x的后y个字符
• repeat（x,y）将字符串x重复y次
• space(x）返回x个空格
• replace（x.y.z）将字符串z替代字符串x中的字符串y
• strcmp(x,y)比较x和y，返回的值可以为-1,0,1
• substring(x,y,z）获取从字符串x中的第y个位置开始长度为z的字符串
• reverse（x）将字符串x反转

三，存储过程简介

• 简介

• 是一组为了完成特定功能的SQL语句集合
• 比传统SQL速度更快，执行效率更高

• 存储过程的优点

• 执行一次后，会将生成的二进制代码驻留缓冲区，提高执行效率
• SQL语句加上控制语句的集合，灵活性高
• 在服务器端存储，客户端调用，降低网络负载
• 可多次重复被调用，可随时修改，不影响客户端调用
• 可完成所有数据库操作，也可控制数据库的信息访问权限

• 创建存储过程

• 使用CREATE PROCEDURE语句创建存储过程
• 创建存储过程的语法结构
• CREATE PROCEDURE <过程名> （过程参数[…]）<过程体> [过程参数[…]] 格式 [IN|OUT|INOUT] <参数名> <类型>

• 参数分为

• 输入参数：IN
• 输出参数：OUT
• 输入/输出参数：INOUT

• 存储过程的主体部分，被称为过程体
• .以BEGIN开始，以END结束，若只有一条SQL语句
• 以DELIMITER开始和结束

1，创建存储过程

• 存储过程的主体部分，被称为过程体
• 以begin开始，以end结束，若只有一条SQL语句，则可以省略begin-end

• 以delimiter开始和结束

mysql> delimiter $$
mysql> create procedure a()
    -> begin
    -> select * from chengji limit 3;
    -> end $$
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> call a();
+----+-----+----------+--------+------+
| id | age | name     | scrore | num  |
+----+-----+----------+--------+------+
|  1 |  17 | zhangsan |   60.0 | NULL |
|  2 |  18 | lisi     |   70.0 | NULL |
|  3 |  18 | wangwu   |   80.0 | NULL |
+----+-----+----------+--------+------+
3 rows in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

• 不带参数的存储过程

mysql> delimiter $$
mysql> create procedure p(in num1 int,out num2 int,inout num3 int)
    -> begin
    -> select num1,num2,num3;
    -> set num1=10,num2=20,num3=30;
    -> select num1,num2,num3;
    -> end $$
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql> call p(@num1,@num2,@num3);
+------+------+------+
| num1 | num2 | num3 |
+------+------+------+
|    1 | NULL |    3 |
+------+------+------+
1 row in set (0.00 sec)

+------+------+------+
| num1 | num2 | num3 |
+------+------+------+
|   10 |   20 |   30 |
+------+------+------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)
in和inout参数会将全局变量的值传入存储过程中，而out参数不会将全局变量的值传入存储过程中，在全局过程使用中，参数值in、out、inout都会发生改变


mysql> select @num1,@num2,@num3;
+-------+-------+-------+
| @num1 | @num2 | @num3 |
+-------+-------+-------+
|     1 |    20 |    30 |
+-------+-------+-------+
1 row in set (0.00 sec)
调用完存储过程后，发现in参数不会对全局变量的值引起变化，而out和inout参数调用完存储过程后，会对全局变量的值产生变化，会将存储过程引用后的值赋值给全局变量，in参数赋值类型可以是变量还有定值，而out和inout参数赋值类型必须是变量

2， 删除存储过程

1、删除存储过程的语法
	DROP { PROCEDURE | FUNCTION } [ IF EXISTS ] <过程名>
2、删除的过程
mysql> drop procedure a;
mysql> call a();
ERROR 1305 (42000): PROCEDURE score.a does not exist

3，修改存储过程

1、存储过程的修改分为特征修改和内容修改
2、特征修改的方法
	ALTER PROCEDURE <过程名> [<特征>...]
3、内容修改可先删除原有存储过程，之后再创建方法