sql实战（牛客网在线）

一、查找最晚入职员工的所有信息，为了减轻入门难度，目前所有的数据里员工入职的日期都不是同一天(sqlite里面的注释为--,mysql为comment)CREATE TABLE `employees` (`emp_no` int(11) NOT NULL,-- '员工编号'`birth_date` date NOT NULL,`first_name` varchar(14) NOT NULL,`l

Valentino112358

179人浏览 · 2020-09-11 00:39:37

Valentino112358 · 2020-09-11 00:39:37 发布

https://www.nowcoder.com/practice/6d35b1cd593545ab985a68cd86f28671?tpId=82&&tqId=29756&rp=1&ru=/ta/sql&qru=/ta/sql/question-ranking

一、(order by)

查找最晚入职员工的所有信息，为了减轻入门难度，目前所有的数据里员工入职的日期都不是同一天
(sqlite里面的注释为--,mysql为comment)

CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,  -- '员工编号'
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));

使用 order by（默认是升序）进行统一排，然后条件排序 desc (降序，从高到低，值由大变小) / asc(升序)，order by 必不可少，条件在择其一。然后用 limit 提取数据即可。

select * from employess order by hire_date desc limit 1;
或
select * from employess order by hire_date desc limit 0,1;

注意所有的数据里员工入职的日期都不是同一天，否则用以上方法不符，要用以下方法。

select * from employess where hire_date = (select max(hire_date) from employess);

=================================================================================================

二、（limit）

查找入职员工时间排名倒数第三的员工所有信息，为了减轻入门难度，目前所有的数据里员工入职的日期都不是同一天
CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));

注意是倒数第三，limit {index},{num} index是起始位置，num是筛选出的数据条数。

select * from employees order by hire_date desc limit 2,1;

三、(left join)

查找各个部门当前领导的薪水详情以及其对应部门编号dept_no，输出结果以salaries.emp_no升序排序，
并且请注意输出结果里面dept_no列是最后一列
CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL, -- '员工编号',
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));
CREATE TABLE `dept_manager` (
`dept_no` char(4) NOT NULL, -- '部门编号'
`emp_no` int(11) NOT NULL, --  '员工编号'
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));

参考输出结果：

答案：

因为是各个部门当前领导的薪水详情，所以dept_manager作为主表。

select 
dept_manager.emp_no as emp_no, salary, from_date, dept_manager.to_date as to_date, dept_no 
from dept_manager left join salaries 
on dept_manager.emp_no = salaries.emp_no
order by dept_manager.emp_no;

四、（inner join）

查找所有已经分配部门的员工的last_name和first_name以及dept_no(请注意输出描述里各个列的前后顺序)
CREATE TABLE `dept_emp` (
`emp_no` int(11) NOT NULL,
`dept_no` char(4) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));
CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));

inner join,right join,left join之间的区别：

inner join(等值连接) 只返回两个表中联结字段相等的行

left join(左联接) 返回包括左表中的所有记录和右表中联结字段相等的记录

right join(右联接) 返回包括右表中的所有记录和左表中联结字段相等的记录

答案：

select employees.last_name,employees.first_name,dept_emp.dept_no from employees 
inner join dept_emp 
on employees.emp_no=dept_emp.emp_no;

注意题目是所有已分配部门的员工，所以用inner join，这样左表不会全部包括。

或者

select last_name, first_name, dept_emp.dept_no as dept_no from employees 
right join dept_emp on employees.emp_no = dept_emp.emp_no;

五、（left join）

查找所有员工的last_name和first_name以及对应部门编号dept_no，
也包括暂时没有分配具体部门的员工(请注意输出描述里各个列的前后顺序)
CREATE TABLE `dept_emp` (
`emp_no` int(11) NOT NULL,
`dept_no` char(4) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));
CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));

答案：

select employees.last_name,employees.first_name,dept_emp.dept_no from employees 
left join dept_emp 
on employees.emp_no=dept_emp.emp_no;

查找所有员工的信息，包括暂时没分配部门的，所以用left join，包括左表中所有数据。

六、

查找所有员工入职时候的薪水情况，给出emp_no以及salary， 并按照emp_no进行逆序(请注意，一个员工可能有多次涨薪的情况)
CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));
CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,   -- 从哪天到
`to_date` date NOT NULL,     -- 哪天的薪资
PRIMARY KEY (`emp_no`,`from_date`));

其中有隐含关系为入职日为第一薪资数据的开始日from_date，从from_date那天到to_date那天的薪水，这段时间薪水不变。

答：

select employees.emp_no,salaries.salary from employees 
left join salaries on employees.emp_no = salaries.emp_no 
where hire_date = from_date 
order by employees.emp_no desc;

因为是所有员工，所以用的是left join，即包括左表所有数据。

七、having

查找薪水变动超过15次的员工号emp_no以及其对应的变动次数t
CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));

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

HAVING 子句可以让我们筛选分组后的各组数据，用于对where和group by查询出来的分组进行过滤，查出满足条件的分组结果。它是一个过滤声明，是在查询返回结果集以后对查询结果进行的过滤操作。（对分组结果进行过滤）

题目隐含：
1.一条记录就算一次涨幅(严格意义上，从第二条记录算起才算一次涨幅)
2.任何一条记录都算一次涨幅(严格意义上，必须比上一条记录的工资高才算一次涨幅)

答：

select emp_no,count(emp_no) from salaries 
group by emp_no
having count(emp_no)>15;

八、group by

找出所有员工当前(to_date='9999-01-01')具体的薪水salary情况，对于相同的薪水只显示一次,并按照逆序显示
CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));

对select查询出来的结果集按照某个字段或者表达式进行分组，获得一组组的集合。

因为相同的薪水只显示一次，所以使用group by salary，用salary进行分组。

答：

select salary from salaries 
where to_date='9999-01-01' 
group by salary 
order by salary desc;

九、

获取所有部门当前(dept_manager.to_date='9999-01-01')manager的当前(salaries.to_date='9999-01-01')薪水情况，给出dept_no, emp_no以及salary(请注意，同一个人可能有多条薪水情况记录)
CREATE TABLE `dept_manager` (
`dept_no` char(4) NOT NULL,
`emp_no` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));
CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));

同一个人可能有多条薪水情况记录，所以用left join

答：

select dept_manager.dept_no,dept_manager.emp_no,salaries.salary from dept_manager 
left join salaries on dept_manager.emp_no = salaries.emp_no 
where dept_manager.to_date='9999-01-01' and salaries.to_date='9999-01-01';

十、not in

获取所有非manager的员工emp_no
CREATE TABLE `dept_manager` (
`dept_no` char(4) NOT NULL,
`emp_no` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));

CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));

not in将非manger的员工排除。

答：

select emp_no from employees 
where emp_no not in 
(select emp_no from dept_manager);

十一、不等于！=

获取所有员工当前的(dept_manager.to_date='9999-01-01')manager，如果员工是manager的话不显示(也就是如果当前的manager是自己的话结果不显示)。输出结果第一列给出当前员工的emp_no,第二列给出其manager对应的emp_no。
CREATE TABLE `dept_emp` (
`emp_no` int(11) NOT NULL, -- '所有的员工编号'
`dept_no` char(4) NOT NULL, -- '部门编号'
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));

CREATE TABLE `dept_manager` (
`dept_no` char(4) NOT NULL, -- '部门编号'
`emp_no` int(11) NOT NULL, -- '经理编号'
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));

获得所有员工当前的manager，根据dept_no判断，在同一个部门，可以找到对应的领导。

答：

select dept_emp.emp_no,dept_manager.emp_no from dept_emp 
left join dept_manager on dept_emp.dept_no = dept_manager.dept_no 
where dept_emp.emp_no != dept_manager.emp_no
and dept_manager.to_date='9999-01-01';

十二、默认是升序的、max取出最大的

获取所有部门中当前(dept_emp.to_date = '9999-01-01')员工当前(salaries.to_date='9999-01-01')薪水最高的相关信息，
给出dept_no, emp_no以及其对应的salary，按照部门升序排列。
CREATE TABLE `dept_emp` (
`emp_no` int(11) NOT NULL,
`dept_no` char(4) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));

CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));

max取出最大的薪资，根据部门的dept_no分组再进行排序。

答：

select dept_emp.dept_no,dept_emp.emp_no,max(salaries.salary) from dept_emp 
left join salaries on dept_emp.emp_no = salaries.emp_no 
where dept_emp.to_date = '9999-01-01' and salaries.to_date='9999-01-01'
group by dept_emp.dept_no
order by dept_emp.dept_no asc;

十三、 having、count()

从titles表获取按照title进行分组，每组个数大于等于2，给出title以及对应的数目t。
CREATE TABLE IF NOT EXISTS "titles" (
`emp_no` int(11) NOT NULL,
`title` varchar(50) NOT NULL,
`from_date` date NOT NULL,
`to_date` date DEFAULT NULL);

此题和第七题一致，题中按照title进行分组，即group by title，每组个数大于等于2，即再使用having，having子句可以让我们筛选分组后的各组数据。

答：

select title,count(title) from titles 
group by title 
having count(title)>=2;

十四、distinct

从titles表获取按照title进行分组，每组个数大于等于2，给出title以及对应的数目t。
注意对于重复的emp_no进行忽略(即emp_no重复的title不计算，title对应的数目t不增加)。
CREATE TABLE IF NOT EXISTS `titles` (
`emp_no` int(11) NOT NULL,
`title` varchar(50) NOT NULL,
`from_date` date NOT NULL,
`to_date` date DEFAULT NULL);

注意对于重复的emp_no进行忽略(即emp_no重复的title不计算，title对应的数目t不增加)。

在表中，可能会包含重复值。这并不成问题，不过，有时您也许希望仅仅列出不同（distinct）的值。

关键词 DISTINCT 用于返回唯一不同的值。

答：

select title,count(distinct emp_no) as t from titles 
group by title 
having t>=2;

十五、desc逆序，从高到低

查找employees表所有emp_no为奇数，且last_name不为Mary(注意大小写)的员工信息，并按照hire_date逆序排列(题目不能使用mod函数)
CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));

desc是逆序，从高到低排序。

答：

select * from employees 
where emp_no%2=1 and last_name != "Mary" 
order by hire_date desc;

十六、avg

统计出当前(titles.to_date='9999-01-01')各个title类型对应的员工当前(salaries.to_date='9999-01-01')
薪水对应的平均工资。
结果给出title以及平均工资avg。
CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));

CREATE TABLE IF NOT EXISTS "titles" (
`emp_no` int(11) NOT NULL,
`title` varchar(50) NOT NULL,
`from_date` date NOT NULL,
`to_date` date DEFAULT NULL);

各个title类型的对应的员工的平均薪资，需要用到group by title，

答：

select titles.title,avg(salaries.salary) from salaries 
left join titles 
on salaries.emp_no = titles.emp_no 
where titles.to_date='9999-01-01' and salaries.to_date='9999-01-01'
group by titles.title;

十七、order by

获取当前（to_date='9999-01-01'）薪水第二多的员工的emp_no以及其对应的薪水salary
CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));

涉及到第二多，这些，order by、limit必须。

答：

select emp_no,salary from salaries 
where to_date='9999-01-01' 
order by salary desc 
limit 1,1;

十八、聚合函数，一般在having下

只有SELECT子句和HAVING子句（以及ORDER BY子句）中能够使用聚合函数。

WHERE子句用来指定数据行的条件，不能使用聚合函数；HAVING子句用来指定分组的条件，可以用聚合函数。

查找当前薪水(to_date='9999-01-01')排名第二多的员工编号emp_no、薪水salary、last_name以及first_name，
你可以不使用order by完成吗
CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));

CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));

先将最大的值排除，再在剩余的数据里面取最大值即可获得，薪资第二多的员工。（注意筛选条件的执行顺序）

答：

select employees.emp_no,max(salaries.salary),employees.last_name,employees.first_name from employees 
left join salaries on employees.emp_no=salaries.emp_no
where to_date='9999-01-01' 
and salaries.salary<(select max(salaries.salary) from salaries where to_date='9999-01-01');

mySql的执行顺序

from ->on ->join ->where ->group by ->having ->select ->distinct ->order by ->limit

十九、多表查询

查找所有员工的last_name和first_name以及对应的dept_name，也包括暂时没有分配部门的员工
CREATE TABLE `departments` (
`dept_no` char(4) NOT NULL,
`dept_name` varchar(40) NOT NULL,
PRIMARY KEY (`dept_no`));

CREATE TABLE `dept_emp` (
`emp_no` int(11) NOT NULL,
`dept_no` char(4) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));

CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));

多表查询，两个左联接。

答：

select employees.last_name,employees.first_name,departments.dept_name from employees 
left join dept_emp on dept_emp.emp_no = employees.emp_no
left join departments on dept_emp.dept_no = departments.dept_no;

二十、聚合函数在select字句后的应用

查找员工编号emp_no为10001其自入职以来的薪水salary涨幅(总共涨了多少)
growth(可能有多次涨薪，没有降薪)
CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));

聚合函数的应用在select子句后的应用 max(salary)-min(salary)

答：

select max(salary)-min(salary) as growth from salaries 
where emp_no = "10001";

二十一、嵌套查询

查找所有员工自入职以来的薪水涨幅情况，给出员工编号emp_no以及其对应的薪水涨幅growth，并按照growth进行升序 
（注:可能有employees表和salaries表里存在记录的员工，有对应的员工编号和涨薪记录，但是已经离职了，
离职的员工salaries表的最新的to_date!='9999-01-01'，这样的数据不显示在查找结果里面） 
CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL, --  '入职时间'
PRIMARY KEY (`emp_no`));

CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL, --  '一条薪水记录开始时间'
`to_date` date NOT NULL, --  '一条薪水记录结束时间'
PRIMARY KEY (`emp_no`,`from_date`));

先设置子表，入职的工资表和现在的工资表，相减获得薪水涨幅。

答：

-- to_date!='9999-01-01' 说明已离职
select b.emp_no, (b.salary-a.salary) as growth
from
-- 入职工资表
(select employees.emp_no,salaries.salary from employees 
left join salaries on employees.emp_no=salaries.emp_no 
and employees.hire_date = salaries.from_date) as a

inner join

-- 现在工资表
(select emp_no,salary from salaries
where to_date='9999-01-01') as b
-- to_date='9999-01-01' 说明还在职
on a.emp_no=b.emp_no
order by growth;

二十二、多表查询，count(*)

统计各个部门的工资记录数，给出部门编码dept_no、
部门名称dept_name以及部门在salaries表里面有多少条记录sum
CREATE TABLE `departments` (
`dept_no` char(4) NOT NULL,
`dept_name` varchar(40) NOT NULL,
PRIMARY KEY (`dept_no`));
CREATE TABLE `dept_emp` (
`emp_no` int(11) NOT NULL,
`dept_no` char(4) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));
CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));

根据部门id分组， count(*)获得一个部门内薪资数据条数。

答：

select departments.dept_no,departments.dept_name,count(*) from departments 
left join dept_emp on departments.dept_no = dept_emp.dept_no
left join salaries on dept_emp.emp_no = salaries.emp_no
group by departments.dept_no;  -- 根据部门id分组， count(*)获得一个部门内薪资数据条数

二十三、排序dense_rank()

对所有员工的当前(to_date='9999-01-01')薪水按照salary进行按照1-N的排名，
相同salary并列且按照emp_no升序排列
CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));

https://blog.csdn.net/ONEMOOC/article/details/106930299

dense_rank()是的排序数字是连续的、不间断。当有相同的分数时，它们的排名结果是并列的，例如，1,2,2,3。

dense_rank() over (order by salary desc)按salary的升序排列。

答：

select emp_no,salary ,dense_rank() over (order by salary desc) rank
from salaries
where to_date='9999-01-01';

二十四、多表查询

获取所有非manager员工当前的薪水情况，给出dept_no、emp_no以及salary ，当前表示to_date='9999-01-01'
CREATE TABLE `dept_emp` (
`emp_no` int(11) NOT NULL,
`dept_no` char(4) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));

CREATE TABLE `dept_manager` (
`dept_no` char(4) NOT NULL,
`emp_no` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));

CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));

CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));

多表查询注意表与表之间的关系。

答：

select dept_emp.dept_no,employees.emp_no,salaries.salary 
from employees
inner join dept_emp on dept_emp.emp_no = employees.emp_no
inner join salaries on dept_emp.emp_no=salaries.emp_no
where salaries.to_date='9999-01-01'
and dept_emp.emp_no not in (select emp_no from dept_manager);

三十三、创建表格，if not exists

创建一个actor表，包含如下列信息(注：sqlite获取系统默认时间是datetime('now','localtime'))

列表	类型	是否为NULL	含义
actor_id	smallint(5)	not null	主键id
first_name	varchar(45)	not null	名字
last_name	varchar(45)	not null	姓氏
last_update	timestamp	not null	最后更新时间，默认是系统的当前时间

答：

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'))
);

三十四、插入

对于表actor批量插入如下数据(不能有2条insert语句哦!)
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')))

actor_id	first_name	last_name	last_update
1	PENELOPE	GUINESS	2006-02-15 12:34:33
2	NICK	WAHLBERG	2006-02-15 12:34:33

答：

insert INTO actor values 
(1,"PENELOPE","GUINESS","2006-02-15 12:34:33"),(2,"NICK","WAHLBERG","2006-02-15 12:34:33");

三十五、插入，insert，ignore

https://blog.csdn.net/mouday/article/details/81281946

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

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')))

actor_id	first_name	last_name	last_update
'3'	'ED'	'CHASE'	'2006-02-15 12:34:33'

插入数据，有则忽略，无则创建

答：

insert or ignore into actor values('3','ED','CHASE','2006-02-15 12:34:33');

===================================================================================

三十六、建表新方法，导入表

对于如下表actor，其对应的数据为:

actor_id	first_name	last_name	last_update
1	PENELOPE	GUINESS	2006-02-15 12:34:33
2	NICK	WAHLBERG	2006-02-15 12:34:33

请你创建一个actor_name表，并且将actor表中的所有first_name以及last_name导入该表.

actor_name表结构如下：

列表	类型	是否为NULL	含义
first_name	varchar(45)	not null	名字
last_name	varchar(45)	not null	姓氏

将select的结果插入新表。

答：

create table actor_name as
select first_name, last_name from actor;

==========================================================================================

三十七、创建索引index

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

(注:在 SQLite 中,除了重命名表和在已有的表中添加列,ALTER TABLE 命令不支持其他操作)

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

(请先创建唯一索引，再创建普通索引)

答：

SQL CREATE INDEX 语法

在表上创建一个简单的索引。允许使用重复的值

SQL CREATE UNIQUE INDEX 语法

在表上创建一个唯一的索引。唯一的索引意味着两个行不能拥有相同的索引值，就如身份证号不能相同。

由于身份证号字段比较大，我不建议你把身份证号当做主键，那么现在你有两个选择，要么给id_card字段创建唯一索引，要么创建一个普通索引。如果业务代码已经保证了不会写入重复的身份证号，那么这两个选择逻辑上都是正确的。

create unique index uniq_idx_firstname on actor(first_name);  -- 对first_name创建唯一索引
create index idx_lastname on actor(last_name);  -- 对last_name创建普通的索引

=========================================================================================

三十八、创建视图

针对actor表创建视图actor_name_view，只包含first_name以及last_name两列，并对这两列重新命名，first_name为first_name_v，last_name修改为last_name_v：

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')))

在 SQL 中，视图是基于 SQL 语句的结果集的可视化的表。

视图包含行和列，就像一个真实的表。视图中的字段就是来自一个或多个数据库中的真实的表中的字段。我们可以向视图添加 SQL 函数、WHERE 以及 JOIN 语句，我们也可以提交数据，就像这些来自于某个单一的表。

注释：数据库的设计和结构不会受到视图中的函数、where 或 join 语句的影响。

SQL CREATE VIEW 语法

CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

答：

create view actor_name_view as 
select first_name first_name_v, last_name last_name_v from actor;

视图相当于表的副本，可以是一个表的，也可以是几个相关联的表提取出来的一个视图，主要是用来显示数据的，修改视图对数据库表没有影响。

=======================================================================

三十九、使用强制索引

针对salaries表emp_no字段创建索引idx_emp_no，查询emp_no为10005, 使用强制索引。

CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));

create index idx_emp_no on salaries(emp_no);  -- 创建索引

使用强制索引

"INDEXED BY index-name" 子句规定必须需要命名的索引来查找前面表中值。

如果索引名 index-name 不存在或不能用于查询，然后 SQLite 语句的准备失败。

SELECT * FROM salaries 
INDEXED BY idx_emp_no 
WHERE emp_no = 10005;

索引有助于加快 SELECT 查询和 WHERE 子句，但它会减慢使用 UPDATE 和 INSERT 语句时的数据输入。索引可以创建或删除，但不会影响数据。

====================================================================================

四十、alter修改表

存在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')));

现在在last_update后面新增加一列名字为create_date, 类型为datetime, NOT NULL，默认值为'0000-00-00 00:00:00'

答：

ALTER TABLE actor 
 -- 增加 列名
ADD COLUMN create_date 
-- 类型
datetime not null default('0000-00-00 00:00:00');

①date类型可用于需要一bai个日期值du而不需要时间部分时；

②zhidatetime类型:可用于需要同时包含日期和时间信dao息的值。

================================================================================

四十一、构造一个触发器

构造一个触发器audit_log，在向employees_test表中插入一条数据的时候，触发插入相关的数据到audit中。

CREATE TABLE employees_test(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL
);

CREATE TABLE audit(
EMP_no INT NOT NULL,
NAME TEXT NOT NULL
);

答：

create trigger audit_log after insert on employees_test   -- employees_test有数据插入时
begin  -- 也插入相关数据到 audit
    insert into audit values(new.id,new.name);
end

触发时间

当 SQL 指令发生时，会令行中数据发生变化，而每张表中对应的行有两种状态：数据操作前和操作后

before：表中数据发生改变前的状态
after：表中数据发生改变后的状态

四十二、delete删除数据，group by

删除emp_no重复的记录，只保留最小的id对应的记录。

CREATE TABLE IF NOT EXISTS titles_test (
id int(11) not null primary key,
emp_no int(11) NOT NULL,
title varchar(50) NOT NULL,
from_date date NOT NULL,
to_date date DEFAULT NULL);

insert into titles_test values 
('1', '10001', 'Senior Engineer', '1986-06-26', '9999-01-01'),
('2', '10002', 'Staff', '1996-08-03', '9999-01-01'),
('3', '10003', 'Senior Engineer', '1995-12-03', '9999-01-01'),
('4', '10004', 'Senior Engineer', '1995-12-03', '9999-01-01'),
('5', '10001', 'Senior Engineer', '1986-06-26', '9999-01-01'),
('6', '10002', 'Staff', '1996-08-03', '9999-01-01'),
('7', '10003', 'Senior Engineer', '1995-12-03', '9999-01-01');

删除重复数据emp_no，所以属于根据emp_no进行分组，用min函数在分组内选出较小的id，根据筛选后的id删除不满足条件的数据。

答：

delete from titles_test where id not in
(select min(id) from titles_test group by emp_no);

一门面向 Data 和 AI 的低代码、云原生的开源编程语言

无需安装部署，在线快速体验 Byzer

更多推荐

编译时权限控制

前言权限控制，对于 MLSQL 而言的重要程度可以说是生命线。 MLSQL 需要面对各式各样的资源访问，比如 MySQL, Oracle,HDFS，Hive，Kafka，Sorl，ElasticSearch，Redis，API，Web等等，不同用户对这些数据源（以及表，列）的权限是不一样的。传统模式是，每个用户都需要有个 proxy user，然后到每个数据源里面给这个 proxy user

Byzer 白泽

Byzer 支持 JDBC 聚合下推

聚合下推PR链接聚合下推我们知道 Byzer 支持多数据源和联邦查询，可以方便分析师在一个平台上快速的分析来自多种数据源的数据，从而进行灵活的探索式分析。使用场景和优势当前 Byzer 中加载数据的方式会拉取明细数据到 spark 中进行聚合计算，对于小数据量或者在分布式文件系统上的数据源来说是常规操作。但是对于 JDBC 数据源或者有分析能力的 OLAP 系统来说拉取明细数据可能就不是最

Byzer 白泽

Byzer 术语表

Byzer-lang Byzer ，又称为 Byzer-lang，一门面向 Data 和 AI 的低代码、云原生的开源编程语言。 Byzer 是一门结合了声明式编程和命令式编程的混合编程语言，其低代码且类 SQL 的编程逻辑配合内置算法及插件的加持，能帮助数据工作者们高效打通数据链路，完成数据的清洗转换，并快速地进行机器学习相关的训练及预测。 Byzer 希望能够提供一套语言、一个引擎，就能覆盖整