牛客网数据库SQL实践
weixin_43160613
小菜鸟的坚持历程
展开
-
数据库SQL实践61:对于employees表中,给出奇数行的first_name
思路:将小于等于e1.first_name的first_name的个数统计下,然后求余2看是否等于1,若是则取出。select e1.first_name from employees e1where(select count(*) from employees e2 where e1.first_name>=e2.first_name)%2=1; ...原创 2018-12-19 10:06:36 · 300 阅读 · 0 评论 -
数据库SQL实践45:将titles_test表名修改为titles_2017
思路:alter table titles_test rename to titles_2017;rename table titles_test to titles_2017;两个方法在mysql控制台中都可以成功,而在牛客网中只有第一个可以通过。...原创 2018-12-03 09:34:50 · 262 阅读 · 0 评论 -
数据库SQL实践43:将所有to_date为9999-01-01的全部更新为NULL
思路:运用update 表名 set 列名=新列值,列名=新列值,....where 表达式。update titles_test set from_date='2001-01-01',to_date=null where to_date='9999-01-01';原创 2018-12-01 10:28:03 · 93 阅读 · 0 评论 -
数据库SQL实践40:在last_update后面新增加一列名字为create_date
思路:用 alter table ... add ... 语句可以向已存在的表插入新字段,并且能够与创建表时一样,在字段名和数据类型后加入not null、default等限定alter table actor add create_date datetime not null default '0000-00-00 00:00:00'; ...原创 2018-11-28 09:25:47 · 118 阅读 · 0 评论 -
数据库SQL实践39:针对上面的salaries表emp_no字段创建索引idx_emp_no
思路:SQLite中,使用 indexed by 语句进行强制索引查询select * from salaries indexed by idx_emp_no where emp_no=10005;MySQL中,使用 force index(indexname)语句进行强制索引查询select * from salaries force index idx_emp_no whe...原创 2018-11-27 10:06:55 · 213 阅读 · 0 评论 -
数据库SQL实践38:针对actor表创建视图actor_name_view
思路:1.直接在视图名的后面用小括号创建视图中的字段名create view actor_name_view (first_name_v, last_name_v) asselect first_name, last_name from actor;2.注意 create view ... as ... 中的 as 是创建视图语法中的一部分,而后面的两个 as 只是为字段创建别名...原创 2018-11-27 09:55:30 · 160 阅读 · 0 评论 -
数据库SQL实践37:对first_name创建唯一索引uniq_idx_firstname
思路:用create函数创建唯一索引和普通索引。create unique index uniq_idx_firstname on actor(first_name);create index idx_lastname on actor(last_name);mysql数据库还可以alter table actor add unique index uniq_idx_firs...原创 2018-11-27 09:47:38 · 201 阅读 · 0 评论 -
数据库SQL实践42:删除emp_no重复的记录,只保留最小的id对应的记录
思路:先找出每个emp_no中最小的id,若id不在最小id集合中则删除该记录。delete from titles_test where id not in (select min(id) from titles_test group by emp_no);delect from 表名 where 表达式。似乎不可以用group by等其他子句。...原创 2018-11-30 09:43:56 · 166 阅读 · 0 评论 -
数据库SQL实践44:
思路:方法一:全字段更新替换。由于 replace 的新记录中 id=5,与表中的主键 id=5 冲突,故会替换掉表中 id=5 的记录,否则会插入一条新记录(例如新插入的记录 id = 10),并且要将所有字段的值写出,否则将置为空。已经知道所有字段值的写法:replace into titles_test values(5, 10005, 'Senior Engineer', '...原创 2018-12-02 10:10:49 · 100 阅读 · 0 评论 -
数据库SQL实践41:构造一个触发器audit_log
思路:1.创建触发器使用语句:create trigger trigname;2.指定触发器触发的事件在执行某操作之前还是之后,使用语句:before/after [insert/update/add] on tablename3.触发器触发的事件写在begin和end之间;4.触发器中可以通过new获得触发事件之后对应的tablename的相关列的值,old获得触发事件之前的对...原创 2018-11-29 09:35:18 · 171 阅读 · 0 评论 -
数据库SQL实践35:批量插入数据,不使用replace操作
思路:SQLite如果不存在则插入,如果存在则忽略INSERT OR IGNORE INTO tablename VALUES(...);如果不存在则插入,如果存在则替换INSERT OR REPLACE INTO tablename VALUES(...);这里指的存在表示的是unique属性的列值存在的情况下,unique表示键值唯一mysql 去掉orinser...原创 2018-11-23 09:27:05 · 129 阅读 · 0 评论 -
数据库SQL实践32:将employees表的所有员工的last_name和first_name拼接起来作为Name
思路:SQLite字符串的连接需要用||。Mysql字符串连接可以用concat或者concat_wsselect last_name||" "||first_name as Name from employees;CONCAT(str1,str2,…) 返回结果为连接参数产生的字符串。如有任何一个参数为NULL ,则返回值为 NU...原创 2018-11-20 09:30:48 · 788 阅读 · 0 评论 -
数据库SQL实践30:使用子查询的方式找出属于Action分类的所有电影对应的title,description
思想:题目要求使用子查询的方式找出属于Action分类的所有电影对应的title,description。首先通过条件(select category_id from category where name='Action')找出属于Action分类的分类id其次通过条件(select film_id from film_category where category_id in (s...原创 2018-11-18 10:32:36 · 302 阅读 · 0 评论 -
数据库SQL实践36:创建一个actor_name表
思路:1.先按题目要求创建一个表actor_name;2.然后从actor中选出first_name和last_name插入actor_name中。(注意点:这时没有values。我也不太懂为什么)create table actor_name (first_name varchar(45) not null,last_name varchar(45) not null);in...原创 2018-11-24 09:43:02 · 155 阅读 · 0 评论 -
数据库SQL实践47:如何获取emp_v和employees相同的数据
思路:先找出emp_v中的emp_no,然后作为集合,判断employees中的emp_no是否在这个集合中,若在则取出记录。select * from employees where emp_no in (select emp_no from emp_v);由于视图 emp_v 的记录是从 employees 中导出的,所以要判断两者中相等的数据,只需要判断emp_no相等即可。...原创 2018-12-05 09:51:51 · 189 阅读 · 0 评论 -
数据库SQL实践50:将employees表中的所有员工的last_name和first_name通过(')连接起来
思路:在本题所用的SQLite数据库中,只支持用连接符号"||"来连接字符串,不支持用函数连接select last_name||"'"||first_name as name from employees;原创 2018-12-08 09:54:37 · 1495 阅读 · 0 评论 -
数据库SQL实践54:查找排除当前最大、最小salary之后的员工的平均工资avg_salary
思路:先选出最大工资,再选出最小工资,最终排除最大和最小工资,求平均。(但题目要求排除当前最大工资和当前最小工资,似乎有bug)select avg(salary) as avg_salary from salarieswhere to_date = '9999-01-01'and salary not in (select max(salary) from salaries) a...原创 2018-12-12 09:41:35 · 533 阅读 · 0 评论 -
数据库SQL实践60:统计salary的累计和running_total
思路:观察可知running_total是小于或等于当前emp_no工资的总和。select s.emp_no,s.salary,(select sum(ns.salary) from salaries ns where ns.emp_no <=s.emp_no and ns.to_date='9999-01-01') as running_totalfrom salaries...原创 2018-12-18 09:46:03 · 140 阅读 · 0 评论 -
数据库SQL实践59:获取有奖金的员工相关信息。
思路:可以奖金可以通过btype*salary/10.0得到。注意要除以10.0,如果除以10的话,结果的小数位会被舍去。select e.emp_no,e.first_name,e.last_name,eb.btype,s.salary,(eb.btype*s.salary/10.0) as bonus from employees e inner join emp_bonus eb...原创 2018-12-17 18:27:48 · 446 阅读 · 0 评论 -
数据库SQL实践53:按照dept_no进行汇总
思路:聚合函数group_concat(X,Y),其中X是要连接的字段,Y是连接时用的符号,可省略,默认为逗号。此函数必须与 GROUP BY 配合使用。此题以 dept_no 作为分组,将每个分组中不同的emp_no用逗号连接起来(即可省略Y)。select dept_no,group_concat(emp_no)as employees from dept_emp group by ...原创 2018-12-11 09:31:53 · 205 阅读 · 0 评论 -
数据库SQL实践58:获取employees中的行数据,且这些行也存在于emp_v中
思路:根据题意,不能使用 INTERSECT 关键字,但由于视图 emp_v 的记录是从 employees 中导出的,因此要判断两者中相等的数据,只需要判断emp_no相等即可。方法一:用 where 选取二者 emp_no 相等的记录select em.* from employees as em, emp_v as ev where em.emp_no = ev.emp_no...原创 2018-12-16 09:24:46 · 232 阅读 · 0 评论 -
数据库SQL实践52:获取Employees中的first_name
思路:substr(X,Y,Z) 或 substr(X,Y) 函数的使用。其中X是要截取的字符串。Y是字符串的起始位置(注意第一个字符的位置为1,而不为0),Y取值范围是±(1~length(X)),当Y等于length(X)时,则截取最后一个字符;当Y等于负整数-n时,则从倒数第n个字符处截取。Z是要截取字符串的长度,取值范围是正整数,若Z省略,则从Y处一直截取到字符串末尾;若Z大于剩下的字...原创 2018-12-10 09:50:41 · 173 阅读 · 0 评论 -
数据库SQL实践57:使用含有关键字exists查找未分配具体部门的员工的所有信息。
思路:在 employees 中挑选出令(SELECT emp_no FROM dept_emp WHERE emp_no = employees.emp_no)不成立的记录,也就是当 employees.emp_no=10011的时候。select * from employees where not exists (select emp_no from dept_emp where ...原创 2018-12-15 11:10:50 · 324 阅读 · 0 评论 -
数据库SQL实践49:针对库中的所有表生成select count(*)对应的SQL语句
思路:列出数据库中所有表名:select name from sqlite_master where type='table'用||连接"select count(*) from" || name || ";"从而实现连接select "select count(*) from " || name || ";" as cntsfrom sqlite_master where...原创 2018-12-07 09:42:01 · 412 阅读 · 0 评论 -
数据库SQL实践51:查找字符串'10,A,B' 中逗号','出现的次数cnt
思路:由于 SQLite 中没有直接统计字符串中子串出现次数的函数,因此本题用length()函数与replace()函数的结合灵活地解决了统计子串出现次数的问题,属于技巧题,即先用replace函数将原串中出现的子串用空串替换,再用原串长度减去替换后字符串的长度,最后除以子串的长度(本题中此步可省略,若子串长度大于1则不可省)select length("10,A,B")-length...原创 2018-12-09 10:46:22 · 751 阅读 · 0 评论 -
数据库SQL实践56:获取所有员工的emp_no
思路:先将employees和dept_emp内连接找到每个员工所对应的部门,然后左连接emp_bonus找出每个员工的奖金类型btype和得到的实间recevied。select e.emp_no,d.dept_no,eb.btype,eb.recevied from employees as e inner join dept_emp as d on e.emp_no=d.emp_n...原创 2018-12-14 09:58:37 · 145 阅读 · 0 评论 -
数据库SQL实践46:在audit表上创建外键约束,其emp_no对应employees_test表的主键id
思路:由于SQLite中不能通过 alter table ... add foreign key ... references ... 语句来对已创建好的字段创建外键,因此只能先删除表,再重新建表的过程中创建外键drop table audit;create table audit( EMP_no INT NOT NULL, create_date datetime N...原创 2018-12-04 09:40:27 · 145 阅读 · 0 评论 -
数据库SQL实践55:分页查询employees表,每5行一页,返回第2页的数据
思路:根据题意,每行5页,返回第2页的数据,即返回第6~10条记录。select * from employees limit 5,5;原创 2018-12-13 09:31:10 · 1110 阅读 · 0 评论 -
数据库SQL实践48:将所有获取奖金的员工当前的薪水增加10%
思路:薪水增加10%,即salary=salary*1.1update salaries set salary=salary*1.1 where emp_no in (select emp_no from emp_bonus);原创 2018-12-06 09:35:11 · 10924 阅读 · 2 评论 -
数据库SQL实践29:使用join查询方式找出没有分类的电影id以及名称
思想:题目要求使用join查询方式找出没有分类的电影id以及名称。首先将表film和film_category进行左连接,即若film没有category_id,则category_id=null最后用where条件找出没有category_id的film。select f.film_id,f.title from film f left join film_category f...原创 2018-11-17 09:51:08 · 408 阅读 · 0 评论 -
数据库SQL实践34:批量插入数据
思路:运用insert into 表名 (列名1,列名2,列名3....)values (values1,values2,....),(values1,values2,....)...;1.当给所有列插入数据时,加粗的列名可以省略不写2.每条数据用逗号分隔,从而实现批量插入insert into actor values(1,"PENELOPE","GUINESS","2006...原创 2018-11-22 09:29:33 · 157 阅读 · 0 评论 -
数据库SQL实践27:给出每个员工每年薪水涨幅超过5000的员工编号emp_no
思想:题目要求给出每个员工每年薪水涨幅超过5000的员工编号emp_no、薪水变更开始日期from_date以及薪水涨幅值salary_growth,并按照salary_growth逆序排列。提示:在sqlite中获取datetime时间对应的年份函数为strftime('%Y', to_date)首先通过条件将s1表和s2以员工号相等相连其次通过条件将s2.salary-s1.s...原创 2018-11-15 10:35:07 · 454 阅读 · 0 评论 -
数据库SQL实战15:查找employees表
思想:题目要求查找employees表所有emp_no为奇数,且last_name不为Mary的员工信息,并按照hire_date逆序排列。首先通过条件(emp_no%2)=1找出所有奇数的emp_no,其次通过条件last_name!='Mary'找出last_name不为Mary的员工,最后通过条件order by hire_date desc实现按照hire_date逆序排序。se...原创 2018-11-03 10:38:59 · 280 阅读 · 0 评论 -
数据库SQL实践13:从titles表获取按照title进行分组
思想:题目要求从titles表获取按照title进行分组,每组个数大于等于2,给出title以及对应的数目t。首先通过条件group by title实现按照title分组,其次通过条件having t>=2实现每组个数大于等于2(其中t是count(*)即每组的个数)。select title,count(*) as t from titles group by title ha...原创 2018-11-01 10:29:44 · 280 阅读 · 0 评论 -
数据库SQL实践11:获取所有员工当前的manager
思想:题目要求获取所有员工当前的manager,如果当前的manager是自己的话结果不显示,当前表示to_date='9999-01-01'。结果第一列给出当前员工的emp_no,第二列给出其manager对应的manager_no。首先通过条件e.dept_no = m.dept_no找出员工对应的部门,然后通过条件m.to_date ='9999-01-01'确定部门的当前经理,最后通...原创 2018-10-30 09:53:38 · 156 阅读 · 0 评论 -
数据库SQL实战12:获取所有部门中当前员工薪水最高的相关信息
思想:题目要求获取所有部门中当前员工薪水最高的相关信息,给出dept_no, emp_no以及其对应的salary。首先通过条件d.to_date = '9999-01-01'找出所有部门当前员工,然后通过条件s.to_date = '9999-01-01'找出各员工当前薪水,接着通过条件d.emp_no = s.emp_no一一对应,最后按照部门号进行分组,并找出其中salary最大的那个。...原创 2018-10-31 12:11:02 · 938 阅读 · 0 评论 -
数据库SQL实践10:获取所有非manager的员工emp_no
思想:题目要求获取所有非manager的员工emp_no。首先通过子查询找到部门经理的emp_no,然后通过条件where emp_no not in找出非manager的emp_no。select emp_no from employees where emp_no not in (select emp_no from dept_manager);大佬们的另一种做法:sele...原创 2018-10-29 13:50:24 · 89 阅读 · 0 评论 -
数据库SQL实践9:获取所有部门当前manager的当前薪水情况
思想:题目要求获取所有部门当前manager的当前薪水情况,给出dept_no, emp_no以及salary,当前表示to_date='9999-01-01'。首先通过条件d.to_date = '9999-01-01'来找出部门当前manager,然后通过条件s.to_date = '9999-01-01'来找出当前薪水,最后通过条件d.emp_no = s.emp_no来确定部门当前ma...原创 2018-10-29 11:05:04 · 122 阅读 · 0 评论 -
数据库SQL实践8:找出所有员工当前薪水salary情况
思想:题目要求找出所有员工当前薪水salary情况,对于相同的薪水只显示一次,并按照逆序显示。首先根据当前薪水的要求通过条件to_date = '9999-01-01'来达到目的,然后根据相同的薪水只显示一次的要求通过条件distinct salary来达到目的,最后通过order by salary desc来实现逆序。select distinct salary from salar...原创 2018-10-28 13:17:42 · 159 阅读 · 0 评论 -
数据库SQL实践7:查找薪水涨幅超过15次的员工号emp_no以及其对应的涨幅次数t
思想:题目要求查找薪水涨幅超过15次的员工号emp_no以及其对应的涨幅次数t。首先通过group by函数对emp_no进行分组,将同一个emp_no的记录分为一组并通过count(*)计算这个emp_no组中一共有几条记录,最后通过having函数找出记录数大于15的。知识点:1.group by 按照emp_no进行分组,同一个emp_no分为一组。2.count(*) 计算...原创 2018-10-28 13:12:58 · 121 阅读 · 0 评论