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 null

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 右表) 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 CASE
  WHEN "公式1" THEN "结果1"
  WHEN "公式2" THEN "结果1"
  ...
  [ELSE "default"] END
  FROM "表名";

#ELSE 子句则并不是必须的。

五. 无值 ' ' 和 空值NULL 的区别

无值：

• 无值'' 的长度为 0，不占用空间

• 可以通过 字段名 = '' 字段名 !=或<> '' 来过滤字段的值是否为无值的行

• 指定字段使用函数 count(字段) 不会忽略无值的行

空值：

• 空值NULL 的长度为 NULL，占用空间

• 可以通过 字段名 is null 字段名 is not null 来过滤字段的值是否为NULL的行

• 指定字段使用函数 count(字段) 会忽略NULL的行

六、正则表达式

使用语法：

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 视图表名);