MySQL高阶语句
一、环境布置
1、创建一个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’);
2、创建一个Store_Info的表
CREATE TABLE Store_Info (Store_Name char(20),Sales int(20),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’);
二、SELECT 查询资料
1、显示表格中一个或数个栏位的所有资料
SELECT 列名 FROM 表名;
SELECT Store_Name FROM Store_Info;
2、不显示重复资料–DISTINCT
SELECT DISTINCT 列名 FROM 表名;
SELECT DISTINCT Store_Name FROM Store_Info;
3、有条件的查询–WHERE
SELECT 列名 FROM 表名 WHERE 搜索条件;
SELECT Sales FROM Store_Info WHERE Sales = 700;
4、且和或–AND OR
SELECT 列名 FROM 表名 WHERE 搜索条件 (AND或OR)搜索条件;
SELECT Sales FROM Store_Info WHERE Sales>250 AND Sales <1500;
5、显示已知值得资料–IN
SELECT * FROM 表名 WHERE IN (搜索值1,搜索值2);
SELECT * FROM Store_Info where Sales IN (300,250);
6、显示连个值范围内的资料–BETWEEN
SELECT 列名(或*)FROM 表名 WHERE 列名 BETWEEN 范围值1 AND 范围值2;
SELECT * FROM Store_Info where Sales BETWEEN 300 and 1500 ORDER BY Sales ASC;
7、通配符–通常与LIKE搭配使用
% : 百分号表示零个、一个或多个字符
_ : 下划线表示单个字符
‘A_Z’: 所有以’A’起头,另一个任何值的字符,且以’Z’为结尾的字符串。例如,‘ABZ’ 和’A2Z’都符合这一个模式,而’AKKZ’ 并不符合
‘ABC%’: 所有以’ABC’ 开头的字符串。 例如,‘ABCD’ 和’ABCABC’ 都符合这个模式。
‘%XYZ’: 所有以’XYZ’ 结尾的字符串。 例如,‘WXYZ’ 和’ZZXYZ’ 都符合这个模式。
‘%AN%’: 所有含有’AN’这个模式的字符串。例如,‘LOS ANGELES’和’SAN FRANCISCO’ 都符合这个模式。
‘_AN%’: 所有第二个字母为’A’ 和第三个字母为’N’ 的字符串。例如,‘SAN FRANCISCO’ 符合这个模式,而’LOS ANGELES’则不符合这个模式。
8、匹配一个模式来找出我们所要的资料–LIKE
SELECT 列名(或*)FROM 表名 WHERE 列名 LIKE ‘通配符的搜索条件’;
9、按关键字排序–ORDER BY
SELECT 列名(或者*)FROM 表名 WHERE 搜索条件 OREDER BY 列名 (ASC/DESC);
#ASC 升序
#DESC 降序
SELECT * FROM Store_Info WHERE Store_Name LIKE ‘L%’ ORDER BY Sales ASC;
三、函数
1、数学函数
函数 | 作用 |
---|---|
abs(x) | 返回x的绝对值 |
rand() | 返回0到1的随机数 |
mod(x,y) | 返回x除以y以后得余数 |
power(x,y) | 返回x的y次方 |
round(x) | 返回离x最近的整数 |
round(x,y) | 保留x的y位小数四舍五入后的值 |
sqrt(x) | 返回x的平方根 |
truncate(x,y) | 返回数字x截断为y为小数的值 |
ceil(x) | 返回大于或等于x的最小整数 |
floor(x) | 返回小于或等于x的最大整数 |
greatest(x1,x2…) | 返回集合中最大的值 |
least(x1,x2…) | 返回集合中最小的值 |
举例
2、聚合函数
函数 | 作用 |
---|---|
avg() | 返回指定列的平均值 |
count() | 返回指定列中非NULL值的个数 |
min() | 返回指定列的最小值 |
mx() | 返回指定列的最大值 |
sum() | 返回指定列的所有值之和 |
举例
3、字符串函数
函数 | 作用 |
---|---|
trim() | 返回去除指定格式的值 |
concat(x,y) | 将提供的参数x和y拼接成一个字符串 |
substr(x,y) | 获取从字符串x中的第y个位置开始的字符串,很substring()函数作用相同 |
substr(x,y,z) | 获取字符串x中的第y个位置开始长度为z的字符串 |
length(x) | 返回字符串x的长度 |
replace(x,y,z) | 用字符串z替代字符串x中的字符串y |
upper(x) | 将字符串x的所有字母变成大写字母 |
lower(x) | 将字符串x的所有字母变成小写字母 |
left(x,y) | 返回字符串x的前y个字符 |
right(x,y) | 返回字符串x的后y个字符 |
repeat(x,y) | 将字符串x重复y次 |
sapce(x) | 返回x个空格 |
strcmp(x,y) | 比较x和y,返回的值可以为-1,0,1 |
reverse(x) | 将字符串x反转 |
举例
SELECT TRIM ([位置] [要移除的字符串] FROM 字符串);
#[位置]: 值可以为 LEADING (起头),TRAILING (结尾),BOTH (起头及结尾)
#[要移除的字符串]: 从字串的起头、结尾或起头及结尾移除的字符串。缺省时为空格
在/etc/my.cnf可以开启PIPES_AS_CONCAT模块,可以将“||”视为字符串|
四、GROUP BY
对GROUP BY后面的栏位的查询结果进行汇总分组,通常是结合聚合函数一起使用的 GROUP BY 有一个原则,就是 SELECT 后面的所有列中,没有使用聚合函数的列,必须出现在GROUPBY后面。
SELECT 列名1,聚合函数 FROM 表名 GROUP BY 列名1;
SELECT Store_Name,SUM(Sales) FROM Store_Info GROUP BY Store_Name;
五、Having
用来过滤由 GROUP BY 语句返回的记录集,通常与 GROUP BY 语句联合使用
HAVING 语句的存在弥补了 WHERE 关键字不能与聚合函数联合使用的不足。如果被 SELECT 的只有函数栏,那就不需要GROUP BY子句
SELECT 列名1 聚合函数 FROM 表名 GROUP BY 列名1 HAVING 搜索条件;
SELECT Store_Name,SUM(Sales) FROM Store_Info GROUP BY Store_Name HAVING SUM(Sales)>1500;
六、别名–AS
SELECT 表格别名.列名(AS)列别名 FROM 表格吗 表格别名;
SELECT A.Store_Name bbb FROM Store_Info A;
七、子查询
连接表格,在WHERE 子句或HAVING 子句中插入另一个SQL 语句
SELECT 列名1(聚合函数)FROM 表格1 WHERE 列名2 IN #外查询部分(后执行)
(SELECT 列名2 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
用来测试内查询有没有产生任何结果,类似布尔值是否为真
#如果有的话,系统就会执行外查询中的SQL语句。若是没有的话,那整个SQL语句就不会产生任何结果
SELECT 列名(或聚合函数)FROM 表名 WHERE EXISTS (SELECT 列名 FROM 表名 WHERE 搜索条件);
九、查询连接
inner join(等值相连): 只返回两个表中联结字段相等的行
left join(左联接): 返回包括左表中的所有记录和右表中联结字段相等的记录
right join(右联接): 返回包括右表中的所有记录和左表中联结字段相等的记录
SELECT * FROM location A INNER JOIN Store_Info B on A.Store_Name = B.Store_Name;
SELECT * FROM location A RIGHT JOIN Store_Info B on A.Store_Name = B.Store_Name;
SELECT * FROM location A LEFT JOIN Store_Info B on A.Store_Name = B.Store_Name;
十、视图–CREATE VIEW
可以被当作是虚拟表或存储查询
视图跟表格的不同是,表格中有实际储存资料,而视图是建立在表格之上的一个架构,它本身并不实际储存资料。 临时表在用户退出或同数据库的连接断开后就自动消失了,而视图不会消失
视图不含有数据,只存储它的定义,它的用途一般可以简化复杂的查询。比如你要对几个表进行连接查询,而且还要进行统计排序等操作,写的SQL语句会很麻烦的,用视图将几个表联结起来,然后对这个视图进行查询操作,就和对一个表查询一样,很方便
CREATE VIEW “视图表名” AS “SELECT 语句”;
CREATE VIEW VIEW 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;
删除视图
DROP VIEW 视图名;
UNION: 生成结果的资料值将没有重复,且按照字段的顺序进行排序
[SELECT 语句1] UNION [SELECT 语句2];
SELECT Store_Name FROM location UNION SELECT Store_Name FROM Store_Info;
UNION ALL: 将生成结果的资料值都列出来,无论有无重复
[SELECT 语句1] UNION ALL [SELECT 语句2];
SELECT Store_Name FROM location UNION ALL SELECT Store_Name FROM Store_Info;
十一、交集值–取两个SQL语句结果的交集
SELECT A.Store_Name FROM location A INNER JOIN Store_Info B ON A.Store_Name = B.Store_Name; #有重复值得
或者
SELECT A.Store_Name FROM location A INNER JOIN Store_Info B USING (Store_Name); #使用USING有同样的效果
SELECT DISTINCT A.Store_Name FROM location A INNER JOIN Store_Info B ON A.Store_Name = B.Store_Name; #加一个DISTINCT可以进行去重
十二、无交集值
SELECT DISTINCT Store_Name FROM location WHERE (Store_Name) NOT IN (SELECT Store_Name FROM Store_Info); #IN也可以连接其他表
十三、CASE
是SQL用来做为 IF-THEN-ELSE 之类逻辑的关键字
SELECT CASE 列名
WHEN 条件1 THEN 结果1
WHEN 条件2 THEN 结果2
…
ELSE 结果N
END
FROM 表名
#"条件"可以是一个数值或是公式。ELSE 子句则并不是必须的
创建一个新表
CREATE TABLE Total_Sales (Name char(10),Sales int(5));
INSERT INTO Total_Sales VALUES (‘zhangsan’,10);
INSERT INTO Total_Sales VALUES (‘lisi’,15);
INSERT INTO Total_Sales VALUES (‘wangwu’,20);
INSERT INTO Total_Sales VALUES (‘zhaoliu’,40);
INSERT INTO Total_Sales VALUES (‘sunqi’,50);
INSERT INTO Total_Sales VALUES (‘zhouba’,20);
INSERT INTO Total_Sales VALUES (‘wujiu’,30);
1、算排名
表格自我连结 (self Join),然后将结果依序列出,算出每一行之前(包含那一行本身)有多少行数
#统计sales栏位的值是比自已本身的值小的以及sales栏位和Name栏位都相同的数量,比如zhangsan为5+1=6
SELECT A1.Name,A1.Sales,COUNT(A2.Sales) Rank FROM Total_Sales A1,Total_Sales A2
WHERE A1.Sales < A2.Sales OR (A1.Sales = A2.Sales AND A1.Name = A2.Name)
GROUP BY A1.Name,A1.Sales ORDER BY A1.Sales DESC;
2、算中位数
SELECT Sales Middle FROM (SELECT A1.Sales,A1.Name,COUNT(A2.Sales) Rank FROM Total_Sales A1 ,Total_Sales A2 #第一行是为了设立别名
WHERE A1.Sales < A2.Sales OR (A1.Sales = A2.Sales AND A1.Name<= A2.Name) #第二行为了给写出sales列名中比自己本身值小的数,以及等于自己的数,名字上也做了比大小,以防有重复的
GROUP BY A1.Name,A1.Sales OREDER BY A1.Sales DCSE ) A3 #第三行为了排序和分组,以及给以上的派生表设立别名
WHERE A3.Rank (SELECT (COUNT(*)+1) DIV 2 FROM Total_Sales); #第四行将Rank表的行数加1 再除以2 得出那中值行
#每个派生表必须有自己的别名,所以别名 A3 必须要有
#DIV 是在MySQL中算出商的方式
3、算累计总值
表格自我连结(Self Join), 然后将结果依序列出,算出每一行之前(包含那一行本身)的总合
SELECT A1.Name,A1.Sales,SUM(A2.Sales) Sum_Total FROM Total_Sales A1,Total_Sales A2
WHERE A1.Sales < A2.Sales OR (A1.Sales=A2.Sales AND A1.Name = A2.Name)
GROUP BY A1.Name,A1.Sales ORDER BY A1.Sales DESC;
4、算总百分比
SELECT A1.Name,A1.Sales,A2.SALES/(SELECT SUM(Sales) FROM Total_Sales) SUM FROM Total_Sales A1, Total_Sales A2
WHERE A1.Sales < A2.Sales OR (A1.Sales < A2.Sales AND A1.Name = A2.Name)
GROUP BY A1.Name,A1.Sales ORDER BY A1.Sales DESC;
#SELECT SUM(Sales) FROM Total_sales 这一段子查询是用来算出总合
#总合算出后,我们就能够将每一行一一除以总合来求出每一行的总合百分比
5、累计综合百分比
SELECT A1.Name,A1.Sales,SUM(A1.Sales)/(SELECT SUM(Sales) FROM Total_Sales) SUM FROM Total_Sales A1,Total_Sales A2
WHERE A1.Sales < A2.Sales OR(A1.Sales = A2.Sales AND A1.Name = A2.Name)
GROUP BY A1.Name,A1.Sales ORDER BY A1.Sales DESC;
十四、空值(NULL)和无值(’ ')的区别
无值的长度为0,不占用空间的; 而NULL值的长度是NULL,是占用空间的。
Is NULL或者Is NOT NULL,是用来判断字段是不是为NULL或者不是NULL,不能查出是不是无值的。
无值的判断使用 =’’ 或者 <>’’ 来处理。 <>代表不等于。
在通过 count()指定字段统计有多少行数时,如果遇到NULL 值会自动忽略掉,遇到无值会加入到记录中进行计算。
创建一个表
CREATE TABLE CITY (NAME VARCHAR(20));
insert into CITY values(‘beijing’);
insert into CITY values(‘shanghai’);
insert into CITY values(‘nanjing’);
insert into CITY values();
insert into CITY values();
insert into CITY values(’’);
insert into CITY values(’’);
SELECT * FROM city WHERE name IS NULL; #不会把无值计算进去
SELECT * FROM city WHERE name IS NOT NULL; #会把无值也计算进去
SELECT * FROM city WHERE name = ‘’;
SELECT * FROM city WHERE name <> ‘’;
SELECT COUNT(*) FROM city; #空值和无值都会计算进去
SELECT COUNT(name) FROM city; #忽略空值,但是会把无值计算进去
十五、正则表达式
匹配模式 | 描述 | 实例 |
---|---|---|
^ | 匹配文本的开始字符 | ‘^bd’ 匹配以 bd 开头的字符串 |
$ | 匹配文本的结束字符 | ‘qn$’ 匹配以 qn 结尾的字符串 |
. | 匹配任何单个字符 | ‘s.t’ 匹配任何 s 和 t 之间有一个字符的字符串 |
* | 匹配零个或多个在它前面的字符 | ‘fo*t’ 匹配 t 前面有任意个 o |
+ | 匹配前面的字符 1 次或多次 | ‘hom+’ 匹配以 ho 开头,后面至少一个m 的字符串 |
字符串 | 匹配包含指定的字符串 | ‘clo’ 匹配含有 clo 的字符串 |
p1|p2 | 匹配 p1 或 p2 | ‘bg|fg’ 匹配 bg 或者 fg |
[…] | 匹配字符集合中的任意一个字符 | ‘[abc]’ 匹配 a 或者 b 或者 c |
[^…] | 匹配不在括号中的任何字符 | ‘[^ab]’ 匹配不包含 a 或者 b 的字符串 |
{n} | 匹配前面的字符串 n 次 | ‘g{2}’ 匹配含有 2 个 g 的字符串 |
{n,m} | 匹配前面的字符串至少 n 次,至多m 次 | ‘f{1,3}’ 匹配 f 最少 1 次,最多 3 次 |
SELECT "栏位” FROM “表名” WHERE “栏位” REGEXP {模式};
SELECT * FROM CITY WHERE NAME REGEXP ‘bei’; #匹配包含‘bei’的字符串
SELECT * FROM CITY WHERE NAME REGEXP ‘^[n]’; #匹配以n开头的字符串
SELECT * FROM CITY WHERE NAME REGEXP ‘ing$’; #匹配以ing结尾的字符串
十六、存储过程
存储过程是一组为了完成特定功能的SQL语句集合。
存储过程在使用过程中是将常用或者复杂的工作预先使用SQL语句写好并用一个指定的名称存储起来,这个过程经编译和优化后存储在数据库服务器中。当需要使用该存储过程时,只需要调用它即可。存储过程在执行上比传统SQL速度更快、执行效率更高。
存储过程的优点:
执行一次后,会将生成的二进制代码驻留缓冲区,提高执行效率
SQL语句加上控制语句的集合,灵活性高
在服务器端存储,客户端调用时,降低网络负载
可多次重复被调用,可随时修改,不影响客户端调用
可完成所有的数据库操作,也可控制数据库的信息访问权限
1、创建存储过程
delimiter $$ #将语句的结束符号从分好修改成任意符号
CREATE PROCEDURE CITY() #创建存储过程,名字自定义
BEGIN #以BEGIN作为开始的关键词
SELECT * FROM CITY; #设置过程语句
END $$ #以END作为结尾
delimiter ; #将;修改回来
2、调用过程
CALL 存储名称;
3、查看存储过程的参数
SHOW CREATE PROCEDURE [数据库名].存储过程名;
SHOW CREATE PROCEDURE Proc;
SHOW PROCEDURE STATUS [LIKE ‘%Proc%’] \G
4、存储过程的参数
IN 输入参数:表示调用者向过程传入值(传入值可以是字面量或变量)
OUT 输出参数:表示过程向调用者传出值(可以返回多个值)(传出值只能是变量)
INOUT 输入输出参数:既表示调用者向过程传入值,又表示过程向调用者传出值(值只能是变量)
delimiter ##
CREATE PROCEDURE ADDRESS(IN aaa VARCHAR(20)) #aaa是输入参数可以随便定义
BEGIN
SELECT * FROM location WHERE Store_Name = aaa;
END ##
delimiter ;
5、删除存储过程
存储过程内容的修改方法是通过删除原有存储过程,之后再以相同的名称创建新的存储过程
DROP PROCEDURE IF EXISTS Proc;
6、存储过程的控制语句
#创建一个表
create table t (id int(10));
insert into t values(10);
①条件语句if-then-else
delimiter ##
CREATE PROCEDURE ID(IN AAA INT) #设置参数的类型
BEGIN
DECLARE BBB INT; #宣布BBB的类型
SET BBB=AAA*2; #BBB等于AAA*2
if BBB >=9 then
UPDATE t set id=id+1;
else
UPDATE t set id=id-1;
END if;
END ##
delimiter ##
②循环语句while-end-while
delimiter ##
CREATE PROCEDURE ID2()
BEGIN
DECLARE AAA INT(10);
SET AAA=0;
WHILE AAA<6 DO
INSERT INTO t VALUES(AAA);
SET AAA=AAA+1;
END WHILE;
END ##
delimiter ;
CALL ID2;