mysql高级sql语句 二
目录
一. 求交集
1.1 内连接
1.2 左连接
1.3 右连接
1.4 子查询
1.5 多表查询
1.6 并集+分组
二. 求差集
2.1 求左表差集
2.2 求右表差集
2.3 求两个表的差集
三. 视图表view
3.1 视图表的使用
3.2 视图表里的数据能不能修改？
四. case语句
五. 无值 ' ' 和 空值NULL 的区别
六、正则表达式
七. Mysql输入数据的方法
八. Mysql导出导入CSV文件
九. 如何删除重复数据
9.1 进保留一条重复的数据
9.2 一条重复的数据都不保留
一. 求交集
1.1 内连接
select A.字段 from 左表 A inner join 右表 B on A.字段 = B.字段;select A.字段 from 左表 A inner join 右表 B using(字段); （合并相同的字段）
1.2 左连接
select B.字段 from 左表 A left join 右表 B on A.字段 = B.字段 where B.字段 is not null;
1.3 右连接
select A.字段 from 左表 A right join 右表 B on A.字段 = B.字段 where A.字段 is not null;
1.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.字段);
1.5 多表查询
select A.字段 from 左表 A, 右表 B where A.字段 = B.字段;
1.6 并集+分组
select A.字段 from (select distinct 字段 from 左表 union all select distinct 字段 from 右表) as A group by A.字段 having count(A.字段) > 1; # A代表并集后的结果抽象出来的派生表
二. 求差集
2.1 求左表差集
select A.字段 from 左表 A left join 右表 B on A.字段 = B.字段 where B.字段 is null;select A.字段 from 左表 A where A.字段 not in (select B.字段 from 右表 B);select A.字段 from 左表 A where not exists (select B.字段 from 右表 B where A.字段 = B.字段);
2.2 求右表差集
select B.字段 from 左表 A right join 右表 B on A.字段 = B.字段 where A.字段 is null;
2.3 求两个表的差集
select A.字段 from 左表 A left join 右表 B on A.字段 = B.字段 where B.字段 is nullselect B.字段 from 左表 A right join 右表 B on A.字段 = B.字段 where A.字段 is null;select A.字段 from (select distinct 字段 from 左表 union all select distinct 字段 from 右表) as A group by A.字段 having count(A.字段) = 1;
三. 视图表view
3.1 视图表的使用
CREATE VIEW ----视图，可以被当作是虚拟表或存储查询。
视图跟表格的不同是，表格中有实际储存数据记录，而视图是建立在表格之上的一个架构，它本身并不实际储存数据记录。
临时表在用户退出或同数据库的连接断开后就自动消失了，而视图不会消失。
视图不含有数据，只存储它的定义，它的用途一般可以简化复杂的查询。比如你要对几个表进行连接查询，而且还要进行统计排序等操作，写SQL语句会很麻烦的，用视图将几个表联结起来，然后对这个视图进行查询操作，就和对一个表查询一样，很方便。
语法：
CREATE VIEW "视图表名" AS "SELECT 语句";drop view 视图表名; #删除视图表
3.2 视图表里的数据能不能修改？
-
如果定义的select语句查询的字段是没有被处理过的源表字段，则可以通过视图表修改源表的数据
-
如果定义的select语句查询的字段是被函数或group by等命令处理过的字段，则不能直接修改视图表的数据
四. case语句
CASE ----是 SQL 用来做为 IF-THEN-ELSE 之类逻辑的关键字
语法一：
SELECT CASE "字段名"WHEN "数值1" THEN "结果1"WHEN "数值2" THEN "结果2"...[ELSE "default"]END
FROM "表名";
语法二：
SELECT CASEWHEN "公式1" THEN "结果1"WHEN "公式2" THEN "结果1"...[ELSE "default"] ENDFROM "表名";#ELSE 子句则并不是必须的。五. 无值 ' ' 和 空值NULL 的区别
无值：
-
无值'' 的长度为 0，不占用空间
-
可以通过 字段名 = '' 字段名 !=或<> '' 来过滤字段的值是否为无值的行
-
指定字段使用函数 count(字段) 不会忽略无值的行
空值：
-
空值NULL 的长度为 NULL，占用空间
-
可以通过 字段名 is null 字段名 is not null 来过滤字段的值是否为NULL的行
-
指定字段使用函数 count(字段) 会忽略NULL的行
六、正则表达式
| 匹配模式 | 描述 | 实例 |
| ^ | 匹配文本的开始字符 | ‘^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 {模式};
七. Mysql输入数据的方法
- 使用insert into 语句插入数据
-
使用load date infile 'csv文件'语句
-
使用第三方客户端工具，比如 navicat
八. Mysql导出导入CSV文件
1）修改mysql配置文件，在[mysqld]配置项下面添加 secure_file_priv="" ，重启服务
2）创建导出目录，并授权权限 mkdir mysql-output; chown mysql:mysql mysql-output/
select * into outfile '/opt/test/xy103.csv' fields terminated by ',' enclosed by '"' lines terminated by '\n' from xy103; #导出表数据到CSV文件中load data infile '/opt/test/xy103.csv' into table xy103 fields terminated by ',' enclosed by '"' lines terminated by '\n'; #导入CSV文件数据到mysql表中fields terminated by ',' 指定CVS文件的字段分隔符
enclosed by '"' 指定CVS文件的字段内容边界符
lines terminated by '\n' 指定CVS文件的行分隔符九. 如何删除重复数据
9.1 进保留一条重复的数据
语法：
create view 视图表名 as select min(id) from 表 group by 重复的字段名;delete from 表 where id not in (select 字段 from 视图表名);
9.2 一条重复的数据都不保留
语法：
create view 视图表名 as select 重复的字段名 group by 重复的字段名 having count(字段) > 1;delete from 表 where 重复的字段名 in (select 字段 from 视图表名);
