mysql高阶语句：

mysql高阶语句：

高阶语法的查询语句

select * from 表名

一、排序

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

create table info (
id int primary key,
name varchar(10),
score decimal(5,2),
address varchar(20),
hobbid int(5));

insert into info values(1,‘liuyi’,80,‘beijing’,2);
insert into info values(2,‘wangwu’,90,‘shengzheng’,2);
insert into info values(3,‘lisi’,60,‘shanghai’,4);
insert into info values(4,‘tianqi’,99,‘hangzhou’,5);
insert into info values(5,‘jiaoshou’,98,‘laowo’,3);
insert into info values(6,‘hanmeimei’,10,‘nanjing’,3);
insert into info values(7,‘lilei’,11,‘nanjing’,5);

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

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

desc 降序排列，需要添加

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

select id,name from info ORDER BY id;

select * 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;

嵌套条件

select * from info where score > 70 or (score > 0 and score < 60);

2、去重查询：

select distinct 去重对象 from 表名;

select distinct address from info;

三、分组：group by

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

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

1、聚合函数：

count() 统计有多少行

sum() 列的值相加求和

avg() 列的值求平均数

max() 过滤出列的最大值

min() 过滤出列的最小值

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

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

在聚合函数分组语句中，所有的非聚合函数列，都要在group by 语句当中

根据where条件筛选统计

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

2、分组和having语句

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

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

select count(name),hobbid,score from info group by hobbid,score having score> 80 order by count(name) desc;

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

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

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

limit 1，3 1是位置偏移量（可选参数）

如果不设定位置偏移量 ，默认从第一行开始 默认的值0

快速的查看后几行

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;

select i.name 姓名,i.score 成绩 from info i;

给表取别名 info得别名 i

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)

select 语句当中又嵌套了一个select，嵌套得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 “条件”);

select count(*) from info where EXISTS(select id from test3 where score > 10);

6、多表查询

select avg(a.score) from info a where a.id in(select b.id from test b where a.id = b.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 视图名;

九、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.a_id,a.a_name from test1 a inner join test2 b on a.a_name=b.b_name

2、左连接：left join

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

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

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

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

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

select * from test1 a left join test2 b on a.a_name=b.b_name;

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

已比较条件为标准 ，展示结果 两个表相同的部分展示出来，做拼接，不同结果显示null

test01是左，info是右

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

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

以左边为标准，相同的才展示

3、右连接：right join

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

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

匹配右表的所有行，以及左表中符合条件的行，不符合显示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

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

基本格式：

elimiter $$

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<inscore;

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。