mysql高级查询语句

一、按关键字排序

1、使用ORDER BY语句来实现排序
2、排序可针对一个或多个字段
3、ASC：升序，默认排序方式
4、DESC：降序
5、ORDER BY的语法结构
select column1，column2，… from 库名 order by column1,column,… asc|desc;

语句使用
mysql -uroot -p123123
mysql> create database score;
mysql> use score;
mysql> create table test(xuehao int(6) not null primary key,nianling int(3) not null,xingming char(20) not null,chengji int(3) not null);
mysql> insert into test values(201001,17,‘zhangsan’,60),(201002,17,‘zhaoliu’,95),(201003,18,‘lisi’,70),(201004,18,‘wangwu’,80),(201005,19,‘tianqi’,55);
mysql> select * from test;

升序
mysql> select chengji from test order by chengji asc;

mysql> select chengji from test order by chengji; #默认是ASC

降序
mysql> select chengji from test order by chengji desc;

按单字段排序
mysql> select xuehao,xingming,chengji from test order by chengji;

按多字段排序
mysql> select xingming,chengji from test order by nianling desc,chengji desc;

二、对结果进行分组

1、使用GROUP BY语句来实现分组
2、通常结合聚合函数一起使用
3、可以按一个或多个字段对结果进行分组
4、GROUP BY分组

mysql> insert into test values(201006,18,‘zhangsan’,80),(201007,19,‘lisi’,70);

mysql> select * from test;

mysql> select count(xingming),nianling from test group by nianling;

GROUP BY结合ORDER BY
mysql> select count(xingming),nianling from test group by nianling order by nianling desc;

三、限制结果条目

1、只返回select查询结果的第一行或第几行
2、使用limit语句限制条目
3、limit语法结构
select column1，column2，… from 库名 limit 位置偏移量

mysql> select * from test limit 3;

mysql> select * from test limit 3,3;

四、设置别名

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

mysql> select t.xuehao as 学号,t.nianling as 年龄,t.xingming as 姓名,t.chengji as 成绩 from test as t;
4.4、as作为连接语句

mysql> create table test1 as select * from test;

mysql> select * from test1;

五、通配符的使用

1、用于替换字符串中的部分字符
2、通常配合like一起使用，并协同where完成查询
3、常用通配符

1. %：表示0个，1个或多个
2. _：表示单个字符

mysql> select xuehao,xingming from test where xingming like ‘z%’;

mysql> select xuehao,xingming from test where xingming like ‘lis_’;

六、子查询

1、也称作内查询或者嵌套查询
2、先于主查询被执行，其结果将作为外层查询的条件
3、在增删改查中都可以使用子查询
4、支持多层嵌套
5、IN语句是用来判断某个值是否在给定的结果集中
6、子查询的用法
查询：
mysql> select xuehao as 学号,chengji as 成绩 from test where chengji in (select chengji from test where chengji >=60);

查询结合降序使用：
mysql> select xuehao as 学号,chengji as 成绩 from test where chengji in (select chengji from test where chengji >=60) order by chengji desc;

插入：
mysql> create table test_ as select * from score;
mysql> delete from test_;
mysql> select * from test_;
mysql> insert into test_ select * from test where chengji in (select chengji from test where chengji >=80);
mysql> select * from test_;

修改：、
mysql> alter table test_ add column num int(3);
mysql> desc test_ ;
mysql> update test_ set num=101 where chengji in (select chengji from test where chengji >=80);
mysql> select * from test_;

删除：
mysql> delete from test where chengji in(select chengji from (select *from test where chengji >=75)a);
mysql> select * from test;

七、NULL值

1、表示缺失的值
2、与数字0或者空白（spaces）是不同的
3、使用IS NULL或IS NOT NULL进行判断
4、NULL值和空值的区别

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

插入空值：
mysql> alter table test_ add column class varchar(16);
mysql> select * from test_;
mysql> insert into test_ values(201007,19,‘lisi’,80,102,’’);
mysql> select * from test_;

null的用法：
mysql> select * from test_ where class is null ;

八、正则表达式

1、根据指定的匹配模式匹配记录中符合要求的特殊字符
2、使用REGEXP关键字指定匹配模式
3、常用匹配模式
字符 说明
^ 匹配开始字符
$ 匹配结束字符
. 匹配任意单个字符
以z开头的姓名：(^)
mysql> select xuehao,xingming,chengji from test_ where xingming regexp ‘^z’;

以n结尾的姓名：( ) m y s q l > s e l e c t x u e h a o , x i n g m i n g , c h e n g j i f r o m t e s t w h e r e x i n g m i n g r e g e x p ′ n ) mysql> select xuehao,xingming,chengji from test_ where xingming regexp 'n )mysql>selectxuehao,xingming,chengjifromtestw​herexingmingregexp′n’;

匹配单个字符（.）
mysql> select xuehao,xingming,chengji from test_ where xingming regexp ‘lis.’;

匹配前面字符至少1次（+）
mysql> insert into test_ values(201008,20,‘lio’,75,103,’’),(201009,20,‘lioo’,85,104,’’),(201009,20,‘liooo’,55,105,’’);
mysql> select xuehao,xingming,chengji from test_ where xingming regexp ‘lioo+’;

匹配任意个前面的字符()
mysql> select xuehao,xingming,chengji from test_ where xingming regexp 'lio’;

匹配p1或p2(p1|p2)
mysql> select xuehao,xingming,chengji from test_ where xingming regexp ‘l|n’;

匹配字符集中括号内的任何字符([…])
mysql> select xuehao,xingming,chengji from test_ where xingming regexp ‘[hz]’;

匹配前面的字符串n次{n}
mysql> select xuehao,xingming,chengji from test_ where xingming regexp ‘o{2}’;

匹配前面的字符串至少n次，至多m次({n,m})
mysql> select xuehao,xingming,chengji from test_ where xingming regexp ‘o{2,3}’;

mysql> select 5+2 as addition,8-5 as subtraction,6*7 as multiplication,8/2 as division,9%4 as remainder;

九、算术运算符

1、MySQL支持的算术运算符
2、比较运算符

1. 字符串的比较默认不区分大小写，可使用binary来区分
2. 常用比较运算符
   
   mysql> select 2=4,2=‘2’,‘e’=‘e’,(4+4)=(5+3),‘n’=NULL;

从以上查询可以看出：
①如果两者都是整数，则按整数值进行比较
②如果一个整数一个字符串，则会自动将字符串转换为数字，再进行比较
③如果两者都是字符串，则按照字符串进行比较
④如果两者中至少有一个值是NULL，则比较的结果是NULL

mysql> select 2>4,2<‘2’, ‘e’>=‘e’,(4+4)<=(5+3);

mysql> select 2!=4,null is null,null is not null,2 between 1 and 4;

mysql> select greatest (5,8,12),least (1,5,4);

十、逻辑运算符

1、又称为布尔运算符
2、用来判断表达式的真假
3、常用的逻辑运算符
逻辑非
mysql> select not 2,!3,not 0,!(4-4);

逻辑与
mysql> select 2 and 3,4 && 0,0 && NULL,1 and NULL;

十一、位运算符

1、对二进制数进行计算的运算符
2、常用的位运算符

mysql> select 4&5,4|5,4&~3,3^4,2<<2,2>>1;

十二、连接查询

    MySQL的连接查询，通常都是将来自两个或多个表的行结合起来，基于这些表之间的共同字段，进行数据的拼接，首先，要确定一个主表作为结果集，然后将其他表的行有选择性的连接到选定的主表结果集上，使用较多的连接查询包括：内连接、左连接和右连接

1、内连接
mysql> select t.xuehao,t.xingming,t.chengji from test_ t inner join test t1 on t.xingming=t1.xingming;
2、左连接
mysql> select t.xuehao,t.xingming,t.chengji from test_ t left join test t1 on t.xingming=t1.xingming;
3、右连接
mysql> select t.xuehao,t.xingming,t.chengji from test_ t right join test t1 on t.xingming=t1.xingming;

十三、函数

1、数学函数
常用的数学函数举例说明 1 mysql> select abs(-12),rand(),rand(),mod(4,5),power(2,6);

mysql> select round(2.4),round(2.5),round(2.4235,2),sqrt(2),truncate(2.4652,2);

mysql> select ceil(2.2),floor(2.8),greatest(1,2,3,4,5),least(1,2,3,4,5);
2、聚合函数：对表中数据记录进行集中概括而设计的一类函数

聚合函数举例
mysql> select avg(chengji) from test;

mysql> select sum(chengji) from test;

mysql> select min(chengji) from test;

mysql> select max(chengji) from test;

mysql> alter table test add sex char(2);
mysql> select * from test;

mysql> update test set sex=‘男’ where xingming=‘lisi’;
mysql> update test set sex=‘男’ where xingming=‘zhangsan’;
mysql> select * from test;

mysql> select count(sex) from test;

3、字符串函数
举例说明
mysql> select length(‘abc’),trim( ‘ab’ ),concat(‘ab’,‘cd’),upper(‘abc’),lower(‘ABC’);

mysql> select left(‘abcd’,2),right(‘abcd’,2),repeat(‘abc’,3);

mysql> select space(2),replace(‘abcde’,‘e’,‘g’),strcmp(‘a’,‘b’),strcmp(‘a’,‘a’),strcmp(‘b’,‘a’);

mysql> select substring(‘abcdefg’,3,3),reverse(‘abcdefg’);

4、日期时间函数
mysql> select curdate(),curtime(),now(),month(‘2020-08-15’),week(‘2020-08-15’);

mysql> select hour(‘20:15:45’),minute(‘20:15:45’),second(‘20:15:45’);

mysql> select dayofweek(‘2020-08-15’),dayofmonth(‘2020-08-15’),dayofyear(‘2020-08-15’);

十四、存储过程

1、简介
1.1、是一组为了完成特定功能的SQL语句集合
1.2、比传统的SQL速度更快、执行效率更高
1.3、存储过程的优点
①执行一次后，会将生成的二进制代码驻留缓冲区，提高执行效率
②SQL语句加上控制语句的集合，灵活性高
③在服务器端存储，客户端调用时，降低网络负载
④可多次重复被调用，可随时修改，不影响客户端调用
⑤可完成所有的数据库操作，也可控制数据库的信息访问权限
2、创建存储过程
2.1、使用CREATE PROCEDURE语句创建存储过程
2.2、创建存储过程的语法结构

CREATE PROCEDURE <过程名> （过程参数[…]）<过程体> [过程参数[…]] 格式 [IN|OUT|INOUT] <参数名> <类型>

3、参数分为
3.1、输入参数：IN
3.2、输出参数：OUT
3.3、输入/输出参数：INOUT
4、存储过程的主体部分，被称为过程体
5、以BEGIN开始，以END结束，若只有一条SQL语句
6、以DELIMITER开始和结束
7、存储过程

mysql> delimiter m y s q l > c r e a t e p r o c e d u r e a ( ) − > b e g i n − > s e l e c t ∗ f r o m t e s t l i m i t 3 ; − > e n d mysql> create procedure a() -> begin -> select * from test limit 3; -> end mysql>createprocedurea()−>begin−>select∗fromtestlimit3;−>end

mysql> delimiter ;
mysql> call a();

mysql> use score;
mysql> set @num1=1,@num2=2,@num3=3;
mysql> delimiter m y s q l > c r e a t e p r o c e d u r e p ( i n n u m 1 i n t , o u t n u m 2 i n t , i n o u t n u m 3 i n t ) − > b e g i n − > s e l e c t n u m 1 , n u m 2 , n u m 3 ; − > s e t n u m 1 = 10 , n u m 2 = 20 , n u m 3 = 30 ; − > s e l e c t n u m 1 , n u m 2 , n u m 3 ; − > e n d 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 mysql>createprocedurep(innum1int,outnum2int,inoutnum3int)−>begin−>selectnum1,num2,num3;−>setnum1=10,num2=20,num3=30;−>selectnum1,num2,num3;−>end

mysql> delimiter ;
mysql> call p(@num1,@num2,@num3);

总结1：in和inout参数会将全局变量的值传入存储过程中，而out参数不会将全局变量的值传入存储过程中，在全局过程使用中，参数值in、out、inout都会发生改变

mysql> select @num1,@num2,@num3;

总结2：调用完存储过程后，发现in参数不会对全局变量的值引起变化，而out和inout参数调用完存储过程后，会对全局变量的值产生变化，会将存储过程引用后的值赋值给全局变量，in参数赋值类型可以是变量还有定值，而out和inout参数赋值类型必须是变量

8、修改存储过程
8.1、存储过程的修改分为特征修改和内容修改
8.2、特征修改的方法
ALTER PROCEDURE <过程名> [<特征>…]
8.3、内容修改可先删除原有存储过程，之后再创建方法
9、删除存储过程
9.1、删除存储过程的语法
DROP { PROCEDURE | FUNCTION } [ IF EXISTS ] <过程名>
9.2、删除的过程
mysql> drop procedure a;
mysql> call a();
ERROR 1305 (42000): PROCEDURE score.a does not exist