函数
一、数学函数
函数名 | 函数意义 |
abc(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…) | 返回集合中的最小值 |
二、聚合函数
#count(*)包括所有列的行数，在统计结果时，不好忽略值为null
#count(字段)只包括那一行的列数，在统计结果的时候，会忽略列值为null的值
函数名 | 函数意义 |
avg() | 返回指定列的平均值 |
count() | 返回指定列中非 NULL 值的个数 |
min() | 返回指定列的最小值 |
max() | 返回指定列的最大值 |
sum(x) | 返回指定列的所有值之和 |
三、字符串函数
函数名 | 函数意义 |
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 次 |
space(x) | 返回 x 个空格 |
strcmp(x,y) | 比较 x 和 y，返回的值可以为-1,0,1 |
reverse(x) | 将字符串 x 反转 |
SELECT TRIM ([ [位置] [要移除的字符串] FROM ] 字符串);
#[位置]：的值可以为 LEADING (起头), TRAILING (结尾), BOTH (起头及结尾)。
#[要移除的字符串]：从字串的起头、结尾，或起头及结尾移除的字符串。缺省时为空格。四、GROUP BY
BY后面的栏位的查询结果进行汇总分组，通常是结合聚合函数一起使用的 GROUP BY 有一个原则，就是 SELECT
后面的所有列中，没有使用聚合函数的列，必须出现在GROUP BY后面。
格式：SELECT 字段1,SUM(字段2) FROM 表名 GROUP BY 字段1;五、HAVING
用来过滤由GROUP BY语句返回的记录集，通常与GROUP BY语句联合使用。
HAVING语句的存在弥补了WHERE关键字不能与聚合函数联合使用的不足。如果被SELECT的只有函数栏，那就不需要GROUP BY子句。
格式：SELECT 字段1,SUM(字段2) FROM 表名 GROUP BY 字段1 HAVING(函数条件);六、别名
字段别名、表格别名
格式：SELECT “表格別名”.“字段1” [AS] “字段1別名” FROM “表格名” [AS] “表格別名”;七、子查询
连接表格，在WHERE 子句或HAVING 子句中插入另一个SQL 语句
格式：SELECT 字段1 FROM 表格1 WHERE 字段2 [比较运算符] (SELECT 字段1 FROM 表格2 WHERE 条件)可以是符号的运算符
例:=、>、<、>=、<=
也可以是文字的运算符
例:LIKE、IN、BETWEENSQL语句
一、SELECT语句
显示表格中一个或数个字段的所有资料
格式：SELECT 字段 FROM 表名二、DISTINCT语句
不显示重复的资料（去重）
格式：SELECT DISTINCT 字段 FROM 表名三、WHERE语句
有条件查询
语法：SELECT 字段 FROM 表名 WHERE 条件四、AND、OR语句
and（并且）、or（或者）
格式：SELECT 字段 FROM 表名 WHERE 条件1 ([AND|OR] 条件2)+；五、IN语句
显示已知的值的资料
格式：SELECT 字段 FROM 表名 WHERE 字段 IN (‘值1’,‘值2’,……);六、BETWEEN语句
显示两个值范围内的资料
格式：SELECT 字段 FROM 表名 WHERE 字段 BETWEEN ‘值一’ and ‘值二’;七、通配符、LIKE
通常通配符都是跟LIKE一起使用
- %：百分号表示零个、一个或多个字符
- _：下划线表示单个字符
- LIKE：用于匹配模式来查找资料
格式：SELECT 字段 FROM 表名 WHERE 字段 LIKE ‘模式’;八、ORDER BY语句
按关键字排序
格式：SELECT 字段 FROM 表名 [WHERE 条件] ORDER BY 字段 [ASC,DESC];
#ASC：按照升序进行排序，默认的排序方式
#DESC：按照降序进行排序九、EXISTS语句
用来测试内查询有没有产生任何结果，类似布尔值是否为真
如果有的话，系统就会执行外查询中的SQL语句，若是没有，那整个SQL语句就不会产生任何结果。
格式：SELECT 字段1 FROM 表1 WHERE EXISTS (SELECT * FROM 表2 WHERE 条件);例：
select id from s1 where exists (select * from sss where name = 'liufangpeng');
##解析过来的意思就是先进行运行exists括号内的语句，如果有运行结果再去运行括号外的语句
十、inner join、left join、right join语句
语句中的AS是给表进行别名配置
inner join（等值相连）
只返回两个表中联接字段相等的行
语法：SELECT 字段 FROM 表1 INNER JOIN 表2 ON 表1.字段 = 表2.字段;
left join（左联接）
返回包括左表中所有记录和右表中联接字段相等的记录
语法：SELECT 字段 FROM 表1 LEFT JOIN 表2 ON 表1.字段 = 表2.字段;
##出现NULL是因为第一个表中有多出第二个表的数值，第二个表为了对应上用NULL代替
right join（右联接）
返回包括右表中的所有记录和左表中联接字段相等的记录
语法：SELECT 字段 FROM 表1 RIGHT JOIN 表2 ON 表1.字段 = 表2.字段;
十一、CREATE VIEW 视图
可被当作虚拟表或存储查询
视图跟表格不同的是，表格中有实际存储资料，而视图是建立在表格之上的一个架构，它本身并不实际存储资料视图不含有数据，只存储它的定义，它的用途一般可以简化复杂的查询。比如你要对几个表格进行连接查询，而且还要进行统计排序等操作，写SQL语句会很麻烦，用视图将几个表格连接起来，然后对这个视图进行查询操作，就和对一个表查询一样，很方便。
格式：CREATE VIEW 视图表名 AS SELECT语句;例：
##查看inner join语句之后表的信息
select B.name,SUM(A.money) from s1 AS A inner join sss AS B on A.id = B.id group by name;
##把刚刚查看到的表创建为C视图
create view C AS select B.name,SUM(A.money) from s1 AS A inner join sss AS B on A.id = B.id group by name;
show tables;
##查看C视图的内容
select * from C;
##删除视图
drop view C;
十二、UNION 联集
将两个SQL语句的结果合并起来，两个SQL语句产生的字段需要是同样的资料种类
UNION:生成结果的资料值将没有重复，且按照字段的顺序进行排序。
格式：SELECT语句1 UNION SELECT语句2;
UNION ALL：将生成结果的资料值都列出来，无论有无重复
格式：SELECT语句1 UNION ALL SELECT语句2;例：
##可以看到重复的值是只会显示一次
select name from s1 union select id from sss;
select name from s1 union select name from sss;
##加上all的话重复的值是不会去重的
select name from s1 union all select id from sss;
十三、交集值
取两个SQL语句结果的交集
##查询下两个表方便接下来操作对比
select * from s1;
select * from sss;
##查询A表中的name字段从A表等值连接B表返回两表中name字段相同行
select A.name from s1 AS A inner join sss AS B on A.name = B.name;
##把on A.name = B.name替换成了using(name)意义相同
select A.name from s1 AS A inner join sss AS B using(name);
##两表没有重复的行，并且确实有交集的时候用
mysql> select name from (select name from s1 union all select name from sss) A group by A.name having count(*) > 1;
##返回值中有www但是不知道是两表中哪一个的
十四、无交集值
显示第一个SQL语句的结果，且与第二个SQL语句没有交集的结果，还不能重复
select distinct name from s1 where (name) not in (select name from sss);
十五、CASE
是SQL用来作为IF-THEN-ELSE之类逻辑的关键字
格式：SELECT CASE （字段名）
WHEN 条件1 THEN 结果1
WHEN 条件2 THEN 结果2
……
ELSE 结果N
END
FROM 表名
#条件可以是一个数值或是公式。ELSE不是必须的。例：
select * from s1;
##当name字段有www数据，此数据对应的money字段+200
select case name when 'www' then money +200 end A,name from s1;
十六、排名计算
表格自我连接（self join），然后将结果依序列出，算出每一行之前（包括那一行本身）有多少行数
##实验前准备
create table s2 (name char(20),score int);
insert into s2 values('zhangsan',25);
insert into s2 values('lisi',26);
insert into s2 values('wangwu',25);
insert into s2 values('zhaoliu',20);
insert into s2 values('qianqi',10);
select * from s2;select A1.name,A1.score,count(A2.score) AS rank from s2 A1,s2 A2 where A1.score < A2.scoscore OR (A1.score=A2.score and A1.name=A2.name) group by A1.name order by A1.score desc;解析：当A1的score字段值小于A2的score字段值、或者两表score字段值相等并且name字段值相等时，从A1和A2表中查询A1的name字段值、A1的score字段值、和A2的score字段的非空值 rank是别名 ，并为A1的name字段分组，A1的score字段降序排序
十七、算中位数
例：
select * from (select A1.name,A1.score,count(A2.score) AS rank from s2 A1,s2 A2 where A1.score < A2.score OR (A1.score=A2.score and A1.name=A2.name) group by A1.name order by A1.score desc) A3 where A3.rank = (select (count(*)+1) DIV 2 from s2);
##报错原因：排名的时候有重复值计算不出来，建议修改重复值
十八、算累积总计
select A1.*,sum(A2.score) sum_socore from s2 A1,s2 A2 where A1.score < A2.score or(A1.score=A2.score and A1.name=A2.name) group by A1.name order by A1.score desc;
十九、算总合百分比
##select sum(socre) from SCORE
是为了算出字段值总合，然后每一行一一除以总合，算出每行的总合百分比。
select A1.*,A1.score/(select sum(score) from s2) z_sum from s2 A1,s2 A2 where A1.score < A2.score or (A1.score=A2.score and A1.name=A2.name) group by A1.name;
算累计总合百分比
##使用truncate截取结果的小数位两位，并用连接符加上%
select A1.name,A1.score,sum(A2.score),TRUNCATE(sum(A2.score)/(select sum(score) from s2),2) ||'%' Z from s2 A1,s2 A2 where A1.score < A2.score or (A1.score=A2.score and A1.name=A2.name) group by A1.name order by A1.score desc;
二十、空值（null）和无值（’’）的区别
无值的长度为0，不占用空间；而空值null 的长度是null，是占用空间的
IS NULL或者IS NOT NULL，是用来判断字段是不是NULL或者不是NULL，是不能查出是不是无值的
无值的判断使用=’‘或者<>’'来处理。<>代表不等于
在通过count()指定字段统计又多少行数时，如果遇到NULL值会自动忽略掉，遇到空值会自动加入记录中进行计算
例：
create table s3(site varchar(20));
insert into s3 values('nanjing');
insert into s3 values('beijing');
insert into s3 values('');
insert into s3 values('taijin');
insert into s3 values();
insert into s3 values('');
select * from s3;
##可以看出无值的长度为0，空值为NULL
select length(site) from s3;
select * from s3 where site is NULL;
select * from s3 where site is not NULL;
select * from s3 where site ='';
select * from s3 where site <> '';
二十一、存储过程（与Shell函数差不多）
存储过程是一组为了完成特定功能的SQL语句集合
存储过程在使用过程中是将常用或者复杂的工作预先使用SQL语句写好并用一个指定的名称来进行储存，这个过程经编译和优化后存储在数据库服务器中，当需要使用该存储过程时，只需要调用它即可，存储过程在执行上比传统SQL速度更快，执行效率更高。
存储过程的优点
- 执行一次后，会将生成的二进制代码驻留缓冲区，提高执行效率
- SQL语句加上控制语句的集合，灵活性高
- 在服务器端存储，客户端调用时，降低网络负载
- 可多次重复被调用，可随时修改，不影响客户端调用
- 可完成所有的数据库操作，也可控制数据库的信息访问权限
格式：
DELIMITER !! #将语句的结束符号从分号；临时修改，以防出问题，可以自定义
CREATE PROCEDURE XXX() #创建存储过程，过程名自定义,（）可带参数
BEGIN #过程体以关键字BEGIN开始
select * from xxx; #过程体语句
END!! #过程体以关键字END结尾
DELIMITER ; #将语句的结束符号恢复为分号
call XXX; #调用存储过程
====查看存储过程====
show create procedure [数据库.]储存过程名; #查看某个储存过程的具体信息
show create procedure XXX;
show procedure status [like '%XXX%'] \G例：
DELIMITER !!
CREATE PROCEDURE KIND2()
BEGIN
select * from s2;
END!!
DELIMITER ;
CALL KIND2;
##查看储存过程kind2的具体信息
show procedure status like '%KIND2%'\G
存储过程的参数
IN 输入参数，表示调用者向过程传入值（传入值可以是字面量或变量）
OUT输出参数：表示过程向调用者传出值（可以返回多个值，传出值只能是变量）
例：
DELIMITER !!
CREATE PROCEDURE KIND3(IN place char(20))
BEGIN
select * from s2 where name=place;
END!!
DELIMITER ;
CALL KIND3('wangwu');
（1）存储过程的条件语句
DELIMITER !!
##解释：创建存储过程KIND4
CREATE PROCEDURE KIND4(IN num int(10))
BEGIN
##定义变量var字符类型
declare var int;
##var=输入参数num的值*2
set var=num*2;
##如果var>=10
if var>=10 then
##将s1表中的money字段值加1
update s1 set money=money+1;
else
##将s1表中的money字段值减1
update s1 set money=money-1;
end if;
END!!
DELIMITER ;
CALL KIND4(5);
（2）循环语句while
##创建一个新表
create table kkk(id int);
DELIMITER !!
CREATE PROCEDURE KIND5()
BEGIN
declare var int;
set var=0;
while var<5 do
insert into kkk values(var);
set var=var+1;
end while;
END!!
DELIMITER ;
CALL KIND5;
select * from kkk;
