数据库上课第四讲SQL语言_1(DDL及建立数据库)

1、机械自动化学院机械自动化学院20152015主讲：主讲： 顾顾 曦曦 电话：电话：1569718107915697181079EmailEmail：学习目标要求掌握对数据库的基本操作，并了解数据库管理系统的基本功能要求熟练掌握基本的SQL语法，并运用SQL语句完成对数据库的操作*2学习方法结合课堂讲授的知识，强化上机强化上机，以便加深理解。重点：3.2、3.3、3.4、3.5、3.8和3.9节难点：3.3、3.4和3.5节*3主要内容SQL概述数据定义数据定义数据更新数据更新1简单查询简单查询 连接查询连接查询嵌套子查询嵌套子查询 聚合查询聚合查询 集合运算集合运算 SQL查询一般格式查询一般

2、格式数据更新数据更新2 视图（第七讲）视图（第七讲） *41.0 简述 SQL语言于1974年由Boyce等提出，并于19751979年在IBM公司研制的System R数据库管理系统上实现，现已成为国际标准。SQL(Structured Query Language，结构化查询语言)是关系数据库的标准语言。几乎所有的关系型数据库管理系统均采用SQL语言标准。很多数据库厂商都对SQL语句进行了再开发和扩展。微软 SqlServer: T-SQLOracle: PL/SQL标准SQL命令查询:SELECT 插入:INSERT更新:UPDATE 删除:DELETE创建:CREATE 对象删除:DR

3、OP*71.1 SQL的发展 SQL-86：第一个SQL标准，由美国国家标准局(American National Standard Institute，简称ANSI)公布1987年国际标准化组织(International Organization for Standardization，简称ISO)通过。该标准也称为SQL-1*8SQL的发展 SQL-92：在1992年，由ISO和ANSI对SQL-86进行重新修订，该标准也称为SQL-2SQL-99：在1999年，该版本在SQL-2的基础上，扩展了诸多功能，包括递归、触发、面向对象技术等。该标准也称为SQL-3 SQL-2003：该标准是

4、最新的标准，也称SQL-4，于2003年发布 *9SQL标准 标准 大致页数 发布日期nSQL/86 1986年nSQL/89(FIPS 127-1) 120页 1989年nSQL/92 622页 1992年nSQL99 1700页 1999年nSQL2003 2003年SQL/Framework-BNF Grammar for ISO/IEC 9075-1:2003 SQL/Foundation-BNF Grammar for ISO/IEC 9075-2:2003 *10SQL语言的组成（4部分）数据定义语言数据定义语言DDL(Data Definition Language)定义数据库的

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

6、 SQL特点 综合统一 集数据定义语言DDL、数据操纵语言DML、数据控制语言DCL的功能于一体 。高度非过程化 没有条件、循环等控制语句。某些商业数据库进行了过程化扩充,比如PL/SQL。面向集合集合的操作方式采用集合操作方式，其操作对象、查找结果都是元组的元组的集合集合 。同一种语法结构提供两种使用方式SQL语言既是自含式语言，又是嵌入式语言，其语法结构基本一致。SQL功能极强，完成核心功能只用了9个动词。语言简洁，易学易用SQL支持的三级模式结构SQL语言支持三级模式结构，外模式对应视图和部分视图和部分基本表基本表，模式对应基本表基本表，内模式对应存储文件存储文件 SQL视图视图1视图视

7、图2基本表基本表1基本表基本表2基本表基本表3基本表基本表4存储文件存储文件1存储文件存储文件2 外模式外模式 模式模式 内模式内模式SQL支持的三级模式结构基本表基本表数据库中独立存在的表独立存在的表称为基本表，在SQL中一个关系对应一个基本表一个(或多个)基本表对应一个存储文件一个表可以带若干索引索引存放在存储文件中视图视图指从一个或几个基本表(或视图)导出的表，是虚表。虚表。只存放视图的定义而不存放对应数据查询表查询表指查询结果对应的表存储文件存储文件指数据库中存放关系的物理文件*15本课程使用的数据库-ScoreDB（p77）ScoreDB的模式导航图*20课堂演示ScoreDB数据库

8、安装前的设置*211、下载、下载http:/mySQL的安装、配置2、安装、安装解压到安装目录启动服务器启动服务器 cmd-安装目录安装目录 mysqld -console运行客户端运行客户端 cmd-安装目录安装目录 mysql test3、配置3.1、Path路径配置目的：全局变量，可直接调用。*24在Path变量中加上加上“mysql安装目录安装目录/bin;”D:Program Filesmysql-5.5.27-win32bin;*25*263.2、my.ini配置配置目的：配置mySql启动参数，设置客户端和服务端的字符集。clientport = 3306socket = /tm

9、p/mysql.sockdefault-character-set =gbk. .mysqldport = 3306socket = /tmp/mysql.sockbasedir =D:/Program Files/mysql-5.5.27-win32#datadir =character_set_server=gbk*273.3、服务启动方式配置、服务启动方式配置1）命令行启动和停止）命令行启动和停止启动：mysqld console 停止：mysqladmin u root p shutdown;2）设置为）设置为Windows服务（不用）服务（不用） mysqld install 或或

10、net start mysql删除Windows服务mysqld remove 或或 sc delet mysql*283.4、配置客户端和连接字符集、配置客户端和连接字符集目的：减少乱码。1)查看数据库字符集show variables like character%;2）更改数据库字符集set character_set_client = gbk ;set character_set_connection = gbk ;安装、配置Navicat mysql管理客户端2930用Navicat 新建数据库31建表32也可运行SQL建表（library.sql）33返回注意：该方法有乱码风险2.

11、1 数据库的定义2.2 基本表的定义2.3 索引的定义*34SQL数据定义语言SQL的数据定义功能: 数据库（模式）定义、表定义、视图和索引的定义 *352.1 数据库的定义 数据库保存了企业所有的数据，以及相关的一些控制信息，如安全性和完整性约束、关系的存储路径等数据库包含了基本表、视图、索引以及约束等对象，在定义这些对象之前，必须首先定义数据库，然后在数据库中定义所有的对象*36数据库作为一个整体存放在外存的物理文件中物理文件有两种：数据文件：数据文件：存放数据库中的对象数据；日志文件：日志文件：存放用于恢复数据库的企业冗余数据物理文件可以是多个，可以将一个或若干个物理文件设置为一个逻辑设

12、备数据库可以有多个逻辑设备，必须在定义数据库时进行定义数据库的对象存放在逻辑设备上，由逻辑设备与物理文件进行联系，从而实现数据库的逻辑模式与存储模式的独立*371）数据库的创建一个数据库创建在物理介质的一个或多个文件上，它预先分配了将要被数据库和事务日志所使用的物理存储空间存储数据的文件叫做数据文件(data file)存储日志的文件叫做日志文件(log file)创建一个新的数据库时，仅创建了一个空壳，必须在这个空壳中创建对象(如表等)，才能使用这个数据库当创建了一个数据库，与该数据库相关的描述信息会存入到系统的数据字典中在定义数据库的时候，必须定义数据库的名字数据库的名字、逻辑设备名和物理

13、文件名*38定义数据库的语法 CREATE DATABASE ON PRIMARY , . n - 定义主逻辑设备的数据文件 , , . n - 定义用户逻辑设备的数据文件 LOG ON , . n - 定义数据库日志的数据文件 建立学生成绩数据库ScoreDB演示CREATE DATABASE ScoreDB ;*392)数据库的修改修改数据库在运行过程中，可以依据数据量的大小进行修改修改数据库操作的语法为： ALTER DATABASE 3）数据库的删除删除数据库操作的语法为： DROP DATABASE 删除数据库时，系统会同时从系统的数据字典中将该数据库的描述一起删除*41建立学生成绩

14、数据库ScoreDBDROP DATABASE if exists ScoreDB;CREATE DATABASE ScoreDB ;USE ScoreDB;2.2 基本表的定义创建数据库后，就可在数据库中建立基本表。SQL中域的概念用数据类型来实现定义表的属性时 需要指明其数据类型及长度 选用哪种数据类型 n取值范围 n要做哪些运算 *42基本数据类型整型：int(4B)，smallint(2B)，tinyint(1B)；实型：float，real(4B)，decimal(p, n)，numeric(p, n)；字符型：char(n)，varchar(n)，text；2进制型：binary(

15、n)，varbinary(n)，image；逻辑形：bit，只能取0和1，不允许为空；货币型：money(8B, 4位小数)，small money(4B, 2位小数)；时间型：datetime(4B, 从1753.1.1开始)，smalldatetime(4B, 从1900.1.1开始)其中：image为存储图象的数据类型，text存放大文本数据*431)创建基本表CREATE TABLE （ ， ， ）； 如果完整性约束条件涉及到该表的多个属性列，则必须定义在表级上，否则既可以定义在列级也可以定义在表级。 *44建立ScroeDB的4张表CREATE TABLE Class ( /* 定义

16、班级表Class*/ classNo char(6) NOT NULL , /*班级号*/ className varchar(30) NOT NULL, /*班级名*/ institute varchar(30) NOT NULL, /*所属学院*/ grade smallint default 0 NOT NULL, /*年级*/ classNum tinyint NULL, /*班级人数*/ CONSTRAINT ClassPK PRIMARY KEY (classNo) ) 属性定义主码/* */注释约束：缺省值数据类型表名课程表CourseCREATE TABLE Course (

17、/* 定义课程表Course*/ courseNo char(3) NOT NULL , /*课程号*/ courseName varchar(30) NOT NULL, /*课程名*/ creditHour numeric(1) default 0 NOT NULL, /*学分*/ courseHour tinyint default 0 NOT NULL, /*课时数*/ priorCourse char(3) NULL, /*先修课程*/ CONSTRAINT CoursePK PRIMARY KEY (courseNo), FOREIGN KEY (priorCourse) REFER

18、ENCES Course(courseNo) ) 定义外码priorCourse， 被参照表是Course被参照列是courseNo学生表StudentCREATE TABLE Student ( studentNo char(7) NOT NULL , /*学号*/ studentName varchar(20) NOT NULL , /*姓名*/ sex char(2) NULL, /*性别*/ birthday datetime NULL, /*出生日期*/ native varchar(20) NULL, /*籍贯*/ nation varchar(30) default 汉族 NUL

19、L, /*民族*/ classNo char(6) NULL, /*所属班级*/ CONSTRAINT StudentPK PRIMARY KEY (studentNo), CONSTRAINT StudentFK FOREIGN KEY (classNo) REFERENCES Class(classNo) 定义成绩表ScoreCREATE TABLE Score ( studentNo char(7) NOT NULL , /*学号*/ courseNo char(3) NOT NULL , /*课程号*/ score numeric(5, 1) default 0 NOT NULL, /

20、*成绩*/ CHECK( score BETWEEN 0.0 AND 100.0) , /*自定义约束 */ /* 主码由两个属性构成，必须作为表级完整性约束进行定义*/ CONSTRAINT ScorePK PRIMARY KEY (studentNo, courseNo), CONSTRAINT ScoreFK1 FOREIGN KEY (studentNo) REFERENCES student(studentNo), CONSTRAINT ScoreFK2 FOREIGN KEY (courseNo) REFERENCES course(courseNo) *48基本表的修改（ ALT

21、ER TABLE ）修改表的结构，如扩充列等。修改基本表操作的语法为：ALTER TABLE ADD 完整性约束 DROP ALTER COLUMN ；*49基本表的修改增加列（新增一列的值为空值）：ALTER TABLE ADD 增加约束：ALTER TABLE ADD CONSTRAINT 删除约束：ALTER TABLE DROP 修改列的数据类型：ALTER TABLE ALTER COLUMN *50基本表的删除 DROP TABLE RESTRICT | CASCADE为被删除的表名若选择RESTRICT，则该表的删除有限制条件即该表不能有视图、触发器以及被其它表所引用（如检查约束

22、CHECK，外码约束FOREIGN KEY），该项为缺省项。若选择CASCADE，则该表的删除没有限制条件在删除基本表的同时，也删除建立在该表上的所有索引、完整性规则、触发器和视图。删除表时，系统会同时从系统的数据字典中将该表的描述一起删除。注意注意：mySQL、SQL Server都不支持 RESTRICT | CASCADE选项*512.3 索引的定义索引是加快数据检索的一种工具一张表可以建立多个索引，可从不同的角度加快查询速度；如果索引建立得较多，会给数据维护带来较大的系统开销。索引是由的记录构成索引逻辑上按照搜索码值进行排序，但不改变表中记录的物理顺序；索引和基本表分别存储。数据库的索

23、引一般按照B+树结构来组织，也有Hash索引和位图索引等。索引相关的描述信息会保存到数据字典中。*52例：在班级表中按所属学院建立的索引InstituteIdx：*53索引的类型聚集聚集索引每张表仅能建立一个聚集索引聚集索引是按搜索码值的某种顺序(升序/降序)来重新组织表记录聚集索引可以极大地提高查询速度聚集索引给数据的修改带来困难。一般建立了聚集索引的表不进行更新操作，仅执行查询操作，这在数据仓库中使用得较多。非聚集非聚集索引：是普通索引，一张表可以建立多个普通索引。*54建立索引操作的语法 CREATE UNIQUE CLUSTERED | NONCLUSTERED INDEX ON (

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

25、引InstituteIdxCREATE NONCLUSTERED INDEX InstituteIdx ON Class(institute)例3.73 在学生表中，首先按班级编号的升序，然后按出生日期的降序降序建立一个非聚集索引ClassBirthIdx。CREATE INDEX ClassBirthIdx ON Student(classNo, birthday DESC)*57Mysql中查看表中查看表“tbName”已建立的索引的命令：已建立的索引的命令：mysql show index from tbName; 索引的删除索引一旦建立，用户不需要管理它，由系统自动维护；可删除那些不经

26、常使用的索引；删除索引操作的语法为：DROP INDEX 删除索引时，系统会同时从系统的数据字典中将该索引的描述一起删除。例3.74 删除InstituteIdx索引。DROP INDEX InstituteIdx*583.1 插入数据插入数据 3.2 删除数据删除数据3.3 修改数据修改数据*593.1 插入数据两种方式：插入单条记录，插入子查询子查询的结果，一次插入多条记录。（第六讲）插入一条元组：INSERT INTO ( , . ) VALUES ( , . ) columnName1. ：指明被插入的元组按指定的属性名称和顺序插入。 该项可以省略。若省略，表示必须按照表的属性个数属性

27、个数和属性顺序属性顺序插入新元组。*60示例：ScoreDB 导入数据数据文件：scoredb 数据文件.sql在命令行下，用insert命令导入（navicat有可能出问题）：或（暂不推荐）使用“source命令”(与文件编码有关，可能出现乱码)；mysql source X.sql；*61例3.75 将一个新学生元组（0700006, 李相东, 男, 1991-10-21 00:00, 云南, 撒呢族, CS0701）插入到学生表Student中。INSERT INTO Student VALUES ( 0700006, 李相东, 男, 1991-10-21 00:00, 云南, 撒呢族,

28、 CS0701 )表名Student后没有指定列名，表示按照Student表定义的属性列的个数个数和顺序顺序将新元组插入到Student表中。*62 例3.76 将一个新学生元组（姓名：章李立，出生日期：1991-10-12 00:00，学号：0700007）插入到学生表Student中。 INSERT INTO Student ( studentName, birthday, studentNo) VALUES ( 章李立, 1991-10-12 00:00, 0700007 ) 按照指定列的顺序和列的个数向学生表Student插入一个新元组 没有列出的属性列自动取空值； 插入新元组时，数据

29、的组织可不按照表结构定义的属性个数和顺序进行插入。*633. 2 删除数据删除命令：DELETE FROM WHERE ：要删除记录的表名；WHERE ：指出被删除的记录所满足的条件该项可以省略，若省略则表示删除表中的所有记录；WHERE子句中可以包含子查询。*64例3.79删除学号为0800001同学的选课记录。*65DELETE FROM Score WHERE studentNo=08000013.9.3 修改数据UPDATE SET = , = . FROM AS , AS . WHERE *66其中：要进行修改记录的表名；SET = , = . : 用表达式的值替代属性列的值一次可以

30、修改元组的多个属性列，之间以逗号分隔；WHERE ：指出被修改的记录所满足的条件该项可以省略，若省略，表示修改表中的所有记录；WHERE子句中可以包含子查询。*67例3.84将学号为0800001同学的出生日期修改为1992年5月6日出生，籍贯修改为福州。*68UPDATE Student SET birthday=1992-5-6 00:00, native=福州 WHERE studentNo=0800001*691、配置mysql，熟悉以下操作：启动服务器停止服务器登录客户端查看和配置字符集2、安装navicat3、使用命令行客户端建立数据库scoreDB4、在数据库ScoreDB中创建

31、表student、class、score、course。*70附1：安装安装mysql解压到安装目录启动服务器启动服务器 cmd-安装目录安装目录 mysqld -console运行客户端运行客户端 cmd-安装目录安装目录 mysql test3、配置3.1、Path路径配置目的：全局变量，可直接调用。*72在Path变量中加上加上“mysql安装目录安装目录/bin;”D:Program Filesmysql-5.5.27-win32bin;*73*741.2、my.ini配置配置目的：配置mySql启动参数，设置客户端和服务端的字符集。Mysql根目录新建my.ini，加入如下（参考my

32、.ini）clientport = 3306socket = /tmp/mysql.sockdefault-character-set =gbkmysqldport = 3306socket = /tmp/mysql.sock#basedir =#datadir =character_set_server=gbk*751.3、服务启动方式配置、服务启动方式配置1）命令行启动和停止）命令行启动和停止启动：mysqld console 停止：mysqladmin u root p shutdown;2）设置为）设置为Windows服务（不用）服务（不用） mysqld install 或或 net

33、 start mysql删除Windows服务mysqld remove 或或 sc delet mysql*761.4、配置客户端和连接字符集、配置客户端和连接字符集目的：减少乱码。1)查看数据库字符集show variables like character%;2）更改数据库字符集set character_set_client = gbk ;set character_set_connection = gbk ;注：除character_set_filesystem、character_set_system和character_sets_dir，其他变量均设置为gbk。安装、配置Navi

34、cat mysql管理客户端7778用Navicat 新建数据库79建表80附2 建立学生成绩数据库ScoreDB*81DROP DATABASE if exists ScoreDB;CREATE DATABASE ScoreDB ;USE ScoreDB;附3 建立ScroeDB的4张表CREATE TABLE Class ( /* 定义班级表Class*/ classNo char(6) NOT NULL , /*班级号*/ className varchar(30) NOT NULL, /*班级名*/ institute varchar(30) NOT NULL, /*所属学院*/ gr

35、ade smallint default 0 NOT NULL, /*年级*/ classNum tinyint NULL, /*班级人数*/ CONSTRAINT ClassPK PRIMARY KEY (classNo) ) 课程表CourseCREATE TABLE Course ( /* 定义课程表Course*/ courseNo char(3) NOT NULL , /*课程号*/ courseName varchar(30) NOT NULL, /*课程名*/ creditHour numeric(1) default 0 NOT NULL, /*学分*/ courseHour tinyint default 0 NOT NULL, /*课时数*/ priorCourse char(3) NULL, /*先修课程*/ CONSTRAINT CoursePK PRIMARY KEY (courseNo), FOREIGN KEY (priorCourse) REFERENCES Course(courseNo) ) 学生表StudentCREATE TABLE Studen