java暑期培训-文件les01-数据库

第一章绪论ID:SCSC012-SQLRoadMap第一节引言第二节

SQL第三节查询语句第四节更新数据第五节视图第六节数据类型第七节SQL语言组成

第一节引言一、数据（Data）数据实际上就是描述事物的符号记录。二、数据库（Database，简称DB）数据库是长期存储在计算机内有结构的、大量的、共享的数据集合。三、数据库管理系统（DatabaseManagementSystem，简称DBMS）数据库管理系统是位于用户与操作系统之间的一层数据管理软件。四、数据库系统（DatabaseSystem，简称DBS）数据库系统是指在计算机系统中引入数据库后的系统构成，一般由数据库、数据库管理系统（及其开发工具）、应用系统、数据库管理员和用户构成。五、数据库系统管理员（DatabaseAdministrator，简称DBA）数据库系统管理员负责数据库的建立、使用和维护的专门的人员。

第一节引言六、概念及术语数据结构化数据的共享性数据冗余度数据的一致性物理独立性逻辑独立性数据的安全性（Security）数据的完整性（Integrity）并发（Concurrency）控制数据库恢复（Recovery）

第二节

SQL结构化查询语言关系代数和关系演算是形式化查询语言，商业DBMS使用SQL（StructuredQueryLanguage）。1974年由IBM的San

Jose研究室提出，最初叫SEQUEL（StructuredEnglishQueryLanguage）关系数据库系统通过SQL对数据库进行查询和更新目前有许多不同版本的SQL语言,有两个不同的主要标准:ANSI(AmericanNationalStandardsInstitute)1986ISO(InternationalStandardsOrganization)：SQL-89,SQL-92,SQL2,正在制定SQL3

一、SQL特点1．一体化

SQL是一种一体化的语言，它包括了数据定义（DDL）、（DML）查询、更新、控制（DCL）四方面功能。

可以完成数据库活动中的全部工作

以前的非关系模型的数据语言一般包括：内模式描述语言、模式描述语言、外模式描述语言、数据操纵语言等。内容多，操作起来不像SQL那样简单。2．高度非过程化没有必要一步步地告诉计算机“如何”去做，只需描述清楚用户要“做什么”，SQL就可以将要求交给系统，自动完成全部工作。3．面向集合的操作方式操作对象、查询结果是元组的集合；插入、删除、更新操作的对象也可以是元组的集合。

一、SQL特点4．两种使用形式，统一的语法结构宿主式：将SQL嵌入到高级语言中使用自含式：将SQL作为操作命令独立使用现在许多数据库开发工具都将SQL直接融入到自身的语言中。5．语言简洁

SQL虽然功能强且使用两种方式，但只有为数不多的几条命令，另外语法也非常简单，接近自然语言，易掌握、学习。除了以上特点之外，SQL语言还支持数据库的三级模式结构。

二、数据定义

存储过程定义基本表定义定义功能

数据库的定义：和物理数据有关，以后介绍视图定义索引定义

规则定义与数据完整性有关，以后介绍

创建数据库CREATEDATABASESalesON(NAME=Sales_dat,FILENAME='C:\ProgramFiles\MicrosoftSQLServer\MSSQL.1\MSSQL\Data\saledat.mdf',SIZE=10,MAXSIZE=50,FILEGROWTH=5)LOGON(NAME='Sales_log',FILENAME='C:\ProgramFiles\MicrosoftSQLServer\MSSQL.1\MSSQL\Data\salelog.ldf',SIZE=5,MAXSIZE=25,FILEGROWTH=5)GO

数据库备份与恢复备份：BACKUPDATABASEpubsTODISK='d:\ccc.dat'withformat,NAME='Student'

数据库备份与恢复恢复：查看数据库备份信息：RESTOREFILELISTONLYFROMDISK='d:\ccc.dat'恢复数据库RESTOREDATABASETestDBFROMDISK='d:\ccc.dat'WITHMOVE'pubs'TO'C:\ProgramFiles\MicrosoftSQLServer\MSSQL\data\pubs1.mdf',MOVE'pubs_log'TO'C:\ProgramFiles\MicrosoftSQLServer\MSSQL\data\pubs1_log.ldf'

定义基本表CREATETABLE<表名>(

<列名><数据类型>[列级完整性约束条件][，

<列名><数据类型>[列级完整性约束条件]...)[,

<表级完整性约束条件>];例:USEsofteemCREATETABLEsft_Rebbs(

rebbsidnumeric(10)primarykey,contentvarchar(200)NOTNULL,authorvarchar(20)NOTNULL,bbs_idnumeric(10)NOTNULL)

定义主键CREATETABLE[dbo].[role]( [ID][int]IDENTITY(1,1)NOTNULL, [ROLE_NAME][varchar](50)COLLATEChinese_PRC_CI_ASNULL,

CONSTRAINT[PK_role]PRIMARYKEYCLUSTERED

(

[ID]ASC

)ON[PRIMARY])ON[PRIMARY]

修改基本表ALTERTABLE<表名>[ADD<新列名><数据类型>[完整性约束]][DROP<完整性约束名><完整性约束名>]修改列类型语法[altercolumn<属性><type>] ALTERTABLEstudentALTERCOLUMNSnovarchar(10);例1：向Student表增加“入学时间”列，其数据类型为日期型ALTERTABLEStudentADDeDATE;ALTERTABLEsft_RebbsADDpricefloat

添加外键Eg1:ALTERTABLE[dbo].[users]WITHNOCHECKADDCONSTRAINT[FK_employee_department]FOREIGNKEY([dept_id])REFERENCES[dbo].[department]([id])Eg2:ALTERTABLE[dbo].[users]WITHCHECKADDCONSTRAINT[FK_employee_employee]FOREIGNKEY([manager_id])REFERENCES[dbo].[users]([id])外键名本表字段外表,字段

修改主键ALTER

TABLE

dbo.employees

ADD

CONSTRAINT

pk_employeesidPRIMARY

KEY

CLUSTERED(EmployeeID)

ALTERTABLEMySQL：

ALTERTABLE表名MODIFY[COLUMN]字段名字段类型定义;Oracle：

修改类型时所改字段的数据必须为空：

ALTERTABLE表名MODIFY(字段名字段类型定义);SQLServer：

ALTERTABLE表名ALTERCOLUMN字段名字段类型定义;

ALTERTABLEsft_RebbsALTERCOLUMNpricefloat删除字段：altertable表名dropcolumn列名

sqlserver中修改字段名语句是sp_rename是内置的存储过程

sp_rename'oldTableName','newTableName','OBJECT'例子:

sp_rename'sft_Rebbs','newsft_Rebbs','OBJECT'另外，修改字段名在Oracle里是不允许的，sqlserver和mysql可以

execsp_rename'tableName.oldColumnName','newColumnName','COLUMN'

修改列名SQLSERVER:

暂时只能调用存储过程EXECSP_RENAME

table.column','newcolumn','COLUMN'MYSQL:

可以用CHANGE同时修改列名和列类型,如：

ALTERTABLEtableCHANGEoldcolumnnewcolumnBITNOTNULL

这里oldcolumn和newcolumn可以相同，

也就是说，CHANGE可以取代MODIFY了，可以用来单纯修改列类型或者列名

ORACLE：

在817版本以上支持RENAME了，没有具体测试过，9i以上版本比较保险

删除基本表DROPTABLE<表名>例：删除Student表

DROPTABLE

Student

基本表定义一旦删除，表中的数据、在此表上建立的索引都将自动被删除掉，而建立在此表上的视图虽仍然保留，但已无法引用。因此执行删除操作一定要格外小心。

建立索引

CREATE[UNIQUE][CLUSTER]INDEX<索引名><索引名>ON<表名>(<列名>[<次序>][,<列名>[<次序>]]...);

其中，<表名>指定要建索引的基本表的名字。索引可以建在该表的一列或多列上，各列名之间用逗号分隔。每个<列名>后面还可以用<次序>指定索引值的排列次序，包括ASC（升序）和DESC（降序）两种，缺省值为ASC。例：为学生-课程数据库中的Student、Couse、SC三个表建立索引。其中Student表按学号升序建唯一索引，Couse表按课程号升序建唯一索引，Sno、Cno表按学号升序和课程号降序建唯一索引。CREATEUNIQUEINDEXStusnoONStudent(Sno);CREATEUNIQUEINDEXCoucnoONCouse(Cno);CREATEUNIQUEINDEXSCnoONSC(SnoASC,CnoDESC);

删除索引

DROPINDEX<索引名>;例：删除Student表的Stusname索引

DROPINDEXStusname;

索引一经建立，就由系统使用和维护它，不需用户干预。建立索引是为了减少查询操作的时间，但如果数据增删改频繁，系统会花费许多时间来维护索引。这时，可以删除一些不必要的索引。删除索引时，系统会同时从数据字典中删去有关该索引的描述。

第三节查询语句SELECT[ALL|DISTINCT]<目标列表达式>[,<目标列表达式>]...FROM<表名或视图名>[,<表名或视图名>]...[WHERE<条件表达式>][GROUPBY<列名1>[HAVING<条件表达式>]][ORDERBY<列名2>[ASC|DESC]];整个SELECT语句的含义是，根据WHERE子句的条件表达式，从FROM子句指定的基本表或视图中找出满足条件的元组，再按SELECT子句中的目标列表达式，选出元组中的属性值形成结果表。如果有GROUP子句，则将结果按<列名1>的值进行分组，该属性列值相等的元组为一个组，每个组产生结果表中的一条记录。通常会在每组中作用集函数。如果GROUP子句带HAVING短语，则只有满足指定条件的组才予输出。如果有ORDER子句，则结果表还要按<列名2>的值的升序或降序排序。

单表查询例1查询所有书名和书号

SELECTSno,Sname

FROMStudent;

例2查所有书的名及出版时间

SELECTSname,1996-Sage

FROMStudent;例3查全体学生的姓名、出生年份和所有系，要求用小写字母表示所有系名

SELECTSname,'YearofBirth:',1996-Sage,ISLOWER(Sdept)

FROMStudent;

单表查询例4查所有年龄在20岁以下的学生姓名及其年龄

SELECTSname,Sage

FROMStudent

WHERESage<20;

或

SELECTSname,Sage

FROMStudent

WHERENOTSage>=20;

单表查询例5查询年龄在20至23岁之间的学生的姓名、系别、和年龄

SELECTSname,Sdept,Sage

FROMStudentWHERESageBETWEEN20AND23;例6查姓“欧阳”且全名为三个汉字的学生的姓名SELECTSname

FROMStudent

WHERESnameLIKE'欧阳__';

单表查询例7查以”DB_”开头，且倒数第三个字符为i的课程的详细情况SELECT*

FROMCourse

WHERECnameLIKE’DB\_%i__’ESCAPE’\’;例8查询选修了3号课程的学生的学号及其成绩，查询结果按分数的降序排列

SELECTSno,Grade

FROMSC

WHERECno='1'GROUPBYGradeDESC;

单表查询例9

计算1号课程的学生平均成绩SELECTAVG(Grade)

FROMSC

WHERECno='1';例10查询学习1号课程的学生最高分数SELECTMAX(Grade)

FROMSCWHERECno='1';

单表查询例11查询信息系选修了3门以上课程的学生的学号SELECTSnoFROMSCWHERESdept='IS';GROUPBYSnoHAVINGCOUNT(*)>3;

单表分页查询例11

简单分页：SELECT

TOP10*FROMTB_BOOKWHEREidNOT

IN(SELECT

TOP10idFROMTB_BOOK)例12带条件分页SELECT

TOP10*FROMTB_BOOKWHEREidNOT

IN(SELECT

TOP10idFROMTB_BOOKWHEREbook_price>10)ANDbook_price>10

连接查询例1自然连接Student和SC表SELECTStudent.Sno,Sname,Ssex,Sage,Sdept,Cno,GradeFROMStudent,SCWHEREStudent.Sno=SC.Sno;例2查询选修2号课程且成绩在90分以上的所有学生SELECTStudent.Sno,Sname

FROMStudent,SC

WHEREStudent.Sno=SC.SnoAND

o='2'AND

SC.Grade>90;

嵌套查询例1查询与“刘晨”在同一个系学习的学生

SELECTSno,Sname,Sdept

FROMStudent

WHERESdeptIN

SELECTSdept

FROMStudent

WHERESname='刘晨';在上例中，由于一个学生只可能在一个系学习，也就是说内查询刘晨所在系的结果是一个唯一值，因此该查询也可以用比较运算符来实现，其SQL语句如下：

SELECTSno,Sname,Sdept

FROMStudent

WHERESdept=

SELECTSdept

FROMStudent

WHERESname='刘晨';

集合查询每一个SELECT语句都能获得一个或一组元组。若要把多个SELECT语句的结果合并为一个结果,可用集合操作来完成。集合操作主要包括并操作UNION、交操作INTERSECT和差操作MINUS。

使用UNION将多个查询结果合并起来，形成一个完整的查询结果时，系统会自动去掉重复的元组。需要注意的是，参加UNION操作的各数据项数目必须相同；对应项的数据类型也必须相同。例:查询计算机科学系的学生及年龄不大于19岁的学生SELECT*

FROMStudent

WHERESdept='CS'

UNION

SELECT*

FROMStudent

WHERESage<=19

第四节更新数据a、插入数据b、修改数据c、删除数据

a、插入数据例1将一个新学生记录（学号：95020；姓名：陈冬；性别：男；所在系：IS；年龄：18岁）插入Student表中

INSERT

INTOStudent

VALUES('95020','陈冬','男','IS',18);例2插入一条选课记录('95020'，'1')

INSERTINTOSC(Sno,Cno)

VALUES('95020','1');

a、插入数据例1将一个新学生记录（学号：95020；姓名：陈冬；性别：男；所在系：IS；年龄：18岁）插入Student表中

INSERT

INTOStudent

VALUES('95020','陈冬','男','IS',18);例2插入一条选课记录('95020'，'1')

INSERT

INTOSC(Sno,Cno)

VALUES('95020','1');a、插入数据，返回Identity号INSERTINTOjobs(job_desc,min_lvl,max_lvl)VALUES('Accountant',12,125)SELECT@@IDENTITYAS‘Identity‘;SELECTSCOPE_IDENTITY()AS[SCOPE_IDENTITY]SELECTCAST(scope_identity()ASint)

b、修改数据例1将学生95001的年龄改为22岁

UPDATEStudent

SETSage=22

WHERESno='95001';例2将所有学生的年龄增加1岁

UPDATEStudent

SETSage=Sage+1;

c、删除数据例1删除学号为95019的学生记录

DELETE

FROMStudentWHERESno='95019';例2删除计算机科学系所有学生的选课记录

DELETE

FROMSC

WHERE'CS'=

(SELETESdept

FROMStudent

WHEREStudent.Sno=SC.Sno);

第五节视图定义视图例1建立信息系学生的视图。

CREATEVIEWIS_Student

AS

SELECTSno,Sname,Sage

FROMStudent

WHERESdept='IS';例2将Student表中所有女生记录定义为一个视图

CREATEVIEWF_Student(stdnum,name,sex,age,dept)

AS

SELECT*

FROMStudent

WHERESsex='女';

更新视图例1将信息系学生视图IS_Student中学号为95002的学生姓名改为“刘辰”

UPDATEIS_Student

SETSname='刘辰'

WHERESno='95002';例2向信息系学生视图IS_S中插入一个新的学生记录，其中学号为95029，姓名为赵新，年龄为20岁

INSERT

INTOIS_Student

VALUES('95029','赵新',20);

DBMS将其转换为对基本表的更新：

INSERT

INTOStudent(Sno,Sname,Sage,Sdept)

VALUES('95029','赵新',20,'IS');

这里系统自动将系名'IS'放入VALUES子句中。

视图的特点视图最终是定义在基本表之上的，对视图的一切操作最终也要转换为对基本表的操作。而且对于非行列子集视图进行查询或更新时还有可能出现问题。既然如此，为什么还要定义视图呢？这是因为合理使用视图能够带来许多好处。1.视图能够简化用户的操作2.视图使用户能以多种角度看待同一数据3.视图对重构数据库提供了一定程度的逻辑独立性4.视图能够对机密数据提供安全保护

第六节数据类型1.整型数据类型2.浮点数据类型3.字符数据类型4.日期和时间数据类型5.文本和图形数据类型6.货币数据类型7.位数据类型8.二进制数据类型9.特殊数据类型

1.整型数据类型整型数据类型是最常用的数据类型之一，它主要用来存储数值，可以直接进行数据运算，而不必使用函数转换。int（integer）：int（或integer）数据类型可以存储从-231（-2，147，483，648）到231-1（2，147，483，647）范围之间的所有正负整数。Smallint：可以存储从-215（-32，768）到215-1范围之间的所有正负整数。Tinyint：可以存储从0到255范围之间的所有正整数。

2.浮点数据类型浮点数据类型用于存储十进制小数。浮点数值的数据在SQLServer中采用只入不舍的方式进行存储。Real：可以存储正的或者负的十进制数值，最大可以有7位精确位数。Float：可以精确到第15位小数，其范围从-1.79E-308到1.79E+308。Decimal和numeric：Decimal数据类型和numeric数据类型完全相同，它们可以提供小数所需要的实际存储空间，但也有一定的限制，可以用2到17个字节来存储从-1038-1到1038-1之间的数值。

3.字符数据类型字符数据类型可以用来存储各种字母、数字符号和特殊符号。char：其定义形式为char（n），每个字符和符号占用一个字节的存储空间。varchar：其定义形式为varchar（n）。用char数据类型可以存储长达255个字符的可变长度字符串。nchar：其定义形式为nchar（n）。nvarchar：其定义形式为nvarchar（n）。

varchar与char的区别char对英文(ASCII)字符占用1个字节，对一个汉字占用2个字节varchar的类型不以空格填满，比如varchar(100)，如果它的值只是"qian",则它的值就是"qian“而char不一样，比如char(100),它的值是“qian”，而实际上它在数据库中是“qian”(qian后共有96个空格，就是把它填满为100个字节)。由于char是以固定长度的，所以它的速度会比varchar快很多!但程序处理起来要麻烦一点，要用trim之类的函数把两边的空格去掉!

nvarchar和ncharnchar: Fixed-lengthUnicodecharacterdataofncharacters.nmustbeavaluefrom1through4,000.Storagesizeistwotimesnbytes.nvarchar: Variable-lengthUnicodecharacterdataofncharacters.nmustbeavaluefrom1through4,000.Storagesize,inbytes,istwotimesthenumberofcharactersentered.Thedataenteredcanbe0charactersinlength

4.日期和时间数据类型datetime：用于存储日期和时间的结合体。它可以存储从公元1753年1月1日零时起到公元9999年12月31日23时59分59秒之间的所有日期和时间。smalldatetime：与datetime数据类型类似，但其日期时间范围较小，它存储从1900年1月1日到2079年6月6日内的日期。

5.文本和图形数据类型text：用于