澳门新萄京官方网站-www.8455.com-澳门新萄京赌场网址

澳门新萄京官方网站:SQLserver高档编制程序,s

2019-09-16 作者:数据库网络   |   浏览(129)

1、数据库设计

if exists(select name from sysobjects where name = 'stuInfo')
drop table stuInfo
go
create table stuInfo
(
stuName varchar(20) not null, --学员名称
stuNo char(6) not null, --学号
stuSex char(2) not null, --性别
stuAge int not null, --年龄
stuID numeric(18,0) not null, --身份证号,numeric(18,0)表示18位数字,小数位数为0
stuSeat smallint identity(1,1), --座位号:自动编号(标识列),从1开始自增
stuAddr text --住址
)
go

 

第16课-数据库开发及ado.net

 

数据库设计的重要性:

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

alter table stuInfo add constraint PK_stuID primary key(stuID);
alter table stuInfo add constraint UQ_stuSeat unique(stuSeat);
alter table stuInfo add constraint CK_stuSex check(stuSex

数据库SQl,创建数据库和表,增删改语句,约束,top和Distinct,聚合函数介绍

用户管理

数据库设计的步骤:

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

in('男','女'));

if exists(select name from sysobjects where name='stuMarks')
drop table stuMarks
go
create table stuMarks
(
examNo char(7) not null, --考号
stuNo char(6) not null, --学号
writtenExam int not null, --笔试成绩
labExam int not null --机试成绩
)
go

alter table stuMarks add constraint PK_examNo primary key(examNo);

 

SQL语句入门(脚本、命令)

为scott用户解锁

E-R图:

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

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

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

澳门新萄京官方网站 1

简单理解一下就可以了

澳门新萄京官方网站 2

alter table stuMarks add constraint UQ_stuNo unique(stuNo);

select * from stuInfo;
insert into stuInfo values('张秋丽','s25301','女',18,412724198603151234,'北京海淀');
insert into stuInfo values('李斯文','s25303','男',22,412724198603161234,'河南洛阳');
insert into stuInfo values('李文才','s25302','男',31,412724198603171234,'陕西西安');
insert into stuInfo values('欧阳俊','s25304','男',28,412724198603181234,'新疆喀什');
insert into stuInfo values('欧阳丹','s25305','女',23,412724198603191234,'新疆库车');

select * from stuMarks;
insert into stuMarks values('s27811','s25303',80,58);
insert into stuMarks values('s27813','s25302',50,90);
insert into stuMarks values('s27815','s25301',65,0);
insert into stuMarks values('s27816','s25304',77,82);
insert into stuMarks values('s27817','s25305',70,92);


--局部变量声明/赋值/查询
declare @stuName varchar(20) --声明
set @stuName = '李文才' --赋值@stuName
select * from stuInfo where stuName=@stuName; --查询
declare @stuSeat int
select @stuSeat = stuSeat from stuInfo where stuName=@stuName --赋值@stuSeat
select * from stuInfo where (stuSeat = @stuSeat 1) or (stuSeat = @stuSeat - 1) --查询

Ø Go批处理语句

SQL全名是结构化查询语言(Structured Query Language)

1:解锁
  alter user scott account unlock;

数据库规范化:

go

--IF···ELSE···
--案例1:如果笔试成绩平均分大于70优秀,否则较差
declare @avgWrt float
select @avgWrt=AVG(writtenExam) from stuMarks
if(@avgWrt > 70)
begin
print '成绩优秀,平均成绩:' convert(varchar, @avgWrt)
select top 3 * from stuMarks order by writtenExam desc
end
else
begin
print '成绩较差,平均成绩:' convert(varchar, @avgWrt)
select top 3 * from stuMarks order by writtenExam asc

用于同时执行多个语句

SOL语句是和DBMS“交谈”专用的语言,不同的DBMS都认SQL语法。

2: 更改密码
   alter user scott identified by tiger;

第一范式(1NF):

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

end

--案例2:如果机试成绩平均分大于40优秀,否则较差
declare @avgLab float
select @avgLab=AVG(labExam) from stuMarks
if(@avgLab > 40)
begin
print '成绩优秀,平均成绩:' convert(varchar, @avgLab)
select max(labExam) from stuMarks
end
else
begin
print '成绩较差,平均成绩:' convert(varchar, @avgLab)
select min(labExam) from stuMarks
end

 

Sql中字符串使用单引号:通过写俩个单引号来转义一个单引号。

3: 跳转
   connect sqlplus scott/tiger

第二范式(2NF):

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

go

--while循环语句,可以使用 break 退出所在层的循环,continue 退出当次循环
select * from stuMarks where labExam<60
--案例1:labExam低于60分的,循环加到60分,每次加2分
declare @n int
while(1 = 1)
begin
select @n=count(*) from stuMarks where labExam < 60
if(@n > 0)
begin
update stuMarks set labExam = labExam 2 where labExam < 60
end
else
begin
break
end
end
select * from stuMarks

Ø 使用、切换数据库

Sql中的注释“——” 单行注释比较好

4  查询测试OK
    select * from emp;

第三范式(3NF):

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

--update stuMarks set labExam=50 where labExam=60

select * from stuMarks where writtenExam<60
--案例2:writtenExam低于60分的,循环加到60分,每次加2分
declare @m int
while(1=1)
begin
select @m=count(*) from stuMarks where writtenExam < 60
if(@m > 0)
begin
update stuMarks set writtenExam = writtenExam 2 where writtenExam < 60
end
else
break;
end
select * from stuMarks


--case多分支语句
--select col1,col2 =
-- case
-- when···col2 = condition1···then···
-- when···col2 = condition2···then···
-- else···
-- end
--from tabName
select stuNo,writtenExam =
case
when writtenExam >= 90 then 'A'
when writtenExam between 80 and 89 then 'B'
when writtenExam between 70 and 79 then 'C'
when writtenExam between 60 and 69 then 'D'
else 'E'
end
from stuMarks

use master
go

判断俩个数据是否相等使用=(单等号)

创建用户
create user "username" identified by "userpassword";

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

select * from stuMarks

select stuName,stuAge =
case
when stuAge >= 30 then 'old'
when stuAge between 20 and 29 then 'strong'
when stuAge between 10 and 19 then 'young'
else 'baby'
end
from stuInfo
select * from stuInfo


if exists(select name from sysobjects where name = 'bankCounter')
drop table bankCounter
go
create table bankCounter
(
cstName varchar(32) not null, --顾客姓名
countNo numeric(21,0) not null, --账户
curMoney money, --账户现金余额
time_stamp datetime --时间戳
)
go
alter table bankCounter add constraint PK_countNo primary key(countNo);
alter table bankCounter add constraint CK_curMoney check(curMoney > 0) ;
go
insert into bankCounter values('张三',400006666668888889999,1000,'2012-08-09');
insert into bankCounter values('李四',400006666668888889998,1001,'2013-08-06');
go

 

在sql语句中sql代码不区分大小写

删除用户
drop user "username" cascade;

T-SQL创建数据库:

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

select * from bankCounter

Ø 创建、删除数据库

SQL主要分为:

授权
grant connect,resource,dba to "username";

案例:

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不增长

--事务处理:一种机制,一个操作序列,它将一组数据库操作指令作为一个整体执行,并一起向系统提交或者撤销,即

这组指令要不全部执行,要不全部不执行,有任何一个错误就要立刻回滚/撤销所有操作。
--begin transaction --开始事务
--commit transaction --提交事务
--rollback transaction --回滚(撤销)事务

--案例:在此例中,张三转账1000给李四,但因check约束curMoney必须大于零,报错,所以update(张三)的现金值没变,
-- 但是,李四的现金却增多了1000
use StudentDemoDB
go
update bankCounter set curMoney = curMoney - 1000 where cstName='张三';
update bankCounter set curMoney = curMoney 1000 where cstName='李四';
go
--还原数据
select * from bankCounter
update bankCounter set curMoney = curMoney - 1000 where cstName='李四';

-------------用事务处理
begin transaction
use StudentDemoDB
go
declare @errorT int --定义局部变量记录错误次数
set @errorT = 0 --初始化
update bankCounter set curMoney = curMoney - 1000 where cstName='张三';
set @errorT = @errorT @@error
update bankCounter set curMoney = curMoney 1000 where cstName='李四';
set @errorT = @errorT @@error

if(@errorT > 0)
begin
print '错误编码 #' convert(varchar,@errorT) '!'
rollback transaction
end
else
begin
commit transaction
end
go

方法1

DDL(数据定义语言、建表、建库等语言)(例:Create Table、Drop Table、Alter Table)

查看当前用户的角色
select * from user_role_privs;
select * from session_privs;

T-SQL语句创建表:

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

select * from bankCounter

begin transaction
declare @error int
set @error=0
update bankCounter set curMoney = curMoney - 800 where cstName = '张三'
set @error = @error @@ERROR
update bankCounter set curMoney = curMoney 800 where cstName = '李四'
set @error = @error @@ERROR

if(@error > 0)
begin
rollback transaction
end
else
begin
commit transaction
end
go
select * from bankCounter
go


--索引
--create [unique] [clustered|nonclustered] index index_name
--on table_name (column)
--[with
-- fillfactor = x
--]
--说明
--unique:执行唯一索引,可选
--clustered/nonclustered:指定聚集索引还是非聚集索引,可选
--fillfactor:表示填充因子,制定一个0-100的值,该值指示索引页填满的空间所占的百分比

--案例1:成绩表stuMark中的writtenExam列经常被查询,为了加快查询速度,现在创建索引。因为成绩可能会重复,
--因此索引只能创建 非聚集索引。
use StudentDemoDB
go
if exists(select name from sysindexes where name = 'IX_stuMarks_writtenExam')
drop index stuMarks.IX_stuMarks_writtenExam
go
create nonclustered index IX_stuMarks_writtenExam
on stuMarks(writtenExam)
澳门新萄京官方网站,with fillfactor=30
go


--视图
--create view view_name
--as
--<select语句>
--案例:stuInfo/stuMarks的视图
if exists(select name from sysobjects where name='view_stuMarksInfo')
drop view view_stuMarksInfo
go
create view view_stuMarksInfo
as
select a.stuName,a.stuNo,b.examNo,a.stuSex,a.stuAge,b.writtenExam,b.labExam
from stuInfo a,stuMarks b where a.stuNo = b.stuNo
go

--判断是否存在该数据库,存在就删除
if (exists (select * from sys.databases where name = 'database_name'))
drop database database_name
go
--创建数据库,设置数据库文件、日志文件保存目录
create database database_name
on(
name = 'database_name',
filename = 'c:datadatabase_name.mdf'
)
log on(
name = 'database_name_log',
filename = 'c:datadatabase_name_log.ldf'
)
go

DML(数据操作语言)(例:Select、Insert、Update、Delete)

查看当前用户的系统权限和表级权限
select * from user_sys_privs;
select * from user_tab_privs;

案例:

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
)

select * from view_stuMarksInfo

--存储过程

--系统存储过程
exec sp_databases --列出服务器上所有的的数据库
exec sp_helpdb --列出所有数据库的信息
exec sp_helpdb StudentDemoDB --列出指定数据库的信息
exec sp_renamedb 'A','B' --将数据A的名称改为B
exec sp_tables --列出当前环境下可查询的对象的列表

exec sp_columns stuInfo --列出指定表的的所有列的信息
exec sp_help --列出当前环境下所有的对象信息(表名/约束名/视图)
exec sp_help stuInfo --返回指定表的所有信息(表名/字段/约束/)
exec sp_helpconstraint stuInfo --返回指定表的约束信息
exec sp_helpindex stuInfo --返回指定表的索引信息
exec sp_stored_procedures --返回当前环境中的所有存储过程
exec sp_helptext 'view_stuMarksInfo'

 

DCL(数据库控制语言)(例:GRANT授权、REVOKE取消授权)

查询用户表
select username from dba_users;

T-SQL创建约束:

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

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

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

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

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

--查看视图/默认值/(未加密的|用户定义的存储过程)/触发器的语句文本

--创建不带参的存储过程
--create proc[edure] procedure_name
-- [
-- { @参数1 数据类型 }[= 默认值][output], --其中参数部分可选
-- ······, --其中参数部分可选
-- { @参数n 数据类型 }[= 默认值][output] --其中参数部分可选
-- ]
-- as
-- <select语句>

--案例:查看本次考试平均分,以及未通过(writtenExam < 60 or labExam < 60)的考试学员名单
use StudentDemoDB
go
if exists(select name from sysobjects where name = 'proc_avg_nopass_nopara')
--drop proc proc_avg_nopass_nopara
drop procedure proc_avg_nopass_nopara --与上面的drop proc proc_avg_nopass等价
go
create procedure proc_avg_nopass_nopara
as
select avg(writtenExam) as '笔试平均成绩',avg(labExam) as '机试平均成绩' from stuMarks
select a.stuName,a.stuNo,b.examNo,a.stuSex,a.stuAge,b.writtenExam,b.labExam
from stuInfo a,stuMarks b where a.stuNo = b.stuNo and (b.writtenExam < 60 or b.labExam < 60)
go

exec proc_avg_nopass_nopara
go

select * from stuMarks
update stuMarks set writtenExam=55 where stuNo='s25303'

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

实例代码:

修改用户口令密码
alter user "username" identified by "password";

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

alter table 表名

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

go

--创建带输入参数的存储过程
--create proc[edure] procedure_name
-- [
-- { @参数1 数据类型 }[= 默认值][output], --其中参数部分可选,有output表示输出参数,否则为输入参数
-- ······, --其中参数部分可选,有output表示输出参数,否则为输入参数
-- { @参数n 数据类型 }[= 默认值][output] --其中参数部分可选,有output表示输出参数,否则为输入参数
-- ]
-- as
-- <select语句>

--案例:查看本次考试平均分,以及未通过(writtenExam < 自定义参数 or labExam < 自定义参数)的考试学员名单
use StudentDemoDB
go
if exists(select name from sysobjects where name = 'proc_avg_nopass_para')
drop proc proc_avg_nopass_para
go
create proc proc_avg_nopass_para
@written int,
@lab int
as
select avg(writtenExam) as '笔试平均成绩',avg(labExam) as '机试平均成绩' from stuMarks
select a.stuName,a.stuNo,b.examNo,a.stuSex,a.stuAge,b.writtenExam,b.labExam
from stuInfo a,stuMarks b where a.stuNo = b.stuNo and (b.writtenExam < @written or b.labExam < @lab)
go

exec proc_avg_nopass_para 55,65
go


--默认值(条件)存储过程
--create proc[edure] procedure_name
-- [
-- { @参数1 数据类型 }[= 默认值][output], --其中参数部分可选,有output表示输出参数,否则为输入参数
-- ······, --其中参数部分可选,有output表示输出参数,否则为输入参数
-- { @参数n 数据类型 }[= 默认值][output] --其中参数部分可选,有output表示输出参数,否则为输入参数
-- ]
-- as
-- <select语句>

--案例:查看本次考试平均分,以及未通过(writtenExam/labExam默认<60不通过)的考试学员名单
use StudentDemoDB
go
if exists(select name from sysobjects where name = 'proc_avg_nopass_default')
drop proc proc_avg_nopass_default
go
create proc proc_avg_nopass_default
@writePass int=60, --默认值
@labPass int=60 --默认值
as
select avg(writtenExam) as '笔试平均成绩',avg(labExam) as '机试平均成绩' from stuMarks
select a.stuName,a.stuNo,b.examNo,a.stuSex,a.stuAge,b.writtenExam,b.labExam
from stuInfo a,stuMarks b where a.stuNo = b.stuNo and (b.writtenExam < @writePass or b.labExam < @labPass)
go
exec proc_avg_nopass_default
go


--创建带输出参数的存储过程
--create proc[edure] procedure_name
-- [
-- { @参数1 数据类型 }[= 默认值][output], --其中参数部分可选,有output表示输出参数,否则为输入参数
-- ······, --其中参数部分可选,有output表示输出参数,否则为输入参数
-- { @参数n 数据类型 }[= 默认值][output] --其中参数部分可选,有output表示输出参数,否则为输入参数
-- ]
-- as
-- <select语句>

--案例:查看本次考试平均分,以及未通过(writtenExam/labExam默认<60不通过)的考试学员名单,还想返回不及格人数
use StudentDemoDB
go
if exists(select name from sysobjects where name = 'proc_avg_nopass_output')
drop proc proc_avg_nopass_output
go
create proc proc_avg_nopass_output
@noPassNum int output,
@wPass int=60,
@lPass int=60
as
select avg(writtenExam) as '笔试平均成绩',avg(labExam) as '机试平均成绩' from stuMarks
select a.stuName,a.stuNo,b.examNo,a.stuSex,a.stuAge,b.writtenExam,b.labExam
from stuInfo a,stuMarks b where a.stuNo = b.stuNo and (b.writtenExam < @wPass or b.labExam < @lPass)
select @noPassNum = count(c.stuName) from (select a.stuName,a.stuNo,b.examNo,a.stuSex,a.stuAge,b.writtenExam,b.labExam
from stuInfo a,stuMarks b where a.stuNo = b.stuNo and (b.writtenExam < @wPass or b.labExam < @lPass)) c
go

/*调用存储过程proc_avg_nopass_output*/
declare @sum int --声明一个局部变量,用来接收存储过程中输出的参数值
exec proc_avg_nopass_output @sum output --输出参数,此时@wPass,@lPass使用默认值
print convert(varchar,@sum)
go
declare @sum int
exec proc_avg_nopass_output @sum output,66 --此时@wPass=66,d@lPass使用默认值
go
declare @sum int
exec proc_avg_nopass_output @sum output,66,100 --此时@wPass=66,@lPass=100
go

--判断是否存在该数据库,存在就删除
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

--1.创建一个数据库

显示当前用户
show user;

T-SQL删除约束:

alter table 表名

drop constraint 约束名

 

create database School;

表空间管理

案例:

--添加主键约束(将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

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

--删除数据库

创建表空间
create  tablespace data01 datafile 'd:/DATA01.DBF' size 10M;

(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

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% --主数据文件的增长率
),
--次数据文件的具体描述
(
--数据文件的具体描述
name = 'database_name2_data', --主数据文件的逻辑名
fileName = 'c:database_name2_data.mdf', --主数据文件的物理名
size = 2MB, --主数据文件的初始大小
maxSize = 50MB, --主数据文件增长的最大值
fileGrowth = 10% --主数据文件的增长率
)
--日志文件的具体描述,各参数含义同上
log on (
name = 'database_name_log',
fileName = 'c:database_name_log.ldf',
size = 1MB,
fileGrowth = 1MB
),
(
name = 'database_name2_log',
fileName = 'c:database_name2_log.ldf',
size = 1MB,
fileGrowth = 1MB
)
go

drop database School;

drop tablespace data01 including contents and datafiles;

(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 表名

 

 

--创建数据库的时候指定一些选项

修改表空间大小
alter database datafile 'D:/DATA01.DBF' resize 5M;

(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

Ø 基本数据类型

create database School

增加表空间
alter tablespace newccs add datafile 'D:/DATA02.DBF' size 40M;

(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

on primary

查看数据库文件
select * from dba_table_files;
 
查询当前存在的表空间
select * from v$tablespace;

(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')

 

(

表空间情况
select tablespace_name,sum(bytes)/1024/1024 from dba_data_files group by tablespace_name;

(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

Ø 判断表或其他对象及列是否存在

name='School',

查询表空间剩余空间
select tablespace_name,sum(bytes)/1024/1024 from dba_free_space group by tablespace_name;

(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 '食品'

--判断某个表或对象是否存在
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

filename='D:Desktop置心一处,无事不成。二、编程(四)笔记黑马程序员.NET视频教程-Time第课-数据库开发及ado.netSchool.mdf',

查看表结构
desc table;

(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
澳门新萄京官方网站:SQLserver高档编制程序,server常用语法点。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#中的调用其实就是一个三层架构

--判断该列名是否存在,如果存在就删除
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

size=5mb,

约束管理

 

--filegrowth=10mb,

非空约束
创建非空约束
create table test(id number(6) not null);

Ø 创建、删除表

filegrowth=10%,--按照文件的百分比增长

添加非空约束
alter table 表名 modify 列名 not null

--判断是否存在当前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

maxsize=100mb

删除表中的非空约束
alter table 表名 modify 列名 null

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

)

主键约束 primary key

 

log on

创建primary key 约束
 create table test (id number(6) primary key);

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

(

添加primary key 约束
 alter table 表名 add constraint 自定义约束名 primary key (列名)

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

name='School_log',

删除约束

--添加多个字段
alter table student
add address varchar(22),
tel varchar(11),
idCard varchar(3);

filename='D:Desktop置心一处,无事不成。二、编程(四)笔记黑马程序员.NET视频教程-Time第课-数据库开发及ado.net澳门新萄京官方网站:SQLserver高档编制程序,server常用语法点。School_log.ldf',

alter table 表名 drop constraint 约束名

--判断该列名是否存在,如果存在就删除
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

size=3mb,

唯一约束 unique

 

filegrowth=3%,

创建表时
create table users(
 id number(6) primary key not null,
 name varchar2(20) unique);
)

Ø 添加、删除约束

maxsize=20mb

添加unique约束
alter table 表名 add unique(列名)

--添加新列、约束
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;

)

检查约束 check

----- 多个列、约束一起创建--------
alter table student add
/*添加id主键、自增*/
id int identity constraint id primary key,
/* 添加外键约束*/
number int null
constraint uNumber references classes(number),
/*默认约束*/
createDate decimal(3, 3)
constraint createDate default 2010-6-1
go

 

创建表时
create table users(
  id number(6) primary key not null,
  sex varchar2(2) check(sex='男' or sex='女') default '男';
)

--删除约束
alter table student drop constraint no_uk;

--切换数据库

添加check约束
alter table users addsex varchar2(2) check(sex = '男' or sex = '女') default '男';

 

use School;

外键约束 foreign key

Ø 插入数据

--在School数据库中创建一个学生表.TableStudent

先创建一个班级表
create  table class(
  classId number(6) not null primary key,
  className varchar2(10),
  count number(100)
);

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

Create table TblStudent

再创建一个学生表
create table student(
  studentId number(6) not null primary key,
  studentName varchar2(20),
  studentClass number(6) references class(classId)
);

insert into student
select 'bulise' name, age, sex, cid
from student
where name = 'tony';

(

添加外键约束
alter table student add constraint fk_student_class referenc class (classId);

--多条记录同时插入
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;

   --表中列的定义在这对小刮号中

级联删除约束
添加外键时在外键后面加
alter table student add constraint fk_student_class referenc class (classId) on delete cascade;

 

   tSId int identity(1,1)  primary key,

删除外键时添加级联删除约束
alter table 表名 drop primary key cascade;

Ø 查询、修改、删除数据

   tSName nvarchar(10) not null ,

查看表的所有约束以下两种方法都可以
select table_name,constraint_name,constraint_type from user_constraints
where table_name='大写的表名'

--查询数据
select * from student;
select id, 'bulise' name, age, sex, cid from student
where name = 'tony';
select *, (select max(age) from student) from student
where name = 'tony';

   tSGender bit not null,

select table_name,constraint_name,constraint_type from dba_constraints
where table_name='大写的表名'

--修改数据
update student set name = 'hoho', sex = 1 where id = 1;

   tSAddress nvarchar(300),

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

   tSPhone varchar(100),

 

   tSAge int,

Ø 备份数据、表

   tSBirthday datetime,

--备份、复制student表到stu
select * into stu from student;
select * into stu1 from (select * from stu) t;

   tSCardId varchar(18),

 

   tsClassId int not null--最后一行不加“,”

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

)

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

go

 

--创建一个班级表

 

create table TblClass

 

(

转载自 

tclassId int identity(1,1) primary key,

 

tclassName nvarchar(50) not null,

tclassDesc nvarchar(100)

)

介绍生成脚本工具

数据库→右键→任务→生成脚本

可选择生成什么样的脚本

  1. 选择生成的数据库版本
  2. 是否包含某些脚本等
  3. 是否生成带数据的脚本(2005、2008都有该功能Express没有。)

简单数据插入INSERT

--使用insert语句向数据库的表中插入数据

select * from TblClass

--1.insert向表中插入一条数据

insert into TblClass(tclassName ,tclassDesc ) values('Time第三期','珍惜时光')

--向自动编号列插入数据

set identity_insert tblClass on

insert into TblClass(tclassId ,tclassName ,tclassDesc ) values(101,'Time第三期','珍惜时光')

set identity_insert tblClass off

--Ctrl R 快捷键

--如果要向该表中的除了自动编号以外的其它所有列都插入数据,则可以省略列名

insert into TblClass values('Time第四期','时光不老,你我不散')

 

select * from TblStudent

 

--向TblStudent表中插入数据

----表中列的定义在这对小刮号中

--   tSId int identity(1,1)  primary key,

--   tSName nvarchar(10) not null ,

--   tSGender bit not null,

--   tSAddress nvarchar(300),

--   tSPhone varchar(100),

--   tSAge int,

--   tSBirthday datetime,

--   tSCardId varchar(18),

--   tsClassId int not null--最后一行不加“,”

 

insert into TblStudent(tSName,tSGender,tSAddress,tSPhone,tSAge,tSBirthday,tSCardId,tsClassId)

values('Time001',0,'上海区','1778918281',18,'1989-10-11','78267287282819829X',1)

 

insert into TblStudent

values('Time001',0,'上海区','1778918281',18,'1989-10-11','78267287282819829X',1)

 

--insert语句向表中那些不允许为空的列插入数据(tsClassId 不允许为空,必须插入数据)

insert into TblStudent(tSName,tSGender ,tSAddress ,tsClassId )

values('Time002',0,'上海''郊区',2)

--null值就是表示空值

 

--insert into语句一次只能向表中插入一条记录,如果通过一条语句向表中插入多条记录,

--则需要使用其它方式insert语句。

 

select * from TblClass

--通过一条sql语句向表中插入多条sql语句

insert into TblClass(tclassName ,tclassDesc)

select 'Time第五期','珍惜时间,岁月不老' union

select 'Time第五期','珍惜时间,岁月不老' union

select 'Time第五期','珍惜时间,岁月不老' union

select 'Time第五期','珍惜时间,岁月不老' union

select 'Time第五期','珍惜时间,岁月不老' --最后一行不需要union

 

create table TblClassBak

(

ClsId int identity(1,1) primary key,

ClsName nvarchar(50) not null,

ClsDesc nvarchar(50) null

)

 

select * from TblClassBak

--将TblClass表中的数据导入(复制)到TblClassBak表中

--也是使用Insert语句

--insert into表(列) select 列,列from 表

insert into TblClassBak (ClsName ,ClsDesc )

select tclassName,tclassDesc from TblClass

--N前缀,在存储中文字符时候,必须加大写字母N。

简单数据更新(数据修改)

use School

 

select * from TblClass

--更新语句

update 表名 set 列名=值,列名=值 where 条件

--在编写更新语句的时候,如果没有where条件则表示将表中的所有数据都更新为指定的数据。

--所有学生年龄加

update Student set sAge=sAge 1

--这么写tclassName='▲',表示这列的值变为一个‘▲’

--而我们的要求是在原来的列的基础上加上‘▲’

update TblClass set tclassName=tclassName '▲' where tclassId <100

--更新多列

update TblClass set tclassName=tclassName '▲',tclassDesc =tclassDesc '★' where tclassId =100

--where中可以使用其它的运算符:(||)or ,<>(!=), (&&)and,(!)not

简单数据删除(DELETE)

删除表中全部数据:delete from Student

Delete 只是删除数据,表还在,和Drop Table不同

Delete 也可以带whre子句删除一部分数据:例:delete from student where sAage >20

Truncate table student 的作用跟delete from student 一样

都是删除student表中的全部数据,区别在于:

1.  Truncate语句非常高效。由于truncate操作采用按照最小方式记录日志,所以效率比较高。对于数百万条数据使用truncate删除只要几秒钟,而使用delete需要几个小时。

2.  Truncate语句会把表中的自动编号重置为默认值(恢复到种子的位置),而通过delete删除表中的数据后,还是从之前的数据继续添加。

3.  Truncate语句不触发delete触发器。

4.  Truncate 只能将表中所有数据删除,无法添加where,不能根据条件来删除。

--复习

insert into 表(列) values(值)

update 表 set 列=值,列=值 where ...

--删除语句

delete from 表名  where ...

select * from TblClass

--删除tclassId为偶数的数据

DELETE FROM TblClass where tclassId%2=0

--删除表中全部数据

delete from TblClass

truncate table TblClass

--删除表

drop table TblClass

通过设计器增加约束(结束.保证数据完整性)

--通过设计器添加约束

--为EmapEmail添加一个唯一约束

--设计→右键→索引/键(已存在EmpId主键索引)→添加→设置(类型:唯一键;选择对应的列)

 

--为性别添加一个默认约束,默认为'男'

--设计→选中该列→在下方属性中→默认值或绑定(输入:男)

 

1.主键约束

2.外键约束

3.唯一约束

4.默认约束

5.检查约束

6.非空约束

7.添加外键时,设置级联更新,级联删除

通过SQL添加约束

--删除表

drop table Employees

drop table Department

--创建表

create table Employees

(

    EmpId int identity(1,1),

    EmpName varchar(50),

    EmpGender char(2),

    EmpAge int,

    EmpEmail varchar(100),

    EmpAddress varchar(500)

)

create table Department

(

    DepId int identity(1,1),

    DepName varchar(50)

)

-============手动增加约束==========

--手动删除一列(删除EmpAddress列)

alter table Employees drop column EmpAddress --删除、修改时候需要加关键字column,添加时候则不需要。

 

--手动增加一列(增加一列EmpAddr varchar(1000))

alter table Employees add  EmpAddr varchar(1000) not null

 

--手动修改一下EmpEmail的数据类型(varchar(200))

alter table Employees alter column EmpEmail varchar(200) not null

 

--为EmpId添加一个主键约束

alter table Employees add

constraint PK_Employees_EmpId primary key(EmpId)

 

--非空约束,为EmpName增加一个非空约束

alter table Employees alter column EmpName varchar(50) not null

 

--为EmpName增加一个唯一约束

alter table Employees add constraint

UQ_Employees_EmpName unique(EmpName)

 

--为性别增加一个默认约束,默认为'男'

alter table Employees add constraint

DF_Empyloyees_EmpGender default('男') for EmpGender

 

--为年龄增加一个检查约束:年龄必须在-120岁之间,含岁与岁。

alter table Employees add constraint

CK_Employees_MepAge check(EmpAge>=1 and EmpAge<=120)

 

 

--创建一个部门表,然后为Employee表增加一个DepId列。

alter table Employees add DepId varchar(50) not null

 

--为Department表增加一个主键约束

alter table Department add constraint

PK_Department_DepId primary key(DepId)

 

--为DepName列增加一个非空约束与唯一约束

--非空约束

alter table Department alter column DepName varchar(50) not null

alter table Department add constraint UQ_Department primary key(EepName)

 

--为Employee表增加一个DepId列。

alter table Employees add DepId int not null

 

--增加外键约束

alter table Employees add constraint

FK_Emolopyees_DepId foreign key(DepId) references Department(DepId)

 

 

--删除Employee

drop table Employees

drop table Department

truncate table TblClass

 

--通过sql代码删除约束

alter table Employees drop constraint CK_Employees_EmpAge

 

--用一句话来删除相应的约束

alter table Employees drop constraint

CK_Employees_EmpAge,

CK_Employees_MepAge,

DF_Empyloyees_EmpGender

 

--通过一句代码来创建所有约束

alter table Employees add

constraint UQ_Employees_Empname unique(Empname)

constraint CK_Employees_EmpAge check(EmpAge>=1 and EmpAge<=120),

onnstraint DF_Employees_EmpGender default('男') for EmpGender,

constraint FK_Employees_Dempartment foreign key(DepId)

references Department(DepId) on delete cascade on update cascade

数据检索:数据查询

--查询所有数据:表中的所有列的所有行。

select 

from TblStudent

--查询指定的列的数据,由于没有加where条件所以这里也是要返回所有行的数据。

select tsname,tsgender,tsage

from TblStudent

--as 可以设置显示的列名

select '黄林' as 姓名,18 年龄,'huanglin@yahoo.com' 邮箱

select 1 1 as '1 1的结果'

--获取当前系统时间(网格型)

select getdate() as 时间

--输出时间(文本型)

print getDATE()

TOP、Distinct

TOP/Distinct

--查询前五个

select top 5 * from Employees

--查询ID最大值前五条,降序排序

select top 5 * from Employees order by EmployeeId desc

--查询部分数据前五条

select top 5

  Lastname,

  FirstName,

  [Address]--因为Address是关键,则需加上[]

  from Employees

--查询前百分之十(向上取整)

select top 10 percent * from Employees

 

--获取年级最小的五个学生

select top 5 * from TblStudent order by tsage asc

--获取年纪最大的%的学生

select top 10 * from TblStudent order by tsage desc

 

--distinct是对查询出的整个结果集进行数据重复处理的,而不是针对某一列

select

    distinct

    tsname,

    tsgender,

    tsaddress

from TblStudent

聚合函数                                        

SQL聚合函数:

MAX(最大值)、MIN(最小值)、AVG(平均值)、SUM(和)、COUNT(数量:记录的条数)

本文由澳门新萄京官方网站发布于数据库网络,转载请注明出处:澳门新萄京官方网站:SQLserver高档编制程序,s

关键词: