sqlserver学习笔记（二：简单的增删改查）

use student
--插入所有列，否则会报错：列名或所提供值的数目与表定义不匹配。
insert into dbo.student values('120001','张三','男',18,'云南省昆明市五华区',null,null)

--插入指定列
insert into dbo.student (StudentID,name,major) values('120002','李四','计算机科学与技术')

--同时插入多条数据
insert into dbo.student (StudentID,name,age,major) values('120003','赵一','20','计算机科学与技术'),
('120004','钱二','19','计算机科学与技术'),('120005','孙三','18','计算机科学与技术')

--查询dbo.student所有数据
select * from dbo.student

--查询dbo.student数据总条数
select COUNT(*) as 'student表数据总条数' from dbo.student

--以age为条件，降序查询dbo.student前三条数据数据
select top 3 * from dbo.student order by age desc

--以age为条件，升序查询dbo.student前三条数据数据
select top 3 * from dbo.student order by age asc

--简单的条件查询
select name from dbo.student where StudentID ='120001'

--查询中简单的函数运用sum，avg,max,min
select count(*) as '有年龄记录的学生',sum(age) as '年龄总和' from dbo.student where age is not null

select count(*) as '有年龄记录的学生',avg(age) as '平均年龄' from dbo.student where age is not null

select count(*) as '有年龄记录的学生',max(age) as '最大年龄' from dbo.student where age is not null

select count(*) as '有年龄记录的学生',min(age) as '最小年龄' from dbo.student where age is not null

--update语句，批量修改
update dbo.student set major ='土木工程'

--update语句，带条件
update dbo.student set major ='财务管理' where StudentID ='120001'

--update语句，同时修改多个字段
update dbo.student set gender='女',address ='云南省玉溪市通海县',major ='电气自动化' where StudentID ='120001'

--如果想删除部分数据用delete，注意带上where子句；
--如果想删除表，用drop；
--如果想保留表而将所有数据删除，如果和事务无关，用truncate
drop dbo.student

delete dbo.student

truncate table dbo.student

delete dbo.student where where age is null

delete dbo.student where where StudentID ='120001'