SQL：实操以及理论题目

链接

实际操作

创建，增减内容（视图 表 索引 列）

创建（唯一）索引

题目描述

针对如下表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);

强制索引

题目描述

针对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);

代码
MYSQL中强制索引查询使用：FORCE INDEX(indexname);
SQLite中强制索引查询使用：INDEXED BY indexname;

--MySQL
SELECT * FROM salaries FORCE INDEX idx_emp_no WHERE emp_no = 10005;
--SQLite
select * from salaries indexed by idx_emp_no where emp_no=10005; 

在表中新增一列

题目描述

存在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’

代码

alter table actor add column create_date datetime not null
default '0000-00-00 00:00:00';

新增触发器

题目描述

构造一个触发器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;

针对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 (first_name_v, last_name_v) as
select first_name, last_name from actor;

排序

最晚入职员工：避免用limit等来限制存在同时入职的现象

select *
from employees
where hire_date = (select max(hire_date) from employees)

入职倒数第三晚的员工

select * 
from employees
where hire_date = (
    select distinct(hire_date) from employees order by hire_date desc limit 2,1);

limit n,k：用于指定从n+1位置开始取k个数。

理论部分

写在前面：
面试-数据分析-题目1-4：均最初由网易雷火笔试面试——《面试材料提取》拿过来的。

函数并行比较

where 与 having 的异同，优劣

1. Rownumber

2. 关联规则

和关联规则挖掘算法思想类似，给一个数据表，每个用户购物记录中所有关联商品的数量（比如说一个用户购买了s1,s2,s3三种商品，关联商品则为<s1,s2><s2,s3><s1,s3>，商品自身的组合不算，关联商品仅为两元组）,最终显示所有关联商品的数量，用SQL写出来，写不出的话用其他擅长的语言写也可以

3. 左连接右链接、笛卡尔积

内连接：典型的联接运算，类似 = 大于小于符号，包括相等联接以及自然连接。

外连接：下面列出了您可以使用的 JOIN 类型，以及它们之间的差异。
• JOIN = inner join: 如果表中有至少一个匹配，则返回行
• LEFT JOIN: 即使右表中没有匹配，也从左表返回所有的行
• RIGHT JOIN: 即使左表中没有匹配，也从右表返回所有的行
• FULL JOIN: 只要其中一个表中存在匹配，就返回行

交叉联接，也叫笛卡尔积（实际上就是最普通的逗号，连接若干个表）：
交叉联接返回左表中的所有行，左表中的每一行与右表中的所有行组合。交叉联接也称作笛卡尔积。
FROM 子句中的表或视图可通过内联接或完整外部联接按任意顺序指定；但是，用左或右向外联接指定表或视图时，表或视图的顺序很重要。
交叉连接（CROSS JOIN）：有两种，显式的和隐式的，不带ON子句，返回的是两表的乘积，也叫笛卡尔积。
例如：下面的语句1和语句2的结果是相同的。
语句1：隐式的交叉连接，没有CROSS JOIN。

SELECT O.ID, O.ORDER_NUMBER, C.ID, C.NAME
FROM ORDERS O , CUSTOMERS C
WHERE O.ID=1;

语句2：显式的交叉连接，使用CROSS JOIN。

SELECT O.ID,O.ORDER_NUMBER,C.ID,
C.NAME
FROM ORDERS O CROSS JOIN CUSTOMERS C
WHERE O.ID=1;

4. 建表——见上面的同题

creat table table_name(
col_name1 类型 not null default xx,
col_name2 类型 not null default xx,
primary key(col_namex)
);

5. 你觉得的mysql中的难点

6. 查询优化

这块吧，mysql底层怎样查找，索引是怎么用的，就是把不懂的讲了下，和hive对比了下。现在想想应该是查询语句的灵活运用

7. sql语句，子查询

--子查询练习
/*  
    1、子查询（内查询）在主查询（外查询）之前一次执行完成
    2、子查询的结果被主查询使用
    3、在查询列表中使用子查询，只能是单行单列。见练习2
    4、除非进行TOP N 分析，否则不要在子查询中使用ORDER BY语句，
       因子查询效率较低，排序耗费资源。见练习3
*/

--练习1：查询所有工资大于CLARK的员工信息
--把子查询结果当做一个值使用
SELECT * FROM emp
WHERE sal > (SELECT sal FROM emp WHERE ename = 'CLARK')

--练习2：查询所有员工信息以及工资总和
--把子查询结果当做一个列使用
SELECT emp.*,(SELECT SUM(sal) FROM emp) 工资总和
FROM emp;

--练习3：查询工资最高TOP 5
--把子查询结果当做一个表使用
SELECT * FROM (SELECT * FROM emp ORDER BY sal DESC)
WHERE ROWNUM <= 5;

--练习4：查询员工表中第6到第12条数据
--利用子查询解决ROWNUM不能计算大于等于某个值的问题
SELECT * FROM (SELECT ROWNUM rnum,emp.* FROM emp)
WHERE rnum BETWEEN 6 AND 12;

      --推荐写法（提前过滤）
      SELECT * FROM (SELECT ROWNUM rnum, emp.* FROM emp WHERE ROWNUM<=12)
      WHERE rnum >=6;
      
--多行子查询
--练习1：查询所有不是部门经理的员工
--IN 的使用
SELECT * FROM emp e
WHERE e.empno NOT IN (SELECT manager_id FROM DEPT
WHERE manager_id IS NOT NULL)

--练习2：查询所有员工人数不少于3人的部门信息
--子查询中可以使用子句
SELECT * FROM dept
WHERE deptno IN (SELECT deptno FROM emp 
                 GROUP BY deptno
                 HAVING COUNT(*)>=3 )
                 
--相关子查询（内外交互式）
--练习：查询员工编号，姓名，部门编号，工资，本部门工资总和
SELECT empno,ename,deptno,sal,
                 (SELECT SUM(sal) FROM emp 
                  WHERE e.deptno = deptno) #部门工资总和
FROM emp e
ORDER BY deptno;

8.链表去重

9. 折半查找原理

10. join group by

11. sql选出a表中有的b表没有的

select * from a left join b on a.key = b.key;

12. union和union all有什么区别？

定义

union all是直接连接，取到得是所有值，记录可能有重复
union 是取唯一值，记录没有重复

效率

UNION和UNION ALL关键字都是将两个结果集合并为一个，但这两者从使用和效率上来说都有所不同。

1. 对重复结果的处理：UNION在进行表链接后会筛选掉重复的记录，Union All不会去除重复记录。
2. 从效率上说，UNION ALL 要比UNION快很多，所以，如果可以确认合并的两个结果集中不包含重复数据且不需要排序时的话，那么就使用UNION ALL。

简要回答

UNION去重
UNION ALL不去重
如果面试官再问，可能涉及排序

示例

在中美两国公司，可能有人重名，如果使用union all，是返回所有值；使用union，是返回不重复的值，把重名去掉。

13. SQL 通配符

定义

通配符	描述
%	替代一个或多个字符
_	仅替代一个字符
[charlist]	字符列中的任何单一字符
[^charlist] 或者 [!charlist]	不在字符列中的任何单一字符

例子

现在，我们希望从上面的 “Persons” 表中选取居住在以 “Ne” 开始的城市里的人：

SELECT * FROM Persons
WHERE City LIKE 'Ne%'

我们希望从 “Persons” 表中选取居住在包含 “lond” 的城市里的人：

SELECT * FROM Persons
WHERE City LIKE '%lond%'

我们希望从上面的 “Persons” 表中选取名字的第一个字符之后是 “eorge” 的人：

SELECT * FROM Persons
WHERE FirstName LIKE '_eorge'

我们希望从 “Persons” 表中选取的这条记录的姓氏以 “C” 开头，然后是一个任意字符，然后是 “r”，然后是任意字符，然后是 “er”：

SELECT * FROM Persons
WHERE LastName LIKE 'C_r_er'

使用 [charlist] 通配符
现在，我们希望从上面的 “Persons” 表中选取居住的城市以 “A” 或 “L” 或 “N” 开头的人：

SELECT * FROM Persons
WHERE City LIKE '[ALN]%'

我们希望从上面的 “Persons” 表中选取居住的城市不以 “A” 或 “L” 或 “N” 开头的人：

SELECT * FROM Persons
WHERE City LIKE '[!ALN]%'

14. IN操作符

我们希望从上表中选取姓氏为 Adams 和 Carter 的人：

SELECT * FROM Persons
WHERE LastName IN ('Adams','Carter')

15. between操作符

如需以字母顺序显示介于 “Adams”（包括）和 “Carter”（不包括）之间的人，请使用下面的 SQL：

SELECT * FROM Persons
WHERE LastName
BETWEEN 'Adams' AND 'Carter'

重要事项：
不同的数据库对 BETWEEN…AND 操作符的处理方式是有差异的。某些数据库会列出介于 “Adams” 和 “Carter” 之间的人，但不包括 “Adams” 和 “Carter” ；某些数据库会列出介于 “Adams” 和 “Carter” 之间并包括 “Adams” 和 “Carter” 的人；而另一些数据库会列出介于 “Adams” 和 “Carter” 之间的人，包括 “Adams” ，但不包括 “Carter” 。
mysql: 前后都包含在集合里

如需使用上面的例子显示范围之外的人，请使用 NOT 操作符：

SELECT * FROM Persons
WHERE LastName
NOT BETWEEN 'Adams' AND 'Carter'

16. SELECT INTO 语句

SELECT INTO 语句从一个表中选取数据，然后把数据插入另一个表中。
SELECT INTO 语句常用于创建表的备份复件或者用于对记录进行存档。

定义

可以把所有的列插入新表：

SELECT *
INTO new_table_name [IN externaldatabase] 
FROM old_tablename

或者只把希望的列插入新表：

SELECT column_name(s)
INTO new_table_name [IN externaldatabase] 
FROM old_tablename

示例

（1）下面的例子会制作 “Persons” 表的备份复件：

SELECT *
INTO Persons_backup
FROM Persons

IN 子句可用于向另一个数据库中拷贝表：

SELECT *
INTO Persons IN 'Backup.mdb'
FROM Persons

（2）带有where语句
下面的例子通过从 “Persons” 表中提取居住在 “Beijing” 的人的信息，创建了一个带有两个列的名为 “Persons_backup” 的表：

SELECT LastName,Firstname
INTO Persons_backup
FROM Persons
WHERE City='Beijing'

（3）被连接的表
从一个以上的表中选取数据也是可以做到的。

下面的例子会创建一个名为 “Persons_Order_Backup” 的新表，其中包含了从 Persons 和 Orders 两个表中取得的信息：

SELECT Persons.LastName,Orders.OrderNo
INTO Persons_Order_Backup
FROM Persons
INNER JOIN Orders
ON Persons.Id_P=Orders.Id_P

17. UNIQUE 约束

定义

UNIQUE 约束唯一标识数据库表中的每条记录。

UNIQUE 和 PRIMARY KEY 约束均为列或列集合提供了唯一性的保证。

PRIMARY KEY 拥有自动定义的 UNIQUE 约束。

请注意，每个表可以有多个 UNIQUE 约束，但是每个表只能有一个 PRIMARY KEY 约束。

例子

（1）创建table时
SQL 在 “Persons” 表创建时在 “Id_P” 列创建 UNIQUE 约束：
MySQL:

CREATE TABLE Persons
(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
UNIQUE (Id_P)
)

多个变量进行限制。

CREATE TABLE Persons
(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT uc_PersonID UNIQUE (Id_P,LastName)
)

（2）进行alter
当表已被创建时，如需在 “Id_P” 列创建 UNIQUE 约束，请使用下列 SQL：

ALTER TABLE Persons
ADD UNIQUE (Id_P)

如需命名 UNIQUE 约束，并定义多个列的 UNIQUE 约束，请使用下面的 SQL 语法：

ALTER TABLE Persons
ADD CONSTRAINT uc_PersonID UNIQUE (Id_P,LastName)

（3）撤销 UNIQUE 约束
如需撤销 UNIQUE 约束，请使用下面的 SQL：

ALTER TABLE Persons
DROP INDEX uc_PersonID

18. 主键限制 PRIMARY KEY

定义

PRIMARY KEY 约束唯一标识数据库表中的每条记录。

主键必须包含唯一的值。主键列不能包含 NULL 值。

每个表都应该有一个主键，并且每个表只能有一个主键。

但是可以为多个列进行主键限制。

示例

（1）创建table时

CREATE TABLE Persons
(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
PRIMARY KEY (Id_P)
);

对多个列进行主键限制

create table persons
(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
constraint pk_person_id primary key (Id_P, LastName)
);

（2）alter table
如果在表已存在的情况下为 “Id_P” 列创建 PRIMARY KEY 约束，请使用下面的 SQL：
MySQL / SQL Server / Oracle / MS Access:

ALTER TABLE Persons
ADD PRIMARY KEY (Id_P)

如果需要命名 PRIMARY KEY 约束，以及为多个列定义 PRIMARY KEY 约束，请使用下面的 SQL 语法：

ALTER TABLE Persons
ADD CONSTRAINT pk_PersonID PRIMARY KEY (Id_P,LastName)

注释：如果您使用 ALTER TABLE 语句添加主键，必须把主键列声明为不包含 NULL 值（在表首次创建时）。

（3）撤销 PRIMARY KEY 约束
如需撤销 PRIMARY KEY 约束，请使用下面的 SQL：

ALTER TABLE Persons
DROP PRIMARY KEY

19. 外键约束 foreign key

20. check约束（判断条件）

定义

CHECK 约束用于限制列中的值的范围。

如果对单个列定义 CHECK 约束，那么该列只允许特定的值。

如果对一个表定义 CHECK 约束，那么此约束会在特定的列中对值进行限制。

示例

下面的 SQL 在 “Persons” 表创建时为 “Id_P” 列创建 CHECK 约束。CHECK 约束规定 “Id_P” 列必须只包含大于 0 的整数。

create table persons(
Id_P int not null,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
check (Id_P > 0)
);

--或者多个列，只记录北京的人
constraint check_person (Id_P > 0 and City = 'Beijing') 

--alter table
alter table persons
add check (Id_P > 0);
--alter table：对多个列进行限制
alter table persons
add constraint check_person check (Id_P > 0 and City = 'Beijing') ;

--撤销
drop check check_person

21. DEFAULT 约束

定义

DEFAULT 约束用于向列中插入默认值。

如果没有规定其他的值，那么会将默认值添加到所有的新记录。

示例

下面的 SQL 在 “Persons” 表创建时为 “City” 列创建 DEFAULT 约束：

CREATE TABLE Persons
(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255) DEFAULT 'Sandnes'
);

通过使用类似 GETDATE() 这样的函数，DEFAULT 约束也可以用于插入系统值：

CREATE TABLE Orders
(
Id_O int NOT NULL,
OrderNo int NOT NULL,
Id_P int,
OrderDate date DEFAULT GETDATE()
);

22. CREATE INDEX创建索引

在不读取整个表的情况下，索引使数据库应用程序可以更快地查找数据。

可以在表中创建索引，以便更加快速高效地查询数据。

用户无法看到索引，它们只能被用来加速搜索/查询。

注释：**更新一个包含索引的表需要比更新一个没有索引的表更多的时间，这是由于索引本身也需要更新。**因此，理想的做法是仅仅在常常被搜索的列（以及表）上面创建索引。

示例

（1）创建table时

--在表上创建一个简单的索引。允许使用重复的值：
CREATE INDEX index_name
ON table_name (column_name)

--在表上创建一个唯一的索引。唯一的索引意味着两个行不能拥有相同的索引值。
CREATE UNIQUE INDEX index_name
ON table_name (column_name)

（2）示例-创建index

--本例会创建一个简单的索引，名为 "PersonIndex"，在 Person 表的 LastName 列：
CREATE INDEX PersonIndex
ON Person (LastName) 

--如果希望以降序索引某个列中的值，您可以在列名称之后添加保留字 DESC：
CREATE INDEX PersonIndex
ON Person (LastName DESC) 

--假如您希望索引不止一个列，您可以在括号中列出这些列的名称，用逗号隔开：
CREATE INDEX PersonIndex
ON Person (LastName, FirstName)

23. increment()

24. View()

25. date()

(1) now()

(2) timestamp()

(3) date_add(var, interval [num] type) date_sub()

(4) format()

(5) date_diff()

(6) format()

26. group by有多个参数时

比如有两个变量在group by里，则是两个变量的笛卡尔积。
比如学生成绩表，如果算总分，按照student id来group的话，算出来是每个人的总分。如果是包括了课程标号来group by，则返回的实际上还是每个人的每一门课的成绩。

27. 混合使用的顺序

1. select top
2. from table
3. join table_B on ...
4. join table_C on...
5. where
6. group by var1
7. having ...
8. order by ... ;

28. having和where的异同

MySql中
相同点

1. 都是对数据进行过滤，只保留符合条件的有效数据

不同点

1. where是不能用字段的别名，having可以
2. where是在having的前面
3. （由于在前面，所以）where对原始的记录进行过滤，having是对分组以后的结果进行过滤。

10000. 一些细节

[not] in () 是孤立值

select student from table where classnum in (1,4);
# 选择来自1班、4班的学生