March 27 2014

sqlserver基础理论和基本操作(2)

use ucjmh
go
–什么是sql 什么是t-sql
/*
(1)SQL语言
SQL语言是关系型数据库的标准语言(只是数据库能够识别的指令)
(2)T-SQL语言
是标准SQL语言的加强版,在标准的SQL命令之上进行了扩充.
T-SQL语言由以下4部分组成:
DDL(数据定义语言)
DML(数据操作语言)
DCL(数据控制语言)
TCL(事务控制语言)
其它:变量说明、流程控制、功能函数
——定义变量、判断、分支、循环结构等
——日期函数、数学函数、字符函数、系统函数等
数据库完整性约束概念
正确性+有效性+相容性=数据完整性
正确性:数据的合法性
有效性:数据是否属于定义的范围
相容性:多用户多任务情况下,保证更新时不出现与实际不一致的情况
数据完整性包括:
(1)实体完整性(Entity Integrity) 要求表中的每一行数据都反映不同的实体
1.UNIQUE 约束
2.PRIMARY KEY 约束
3.IDENTITY 属性
(2)域完整性(Domain Integrity)要求指给定列的输入有效性
1.限制类型(通过数据类型)
2.可能值的范围(通过 FOREIGN KEY 约束、CHECK 约束、DEFAULT 定义、NOT NULL 定义)

(3)参照完整性(Referential Integrity) 确保始终保持主键和外键的关系.确保在不同表之间关键性数据保持一致
1.FOREIGN KEY 约束

(4)用户自定义完整性(User-Defined Integrity)
存储过程、触发器

确保数据完整性的方法主要有:
(1)约束
(2)规则
(3)触发器

列约束和表约束
列约束—对列定义完整性约束条件,包含在列的定义中
表约束—对整个表定义完整性约束条件,在表的最后一列之后定义

约束主要包括:
主键约束(PRIMARY KEY)
缺省(标识)约束(DEFAULT)
检查约束(CHECK)
唯一约束(UNIQUE)
外键约束(FOREIGN KEY)

主键约束和唯一约束的区别
主键约束:
主键约束用来强制实体的完整性,它在表中定义一个主键来唯一标识表中的每行记录.
主键约束有如下特点:每个表只能有一个主键,主键可以是一列,也可以是多列的组合;
主键值必须唯一并且不能为空(即:用作主键的列如果是多列,每一个列都不能有空值出现),
对于多列组合的主键,某列值可以重复,但列的组合值必须唯一。

唯一约束
唯一约束用来强制数据的实体完整性,它主要用来限制表的非主键列中不允许输入重复值。
唯一约束有如下特点:一个表中可以定义多 个唯一约束;每个唯一约束可以定义到一列上,
也可以定义到多列上;空值可以出现在某列中一次。

( –ORACLE中不管你是primary key 还是unique 都会隐式的帮你创建unique类型的索引
CREATE TABLE tt
(
ID NUMBER PRIMARY KEY,
NAME VARCHAR2(20) UNIQUE
)
SELECT * FROM all_indexes WHERE table_name=’TT’ –oracle中执行的)

但是要sqlserver中有些不同:
select * from sys.objects where name =’student’ –object_id=’21575115′
select * from sys.indexes where object_id=’21575115′
–PK__student__3213E83F03317E3D CLUSTERED
–UQ__student__543848DE060DEAE8 NONCLUSTERED
在逻辑上:
oracle有以下几种索引
Single column 单行索引
Concatenated 多行索引
Unique 唯一索引
NonUnique 非唯一索引
Function-based函数索引
Domain 域索引
sqlserver中有两种类型的索引(聚集索引和非聚集索引),
非聚集索引其中包含堆上的非聚集所以,聚集索引上的非聚集索引。
sqlserver会在主键上自动聚集索引 在唯一约束的行上建非聚集索引
*/

–sqlserver中的标识属性:标识属性(IDENTITY)用于给字段赋予自动增长的标识号
/*IDENTITY [ ( seed , increment ) ]
参数说明:
seed—表的第一行所使用的值
increment—增量值*/
–创建一张表
create table student
(
id int primary key identity(1,1), –不要一激动写成number了
name varchar(20) not null,
address varchar(200) default(‘地址不详’),
person_id varchar(18) check(len(person_id)=18) unique
)
/** insert into student values(‘冯帅’,DEFAULT,’41092619960203003′)**/
insert into student values(‘冯帅’,DEFAULT,’41092619960203003′);

insert into student values(‘冯帅’,DEFAULT,’41092619960203003X’);
–提交
/* 消息 3902,级别 16,状态 1,第 1 行
COMMIT TRANSACTION 请求没有对应的 BEGIN TRANSACTION。
commit;*/

/**
ORACLE
SQL plus具有自动提交工作的能力,而不需要用户明确告诉它要做什么,或者通过set autocommit on来实现。
off是默认值,在几乎所有的程序中这都是被推荐的方式。
在commit之前,只有你自己才能看到所做的工作对表的影响,访问这个表的其他人看到的仍然是旧的信息。
实际上,你的工作实在“中间集结”区进行。
即使没有直接下达commit命令,有些操作(如quit, exit)以及数据定义语言的命令也会使提交发生。
SQL Server:
一般的T-SQL语句都是直接针对数据库表执行和编写的,这就是所谓的自动提交,
这是SQL Server的默认设置。即提交是自动进行。
为指示SQL Server不自动提交更改,需要使用以下语句:
SET IMPICIT_TRANSACTIONS ON
*/

–显式事物
begin transaction
insert into student values(‘冯帅二’,DEFAULT,’41092619990203003X’);
select * from student ;
rollback transaction; –commit transaction
select * from student ;

 
–创建好的表怎么进行修改 使约束更好呢
/*修改数据库表语法规则如下:
ALTER TABLE 表名
[ADD 子句]
[ALTER COLUMN 子句]
[DROP 子句]
[ADD CONSTRAINT 子句]
[DROP CONSTRAINT 子句] */

–primary key: add CONSTRAINT <: 约束名>] PRIMARY KEY [(列名表)]
–unique: add CONSTRAINT <: 约束名>] UNIQUE [(列名表)]
–check : add [CONSTRAINT<:约束名>] CHECK(<条件>)
–not null : add CONSTRAINT <: 约束名>] NOT NULL
–default : ADD [CONSTRAINT <: 约束名>] DEFAULT <默认值> FOR [(<列名>)]
–FOREIGN KEY: ADD [CONSTRAINT<: 约束>] FOREIGN KEY (<列表名1>)REFERENCE <引用表名>[(列名表2)] [ON DELETE<RESTRICT|CASCADE|SET NULL>]
–SQLSERVER中的一些简单语法:

create table stuinfo
(
stuno varchar(30) not null,
stuname varchar(30) not null,
stuage int,
stusex char(2),
stuaddress varchar(300)
)
alter table stuinfo add constraint pk_stuno primary key(stuno)
alter table stuinfo add constraint ck_stuage check(stuage between 15 and 45)
alter table stuinfo add constraint df_stuage default(18) for stuage
alter table stuinfo add constraint ck_stusex check(stusex=’男’ or stusex=’女’)
alter table stuinfo add constraint df_stusex default(‘男’) for stusex
alter table stuinfo add constraint df_stuaddress default(‘地址不详’) for stuaddress

select * from stuinfo

–发现少写了一个字段:stuid
alter table stuinfo add stuid char(18)
insert的使用:
–insert into 表名(字段名1,字段名2….) values(值1,值2….)
insert into stuinfo(stuno,stuname,stuage,stusex,stuaddress,stuid) values(‘1001′,’张三’,20,’男’,’河南省郑州市’,’101010101010101010′)
–主键约束
— –违反了 PRIMARY KEY 约束 ‘pk_stuno’。不能在对象 ‘dbo.stuinfo’ 中插入重复键。
insert into stuinfo(stuno,stuname,stuage,stusex,stuaddress,stuid) values(‘1001′,’张三’,20,’男’,’河南省郑州市’,’101010101010101010′)
–不能将值 NULL 插入列 ‘stuno’,表 ‘three.dbo.stuinfo’;列不允许有空值。INSERT 失败。
insert into stuinfo(stuno,stuname,stuage,stusex,stuaddress,stuid)
values(null,’张三’,20,’男’,’河南省郑州市’,’101010101010101010′)
–检查约束
–INSERT 语句与 CHECK 约束”ck_stuage”冲突。该冲突发生于数据库”three”,表”dbo.stuinfo”, column ‘stuage’。
insert into stuinfo(stuno,stuname,stuage,stusex,stuaddress,stuid)
values(‘1002′,’李四’,88,’男’,’河南省郑州市’,’101010101010101011′)
insert into stuinfo(stuno,stuname,stuage,stusex,stuaddress,stuid)
values(‘1002′,’李四’,24,’非’,’河南省郑州市’,’101010101010101011′) –还是与check约束相冲突
–唯一约束
–首先给stuid添加唯一约束
alter table stuinfo add constraint uq_stuid unique(stuid)
insert into stuinfo(stuno,stuname,stuage,stusex,stuaddress,stuid)
values(‘1002′,’李四’,24,’男’,’河南省安阳市’,’101010101010101010′) –违反了 UNIQUE KEY 约束 ‘uq_stuid’。不能在对象 ‘dbo.stuinfo’ 中插入重复键。
insert into stuinfo(stuno,stuname,stuage,stusex,stuaddress,stuid)
values(‘1002′,’李四’,24,’男’,’河南省安阳市’,’101010101010101011′) –对的,因为身份证号在表中不存在,所以添加成功
insert into stuinfo(stuno,stuname,stuage,stusex,stuaddress,stuid)
values(‘1003′,’王五’,25,’女’,’河南省安阳市’,null) –对的,因为身份号此列的值可以为空
insert into stuinfo(stuno,stuname,stuage,stusex,stuaddress,stuid)
values(‘1004′,’王五’,25,’女’,’河南省安阳市’,null) –错的,因为身份号此列的值已经有空值
–默认约束
–所定的语句是给表中所有的字段插入数据
insert into stuinfo(stuno,stuname,stuage,stusex,stuaddress,stuid)
values(‘1004′,’赵六’,’101010101010101012′) –应该有几个字为什么就应该有几个值
–对默认值如何进行处理
insert into stuinfo(stuno,stuname,stuage,stusex,stuaddress,stuid)
values(‘1004′,’赵六’,default,default,default,’101010101010101012′) –对的,使用default使用默认值
–非空约束
insert into stuinfo(stuno,stuname,stuage,stusex,stuaddress,stuid)
values(‘1005′,null,default,default,default,’101010101010101013’) –不能将值 NULL 插入列 ‘stuname’,表 ‘three.dbo.stuinfo’;列不允许有空值。INSERT 失败。
–以上的插入是给表中所有的列全部插入数据
–简单的写法
insert into stuinfo values(‘1005′,’马七’,default,’女’,default,’101010101010101013′) –省去表名之后的(所有字段名)
–能不能插入部分数据
insert into stuinfo(stuno,stuname,stuid) values(‘1006′,’痛惜’,’101010101010101014′) –1,能插入部分数据;2,默认值可以不插入值
insert into stuinfo(stuname,stuid) values(‘小冯’,’101010101010101015′) –错的,插入数据是整行的插入,给每个字段都要插入值,给stuno值入值是插入的是null值,与主键约束相冲突
insert into stuinfo(stuno,stuid) values(‘1007′,’101010101010101015’) –不能将值 NULL 插入列 ‘stuname’,表 ‘three.dbo.stuinfo’;列不允许有空值。INSERT 失败。
insert into stuinfo(stuno,stuname) values(‘1007′,’小痛惜’) –违反了 UNIQUE KEY 约束 ‘uq_stuid’。不能在对象 ‘dbo.stuinfo’ 中插入重复键。

–update语法:
–update 表名 set 字段名1=新值1,字段名2=新值2,… where 条件
–将马七名字字改为马七七
update stuinfo set stuname=’马七七’ where stuno=’1005′
–将马七七改为马七,将地址改为焦作市
update stuinfo set stuname=’马七’,stuaddress=’河南省焦作市’ where stuno=’1005′
update stuinfo set stusex=’男’ –更新时如果不加where条件则更新表中所有的数据
update stuinfo set stuno=’1001′ where stuno=’1006′ –与主键约束相冲突
update stuinfo set stuname=null where stuno=’1001′ –与not null约束相冲突
update stuinfo set stusex=’非’ –与check约束相ct
update stuinfo set stuaddress=default where stuno=’1005′
update stuinfo set stuno=’1007′ where stuno=’1005′
——————————————————–
–注意:
–1,可以更新1列或多列
–2,可以更新0行,1行,多行
update stuinfo set stuname=’aaa’ where stuno=’999′
——————————-
–语法:
–delete [from] 表名 where 条件
delete from stuinfo where stuno=’1007′ –删除了一行
delete from stuinfo where stuno=’1002′ or stuno=’1003′ –理解or和and –删除了多行
–注意:
–删除时可以删除0行,1行,多行
delete stuinfo –不加where条件,则删除表中所有的数据

–区分drop和delete之间的区别
–drop删除时是将表和表中的数据全部删除
–delete如果不加where条件是将表中所有的数据全部删除,但是表结构还在

–truncate删除表中全部的数据
–语法:truncate table 表名
–truncate table stuinfo
–1,truncate 不能加where条件,delete删除数据时可以加where条件
–2,truncate删除全部数据就和delete不加where条件是相同的
–3,truncate删除数据时比delete速度要快

 

————————————————
–与外键相关的增,删,改
–现在已经主键表,所以建 一张外键表
create table stuscore
(
stuno varchar(30) not null,
labexam int,
writtenexam int
)
–添加外键约束
alter table stuscore add constraint fk_stuno foreign key(stuno) references stuinfo(stuno)
–插入数据
insert into stuscore values(‘1004’,33,44) –主键表中有1004学员
insert into stuscore values(‘1009’,100,20) –错的,与外键约束相冲突,原因是主键表中没有1009学员

insert into stuscore values(‘1005′,33,55) –正确
–更新
update stuscore set labexam=100 where stuno=’1005′
update stuscore set stuno=’1007′ where stuno=’1004′
update stuscore set stuno=’1008′ where stuno=’1004′
update stuscore set stuno=’1008′ where stuno=’1005′
–删除

delete from stuinfo where stuno=’1005′
delete from stuscore where stuno=’1005’
–总结:
–删除与外键相关的数据时:先删外键表中的数据,再删除主键表中的数据
–插入时应该先向主键表中插入数据,再向外键表中插入数据
————————————————————

 

–一次性插入多行数据的三种方法:
–1,select …into
–语法:
–select 原表中的字段1,原表中的字段2…
–into 新表名
–from 原名名
–[where 条件s]

select * from stuinfoback –对象名 ‘stuinfoback’ 无效。

select *
into stuinfoback
from stuinfo –1,建新表,2,从from后的表中查询数据,3,将查询到的数据插入到新表中

–从stuinfo表中取得所有的男学员的信息,创建一个通讯录表
select stuname as 姓名,stuaddress as 地址
into tongxunlu –新表名必须不存在[此语句只能执行一次]
from stuinfo
where stusex=’男’

select * from tongxunlu

–2,insert …select
–语法:
–insert into 要插入的表名(新表中的字段1,新表中的字段2….)
–select 原表中的字段1,原表中的字段2,… from 原表名 [where 条件s]

select * from tongxunlu2 –对象名 ‘tongxunlu2’ 无效。

–以下的语句要成功的执行必须tongxunlu2表必须存在
–create table tongxunlu2
–(
— stuname varchar(30),
— stuaddress varchar(300)
–)

–drop table tongxunlu2

–复制表结构但不复制约束
select stuname,stuaddress
into tongxunlu2
from stuinfo where 1=2

insert into tongxunlu2(stuname,stuaddress) select stuname,stuaddress from stuinfo

–3, union
insert into stuinfo
select ‘1008’,’小九’,20,’女’,’甘肃天水’,’101010101010101014′ union
select ‘1009’,’小九九’,20,’女’,’甘肃天水’,’101010101010101015′ union
select ‘1010’,’小桥’,20,’女’,’甘肃天水’,’101010101010101016′ union
select ‘1011’,’小七’,20,’女’,’甘肃天水’,’101010101010101017′
insert into stuinfo
select ‘1012’,’太阳’,21,’男’,’河南’,’101010101010101018′ union
select ‘1012’,’太阳’,21,’男’,’河南’,’101010101010101018′ union
select ‘1012’,’太阳’,21,’男’,’河南’,’101010101010101018′ union
select ‘1012’,’太阳’,21,’男’,’河南’,’101010101010101018′ union
select ‘1012’,’太阳’,21,’男’,’河南’,’101010101010101018′ union
select ‘1012’,’太阳’,21,’男’,’河南’,’101010101010101018′
insert into stuinfo
select ‘1015’,’太阳’,21,’男’,default,’101010101010101021′ union
select ‘1016’,’太阳’,21,’男’,’河南’,’101010101010101022′

–注意: 请注意,UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时,每条 SELECT 语句中的列的顺序必须相同
–1,最后一个union不要
–2,值与值之间用逗号隔开
–3,select 和union之间的数据不要使用圆括号
–4,union可以过滤掉重复的行
–5,union中不能使用default
–UNION 结果集中的列名总是等于 UNION 中第一个 SELECT 语句中的列名。
–union 和union all 区别在于一个会去除重复记录 一个不会
select * from stuinfo
select * from stuscore

–如果想查询所有对象相关的东西 可以查看 sys.
–所有的对象都在sys.objects 找到objectid可以在其它视图或基表中找到对应信息



Copyright 2019. All rights reserved.

Posted 2014年3月27日 by ucjmh in category "sqlserver

Leave a Reply

Your email address will not be published. Required fields are marked *