一文帶你用 SQL 征服數學建模資料處理-有解無憂

一文帶你領略 SQL 的強大

1. SQL 簡介

? SQL (Structured Query Language:結構化查詢語言) 是用于用于管理關系資料庫管理系統（RDBMS）， SQL 的范圍包括資料插入、查詢、更新和洗掉，資料庫模式創建和修改，以及資料訪問控制，

2.優勢

? SQL 面向資料庫進行增刪查改，在我們沒有接觸資料庫的時候，如果要對資料進行處理，一般會用到 for 回圈進行遍歷，比如，我們有一個結構體陣列（下文稱為表），里面包含著名字，年齡和性別，如果我們要把性別為女的同學查找出來，我們需要使用 for 回圈一遍，訪問每一個結構體中的性別，進行甄別，這種方法對于幾百幾千條資料來說耗費時間看似沒有差別，但如果是幾十萬，幾百萬條資料，時間的耗費則越來越重，

? 再者，如果存在另外一個表，里面記錄著每一同學每次考試的成績，我們現在需要將兩個表進行合并，成為一條包含著名字，性別，年齡以及成績（假設名字唯一）的資料，這時我們就需要使用兩個 for 回圈來處理，并構造新的結構體來進行存盤，即便其他語言比如 Python 等不需要構造結構體，但兩層的 for 回圈帶來的時間耗費的提高也是不容小覷的，更何況會存在更多層 for 回圈的情形

? 筆者曾參加過 2020年的數模國賽，選擇的就是大資料問題，說實話，當時沒有用到過資料庫，用的雖然是 Python 但處理資料的時候，使用了許多 for 回圈，耗費了許多時間，只水了個省二，貼一下當時處理處理資料的一小部分代碼，可以看出，不僅資料量大而且處理邏輯復雜，還很容易出錯，比賽完之后就覺得如果使用資料庫來進行處理，定會有奇效的，果然，幾分鐘 for 才得到的資料 SQL 一兩秒秒了，所以筆者很推薦準備參加數學建模并且選擇大資料題目的同學，加強一下資料庫的學習以及練習，

# Dep_In_Profit 企業進貨稅價和
# Dep_In_Price  企業進貨價格和
# Dep_In_Rex  企業進貨稅收和
# Dep_In_Tic_T 企業進貨有效發票
# Dep_In_Tic_T_R 負數票據
# Dep_In_Tic_T_S 正數票據
# Dep_In_Tic_F 企業進貨無效發票
Dep_In_Profit_M = np.zeros(302)
Dep_In_Price_M = np.zeros(302)
Dep_In_Rex_M  = np.zeros(302)
Dep_In_Profit_N = np.zeros(302)
Dep_In_Price_N = np.zeros(302)
Dep_In_Rex_N  = np.zeros(302)
Dep_In_Tic_T = np.zeros(302)
Dep_In_Tic_F = np.zeros(302)
Dep_In_Tic_T_R = np.zeros(302)
Dep_In_Tic_T_S = np.zeros(302)
for i in range(0,395175):
    if In_items.iloc[i,9] == 1:
        Dep_In_Tic_T[In_items.iloc[i, 8]-124] = Dep_In_Tic_T[In_items.iloc[i, 8]-124] + 1
        if In_items.iloc[i, 6] < 0:
            Dep_In_Profit_N[In_items.iloc[i, 8]-124] = Dep_In_Profit_N[In_items.iloc[i, 8]-124] + In_items.iloc[i, 6]
            Dep_In_Price_N[In_items.iloc[i, 8]-124] = Dep_In_Price_N[In_items.iloc[i, 8]-124] + In_items.iloc[i, 4]
            Dep_In_Rex_N[In_items.iloc[i, 8]-124] = Dep_In_Rex_N[In_items.iloc[i, 8]-124] + In_items.iloc[i, 5]
            Dep_In_Tic_T_R[In_items.iloc[i, 8]-124] = Dep_In_Tic_T_R[In_items.iloc[i, 8]-124] + 1
        else:   
            Dep_In_Profit_M[In_items.iloc[i, 8]-124] = Dep_In_Profit_M[In_items.iloc[i, 8]-124] + In_items.iloc[i, 6]
            Dep_In_Price_M[In_items.iloc[i, 8]-124] = Dep_In_Price_M[In_items.iloc[i, 8]-124] + In_items.iloc[i, 4]
            Dep_In_Rex_M[In_items.iloc[i, 8]-124] = Dep_In_Rex_M[In_items.iloc[i, 8]-124] + In_items.iloc[i, 5]
            Dep_In_Tic_T_S[In_items.iloc[i, 8]-124] = Dep_In_Tic_T_S[In_items.iloc[i, 8]-124] + 1
    else:
        Dep_In_Tic_F[In_items.iloc[i, 8]-124] = Dep_In_Tic_F[In_items.iloc[i, 8]-124] + 1

3.用武之處

? 首先是數學建模以及大資料處理方面的應用，當然這方面的應用是 SQL 相較于一般處理方法的優勢所在，并且在處理多表級聯關系時，SQL 顯得更加簡潔，for 回圈或其他一般方式顯得更加復雜以及更容易出錯，

? 其次在應用開發時，無論是 web 應用還是移動端，資料庫肯定是需要的，用于前后端資料的聯系，雖然平時并不會設計很大的資料量，但熟悉 SQL 的基本增刪改查也是很有必要噠，

4.入門

1. 增

1.1 增加資料庫

create database learn; # learn 為資料庫的名稱 
use learn; # 使用此資料庫
set names utf8; # 設定編碼

1.2 增加表

CREATE TABLE Persons # 括號里包含著每一個鍵的名稱以及資料型別用逗號隔開
(
    PersonID  int,
    LastName  varchar(255),
    FirstName varchar(255),
    Address   varchar(255),
    City      varchar(255)
    # 可在定義之后加上一些約束，之后會提到
);

1.3 增加表中的資料

1.3.1 插入整條資料

insert into Persons # 插入整條資料的時候要與表中的鍵一一對應 
values (1, 'mary', 'Smith', '127.0.0.1', 'Beijing');
insert into Persons
values (3, 'mar', 'Smith', '127.0.4.1', 'Beijing');
insert into Persons
values (4, 'max', 'Smith', '127.0.6.1', 'Tianjin');
insert into Persons
values (5, 'mute', 'Rio', '127.0.6.1', 're');
insert into Persons
values (6, 'mate', 'Linda', '127.1.6.1', 'Tianjin');
insert into Persons
values (2, 'mario', 'Smith', '127.1.0.1', 'Nanjing');

1.3.2 插入指定鍵的資料

INSERT INTO Persons (PersonID,LastName,City) # 括號中的便是 表中的鍵 下面插入的 value 需要與之對應
VALUES ('mirry','Smith','Hangzhou');

2. 刪

2.1 洗掉資料庫

drop database learn; # 刪庫需謹慎

2.2 洗掉表

drop TABLE website; # 洗掉表
TRUNCATE TABLE table_name # 洗掉表的資料，表本身存在

2.3 洗掉索引

ALTER TABLE table_name DROP INDEX index_name; # 適用于MySQL 
DROP INDEX index_name; # 適用于 DB2/Oracle
DROP INDEX table_name.index_name; # 適用于 MS SQL Server

3. 改

3.1 表中增加列

ALTER TABLE table_name 				# 需要指定 表的名稱，列的名稱 以及 資料型別
ADD column_name datatype

alter table ts
add sex nvarchar(10);

3.2 表中洗掉列

ALTER TABLE table_name         		# 需要指定 表的名稱，列的名稱
DROP COLUMN column_name

alter table ts
drop column sex;

3.3 表中修改列的資料型別

# SQL Server / MS Access 適用
ALTER TABLE table_name         		# 需要指定 表的名稱，列的名稱，列要修改成的資料型別	
ALTER COLUMN column_name datatype

# My SQL / Oracle 適用
ALTER TABLE table_name				# 需要指定 表的名稱，列的名稱，列要修改成的資料型別	
MODIFY COLUMN column_name datatype

alter table ts
modify column sex nvarchar(100);

4. 查

4.1 查詢所有記錄

select * 				# * 便是指代所有鍵
from Persons;

4.2 查詢指定行的記錄

# 先指定資料庫
use learn;
select PersonID,City from Persons;
# 直接訪問
select PersonID,City from learn.Persons;

4.3 查詢資料庫

show databases;

4.4 查詢資料庫中表的名稱

show tables;

5. 主鍵，外鍵，UNIQUE 約束

? 主鍵是約束標識表中每一條記錄的，就相當于我們的身份證，不能為 NULL , 也不能重復，在插入資料的時候必須為之指定，每一個表都有且僅有一個主鍵，

create table ts(
    id int,
    name varchar(25),
    primary key (id) # 指定主鍵 
);

? 外鍵是一個表指向另一個表中的 UNIQUE KEY(唯一約束的鍵)，假設有每個班任課老師的表和每個教職工的表這兩張表，

班級任課老師表

任課老師教職工號（外鍵）	老師	班級
03	C	二班
01	A	一班
03	C	三班
02	B	四班

教職工表

老師	教職工號（主鍵）
C	03
B	02
A	01

? 班級任課老師表中的任課老師教職工號指向教職工表的教職工號，

? 因此，教職工號作為教職工的主鍵(PRIMARY KEY)，任課老師教職工號作為班級任課老師表的外鍵(FOREIGN KEY)，

# 適用于 MySQL
CREATE TABLE TS
(
    id int NOT NULL,
    name varchar(25),
    P_Id int,
    primary key (id), # 指定主鍵 
    FOREIGN KEY (P_Id) REFERENCES TES(P_Id)
)
# 適用于SQL Server / Oracle / MS Access
CREATE TABLE TS
(
	id int NOT NULL PRIMARY KEY,
    name varchar(25),
    P_Id int NOT NULL FOREIGN KEY REFERENCES TES(P_Id)
)

? UNIQUE 約束唯一標識資料庫表中的每條記錄，和主鍵不同，表中允許多個UNIQUE 約束出現，PRIMARY KEY 約束擁有自動定義的 UNIQUE 約束，這地方在處理資料的時候用到的時候沒有太多，就當了解一下，

? 約束的創建

create table ts(
    id int NOT NULL,
    name varchar(25),
    UNIQUE (id) # UNIQUE 約束
);
或者
create table ts(
    id int NOT NULL UNIQUE,# UNIQUE 約束
    name varchar(25)
);

? 約束的洗掉

# MySQL
ALTER TABLE ts
DROP INDEX id

# SQL Server / Oracle / MS Access
ALTER TABLE Persons
DROP CONSTRAINT id

5.進階

1. 運算子

1.1 WHERE（無法與聚合函式一起使用）

? 之前舉的一個查詢為女生的同學的例子，傳統方法為 for 回圈，這樣耗時耗力，使用 where 我們可以很簡單的秒了

# 基本語法
SELECT column_name,column_name   # 需要查詢的列
FROM table_name
WHERE column_name operator value;   # 這里為一些條件陳述句，接下來會提及

1.2 IN

? 當我們需要查詢某一鍵符合多個值的時候，這個便派上了用場

create table grade(
    name nchar(10),
    grade int
);
insert into grade values ('a', 100);
insert into grade values ('b', 100);
insert into grade values ('c', 90);
insert into grade values ('a', 100);
insert into grade values ('b', 95);
insert into grade values ('c', 93);

# 基本語法
SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1,value2,...);
# 查詢名字是a,b,c中間一人的資料
select * from grade
where name in ('a','b','c');  # 括號里的元素要與鍵的資料型別相同
# 查詢成績是 90 93 100 之間的同學資料
select * from grade
where grade in (90, 93 ,100);

1.3 BETWEEN

? 我們需要查詢某一鍵符合在某一范圍時，這個便派上了用場

# 基本語法
SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

select * from grade
where grade between 95 and 100;

1.4 LIKE

? LIKE 運算子用于在 WHERE 子句中搜索列中的指定模式，主要就是匹配模式的書寫，可能會涉及到正則運算式，下面給出菜鳥課程總結的通配符，對正則不熟悉的同學可參考我的另外一篇博文，

通配符	匹配物件
%	替代 0 個或多個字符
_	替代一個字符
[charlist]	字符列中的任何單一字符
[^charlist] 或 [!charlist]	不在字符列中的任何單一字符

? MySQL 中使用 REGEXP 或 NOT REGEXP 運算子 (或 RLIKE 和 NOT RLIKE) 來操作正則運算式

# 查詢以 c或d或e開頭的同學名字的資料
select * from grade
where name REGEXP '^[cde]';

# 查詢以 c到e字母開頭的同學名字的資料
select * from grade
where name REGEXP '^[c-e]';

# 查詢 不以 c到e字母開頭的同學名字的資料
select * from grade
where name REGEXP '^[^c-e]';

# 查詢一個字母的同學名字的資料
select * from grade
where name like '_';

2. SELECT DISTINCT

? 以上面班級任課老師表為例，如果我們想要任課老師的教職工號，我們可以用 select 來只對任課老師教職工號進行選擇，但查詢的結果會有重復，C 的教職工號出現兩次，我們可以使用 SELECT DISTINCT 進行查詢而不會出現重復的情況，一個重要的特點就是每一條查詢結果都各不相同，

# 查詢參加考試的同學
select distinct name
from grade;

# 查詢考試分數的種類
select distinct grade
from grade;

# 查詢學生及其分數的種類
select distinct grade,name
from grade;

3. JOIN

? join 用于將多個表聯系在一起，大致可分為四種join方法

join 型別	描述
INNER JOIN	如果表中有至少一個匹配，則回傳行
LEFT JOIN	即使右表中沒有匹配，也從左表回傳所有的行
RIGHT JOIN	即使左表中沒有匹配，也從右表回傳所有的行
FULL JOIN	只要其中一個表中存在匹配，則回傳行


create table TeaNo(
    name varchar(5) NOT NULL,
    Tno varchar(5) NOT NULL ,
    sex varchar(5) NOT NULL ,
    PRIMARY KEY (Tno)
);
create table Teach(
    Tno varchar(5) NOT NULL ,
    Teacher varchar(2) NOT NULL ,
    Class varchar(3) NOT NULL ,
    FOREIGN KEY (Tno) references TeaNo(Tno)

);

insert into TeaNo values ('C','03','男');
insert into TeaNo values ('B','02','男');
insert into TeaNo values ('A','01','女');
insert into TeaNo values ('D','04','女');

insert into Teach values ('03', 'C', '二班');
insert into Teach values ('01', 'A', '一班');
insert into Teach values ('03', 'C', '三班');
insert into Teach values ('03', 'B', '四班');

# inner join

select TeaNo.name,TeaNo.sex,Teach.Class
from TeaNo inner join Teach
on TeaNo.Tno = Teach.Tno;
# A,女,一班
# C,男,二班
# C,男,三班
# C,男,四班

# LEFT JOIN  B 和 D 老師都在右邊找不到對應的資訊但仍回傳左邊一行，空缺地方為NULL

select TeaNo.name,TeaNo.sex,Teach.Class
from TeaNo left outer join Teach
on TeaNo.Tno = Teach.Tno;
# A,女,一班
# B,男,
# C,男,二班
# C,男,三班
# C,男,四班
# D,女,

# Right JOIN
select TeaNo.name,TeaNo.sex,Teach.Class
from TeaNo right outer join Teach
on TeaNo.Tno = Teach.Tno;
# C,男,二班
# A,女,一班
# C,男,三班
# C,男,四班

# FULL JOIN 似乎語法并不支持

4. 別名

? 在進行查詢時，可以通過創建別名讓列名稱或者表名稱的可讀性更強，

# 列的別名
SELECT name AS TeacherName
FROM TeaNo;

# 表的別名 多用于多表查詢時
SELECT name
FROM TeaNo AS TeacherName;

5. SQL 函式

? 在資料處理上，正是由于這些可以直接使用的函式，讓資料處理更加簡便，下列是菜鳥課程上總結的函式

函式名	作用
FORMAT()	格式化某個欄位的顯示方式
LEN()	回傳某個文本欄位的長度
ROUND()	對某個數值欄位進行指定小數位數的四舍五入
UCASE()	將某個欄位轉換為大寫
LCASE()	將某個欄位轉換為小寫
AVG()	回傳平均值
COUNT()	回傳行數
NOW()	回傳當前的系統日期和時間
FIRST()	回傳第一個記錄的值
LAST()	回傳最后一個記錄的值
MAX()	回傳最大值
MID()	從某個文本欄位提取字符，MySql 中使用
SubString(欄位，1，end)	從某個文本欄位提取字符
MIN()	回傳最小值
SUM()	回傳總和

# FORMAT() 括號里的元素都是必須的
SELECT FORMAT(column_name,format) FROM table_name; 

# 得到系統時間
select date_format(NOW(),'%Y-%m-%d') As data;

6. HAVING

? 由于WHERE 關鍵字無法與聚合函式一起使用，因此引入Having，HAVING 子句可以讓我們篩選分組后的各組資料，

# 基本語法
SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value;

# aggregate_function(column_name) 為聚合函式，不能與 where 連用

7. Union 與 Union All

? 二者用于合并兩個或多個 SELECT 陳述句的結果，

? 注意事項：UNION 內部的每個 SELECT 陳述句必須擁有相同數量的列，列也必須擁有相似的資料型別，同時，每個 SELECT 陳述句中的列的順序必須相同，

? 二者不同之處：Union 用于取并集去重，Union All 用于取并集不去重，

create table country1(
    Name varchar(10),
    Eng varchar(10)
);
create table country2(
    Name varchar(10),
    Eng varchar(10),
    amount int
);
insert into country1 values ('中國', 'CN');
insert into country1 values ('美國', 'USA');
insert into country1 values ('英國', 'UK');
insert into country1 values ('日本', 'Japan');
insert into country2 values ('德國','German',100);
insert into country2 values ('澳大利亞','Australia',100);
insert into country2 values ('俄羅斯','Russia',100);
insert into country2 values ('德國','German',100);
# 取并集 去重
select Name,Eng
from country1
UNION
select Name,Eng
from country2;
# 取并集不去重
select Name,Eng
from country1
UNION ALL
select Name,Eng
from country2;

8.日期問題

? 日期處理問題是對資料進行提取，分類的重要環節，比如，在數學建模中，我們經常需要對一家公司的收入按月份或者年份進行提取，資料庫內置的函式就會有奇效，下列是菜鳥課程總結的一些函式，

函式名稱	函式用處
DATE(date)	提取 date 中的日期部分
DATE_ADD(date,INTERVAL expr type)	從日期加上指定的時間間隔，date 為合法的日期，expr 為指定的時間間隔
DATE_FORMAT()	用于以不同的格式顯示日期/時間資料，
DATE_SUB(date,INTERVAL expr type)	從日期減去指定的時間間隔，date 為合法的日期，expr 為指定的時間間隔
DATEDIFF(date1,date2)	回傳兩個日期之間的天數date1 和 date2 引數是合法的日期或日期/時間運算式
EXTRACT()	用于回傳日期/時間的單獨部分，如年，月，日

9. 視圖

? 根據本人的理解，視圖就是將一個或者多個表之間關聯起來并作為一個虛擬表回傳，這個虛擬表是動態改變的，我們下次再次將多個表關聯起來時只需要操作視圖即可，

? 在 SQL 中，視圖是基于 SQL 陳述句的結果集的可視化的表，視圖包含行和列，就像一個真實的表，視圖中的欄位就是來自一個或多個資料庫中的真實的表中的欄位，您可以向視圖添加 SQL 函式、WHERE 以及 JOIN 陳述句，也可以呈現資料，就像這些資料來自于某個單一的表一樣，

? 視圖總是顯示最新的資料！每當用戶查詢視圖時，資料庫引擎通過使用視圖的 SQL 陳述句重建資料，

? 視圖所查詢出來的資料只能進行查看，不能增刪改，

# 基本語法
CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

10. AND OR

? 將多個條件結合進行篩選

? and : 連接的條件都需要滿足

? or : 連接的條件有一個滿足就可

11. GROUP BY 和 ORDER BY

? ORDER BY 為排序操作，根據指定的屬性來進行排序，可以指定升序以及降序

# 默認為升序排列
select *
from grade
order by grade;

# 降序排列，先按成績，成績一樣再按分數
select *
from grade
order by grade,name desc;

12. EXISTS

SELECT column_name(s)
FROM table_name
WHERE EXISTS
(SELECT column_name FROM table_name WHERE condition);

# 首先執行 外部查詢 SELECT column_name(s) FROM table_name
# 將查詢到的每一條資料傳給內查詢，看內查詢能否查詢到結果，查詢不到回傳Flase，反之為True
# 內查詢回傳為 True 則保留外查詢這一條記錄

13.SELECT TOP

? 用于規定要回傳的記錄的數目，如果查詢回傳的結果很多，輸出花費比較多，我們可以使用此陳述句來限定數目

# 適用于 SQL Server / MS Access
SELECT TOP number|percent column_name(s)
FROM table_name;
# 適用于 MySQL
SELECT column_name(s)
FROM table_name
LIMIT number;
# 適用于 Oracle
SELECT column_name(s)
FROM table_name
WHERE ROWNUM <= number;

select *
from Teach
limit 3;

6. 資料匯出

# 匯出資料
select * from table into outfile '路徑';

# 匯入資料
load data local infile '路徑' into table 表名 fields terminated by ‘\t’

? 在此之前，需要修改一下資料匯出的默認路徑

使用 show variables like '%secure%'; 查看secure-file-priv設定，

  			2. secure_file_prive=null ––限制mysqld 不允許匯入匯出
     secure_file_priv=/path/ – --限制mysqld的匯入匯出只能發生在默認的/path/目錄下
     secure_file_priv=’’ – --不對mysqld 的匯入 匯出做限制
  			3. 在 mysql 的安轉目錄下找到 my.ini 檔案，修改secure_file_prive 為 '' 使其不對mysqld 的匯入 匯出做限制
  			4. 打開計算機管理>>服務與應用程式>>服務>>mysql（版本不同名字可能不同）>>右鍵重新啟動>>完成重啟
  			5. 重復 1 步驟，看看修改是否成功，若沒有成功可以試試重啟電腦，
  			6. 之后再進行匯入匯出操作

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/242932.html

標籤：其他

上一篇：范式函式依賴多值依賴

下一篇：SQL Server資料庫技術期末大作業