結構化查詢語言或 SQL 是一種標準資料庫語言，用於從 MySQL、Oracle、SQL Server、PostgreSQL 等關聯式資料庫中建立、維護、銷毀、更新和檢索資料。
實體關係模型（ER）
它是一個用來描述資料庫中資料結構的概念架構。它旨在以更抽象的方式表示現實世界的實體及其之間的關係。這類似於程式語言的物件導向程式設計。
實體：這些是現實世界中具有獨特存在的物件或“事物”，例如客戶、產品或訂單。
關係： 這些定義實體如何相互關聯。例如，「客戶」實體可能與「訂單」實體有關係
指令：
建立資料庫
create database <database_name>;
登入後複製
列出資料庫
使用資料庫
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）：
GRANT：允許指定使用者執行指定任務。
REVOKE：取消先前授予或拒絕的權限。
事務控制語言（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
登入後複製
1NF之後：
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
登入後複製
2NF之後：
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
登入後複製
3NF之後：
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;
登入後複製
Order By:
This command is used to sort the data based on the field in ascending or descending order.
Data:
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
登入後複製
Null
This command is used to test for empty values
select * from tablename
where colname IS NULL;
登入後複製
Group By
This command is used to arrange similar data into groups using a function.
select department, avg(salary) AS avg_salary
from employees
group by department;
登入後複製
Like:
This command is used to search a particular pattern in a column.
SELECT *
FROM employees
WHERE first_name LIKE 'a%';
登入後複製
SELECT *
FROM salesman
WHERE name BETWEEN 'A' AND 'L';
登入後複製
Wildcard:
Characters used with the LIKE operator to perform pattern matching in string searches.
% - Percent
_ - Underscore
How to print Wildcard characters?
SELECT 'It\'s a beautiful day';
登入後複製
SELECT * FROM table_name WHERE column_name LIKE '%50!%%' ESCAPE '!';
登入後複製
Case
The CASE statement in SQL is used to add conditional logic to queries. It allows you to return different values based on different conditions.
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;
登入後複製
Display Text
1) Print something
select ' For', ord_date, ',there are', COUNT(ord_no)
group by colname;
登入後複製
2) Print numbers in each column
3) Print some calculation
4) Print wildcard characters
select colname1,'%',colname2
from tablename;
登入後複製
5) Connect two colnames
select first_name || ' ' || last_name AS colname
from employees
登入後複製
6) Use the nth field
select *
from orders
group by colname
order by 2 desc;
登入後複製
Constraints
1) Not Null:
This constraint is used to tell the field that it cannot have null value in a column.
create table employees(
id int(6) not null
);
登入後複製
2) Unique:
This constraint is used to tell the field that it cannot have duplicate value. It can accept NULL values and multiple unique constraints are allowed per table.
create table employees (
id int primary key,
first_name varchar(50) unique
);
登入後複製
3) Primary Key:
This constraint is used to tell the field that uniquely identifies in the table. It cannot accept NULL values and it can have only one primary key per table.
create table employees (
id int primary key
);
登入後複製
4) Foreign Key:
This constraint is used to refer the unique row of another table.
create table employees (
id int primary key
foreign key (id) references owner(id)
);
登入後複製
5) Check:
This constraint is used to check a particular condition for data to be stored.
create table employees (
id int primary key,
age int check (age >= 18)
);
登入後複製
6) Default:
This constraint is used to provide default value for a field.
create table employees (
id int primary key,
age int default 28
);
登入後複製
Aggregate functions
1)Count:
select count(*) as members from employees;
登入後複製
2)Sum:
select sum(salary) as total_amount
FROM employees;
登入後複製
3)Average:
select avg(salary) as average_amount
FROM employees;
登入後複製
4)Maximum:
select max(salary) as highest_amount
FROM employees;
登入後複製
5)Minimum:
select min(salary) as lowest_amount
FROM employees;
登入後複製
6)Round:
select round(123.4567, -2) as rounded_value;
登入後複製
Date Functions
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);
登入後複製
Joins
Inner Join
This is used to combine two tables based on one common column.
It returns only the rows where there is a match between both tables.
Data
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;
登入後複製
Left Join
This type of join returns all rows from the left table along with the matching rows from the right table. Note: If there are no matching rows in the right side, it return 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;
登入後複製
Right Join
This type of join returns all rows from the right table along with the matching rows from the left table. Note: If there are no matching rows in the left side, it returns 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;
登入後複製
Self Join
This type of join is used to combine with itself especially for creation of new column of same data.
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;
登入後複製
Full Join/ Full outer join
This type of join is used to combine the result of both left and right join.
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;
登入後複製
Cross Join
This type of join is used to generate a Cartesian product of two tables.
SELECT e.name, d.department_name
FROM Employees e
CROSS JOIN Departments d;
登入後複製
Nested Query
A nested query, also known as a subquery, is a query within another SQL query. The nested query is executed first, and its result is used by the outer query.
Subqueries can be used in various parts of a SQL statement, including the SELECT clause, FROM clause, WHERE clause, and HAVING clause.
1) Nested Query in SELECT Clause:
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) Nested Query in WHERE Clause:
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) Nested Query in FROM Clause:
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id;
登入後複製
4) Nested Query with EXISTS:
SELECT customer_name
FROM customers c
WHERE EXISTS (
SELECT 1
FROM orders o
WHERE o.customer_id = c.customer_id
);
登入後複製
登入後複製
Exists
This command is used to test the existence of a particular record. Note: When using EXISTS query, actual data returned by subquery does not matter.
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
);
登入後複製
COALESCE
The COALESCE function in SQL is used to return the first non-null expression among its arguments. It is particularly useful for handling NULL values and providing default values when dealing with potentially missing or undefined data.
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(Procedural Language/Structured Query Language)
It is Oracle's procedural extension to SQL. If multiple SELECT statements are issued, the network traffic increases significantly very fast. For example, four SELECT statements cause eight network trips. If these statements are part of the PL/SQL block, they are sent to the server as a single unit.
Blocks
They are the fundamental units of execution and organization.
1) Named block
Named blocks are used when creating subroutines. These subroutines are procedures, functions, and packages. The subroutines can be stored in the database and referenced by their names later on.
Ex.
CREATE OR REPLACE PROCEDURE procedure_name (param1 IN datatype, param2 OUT datatype) AS
BEGIN
-- Executable statements
END procedure_name;
登入後複製
2) Anonymous
They are blocks do not have names. As a result, they cannot be stored in the database and referenced later.
DECLARE
-- Declarations (optional)
BEGIN
-- Executable statements
EXCEPTION
-- Exception handling (optional)
END;
登入後複製
Declaration
It contains identifiers such as variables, constants, cursors etc
Ex.
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
登入後複製
Rowtype
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;
登入後複製
Execution
It contains executable statements that allow you to manipulate the variables.
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
登入後複製
Input of text
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-else loop
IF rating > 7 THEN
v_message := 'You are great';
ELSIF rating >= 5 THEN
v_message := 'Not bad';
ELSE
v_message := 'Pretty bad';
END IF;
登入後複製
Loops
Simple Loop
declare
begin
for i in 1..5 loop
dbms_output.put_line('Value of i: ' || i);
end loop;
end;
登入後複製
While Loop
declare
counter number := 1;
begin
while counter <= 5 LOOP
dbms_output.put_line('Value of counter: ' || counter);
counter := counter + 1;
end loop;
end;
登入後複製
Loop with Exit
declare
counter number := 1;
begin
loop
exit when counter > 5;
dbms_output.put_line('Value of counter: ' || counter);
counter := counter + 1;
end loop;
end;
登入後複製
Procedure
A series of statements accepting and/or returning
zero variables.
--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
登入後複製
Function
A series of statements accepting zero or more variables that returns one value.
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
登入後複製
All types of I/O
p_name IN VARCHAR2
p_lname OUT VARCHAR2
p_salary IN OUT NUMBER
登入後複製
Triggers
DML (Data Manipulation Language) triggers are fired in response to INSERT, UPDATE, or DELETE operations on a table or view.
BEFORE Triggers:
Execute before the DML operation is performed.
AFTER Triggers:
Execute after the DML operation is performed.
INSTEAD OF Triggers:
Execute in place of the DML operation, typically used for views.
Note: :new represents the cid of the new row in the orders table that was just inserted.
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;
登入後複製
Window function
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;
登入後複製
ACID Properties:
Atomicity:
All operations within a transaction are treated as a single unit.
Ex. Consider a bank transfer where money is being transferred from one account to another. Atomicity ensures that if the debit from one account succeeds, the credit to the other account will also succeed. If either operation fails, the entire transaction is rolled back to maintain consistency.
一致：
一致性保證資料庫在交易前後保持一致的狀態。
例如如果一筆轉帳交易減少了一個帳戶的餘額，它也應該增加接收帳戶的餘額。這維持了系統的整體平衡。
隔離：
隔離性確保事務的並發執行會產生一種系統狀態，如果事務是串列執行的，即一個接一個地執行，則將獲得該系統狀態。
前任。考慮兩個事務 T1 和 T2。如果T1 將資金從帳戶A 轉移到帳戶B，並且T2 檢查帳戶A 的餘額，則隔離可確保T2 在轉帳之前（如果T1 尚未提交）或轉帳之後（如果T1 尚未提交）看到帳戶A 的餘額已提交），但不是中間狀態。
耐用性：
持久性保證事務一旦提交，其影響是永久性的並且不會受到系統故障的影響。即使系統崩潰或重啟，事務所所做的更改也不會遺失。
資料類型
1) 數字資料型別
int
decimal(p,q) - p 是大小，q 是精確度
2) 字串資料型別
char(value) - max(8000) && 不可變
varchar(值) - max(8000)
文字 - 最大尺寸
3) 日期資料型別
日期
時間
日期時間
以上是SQL面試完整指南的詳細內容。更多資訊請關注PHP中文網其他相關文章！
