原文: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语法,复杂的语法将下面的文章讲解...
时间: 2024-10-22 10:06:47