SQL插入例题

--创建表TongXunLu
CREATE TABLE TongXunLu
 (
 [tName] nvarchar(30),
 [tAddress] nvarchar(50),
 [tEmail] varchar(50)
 )
--创建表 students
CREATE TABLE students
 (
 [sId] int IDENTITY (1, 1) primary key NOT NULL ,
 [sName] varchar (50)   NOT NULL ,
 [sAddress] varchar (50) default 'daxing'   ,
 [sEmail] varchar (50) check([sEmail] like '%@%')   ,
 [sSex] bit NOT NULL
 )

insert into students (sname,saddress,semail,ssex) values ('张果老',default,'a@a',1)
insert into [students] (sname,saddress,semail,ssex) values ('李寻欢',null,'b@b',1)
insert into [students] (sname,saddress,semail,ssex) values ('令狐冲',default,'c@c',1)
insert into [students] (sname,saddress,semail,ssex) values ('程灵素',null,'d@f',0)
insert into [students] (sname,saddress,semail,ssex) values ('笑哈哈','beijing','e@e',0)
insert into [students] (sname,saddress,semail,ssex) values ('乐呵呵','neimenggu','f@f',1)
insert into [students] (sname,saddress,semail,ssex) values ('AAAAA',default,'a@a',1)
insert into [students] (sname,saddress,semail,ssex) values ('BBBBB',null,'b@b',1)
insert into [students] (sname,saddress,semail,ssex) values ('CCCCC',default,'c@c',1)
insert into [students] (sname,saddress,semail,ssex) values ('DDDDDD',null,'d@f',0)
insert into [students] (sname,saddress,semail,ssex) values ('EEEEE','beijing','e@e',0)
insert into [students] (sname,saddress,semail,ssex) values ('FFFFF','neimenggu','f@f',1)
insert into [students] (sname,saddress,semail,ssex) values ('GGGGGG',default,'a@a',1)
insert into [students] (sname,saddress,semail,ssex) values ('HHHHHH',null,'b@b',1)
insert into [students] (sname,saddress,semail,ssex) values ('IIIIII',default,'c@c',1)
insert into [students] (sname,saddress,semail,ssex) values ('JJJJJ',null,'d@f',0)
insert into [students] (sname,saddress,semail,ssex) values ('KKKKK','beijing','e@e',0)
insert into [students] (sname,saddress,semail,ssex) values ('MMMMM','neimenggu','f@f',1)
insert into [students] (sname,saddress,semail,ssex) values ('LLLLLL',default,'a@a',1)
insert into [students] (sname,saddress,semail,ssex) values ('NNNNN',null,'b@b',1)
insert into [students] (sname,saddress,semail,ssex) values ('OOOOO',default,'c@c',1)
insert into [students] (sname,saddress,semail,ssex) values ('PPPPP',null,'d@f',0)
insert into [students] (sname,saddress,semail,ssex) values ('QQQQQ','beijing','e@e',0)
insert into [students] (sname,saddress,semail,ssex) values ('RRRRR','neimenggu','f@f',1)
insert into [students] (sname,saddress,semail,ssex) values ('SSSSSS',default,'a@a',1)
insert into [students] (sname,saddress,semail,ssex) values ('TTTT',null,'b@b',1)
insert into [students] (sname,saddress,semail,ssex) values ('UUUUU',default,'c@c',1)
insert into [students] (sname,saddress,semail,ssex) values ('VVVV',null,'d@f',0)
insert into [students] (sname,saddress,semail,ssex) values ('WWWWW','beijing','e@e',0)
insert into [students] (sname,saddress,semail,ssex) values ('XXXXX','neimenggu','f@f',1)
insert into [students] (sname,saddress,semail,ssex) values ('YYYYY','beijing','e@e',0)
insert into [students] (sname,saddress,semail,ssex) values ('ZZZZZ','neimenggu','f@f',1)

 

--显示TongXunlu 和 students表中的内容
select * from TongXunlu
select * from students

--1.插入多行  插入数据的表之必需存在
insert into TongXuelu(tName,tAddress,tEmail)
select sname,saddress,semail
from students

delete TongXunlu

--2.插入多行 插入数据的表之前必须不能存在
drop table TongXunlu
select IDENTITY(int,1,1) as '我的标识',sname,saddress,semail
into TongXunlu
from students

--3.插入多行

INSERT  TongXunLu (tName,tAddress,tEmail)
SELECT '测试女生1','daxing','daxing @accp.com'     UNION
SELECT '测试女生2','huangcun','huangcun @accp.com' UNION
SELECT '测试女生3','haidian', 'haidian @accp.com'  UNION
SELECT '测试男生1','hebei','[e-mail]'        UNION
SELECT '测试男生2','shanghai','[e-mail]'  UNION
SELECT '测试男生3','nmg','[e-mail]'            UNION
SELECT '测试女生4','xj','[e-mail]'              UNION
SELECT '测试男生4','qh','[e-mail]'              UNION
SELECT '测试男生5','taiwan','[e-mail]'

--更新
update TongXunlu set tAddress='大众电器' where tAddress='xj'

--删除方法1
delete from Tongxunlu 
--删除方法2
TRUNCATE TABLE
--替换方法
update card set password=replace(password,'o','0' )