秋招SQL题难点总结

Java后端各科最全八股自用整理，获取方式见：

• https://bbs.csdn.net/topics/618573030?spm=1001.2014.3001.6377
• https://github.com/AdaCoding123/CS_Resource/blob/main/README2.md

一、SQL语法总结

SQL21 打折

select
prod_id,prod_price,round(prod_price*0.9,1) sale_price
from
Products


select      
    prod_id,     
    prod_price,    
    prod_price * 0.9 sale_price
from
    Products

SQL22 顾客登录名

这道题有三个关键点：

1.截取函数：substring()

• 用法：SUBSTRING(str ,n ,m)：返回字符串str从第n个字符截取到第m个字符；

2.拼接函数：concat()

• 用法：select concat(A,B) 或者select A || B

3.大写函数UPPER()

select  cust_id,cust_name, 
upper(concat(substring(cust_contact,1,2),substring(cust_city,1,3))) as user_login
from Customers;

SQL23 返回 2020 年 1 月的所有订单的订单号和订单日期

法一

select order_num, order_date
from Orders
where order_date like "2020-01%"
order by order_date;

法二

select order_num, order_date
from Orders
where Year(order_date)='2020' and Month(order_date)='01'
order by order_date;

法三

select order_num, order_date
from Orders
where date_format(order_date, '%Y%m') = '202001'
order by order_date;

SQL24 确定已售出产品的总数

select sum(quantity) as items_ordered
from OrderItems;

SQL25 确定已售出产品项 BR01 的总数

select sum(quantity) as items_ordered
from OrderItems
where prod_id="BR01";

SQL27 返回每个订单号各有多少行数

select  order_num,count(*) as order_lines
from OrderItems  
group by order_num
order by order_lines;

SQL28 每个供应商成本最低的产品

select  vend_id,min(prod_price) as cheapest_item
from Products  
group by vend_id
order by cheapest_item ;

HAVING 子句

在 SQL 中增加 HAVING 子句原因是，WHERE 关键字无法与聚合函数一起使用。

SQL29 返回订单数量总和不小于100的所有订单的订单号

select  order_num
from OrderItems 
group by order_num
having sum(quantity)>=100
order by order_num;

注意以上三者的顺序！！

###SQL30 计算总和

select  order_num,sum(item_price*quantity) as total_price
from OrderItems 
group by order_num
having total_price>=1000
order by order_num;

SQL32 返回购买价格为 10 美元或以上产品的顾客列表

select  cust_id
from Orders
where order_num in(
    select order_num 
    from OrderItems
    where item_price>=10
);

SQL25 确定已售出产品项 BR01 的总数

select sum(quantity) as items_ordered
from OrderItems
where prod_id="BR01";

SQL25 确定已售出产品项 BR01 的总数

select sum(quantity) as items_ordered
from OrderItems
where prod_id="BR01";

SQL25 确定已售出产品项 BR01 的总数

select sum(quantity) as items_ordered
from OrderItems
where prod_id="BR01";

SQL25 确定已售出产品项 BR01 的总数

select sum(quantity) as items_ordered
from OrderItems
where prod_id="BR01";

SQL25 确定已售出产品项 BR01 的总数

select sum(quantity) as items_ordered
from OrderItems
where prod_id="BR01";

二、SQL进阶

索引创建、删除与使用：

1.1 create方式创建索引

CREATE
[UNIQUE -- 唯一索引| FULLTEXT -- 全文索引] INDEX index_name 
ON table_name -- 不指定唯一或全文时默认普通索引(column1[(length) [DESC|ASC]] [,column2,...]) -- 可以对多列建立组合索引 

1.2 alter方式创建索引

ALTER TABLE tb_name ADD [UNIQUE | FULLTEXT] [INDEX] index_content(content)

2.1 drop方式删除索引

DROP INDEX <索引名> ON <表名>

2.2 alter方式删除索引

ALTER TABLE <表名> DROP INDEX <索引名>

3.1 索引的使用：

• 索引使用时满足最左前缀匹配原则，即对于组合索引(col1, col2)，在不考虑引擎优化时，条件必须是col1在前col2在后，或者只使用col1，索引才会生效；
• 索引不包含有NULL值的列
• 一个查询只使用一次索引，where中如果使用了索引，order by就不会使用
• like做字段比较时只有前缀确定时才会使用索引
• 在列上进行运算后不会使用索引，如year(start_time)<2020不会使用start_time上的索引

SQL12 创建索引

create index idx_duration on examination_info(duration);
create unique index uniq_idx_exam_id on examination_info(exam_id);
create fulltext index full_idx_tag on examination_info(tag);

-- 普通索引
ALTER TABLE examination_info
ADD INDEX idx_duration(duration);

-- 唯一索引
ALTER TABLE examination_info
ADD UNIQUE INDEX uniq_idx_exam_id(exam_id);

-- 全文索引
ALTER TABLE examination_info
ADD FULLTEXT INDEX full_idx_tag(tag);

SQL13 删除索引

drop index uniq_idx_exam_id on examination_info;
drop index full_idx_tag on examination_info;

SQL37 对first_name创建唯一索引uniq_idx_firstname

# create unique index uniq_idx_firstname on actor(first_name);
# create  index idx_lastname on actor(last_name);

alter table actor 
add unique index uniq_idx_firstname(first_name);
alter table actor 
add  index idx_lastname(last_name);

SQL39 针对上面的salaries表emp_no字段创建索引idx_emp_no

MYSQL中强制索引查询使用：FORCE INDEX(indexname)

SQLite中强制索引查询使用：INDEXED BY indexname

select * from salaries
force index(idx_emp_no)
where emp_no=10005;

SQL25 确定已售出产品项 BR01 的总数

select sum(quantity) as items_ordered
from OrderItems
where prod_id="BR01";

SQL25 确定已售出产品项 BR01 的总数

select sum(quantity) as items_ordered
from OrderItems
where prod_id="BR01";

SQL25 确定已售出产品项 BR01 的总数

select sum(quantity) as items_ordered
from OrderItems
where prod_id="BR01";

---

更多后端全部八股点击👉👉【闲鱼】https://m.tb.cn/h.5yHpgkY?tk=O8bhWpn1NBD CZ8908 「我在闲鱼发布了【京985计算机硕士自用后端八股文出售，不同于市面上的几块钱八】」
点击链接直接打开

Java后端各科最全八股自用整理，获取方式见：

• https://bbs.csdn.net/topics/618573030?spm=1001.2014.3001.6377
• https://github.com/AdaCoding123/CS_Resource/blob/main/README2.md

---

整理不易🚀🚀，关注和收藏后拿走📌📌欢迎留言🧐👋📣
欢迎专注我的公众号AdaCoding 和 Github：AdaCoding123