今日头条—抖音部门测试开发工程师数据库面试题

今日头条—抖音部门测试开发工程师数据库面试题

Class表

图片发自简书App


Book表

图片发自简书App

--练习

--创建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','你试试？&nbsp')

insert into book values('002','新概念2册','英语','2','你试试？&lt')

insert into book values('003','新概念3册','英语','3','你试试？&gt')

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','你试试？&nbsp')

insert into book values('002','新概念2册','英语','2','你试试？&lt')

insert into book values('003','新概念3册','英语','3','你试试？&gt')

insert into book values('004','新概念4册','英语','4','你试试？&quot')

insert into book values('005','新概念5册','英语','5','你试试？&nbsp；我会的！')

insert into book values('006','初一数学人教版1册','数学','1','你试试？&nbsp；哈飞')

insert into book values('007','初一语文人教版1册','语文','1','你试试？我会的！')

insert into book values('008','初一语文人教版2册','语文','2','你试“哈飞”？')

insert into book values('009','初一地理人教版1册','地理','1','你试试&lt；朴新>？')

insert into book values('010','初一生物人教版1册','生物','1','你试试&lt；朴新&gt；？')

--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转义字符的记录（例如：&nbsp；&lt：等）

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 不能用于参与了索引视图的表

©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 212,332评论 6赞 493
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 90,508评论 3赞 385
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 157,812评论 0赞 348
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 56,607评论 1赞 284
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 65,728评论 6赞 386
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 49,919评论 1赞 290
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 39,071评论 3赞 410
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 37,802评论 0赞 268
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 44,256评论 1赞 303
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 36,576评论 2赞 327
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 38,712评论 1赞 341
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 34,389评论 4赞 332
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 40,032评论 3赞 316
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 30,798评论 0赞 21
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 32,026评论 1赞 266
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 46,473评论 2赞 360
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 43,606评论 2赞 350

推荐阅读更多精彩内容

  • 摘录的一些知识点
    ORACLE自学教程 --create tabletestone ( id number, --序号usernam...
    落叶寂聊阅读 1,072评论 0赞 0
  • Java面试
    一. Java基础部分.................................................
    wy_sure阅读 3,805评论 0赞 11
  • 2018-06-11
    mean to add the formatted="false" attribute?.[ 46% 47325/...
    ProZoom阅读 2,694评论 0赞 3
  • 粉色
    1杏色和粉色的颜色搭配在一起，温暖而优雅。 2搭配有层次感。颜色之间相互呼应。 3模特的衣服顺应季节。冬天的温暖感。
    青岛金狮殷效娟阅读 798评论 6赞 1
  • 七绝——月婵
    月婵清韵醉千古 秀木别枝守万年 寂悦淡宁痴情望 风箫湖影心波连
    阳春一枝雪阅读 233评论 2赞 11