SQL复习

在SQL复习中，分为两大部分，一主要是先大致过一遍常用的数据操作，二是再结合牛客网上的SQL例题和编程进行查漏补缺。

1. 简介
定义： SQL语句即结构化查询语言，目前使用的是SQL92，
做什么： SQL语句解决–1）关系数据库（关系型数据库是由多张能互相联接的二维行列表格组成的数据库），2 ）非关系型数据库，即数据结构化存储方法的集合，可以是文档或者键值对等，常用spark，hive作为接口。
分类： 常用SQL语句类型

• DDL（ Data Definition Language数据定义语言）： 数据定义类型 ，用来建立数据库

• TPL 事务处理语言

• DCL（ Data Control Language数据控制语言；控制数据库对象的访问权限

• DML（ Data Manipulation Language数据操作语言），对数据进行操作，****

• **2. SQL语法
  insert into table : 数据不存在则插入数据，存在则报错；
  insert or replaceinto table ：数据不存在则插入数据，存在则替换；

insert or ignore into table：数据不存在则插入数据，存在则忽略；

本题考查创建索引的语句：CREATE INDEX 名称 ON 表名(字段名)和唯一索引的关键字UNIQUE

CREATE UNIQUE INDEX uniq_idx_firstname ON actor(first_name);
CREATE INDEX idx_lastname ON actor(last_name);

创建视图 create view 视图名字 as select 列名1，列名2…from 表
【代码】
create view actor_name_view as
select first_name as first_name_v , last_name as last_name_v

本题的思路为如何强制使用索引，indexed by语句
SELECT * FROM salaries INDEXED BY idx_emp_no WHERE emp_no = 10005
【修改 alter】
【增加 add】
【删除 drop】
后面的COLUMN 可以忽略。
alter table actor
add create_date datetime not null default(‘0000-00-00 00:00:00’);

.触发器语法
create trigger [tiggername] before/after [eventname] on [eventname’s tablename]
begin

      --- trigger logic goes here ---;

 end;

2. 使用关键字 new（更新后） / old（更新前） 来访问表单更新的元素。参考http://www.runoob.com/sqlite/sqlite-trigger.html

【代码】
create trigger audit_log after insert
on employees_test
begin
insert into audit values(new.id,new.name);
end;
删除最小记录只保留最小的
delete from titles_test
where emp_no
not in (
select
min(id)
from titles_test
group by emp_no)
update 表 set 字段1=…，字段2=…，… where 条件表达式
update titles_test
set from_date = ‘2001-01-01’,to_date = NULL
where to_date = ‘9999-01-01’

本题考查的是replace函数，其中包含三个参数，第一个参数为该字段的名称，第二参数为该字段的需要被修改值，第三个参数为该字段修改后的值。

update titles_test
set emp_no = replace(emp_no,10001,10005)
where id = 5
alter table 旧表名 rename to 新表名

在修改Sql Server表结构时，常用到Alter语句，把一些常用的alter语句列举如下。

1、向表中添加字段

Alter table [表名] add [列名] 类型

2、删除字段

Alter table [表名] drop column [列名]

3、修改表中字段类型 （可以修改列的类型，是否为空）

Alter table [表名] alter column [列名] 类型

4、添加主键

Alter table [表名] add constraint [ 约束名] primary key( [列名])

5、添加唯一约束

Alter table [表名] add constraint [ 约束名] unique([列名])

6、添加表中某列的默认值

Alter table [表名] add constraint [约束名] default(默认值) for [列名]7、添加约束

Alter table [表名] add constraint [约束名] check (内容)

8、添加外键约束

Alter table [表名] add constraint [约束名] foreign key(列名) referencese 另一表名(列名)

9、删除约束

Alter table [表名] drop constraint [约束名]

10、重命名表

exec sp_rename ‘[原表名]’,’[新表名]’

11、重命名列名

exec sp_rename ‘[表名].[列名]’,’[表名].[新列名]’

12、删除主键，以及主键上的索引

alter table table_name drop constraint clusteredName

【添加外键】
应该是这样的

alter table audit
add foreign key (EMP_no)
references employees_test(id)
1
2
3
如果【删除外键】

alter table audit
drop foreign key EMP_no
1
2
3
由于oj原因只好

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

查询视图和表的交集
SELECT * FROM employees INTERSECT SELECT * FROM emp_v
首先了解SQLITE_MASTER：
SQLite数据库中一个特殊的名叫 SQLITE_MASTER 上执行一个SELECT查询以获得所有表的索引。每一个 SQLite 数据库都有一个叫 SQLITE_MASTER 的表， 它定义数据库的模式。 SQLITE_MASTER 表看起来如下：
CREATE TABLE sqlite_master (
type TEXT,
name TEXT,
tbl_name TEXT,
rootpage INTEGER,
sql TEXT
);
对于表来说，type 字段永远是 ‘table’，name 字段永远是表的名字。
解答如下：

select “select count(*) from “||name||”;”
as cnts from sqlite_master where type=‘table’

|| 作用
||表示拼接，如’a’||‘b’ 等价于’ab’

二、|| 举例：
批量生成select语句

获取Employees中的first_name，查询按照first_name最后两个字母，按照升序进行排列

【sql的字符串下标从1-length(str)】
【substr(X,Y,Z) x是字符串，y是起始位置，z是长度】
【substr(X,Y) x是字符窜，y是起始位置，选择的是从y向后的字符串】

select first_name
from employees
order by substr(first_name,length(first_name)-1)
本题要用到SQLite的聚合函数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 dept_no
select avg(salary） as avg_salary
from salaries
where salary not in
(select max(salary) from salaries
union
select min(salary) from salaries)
and to_date=‘9999-01-01’

题意，每行5页，返回第2页的数据，即返回第6~10条记录，以下有两种方法可以解决：
方法一：利用 LIMIT 和 OFFSET 关键字。LIMIT 后的数字代表返回几条记录，OFFSET 后的数字代表从第几条记录开始返回（第一条记录序号为0），也可理解为跳过多少条记录后开始返回。
1
SELECT * FROM employees LIMIT 5 OFFSET 5
方法二：只利用 LIMIT 关键字。注意：在 LIMIT X,Y 中，Y代表返回几条记录，X代表从第几条记录开始返回（第一条记录序号为0），切勿记反。
1
SELECT * FROM employees LIMIT 5,5
这两种方法等价
自然交： 根据左右两表的相同列创建一个隐含的join操作，相同列就是两表中列名相同的两列。自然交可以是内交，左交或者是右交。默认是内交。

本题主要考查 SQLite 中 CASE 表达式的用法。即当 btype = 1 时，得到 salary * 0.1；当 btype = 2 时，得到 salary * 0.2；其他情况得到 salary * 0.3。详细用法请参考：
http://www.sqlite.org/lang_expr.html 中的【The CASE expression】
http://www.2cto.com/database/201202/120267.html 中的【条件表达式】

按照salary的累计和running_total，其中running_total为前两个员工的salary累计和，其他以此类推。 具体结果如下Demo展示。
SELECT s1.emp_no, s1.salary,
(SELECT SUM(s2.salary) FROM salaries AS s2
WHERE s2.emp_no <= s1.emp_no AND s2.to_date = ‘9999-01-01’) AS running_total
FROM salaries AS s1 WHERE s1.to_date = ‘9999-01-01’ ORDER BY s1.emp_no

本题的思路为复用 salaries 表进行子查询，最后以 s1.emp_no 排序输出求和结果。
1、输出的第三个字段，是由一个 SELECT 子查询构成。将子查询内复用的 salaries 表记为 s2，主查询的 salaries 表记为 s1，当主查询的 s1.emp_no 确定时，对子查询中不大于 s1.emp_no 的 s2.emp_no 所对应的薪水求和
2、注意是对员工当前的薪水求和，所以在主查询和子查询内都要加限定条件 to_date = ‘9999-01-01’
又是一道比较典型的题目，需要理解记忆一下，答案来自牛客网，Mark。

对于employees表中，给出奇数行的first_name
SELECT e1.first_name FROM
(SELECT e2.first_name,
(SELECT COUNT(*) FROM employees AS e3
WHERE e3.first_name <= e2.first_name)
AS rowid FROM employees AS e2) AS e1
WHERE e1.rowid % 2 = 1

又一道送命题，题目看起来异常简单，实现过程较为复杂。参见牛客网分析解答：
首先题目的叙述有问题，导致理解有误，输出的数据与参考答案不同。先给出正确的题目叙述：【对于employees表，在对first_name进行排名后，选出奇数排名对应的first_name】。
1、本题用到了三层 SELECT 查询，为了便于理解，采用缩进方式分层，且最外层对应e1，最内层对应e3；
2、在e3层中，采用 COUNT() 函数对 e2.first_name 进行排名标号，即在给定 e2.first_name的情况下，不大于 e2.first_name 的 e3.first_name 的个数有多少，该个数刚好与 e2.first_name 的排名标号匹配，且将该值命名为 rowid；
/注意：排名标号后并未排序，即[Bob, Carter, Amy]的排名是[2,3,1]，选取奇数排名后输出[Carter, Amy]，所以可见参考答案中的first_name并未按字母大小排序/
3、在e1层中，直接在限定条件 e1.rowid % 2 = 1 下，代表奇数行的 rowid，选取对应的 e1.first_name；
4、e2层则相当于连接e1层（选取表示层）与e3层（标号层）的桥梁。

SQL 的主键和外键的作用：

外键取值规则：空值或参照的主键值
(1)插入非空值时，如果主键值中没有这个值，则不能插入。
(2)更新时，不能改为主键表中没有的值。
(3)删除主键表记录时，可以在建外键时选定外键记录一起联删除还是拒绝删除。
(4)更新主键记录时，同样有级联更新和拒绝执行的选择。
简而言之，SQL的主键和外键就是起约束作用。
关系型数据库中一条记录中有若干个属性，若其中某一个属性组（注意是组）能唯一标识一条记录，该属性就可以成为一个主键。例如：

学生表(学号，姓名，性别，班级)
其中每个学生的学号是唯一的，学号就是一个主键；

课程表(课程编号，课程名，学分)
其中课程编号是唯一的，课程编号就是一个主键；

成绩表(学号，课程号，成绩)
成绩表中单一一个属性无法唯一标识一条记录，学号和课程编号的组合才可以唯一标识一条记录，所以学号和课程编号的属性组是一个主键。
成绩表中的学号不是成绩表中的主键，但它和学生表中的学号相对应，并且学生表中的学号是学生表的主键，则称成绩表中的学号是学生表的外键；同理，成绩表中的课程号是课程表的外键。

定义主键和外键主要是为了维护关系数据库的完整性，总结一下：

1.主键是能确定一条记录的唯一标识。比如，一条记录包括身份证号码，姓名，年龄。身份证号码是唯一确认你这个人的，其他的都可能有重复，所以身份证号码是主键。
外键用于与另一张表相关联。是能确认另一张表记录的字段，用于保持数据的一致性。比如，A表中的一个字段，是B表的主键，那它就可以是A表的外键。

2.主键、外键和索引的区别

定义：
主键：唯一标识一条记录，不能有重复，不允许为空。
外键：表的外键是另一表的主键，外键是可以有重复的，可以是空值。
索引：该字段没有重复值，但可以有一个空值。
作用：
主键：用来保证数据完整性
外键：用来和其他表建立联系用
索引：用来提高查询排序的速度
个数：
主键：主键只能有一个。
外键：一个表可以有多个外键。
索引：一个表可以有多个唯一索引。