T-SQL_4 增删改查语句

数据库四种基本操作:

 

1, "增"  插入语句

 

/*插入语句*/use MyTestsDBgo--1, 添加标识列if exists(select name from sysobjects where name = 'newTable')drop table newTableselect pID, pName, identity(int, 1, 1) as pNointo newTablefrom TB_persongo--2, 插入一条记录insert into TB_person(pID, pName)values(610322198602239777,'达熊')insert into TB_stuInfo(stuNO, stuName, stuAge, stuID)values(2009106801, '宁波', 16, 610322198602239777)--3, 插入多条记录insert into TB_personselect 610322198602239775,'GOF' unionselect 610322198602239771,'Lee' unionselect 610322198602239773,'小马哥'

 

2, "删" 删除语句

 

use MyTestsDBgo/*删除数据*/--已被引用过的数据行,删除时触发REFERENCE约束冲突--delete from TB_person--where pID = 610322198602239777delete from TB_personwhere pID = 610322198602239775/*清空表*/Truncate table TB_stuInfo

 

3, "改" 修改语句

 

/*修改语句*/update TB_stuInfo set stuAge = 18where stuAge < 18

 

4, "查" 查询语句

 

select stuName as 学生姓名, 学生年龄 = stuAge, stuAddress as 学生住址from TB_stuInfowhere stuAge > 18group by stuAgehaving COUNT(stuAge)>1