第四讲sql语言1ddl及建立数据库

数据库系统原理与设计

第4讲SQL语言-1主讲：顾曦电话：Email：学习目标要求掌握对数据库的基本操作，并了解数据库管理系统的基本功能要求熟练掌握基本的SQL语法，并运用SQL语句完成对数据库的操作16:562学习方法结合课堂讲授的知识，强化上机，以便加深理解。重点：3.2、3.3、3.4、3.5、3.8和3.9节难点：3.3、3.4和3.5节16:563主要内容SQL概述数据定义数据更新1简单查询

连接查询嵌套子查询

聚合查询

集合运算

SQL查询一般格式数据更新2

视图（第七讲）

16:5641、SQL概述

1.0简述SQL语言于1974年由Boyce等提出，并于1975～1979年在IBM公司研制的SystemR数据库管理系统上实现，现已成为国际标准。SQL(StructuredQueryLanguage，结构化查询语言)是关系数据库的标准语言。几乎所有的关系型数据库管理系统均采用SQL语言标准。很多数据库厂商都对SQL语句进行了再开发和扩展。微软SqlServer:T-SQLOracle:PL/SQL标准SQL命令查询:SELECT插入:INSERT更新:UPDATE

删除:DELETE创建:CREATE对象删除:DROP16:5671.1SQL的发展SQL-86：第一个SQL标准，由美国国家标准局(AmericanNationalStandardInstitute，简称ANSI)公布1987年国际标准化组织(InternationalOrganizationforStandardization，简称ISO)通过。该标准也称为SQL-116:568SQL的发展SQL-92：在1992年，由ISO和ANSI对SQL-86进行重新修订，该标准也称为SQL-2SQL-99：在1999年，该版本在SQL-2的基础上，扩展了诸多功能，包括递归、触发、面向对象技术等。该标准也称为SQL-3

SQL-2003：该标准是最新的标准，也称SQL-4，于2003年发布16:569SQL标准

标准大致页数发布日期SQL/861986年SQL/89(FIPS127-1)120页1989年SQL/92622页1992年SQL991700页1999年SQL20032003年SQL/Framework-BNFGrammarforISO/IEC9075-1:2003

SQL/Foundation-BNFGrammarforISO/IEC9075-2:2003

16:5610SQL语言的组成（4部分）数据定义语言DDL(DataDefinitionLanguage)定义数据库的逻辑结构，包括数据库、基本表、视图和索引等，扩展DDL还支持存储过程、函数、对象、触发器等的定义DDL包括3类语言，即定义、修改和删除数据操纵语言DML(DataManipuplationLanguage)对数据库的数据进行检索和更新，其中更新操作包括插入、删除和修改数据数据控制语言DCL(DataControlLanguage)对数据库的对象进行授权、用户维护(包括创建、修改和删除)、完整性规则定义和事务定义等其它主要是嵌入式SQL语言和动态SQL语言的定义，规定了SQL语言在宿主语言中使用的规则扩展SQL还包括数据库数据的重新组织、备份与恢复等功能1.2SQL特点

综合统一集数据定义语言DDL、数据操纵语言DML、数据控制语言DCL的功能于一体。高度非过程化没有条件、循环等控制语句。某些商业数据库进行了过程化扩充,比如PL/SQL。面向集合的操作方式采用集合操作方式，其操作对象、查找结果都是元组的集合。同一种语法结构提供两种使用方式SQL语言既是自含式语言，又是嵌入式语言，其语法结构基本一致。SQL功能极强，完成核心功能只用了9个动词。语言简洁，易学易用SQL支持的三级模式结构SQL语言支持三级模式结构，外模式对应视图和部分基本表，模式对应基本表，内模式对应存储文件SQL视图1视图2基本表1基本表2基本表3基本表4存储文件1存储文件2外模式模式内模式SQL支持的三级模式结构基本表数据库中独立存在的表称为基本表，在SQL中一个关系对应一个基本表一个(或多个)基本表对应一个存储文件一个表可以带若干索引索引存放在存储文件中视图指从一个或几个基本表(或视图)导出的表，是虚表。只存放视图的定义而不存放对应数据查询表指查询结果对应的表存储文件指数据库中存放关系的物理文件16:5615本课程使用的数据库-ScoreDB（p77）ScoreDB的模式导航图16:5620课堂演示ScoreDB数据库安装前的设置16:56211、下载

mySQL的安装、配置下载ZIP版2、安装解压到安装目录启动服务器cmd->安装目录mysqld--console测试客户端cmd->安装目录mysqltest16:56243、服务的启动与关闭（4种方式）1）命令行启动和停止启动：mysqld–console停止：mysqladmin–uroot–pshutdown;启动命令行客户端：mysql–uroot-p2）设置为Windows服务（不用）mysqld–install或netstartmysql删除Windows服务mysqld–remove或scdeletmysql3）使用批处理启动、停止服务器，启动命令行客户端启动服务器：

新建文本文件：startDB.bat

编辑文本：关闭服务器：

新建文本文件：shutDownDB.bat

编辑文本：16:5625“callC:\安装目录\bin\mysqld–console”“callC:\安装目录4\bin\mysqladmin-uroot-ppassshutdown”启动CMD客户端

新建文本文件：dbClient.bat

编辑文本：16:5626callC:\安装目录\bin\mysql-uroot-ppass4）Path路径配置目的：配置mysql/bin目录入path变量中，可直接调用该目录下的命令。16:5627在Path变量中加上“mysql安装目录/bin;”16:562816:56294、配置1）my.ini配置[client]port =3306socket =/tmp/mysql.sockdefault-character-set=utf8......[mysqld]port =3306socket =/tmp/mysql.sockbasedir="D:/ProgramFiles/mysql-5.5.27-win32"#datadir=character_set_server=utf8目的：配置mySql启动参数，设置客户端和服务端的字符集。16:56302）配置客户端和连接字符集目的：减少乱码。查看数据库字符集更改数据库字符集showvariableslike'character%';setcharacter_set_client=utf8;setcharacter_set_connection=utf8;安装、配置Navicat

mysql管理客户端

3132用Navicat新建数据库33建表34也可运行SQL建表（library.sql）35返回注意：该方法有乱码风险2、SQL数据定义语言 2.1数据库的定义 2.2基本表的定义 2.3索引的定义16:5636SQL数据定义语言16:56372.1数据库的定义数据库保存了企业所有的数据，以及相关的一些控制信息，如安全性和完整性约束、关系的存储路径等数据库包含了基本表、视图、索引以及约束等对象，在定义这些对象之前，必须首先定义数据库，然后在数据库中定义所有的对象16:5638数据库作为一个整体存放在外存的物理文件中物理文件有两种：数据文件：存放数据库中的对象数据；日志文件：存放用于恢复数据库的企业冗余数据物理文件可以是多个，可以将一个或若干个物理文件设置为一个逻辑设备数据库可以有多个逻辑设备，必须在定义数据库时进行定义数据库的对象存放在逻辑设备上，由逻辑设备与物理文件进行联系，从而实现数据库的逻辑模式与存储模式的独立16:56391）数据库的创建一个数据库创建在物理介质的一个或多个文件上，它预先分配了将要被数据库和事务日志所使用的物理存储空间存储数据的文件叫做数据文件(datafile)存储日志的文件叫做日志文件(logfile)创建一个新的数据库时，仅创建了一个空壳，必须在这个空壳中创建对象(如表等)，才能使用这个数据库当创建了一个数据库，与该数据库相关的描述信息会存入到系统的数据字典中在定义数据库的时候，必须定义数据库的名字、逻辑设备名和物理文件名16:5640定义数据库的语法

CREATEDATABASE<databaseName>[ON[[PRIMARY]<filespec>[,...n]]--定义主逻辑设备的数据文件[,<filegroup>[,...n]]]

--定义用户逻辑设备的数据文件[LOGON{<filespec>[,...n]}]--定义数据库日志的数据文件

建立学生成绩数据库ScoreDB演示CREATEDATABASEScoreDB

;16:56412)数据库的修改修改数据库在运行过程中，可以依据数据量的大小进行修改修改数据库操作的语法为：

ALTERDATABASE<databaseName>3）数据库的删除删除数据库操作的语法为：

DROPDATABASE<databaseName>删除数据库时，系统会同时从系统的数据字典中将该数据库的描述一起删除16:5643建立学生成绩数据库ScoreDBDROPDATABASEifexistsScoreDB;CREATEDATABASEScoreDB;USEScoreDB;2.2基本表的定义创建数据库后，就可在数据库中建立基本表。SQL中域的概念用数据类型来实现定义表的属性时需要指明其数据类型及长度选用哪种数据类型取值范围要做哪些运算16:5644基本数据类型整型：int(4B)，smallint(2B)，tinyint(1B)；实型：float，real(4B)，decimal(p,n)，numeric(p,n)；字符型：char(n)，varchar(n)，text；2进制型：binary(n)，varbinary(n)，image；逻辑形：bit，只能取0和1，不允许为空；货币型：money(8B,4位小数)，smallmoney(4B,2位小数)；时间型：datetime(4B,从1753.1.1开始)，smalldatetime(4B,从1900.1.1开始)其中：image为存储图象的数据类型，text存放大文本数据16:56451)创建基本表CREATETABLE<表名>

（<列名><数据类型>[<列级完整性约束条件>][，<列名><数据类型>[<列级完整性约束条件>]]…[，<表级完整性约束条件>]）；如果完整性约束条件涉及到该表的多个属性列，则必须定义在表级上，否则既可以定义在列级也可以定义在表级。16:5646建立ScroeDB的4张表CREATETABLEClass(

/*定义班级表Class*/

classNo char(6) NOTNULL,/*班级号*/

classNamevarchar(30)NOTNULL,/*班级名*/

institutevarchar(30)NOTNULL,/*所属学院*/

gradesmallintdefault0NOTNULL,/*年级*/

classNumtinyint NULL,/*班级人数*/

CONSTRAINTClassPKPRIMARYKEY(classNo))属性定义主码/**/注释约束：缺省值数据类型表名课程表CourseCREATETABLECourse(/*定义课程表Course*/

courseNo char(3) NOTNULL,/*课程号*/

courseNamevarchar(30)NOTNULL,/*课程名*/

creditHournumeric(1)default0NOTNULL,/*学分*/

courseHourtinyintdefault0NOTNULL,/*课时数*/

priorCoursechar(3) NULL,/*先修课程*/

CONSTRAINTCoursePKPRIMARYKEY(courseNo),

FOREIGNKEY(priorCourse)REFERENCES

Course(courseNo))

定义外码priorCourse，被参照表是Course被参照列是courseNo学生表StudentCREATETABLEStudent(

studentNochar(7) NOTNULL, /*学号*/

studentNamevarchar(20)NOTNULL, /*姓名*/

sex char(2)NULL, /*性别*/

birthdaydatetime NULL, /*出生日期*/

nativevarchar(20)NULL, /*籍贯*/

nationvarchar(30)default'汉族'NULL,/*民族*/

classNochar(6) NULL, /*所属班级*/

CONSTRAINTStudentPKPRIMARYKEY(studentNo),

CONSTRAINTStudentFKFOREIGNKEY(classNo)

REFERENCES

Class(classNo))定义成绩表ScoreCREATETABLEScore(

studentNochar(7) NOTNULL, /*学号*/

courseNochar(3) NOTNULL, /*课程号*/

scorenumeric(5,1)default0NOTNULL,/*成绩*/

CHECK(scoreBETWEEN0.0AND100.0),/*自定义约束*//*主码由两个属性构成，必须作为表级完整性约束进行定义*/

CONSTRAINTScorePKPRIMARYKEY(studentNo,courseNo),CONSTRAINTScoreFK1FOREIGNKEY(studentNo)

REFERENCES

student(studentNo),

CONSTRAINTScoreFK2FOREIGNKEY(courseNo)

REFERENCES

course(courseNo))16:565016:5651scoreDB模式导航图基本表的修改（ALTERTABLE）修改表的结构，如扩充列等。修改基本表操作的语法为：ALTERTABLE<表名>[ADD<新列名><数据类型>[完整性约束]][DROP<完整性约束名>][ALTERCOLUMN<列名><数据类型>]；16:5652基本表的修改增加列（新增一列的值为空值）：ALTERTABLE<tableName>

ADD<columnName><dataType>增加约束：ALTERTABLE<tableName>

ADDCONSTRAINT<constraintName>删除约束：ALTERTABLE<tableName>

DROP<constraintName>修改列的数据类型：ALTERTABLE<tableName>ALTERCOLUMN<columnName><newDataType>16:5653基本表的删除

DROPTABLE<tableName>[RESTRICT|CASCADE]<tableName>为被删除的表名RESTRICT：该表的删除有限制条件即该表不能有视图、触发器以及被其它表所引用（如检查约束CHECK，外码约束FOREIGNKEY），该项为缺省项。CASCADE：该表的删除没有限制条件在删除基本表的同时，也删除建立在该表上的所有索引、完整性规则、触发器和视图。删除表时，系统会同时从系统的数据字典中将该表的描述一起删除。注意：mySQL、SQLServer都不支持[RESTRICT|CASCADE]选项16:56542.3索引的定义索引是加快数据检索的一种工具一张表可以建立多个索引，可从不同的角度加快查询速度；如果索引建立得较多，会给数据维护带来较大的系统开销。索引是由<搜索码值，指针>的记录构成索引逻辑上按照搜索码值进行排序，但不改变表中记录的物理顺序；索引和基本表分别存储。数据库的索引一般按照B+树结构来组织，也有Hash索引和位图索引等。索引相关的描述信息会保存到数据字典中。16:5655例：在班级表中按所属学院建立的索引InstituteIdx：16:5656索引的类型聚集索引每张表仅能建立一个聚集索引聚集索引是按搜索码值的某种顺序(升序/降序)来重新组织表记录聚集索引可以极大地提高查询速度聚集索引给数据的修改带来困难。一般建立了聚集索引的表不进行更新操作，仅执行查询操作，这在数据仓库中使用得较多。非聚集索引：是普通索引，一张表可以建立多个普通索引。16:5657建立索引操作的语法

CREATE[UNIQUE][CLUSTERED|NONCLUSTERED]INDEX<indexName>

ON<tableName>(<columnName1>[ASC|DESC],<columnName2>[ASC|DESC],…)

[ON<filegroupName>]16:5658其中：UNIQUE：表示建立唯一索引；CLUSTERED|NONCLUSTERED：表示建立聚集或非聚集索引，默认为非聚集索引；<indexName>：索引的名称，索引是数据库中的对象，因此在一个数据库中必须唯一；(<columnName1>[ASC|DESC],<columnName2>[ASC|DESC],…)：可为多个属性建立索引[ASC|DESC]：按升序或降序建立索引，默认为升序；ON<filegroupName>：指定索引文件存放在哪个逻辑设备上，该逻辑设备必须是在创建数据库时定义的，或加入到数据库中的逻辑设备。缺省该项时自动将对象建立在主逻辑设备上。16:5659[例3.72]在班级表中按所属学院建立一个非聚集索引InstituteIdxCREATE[NONCLUSTERED]INDEXInstituteIdx

ONClass(institute)[例3.73]在学生表中，首先按班级编号的升序，然后按出生日期的降序建立一个非聚集索引ClassBirthIdx。CREATEINDEXClassBirthIdx

ON

Student(classNo,birthdayDESC)16:5660Mysql中查看表“tbName”已建立的索引的命令：mysql>showindexfromtbName;索引的删除索引一旦建立，用户不需要管理它，由系统自动维护；可删除那些不经常使用的索引；删除索引操作的语法为：DROPINDEX<indexName>

[fromtableName]删除索引时，系统会同时从系统的数据字典中将该索引的描述一起删除。[例3.74]删除InstituteIdx索引。DROPINDEXInstituteIdxfromclass16:56613、SQL数据更新语言

3.1插入数据 3.2删除数据 3.3修改数据16:56623.1插入数据两种方式：插入单条记录，插入子查询的结果，一次插入多条记录。（第六讲）插入一条元组：INSERTINTO<tableName>

[(<columnName1>[,<columnName2>...])]VALUES(<value1>[,<value2>...]])columnName1...：指明被插入的元组按指定的属性名称和顺序插入。该项可以省略。若省略，表示必须按照<tableName>表的属性个数和属性顺序插入新元组。16:5663示例：ScoreDB导入数据数据文件：scoredb数据文件.sql在命令行下，用insert命令导入（navicat有可能出问题）：或（暂不推荐）使用“source命令”(与文件编码有关，可能出现乱码)；mysql>sourceX.sql；16:5664[例3.75]将一个新学生元组（‘0700006’,‘李相东’,‘男’,‘1991-10-2100:00’,‘云南’,‘撒呢族’,‘CS0701’）插入到学生表Student中。INSERTINTOStudent VALUES('0700006','李相东','男','1991-10-2100:00','云南','撒呢族','CS0701')表名Student后没有指定列名，表示按照Student表定义的属性列的个数和顺序将新元组插入到Student表中。16:5665

[例3.76]将一个新学生元组（姓名：章李立，出生日期：1991-10-1200:00，学号：0700007）插入到学生表Student中。

INSERTINTOStudent(studentName,birthday,studentNo)

VALUES('章李立','1991-10-1200:00','0700007')按照指定列的顺序和列的个数向学生表Student插入一个新元组没有列出的属性列自动取空值；插入新元组时，数据的组织可不按照表结构定义的属性个数和顺序进行插入。16:56663.2删除数据删除命令：DELETEFROM<tableName>[WHERE<predicate>]<tableName>：要删除记录的表名；[WHERE<predicate>]：指出被删除的记录所满足的条件该项可以省略，若省略则表示删除表中的所有记录；WHERE子句中可以包含子查询。16:5667[例3.79]删除学号为0800001同学的选课记录。16:5668DELETEFROMScore

WHERE

studentNo='0800001'3.9.3修改数据UPDATE<tableName>

SET<columnName1>=<expr1>[,<columnName2>=<expr2>...][FROM<tableName1|queryName1|viewName1>[AS][<aliasName1>][,<tableName2|queryName2|viewName2>[AS][<aliasName2>]...][WHERE<predicate>]16:5669其中：<tableName>：要进行修改记录的表名；SET<columnName1>=<expr1>[,<columnName2>=<expr2>...]:用表达式的值替代属性列的值一次可以修改元组的多个属性列，之间以逗号分隔；[WHERE<predicate>]：指出被修改的记录所满足的条件该项可以省略，若省略，表示修改表中的所有记录；WHERE子句中可以包含子查询。16:5670[例3.84]将学号为0800001同学的出生日期修改为1992年5月6日出生，籍贯修改为福州。16:5671UPDATE

Student

SET

birthday='1992-5-600:00',native='福州'

WHERE

studentNo='0800001'课堂练习16:56721、配置mysql，熟悉以下操作：启动服务器停止服务器登录客户端查看和配置字符集2、安装navicat3、使用命令行客户端建立数据库scoreDB4、在数据库ScoreDB中创建表student、class、score、course。16:5673附1：安装mysql解压到安装目录启动服务器cmd->安装目录mysqld--console运行客户端cmd->安装目录mysqltest3、配置3.1、Path路径配置目的：全局变量，可直接调用。16:5675在Path变量中加上“mysql安装目录/bin;”D:\ProgramFiles\mysql-5.5.27-win32\bin;16:567616:56771.2、my.ini配置目的：配置mySql启动参数，设置客户端和服务端的字符集。Mysql根目录新建my.ini，加入如下（参考my.ini）[client]port =3306socket =/tmp/mysql.sockdefault-character-set=utf8[mysqld]port =3306socket =/tmp/mysql.sock#basedir=#datadir=character_set_server=utf816:56781.3、服务启动方式配置1）命令行启动和停止启动：mysqld–console停止：mysqladmin–uroot–pshutdown;2）设置为Windows服务（不用）mysqld–install或netstartmysql删除Windows服务mysqld–remove或scdeletmysql16:56791.4、配置客户端和连接字符集目的：减少乱码。1)查看数据库字符集showvariableslike'character%';2）更改数据库字符集setcharacter_set_client=utf8;setcharacter_set_connection=utf8;注：除character_set_filesystem、character_set_system和character_sets_dir，其他变量均设置为utf8。安装、配置Navicat

mysql管理客户端

8081建表82附2建立学生成绩数据库ScoreDB16:5683DROPDATABASEifexistsScoreDB;CREATEDATABASEScoreDB;USEScoreDB;附3建立ScroeDB的4张表CREATETABLEClass(

/*定义班级表Class*/

classNo char(6) NOTNULL,/*班级号*/

classNamevarchar(30)NOTNULL,/*班级名*/

institutevarchar(30)NOTNULL,/*所属学院*/

gradesmallintdefault0NOTNULL,/*年级*/

classNumtinyint NULL,/*班级人数*/

CONSTRAINTClassPKPRIMARYKEY(classNo))课程表CourseCREATETABLECourse(/*定义课程表Course*/

courseNo char(3) NOTNULL,/*课程号*/

courseNamevarchar(30)NOTNULL,/*课程名*/

creditHournumeric(1)default0NOTNULL,/*学分*/

courseHourtinyintdefault0NOTNULL,/*课时数*/

priorCoursechar(3) NULL,/*先修课程*/

CONSTRAINTCoursePKPRIMARYKEY(courseNo),

FOREIGNKEY(priorCourse)REFERENCES

Course(courseNo))学生表StudentCREATETABLEStudent(

studentNochar(7) NOTNULL, /*学号*/

studentNamev