SQL学习笔记

SQLServer

–示例.建库测试

–示例.建库测试

create database TestDBon( name=’TestDB’,–数据库逻辑名 filename=’D:\Test\TestDB.mdf’,–主文件路径 size=4,–文件初始大小 filegrowth=20%–数据增长方式)log on( name=’TestDB_log’,–数据库日志逻辑名 filename=’D:\Test\TestDB_log.ldf’,–主文件路径 size=2,–初始大小 filegrowth=10%–数据增长方式)

 

–示例.建表测试use TestDBcreate table Test( ts1 int identity(1,1) primary key,–主键自增标识列 ts2 varchar(100) not null,–非空列 ts3 int check(ts3>=16 and ts3<=35),–CHECK约束限制列 ts4  varchar(10) default(‘LESEN丶J’),–默认值 ts5  int unique,–禁止重复 )

–外键create table Test1( ts1 int foreign key references Test(ts1) identity(1,1) primary key not null,–foreign key references Test(ts1)外键关系语法 ts2  varchar(10), ts3  varchar(10),)
–为表Test1的ts1添加主键–alter table Test1–add Constraint PK_Test1_TID primary key(ts1)

–为表Test1的ts1添加外键–alter table Test1–add Constraint FK_Test1_Test–foreign key (ts1) references Test(ts1)
–为表Test1的ts2添加CHECK约束–alter table Test1–add Constraint CK_Test1_Test CHECK–(ts2 in(‘a’,’b’,’c’,’d’))

 

–为表Test1的ts3添加默认值–alter table Test1–add Constraint DF_Test1_Test default(‘LESEN丶J’)for st3

 

–插入数据insert into Test(ts2,ts3,ts4,ts5) values(‘sdfds’,23,’等’,13)
insert into Test(ts2,ts3,ts5) values(‘sfs’,23,4654)

–插入多行数据insert Test(ts2,ts3,ts4,ts5)select ‘a1′,17,’c1’,1 unionselect ‘a2′,18,’c2’,2 unionselect ‘a3′,17,’c3’,3 unionselect ‘a4′,16,’c4’,4 unionselect ‘a5′,18,’c5’,5 unionselect ‘a6′,19,’c6’,6 unionselect ‘a7′,20,’ct’,7 unionselect ‘a8′,21,’cq’,8 unionselect ‘a6’,21,”,9

 

–删除所有记录delete from Test
–删除多条delete from Testwhere ts1 in(11,10)
–删除表记录truncate table Test1
–修改表内数据update Test set ts2=’a11′,ts4=null where ts1=21

–查询表内数据select * from Test where ts1=21
–别名查询select ts1 as 第一列,ts2 as 第二列,ts3 as 第三列, ts4 as 第四列,ts5 as 第五列 from Test

–查询前n行select top 10 * from Test
–查询前n%行select top 20 percent * from Test

–条件查询select * from Test where ts3>20
–排序select * from Test order by ts3 asc –升序select * from Test order by ts3 desc,ts2 asc –降序
–模糊查询select * from Test where ts2 like ‘%s%’
— ~~~~ where between ts3 19 an 21 语法取二者之间

 

–sum 求和 AVG平均值 MAX/MIN最大/最小值 count求总数select SUM(ts3) from Test

 

其他

http://www.w3school.com.cn/sql/