SQL 实战（五）

一、

将所有to_date为9999-01-01的全部更新为NULL,且 from_date更新为2001-01-01。
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');

答案如下：

update titles_test set to_date=NUll,from_date='2001-01-01'

where to_date='9999-01-01'

 

二、

题目描述

将id=5以及emp_no=10001的行数据替换成id=5以及emp_no=10005,其他数据保持不变，使用replace实现。
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');

1、update 解法

update titles_test set emp_no='10005'

where id='5'

2、replace 

2.1

对比图1和图2 ， 可以发现： 在图1中， id为2 的记录是表中的第一条记录， 当执行完上述的replace语句之后， id为2的记录位于整张表的最后。 这就说明， 这条replace语句删除了原有的id为2的记录， 有插入了一条新的id为2的记录。

答案如下：

replace into titles_test values 
('5', '10005', 'Senior Engineer', '1986-06-26', '9999-01-01')

2.2

对比图2和图3 ， 可以看到， id为2， name为lisi的记录的class字段没有值。

2.3 replace根据主键确定被替换的是哪一条记录

 在该表中， 把id和name指定为复合主键。 在上面两条语句执行的时候， 都在values中指定了id为2， name为lisi 。 执行之后看到的结果也是id为2， name为lisi的记录被替换。

这就说明了replace语句根据主键的值确定被替换的是哪一条记录。

可以看到， 在表中插入了一条新的记录。

6 如果新插入的或替换的记录中， 有字段和表中的其他记录冲突， 那么会删除那条其他记录

上面的第5步同时也说明了这个问题。 对比图4 和图5 ， 发现在插入一条新的id为100， name为a的记录之后， 还删除了id为2， name为lisi的记录。

为什么会这样呢？ 我们在开始的时候说过， 表中的email字段加上了唯一约束。 id为2的记录的email和新插入的id为100的记录中的email相同，

都是123456@qq.com 。 这就导致违反唯一约束， 所以在插入id为100的记录之前， 删除了id为2的记录。

对比图4 和 图5 ， 发现id为5的记录被替换掉， 并且把这条记录的email设置为2@163.com， 这和图4中原有的id为6的记录冲突，

所以导致id为6的记录被删除， 在图5 中已经没有id为6的那条记录了。

replace语句和update语句的对比

对于update语句， 因为经常使用到，应该算比较熟悉。 下面对比一下update和replace语句的行为， 只是简单陈述， 不再以具体实例说明。

update语句使用where子句定位被更新的记录；

update语句可以一次更新一条记录， 也可以更新多条记录， 只要这多条记录都复合where子句的要求；

update只会在原记录上更新字段的值， 不会删除原有记录， 然后再插入新纪录；

如果在update语句中没有指定一些字段， 那么这些字段维持原有的值， 而不会被置空；

3、update 和replace 组合

UPDATE titles_test SET 

emp_no = REPLACE(emp_no,10001,10005) WHERE id = 5

 

三、

题目描述

将titles_test表名修改为titles_2017。
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');

 

alter table titles_test rename to titles_2017 

　　

四、

题目描述

在audit表上创建外键约束，其emp_no对应employees_test表的主键id。
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,
create_date datetime NOT NULL
);

答案：

1、

alter table audit

add foreign key (EMP_no)

references employees_test(ID)

可以看到sqlite3不通过这样对已经存在的表的创建外键的方式。

2、删除原表，再新创建表的同时创建外键。

DROP TABLE audit;
CREATE TABLE audit(
    EMP_no INT NOT NULL,
    create_date datetime NOT NULL,
    FOREIGN KEY(EMP_no) REFERENCES employees_test(ID));

强制的空格，括号格式，　　

 

五、

存在如下的视图：
create view emp_v as select * from employees where emp_no >10005;
如何获取emp_v和employees有相同的数据？
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`));

1、emp_v 就是从employees中挑出来的，所以emp_v就是他们的交集

select * from emp_v

2、where 条件判断

select ev.* from emp_v as ev,employees as ep --一定注意ev.*
where ev.emp_no=ep.emp_no　　

注意输出的只有一个表中的数据，所以是ev.*(或者ep.*)

3、

select * from emp_v 

intersect 

select * from employees 

 

五、

将所有获取奖金的员工当前的薪水增加10%。
create table emp_bonus(
emp_no int not null,
recevied datetime not null,
btype smallint not null);
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`));

1、更新来改变值

update salaries set salary=salary*1.1

where emp_no in (select e.emp_no from emp_bonus as e)

　　

六、

题目描述

针对库中的所有表生成select count(*)对应的SQL语句
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 emp_bonus(
emp_no int not null,
recevied datetime not null,
btype smallint not null);
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 `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 'select count(*) from '||name||';' as cnts from  --连接符的应用

(SELECT name FROM sqlite_master where type='table');  --表名字的获取

注意“||”的应用和sqlite_master来获取名字。

 

七、

题目描述

将employees表中的所有员工的last_name和first_name通过(')连接起来。
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 last_name ||"'"|| first_name from employees

　　

八、

题目描述

查找字符串'10,A,B' 中逗号','出现的次数cnt。

length 和replace函数结合来做

 

select length('10,A,B')-length(replace('10,A,B',',','')) as cnt

　　

九、

题目描述

获取Employees中的first_name，查询按照first_name最后两个字母，按照升序进行排列
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`));

1、

select first_name from employees order by substr(first_name,-2)

2、

select first_name from employees order by substr(first_name,length(first_name)-1,2)

 

十

题目描述

按照dept_no进行汇总，属于同一个部门的emp_no按照逗号进行连接，结果给出dept_no以及连接出的结果employees
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`));

 

select dept_no,group_concat(emp_no)as employees
from dept_emp 
group by dept_no