目录
1.MySQL进阶查询
MySQL 高级(进阶) SQL 语句
use mydb;
###在数据库mydb中创建数据表location,并添加数据记录
create table location (Region char(20),Store_Name char(20));
insert into location values('East','Boston');
insert into location values('East','New York');
insert into location values('West','Los Angeles');
insert into location values('West','Houston');
location 表格
+----------+--------------+
| Region | Store_Name |
|----------+--------------|
| East | Boston |
| East | New York |
| West | Los Angeles |
| West | Houston |
+----------+--------------+
###在数据库mydb中创建数据表store_info,并添加数据记录
create table store_info (Store_Name char(20),Sales int(10),Date char(10));
insert into store_info values('Los Angeles','1500','2020-12-05');
insert into store_info values('Houston','250','2020-12-07');
insert into store_info values('Los Angeles','300','2020-12-08');
insert into store_info values('Boston','700','2020-12-08');
insert into store_info values('Washington','1000','2020-12-09');
insert into store_info values('Chicago','800','2020-12-10');
store_info 表格
+--------------+---------+------------+
| Store_Name | Sales | Date |
|--------------+---------+------------|
| Los Angeles | 1500 | 2020-12-05 |
| Houston | 250 | 2020-12-07 |
| Los Angeles | 300 | 2020-12-08 |
| Boston | 700 | 2020-12-08 |
| Washington | 1000 | 2020-12-09 |
| Chicago | 800 | 2020-12-10 |
+--------------+---------+------------+
1.1 select
显示表格中一个或数个字段的所有数据记录
语法：SELECT "字段" FROM "表名";
SELECT Store_Name FROM store_info;
1.2 distinct
不显示重复的数据记录
语法：SELECT DISTINCT "字段" FROM "表名";
SELECT DISTINCT Store_Name FROM store_info;
1.3 where
有条件查询
语法：SELECT "字段" FROM "表名" WHERE "条件";
SELECT Store_Name FROM store_info WHERE Sales > 1000;
1.4 and or
且 或
语法：SELECT "字段" FROM "表名" WHERE "条件1" {[AND|OR] "条件2"}+ ;
SELECT Store_Name FROM store_info WHERE Sales > 1000 OR (Sales < 500 AND Sales > 200); 
1.5 in
显示已知的值的数据记录
语法：SELECT "字段" FROM "表名" WHERE "字段" IN ('值1', '值2', ...);
select * from store_info where store_name in ('Houston','Los Angeles');
1.6 between
显示两个值范围内的数据记录
语法：SELECT "字段" FROM "表名" WHERE "字段" BETWEEN '值1' AND '值2';
SELECT * FROM store_info WHERE Date BETWEEN '2020-12-06' AND '2020-12-10';
1.7 通配符
通常通配符都是跟 LIKE 一起使用的
% ：百分号表示零个、一个或多个字符
_ ：下划线表示单个字符
'A_Z'：所有以 'A' 起头，另一个任何值的字符，且以 'Z' 为结尾的字符串。例如，'ABZ' 和 'A2Z' 都符合这一个模式，而 'AKKZ' 并不符合 (因为在 A 和 Z 之间有两个字符，而不是一个字符)。
'ABC%': 所有以 'ABC' 起头的字符串。例如，'ABCD' 和 'ABCABC' 都符合这个模式。
'%XYZ': 所有以 'XYZ' 结尾的字符串。例如，'WXYZ' 和 'ZZXYZ' 都符合这个模式。
'%AN%': 所有含有 'AN'这个模式的字符串。例如，'LOS ANGELES' 和 'SAN FRANCISCO' 都符合这个模式。
'_AN%'：所有第二个字母为 'A' 和第三个字母为 'N' 的字符串。例如，'SAN FRANCISCO' 符合这个模式，而 'LOS ANGELES' 则不符合这个模式。
select * from store_info where store_name like '__us%';
select * from store_info where store_name like '_os%';
1.8 like
匹配一个模式来找出我们要的数据记录
语法：SELECT "字段" FROM "表名" WHERE "字段" LIKE {模式};
SELECT * FROM store_info WHERE Store_Name like '%os%';
2.MySQL数据库函数
2.1 数学函数
SELECT abs(-1), rand(), mod(5,3), power(2,3), round(1.89);
SELECT round(1.8937,3), truncate(1.235,2), ceil(5.2), floor(2.1), least(1.89,3,6.1,2.1);
获取1000以内的随机整数；
select rand() * 1000;
###以下获取1000以内随机整数的两种方法
select truncate(rand() * 1000,0);
select round(rand() * 1000);
2.2 聚合函数
###返回指定列sales的平均值
SELECT avg(Sales) FROM store_info; 
###返回指定列store_name列中非NULL值的个数
select count(store_name) from store_info;
select count(distinct store_name) from store_info;
2.3 字符串函数
select concat(region,store_name) from location where store_name='Los Angeles';
select concat(region,' ',store_name) from location where store_name='Los Angeles';
#如sql_mode开启了PIPES_AS_CONCAT，"||"视为字符串的连接操作符而非或运算符，和字符串的拼接函数Concat相类似，这和Oracle数据库使用方法一样的
select region || ' ' || store_name from location where store_name='Los Angele
select substr(store_name,3) from location where store_name='Los Angeles';
select * from location;
select substr(store_name,3,4) from location where store_name='Los Angeles';
select trim([ [位置] [要移除的字符串] from ] 字符串);
#[位置]：的值可以为leading(起头),trailing(结尾),both(起头及结尾)。
#[要移除的字符串]：从字串的起头、结尾，或起头及结尾移除的字符串。缺省时为空格。
select trim(leading 'Ne' from 'New York');
select trim(trailing 'aw' from 'Newaw Yorkaw');
select trim(both 'aw' from 'awNewaw Yorkaw');
select region,length(store_name) from location;
select replace(region,'ast','astern') from location;
3.查询函数
3.1 group by
对GROUP BY后面的字段的查询结果进行汇总分组，通常是结合聚合函数一起使用的
语法：SELECT "字段1", SUM("字段2") FROM "表名" GROUP BY "字段1";
select store_name,sum(sales) from store_info group by store_name order by sales desc;
GROUP BY 有一个原则，凡是在 GROUP BY 后面出现的字段，必须在 SELECT 后面出现；
凡是在 SELECT 后面出现的、且未在聚合函数中出现的字段，必须出现在 GROUP BY 后面
3.2 sql执行顺序
3.3 having
用来过滤由 GROUP BY 语句返回的记录集，通常与 GROUP BY 语句联合使用
语法：SELECT "字段1", SUM("字段2") FROM "表格名" GROUP BY "字段1" HAVING (函数条件);
select store_name,sum(sales) from store_info group by store_name having sum(sales) > 1000;
HAVING 语句的存在弥补了 WHERE 关键字不能与聚合函数联合使用的不足。
3.4 别名（字段别名 表别名）
字段別名 表格別名
语法：SELECT "表格別名"."字段1" [AS] "字段別名" FROM "表格名" [AS] "表格別名";
SELECT A.Store_Name Store, SUM(A.Sales) "Total Sales" FROM store_info A GROUP BY A.Store_Name;3.5 子查询
连接表格，在WHERE 子句或 HAVING 子句中插入另一个 SQL 语句
语法：SELECT "字段1" FROM "表格1" WHERE "字段2" [比较运算符] #外查询
(SELECT "字段1" FROM "表格2" WHERE "条件"); #内查询
#可以是符号的运算符，例如 =、>、<、>=、<=、!=；也可以是文字的运算符，例如 LIKE、IN、BETWEEN
SELECT SUM(Sales) FROM store_info WHERE Store_Name IN
(SELECT Store_Name FROM location WHERE Region = 'West');
---- EXISTS ----用来根据条件过滤查询结果，并只返回满足条件的行
语法：SELECT "字段1" FROM "表格1" WHERE EXISTS (SELECT * FROM "表格2" WHERE "条件");
#这里的子查询作为条件进行判断。如果子查询返回至少一行结果，则外部查询的结果将包含该行
select * from store_info A where exists (select Store_Name from location B where B.store_name = A.store_name); 
3.6 exists（类查询）
用来根据条件过滤查询结果，并只返回满足条件的行
语法：SELECT "字段1" FROM "表格1" WHERE EXISTS (SELECT * FROM "表格2" WHERE "条件");
#这里的子查询作为条件进行判断。如果子查询返回至少一行结果，则外部查询的结果将包含该行
select * from store_info A where exists (select Store_Name from location B where B.store_name = A.store_name);
4.连接查询
4.1 表连接
###图三sql语句内连接表示
select * from location A inner join store_info B on A.store_name = B.store_name; #解法一
select * from location A inner join store_info B using(store_name); #解法二
select * from location A,store_info B where A.store_name=B.store_name; #解法三多表连接，没有以上两种解法效率高
select region,sum(B.sales) from location A,store_info B where A.store_name=B.store_name group by(region) order by sum(B.sales);
select store_name from location where store_name in (select store_name from store_info); #解法四子查询
###图三sql语句左连接表示
select * from location A left join store_info B on A.store_name = B.store_name;
###图三sql语句右连接表示
select * from location A right join store_info B on A.store_name = B.store_name;
4.2 view视图
视图，可以被当作是虚拟表或存储查询
视图跟表格的不同是，表格中有实际储存数据记录，而视图是建立在表格之上的一个架构，它本身并不实际储存数据记录。
临时表在用户退出或同数据库的连接断开后就自动消失了，而视图不会消失。
视图不含有数据，只存储它的定义，它的用途一般可以简化复杂的查询。比如你要对几个表进行连接查询，而且还要进行统计排序等操作，写SQL语句会很麻烦的，用视图将几个表联结起来，然后对这个视图进行查询操作，就和对一个表查询一样，很方便。
CREATE VIEW "视图表名" AS "SELECT 语句";
例如
CREATE VIEW V_REGION_SALES AS SELECT A.Region REGION,SUM(B.Sales) SALES FROM location A
INNER JOIN store_info B ON A.Store_Name = B.Store_Name GROUP BY REGION;
SELECT * FROM V_REGION_SALES;
DROP VIEW V_REGION_SALES;
视图表view 里的数据是否能修改？
视图表保存的是select查询语句的定义。 create view 视图表名 as select语句;
如果定义的select语句查询的字段是没有被处理过的源表字段，则可以通过视图表修改源表的数据。
如果定义的select语句查询的字段被函数或 group by 等处理的字段，则不能直接修改视图表的数据。
4.3 联集
联集，将两个SQL语句的结果合并起来
###上图联集的SQL语句表示
UNION ：生成结果的数据记录值将没有重复，且按照字段的顺序进行排序
语法：[SELECT 语句 1] UNION [SELECT 语句 2];
UNION ALL ：将生成结果的数据记录值都列出来，无论有无重复
语法：[SELECT 语句 1] UNION ALL [SELECT 语句 2];
select store_name from location union select store_name from store_info;
select store_name from location union all select store_name from store_info;
4.4 CASE
SQL 用来做为 IF-THEN-ELSE 之类逻辑的关键字
#根据一个字段的值来生成一个新字段的结果
select case 字段 when '值1' then 结果1
when '值2' then 结果2
....
else 默认结果 end AS 'case字段别名'
from 表;
#根据一个条件表达式来生成一个新字段的结果
select case when '条件表达式1' then 结果1
when '条件表达式2' then 结果2
....
else 默认结果 end AS 'case字段别名'
from 表;
例
#根据一个字段的值来生成一个新字段的结果
select store_name, case Store_Name when 'Los Angeles' then sales*2
-> when 'Boston' then 2000
-> when 'Washington' then sales+500
-> else sales end AS 'new sales' -> from store_info;
#根据一个条件表达式来生成一个新字段的结果
mysql> select store_name, case Store_Name when 'Los Angeles' then sales*2
-> when store_name!='Los Angeles' then sales+100
-> end 'new sales'
-> from store_info;
4.5 空值(NULL) 和 无值('') 的区别
（1）无值的长度为 0，不占用空间的；而 NULL 值的长度是 NULL，是占用空间的。
（2）IS NULL 或者 IS NOT NULL，是用来判断字段是不是为 NULL 或者不是 NULL，不能查出是不是无值的。
（3）无值的判断使用=''或者<>''来处理。<> != 代表不等于。
（4）在通过 count()指定字段统计有多少行数时，如果遇到 NULL 值会自动忽略掉，遇到无值会加入到记录中进行计算。
4.6 正则表达式
语法：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';
如果过滤重复数据？
#删除全部重复数据，一条不留
create view 视图表名 as select 字段 from 表 group by 字段 having count(字段) > 1;
delete from 表 where 字段 in (select 字段 from 视图表名);
mysql> create view v_t1_name as select name from t1 group by name having count(name) > 1;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from v_t1_name;
+------+
| name |
+------+
| cat |
| dog |
+------+
2 rows in set (0.00 sec)
#删除重复数据，仅保留一条
create view 视图表名 as select min(字段) from 表 group by 字段;
delete from 表 where 字段 not in (select 字段 from 视图表名);
mySQL 导出导入 CSV文件
MySQL 导出导入 CSV文件
vim /etc/my.cnf
[mysqld]
secure_file_priv=""
systemctl restart mysqld
mkdir /mysql-output
chmod 777 /mysql-output
#MySQL 导出表数据到 CSV文件
select * into outfile '/mysql-output/XXX.csv' fields terminated by ',' enclosed by '"' lines terminated by '\n' from 表名;
#MySQL 导入 CSV文件
load data infile '/mysql-output/XXX.csv' into table 表名 fields terminated by ',' enclosed by '"' lines terminated by '\n';
5. 存储过程
5.1 存储过程概述
存储过程是一组为了完成特定功能的SQL语句集合。
存储过程在使用过程中是将常用或者复杂的工作预先使用SQL语句写好并用一个指定的名称存储起来，这个过程经编译和优化后存储在数据库服务器中。当需要使用该存储过程时，只需要调用它即可。存储过程在执行上比传统SQL速度更快、执行效率更高。
5.2 存储过程的优点
（1）执行一次后，会将生成的二进制代码驻留缓冲区，提高执行效率
（2）SQL语句加上控制语句的集合，灵活性高
（3）在服务器端存储，客户端调用时，降低网络负载
（4）可多次重复被调用，可随时修改，不影响客户端调用
（5）可完成所有的数据库操作，也可控制数据库的信息访问权限
5.3 创建存储过程
| 名称 | 描述 |
| DELIMITER $$ | 将语句的结束符号从分号;临时改为两个$$(可以是自定义) |
| CREATE PROCEDURE Proc() | 创建存储过程，过程名为Proc，不带参数 |
| BEGIN | 过程体以关键字 BEGIN 开始 |
| select * from store_info; | 过程体语句 |
| END $$ | 过程体以关键字 END 结束 |
| DELIMITER ; | 将语句的结束符号恢复为分号 |
5.4 调用存储过程，查看存储过程
CALL Proc; #调用存储过程
SHOW CREATE PROCEDURE [数据库.]存储过程名; #查看某个存储过程的具体信息
SHOW CREATE PROCEDURE mydb.proc1;
SHOW CREATE PROCEDURE Proc;
SHOW PROCEDURE STATUS [LIKE '%Proc%']
SHOW PROCEDURE STATUS LIKE '%Proc1%';
5.5 存储过程的参数
in 参数名 数据类型 #传入参数，调用时参数值可以是纯量值，也可以变量
out 参数名 数据类型 #传出参数，调用时参数值只能是变量
inout 参数名 数据类型 #传入传出参数，调用时参数值只能是变量
delimiter $$
create procedure proc2(in myname varchar(10))
begin
select * from test01 where name = myname;
end$$
delimiter ;
call proc2('XXX')
delimiter $$
create procedure proc3(in myid int, in myname varchar(10), in myage smallint)
begin
insert into test01 values (myid, myname, myage);
end$$
delimiter ;
call proc3(4, 'fengyu', 25);
delimiter $$
create procedure proc4(in myname varchar(10), out out_age smallint)
begin
select age into out_age from test01 where name = myname;
end$$
delimiter ;
call proc4('shicaijun', @myage);
select @myage;
out 调用时参数值只能是变量
#统计多少学生大于20岁
delimiter $$
create procedure proc5(inout inout_var int)
begin
select count(age) into inout_var from test01 where age > inout_var;
end$$
delimiter ;
set @myage=20;
select @myage;
call proc5(@myage);
select @myage; 
5.6 删除存储过程
存储过程内容的修改方法是通过删除原有存储过程，之后再以相同的名称创建新的存储过程。如果要修改存储过程的名称，可以先删除原存储过程，再以不同的命名创建新的存储过程。
DROP PROCEDURE IF EXISTS Proc; #仅当存在时删除，不添加 IF EXISTS 时，如果指定的过程不存在，则产生一个错误
##存储过程的控制语句##
create table t (id int(10));
insert into t values(10);
5.7 存储过程的控制语句
create table test02 (id int primary key auto_increment, name varchar(20), sex varchar(10), age smallint， team char(3)); #创建test02
（1）条件语句if-then-else ···· end if
delimiter $$
create procedure proc6(in myname varchar(20), in mysex varchar(10), in myage smallint)
begin
if myage>=18
then
insert into test02 (name, sex, age, team) values (myname, mysex, myage, '成年组');
else
insert into test02 (name, sex, age, team) values (myname, mysex, myage, '少年组');
end if;
end $$
delimiter ;
call proc6('刘与淳', '女', 16);
call proc6('王雨婷', '女', 25);
call proc6('主播', '男', 18);
call proc6('kx', '男', 17);
create table test03(id int ,name varchar(50));
（2）循环语句while ···· end while
delimiter $$
create procedure proc7()
begin
declare i int;
set i=1;
while i<=50000
do
insert into test03 values (i, concat('student', i));
set i=i+1;
end while;
end $$
delimiter ;
call proc7;
select * from test03;
2606
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
