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 |
+----------+--------------+
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');
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 |
+--------------+---------+------------+
高阶查询
---- select ----显示表格中一个或数个字段的所有数据记录
语法：select "字段" from "表名";
select store_name from store_info;
---- distinct ----不显示重复的数据记录
语法：select distinct "字段" from "表名";
select distinct store_name from store_info;
---- where ----有条件查询
语法：select "字段" from "表名" where "条件";
select store_name from store_info where sales > 1000;
---- and or ----且 或
语法：select "字段" from "表名" where "条件1" {[and|or] "条件2"}+ ;
select store_name from store_info where sales > 1000 or (sales < 500 and sales > 200);
---- in ----显示已知的值的数据记录
语法：select "字段" from "表名" where "字段" [not]in ('值1', '值2', ...);
select * from store_info where store_name in ('los angeles', 'houston');
---- between ----显示两个值范围内的数据记录
语法：select "字段" from "表名" where "字段" between '值1' and '值2';
select * from store_info where date between '2020-12-06' and '2020-12-10';
---- 通配符 ----通常通配符都是跟 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' 则不符合这个模式。
---- like ----匹配一个模式来找出我们要的数据记录
语法：select "字段" from "表名" where "字段" like {模式};
select * from store_info where store_name like '%os%';
---- order by ----按关键字排序
语法：select "字段" from "表名" [where "条件"] order by "字段" [asc, desc];
#asc 是按照升序进行排序的，是默认的排序方式。
#desc 是按降序方式进行排序。
select store_name,sales,date from store_info order by sales desc;
总结
mysql 高阶查询
select 字段 from 表 where 字段 = 值 [and|or 字段 = 值];
!= <> > >= < <=
[not] in (值1, 值2, ...);
between 值1 and 值2;
like '通配符表达式'; % _
regexp '正则表达式'; ^ $ . * {} [] |
select 字段 from 表 order by 字段 ASC|DESC; #排序
select 字段,聚合函数(字段) from 表 group by 字段; #分组，通常会结合聚合函数使用
select 字段,聚合函数(字段) from 表 group by 字段 having 条件表达式; #having的作用：根据group by分组后的结果再进行条件过滤
函数
---- 函数 ----
数学函数：
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);
聚合函数：
avg() 返回指定列的平均值
count() 返回指定列中非 null 值的个数
min() 返回指定列的最小值
max() 返回指定列的最大值
sum(x) 返回指定列的所有值之和
select avg(sales) from store_info;
select count(store_name) from store_info;
select count(distinct store_name) from store_info;
select max(sales) from store_info;
select min(sales) from store_info;
select sum(sales) from store_info;
city 表格
+----------+
| name |
|----------|
| beijing |
| nanjing |
| shanghai |
| <null> |
| <null> |
+----------+
select count(name) from city;
select count(*) from city;
#count(*) 包括了所有的列的行数，在统计结果的时候，不会忽略列值为 null
#count(列名) 只包括列名那一列的行数，在统计结果的时候，会忽略列值为 null 的行
字符串函数：
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 concat(region, store_name) from location where store_name = 'boston';
#如sql_mode开启了pipes_as_concat，"||"视为字符串的连接操作符而非或运算符，和字符串的拼接函数concat相类似，这和oracle数据库使用方法一样的
select region || ' ' || store_name from location where store_name = 'boston';
select substr(store_name,3) from location where store_name = 'los angeles';
select substr(store_name,2,4) from location where store_name = 'new york';
select trim ([ [位置] [要移除的字符串] from ] 字符串);
#[位置]：的值可以为 leading (起头), trailing (结尾), both (起头及结尾)。
#[要移除的字符串]：从字串的起头、结尾，或起头及结尾移除的字符串。缺省时为空格。
select trim(leading 'ne' from 'new york');
select region,length(store_name) from location;
select replace(region,'ast','astern')from location;
---- 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;
---- 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;
---- 别名 ----字段別名 表格別名
语法：select "表格別名"."字段1" [as] "字段別名" from "表格名" [as] "表格別名";
select a.store_name store, sum(a.sales) "total sales" from store_info a group by a.store_name;
---- 子查询 ----连接表格，在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);
---- 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');
总结
数学函数 round(x,y) truncate(x,y) rand() greatest(值1, 值2, ....) least(值1, 值2, ....)
聚合函数 sum(字段) avg(字段) max(字段) min(字段) count(字段) count(*)
字符串函数 concat(字段1, 字段2, ....) substr(x,y,z) replace(x,y,z) length(字段)
字段1 || 字段2 substring(x,y,z)
select 字段 AS 字段别名 from 表 AS 表别名;
select 字段 from 表1 where 字段 运算符 (select 字段 from 表2 where 字段);
外查询 内查询 （内查询的结果会作为外查询的条件来使用）
连接查询
location 表格
+----------+--------------+
| region | store_name |
|----------+--------------|
| east | boston |
| east | new york |
| west | los angeles |
| west | houston |
+----------+--------------+
update store_info set store_name='washington' where sales=300;
store_info 表格
+--------------+---------+------------+
| store_name | sales | date |
|--------------+---------+------------|
| los angeles | 1500 | 2020-12-05 |
| houston | 250 | 2020-12-07 |
| washington | 300 | 2020-12-08 |
| boston | 700 | 2020-12-08 |
+--------------+---------+------------+
inner join(内连接)：只返回两个表中联结字段相等的行
left join(左连接)：返回包括左表中的所有记录和右表中联结字段相等的记录
right join(右连接)：返回包括右表中的所有记录和左表中联结字段相等的记录
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 ;
内连接一：
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;
---- create 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;
---- union ----联集，将两个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;
总结
表连接
inner join 内连接，只返回两个表的字段相等的行记录
left join 左连接，返回左表所有的行记录和右表字段相等的行记录，不相等的行返回null
right join 右连接，返回右表所有的行记录和左表字段相等的行记录，不相等的行返回null
union 联集，将两个select查询语句的结果合并，并去重
union all 联集，将两个select查询语句的结果合并，不去重
full outer join 全外连接，返回左表和右表中所有的行记录，MySQL不支持
求交集
内连接
select A.字段 from 左表 as A inner join 右表 as B on A.字段 = B.字段;
select A.字段 from 左表 as A inner join 右表 as B using(同名字段);
左/右连接
select A.字段 from 左表 as A left join 右表 as B on A.字段 = B.字段 where B.字段 is not null;
select B.字段 from 左表 as A right join 右表 as B on A.字段 = B.字段 where A.字段 is not null;
多表查询
select A.字段 from 左表 A, 右表 B where A.字段 = B.字段;
子查询
select A.字段 from 左表 A where A.字段 in (select B.字段 from 右表 B);
分组统计
select A.字段,count(字段) from (select distinct 字段 from 左表 union all select distinct 字段 from 右表) A group by A.字段 having count(字段) > 1;
求左表无交集
select A.字段 from 左表 as A left join 右表 as B on A.字段 = B.字段 where B.字段 is null;
select A.字段 from 左表 A where A.字段 not in (select B.字段 from 右表 B);
求右表无交集
select B.字段 from 左表 as A right join 右表 as B on A.字段 = B.字段 where A.字段 is null;
select B.字段 from 右表 B where B.字段 in (select A.字段 from 左表 A);
求两个表无交集
select A.字段 from 左表 as A left join 右表 as B on A.字段 = B.字段 where B.字段 is null
union
select B.字段 from 左表 as A right join 右表 as B on A.字段 = B.字段 where A.字段 is null;
select A.字段 from (select distinct 字段 from 左表 union all select distinct 字段 from 右表) A group by A.字段 having count(A.字段) = 1;
create view 视图表名 as select distinct 字段 from 左表 union all select distinct 字段 from 右表;
select 字段 from 视图表名 group by 字段 having count(字段) = 1;
create view 视图表名 as select语句;
CASE 语句、正则表达式应用
---- case ----是 sql 用来做为 if-then-else 之类逻辑的关键字
语法：
select case ("字段名")
when "条件1" then "结果1"
when "条件2" then "结果2"
...
[else "结果n"]
end
from "表名";
# "条件" 可以是一个数值或是公式。 else 子句则并不是必须的。
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;
#"new sales" 是用于 case 那个字段的字段名。
---- 空值(null) 和 无值('') 的区别 ----
1．无值的长度为 0，不占用空间的；而 null 值的长度是 null，是占用空间的。
2．is null 或者 is not null，是用来判断字段是不是为 null 或者不是 null，不能查出是不是无值的。
3．无值的判断使用=''或者<>''来处理。<> 代表不等于。
4．在通过 count()指定字段统计有多少行数时，如果遇到 null 值会自动忽略掉，遇到无值会加入到记录中进行计算。
city 表格
+----------+
| name |
|----------|
| beijing |
| nanjing |
| shanghai |
| <null> |
| <null> |
| shanghai |
| |
+----------+
select length(null), length(''), length('1');
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 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';
总结
select case '字段' when 值1或表达式 then 值或表达式 #根据一个字段的条件设置一个新的字段的值
when 值2或表达式 then 值或表达式
....
else 值或表达式 end AS "字段别名"
from 表;
无值'' 和 空值null 的区别？
空值null 的长度为 NULL ，占用空间；可以通过 字段 is null 或 字段 is not null 判断是否为null行；count(字段) 会忽略 null 行
无值'' 的长度为 0 ，不占用空间；可以通过 字段 = '' 或 字段 <> '' 判断是否为无值的行；count(字段) 不会忽略 无值 的行
!= ''
存储过程（数据库脚本）
---- 存储过程 ----
存储过程是一组为了完成特定功能的sql语句集合。
存储过程在使用过程中是将常用或者复杂的工作预先使用sql语句写好并用一个指定的名称存储起来，这个过程经编译和优化后存储在数据库服务器中。当需要使用该存储过程时，只需要调用它即可。存储过程在执行上比传统sql速度更快、执行效率更高。
存储过程的优点：
1、执行一次后，会将生成的二进制代码驻留缓冲区，提高执行效率
2、sql语句加上控制语句的集合，灵活性高
3、在服务器端存储，客户端调用时，降低网络负载
4、可多次重复被调用，可随时修改，不影响客户端调用
5、可完成所有的数据库操作，也可控制数据库的信息访问权限
##创建存储过程##
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');
delimiter $$
mysql> create procedure proc3(in myname char(10), out outname int)
-> begin
-> select sales into outname from t1 where name = myname;
-> end $$
delimiter ;
call proc3('yzh', @out_sales);
select @out_sales;
delimiter $$
mysql> create procedure proc4(inout insales int)
-> begin
-> select count(sales) into insales from t1 where sales < insales;
-> end $$
delimiter ;
set @inout_sales=1000;
call proc4(@inout_sales);
select @inout_sales;
##删除存储过程##
存储过程内容的修改方法是通过删除原有存储过程，之后再以相同的名称创建新的存储过程。如果要修改存储过程的名称，可以先删除原存储过程，再以不同的命名创建新的存储过程。
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;
总结
创建存储过程：
1）修改SQL语句结束符 delimiter $$
2）创建存储过程 use 库名 create procedure 存储过程名() begin SQL语句序列; end$$
3）把结束符改回分号 delimiter ;
4）调用存储过程 call 存储过程名;
查看存储过程
show create procedure 存储过程名;
show procedure status like '存储过程名'\G
存储过程参数（只能用于字段）
in 传入参数
out 传出参数
inout 传入传出参数
create procedure 存储过程名(in 传入参数名 参数数据类型, out 传出参数名 参数数据类型)
begin
select 字段 into 传出参数 from 表 where 字段 = 传入参数;
end$$
call 存储过程名(参数值|@变量名; @变量名); #传入参数指定的值可以是纯量值，也可以是变量名；传出参数指定的值只能是变量名
create procedure 存储过程名(inout 参数名 参数数据类型)
begin
select 字段 into 参数名 from 表 where 字段 = 参数名;
end$$
set @变量名 = 传入的值; #设置传入的值
call 存储过程名(@变量名); #传入传出参数指定的值只能是变量名
select @变量名; #查看传出的值
存储过程控制语句
条件语句：
if 条件表达式 then
SQL语句序列1;
else
SQL语句序列2;
end if;
循环语句：
while 条件表达式
do
SQL语句序列;
set 条件迭代;
end while;
扫一扫
3990
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
