目录
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;