SQL_Server数据库基本操作

创建table

use TestDB
create table PersonInfos
(
birthday date null,
id int not null primary key identity(1,1),
cnname nvarchar(20) null,
engname varchar(20) null,
salary decimal(18,2) null default(0.00)
)

create table ProvinceInfos
(
     proId int primary key identity(1,1),
	 ProName nvarchar(20) unique not null
)

在sql语句中是不区分大小写的。

添加约束

• 主键约束（primary key）:该列中的数据可以唯一标识一条记录，主键字段不能为空，必须唯一，一张表只能够有一个主键。
• 唯一约束（unique）：这个字段中的数据必须是唯一存在的。
• 非空约束：设计表的时候，字段后面写的null与not null
• 检测约束（check）：限定某个字段的表达式，例： age>0 and age <100
• 默认约束（default）：如果不给取值就使用默认值否则使用提供的值
• 外键约束（foreign key）：限定表格中某一列的取值必须是另外一个表中主键的值，确定表与表之间的联系方式。

use TestDB
create table PersonInfos
(
birthday date null check(birthday>'1000-01-01' and birthday<'2999-01-01'),
id int not null primary key  identity(1,1),--主键约束，并且标识种子增量为1（自动增加）
cnname nvarchar(20) null,
engname varchar(20) null,
salary decimal(18,2) not null default(0.00)
)

alter table Players
add foreign key(TeamId) references Teams(Id)

修改table

删除列

alter table PersonInfos drop column cnname,column engname

可以一次性删除多列，但是如果列中有约束，不能这样直接删除，需要先删除约束。

增加列

alter table PersonInfos add cnname nvarchar(20) null,engname varchar(20) null,salary decimal(18,2) default(0.00)

可以一次性增加多列。

alter table PersonInfos add proId int foreign key references ProvinceInfos(proId)

增加外键列。

增加

说明：主键列是自动增长，在增加插入的时候可以忽略不计。字符串需要使用单引号。

insert into PersonInfos 
values('1998-9-10','巴拉巴拉','balabala...',10000.99)

如果插入的时候允许为null，也可以插入null。还可以一次性插入多行数据，括号之间使用逗号隔开。

insert into PersonInfos 
values('1998-9-10','巴拉巴拉','balabala...',10000.99),('9999-9-10','巴拉巴拉啦啦啦','balabala...',null)

修改

update PersonInfos 
set cnname='张含韵',salary=0 
where id=5

上句中修改了id=5的两列信息，中间用逗号隔开。where后面的条件语句也可以更复杂，在后的查询中再细讲。

删除

delete from PersonInfos 
where id>=4

查询

查询所有列

select * from PersonInfos

使用*代表所有列。

查询指定列

select birthday,cnname from PersonInfos

有多列的时候，中间用逗号隔开。

别名查询（as）

select cnname as 中文名 from PersonInfos

去重查询（distinct）

select distinct proid from PersonInfos

条件查询（where）

条件语句where后面支持多种运算符

比较运算符

支持 =、>、<、<=、>=和!=

select * from PersonInfos 
where id>1

select * from PersonInfos 
where cnname='张含韵'

逻辑运算符

支持and、or和not

select * from PersonInfos 
where id>2 and birthday>'2000-01-01'

模糊查询

like，%表示任意多个任意字符，_表示一个任意字符

select * from PersonInfos 
where cnname like '张%'

select * from PersonInfos 
where cnname like '张__'

范围查询

in表示再一个非连续的范围内（是这个非连续中的某一个值）,not in也是支持的。

select * from PersonInfos 
where id in(2,4,6)

select * from PersonInfos 
where id not in(1,2,6)

between…and…表示在一个连续的范围内

select * from PersonInfos 
where birthday between '1995-01-01' and '2000-12-30'

空判断

select * from PersonInfos 
where salary is null

select * from PersonInfos 
where salary is not null

排序（order by）

asc：升序（ascend）

desc：降序（descend）

select * from PersonInfos 
where salary is not null 
order by salary desc,birthday asc

Top

select top 3 * from PersonInfos 
where salary is not null 
order by salary desc

薪水前3。

聚合函数

总数

count(*)表示计算总行数，括号中写星与列名结果是相同的

select count(*) from PersonInfos  
where salary is not null

最大数和最小值

max(列) 表示求此列的最大值

select MAX(salary) 
from PersonInfos  
where salary is not null

min(列) 表示求此列的最小值

select min(salary) 
from PersonInfos  
where salary is not null

求和与平均值

avg(列) 表示求此列的平均值

select avg(salary) 
from PersonInfos  
where salary is not null

sum(列) 表示求此列的和

select sum(salary) 
from PersonInfos  
where salary is not null

分组查询（group by）

select salary,count(*) as 数量 from PersonInfos
group by salary

按相同的值进行分组，统计每一组的数量。

过滤查询（having）

select salary,count(*) as 数量 from PersonInfos
group by salary
having salary>1000.00

多表查询

内连接

select * from PersonInfos,ProvinceInfos
where PersonInfos.proId=ProvinceInfos.proId

两张表通过外键关联在一起了，如果希望查询的时候两张表链接在一起，可以查询多个表并加上where条件。上述写法是内连接的一种隐式写法，只匹配两张表有关联的数据，没有匹配上的不显示。

也可以这么写：

select * from PersonInfos
inner join ProvinceInfos
on ProvinceInfos.proId=PersonInfos.proId

外连接

外连接与内连接的区别就是外连接会显示没有匹配到的数据，显示为null。外连接分为左连接和右连接。其实如果两个表之间建立了外键关系，是不会出现匹配不上的情况，因为匹配不上的数据会报错，所以多用在两个表没有建立外键的情况。

select * from PersonInfos
left join ProvinceInfos --左连接，以第一张表为准，第二张表进行匹配，没有匹配上的数据为null
on ProvinceInfos.proId=PersonInfos.proId

select * from PersonInfos
right join ProvinceInfos--右连接，以第二张表为准，第一张表进行匹配，没有匹配上的数据为null
on ProvinceInfos.proId=PersonInfos.proId

子查询

例如：需要查找工资大于13000.00的人的所在的省，这里其实有两个操作

1、先找到工资大于13000.00的员工（在员工信息表中）

2、查找上述员工的省份（在省份信息表中）

但是我们也可以使用一句sql完成。

select ProName from ProvinceInfos
where proId in (select proId from PersonInfos where salary>13000.00)

多表查询和子查询都是在多个表关联时的查询情况，根据需求可以变通。上例中也可以使用多表查询：

select PersonInfos.birthday,PersonInfos.cnname,PersonInfos.salary,ProvinceInfos.ProName from PersonInfos,ProvinceInfos
where PersonInfos.salary>13000.00 and PersonInfos.proId=ProvinceInfos.proId

存储过程

一种为了完成特定功能的一个或一组SQL语句集合。经编译后存储在服务器端的数据库中，可以利用存储过程来加速SQL语句的执行，调用名称，传入参数，执行来完成特定功能。

自定义存储过程：用户自己创建，特定功能而创建。可以传入参数，也可以有返回值，表明存储过程执行是否成功（也可以返回一个数据结果吧）。里面可以只是一个操作，也可以包括多个。

执行：execute 存储过程名 参数列表（多个参数以逗号隔开）。

优点：

• 提高了应用程序的通用性和可移植性。多次调用，而不必重新再去编写，维护人员可以随时修改。
• 可以更有效的管理数据库权限
• 提高执行SQL的速度
• 减轻服务器的负担

缺点：

• 专门维护它
• 占用数据库空间。

创建一个简单的存储过程，并且执行这个存储过程

create proc AddOnePerson
@birthday date,
@cnname nvarchar(20),
@engname varchar(20),
@proId int   --传入参数，在执行存储过程的时候都需要传入
as
begin
declare @salary decimal(18,2)--声明局部变量
set @salary=30000.99 --给局部变量赋值
insert into PersonInfos
values(@birthday,@cnname,@engname,@salary,@proId)
end

execute AddOnePerson '2008-02-15','朱道宽','zhudaokuan',1
select * from PersonInfos

带输出参数的存储过程：

create proc GetProvinceName
@id int, --默认为输入参数
@ProName nvarchar(20) output --输出参数
as
begin
  select @ProName=ProName from ProvinceInfos where proId=(select proId from PersonInfos where id=@id )
end

declare @pn nvarchar(20)
execute GetProvinceName 2,@pn output
print @pn