数据库
文章平均质量分 87
什么你竟然不会敲代码
因故停了,谢谢大家支持。不必私信
展开
-
高级数据库开发技术-复习
1.index索引B+能做的• 全键值• 键值范围• 键前缀查找索引目的:提高查询效率索引的另一面(问题)磁盘空间的开销处理的开销数据库系统处理的开销索引的使用是否合理,首先取决于它是否有用• 判断索引适用性的依据是检索比例(retrieval ratios)什么时候应该使用B树索引• 仅需要通过索引访问基本表的很少一部分行• 如果要处理表中的多行,可以使用索引而不使用表(要访问的字段全部包含在索引中时)索引的建立必须慎重,对每个索引的必要性都应该经过仔细分析,要有建立的依原创 2021-04-19 20:29:13 · 1523 阅读 · 5 评论 -
《数据库系统概论》知识点总结索引
《数据库系统概论》-Chapter1-绪论知识点整理《数据库系统概论》-Chapter2-关系数据库知识点整理《数据库系统概论》-Chapter3-SQL《数据库系统概论》-Chapter4-数据库安全性《数据库系统概论》-Chapter5-数据库完整性《数据库系统概论》-Chapter6-关系数据理论《数据库系统概论》-Chapter7-数据库设计《数据库系统概论》-Chapter8-数据库编程《数据库系统概论》-Chapter9-关系查询处理和查询优化《数据库系统概论》-Chapter原创 2020-06-03 09:52:22 · 1616 阅读 · 3 评论 -
LeetCode-SQL-[Easy]1179. 重新格式化部门表
Create table If Not Exists Department (id int, revenue int, month varchar(5))Truncate table Departmentinsert into Department (id, revenue, month) values ('1', '8000', 'Jan')insert into Department ...原创 2020-03-29 10:31:30 · 219 阅读 · 0 评论 -
LeetCode-SQL-[Easy][水题]627. 交换工资
create table if not exists salary(id int, name varchar(100), sex char(1), salary int)Truncate table salaryinsert into salary (id, name, sex, salary) values ('1', 'A', 'm', '2500')insert into salar...原创 2020-03-29 10:26:29 · 194 阅读 · 0 评论 -
LeetCode-SQL-[Mid]626. 换座位
Create table If Not Exists seat(id int, student varchar(255))Truncate table seatinsert into seat (id, student) values ('1', 'Abbot')insert into seat (id, student) values ('2', 'Doris')insert into...原创 2020-03-29 10:24:29 · 226 阅读 · 0 评论 -
LeetCode-SQL-[Easy][水题]620. 有趣的电影
Create table If Not Exists cinema (id int, movie varchar(255), description varchar(255), rating float(2, 1))Truncate table cinemainsert into cinema (id, movie, description, rating) values ('1', 'Wa...原创 2020-03-29 10:18:28 · 204 阅读 · 0 评论 -
LeetCode-SQL-[Hard]601. 体育馆的人流量
Create table If Not Exists stadium (id int, visit_date DATE NULL, people int)Truncate table stadiuminsert into stadium (id, visit_date, people) values ('1', '2017-01-01', '10')insert into stadium ...原创 2020-03-29 10:15:40 · 293 阅读 · 0 评论 -
LeetCode-SQL-[Easy][水题]596. 超过5名学生的课
select class from coursesgroup by classhaving count(distinct student)>=5原创 2020-03-28 12:05:04 · 205 阅读 · 0 评论 -
LeetCode-SQL-[Easy][水题]595. 大的国家
SELECT name, population, area FROM worldWHERE area > 3000000 OR population > 25000000原创 2020-03-28 11:59:19 · 215 阅读 · 0 评论 -
LeetCode-SQL-[Mid]1393. 资本损益[自翻译]
本题自翻译,翻译版本已提交官方审核(stock_name, day) 是这张表的主键. operation这一列是一个ENUM枚举类型('Sell', 'Buy')此表的每一行都表示具有股票名称 stock_name 的股票在 operation_day 这一天,以价格price执行了一个操作operation('Sell', 'Buy')我们保证每一个'Sell'(卖出)操作,在卖出...原创 2020-03-28 11:42:49 · 752 阅读 · 0 评论 -
LeetCode-SQL-[Hard]1384. 按年度列出销售总额
Create table If Not Exists Product (product_id int, product_name varchar(30))Create table If Not Exists Sales (product_id varchar(30), period_start date, period_end date, average_daily_sales int)Tr...转载 2020-03-28 11:13:13 · 989 阅读 · 0 评论 -
LeetCode-SQL-[Easy]1378. 使用唯一标识码替换员工ID
Create table If Not Exists Employees (id int, name varchar(20))Create table If Not Exists EmployeeUNI (id int, unique_id int)Truncate table Employeesinsert into Employees (id, name) values ('1', '...原创 2020-03-28 10:48:10 · 799 阅读 · 1 评论 -
LeetCode-SQL-[Hard]1369. 获取最近第二次的活动
select username,activity,startDate,endDatefrom UserActivity uwhere (u.username, u.startDate) in ( select u2.username, max(u2.startDate) from UserActivity u2 where (u2.username, u...原创 2020-03-28 10:43:47 · 328 阅读 · 0 评论 -
LeetCode-SQL-[Mid]1364. 顾客的可信联系人数量
Create table If Not Exists Customers (customer_id int, customer_name varchar(20), email varchar(30))Create table If Not Exists Contacts (user_id int, contact_name varchar(20), contact_email varchar(...原创 2020-03-28 10:33:27 · 299 阅读 · 0 评论 -
LeetCode-SQL-[Mid]1355. 活动参与者
Create table If Not Exists Friends (id int, name varchar(30), activity varchar(30))Create table If Not Exists Activities (id int, name varchar(30))Truncate table Friendsinsert into Friends (id, na...原创 2020-03-28 10:14:03 · 275 阅读 · 0 评论 -
LeetCode-SQL-[Easy]1350. 院系无效的学生
Create table If Not Exists Departments (id int, name varchar(30))Create table If Not Exists Students (id int, name varchar(30), department_id int)Truncate table Departmentsinsert into Departments ...原创 2020-03-28 09:31:49 · 312 阅读 · 0 评论 -
LeetCode-SQL-1341. 电影评分
Create table If Not Exists Movies (movie_id int, title varchar(30))Create table If Not Exists Users (user_id int, name varchar(30))Create table If Not Exists Movie_Rating (movie_id int, user_id int...原创 2020-03-27 17:05:19 · 368 阅读 · 0 评论 -
LeetCode-SQL-1336. 每次访问的交易次数(未解答)
Create table If Not Exists Visits (user_id int, visit_date date)Create table If Not Exists Transactions (user_id int, transaction_date date, amount int)Truncate table Visitsinsert into Visits (use...原创 2020-03-27 16:33:55 · 612 阅读 · 0 评论 -
LeetCode-SQL-1327. 列出指定时间段内所有的下单产品
Create table If Not Exists Products (product_id int, product_name varchar(40), product_category varchar(40))Create table If Not Exists Orders (product_id int, order_date date, unit int)Truncate tab...原创 2020-03-27 16:12:21 · 432 阅读 · 0 评论 -
LeetCode-SQL-1322. 广告效果
Create table If Not Exists Ads (ad_id int, user_id int, action ENUM('Clicked', 'Viewed', 'Ignored'))Truncate table Adsinsert into Ads (ad_id, user_id, action) values ('1', '1', 'Clicked')insert in...原创 2020-03-27 16:04:15 · 465 阅读 · 0 评论 -
LeetCode-SQL-1321. 餐馆营业额变化增长
Create table If Not Exists Customer (customer_id int, name varchar(20), visited_on date, amount int)Truncate table Customerinsert into Customer (customer_id, name, visited_on, amount) values ('1', ...原创 2020-03-27 15:14:53 · 334 阅读 · 0 评论 -
LeetCode-SQL-1308. 不同性别每日分数总计
Create table If Not Exists Scores (player_name varchar(20), gender varchar(1), day date, score_points int)Truncate table Scoresinsert into Scores (player_name, gender, day, score_points) values ('A...原创 2020-03-27 12:02:22 · 323 阅读 · 0 评论 -
LeetCode-SQL-1303. 求团队人数
Create table If Not Exists Employee (employee_id int, team_id int)Truncate table Employeeinsert into Employee (employee_id, team_id) values ('1', '8')insert into Employee (employee_id, team_id) va...原创 2020-03-27 11:31:08 · 429 阅读 · 0 评论 -
LeetCode-SQL-1294. 不同国家的天气类型
select country_name,case when avg(weather_state)<=15 then 'Cold'when avg(weather_state)>=25 then 'Hot'else 'Warm' end weather_typefrom Weather w join Countries c on c.country_id=w.c...原创 2020-03-27 11:25:38 · 296 阅读 · 0 评论 -
LeetCode-SQL-1285. 找到连续区间的开始和结束数字
SELECT min(log_id) start_id,max(log_id) end_idFROM ( SELECT log_id, CASE WHEN @preNum = log_id - 1 THEN @groupNum := @groupNum ELSE @groupNum := @groupNum + 1 END...原创 2020-03-27 10:55:43 · 756 阅读 · 0 评论 -
LeetCode-SQL-1280. 学生们参加各科测试的次数(cross join)
Create table If Not Exists Students (student_id int, student_name varchar(20))Create table If Not Exists Subjects (subject_name varchar(20))Create table If Not Exists Examinations (student_id int, ...原创 2020-03-26 18:27:11 · 547 阅读 · 0 评论 -
LeetCode-SQL-1270. 向公司CEO汇报工作的所有人
Create table If Not Exists Employees (employee_id int, employee_name varchar(30), manager_id int)Truncate table Employeesinsert into Employees (employee_id, employee_name, manager_id) values ('1', ...转载 2020-03-26 17:50:34 · 390 阅读 · 0 评论 -
LeetCode-SQL-1264. 页面推荐
Create table If Not Exists Friendship (user1_id int, user2_id int)Create table If Not Exists Likes (user_id int, page_id int)Truncate table Friendshipinsert into Friendship (user1_id, user2_id) va...原创 2020-03-26 17:43:01 · 323 阅读 · 0 评论 -
LeetCode-SQL-1251. 平均售价
Create table If Not Exists Prices (product_id int, start_date date, end_date date, price int)Create table If Not Exists UnitsSold (product_id int, purchase_date date, units int)Truncate table Price...原创 2020-03-26 17:31:23 · 427 阅读 · 0 评论 -
LeetCode-SQL-1241. 每个帖子的评论数
Create table If Not Exists Submissions (sub_id int, parent_id int)Truncate table Submissionsinsert into Submissions (sub_id, parent_id) values ('1', 'None')insert into Submissions (sub_id, parent_...原创 2020-03-26 17:20:06 · 367 阅读 · 0 评论 -
LeetCode-SQL-1225. 报告系统状态的连续日期
Create table If Not Exists Failed (fail_date date)Create table If Not Exists Succeeded (success_date date)Truncate table Failedinsert into Failed (fail_date) values ('2018-12-28')insert into Fail...原创 2020-03-26 13:04:59 · 485 阅读 · 0 评论 -
LeetCode-SQL-1212. 查询球队积分
select team_id,team_name,sum(if(num_points is null,0,num_points)) as num_points from (select ( case when goals>goals2 then 3 when goals=goals2 then 1 when goals<goals2 then 0...原创 2020-03-26 11:57:26 · 438 阅读 · 0 评论 -
LeetCode-SQL-1211. 查询结果的质量和占比
select distinct query_name, ROUND(sum(rating / position) / count(query_name ), 2) quality, round(sum(if(rating<3,1,0))/count(query_name )*100,2) poor_query_percentageFROM QueriesGROUP BY q...原创 2020-03-26 11:29:24 · 381 阅读 · 0 评论 -
LeetCode-SQL-1205. 每月交易II
# Write your MySQL query statement belowSELECT month,country, SUM(IF(type = 'approved', 1, 0)) AS approved_count, SUM(IF(type = 'approved', amount, 0)) AS approved_amount, ...原创 2020-03-26 11:18:30 · 374 阅读 · 0 评论 -
LeetCode-SQL-1204. 最后一个能进入电梯的人
Create table If Not Exists Queue (person_id int, person_name varchar(30), weight int, turn int)Truncate table Queueinsert into Queue (person_id, person_name, weight, turn) values ('5', 'George ...转载 2020-03-25 15:51:36 · 578 阅读 · 0 评论 -
LeetCode-SQL-1193. 每月交易 I
selectdate_format(trans_date,'%Y-%m') as month,country,count(*) as trans_count,sum(if(state='approved',1,0)) as approved_count,sum(amount) as trans_total_amount,sum(if(state='approved',amou...转载 2020-03-25 14:42:36 · 620 阅读 · 0 评论 -
LeetCode-SQL-1174. 即时食物配送 II
select round(count( case when d.order_date = d.customer_pref_delivery_date then 1 end) * 100/count(*),2) as immediate_percentagefrom Delivery d,(select delivery_id,customer_id,min(orde...原创 2020-03-25 13:09:12 · 505 阅读 · 0 评论 -
LeetCode-SQL-1173. 即时食物配送 I
select round((select count(delivery_id) from Deliverywhere order_date = customer_pref_delivery_date)* 100 / count(delivery_id), 2) as immediate_percentagefrom Delivery原创 2020-03-25 11:51:55 · 399 阅读 · 0 评论 -
LeetCode-SQL-1164. 指定日期的产品价格
select p1.product_id,p1.new_price pricefrom products p1where (p1.product_id,p1.change_date) in ( select p.product_id,max(change_date) lastDate from products p where p.change_date<...原创 2020-03-25 11:47:47 · 466 阅读 · 0 评论 -
LeetCode-SQL-1159. 市场分析 II
SELECT Users.user_id AS 'seller_id', if(O3.item_id is not null AND Users.favorite_brand=Items.item_brand,'yes','no') AS '2nd_item_fav_brand' FROM Users LEFT JOIN ( ...转载 2020-03-25 11:36:13 · 511 阅读 · 0 评论