计算机基础与程序设计_第9章

1、SQL Server数据库基础本章将介绍数据库的基本概念，基本的使用方法。主要内容创建用户数据库创建表创建多表视图用SQL管理数据库用SQL查询数据库任务1创建用户数据库 在SQL Server 2008 R2中创建一个用户数据库，数据库名称为“jxgl”。学习目的：学习数据库的基本概念；学习创建、修改、删除数据库的方法； 相关知识1数据库的基本概念1、数据库数据库（Database，简称为DB）是长期存储在计算机内、有组织的、可共享的、统一管理的相关数据的集合。2、数据库管理系统数据库管理系统（Database Management System，简称DBMS）是用户与数据库之间的一个数据管

2、理软件，它的主要任务是对数据库的建立、运用和维护进行统一管理、统一控制。即用户不能直接接触数据库，而只能通过DBMS来操作数据库。3、数据模型数据模型是指数据库管理系统中数据的存储结构，是构建数据库的基础。常见的有层次模型、网状模型和关系模型。关系模型是一种应用最多的数据模型。相关知识1SQL Server概述 SQL Server是Microsoft推出的关系型数据库管理系统，Microsoft在2008年发布了SQL Server 2008.功能：全面的数据和用户管理特点：良好的图形用户界面、强大的帮助功能SQL Server系统数据库两类数据库：系统和用户数据库在安装SQL Server

3、 2008时系统创建了4个系统数据库：master ：重要model：模板msdb：存储计划信息tempdb：临时主文件扩展名：.mdf日志文件扩展名：.ldfSQL Server 用户数据库用户数据库至少具有两个文件：一个数据文件和一个日志文件。SQL Server 数据库对象1）表：是最主要的数据库对象，用来存储和错着数据的逻辑结构。2）索引3）视图：是从一个或者多个基本表中引出的表。4）约束5）默认值6）触发器7）存储过程：是一组为了完成特定功能的SQL语句集合。数据库的修改数据库的删除任务2创建表 在任务1创建的数据库“jxgl”中，创建一个关于学生基本信息的表，表名为“xs”，表结构

4、如课本P273表9-1所示。学习目的：学习创建数据表；学习表、字段、数据类型的概念；学习数据完整性及约束的概念；学习索引的概念； 相关知识2表的概念表用于存储数据库中数据，是数据库的基础，一个数据库通常会包含一个或者多个表。表是一个二位结构，由行和列组成。字段：列称为字段，每个列都有列名和其自由的属性。记录：行称为记录，记录由主键唯一确定。表结构：多个列即构成了表结构。NULL：为空值。主键：如果某一个字段可以唯一标识一条记录，可以将该字段指定为主键。相关知识2字段和字段类型类型名称数据类型长度/字节说明整数型bigint整数8取值为-263263-1int整数4取值为-231231-1sma

5、llint整数2取值为-3276832767tinyint整数1取值为0255二进制型binary(n)二进制由用户指定固定长度的二进制数据，最大长度为8000字节varbinary(n)可变长度的二进制由用户指定最大长度为8000字节位型bit位1位仅存储1或0字符型char(n)字符由用户指定nchar(n)字符由用户指定相关知识2date日期30001-1-19999-12-31datetime日期和时间81753-1-19999-12-31smalldatetime日期和时间41900-1-12079-12-31浮点型float浮点数8-1.79E+3081.79E+308real浮点

6、数4-3.40E+383.4E+38图像型image图像、视频、声音二进制存储，最大长度为230-1字节货币型money货币8-263263-1类型名称数据类型长度/字节说明日期时间相关知识2数据完整性1.实体完整性2.域完整性3.参照完整性4.用户自定义完整性约束索引1.目的：为了提高查找速度2.索引类型：（1）聚集索引 （2）非聚集索引任务3 创建多表要求：在“jxgl”数据库中，已经创建了学生表“xs”，再创建3个表：1）教师表“js”：描述教师基本信息；2）课程表“kc”：描述课程基本信息；3）选课表“xk”：选课的情况，即哪个学生选了哪个老师的哪门课；目的：学习多表概念；学习主键和外

7、键的概念；学习使用参照完整性；任务4 视图要求：在任务3创建的“jxgl”数据库中，按如下要求完成查询：1）查询所有学生的选课情况，包括学生、课程和任课教师的基本情况；2）查询“李牧”的选课情况，包括课程和任课教师的基本情况；3）查询“机械063”班并且性别为“男”的学生都选了哪些课程；目的：学习关系的概念、关系的相关操作；掌握一对一关系、一对多关系和多对多关系的概念；学习视图的概念、视图的相关操作。学习筛选条件的使用方法。相关知识4视图的概念视图是一种数据库对象，它是从一个或多个表或视图中导出的动态表，即它可以从一个或多个表中的一个或者多个列提取数据，并按照行和列来显示。数据库关系图在数据库

8、技术中，有三种关系类型，分别是一对一关系、一对多关系和多对多关系。(1)一对一关系：若建立关系的两个表，被关联的字段在两个表中分别是主键，则建立的关系为一对一关系。 (2)一对多关系：若建立关系的两个表，被关联的字段仅有一个是主键，则建立的关系为一对一关系。 (3)多对多关系：多对多关系必须借助第三个表实现。如一个学生可以选修多门课程，一门课程可以被多名学生选修，学生表和课程表是多对多关系，可以借助选课表实现多对多关系。任务5 用SQL管理数据库要求：在SQL Server中使用SQL语言创建一个学生选课数据库（xsxk），要求包含如下两个表的信息；1）学生表：包含学号、姓名、性别字段，“学号

9、”为主键。2）选课表：包含学号、课程编号字段，“学号+课程编号”为主键。3）为选课表设置外键“学号”；目的：学习T-SQL的基本使用方法；学习使用T-SQL语言创建数据库的方法；学习使用T-SQL语言创建表的方法；学习使用T-SQL语言创建外键、主键的方法；学习使用T-SQL语言设置默认值的方法；相关知识5SQL概述SQL是结构化查询语言（Structured Query Language）的缩写。SQL是一种一体化的语言，所有的关系数据库管理系统都支持SQL，其功能包括数据查询、数据操纵、数据定义和数据控制四个部分。使用CREATE DATABASE 命令创建数据库CREATE DATABA

10、SE 数据库名 ON PRIMARY | FILEGROUP 文件组名 （NAME=数据文件的逻辑名， FILENAME=数据文件的物理名 ，SIZE=文件初始大小 ，MAXSIZE=增长到的最大容量|UNLIMITED ，FILEGROWTH=文件空间的增量 ) ，() 使用CREATE DATABASE 命令创建数据库 LOG ON （NAME=日志文件的逻辑名， FILENAME=日志文件的物理名 ，SIZE=文件初始大小 ，MAXSIZE=增长到的最大容量|UNLIMITED ，FILEGROWTH=文件空间的增量 ） ，（） 在命令格式中，用 括起来的内容表示是可选的； ，（）表示重

11、复前面的内容；用括起来表示在实际编写语句时，用相应的内容替代；用 括起来表示是必选的；类似A|B的格式，表示A和B只能选择一个，不能同时都选。数据库名称在服务器中必须唯一，最长为128个字符，并且要符合标识符的命名规则。每个服务器管理的数据库最多为32767个。ON：指定存放数据库的数据文件信息。LOG ON：指明事务日志文件的相关定义。如果没有本选项，则系统会自动产生一个文件名前缀 与数据库名相同，容量为所有数据库文件 大小1/3的事务日志文件。T-SQL 语句命令的表操作create table ( 列名1 列的数据类型及宽度等特征， 列名2 列的数据类型及宽度等特征，）ALTER TAB

12、LE命令的语法如下：ALTER TABLE ADD 列约束 ，. n |WITH CHECK|WITH NOCHECK ADD ，. n |DROP COLUMN 列名|CONSTRAINT 约束名 ，. n |ALTER COLUMN 列名 新数据类型（新数据宽度，新小数位数） | CHECK|NOCHECK CONSTRAINT ALL 约束名 ，. n ALTER TABLE 命令增加列增加列约束启用或禁止约束新添加的外键或检查约束独立增加约束删除列删除约束修改列启用或禁止约束任务6 用SQL查询数据库要求：使用SQL语句，查询任务5数据库中学生选课情况，按如下要求查询；1）查询所有学生

13、的选课情况；2）查询“zhang”的选课情况；3）按“学号”分组显示每个学生的课程数；目的：学习INSERT语句的使用方法；学习SQL语句创建视图的方法；学习SQLECT语句的使用方法；学习UPDATE语句的使用方法；学习DELETE语句的使用方法；学习SQL中表达式的使用方法；相关知识6SQL查询语法结构查询语句SELECTSELECT命令的语句格式：SELECT ALL / DISTINCT */字段列表FROM , WHERE条件表达式GROUP BY HAVING ORDER BY ASC/DESCApplication Logic客户程序查询请求查询结果集SQL SERVERA B

14、C D E F G SELECT * FROM tblstudent查询产生一个虚拟表，看到的是表形式显示的结果，但结果并不真正存储，每次执行查询只是现从数据表中提取数据，并按照表的形式显示出来什么是查询？查询全部的行和列SELECT * FROM tblstudent 或SELECT stud_id,stud_name,stud_sex,birth,enroll_date, identity_id,class_id FROM tblstudent查询部分列SELECT stud_id,stud_name FROM tblstudentSELECT stud_name, year(getdat

15、e() - year(birth) FROM tblstudent数据查询-基础查询经过计算值消除取值重复的行SELECT distinct stud_id FROM tblscoreSELECT top 3 * FROM tblstudentSELECT top 3 percent * FROM tblstudent 比较大小（简单查询）（含别名的使用）select Stud_name 姓名，年龄=year(getdate()-year(birth) from tblStudent where year(getdate()-year(birth) 20数据查询-distinct和top n等

16、使用top n返回前n条记录确定范围（简单查询）select Stud_name,class_id, year(getdate()-year(birth)from tblStudent where year(getdate()-year(birth) BETWEEN 20 AND 23 SELECT Stud_id 学号,stud_name 姓名,stud_sex 性别FROM tblstudentWHERE class_id in (310220,340431,320531)字符串匹配（LIKE 或NOT LIKE）SELECT Stud_name FROM tblStudentWHERE

17、Stud_name LIKE 诸葛_ 数据查询-in和like使用谓词in或not in空值的查询SELECT * FROM tblscore WHERE score IS NULL使用常量列SELECT 学号=stud_id,课程号=course_id,3 AS 学期,成绩=scoreFROM tblstudent思考：非空值怎么查？数据查询-空值SELECT * FROM tblscore WHERE score IS not NULL注：不能书写成：not is null多重条件查询SELECT Stud_name 姓名,年龄=year(getdate()-year(birth) FRO

18、M tblStudent WHERE class_id= 310220 AND year(getdate()-year(birth)20 and year(getdate()-year(birth)=60SELECT COUNT (*) as 及格人数 FROM Score WHERE Score=60数据查询-聚合函数分组查询语法结构Select distinct , . n From where Group by Having 数据查询-分组在学生成绩表tblscore中（1）查询各个课程号及相应的选课人数。（2）查询每个学生选修的课程门数。（3）查询选修了3门以上课程的学生学号。（4）查询有3门以上课程是90分以上的学生的学号及（90分以上的）课程数数据查询-分组（1）select course_id,count(Stud_id) from tblscore group by course_id （2）select stud_id,count(course_id) from tblscore group by