SQL Server与ADO.Net(二)
--------------------- ASP.Net+Android+IOS开发、.Net培训、期待与您交流! ----------------------
1、数据的更新(修改数据)
l 修改单个值
• updata 要修改的表名 set 字段名=值 where条件
l 修改多个值
• updata 表名 set 字段1=值1,字段2=值2 where条件
l 追加可使用:字段=字段+值
l where多条件使用and连接
• 优先级:not -> and -> or
• 不等于:<>或!=
l 更新一个列:update Student set sSex = ‘男’
l 更新多个列: update Student set sSex ='女',sAge = 18,sBirthday='1989-8-8'
l 更新一部分数据: update Student set sClassId= 4 where sClassId = 1,用where语句表示只更新Name是’tom’的行,注意SQL中等于判断用单个=,而不是==。
l Where中还可以使用复杂的逻辑判断update Student set sAge=30 wheresName='华佗' or sAge<25 ,or相当于C#中的||(或者)
l 所有学生的年龄加1update Student set sAge = sAge +1
l update Student set sClassId=6
l where (sAge>20 and sAge<30)or(sAge=50)
l Where中可以使用的其他逻辑运算符:or、and、not、<、>、>=、<=、 <>(或!=)等
2、备份表数据(*)
l select * into 新表名 from 源表名
l 可以加where条件
l 备份表结构
• select * into 新表 from 源表 where 1>2
l 备份表时只会备份表结构,对于约束不会备份
3、插入多条数据(*)
l 从源表中某特定数据加到一新表中
l insert into 目标表名 select 字段列表 from 源表名
4、数据删除
l 删除表中全部数据:delete from 表名
• 只删除数据,如果不加where条件表明删除所有数据
• delete只是删除数据,表还在,和drop table不同。
l delete也可以带where子句来删除一部分数据
l 对于删除的数据,主键自动增长计数器依旧执行
l 想主键清零,从头来过使用truncate
• truncate table 表名
• truncate清空表中的数据,不能加where子句
• truncate删除是数据不计入日志,慎用
l 删除表
• drop table 表名
5、约束
l 数据库约束是为了保证数据的完整性(正确性)而实现的一套机制
l 非空约束 not null
l 主键约束(PK) primary key constraint 唯一且不为空
l 唯一约束 (UQ)unique constraint 唯一,允许为空,但只能出现一次
l 默认约束 (DF)default constraint 默认值
l 检查约束 (CK)check constraint 范围以及格式限制
l 外键约束 (FK)foreign key constraint 表关系
9、手动添加约束(设计器)
l 单击右键 -> 设计表
l 主键
l 唯一约束
• 右键 -> 索引键 -> 添加 -> 常规 -> 唯一键
• 命名“UQ_表名_字段名”
l 检查约束
• 右键 -> check约束 -> 添加 -> 表达式
• 命名“CK_表名_列名”
10、T-SQL实现添加约束
l 使用修改表结构方式实现
l 修改表结构使用alter table
l alter table 表名 add constraint 约束名 约束类型与条件
l 主键约束:primary key(字段)
l 唯一约束:unique(字段)
• 注意为空与空字符串
l check约束:check(约束表达式)
l 默认约束:default(值) for 字段名
l 添加多个约束(逗号隔开)
• alter table 表名
• add
• constraint 约束名 约束类型与条件
l 如:1、EmpId设为主键
修改表结构使用alter table
alter table Employee add constraintPK_Empolye_EmpId primary key(EmpId)
修改表Employee
在表Employee中添加一个约束,约束类型是主键约束
主键字段为EmpId,名字为PK_Employee_EmpId
l 2、EmpName必须唯一(不能有重名)
alter table Employee add constraintUQ_Employee_EmpName unique(EmpName)
l 3、性别只能是“男”或“女”
altertable Employee
addconstraint CK_Employee_EmpGender check(EmpGender='男' or EmpGender='女')
l 4、年龄只能是0-150岁(含)
altertable Employee
addconstraint CK_Employee_EmpAge check(EmpAge>=0 and EmpAge<=150)
l 5、Email不能为空
添加一些别的约束(长度大于20,必须包含@)
函数len(),可以得到字段的长度
altertable Employee add constraint
CK_Employee_EmpEmailcheck(len(EmpEmail)>=20 and Empmail like '%@%')
l 6、alter table 表名 constraint DF_表名_列名 default(值) for 列名
11、删除约束
l alter table 表名 drop constraint 约束名
l 可以删除多个约束,用,分隔开
12、建表时添加约束
l 在字段后直接加上约束类型与条件
l 主键:primary kay
l 非空约束:not null
l 唯一约束:unique
l 检查约束:check(约束表达式)
l 默认值约束:default(值)
13、如何查看已有约束
l 约束与表是同一级别
l select * from sys.objects
• type不同表示的类型不一样
• S 系统表
• U 用户表
• PK 主键
• UQ 唯一约束
• C 检查约束
• D 默认约束
l 注:存储过程
sp_tables 获取数据库中的表和视图
sp_databases 获取所有数据库
sp_datatype_info 获取数据的类型
l 如何查看存储过程的实现
选择可编程性 -> 找到sp_tables(可以删选得到)-> 右键修改
l 可是使用sp_help 表名进行查看
14、修改表
l 删除一列
• alter table 表名 drop column 列名
l 增加一列
• alter table 表名 add 列名 类型
l 修改数据类型
• alter table 表名 alter column 列名 类型
15、关于外键
l 设计器操作
• 在外键表上右键设计
• 任意右键 -> 关系 -> 添加 -> 设置表和列规范(谁与谁关联)
• 命名规范:FK_外键表_主键表_主键
l T-SQL
• alter table 外键表 add constraint 外键名 foreign key(外键) references 主键表(主键)
16、数据的检索(查询)
l 检索数据
• select * from 表名
• select 字段列表 from 表名
• select * from 表名 where条件
l 别名
• 字段名 as 别名(可省略as)
• 别名=字段名
l select的常见使用
• 获得当前时间:select getdate()
• 语句计算结果:select 1+1
附:随机练习代码
--练习
--use master
--go
--createdatabase School
--on
--(
-- name='School',
-- filename='D:\Files\DataBase\School.mdf',
-- size=3mb,
-- maxsize=1024mb,
-- filegrowth=10%
--)
--log on
--(
-- name='School_Log',
-- filename='D:\Files\DataBase\School_Log.ldf',
-- size=1mb,
-- maxsize=1024mb,
-- filegrowth=10%
--)
--go
--创建学生成绩表Score
--scoreId,studentId,english,math
use School
go
create tableT_Score
(
scoreId int,
studentId int,
english int,
math int
)
--创建老师表Teacher
--tId,tName,tSex,tAge,tSalary,tBirthday
create tableT_Teacher
(
tId int,
tName nvarchar(10),
tSex bit,
tAge int,
tSalary int,
tBirthday datetime
)
--学生表Student
--studentId name age
create tableT_Student
(
studentId int,
[name] nvarchar(10),
age int
)
--插入数据
insert intoT_Teacher(tName,tSex, tAge, tSalary,tBirthday) values('赵六',0,25,3500,1988-6-5)
insert intoT_Teacher(tName,tSex, tAge, tSalary,tBirthday) values('赵六c','True',25,3500,'1988-6-5')
--select语句查看表内容
select *from T_Teacher;
select tName,tBirthdayfrom T_Teacher;
--当插入完整数据的时候,列名可以省略(不推荐使用)
insert intoT_Teacher
values('小五',1,24,4000,'1989-9-28')
--当没有做非空约束的时候,可以不给其插入数据
--即insertinto可以插入一部分内容
insert intoT_Teacher(tName,tSex)values('兰兰','false')
--当数据为空的时候,里面是null,null表示数据不知道是什么
--修改所有的age为岁
update T_Teacherset tAge=25;
--此类操作慎重操作,update使用极少出现全部更改的情况,注意每次使用时都要有where,where表示条件,更新数据满足的要求
update T_Teacherset tAge=30where tSalary>=4500;
--将性别为的年龄都改为,工资都改为
update T_Teacherset tAge=20,tSalary=5000where tSex=0;
--将男老师的工资都加元
update T_Teacherset tSalary=tSalary+2000where tSex=1;
--将工资大于,年龄大于的男老师,名字后面加上小康
update T_Teacherset tName=tName+'_小康'where tAge>25and tSalary>6000and tSex=1;
--备份T_Teacher表数据
select *into NewTeacher from T_Teacher;
--这里只备份了数据和自动增长,约束没有跟过来
--备份所有的姓名
select *into T_Name from T_Teacher wheretAge>=25;
--备份表结构
select *into T_NewTeacherfrom T_Teacher where 1>2;
--插入金条数据
insert into表名(字段名)--values(值)
select *from 源表名
insert intoT_NewTeacher
select *from T_Teacher
--删除T_T_NewTeacher中所有的数据
delete fromT_NewTeacher
--删除T_T_NewTeacher中某些数据
delete fromT_NewTeacher wheretAge<18;
--truncate删除表(重置自动增长字段的索引)
truncate table T_NewTeacher
--truncate要慎用,因为它不将数据记录日志文件,一旦被干掉,就无法恢复
--直接删除表,就是表没了,不仅仅是数据没了
drop tableT_NewTeacher
use School
go
--查看数据
select *from MyStudent
--练习:给studentId是的英语成绩加分
update MyStudentset FEnglish=FEnglish+10where FId=1
--练习:考试题偏难,所有人的成绩加分
update MyStudentset FMath=FMath+5,FEnglish=FEnglish+5;
--练习:所有女学生的年龄减岁
update MyStudentset FAge=FAge-1where FGender='女'and FAge>=2;
--==外键约束===
--首先插入分数,使用一个学生
use School
go
select *from T_Student;
select *from T_Score;
--插入分数
insert intoT_Score(studentId,english, math)values(1,100,100)
--删除一条学生ID等于的记录
delete fromT_Student where studentId=1;
--此时会报错
--如果非要删除学生信息就需要将外键表中的引用全部删除
delete fromT_Score where studentId=1; --删除外键表引用
delete fromT_Student where studentId=1; --删除主键表数据
--即使是主键表,如果数据没有被引用,删除不会受到影响
--T-SQL添加外键
--alter table 外键表addconstraint外键名foreign key(外键) references主键表(主键)
alter tableT_Score add constraint Fk_T_Score_T_Student_studentIdforeignkey(studentId)references T_Student(studentId);
--==T-SQL添加约束===
--重新创建两张表,来实现修改表结构,添加约束
use School
go
create tableStudent
(
studentId int identity(1,1)primary key,
[name] nvarchar(10),
age int
)
--修改表结构添加约束
--er table 表名addconstraint约束名约束类型与条件
--添加一个唯一约束
alter tableStudent add constraint UQ_Studnet_nameunique([name]);
--添加数据检查
insert intoStudent values('张三',18);
--添加检查约束
--年龄要求在岁到岁之间
alter tableStudent add constraint CK_Student_agecheck(age>=18and age<=40);
--添加一个数据
insert intoStudent values('李四',41);
--添加一个默认约束,默认年龄都是岁
alter tableStudent add constraint DF_Student_agedefault(20)for age;
insert intoStudent([name])values('王五');
--一次添加多个约束
alter tableStudent
add
constraint UQ_Studnet_name unique([name]),
constraint CK_Student_age check(age>=18and age<=40),
constraint DF_Student_age default(20)for age;
--删除约束
--alter table 表名dropconstraint约束名
alter tableStudent drop constraint UQ_Studnet_name;
--一次删除多条约束
alter tableStudent
drop
constraint UQ_Studnet_name,
constraint CK_Student_age ,
constraint DF_Student_age ;
--==建表时添加约束==
create tableStudentInfo
(
studentId int identity(1,1)primary key,
[name] nvarchar(10)unique not null,
age intcheck(age>=18and age<=40)default(20)
)
--查看系统表数据
select *from sys.objects
sp_help StudentInfo
--==修改表==
create tableTT
(
studentId int identity(1,1)primary key,
[name] nvarchar(10),
age int
)
--添加一个性别的字段
alter tableTT add gender bit,mathint,englishint;
alter tableTT add isDel bit default('false');
--删除一个字段
alter tableTT drop column math,english;
--修改字段类型
alter tableTT alter column gender nvarchar(10);
--==约束练习==
--Teacher表中
--tSex 控制只能是男女,默认男
--tAge 在-40之间默认
--方法
create tableTeacher
(
tSex nvarchar(10)default('男')check(tSex='男'or tSex='女'),
tAge intcheck(tAge>=30and tAge<=40)default(30)
)
insert intoTeacher(tAge)values(40);
insert intoTeacher(tSex)values('女');
select *from Teacher;
--方法
create tableTeacher1
(
tSex nvarchar(10),
tAge int
)
alter tableTeacher1
add
constraint CK_Teacher1_tSex check(tSex='男'or tSex='女'),
constraint DF_Teacher1_tSex default('男')for tSex,
constraint CK_Teacher1_tAge check(tAge>=30and tAge<=40),
constraint DF_Teacher1_tAge default(30)for tAge
insert intoTeacher1(tAge)values(50);
insert intoTeacher1(tSex)values('female');
select *from Teacher1;
--Score表中
--studentId 是外键 先要把Student表中的sId设置为主键
--测试:在学生表(主表)中删除在成绩表中被引用的学生记录。
--测试:成绩表中添加一条新成绩,studentId在学生表中没有。
create tableScore1
(
id intidentity(1,1)primary key,
[sId] int,
english intcheck(english>=0and english<=150),
math intcheck(math>=0and math<=150)
);
go
create tableStudent1
(
id intidentity(1,1),
[name] nvarchar(10),
age int,
gender nvarchar(10)
);
go
alter tableStudent1
add constraintPK_Student1_id primarykey(id);
alter tableScore1
add constraintFK_Score1_sid
foreign key([sid])references Student1(id);
insert intoStudent1 values('上三',18,'男');
insert intoStudent1 values('李四',20,'女');
insert intoScore1([sId],math,english)values(1,150,150);
select *from Student1;
select *from Score1;
delete fromStudent1 where id=1; --报错
delete fromStudent1 where id=2; --可删除
delete fromScore1 where [sId]=1;
delete fromStudent1 where id=1;
--==检索==
select *from MyStudent;
--测试的时候使用*可以,在真实的项目中全部将字段写出
Select Fid,FName, FAge, FGender,FMath, FEnglish from MyStudent;
Select --显示
Fid as这是ID,
FName 这是姓名,
这是年龄=FAge,
FGender,FMath,FEnglish,
FMath+FEnglishas 总分
from MyStudent
where FGender='女'and FAge<20;
select GETDATE();
select 1+1;
---------------------- ASP.Net+Android+IOS开发、.Net培训、期待与您交流! ----------------------