目录
1.1SELECT ——显示表格中一个或数个字段的所有数据记录
1.7 通配符 ——通常通配符与like 一起使用进行模糊查询
9、CREATE VIEW ——视图,可以被当作是虚拟表或存储查询。
无交集值——显示第一个SQL语句的结果,且与第二个SQL语句没有交集的结果
11、CASE——是 SQL 用来做 IF-THEN-ELSE 之类逻辑的关键字
1、SQL语句
先创建与两个表
use my;
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');
create table store_info(store_name char(20),sales int(10),date char(10));
insert into store_info values('Los Angeles',1500,'2022-05-30');
insert into store_info values('Houston',250,'2022-05-31');
insert into store_info values('Los Angeles',300,'2022-06-01');
insert into store_info values('Boston',700,'2022-06-02');
1.1SELECT ——显示表格中一个或数个字段的所有数据记录
语法:SELECT "字段" FROM "表名";
例:
SELECT store_Name FROM store_info; #字段名不分大小写
select store_Name,sales FROM store_info;
1.2DISTINCT ——不显示重复的数据记录
语法:SELECT DISTINCT "字段" FROM "表名";
select distinct Store_Name FROM store_info;
1.3WHERE ——有条件查询
语法:SELECT "字段" FROM "表名" WHERE "条件";
SELECT Store_Name FROM store_info WHERE Sales > 100;
select * from store_info where store_name='Houston';
1.4AND OR ——且 或
语法:SELECT "字段" FROM "表名" WHERE "条件1" {[AND|OR] "条件2"}+ ;
例:
SELECT Store_Name FROM store_info WHERE Sales > 100 OR Sales < 500;
select store_name,sales from store_info where sales > 100 and sales < 500;
select store_name,sales from store_info where (sales > 200 and sales < 500) or sales > 100;
1.5 IN——显示已知的值的数据记录
语法:SELECT "字段" FROM "表名" WHERE "字段" IN ('值1', '值2', ...);
例:
SELECT * FROM store_info WHERE store_name IN ('Los Angeles', 'Houston');
SELECT * FROM store_info WHERE store_name not in ('Los Angeles', 'Houston');
1.6BETWEEN ——显示两个值范围内的数据记录
语法:SELECT "字段" FROM "表名" WHERE "字段" BETWEEN '值1' AND '值2';
SELECT * FROM store_info WHERE sales BETWEEN 200 and 700;
SELECT * FROM store_info WHERE Date BETWEEN '2022-05-02' AND '2022-06-02';
1.7 通配符 ——通常通配符与like 一起使用进行模糊查询
通配符 | 作用 |
% | 百分号表示零个、一个或多个字符 |
_ | 下划线表示单个字符 |
%A_Z% | 所有以'A'起头,另一个任何值的字符,且'z'为结尾的字符串。例如,'ABZ'和 'A2Z' |
%XYZ | 所有以 'XYZ' 结尾的字符串。例如,'WXYZ' 和 'ZZXYZ' |
%AN% | 所有包含'AN'的字符串 例如,'LOSANGELES'和'SAN FRANCISCO' |
ABC% | 所有以ABC开头的字符串 如:'ABCD'、'ABCABC' |
_AN% | 所有 第二个字母为 A 第三个字母 为N 的字符串 例如,'SAN FRANCISCO'符合这个模式,而'LOSANGELES'则不符合这个模式。 |
select * from store_info where store_Name like '_o%s';
select * from store_info where store_Name like '%ON';
select * from store_info where store_Name like '_os%';
1.8like——匹配一个模式来找出我们要的数据记录
语法:SELECT "字段" FROM "表名" WHERE "字段" LIKE {模式};
例:
SELECT * FROM Store_Info WHERE Store_Name like '%os%';
1.9ORDER BY——按关键字排序
语法:SELECT "字段" FROM "表名" [WHERE "条件"] ORDER BY "字段" [ASC, DESC];
#ASC 是按照升序进行排序的,是默认的排序方式。
#DESC 是按降序方式进行排序。
例:
SELECT Store_Name,Sales,Date FROM store_info ORDER BY Sales DESC;
SELECT Store_Name,Sales,Date FROM store_info ORDER BY Sales;
2、函数
2.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的最大整数 |
qreatest(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.2聚合函数:
函数 | 含义 |
---|---|
avg() | 返回指定列的平均值 |
count() | 返回指定列中非 NULL 值的个数 |
min() | 返回指定列的最小值 |
max() | 返回指定列的最大值 |
sum(x) | 返回指定列的所有值之和 |
SELECT avg(Sales) FROM store_info; #查看sales字段的平均值
SELECT max(Sales) FROM store_info; #查看sales字段的最小值
SELECT min(Sales) FROM store_info; #查看sales字段的最大值
SELECT sum(Sales) FROM store_info; #查看sales字段的总和
SELECT count(Store_Name) FROM store_info; #查看store_name字段的非null个数
SELECT count(DISTINCT Store_Name) FROM store_info; #查看store_name字段去重后的非null个数
count函数
创建一个表格
create table city(name char(20));
insert into city values('xuzhou');
insert into city values('nanjing');
insert into city values('hangzhou');
insert into city values();
insert into city values();
select * from city;
count(*) 包括了所有的列的行数,全表扫描在统计结果的时候,不会忽略列值为 NULL
count(列名) 只包括列名那一列的行数,在统计结果的时候,会忽略列值为 NULL 的行
SELECT count(name) FROM city;
SELECT count(*) FROM city;
2.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 反转 |
字符串的拼接contact
SELECT concat(字段1, 字段2) FROM 表名 [WHERE 字段名 = '字段值'];
例:
select concat(region,store_name) from location; #将location表的region与store_name字段拼接起来
select concat(region,' ',store_name) from location; #将location表的region与store_name字段拼接起来,字段之间有空格
字符串的拼接||
如果/etc/my.cnf文件中的sql_mode开启了PIPES_AS_CONCAT,"||"视为字符串的连接操作符而非或运算符,和字符串的拼接函数Concat相类似,这和Oracle数据库使用方法一样的
查看/etc/my.cnf文件中的sql_mode开启了PIPES_AS_CONCAT是否开启
SELECT 字段名 || 字段名 FROM 表名 [WHERE 字段名 = '字段值'];
例:
SELECT Region || Store_Name FROM location;
#将location表的Region与Store_Name字段的值连接起来
SELECT Region || ' ' || Store_Name FROM location WHERE Store_Name = 'Boston';
#将location表中的Store_Name为Boston的Region与Store_Name字段的值连接起来,且字段之间有空格
截取字符串substr
SELECT substr(Store_Name,2) FROM location WHERE Store_Name = 'Los Angeles';
select substr(region || ' ' || store_name,5) from location;
SELECT substr(Store_Name,2,4) FROM location WHERE Store_Name = 'New York';
去除指定格式的值TRIM
SELECT TRIM ([ [位置] [要移除的字符串] FROM ] 字符串);
#[位置]:的值可以为 LEADING (起头), TRAILING (结尾), BOTH (起头及结尾)。
#[要移除的字符串]:从字串的起头、结尾,或起头及结尾移除的字符串。缺省时为空格。
例:
SELECT TRIM(LEADING 'Ne' FROM 'New York');
select trim(leading 'New' from (select store_name from location where store_name='New York'));
返回字符串长度length
SELECT Region,length(Store_Name) FROM location;
字符串替换replace
SELECT REPLACE(Region,'ast','astern')FROM location;
3、GROUP BY
GROUP BY有两个作用为汇总与分组
- 对GROUP BY后面的字段的查询结果进行汇总分组,通常是结合聚合函数一起使用的
- GROUP BY 有一个原则,凡是在 GROUP BY 后面出现的字段,必须在 SELECT 后面出现;
- 凡是在 SELECT 后面出现的、且未在聚合函数中出现的字段,必须出现在 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;
select store_name,count(store_name) from store_info group by store_name;
4、HAVING
- 用来过滤由 GROUP BY 语句返回的记录集,通常与 GROUP BY 语句联合使用
- HAVING 语句的存在弥补了 WHERE 关键字不能与聚合函数联合使用的不足。
语法:SELECT "字段1", SUM("字段2") FROM "表格名" GROUP BY "字段1" HAVING (函数条件);
SELECT Store_Name, SUM(Sales) FROM store_info GROUP BY Store_Name HAVING SUM(Sales) > 1500;
5、别名——字段別名 表格別名
语法:SELECT "表格別名"."字段1" [AS] "字段別名" FROM "表格名" [AS] "表格別名";
SELECT Store_Name, SUM(Sales) FROM store_info A GROUP BY Store_Name;
SELECT A.Store_Name Store, SUM(A.Sales) "Total Sales" FROM store_info A GROUP BY A.Store_Name;
6、子查询 ——连接表格
在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');
SELECT SUM(A.Sales) FROM store_info A WHERE A.Store_Name IN (SELECT Store_Name FROM location B WHERE B.Store_Name = A.Store_Name);
7、EXISTS——用来测试内查询有没有产生任何结果
类似布尔值是否为真
如果有的话,系统就会执行外查询中的SQL语句。若是没有的话,那整个 SQL 语句就不会产生任何结果。
语法:SELECT "字段1" FROM "表格1" WHERE EXISTS (SELECT * FROM "表格2" WHERE "条件");
例:
SELECT SUM(Sales) FROM store_info WHERE EXISTS (SELECT * FROM location WHERE Region = 'West');
8、连接查询
连接类型
- inner join(内连接):只返回两个表中联结字段相等的行
- left join(左连接):返回包括左表中的所有记录和右表中联结字段相等的记录
- right join(右连接):返回包括右表中的所有记录和左表中联结字段相等的记录
左链接
SELECT * FROM location A LEFT JOIN store_info B on A.Store_Name = B.Store_Name ;
右连接
UPDATE store_info SET store_name='Washington' WHERE sales=300;
SELECT * FROM location A RIGHT JOIN store_info B on A.Store_Name = B.Store_Name ;
内连接
内连接一
SELECT * FROM location A INNER JOIN store_info B on A.Store_Name = B.Store_Name ;
内连接二
SELECT * FROM location A, store_info B WHERE A.Store_Name = B.Store_Name;
SELECT A.Region REGION, SUM(B.Sales) SALES FROM location A, store_info B
WHERE A.Store_Name = B.Store_Name GROUP BY REGION;
9、CREATE VIEW ——视图,可以被当作是虚拟表或存储查询。
- 视图跟表格的不同是,表格中有实际储存数据记录,而视图是建立在表格之上的一个架构,它本身并不实际储存数据记录。
- 临时表在用户退出或同数据库的连接断开后就自动消失了,而视图不会消失。
- 视图不含有数据,只存储它的定义,它的用途一般可以简化复杂的查询。比如你要对几个表进行连接查询,而且还要进行统计排序等操作,写SQL语句会很麻烦的,用视图将几个表联结起来,然后对这个视图进行查询操作,就和对一个表查询一样,很方便。
- 插入数据时要视图表与原表结构相同才可以插入,否则会报错
创建视图语法:CREATE VIEW "视图表名" AS "SELECT 语句";
删除视图语法:DROP VIEW 视图表名;
保存的是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;
10、UNION ——联集
将两个SQL语句的结果合并起来,两个SQL语句所产生的字段需要是同样的数据记录种类
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, store_info B where A.store_name = B.store_name;
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);
取两个SQL语句结果的交集,且没有重复
SELECT DISTINCT A.Store_Name FROM location A INNER JOIN Store_Info B USING(Store_Name);
左连接
select * from location A left join store_info B on A.store_name = B.store_name;
select distinct A.store_name from location A left join store_info B on A.store_name = B.store_name where B.store_name is not null;
右连接
select * from location A right join store_info B using(store_name);
select B. store_name from location A right join store_info B using(store_name);
内查询
select distinct store_name from location where store_name in (select store_name from store_info);
union与group by
select distinct store_name from location union all select distinct store_name from store_info;
#将两个表的store_name值结合起来而且不去重显示
create view v_store_name as select distinct store_name from location union all select distinct store_name from store_info;
#将store_name创建视图表
select store_name,count(*) from v_store_name group by store_name;
#对视图表的store_name字段分组汇总,计算值的个数
select store_name from v_store_name group by store_name having count(*) >1;
#统计store_name字段数量大于1,作为两个表的交集
无交集值——显示第一个SQL语句的结果,且与第二个SQL语句没有交集的结果
11、CASE——是 SQL 用来做 IF-THEN-ELSE 之类逻辑的关键字
语法:
SELECT CASE ("字段名")
WHEN "条件1" THEN "结果1"
WHEN "条件2" THEN "结果2"
...
[ELSE "结果N"]
END
FROM "表名";
# "条件" 可以是一个数值或是公式。 ELSE 子句则并不是必须的。
mysql> select store_name,case store_name
-> when 'houston' then sales + 1000
-> when 'boston' then sales * 2
-> else sales
-> END
-> as "new sales",date from store_info;
#"New Sales" 是用于 CASE 那个字段的字段名
空值(NULL) 和 无值('') 的区别
- 无值的长度为 0,不占用空间的;而 NULL 值的长度是 NULL,是占用空间的。
- IS NULL 或者 IS NOT NULL,是用来判断字段是不是为 NULL 或者不是 NULL,不能查出是不是无值的。
- 无值的判断使用=''或者<>''来处理。<> 代表不等于。
- 在通过 count()指定字段统计有多少行数时,如果遇到 NULL 值会自动忽略掉,遇到无值会加入到记录中进行计算。、
select length(null),length(''),length('abc');
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;
12、正则表达式
匹配模式 | 描述 | 实例 |
---|---|---|
^ | 匹配文本的开始字符 | '^bd'匹配以bd开头的字符串 |
$ | 匹配文本的结束字符 | 'qn$'匹配以gn结尾的字符串 |
. | 匹配任何单个字符 | 's.t'匹配任何s和t之间有—个字符的字符串 |
* | 匹配零个或多个在它前而的字符 | 'fo*t'匹配t前而有任意个。 |
+ | 匹配前面的字符1次或多次 | 'hom+'匹配以ho开头,后而至少一个m的字符串. |
. | 匹配任何单个字符 | 's.t'匹配任何s和t之间有—个字符的字符串 |
* | 匹配零个或多个在它前而的字符 | 'fo*t'匹配t前而有任意个。 |
+ | 匹配前面的字符1次或多次 | 'hom+'匹配以ho开头,后而至少一个m的字符串. |
字符串 | 匹配包含指定的字符串 | 'clo'匹配含有clo的字符串 |
p1lp2 | 匹配p1或p2 | 'bg |
[...] | 匹配字符集合中的任意一个字符 | '[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 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';
![](https://i-blog.csdnimg.cn/blog_migrate/e7a0b694ba058817f72935b4d597b550.png)
13、存储过程
存储过程是一组为了完成特定功能的SQL语句集合。
存储过程在使用过程中是将常用或者复杂的工作预先使用SQL语句写好并用一个指定的名称存储起来,这个过程经编译和优化后存储在数据库服务器中。当需要使用该存储过程时,只需要调用它即可。存储过程在执行上比传统SQL速度更快、执行效率更高。
存储过程的优点:
- 执行一次后,会将生成的二进制代码驻留缓冲区,提高执行效率
- SQL语句加上控制语句的集合,灵活性高
- 在服务器端存储,客户端调用时,降低网络负载
- 可多次重复被调用,可随时修改,不影响客户端调用
- 可完成所有的数据库操作,也可控制数据库的信息访问权限
创建与调用存储过程
DELIMITER $$ #将语句的结束符号从分号;临时改为两个$$(可以是自定义)
CREATE PROCEDURE Proc() #创建存储过程,过程名为Proc,不带参数
-> BEGIN #过程体以关键字 BEGIN 开始
-> select * from store_info; #过程体语句
-> END $$ #过程体以关键字 END 结束
DELIMITER ; #将语句的结束符号恢复为分号
CALL Proc; #调用存储过程
查看存储过程
SHOW CREATE PROCEDURE [数据库.]存储过程名; #查看某个存储过程的具体信息
SHOW CREATE PROCEDURE Proc;
SHOW PROCEDURE STATUS [LIKE '%Proc%'] \G #查看存储过程状态
存储过程的参数
- IN 输入参数:表示调用者向过程传入值(传入值可以是字面量或变量)
- OUT 输出参数:表示过程向调用者传出值(可以返回多个值)(传出值只能是变量)
- INOUT 输入输出参数:既表示调用者向过程传入值,又表示过程向调用者传出值(值只能是变量)
DELIMITER $$
CREATE PROCEDURE Proc1(IN inname CHAR(16))
-> BEGIN
-> SELECT * FROM store_info WHERE Store_Name = inname;
-> END $$
DELIMITER ;
#调用参数
CALL Proc1('Boston');
删除存储过程
存储过程内容的修改方法是通过删除原有存储过程,之后再以相同的名称创建新的存储过程。
DROP PROCEDURE IF EXISTS Proc; #仅当存在时删除,不添加 IF EXISTS 时,如果指定的过程不存在,则产生一个错误
存储过程的控制语句
create table t (id int(10));
insert into t values(10);
(1)条件语句if-then-else ···· end if
DELIMITER $$
CREATE PROCEDURE proc2(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 Proc2(6);
(2)循环语句while ···· end while
DELIMITER $$
CREATE PROCEDURE proc3()
-> begin
-> declare var int(10);
-> set var=0;
-> while var<6 do
-> insert into t values(var);
-> set var=var+1;
-> end while;
-> end $$
DELIMITER ;
CALL Proc3;