sqlserver_2008_基础语句练习

sqlserver2008基础语句练习

以下语句都在sqlserver2008中调试过,是学习sqlserver的入门\基本语句.当然,如果你的目的仅仅是使用数据库,而不是维护\管理数据库,我想掌握这些基本也就够了.

附一常用日期函数及示例

刘一陈二张三李四王五赵六孙七周八吴九郑十

create database d1

--创建一个名为d1的数据库

use d1

create table score_info

(

scr_id varchar(2)primary key,

scr_nam varchar(30),

scr_value int,

stu_id varchar(20)

)

--创建一个名为d1的数据库score_info

alter table score_info

add constraint fk_student_info_stu_id

foreign key(stu_id)

references student_info(stu_id)

drop constraint fk_student_info_stu_id

--取消约束

create table tttt

(

val varchar(8)constraint test_check2

check(val like'00[0-9][0-9]-[a-z|A-Z][0-9][0-9]')

)

--创建一个符合正则表达式的约束,0012-a34符合

create table Persons

(

Id_P int NOT NULL,

LastName varchar(255)NOT NULL,

FirstName varchar(255),

Address varchar(255),

City varchar(255),

constraint chk_Person check(Id_P>0AND City='Sandnes')

)

--在创建新表时声明一个名称为chk_Person的约束,它对两个字段进行约束

create table Stu(

Sno char(8)primary key,

Sname char(8)NOT NULL,

Ssex char(2)check(Ssex in('男','女')),

Sage smallint,

Sdept char(20),

Sclass char(4)NOT NULL,

Stotal smallint default0

)

--创建一个表,两个约束:Ssex仅选男\女,Stotal默认为

alter table Persons

add constraint DF_address default('地址不详')for city --更改Person表的city列,使其默认值为'地址不详'

create index index_1

on Persons(lastname)

--对Person表的lastname列创建一个名称为index_1的索引

create index index_2

on Persons(lastname,firstname)

--创建一个包含lastname,firstname两个字段的索引index_2

drop index index_1on Persons

--删除Person上的索引index_1

truncate table tttt

--删除表tttt,而保留表的框架,速度比delete快,不能同where搭配

drop table tb001

--从数据库中删除表tb001

alter table Students

add Sbirthday date

--为表Students创建一个数据类型为date,名称为Sbirthday的列

alter table students

add constraint ccc default('1900-01-01')for Sbirthday --为Sbirthday列创建一个default约束ccc

alter table Students

drop Sbirthday

--试图删除一个带约束或带索引的列,将出现错误

消息3728，级别16，状态1，第1行

'Sbirthday'不是约束。

消息3727，级别16，状态0，第1行

未能删除约束。请参阅前面的错误信息。

alter table students

drop constraint ccc

--删除约束

alter table students

drop column Sbirthday

--删除Sbirthday列

alter table students

add Sbirthday date

--添加列

alter table Students

alter column Sbirthday varchar(30)

--更改列的数据类型

create table person1

(

id int primary key identity,

name varchar(20)

)

--创建表,id值自动增加

create table person2

(

id int primary key identity(18,10),

name varchar(20)

)

--创建一个表,id从18开始,以10为单位递增

create table stu01

(

id int primary key identity,

name varchar(30),

age int,

sex varchar(2)check(sex in('男','女')),

height float,

weight float

)

insert into stu01values('Liu Yiyi',20,'男',180.5,75.654)

insert into stu01values('Chen Erer',18,'女',175,70.666)

insert into stu01values('Zhang Sansan',22,'男',186,77.456)

insert into stu01values('Li Sisi',20,'女',160,50.335)

insert into stu01values('Wang Wuwu',21,'女',158.5,50.555)

insert into stu01values('Zhao Liuliu',20,'男',172.5,75.001)

create view stu01_less_70kg as

select*from stu01

where weight<=70

--创建一个体重小于kg的视图

create view stu01_male as

select*from stu01

where sex='男'

--创建一个sex为'男'的视图

use Northwind

select*from[Product Sales for1997]

where ProductSales>=(Select avg(ProductSales)from[Product Sales for1997]) --在视图中选取数据,其中product sales for1997是一个视图

create view[products sales below average1997]as

select*from[product sales for1997]

where productsales<(select avg(productsales)from[product sales for1997]) --创建一个视图,该命令在Northwind下执行

select*from[products sales below average1997] where productsales<2000

create view[price less than30]as

select productid,productname,unitprice

from products where unitprice<=30

--创建视图price less than30

alter view[price less than30]as

select productid,productname,unitprice,UnitsInStock

from products

where unitprice<30

--修改一个视图

create view view111as

select ProductID,ProductName,Categoryid

from Products

where Discontinued=0

--创建视图view111

drop view view111

--删除一个视图

use db01

select*into product_copy from Northwind..Products

--把一个数据库中的表copy到另一个数据库中

use Northwind

select*into db01..order_copy from orders

--拷贝order表到db01数据库中的order_copy(order_copy为新建表)中

alter table datatime_p1

alter column orderdate varchar(30)

null_practise01表

select name,price*(instock+onsale)

from null_practise01

--参与相加的两个列有一个为NULL,结果就为NULL

select name,price,instock,onsale,

price*(isnull(instock,0)+isnull(onsale,0))as amount from null_practise01

--NULL值按0计算,isnull函数判断是否为空,返回第一个或第二个参数

select count(*)from null_practise01

select count(onsale)from null_practise01

select count(*)from null_practise01

where onsale is null or instock is null

print@@version

--查询版本

select SUM(age)from stu01

select COUNT(*)from stu01

select AVG(age)from stu01

select AVG(height)from stu01where sex='男'

select AVG(height)from stu01where sex='女'

select top(1)*from null_practise01

select top1name,price,instock,onsale from null_practise01

where instock is not null and onsale is null

--选择instock不为空,且onsale为空的第一行(注意top的用法)

select last(instock)from null_practicr1????

select max(instock)from null_practise01

select*from null_practise01where instock=max(instock)

消息147，级别15，状态1，第1行

聚合不应出现在WHERE子句中，除非该聚合位于HAVING子句或选择列表所包含的子查询中，并且要对其进行聚合的列是外部引用。

select城市,count(城市)as小计from客户

where城市in('北京','上海','天津','深圳')group by城市

--统计4个城市的客户数量

select sum(instock)from null_practise01

select

sum(instock)as库存总和,

sum(onsale)as柜台总和,

stdevp(instock)as库存标准差,

stdev(onsale)as柜台标准差

from null_practise01

select*into tb001

from northwind..orders where customerid='alfki'

select shipvia,sum(freight)as totalweight from tb001 group by shipvia

select employeeid,sum(freight)from tb001group by employeeid

select*from tb001where freight=(select MAX(freight)from tb001) --选择Freight为最大值的行

select employeeid,shipvia,sum(freight)from tb001

group by employeeid,shipvia

create table orders

(

o_id int primary key identity,

o_date date,

o_price float,

customer varchar(20)

)

insert into orders values('2008/12/29',1000,'Bush')

insert into orders values('2008/11/23',1600,'Carter') insert into orders values('2008/10/05',700,'Bush')

insert into orders values('2008/08/21',300,'Bush')

insert into orders values('2008/08/06',200,'Adams')

insert into orders values('2008/07/24',100,'Carter')

insert into orders values('2008/09/22',1200,'Adams')

insert into orders values('2008/07/17',500,'George')

insert into orders values('2008/11/16',2200,'Carter')

select customer,SUM(o_price)from orders group by customer

select customer,SUM(o_price)from orders

group by customer having SUM(o_price)<=2000

select customer,sum(o_price)from orders

group by customer

having customer like'a%'

select customer,sum(o_price)from orders

group by customer

having(customer like'b%')and(sum(o_price)>=2000)

select customer,sum(o_price)from orders

where customer like'c%'or customer like'a%'

group by customer

having sum(o_price)>1000

select customer,sum(o_price)from orders

group by customer

having sum(o_price)>1000

and(customer like'c%'or customer like'a%')

students表

select UPPER(stu_name)from students

Select lower(stu_name)from students

select AVG(stu_age)from students

--age为int型数据

alter table students

alter column stu_age float

--更改stu_age数据类型为float型

select AVG(stu_age)from students

.

select LEFT(avg(stu_age),6)from students

从左边开始保留6位数字,小数点占一位,第6位是0也列出,舍去部分不四舍五入.如0.1010601=>0.1010. select right('123.1010601',8)

select SUBSTRING('123.456',2,3)

select SUBSTRING('123.456',4,5)

select SUBSTRING('123.4567890123',6,7)

select SUBSTRING('abcdefghijklmn',4,5)

select SUBSTRING('中华人民共和国万岁',3,5)

select ABS('-213')

=>:213.

select LEN('中华人民共和国')

=>7

select stu_name,LOWER(stu_name),LEN(stu_name)as name_length from students

USE db02

go

create table Student

(

Stu_ID char(12)not null primary key,---学号

Stu_Name varchar(50),---姓名

Ssex char(2),----性别

Sage int,----年龄

Sdept varchar(50)----系别

)

create table stu_Course

(

Stu_ID char(12)not null,

Course_ID char(9)not null,---课程编号

Term char(6),----选修学期

Score decimal(3,2),----成绩

Creidt int-----学分

constraint PK_Stu_Course primary key(Stu_ID,Course_ID)------主键

)

create table Course(Course_ID char(9)not null,

cname char(8),

primary key(Course_ID))

create table Teacher

(

Tea_ID char(9)not null primary key,--教师编号

Tea_Name nchar(50)not null,--姓名

Tea_Sex nchar(2)null check(Tea_Sex='男'and Tea_Sex='女')DEFAULT('男'),--性别Tea_Age int null check(Tea_Age>0and Tea_Age<99),--年龄

Tea_Course_ID CHAR(9)not null,--授课编号

--constraint PK_Tea_ID PRIMARY KEY clustered(Tea_ID),

constraint FK_Tea_course_ID FOREIGN KEY(Tea_Course_ID)references Course(Course_ID) --外键约束

)

select stu_weight,ROUND(stu_weight,3)from students

select cast(round(12.5,2)as numeric(5,2))

select cast(round('12.567',2)as numeric(5,2))

select cast(round(122.5255,2)as numeric(5,2))

=>122.53

select cast(round(1222.5255,2)as numeric(5,2))

消息8115，级别16，状态8，第1行

将numeric转换为数据类型numeric时出现算术溢出错误。

numeric函数的2个参数:第一个表示数据长度(不含小数点)，第二个参数表示小数点后位数。select cast(round(1222.5255,2)as numeric(6,2))

=>122.53

select cast(round(1222.5255,2)as numeric(20,2))

=>122.53

select YEAR(entrance_day)as En_Year from students

select month(entrance_day)as En_Month from students

select GETDATE()

select DateName(year,Getdate())

select DateName(MONTH,Getdate())

select DateName(day,Getdate())

select DateName(HOUR,Getdate())

select DateName(MINUTE,Getdate())

select DateName(SECOND,Getdate())

select DateName(WEEKDAY,Getdate())

附一常用日期函数及示例1、常用日期方法

(下面的GetDate()='2006-11-0813:37:56.233')

(1)DATENAME(datepart,date)

返回表示指定日期的指定日期部分的字符串。Datepart详见下面的列表. SELECT DateName(day,Getdate())–返回8

(2)DATEPART(datepart,date)

返回表示指定日期的指定日期部分的整数。

SELECT DATEPART(year,Getdate())–返回2006

(3)DATEADD(datepart,number,date)

返回给指定日期加上一个时间间隔后的新datetime值。

SELECT DATEADD(week,1,GetDate())--当前日期加一周后的日期(4)DATEDIFF(datepart,startdate,enddate)

返回跨两个指定日期的日期边界数和时间边界数。

SELECT DATEDIFF(month,'2006-10-11','2006-11-01')--返回1 (5)DAY(date)

返回一个整数，表示指定日期的天datepart部分。

SELECT day(GetDate())–返回8

(6)GETDATE()

以datetime值的SQL Server2005标准内部格式返回当前系统日期和时间。

SELECT GetDate()--返回2006-11-0813:37:56.233

(7)MONTH(date)

返回表示指定日期的“月”部分的整数。

SELECT MONTH(GETDATE())--返回11

(8)YEAR(date)

返回表示指定日期的“年”部分的整数。

SELECT YEAR(GETDATE())--返回2006

2、取特定日期

(1)获得当前日期是星期几

SELECT DateName(weekday,Getdate())--Wednesday

(2)计算哪一天是本周的星期一

SELECT DATEADD(week,DATEDIFF(week,'1900-01-01',getdate()),'1900-01-01')--返回2006-11-0600:00:00.000

或

SELECT DATEADD(week,DATEDIFF(week,0,getdate()),0)

(3)当前季度的第一天

SELECT DATEADD(quarter,DATEDIFF(quarter,0,getdate()),0)—返回2006-10-01

00:00:00.000

(4)如何取得某个月的天数

SELECT Day(dateadd(ms,-3,DATEADD(mm,DATEDIFF(m,0,'2006-02-03')+1,0)))—返回28

(5)一个季度多少天

declare@m tinyint,@time smalldatetime

select@m=month(getdate())

select@m=case when@m between1and3then1

when@m between4and6then4

when@m between7and9then7

else10end

select@time=datename(year,getdate())+'-'+convert(varchar(10),@m)+'-01' select datediff(day,@time,dateadd(mm,3,@time))—返回92

(6)获得年月日(yyyy-MM-dd)

SELECT CONVERT(VARCHAR(10),GETDATE(),120)–返回2006-11-08

3、其它

(1)--下面的示例将日期指定为数字。数据库引擎将0解释为1900年1月1日。

SELECT MONTH(0),DAY(0),YEAR(0)–返回111900

--下面两句是等效的

SELECT DATENAME(WEEKDAY,0)

SELECT DATENAME(WEEKDAY,'1900-01-01')

(2)SET DATEFIRST{number|@number_var}

将一周的第一天设置为从1到7的一个数字。

SET DATEFIRST1--表示一周的第一天是“星期一"

SELECT DATENAME(WEEKDAY,GETDATE())--Wednesday

SELECT DATEPART(weekday,GETDATE())--返回3

--查看当前设置情况

select@@DATEFIRST

(3)SET DATEFORMAT{format|@format_var}

设置用于输入datetime或smalldatetime数据的日期部分（月/日/年）的顺序。

...有效参数包括mdy、dmy、ymd、ydm、myd和dym。

...该设置仅用在将字符串转换为日期值时的解释中。它不影响日期值的显示。

...SET DATEFORMAT的设置是在执行或运行时设置，而不是在分析时设置。...SET DATEFORMAT将覆盖SET LANGUAGE的隐式日期格式设置。

下面是例子:

--Set date format to year,day,month.

SET DATEFORMAT ydm;

GO

DECLARE@datevar DATETIME;

SET@datevar='1998/31/12';

SELECT@datevar AS DateVar;

GO

--Set date format to year,month,day.

SET DATEFORMAT ymd;

GO

DECLARE@datevar DATETIME;

SET@datevar='1998/12/31';

SELECT@datevar AS DateVar;

GO

(4)日期部分的列表

日期部分缩写

year yy,yyyy

quarter qq,q

month mm,m

dayofyear dy,y

day dd,d

week wk,ww

weekday dw

hour hh

minute mi,n

second ss,s

ms

milliseco

nd

数据类型范围精确度

datetime

3.33毫秒

1753年1月1日到9999年12月31

日

smalldatet

ime1900年1月1日到2079年6月6日