mysql高级（进阶）语句_mysql语句进阶

---

一、mysql高级（进阶）语句

创建表格插入数据

location表

mysql> use wangbao;
Database changed
mysql> create table location (Region char(20),Store_Name char(20));
Query OK, 0 rows affected (0.00 sec)

mysql> insert into location values('East','Boston');
Query OK, 1 row affected (0.00 sec)

mysql> insert into location values('East','New York');
Query OK, 1 row affected (0.00 sec)

mysql> insert into location values('West','Los Angeles');
Query OK, 1 row affected (0.00 sec)

mysql> insert into location values('West','Houston');
Query OK, 1 row affected (0.01 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

store_info表

mysql> create table store_info (Store_Name char(20),Sales int(10),Date char(10));
Query OK, 0 rows affected (0.10 sec)

mysql> insert into store_info values('Los Angeles','1500','2020-12-05');
Query OK, 1 row affected (0.00 sec)

mysql> insert into store_info values('Houston','250','2020-12-07');
Query OK, 1 row affected (0.00 sec)

mysql> insert into store_info values('Los Angeles','300','2020-12-08');
Query OK, 1 row affected (0.00 sec)

mysql> insert into store_info values('Boston','700','2020-12-08');
Query OK, 1 row affected (0.00 sec)

mysql> insert into store_info values('Boston','700','2020-12-08');
Query OK, 1 row affected (0.01 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

SELECT

---- SELECT ----显示表格中一个或数个字段的所有数据记录
语法：SELECT "字段" FROM "表名";
mysql> SELECT Store_Name FROM store_info;

1
2
3
4

DISTINCT

---- DISTINCT ----不显示重复的数据记录
语法：SELECT DISTINCT "字段" FROM "表名";
mysql> select distinct Store_Name from store_info;


1
2
3
4
5

WHERE

---- WHERE ----有条件查询
语法：SELECT "字段" FROM "表名" WHERE "条件";
mysql> SELECT Store_Name FROM store_info WHERE Sales > 1000;
1
2
3

AND OR

---- AND OR ----且 或
语法：SELECT "字段" FROM "表名" WHERE "条件1" {[AND|OR] "条件2"}+ ;
mysql> select store_name from store_info where Sales > 1000 OR (Sales < 500 AND Sales > 200);


1
2
3
4
5

IN

---- IN ----显示已知的值的数据记录
语法：SELECT "字段" FROM "表名" WHERE "字段" IN ('值1', '值2', ...);

1
2
3

BETWEEN

---- BETWEEN ----显示两个值范围内的数据记录
语法：SELECT "字段" FROM "表名" WHERE "字段" BETWEEN '值1' AND '值2';
mysql> select * from store_info where date between '2020-12-06' AND '2020-12-10';


1
2
3
4
5

通配符

LIKE

---- LIKE ----匹配一个模式来找出我们要的数据记录
语法：SELECT "字段" FROM "表名" WHERE "字段" LIKE {模式};
mysql> SELECT * FROM store_info WHERE Store_Name like '%os%';

1
2
3
4

ORDER BY

---- ORDER BY ----按关键字排序
语法：SELECT "字段" FROM "表名" [WHERE "条件"] ORDER BY "字段" [ASC, DESC];
#ASC 是按照升序进行排序的，是默认的排序方式。
#DESC 是按降序方式进行排序
mysql> select store_name,sales,date from store_info order by sales DESC;

1
2
3
4
5
6

函数—数学函数

聚合函数

字符串函数

##将其拼接成一个字段
mysql> SELECT concat(Region, Store_Name) FROM location WHERE Store_Name = 'Boston';

#如sql_mode开启了PIPES_AS_CONCAT，"||"视为字符串的连接操作符而非或运算符，和字符串的拼接函数Concat相类似，这和Oracle数据库使用方法一样的
SELECT Region || ' ' || Store_Name FROM location WHERE Store_Name = 'Boston';
1
2
3
4
5

SELECT TRIM ([ [位置] [要移除的字符串] FROM ] 字符串);
#[位置]：的值可以为 LEADING (起头), TRAILING (结尾), BOTH (起头及结尾)。 
#[要移除的字符串]：从字串的起头、结尾，或起头及结尾移除的字符串。缺省时为空格。
1
2
3

GROUP BY

---- GROUP BY ----
对GROUP BY后面的字段的查询结果进行汇总分组，通常是结合聚合函数一起使用的
GROUP BY 有一个原则，凡是在 GROUP BY 后面出现的字段，必须在 SELECT 后面出现；
凡是在 SELECT 后面出现的、且未在聚合函数中出现的字段，必须出现在 GROUP BY 后面
语法：SELECT "字段1", SUM("字段2") FROM "表名" GROUP BY "字段1";

1
2
3
4
5
6

HAVING

---- HAVING ----
用来过滤由 GROUP BY 语句返回的记录集，通常与 GROUP BY 语句联合使用
HAVING 语句的存在弥补了 WHERE 关键字不能与聚合函数联合使用的不足。
语法：SELECT "字段1", SUM("字段2") FROM "表格名" GROUP BY "字段1" HAVING (函数条件);
SELECT Store_Name, SUM(Sales) FROM Store_Info GROUP BY Store_Name HAVING SUM(Sales) > 1500;
1
2
3
4
5

别名

---- 别名 ----
字段別名 表格別名
语法：SELECT "表格別名"."字段1" [AS] "字段別名" FROM "表格名" [AS] "表格別名";
SELECT A.Store_Name Store, SUM(A.Sales) "Total Sales" FROM Store_Info A GROUP BY A.Store_Name;
1
2
3
4

子查询

---- 子查询 ----
连接表格，在WHERE 子句或 HAVING 子句中插入另一个 SQL 语句
语法：SELECT "字段1" FROM "表格1" WHERE "字段2" [比较运算符] 				#外查询
(SELECT "字段1" FROM "表格2" WHERE "条件");									#内查询
#可以是符号的运算符，例如 =、>、<、>=、<= ；也可以是文字的运算符，例如 LIKE、IN、BETWEEN
mysql> SELECT SUM(Sales) FROM store_info WHERE Store_Name IN  (SELECT Store_Name FROM location WHERE Region = 'West');
mysql> SELECT SUM(A.Sales) FROM store_info A WHERE A.Store_Name IN 
    -> (SELECT Store_Name FROM location B WHERE B.Store_Name = A.Store_Name);

1
2
3
4
5
6
7
8
9

EXISTS

---- EXISTS ----
用来测试内查询有没有产生任何结果，类似布尔值是否为真
#如果有的话，系统就会执行外查询中的SQL语句。若是没有的话，那整个 SQL 语句就不会产生任何结果。
语法：SELECT "字段1" FROM "表格1" WHERE EXISTS (SELECT * FROM "表格2" WHERE "条件");

mysql> SELECT SUM(Sales) FROM store_info WHERE EXISTS (SELECT * FROM location WHERE Region = 'West');
+------------+
| SUM(Sales) |
+------------+
|       3450 |
+------------+
1
2
3
4
5
6
7
8
9
10
11

连接查询

UPDATE Store_Info SET store_name='Washington' WHERE sales=300;
inner join(内连接)：只返回两个表中联结字段相等的行
left join(左连接)：返回包括左表中的所有记录和右表中联结字段相等的记录 
right join(右连接)：返回包括右表中的所有记录和左表中联结字段相等的记录

1
2
3
4
5

内连接1

mysql> SELECT * FROM location A INNER JOIN store_info B on A.Store_Name = B.Store_Name ;
+--------+-------------+-------------+-------+------------+
| Region | Store_Name  | Store_Name  | Sales | Date       |
+--------+-------------+-------------+-------+------------+
| West   | Los Angeles | Los Angeles |  1500 | 2020-12-05 |
| West   | Houston     | Houston     |   250 | 2020-12-07 |
| East   | Boston      | Boston      |   700 | 2020-12-08 |
| East   | Boston      | Boston      |   700 | 2020-12-08 |
+--------+-------------+-------------+-------+------------+
4 rows in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11

内连接2

mysql> SELECT * FROM location A, store_info B WHERE A.Store_Name = B.Store_Name;
+--------+-------------+-------------+-------+------------+
| Region | Store_Name  | Store_Name  | Sales | Date       |
+--------+-------------+-------------+-------+------------+
| West   | Los Angeles | Los Angeles |  1500 | 2020-12-05 |
| West   | Houston     | Houston     |   250 | 2020-12-07 |
| East   | Boston      | Boston      |   700 | 2020-12-08 |
| East   | Boston      | Boston      |   700 | 2020-12-08 |
+--------+-------------+-------------+-------+------------+



mysql> SELECT A.Region REGION, SUM(B.Sales) SALES FROM location A, store_info B  WHERE A.Store_Name = B.Store_Name GROUP BY REGION;
+--------+-------+
| REGION | SALES |
+--------+-------+
| East   |  1400 |
| West   |  1750 |
+--------+-------+
2 rows in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

正则表达式

语法：SELECT “字段” FROM “表名” WHERE “字段” REGEXP {模式};

SELECT * FROM Store_Info WHERE Store_Name REGEXP 'os';
SELECT * FROM Store_Info WHERE Store_Name REGEXP '^[A-G]';
SELECT * FROM Store_Info WHERE Store_Name REGEXP 'Ho|Bo';
1
2
3

存储过程

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

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

存储过程的优点：

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

创建存储过程

DELIMITER $$							#将语句的结束符号从分号;临时改为两个$$(可以是自定义)
CREATE PROCEDURE Proc()					#创建存储过程，过程名为Proc，不带参数
-> BEGIN								#过程体以关键字 BEGIN 开始
-> select * from Store_Info;			#过程体语句
-> END $$								#过程体以关键字 END 结束
DELIMITER ;								#将语句的结束符号恢复为分号

##调用存储过程##
CALL Proc;

##查看存储过程##
SHOW CREATE PROCEDURE [数据库.]存储过程名;		#查看某个存储过程的具体信息

SHOW CREATE PROCEDURE Proc;

SHOW PROCEDURE STATUS [LIKE '%Proc%'] \G
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

存储过程的参数

IN 输入参数：表示调用者向过程传入值（传入值可以是字面量或变量）
OUT 输出参数：表示过程向调用者传出值(可以返回多个值)（传出值只能是变量）
INOUT 输入输出参数：既表示调用者向过程传入值，又表示过程向调用者传出值（值只能是变量）

DELIMITER $$				
CREATE PROCEDURE Proc1(IN inname CHAR(16))		
-> BEGIN					
-> SELECT * FROM Store_Info WHERE Store_Name = inname;
-> END $$					
DELIMITER ;					

CALL Proc1('Boston');
1
2
3
4
5
6
7
8
9
10
11
12

删除存储过程

存储过程内容的修改方法是通过删除原有存储过程，之后再以相同的名称创建新的存储过程。

DROP PROCEDURE IF EXISTS Proc;		#仅当存在时删除，不添加 IF EXISTS 时，如果指定的过程不存在，则产生一个错误
1
2
3

存储过程的控制语句

create table t (id int(10));
insert into t values(10);

（1）条件语句if-then-else ···· end if 
DELIMITER $$  
CREATE PROCEDURE proc2(IN pro int)  
-> begin 
-> declare var int;  
-> set var=pro*2;   
-> if var>=10 then 
-> update t set id=id+1;  
-> else 
-> update t set id=id-1;  
-> end if;  
-> end $$
 
DELIMITER ;

CALL Proc2(6);

（2）循环语句while ···· end while
DELIMITER $$  
CREATE PROCEDURE proc3()
-> begin 
-> declare var int(10);  
-> set var=0;  
-> while var<6 do  
-> insert into t values(var);  
-> set var=var+1;  
-> end while;  
-> end $$  

DELIMITER ;

CALL Proc3;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35