目录
9、having 用于过滤由group by语句返回的记录集
一、高级SQL语句
1、select 查询表中一个或多个字段的数据
语法:select 字段1,字段2,... 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 store_name from store_info where sales>1000;
4、and与or 且与或
语法:select 字段 from 表名 where 条件1 and|or 条件2;
例:select store_name from store_info where sales >200 and sales <1000;
5、in 显示在某个范围值内 的字段的信息
语法:select 字段 from 表名 where 字段 in (值1,值2,....);
例:select store_name from store_info where store_name ('Los Angeles','Houston');
6、between 显示两个值范围内的数据记录
语法:select 字段 from 表名 where 字段 between 值1 and 值2;
例:select store_name,sales from store_info where sales between 300 and 1000;
7、order by 对字段进行排序
默认升序 (asc) 的排序方式,使用desc实现降序排序
语法:select 字段 from 表名 [ where 条件 ] order by 字段 asc|desc;
例:select * from store_info order by sales desc;
8、group by 对字段进行分组汇总
语法:select 字段 from 表名 group by 字段;
例:select store_name,sum(sales),date from store_info group by store_name;
9、having 用于过滤由group by语句返回的记录集
通俗来说,就是用来过滤出符合条件的group by语句所返回的内容
语法:select 字段 from 表名 group by 字段 having 条件表达式; #条件表达式必须是使用函数的条件表达式
例: select store_name,sum(sales),date from store_info group by store_name having sum(sales) > 500;
10、like 模糊查询
使用通配符进行模糊查询
通配符
%:百分号表示零个、一个或多个字符
_:下划线表示单个字符
语法:select 字段 from 表名 where 字段 like '模糊查询的内容';
例:select store_name from store_info where store_name like '_os%';
11、别名 字段别名,表格别名
语法:select 别名2.字段 [as] 别名1 from 表名 [as] 别名2;
例:select A.store_name name from store_info as A ;
12、exists 用来测试内查询有没有产生任何结果
类似布尔值是否为真,如果有的话,系统就会执行外查询中的SQL语句。若是没有的话,那整个 SQL 语句就不会产生任何结果。
语法:select 字段1 from 表1 where exists (select 字段2 from 表2 where 条件表达式 )
例:SELECT SUM(Sales) FROM Store_Info WHERE EXISTS (SELECT * FROM location WHERE Region = 'West');
二、多表查询
多表查询方式:内连接、左连接、右连接、联集
内连接(inner join)
语法:select * from 表1 inner join 表2 on 表1.字段=表2.字段
例:select * from location A inner join store_info B on A.store_name=B.store_name;
左连接(left join)
语法:select * from 表1 left join 表2 on 表1.字段=表2.字段
例:select * from location A left join store_info B on A.store_name=B.store_name;
右连接(right join)
语法:select * from 表1 right join 表2 on 表1.字段=表2.字段
例:select * from location A right join store_info B on A.store_name=B.store_name;
联集 ( union | union all )
语法:[SELECT 语句 1] UNION [SELECT 语句 2];
[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;
取两个表无交集的值
1、取左表中存在,而右表中不存在的数据
语法:select 字段 from 左表 where 字段 not in (select 字段 from 右表)
如:select store_name from location where store_name not in (select store_name from store_info);
2、取右表中存在,而左表中不存在的数据
语法:select 字段 from 右表 where 字段 not in (select 字段 from 左表)
如:select store_name from store_info where store_name not in (select store_name from location);
三、函数
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...) | 返回集合中最小的值,也可以返回多个字段的最小的值 |
例子:
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);
2、聚合函数
函数 | 解释 |
avg() | 返回指定列的平均值 |
conut() | 返回指定列中非 NULL 值的个数 |
min() | 返回指定列的最小值 |
max() | 返回指定列的最大值 |
sum() | 返回指定列的所有值之和 |
例1:SELECT avg(Sales) FROM store_info;
例2:SELECT count(Store_Name) FROM store_info;
例3:SELECT count(DISTINCT Store_Name) FROM store_info;
例4:SELECT max(Sales) FROM store_info;
例5:SELECT min(Sales) FROM store_info;
例6:SELECT sum(Sales) FROM store_info;
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 次 |
space(x) | 返回 x 个空格 |
strcmp(x,y) | 比较 x 和 y,返回的值可以为-1,0,1 |
reverse(x) | 将字符串 x 反转 |
trim()函数
语法:SELECT TRIM ([ [位置] [要移除的字符串] FROM ] 字符串);
#[位置]:的值可以为 LEADING (起头), TRAILING (结尾), BOTH (起头及结尾)。
#[要移除的字符串]:从字串的起头、结尾,或起头及结尾移除的字符串。缺省时为空格。例1:SELECT TRIM(LEADING 'Ne' FROM 'New York');
concat () 函数
#如sql_mode开启了PIPES_AS_CONCAT,"||"视为字符串的连接操作符而非或运算符,和字符串的拼接函数Concat相类似,这和Oracle数据库使用方法一样的
例2:SELECT concat(Region, Store_Name) FROM location WHERE Store_Name = 'Boston';
例3:SELECT Region || ' ' || Store_Name FROM location WHERE Store_Name = 'Boston';
substr () 函数
例4:SELECT substr(Store_Name,3) FROM location WHERE Store_Name = 'Los Angeles';
例5:SELECT substr(Store_Name,2,4) FROM location WHERE Store_Name = 'New York';
length() 函数
例6:SELECT Region,length(Store_Name) FROM location;
replace () 函数
例7:SELECT REPLACE(Region,'ast','astern')FROM location;
四、视图
视图,可以被当作是虚拟表或存储查询。
视图跟表格的不同是,表格中有实际储存数据记录,而视图是建立在表格之上的一个架构,它本身并不实际储存数据记录。
临时表在用户退出或同数据库的连接断开后就自动消失了,而视图不会消失。
视图不含有数据,只存储它的定义,它的用途一般可以简化复杂的查询。比如你要对几个表进行连接查询,而且还要进行统计排序等操作,写SQL语句会很麻烦的,用视图将几个表联结起来,然后对这个视图进行查询操作,就和对一个表查询一样,很方便。
1、创建视图
语法: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;
2、删除视图
语法:drop view 视图表名;
如:DROP VIEW V_REGION_SALES;
注意:在创建视图表时,后面的select语句所查询的字段是原表中的字段时,视图表中的数据是可以修改的;
若在创建视图表时,后面的select语句所查询的字段是通过聚合函数处理过的或被处理过的数据,视图表中的数据是不可以修改的。
五、case语句及正则表达匹配
1、case语句
case是 SQL 用来做为 IF-THEN-ELSE 之类逻辑的关键字
语法:
SELECT CASE ("字段名")
WHEN "条件1" THEN "结果1"
WHEN "条件2" THEN "结果2"
...
[ELSE "结果N"]
END
FROM "表名";
如: select store_name,case store_name
-> when 'Los Angeles' then sales*2
-> when 'Boston' then 2000
-> else sales
-> end
-> "New Sales",date from store_info;
2、空值(NULL)与无值(“”)的区别
- 无值的长度为 0,不占用空间的;而 NULL 值的长度是 NULL,是占用空间的。
- IS NULL 或者 IS NOT NULL,是用来判断字段是不是为 NULL 或者不是 NULL,不能查出是不是无值的。
- 无值的判断使用=''或者<>''来处理。<> 代表不等于。
- 在通过 count()指定字段统计有多少行数时,如果遇到 NULL 值会自动忽略掉,遇到无值会加入到记录中进行计算。
3、正则匹配
匹配模式 | 描述 |
^ | 匹配文本的开始字符 ‘^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 |
[abcd] | 匹配字符集合中的任意一个字符 ‘[abcd]’ 匹配 a 或者 b 或者 c或者d |
[^abcd] | 匹配不在括号中的任何字符 ‘[^abcd]’ 匹配不包含 a 或者 b或者 c或者d 的字符串 |
{n} | 匹配前面的字符串 n 次 ‘g{2}’ 匹配含有 2 个 g 的字符串 |
{n,m} | 匹配前面的字符串至少 n 次,至多m 次 ‘f{1,3}’ 匹配 f 最少 1 次,最多 3 次 |
语法: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';
六、存储过程
存储过程是一组为了完成特定功能的SQL语句集合。
存储过程在使用过程中是将常用或者复杂的工作预先使用SQL语句写好并用一个指定的名称存储起来,这个过程经编译和优化后存储在数据库服务器中。当需要使用该存储过程时,只需要调用它即可。存储过程在执行上比传统SQL速度更快、执行效率更高。
1、存储过程的优点
1、执行一次后,会将生成的二进制代码驻留缓冲区,提高执行效率
2、SQL语句加上控制语句的集合,灵活性高
3、在服务器端存储,客户端调用时,降低网络负载
4、可多次重复被调用,可随时修改,不影响客户端调用
5、可完成所有的数据库操作,也可控制数据库的信息访问权限
2、创建存储过程
#首先使用delimiter修改语句的结束符,可以是$$,##,!!等自定义的符号
delimiter $$
#其次开始创建存储过程
create procedure 存储过程名()
->begin #关键字begin开始
->SQL语句序列
->end$$ #存储过程的结束语句
#最后再将SQL的语句结束符改回来成分号“;”
delimiter ;
#调用存储过程
call 存储过程名
#查看存储过程
show create peocedure 存储过程名;
或
show procedure status 存储过程名\G
如:
delimiter ##
create procedure proc1()
->begin
->create table t1 (id int,name varchar(20),age int,primary key(id));
->insert into t1 values(1,'张三',18);
->insert into t1 values(2,'李四',19);
->insert into t1 values(3,'王五',20);
->end##
delimiter ;
call proc1();
3、存储过程中的参数
IN 输入参数:表示调用者向过程传入值(传入值可以是字面量或变量)
OUT 输出参数:表示过程向调用者传出值(可以返回多个值)(传出值只能是变量)
INOUT 输入输出参数:既表示调用者向过程传入值,又表示过程向调用者传出值(值只能是变量)
1)IN输入参数
实例:
delimiter $$
create procedure proc2(in inname char(16))
->begin
->select * from store_info where store_name=inname;
->end$$
delimiter ;
call proc2('Boston');
2)OUT输出参数
实例:
delimiter $$
mysql> create procedure prco3(in inname varchar(20),out outage int)
-> begin
-> select age into outage from t1 where inname=name;
-> end$$
delimiter ;
call proc3('李四',@age);
select @age;
3)INOUT输入输出参数
实例:
delimiter $$
mysql>creater procedure proc4(inout inage int)
->begin
->select age into inage from t1 where age>inage;
->end$$
delimiter ;
set @age=19;
call proc4(@age);
seect @age;
4、删除存储过程
语法:drop procedure if exists 存储过程名;
#仅当存在时删除,不添加 IF EXISTS 时,如果指定的过程不存在,则产生一个错误
实例:
drop procedure if exists prco3;
5、存储过程的控制语句
1)条件语句
if-then-else....end if
实例:
DELIMITER $$
CREATE PROCEDURE proc6(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 proc6(6);
select * from t;
2)循环语句
while ... end while
实例:
delimiter $$
create procedure proc7()
->begin
->declare var int(10);
->set var=0;
->create table t2(id int,name char(10));
->while var <5
->do
->insert into t2 values(var,concat('a',var));
->set var=var+1;
->end while;
->end $$
delimiter ;
call proc7();
select * from t2;