SQL语句备忘

 

 建立表

create table T_Person1(Id int not null,Name nvarchar(50),Age int null);

 

插入数据

insert into T_Person1(id,name,age) values (1,'Jim',20);

 

删除表 

drop table T_Person1;

 

更新数据

update T_Person1 set age = 20;                            //更新所有的age为 

update T_Person1 set age = 20,name = 'ivan';              //更新所有的age,name为,ivan 

update T_Person1 set nickname = N'青年人' where age>=20;   //age>=20 的nickname为青年人 

where age>=20 or age=30;             //or  或者 

where age>=20 and age=30;            //and 并且

 

删除数据

truncate table table_name          //清空SQL数据库让序列ID重新开始排列

delete from T_Person1;                           //删除表格中所有的数据,表格还在 

delete from T_Person1 where age>=30;             //删除age>=30的数据

 

查询数据 

select * from T_Person1;                          //查询字段的所有数据 

select name,age from T_Person1;                   //查询name,age字段的所有数据

select name as 姓名,age as 年龄from T_Person1;     //同上,起别名name=姓名 

select count(*) from T_Person1;                   //查询表中有多少条数据 

select count(*) from T_Person1 where age>1;       //查询年龄大于有多少条数据

select max(age) from T_Person1;                   //查询表中age的最大值 

select min(age) from T_Person1;                   //查询表中age的最小值

select avg(age) from T_Person1;                   //查询表中age的平均值 

select sum(age) from T_Person1;                   //查询表中age的和

 

数据排序

select * from T_Person1 order by age ASC;            //不填写默认为升序排列小到大 

select * from T_Person1 order by age DESC;           //降序排列从大到小 

select * from T_Person1 order by age DESC,name ASC;  //先age降序,再name升序 

 

select * from T_Person1                              //where必须放在order by前

where age >20  

order by age ASC,name desc

 

模糊查询

select * from T_Person1 where name like '_van'    // _下划线代表一个任意的字符 

select * from T_Person1 where name like 'i%';     // %百分号代表多个任意字符 

select * from T_Person1 where age in(20,21,30); 

select * from T_Person1 where age >20 and age <30; 

select * from T_Person1 where age between 20 and 30; 

select * from T_Person1 where nickname is null;

 

 

数据分组

select age,count(*) from T_Person1        //以age分组,显示各个年龄的个数

group by age;

 

 select age,count(*) from T_Person1        //where要放在group by前

where name like '%i%'

group by age

 

select age,count(*) from T_Person1        //having是对分组后的信息过滤,having要放在group by后

group by age

having count(*) > 1

 

select top 3 * from T_Person1            //取字段age中最大的前位

order by age desc

 

select top 3 * from T_Person1            //查询排除前后,从第开始的个age

where age not in (select top 3 age from T_Person1 order by age desc)

order by age desc

 

select distinct age from T_Person1        //消除重复的age

 

select Fname from T_Person1        //将T_Person1和T_Person2的结果合并输出

union

select Fname from T_Person2

 

数字函数

ABS()求绝对值

CEILING()舍入到最大整数,3.33舍为4,      2.89舍为3,    -3.61舍为-3;

FLOOR()舍入到最小值,3.33舍为3,      2.89舍为2,    -3.61舍为-4;

ROUND()四舍五入;

 

字符串函数

LEN()长度;

LOWER()、UPPER()：转大写、小写;

LTRIM()去掉左边空格;

RTRIM()去掉右边空格;

 

日期函数

GetDate()取得当前日期时间;

DateAdd(datepart,number,date),datepart为单位,number为增量,date为待计算的日期;

DateDiff(datepart,startdate,enddate),datepart为单位,startdate为开始,enddate为结束日期;

DatePart(datepart,date),datepart为单位,date为待计算的日期;

 

datepart可选值

取值	别名	说明	说明
year	yy,yyyy	年	SELECT DATEADD(year,1,GETDATE())
month	mm,m	月	SELECT DATEADD(month,1,GETDATE())
day	dd,d	日	SELECT DATEADD(day,1,GETDATE())加一天
hour	hh	时	SELECT DATEADD(hour,1,GETDATE())
minute	mi,n	分	SELECT DATEADD(minute,1,GETDATE())
second	ss,s	秒	SELECT DATEADD(second,60,GETDATE())
millisecond	ms	毫秒	SELECT DATEADD(millisecond,1000,GETDATE())
weekday	dw,w	星期几	SELECT DATEADD(weekday,1,GETDATE())加一天
quarter	qq,q	季度	SELECT DATEADD(quarter,1,GETDATE())加一季为3个月
dayofyear	dy,y	当年度的第几天
week	wk,ww	当年度的第几周