《SQL基础入门》PPT课件.ppt

《《SQL基础入门》PPT课件.ppt》由会员分享，可在线阅读，更多相关《《SQL基础入门》PPT课件.ppt（116页珍藏版）》请在三一办公上搜索。

1、第章SQL,SQL概述,(Structured Query Language)简称SQL作用：结构化查询语言是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统,2,学习目标,创建数据库利用SQL的DDL语句创建数据表，并理解SQL支持的数据类型利用SQL建立完整性约束理解查询语句基本结构能够用SELECT语句进行简单查询能够按照字段对信息进行筛选能够对查询的记录进行排序能够进行集合查询能够使用聚集函数查询能够进行嵌套查询理解连接的函义，并使用连接进行查询利用SQL进行数据库的修改,3,一、创建数据库,方法：1、图形化创建2、用CREATE语句创建,4,任务：在Man

2、agement Studio中创建数据库student。,【任务分析】根据任务要求，要求使用SQL Server Management Studio的图形化界面创建名为student的数据库，并保存在C盘db文件夹下。数据库的具体属性如下表所示：,student的数据文件和事务日志文件,相关知识数据库的文件结构,逻辑结构是指数据库由哪些性质的信息组成。SQL Server的数据库是由表、视图、索引、存储过程和触发器等各种不同的对象组成，它们构成了数据库的逻辑结构。,SQL Server 2012数据库的常用对象,物理结构也称为存储结构，表示数据库文件是如何在磁盘上存放的。SQL Server

3、2012中的数据库文件在磁盘上以文件的单位存放，由数据库文件和事务日志文件组成。根据文件作用的不同，又可以将它们分为三类：主数据库文件、辅助数据库文件和事务日志文件。其中，主数据库文件是必须要有的，辅助数据库文件可以根据需要设置一个或者多个，而事务日志文件至少有一个。,相关知识数据库的文件结构,基本概念,主要数据文件,数据文件,数据库 次要数据文件,事务日志文件,用于存储数据库中的所有对象，如表,用来存储数据库的启动信息、部分或全部数据，扩展名为.mdf,每个数据库只能有一个主要数据文件,用于保存所有主要数据文件中容纳不下的数据，扩展名为.ndf,可有多个,保存用于恢复数据库的日志信息,至少一

4、个,操作步骤,1、打开Management Studio界面,操作步骤,2、打开“新建数据库”对话框,操作步骤,3、按要求设置数据库基本属性,任务 用CREATE DATABASE语句创建数据库student,【任务分析】要求使用Management Studio创建名为student的数据库，同时设置数据库的相关属性。,student的数据文件和事务日志文件,知识说明：CREATE DATABASE语句的基本格式,CREATE DATABASE database_name ON PRIMARY（）LOG ON（）,关键代码,create database studenton primary(

5、name=student1,filename=d:dbstudent.mdf,size=10MB,maxsize=100MB,filegrowth=5MB),(name=student2,filename=d:dbstudent.ndf,size=8MB,maxsize=50MB,filegrowth=2MB),15,log on(name=studentlog,filename=d:dbstudent.ldf,size=5MB,maxsize=50MB,filegrowth=5MB),16,分离与附加数据库,1.分离数据库用 SQL Server Management Studio分离数据库

6、展开树形目录，定位到要分离的数据库上单击鼠标右键，在弹出的快捷菜单中，选择【任务】|【分离】命令。,2023/7/8,17,附加数据库,1.用 SQL Server Management Studio附加数据库1）在数据库节点上单击鼠标右键，弹出快捷菜单，选择【附加】命令。2）出现【附加数据库】对话框，单击【添加】按钮，出现【定位数据库文件】对话框,在这个对话框里，默认只显示数据库的数据文件，即“mdf”文件。,2023/7/8,18,数据库分离与附加的作用和功能,一、可以切断数据库的使用(比如当数据库日志很大占用了空间时，可以用分离数据库的方法来切断数据库，从而删除以前的数据库日志，来节省空

7、间）二、可以在需要更换数据库物理存放地址时使用。如果您数据库系统安装在系统盘（比如 C 盘），由于 C 盘容易受病毒侵害，您也许希望您的数据存放在非系统盘（比如 D 盘），要做的这点很简单，您并不需要重装数据库，只要把数据“分离”，然后将相关文件移动到 D 盘的某个目录，接着“附加”数据库即可。,相关知识脱机与联机,脱机与联机是针对数据库的当前状态来说的，当一个数据库处于可操作、可查询的状态时就是联机状态，而一个数据库尽管可以看到其名字出现在数据库节点中，但对其不能执行任何有效的数据库操作时就是脱机状态。,脱机与联机数据库,脱机数据库1.用 SQL Server Management Stud

8、io脱机数据库1）展开树形目录，定位到要脱机的数据库上单击鼠标右键，在弹出的快捷菜单中，选择【任务】|【脱机】命令2）弹出【使数据库脱机】对话框，单击【关闭】按钮即可完成操作。,2023/7/8,21,联机数据库,1.用 SQL Server Management Studio联机数据库1）展开树形目录，定位到要联机的数据库上单击鼠标右键，在弹出的快捷菜单中，选择【任务】|【联机】命令。2）弹出【使数据库联机】对话框，单击【关闭】按钮即可完成操作,2023/7/8,22,收缩数据库,1 用 Management Studio收缩数据库和文件1）在目标数据库上单击鼠标右键，在弹出的快捷菜单中选择

9、【任务】|【收缩】|【数据库】。2）出现【收缩数据库】对话框，选中并设置相应的值，点击【确认】。,2023/7/8,23,收缩数据库,3）如果想收缩数据库的指定数据文件或日志文件，可右击相应的数据库，弹出快捷菜单，选择【任务】|【收缩】|【文件】4）出现【收缩文件】对话框，选中并设置相应的值，点击【确认】。,删除数据库,1 用 SQL Server Management Studio删除数据库在【对象资源管理器】中，定位在目标数据库上单击鼠标右键，在弹出的快捷菜单中选择【删除】。2用 T-SQL 命令删除数据库 DROP DATABASE database_name,.n,2023/7/8,2

10、5,可同时删除多个数据库,-例1DROP DATABASE BJGL,例2DROP DATABASE student2-例3DROP DATABASE 东软,思考,假如没有对日志文件进行定义，那么日志文件的大小和数据文件有什么差别？怎样创建有多个数据文件的数据库？,课堂实践操作,1在SQL Server中创建数据库，具体要求如下：（1）数据库名称为college。（2）主要数据文件：逻辑文件名为college1，物理文件名为college.mdf，初始容量为10MB，最大容量为100MB，递增量为10MB。（3）次要数据文件：逻辑文件名为college 2，物理文件名为college.ndf，

11、初始容量为5MB，最大容量为50MB，递增量为5MB。（4）事务日志文件：逻辑文件名为college Log，物理文件名为college.ldf，初始容量为5MB，最大容量为50MB，递增量为5MB。,二、创建数据表,方法：1、图形化创建2、用DDL语句创建,28,数据类型,29,整数类型比较,bigint：从-263(-9223372036854775808)到263-1(9223372036854775807)的整型数据，存储大小为 8 个字节。一个字节就是8位，那么bigint就有64位int：从-231(-2,147,483,648)到231-1(2,147,483,647)的整型数据

12、，存储大小为 4 个字节。int类型，最大可以存储32位的数据 smallint：从-215(-32,768)到215-1(32,767)的整数数据，存储大小为 2 个字节。smallint就是有16位 tinyint：从0到255的整数数据，存储大小为 1 字节。tinyint就有8位。,30,字符类型数据,1、CHAR。CHAR存储定长数据很方便，CHAR字段上的索引效率级高，比如定义char(10)，那么不论你存储的数据是否达到了10个字节，都要占去10个字节的空间。2、VARCHAR。存储变长数据，但存储效率没有CHAR高。如果一个字段可能的值是不固定长度的，我们只知道它不可能超过10

13、个字符，把它定义为 VARCHAR(10)是最合算的。3、TEXT。text存储可变长度的非Unicode数据，最大长度为231-1(2,147,483,647)个字符。,31,任务一：在student数据库中创建数据表student的表结构,【任务分析】数据表包括表结构、约束以及记录等三个要件。这个任务我们主要是完成对数据表student表结构的创建，表结构如下表所示：,表 25 student表的结构,任务一：创建数据表student的表结构,【任务分析】可以使用两种方法来解决这个问题：利用Management Studio工具，在图形化的界面中创建student表结构。使用CREATE

14、TABLE语句创建表结构。,方法一：操作界面（操作步骤）,展开需要创建表的数据库“student”并右击“表”，在弹出的快捷菜单中选择“新建表”命令。,方法一：操作界面（操作步骤）,在打开的表设计器对话框中，按照任务要求设置表student各列的列名、数据类型、允许空（非空约束）,方法一：操作界面（操作步骤）,各列创建完成后，单击工具栏中的【保存】按钮，系统自动打开“选择名称”对话框，输入新建的表的名称“student”,知识说明数据表的三个要件,列名：即字段名，最大长度为128个字符，可以包含汉字、英文字母、数字、下划线及其他符号。同一张表中列名必须唯一。数据类型：即字段的数据类型。见下页。

15、允许空：为复选框，选中表示该列在插入和修改记录时可以不赋值。说明：是对此列的注释,练习请在student数据库中建下列表,练习：class的表结构练习：department的表结构,练习：teacher的表结构,练习：professional的表结构练习：course的表结构,练习：teaching的表结构练习：choice的表结构,方法二、用DDL语句创建表,43,利用SQL的DDL语句创建数据表例：,CREATE TABLE STUDENT(ID VARCHAR(13)NOT NULL,name VARCHAR(20),tot_cred INT,dept_name VARCHAR(30)N

16、OT NULL,Sbirthday datetime,Primary key(id),Foreign key(dept_name)references department如果没有department表，则暂时不键立外键),字段名,数据类型,是否能空,设置主码,设置外码,练习：用SQL在college数据库建立下表,关系模式如下：Department(dept_name,building,budget)dept_name表示系部名称，类型为VARCHAR(30),非空Building表示所在教学楼名称，类型为VARCHAR(30)Budget表示年度预算，类型为 NUMERIC(10,2)Co

17、urse(course_id,title,credits,dept_name)Course_id表示课程编号，类型为VARCHAR(10),非空Title表示课程名称，类型为VARCHAR(40)Credits表示学分，类型为intdept_name表示所属系部，类型为VARCHAR(30),45,三、利用SQL修改表,46,1、修改字段2、修改字段属性3、修改字段是否为空4、修改主键5、修改外键,-如需在表中添加列ALTER TABLE table_nameADD column_name datatype例：ALTER TABLE classADD x int,47,-如需在表中删除列ALT

18、ER TABLE table_nameDROP COLUMN column_name例：ALTER TABLE classDROP COLUMN x,48,-改变表中列的名称方法一：先删除列，再建新列方法二：EXEC sp_rename 表名.原列名,新列名例：exec sp_rename class.pnoo,pno,49,-修改表的名称EXEC sp_rename 表名,新表名例：exec sp_rename class,class1,50,2、改变表中列的数据类型ALTER TABLE table_nameALTER COLUMN column_name datatype例：Alter

19、table classAlter column pno char(5),51,3、通过修改表：建立非空/空约束,例：Alter table class alter column pno char(4)not null,52,4、通过修改表：建立主键,Use collegegoalter table studentadd constraint newpk11primary key(studentid)注意：如果在建表时，已创建主键，请先删除再建。,53,5、通过修改表：建立外键,Use collegegoalter table studentadd constraint newfkforeign

20、 key(dept_name)references department,54,练习：,1、在student数据库中分别给学生表，班级表，系部表，课程表中用图形化界面的方法建立主、外键。2、删除college表中的主、外键，通过修改语句，在college数据库中分别给学生表，系部表，课程表建立主、外键，要求用SQL语句完成，并保存代码。3、将课堂演示中修改过的表的名称，属性等修改回来。,55,鼠标右击“对象资源管理器”窗口的节点【student】的子节点【数据库关系图】，弹出快捷菜单,6、建立数据库关系图显示表间关系,2.单击【新建数据库关系图】命令，在“Management Studio”中

21、打开一个数据库关系图的标签页，并且激活了“添加表”对话框，在【表】列表框中选择表student和表class,单击【添加】按钮，并单击【关闭】按钮，关闭“添加表”对话框。在【关系图】的标签页中显示已添加的表student和class,4、单击【保存】按钮，会弹出“选择名称”对话框，输入关系图的名称“学生-班级”或者“student_class”，可以将数据库关系图保存在数据库中。,59,7、在数据表中插入数据,方法一：图形化界面插入数据,60,方法二：利用代码,语法：Insert into tablename(first_column,.last_column)values(first_val

22、ue,.last_value),61,四、利用SQL实现完整性约束,62,知识说明-数据完整性,实体完整性：又称行完整性，要求在表中不能存在完全相同的行，而且每行都要具有一个非空且又不重复的主键值。例如：学生管理系统的student表(sno,sname,ssex,)中，sno为主键，该属性不能为空,63,知识说明-数据完整性,参照完整性：又称引用完整性，指表间的规则，作用于有关联的两个或两个以上的表，通过使用主键和外键（或唯一键）之间的关系，使表中的键值在相关表中保持一致。,64,知识说明-数据完整性,用户自定义完整性：指针对某一具体关系数据库的约束条件，它反映某一具体应用所涉及的数据必须满

23、足的语义要求。SQL Server 2012提供非空约束、对属性的check约束、对元组的check约束、触发器等来实现用户的完整性要求。,65,1、非空、主、外键约束（前节已学习）2、unique约束3、check子句4、创建规则,66,2、建立唯一键,67,例：,ALTER TABLE student1ADD CONSTRAINT uk_bjmcUNIQUE NONCLUSTERED(id),68,练习：,1、在student数据库中分别向学生表，班级表，系部表，课程表中用图形化界面的方法分别插入5条记录2、在college数据库中分别向学生表，系部表，课程表分别插入3条记录，要求用SQL

24、语句完成，并保存代码。3、建立student、college数据库中各表的数据关系图，并保存.、给student、college数据库中的数据表中添加唯一键约束。,69,3、check约束,70,将数据表的sscore字段设置在306到650的范围之间。,【任务分析】假定该校学生的入学成绩的最高分是650，最低分是306分，即在306到650的范围之间，该任务就要为sscore设置检查约束，如果输入的入学成绩超出这个范围，系统会认为输入的信息有误拒绝接收数据，从而保证了数据的完整性,71,操作方式创建步骤：展开“对象资源管理器”窗口中的节点“dbo.student”，右击其子节点“约束”，弹出

25、快捷菜单。选择快捷菜单中的“新建约束”命令，打开“CHECK约束”对话框，单击【添加】按钮，单击“表达式”右侧 按钮，弹出“CHECK 约束表达式”对话框。,72,在其中编辑约束条件“sscore=306 and sscore=650”。单击“确定”按钮，并单击“CHECK 约束”对话框中的【关闭】按钮。创建结果：刷新节点“dbo.student”，展开其下的子节点“约束”，就会看到新产生的叶节点CK_student，就是刚才创建的检查约束。,73,通过修改表建立check约束：,例：ALTER TABLE studentADD CONSTRAINT chk1CHECK(sscore=306

26、and sscore=650）,74,删除约束：,方法一：在图形界面删除方法二：drop constraint k1（和删除表，数据库不同，不能直接这样删除）,75,删除约束：,alter table studentdrop constraint ck1,76,4、规则的建立与绑定,语法：CREATE RULE 规则名AS变量 规则定义 GOEXEC sp_bindrule 规则名,表名.字段名,77,例：创建规则使学生表中的学生姓名必须是个字符，且都在a-z之间,CREATE RULE rule1ASX like a-za-za-za-zGOEXEC sp_bindrule rule1,st

27、uden.sname,78,解除规则绑定：,例：Use studentgoEXEC sp_unbindrule student.sname drop rule rule1,79,练习：利用SQL实现,、在student数据库中建立check约束，使学生性别只能是男或女、建立约束要求使college数据库中的学生总学分大于0、建立规则要求使college数据库中的学生总学分大于5、建立规则要求使student数据库中的班号信息必须中个字符，且每个字符必须在0到9字间、删除以上约束，并保留以上代码,80,四、语法,81,SELECT 语句的基本语法格式 SELECT select_list INT

28、O new_table_name FROM table_list WHERE search_condition1 GROUP BY group_by-list HAVING search_condition2 ORDER BY order_listASC DESC,查询语句简单查询（基本的投影、选择操作）,82,数据库关系基本运算,关系数据库的关系之间可以通过运算获取相关的数据，其基本运算的种类主要有选择、投影和连接运算。投影 从一个表中选择一列或者几列形成新表的运算称为投影。投影是对数据表的列进行的一种筛选操作，新表的列的数量和顺序一般与原表不尽相同。在SQL Server中的投影操作通过S

29、ELECT子句中限定列名列表来实现。,83,选择 从一个表中选择若干行形成新表的运算称为选择。选择是对数据表的行进行的一种筛选操作，新表的行的数量一般跟原表不尽相同。在SQL Server中的选择操作通过WHERE 子句中限定记录条件来实现。连接（中级）从两个或两个以上的表中选择满足某种条件的记录形成新表的运算称为连接。连接与投影和选择不同，它的运算对象是多表。它可以分为交叉连接、自然连接、左连接、右连接等不同的类型。,84,程序代码：USE collegeGOSELECT*FROM studentGO,85,投影操作,查询所有信息，可以在select子句中依次列出表中的所有列，也可以使用通配

30、符“”,From子句指明数据来源于哪张数据表或视图,86,投影操作例:在学生数据库中查询student表的学生编码、姓名,程序代码：USE studentGOSELECT sno,snameFROM student,87,例：查询学生数据库中 student表的学生的姓名和年龄，并且要求在显示姓名时用“姓名”字段名显示，年龄用“年龄”字段名显示。,88,程序代码：USE studentGOSELECT sname 姓名,YEAR(GETDATE()-YEAR(sbirthday)年龄 FROM studentGO,查询 student表的学生的姓名和年龄,该列的别名，用来友好地显示相关查询字段

31、的 信息。,Year()函数的功能是返回年份，getdate()函数的功能是返回系统当前的时间和日期。YEAR(GETDATE()-YEAR(sbirthday)表达式用来计算学生的年龄。,89,李姗 信管教研室,选择操作,查询表中表中的若干行，可以通过WHERE、TOP和DISTINCT来实现。,90,选择操作,WHERE子句可以筛选出满足条件的记录TOP可以对记录的条数进行具体限定DISTINCT则可以清除一些重复的行,91,Select语句格式 select top n distinct列名列表 from 表名 where 查询条件,92,程序代码：USE college GOSELEC

32、T*FROM studentWHERE tot_cred=64 AND dept_name=外语部GO,93,例：查询大学数据库中student表的“外语部”总学分数是64学分的同学信息,两个条件之间是并且的关系，可以用逻辑运算符and。,程序代码：USE college GOSELECT TOP 3*FROM courseWHERE course_id=0101001GO,94,例：在大学数据库中查询前三条course表中编号“0101001”课程信息,表示查询前3条记录。还可在字段列表之前用top 20 percent关键字，查询结果将只显示前20%的记录。Top字句位于select和列名

33、列表之间。,程序代码：USE college GOSELECT distinct nameFROM student,95,例：在大学数据库中student表中所有同学的姓名，并且相同姓名不重复显示。,Distinct用于清除一些重复的行，使用时介于select和列名列表之间。,练习：在数据xkgl数据库中查询（示例数据库）,1、查询teacher表中所有女教师的信息。2、查询teacher表中所有女教师的姓名和职称。3、查询07010211班年龄在18岁以上的学生信息、查询学分值大于等于4的课程的名称、查询年龄大于40岁的教授的姓名和性别、查询所有课程的名称,96,排序显示与模糊查询,【例】在

34、大学数据库中student表中查询全体学生的信息，查询结果按学号升序，总学分降序排列。,97,排序显示,对于结果的排序可以使用ORDER BY 语句来控制，其中ASC表示升序，DESC表示降序。,98,USE college GOSELECT*FROM studentORDER BY ID ASC,tot_cred DESC GO,99,层次递进说明，不是并列的。,模糊查询,【例】在student表中查询陈姓学生的基本信息，查询结果以学分降序排序。分析：这里的查询条件“陈姓”学生含义比较宽泛，不能直接使用name=陈来表示，而要使用LIKE子句并跟上通配符的形式。查询结果排序则可以使用ORDE

35、R BY 语句来控制，其中ASC表示升序，DESC表示降序。,100,使用字符匹配运算符,101,USE studentGOSELECT*FROM studentWHERE name LIKE 陈%ORDER BY tot_cred DESCGO,102,程序代码,1、在student表中查询全体学生信息，查询结果按出生日期降序排列。(order by sbirthday desc)2、在teacher表中查询所有女教师的信息，按出生日期升序排列。3、在student表中查询男学生信息，按入学时间升序排列。、查询09级所有班级的班长姓名。、查询姓“张”、“李”、“王”的教师的信息。、查询课程名

36、中有“计算机”的课程的信息,103,练习：在数据xkgl数据库中查询（示例数据库）,104,WHERE子句的常用查询条件,比较运算符、=、=、!、!=比较结果是布尔值，即：true(表达式结果为真)、false及unknown。数据类型为text、ntext、image的数据不可用比较运算符。,105,使用逻辑运算符 and、or、not三种。BETWEENAND运算符查询指定条件范围的记录 使用BETWEENAND运算符查询时在BETWEEN运算符和AND运算符后面都需要给定一个值。其语法格式如下：字段1 BETWEEN 值1 AND 值2,106,集合运算符in、not in，用来查找某个

37、值是否属于某个集合记录。,107,例：,查询总分超过500的学生信息查询总分在500到1000之间的学生信息查询总分在小于500或者大于1000的学生信息查询总分既不小于500也不大于1000的学生信息,108,例：查询班号不是0101的学生信息,use studentgoselect*from studentwhere not classno=0101,109,练习：,查询职称为教授或者年龄超过50岁的女教师信息。查询出生日期在1975到1980间的教师信息。查询不在1975出生的教师信息。查询“09软件技术1班”或者是“09软件测试1班”的班级信息。查询既不是“09软件技术1班”也不是“0

38、9软件测试1班”的班级信息。,110,用函数查询,学习目标：知道常用的内置函数的种类会使用字符串等常用的内置函数,111,程序代码：SELECT name 姓名FROM studentWHERE LEFT(name,1)=陈GO,112,Left(字符型表达式,整型表达式)函数返回字符串中从左边开始指定个数的字符。等价于使用通配符“陈%”。,常用日期函数,GETDATE()-函数以DATETIME 的缺省格式返回系统当前的日期和时间DAY()-函数返回date_expression 中的日期值MONTH()-函数返回date_expression 中的月份值YEAR()-函数返回date_ex

39、pression 中的年份值DATEDIFF(,)函数返回两个日期之间的天数/月数、年数。,113,例如：SELECT DATEDIFF(day,2007-12-20,2008-12-30)SELECT DATEDIFF(month,2007-12-20,2008-12-30)SELECT DATEDIFF(year,2007-12-20,2008-12-30),114,常用字符串函数,LTRIM()-函数把字符串头部(左边)的空格去掉RTRIM()-函数把字符串尾部(右边)的空格去掉LEFT(),RIGHT(),SUBSTRING()-函数返回部分字符串 例如：select substring(abd,2,2),115,练习：,、查询student表中所有同学的姓名和年龄。、查询student表中所有男同学的姓名和年龄。3、查询所有1994年出生的同学姓名、月份、日期、查询所有同学的姓名以、出生的天数。5、查询并返回所有同学姓名及学号最后两位数6、查询并返回所有姓李的同学的名,116,