数据库代码

on primary --指定一个首要数据文件

(name=ccc1_data,--逻辑名字，便于管理文件

filename='d:\\SQL练习\\ccc1.mdf',--物理名字，真正的放在磁盘中的文件 size=3,--最小值

maxsize=200,--最大值 filegrowth=1)--增长值

log on--创建上个数据库的日志文件 (name=ccc1_log,--逻辑名字

filename='d:\\SQL练习\\ccc2.ldf',--物理名字 size=3,--最小值

maxsize=200,--最大值 filegrowth=1)--增长值

exec sp_helpdb ccc --查看数据库 drop database ccc --删除数据库

create database sample on primary

(name=sample_data,

filename='d:\\SQL练习\\sample.mdf', size=3,

filegrowth=1), filegroup data (name=sample1,

filename='d:\\SQL练习\\sample1.ndf', size=3,

maxsize=200, filegrowth=1) log on

(name=sample_log,

filename='d:\\SQL练习\\sample.ldf', size=1,

filegrowth=10%)

create database stuabc--创建默认数据库 drop database stuabc --删除数据库

create database student1--创建数据库student1 on primary--指定一个首要数据文件 (name=student1_data,--逻辑名字

filename='d:\\SQL练习\\student1.mdf',--物理名字 size=10,--最小值

filegrowth=10%)--最大值

log on--创建上个数据库的日志文件 (name=student_log,--逻辑名字

filename='d:\\SQL练习\\student1.ldf',--物理名字 size=1,--最小值

maxsize=5,--最大值 filegrowth=1)--增长值

create database students--创建数据库students on primary--指定一个首要数据文件 (name=student1,--逻辑名字

filename='d:\\SQL练习\\student3.mdf',--物理名字 size=10,--最小值

filegrowth=10%),--增长值 (name=student2,--逻辑名字

filename='d:\\SQL练习\\student2.ndf',--物理名字 size=20,--最小值

maxsize=100,--最大值 filegrowth=1)--增长值

log on --创建上个数据库的日志文件 (name=studentlog1,--逻辑名字

filename='d:\\SQL练习\\studentlog1.ldf',--物理名字 size=10,--最小值 maxsize=50,--最大值 filegrowth=1),--增长值

(name=studentlog2,--逻辑名字

filename='d:\\SQL练习\\studentlog2.ldf',--物理名字 size=10,--最小值 maxsize=50,--增长值 filegrowth=1)--增长值

----修改数据库

alter database student1--修改数据库student1 add filegroup data1--增加文件组data1

alter database student1--修改数据库student1 add file--增加两个次要数据文件 (name=stu_data1,

filename='d:\\SQL练习\\stu1.ndf', size=3,

maxsize=200, filegrowth=1), (name=stu_data2,

filename='d:\\SQL练习\\stu2.ndf', size=3,

filegrowth=10%)

to filegroup data1--增加到文件组data1

exec sp_helpdb student1--查看数据库student1 alter database student1--修改数据库student1 add file--增加一个日志文件 (name=stu_log,

filename='d:\\SQL练习\\stu1.ldf' )

alter database student1--修改数据库student1 remove file stu_data2--删除数据文件

alter database student1--修改数据库student1 remove file stu_data1--删除数据文件

alter database student1--修改数据库student1 remove filegroup data1--删除文件组data1

alter database student1--修改数据库student1中某一文件的属性 modify file (name=stu_log, size=5)

alter database student1--修改数据库student1 remove file stu_log--删除日志文件 exec sp_helpdb student1

----移动数据库

exec sp_detach_db student1,true--分离数据库

exec sp_attach_db @dbname='student1',--连接数据库 @filename1='e:\\student1.mdf', @filename2='e:\\student1.ldf'

exec sp_detach_db student1,true--分离数据库

exec sp_attach_db @dbname='student1',--连接数据库 @filename1='d:\\SQL练习\\student1.mdf', @filename2='d:\\SQL练习\\student1.ldf' exec sp_helpdb student1--查看数据库

----重命名数据库

exec sp_renamedb 'student1','fff' exec sp_renamedb 'fff','student1' drop database student1 create database sample

-----数据库课堂作业 -----第一题

create database sample--创建数据库sample drop database sample--删除数据库sample -----第二题

create database sample--创建数据库sample on primary--指定一个首要数据文件 (name=sample_dat,--逻辑名字

filename='d:\\数据库示例\\sample示例\\sample.mdf',--物理名字 size=3,--最小值

maxsize=10,--最大值

filegrowth=1)--增长值

log on--创建上个数据库的日志文件 (name=sample_log,--逻辑名字

filename='d:\\数据库示例\\sample示例\\sample.ldf',--物理名字 size=3,--最小值

maxsize=10,--最大值 filegrowth=1)--增长值

drop database sample--删除数据库sample ----第三题

create database sales on primary

(name=Spri1_dat,

filename='d:\\数据库示例\\sample示例\\spri1_dat.mdf', filegrowth=15%), (name=spri2_dat,

filename='d:\\数据库示例\\sample示例\\spri2_dat.ndf', filegrowth=15%),

filegroup salesgroup1--创建文件组salesgroup1 (name=sgrp1fi1,

filename='d:\\数据库示例\\sample示例\\sgrp1fi1.ndf'), (name=sgrp1fi2,

filename='d:\\数据库示例\\sample示例\\sgrp1fi2.ndf'), filegroup salesgroup2--创建文件组salesgroup2 (name=sgrp2fi1,

filename='d:\\数据库示例\\sample示例\\sgrp2fi1.ndf'), (name=sgrp2fi2,

filename='d:\\数据库示例\\sample示例\\sgrp2fi2.ndf') log on

(name=spri_log,

filename='d:\\数据库示例\\sample示例\\spri.ldf', size=5,

maxsize=20, filegrowth=2)

drop database sales--删除数据库sales -----第四题

create database 产品数据库 on primary

(name=产品数据库,

filename='d:\\数据库示例\\sample示例\\产品数据库.mdf') log on

(name=产品数据库_log,

filename='d:\\数据库示例\\sample示例\\产品数据库_log.ldf') drop database 产品数据库--删除数据库产品数据库 ----第五题

create database TestDB on primary

(name=TestDB_data,

filename='d:\\数据库示例\\sample示例\\TestDB_data.mdf', size=5,

maxsize=15, filegrowth=1) log on

(name=TestDB_log,

filename='d:\\数据库示例\\sample示例\\TestDB_log.ldf', size=5,

maxsize=10, filegrowth=1)

exec sp_renamedb 'TestDB','测试数据库'--重命名数据库

alter database 测试数据库--修改数据库测试数据库中某一文件的属性 modify file

(name=TestDB_log, maxsize=15, filegrowth=2)

drop database 测试数据库

create database sample on primary

(name=sample_data,

filename='d:\\SQL练习\\sample.mdf') log on

(name=sample_log,

filename='d:\\SQL练习\\sample.ldf') drop database sample

----------------------------------------------------------------------- -----------------------建表---------------------------------------------

use sample ----使用数据库sample go

create table 整型数据----创建整型数据表 (bitnum bit,-----bitnum 列名

tinyintnum tinyint,----tinyintnum 列名

smallintnum smallint,----smallintnum 列名 intnum int,----intnum 列名

bigintnum bigint)-----bigintnum 列名 insert into 整型数据----向表中插入数据

values(0,12,344,565,677888888)------向表中插入某些值 select * from 整型数据-----查看表 insert into 整型数据

values(1,33,666,666,9876656565)

create table 字符数据----创建字符数据表 (charnum char(5),-----char(5)-开辟空间为 varcharnum varchar(5), textnum text)

insert into 字符数据

values('a','a','this is a text type') select * from 字符数据

create table 实型数据----创建实型数据表 (realnum real, floatnum float,

numericnum numeric(5,2),

decimalnum decimal(6,3)) insert into 实型数据

values(1111111.11,111111111111111.11111,123,123.45) select * from 实型数据

create table 货币数据----创建货币数据表 (smallmoneynum smallmoney, moneynum money)

insert into 货币数据 values($123,$12345) select * from 货币数据

create table 时间数据----创建时间数据表 (datetimenum datetime,

smalldatetimenum smalldatetime) insert into 时间数据

values('12/3/2007 10:26:30.33','3/12/2007 10:26:40') select * from 时间数据

----------------------------------------------------------------------- ---------------建表的约束------------------------------------------------

create database student 学号姓名 性别 年龄 籍贯

课程号课程名 任课教师

学号课程号成绩 use student go

create table stu_info------------------------------------学生信息表

(s_number varchar(10) primary key,------学号-----primary key主键---主键约束 sex char(3) default '男',----------------性别-----default默认值-----默认约束 age int,-------------------------------------------------年龄

s_name varchar(30) not null,----------姓名----not null--不允许为空---空约束 addr varchar(50),----------------------------------------地址

constraint ch_sex check(sex='男'or sex='女'),-----------constraint--约束---check --检查约束-性别

constraint ch_age check(age between 0 and 150))------------检查约束-年龄 exec sp_help stu_info------------------------------------查询表的结构 insert into stu_info-------------------------------------添加数据 values('r0708b01','王庆伦',default,19,'山东') insert into stu_info

values('r0708b02','董莎','女',19,'山东') insert into stu_info

values('r0708b03','郭素贞','女',19,'山东') insert into stu_info

values('r0708b04','郭凤茹','女',19,'山东')

select * from stu_info---------------------------------查询表的内容

create table course_info -----------------------course -课程----课程信息表 (c_number varchar(10) primary key,-----------------------课程号

c_name varchar(30) not null,-----------------------------课程名 c_teacher varchar(30) ,----------------------------------授课教师

constraint un_cteacher unique(c_teacher)-----------------unique--唯一约束 )

insert into course_info----------------------------------添加数据 values('c001','办公','姜凤丽') insert into course_info

values('c002','组装','刘全涛') insert into course_info

values('c003','网基','马丽娜') insert into course_info

values('c004','C语言','3*') insert into course_info

values('c005','Dreamweaver','贾原')

select * from course_info--------------------------------查看数据

create table stu_score-----------------------------------成绩表 (s_number varchar(10),-----------------------------------学号 c_number varchar(10),------------------------------------课程号 score real,----------------------------------------------成绩

constraint pk_score primary key(s_number,c_number),------建立两个主键 constraint fk_s_number foreign key(s_number)references

stu_info(s_number),----foreign key外键连接，以学号(主键)连接-------foreign key 外键约束 表与表进行链接

constraint fr_c_number foreign key(c_number) references

course_info(c_number))----foreign key 外键链接,以书号(主键)链接 insert into stu_score

values('r0708b05','c001',90) insert into stu_score

values('r0708b01','c002',) select * from stu_score

alter table stu_info-----------------------------------修改表的结构,添加一列 add tep varchar(15) alter table stu_info-----------------------------------修改表的结构,添加一列 add date datetime default getdate() alter table stu_info add email varchar(30)

insert into stu_info(s_number,s_name)-------------------添加一个数据 values('r0708b08','朱蒙猛')

alter table stu_info-----------------------------------修改表的结构,删除一列 drop column email

alter table stu_info-------------------------------修改表的结构中某一列的属性 alter column s_name varchar(50) not null

exec sp_rename 'stu_info.s_name','姓名','column'---修改表的结构中某一列的名字 exec sp_rename 'stu_info.姓名','s_name','column'

exec sp_rename 'stu_info','学生信息表'------------------修改表名

exec sp_rename '学生信息表','stu_info'------------------修改表名

select * from stu_info----------------------------------查看数据

exec sp_help stu_info-----------------------------------查看表的结构

alter table stu_score----------------------------------修改表的结构,添加约束 add constraint fk_s_number foreign key(s_number)references stu_info(s_number) alter table stu_score

add constraint fk_c_number foreign key(c_number)references course_info(c_number)

alter table stu_score----------------------------------修改表的结构,删除约束 drop constraint fk_s_number alter table stu_score----------------------------------修改表的结构,删除约束 drop constraint fk_c_number

------向表中添加全部数据

values('r0708b09','李发生',default,23,'甘肃','1234567','3/12/1988') -----向表中添加部分数据-----添加时必须把主键和不允许为空的列添加上 insert into stu_info(s_number,s_name,sex) values('r0708b10','杨立一',default) insert into stu_info

values('r0708b10','杨立一',default,18,'河北','123654987','6/12/19') ------创建表aa create table aa

(s_number varchar(10), s_name varchar(30), sex varchar(3), age int,

addr varchar(30), tep varchar(20), date datetime)

------从已知表中添加数据到新表 ----添加数据全部 insert into aa

select * from stu_info ----查询表的内容 select * from aa

-----向表中添加性别为男的信息 insert into aa

select * from stu_info where sex='男'

-----删除aa表的所有内容 delete from aa

----从已知表中查询几列添加到新表

insert into aa(s_number,s_name)

select s_number,s_name from stu_info

-----更新一列

update stu_info

set tep='1234567' ----根据条件更新列 update stu_info

set date='19 12 1'

where s_number='r0708b08' -----根据条件删除项

delete from stu_info

where s_number='r0708b10' ----查看aa表的所有内容 select * from aa -----删除空项 delete from aa where sex is null

----查看stu_score表的所有内容 select * from stu_score -----原基础上增长% 全体增长 update stu_score set score=score*1.1

select score*1.1 from stu_score -----原基础上减少% 且指定具体的某一行 update stu_score set score=score*0.8

where s_number='r0708b01'

----------------------------------------------------------------------- -------------------查询------------------------------------------------- create database r0708b--创建数据库r0708b on primary--指定一个首要数据文件 (name=r0708b_data,--逻辑名字

filename='d:\\SQL练习\\r0708b.mdf',--物理名字 size=3,--最小值

maxsize=10,--最大值 filegrowth=1)--增长值

log on--创建上个数据库的日志文件 (name=r0708b_log,--逻辑名字

filename='d:\\SQL练习\\r0807b.ldf',--物理名字 size=3,--最小值

maxsize=10,--最大值 filegrowth=1)--增长值

use r0708b go

create table stu_info----------------------------------------新建学生信息表 (

s_number char(10) primary key,------------------------学号 s_name varchar(20) NOT NULL,--------------------------姓名 sex char(2) default '男',-----------------------------性别 age int,-------------------------------------------------年龄

addr varchar(50),----------------------------------------地址 birthday datetime null,-------------------------------生日

constraint ch_sex check(sex ='男' or sex='女'),----------检查约束(性别) constraint ch_age check(age between 0 and 150)---------检查约束(年龄) )

CREATE TABLE course_info----------------------------------------新建课程表 (

c_number char(10) primary key,--------------------------课程号 c_name varchar(20) NOT NULL,----------------------------课程名 c_teacher varchar(50),-------------------------------------授课教师 constraint uk_teacher unique (c_teacher)--------------------唯一约束 )

CREATE TABLE stu_score -----------------------------------------新建成绩表 ( s_num char(10) ,----------------------------------------学号 c_num char(10) ,---------------------------------------课程号 score real,-----------------------------------------------成绩 constraint p_sc primary key (s_num,c_num),----------------主键约束 constraint chk_score check(score between 0 and 100),-----成绩约束 constraint fk_snum foreign key (s_num) references stu_info(s_number),----外键约束

constraint fk_cnum foreign key (c_num) references course_info(c_number)----外键约束 )

use r0708b go

insert into stu_info-----------------------------------插入数据到表stu_info values('S99001','范捷','女',19,'北京','1980 5 1') insert into stu_info

values('S99002','李伟','男',20,'天津','1980 7 13') insert into stu_info

values('S99003','赵敏','女',20,'上海','1980 12 3') insert into stu_info

values('S99004','张宁','女',21,'北京','1980 11 19') insert into stu_info

values('S99005','','男',22,'北京','1980 6 21') insert into stu_info

values('S99006','杜强','男',22,'天津','1980 3 16') insert into stu_info

values('S98001','达到','女',21,'北京','1980 11 19') insert into stu_info

values('D99001','','男',22,'北京','1980 6 21') insert into stu_info

values('S99007','达到','男',22,'天津','1980 3 16')

insert into course_info-----------------------------插入数据到表course_info values('C001','计算机基础','刘全涛') insert into course_info

values('C002','C语言','武磊') insert into course_info

values('C003','SQL','姜凤丽') insert into course_info

values('C004','VB.ENT','于丽敏') insert into course_info

values('C005','案例','陶坤')

insert into stu_score---------------------------------插入数据到表stu_score values('S99001','C001',85) insert into stu_score

values('S99001','C002',75) insert into stu_score

values('S99001','C003',78) insert into stu_score

values('S99002','C001',65) insert into stu_score

values('S99003','C001',67) insert into stu_score

values('S99004','C001',56) insert into stu_score

values('S99005','C004',85) insert into stu_score

values('S99006','C002',80) go

select * from stu_info---------------------------------------查看所有数据

select s_number,s_name,addr from stu_info--------------------查看部分数据

select distinct s_num from stu_score-------------------------去掉重复项

select top 2 s_num from stu_score----------------------------查询前两行

select top 20 percent * from stu_score--------------------查询一个表中的前%

select sum(score) 分数和,avg(score) 平均值,count (*) 行数from stu_score----用这几个函数并起别名

select max(score) as 最大值,min(score) as 最小值,count (*) as 行数from stu_score

select 学号=s_num from stu_score

select 最大值=max(score) from stu_score

select 'r0708b班的最高分数是'+str(max(score)) from stu_score---连接在一起输出

select max(score)*5 from stu_score------------------------------运算

select * from stu_info------------------------------------------条件输出 where age >20

select * from stu_info

where age>20 and addr='北京'------------------------------------and的用法

select * from stu_info

where age>20 or addr='北京'-------------------------------------or 的用法

select * from stu_score-----------------------------------------算数运算符 where score>=70 and score<=80

select * from stu_score-----------------------------------------逻辑运算符 where score between 70 and 80

select * from stu_score----------------------------------------not 的用法 where score not between 70 and 80

select * from stu_score ----------------------------------------逻辑运算符 where not score between 70 and 80

select * from stu_info------------------------------------------%的用法 where s_number like 's%'

select * from stu_info------------------------------------------_的用法 where s_number like '_99001'

select * from stu_info------------------------------------------[]的用法 where s_number like '_9900[1,2,3]'

select * from stu_info------------------------------------------[^]的用法 where s_number like '_9900[^1,2,3]'

select * from stu_info ------------------------------- -----or 与in 的区别 where addr='北京' or addr='天津'

select * from stu_info

where not (addr='北京' or addr='天津')

select * from stu_info------------------------------------------in 的用法 where addr in ('北京','天津')

select * from stu_info-------------------------------------not in 的用法 where addr not in ('北京','天津') ---查询成绩,以学号降序排列 select * from stu_score order by score asc ----以学号升序排列

select * from stu_score order by score desc ----以学号分组查询

select s_num 学号,sum(score) 分数和,avg(score) 平均分,count(*) 行数from stu_score

group by s_num

----以学号分组查询平均分大于等于分

select s_num 学号,sum(score) 分数和,avg(score) 平均分,count(*) 行数from stu_score

group by s_num

having avg(score)>=75

----查询每一个城市的年龄之和和平均年龄

select addr 城市, sum(age) 年龄和,avg(age) 平均年龄from stu_info group by addr

----查询每一个城市的平均年龄大于的年龄之和和平均年龄

select addr 城市, sum(age) 年龄和,avg(age) 平均年龄from stu_info group by addr

having avg(age)>20

---内连接

----查询学生姓名,课程名和成绩

select stu_info.s_name,course_info.c_name,stu_score.score from stu_info inner join stu_score

on stu_info.s_number=stu_score.s_num join course_info on course_info.c_number=stu_score.c_num ------显示三个表的所有字段

select * from stu_info inner join stu_score

on stu_info.s_number=stu_score.s_num join course_info on course_info.c_number=stu_score.c_num

---练习-查询所有学生的学号/课程名/课程号和成绩 select

stu_info.s_number,course_info.c_number,course_info.c_name,stu_score.score

from stu_info inner join stu_score

on stu_info.s_number=stu_score.s_num join course_info on course_info.c_number=stu_score.c_num -----没有重复的列名,表名可以省略

select s_num,c_num,c_name,score from stu_score

join course_info on course_info.c_number=stu_score.c_num

----练习--查询所有学生的基本信息和成绩

select stu_info.*,stu_score.score from stu_info join stu_score on stu_info.s_number=stu_score.s_num

---外连接---左连接

-----查询所有的学生的基本信息,若有成绩则显示成绩

select stu_info.*,stu_score.score from stu_info left join stu_score on stu_info.s_number=stu_score.s_num ------右连接

select stu_info.*,stu_score.score from stu_score right join stu_info on stu_info.s_number=stu_score.s_num -----全连接

select stu_info.*,stu_score.score from stu_info full join stu_score on stu_info.s_number=stu_score.s_num

where score is null----null 是一种状态,不是值所以用is null 而不用=null ----查询那些学生有成绩,显示学生的基本信息和成绩 -----1第一种方法

select stu_info.*,score from stu_info,stu_score where stu_info.s_number=stu_score.s_num -----第二种方法

select stu_info.*,stu_score.score from stu_info join stu_score on stu_info.s_number=stu_score.s_num ----左连接

select stu_info.*,stu_score.score from stu_info left outer join stu_score

on stu_info.s_number=stu_score.s_num----outer 可以省略 ----自连接

---查询重名的学生的学号,姓名和籍贯

select a1.s_number,a2.s_name,a2.addr from stu_info a1 join stu_info a2 on a1.s_name=a2.s_name where a1.s_number<>a2.s_number ---1.查询未选过课的课程的基本信息 ----第一种方法 select course_info.*,stu_score.score from course_info left join stu_score on course_info.c_number=stu_score.c_num ----第二种方法

select * from course_info

where c_number not in(select distinct c_num from stu_score) ----2.查询选过课的基本信息 select * form course_info

where c_number in (select distinct c_num from stu_score) ----3.查询考过试的学生的基本信息 select * from stu_info

where s_number in(select distinct s_num from stu_score) ----4.查询未考过试的学生的基本信息 select * from stu_info

where s_number not in (select distinct s_num from stu_score) ----5.查询成绩高于平均成绩的学号,姓名,课程号,课程名和成绩 select s_number,s_name,c_number,c_name,score from stu_info join stu_score on

stu_info.s_number=stu_score.s_num join course_info on

course_info.c_number=stu_score.c_num

where score>(select avg(score) from stu_score) -----6.查询成绩高于平均成绩的学生的姓名,年龄和籍贯 ----第一种方法

select distinct s_name,age,addr from stu_info join stu_score on stu_info.s_number=stu_score.s_num where score>(select avg(score) from stu_score) -----第二种方法:多次嵌套

select s_name,age,addr from stu_info

where s_number in (select s_num from stu_score

where score>(select avg(score) from stu_score)) ----7.查询平均成绩大于等于分的学生姓名(使用分组) ----第一种方法

select s_name from stu_info join stu_score on stu_info.s_number=stu_score.s_num group by s_name

having avg(score)>=75 ----第二种方法

select s_name from stu_info

where s_number in (select s_num from stu_score group by s_num

having avg(score)>=75) ----分组时 select 后只可跟分组的列名或函数

----8.查询平均成绩大于等于分的男生的学号和平均成绩 ----第一种方法

select s_number 学号,avg(score) 平均成绩from stu_info join stu_score on stu_info.s_number=stu_score.s_num

where sex in (select sex = '男' from stu_info) group by s_number

having avg(score)>=75 ----第二种方法

select s_num,avg(score) from stu_score

where s_num in(select s_number from stu_info where sex='男') group by s_num

having avg(score)>=75

----交叉连接---cross join ---形成的是笛卡尔积

select * from stu_info cross join stu_score

select * from stu_info,stu_score

---合并查询union 多个记录结果放在一起显示 select s_number from stu_info union

select s_num from stu_score

----------------------列数一致:上面有几列下面也有几列 ----------------------相应的类型一致 ----------------------可以合并多个

----保存查询结果

----into stu_information ---保存到information表

select s_num,avg(score) avgl into information from stu_score where s_num in(select s_number from stu_info where sex='男') group by s_num

having avg(score)>=75

select * from information

----------------------------------------------------------------------- ---------------变量----------------------------------------------------- ----全局变量

print @@connections print @@cpu_busy print @@cursor_rows print @@datefirst

-----局部变量

declare @num1 int,@num2 int----定义变量num1,num2 set @num1=3---赋值 set @num2=4

print @num1---输出 print @num2

declare @num1 int,@num2 int,@num3 int---定义变量 set @num1=20---赋值

set @num2=(3*@num1-10)/3 set @num3=@num2%6

select @num1 num1,@num2 num2,@num3 num3----输出

declare @num1 int,@num2 int----定义变量 set @num1=20

set @num2=(@num1*3-10)/3

if @num1>@num2----if else 语句 print @num1 else

print @num2

----------------------------------------------------------------------- ------------函数-------------------------------------------------------- ----数学函数

select sqrt(4),square(4)---sqrt--开方---square---平方 select abs(4),abs(-3)----绝对值 select cos(pi()/3)----余弦函数 select sin(pi()/6)----正弦函数 select pi()---

select rand()----随机函数 ----字符串函数

select len('sseeiye')---求有效长度,一个汉字也占一位 select len('哦嗯哦哦额')

select left('dsfwu',2)---从左边开始输出,输出个字符 select right('sdeoiru',4)---从右边开始输出,输出个字符

select substring('sopueiuo',4,3)---从第个位置开始输出,输出个字符

select replace('dfeioueoiuo','df','万恶讴')---把字符串中'df'替换为'万恶讴' select str(398)----把其他类型转换为字符类型

----日期函数

print getdate()----今天的日期 select getdate()

select day(getdate()),day('12/1/2008')----输出日 select month(getdate()),month('12/1/2008')---输出月 select year(getdate()),year('12/1/2008')----输出年

select dateadd(year,5,getdate()),dateadd(month,15,getdate())---日期加 select dateadd(year,-5,getdate()),dateadd(month,-15,getdate())---日期减

----系统内置函数:空函数-isnull

select * from stu_info----查询表的所有内容

insert into stu_info(s_number,s_name)--添加数据 values('r0708b01','王庆伦')

insert into stu_info(s_number,s_name) values('r0708b02','董莎')

insert into stu_info(s_number,s_name) values('r0708b03','郭素贞')

select * from stu_info---查询刚添加的数据 where left(s_number,7)='r0708b0'

select s_number,s_name,isnull(age,18) age from stu_info----查询刚添加的数据并让年龄默认为18

where left(s_number,7)='r0708b0'

select * from stu_score----查询成绩表中的所有数据 select avg(score) from stu_score----查询平均成绩 insert into stu_score(s_num,c_num)----添加数据 values('S99001','c005')

select avg(isnull(score,0)) from stu_score----查询添加数据后的平均数据

----转换函数:

---cast /convert

declare @num1 int, @var varchar(10) set @num1=10 set @var='a'

--select cast (@num1 as char(10))+'5' print convert(char(10),@num1)+'asdfd'

print cast(rand()*100 as int)----把随机函数转换为整型 print convert(int,rand()*10)

declare @num1 int,@num2 int,@var varchar(10) set @num1=10 select @num2=20

select @var='afdsfg'

print str(@num1)+@var-----显示转换 print @num1+'5'----隐式转换

----作业

---1.定义一个变量赋值如果值>=80 输出优如果80>值>=60 输出良否则输出不及格 ----第一种方法

declare @num int set @num=79 if @num>=80 print '优'

else if 60<=@num print '良' else

print '不及格' ----第二种方法

declare @num int,@var varchar(30) set @num=79 set @var=case

when @num>=80 then '优' when @num>=60 then '良' else '不及格' end print @var

-----练习求计算机基础的平均成绩如果大于等于输出'成绩理想'并输出平均分 -----否则输出'成绩不理想'并输出平均分 declare @avg real

set @avg=(select avg(score) from stu_score where c_num='c001')

if @avg>=75 begin

print '成绩理想!!' print @avg end else begin

print '成绩不理想!!' print @avg end

-----练习求学号为'S99001'的平均成绩如果大于等于输出'成绩理想'并输出平均分 -----否则输出'成绩不理想'并输出平均分 declare @avg real

set @avg=(select avg(score) from stu_score where s_num='S99001') if @avg>=75 begin

print '成绩理想!!' print @avg end else begin

print '成绩不理想!!' print @avg end

---if else 或者while 语句里语句多于条时用begin end (相当于C语言中的大括号) -----查询时用课程名代替课程号

select s_num 学号,课程名=case c_num

when 'c001' then '计算机基础' when 'c002' then 'C语言' when 'c003' then 'SQL' when 'c004' then 'vb.net' end ,score from stu_score

----把成绩表中的成绩如果大于显示'优'60<=成绩=<80显示'良'否则显示'不及格' select s_num ,c_num ,成绩=case when score>80 then '优' when score>=60 then '良' else '不及格' end from stu_score ---2.求!

----第一种方法

declare @i int,@sum int set @i=1 set @sum=1 while @i<=6 begin

set @sum=@sum*@i set @i=@i+1 end

print @sum

----第二种方法--goto:无条件进行连接 ----goto 必须与if else 连用 declare @sum int,@i int set @i=1

set @sum=1 loop:

set @sum=@sum*@i set @i=@i+1 if @i<=6 goto loop else

print @sum

----break continue 只能用在while循环里 ----break 结束整个循环continue 结束本次循环 declare @i int,@sum int set @i=1 set @sum=1 while @i<=6 begin

set @sum=@sum*@i set @i=@i+1 if @sum>100 break end

print @sum

----waitfor

select * from stu_score

waitfor delay '00:00:10'----延迟秒--delay--延迟 select * from stu_score

waitfor time '10:00'---延迟分--time 时间 ----return ----调用函数

----查询每门课程的平均分

select distinct c_num,平均分=dbo.fun_avg(c_num) from stu_score--dbo--用表来调用函数

---fun_avg--函数名--(参数)

create function fun_avg(@cnum varchar(10))--创建函数--fun_avg--函数名() 接收参数(@(变量) 类型)

returns real---返回值的类型 begin

declare @avg real----定义变量

set @avg=(select avg(score) from stu_score where c_num=@cnum) return @avg---返回值 end

select avg(score) from stu_score where c_num='C001'

select avg(score) from stu_score

----1.求每位同学的平均分(通过函数求) ----通过学号

select distinct s_num,平均分=dbo.fun_savg(s_num) from stu_score create function fun_savg(@snum varchar(10)) returns real begin

declare @avg real

set @avg=(select avg(score) from stu_score where s_num=@snum) return @avg end

----通过姓名

select distinct s_name,平均分=dbo.fun_ssavg(s_name) from stu_score join stu_info on stu_score.s_num=stu_info.s_number create function fun_ssavg(@sname varchar(10)) returns real begin

declare @avg real

set @avg=(select avg(score) from stu_score join stu_info on stu_score.s_num=stu_info.s_number where s_name=@sname) return @avg end

----2.输入班级(通过学号得到)和课程名求此班此门课的总分(两个参数) ----第一种方法使用substring

select distinct 班级=substring(s_num,1,len(s_num)-2),c_name ,

总分=dbo.fun_sum(substring(s_num,1,len(s_num)-2),c_name) from stu_score join course_info

on stu_score.c_num=course_info.c_number

create function fun_sum(@class varchar(10),@cname varchar(20)) returns real begin

declare @sum real

set @sum=(select sum(score) from stu_score,course_info where stu_score.c_num=course_info.c_number

and substring(s_num,1,len(s_num)-2)=@class and c_name=@cname) return @sum end

------第二种方法使用left

select distinct 班级=left(s_num,len(s_num)-2),c_name, 总分=dbo.fun_sum(left(s_num,len(s_num)-2),c_name)

from stu_score join course_info on stu_score.c_num=course_info.c_number create function fun_sum(@class varchar(10),@cname varchar(20)) returns real begin

declare @sum real

set @sum=(select sum(score) from stu_score,course_info where stu_score.c_num=course_info.c_number and left(s_num,len(s_num)-2)=@class and c_name=@cname) return @sum end

----------------------------------------------------------------------- ----------视图---------------------------------------------------------- select * from v1----查询视图v1

exec sp_helptext v1----查询视图v1的代码 -----创建视图

create view scs as

select s_number,s_name,c_number,c_name,score from stu_score join stu_info on stu_score.s_num=stu_info.s_number join course_info on stu_score.c_num=course_info.c_number select * from scs ---1求学号和平均分

create view savg----创建视图 as

select s_num,avg(score) avg1 from stu_score group by s_num

select * from savg

----2.求学号.姓名和平均分 ----第一种方法

create view savg1 as

select savg.*,s_name from savg,stu_info where savg.s_num=stu_info.s_number select * from savg1 ----第二种方法

create view ssavg as

select s_num,s_name,avg(score) avg1 from stu_score join stu_info on stu_score.s_num=stu_info.s_number group by s_num, s_name select *from ssavg

----对视图的更改

alter view scs---更改视图 as

select s_number,s_name,c_name,score from stu_info join stu_score on stu_info.s_number=stu_score.s_num join course_info on

course_info.c_number=stu_score.c_num

drop view v1 ----删除视图

insert into scs(s_number,s_name)----插入数据 values('12345','addf')

update scs----通过视图修改表的内容 set score=80

select * from scs

select * from stu_info----察看数据

-----创建一个视图把所有的男生的基本信息放在视图中 create view boyinfo as

select stu_info.* from stu_info where sex='男'

----修改视图:加密和检查 alter view boyinfo

with encryption ---加密 as

select * from stu_info where sex='男'

with check option --检查

select * from boyinfo--查询

exec sp_helptext boyinfo----查看代码

insert into boyinfo

values('12233','asdf','男',19,'sded','2000 12 12')---插入数据

----------------------------------------------------------------------- ---------------索引----------------------------------------------------- select *from stu_info

create index ff----创建索引 on stu_info(s_name)

select * from stu_info where s_name=''

create clustered index dd---创建聚集索引 on information(s_num)

create nonclustered index df---创建非聚集索引 on stu_info(addr)

drop index stu_info.ff---删除索引(在索引名前加上表名)

----------------------------------------------------------------------- ---------------存储过程------------------------------------------------- create procedure jiecheng----创建存储过程 @num int as

declare @i int,@sum int set @i=1 set @sum=1

while @i<=@num begin

set @sum=@sum*@i set @i=@i+1 end

print @sum

exec jiecheng 7----数字可任意改,改成几,就是求几的介乘

create table use_info----创建用户表 (id int identity (1,1),

use_name varchar(50) primary key, use_pass varchar(50) not null) select * from use_info

----对用户信息表的处理 ----1 登录(查询) ----2 注册(添加) ----3 修改密码 ----4 删除用户

-----1.登录 use r0708b

if exists (select name from sysobjects---sysobjects 系统对象 where name='use_sel' and type='p')---type-查找的类型 drop procedure use_sel----删除存储过程 go

create procedure use_sel---创建存储过程

@use_name varchar(50),@use_pass varchar(50) as

declare @i int

set @i=(select count(*) from use_info

where use_name=@use_name and use_pass=@use_pass) if @i=1

print '登录成功' else

print '用户名或密码错误'

exec use_sel '郭凤茹','123456'---调用存储过程

---2 注册

----在创建存储过程时如果出错后,需删除存储过程,再重新创建

----若加上下面的语句后就可不用删除了,在修改后直接执行一下就可以 -----type +'查找类型'-'p'-存储过程-'v'-视图-'py'-触发器 use r0708b

if exists (select name from sysobjects---sysobjects 系统对象 where name='use_insert' and type='p')---type-查找的类型 drop procedure use_insert----删除存储过程 go

create procedure use_insert----创建存储过程 @use_name varchar(20), @use_pass varchar(50) as

declare @i int,@re_id int

set @i=(select count(*) from use_info where use_name=@use_name) if @i=1 begin

print '注册名已存在!!!' end else begin

insert into use_info

values(@use_name,@use_pass) print '注册成功!!' end go

exec @i=use_insert 'guofengru','123456',@name output----调用存储过程 select * from use_info

----3 修改密码 ----1

use r0708b

if exists (select name from sysobjects

where name='use_update' and type='p') drop procedure use_update go

create procedure use_update-----创建存储过程 @use_name varchar(20),----用户名 @old_pass varchar(20),----旧密码 @new_pass varchar(20)----新密码 as

declare @i int

set @i=(select count(*) from use_info

where use_name=@use_name and use_pass=@old_pass) if @i=1 begin

print '登录成功!!' update use_info

set use_pass=@new_pass where use_name=@use_name print '修改成功!!' end else

print '用户名或密码错误!!' go

exec use_update '郭凤茹','123','12','1'---调用存储过程 ----2

use r0708b

if exists (select name from sysobjects

where name='use_update' and type='p') drop procedure use_update go

create procedure use_update-----创建存储过程 @use_name varchar(20),----用户名 @old_pass varchar(20),----旧密码 @new_pass varchar(20),----新密码 @nn_pass varchar(20)-----确认密码 as

declare @i int

set @i=(select count(*) from use_info

where use_name=@use_name and use_pass=@old_pass) if @i=1 begin

print '登录成功!!'

if @new_pass=@nn_pass begin

update use_info

set use_pass=@new_pass where use_name=@use_name

print '修改成功!!' end else

print '密码不一致!!请重新修改' end else

print '用户名或密码错误!!' go

exec use_update '郭凤茹','123','12','1'---调用存储过程

select * from use_info

----4 删除用户 use r0708b

if exists (select name from sysobjects

where name='use_delete' and type='p') drop procedure use_delete go

create procedure use_delete @use_name varchar(20) as

delete from use_info

where use_name=@use_name go

exec use_delete 'guofengru' select * from use_info

----------------------------------------------------------------------- ---------------输出参数print--------------------------------------------- ----注册 use r0708b

if exists (select name from sysobjects---sysobjects 系统对象 where name='use_insert' and type='p')---type-查找的类型 drop procedure use_insert----删除存储过程 go

create procedure use_insert----创建存储过程 @use_name varchar(20), @use_pass varchar(50),

@re_id int output,----output ---表示该变量是输出参数 @re_usename varchar(20) output as

declare @i int

set @i=(select count(*) from use_info where use_name=@use_name ) if @i=1

print '注册名已存在!!!' else begin

insert into use_info

values(@use_name,@use_pass)

set @re_id=(select id from use_info where use_name=@use_name)

set @re_usename=@use_name print '注册成功!!' end

declare @i int,@name varchar(20)----调用存储过程

exec use_insert 'guofeng','123456',@i output,@name output print rtrim(@name)+'是第'+rtrim(@i) +'位注册成功' select * from use_info

----登录 use r0708b

if exists (select name from sysobjects

where name='use_sel' and type='p') drop procedure use_sel go

create procedure use_sel @use_name varchar(20), @use_pass varchar(20), @re_id int output,

@re_usename varchar(20) output as

declare @i int

set @i=(select count(*) from use_info

where use_name=@use_name and use_pass=@use_pass) if @i=1 begin

print '登录成功!!'

set @re_id=(select id from use_info where use_name=@use_name) set @re_usename =@use_name end else

print '用户名或密码错误'

declare @i int,@name varchar(20)

exec use_insert 'guofengru','123456',@i output,@name output print rtrim(@name)+'是第'+rtrim(@i) +'位登录成功' select * from use_info

----更改密码

use r0708b

if exists (select name from sysobjects

where name='use_update' and type='p') drop procedure use_update go

create procedure use_update-----创建存储过程 @use_name varchar(20),----用户名 @old_pass varchar(20),----旧密码 @new_pass varchar(20),----新密码 @re_id int output,

@re_usename varchar(20) output as

declare @i int

set @i=(select count(*) from use_info

where use_name=@use_name and use_pass=@old_pass) if @i=1 begin

print '登录成功!!' update use_info

set use_pass=@new_pass where use_name=@use_name print '修改成功!!'

set @re_id=(select id from use_info where use_name=@use_name) set @re_usename=@use_name end else

print '用户名或密码错误!!' go

declare @i int,@name varchar(20)---调用存储过程

exec use_update '郭凤茹','123','12','12',@i output ,@name output print rtrim(@name)+'用户您好,您的ip号是'+rtrim(@i) select * from use_info ----删除用户 use r0708b

if exists (select name from sysobjects

where name='use_delete' and type='p') drop procedure use_delete go

create procedure use_delete @use_name varchar(20), @re_id int output,

@re_usename varchar(20) output as begin

delete from use_info

where use_name=@use_name

set @re_id =(select id from use_info where use_name=@use_name) set @re_usename=@use_name end go

declare @i int,@name varchar(20)

exec use_delete 'guofengru',@i output,@name output

print 'id号为'+rtrim(@i)+'的用户'+rtrim(@name)+'已成功被删除' select * from use_info

----------------------------------------------------------------------- -------------------返回值return----------------------------------------- use r0708b

if exists (select name from sysobjects

where name='use_insert' and type='p') drop procedure use_insert go

create procedure use_insert

@use_name varchar(20), @use_pass varchar(20),

@re_usename varchar(20) output as

declare @i int, @re_id int

set @i=(select count(*) from use_info where use_name=@use_name ) set @re_id=(select id from use_info where use_name=@use_name) set @re_usename=@use_name return @re_id if @i=1

print '注册名已存在' else begin

insert into use_info

values (@use_name,@use_pass) print '注册成功' end go

declare @i int,@re_id int,@name varchar(20)

exec @i=use_insert 'guofeng','123',@name output print rtrim(@name)+'是第'+rtrim(@i)+'位注册成功'

----------------------------------------------------------------------- ------------------临时表------------------------------------------------ use r0708b

if exists(select name from sysobjects

where name='p_avg' and type='p') drop procedure p_avg go

create procedure p_avg @class varchar(10) as

create table #savg (s_num varchar(10), avgl real)

insert into #savg

select s_num,avg(score) from stu_score group by s_num

having avg(score)>=75

select s_name,#savg.* from #savg,stu_info where #savg.s_num=stu_info.s_number and left(s_num,len(s_num)-2)=@class go

exec p_avg 's990' select * from #savg

select * from stu_score

----------------------------------------------------------------------- ------------------------游标-------------------------------------------- ---静态游标

declare cur_student cursor----创建游标 for

select s_number,s_name from stu_info

open cur_student----打开游标

fetch next from cur_student

deallocate cur_student----删除游标

----动态游标

declare cur_student cursor dynamic for

select s_number,s_name from stu_info

open cur_student

fetch next from cur_student

fetch first from cur_student

fetch last from cur_student

fetch relative 4 from cur_student

----滚动游标

declare cur_student cursor scroll for

select s_number,s_name from stu_info

open cur_student

fetch next from cur_student

fetch first from cur_student

fetch relative 4 from cur_student

fetch absolute 4 from cur_student -----决定到第行

----------------------------------------------------------------------- --------------------触发器---------------------------------------------- use r0708b

if exists (select name from sysobjects

where name='tr_stuscore' and type='tr') drop trigger tr_stuscore go

create trigger tr_stuscore------------创建添加，修改的触发器 on stu_score

for insert ,update as

print '触发执行'

declare @cnum varchar(10)

set @cnum=(select c_num from inserted)

if @cnum in(select c_number from course_info) begin

update course_info set geshu=geshu+1 where c_number=@cnum end go

insert into stu_score----向成绩表中添加数据 values('s99005','c003',56) select *from stu_score

update stu_score----修改成绩表 set c_num='c003'

where s_num='s99001'

alter table course_info----修改成绩表，添加一行（个数） add geshu int

update course_info----给个数这一行赋值为0 set geshu=0

use r0708b

if exists (select name from sysobjects

where name='tr_studelete' and type='tr') drop trigger tr_studelete go

create trigger tr_studelete------------创建删除的触发器 on stu_info for delete as

declare @snum varchar(10)

set @snum=(select s_number from deleted) delete from stu_score where s_num=@snum go

delete from stu_info------------调用触发器 where s_number='s99001'