MYSQL的高阶语句

目录

一、排序：

1、使用select语句，用order by来对表进行排序

2、ORDER BY 结合where条件进行过滤：

二、区间查询判断和去重查询

1、and/or：且/或

2、去重查询：

三、分组：group by

1、聚合函数：

2、分组和having语句

四、limit限制输出的结果记录：查看表中的指定行的记录

五、通配符：

1、%：表示0个，1个或者多个

2、_：表示单个字符，只能表示1个

六、别名：alias

1、设置别名：alias 简写 AS

2、as创表：

七、子查询：内查询，嵌套查询

1、单表示例

2、不同表/多表示例：

3、not in取反：

4、配合insert、update、delete已使用

5、exists：

八、视图：mysql中的视图view

1、视图表和真表之间的区别：

2、视图的操作命令：

3、多表结合视图：

4、视图和原表的更新数据变化问题：

九、null值和空值：

十、连接查询：

1、内连接：inner join

2、左连接：left join

3、右连接：right join

十一、存储过程：

1、创建存储过程：

2、存储过程常用命令：

3、存储过程中参数有三种运用方式：

3.1、in

3.2、out

3.3、inout

4、存储过程中的控制语句：if else

5、while循环语句：

6、注意点：

---

一、排序：

创建一个库，一个表用作实验：

create table info (

id int(4) primary key,

name varchar(10) not null,

score DECIMAL(5,2),

address VARCHAR(20),

sex char(3) not NULL

);

创建一个表格用作实验

1、使用select语句，用order by来对表进行排序

ASC：升序排列（默认，可以不加

desc：降序排列，需要添加

以id为基础顺序和逆序排序

select id,name from info ORDER BY id;

select id,name from info ORDER BY id desc;

2、ORDER BY 结合where条件进行过滤：

select name,score,address from info where address='蜀汉' order by score desc;

查id姓名 成绩，根据性别=女。按照id进行降序排序

select id,name,score from info where sex='女' ORDER BY id desc;

两个排序条件：

select id,name,score from info where sex='女' ORDER BY id desc,score desc;

只有第一个参数出现相同的值时，第二个才会按照要求排序

二、区间查询判断和去重查询

1、and/or：且/或

score大于70且小于等于90：

select * from info where score > 70 and score <= 90;

score大于70或者小于90（or条件只要满足一个都会过滤）：

select * from info where score > 70 and score <= 90;

score大于70中的大于75且小于90

select * from info where score > 70 and (score >75 and score <90);

or只要满足一个即可：

select * from info where score > 70 or (score >75 and score <90);

嵌套条件：

满足性别是男，然后进行筛选 score 80-90

select * from info where sex='男' and (score>80 and score<90);

2、去重查询：

select distinct 去重对象 from 表名;

select distinct address from info;

根据address去重，然后过滤出成绩=90，性别是男

select DISTINCT address from info where sex='男' and score=90;

select DISTINCT address,name,score from info where sex='男' and score=90;

三、分组：group by

如何对结果进行分组查询：group by语句

一般是结合聚合函数一块使用

1、聚合函数：

count() 统计有多少行

sum() 列的值相加求和

avg() 列的值求平均数

max() 过滤出列的最大值

min() 过滤出列的最小值

分组的时候可以按照一个字段，也可以按照多个字段，对结果进行分组处理

按照性别分组：

select count(name),sex from info group by sex;

根据where条件筛选统计出来的男和女 score >=80

select count(name),sex,score from info where score >= 80 group by sex;

求和：以地址为分组，对score求和

select sum(score),address from info group by address;

select sum(score),count(id),address from info group by address;

男生女生的平均成绩：

select avg(score),sex from info group by sex;

2、分组和having语句

如何使用group by实现条件的过滤：后不能用where要用having语句实现条件语句

分清是先分组再排序还是先排序再分组

命令使用顺序：select 参数 from 表 group by     having     order by

select avg(score),address from info group by address having avg(score) > 60;

按照地址分组，求平均成绩，然后>50，按照平均成绩降序排序

select avg(score),address from info group by address having avg(score)>50 order by avg(score) desc;

统计name的行数，计算出学生的个数，把成绩也查出来，然后按照统计出来的学生个数，升序排列，按照地址分组，学生的成绩>=70分

select count(name),score,address from info group by address having score >=70 order by count(name);

按照性别分组，求出男生和女生的最大成绩，最大成绩是否超过75分。满足条件过滤出来

select max(score),sex from info group by sex having max(score) >75;

使用聚合函数必须要加group by 分组条件，要选用多个重复值的列。

过滤条件要用having语句过滤条件

四、limit限制输出的结果记录：查看表中的指定行的记录

limit 8,3 意思是查看第9行往下3行，也就是9、10、11行

只看前三行

select * from info limit 3;

看2-5行

select * from info limit 1,4;

看6-8行

select * from info limit 5,3;

快速的查看后几行

select * from info order by id desc limit 3;

五、通配符：

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

通配符和like一块使用，使用where语句一起完成查询

1、%：表示0个，1个或者多个

select * from info where address like '山%';

山%：以山为开头

select * from info where address like '%山';

%山：以山为结尾

select * from info where address like '%山%';

%山%：内容中间只要有山就匹配

2、_：表示单个字符，只能表示1个

select * from info where name like '刘__';

表示查询刘xx

select * from info where name like '_刘_';

表示查询x刘x

select * from info where name like '__刘';

表示xx刘

这两个通配符可以结合在一块使用：

六、别名：alias

1、设置别名：alias 简写 AS

在MySQL查询时，表的名字或者字段名太长，可以使用别名进行替代。方便书写。可以增加可读性

alias使用：as可加可不加

select name as 姓名,score as 成绩 from info;

select name 姓名,score 成绩 from info;

2、as创表：

as也可以创建表：约束条件不会被复制过去，只能复制标的数据结构

create table test as select * from info;

相当于创建一个表test，表数据结构完整的从info复制过来，但是约束不会被复制

思考：as创建外键还在不在，索引还在不在？

as创建表格，约束条件（主键、外键、索引）都不会被复制过去，只是复制表的数据结构

也可以加入where语句来创表：

create table test1 as select * from info where score >= 60;

只复制info表的score>=60的值到test1表中来，一样的，约束条件不在

可以给表起别名，但是要注意，别名不能和数据库中的其他表冲突重名

列的别名在结果中可以显示，但是表的别名在查询结果中没有显示，只能用于查询

七、子查询：内查询，嵌套查询

select....(select)

子查询也称内查询或者嵌套查询，是指在一个查询语句里面还嵌套着另一个查询语句

括号里面的查询语句回先于主查询语句执行。然后再把子查询的结果作为外层的条件返回给主查询进行下一步的查询过滤：

先执行括号内的子语句，子语句的结果集，作为主语句的判断条件

子语句可以和主语句所查询的表相同，也可以是不同表

子查询语句返回的结果只能是一列不能是多列：where什么，子查询就要过滤什么

要一一对应：如下

先执行括号里的子语句，先从表info中过滤出score>80的id。再从表info中过滤出子语句中id的name和score：

1、单表示例

select name,score from info where id in (select id from info where score > 80);

主语句：select name,score from info where id

子语句（集合）：select id from info where score > 80

子语句的sql语句是为了，最后过滤出一个结果集，用于主语句的判断条件

in：将主表和子表关联/连接的语法

2、不同表/多表示例：

先过滤子语句，再把结果作为主语句的where条件

select id,name,score info where name in(select name from test);

select id,name,score from info where id in (select id from test);

当表达式与子查询返回的结果集中的某个值相等时，返回 TRUE，否则返回 FALSE。

若启用了 NOT 关键字，则返回值相反。

需要注意的是，子查询只能返回一列数据，如果需 求比较复杂，一列解决不了问题，可以使用多层嵌套的方式来应对。

多数情况下，子查询都是与 SELECT 语句一起使用的

3、not in取反：

select id,name,score from info where id not in(select id from test);

先过滤test的id（1 2 3）取反就是 非123的所有

然后过滤info表的非123id

子语句后面可以加加where条件

select id,name,score from info where id not in (select id from info where score>70);

4、配合insert、update、delete已使用

子查询语句不仅可以用在select中，也可以用在insert update delete中一起使用

先创建一个和info表数据结构相同的表：

insert into demo select * from info where id in(select id from info where sex='女');

插入数据，要求按地址，包含蜀汉插入到demo1

通配符结合模糊查询 where 列名 like '%'

insert into demo1 select * from info where address in(select address from info where address like '%蜀汉%');

修改info表score=100，not in 子查询的条件是id > 1，意思就是<=1的修改

update info set score=100 where id not in (select id from test where id>1);

这里非id>1，也就是id<=1，在表中也就是更改id=1的值

delete from info where id in (select id from info where score>80);

删除分数大于80的列

5、exists：

关键字子查询，关键字在子查询时，主要用于判断子查询的结果集是否为空，不为空返回true。为空返回false

用来测试内查询有没有产生任何结果，类似布尔值是否为真

如果内查询有结果的话，系统就会执行外查询中的SQL语句。若是没有结果的话，那整个SQL语句就不会产生任何结果

格式：

SELECT "字段1" FROM "表格1" WHERE EXISTS (SELECT * FROM "表格2" WHERE "条件");

根据info表 查询出>80分的同学，然后统计有多少个

若是如下直接查询

select count(*) from info a where EXISTS(select id from info where score > 80);

要想内外部有连接，要设置别名，要使外面的info.id=里面的a.id

select count(*) from info a where EXISTS(select id from info where score > 80 and info.id=a.id);

八、视图：mysql中的视图view

视图在mysql中是一个虚拟的表。基于查询结果得出的一个虚拟表

在工作中，我们查询的表未必就是真表。有可能是基于真表查询结果的一个虚拟表

可以简化复杂的查询语句，隐藏表的细节。提供安全的数据访问

创建视图表可以是一张表的结果集，也可以是多个表共同查询的结果集

1、视图表和真表之间的区别：

1. 存储方式区别：真实的表是存储实际数据，真正写在磁盘当中的。视图不存储任何数据，仅仅是一个查询结果集的虚拟表。
2. 数据更新的区别：真实的表可以增删改查，但是视图一般情况只能用于查询，展示数据
3. 占用空间区别：真实的表占用空间，视图是不占用数据库空间的

2、视图的操作命令：

创建视图表：

创建一个info的score>=0 的视图表：

create view 视图名 as select * from info where score >=80;

查询视图表：

查看当前库的视图表：

show full tables in 库名 where table_type like 'view';

删除视图表：

drop view 视图名;

3、多表结合视图：

只有指定相同的部分才能结合表：（注意后面的判断语句）

info和test01结合视图：

基于两个表创建视图：

create view v_info(id,name,score,age) as

select a.id,a.name,a.score,b.age from info a,test01 b

where  a.name=b.name;

info别名a，test01别名b

展示a表和b表name列相同的列，展示a表的id、name、score，b表的age

先看select部分：select a.id,a.name,a.score,b.age from info a,test01 b where a.name=b.name;

这里的别名只是起方便作用，a、b替换成info、test01 效果一样

4、视图和原表的更新数据变化问题：

原表数据变化，视图表的数据也会变化

视图表的数据变化，原表也会变化

视图是基于查询的结果集，原表数据变化，视图表的数据也会变化

更新视图表，原表的数据也会变。一般情况下是不对视图表进行改的操作

真表占80%，视图适用于安全性要求 比较高的场景，对方访问，基本都是视图。

九、null值和空值：

null就是什么都没有，好比真空

空值不代表为空，好比空气

检测null行

select * from info where score is null;

null值和空值的统计：

select count(address) from info;

null值不会被统计，空值会被统计（因为空值也占字符）

十、连接查询：

内连接：inner join

左连接：left join

右连接：right join

on后面的判断条件很重要：指定两表相同的参数合并

1、内连接：inner join

是把两张表或者多张表（不超过三张），同时符合特定条件的数据记录的组合

只有一个或者多个列的相同值才会有查询结果

只用两张表相同的列，才会显示

select a.id,a.name from test01 a inner join info b on a.name=b.name;

on后面才是匹配条件：

select a.id,a.name from test01 a inner join info b on a.id=b.id;

2、左连接：left join

左外连接，在left join关键字来表示。在左连接中，左侧表是基础表

接收左表的所有行，然后和右表（参考表）记录进行匹配。

匹配左表的所有行，以及右表中符合条件的行

以左表为准，左表有的且相同的就展示

匹配的记录，不匹配的记录null值

select * from test01 a left join info b on a.name=b.name;

test01是左，info是右

谁在前谁是左，谁在后谁是右

匹配左表所有的行，以及右表和左表相同参数的行

3、右连接：right join

右外连接，right join 以右侧表为基础。接收右侧表的所有记录，匹配的记录，不匹配的记录null值

select * from test01 a right join info b on a.name=b.name;

在左边就是左表，在右边就是右表

后面规定别名，前面select查询条件的时候要加别名

select a.id,b.name from test01 a right join info b on a.name=b.name;

十一、存储过程：

存储过程也叫做数据库脚本（MySQL脚本，SQL脚本）

是一组为了完成特定功能的sql语句的集合。类似于函数。

写好了一个存储过程之后，我们可以像函数一样随时调用sql语句的集合

适用于复杂的，需要很多sql语句联合执行完成的任务。

利用脚本的方式执行数据库操作

存储过程在执行上比sql语句的执行速度要快，效率也更高

创建表用作实验：

create table info (

id int(4),

name varchar(15),

score decimal(5,2),

pass varchar(12)

);

1、创建存储过程：

格式：

delimiter $$

#将语句的结束符号从分号，临时改变成两个$$，符号可以自定义

create procedure proc ()

#创建存储过程 proc是存储过程名 不能重复，在当前库中唯一。()括号里不定义任何方法

begin

#过程体开始的关键字

select * from info;

#begin后面跟上的是需要执行的sql语句

end $$

#整个语句结束，和上面的定义开始符号一一对应

delimiter ;

#将结束语句的符号恢复为分号

delimiter的作用就是保证整个sql语句能被完整执行

举例：

delimiter $$

create procedure proc1()

BEGIN

create table demo1(id int,name varchar(5),age int);

insert into demo1 values(1,'aa',15);

insert into demo1 values(2,'bb',16);

insert into demo1 values(3,'cc',17);

select * from demo1;

end $$

delimiter;

创建存储过程proc1

没有调用存储结构，表不会创建

2、存储过程常用命令：

查看当前库中有多少存储过程：

show PROCEDURE status where db='ku';

show PROCEDURE status like '%存储过程名%';

show create procedure 存储过程名\G;

调用存储过程：

call 存储过程名;

删除存储过程：

drop procedure if exists 存储过程名;

3、存储过程中参数有三种运用方式：

1、in 输入参数，调用者向存储过程传入一个值

2、out 输出参数，表示存储过程向调用者传出值（可以返回多个值）

3、inout 输入输出参数，表示调用者先向存储过程传入值，存储过程对传入值可能进行额外的操作之后，返回给调用者

3.1、in

如何给存储过程进行传参：

基本格式：

delimiter $$

create procedure 存储过程名(in 传入参数名 传入参数数据类型,out 传出参数名 传出参数数据类型)

begin

select 字段 into 传出参数名 from 表名 where 字段=传入参数名;

end $$

delimiter ;

call 存储过程名(参数值，@变量名)

#传出参数的值只能用变量获取

举例：

delimiter $$

create PROCEDURE test1 (in uname char(20) )

BEGIN

select * from info where name = uname;

select * from info;

end $$

delimiter;

往存储过程传参

call 存储过程名('参数');

可以把多个语句写在一块

数据清洗和执行数据入库：用于分析用户行为

delimiter $$

create PROCEDURE test1 (in uname char(20) )

BEGIN

select * from info where name = uname;

select * from info;

update info set name='james' where name=uname;

end $$

delimiter;

3.2、out

传出参数：

delimiter $$

create procedure test2 (out num int)

BEGIN

set num=100;

end $$

delimiter ;

call test(@num);

insert into info values(4,'kobe',@num,'y');

传入参数的结果，存储到传出参数中：

delimiter $$

create PROCEDURE test4(in myname char(10),out outname int)

BEGIN

select score into outname from info where name=myname;

END $$

delimiter ;

call test4('kobe',@dick);

select @dick;

3.3、inout

输入参数和输出参数：

基本格式：

delimiter $$

create procedure 存储过程名(inout 参数名 参数数据类型)

begin

select 字段 into 传出参数名 from 表名 where 字段=参数名;

end $$

delimiter ;

set @变量名 传入值

#变量赋值，传入值

call 存储过程名(@变量名)

#传入传出参数的值只能用变量

select @变量名

#此时变量内容应该为传出值

举例：

delimiter $$

create PROCEDURE test5 (inout str varchar(10))

begin

select str; -- 显示输入的字符

set str = concat(str,'one'); -- concat拼接函数，让传入的字符串做一个拼接

select str;  -- 查看加工之后的字符串

end $$

delimiter;

set @str='paul'；

call test5(@str)；

update info set neme=@str where id = 1;

inout过程：

in：先传入，定义变量的值，初始值

call test5：调用存储过程，把变量的值传入存储过程

@demo=aaaone

delimiter $$

create PROCEDURE test6(inout inscore int)

BEGIN

select count(score) into inscore from info where score

END $$

delimiter ;

set @test=50;

call test6(@test)

select @test;

4、存储过程中的控制语句：if else

delimiter $$

create procedure test4 (inout num int)

begin

if num >= 10 then

set num=num-6;

else

set num=num*2;

end if;

select num;

end $$

delimiter;

set @num=19

call test4(@num)

update info set id=@num where score = 100;

调用多个参数，范围的方式匹配，完成传参------写入表中

delimiter $$

create PROCEDURE test7 (inout score int,out grade varchar(15))

begin

if score BETWEEN 85 and 100 then

set grade = '优秀';

elseif score BETWEEN 60 and 84 then

  set grade = '一般';

else

set grade = '不及格';

end IF;

select grade;

end $$

delimiter;

set @score=55;

call test7(@score,@grade);

update info set score=@score,pass=@grade where id = 1;

5、while循环语句：

delimiter $$

create PROCEDURE test8 (out result int)

BEGIN

DECLARE a int;

DECLARE i int;

set a=10;

set i=1;

while i<=10 do

set a=a+10;

set i=i+1; -- 相当于i++

end while;

set result = a;

end $$

delimiter;

6、注意点：

使用存储过程中，在内部变量不需要加@，外部使用和复制要加@

引用变量在存储过程begin之后声明变量：declare a int; declare i int; 声明变量要加数据类型

要想使用存储过程里面的结果，必须要out才能传出参数，在声明存储变量时，要定义好参数的传参方式 in out inout。