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