黑马程序员-自学笔记-SQL Server与ADO.Net（二）

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培训、期待与您交流！ ----------------------