建立表
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为待计算的日期;
取值 | 别名 | 说明 | 说明 |
---|---|---|---|
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 | 当年度的第几周 |