请选择 进入手机版 | 继续访问电脑版

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

[复制链接]
菜鸡 发表于 2021-1-1 09:58:07 | 显示全部楼层 |阅读模式 打印 上一主题 下一主题
一文带你明确 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 中使用 REGEXPNOT 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
免责声明:如果侵犯了您的权益,请联系站长,我们会及时删除侵权内容,谢谢合作!
回复

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则


专注素材教程免费分享
全国免费热线电话

18768367769

周一至周日9:00-23:00

反馈建议

27428564@qq.com 在线QQ咨询

扫描二维码关注我们

Powered by Discuz! X3.4© 2001-2013 Comsenz Inc.( 蜀ICP备2021001884号-1 )