SQL面试完整指南
实体关系模型（ER）
创建数据库
列表数据库
使用数据库
表格的显示结构
SQL的子语言
聚合函数
日期函数
加入
PL/SQL（过程语言/结构化查询语言）
数据类型
由 Mux 赞助的 DEV 全球展示挑战赛：展示你的项目！
结构化查询语言（SQL）是一种标准数据库语言，用于创建、维护、销毁、更新和检索关系数据库（如 MySQL、Oracle、SQL Server、PostgreSQL 等）中的数据。
实体关系模型（ER）
它是一个用于描述数据库中数据结构的概念框架。它的设计目的是以更抽象的方式表示现实世界的实体及其之间的关系。这类似于面向对象编程之于编程语言。
实体：这些是现实世界中具有独立存在的对象或“事物”，例如顾客、产品或订单。
关系：关系定义了实体之间的相互联系。例如，“客户”实体可能与“订单”实体存在某种关系。
命令：
创建数据库
create database <database_name>;
列表数据库
show databases;
使用数据库
use <database_name>
表格的显示结构
DESCRIBE table_name;
SQL的子语言
数据查询语言（DQL）：
用于对数据执行查询的语言。此命令用于从数据库中检索数据。
命令：
1）选择：
select * from table_name;
select column1,column2 from table_name;
select * from table_name where column1 = "value";
数据定义语言（DDL）：
用于定义数据库模式的语言。此命令用于创建、修改和删除数据库，但不能删除数据。
命令
1）创建：
create table table_name(
column_name data_type(size) constraint,
column_name data_type(size) constraint
column_name data_type(size) constraint
);
2) 删除：
此命令将彻底删除表/数据库。
drop table table_name;
drop database database_name;
3) 截断：
此命令仅删除数据。
truncate table table_name;
4) 修改：
此命令可以添加、删除或更新表中的列。
添加
alter table table_name
add column_name datatype;
调整
alter table table_name
modify column column_name datatype;
--ALTER TABLE employees
--MODIFY COLUMN salary DECIMAL(10,2);
降低
alter table table_name
drop column_name datatype;
数据操作语言（DML）：
用于操作数据库中数据的语言。
1) 插入：
此命令仅用于插入新值。
insert into table_name
values (val1,val2,val3,val4); //4 columns
2）更新：
update table_name set col1=val1, col2=val2 where
col3 = val3;
3）删除：
delete from table_name where col1=val1;
数据控制语言（DCL）：
授权：允许指定用户执行指定任务。
撤销：取消先前授予或拒绝的权限。
事务控制语言（TCL）：
它用于管理数据库中的事务。它管理由 DML 命令所做的更改。
1) 提交：
用于将当前事务期间所做的所有更改保存到数据库。
BEGIN TRANSACTION;
UPDATE employees
SET salary = salary * 1.1
WHERE department = 'Sales';
COMMIT;
2) 回滚：
用于撤销当前事务期间所做的所有更改。
BEGIN TRANSACTION;
UPDATE employees
SET salary = salary * 1.1
WHERE department = 'Sales';
ROLLBACK;
3）存档点
begin transaction;
update customers
set first_name= 'one'
WHERE customer_id=4;
SAVEPOINT one;
update customers
set first_name= 'two'
WHERE customer_id=4;
ROLLBACK TO SAVEPOINT one;
COMMIT;
拥有：
此命令用于根据聚合函数筛选结果。我们不能在 WHERE 语句中使用聚合函数，因此可以在此命令中使用。
注意：当我们需要使用自定义列进行比较时，可以使用此命令；而 WHERE 命令用于使用现有列进行比较。
select Department, sum(Salary) as Salary
from employee
group by department
having sum(Salary) >= 50000;
在
当用户要求排除两个或多个特定项目时，可以使用此命令。
select * from table_name
where colname not in ('Germany', 'France', 'UK');
清楚的：
此命令用于根据所选字段仅检索唯一数据。
Select distinct field from table;
SELECT COUNT(DISTINCT salesman_id)
FROM orders;
相关查询
这是一个子查询（嵌套在另一个查询中的查询），它引用外部查询中的列。
SELECT EmployeeName, Salary
FROM Employees e1
WHERE Salary > (
SELECT AVG(Salary)
FROM Employees e2
WHERE e1.DepartmentID = e2.DepartmentID
);
正常化
规范化是一种数据库设计技术，用于组织表，以减少冗余并提高数据完整性。规范化的主要目标是将大型表拆分成更小、更易于管理的部分，同时保持数据之间的关系。
第一范式（1NF）：
列中的所有值都是原子值（不可分割的）。
每一列只包含一种类型的数据。
EmployeeID | EmployeeName | Department | PhoneNumbers
----------------------------------------------------
1 | Alice | HR | 123456, 789012
2 | Bob | IT | 345678
第一范式之后：
EmployeeID | EmployeeName | Department | PhoneNumber
----------------------------------------------------
1 | Alice | HR | 123456
1 | Alice | HR | 789012
2 | Bob | IT | 345678
第二范式 (2NF)
它符合第一范式 (1NF)。
所有非键属性都完全函数依赖于主键（没有部分依赖）。
EmployeeID | EmployeeName | DepartmentID | DepartmentName
---------------------------------------------------------
1 | Alice | 1 | HR
2 | Bob | 2 | IT
第二范式之后：
EmployeeID | EmployeeName | DepartmentID
---------------------------------------
1 | Alice | 1
2 | Bob | 2
DepartmentID | DepartmentName
------------------------------
1 | HR
2 | IT
第三范式（3NF）
它符合第二范式（2NF）。
所有属性都仅函数依赖于主键（没有传递依赖）。
EmployeeID | EmployeeN | DepartmentID | Department | DepartmentLocation
--------------------------------------------------------------------------
1 | Alice | 1 | HR | New York
2 | Bob | 2 | IT | Los Angeles
第三范式之后：
EmployeeID | EmployeeN | DepartmentID
----------------------------------------
1 | Alice | 1
2 | Bob | 2
DepartmentID | DepartmentName | DepartmentLocation
-----------------------------------------------
1 | HR | New York
2 | IT | Los Angeles
联盟：
此命令用于合并两个或多个 SELECT 语句的结果。
Select *
from table_name
WHERE (subject = 'Physics' AND year = 1970)
UNION
(SELECT * FROM nobel_win WHERE (subject = 'Economics' AND year = 1971));
限制：
此命令用于限制从查询中检索的数据量。
select Department, sum(Salary) as Salary
from employee
limit 2;
抵消：
此命令用于在返回结果之前跳过指定行数。
select Department, sum(Salary) as Salary
from employee
limit 2 offset 2;
排序依据：
此命令用于按字段升序或降序对数据进行排序。
数据：
create table employees (
id int primary key,
first_name varchar(50),
last_name varchar(50),
salary decimal(10, 2),
department varchar(50)
);
insert into employees (first_name, last_name, salary, department)
values
('John', 'Doe', 50000.00, 'Sales'),
('Jane', 'Smith', 60000.00, 'Marketing'),
('Jim', 'Brown', 60000.00, 'Sales'),
('Alice', 'Johnson', 70000.00, 'Marketing');
select * from employees order by department;
select * from employees order by salary desc
无效的
此命令用于检查值是否为空。
select * from tablename
where colname IS NULL;
按分组
此命令用于通过函数将相似的数据分组。
select department, avg(salary) AS avg_salary
from employees
group by department;
喜欢：
此命令用于在列中搜索特定模式。
SELECT *
FROM employees
WHERE first_name LIKE 'a%';
SELECT *
FROM salesman
WHERE name BETWEEN 'A' AND 'L';
外卡：
与 LIKE 运算符一起使用的字符，用于在字符串搜索中执行模式匹配。
% - 百分号
_ - 下划线
如何打印通配符？
SELECT 'It\'s a beautiful day';
SELECT * FROM table_name WHERE column_name LIKE '%50!%%' ESCAPE '!';
案件
SQL 中的 CASE 语句用于向查询添加条件逻辑。它允许您根据不同的条件返回不同的值。
SELECT first_name, last_name, salary,
CASE salary
WHEN 50000 THEN 'Low'
WHEN 60000 THEN 'Medium'
WHEN 70000 THEN 'High'
ELSE 'Unknown'
END AS salary_category
FROM employees;
显示文本
1）打印一些东西
Select "message";
select ' For', ord_date, ',there are', COUNT(ord_no)
group by colname;
2）在每一列打印数字
Select 1,2,3;
3）打印一些计算结果
Select 6x2-1;
4) 打印通配符
select colname1,'%',colname2
from tablename;
5）连接两个列名
select first_name || ' ' || last_name AS colname
from employees
6) 使用第 n 个字段
select *
from orders
group by colname
order by 2 desc;
约束条件
1) 非空：
此约束用于告诉字段，该字段的列中不能有空值。
create table employees(
id int(6) not null
);
2) 唯一约束：
此约束用于告知字段不能有重复值。它可以接受 NULL 值，并且每个表允许有多个唯一约束。
create table employees (
id int primary key,
first_name varchar(50) unique
);
3) 主键：
此约束用于指定表中唯一标识字段。它不能接受 NULL 值，并且每个表只能有一个主键。
create table employees (
id int primary key
);
4) 外键：
此约束用于引用另一个表中的唯一行。
create table employees (
id int primary key
foreign key (id) references owner(id)
);
5) 检查：
此约束用于检查要存储的数据是否满足特定条件。
create table employees (
id int primary key,
age int check (age >= 18)
);
6) 默认值：
此约束用于为字段提供默认值。
create table employees (
id int primary key,
age int default 28
);
聚合函数
1）数量：
select count(*) as members from employees;
2）总和：
select sum(salary) as total_amount
FROM employees;
3）平均值：
select avg(salary) as average_amount
FROM employees;
4）最大值：
select max(salary) as highest_amount
FROM employees;
5）最低要求：
select min(salary) as lowest_amount
FROM employees;
6）回合：
select round(123.4567, -2) as rounded_value;
日期函数
1) datediff
select a.id from weather a join weather b on datediff(a.recordDate,b.recordDate)=1 where a.temperature > b.temperature;
2) date_add
select date_add("2017-06-15", interval 10 day);
SECOND
MINUTE
HOUR
DAY
WEEK
MONTH
QUARTER
YEAR
3) date_sub
SELECT DATE_SUB("2017-06-15", INTERVAL 10 DAY);
加入
内连接
此函数用于根据一个公共列合并两个表。
它仅返回两个表中匹配的行。
数据
create table employees(
employee_id int(2) primary key,
first_name varchar(30),
last_name varchar(30),
department_id int(2)
);
create table department(
department_id int(2) primary key,
department_name varchar(30)
);
insert into employees values (1,"John","Dow",10);
insert into employees values (2,"Jane","Smith",20);
insert into employees values (3,"Jim","Brown",10);
insert into employees values (4,"Alice","Johnson",30);
insert into department values (10,"Sales");
insert into department values (20,"Marketing");
insert into department values (30,"IT");
select e.employee_id,e.first_name,e.last_name,d.department_name
from employees e
inner join department d
on e.department_id=d.department_id;
左连接
这种类型的连接会返回左表中的所有行以及右表中与之匹配的行。注意：如果右表中没有匹配的行，则返回 null。
select e.employee_id, e.first_name, e.last_name, d.department_name
from employees e
left join departments d
on e.department_id = d.department_id;
右接
这种类型的连接会返回右表中的所有行以及左表中与之匹配的行。注意：如果左表中没有匹配的行，则返回 null。
SELECT e.employee_id, e.first_name, e.last_name, d.department_name
FROM employees e
RIGHT JOIN departments d
ON e.department_id = d.department_id;
自加入
这种类型的连接用于将自身合并，尤其适用于创建相同数据的新列。
SELECT e.employee_id AS employee_id,
e.first_name AS employee_first_name,
e.last_name AS employee_last_name,
m.first_name AS manager_first_name,
m.last_name AS manager_last_name
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.employee_id;
全连接/全外连接
这种类型的连接用于合并左连接和右连接的结果。
SELECT e.employee_id, e.first_name, e.last_name, d.department_name
FROM employees e
FULL JOIN departments d
ON e.department_id = d.department_id;
十字连接
这种类型的连接用于生成两个表的笛卡尔积。
SELECT e.name, d.department_name
FROM Employees e
CROSS JOIN Departments d;
嵌套查询
嵌套查询（也称为子查询）是指位于另一个 SQL 查询内部的查询。嵌套查询会先执行，其结果会被外部查询使用。
子查询可以用于 SQL 语句的各个部分，包括 SELECT 子句、FROM 子句、WHERE 子句和 HAVING 子句。
1) SELECT 子句中的嵌套查询：
SELECT e.first_name, e.last_name,
(SELECT d.department_name
FROM departments d
WHERE d.id = e.department_id) AS department_name
FROM employees e;
2) WHERE 子句中的嵌套查询：
SELECT first_name, last_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);
SELECT pro_name, pro_price
FROM item_mast
WHERE pro_price = (SELECT MIN(pro_price) FROM item_mast);
3) FROM 子句中的嵌套查询：
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id;
4) 使用 EXISTS 的嵌套查询：
SELECT customer_name
FROM customers c
WHERE EXISTS (
SELECT 1
FROM orders o
WHERE o.customer_id = c.customer_id
);
存在
此命令用于测试特定记录是否存在。注意：使用 EXISTS 查询时，子查询返回的实际数据无关紧要。
SELECT customer_name
FROM customers c
WHERE EXISTS (
SELECT 1
FROM orders o
WHERE o.customer_id = c.customer_id
);
SELECT customer_name
FROM customers c
WHERE NOT EXISTS (
SELECT 1
FROM orders o
WHERE o.customer_id = c.customer_id
);
合并
SQL 中的 COALESCE 函数用于返回其参数中的第一个非空表达式。它在处理 NULL 值以及在处理可能缺失或未定义的数据时提供默认值方面特别有用。
CREATE TABLE employees (
first_name VARCHAR(50),
middle_name VARCHAR(50),
last_name VARCHAR(50)
);
INSERT INTO employees (first_name, middle_name, last_name) VALUES
('John', NULL, 'Doe'),
('Jane', 'Marie', 'Smith'),
('Emily', NULL, 'Johnson');
SELECT
first_name,
COALESCE(middle_name, 'No Middle Name') AS middle_name,
last_name
FROM
employees;
PL/SQL（过程语言/结构化查询语言）
它是Oracle对SQL的过程式扩展。如果执行多个SELECT语句，网络流量会迅速显著增加。例如，四个SELECT语句会导致八次网络请求。如果这些语句包含在PL/SQL块中，它们会作为一个整体发送到服务器。
积木
它们是执行和组织的基本单位。
1）命名块
命名块用于创建子程序。这些子程序可以是过程、函数或包。子程序可以存储在数据库中，并在以后通过其名称引用。
前任。
CREATE OR REPLACE PROCEDURE procedure_name (param1 IN datatype, param2 OUT datatype) AS
BEGIN
-- Executable statements
END procedure_name;
2) 匿名
数据块：这类数据块没有名称。因此，它们无法存储在数据库中，也无法在以后被引用。
DECLARE
-- Declarations (optional)
BEGIN
-- Executable statements
EXCEPTION
-- Exception handling (optional)
END;
声明部分
包含变量、常量、游标等标识符
。例如：
declare
v_first_name varchar2(35) ;
v_last_name varchar2(35) ;
v_counter number := 0 ;
v_lname students.lname%TYPE; // takes field datatype from column
行类型
DECLARE
v_student students%rowtype;
BEGIN
select * into v_student
from students
where sid='123456';
DBMS_OUTPUT.PUT_LINE(v_student.lname);
DBMS_OUTPUT.PUT_LINE(v_student.major);
DBMS_OUTPUT.PUT_LINE(v_student.gpa);
END;
执行：
它包含可执行语句，允许您操作变量。
declare
v_regno number;
v_variable number:=0;
begin
select regno into v_regno from student where regno=1;
dbms_output.put_line(v_regno || ' '|| v_variable);
end
输入文本
DECLARE
v_inv_value number(8,2);
v_price number(8,2);
v_quantity number(8,0) := 400;
BEGIN
v_price := :p_price;
v_inv_value := v_price * v_quantity;
dbms_output.put_line(v_inv_value);
END;
如果-否则循环
IF rating > 7 THEN
v_message := 'You are great';
ELSIF rating >= 5 THEN
v_message := 'Not bad';
ELSE
v_message := 'Pretty bad';
END IF;
循环
简单循环
declare
begin
for i in 1..5 loop
dbms_output.put_line('Value of i: ' || i);
end loop;
end;
while 循环
declare
counter number := 1;
begin
while counter <= 5 LOOP
dbms_output.put_line('Value of counter: ' || counter);
counter := counter + 1;
end loop;
end;
循环退出
declare
counter number := 1;
begin
loop
exit when counter > 5;
dbms_output.put_line('Value of counter: ' || counter);
counter := counter + 1;
end loop;
end;
程序
一系列接受和/或返回
零变量的语句。
--creating a procedure
create or replace procedure proc (var in number) as
begin
dbms_output.put_line(var);
end
--calling of procedure
begin
proc(3);
end
功能
一系列接受零个或多个变量并返回一个值的语句。
create or replace function func(var in number)
return number
is res number;
begin
select regno into res from student where regno=var;
return res;
end
--function calling
declare
var number;
begin
var :=func(1);
dbms_output.put_line(var);
end
所有类型的 I/O
p_name IN VARCHAR2
p_lname OUT VARCHAR2
p_salary IN OUT NUMBER
指数
索引是一种数据结构，它通过让 MySQL 更高效地查找和检索数据来提升查询性能。通常情况下，MySQL 会扫描表中的每一行。但现在有了索引，速度比扫描整个表要快得多。
create index student_index on student(name);
create index second_index on student(name,year);
select * from student where name = 'Madhav';
顺序
它是一个数据库对象，用于生成一系列唯一的数字。
create sequence testing
start with 1
increment by 1;
insert into student values(student_id.nextval, 'name','age');
代名词
它是一个数据库对象，为另一个数据库对象（例如表、视图、序列或存储过程）提供别名。
create synonym std for student;
select * from std;
看法
视图是一种虚拟表，它提供了一种以自定义格式呈现来自一个或多个表的数据的方法。与真实表不同，视图本身并不存储数据。相反，它是一个存储的查询，可以像查询表一样进行查询。
create view view_name as
select * from school;
select * from [view_name];
drop view view_name;
触发器
DML（数据操作语言）触发器会在对表或视图执行 INSERT、UPDATE 或 DELETE 操作时触发。
前置触发器：
在执行 DML 操作之前执行。
后置触发器：
在执行 DML 操作之后执行。
替代触发器：
代替 DML 操作执行，通常用于视图。
注意：: new表示刚刚插入到订单表中的新行的 cid。
create or replace trigger t_name
after update on student
for each row
begin
dbms_output.put_line(:NEW.regno);
end
--after updation
update student
set name='name'
where regno=1;
窗口函数
SELECT
id,name,gender,
ROW_NUMBER() OVER(
PARTITION BY name
order by gender
) AS row_number
FROM student;
SELECT
employee_id,
department_id,
salary,
RANK() OVER(
PARTITION BY department_id
ORDER BY salary DESC
) AS salary_rank
FROM employees;
酸性特性：
原子性：
事务中的所有操作都被视为一个整体。
例如，考虑一笔银行转账，资金从一个账户转移到另一个账户。原子性确保如果从一个账户扣款成功，则记入另一个账户的款项也会成功。如果其中任何一个操作失败，则整个事务将被回滚以保持一致性。
一致性：
一致性确保数据库在事务处理前后保持状态一致。
例如，如果一笔转账事务减少了一个账户的余额，则收款账户的余额也应该增加。这样可以维护系统的整体余额平衡。
隔离性：
隔离性确保事务的并发执行最终得到的系统状态与事务串行执行（即一个接一个地执行）时所达到的状态相同。
例如，考虑两个事务 T1 和 T2。如果 T1 将资金从账户 A 转账到账户 B，而 T2 检查账户 A 的余额，隔离性确保 T2 看到的要么是转账前的余额（如果 T1 尚未提交），要么是转账后的余额（如果 T1 已提交），而不会看到中间状态。
持久性：
持久性保证事务一旦提交，其效果将永久存在，并能经受住系统故障的考验。即使系统崩溃或重启，事务所做的更改也不会丢失。
数据类型
1) 数值数据类型
int
decimal(p,q) - p 为大小，q 为精度
2) 字符串数据类型
char(值) - 最大长度 8000 且不可变
varchar(值) - 最大长度 8000
文本- 最大长度
3) 日期数据类型：
日期
时间