今日头条—抖音部门测试开发工程师数据库面试题
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 通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放。