T-Sql(一)简单语法

原文:T-Sql(一)简单语法

  Sql Server是鄙人学习的第一种数据库,对Sql Server有一种特别的情感,下面就说一下Sql Server的简单语法,适用初学者。

  1,创建数据库create database

create database My_FrirstCreate           --创建数据库
go

use My_FrirstCreate           --连接数据库              
go

  2,创建表create table

create table dbo.Students          --创建表(数据类型,是否NULL)
  (StudentID int primary key not null,
  Name varchar(25)not null,
  Scores int null)
go

  3,插入数据insert

insert dbo.Students(StudentID,Name,Scores)    --插入数据    
   values(100204201,张三,50)
go

insert dbo.Students
   values(100204202,李四,null)
go

insert into table1              --利用insert,select向表里插数据      
select ID,Name,Date 
from table2
where Name="张三";
go

   4,使用select,into创建新表

select{列名}       --使用select,into创建新表
into 新表名
from 旧表;

  5,更新,删除数据update delete

update dbo.Students         --更新数据
   set Scores=70
   where StudentID=100204202
go
delete from Students
   where Name=张三

   6,改变字段的属性

alter table Produce.Product     --改变字段的属性
alter column Name char(50) not null

  7,数据类型转换

print cast (2011-12-12 as datetime)     --cast类型转换
print convert(datetime,getdate())         --convert类型转换

  8,like查询语法

--检索名称以‘hl’开头的信息
select t.ProductKey,t.ModelName
from dbo.DimProduct t
where t.ModelName like hl%;
--检索名称以‘hl’结尾的信息
select t.ProductKey,t.ModelName
from dbo.DimProduct t
where t.ModelName like %hl;
--检索名称类似‘hl’的信息
select t.ProductKey,t.ModelName
from dbo.DimProduct t
where t.ModelName like %hl%;

  9,条件查询语法

--每种颜色有多种件产品:
select COUNT(*) from dbo.DimProduct;
select * from dbo.DimProduct where Color = black;
select count(*) from dbo.DimProduct where Color = black;

--分组:
select color from dbo.DimProduct;
select color,COUNT(*) from dbo.DimProduct
group by Color;
--商品库中:相同颜色产品数量大于50的商品颜色
select color,COUNT(*) from dbo.DimProduct
group by Color
having count(*) >= 50;

select * from dbo.DimProduct
order by Color asc;

select color,COUNT(*) from dbo.DimProduct
group by Color
having count(*) >= 50
order by COUNT(*) asc;

select color,COUNT(*) from dbo.DimProduct
group by Color
having count(*) >= 50
order by COUNT(*) desc;

--商品库中:1998生产的,相同颜色产品数量大于5的商品颜色
select color,COUNT(*) from dbo.DimProduct
where YEAR(StartDate)=1998
group by Color
having count(*) >= 50
order by COUNT(*) desc;

select color,count(*) from dbo.DimProduct t
where YEAR(t.StartDate)>1998
group by color
having COUNT(*)>50
order by COUNT(*) desc;

   10,联接join语法

select m.LoginID as ManagerLoginID,e.*       --左联接
from HumanResources.Employee e
left join HumanResources.Employee m
on m.employeeID = e.ManagerID

select m.LoginID as ManagerLoginID,e.*       --右联接
from HumanResources.Employee e
right join HumanResources.Employee m
on m.employeeID = e.ManagerID

 

   本文只是简单的介绍下T-Sql语法,复杂的语法将下面的文章讲解...

T-Sql(一)简单语法,古老的榕树,5-wow.com

郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。