SQL表的管理及使用

create database yourschool
go

use yourschool
go


--创建表

create table teacher
(
    tId int primary key identity(1,1), --identity设立自增长，从1开始每增加一条记录，主键值就增加1
    tName varchar(20),
    tSex varchar(6)
)

select * from teacher

go
--一个老师教多个学生，一个学生只有一个老师我们称这种关系叫做一对多的关系
--一的这面我们称为主键表，多的那面我们称为外键表，主键表不依赖外键表，也就是主键表不知道外键表的存在 
create table student
(
    sId int primary key identity(1,1),
    sName varchar(20) not null,
    sSex varchar(10),
    tId int foreign key references teacher(tId)
        --这里是设置外键约束，references表明引用的主键表是什么表括号里是主键表中被引用的字段，在这里tid，外键可以为空，比如在这个示例中，表明一个学生还没选老师。
)


--简单的insert语句(向表中增加一条数据)

insert teacher(tName,tsex) values('Micle','male')

--省略清单的insert语句

insert into teacher values('Bill','male')
go


select * from teacher



insert into student values('祥子','男',7)--因为有外键约束，所以不能成功，因为主键表teachr表中并没id为7的老师

insert into student values('祥子','男',1)
insert into student values('祥子弟','男',2) --成功，因为主键表中有tid为1的行(记录)

--无条件删除，慎用

select * from student

delete from student

--有条件删除

delete from student where sName like '祥_'
delete from student where sName = '祥子'

delete from student where sId = 16

--对主键表的删除操作

select * from teacher
select * from student

insert into teacher values('Rebica','female')

delete from teacher where tid = 1 --不能成功，因为编号为1的老师在外键表中有相应的学生

delete from teacher where tid = 3 --可以成功，因为rebica在外键表，学生表中没有对应的记录

--删除表

drop table teacher
go

--查看表属性
sp_help teacher
go

--查看表的行数和所用的存储空间大小
sp_spaceused teacher
go

--查看表和数据库对象间的关系
sp_depends student
go

select * from student

--索引，SQLServer中的每一行是没有前后顺序的，所以当查询一行时要执行全表扫描，建立索引可以生成一个只有一列的小的索引表可以加快查找速度。在键值上建立的索引是簇集索引(Clustered)，每个表只有一个主键，所以在一个表中簇集索引只能有一个簇集索引根据键值进行排序，非簇集索引(Nonclustered)不进行排序。建立索引可加快查询速度，但有些情况也不是这样如：查找一个小表，或在一个大表中查找大多数数据就不能加快速度。当在一个大表中查找有限数据时会明显加快速度。

--适合建立索引的情况：经常被查询搜索的列，如经常在where子句中出现的列；在Order by子句中使用的列；是外键或主键的列；列值唯一的列。
 --不适合建立索引的情况：查询中很少使用到的列；包含太多重复的值如性别列；数据类型为bit,text,image等的列不能建立索引。
--可以在多列上建立一个索引这叫复合索引，用复合索引代替多个单独的索引在数据操作期间所用的开销会更小

--创建索引

create table empPay
(
    employeeId int not null,
    basePay money not null,
    commission decimal(2,2) not null
)
go

select * from empPay
delete from empPay
insert into empPay values(1,20,0.4)

create unique clustered index employeeIdIndex
on empPay(employeeId)
go
--删除索引
drop index empPay.employeeIdIndex
go


--编辑表格数据

--简单的insert语句(向表中插入数据)

insert teacher(tName,tsex) values('Jhon','male')

--省略清单的insert语句

insert into teacher values('Bill','male')
go

insert into student values('祥子','男',19)--因为有外键约束，所以不能成功，因为主键表teachr表中并没id为19的老师


--使用update修改数据
select * from teacher
--把id为1的老师修改

update teacher set tName = '李磊' where tId = 2
go

--update sudent set stuAvgrade = stuAvgrad + 20

--把名字叫bill的所有老师的名字修改
update teacher set tName = '张强' where tName = 'Bill'


select * from teacher

--批量更新
--把所有人的名字清为空串
update teacher set tName = ''
go

---暂时不用
update teacher set tName = 'Jhon' 
from (select top 2 * from teacher order by tName) as t1
go

select * from teacher


--删除表格数据

delete from teacher --删除所有表格数据
go

delete from teacher where tId = 1 -- 按条件删除表格数据


--利用子查询进行可控的批量更新
update teacher set tName = 'Lenon' 
from(select top 2 * from teacher order by tName) as t1
where teacher.tName = t1.tName
go

select * from teacher