《数据库与信息系统》SQL语言

SQL语言与可编程对象4《数据库与信息系统》SQL语言全文共59页，当前为第1页。目录常用SQL语句4.1可编程对象4.2《数据库与信息系统》SQL语言全文共59页，当前为第2页。SQL语言概述《数据库与信息系统》SQL语言全文共59页，当前为第3页。SQL语言概述SQL是结构化查询语言（StructureQueryLanguage）的简称，是关系数据库操作语言的国际标准。MySQL所使用的SQL语言遵从ANSI-92国际标准。《数据库与信息系统》SQL语言全文共59页，当前为第4页。SQL语言的特点非过程化的描述性语言。语言简洁、易学易用。使用方式联机交互独立使用嵌入到其他宿主语言输入SQL语句操作结果《数据库与信息系统》SQL语言全文共59页，当前为第5页。在NavicatforMySQL中打开交互式操作窗口1.点击工具栏中的“查询”按钮2.点击“新建查询”3.在“查询编辑器”中输入命令，点击“运行”。《数据库与信息系统》SQL语言全文共59页，当前为第6页。SQL语言的主要语句类别维护数据结构。支持建立、修改或删除数据库及数据对象操作。DDL(DataDefinitionLanguage)数据库定义语言数据访问。支持对数据库中数据的查询、插入、修改和删除等操作。DML(DataManipulationLanguage)数据库操纵语言数据库监视。支持对数据对象的授权、安全控制等操作。DCL(DataControlLanguage)数据库控制语言操作对象是数据库或者对象（表、视图等）操作对象是表中的数据《数据库与信息系统》SQL语言全文共59页，当前为第7页。SQL语言的书写规则不区分字母大小写一条语句可写在一行或多行上可用注释增强语句可读性①单行注释（--）,也可以用#

②多行注释（/*…*/）例如：/*打开e_learning数据库。当第一次访问某数据库时，需要使用USE语句打开数据库，否则其他SQL语句不能执行*/USEe_learning;【例】SELECTStudentCode,StudentNameFROMstudent;

--查询学生表中学生的学号和姓名语句中的单词可以是关键词，也可以是标识符关键字标识符语句以“；”结尾注意--之后必须有空格！！！《数据库与信息系统》SQL语言全文共59页，当前为第8页。THANKYOU!《数据库与信息系统》SQL语言全文共59页，当前为第9页。数据库定义语言（DDL）简介《数据库与信息系统》SQL语言全文共59页，当前为第10页。数据库定义语言DDL维护数据结构。支持建立、修改或删除数据库及数据对象的操作。DATABASETABLEVIEWPROCEDURETRIGGERCREATEDROPALTER主要语句：（动作+操作对象类型+对象名）创建删除更改数据库表视图存储过程触发器object_name《数据库与信息系统》SQL语言全文共59页，当前为第11页。DDL语句举例——创建数据库CREATEDATABASE数据库名

[[DEFAULT]CHARACTERSET字符集]，[[DEFAULT]COLLATE校对规则];CREATEDATABASEpet;

--创建一个名为pet的数据库，采用默认的字符集和排序规则CREATEDATABASEplantCHARACTERSETutf8;/*创建一个名为plant的数据库，设置其字符集为utf8*/[]表示可选项《数据库与信息系统》SQL语言全文共59页，当前为第12页。DDL语句举例——创建数据库命令演示《数据库与信息系统》SQL语言全文共59页，当前为第13页。DDL语句举例——使用、删除数据库USE数据库名;

--打开该数据库作为当前可操作的数据库USEpet;--将pet设定为当前数据库DROPDATABASE数据库名;

--删除数据库DROPDATABASEplant;--删除数据库plant《数据库与信息系统》SQL语言全文共59页，当前为第14页。DDL语句举例——创建数据表CREATETABLE表名(字段名1字段数据类型字段约束,字段名2字段数据类型字段约束,

……Constraint约束说明);USEpet;/*将数据库pet设定为当前数据库*/CREATETABLE'cat'(

'CatCode'int(4)NOTNULLAUTO_INCREMENT,/*不可取空值，创建自增约束*/'CatName'varchar(16)NOTNULL,

/*不可取空值*/'Gender'char(1)NOTNULLDEFAULT'母',/*Gender默认值为母*/'Birthday'dateNOTNULL,'Photo'varchar(30)DEFAULTNULL,'Introduction'text,'RegTime'timestampDEFAULTCURRENT_TIMESTAMP,/*默认当前时间*/

PRIMARYKEY('CatCode')/*设置CatCode为主键*/);字段名的单引号可以去掉字段名数据类型约束CatCodeint(4)非空,自增，主键CatNamevarchar(16)非空Genderchar(1)非空，默认’母’Birthdaydate非空Photovarchar(30)默认为空IntroductiontextRegTimetimestamp默认CURRENT_TIMESTAMP描述字段的每一行末尾是逗号语句末尾是分号《数据库与信息系统》SQL语言全文共59页，当前为第15页。DDL语句举例——创建索引CREATE[UNIQUE|FULLTEXT|SPATIAL]INDEX

索引名ON数据表名(字段ASC|DESC);--ASC升序，DESC降序CREATEUNIQUEINDEXIX_Name

ONcat(CatName

DESC);--默认为升序ASC例：在cat表的CatName字段上建立唯一索引IX_Name，按名字降序排列字段名数据类型约束CatCodeint(4)非空,自增，主键CatNamevarchar(16)非空Genderchar(1)非空，默认’母’Birthdaydate非空Photovarchar(30)默认为空IntroductiontextRegTimetimestamp默认CURRENT_TIMESTAMPcat表表名要建立的索引字段名《数据库与信息系统》SQL语言全文共59页，当前为第16页。DDL语句举例——删除数据表、索引DROPTABLE数据表名;DROPTABLEcat;/*删除cat表*/DROPINDEX索引名on数据表;DROPINDEXIX_Nameoncat;

/*删除cat表IX_Name索引*/《数据库与信息系统》SQL语言全文共59页，当前为第17页。DDL语句举例——修改数据对象ALTER数据对象对象名;ALTERUNIQUEINDEXIX_NameONcat(CatNameASC);Alter语句与Create语句格式完全相同，是在已经创建的数据对象做修改。/*修改索引IX_Name：按姓名升序排列*/《数据库与信息系统》SQL语言全文共59页，当前为第18页。转储数据定义SQL文件生成脚本xx.sql执行脚本在NavicatforMySQL中创建各种数据库对象，实质上是以可视化方式生成DDL语句，这些语句可以保存到一个SQL脚本文件中，以后运行该文件可重建数据库对象。数据库对象数据库对象《数据库与信息系统》SQL语言全文共59页，当前为第19页。SQL脚本生成与使用举例例：（1）转储student表的SQL文件student.sql；（2）创建一个新的数据库example；（3）在example数据库中运行文件student.sql建立student表。

选中表，点击右键新建数据库后，选中该数据库，右键运行*.sql文件转储单个表转储整个数据库步骤类似，选中数据库后转储《数据库与信息系统》SQL语言全文共59页，当前为第20页。数据库定义语言DDL维护数据结构。支持建立、修改或删除数据库及数据对象的操作。DATABASETABLEVIEWPROCEDURETRIGGERCREATEDROPALTER主要语句：《数据库与信息系统》SQL语言全文共59页，当前为第21页。THANKYOU!《数据库与信息系统》SQL语言全文共59页，当前为第22页。数据库操作语言（DML）：数据查询语句SELECT——单表查询《数据库与信息系统》SQL语言全文共59页，当前为第23页。数据操纵语言DML实现数据访问。支持对数据的查询和更新操作。常用语句（增删改查）语句功能SELECT从表中查询数据

INSERT向表中添加记录UPDATE修改表中数据DELETE从表中删除记录更新查询增删改查《数据库与信息系统》SQL语言全文共59页，当前为第24页。查询语句SELECTSELECT字段列表FROM表名[WHERE查询条件][GROUPBY分组字段]

[HAVING分组条件

][ORDERBY字段名

[ASC/DESC]];语法格式主句子句从一个或多个表中查询数据。单表查询只需要查询一个表。《数据库与信息系统》SQL语言全文共59页，当前为第25页。数据查询语句SELECT——主句《数据库与信息系统》SQL语言全文共59页，当前为第26页。SELECT语句——主句SELECT字段列表FROM表名;《数据库与信息系统》SQL语言全文共59页，当前为第27页。查询问题实例1——查询表中某些字段的信息问题：怎么知道每一位同学的姓名和性别？分析：查询student表中”StudentName”和“Gender”这两个字段的信息。《数据库与信息系统》SQL语言全文共59页，当前为第28页。SELECT语句——主句SELECT字段列表FROM表名;SELECTFROMStudentName,GenderStudent；

字段列表：表中字段多个字段之间用逗号分隔所有字段可用通配符“*”表示Student表中学生姓名和性别《数据库与信息系统》SQL语言全文共59页，当前为第29页。问题：怎么知道每一位同学的所有信息？分析：查询student表中所有字段的信息查询问题实例2——查询表中所有字段的信息《数据库与信息系统》SQL语言全文共59页，当前为第30页。SELECT语句——主句SELECT字段列表FROM表名;SELECTFROM*Student；

字段列表：表中字段多个字段之间用逗号分隔所有字段可用通配符“*”表示Student表中全部信息《数据库与信息系统》SQL语言全文共59页，当前为第31页。问题：查询课程号和课程名信息，查询结果中的字段名分别是“课程号”和“课程名”？分析：查询course表中”CourseCode”和“CourseName”这两个字段的信息给查询结果的字段名取个别名查询问题实例3——给查询结果中的字段名取个别名《数据库与信息系统》SQL语言全文共59页，当前为第32页。SELECT语句——主句SELECT字段列表FROM表名;SELECTCourseCodeAS课程号,CourseName课程名FROMcourse;字段列表：表中字段“字段名AS别名”替换列标题显示用别名显示查询结果和列标题《数据库与信息系统》SQL语言全文共59页，当前为第33页。问题：根据course表信息，怎么知道有多少种不同的学科？分析：查询course表中的SubjectCode字段？查询问题实例4——查询表中可能有重复值的字段《数据库与信息系统》SQL语言全文共59页，当前为第34页。SELECT语句——主句SELECT字段列表FROM表名；SELECTSubjectCodeFROMCourse;字段列表：字段名前加限制，优化查询结果：DISTINCT：相同字段值只显示一条记录SELECTDISTINCTSubjectCodeFROMCourse;查询Course表中学科号查询Course表中学科号（只显示一条）《数据库与信息系统》SQL语言全文共59页，当前为第35页。问题：已知数据库中courseenroll表中所有同学成绩偏低5分，根据该表信息如何查询得到正常成绩（不更改表的数据）？分析：查询courseenroll表中的Score字段，然后怎么调整呢？查询问题实例5——查询时针对字段值的算术操作《数据库与信息系统》SQL语言全文共59页，当前为第36页。SELECT语句——主句SELECT字段列表FROM表名;SELECTStudentCode,CourseCode,Score+5

AS成绩,‘成绩调整'AS说明FROMcourseenroll;字段列表：可新增字段(常量、表达式)查询courseenroll表，将成绩加5分显示，并增加一列成绩调整注：courseenroll表中数据未更改。《数据库与信息系统》SQL语言全文共59页，当前为第37页。问题：如何知道课程的平均分/最高分/最低分/总分/总人数？分析：查询courseenroll表中，对相应字段做聚合操作（求平均值/最大值/最小值/总数/计数）？查询问题实例6——查询时针对字段值的聚合运算《数据库与信息系统》SQL语言全文共59页，当前为第38页。SELECT语句——主句SELECT字段列表FROM表名；SELECTAvg(Score)

AS平均分,Max(Score)AS最高分,Min(Score)AS最低分,Count(StudentCode)AS总人次FROMcourseenroll;字段列表：可新增字段(常量、表达式)表达式中常用聚合函数函数名函数功能AVG计算某一字段的平均值COUNT统计某一字段的个数MAX查找某一字段的最大值MIN查找某一字段的最小值SUM计算某一字段的总和含有聚合函数的查询结果注：courseenroll表中数据未更改。《数据库与信息系统》SQL语言全文共59页，当前为第39页。THANKYOU!《数据库与信息系统》SQL语言全文共59页，当前为第40页。数据查询语句SELECT——子句《数据库与信息系统》SQL语言全文共59页，当前为第41页。问题举例：怎么从student表中获取所有男同学的信息？如何按照生源地，统计各地学生的平均成绩？查询学生的成绩，并按年龄从小到大排序？…分析：查询条件分组排序未解决的其他查询问题《数据库与信息系统》SQL语言全文共59页，当前为第42页。查询语句SELECTSELECT字段列表FROM表名[WHERE查询条件][GROUPBY分组字段]

[HAVING分组条件

][ORDERBY字段名

[ASC/DESC]];语法格式主句子句从一个或多个表中查询数据。

《数据库与信息系统》SQL语言全文共59页，当前为第43页。查询语句SELECT——WHERE子句SELECT字段列表FROM表名WHERE

查询条件;

查询条件：是一个关系或逻辑表达式查询条件谓词比较>、>=、<、<=、=、<>(不等于)、!>(不大于)、!<(不小于)、!=(不等于)确定范围BETWEEN…AND、NOTBETWEEN…AND确定集合IN、NOTIN、EXISTS字符匹配LIKE、NOTLIKE空值ISNULL、ISNOTNULL否定NOT逻辑运算AND、OR常用的关系和逻辑运算《数据库与信息系统》SQL语言全文共59页，当前为第44页。SELECT语句——WHERE子句的相等查询SELECT字段列表FROM表名WHERE

查询条件;

查询条件：比较运算>、>=、<、<=、=、<>(不等于)、!=(不等于)、

!>(不大于)、!<(不小于)。SELECTStudentCode,StudentName,Gender,LocationFROMstudentWHEREGender=‘女’;例如：查询Student表中所有女生的信息。SELECTStudentCode,StudentName,Gender,LocationFROMStudentWHEREGender=‘女’ANDLocation=‘上海’;例如：查询生源地为上海的女生的信息。生源地为上海的女生信息女生信息例如：查询student表中学号为1003的学生信息。SELECT*FROMstudentWHEREStudentCode=1003;AND表示两个条件都必须满足，是“且”的意思《数据库与信息系统》SQL语言全文共59页，当前为第45页。SELECT语句——WHERE子句的区间查询1SELECT字段列表FROM表名WHERE

查询条件;

SELECTStudentCode,CourseCode,ScoreFROMcourseenrollWHERECourseCode=‘C001'ANDScore>=70ANDScore<=90;例如：在courseenroll表中查询选修了课程号C001、成绩在70～90分之间的所有学生的学号、课程号及成绩信息。查询条件：确定范围[NOT]BETWEEN

值1AND

值2[不]在值1和值2之间90>=Score>=70ScoreBETWEEN70AND90等价于成绩在70～90分之间查询条件，跟在where字句后查询的字段（列表），写在select主句中表名《数据库与信息系统》SQL语言全文共59页，当前为第46页。SELECT语句——WHERE子句的区间查询2SELECT字段列表FROM表名WHERE

查询条件;

SELECTStudentCode,StudentName,BirthdayFROMStudentWHEREBirthdayNOTBETWEEN‘1970-01-01’

AND

‘2004-12-31’;例如：查询Student表中不在1970—2004出生的学生学号、姓名和生日。查询条件：确定范围[NOT]BETWEEN

值1AND

值2[不]在值1和值2之间使用Year()函数Year(Birthday)NOTBETWEEN1970AND2004不在1970—2004年之间出生《数据库与信息系统》SQL语言全文共59页，当前为第47页。SELECT语句——WHERE子句的集合查询SELECT字段列表FROM表名WHERE

查询条件;

SELECTStudentCode,StudentName,LocationFROMStudentWHERE

LocationIN('黑龙江','吉林','辽宁');例如：查询student表中来自东北三省的学生，即生源地为黑龙江、吉林、辽宁的学生信息。查询条件：IN运算确定是否在集合中等价于关系运算Location='黑龙江'ORLocation='吉林'ORLocation='辽宁'来自东三省《数据库与信息系统》SQL语言全文共59页，当前为第48页。SELECT语句——WHERE子句的模糊查询SELECT字段列表FROM表名WHERE

查询条件;查询条件：LIKE运算确定字符串模式是否匹配例如：一个人的姓名姓“王”：LIKE‘王%’；姓“王”单名：LIKE‘王_’中间字“小”：LIKE‘_小%’不姓“王”：LIKE'[^王]%'通配符说明%匹配0个或多个任意字符_匹配1个任意字符[]匹配集合中任意单个字符[^]不匹配集合中的任意单个字符LIKE模式通配符《数据库与信息系统》SQL语言全文共59页，当前为第49页。SELECT语句——WHERE子句的模糊查询举例SELECT字段列表FROM表名WHERE

查询条件;

查询条件：LIKE运算确定字符串模式是否匹配SELECTStudentCode,StudentNameFROMstudentWHEREStudentNameLIKE‘[^刘]%';例如：查询student表中不姓刘的学生的学号和姓名。例如：查询course表中课程名中包含“技术”两个字的课程号及课程名。SELECTCourseCode,CourseNameFROMcourseWHERECourseNameLIKE'%技术%';名称包含“技术”的课程不姓刘的学生SELECTStudentCode,StudentNameFROMstudentWHEREStudentNameNOTLIKE'刘%';《数据库与信息系统》SQL语言全文共59页，当前为第50页。查询语句SELECT——GROUPBY子句SELECT字段列表FROM表名GROUPBY分组字段[HAVING分组条件];分类汇总。即按分组字段把具有相同值的记录汇总计算合并成一条记录。用于分组的字段包含分组字段、汇总计算字段SELECTCourseCode,Count(StudentCode)AS选课人数,AVG(Score)AS平均分FROMcourseenrollGROUPBYCourseCode例如：统计选课表中每门课程的选课人数和平均分。HAVINGAVG(Score)>=80;或：HAVING平均分>=80;只显示平均80分以上的课程。;表名分组字段分组条件查询的字段（列表）注意：HAVING子句只能跟在GROUPBY子句后，不能单独使用。SELECT后查询的字段列表中必须包含分组字段。比如这里的CourseCode字段。《数据库与信息系统》SQL语言全文共59页，当前为第51页。查询语句SELECT——GROUPBY子句与WHERE的区别SELECT字段列表FROM表名GROUPBY分组字段[HAVING分组条件];WHERE子句和HAVING字句执行机制的区别：WHERE子句：先筛选再汇总HAVING子句：先汇总再筛选SELECTCourseCode,COUNT(StudentCode)AS选课人数,AVG(Score)AS平均分FROMcourseenrollWHERECourseCode='C001';例如：查询课程号为C001的选课人数。SELECTCourseCode,COUNT(StudentCode)AS选课人数,AVG(Score)AS平均分FROMcourseenrollGROUPBYCourseCodeHAVINGCourseCode='C001';先筛选课程号，再汇总被选出的课程的选课人数先汇总每门课程的选课人数，再根据课程号筛选如果在筛选条件中包含汇总结果，那么用WHERE子句无法实现，因为WHERE子句中不能包含聚合函数，这时只能用HAVING子句.例如：

HAVING

COUNT(StudentCode)>3《数据库与信息系统》SQL语言全文共59页，当前为第52页。查询语句SELECT——GROUPBY子句中有多个分组字段SELECT字段列表FROM表名GROUPBY分组字段[HAVING分组条件];GROUPBY后有多个分组字段，则表示多次分组。SELECTLocation,Gender,COUNT(StudentCode)AS学生人数FROMStudentGROUPBYLocation,Gender;例如：统计各生源地男、女学生的人数。分组字段查询的字段（列表）《数据库与信息系统》SQL语言全文共59页，当前为第53页。查询语句SELECT——使用GROUPBY子句的注意事项SELECT字段列表FROM表名GROUPBY分组字段[HAVING

分组条件];注意：分组字段必须出现在SELECT后的字段列表中（可以是字段，也可在聚合函数中！）HAVING字句必须和GROUP

BY字句一起使用，不能单独使用。