SQL查漏补缺(01)

第一部分 基础知识

01.DCL，DML，DQL，DDL 各自有什么涵义和作用？

DCL(Data Control Language)是指数据库控制功能，用来设置更改数据库的角色权限的语句，例如：Grant,Revoke,Deny

基础的使用语句举例：

grant select on mytable to user_name;

REVOKE ALL PRIVILEGES ON table_name FROM user_name;

deny all privileges on table_name to user_name

DML(Data manipulation language)数据操纵语言，对数据库中的对象进行操纵和修改的语言，常见的语句例如insert,update,delete,truncate等

基础的使用语句举例：

insert into table_name values(value1,value2...)

DQL(Data Query Language)

Select语句...

DDL（Data definition language）数据定义语言，常见的由create，alter，drop三个语句组成

create table table_name(column1 data_type1,column2 data_type2,column3 data_type3)

alter table add unique index(column1)

drop table table_name

02.truncate和delete有什么区别？

1. 条件删除

2. 事务回滚

3. 清理速度

4. 高水位重置

03 索引，视图和分区的关联与区别？

1)数据库分区
数据库分区是将数据量大的表的数据均摊到不同的硬盘、系统或不同服务器存储介质中（将数据从物理上分成若干个小表存储），实际上还是一张表。
将表的数据均衡到不同的地方，为了在特定的SQL操作中减少数据读写的总量以缩减响应时间。提升查询效率。

建表语句举例：

CREATE TABLE ext_table4
(
last_name STRING(10),
first_name STRING(10),
state STRING(10),
address STRING(10)
)
PARTITIONED BY (last_name)

2)视图

视图是从一个或几个基本表（或视图）导出的表。它与基本表不同，是一个虚表。数据库只存放视图的定义，而不存放视图对应的数据，这些数据仍存放在原来的基本表中。所以基本表中的数据发生变化，从视图中查询出的数据也就随之改变了。从这个意义上讲，视图就像一个窗口，透过它可以看到数据库中自己感兴趣的数据及其变化。
视图创建举例：

CREATE VIEW Student（Sno，Sname，Ssex，Sage，Sdept）
 
AS
 
SELECT SX.Sno，SX.Sname，SY.Ssex，SX.Sage，SY.Sdept
 
FROM SX，SY
 
WHERE SX.Sno=SY.Sno；

3)索引

索引（index）是帮助MySQL高效获取数据的数据结构(有序)。在数据之外，数据库系统还维护着满足 特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据， 这样就可以在这些数据结构 上实现高级查找算法，这种数据结构就是索引。

索引创建举例：

CREATE SECTION INDEX
ON region (r_name) PARTITION ON (partition_p1)

04 在Sql中，如何进行行转列，如何进行列转行，如何多行变一行？

行列互转：

1.使用join拼接

2.使用union拼接

列转行：

SELECT id,  
      sum(if(subject='MATH', score, NULL)) as `MATH`,  
      sum(if(subject='ENGLISH', score, NULL)) as `ENGLISH`, 
      sum(if(subject='CHINESE', score, NULL)) as `CHINESE`
FROM score 
GROUP BY id 

05 各个JOIN的使用有什么区别？

JOIN用于按照ON条件联接两个表，主要有四种：
INNER JOIN：内部联接两个表中的记录，仅当至少有一个同属于两表的行符合联接条件时，内联接才返回行。我理解的是只要记录不符合ON条件，就不会显示在结果集内。
LEFT JOIN / LEFT OUTER JOIN：外部联接两个表中的记录，并包含左表中的全部记录。如果左表的某记录在右表中没有匹配记录，则在相关联的结果集中右表的所有选择列表列均为空值。理解为即使不符合ON条件，左表中的记录也全部显示出来，且结果集中该类记录的右表字段为空值。
RIGHT JOIN / RIGHT OUTER JOIN：外部联接两个表中的记录，并包含右表中的全部记录。简单说就是和LEFT JOIN反过来。
FULL JOIN / FULL OUTER JOIN：完整外部联接返回左表和右表中的所有行。

06 Sql中的日期与时间类型？

第二部分 实战演练 

01.牛客网 SQL在线编程练习-26题

Case具有两种格式。简单Case函数和Case搜索函数。

简单Case函数格式：

CASE 列名
WHEN 条件值1 THEN 选项1
WHEN 条件值2 THEN 选项2
……
ELSE 默认值
END

（注释：列名，这里是sex，如果放在case后，则显示在屏幕上的列名即为列名sex）

Case搜索函数：

CASE
WHEN 条件1 THEN 选项1
WHEN 条件2 THEN 选项2
……
ELSE 默认值
END

（注释：列名，这里是sex，如果放在when后，若是不为整个CASE WHEN语句写个别名的话，则显示在屏幕上的列名即为整个CASE WHEN语句）

02.现在有一个考勤打卡表（员工ID Decimal，部门 Decimal，上班时间 DateTime，下班时间 DateTime），记录了某公司某个月的打卡情况，根据上述信息回答以下问题：

1)取数：每天，每个部门中，来的最早的人是谁？走的最晚的人又是谁？

--上班时间最早
Select 员工ID,部门,first_value(上班时间) over(partition by 部门,date(上班时间) order by 上班时间 asc) as 上班时间 from 考勤打卡表;

--下班时间同理

2)取数：在这个月中，来的最早次数最多的人，走的最晚次数最多的人又是谁？

--上班时间
select 员工ID,部门,count(*) as 次数 from
(Select 员工ID,部门,first_value(上班时间) over(partition by 部门,date(上班时间) order by 上班时间 asc) as 上班时间 from 考勤打卡表) D00
order by 次数;
--将上个问题中的结果保存为D00

--下班时间做法类似，这里就不详细赘述了。

03.牛客网 SQL在线编程练习-33题

 关于取最值的另一种表述形式。