第一部分 基础知识
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题
关于取最值的另一种表述形式。