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