Date: 2019-08-02
1. 创建一个actor表,包含如下列信息
列表 | 类型 | 是否为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 | 最后更新时间,默认是系统的当前时间 |
注意:
根据题意,本题关键点是actor_id的主键设置与last_update的默认获取系统时间:
1、在actor_id字段末尾加上PRIMARY KEY是将该字段设置为主键,或者在表的最后一行加上PRIMARY KEY(actor_id)
2、在last_update末尾加上DEFAULT是为该字段设置默认值,且默认值为(datetime('now','localtime')),即获得系统时间,注意最外层的括号不可省略
CREATE TABLE 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'))
)
2. 对于表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')))
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语句进行多行数据到的插入。以及数据类型的插入。
INSERT INTO actor(actor_id, first_name, last_name,last_update)
VALUES(
'1',
'PENELOPE',
'GUINESS',
'2006-02-15 12:34:33'),
('2',
'NICK',
'WAHLBERG',
'2006-02-15 12:34:33');
3. 批量插入数据,不使用replace操作
对于表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' |
注意::
1) 在 SQLite 中,用 INSERT OR IGNORE 来插入记录,或忽略插入与表内UNIQUE字段都相同的记录
1 |
|
2)用 INSERT OR REPLACE 来插入记录,或更新替代与表内UNIQUE字段都相同的记录
2 |
|
INSERT or IGNORE INTO actor
VALUES ('3','ED','CHASE','2006-02-15 12:34:33');
4. 创建一个actor_name表 并从现有的表中导入相应的数据
对于如下表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 | 姓氏 |
# 首先利用create语句建立一个新表,然后将actor表中的数据进行导入。
CREATE TABLE actor_name (
first_name varchar(45) NOT NULL,
last_name varchar(45) NOT NULL);
INSERT into actor_name SELECT first_name, last_name from actor
5. 对first_name创建唯一索引uniq_idx_firstname 考察的是建立唯一索引和普通索引
针对如下表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')))
对first_name创建唯一索引uniq_idx_firstname,对last_name创建普通索引idx_lastname
create unique index uniq_idx_firstname on actor(first_name);
create index idx_lastname on actor(last_name);
6. 针对actor表创建视图actor_name_view
针对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')))
CREATE VIEW actor_name_view AS
SELECT first_name as first_name_v, last_name as last_name_v
FROM actor
7. 针对上面的salaries表emp_no字段创建索引idx_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`));
create index idx_emp_no on salaries(emp_no); # 原始题目中已经建立好了索引
select * from salaries
indexed by idx_emp_no
where emp_no = '10005'
# 满足条件时,强制索引。
8. 在last_update后面新增加一列名字为create_date (新增列)
存在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';
9. 构造一个触发器audit_log
构造一个触发器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
begin
INSERT INTO audit VALUES(new.id, new.name);
end;
10. 删除emp_no重复的记录,只保留最小的id对应的记录。 (删除在某列上重复的值记录)
删除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');
思路;先用 GROUP BY 和 MIN() 选出每个 emp_no 分组中最小的 id,然后用 DELETE FROM ... WHERE ... NOT IN ... 语句删除 “非每个分组最小id对应的所有记录”
DELETE FROM titles_test WHERE id NOT IN
(SELECT MIN(id) FROM titles_test GROUP BY emp_no
11. 将所有to_date为9999-01-01的全部更新为NULL
将所有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'
12. 将id=5以及emp_no=10001的行数据替换成id=5以及emp_no=10005
将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');
UPDATE titles_test
SET emp_no = REPLACE(emp_no,10001,10005)
WHERE id = 5
# 或者
replace into titles_test values(5, 10005, 'Senior Engineer', '1986-06-26', '9999-01-01');
13. 将titles_test表名修改为titles_2017 (修改表名:重命名表)
将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
14. 在audit表上创建外键约束,其emp_no对应employees_test表的主键id (删除已有的表,然后基于之前的内容新建并构建外键)
在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
);
DROP TABLE audit;
CREATE TABLE audit(
emp_no INT NOT NULL,
create_date datetime NOT NULL,
FOREIGN KEY(emp_no) REFERENCES employees_test(ID));
15. 如何获取emp_v和employees有相同的数据no
存在如下的视图:
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`));
输出格式:
emp_no | birth_date | first_name | last_name | gender | hire_date |
---|---|---|---|---|---|
10006 | 1953-04-20 | Anneke | Preusig | F | 1989-06-02 |
10007 | 1957-05-23 | Tzvetan | Zielinski | F | 1989-02-10 |
10008 | 1958-02-19 | Saniya | Kalloufi | M | 1994-09-15 |
10009 | 1952-04-19 | Sumant | Peac | F | 1985-02-18 |
10010 | 1963-06-01 | Duangkaew | Piveteau | F | 1989-08-24 |
由于视图 emp_v 的记录是从 employees 中导出的,所以要判断两者中相等的数据,只需要判断emp_no相等即可。
方法一:用 WHERE 选取二者 emp_no 相等的记录
1 |
|
方法二:用 INTERSECT 关键字求 employees 和 emp_v 的交集
可参考:http://www.sqlite.org/lang_select.html
1 |
|
方法三:仔细一想,emp_v的全部记录均由 employees 导出,因此可以投机取巧,直接输出 emp_v 所有记录
1 |
|
【错误方法:】用以下方法直接输出 *,会得到两张表中符合条件的重复记录,因此不合题意,必须在 * 前加表名作限定
SELECT * FROM employees, emp_v WHERE employees.emp_no = emp_v.emp_no
16. 将所有获取奖金的员工当前的薪水增加10% (借助领取奖学金的表信息,修改薪水表 中某一列的值)
将所有获取奖金的员工当前的薪水增加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`));
UPDATE salaries
SET salary = salary * 1.1
WHERE emp_no in (select emp_no from emp_bonus)
17. 针对库中的所有表生成select count(*)对应的SQL语句
针对库中的所有表生成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`));
输出格式:
cnts |
---|
select count(*) from employees; |
select count(*) from departments; |
select count(*) from dept_emp; |
select count(*) from dept_manager; |
select count(*) from salaries; |
select count(*) from titles; |
思路:
本题主要有以下两个关键点:
1、在 SQLite 系统表 sqlite_master 中可以获得所有表的索引,其中字段 name 是所有表的名字,而且对于自己创建的表而言,字段 type 永远是 'table',详情可参考:
http://blog.csdn.net/xingfeng0501/article/details/7804378
2、在 SQLite 中用 “||” 符号连接字符串
SELECT "select count(*) from " || name || ";" AS cnts
FROM sqlite_master WHERE type = 'table'
18. 将employees表中的所有员工的last_name和first_name通过(')连接起来。
将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`));
输出格式:
name |
---|
Facello'Georgi |
Simmel'Bezalel |
Bamford'Parto |
Koblick'Chirstian |
Maliniak'Kyoichi |
Preusig'Anneke |
Zielinski'Tzvetan |
Kalloufi'Saniya |
Peac'Sumant |
Piveteau'Duangkaew |
SELECT last_name ||"'"|| first_name as name
FROM employees
19. 查找字符串'10,A,B' 中逗号','出现的次数cnt
查找字符串'10,A,B' 中逗号','出现的次数cnt。
思路:由于 SQLite 中没有直接统计字符串中子串出现次数的函数,因此本题用length()函数与replace()函数的结合灵活地解决了统计子串出现次数的问题,属于技巧题,即先用replace函数将原串中出现的子串用空串替换,再用原串长度减去替换后字符串的长度,最后除以子串的长度(本题中此步可省略,若子串长度大于1则不可省)
select (length('10,A,B')-length(replace('10,A,B',',',''))) as cnt
20. 获取Employees中的first_name
获取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`));
输出格式:
first_name |
---|
Chirstian |
Tzvetan |
Bezalel |
Duangkaew |
Georgi |
Kyoichi |
Anneke |
Sumant |
Mary |
Parto |
思路: 本题考查 substr(X,Y,Z) 或 substr(X,Y) 函数的使用。其中X是要截取的字符串。Y是字符串的起始位置(注意第一个字符的位置为1,而不为0),取值范围是±(1~length(X)),当Y等于length(X)时,则截取最后一个字符;当Y等于负整数-n时,则从倒数第n个字符处截取。Z是要截取字符串的长度,取值范围是正整数,若Z省略,则从Y处一直截取到字符串末尾;若Z大于剩下的字符串长度,也是截取到字符串末尾为止。
SELECT first_name
FROM employees
ORDER BY substr(first_name, length(first_name)-1)
|
|