1.随机取样
Select top(n)* from table
Order by newid()
从table中随机选取n个记录。
2.新建表
create table tablename (
列名称1 数据类型,
列名称2 数据类型,
列名称3 数据类型)
3.给新表中插入值
Insert into 表名(列名1,列名2,列名3) values (值1,值2,值3)
4.更新表中数据
Update 表名 set 列名1=’’,列名2=‘’,列名3=‘’
5. SQl中的左连接,右连接,内连接,全连接,自连接的理解
create table a (aid varchar(2), anamevarchar(10), bid varchar(2) );
create table b( bid varchar(2), bname varchar(10));
insert into a values('s1','sname1','c1');
insert into a values('s2','sname2','c2');
insert into a values('s3','sname3','c3');
insert into a values('s4','sname4','c4');
insert into a values('s5','sname5','c5');
insert into b values('c1','cname1');
insert into b values('c2','cname2');
insert into b values('c3','cname3');
insert into b values('c6','cname6');
insert into b values('c7','cname7');
(1)自然连接、inner join 和join
select * from a ,b where a.bid=b.bid
select * from a join b on a.bid=b.bid
select cast(RAND()*100 as int) from a
(2)左连接和左外连接(left join,left outer join)
select * from a left join b on a.bid=b.bid
select * from a left outer join b ona.bid=b.bid
(3)右连接和右外连接(right join,right outer join)
select * from a right join b on a.bid=b.bid
select * from a right outer join b ona.bid=b.bid
(4)Full Join和Full Outer Join 全连接、全外连接,效果一样
select * from a full join b on a.bid=b.bid
select * from a full outer join b ona.bid=b.bid
6、row_number()
基本语法:ROW_NUMBER() OVER(PARTITION BY COLUMN ORDER BY COLUMN)
简单的说row_number()从1开始,为每一条分组记录返回一个数字。可按下面方式使用
ROW_NUMBER() OVER (ORDER BY colName DESC) 是先把colName列降序,再为降序以后的每条记录返回一个序号。
row_number() OVER (PARTITION BY COL1 ORDER BY COL2) 表示根据COL1分组,在分组内部根据COL2排序,而此函数计算的值就表示每组内部排序后的顺序编号(组内连续的唯一的)
7、indentity()返回行标
select no=Identity(int,1,1),* into temp
from thefristtable
select no from temp where 姓名 ='王彦宏'
返回姓名为王彦宏的字段所在行标。