牛客网SQL题

1.分页查询employees表，每5行一页，返回第2页的数据。

由于每一页有5行，注意到SQL中行号是从0开始的，因此从第5行开始就是第二页了
因此使用LIMIT 5,5，即方法一：

------------------------------------------------------------------------

SELECT * FROM employees LIMIT 5,5;

------------------------------------------------------------------------
也可以使用LIMIT m OFFSET n :表示从第n行开始(包括)读取m行，如果不足m行，则读取到表最后停止
该语句等价于 LIMIT n,m;
在SQL Server和Access中可以使用TOP语句，但SQLite不支持。
 方法二：

------------------------------------------------------------------------
SELECT * FROM employees
LIMIT 5 OFFSET 5;

------------------------------------------------------------------------

2.获取select * from employees对应的执行计划。

--------------------------------------------------------------------------------------------------------------------------------------

explain select * from employees.

--------------------------------------------------------------------------------------------------------------------------------------

可以用 "EXPLAIN" 关键字或 "EXPLAIN QUERY PLAN" 短语，用于描述表的细节、具体说明与用法

3.将employees表的所有员工的last_name和first_name拼接起来作为Name，中间以一个空格区分

不同数据库连接字符串的方法不完全相同，MySQL、SQL Server、Oracle等数据库支持CONCAT方法，而本题所用的SQLite数据库只支持用连接符号"||"来连接字符串。

 

SELECT last_name || '' '' || first_name AS name From employees.

4.对于表actor批量插入如下数据,如果数据已经存在，请忽略，不使用replace操作.

对于sqlite3:

insert or ignore into actor

values(3,'ED','CHASE','2006-02-15 12:34:33');

对于mysql：

 

insert IGNORE into actor

values(3,'ED','CHASE','2006-02-15 12:34:33');

 

insert into:插入数据,如果主键重复，则报错

insert repalce:插入替换数据,如果存在主键或unique数据则替换数据

insert ignore:如果存在数据,则忽略

 

5.针对如下表actor结构创建索引：

CREATE TABLE IF NOT EXISTS actor (

actor_id smallint(5) NOT NULL PRIMARY KEY,
first_name varchar(45) NOT NULL,
last_name varchar(45) NOT NULL,
last_update timestamp NOT NULL DEFAULT (datetime('now','localtime')))
对first_name创建唯一索引uniq_idx_firstname，对last_name创建普通索引idx_lastname。

 --------------------------------------------------------------------------------------------------------------------------

 //创建唯一索引CREATE UNIQUE INDEX uniq_idx_firstname on actor(first_name);
//创建普通索引
CREATE INDEX idx_lastname on actor(last_name);

---------------------------------------------------------------------------------------------------------------------------

 

6.（网易2018数据分析实习卷）好评率是会员对平台评价的重要指标。现在需要统计2018年1月1日到2018年1月31日，用户'小明'提交的母婴类目"花王"品牌的好评率（好评率=“好评”评价量/总评价量）:

用户评价详情表：a

字段：id（评价id，主键），create_time（评价创建时间，格式'2017-01-01'）， user_name(用户名称)，goods_id(商品id，外键) ，

sub_time（评价提交时间，格式'2017-01-01 23:10:32'），sat_name（好评率类型，包含：“好评”、“中评”、“差评”）

商品详情表：b

字段：good_id（商品id，主键），bu_name（商品类目）, brand_name(品牌名称)。

---------------------------------------------------------------------------------------------------------------------------------

select

 sum(casewhen sat_name ='好评'then count(sat_name) end ) / count(sat_name)  as "好评率"

 

from a,b

where a.good_id = b.good_id

and a.user_name ='小明'

and a.sub_time between to_date('2018-1-1','yyyy-mm-dd') and to_date('2018-1-31','yyyy-mm-dd')

and b.bu_name = '母婴'

and b.brand_name ='花王'

-----------------------------------------------------------------------------------------------------------------------------------

1)
select

b.brand_name,a.logday,sum(sale_amt) as sale
from a,b
where a.SKU_ID=b.SKU_ID and user_name='小明' and logday between '2017-01-01' and '2017-12-31'

group by brand_name,logday having( select count(logday) from ( select b1.brand_name,a1.logday,sum(sale_amt) as sale1
from a a1,b b1
where a1.SKU_ID=b1.SKU_ID and b1.user_name='小明' and a1.logday between '2017-01-01' and '2017-12-31'
group by b1.brand_name,a1.logday ) t where b.brand_name=b1.brand_name and sale1>sale

)<3

(2)第二问比较复杂，所以要创建几个视图

create view t_view as

select brand_name,logday,sum(saleamt) as sale
from a,b
where a.SKU_ID=b.SKU_ID and user_name='小明' and logday in ('2017-01-01','2017-12-31')
group by brand_name,logday; 

create view f_view as

select t1.brand_name,t1.logday,(t1.sale/t2.sale-1) as growth,t1.sale
from t_view t1,t_view t2
where t1.logday-t2.logday=1; 

select distinct f1.brand_name,f1.logday,f1.sale

from f_view f1,f_view f2,f_view f3
where
f1.brand_name=f2.brand_name
and f2.brand_name=f3.brand_name
((f1.logday-f2.logday=-1
and
f1.logday-f3.logday=-2)
or
(f1.logday-f2.logday=1
and
f1.logday-f3.logday=-1)
or
(f1.logday-f2.logday=1
and
f1.logday-f3.logday=2))
and f1.growth>0.5 and f2.growth>0.5
and f3.growth>0.5;

 

8.数据库中事务的四大特性（ACID）

⑴ 原子性（Atomicity）

　　原子性是指事务包含的所有操作要么全部成功，要么全部失败回滚，这和前面两篇博客介绍事务的功能是一样的概念，因此事务的操作如果成功就必须要完全应用到数据库，如果操作失败则不能对数据库有任何影响。

⑵ 一致性（Consistency）

　　一致性是指事务必须使数据库从一个一致性状态变换到另一个一致性状态，也就是说一个事务执行之前和执行之后都必须处于一致性状态。

　　拿转账来说，假设用户A和用户B两者的钱加起来一共是5000，那么不管A和B之间如何转账，转几次账，事务结束后两个用户的钱相加起来应该还得是5000，这就是事务的一致性。

⑶ 隔离性（Isolation）

　　隔离性是当多个用户并发访问数据库时，比如操作同一张表时，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离。

　　即要达到这么一种效果：对于任意两个并发的事务T1和T2，在事务T1看来，T2要么在T1开始之前就已经结束，要么在T1结束之后才开始，这样每个事务都感觉不到有其他事务在并发地执行。

　　关于事务的隔离性数据库提供了多种隔离级别，稍后会介绍到。

⑷ 持久性（Durability）

　　持久性是指一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。

　　例如我们在使用JDBC操作数据库时，在提交事务方法后，提示用户事务操作完成，当我们程序执行完成直到看到提示后，就可以认定事务以及正确提交，即使这时候数据库出现了问题，也必须要将我们的事务完全执行完成，否则就会造成我们看到提示事务处理完毕，但是数据库因为故障而没有执行事务的重大错误。