select *
from Employees
--查询Employees表中每个雇员的地址和电话
select Address, PhoneNumber
from Employees
--查询EmployeesID为000001的雇员的地址和电话
select Address, PhoneNumber
from Employees
where EmployeeID='000001'
go
--查询月收入高于2000元的员工号码
select EmployeeID
from Salary
where InCome >2000
go
--查询1970年以后出生的员工的姓名和住址
select Name, Address
from Employees
where Birthday >= '1970-01-01'
go
--查询所有财务部员工的号码和姓名
select PhoneNumber, Name
from Employees
where DepartmentID =
(select DepartmentID
from Departments
where DepartmentName = '财务部')
go
数据库实验总结【一】试验内容
1、 数据表的建立
基本表《简单的》带有主键
带有外码约束的(外码来自其他表或者本表)
2、 数据表的修改
添加删除列
修改列属性类型
添加删除约束(约束名)
元组的添加,修改,删除
删除数据表
试验过程
1、create table student
(
sno char(9) primary key , /*sno是主码 列级完整性约束条件*/
sname char(20) unique, /*sname取唯一值*/
ssex char(2),
sage smallint, /*类型为smallint*/
sdept char(20) /*所在系*/
)
create table course
(
cno char(4) primary key, /*列级完整性约束条件,cno是主码*/
cname char(40),
cpno char(4), /*cpno的含义是先行课*/
ccredit smallint,
foreign key (cpno) references course(cno)
/*表级完整性约束条件,cpno是外码,被参照表是course,被参照列是cno*/
)
create table sc
(
sno char(9),
cno char(4),
grade smallint,
primary key (sno,cno),
/*主码有两个属性构成,必须作为表级完整性进行定义*/
foreign key (sno) references student(sno),
/*表级完整性约束条件,sno是外码,被参照表是student*/
foreign key (cno) references course(cno),
/*表级完整性约束条件,cno是外码,被参照表示course*/
)
例1、create table s
(
cno varchar(3), /*变长的字符串,输入2个字符就是两个字符不会补空格*/
sname varchar(20),
status int,
city varchar(20),
constraint pk_sno primary key(sno), /*约束条件的名字为pk_sno*/
)
create table p
(
pno varchar(3),
pname varchar(20),
color varchar(3),
weight int,
constraint pk_pno primary key (pno), /*约束条件的名字是pk_pno*/
)
create table j
(
jno varchar(3),
jname varchar(20),
city varchar(20),
constraint pk_jno primary key(jno) /*约束条件的名字为pk_jno*/
)
例2、create table spj
(
sno varchar(3), /*第一个表中的主码*/
pno varchar(3),
jno varchar(3),
qty int, /*数量*/
constraint pk_spj primary key(sno,pno,jno), /*主码由3个属性组成*/
foreign key(sno) references s(sno),
/*表级完整性约束条件,sno是外码,被参照表是s*/
foreign key(pno) references p(pno),
/*表级完整性约束条件,pno是外码,被参照表是p*/
foreign key(jno) references j(jno),
/*表级完整性约束条件,jno是外码,被参照表是j*/
)
2、数据表的更改
在s表中添加一个concat 列
alter table s add concat varchar(20)
在s表中删除concat 列
alter table s drop column concat
更改s表 concat列的属性 把长度由20改为30
alter table s alter column concat varchar(30)
联系方式 名字为concat 修改属性为唯一的 属性名为con_concat
alter table s add constraint con_concat unique(concat)
删除约束关系con_concat
alter table s drop constraint con_concat
/*插入一个元组*/
insert into s valus(‘s1’,’精益’,20,’天津’) /*20不能写成’20’*/
试验中的问题的排除与总结:
1、在创建spj时
有三个实体所以从3个实体中取主码,还有一个数量属性也要写上
主码由那3个主码确定
2、更改一个数据库中数据表时一定要先使该数据库处于正在使用状态
3、constraint
是可选关键字,表示 primary key、not null、unique、foreign key 或 check 约束定义的开始。约束是特殊属性,用于强制数据完整性并可以为表及其列创建索引。
4、--go可以不加但是要注意顺序 注:go --注释 提示错误
5、注意添加一个空元素用 null
附 sql备份
--创建一个数据库 student
create database student
go
--在数据库student中创建表student course sc 注意顺序
use student
----------------------------------------------------------------
create table student
(
sno char(9) primary key, /*sno是主码 列级完整性约束条件*/
sname char(10) unique, /*sname取唯一值*/
ssex char(2),
sage smallint, /*类型为smallint*/
sdept char(20) /*所在系*/
)/*要加*/
-----------
数据库实验总结【二】我在sql server 索引基础知识系列中,第一篇就讲了记录数据的基本格式。那里主要讲解的是,数据库的最小读存单元:数据页。一个数据页是8k大小。
对于数据库来说,它不会每次有一个数据页变化后,就存到硬盘。而是变化达到一定数量级后才会作这个 *** 作。 这时候,数据库并不是以数据页来作为 *** 作单元,而是以64k的数据(8个数据页,一个区)作为 *** 作单元。
区是管理空间的基本单位。一个区是八个物理上连续的页(即 64 kb)。这意味着 sql server 数据库中每 mb 有 16 个区。
为了使空间分配更有效,sql server 不会将所有区分配给包含少量数据的表。sql server 有两种类型的区:
统一区,由单个对象所有。区中的所有 8 页只能由所属对象使用。
混合区,最多可由八个对象共享。区中八页的每页可由不同的对象所有。
通常从混合区向新表或索引分配页。当表或索引增长到 8 页时,将变成使用统一区进行后续分配。如果对现有表创建索引,并且该表包含的行足以在索引中生成 8 页,则对该索引的所有分配都使用统一区进行。
为何会这样呢?
其实很简单:
读或写 8kb 的时间与读或写 64 kb的时间几乎相同。
在 8 kb 到 64 kb 范围之内,单个磁盘 i/o 传输 *** 作所花的时间主要是磁盘取数臂和读/写磁头运动的时间。
因此,从数学上来讲,当需要传输 64 kb 以上的 sql 数据时,
尽可能地执行 64 kb 磁盘传输是有益的,即分成数个64k的 *** 作。
因为 64 kb 传输基本上与 8 kb 传输一样快,而每次传输的 sql server 数据是 8 kb 传输的 8 倍。
我们通过一个实例来看 有and *** 作符时候的最常见的一种情况。我们有下面一个表,
create table [dbo].[member]( [member_no] [dbo].[numeric_id] identity(1,1) not null, [lastname] [dbo].[shortstring] not null, [firstname] [dbo].[shortstring] not null, [middleinitial] [dbo].[letter] null, [street] [dbo].[shortstring] not null, [city] [dbo].[shortstring] not null, [state_prov] [dbo].[statecode] not null, [country] [dbo].[countrycode] not null, [mail_code] [dbo].[mailcode] not null, [phone_no] [dbo].[phonenumber] null, [photograph] [image] null, [issue_dt] [datetime] not null default (getdate()), [expr_dt] [datetime] not null default (dateadd(year,1,getdate())), [region_no] [dbo].[numeric_id] not null, [corp_no] [dbo].[numeric_id] null, [prev_balance] [money] null default (0), [curr_balance] [money] null default (0), [member_code] [dbo].[status_code] not null default (' '))
这个表具备下面的四个索引:
索引名 细节 索引的列
member_corporation_link nonclustered located on primary corp_no
member_ident clustered, unique, primary key located on primary member_no
member_region_link nonclustered located on primary region_no
memberfirstname nonclustered located on primary firstname
当我们执行下面的sql查询时候,
select m.member_no, m.firstname, m.region_nofrom dbo.member as mwhere m.firstname like 'k%' and m.region_no >6 and m.member_no <5000go
sql server 会根据索引方式,优化成下面方式来执行。
select a.member_no,a.firstname,b.region_nofrom(select m.member_no, m.firstname from dbo.member as m where m.firstname like 'k%' and m.member_no <5000) a , -- 这个查询可以直接使用 memberfirstname 非聚集索引,而且这个非聚集索引覆盖了所有查询列-- 实际执行时,只需要 逻辑读取 3 次
(select m.member_no, m.region_no from dbo.member as mwhere m.region_no >6) b
-- 这个查询可以直接使用 member_region_link 非聚集索引,而且这个非聚集索引覆盖了所有查询列-- 实际执行时,只需要 逻辑读取 10 次
where a.member_no = b.member_no
不信,你可以看这两个sql 的执行计划,以及逻辑读信息,都是一样的。
其实上面的sql,如果优化成下面的方式,实际的逻辑读消耗也是一样的。为何sql server 不会优化成下面的方式。是因为 and *** 作符优化的另外一个原则。
1/26 的数据和 1/6 的数据找交集的速度要比 1/52 的数据和 1/3 的数据找交集速度要慢。
select a.member_no,a.firstname,b.region_nofrom(select m.member_no, m.firstname from dbo.member as mwhere m.firstname like 'k%' -- 1/26 数据) a,
(select m.member_no, m.region_no from dbo.member as mwhere m.region_no >6 and m.member_no <5000-- 1/3 * 1/ 2 数据) bwhere a.member_no = b.member_no
当然,我们要学习sql 如何优化的话,就会用到查询语句中的一个功能,指定查询使用哪个索引来进行。
比如下面的查询语句
select m.member_no, m.firstname, m.region_nofrom dbo.member as m with (index (0))where m.firstname like 'k%' and m.region_no >6 and m.member_no <5000go
select m.member_no, m.firstname, m.region_nofrom dbo.member as m with (index (1))where m.firstname like 'k%' and m.region_no >6 and m.member_no <5000goselect m.member_no, m.firstname, m.region_nofrom dbo.member as m with (index (membercovering3))where m.firstname like 'k%' and m.region_no >6 and m.member_no <5000goselect m.member_no, m.firstname, m.region_nofrom dbo.member as m with (index (memberfirstname, member_region_link))where m.firstname like 'k%' and m.region_no >6 and m.member_no <5000go
这里 index 计算符可以是 0 ,1, 指定的一个或者多个索引名字。对于 0 ,1 的意义如下:
如果存在聚集索引,则 index(0) 强制执行聚集索引扫描,index(1) 强制执行聚集索引扫描或查找(使用性能最高的一种)。
如果不存在聚集索引,则 index(0) 强制执行表扫描,index(1) 被解释为错误。
总结知识点:
简单来说,我们可以这么理解:sql server 对于每一条查询语句。会根据实际索引情况(sysindexes 系统表中存储这些信息),分析每种组合可能的成本。然后选择它认为成本最小的一种。作为它实际执行的计划。
成本代价计算的一个主要组成部分是逻辑i/o的数量,特别是对于单表的查询。
and *** 作要满足所有条件,这样,经常会要求对几个数据集作交集。数据集越小,数据集的交集计算越节省成本。
的项目中,竟然出现了滥用聚集索引的问题。看来没有培训最最基础的索引的意义,代价,使用场景,是一个非常大的失误。这篇博客就是从这个角度来罗列索引的基础知识。
使用索引的意义
索引在数据库中的作用类似于目录在书籍中的作用,用来提高查找信息的速度。
使用索引查找数据,无需对整表进行扫描,可以快速找到所需数据。
使用索引的代价
索引需要占用数据表以外的物理存储空间。
创建索引和维护索引要花费一定的时间。
当对表进行更新 *** 作时,索引需要被重建,这样降低了数据的维护速度。
创建索引的列
主键
外键或在表联接 *** 作中经常用到的列
在经常查询的字段上最好建立索引
不创建索引的列
很少在查询中被引用
包含较少的惟一值
定义为 text、ntext 或者 image 数据类型的列
heaps是staging data的很好选择,当它没有任何index时
excellent for high performance data loading (parallel bulk load and parallel index creation after load)
excellent as a partition to a partitioned view or a partitioned table
聚集索引提高性能的方法,在前面几篇博客中分别提到过,下面只是一个简单的大纲,细节请参看前面几篇博客。
何时创建聚集索引?
clustered index会提高大多数table的性能,尤其是当它满足以下条件时:
独特, 狭窄, 静止: 最重要的条件
持续增长的,最好是只向上增加。例如:
identity
date, identity
guid (only when using newsequentialid() function)
聚集索引唯一性(独特型的问题)
由于聚集索引的b+树结构的叶子节点必须指向具体数据。如果你要建立聚集索引的列不唯一,并且你指定的创建的聚集索引是非唯一的聚集索引,则会有以下情况:
如果未使用 unique 属性创建聚集索引,数据库引擎 将向表自动添加一个四字节 uniqueifier 列。必要时,数据库引擎 将向行自动添加一个 uniqueifier 值,使每个键唯一。此列和列值供内部使用,用户不能查看或访问。
*****系实验(上机)报告课程名称 数据库系统基础
实验名称 数据查询与存储过程
学号 33
学生姓名 嘻习喜戏
成绩
年 月 日
序号 5 实验名称 SQL数据查询
实验目的:
熟练掌握SQL SELECT 语句,能够运用该语句完成各种查询。
实验内容:
用SQL SELECT 语句完成下列查询:
1. 查询客户表中的所有记录。
2. 从订购单表中查询客户号信息(哪些客户有订购单)。
4. 查询单价在20元以上(不含)的产品名称为牛奶的产品信息。
5. 查询单价在20元以上(不含)的产品名称为牛奶或德国奶酪的产品信息。
6. 查询有2003年7月订购单的客户名称、联系人、电话号码和订单号信息。
7. 查询有德国奶酪订货的客户的名称、联系人和电话号码信息。
8. 查询有德国奶酪订购需求的订单名细记录。
9. 查询所有订购数量(即订单名细中每个订购项目的数量)都在10个以上的订购单的信息。
10. 找出和德国奶酪同等价位的所有产品信息。
11. 查询单价范围在10元到30元范围内的产品信息(使用BETWEEN…AND)。
12. 从客户表中查询出客户名称中有“公司”二字的客户信息(使用LIKE运算符)。
13. 从客户表中查询出客户名称中没有“公司”二字的客户信息(使用NOT LIKE运算符)。
14. 按产品的单价升序列出全部产品信息。
15. 先按产品名称排序,再按单价排序列出全部产品信息。
16. 从产品表中查询共有几种产品。
17. 从订购名细表中查询德国奶酪的订购总数。
18. 计算德国奶酪所有订购的总金额。
19. 求所有订购单的平均金额,在查询结果中列出订购单的个数和平均金额。
20. 求每个订购单订购的项目数和总金额。
21. 求每个客户包含了德国奶酪订购的订单号及其最高金额和最低金额。
22. 求至少有两个订购项目的订购单的平均金额。
23. 找出尚未最后确定订购单(即订购日期为空值的记录)的有关客户信息(客户的名称、联系人和电话号码)和订单号。
24. 找出在2000年1月1日之后签订的订购单的客户信息(客户的名称、联系人和电话号码)、订单号和订购日期。
25. 列出每类产品(相同名称)具有最高单价的产品信息(产品号、名称、规格说明和单价,提示:使用内外层互相关嵌套查询)。
26. 确定哪些客户目前没有订购单(使用谓词NOT EXISTS)。
27. 查询目前有订购单的客户的信息(使用谓词EXISTS)。
28. 查询符合条件的产品信息,要求该产品的单价达到了任意一款产品名称为牛奶的单价的一半(使用ANY或SOME量词)。
29. 查询符合条件的产品信息,要求该产品的单价大于任何一款产品名称为牛奶的单价(使用ALL量词)。
30. 设计如下的连接 *** 作,并分析各自的特点:
•广义笛卡儿积
•内连接
•外连接
•左连接
•右连接
•全连接
掌握存储过程的创建命令,按照题目要求创建存储过程,理解存储过程的作用。
(1) 建立存储过程。查询单价范围在x元到y元范围内的产品信息。
(2) 建立存储过程。查询在某年某月某日之后签订的订购单的客户信息(客户的名称、联系人和电话号码)、订单号和订购日期。
(3) 建立存储过程。将某产品的订购日期统一修改为一个指定日期。
(4) 建立存储过程。删除没有签订单的客户信息。
实验要求:
用SELECT语句完成本次实验,并提交上机报告。
(1) 掌握存储过程的创建命令,按照实验内容的要求创建存储过程,理解存储过程的作用。
(2) 用CREATE PROCEDURE和EXECUTE 语句完成本次实验,并提交上机报告。
实验准备(本实验预备知识和为完成本实验所做的准备):
仔细阅读课本第五章关于SQL的数据查询功能的内容
实验过程(实验的 *** 作过程、遇到的问题及其解决办法或未能解决的问题):
用SQL SELECT 语句完成以上30题查询
实验总结(总结本次实验的收获、未解决的问题以及体会和建议等):
熟练掌握SQL SELECT 语句,能够运用该语句完成各种查询
附录(SQL语句):
--1. 查询客户表中的所有记录。
select * from 客户
--2. 从订购单表中查询客户号信息(哪些客户有订购单)
select 客户号from 订单where 订单号!=null
--3. 查询单价在元以上(含)的产品信息。
select *from 产品where 单价>20 or 单价=20
--4. 查询单价在元以上(不含)的产品名称为牛奶的产品信息。
select *from 产品where 单价>20 and 产品名称='牛奶'
--. 查询单价在元以上(不含)的产品名称为牛奶或德国奶酪的产品信息
select *from 产品where 单价>20 and (产品名称='牛奶'or 产品名称='德国奶酪')
--6. 查询有年月订购单的客户名称、联系人、电话号码和订单号信息
select 客户名称,联系人, 电话,订单号from 客户,订单where (year(订购日期)=2003 and month (订购日期)=7)and (订单.客户号=客户.客户号)
--7. 查询有德国奶酪订货的客户的名称、联系人和电话号码信息。
select 客户名称,联系人, 电话from 客户
where
(客户号= (select 客户号from 订单where(订单号 =(select 订单号from 订单明细
where 产品号= ( select 产品号from 产品where 产品名称= ' 德国奶酪' )))))
--8. 查询有德国奶酪订购需求的订单名细记录。
select * from 订单明细where (数量!=null and 产品号=(select 产品号from 产品where 产品名称= '德国奶酪'))
--9. 查询所有订购数量(即订单名细中每个订购项目的数量)都在个以上的订购单的信息。
select * from 订单where (订单号in (select 订单号from 订单明细where (数量>10)))
--10. 找出和德国奶酪同等价位的所有产品信息。
select * from 产品where (
--11. 查询单价范围在元到元范围内的产品信息(使用BETWEEN…AND)。
select * from 产品where (单价between 10 and 30)
--12. 从客户表中查询出客户名称中有“公司”二字的客户信息(使用LIKE运算符)
select * from 客户where 客户名称like '%公司%'
--13. 从客户表中查询出客户名称中没有“公司”二字的客户信息(使用NOT LIKE运算符)。
select * from 客户where 客户名称not like '%公司%'
--14. 按产品的单价升序列出全部产品信息。
select *from 产品order by 单价
--15. 先按产品名称排序,再按单价排序列出全部产品信息。
select * from 产品order by 产品名称,单价
--16. 从产品表中查询共有几种产品。
select count ( distinct 产品名称) as 产品总数from 产品
--17. 从订购名细表中查询德国奶酪的订购总数
select sum (数量) as '订购奶酪数量'
from 订单明细
where 产品号in(select 产品号from 产品where 产品名称='德国奶酪')
--18. 计算德国奶酪所有订购的总金额
declare @a money
select @a=(select 单价from 产品where 产品名称='德国奶酪')
declare @b int
select @b=(select sum (数量) as '订购奶酪数量'
from 订单明细
where 产品号in(select 产品号from 产品where 产品名称='德国奶酪'))
declare @c int
select @c=@a*@b
select @c as 总金额
--19. 求所有订购单的平均金额,在查询结果中列出订购单的个数和平均金额。
select 订单均值= avg(单价*数量) ,订单个数=count ( 订单号)
from 订单明细,产品
where 产品.产品号=订单明细.产品号
--20. 求每个订购单订购的项目数和总金额。
select 订单号, count (产品.产品号) as 项目数,sum(数量*单价) as 总金额
from 产品,订单明细
where (产品.产品号=订单明细.产品号)
group by 订单号
--21.求每个客户包含了德国奶酪订购的订单号及其最高金额和最低金额
select 客户.客户号,产品.产品号,数量*单价as 总金额
from 客户,订单,订单明细,产品
where 客户.客户号=订单.客户号and 订单.订单号=订单明细.订单号and 订单明细.产品号=产品.产品号and
产品名称='德国奶酪'
order by 客户号
compute max(数量*单价),min (数量*单价) by 客户号
--22.求至少有两个订购项目的订购单的平均金额
select 订单号,avg(数量*单价),count(产品.产品号)
from 订单明细,产品
where 订单明细.产品号=产品.产品号
group by 订单号
having count(产品.产品号)>=2
--23.找出尚未最后确定订购单(即订购日期为空值的记录)的有关客户信息
-- (客户的名称、联系人和电话号码)和订单号
select 客户名称,联系人,电话,订单明细.订单号
from 客户, 订单明细,订单
where(客户.客户号= 订单.客户号) and 订购日期=null
--24.找出在年月日之后签订的订购单的客户信息
--(客户的名称、联系人和电话号码)、订单号和订购日期
select 客户名称,联系人,电话,订单号,订购日期
from 客户,订单
where 客户.客户号=订单.客户号
and year(订购日期)>1996 and month(订购日期)>4 and day(订购日期)>2
--25.列出每类产品(相同名称)具有最高单价的产品信息
--(产品号、名称、规格说明和单价,提示:使用内外层互相关嵌套查询)
select A.产品号, A.产品名称, A.规格说明, A.单价
from 产品A
where 单价= (SELECT MAX(单价)
FROM 产品B
WHERE A.规格说明= B.规格说明)
--26.确定哪些客户目前没有订购单(使用谓词NOT EXISTS)
select *
from 客户
where not exists (select* from 订单where 客户号=订单.客户号)
--27.查询目前有订购单的客户的信息(使用谓词EXISTS)
select *
from 客户
where exists (select* from 订单where 客户号=订单.客户号)
--28.查询符合条件的产品信息,要求该产品的单价达到了任
--意一款产品名称为牛奶的单价的一半(使用ANY或SOME量词)
select *
from 产品a
where(单价>any(select 单价/2 from 产品b where b.产品名称='牛奶'))
--29.查询符合条件的产品信息,要求该产品的单价大于任何
--一款产品名称为牛奶的单价(使用ALL量词)
select *
from 产品a
where(单价>all(select 单价from 产品b where b.产品名称='牛奶'))
--30.设计如下的连接 *** 作,并分析各自的特点:
--•广义笛卡儿积
SELECT *
FROM 客户CROSS JOIN 订购单
WHERE 客户.客户号= 订购单.客户号
--•内连接
SELECT *
FROM 客户INNER JOIN 订购单
ON 客户.客户号= 订购单.客户号
--•外连接
--•左连接
SELECT *
FROM 客户LEFT JOIN 订购单
ON 客户.客户号= 订购单.客户号
--•右连接
SELECT *
FROM 客户RIGHT JOIN 订购单
ON 客户.客户号= 订购单.客户号
--•全连接
SELECT *
FROM 客户FULL JOIN 订购单
ON 客户.客户号= 订购单.客户号
说明:
1. 上机报告上传到211.68.36.251的数据库文件夹中的上传目录
2. 文件名的命名规则为:学号+姓名+实验+序号。如:9724101汪伟的第二次上机报告名为:9724101汪伟实验2
3. 封面由学生填写;
4. 正文的实验名称、实验目的、实验内容、实验要求已经由教师指定;
5. 实验准备由学生在实验或上机之前填写;
6. 实验过程由学生记录实验的过程,包括 *** 作过程、遇到哪些问题以及如何解决等;
7. 实验总结由学生在实验后填写,总结本次实验的收获、未解决的问题以及体会和建议等;
8. 将相关的语句粘贴到附录中。
你自己改改吧。想要word原版的话再说一声。
欢迎分享,转载请注明来源:内存溢出
评论列表(0条)