SQL Server 2005 数据库基础及应用技术教程与实训4

【例4.1】使用T-SQL语句创建影像租借数据库，数据库文件及日志文件均在E:\影像\data下，数据文件初始大小为10MB，以5MB大小增长，最大为50MB。

日志文件初始大小为5MB，以1MB大小增长，最大为25MB。

CREATE DATABASE 影像租借ON( NAME=yingxiang_dat,FILENAME=’e:\影像\data\影像租借.mdf’,SIZE=10,MAXSIZE=50,FILEGROWTH=5MB)LOG ON( NAME=yingxiang_log,FILENAME=’e:\影像\data\影像租借_log.ldf’,SIZE=5,MAXSIZE=25,FILEGROWTH=1MB)GO【例4.2】对于影像租借数据库进行修改，将数据库文件的最大容量改为100MB，数据库文件修改为每次10MB大小增长。

命令如下：USE MasterALTER DATABASE 影像租借MODIFY FILE( NAME=[影像租借]’,MAXSIZE=100MB,FILEGROWTH=10MB)【例4.3】将影像租借数据库设置为只读的T_SQL语句如下。

USE masterEXEC sp_dboption '影像租借', 'read only', 'TRUE'【例4.4】删除影像租借数据库，在查询窗口中输入如下语句，并执行即可。

DROP DA TABASE 影像租借【例4.5】打开影像租借数据库。

USE 影像租借【例4.6】以下语句可以查看当前服务器上所有数据库的信息。

sp_helpdbsp_helpdb 影像租借【例4.7】查看影像租借数据库的所有文件信息的命令如下：use 影像租借goexec sp_helpfile【例4.8】将影像租借数据库更名为'影碟租借。

Exec sp_renamedb '影像租借' , '影碟租借'【例4.9】把影像租借数据库的大小收缩到50MB，并保留释放的文件空间。

数据库应用基础A实训一、实训目的1、进一步明确数据库管理的主要功能。

2、进一步掌握SQL Server 2005数据库及其组成。

3、进一步熟悉主要数据库对象的操作。

4、提高应用SQL Server 2005数据库的能力。

5、提高T-SQL语句的编写能力。

二、实训内容（一）关系运算练习对于学生选课关系，其关系模式如下：学生（学号，姓名，年龄，所在系）课程（课程名，课程号，先行课）选课（学号，课程号，成绩）试用关系代数完成下列查询：①求成绩不及格的学生学号和姓名Π学号，姓名（δ成绩<60（学生∞选课））②求学过数据库课程的学生学号和姓名Π学号，姓名（δ课程名=‘数据库’（学生∞选课∞课程））③求数据库成绩不及格的学生学号和姓名Π学号，姓名（δ课程名=‘数据库’∧成绩<60（学生∞选课∞课程））④求学过数据库和数据结构课程的学生学号和姓名Π学号，姓名（δ课程名=‘数据库’（学生∞选课∞课程））∩Π学号，姓名（δ课程名=‘数据结构’（学生∞选课∞课程））⑤求学过数据库或数据结构课程的学生学号和姓名Π学号，姓名（δ课程名=‘数据库’（学生∞选课∞课程））∪Π学号，姓名（δ课程名=‘数据结构’（学生∞选课∞课程））⑥求没学过数据库课程的学生学号Π学号（学生）-Π学号（δ课程名=‘数据库’（选课∞课程））（二）E-R图设计1、有以下学校选课管理的信息系统开发需求：（1）学生查看成绩、查看课程和开课教师信息。

（2）学生选课。

（3）教师查看所开课的选课学生基本信息。

给出E-R图，然后给出关系模式。

2）关系模式学生（*学号，姓名，性别，&专业号）教师（*工号，姓名，性别，&系编号）课程（*课程号，课程名，课程性质（公共、专业），学分）学院（*学院编号，名称）系（*系编号，名称，&学院编号）专业（*编号，名称，&系编号）学生－课程（*&学号，*&课程号，考试成绩）教师－课程（*&工号，*&课程号，上课时间）注：*表示主码，&表示外码2、请设计一个连锁商店数据库，保存商店的信息，包括：商店编号，商店名称，电话。

第1部分上机实验指导这一部分为SQL Server 2005的上机实验内容，包括12个实验，每个实验和课堂教学紧密配合，且由浅入深地安排实验内容，旨在使读者通过上机实践领会课堂学习内容，达到巩固与提高的目的。

上机实验指导部分使用的数据库，如果没有特别指明，则为studentsdb 数据库，该数据库从实验2开始创建。

实验1 SQL Server 2005的安装及管理工具的使用1．实验目的（1）了解SQL Server 2005安装对软、硬件的要求，掌握安装方法。

（2）了解SQL Server的注册和配置方法。

（3）了解SQL Server 2005包含的主要组件及其功能。

（4）熟悉SQL Server 2005管理平台的界面及基本使用方法。

（5）了解数据库及其对象。

（6）了解在SQL Server管理平台中执行SQL语句的方法。

2．实验内容及步骤（1）根据软、硬件环境的要求，安装SQL Server 2005（在安装过程中，请选择安装示例数据库AdventureWorks）。

（2）通过选择“开始”→“程序”→Microsoft SQL Server 2005→“配置工具”→SQL Server Configuration Manager菜单项，打开“SQL Server配置管理器”窗口，在界面左边的树目录中选择“SQL Server 2005 服务”，在右边的项列表区中选择SQL Server。

并且通过该窗口“启动服务”和“停止服务”图标来启动和停止SQL Server服务。

（3）通过“开始”→“程序”→Microsoft SQL Server 2005→SQL Server Management Studio 菜单项打开SQL Server管理平台。

（4）在SQL Server管理平台中，注册服务器。

查看本地已注册的SQL Server。

查找网络上另一台计算机，并且注册该机上的SQL Server，注册时使用“Windows身份认证”或“SQL Server身份认证”的连接方式。

课后实训参考答案单元1（SQL Server概述）1、使用SQL语句。

在Sale数据库中创建名为MyDataType的用户定义数据类型，数据类型为NV ARCHAR，长度为20，该列允许为NULL。

USE SaleGOEXEC sp_addtype MyDataType,'NVARCHAR(20)','NULL' GO单元2（单表数据）使用查询窗口或sqlcmd实施查询。

本实训使用Xk数据库。

--1．查看系部编号为“03”的系部名称。

USE XkGOSELECT DepartNameFROM DepartmentWHERE DepartNo='03'GO--2．查看系部名称中包含有“工程”两个字的系的全名。

USE XkGOSELECT DepartNameFROM DepartmentWHERE DepartName LIKE'%工程%' GO--3．显示共有多少个系部。

USE XKGOSELECT'系部总数'=COUNT(*)FROM DepartmentGO--4．显示“01”年级共有多少个班级。

USE XKGOSELECT'01 级班级数'=COUNT(*)FROM ClassWHERE ClassNo LIKE'2001%'GOSELECT'01 级班级数'=COUNT(*)FROM ClassWHERE ClassName LIKE'01%'GOSELECT'01 级班级数'=COUNT(*)FROM StudentWHERE ClassNo LIKE'2001%'GO--5．查看在“周二晚”上课的课程名称和教师。

USE XKGOSELECT'课程名称'=CouName,'任课教师'=TeacherFROM CourseWHERE SchoolTime='周二晚'GO--6．查看姓“张”、“陈”、“黄”同学的基本信息，要求按照姓名降序排序查询结果。

数据库应用技术SQLServer2005基础篇课程设计一、课程设计目的本次课程设计旨在让学生通过实际操作，掌握SQLServer2005基础知识，并能运用学过的知识进行实际操作与解决问题，提高学生的数据库应用水平。

二、课程设计内容1. 环境搭建使用SQLServer2005 Management Studio（简称SSMS）进行环境搭建，包括创建数据库、数据表、视图等。

2. SQL语言基础讲解SQL语言基础知识，包括DDL（数据定义语言）、DML（数据操作语言）、DCL（数据控制语言）等，让学生了解数据库的基本操作。

3. 数据表设计讲解数据表设计的基本原则和方法，包括数据类型、字段约束、数据表关系等，让学生能够熟练地设计出符合要求和规范的数据表。

4. SQL查询讲解SQL查询的基本语法，包括SELECT、FROM、WHERE、GROUP BY、HAVING、ORDER BY等关键字。

并通过实例让学生了解SQL查询的应用场景和技巧。

5. 数据库备份与还原通过讲解SQLServer的备份和还原功能，让学生了解备份和还原数据库的方法和注意事项，以防止数据丢失和恢复数据。

三、课程设计要求1. 实验环境要求•操作系统：Windows 7及以上•数据库软件：SQLServer2005及以上•SSMS（SQLServer Management Studio）2. 实验过程•独立完成实验，不得抄袭和剽窃。

•所有实验均需要提交实验报告，包含实验目的、实验步骤、实验结果以及总结等内容。

•实验过程中需记录操作步骤和注意事项。

四、课程设计实验题目实验一：环境搭建1.安装SQLServer2005和SSMS。

2.创建一个名为。

SQLServer2005数据库原理与实训教程课程设计概述本课程设计旨在帮助学生掌握SQLServer2005数据库的原理、操作和应用，包括数据库的基本概念和原理、SQLServer2005的安装和配置、数据库的创建、结构与数据类型、SQL语言和查询操作、备份和恢复、安全性与权限管理等方面内容。

同时，本设计还涵盖了实际应用和实训环节，帮助学生掌握实际操作技能。

教学目标1.了解SQLServer2005的基本概念和原理；2.能够熟练安装并配置SQLServer2005；3.理解数据库的创建，包括数据库结构与数据类型的设计；4.掌握SQL语言和查询操作；5.实现数据库备份和恢复；6.熟悉数据库安全性与权限管理。

教学内容第一章：SQLServer2005数据库概述•数据库基本概念和原理•SQLServer2005与其他数据库管理系统的比较•安装SQLServer2005并配置第二章：数据库的创建与管理•数据库结构的设计•数据库的存储与数据类型•数据表的创建和管理•数据库的备份和恢复第三章：SQL语言基础•SQL语言的特点和语法规则•SQL语言的数据类型、运算符和函数•SQL语句的分类和执行顺序•SQL语句的调试与优化第四章：常用查询操作•SELECT语句及其语法规则•WHERE子句及其用法•JOIN操作及其用法•聚合函数及其用法第五章：高级查询操作•子查询及其用法•视图的创建及其用法•存储过程及其用法•触发器及其用法第六章：数据库安全性与权限管理•数据库安全性的概念和原则•数据库用户和角色的管理•数据库对象权限的分配和控制•数据库访问日志的监控实训环节除以上理论内容外，本课程设计还将有配套实训环节，具体内容如下：实验1：SQLServer2005的安装和配置•了解SQLServer2005的安装包和安装流程•安装SQLServer2005并配置数据库实例实验2：数据库结构的设计与创建•参考某公司的业务需求，设计相关的数据库结构•根据设计，创建相关数据库和数据表实验3：SQL语言的基本操作•给定一些数据查询需求，通过编写SQL语句实现查询操作实验4：高级查询操作实践•给定一些复杂查询需求，通过编写SQL语句实现查询操作•利用存储过程或者触发器实现数据的自动生成和自更新评分方案1.日常表现和参与度：40分2.课程设计报告：40分3.实训项目完成情况：20分总结本课程设计旨在帮助学生掌握SQLServer2005数据库的原理和操作，通过理论课程和实训环节帮助学生实践操作技能。

SQL Server 2005实验指导书实验一：SQL SERVER网络数据库基础实验目的：1.了解安装SQL SERVER 2005服务器版的硬件要求和软件环境。

2.掌握SQL SERVER 2005服务器版的安装方法。

3.掌握SQL SERVER服务器安装后的安全配置。

4.掌握配置SQL SERVER 2005服务器的方法5.掌握在客户机上注册SQL SERVER 2005的方法实验内容：1）在客户机上安装SQL Server 2005系统2）以WINDOWS XP为实验环境，以《安全配置清单》的要求为准做操作系统的安全配置3）对客户机的SQL Server作安全配置：安装数据库补丁包、修改SA账户密码、修改数据库默认端口4）分别使用SQL Server 服务管理器和控制面板启动服务5）在客户端创建一个服务器组teacher,并在此组下注册一个服务器。

以服务器的SA用户登录，查看有哪些数据库及数据库有哪些对象。

6）在服务器上，建立自己的账户，并用该账户登录数据库服务器。

a)在保证唯一性的前提下，建议账户名格式为：班级_学号或班级_姓名全拼。

密码由学生自己设定。

b)请记住自己的账户和密码，以便以后实验时使用。

实验二：数据库操作实验目的：1）掌握用企业管理器和Transcat-SQL语句创建数据库的方法2）掌握修改数据库、数据库更名的方法3）掌握删除数据库的方法实验内容：数据库名称为学生用户名，具体要求如下：1)主要数据文件：逻辑文件名为Test1Data1，物理文件名为<库名1>.mdf,初始容量为1MB，最大容量为10MB，增幅为1MB。

2)次要数据文件：逻辑文件名为Test1Data2,物理文件名为<库名2>.ndf,初始容量为1MB，最大容量为10MB，增幅为1MB。

3)事务日志文件：逻辑文件名为Test1log1,物理文件名为<库名1>.ldf，初始容量为1MB，最大容量为10MB，增幅为512KB。

SQL Server 2005基础应用一．数据库的基本操作--创建数据库create database new_db2on primary(name='new.mdf',filename='e:\new.mdf',size=5mb,maxsize=50mb,filegrowth=10%)--收缩数据库alter database new_dbmodify file(name='new_db',size=15mb)--压缩数据库dbcc shrinkdatabase('new_db',1)--重命名数据库exec sp_renamedb'new_db','Jasxu_db'--删除数据库drop database new_db2二．数据库表的基本操作--创建数据库create database st_dbon primary(name='st.mdf',filename='e:\st,mdf',size=5mb,maxsize=50mb,filegrowth=20%)--删除Jasxu_db数据库drop database Jasxu_db--在st_db数据库中编辑use st_db--创建表create table table_name(学号int primary key identity,--这里的identity意思就是将标志规范设置为递增名称char(6)not null,专业方向varchar(10)not null,系部代码char(2)not null,备注varchar(50))--查看表的基本信息exec sp_help table_name--重命名表exec sp_rename'table_name','new_table'--重命名列exec sp_rename'new_table.备注','其他','column'--添加新列alter table new_table add新列char(10)--更改列的数据类型alter table new_tablealter column新列int not null--删除列alter table new_tabledrop column新列--删除表drop table new_table--案例解析create table t2(id int not null,us varchar(30))--查询表里面的内容select*from t1--删除表的所有数据truncate table t1--创建主键约束alter table t1add constraint pkprimary key clustered(id)--创建外键约束alter table t2add constraint wzforeign key(id)references t1(id)--references代表参照哪个表的主键设置外键三．数据库表的增加、删除、修改--创建系部表create table系部(系部代码char(6)not null primary key,系部名称varchar(30)not null,系主任char(8))--创建专业表create table专业表(专业代码char(4)not null primary key,专业名称varchar(20)not null,系部代码char(6)constraint wz11 references系部(系部代码))--创建班级表create table班级表(班级代码char(9)not null primary key,班级名称varchar(20),专业代码char(4)constraint wz1 references专业表(专业代码),系部代码char(6)constraint wz2 references系部(系部代码),备注varchar(50))--创建学生表create table学生表(学号char(12)not null primary key,姓名char(8),性别char(2),出生日期datetime,入学时间datetime,班级代码char(9)constraint wz3 references班级表(班级代码),系部代码char(6)constraint wz4 references系部(系部代码),专业代码char(4)constraint wz5 references专业表(专业代码))--在new_table表中添加数据insert into new_table values('Jasxu','计算机','01','无')--选择性的插入数据insert into new_table(名称,专业方向,系部代码)values('xsw','软件工程','02') --省略values的insert语句insert into new_table (名称,专业方向,系部代码)select名称,专业方向,系部代码from new_table--修改new_table表update new_table set系部代码='01'update new_table set专业方向='软件工程'where专业方向='计算机'--删除new_table中的内容delete new_table where专业方向='软件工程'delete new_table where学号='10'四．数据库表的简单查询--查询new_table表中所有信息内容select*from new_tableselect学号,名称,专业方向,系部代码,其他from new_table--输出表中的部分字段select学号,名称from new_table--选择表中若干记录(去掉结果中的重复行)select distinct系部代码from new_table--限制返回的行数select top 3 *from new_table--查询学号大于的信息select*from new_table where学号>13--确定范围(between and)select*from new_table where学号between 12 and 16--确定集合（in，not in）select*from new_table where学号in(12,13,14,15)select*from new_table where学号not in(12,13,14,15)--字符匹配select*from new_table where名称like'徐_'--两个字的姓名select*from new_table where名称like'徐__'--三个字的姓名select*from new_table where名称like'徐%'--%代表任意长度select*from new_table where名称like'徐\%'escape'\'--通配符的转换--清空数据truncate table new_table--插入数据insert into new_table values('张学友','网络','01','没有','411')insert into new_table values('刘德华','计算机','02','没有','412') insert into new_table values('舒淇','计算机','01','没有','413')insert into new_table values('梁咏琪','动漫','02','没有','431')insert into new_table values('杨千嬅','计算机','01','没有','465') insert into new_table values('李宇春','动漫','02','没有','485')insert into new_table values('蔡依林','网络','01','没有','468')insert into new_table values('郑源','计算机','02','没有','510')insert into new_table values('陈楚生','动漫','01','没有','550')insert into new_table values('张韶涵','计算机','02','没有','421') insert into new_table values('猛非','动漫','01','没有','423')insert into new_table values('郑秀文','网络','02','没有','411')insert into new_table values('林俊杰','计算机','01','没有','511') insert into new_table values('羽泉','计算机','01','没有','500')insert into new_table values('郭富城','网络','02','没有','400')insert into new_table values('黄品源','动漫','02','没有','589')insert into new_table values('梁朝伟','计算机','02','没有','530') insert into new_table values('李克勤','网络','01','没有','520')insert into new_table values('陈小春','国际金融','02','没有','512') insert into new_table values('刘若英','证券期货','02','没有','421') insert into new_table values('刘嘉玲','房地产金融','01','没有','428') insert into new_table values('谭咏麟','房地产金融','02','没有','498') insert into new_table values('张学友','证券期货','01','没有','454') insert into new_table values('张卫健','证券期货','02','没有','515') insert into new_table values('周传雄','房地产金融','01','没有','532') insert into new_table values('周星驰','国际金融','02','没有','423') insert into new_table values('游鸿明','房地产金融','02','没有','447') insert into new_table values('言承旭','国际金融','02','没有','488') insert into new_table values('许志安','国际金融','01','没有','582') insert into new_table values('叶倩文','房地产金融','01','没有','495') insert into new_table values('叶世荣','房地产金融','02','没有','499') insert into new_table values('张雨生','证券期货','02','没有','531') insert into new_table values('周润发','国际金融','01','没有','531') insert into new_table values('张信哲','证券期货','01','没有','424') insert into new_table values('周渝民','证券期货','02','没有','412') insert into new_table values('太极乐队','证券期货','02','没有','423') --查询new_table表select*from new_table--涉及空值的查询select*from new_table where其他is nullselect*from new_table where not其他is null--用指定使用结果值来创建一个表(注意：在表前加一个#创建出来的是临时表)select学号,名称,高考分数into score_table from new_tableselect*from score_table--对结果进行分组select系部代码from new_table group by系部代码select专业方向from new_table group by专业方向select专业方向from new_table group by专业方向having专业方向<>'动漫'--having起到筛选作用--排序查询（asc升序desc降序）select*from new_table order by高考分数ascselect*from new_table order by高考分数desc五．数据表中对数据进行统计--查询数据库表new_table中的信息select*from new_tableselect*from new_table order by高考分数descselect top 3 *from new_table order by高考分数desc--查询总人数select count(*)as总人数from new_table--这里的as是为列重命名select count(学号)as总人数from new_tableselect count(其他)as总人数from new_table--计算整个班级高考的总分数select sum(高考分数)as总分from new_table--计算整个班级高考的平均分数select avg(高考分数)as平均分from new_table--计算整个班级高考的最大值select max(高考分数)as最大值from new_table--计算整个班级高考的最小值select min(高考分数)as最小值from new_table--对查询结果集中的所有记录进行汇总统计，并显示所有参加汇总记录的详细信息select*from new_table order by专业方向compute sum(高考分数)select专业方向,count(*)as总人数from new_table group by专业方向--统计专业方向一共多少select专业方向,count(*)as总人数from new_table group by专业方向compute count(专业方向)--统计系部总人数select专业方向,count(*)as总人数from new_table group by专业方向compute sum(count(*))六．数据库中表的连接查询--插入数据这里的go起到连接作用insert into系部(系部代码,系部名称,系主任)values('01','计算机系','老张') goinsert into系部(系部代码,系部名称,系主任)values('02','经济管理系','老陈') goinsert into系部(系部代码,系部名称,系主任)values('03','机械系','老李')goinsert into系部(系部代码,系部名称,系主任)values('04','计算机系','老梁') goinsert into专业表(专业代码,专业名称,系部代码)values('0101','软件工程','01')goinsert into专业表(专业代码,专业名称,系部代码)values('0102','网络工程','01')goinsert into专业表(专业代码,专业名称,系部代码)values('0103','信息工程','01')goinsert into专业表(专业代码,专业名称,系部代码)values('0201','工商管理','02')goinsert into专业表(专业代码,专业名称,系部代码)values('0202','物流管理','02')goinsert into专业表(专业代码,专业名称,系部代码)values('0301','模具加工','03')goinsert into专业表(专业代码,专业名称,系部代码)values('0302','机电一体化','03')goinsert into专业表(专业代码,专业名称,系部代码)values('0401','应用数学','04')goinsert into专业表(专业代码,专业名称,系部代码)values('0402','金融数学','04')goinsert into班级表(班级代码,班级名称,专业代码,系部代码,备注)values('010101','软件工程班','0101','01','暂无')goinsert into班级表(班级代码,班级名称,专业代码,系部代码,备注)values('010102','软件工程班','0101','01','暂无')goinsert into班级表(班级代码,班级名称,专业代码,系部代码,备注)values('010103','网络工程班','0102','01','暂无')goinsert into班级表(班级代码,班级名称,专业代码,系部代码,备注)values('010104','网络工程班','0102','01','暂无')goinsert into班级表(班级代码,班级名称,专业代码,系部代码,备注)values('010105','信息工程班','0103','01','暂无')goinsert into班级表(班级代码,班级名称,专业代码,系部代码,备注)values('010106','工商管理班','0201','02','暂无')goinsert into班级表(班级代码,班级名称,专业代码,系部代码,备注)values('010107','物流管理班','0202','02','暂无')goinsert into班级表(班级代码,班级名称,专业代码,系部代码,备注)values('010108','模具加工班','0301','03','暂无')goinsert into班级表(班级代码,班级名称,专业代码,系部代码,备注)values('010109','应用数学班','0401','04','暂无')goinsert into班级表(班级代码,班级名称,专业代码,系部代码,备注) values('0101010','金融数学班','0402','04','暂无')goinsert into班级表(班级代码,班级名称,专业代码,系部代码,备注) values('0101011','金融数学班','0402','04','暂无')goinsert into学生表values('010*********','刘德华','男','1988-5-5','2010-9-1','010101','01','0101')goinsert into学生表values('010*********','张学友','男','1988-1-4','2010-9-1','010102','02','0102')goinsert into学生表values('010*********','梁静茹','女','1988-2-1','2010-9-1','010103','03','0103')goinsert into学生表values('010*********','陈奕迅','男','1983-5-3','2010-9-1','010104','04','0201')goinsert into学生表values('010*********','张韶涵','女','1987-8-6','2010-9-1','010105','01','0202')goinsert into学生表values('010*********','林俊杰','男','1988-6-6','2010-9-1','010106','02','0301')goinsert into学生表values('010*********','孙燕姿','女','1984-5-3','2010-9-1','010107','03','0302')goinsert into学生表values('010*********','周华健','男','1986-8-6','2010-9-1','010108','04','0401')goinsert into学生表values('010*********','尚雯婕','女','1988-6-6','2010-9-1','010109','01','0402')goinsert into学生表values('010*********','任贤齐','男','1984-5-3','2010-9-1','0101010','02','0101')goinsert into学生表values('010*********','魏晨','男','1986-8-6','2010-9-1','0101011','03','0102')goinsert into学生表values('010*********','庞龙','男','1988-6-6','2010-9-1','010101','04','0103')goinsert into学生表values('010*********','刘若英','女','1988-5-3','2010-9-1','010102','01','0201')goinsert into学生表values('010*********','李圣杰','男','1989-8-6','2010-9-1','010103','02','0202')goinsert into学生表values('010*********','吴克群','男','1989-2-9','2010-9-1','010104','03','0301')go--连接查询（交叉查询）select*from学生表cross join班级表--列举学生表中的学生姓名和性别班级表中的班级名称select学生表.姓名,学生表.性别,班级表.班级名称from学生表cross join班级表--将两个表中相同列合为一列select学生表.姓名,学生表.性别,班级表.班级名称from学生表cross join班级表where学生表.班级代码=班级表.班级代码--自然连接select*from学生表join班级表on学生表.班级代码=班级表.班级代码select学生表.姓名,学生表.性别,班级表.班级名称from学生表join班级表on学生表.班级代码=班级表.班级代码--表的自身连接（需要取别名）select a.姓名,b.性别from学生表as a join学生表as b on a.学号=b.学号--外连接表查询create table产品(产品编号char(9)not null,产品名称varchar(20)not null)create table产品销售(产品编号char(9)not null,销量int)insert into产品values('001','显示器')insert into产品values('002','键盘')insert into产品values('003','鼠标')insert into产品销售values('001','25')insert into产品销售values('003','35')insert into产品销售values('005','30')select*from产品select*from产品销售--左外连接select*from产品left join产品销售on产品.产品编号=产品销售.产品编号--右外连接select*from产品right join产品销售on产品.产品编号=产品销售.产品编号--完全外连接select*from产品full join产品销售on产品.产品编号=产品销售.产品编号--合并结果集(把重复的过滤掉了)select姓名,性别,出生日期from学生表union select姓名,性别,出生日期from学生表七．数据库中多表查询和子查询--多表连接查询select学生表.学号,学生表.姓名,学生表.性别,班级表.班级名称,专业表.专业名称,系部.系部名称from学生表join班级表on学生表.班级代码=班级表.班级代码join专业表on学生表.专业代码=专业表.专业代码join系部on学生表.系部代码=系部.系部代码select学生表.学号,学生表.姓名,学生表.性别,班级表.班级名称from学生表join班级表on学生表.班级代码=班级表.班级代码and性别='男'--嵌套查询（子查询）带有IN运算符的子查询，in运算符的子查询返回的结果是集合select*from系部select*from学生表where系部代码='01'select*from学生表where班级代码in(select班级代码from班级表where专业代码in(select专业代码from专业表where系部代码in(select系部代码from系部where系部代码in(01))))--带有比较运算符的子查询select*from学生表where出生日期>(select出生日期from学生表where姓名='刘德华')--带有any（满足条件中的任何一个）或all(大于结果中的所有值)的子查询select*from score_table order by高考分数descselect*from score_table where高考分数>any(select高考分数fromscore_table where学号in(29,25))select*from score_table where高考分数>all(select高考分数fromscore_table where学号in(29,25))--带有exists运算符的子查询select*from score_table where exists(select*from score_table where 名称='张学友')八．数据库中数据完整性约束select*from系部where系部代码=(select系部代码from专业表where专业代码=(select专业代码from班级表where班级代码=(select班级代码from学生表where姓名='刘德华')))--使用sql语句创建唯一约束alter table系部add constraint wywy unique nonclustered(系部名称)--创建检查约束alter table score_table add constraint ck_name check(高考分数>300 and高考分数<600)--创建默认约束alter table new_table add constraint df default'我叫徐守威'for'其他' --删除约束alter table score_table drop constraint ck_name九．数据库中数据规则--创建规则create rule gz as @a>300 and @a<600--绑定规则execute sp_bindrule'gz','new_table.高考分数'select*into new_table1 from new_table--解除规则execute sp_unbindrule'new_table.高考分数'--删除规则drop rule gz--创建默认create default df_name as'男'--绑定默认execute sp_bindefault'df_name','学生表.性别'--解除默认execute sp_unbindefault'df_name','学生表.性别'--删除默认drop default df_name十．数据库中索引--创建索引create clustered index索引名on score_table(名称)create unique clustered index索引名on score_table(名称)--查看索引信息execute sp_helpindex score_table--删除索引drop index score_table.索引名十一.数据库视图--创建视图create view v1 asselect学生表.学号,学生表.姓名,班级表.班级名称,专业表.专业名称,系部.系部名称from学生表join班级表on学生表.班级代码=班级表.班级代码join专业表on班级表.专业代码=专业表.专业代码join系部on专业.系部代码=系部.系部代码--创建一般视图create view视图名asselect*from score_table--创建加密视图create view加密视图名with encryptionasselect*from score_table--创建视图及表的架构绑定create view视图及表的架构绑定with schemabindingasselect姓名,性别from dbo.score_table--在视图中增加、删除数据insert into视图名(姓名,性别)values('xushouwei','男')update视图名set姓名='徐守威'where姓名='xushouwei'delete视图名where姓名='徐守威'--删除视图drop view视图名--系统存储过程查看视图execute sp_helptext视图名十二.数据库存储过程--创建存储过程create procedure passelect学生表.学号,学生表.姓名,班级表.班级名称,专业表.专业名称,系部.系部名称from学生表join班级表on学生表.班级代码=班级表.班级代码join专业表on班级表.专业代码=专业表.专业代码join系部on专业表.系部代码=系部.系部代码--执行存储过程execute p--为存储过程加上参数create procedure p1@sex varchar(10),@id varchar(10)asselect学生表.学号,学生表.姓名,班级表.班级名称,专业表.专业名称,系部.系部名称from学生表join班级表on学生表.班级代码=班级表.班级代码and学生表.性别=@sexjoin专业表on班级表.专业代码=专业表.专业代码join系部on专业表.系部代码=系部.系部代码and系部.系部代码=@id--执行带参数的存储过程execute p1'男','01'--创建带返回参数的存储过程create procedure p2@name varchar(10),@getnum varchar(10)outputasselect @getnum=学号from学生表where姓名=@name--执行带返回参数的存储过程execute p2'刘德华',''--查看存储过程execute sp_helptextexecute sp_dependsexecute sp_help--删除存储过程drop procedure pdrop procedure p,p1drop procedure p,p1,p2十三.数据库触发器--创建触发器(执行插入操作)create trigger myinserton产品for insertasdeclare @a char(10)select @a=产品编号from insertedinsert into产品销售values(@a,0)--查询触发器中的数据select*from产品select*from产品销售--执行插入操作insert into产品values('01','电视')insert into产品values('02','电脑')--创建触发器(执行删除操作)create trigger mydeleteon产品for deleteasdeclare @a char(10)select @a=产品编号from deleteddelete产品销售where产品编号=@a--执行删除操作delete产品where产品编号='02'--查看触发器信息execute sp_helptrigger产品--删除触发器drop trigger myinsertdrop trigger mydelete十四.数据库函数--avg求平均分函数select班级名称,avg(学生表.高考分数)from班级表join学生表on班级表.班级代码=学生表.班级代码group by班级名称--max求最大值select班级名称,max(学生表.高考分数)from班级表join学生表on班级表.班级代码=学生表.班级代码group by班级名称--min求最小值select班级名称,min(学生表.高考分数)from班级表join学生表on班级表.班级代码=学生表.班级代码group by班级名称--abs(x)返回绝对值select abs(-8)--ceiling(x)返回大于或等于所给数字的最小整数select ceiling(3)--pi()(pi值)select pi()--power(x,y)返回x的y次方select power(2,3)--rand()返回~1之间的随机数select rand()--返回数据库的版本号select@@version--获取当前语言select@@language--当前时间select getdate()--取出天select day(getdate())--取出月select month(getdate())--取出年select year(getdate())--加三天(“d”表示天，“m”表示月，“y”表示年)select dateadd(d,3,getdate())as Jasxu_dateadd--取出时间的某一部分(“d”表示天，“m”表示月，“y”表示年)select datename(d,'2013-12-12')select datename(d,getdate())--排名函数select姓名,rank()over(order by高考分数desc)as名次,高考分数from学生表。