SQL-SERVER學習（一） 數據表的基本操作

1、創建一個資料庫，資料庫的名字是UserInfo

　　create database UserInfo;

2、創建一個數據表，數據表的名字是UserInfo
create table UserInfo
(

UserId int primary key identity(1, 1),
UserName varchar(50),
Pwd varchar(20),
Age int,
Gender char(5)

)

3、向表格插入數據
insert into UserInfo(UserName, Pwd, Age, Gender)
values('張三', 'zhangsan123', 20, '男')

insert into UserInfo(UserName, Pwd, Age, Gender)
values('李四', 'lisi123', 42, '女')

insert into UserInfo(UserName, Pwd, Age, Gender)
values('王五', 'wangwu123', 50, '女')

insert into UserInfo(UserName, Pwd, Age, Gender)
values('劉德華', 'lisi123', 42, '男')

insert into UserInfo(UserName, Pwd, Age, Gender)
values('張學友', 'wangwu123', 50, '男')

insert into UserInfo(UserName, Pwd, Age, Gender)
values('劉亦菲', 'lisi123', 25, '女')

insert into UserInfo(UserName, Pwd, Age, Gender)
values('楊冪', 'wangwu123', 33, '女')

insert into UserInfo(UserName, Pwd, Age, Gender)
values('劉詩詩', 'wangwu123', 18, '女')

insert into UserInfo(UserName, Pwd, Age, Gender)
values('鄧紫棋', 'wangwu123', 16, '女')

4、查詢
select (
select * ,age as 'number' from UserInfo ) t
where t.Age>50 and t.number=1

select * from UserInfo where Gender = '女' and Age < 20 and UserName like '鄧%'

select * from UserInfo

5、更改屬性值
update UserInfo set Age = 19 where UserName = '李四'

update UserInfo set Age = 20 where UserName = '李四' or UserName = '王五'

select * from UserInfo where (UserName like '劉%') and (Gender = '女') and (UserName not like '%詩')

select * from UserInfo

6、刪除數據
delete from UserInfo where UserId = 10 or UserId = 11