MySQL高级语句（二）

一、 CREATE VIEW（视图）

可以被当作是虚拟表或存储查询

（1）视图跟表格的不同是，表格中有实际储存资料，而视图是建立在表格之上的一个架构，它本身并不实际储存资料。

（2）临时表在用户退出或同数据库的连接断开后就自动消失了，而视图不会消失。

（3）视图不含有数据，只存储它的定义，它的用途一般可以简化复杂的查询。比如你要对几个表进行连接查询，而且还要进行统计排序等操作，写SQL语句会很麻烦的，用视图将几个表联结起来，然后对这个视图进行查询操作，就和对一个表查询一样，很方便。

#语法：
CREATE VIEW "视图表名" AS "SELECT 语句";

#示例1：创建视图表，表名为new_test1，数据是跟在as后面的select语句，score1表的别名为a，test1表的别名为b；将a表和b表等值连接，连接字段为id，将a表的score和oid列显示到新表中为score和id；将b表中的name列显示到新表为name
create view new_test1 as select a.score score,a.oid id,b.name name from score1 a inner join test1 b on a.oid=b.id;
 
#删除视图
drop view new_test1;

 

 二、联集

联集，将两个SQL语句的结果合并起来，两个SQL语句所产生的栏位需要是同样的资料种类

2.1UNION

生成结果的资料值将没有重复，且按照字段的顺序进行排序

#语法：
[SELECT 语句 1] UNION [SELECT 语句 2];

#示例：搜索两个表中courses列的所有记录，先安装原有顺序列出第一个select语句中的数据，再将第二个表中的数据对照第一个表的数据去重后按源于的顺序列出
 select age from socre1  union select age from test1;

 2.2UNION ALL

将生成结果的资料值都列出来，无论有无重复

#语法：
[SELECT 语句 1] UNION ALL [SELECT 语句 2];

 三、交集值

select a.age from test1 a inner join score1 b on a.age=b.age;
select a.age from test1 a inner join score1 b using(age);

四、无交集值

显示第一个SQL语句的结果，且与第二个SQL语句没有交集的结果，且没有重复

select distinct city from food where (city) not in (select city from info1);

 select distinct a.city from food a left join info1 b using(city) where b.city is null;

 五、CASE

CASE是 SQL 用来做为 IF-THEN-ELSE 之类逻辑的关键字

#语法：
SELECT CASE ("栏位名")
  WHEN "条件1" THEN "结果1"
  WHEN "条件2" THEN "结果2"
  ...
  [ELSE "结果N"]
  END
FROM "表名";
 
# "条件" 可以是一个数值或是公式。 ELSE 子句则并不是必须的。

#示例：
select city, case city
  when '南京' then price - 50
  when '北京' then price - 20
  else price
  end
"new price",data
from info1;
#"new price" 是用于 CASE 那个栏位的栏位名。

 六、表格内容排序

#新建一个表格

clcreate table class (name char(10),score int(4));
insert into ass values('小明',88);
insert into class values('小红',76);
insert into class values('小斌',90);
insert into class values('小云',64);
insert into class values('小芳',73);
insert into class values('小龙',52);
insert into class values('小伟',99);
 
select * from class;

 6.1算排名

表格自我连结 (Self Join)，然后将结果依序列出，算出每一行之前 (包含那一行本身) 有多少行数

select a1.name,a1.scores,count(a2.scores) rank from class a1,class a2 where a1.scores < a2.scores or (a1.scores = a2.scores and a1.name = a2.name) group by a1.name,a1.scores order by a1.scores desc;

 6.2 算中位数

select scores middle from (select a1.name,a1.scores,count(a2.scores) rank from class a1,class a2 where a1.scores < a2.scores or (a1.scores = a2.scores and a1.name <= a2.name) group by a1.name ,a1.scores order by a1.scores desc) a3 where a3.rank = (select (count(*)+1) div 2 from class);

 6.3算累积总计

select a1.*,sum(a2.scores) sum_scores from class a1,class a2 where a1.scores < a2.scores or(a1.scores = a2.scores and a1.name = a2.name) group by a1.name order by a1.scores desc;

 6.4.算总合百分比

#SELECT SUM(Sales) FROM Total_Sales 这一段子查询是用来算出总合
#总合算出后，我们就能够将每一行一一除以总合来求出每一行的总合百分比
select a1.*,a1.scores/(select sum(scores) from class) per_sum from class a1,class a2 where a1.scores<a2.scores or(a1.scores = a2.scores and a1.name = a2.name) group by a1.name order by a1.scores desc;

 取小数点后几位数

select a1.name,a1.scores,sum(a2.scores),truncate(sum(a2.scores)/(select sum(scores) from class),2) ||'%' per_sum from class a1,class a2 where a1.scores < a2.scores or (a1.scores = a2.scores and a1.name=a2.name) group by a1.name order by a1.scores desc;

 七、空值(NULL) 和 无值(’ ') 的区别

（1）无值的长度为 0，不占用空间的；而 NULL 值的长度是 NULL，是占用空间的。

（2）IS NULL 或者 IS NOT NULL，是用来判断字段是不是为 NULL 或者不是 NULL，不能查出是不是无值的。

（3）无值的判断使用=’‘或者<>’'来处理。<> 代表不等于。

（4）在通过 count()指定字段统计有多少行数时，如果遇到 NULL 值会自动忽略掉，遇到无值会加

#示例1：查看‘NULL’，‘’，‘123’所占用的字符
select length (NULL),length(''),length('123');

#示例2：查看空值和非空值
select * from test3 where name = '';
select * from test3 where name <> '';

#示例3：统计行数
select COUNT(*) from test3;
select COUNT(name) from test3;

 八、日期时间函数

字符串函数	描述
curdate()	返回当前时间的年月旦
curtime()	返回当前时间的时分秒
now()	返回当前时间的日期和时间
month(x)	返回日期×中的月份值
week(x)	返回日期×是年度第几个星期
hour(x)	返回×中的小时值
minute(x)	返回×中的分钟值
second(x)	返回×中的秒钟值
dayofweek(x)	返回×是星期几，1星期日，2星期一
fdayofmonth(x)	计算日期×是本月的第几天
dayofyear(x)	计算日期×是本年的第几天

#示例1：显示当前日期
select curdate();

#示例2：显示当前的 时分秒
select curtime();

#示例3：返回月份
select month('2022-12-8');

#示例4：返回一年中的第几周
select week('2021-6-26');

#示例5：返回小时值
select hour('2021-6-22 17:25:21');

#示例6：返回星期几
select dayofweek('1999-4-28 13:42:21');

#示例7：返回在这个月的第几天
select dayofmonth('2022-6-26 13:42:21');

#示例8：返回在这一年的第几天
select dayofyear('2021-8-8 13:42:21');