http://www.web008.net

【美高梅手机版】数据库基础编制程序,SQLserver高档编制程序

(1)对表结构的操作

1、在表中添加一列

语法:alter table 表名

   add  添加的列名  数据类型

例子:在Student表中添加列Hobbies,类型为varchar,宽度:20

            alter  table  Student  

         add  Hobbies  varchar(20)

2、删除表中的一列

语法:alter  table  表名

      drop  column  列名

例子:删除Student表中的Hobbies列

      alter  table  Student

      drop  column  Hobbies

3、修改表中列的数据类型

语法:alter table 表名

      alter  column 列名  修改后的数据类型  not null

例子:修改Student中的Sex列为char型,宽度为2

      alter  table  Student

      alter column Sex char(2) nou null

 

E-R图:

属性:定义实体的性质、实体的特征

实体:数据项(属性)的集合

关联:实体之间相互连接的方式

美高梅手机版 1

简单理解一下就可以了

美高梅手机版 2

 

数据库设计的步骤:

1、收集信息、标识对象、标识属性、标识关系(一对一、一对多、多对一、多对多)

--多条记录同时插入
insert into student
select 'jack', 23, 1, 5 union
select 'tom', 24, 0, 3 union
select 'wendy', 25, 1, 3 union
select 'tony', 26, 0, 5;

T-SQL添加约束的语法格式:

alter table 表名

add constraint 约束名 约束类型 具体的约束说明

 

(9)带输出参数的存储过程

--创建一个存储过程:添加一个学员成绩信息
if OBJECT_ID('InsertExam') is not null
drop proc InsertExam
go
create proc InsertExam
@ExamNo int,
@stuid varchar(50),
@writeExam decimal(10,2),
@ladExam decimal(10,2),
@n int output
as
insert Exam(ExamNo,StuId,WriteExam,LadExam)
values(@ExamNo,@stuid,@writeExam,@ladExam)
select @n=@@ROWCOUNT

go
select*from Exam
--调用添加存储过程
declare @n int
exec InsertExam '8','1006',70,80,@n output
if @n>0
print'添加成功'
else
print'添加失败'

use MySchool
select*from Teacher
--引用到C#案例MySchool_Proc
--添加教员
if OBJECT_ID('InsertTeacher') is not null
drop proc InsertTeacher
go
create proc InsertTeacher
@LoginId varchar(50),
@LoginPwd varchar(50),
@TeacherName varchar(50),
@Sex varchar(50),
@UserStateId int,
@Birthday dateTime,
@n int output
as
insert Teacher(LoginId,LoginPwd,TeacherName,Sex,UserStateId,Birthday)
values(@LoginId,@LoginPwd,@TeacherName,@Sex,@UserStateId,@Birthday)
select @n=@@ROWCOUNT
go

--修改教员
if OBJECT_ID('UpdateTeacher') is not null
drop proc UpdateTeacher
go
create proc UpdateTeacher
@LoginId varchar(50),
@LoginPwd varchar(50),
@TeacherName varchar(50),
@Sex varchar(50),
@UserStateId int,
@Birthday dateTime,
@n int output
as
update Teacher set LoginPwd=@LoginPwd, TeacherName=@TeacherName,
Sex=@Sex,UserStateId=@UserStateId,Birthday=@Birthday
where LoginId=@LoginId
select @n=@@ROWCOUNT
go
--删除教员
if OBJECT_ID('DeleteTeacher') is not null
drop proc DeleteTeacher
go
create proc DeleteTeacher
@LoginId varchar(50),
@n int output
as
delete from Teacher where LoginId=@LoginId
select @n=@@ROWCOUNT
--自我理解
--添加、修改、删除存储过程都是为了应用到C#程序中可以更快的执行,且占系统运行内存不多。
--C#中的调用其实就是一个三层架构

 

1、数据库设计

 

(8)存储过程

--执行dos命令的存储过程CMDShell
--在d盘根目录下创建一个文件夹
execute xp_cmdshell 'md D:DB'
--查看D盘下的所有信息
exec xp_cmdshell 'dir d:'
--查看视图VW_Stu的源代码
exec sp_helptext 'VW_Stu'
--查看一个表的索引
exec sp_helpindex 'stuInfo'
--查看一个数据库中的存储过程
exec sp_stored_procedures
--进行数据库的逻辑名称改名(显示名称)
exec sp_renamedb BankDBs,BankDB--主文件名是不会变的
--查看当前数据库中的表和视图情况
exec sp_tables

--将网格显示改为文本格式显示

use StuDB
--创建一个存储过程
--显示机试和笔试的平均成绩,并且显示本次考试的成绩情况
--还要显示未通过的学员信息
if exists(select*from sys.procedures
where name='ScoreCountl')
drop procedure ScoreCountl
go
create procedure ScoreCountl
as
declare @write decimal,@lab decimal
select @write=AVG(writeExam),@lab=AVG(LadExam)
from Exam
print '笔试成绩:'+convert(varchar(20),@write)
print '机试成绩:'+convert(varchar(20),@lab)
if @write>=70 and @lab>=70
print'本班考试成绩优秀!'
else
print'本班考试成绩一般!'

print'-------------------------------------------------'
print'----------参加本次考试没有通过的学员名单-----------'
select stuname 姓名,si.StuId 学号,WriteExam 笔试成绩,LadExam 机试成绩
from StuInfo si,Exam e
where si.StuId=e.StuId and ( WriteExam<60 or LadExam<60)

go
exec ScoreCountl

--自由调控及格线
if exists(select*from sys.procedures
where name='ScoreCount2')
drop procedure ScoreCount2
go
create procedure ScoreCount2
@w decimal=60,--笔试及格线
@l decimal=60 --机试及格线,加了默认值60
as
declare @write decimal,@lab decimal
select @write=AVG(writeExam),@lab=AVG(LadExam)
from Exam
print '笔试成绩:'+convert(varchar(20),@write)
print '机试成绩:'+convert(varchar(20),@lab)
if @write>=70 and @lab>=70
print'本班考试成绩优秀!'
else
print'本班考试成绩一般!'

print'-------------------------------------------------'
print'----------本次笔试及格线:'+convert(varchar(20),@w)
+'----------本次机试及格线:'+convert(varchar(20),@l)
print'----------参加本次考试没有通过的学员名单-----------'
select stuname 姓名,si.StuId 学号,WriteExam 笔试成绩,LadExam 机试成绩
from StuInfo si,Exam e
where si.StuId=e.StuId and ( WriteExam<@w or LadExam<@l)
go
exec ScoreCount2 @l=40,@w=60 --分别为机试和笔试的及格线
exec ScoreCount2 --有默认值的情况下

use GoodSystem
--创建一个存储过程:查询生产日期为某年的
--商品名称,类型,生产日期,库存
if exists(select*from sys.procedures
where name='ScoreCount3')
drop procedure ScoreCount3
go
create procedure ScoreCount3
@d decimal
as
select Name 商品名称,gt.Type 类型,ProductionDate 生产日期,Amount 库存
from GoodS gs,GoodType gt
where gs.Tid=gt.Tid and year(ProductionDate)=@d
--查询2017年的商品信息
go
exec ScoreCount3 2017

--创建一个存储过程:查询类型为‘食品’的商品信息
if exists(select*from sys.procedures
where name='ScoreCount4')
drop procedure ScoreCount4
go
create procedure ScoreCount4
@s varchar(20)
as
select Name 商品名称,gt.Type 类型,ProductionDate 生产日期,Amount 库存
from GoodS gs,GoodType gt
where gs.Tid=gt.Tid and gt.Type=@s

go
exec ScoreCount4 '食品'

方法2(设置文件大小)、

T-SQL创建数据库:

if DB_ID('数据库名') is not null
drop database 数据库名
go
create database 数据库名
on
(
        name='数据库名',
        filename='物理数据库储存路径数据库文件'
)

Ø 利用存储过程查询表信息

(2)添加约束

 1、添加主键约束
语法:alter  table 表名
              add  constraint   约束名    primary key(要设置主键的列名)
例子:
给Class表添加主键约束
if OBJECT_ID('PK_ClassId') is not null
alter  table  Class
    drop  constraint  PK_ClassId
go
alter  table  Class
add  constraint  PK_ClassId  primary key(ClassId)

2、添加唯一约束
语法:alter table 表名
             add constraint 约束名 unique(要添加唯一约束的列名)
例子:
给信息表stuInfo中的姓名添加唯一约束
if OBJECT_ID('UQ_StuName') is not null
alter  table  StuInfo
 drop  constraint  UQ_StuName
 go
 alter  table  StuInfo
 add  constraint  UQ_StuName
 unique(StuName)

3、添加默认约束
语法:alter table 表名
             add constraint  约束名  Default(默认值)  for 要添加默认值的列名
例子:
给stuInfo表中的Age列添加默认值为18
 if OBJECT_ID('DF_Age') is not null
    alter  table  StuInfo
    drop  constraint  DF_Age
 go
 alter  table  stuInfo 
 add  constraint  DF_Age  Default(18) for Age

4、添加检查约束
语法:alter table 表名
           drop constraint 约束名
          check(列名>=0)
例子:
给笔试成绩添加一个约束,要求成绩必须在0-100之间
if OBJECT_ID('CK_WriteExam') is not null
alter table Exam
drop constraint CK_WriteExam
go
alter table Exam
 add constraint CK_WriteExam
 check(WriteExam>=0 and WriteExam<=100)

5、外键约束
语法:alter table 表名1
       add constraint 约束名
       foreign key(外键约束名)
        references 表名2(外键约束名)
例子:
给班级表与学员信息表创建关系(外键约束)
if OBJECT_ID('FK_Class_StuInfo') is not null
alter table  stuInfo
drop constraint Fk_Class_StuInfo
go
alter table stuInfo
add constraint Fk_Class_StuInfo
foreign key(ClassId)
references Class(ClassId)

--删除约束
Alter table 表名
Drop ConStraint 约束名

--删除表
Drop table 表名

 

方法3(设置次数据文件)

(6)数据库变量格式

use StuDB
select*from StuInfo
select*from Exam
--总学生人数 参考人数 及格人数 未及格人数 及格率
declare @total int,@sum int,@pass int
select @total=COUNT(*) from StuInfo --统计总人数
select @sum=COUNT(*) from Exam --统计参考人数
select @pass=COUNT(*) from Exam
where WriteExam>=60 and LadExam>=60 --统计及格人数

select @total 总人数,@sum 参考人数,@pass 及格人数 ,
@total-@pass 未及格人数,CONVERT(varchar(20),
ceiling( @pass*1.0/@total*10000)/100)+'%' 及格率

select*from StuInfo
--查看上一个错误的编号
select @@ERROR

select*from StuInfo
select*from Class
select @@IDENTITY
insert Class values('s149')

--获取上一次SQL指令影响的命令行数
select @@ROWCOUNT

--判断删除是否成功
--1、直接删除
--2、先查询,然后再删除,再查询

--当前SQL服务器名称,当前服务名称
select @@SERVERNAME ,@@SERVICENAME

--显示当前打开的事务数
select @@TRANCOUNT

--显示当前服务器允许的最大连接数
select @@MAX_CONNECTIONS

--显示当前使用的语言
select @@LANGUAGE

print '当前服务器名称:' +@@servername
print '当前服务名称:' +@@servicename
print '错误编号:' +convert(varchar(6), @@error)

--显示笔试平均成绩,再根据平均成绩显示相应信息
declare @avg float
select @avg=AVG(writeExam) from Exam
print ' ------成绩信息如下:--------'
print'全校平均成绩:'+convert(varchar(20),@avg)
if @avg>=70
begin
print'成绩优秀!'
--显示前三名的学员信息
select top 3 StuName,si.StuId,WriteExam,LadExam
from StuInfo si,Exam e
where si.StuId=e.StuId
order by WriteExam desc --笔试降序
end
else
begin
print'成绩比较差!'
--显示后三名的学员信息
select top 3 StuName,si.StuId,WriteExam,LadExam
from StuInfo si,Exam e
where si.StuId=e.StuId
order by WriteExam asc --笔试降序
end

--对全班学员进行提分,保证每位同学的笔试成绩全部通过
while(1=1) --永真循环
begin
declare @count int --保存未通过的人数
--统计为通过的人数
select @count=Count(*) from Exam where writeExam<60
if (@count=0)
begin
break --终止循环
end
--进行加分
update Exam set WriteExam=100 where writeExam>=98
update Exam set WriteExam=WriteExam+2 where WriteExam<98

end
print'----------加分后的学员成绩如下:--------------'
select*from Exam

--显示学员笔试成绩的等级制
--90以上:优秀,80-90:良好,70-80:中等,60-69 :一般
--60以下:不及格
select ExamNo 考号,StuId 学号,WriteExam 笔试成绩 ,LadExam 机试成绩,
等级=
case
when writeExam>=90 then '优秀'
when writeExam>=80 then '良好'
when writeExam>=70 then '中等'
when writeExam>=60 then '一般'
else '不及格'
end
from Exam

--显示所有学员打的姓名,性别,年龄,笔试成绩,机试成绩
--没有成绩的学员显示缺考
select stuName,sex,Age,WriteExam=
case
when WriteExam IS null then '缺考'
else CONVERT(varchar(20),writeExam)
end,
LadExam=
case
when LadExam IS null then '缺考'
else CONVERT(varchar(20),ladexam)
end
from StuInfo si left join Exam e
on si.StuId=e.StuId

--未参加考试的学员成绩为0
select stuName ,sex ,ISNULL(age,18),
ISNULL(WriteExam,0),
ladexam=
case
when ladexam IS null then 0
else ladexam
end
from StuInfo si left join Exam e
on si.StuId=e.StuId

(7)索引、视图、事务

--创建索引
语法:
if exists(select*from sys.indexes
where name='IX_stuinfo_AgeName')
drop index stuinfo.IX_stuinfo_AgeName
go
create nonclustered index 索引名
on 表名(按某列升序或降序)
例子:
create nonclustered index IX_stuinfo_AgeName
on stuinfo(age,stuname desc)
备注:列名后加 desc 是降序的意思,不加默认升序
备注2:nonclustered表示创建非聚集索引 还有如:unique表示创建唯一性索引,clustered 表示创建聚集索引

--使用索引
语法:
select*from 有该索引的表名
with (index=索引名)
例子:
使用索引IX_stuinfo_AgeName 查询学员信息
select*From StuInfo
with(index=IX_Stuinfo_AgeName)

--索引的优点和缺点
优点:
1、加快访问速度
2、加强行的唯一性
缺点:
1、带索引的表在数据库中需要更多的存储空间
2、更新数据的命令需要更长的处理时间,因为它们需要对索引进行更新

--创建视图
语法:
create view 视图名
as
select 列名 from 表1,表2
where 表1.id =表2.id order by 条件
例子:
创建一个视图:获取学员的姓名、性别、年龄、笔试成绩、机试成绩、并且按笔试成绩降序排序
if exists(select*from sys.views
where name='VW_Stu')
drop view VW_Stu --有相同视图则删除原视图
go
create view VW_Stu
as
select top 100 stuName,Sex,Age,WriteExam,LadExam
from StuInfo si,Exam e
where si.StuId=e.StuId
order by WriteExam Desc
go
--视图的使用
select *from VW_Stu

--事务
事务的ACID属性
1、原子性
一个事务对数据库的所有操作,是一个不可分割的工作单元。这些操作要么全部执行,要么什么也不做。保证原子性是数据库系统本身的职责,由DBMS的事务管理子系统来实现。

2、一致性
一个事务独立执行的结果应保持数据库的一致性,即数据不会因为事务的执行而遭受破坏。确保单个事务的一致性是编写事务的应用程序员的职责。在系统运行时,由DBMS的完整性子系统执行任务。

3、隔离性
在多个事务并发执行时,系统应保证这些事务先后单独执行时的结果一样,此时称事务达到了隔离性的要求,也就是在多个并发事务执行时,保证执行结果是正确的,如同单用户环境一样。隔离性是由DBMS的并发控制子系统实现的。
4、持久性

语法:
--开启事务
begin transaction
declare @error int --定义变量,记录错误
set @error=0 --默认无错
Update bank set Blance=Blance+5000 where Bname='join'
set @error=@errror+@@ERROR
Update bank set Blance=Blance-5000 where Bname='jack'
set @error=@errror+@@ERROR
if(@error<>0) --如果错误号不为零,说明有操作出错
begin
raiserror('转账过程出错',10,1)
rollback --回滚全部操作
end

else
begin
print '恭喜你,转账成功!'
commit --提交所有操作
end

实例:
--转账事务,转账900
begin tran
declare @err int=0 --声明一个变量,初值为0
update Bank set Cmoney=Cmoney-900 where Cname='张三'
set @err=@err+@@ERROR
update Bank set Cmoney=Cmoney+900 where Cname='李四'
set @err=@err+@@ERROR
if @err>0 --条件
begin
print'交易失败,事务回滚!'
rollback
end
else
begin
print'交易成功,事务提交'
commit tran
end

 

数据库设计的重要性:

减少冗余,提高性能、易维护

 

第一范式(1NF):

每列都应该是原子性的,五重复的域

Ø 插入数据

第三范式(3NF):

第三范式要求各列与主键列直接相关

 

案例:

if DB_ID('Student')is not null
drop databese Student
go
create databese Student
on
(
    name='Student',
    finema='E:第二学期SQLstuDBStudent.mdf'
)

 

数据文件参数 描述
name 数据库逻辑名称
filename 数据库物理文件名
size 数据文件初始化大小,单位默认为M
maxsize 数据文件可增长到最大值,单位默认阿M,不指定即无限大
filegrowth 数据库每次增长率,可以是百分比,默认单位M,0不增长

insert into classes(name) values('1班');
insert into classes values('2班', '2011-06-15');

案例:

if object_ID('StuInfo')is not null
drop table StuInfo
go
create table StuInfo
(
      StuId int identity(1,1) primary key,
      StuName varchar(10) not null,
      StuSex varchar(2) not null,
      StuAge varchar(3) not null
)

--添加字段
alter table student add address varchar(50) not null;
--修改字段
alter table student alter column address varchar(20);
--删除字段
alter table student drop column number;

T-SQL删除约束:

alter table 表名

drop constraint 约束名

--查询student相关信息
exec sp_help student;
exec sp_help classes;

(3)高级查询语法格式

--内连接
语法:
select 要查询的属性
from 表1 inner join 表2
on 表1.Id=表2.Id
where 要限制的条件(可以不要)

--左外连接
语法:
select 要查询的属性
from 表1 left outer join 表2
on 表1.id=表2.id

--右外连接
语法:
select 要查询的属性
from 表1 right outer join 表2
on 表1.id=表2.id

--全外连接
语法:
select 要查询的属性
from 表1 full outer join 表2
on 表1.id=表2.id

--交叉连接
语法:
select 要查询的属性
from 表1 Cross join 表2
where 条件

--自连接
select 要查询的属性
from 表1 , 表2
where 表1.id=表2.id

Ø 给表添加字段、修改字段、删除字段

第二范式(2NF):

在第一范式的基础上属性完全依赖于主键

Ø 添加、删除约束

(5)变量函数

--常用的数学函数
--1、ABS 求绝对值 交易之前-交易之后
--2、POWER 求次方
select POWER(2,10),POWER(2.0000,0.5) --1kb 1024
select POWER(2.0000000,1.000000/3)
--3、求圆周率
select PI()
--4、Rount() --四舍五入函数
select ROUND(315.4567,2),Round(315.4567,0),Round(345.4567,-2)
--5、ceiling --取比原数大的整数
--6、floor --取比原数小的整数
select CEILING(3.00000000001),FLOOR(3.99999999)
--7、ASCII 返回一个字符的ASCII码值
select ASCII('A')
--8、Rand() 返回一个0-1之间的随机数
--select nCHAR(214)+nCHAR(208)
select RAND()
select RAND(DATEPART(ss,GetDate())*2)--这样打变化频率小
go

--产生一个银行卡号,前8位为'6225 3800' 后8位为随机数字,
--请用T-SQL编码完成
--select RAND()
declare @rand numeric(20,8)
select @rand=RAND()
print @rand
declare @s varchar(16)
set @s=SUBSTRING(STR(@rand,10,8),3,8)
print @s
set @s='62253800'+@s
print '你的新银行卡号为:'+@s

--日期函数
--1、getdate():返回服务器上的当前时间
select GETDATE()
--2、datepart:返回一个日期的一部分值(整形)
--3、datename:返回一个日期的一部分值(字符串)
--返回一周的第几天(星期天是第一天)
select DATEPART(DW,GETDATE()),DATENAME(DW,GETDATE())
--返回一年的第几周
select DATEPART(WEEK,GETDATE()),DATENAME(WEEK,GETDATE())
--4、datediff 日期比较函数
--返回每个交易已经发生了多少天
select DATEDIFF(DD,transdate,GETDATE()) from TransInfo

--字符串函数
--1、LEN:返回一个字符串的字符数
select LEN('中国'),LEN('abc123!')
select LEN('abc '),LEN(' abc')--数据类型为varchar类型,会自动消除没字符连接的空格

--2、dataLength:返回一个字符串的字节数
select dataLength('中国'),dataLength('abc123!')
select RIGHT('abcdef',3),LEFT('abcdef',3)
--4、substring:字符串截取函数
select SUBSTRING('abcdef',2,3)--和C#不一样,下标从一开始
--5、charIndex:字符串查找函数
select charIndex('a','bcad',1)
--6、upper:字母大写转换函数
--7、lower:字母小写转换函数
select UPPER('abc123'),LOWER('abCCC123中!')
--8、space:产生空格函数
select len('abc'+SPACE(10) +'123') ,'abc'+SPACE(10) +'123' --len是测定总长度
--9、replicate:字符串重复函数
select REPLICATE('abc',3)
--10、replace:字符替换函数
select REPLACE('11111111','1','o')--将1替换为o
select REPLACE('o0o0o0o0000oo','0','o'),'00000000'--将0替换为o
select REPLACE( REPLACE('0o0oil0oillil10ol1','l','1'),'0','o')
--11、stuff:字符替换函数
select STUFF('湖南武汉',2,1,'北')
select STUFF('中国长沙',3,0,'湖南')--输出中国湖南长沙
--12、ltrim和rtrim:去掉字符串左边或右边的全部空格
select len(ltrim(' a bc'))
--13、str:将数值转换为字符串函数
select STR(12345.65,8,2)
--14、char:将一个ASCII码值转换为一个字符
select CHAR(97),ASCII('a')

 

数据库规范化:

Ø 创建、删除表

(4)高级查询实例

if DB_ID('GoodSystem') is not null
drop database GoodSystem
go
create database GoodSystem on --创建一个商品数据库
(
name='GoodSystem',
filename='E:SQL第二章上机任务GoodSystem.mdf'
)
--打开数据库
use GoodSystem
--创建商品类型表GoodsType
if OBJECT_ID('GoodType') is not null
drop table GoodType
go
create table GoodType
(
Tid int primary key,
Type varchar(20)
)
--创建商品信息表Goods
if OBJECT_ID('Goods') is not null
drop table Goods
go
create table Goods
(
id int primary key,
Tid int ,
Name varchar(50),
Price money,
ProductionDate datetime,
Amount int
)
--给商品类型表GoodsType添加测试数据
insert GoodType select '1','家电' union
select '2','电子' union
select '3','食品' union
select '4','生活用品'

--给商品信息表Goods添加测试数据
insert Goods select '1','1','冰箱','3344','2017-6-3','100' union
select '2','1','电视','1777','2016-10-4','100' union
select '3','1','微波炉','333','2017-2-26','100' union
select '4','2','手机','4500','2017-5-7','100' union
select '5','2','显示器','1777','2016-12-4','100' union
select '6','2','主机','1500','2017-3-9','100' union
select '7','3','老干妈','9','2017-7-6','100' union
select '8','3','爽口榨菜','3.6','2017-6-8','100'

--查询商信息表中的商品名称,价钱,生产日期。
select Name 商品名称,Price 价钱,ProductionDate 生产日期
from Goods
--查询商品类型、商品名称、价钱、生产日期
select gt.Type 商品类型,gs.Name 商品名称 , gs.Price 商品价钱,gs.ProductionDate 生产日期
from GoodType gt inner join Goods gs
on gt.Tid=gs.Tid
--查询生产日期为2017的商品类型、商品名称、价钱、生产日期
select gt.Type 商品类型,gs.Name 商品名称 , gs.Price 价钱,gs.ProductionDate 生产日期
from GoodType gt inner join Goods gs
on gt.Tid=gs.Tid and year(gs.ProductionDate) like '2017%'
--查询商品类型为“电子”的商品类型、商品名称、价钱、生产日期,数据按价钱降序排列
select gt.Type 商品类型,gs.Name 商品名称,gs.Price 价钱 ,gs.ProductionDate 生产日期
from GoodType gt inner join GoodS gs
on gt.Tid=gs.Tid and gt.Type='电子'
order by Price desc

--统计每种商品类型的商品数量
select gt.Type 商品类型, COUNT(gs.Amount) 商品数量
from GoodType gt inner join Goods gs
on gt.Tid=gs.Tid
group by gt.Type

--查询所有商品类型对应的所有商品信息
select gt.Type 商品类型,gs.Name 商品名称 , gs.Price 商品价钱,gs.ProductionDate 生产日期
from GoodType gt left join Goods gs
on gt.Tid=gs.Tid

--查询价钱是333、1500、4500的商品信息
select Type 商品类型,Name 商品名称,Price 商品价钱,ProductionDate 生产日期
from GoodType gt ,Goods gs
where Price in(333,1500,4500)
and (gs.Tid=gt.Tid)

--查询没有对应商品信息的商品类型信息(类型编号,类型名称)
select Tid 编号,Type 类型 from GoodType
where Tid not in
(select Tid from Goods)

--查询所有商品的平均价钱
select AVG(Price) 平均价钱 from Goods

--查询价钱大于平均价钱的商品信息
select Type 商品类型,Name 商品名称,Price 商品价钱,ProductionDate 生产日期
from GoodType gt , Goods gs
where gs.Tid=gt.Tid
and Price>
(select AVG(Price) 平均价钱 from Goods)

--查询每种商品类型平均价钱超出总平均价钱的类型名称、平均价钱
select gt.Type 类型名称,AVG(Price) 价钱
from Goods gs,GoodType gt
where gs.Tid=gt.Tid
and
gs.Tid in
(select gs.Tid
from Goods
group by Tid
having AVG(Price)>
(select AVG(Price) 平均价钱 from Goods))
group by gt.Type
--查询所有商品总金额超20万的商品类型、名称、总金额
select Type 商品类型 ,Name 名称 ,Price*Amount 总金额
from GoodS gs , GoodType gt
where gs.Tid=gt.Tid
and Price*Amount>200000
--库存报警(低10)

--延迟到14:03:00 执行查询
waitfor time '14:03:00'
select*from Goods
--延迟两秒执行操作
waitfor delay '00:00:02'
select *from Goods

 

T-SQL创建约束:

主键约束:(primary key constraint):主键列数据唯一,并不为空,简称:PK

唯一约束:(unique constraint):保证该列不允许除夕重复值,简称:UQ

检查约束:(check constraint):限制列中允许的取值以及多个列直接的关系,简称:CK

默认约束:(default constraint):设置某列的默认值,简称:DF

外键约束:(foreign key constraint):用于在两个表之间建立关系,需要指定主从表,简称:FK

--判断该列名是否存在,如果存在就删除
if (exists (select * from sys.columns where object_id = object_id('student') and name = 'idCard'))
alter table student drop column idCard
go
if (exists (select * from information_schema.columns where table_name = 'student' and column_name = 'tel'))
alter table student drop column tel
go

T-SQL语句创建表:

if object_ID('表名')is not null
drop table 表名
go
create table 表名
(
      字段1 数据类型 列的特性,
      字段2 数据类型 列的特性
)

--判断是否存在该数据库,存在就删除
if (exists (select * from sys.databases where name = 'database_name'))
drop database database_name
go
create database database_name
--默认就属于primary主文件组,可省略
on primary (
--数据文件的具体描述
name = 'database_name_data',        --主数据文件的逻辑名
fileName = 'c:database_name_data.mdf',     --主数据文件的物理名
size = 3MB,                  --主数据文件的初始大小
maxSize = 50MB,               --主数据文件增长的最大值
fileGrowth = 10%                --主数据文件的增长率
)
--日志文件的具体描述,各参数含义同上
log on (
name = 'database_name_log',
fileName = 'c:database_name_log.ldf',
size = 1MB,
fileGrowth = 1MB
)
go

T-SQL语句创建和管理数据库和表:

--判断是否存在当前table
if (exists (select * from sys.objects where name = 'classes'))
drop table classes
go
create table classes(
id int primary key identity(1, 2),
name varchar(22) not null,
createDate datetime default getDate()
)
go
if (exists (select * from sys.objects where object_id = object_id('student')))
drop table student
go

案例:

--添加主键约束(将StuNo设为主键)
alter table StuInfo add constraint PK_StuNO primary key (StuNo)
go
--添加默认约束(性别默认为男)
alter table StuInfo Add constraint DF_StuSex DEFAULT ('男')for StuSex 
go
--添加检查约束(年龄必须为40之间)
alter table StuInfo Add constraint CK_StuAge check(StuAge>=18 and StuAge<=40)
go
--添加外键约束
alter table Exam Add constraint FK_StuNo FORELGN KEY (StuNo)references StuInfo(StuNo)
go

--添加新列、约束
alter table student
add number varchar(20) null constraint no_uk unique;
--增加主键
alter table student
add constraint pk_id primary key(id);
--添加外键约束
alter table student
add constraint fk_cid foreign key (cid) references classes(id)
go
--添加唯一约束
alter table student
add constraint name_uk unique(name);
--添加check约束
alter table student with nocheck
add constraint check_age check (age > 1);
alter table student
add constraint ck_age check (age >= 15 and age <= 50)
--添加默认约束
alter table student
add constraint sex_def default 1 for sex;
--添加一个包含默认值可以为空的列
alter table student
add createDate smalldatetime null
constraint createDate_def default getDate() with values;

--判断某个表或对象是否存在
if (exists (select * from sys.objects where name = 'classes'))
print 'Exist';
go
if (exists (select * from sys.objects where object_id = object_id('student')))
print 'Exist';
go
if (object_id('student', 'U') is not null)
print 'Exist';
go

Ø 使用、切换数据库

--创建table
create table student(
id int identity(1, 1) not null,
name varchar(20),
age int,
sex bit,
cid int
)
go

Ø 备份数据、表

--删除数据(from可省略)
delete from student where id = 1;

郑重声明:本文版权归美高梅163888所有,转载文章仅为传播更多信息之目的,如作者信息标记有误,请第一时间联系我们修改或删除,多谢。