今日头条—抖音部门测试开发工程师数据库面试题
Class表
今日头条—抖音部门测试开发工程师数据库面试题Book表
今日头条—抖音部门测试开发工程师数据库面试题
--练习
--创建class表(code编码,name名称,model班型,book_code教材编码,state编码)
create table Class(Code char(20) not null,Name char(40) not null,Model char(40),Book_code char(10) not null,State char(10)
not null,Price char(10) not null)
insert into class values('PX201705001','初一地理强化','精品班','009','1','1180')
insert into class values('PX201705002','初一语文强化','精品班','007','0','1180')
insert into class values('PX201705003','初一数学强化','精品班','006','1','980')
insert into class values('PX201705004','初一英语强化','精品班','003','1','780')
insert into class values('0120176001','张晓明','一对一','','1','15000')
--创建教材表(code编码,name名称,suject科目,ver教材版本,desc教材描述)
create table Book(Code char(20) not null,Name char(40) not null,Subject char(40) not null,Ver char(10) not null,desc char(10)
not null)
insert into book values('001','新概念1册','英语','1','你试试? ')
insert into book values('002','新概念2册','英语','2','你试试?<')
insert into book values('003','新概念3册','英语','3','你试试?>')
insert into book values('004','新
练习题
Class表
Book表
--练习
--创建class表(code编码,name名称,model班型,book_code教材编码,state编码)
create table Class(Code char(20) not null,Name char(40) not null,Model char(40),Book_code char(10) not null,State char(10)
not null,Price char(10) not null)
insert into class values('PX201705001','初一地理强化','精品班','009','1','1180')
insert into class values('PX201705002','初一语文强化','精品班','007','0','1180')
insert into class values('PX201705003','初一数学强化','精品班','006','1','980')
insert into class values('PX201705004','初一英语强化','精品班','003','1','780')
insert into class values('0120176001','张晓明','一对一','','1','15000')
--创建教材表(code编码,name名称,suject科目,ver教材版本,desc教材描述)
create table Book(Code char(20) not null,Name char(40) not null,Subject char(40) not null,Ver char(10) not null,desc char(10)
not null)
insert into book values('001','新概念1册','英语','1','你试试? ')
insert into book values('002','新概念2册','英语','2','你试试?<')
insert into book values('003','新概念3册','英语','3','你试试?>')
insert into book values('004','新概念4册','英语','4','你试试?"')
insert into book values('005','新概念5册','英语','5','你试试? ;我会的!')
insert into book values('006','初一数学人教版1册','数学','1','你试试? ;哈飞')
insert into book values('007','初一语文人教版1册','语文','1','你试试?我会的!')
insert into book values('008','初一语文人教版2册','语文','2','你试“哈飞”?')
insert into book values('009','初一地理人教版1册','地理','1','你试试<;朴新>?')
insert into book values('010','初一生物人教版1册','生物','1','你试试<;朴新>;?')
--1 请用1个sql获取教材科目(subject)都有几科(要求不重复)?
--考察关键字distinct
select distinct subject from book
--2 请用1个sql统计每个科目(subject)的教材共多少本?
--考察分组group by
select subject,count(*) as sum_sujects from book group by subject
--3 请用1个sql获取有超过3本教材的科目和教材数量?
--考察过滤分组having
select subject,count(*) as sum_subject from book group by subject having count(*) >3
--4 请用1个sql查询出班级表中所有班级信息和每个班级对应的教材名称是什么?
--考察内联结/自然联结innor join
select class.*,book.name from class inner join book on class.book_code = book.code
select c.*,b.name from class as c,book as b where c.book_code = b.code
--加:请用1个sql查询出班级表中所有班级信息和每个班级对应的教材名称是什么?还要没有教材版本的班级信息
--考察外联结outer join 不支持right outer join和full outer join;left outer join 表示从from子句中左边的表(class表)中选择所有行
select class.*,book.name from class left outer join book on class.book_code = book.code
--5 请用1个sql语句获取班级类型(Model)为“精品班”且“教材的版本”为第一版的“有效”班级总价格是多少?
--考察and sum() 表别名
select c.code,c.model,c.book_code,c.state,b.code,b.ver,sum(c.price) as sum_price from class as c,book as b where c.model = '精品班' and c.book_code = b.code and b.ver = '1' and c.state = '1'
--6 请用一个sql把第4题目中班级对应教材为空的班级教材更新关联成“初一生物人教版1册”教材
select class.*, book.name from class left outer join book on class.book_code = book.code -- 第四题
--“初一生物人教版1册”对应book_code = 010,更新之前为空的class表里的book_code为010就行
update class set book_code = '010' where code = '0120176001'
--7 请用一个sql把班级表的price字段进行扩容,改成varchar(20),原本是char(10)
alter table class modify column price varchar(20);
如果是int类型:alter table class modify column price int(20);
8 请用1个sql语句获取描述desc中包含html转义字符的记录(例如: ;<:等)
select * from book where desc like ‘%&%’
转义字符串的组成
转义字符串(Escape Sequence),即字符实体(Character Entity)分成三部分:第一部分是一个&符号,英文叫ampersand;第二部分是实体(Entity)名字或者是#加上实体(Entity)编号;第三部分是一个分号。
转义字符串(Escape Sequence)也称字符实体(Character Entity)。在HTML中,定义转义字符串的原因有两个:第一个原因是像“<”和“>”这类符号已经用来表示HTML标签,因此就不能直 接当作文本中的符号来使用。为了在HTML文档中使用这些符号,就需要定义它的转义字符串。当解释程序遇到这类字符串时就把它解释为真实的字符。在输入转 义字符串时,要严格遵守字母大小写的规则。第二个原因是,有些字符在ASCII字符集中没有定义,因此需要使用转义字符串来表示。
9 请用一个sql语句获取描述desc中包含三个英文字母连写记录
如果是oracle库,可以用sql:
select * from book where REGEXP_LIKE(desc,'[[:alpha:]]{3}')
正则:
https://www.cnblogs.com/q1104460935/p/7991321.html
如果是mysql库,可以用sql:但是没搜出正确答案来
select * from book where desc like '%[a-z][a-z][a-z]%'
10 如果教材数据很多,执行上述查询速度不是很理想,请说出你对数据库表(或java代码方面)的优化建议
这张表可以拆分,拆分成两张表,书籍详细信息表,书籍类型表。两张表都建索引,这样查询就快了
1)、在数据库设计方面:
(1)建立索引;
(2)分区(MySQL,比如按时间分区);
(3)尽量使用固定长度的字段;
(4)限制字段长度;
2)、在数据库I/O方面:
(1)增加缓冲区;
(2)如果涉及表的级联,不同的表存储在不同的磁盘上,以增加I/O速度;
3)、在SQL语句方面:
(1)优化SQL语句,减少比较次数;
(2)限制返回的条目数(MySQL中用limit);
4)、在Java方面:
如果是反复使用的查询,使用PrepaerdStatement减少查询次数。
11 请写出sql语言中truncate和Delect,Drop表操作命令的差别?
相同点:
1).truncate和不带where子句的delete、以及drop都会删除表内的数据。
2).drop、truncate都是DDL语句(数据定义语言),执行后会自动提交。
不同点:
1). truncate 和 delete 只删除数据不删除表的结构(定义) drop 语句将删除表的结构被依赖的约束(constrain)、触发器(trigger)、索引(index);依赖于该表的存储过程/函数将保留,但是变为 invalid 状态。
2). delete 语句是数据库操作语言(dml),这个操作会放到 rollback segement 中,事务提交之后才生效;如果有相应的 trigger,执行的时候将被触发。 truncate、drop 是数据库定义语言(ddl),操作立即生效,原数据不放到 rollback segment 中,不能回滚,操作不触发 trigger。
3).delete 语句不影响表所占用的 extent,高水线(high watermark)保持原位置不动 drop 语句将表所占用的空间全部释放。 truncate 语句缺省情况下见空间释放到 minextents个 extent,除非使用reuse storage;truncate 会将高水线复位(回到最开始)。
4).速度,一般来说: drop> truncate > delete
5).安全性:小心使用 drop 和 truncate,尤其没有备份的时候.否则哭都来不及 使用上,想删除部分数据行用 delete,注意带上where子句. 回滚段要足够大. 想删除表,当然用 drop 想保留表而将所有数据删除,如果和事务无关,用truncate即可。如果和事务有关,或者想触发trigger,还是用delete。 如果是整理表内部的碎片,可以用truncate跟上reuse stroage,再重新导入/插入数据。 6).delete是DML语句,不会自动提交。drop/truncate都是DDL语句,执行后会自动提交。
7)、TRUNCATE TABLE 在功能上与不带 WHERE 子句的 DELETE 语句相同:二者均删除表中的全部行。但 TRUNCATE TABLE 比 DELETE 速度快,且使用的系统和事务日志资源少。DELETE 语句每次删除一行,并在事务日志中为所删除的每行记录一项。TRUNCATE TABLE 通过释放存储表数据所用的数据页来删除数据,并且只在事务日志中记录页的释放。
8)、TRUNCATE TABLE 删除表中的所有行,但表结构及其列、约束、索引等保持不变。新行标识所用的计数值重置为该列的种子。如果想保留标识计数值,请改用 DELETE。如果要删除表定义及其数据,请使用 DROP TABLE 语句。 9)、对于由 FOREIGN KEY 约束引用的表,不能使用 TRUNCATE TABLE,而应使用不带 WHERE 子句的 DELETE 语句。由于 TRUNCATE TABLE 不记录在日志中,所以它不能激活触发器。 10)、TRUNCATE TABLE 不能用于参与了索引视图的表
网友评论