MySQL高级语句运用

目录

一、创建数据库和表

二、select（显示表格中的一个或者多个字段中所有的信息）

三、distinct（查询不重复记录）

四、where

五、and、or

六、in

七、between

八、like通配符

九、order  by（排序）

十、函数

10.1数学函数

10.2聚合函数

10.3字符串函数

十一、group  by

十二、having（问）

十三、别名

十四、连接查询

1.inner join——等值相连

2.left join——左联接

3.right join——右联接

十五、子查询（表连接）

十六、EXISTS

十七、CREATE VIEW（视图）

十八、联集

1.UNION

2.UNION ALL

十九、交集值

二十、无交集值

二十一、CASE

二十二、表格内容排序

1.算排名

 2.算中位数

二十三、空值（NULL）和无值（' '）区别

二十四、日期时间函数

二十五、存储过程

1 .存储过程的优点

2 .创建、调用和查看存储的过程

2.1创建存储过程

2.2调用存储过程

2.3查看存储过程 

2.4.删除存储过程

2.5 存储过程的参数

2.6存储过程的控制语句

 条件控制语句 if-then-else … end if

 循环语句 while … end while

---

一、创建数据库和表

为了展示实验操作，创建一个新的数据库和两张表

 两张表中任意插入一些数据

二、select（显示表格中的一个或者多个字段中所有的信息）

显示表格中的一个或者多个字段中所有的信息
#语法：
select 字段名  from 表名;

三、distinct（查询不重复记录）

查询不重复记录
#语法:
select distinct 字段 from 表名﹔

四、where

#语法：
select '字段' from 表名  where 条件

 

五、and、or

#语法：
select 字段名  from 表名 where 条件1 (and|or) 条件2 (and|or)条件3;

六、in

#语法：
select 字段名  from 表名 where 字段 in ('值1'，'值2'....);

七、between

#语法：
select 字段名  from 表名 where 字段 between  '值1' and '值2';
包括 and两边的值

与in类似

八、like通配符

通配符通常是和 like 一起使用

#语法：
select 字段名  from 表名 where 字段 like 模式

九、order  by（排序）

#语法：

select 字段名  from 表名 order by 字段 [asc,desc];
select 字段名  from 表名 where 条件 order by 字段 [asc,desc];
asc ：正向排序
desc ：反向排序
默认是正向排序

十、函数

10.1数学函数

 随机生成一个数
select rand (1);

返回离2.6最近的数
select round(2.6);
 
#返回离2.4最近的数

返回数字 2.335321 截断为2位小数的值
select truncate(2.335321,2);

返回集合中最大的值
 select greatest(1,4,3,9,20);

返回集合中最小的值
select least(1,4,3,9,20);

10.2聚合函数

 求表中年龄的平均值
select avg(age) from stu1;

求表中年龄的总和
select sum(age) from stu1;

求表中年龄的最大值
 select max(age) from stu1;

求表中年龄的最小值
select min(age) from stu1;

求表中有多少年龄字段非空记录
select count(age) from stu1;
 
count（明确字段）：不会忽略空记录

#示例6：求表中有多少条记录
 select count(*) from stu1;
 
 count（*）包含空字段，会忽略空记录 

10.3字符串函数

语法：
select trim (位置 要移除的字符串 from 字符串)
 
其中位置的值可以是 
leading（开始） 
trailing(结尾)
both(起头及结尾)
 
#区分大小写
要移除的字符串:从字符串的起头、结尾或起头及结尾移除的字符串，缺省时为空格。

leagth

 #语法：
select length（字段） from 表名；

 #语法：
select replace(字段，'原字符''替换字符') from 表名；

 #语法：
select concat(字段1，字段2）from 表名

 #语法：
select substr(字段，开始截取字符，截取的长度)  where 字段='截取的字符串' 

将所有字符串变成大写字母

select upper(name) from FARE;

 改为小写

select lower('ABXC');

 

select left(site,2) from 表;
select right(site,3) from 表;

重复输出

 select repeat(site,2) from 表;

 select strcmp(值1，值2);

十一、group  by

• 对group by 后面的字段的查询结果进行汇总分组，通常是结合聚合函数一起使用的

• group by 有一个原则，就是select 后面的所有列中，没有使用聚合函数的列必须出现在 group by 的后面。

#语法：
select 字段1，sum(字段2) from 表名 group by 字段1；

十二、having（问）

• having：用来过滤由group by语句返回的记录集，通常与group by语句联合使用

• having语句的存在弥补了where关键字不能与聚合函数联合使用的不足。如果被SELECT的只有函数栏，那就不需要GROUP BY子句。

• 要根据新表中的字段，来指定条件

#语法:
SELECT 字段1，SUM("字段")FROM 表格名 GROUP BY 字段1 having（函数条件);

//表中男性的平均年龄

select sex,avg(age) from stu1 group by sex having sex='男';

十三、别名

v#语法：
SELECT "表格別名"."栏位1" [AS] "栏位別名" FROM "表格名" [AS] "表格別名";

十四、连接查询

1.inner join——等值相连

SELECT 字段 FROM 表1 INNER JOIN 表2 ON 表1.字段 = 表2.字段

只返回两个表中联接字段相等的行

2.left join——左联接

返回包括左表中所有记录和右表中联接字段相等的记录

SELECT 字段 FROM 表1 LEFT JOIN 表2 ON 表1.字段 = 表2.字段;

3.right join——右联接

返回包括右表中的所有记录和左表中联接字段相等的记录

SELECT 字段 FROM 表1 RIGHT JOIN 表2 ON 表1.字段 = 表2.字段;

十五、子查询（表连接）

语法：
SELECT "栏位1" FROM "表格1" WHERE "栏位2"    [比较运算符]            
#外查询
(SELECT "栏位1" FROM "表格1" WHERE "条件");    

十六、EXISTS

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

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

 #语法：
SELECT "栏位1" FROM "表格1" WHERE EXISTS (SELECT * FROM "表格2" WHERE "条件");

十七、CREATE VIEW（视图）

可以被当作是虚拟表或存储查询

（1）视图跟表格的不同是，表格中有实际储存资料，而视图是建立在表格之上的一个架构，它本身并不实际储存资料。

（2）临时表在用户退出或同数据库的连接断开后就自动消失了，而视图不会消失。

（3）视图不含有数据，只存储它的定义，它的用途一般可以简化复杂的查询。比如你要对几个表进行连接查询，而且还要进行统计排序等操作，写SQL语句会很麻烦的，用视图将几个表联结起来，然后对这个视图进行查询操作，就和对一个表查询一样，很方便。
 

#语法：
CREATE VIEW "视图表名" AS "SELECT 语句";

视图只能显示出数据，视图中不存储数据，只能存储它的定义

删除视图表，需要删除才消失，退出或者断开数据库连接不会消失

十八、联集

• 联集，将两个SQL语句的结果合并起来，两个SQL语句所产生的栏位需要是同样的资料种类

1.UNION

生成结果的资料值将没有重复，且按照字段的顺序进行排序

#语法：
[SELECT 语句 1] UNION [SELECT 语句 2];

2.UNION ALL

将生成结果的资料值都列出来，无论有无重复

#语法：
[SELECT 语句 1] UNION ALL [SELECT 语句 2];

十九、交集值

取两个SQL语句结果的交集

二十、无交集值

显示第一个SQL语句的结果，且与第二个SQL语句没有交集的结果，还不能重复

二十一、CASE

CASE是 SQL 用来做为 IF-THEN-ELSE 之类逻辑的关键字

#语法：
SELECT case ("字段名")
  when "条件1" then "结果1"
  when "条件2" then "结果2"
  ...
  [else "结果N"]
  END
FROM "表名";
 
# "条件" 可以是一个数值或是公式。 else 子句则并不是必须的。

二十二、表格内容排序

1.算排名

表格自我连结 (Self Join)，然后将结果依序列出，算出每一行之前 (包含那一行本身) 有多少行数

 2.算中位数

二十三、空值（NULL）和无值（' '）区别

（1）无值的长度为 0，不占用空间的；而 NULL 值的长度是 NULL，是占用空间的。

（2）IS NULL 或者 IS NOT NULL，是用来判断字段是不是为 NULL 或者不是 NULL，不能查出是不是无值的。

（3）无值的判断使用=’‘或者<>’'来处理。<> 代表不等于。

（4）在通过 count()指定字段统计有多少行数时，如果遇到 NULL 值会自动忽略掉，遇到无值会加入到记录中进行计算。

查看占用长度

二十四、日期时间函数

 

 #示例1：显示当前日期
select curdate();

#示例2：显示当前的 时分秒
select curtime();

#示例3：返回月份
select month('2021-12-8');

#示例4：返回一年中的第几周
select week('2021-12-8');

#示例5：返回小时值
select hour('2021-12-8 13:42:21');

#示例6：返回星期几
select dayofweek('2021-12-8 13:42:21');

#示例7：返回在这个月的第几天
select dayofmonth('2021-12-8 13:42:21');

#示例8：返回在这一年的第几天
select dayofyear('2021-12-8 13:42:21');

二十五、存储过程

• 存储过程是组为了完成特定功能的SQL语句集合。

• 存储过程在使用过程中是将常用或者复杂的工作预先使用SQL语句写好并用一个指定的名称存储起来，这个过程经编译和优化后存储在数据库服务器中。当需要使用该存储过程时，只需要调用它即可。存储过程在执行上比传统SQL速度更快、执行效率更高

1 .存储过程的优点

封装性

通常完成一个逻辑功能需要多条 SQL 语句，而且各个语句之间很可能传递参数，所以，编写逻辑功能相对来说稍微复杂些，而存储过程可以把这些 SQL 语句包含到一个独立的单元中，使外界看不到复杂的 SQL 语句，只需要简单调用即可达到目的。并且数据库专业人员可以随时对存储过程进行修改，而不会影响到调用它的应用程序源代码

1. 可增强 SQL 语句的功能和灵活性

   存储过程可以用流程控制语句编写，有很强的灵活性，可以完成复杂的判断和较复杂的运算。

2. 可减少网络流量

   由于存储过程是在服务器端运行的，且执行速度快，因此当客户计算机上调用该存储过程时，网络中传送的只是该调用语句，从而可降低网络负载。

3. 封装性

   通常完成一个逻辑功能需要多条 SQL 语句，而且各个语句之间很可能传递参数，所以，编写逻辑功能相对来说稍微复杂些，而存储过程可以把这些 SQL 语句包含到一个独立的单元中，使外界看不到复杂的 SQL 语句，只需要简单调用即可达到目的。并且数据库专业人员可以随时对存储过程进行修改，而不会影响到调用它的应用程序源代码

4. 提高性能

   当存储过程被成功编译后，就存储在数据库服务器里了，以后客户端可以直接调用，这样所有的 SQL 语句将从服务器执行，从而提高性能。但需要说明的是，存储过程不是越多越好，过多的使用存储过程反而影响系统性能

5. 使数据独立

   数据的独立可以达到解耦的效果，也就是说，程序可以调用存储过程，来替代执行多条的 SQL 语句。这种情况下，存储过程把数据同用户隔离开来，优点就是当数据表的结构改变时，调用表不用修改程序，只需要数据库管理者重新编写存储过程即可。

6. 提高数据库的安全性和数据的完整性

   存储过程提高安全性的一个方案就是把它作为中间组件，存储过程里可以对某些表做相关操作，然后存储过程作为接口提供给外部程序。这样，外部程序无法直接操作数据库表，只能通过存储过程来操作对应的表，因此在一定程度上，安全性是可以得到提高的。

2 .创建、调用和查看存储的过程

2.1创建存储过程

#语法：
CREATE PROCEDURE <存储过程名> ( [过程参数[,…] ] ) <过程体>
[过程参数[,…] ] 格式
<过程名>：尽量避免与内置的函数或字段重名
<过程体>：语句
[ IN | OUT | INOUT ] <参数名><类型>

1. 过程名 存储过程的名称，默认在当前数据库中创建。若需要在特定数据库中创建存储过程，则要在名称前面加上数据库的名称，即 db_name.sp_name。 需要注意的是，名称应当尽量避免选取与 MySQL 内置函数相同的名称，否则会发生错误。
2. 过程参数 存储过程的参数列表。其中，<参数名>为参数名，<类型>为参数的类型（可以是任何有效的 MySQL 数据类型）。当有多个参数时，参数列表中彼此间用逗号分隔。存储过程可以没有参数（此时存储过程的名称后仍需加上一对括号），也可以有 1 个或多个参数。 MySQL 存储过程支持三种类型的参数，即输入参数、输出参数和输入/输出参数，分别用 IN、OUT 和 INOUT 三个关键字标识。其中，输入参数可以传递给一个存储过程，输出参数用于存储过程需要返回一个操作结果的情形，而输入/输出参数既可以充当输入参数也可以充当输出参数。
3. 过程体 存储过程的主体部分，也称为存储过程体，包含在过程调用的时候必须执行的 SQL 语句。这个部分以关键字 BEGIN 开始，以关键字 END 结束 在 MySQL 中，服务器处理 SQL 语句默认是以分号作为语句结束标志的。然而，在创建存储过程时，存储过程体可能包含有多条 SQL 语句，这些 SQL 语句如果仍以分号作为语句结束符，那么 MySQL 服务器在处理时会以遇到的第一条 SQL 语句结尾处的分号作为整个程序的结束符，而不再去处理存储过程体中后面的 SQL 语句，这样显然不行。
4. 为解决以上问题，通常使用 DELIMITER 命令将结束命令修改为其他字符。语法格式如下： delimiter $ $

语法说明如下：
$$ 是用户定义的结束符，通常这个符号可以是一些特殊的符号，如两个“?”或两个“￥”等。
当使用 DELIMITER 命令时，应该避免使用反斜杠“\”字符，因为它是 MySQL 的转义字符
 
成功执行这条 SQL 语句后，任何命令、语句或程序的结束标志就换为两个??
mysql > DELIMITER ??
若希望换回默认的分号“;”作为结束标志，则在 MySQL 命令行客户端输入下列语句即可
mysql > DELIMITER ;
注意：DELIMITER 和分号“;”之间一定要有一个空格

delimiter $$					#将语句的结束符号从分号;临时改为两个$$ (可以是自定义)
create procedure proc5()        #创建存储过程，过程名为proc， 不带参数
-> begin                        #过程体以关键字BEGIN开始
-> select * from students;      #过程体语句（自己根据需求进行编写）
-> end $$						#过程体以关键字END结束
delimiter ;                     #将语句的结束符号恢复为分号

2.2调用存储过程

#语法
call 过程名
 
#示例：
call proc5

2.3查看存储过程 

#查看某个存储过程的具体信息（如果在指定库中，库名可以省略）
SHOW CREATE PROCEDURE [数据库.] 存储过程名;
##示例1：
show create procedure hellodb.proc5\G;
 
##示例2：
show create procedure proc5\G;

 

2.4.删除存储过程

#语法
DROP PROCEDURE IF EXISTS 过程名;
 
#示例：
drop procedure if exists proc5;

 

2.5 存储过程的参数

• IN输入参数: 表示调用者向过程传入值(传入值可以是字面量或变量)

• OUT输出参数: 表示过程向调用者传出值(可以返回多个值)(传出值只能是变量)

• INOUT输入输出参数: 既表示调用者向过程传入值，又表示过程向调用者传出值(值只能是变量)

#示例：
delimiter

createprocedureproc11(ininnamechar(40))−>begin−>select∗fromstudentswherename=inname;−>end" role="presentation" style="position: relative;">createprocedureproc11(ininnamechar(40))−>begin−>select∗fromstudentswherename=inname;−>end$$createprocedureproc11(ininnamechar(40))->begin->select\ast fromstudentswherename=inname;->end$$

                                                                             
mysql> delimiter ;
call proc11('Xi Ren');

2.6存储过程的控制语句

 条件控制语句 if-then-else … end if

delimiter $$ 										#修改默认结束符为$$
create procedure proc12(in pro int)					#创建存储过程proc2，参数为pro，类型为int
-> begin											#过程体以关键字begin开始
-> declare var int;									#定义变量var为int类型
-> set var=pro*3;									#设置变量var等于传入参数的3倍
-> if var>=10 then									#如果变量var大于10，则执行下面过程体
-> update students set age=age+10;				    #设置表students中的age加10
-> else												#如果变量var不大于10，则执行下面过程体
-> update students set age=age-10;					#设置表students中的age减10
-> end if;											#结束if语句
-> end $$   										#创建存储过程结束
delimiter ; 										#重新修改默认结束符为原始的;
CALL proc12(4);										#调用proc2存储过程，并传入参数4

 

 循环语句 while … end while

#创建表
create table nametest(id int auto_increment primary key,name char(40),age int default 20);
							
DELIMITER $$											#修改默认结束符为$$
-> create procedure yxp() 							#创建存储过程yxp
-> begin												#过程体以关键字begin开始
-> declare i int; 								#定义变量i为int类型（长度最大为10）
-> set i = 1; 											#设置i = 1;
->  while i <= 100									#使用while循环，i要小于100
-> do insert into nametest(name,age) values
 			#满足条件则进行添加数据，内容为变量i
-> set i=i+1;										#变量i每次循环后加1 							
-> end while; 											#结束while循环
-> end $$												#创建存储过程结束
delimiter ;												#重新修改默认结束符为原始的;
CALL proc6;												#调用yxp存储过程