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

1、SQL语言与可编程对象4目 录常用SQL语句4.1可编程对象4.2SQL语言概述SQL语言概述SQL是结构化查询语言（Structure Query Language）的简称，是关系数据库操作语言的国际标准。MySQL所使用的SQL语言遵从ANSI-92国际标准。SQL语言的特点非过程化的描述性语言。语言简洁、易学易用。使用方式联机交互独立使用嵌入到其他宿主语言输入SQL语句操作结果在Navicat for MySQL中打开交互式操作窗口1.点击工具栏中的“查询”按钮2.点击“新建查询”3.在“查询编辑器”中输入命令，点击“运行”。SQL语言的主要语句类别维护数据结构。支持建立、修改或删除数据

2、库及数据对象操作。DDL(Data Definition Language) 数据库定义语言数据访问。支持对数据库中数据的查询、插入、修改和删除等操作。DML(Data Manipulation Language) 数据库操纵语言数据库监视。支持对数据对象的授权、安全控制等操作。DCL(Data Control Language) 数据库控制语言操作对象是数据库或者对象（表、视图等）操作对象是表中的数据SQL语言的书写规则不区分字母大小写一条语句可写在一行或多行上可用注释增强语句可读性 单行注释（-）,也可以用# 多行注释（/* */）例如：/*打开e_learning数据库。当第一次访问某数

3、据库时，需要使用USE语句打开数据库，否则其他SQL语句不能执行*/USE e_learning; 【例】SELECT StudentCode, StudentName FROM student; - 查询学生表中学生的学号和姓名语句中的单词可以是关键词，也可以是标识符关键字标识符语句以“；”结尾注意-之后必须有空格！THANK YOU!数据库定义语言（DDL）简介数据库定义语言DDL维护数据结构。支持建立、修改或删除数据库及数据对象的操作。DATABASETABLEVIEWPROCEDURETRIGGERCREATE DROPALTER主要语句：（动作+操作对象类型+对象名）创建删除更改数据

4、库表视图存储过程触发器object_nameDDL语句举例创建数据库CREATE DATABASE 数据库名 DEFAULT CHARACTER SET 字符集，DEFAULT COLLATE 校对规则;CREATE DATABASE pet; - 创建一个名为pet的数据库，采用默认的字符集和排序规则CREATE DATABASE plant CHARACTER SET utf8; /*创建一个名为plant的数据库，设置其字符集为utf8 */ 表示可选项DDL语句举例创建数据库命令演示DDL语句举例使用、删除数据库USE 数据库名; - 打开该数据库作为当前可操作的数据库USE pet;

5、 - 将pet设定为当前数据库DROP DATABASE 数据库名; - 删除数据库DROP DATABASE plant; - 删除数据库plantDDL语句举例创建数据表CREATE TABLE 表名(字段名1 字段数据类型 字段约束,字段名2 字段数据类型 字段约束, Constraint 约束说明);USE pet; /*将数据库pet设定为当前数据库*/CREATE TABLE cat ( CatCode int(4) NOT NULL AUTO_INCREMENT, /*不可取空值，创建自增约束*/ CatName varchar(16) NOT NULL, /*不可取空值*/ G

6、ender char(1) NOT NULL DEFAULT 母, /*Gender默认值为母*/ Birthday date NOT NULL, Photo varchar(30) DEFAULT NULL, Introduction text, RegTime timestamp DEFAULT CURRENT_TIMESTAMP, /*默认当前时间*/ PRIMARY KEY (CatCode) /*设置CatCode为主键*/);字段名的单引号可以去掉字段名数据类型约束CatCodeint(4)非空, 自增，主键CatNamevarchar(16)非空Genderchar(1)非空，默

7、认母Birthdaydate非空Photovarchar(30)默认为空IntroductiontextRegTimetimestamp默认CURRENT_TIMESTAMP描述字段的每一行末尾是逗号语句末尾是分号DDL语句举例创建索引CREATE UNIQUE|FULLTEXT|SPATIAL INDEX 索引名ON 数据表名(字段 ASC|DESC); - ASC升序，DESC降序CREATE UNIQUE INDEX IX_Name ON cat (CatName DESC);-默认为升序ASC例：在cat表的CatName字段上建立唯一索引IX_Name，按名字降序排列字段名数据类型约

8、束CatCodeint(4)非空, 自增，主键CatNamevarchar(16)非空Genderchar(1)非空，默认母Birthdaydate非空Photovarchar(30)默认为空IntroductiontextRegTimetimestamp默认CURRENT_TIMESTAMPcat表表名要建立的索引字段名DDL语句举例删除数据表、索引DROP TABLE 数据表名;DROP TABLE cat;/*删除cat表*/DROP INDEX 索引名 on 数据表;DROP INDEX IX_Name on cat; /*删除cat表IX_Name索引*/DDL语句举例修改数据对象A

9、LTER 数据对象 对象名;ALTER UNIQUE INDEX IX_NameON cat (CatName ASC);Alter语句与Create语句格式完全相同，是在已经创建的数据对象做修改。/*修改索引IX_Name：按姓名升序排列*/转储数据定义SQL文件生成脚本xx.sql执行脚本在Navicat for MySQL中创建各种数据库对象，实质上是以可视化方式生成DDL语句，这些语句可以保存到一个SQL脚本文件中，以后运行该文件可重建数据库对象。数据库对象数据库对象SQL脚本生成与使用举例例： （1） 转储student表的SQL文件student.sql； （2）创建一个新的数据库

10、example； （3）在example数据库中运行文件student.sql建立student表。 选中表，点击右键新建数据库后，选中该数据库，右键运行*.sql文件转储单个表转储整个数据库步骤类似，选中数据库后转储数据库定义语言DDL维护数据结构。支持建立、修改或删除数据库及数据对象的操作。DATABASETABLEVIEWPROCEDURETRIGGERCREATE DROPALTER主要语句：THANK YOU!数据库操作语言（DML）：数据查询语句SELECT 单表查询数据操纵语言DML实现数据访问。支持对数据的查询和更新操作。常用语句（增删改查）语句功能SELECT从表中查询数据

11、INSERT向表中添加记录UPDATE修改表中数据DELETE从表中删除记录更新查询增删改查查询语句SELECTSELECT 字段列表 FROM 表名 WHERE 查询条件 GROUP BY 分组字段 HAVING 分组条件 ORDER BY 字段名 ASC/DESC ;语法格式主句子句从一个或多个表中查询数据。单表查询只需要查询一个表。数据查询语句SELECT 主句SELECT语句主句SELECT 字段列表 FROM 表名;查询问题实例1查询表中某些字段的信息问题：怎么知道每一位同学的姓名和性别？分析：查询student表中”StudentName”和“Gender”这两个字段的信息。SEL

12、ECT语句主句SELECT 字段列表 FROM 表名;SELECT FROMStudentName, Gender Student； 字段列表：表中字段多个字段之间用逗号分隔所有字段可用通配符“*”表示Student表中学生姓名和性别问题：怎么知道每一位同学的所有信息？分析：查询student表中所有字段的信息查询问题实例2查询表中所有字段的信息SELECT语句主句SELECT 字段列表 FROM 表名;SELECT FROM*Student； 字段列表：表中字段多个字段之间用逗号分隔所有字段可用通配符“*”表示Student表中全部信息问题：查询课程号和课程名信息，查询结果中的字段名分别是“

13、课程号”和“课程名”？分析：查询course表中”CourseCode”和“CourseName”这两个字段的信息给查询结果的字段名取个别名查询问题实例3给查询结果中的字段名取个别名SELECT语句主句SELECT 字段列表 FROM 表名;SELECT CourseCode AS课程号, CourseName 课程名 FROM course;字段列表：表中字段“字段名 AS 别名”替换列标题显示用别名显示查询结果和列标题问题：根据course表信息，怎么知道有多少种不同的学科？分析：查询course表中的SubjectCode字段？查询问题实例4查询表中可能有重复值的字段SELECT语句主句

14、SELECT 字段列表 FROM 表名；SELECT SubjectCode FROM Course; 字段列表：字段名前加限制，优化查询结果：DISTINCT：相同字段值只显示一条记录SELECT DISTINCT SubjectCode FROM Course; 查询Course表中学科号查询Course表中学科号（只显示一条）问题：已知数据库中courseenroll表中所有同学成绩偏低5分，根据该表信息如何查询得到正常成绩（不更改表的数据）？分析：查询courseenroll表中的Score字段，然后怎么调整呢？查询问题实例5查询时针对字段值的算术操作SELECT语句主句SELECT

15、字段列表 FROM 表名;SELECT StudentCode, CourseCode, Score+5 AS 成绩, 成绩调整 AS 说明FROM courseenroll;字段列表：可新增字段(常量、表达式)查询courseenroll表，将成绩加5分显示，并增加一列成绩调整注：courseenroll表中数据未更改。问题：如何知道课程的平均分/最高分/最低分/总分/总人数？分析：查询courseenroll表中，对相应字段做聚合操作（求平均值/最大值/最小值/总数/计数）？查询问题实例6查询时针对字段值的聚合运算SELECT语句主句SELECT 字段列表 FROM 表名；SELECT A

16、vg(Score) AS 平均分,Max (Score) AS 最高分,Min(Score) AS 最低分,Count(StudentCode) AS 总人次FROM courseenroll;字段列表：可新增字段(常量、表达式)表达式中常用聚合函数函数名函数功能AVG计算某一字段的平均值COUNT统计某一字段的个数MAX查找某一字段的最大值MIN查找某一字段的最小值SUM计算某一字段的总和含有聚合函数的查询结果注：courseenroll表中数据未更改。THANK YOU!数据查询语句SELECT 子句问题举例：怎么从student表中获取所有男同学的信息？如何按照生源地，统计各地学生的平均

17、成绩？查询学生的成绩，并按年龄从小到大排序？分析：查询条件分组排序未解决的其他查询问题查询语句SELECTSELECT 字段列表 FROM 表名 WHERE 查询条件 GROUP BY 分组字段 HAVING 分组条件 ORDER BY 字段名 ASC/DESC ;语法格式主句子句从一个或多个表中查询数据。 查询语句SELECTWHERE子句SELECT 字段列表 FROM 表名WHERE 查询条件; 查询条件：是一个关系或逻辑表达式查询条件谓词比较、=、=、=、(不等于)、!(不大于)、!、=、=、=、(不等于)、!=(不等于)、 !(不大于)、!= 70 AND Score=Score=7

18、0Score BETWEEN 70 AND 90等价于成绩在7090分之间查询条件，跟在where字句后查询的字段（列表），写在select主句中表名SELECT语句WHERE子句的区间查询2SELECT 字段列表 FROM 表名WHERE 查询条件; SELECT StudentCode, StudentName, Birthday FROM StudentWHERE Birthday NOT BETWEEN 1970-01-01 AND 2004-12-31;例如：查询Student表中不在19702004出生的学生学号、姓名和生日。查询条件：确定范围NOT BETWEEN 值1 AND

19、值2不在值1和值2之间使用Year()函数Year(Birthday) NOT BETWEEN 1970 AND 2004不在19702004年之间出生SELECT语句WHERE子句的集合查询SELECT 字段列表 FROM 表名WHERE 查询条件; SELECT StudentCode, StudentName, Location FROM StudentWHERE Location IN (黑龙江,吉林,辽宁);例如：查询student表中来自东北三省的学生，即生源地为黑龙江、吉林、辽宁的学生信息。查询条件：IN运算确定是否在集合中等价于关系运算Location =黑龙江 OR Loca

20、tion =吉林 OR Location =辽宁来自东三省SELECT语句WHERE子句的模糊查询SELECT 字段列表 FROM 表名WHERE 查询条件;查询条件：LIKE运算确定字符串模式是否匹配例如：一个人的姓名姓“王”： LIKE 王%；姓“王”单名： LIKE 王_中间字“小”： LIKE _小%不姓“王”： LIKE 王%通配符说明%匹配0个或多个任意字符_匹配1个任意字符 匹配集合中任意单个字符不匹配集合中的任意单个字符LIKE模式通配符SELECT语句WHERE子句的模糊查询举例SELECT 字段列表 FROM 表名WHERE 查询条件; 查询条件：LIKE运算确定字符串模式

21、是否匹配SELECT StudentCode, StudentName FROM studentWHERE StudentName LIKE 刘%;例如：查询student表中不姓刘的学生的学号和姓名。例如：查询course表中课程名中包含“技术”两个字的课程号及课程名。 SELECT CourseCode, CourseName FROM courseWHERE CourseName LIKE %技术%;名称包含“技术”的课程不姓刘的学生SELECT StudentCode, StudentName FROM studentWHERE StudentName NOT LIKE 刘%;查询语句

22、SELECTGROUP BY子句SELECT 字段列表 FROM 表名GROUP BY 分组字段 HAVING 分组条件 ; 分类汇总。即按分组字段把具有相同值的记录汇总计算合并成一条记录。用于分组的字段包含分组字段、汇总计算字段SELECT CourseCode, Count(StudentCode) AS 选课人数, AVG(Score) AS 平均分 FROM courseenrollGROUP BY CourseCode例如：统计选课表中每门课程的选课人数和平均分。HAVING AVG(Score) =80; 或：HAVING 平均分=80;只显示平均80分以上的课程。;表名分组字段分

23、组条件查询的字段（列表）注意：HAVING子句只能跟在GROUP BY子句后，不能单独使用。SELECT后查询的字段列表中必须包含分组字段。比如这里的CourseCode字段。查询语句SELECTGROUP BY子句与WHERE的区别SELECT 字段列表 FROM 表名GROUP BY 分组字段 HAVING 分组条件 ; WHERE子句和HAVING字句执行机制的区别：WHERE子句：先筛选再汇总HAVING子句：先汇总再筛选SELECT CourseCode, COUNT(StudentCode) AS 选课人数, AVG(Score) AS 平均分FROM courseenrollWH

24、ERE CourseCode=C001;例如：查询课程号为C001的选课人数。SELECT CourseCode, COUNT(StudentCode) AS 选课人数, AVG(Score) AS 平均分FROM courseenrollGROUP BY CourseCodeHAVING CourseCode=C001;先筛选课程号，再汇总被选出的课程的选课人数先汇总每门课程的选课人数，再根据课程号筛选如果在筛选条件中包含汇总结果，那么用WHERE子句无法实现，因为WHERE子句中不能包含聚合函数，这时只能用HAVING子句.例如： HAVING COUNT(StudentCode)3查询语

25、句SELECTGROUP BY子句中有多个分组字段SELECT 字段列表 FROM 表名GROUP BY 分组字段 HAVING 分组条件 ; GROUP BY后有多个分组字段，则表示多次分组。SELECT Location, Gender, COUNT(StudentCode) AS 学生人数FROM StudentGROUP BY Location, Gender;例如：统计各生源地男、女学生的人数。分组字段查询的字段（列表）查询语句SELECT使用GROUP BY子句的注意事项SELECT 字段列表 FROM 表名GROUP BY 分组字段 HAVING 分组条件 ; 注意：分组字段必须出现在SELECT后的字段列表中（可以是字段，也可在聚合函数中！）HAVING字句必须和GROUP BY字句一起使用，不能单独使用。HAVING字句和WHERE字句的实现机制的差异查询语句SELECTORDER BY子句SELECT 字段列表 FROM 表名ORDER BY 字段 ASC/DESC;按特定字段值为查询结果排序。排序依据SELECT S