SQL

数据库的创建 create database

修改数据库名字

sp_renamedb School, hisschool

sp_renamedb hisschool,School

删除数据库

Drop Database School。

删除表中数据 delete

 

向数据表中追加字段

通用式： alter table [表名] add [字段名] 字段属性 default 缺省值 default 是可选参数

列： alter table Goods add Type varchar(20)

--把名字叫bill的所有老师的名字修改 update
update teacher set tName = '张强' where tName = 'Bill'

 //自引用实列：

create table Category
(
 CategoryId int primary key identity(1,1),   <primary key>主键约束：唯一且不能为空，<identity>自增长。
 CategoryName varchar(20) not null,
 CatId_CategoryId int foreign key references Category(CategoryId)    所属Id
)
go

create database SQLschool //创建名为SQLschool的数据库
go

use SQLschool //定位到该数据库
go

--列级约束
create table student
(
	stuId char(8) primary key, --主键约束:唯一，且不能为空
	stuName varchar(10) not null, --非空约束
	stuSex char(2) check(stuSex = '男' or stuSex = '女'),--检查约束
	stuBirth smallDatetime,
	stuSpeciality varchar(50) default '计算机软件与理念',--默认值约束
	stuAvgrade numeric(3,1) check(stuAvgrade >= 0 and stuAvgrade <= 100),
	stuDept varchar(50) default '计算机科学系'
)
go

 查询

--给列取别名
select stuAvgrade as 平均成绩, stuName as '姓名' from student
select stuAvgrade  平均成绩, stuName  '姓名' from student
--查询全体学生的姓名和年龄  select getdate()-当前日期
select stuName, year(getdate()) - year(stuBirth) as 年龄 from student

 --下面这些都使用了聚合函数

select sum(stuAvgrade) as 总成绩 from student

select avg(stuAvgrade) as 平均成绩 from student

select max(stuAvgrade) as 最高成绩 from student

select min(stuAvgrade) as 最小成绩 from student

 

--使用Between查询所有出生在84年8月1日到86年12月25日之间的学生信息

select * from student where stuBirth between '1984-08-01' And '1986-12-25'

排序 查询所有学生按平均成绩排序

select * from student order by stuAvgrade Desc --desc是降序，默认值是Asc

模糊查询

使用like进行模糊查询

--查询所有姓王的同学的信息

select * from student where stuName like '王%'

  --'%'号与任意个字符相匹配其实就是0到n个

--查询所有赵姓同学的信息并且其名字是两个字

--'_'号与一个字符相匹配

select * from student where stuName like '赵_'   

--查询第二字为珍的同学的信息

select * from student where stuName like'_志%'

 存储过程

 

CREATE PROCEDURE GetProductsOnCatalogPromotion
(---需要传入的参数
@DescriptionLength INT,--描述信息长度
@PageNumber INT, -- 第几页
@ProductsPerPage INT, --每页显示几个商品
@HowManyProducts INT OUTPUT -- 一共有多少商品
)
AS

--声明一个表变量
DECLARE @Products TABLE --表变量
(RowNumber INT, --在products原始表上增加一个可靠的编号字段
 ProductID INT,
 Name VARCHAR(50),
 Description VARCHAR(5000),
 Price MONEY,
 Image1FileName VARCHAR(50),
 Image2FileName VARCHAR(50),
 OnDepartmentPromotion BIT,
 OnCatalogPromotion BIT)

-- 给表变量的每个字段赋值
INSERT INTO @Products  
SELECT ROW_NUMBER() OVER (ORDER BY Product.ProductID),
       ProductID, Name, 
       SUBSTRING(Description, 1, @DescriptionLength) + '...' AS Description,
       Price, Image1FileName, Image2FileName, OnDepartmentPromotion, OnCatalogPromotion
FROM Product
WHERE OnCatalogPromotion = 1

-- 给输出参数@HowManyProducts赋值
SELECT @HowManyProducts = COUNT(ProductID) FROM @Products

-- extract the requested page of products
-- 把请求的第几页的内容从@Products表变量中查询出来
SELECT ProductID, Name, Description, Price, Image1FileName,
       Image2FileName, OnDepartmentPromotion, OnCatalogPromotion
FROM @Products
WHERE RowNumber > (@PageNumber - 1) * @ProductsPerPage
  AND RowNumber <= @PageNumber * @ProductsPerPage
GO

declare @HowManyProducts int
exec GetProductsOnCatalogPromotion 15,4,2, @HowManyProducts out 
select @HowManyProducts

--一共多少页：总商品数/每页的产品数 (小数) = 2.1
-- ceiling 

 

create proc GetProductsOfBig
(
@Categroyid int,  --商品类别Id
@PageNumber int,  --第几页
@ProductsPerPage int, --每页显示几个商品
@HowManyProducts int output  --一共多少商品
)
as
declare @Goods table
(
RowNumber int, --在原始表上增加一个可靠的编号字段
GoodsId int,
GoodsName varchar(100),
SkuPrice varchar(20), --商品价格
PictureName varchar(50)--图片
)
insert into @Goods
select ROW_NUMBER() over (order by Goods.GoodsId),
Goods.GoodsId,
Goods.GoodsName,
SkuPrice,
pictureName
from Goods,Skus,Pictures
where (Goods.CategoryId=@Categroyid
and Pictures.GoodsId=Goods.GoodsId
and pictureShow=1
and Skus.GoodsId=Goods.GoodsId)

select @HowManyProducts=COUNT(GoodsId) from @Goods

select * from @Goods 
where RowNumber > (@PageNumber - 1) * @ProductsPerPage
  AND RowNumber <= @PageNumber * @ProductsPerPage
GO