SQL语言

1、结构化查询语言SQL（Structed Query Language）建立在关系运算理论基础上，介于关系代数与关系演算之间，是一种通用的、功能强大的关系数据库语言。

2、SQL语言提供两种主要工作方式：1）交互式命令方式 2）嵌入式程序方式

3、内模式—模式—外模式

4、表、主外键关系、候选键、约束、索引、视图、游标

5、查询、子查询、连接查询（内联接、外联接、交叉联接）

6、聚合函数、count、sum、max、min、avg

7、sql语句，CRUD（create、retrieve、update、delete）

8、T-SQL

declare @min tinyint
set @min=25
select cno,cname,csex,cage,crank from customers where cage between @min and 31

declare @min tinyint,@max tinyint
set @min=25
set @max=31
select cno,cname,csex,cage,crank from customers where cage between @min and @max

declare @male_num tinyint,@female_num tinyint
set @male_num=(select count(*) from customers where csex='男')
set @female_num=(select count(*) from customers where csex='女')
if(@male_num>@female_num)
  select cno,cname,csex,cage from customers where csex='男'
else
  select cno,cname,csex,cage from customers where csex='女'

update customers set crank=
  case
      when crank='普通会员' then '注册会员'
      when crank='注册会员' then '银卡会员'
      when crank='银卡会员' then '金卡会员'
      when crank='金卡会员' then 'VIP会员'
      when crank='VIP会员' then 'VIP会员'

while((select min(price) from products where price<=30)>10)
begin
  update products set price=price*0.9 where price<=30
end
select pno,pname,price from products where price<=30

waitfor delay '00:02:00'  --等待2分钟执行
select * from customers

declare @N int 
select @N=1
sect_1:
  print @N*@N
  select @N=@N+1
  while @N<7
     goto sect_1

9、函数

select avg(price) as avg_price,count(price) as count_price,sum(price) as sum_price,max(price) as max_price,min(price) as min_price from products

selecct var(price) from products --统计方差

select month(0),day(0),year(0)
select year('03/12/1998')
select month('03/12/1998')
select day('03/12/1998')

select getdate()
select getutcdate()
select datepart(year,getdate())

select round(150.75,0) --151.00

select ceiling($123.45),ceiling($-123.45)  --124.0000 -123.0000

select floor(123.45),floor(-123.45),floor($123.45) --123 -124  123.0000

declare @seed smallint
set @seed=1
while @seed<3
begin
  print rand(@seed)
  set @seed=@seed+1
end