商业大数据分析与应用基础-课件 第3章 数据预处理-SQL基础

主讲：第3章SQL语言基础3.1SQL概述3.2数据定义语言3.3数据操纵语言3.4数据查询语言目

录

SQL概述

3.1结构化查询语言(StructuredQueryLanguage)，简称SQL，是数据库编程的核心语言。SQL的发展是从1974年开始的，其发展过程如下：1974年-由Boyce和Chamberlin提出，当时称SEQUEL。1976年-IBM公司的Sanjase研究所在研制RDBMSSYSTEMR时改为SQL。1979年-Oracle公司发表第一个基于SQL的商业化RDBMS产品。1982年-IBM公司出版第一个RDBMS语言SQL/DS。1985年-IBM公司出版第一个RDBMS语言DB2。1986年-美国国家标准化组织ANSI宣布SQL作为数据库工业标准。SQL是一个标准的数据库语言，是面向集合的描述性非过程化语言。SQL的产生和发展SQL的强大功能语法简单适用于数据分析的自然语言，基于关系代数、面向集合。SQL的语法相对于其他编程语言非常简单，常用的关键字也就几个，select、from、where、join、if等。没有像Java、C++等编程语言定义的类、对象、继承之类的复杂概念。高效的语言声明式语言，直接表述想要的结果，而非获得方式。完成同样的统计功能，SQL代码量较少。容易理解很容易将SQL代码映射到二维表中的数据，不同操作的代码其实就是对应着二维表的不断变换，相对容易理解。SQL的产生和发展SQL语言有以下四大类：数据查询语言（DQL）、数据定义语言（DDL）、数据操纵语言（DML），数据控制语言（DCL）。1)DQL(DataQueryLanguage)：数据查询语言，用于查询中的数据，分别为：SELECT、FROM、WHERE、ORDERBY、GROUPBY、HAVING等子句。

SELECT：查询

2)DDL(DataDefinitionLanguage)：数据定义语言，用于创建、修改、删除数据库/数据表等，分别为：

CREATE：创建

ALTER：修改

DROP：删除SQL的分类3)DML(DataManipulationLanguage)：数据操纵语言，用于对表中的数据进行新增、删除、修改操作，分别为：

INSERT:插入

DELETE:删除

UPDATE:更新

4)DCL(DataControlLanguage)：数据控制语言，用于设置或者更改数据库用户或角色权限，分别为：

GRANT：授权

DENY：拒绝权限

REVOKE：回收权限SQL的分类谁在用SQL？只要跟数据相关的从业者都应该掌握SQL，在编程越来越普及的背景下，SQL并不是程序员专有技能。数据开发、数据库工程师可以说是数据直接提供方，将采集的数据进行合理的组织，创建二维表存储数据，设计数据仓库模型，以便分析团队能够便捷地、快速地获取到想要的数据。数据分析、数据挖掘工程师做挖掘的数据很可能来自数据仓库，使用SQL统计可以非常方便地了解数据、分析数据，与数据团队之间沟通成本更低。产品经理和运营产品和运营会经常进行数据分析，他们分析的数据最常见的来源是BI团队提供的报表，但这种方式非常不灵活。因此对于产品和运营如果能掌握SQL自助地分析会大大提升分析效率。SQL与大数据分析

数据定义语言

3.2创建数据库是指在数据库系统中划分一块空间，用来存储相应的数据。这是进行表操作的基础，也是进行数据库管理的基础。创建数据库语法结构使用createdatabase命令可以创建数据库。其语法结构如下。createdatabase[ifnotexists]databasename[[default]charactersetcharset_name][[default]collatecollation_name];创建数据库在创建数据库时，数据库命名有以下几项规则：不能与其他数据库重名，否则将发生错误。名称可以由任意字母、阿拉伯数字、下划线（_）和“$”组成，可以使用上述的任意字符开头，但不能使用单独的数字，否则会造成它与数值相混淆。名称最长可为64个字符，而别名最多可长达256个字符不能使用关键字作为数据库名、表名。在默认情况下，Windows下数据库名、表名的大小写是不敏感的，而在Linux下数据库名、表名的大小写是敏感的。如果为了便于数据库在平台间进行移植，可以采用小写来定义数据库名和表名。创建数据库【例3-1】创建一个名称为student的数据库。createdatabaseifnotexistsstudent;创建表的语法结构表决定了数据库的结构，表是存放数据的地方，一个库需要什么表，各数据库表中有什么样的列，是要合理设计的。创建表的语法结构如下：其中，column_definition：字段的定义。包括指定字段名、数据类型、是否允许空值，指定默认值、主键约束、唯一性约束、注释字段名、是否为外键，以及字段类型的属性等。字段的定义具体格式描述如下：

col_nametype[notnull|null][defaultdefault_value][auto_increment][unique[key]|[primary]key][comment'string'][reference_definition]create[temporary]table[ifnotexists]table_name[([column_definition],...|[index_definition])];创建数据表

创建数据表

整数（

int）；小数（decimal

）；日期时间类型（date,

time,

datetime,

year）；字符串（char,

varchar

）创建数据表

【例3-2-1】创建class数据表。USEstudent;CREATETABLEclass(class_idchar(8)notnullprimarykeycomment'班级编号',class_namevarchar(30)notnulluniquecomment'班级名称',tutorchar(10)comment'班主任',dept_idchar(2)notnullcomment'系编号');创建数据表

【例3-2-2】创建score数据表。CREATETABLEscore(student_idchar(12)notnullcomment'学号',student_namevarchar(30)notnullcomment'学生姓名',course_idvarchar(30)notnullcomment'课程编号',dailyintcomment'平时成绩',finalintcomment'期末成绩');创建数据表

修改表是指修改数据库中已存在的表的定义。通过altertable语句来修改表。修改表包括增加字段、删除字段、修改表名、修改字段数据类型、修改字段名、修改字段的排列位置、更改默认存储引擎和删除表的外键约束等。修改数据库表语法格式如下：altertable

表名

altertable‘books’

+add[column]字段定义[first|after

字段名]//添加字段addcolumn‘book_name’

varchar(30);|alter[column]字段名｛SETDEFAULT默认值|DROPDEFAULT

｝//修改字段默认值|change[column]

原字段名

字段定义//重命名字段change‘book_name’

‘new_book_name’;

|modify[column]字段定义[first|after字段名]//修改字段数据类型|drop[column]字段名 //删除列|rename[TO]新表名 //对表重命名|orderby

字段名 //按字段排序|convertTOcharacterset字符集名[collate

排序规则名]//将字符集转换为二进制|[default]characterset

字符集名[collate

排序规则名]//修改表的默认字符集修改数据表

【例3-3】在class表的dept_id列后面增加一列address。ALTERTABLEclassADDaddressvarchar(30)notnullAFTERdept_id;修改数据表

【补例】删除class数据表中的address字段。ALTERTABLEclassDROPaddress;【补例】修改score数据表中的course_id字段的数据长度为10。ALTERTABLEscoreMODIFYcourse_idvarchar(10);通过创建索引，MySQL可以更快地检索具有特定dept_id值的记录，从而提高查询性能。需要注意的是，索引虽然可以提高查询性能，但也会占用额外的磁盘空间，并在进行数据插入、更新和删除操作时带来一定的开销。因此，在创建索引时需要权衡利弊，根据实际需求和数据量来选择合适的索引策略。创建索引

【例3-4】基于dept_id列创建一个索引。CREATEINDEXidx_dept_idONclass(dept_id);删除数据库是指在数据库系统中删除已经存在的数据库。删除数据库之后，原来分配的空间将被收回。删除数据库语法格式如下：需要注意的是，删除数据库会删除该数据库中所有的表和所有数据。因此，删除数据库前最好存有备份。dropdatabase[ifexists]db_name删除数据库

删除表是指删除数据库中已存在的表。删除表时，会删除表中的所有数据。因此，在删除表时要特别注意。删除表的语法格式如下：droptabletable_name

删除数据表

CREATETABLEnew_table_namelikeold_table_name;INSERTINTOnew_table_nameselect*fromold_table_name;补充：复制表的语法格式如下：

数据操纵语言

3.3使用insertinto语句添加数据，语法格式：说明：（1）使用insert语句可以向表中插入一行数据，也可以插入多行数据，最好一次插入多行数据，各行数据之间用“，”分隔。（2）values子句包含各列需要插入的数据清单，数据的顺序要与列的顺序相对应。若表名后不给出列名，则在values子句中要给出每一列的值，如果列值为空，则值必须置为null，否则会出错。

insert

[into]table_name[(col_name,...)]

values({expr|default},...),(...),...|setcol_name={expr|default},...表记录的插入【例3-5-1】向class表中插入数据：INSERTINTOclass(class_id,class_name,tutor,dept_id,address)VALUES('19020111','文秘1901','张也','02','广东广州'),('19020211','语教1901','乔叶','02','广东潮州'),('19040911','软件1901','刘少阳','04','广东广州'),('19040912','软件1902','赵紫燕','04','广东揭阳'),('19041011','计应1901','王丽','04','广东肇庆'),('19050111','会电1901','吴刚','05','广东江门'),('19050211','经管1901','孙阳辉','05','广东广州'),('20040911','软件2001','刘晓阳','04','广东河源'),('20040912','软件2002','陈玉','04','广东梅州'),('20041011','计应2001','唐杰','04','广东汕头'),('20041012','计应2002','李娅','04','广东广州'),('20041111','网络2001','鲁尼','04','广东佛山'),('20041112','网络2002','李原','04','广东深圳');表记录的插入【例3-5-2】向score表中插入数据：INSERTINTOscore(student_id,student_name,course_id,daily,final)VALUES('20213010111','张三','13','85','98'),('20213010113','李四','12','66','93'),('20213010111','张三','12','77','93'),('20213010113','李四','13','80','95'),('20213010114','王晓明','13','78','90'),('20213010115','邓洁','14','90','91'),('20213010116','刘珊珊','13','87','84'),('20213010117','李桃','12','74','77'),('20213010118','黄安明','14','65','91'),('20213010119','周玲','12','89','56'),('20213010120','伍丽','12','75','79'),('20213010121','何婷婷','13','60','71'),('20213010122','伦绍','14','94','50'),('20213010123','陈隽辉','13','90','73'),('20213010124','张旭东','13','80','95');表记录的插入用update...set...命令可以修改一个表的数据。一般表记录修改的语法格式如下：说明：（1）set子句：根据where子句中指定的条件，对符合条件的数据行进行修改。若语句中不设定where子句，则更新所有行；（2）语句通过SET子句修改符合修改条件行中的相应字段，可以只修改一个字段值，也可以同时修改多个字段值，中间用逗号隔开。其中，expr1、expr2……可以是常量、变量或表达式。updatetable_namesetcol_name1=expr1[,col_name2=expr2...][where子句]表记录的修改

【例3-6】将class表中班级名称（class_name）为“网络2001”的班主任“鲁尼”改为“刘刚”。UPDATEclassSETtutor='刘刚'WHEREclass_name='网络2001';表记录的插入利用delete…from…语句可以从单个表中删除指定表数据，一般表记录删除的语法格式如下：说明：（1）from子句：用于指定从何处删除数据。（2）where子句用于指定删除条件。如果省略where子句则删除该表的所有行。（3）数据删除后将不能恢复，因此，在执行删除之前一定要对数据做好备份。deletefromtbl_name[where子句]表记录的删除

数据查询语言

3.4select语句是SQL语言从数据库中获取信息的一个基本语句。该语句可以实现从一个或多个数据库中的一个或多个表中查询信息，并将结果显示为另外一个二维表的形式，称之为结果集（resultset）。select语句的基本的语法格式可归纳如下：select[all|distinct]selection_list fromtable_source[wheresearch_condition] [groupbygrouping_columns][withrollup] [havingsearch_condition][orderbyorder_expression[asc|desc]] [limitcount]查询(select)1查询表中的所有字段使用select语句查询一个数据表。使用select语句时，首先要确定所要查询的列。“*”代表所有的列。【例3-7】查询class表中的所有数据。2查询表中的部分字段针对表中的多列进行查询，只要在select后面指定要查询的列名即可，多列之间用“，”分隔。【例3-8】查询class表中class_name和tutor的数据。select*from

class;SELECTclass_name,tutorFROMclass;查询(select)3为字段取别名利用select语句查询数据时，输出项一般显示创建表时定义的字段名。MySQL可以为查询显示的每个输出字段或表达式取一个别名，以增加结果集的可读性。例如，可以用as关键字给字段取一个中文名。实现给select子句中的各项取别名其语法格式为：select

项的原名as别名

。as也可省略。【例3-9】在class表中查询class_name、tutor、address字段信息，查询结果的字段名显示为班级名称、班主任、地址。SELECTclass_nameAS'班级名称',tutor'班主任',address'地址'FROMclass;查询(select)使用where子句过滤结果集查询符合指定条件的记录数据。如果要从很多记录中查询出指定的记录，那么就需要一个查询的条件。设定查询条件应用的是where子句，通过where子句可以实现很多复杂的条件查询。在使用where子句时，需要使用一些比较运算符来确定查询的条件。【例3-10】查询表class中系编号是“04”的学生的班级名称、班主任和地址信息。SELECTclass_name,tutor,addressFROMclassWHEREdept_id=04;条件查询(where)使用通配符结合的like搜索条件，是通过进行字符串的比较来选择符合条件的行。like主要用于字符类型数据。当使用like搜索条件时，模式字符串中的所有字符都有意义，包括开头和结尾的空格。字符串内的英文字母和汉字都算一个字符。若要查询字段值不符合某个模式时，也可用通配符并使用notlike作为查询条件。

它有两种通配符：“%”和下划线“_”：“%”可以匹配一个或多个字符，可以代表任意长度的字符串，长度可以为0。“_”只匹配一个字符。模糊匹配(like)select字段列表from

表WHERE字段名[NOT]LIKE模式；【例3-11】在class表中查询所有刘姓的班主任所带班级的名称、班主任和地址。SELECTclass_name,tutor,addressFROMclassWHEREtutorLIKE'刘%';模糊匹配(like)在where子句中，可以使用between搜索条件检索指定范围内的行。使用between搜索条件时，使用between搜索条件相当于用and连接两个比较条件，如“xbetween10and27”相当于表达式“x>=10andx<=27”。由此可见，在生成结果集中，边界值也是符合条件的。检索条件指定排除某个范围的值，一般可以利用notbetween关键字实现。【例3-12】查询score表中期末成绩在70-79之间的学生数据。SELECT*FROMscoreWHEREfinalbetween70and79;范围条件匹配查询(between)还可以怎么写？

in关键字可以判断某个字段的值是否在于指定的集合中。如果字段的值在集合中，则满足查询条件，该记录将被查询出来；如果不在集合中，则不满足查询条件。实际上，使用in搜索条件相当于用or连接两个比较条件，如“xin（10，15）”相当于表达式“x=10orx=15”。也可以使用notin关键词查询不在某取值范围内的记录行数据。【例3-13】查询class表中来自广东潮州、广东汕头和广东梅州3个城市的所有数据。SELECT*FROMclassWHEREaddressIN('广东潮州','广东汕头','广东梅州');集合判断(in)排序(orderby)利用orderby子句可以对查询的结果进行升序（asc）或降序（desc）排列。说明：orderby子句可以通过指定列名、函数值和表达式的值，或SELECT子句中某字段的序号值进行排序。默认情况下，结果集按照升序排列。也可以在输出项的后面加上关键字desc来实现降序输出。对含有null值的列进行排序时，如果是按升序排列，null值将出现在最前面，如果是按降序排列，null值将出现在最后。排序时可以有多个排序依据，优先级按在orderby子句中的位置而定，当第一排序依据的列值相等时，则根据第二个排序依据的列值排序，依此类推。排序(orderby)利用orderby子句进行排序，需要注意如下事项：（1）orderby子句包含的列并不一定出现在选择列表中。（2）orderby子句不可以使用text、ntext或image类型的列。【例3-14】在score表中查询期末成绩高于70分的学生学号、姓名、平时成绩和期末成绩，并按照期末成绩的降序排列。SELECTstudent_id学号,student_name学生姓名,daily平时成绩,final期末成绩

FROMscoreWHEREfinal>70ORDERBYfinalDESC;数量限制(limit)limit是用来限制查询结果的数量的子句。可以指定查询结果从哪条记录开始显示。还可以指定一共显示多少条记录。limit子句中有两个参数“初始位置”和“记录数”，“初始位置”是开始读取第1条记录的编号（注意：在MYSQL查询结果中，第1条位置编号为0），省略该参数则从第1条开始返回；“记录数”是指要查询记录的个数。【例3-15】查询score表中期末成绩最高的学生的学号、姓名、课程编号及期末成绩信息。SELECTstudent_id,st