sql的一些基本操作

create table user_info_No1
(
 usrName varchar(20),
 area varchar(20),
 phone varchar(20)
)

select * from user_info_No1

--加步长
alter table user_info_No1 add userId int identity(1000,1)

--删除一列
alter table user_info_No1 drop column userId

--更改列的属性
alter table user_info_No1 alter column userName varchar(20)

alter table user_info_No1 drop column usrName

--添加一列
alter table user_info_No1 add userName varchar(20)

insert into user_info_No1(userName,area,phone) values('张三','深圳','15899885858')

--多条数据插入
insert into user_info_No1(userName,area,phone)
select '李四','佛山','13988877321'
union all
select '王五','广州','13987775256'
union all
select '小周','深圳','13893773528'
union all
select '小赵','深圳','13889828987'
union all
select '赵钱','佛山','13599663322'
union all
select '孙理','广州','13255448877'
union all
select '小吴','佛山','13788996655'

select  * from user_info_No1

select count(area) from user_info_No1 group by area

--查某一地区的信息条数
select count(*) 深圳 from user_info_No1 where area ='深圳' 

--查各个地区的信息条数 1
select b.area,count(a.area) coun from user_info_No1 a,(select distinct area from  user_info_No1) b
where a.area=b.area group by b.area

--查各个地区的信息条数 2
select area,count(area) coun from user_info_No1 group by area

--查询重复的单

select orderCode,count(orderCode) from tong  group by orderCode having count(orderCode)>=2