目录
一.准备两个表
use mydb;
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','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');
insert into store_info values('Washington','1000','2020-12-09');
insert into store_info values('Chicago','800','2020-12-10');
二.高级查询方式
1.select
select:显示表格中一个或数个字段的所有数据记录
语法：select 字段1，字段2 from 表名;
select store_name from store_info;
2.distinct
distinct:不显示重复的数据记录
语法：select distinct "字段" from "表名";
select distinct store_name from store_info;
3.where
where:有条件查询
语法：select "字段" from 表名" where "条件";
select * from store_info where sales > 1000;4.and or
and or:且 或
语法：select "字段" from "表名" where "条件1" and "条件2";
select "字段" from "表名" where "条件1" or "条件2";
select * from store_info where sales > 1000 or (sales >=200 and sales <=500)5.in
in:显示已知的值的数据记录
语法：select "字段" from "表名" where "字段" in ('值1'， '值2'， ...); #in,遍历一个取值列表
select * from store_info where store_name in ('Houston','Los Angeles');6.between
between:显示两个值范围内的数据记录
语法：select "字段" from "表名" where "字段" between '值1' and '值2';
SELECT * FROM Store_Info WHERE Date BETWEEN '2020-12-06' AND '2020-12-10';7.通配符
通配符:通常通配符都是跟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' 则不符合这个模式。
如： select * from store_info where store_name like '__us%';
select * from store_info where store_name like '_os%';8.like
like:匹配一个模式来找出我们要的数据记录
语法：select "字段" from "表名" where "字段" like {模式};
select * from store_info where store_name like '%on%';三.运用函数查询
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() | 返回指定列的平均值 |
| count() | 返回指定列中非 NULL 值的个数 |
| min() | 返回指定列的最小值 |
| max() | 返回指定列的最大值 |
| sum(字段) | 返回指定列的所有值之和 |
###返回指定列sales的平均值
select avg(sales) from store_info;
###返回指定列store_name列中非NULL值的个数
select count(store_name) from store_info;
select count(distinct store_name) 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 反转 |
select concat(region,store_name) from location where store_name='Los Angeles';
select concat(region,' ',store_name) from location where store_name='Los Angeles';
#如sql_mode开启了PIPES_AS_CONCAT，"||"视为字符串的连接操作符而非或运算符，和字符串的拼接函数Concat相类似，这和Oracle数据库使用方法一样的
select region || ' ' || store_name from location where store_name='Los Angele
select substr(store_name,3) from location where store_name='Los Angeles';
select * from location;
select substr(store_name,3,4) from location where store_name='Los Angeles';
select trim([ [位置] [要移除的字符串] from ] 字符串);
#[位置]：的值可以为leading(起头),trailing(结尾),both(起头及结尾)。
#[要移除的字符串]：从字串的起头、结尾，或起头及结尾移除的字符串。缺省时为空格。
select trim(leading 'Ne' from 'New York');
select trim(trailing 'aw' from 'Newaw Yorkaw');
select trim(both 'aw' from 'awNewaw Yorkaw');
select region,length(store_name) from location;
select replace(region,'ast','astern') from location;
四.高级查询函数
1.order by
order by:按关键字排序
语法：select "字段" from "表名" [where "条件"] order by "字段" [asc, desc];
#asc是按照升序进行排序的，是默认的排序方式。
#desc是按降序方式进行排序。
select * from store_info order by sales desc;
2.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;
3.having
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) > 1000;
4.别名设置查询
别名:字段別名 表格別名
语法：select "表格別名"."字段1" [as] "字段別名" from "表格名" [as] "表格別名";
select A.store_name store,sum(A.sales) "total sales" from store_info A group p by A.store_name;4.1.字段别名
4.2.表别名
5.子查询语句
子查询:连接表格，在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);
6.exists（类查询）
exists:用来根据条件过滤查询结果，并只返回满足条件的行
注意
- 这里的子查询作为条件进行判断。如果子查询返回至少一行结果，则外部查询的结果将包含该行
语法：select "字段1" from "表格1" where exists (select * from "表格2" where "条件");
select sum(sales) from store_info where exists (select * from location where region = 'West');
7.sql语句执行顺序
五.表连接查询
- MYSQL数据库中常用的表连接有三种
1.inner join(内连接)
- 只返回两个表中联结字段相等的行（有交集的值）
2.left join(左连接)
- 返回包括左表中的所有记录和右表中联结字段相等的记录
- 不相等的行返回NULL
3.right join(右连接)
- 返回包括右表中的所有记录和左表中联结字段相等的记录
- 不相等的行返回NULL
4.full outer join（全外连接）
- 返回左表和右表中所有的行记录，MySQL不支持
5.union（联集）
- 将两个select查询语句的结果合并，并去重
6.union all（联集）
- 将两个select查询语句的结果合并，不去重
六.求交集
1.内连接
select A.字段 from 左表 A inner join 右表 B on A.字段=B.字段;
select A.字段 from 左表 A inner join 右表 B using(字段);2.左连接
select B.字段 from 左表 A left join 右表 B on A.字段 = B.字段 where B.字段 is not null;
3.右连接
select A.字段 from 左表 A left join 右表 B on A.字段 = B.字段 where A.字段 is not null;
4.子查询
select A.字段 from 左表 A where A.字段 in (select B.字段 from 右表 B);
select A.字段 from 左表 A where exists (select B.字段 from 右表 B where A.字段 = B.字段);
5.多表查询
select A.字段 from 左表 A, 右表 B where A.字段 = B.字段;6.联集+分组
select A.字段 from (select distinct 字段 from 左表 union all select distinct 字段 from 右表) A group by A.字段 having count(A.字段) > 1;
#此处的 A表 是由内查询语句返回的结果 抽象出的派生表
七.求无交集
1.求左表无交集
select A.字段 from 左表 A left join 右表 B on A.字段 = B.字段 where B.字段 is null;
select 字段 from 左表 where 字段 not in (select 字段 from 右表);
select A.字段 from 左表 A where not exists (select B.字段 from 右表 B where A.字段 = B.字段);
2.求右表无交集
select B.字段 from 左表 A right join 右表 B on A.字段 = B.字段 where A.字段 is null;
select 字段 from 右表 where 字段 not in (select 字段 from 左表);
select A.字段 from 右表 A where not exists (select B.字段 from 左表 B where A.字段 = B.字段);
3.求两个表无交集
select A.字段 from 左表 A left join 右表 B on A.字段 = B.字段 where B.字段 is null
union all
select B.字段 from 左表 A right join 右表 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;
八.view视图
view视图，可以被当作是虚拟表或存储查询
- 视图跟表格的不同是，表格中有实际储存数据记录，而视图是建立在表格之上的一个架构，它本身并不实际储存数据记录
- 临时表在用户退出或同数据库的连接断开后就自动消失了，而视图不会消失
- 视图不含有数据，只存储它的定义，它的用途一般可以简化复杂的查询。比如你要对几个表进行连接查询，而且还要进行统计排序等操作，写SQL语句会很麻烦的，用视图将几个表联结起来，然后对这个视图进行查询操作，就和对一个表查询一样，很方便
语法：CREATE VIEW "视图表名" AS "SELECT 语句";
定义视图的方法求交集
create view v_store_names as select distinct Store_Name from store_info union all select distinct Store_Name from location; #定义视图
select * from v_store_names group by Store_Name having count(Store_Name)=1;
#求两表的交集
drop view v_sum; #删除视图方法补充
视图表能否修改数据？
- 视图表保存的是select查询语句的定义
- 如果select语句查询的字段是没有被处理过的源表字段，则可以通过视图表修改源表的数据
- 如果select语句查询的字段被函数或group by等处理的字段，则不可以通过视图表修改源表的数据
九.case 条件选择查询语句
- 是 SQL 用来做为 IF-THEN-ELSE 之类逻辑的关键字
select case
when "公式1" then "结果1"
when "公式2" then "结果1"
...
[else "default"] end
#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) 和 无值('') 的区别
- 无值的长度为 0，不占用空间的；而 NULL 值的长度是 NULL，是占用空间的
- IS NULL 或者 IS NOT NULL，是用来判断字段是不是为 NULL 或者不是 NULL，不能查出是不是无值的
- 无值的判断使用=''或者<>''来处理。<> != 代表不等于
- 在通过 count()指定字段统计有多少行数时，如果遇到 NULL 值会自动忽略掉，遇到无值会加入到记录中进行计算
十一.导出、导入数据
1.导出数据
vim /etc/my.cnf
添加
secure_file_priv="" #允许导入、导出文件
systemctl restart mysqld.service
mkdir /opt/mysql_files
chmod 777 /opt/mysql_files
select * into outfile '/opt/mysql_files/stroe.csv' fields terminated by ',' enclosed by '"' lines terminated by '\n' from store_info; #导出文件2.导入数据
create table store_info (Store_Name char(20),Sales int(10),Date char(10));
load data infile '/opt/mysql_files/stroe.csv' into table store_info fields terminated by ',' enclosed by '"' lines terminated by '\n';十二.正则表达式
1.sql正则表达式的常见种类
| ^ | 匹配文本的开始字符 |
| $ | 匹配文本的结束字符 |
| . | 匹配任何单个字符 |
| * | 匹配零个或多个在它前面的字符 |
| + | 匹配前面的字符 1 次或多次 |
| 字符串 | 匹配包含指定的字符串 |
| l | 或，“|”前面的不成立时，就匹配后面的字符串 |
| [...] | 匹配字符集合中的任意一个字符 |
| [^...] | 匹配不在括号中的任何字符 |
| {n} | 匹配前面的字符串 n 次 |
| {n,m} | 匹配前面的字符串至少 n 次，至多m 次 |
语法：SELECT "字段" FROM "表名" WHERE "字段" REGEXP {模式};
2603
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
