这是我自己去面试的一点sql题
在这里分享给大家!
希望能尽快找到工作
其中第十一题 我不是很懂 (我用的是mysql 这个题好像是orc的 不知道是不是)
其次第十题的第四小题 我把count < 3 和老师 not like '刘%'的放在一起结果就有些不对
希望有大佬能帮帮我指正一下 十分感谢!
上面是图片 然后创表语句和答案我都将放在下面
感谢支持!
##由于是面试做过的 自己添加一些数据就不加很多了
创表和添加数据语句:
第一题:建表
CREATE TABLE IF NOT EXISTS STUDENTS
(
ID INT COMMENT '编号',
NAME VARCHAR(20) COMMENT '姓名',
GENDER VARCHAR(1) COMMENT '性别男为M,女为F',
JOIN_DATE DATE COMMENT '入学日期',
BIRTHDAY DATE COMMENT '出生日期'
);
自己插入一些数据
INSERT INTO `STUDENTS` VALUES('1','小王','M','2020-06-10','1995-04-01')
('2','小叶','F','2020-06-10','1998-01-01'),
('3','小李','M','2020-06-10','1995-04-01'),
('4','小刘','F','2020-06-10','1998-01-01'),
('5','小一','M','2020-06-10','1995-04-01'),
('6','小二','M','2020-06-10','1998-01-01'),
('7','小三','F','2020-06-10','1998-01-01'),
('8','小四','F','2020-06-10','1995-04-01'),
('9','小五','M','2020-06-10','1998-01-01'),
('10','小六','M','2020-06-10','1998-01-01')
第二题:
UPDATE STUDENTS SET GENDER = '女',BIRTHDAY = '1995-5-1' WHERE ID = '10'
第三题:
SELECT
ID,
NAME,
(CASE WHEN GENDER = 'M' THEN 0 ELSE 1 END )AS GENDER,
JOIN_DATE,
BIRTHDAY
FROM STUDENTS;
第四题:
SELECT * FROM (
(SELECT COUNT(*) AS 'MAN' FROM students
WHERE GENDER = 'M'
AND BIRTHDAY > '1995-05-01' )a
INNER JOIN
(
SELECT COUNT(*) AS 'WOMEN' FROM students
WHERE GENDER = 'F'
AND BIRTHDAY > '1995-05-01'
)b
)
第五题:
存储过程 :往往存储过程中不单单只包含这种简单的查询语法,还会嵌套循环语句、变量、报错处理、事务等
函数:一般以函数名称+参数列表+return返回类型+sql语句+end
第六题:
表和索引所占空间:
truncate:当表被TRUNCATE 后,这个表和索引所占用的空间会恢复到初始大小
DELETE操作不会减少表或索引所占用的空间。
应用范围:
TRUNCATE 只能对TABLE;
DELETE可以是table和view
题外话:drop语句将表所占用的空间全释放掉。
##这里不写全了 想看完整的我转载一个博客
https://www.cnblogs.com/zhizhao/p/7825469.html
第七题:
关于索引的一些创建方法:
普通的索引的创建:
CREATE INDEX (自定义)索引名 ON 数据表(字段);
复合索引的创建:
CREATE INDEX (自定义)索引名 ON 数据表(字段,字段,。。。);
删除索引:DROP INDEX 索引名;
(1)Mysql目前主要有以下几种索引类型:FULLTEXT,HASH,BTREE,RTREE
(2)优点:
1.通过创建唯一性索引,可以保证数据库表中每一行数据的唯一性。
2.可以大大加快数据的检索速度,这也是创建索引的最主要的原因。
3.可以加速表和表之间的连接,特别是在实现数据的参考完整性方面特别有意义。
4.在使用分组和排序子句进行数据检索时,同样可以显著减少查询中分组和排序的时间。
5.通过使用索引,可以在查询的过程中,使用优化隐藏器,提高系统的性能。
缺点:
1.创建索引和维护索引要耗费时间,这种时间随着数据量的增加而增加。
2.索引需要占物理空间,除了数据表占数据空间之外,每一个索引还要占一定的物理空间,如果要建立聚集索引那么需要的空间就会更大。
3.当对表中的数据进行增加、删除和修改的时候,索引也要动态的维护,这样就降低了数据的维护速度。
(3):
1、like 以%开头,索引无效;当like前缀没有%,后缀有%时,索引有效
2、or语句前后没有同时使用索引。当or左右查询字段只有一个是索引,该索引失效,
只有当or左右查询字段均为索引时,才会生效
3、组合索引,不是使用第一列索引,索引失效
4、数据类型出现隐式转化。如varchar不加单引号的话可能会自动转换为int型,
使索引无效,产生全表扫描。
5.在索引列上使用 IS NULL 或 IS NOT NULL操作。索引是不索引空值的,
所以这样的操作不能使用索引,可以用其他的办法处理,例如:数字类型,判断大于0,
字符串类型设置一个默认值,判断是否等于默认值即可。
8、当全表扫描速度比索引速度快时,mysql会使用全表扫描,此时索引失效
第八题:
创表:CREATE TABLE department(NAME VARCHAR(5))
语句:SELECT a.name,b.name FROM department a,department b
WHERE a.name < b.name
第九题:
创表:
CREATE TABLE IF NOT EXISTS STUDENT2
(
name VARCHAR(10) not null COMMENT '姓名',
sub VARCHAR(10) NOT NULL COMMENT '学科',
score VARCHAR(10) NOT NULL COMMENT '分数'
)
插入语句
insert into STUDENT2 VALUES('张三','语文','81'),
('张三','数学','75'),
('李四','语文','76'),
('李四','数学','90'),
('王五','语文','81'),
('王五','数学','100')
(1):select name from STUDENT2 GROUP BY name HAVING min(score) >80
(2): SELECT NAME FROM student2 WHERE score >
(SELECT AVG(score) FROM
(SELECT * FROM student2)a GROUP BY a.sub)
(3):SELECT
NAME,
SUM(CASE WHEN sub = '语文' THEN score ELSE 0 END)AS '语文',
SUM(CASE WHEN sub = '数学' THEN score ELSE 0 END)AS '数学'
FROM student2 GROUP BY NAME
第十题:
创表:
学生表:CREATE TABLE IF NOT EXISTS student3(
id TINYINT ,
name VARCHAR(5) COMMENT '姓名',
grade VARCHAR(2) COMMENT '年级',
sex VARCHAR(2) COMMENT '性别'
)
#选课表
CREATE TABLE IF NOT EXISTS xuanke(
id VARCHAR(5) ,
lessionid VARCHAR(10) ,
score VARCHAR(5)
)
#course课程表
CREATE TABLE IF NOT EXISTS course(
lessionid VARCHAR(10),
lessionname VARCHAR(10),
teachername VARCHAR(10)
)
赋值:
#student3表赋值
INSERT INTO `student3` VALUES
(101,'李军','2','男'),
(103,'陆君','2','女'),
(105,'匡明','2','男'),
(107,'王丽','2','女'),
(108,'曾华','2','男'),
(109,'王芳','2','女');
#选课表赋值
INSERT INTO `xuanke`
VALUES
('101','001','90'),
('101','002','90'),
('101','003','90'),
('101','004','90'),
('103','001','90'),
('105','001','90'),
('107','001','90'),
('108','001','90'),
('109','001','90')
#课程表赋值
INSERT INTO `course`
VALUES
('001','茶艺','小叶'),
('002','计算机','小吴'),
('003','甲骨文','小李'),
('004','欧洲历史','小庄')
第十题解答:
(1):
SELECT DISTINCT c.teachername FROM course AS c
LEFT JOIN xuanke b ON c.lessionid = b.lessionid
LEFT JOIN student3 a ON b.id = a.id
AND a.name LIKE '王%'
ORDER BY c.teachername
(2):
SELECT DISTINCT lessionname FROM course,xuanke,student3
WHERE course.lessionid = xuanke.lessionid
AND xuanke.id = student3.id
GROUP BY student3.id
(3):
SELECT all.name FROM
(
SELECT
a.`id`,a.`name`,a.`grade`,a.`sex`,
b.`lessionid`,b.`score`,
c.`lessionname`,c.`teachername`d FROM course AS c
INNER JOIN xuanke b ON c.lessionid = b.lessionid
INNER JOIN student3 a ON b.id = a.id
)AS `all`
GROUP BY all.id
HAVING COUNT(all.lessionid) < 4
(4):
SELECT NAME FROM student3 WHERE NAME NOT IN(
SELECT a.name FROM student3 a
INNER JOIN
(SELECT * FROM xuanke)b
ON a.id = b.id
INNER JOIN
(SELECT * FROM course)c
ON b.lessionid = c.lessionid
AND c.teachername LIKE '刘%'
HAVING COUNT(b.id) < 3
)
第十一题:
declare
type tab_name is table of varchar2(80) index by binary_integer;
t_name tab_name;
coun number;
str varchar(200);
begin
select table_name bulk collect into t_name from user_tables;
for i in t_name.first .. t_name.last loop
str := 'select count(*) from '|| t_name(i);
execute immediate str into coun;
dbms_output.put_line(t_name(i)||'.....'||coun);
end loop;
end;
第十二题:
select
max(team) team,
min(y) B,
min(y)+count(*) E
from
(
select
a.team,
a.y,
rownum rn
from nba a, nba b
where a.y=b.y-1 and a.team=b.team
) t
group by (y-rn) order by min(y)