--创建表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','hebei@accp.com' UNION
SELECT '测试男生2','shanghai','shanghai@accp.com' UNION
SELECT '测试男生3','nmg','nmg@accp.com' UNION
SELECT '测试女生4','xj','xj@accp.com' UNION
SELECT '测试男生4','qh','qh@accp.com' UNION
SELECT '测试男生5','taiwan','taiwan@accp.com'
--更新
update TongXunlu set tAddress='大众电器' where tAddress='xj'
--删除方法1
delete from Tongxunlu
--删除方法2
TRUNCATE TABLE
--替换方法
update card set password=replace(password,'o','0' )