一文带你用 SQL 征服数学建模数据处理

一文带你明确 SQL 的强大

1. SQL 简介

​ SQL (Structured Query Language:布局化查询语言) 是用于用于管理关系数据库管理系统（RDBMS）。 SQL 的范围包罗数据插入、查询、更新和删除，数据库模式创建和修改，以及数据访问控制。
2.优势

​ SQL 面向数据库举行 增删查改，在我们没有打仗数据库的时候，如果要对数据举行处置惩罚，一般会用到 for 循环举行遍历。比如，我们有一个布局体数组（下文称为表），内里包罗着名字，年事和性别。如果我们要把性别为女的同学查找出来，我们需要使用 for 循环一遍，访问每一个布局体中的性别，举行甄别。这种方法对于几百几千条数据来说泯灭时间看似没有差异，但如果是几十万，几百万条数据，时间的泯灭则越来越重。
​ 再者，如果存在别的一个表，内里记载着每一同学每次测验的成绩，我们现在需要将两个表举行合并，成为一条包罗着名字，性别，年事以及成绩（假设名字唯一）的数据，这时我们就需要使用两个 for 循环来处置惩罚，并构造新的布局体来举行存储，即便其他语言比如 Python 等 不需要构造布局体，但两层的 for 循环带来的时间泯灭的提高也是不容小觑的，更况且会存在更多层 for 循环的情形
​ 笔者曾参加过 2020年 的数模国赛，选择的就是大数据问题，说实话，其时没有用到过数据库，用的虽然是 Python 但 处置惩罚数据的时候，使用了许多 for 循环，泯灭了许多时间，只水了个省二。贴一下其时处置惩罚处置惩罚数据的一小部门代码，可以看出，不但数据量大而且处置惩罚逻辑复杂，还很容易堕落。比赛完之后就以为如果使用数据库来举行处置惩罚，定会有奇效的。果然，几分钟 for 才得到的数据 SQL 一两秒秒了。所以笔者很推荐准备参加数学建模而且选择大数据题目标同学，加强一下数据库的学习以及训练。

1. # 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 增加数据库

1. create database learn; # learn 为数据库的名称 use learn; # 使用此数据库set names utf8; # 设置编码

复制代码

1.2 增加表

1. CREATE TABLE Persons # 括号里包罗着每一个键的名称以及数据类型用逗号隔开(    PersonID  int,    LastName  varchar(255),    FirstName varchar(255),    Address   varchar(255),    City      varchar(255)    # 可在定义之后加上一些约束，之后会提到);

复制代码

1.3 增加表中的数据

1.3.1 插入整条数据

1. insert into Persons # 插入整条数据的时候要与表中的键一一对应 values (1, &#39;mary&#39;, &#39;Smith&#39;, &#39;127.0.0.1&#39;, &#39;Beijing&#39;);insert into Personsvalues (3, &#39;mar&#39;, &#39;Smith&#39;, &#39;127.0.4.1&#39;, &#39;Beijing&#39;);insert into Personsvalues (4, &#39;max&#39;, &#39;Smith&#39;, &#39;127.0.6.1&#39;, &#39;Tianjin&#39;);insert into Personsvalues (5, &#39;mute&#39;, &#39;Rio&#39;, &#39;127.0.6.1&#39;, &#39;re&#39;);insert into Personsvalues (6, &#39;mate&#39;, &#39;Linda&#39;, &#39;127.1.6.1&#39;, &#39;Tianjin&#39;);insert into Personsvalues (2, &#39;mario&#39;, &#39;Smith&#39;, &#39;127.1.0.1&#39;, &#39;Nanjing&#39;);

复制代码

1.3.2 插入指定键的数据

1. INSERT INTO Persons (PersonID,LastName,City) # 括号中的便是 表中的键 下面插入的 value 需要与之对应VALUES (&#39;mirry&#39;,&#39;Smith&#39;,&#39;Hangzhou&#39;);

复制代码

2. 删

2.1 删除数据库

1. drop database learn; # 删库需审慎

复制代码

2.2 删除表

1. drop TABLE website; # 删除表TRUNCATE TABLE table_name # 删除表的数据，表自己存在

复制代码

2.3 删除索引

1. ALTER TABLE table_name DROP INDEX index_name; # 适用于MySQL DROP INDEX index_name; # 适用于 DB2/OracleDROP INDEX table_name.index_name; # 适用于 MS SQL Server

复制代码

3. 改

3.1 表中增加列

1. ALTER TABLE table_name                                 # 需要指定 表的名称，列的名称 以及 数据类型ADD column_name datatypealter table tsadd sex nvarchar(10);

复制代码

3.2 表中删除列

1. ALTER TABLE table_name                         # 需要指定 表的名称，列的名称DROP COLUMN column_namealter table tsdrop column sex;

复制代码

3.3 表中修改列的数据类型

1. # SQL Server / MS Access 适用ALTER TABLE table_name                         # 需要指定 表的名称，列的名称，列要修改成的数据类型        ALTER COLUMN column_name datatype# My SQL / Oracle 适用ALTER TABLE table_name                                # 需要指定 表的名称，列的名称，列要修改成的数据类型        MODIFY COLUMN column_name datatypealter table tsmodify column sex nvarchar(100);

复制代码

4. 查

4.1 查询所有记载

1. select *                                 # * 便是指代所有键from Persons;

复制代码

4.2 查询指定行的记载

1. # 先指定命据库use learn;select PersonID,City from Persons;# 直接访问select PersonID,City from learn.Persons;

复制代码

4.3 查询数据库

1. show databases;

复制代码

4.4 查询数据库中表的名称

1. show tables;

复制代码

5. 主键，外键，UNIQUE 约束

​ 主键是约束标识表中每一条记载的，就相当于我们的身份证，不能为 NULL , 也不能重复，在插入数据的时候必须为之指定，每一个表都有且仅有一个主键。

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

复制代码

​ 外键是一个表指向另一个表中的 UNIQUE KEY(唯一约束的键)，假设有 每个班任课老师的表 和 每个教职工的表 这两张表。
班级任课老师表
任课老师教职工号（外键）老师班级03C二班01A一班03C三班02B四班教职工表
老师教职工号（主键）C03B02A01​ 班级任课老师表中的 任课老师教职工号 指向 教职工表的教职工号。
​ 因此，教职工号作为 教职工的主键(PRIMARY KEY)，任课老师教职工号 作为 班级任课老师表 的 外键(FOREIGN KEY)。

1. # 适用于 MySQLCREATE 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 AccessCREATE 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 约束，这地方在处置惩罚数据的时候用到的时候没有太多，就当相识一下。
​ 约束的创建

1. 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));

复制代码

​ 约束的删除

1. # MySQLALTER TABLE tsDROP INDEX id# SQL Server / Oracle / MS AccessALTER TABLE PersonsDROP CONSTRAINT id

复制代码

5.进阶

1. 操纵符

1.1 WHERE（无法与聚合函数一起使用）

​ 之前举的一个查询为女生的同学的例子，传统方法为 for 循环，这样耗时耗力，使用 where 我们可以很简单的秒了

1. # 根本语法SELECT column_name,column_name   # 需要查询的列FROM table_nameWHERE column_name operator value;   # 这里为一些条件语句，接下来会提及

复制代码

1.2 IN

​ 当我们需要查询某一键符合多个值的时候，这个便派上了用场

1. create table grade(    name nchar(10),    grade int);insert into grade values (&#39;a&#39;, 100);insert into grade values (&#39;b&#39;, 100);insert into grade values (&#39;c&#39;, 90);insert into grade values (&#39;a&#39;, 100);insert into grade values (&#39;b&#39;, 95);insert into grade values (&#39;c&#39;, 93);# 根本语法SELECT column_name(s)FROM table_nameWHERE column_name IN (value1,value2,...);# 查询名字是a,b,c中间一人的数据select * from gradewhere name in (&#39;a&#39;,&#39;b&#39;,&#39;c&#39;);  # 括号里的元素要与键的数据类型相同# 查询成绩是 90 93 100 之间的同学数据select * from gradewhere grade in (90, 93 ,100);

复制代码

1.3 BETWEEN

​ 我们需要查询某一键符合在某一范围时，这个便派上了用场

1. # 根本语法SELECT column_name(s)FROM table_nameWHERE column_name BETWEEN value1 AND value2;select * from gradewhere grade between 95 and 100;

复制代码

1.4 LIKE

​ LIKE 操纵符用于在 WHERE 子句中搜索列中的指定模式，主要就是匹配模式的书写，大概会涉及到正则表达式，下面给出 菜鸟课程 总结的通配符，对正则不熟悉的同学可参考我的别的一篇博文传送门。
通配符匹配对象%替代 0 个或多个字符_替代一个字符[charlist]字符列中的任何单一字符[^charlist] 或 [!charlist]不在字符列中的任何单一字符​ MySQL 中使用 REGEXP 或 NOT REGEXP 运算符 (或 RLIKE 和 NOT RLIKE) 来操纵正则表达式

1. # 查询以 c或d或e开头的同学名字的数据select * from gradewhere name REGEXP &#39;^[cde]&#39;;# 查询以 c到e字母开头的同学名字的数据select * from gradewhere name REGEXP &#39;^[c-e]&#39;;# 查询 不以 c到e字母开头的同学名字的数据select * from gradewhere name REGEXP &#39;^[^c-e]&#39;;# 查询一个字母的同学名字的数据select * from gradewhere name like &#39;_&#39;;

复制代码

2. SELECT DISTINCT

​ 以上面 班级任课老师表 为例，如果我们想要任课老师的教职工号，我们可以用 select 来只对 任课老师教职工号 举行选择，但查询的结果会有重复，C 的教职工号出现两次，我们可以使用 SELECT DISTINCT 举行查询而不会出现重复的情况。一个重要的特点就是每一条查询结果都各不相同。

1. # 查询参加测验的同学select distinct namefrom grade;# 查询测验分数的种类select distinct gradefrom grade;# 查询学生及其分数的种类select distinct grade,namefrom grade;

复制代码

3. JOIN

​ join 用于将多个表联系在一起，大抵可分为四种join方法
join 类型形貌INNER JOIN如果表中有至少一个匹配，则返回行LEFT JOIN即使右表中没有匹配，也从左表返回所有的行RIGHT JOIN即使左表中没有匹配，也从右表返回所有的行FULL JOIN只要此中一个表中存在匹配，则返回行

1. 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 (&#39;C&#39;,&#39;03&#39;,&#39;男&#39;);insert into TeaNo values (&#39;B&#39;,&#39;02&#39;,&#39;男&#39;);insert into TeaNo values (&#39;A&#39;,&#39;01&#39;,&#39;女&#39;);insert into TeaNo values (&#39;D&#39;,&#39;04&#39;,&#39;女&#39;);insert into Teach values (&#39;03&#39;, &#39;C&#39;, &#39;二班&#39;);insert into Teach values (&#39;01&#39;, &#39;A&#39;, &#39;一班&#39;);insert into Teach values (&#39;03&#39;, &#39;C&#39;, &#39;三班&#39;);insert into Teach values (&#39;03&#39;, &#39;B&#39;, &#39;四班&#39;);# inner joinselect TeaNo.name,TeaNo.sex,Teach.Classfrom TeaNo inner join Teachon TeaNo.Tno = Teach.Tno;# A,女,一班# C,男,二班# C,男,三班# C,男,四班# LEFT JOIN  B 和 D 老师都在右边找不到对应的信息但仍返回左边一行，空缺地方为NULLselect TeaNo.name,TeaNo.sex,Teach.Classfrom TeaNo left outer join Teachon TeaNo.Tno = Teach.Tno;# A,女,一班# B,男,# C,男,二班# C,男,三班# C,男,四班# D,女,# Right JOINselect TeaNo.name,TeaNo.sex,Teach.Classfrom TeaNo right outer join Teachon TeaNo.Tno = Teach.Tno;# C,男,二班# A,女,一班# C,男,三班# C,男,四班# FULL JOIN 似乎语法并不支持

复制代码

4. 别名

​ 在举行查询时，可以通过创建别名让列名称大概表名称的可读性更强。

1. # 列的别名SELECT name AS TeacherNameFROM TeaNo;# 表的别名 多用于多表查询时SELECT nameFROM TeaNo AS TeacherName;

复制代码

5. SQL 函数

​ 在数据处置惩罚上，正是由于这些可以直接使用的函数，让数据处置惩罚更加简便，下列是菜鸟课程上总结的函数
函数名作用FORMAT()格式化某个字段的显示方式LEN()返回某个文本字段的长度ROUND()对某个数值字段举行指定小数位数的四舍五入UCASE()将某个字段转换为大写LCASE()将某个字段转换为小写AVG()返回平均值COUNT()返回行数NOW()返回当前的系统日期和时间FIRST()返回第一个记载的值LAST()返回最后一个记载的值MAX()返回最大值MID()从某个文本字段提取字符，MySql 中使用SubString(字段，1，end)从某个文本字段提取字符MIN()返回最小值SUM()返回总和

1. # FORMAT() 括号里的元素都是必须的SELECT FORMAT(column_name,format) FROM table_name; # 得到系统时间select date_format(NOW(),&#39;%Y-%m-%d&#39;) As data;

复制代码

6. HAVING

​ 由于WHERE 关键字无法与聚合函数一起使用，因此引入Having，HAVING 子句可以让我们筛选分组后的各组数据。

1. # 根本语法SELECT column_name, aggregate_function(column_name)FROM table_nameWHERE column_name operator valueGROUP BY column_nameHAVING aggregate_function(column_name) operator value;# aggregate_function(column_name) 为聚合函数，不能与 where 连用

复制代码

7. Union 与 Union All

​ 二者用于合并两个或多个 SELECT 语句的结果。
​ 注意事项：UNION 内部的每个 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每个 SELECT 语句中的列的顺序必须相同。
​ 二者差别之处：Union 用于取并集去重，Union All 用于取并集不去重。

1. create table country1(    Name varchar(10),    Eng varchar(10));create table country2(    Name varchar(10),    Eng varchar(10),    amount int);insert into country1 values (&#39;中国&#39;, &#39;CN&#39;);insert into country1 values (&#39;美国&#39;, &#39;USA&#39;);insert into country1 values (&#39;英国&#39;, &#39;UK&#39;);insert into country1 values (&#39;日本&#39;, &#39;Japan&#39;);insert into country2 values (&#39;德国&#39;,&#39;German&#39;,100);insert into country2 values (&#39;澳大利亚&#39;,&#39;Australia&#39;,100);insert into country2 values (&#39;俄罗斯&#39;,&#39;Russia&#39;,100);insert into country2 values (&#39;德国&#39;,&#39;German&#39;,100);# 取并集 去重select Name,Engfrom country1UNIONselect Name,Engfrom country2;# 取并集不去重select Name,Engfrom country1UNION ALLselect Name,Engfrom 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 语句重建数据。
​ 视图所查询出来的数据只能举行查看，不能增删改。

1. # 根本语法CREATE VIEW view_name ASSELECT column_name(s)FROM table_nameWHERE condition

复制代码

10. AND OR

​ 将多个条件结合举行筛选
​ and : 连接的条件都需要满意
​ or : 连接的条件有一个满意就可
11. GROUP BY 和 ORDER BY

​ ORDER BY 为排序操纵，根据指定的属性来举行排序，可以指定升序以及降序

1. # 默认为升序分列select *from gradeorder by grade;# 降序分列，先按成绩，成绩一样再按分数select *from gradeorder by grade,name desc;

复制代码

12. EXISTS

1. SELECT column_name(s)FROM table_nameWHERE EXISTS(SELECT column_name FROM table_name WHERE condition);# 首先执行 外部查询 SELECT column_name(s) FROM table_name# 将查询到的每一条数据传给内查询，看内查询能否查询到结果，查询不到返回Flase，反之为True# 内查询返回为 True 则保存外查询这一条记载

复制代码

13.SELECT TOP

​ 用于规定要返回的记载的数目，如果查询返回的结果许多，输出耗费比力多，我们可以使用此语句来限定命目

1. # 适用于 SQL Server / MS AccessSELECT TOP number|percent column_name(s)FROM table_name;# 适用于 MySQLSELECT column_name(s)FROM table_nameLIMIT number;# 适用于 OracleSELECT column_name(s)FROM table_nameWHERE ROWNUM >服务与应用步伐>>服务>>mysql（版本差别名字大概差别）>>右键重新启动>>完成重启                          5. 重复 1 步调，看看修改是否乐成，若没有乐成可以试试重启电脑。                          6. 之后再举行导入导出操纵

复制代码

7.实战

  如果有同学想要举行巩固应用的话，可以参考一下我的别的一篇博客，一气呵成~
  使用 SQL 实战处置惩罚2020国赛C题数据

来源：https://blog.csdn.net/qq_44614115/article/details/111982890
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！