SQL server数据库基本语句

create database studentdb
on primary(
name = student_data,
filename='d:\abc\student_data.mdf',
size=5,
maxsize=unlimited,
filegrowth=1
)log on(
name=student_log,
filename='d:\abc\student_log.ldf',
size=5,
maxsize=100,
filegrowth=10%
)

打开数据库

use studentdb

新建数据表

create table xsda(
学号 char(4) not null,
姓名 nchar(20),
性别 char(20),
年龄 int
)

插入记录

insert xsda(学号,姓名,性别,年龄)
values('1001','zhangsan','nan',20),
('1002','lisi','nv',18),
('1003','wagnwu','nan',21),
('1004','xiaoming','nan',23)

修改记录

update xsda
set 性别='nan',年龄=30
where 学号='1002'

删除记录

delete xsda
where 学号='1004'

查询

select * from xsda

删除数据库

drop database studentdb

删除表

drop table xsda

系统存储过程procedure

execute sp_help xsda
execute sp_helpdb 
execute sp_helpdb studentdb

修改表字段名

execute sp_rename 'xsda.姓名','学生姓名'

修改表名

execute sp_rename 'xsda','学生档案表'

修改数据库名

execute sp_rename 'studentdb','学生库'

修改表结构

添加一列

alter table xsda
add 电话 char(11) null

删除一列

alter table xsda
drop column 电话

修改一列

alter table xsda
alter column 性别 nchar(11)

添加约束

修改数据库所有者

exec sp_changedbowner 'SA'

主键约束 primary key

create table xsda(
学号 char(20) primary key,  --列级约束
姓名 nchar(20),
性别 char(20),
年龄 int,
)
if object_id ('xsda') is not null
drop table xsda

create table xsda(
学号 char(20),
姓名 nchar(20),
性别 char(20),
年龄 int,
primary key(学号)  --表级约束
)

if object_id ('xsda') is not null
drop table xsda

create table kecheng(
学号 char(20),
姓名 nchar(20),
课程号 char(20),
成绩 int,
primary key(学号,课程号)
)

添加自定义完整性约束check

create table xsda(
学号 char(20),
姓名 nchar(20),
性别 char(20) check(性别 = '男' or 性别 =  '女'),
年龄 int,
)

create table kecheng(
学号 char(20),
姓名 nchar(20),
课程号 char(20),
成绩 int check (成绩>=0 and 成绩<=100), --成绩0-100之间
primary key(学号,课程号),
check(学号 like '[0-9][0-9][0-9][0-9]') --学号四位数字
)

添加外键约束 foreign key references

create table kecheng(
学号 char(20),       --列级约束 foreign key references xsda(学号),
姓名 nchar(20),
课程号 char(20),
成绩 int,
primary key(学号,课程号),
foreign key(学号) references xsda(学号)
)

添加主键外键约束

exec sp_changedbowner 'sa'
create table xsda(
学号 char(20),
姓名 nchar(20),
性别 char(20),
年龄 int,
primary key(学号),
)
create table kechengbiao(
课程号 char(20),       
课程名称 nchar(20),
学分 char(20),
primary key(课程号),
)
create table chengji(
学号 char(20),
姓名 nchar(20),
课程号 char(20),
成绩 int,
primary key(学号,课程号),
foreign key(学号) references xsda(学号),
foreign key(课程号) references kechengbiao(课程号)
)

已创建表进行添加主外键


alter table 学生 add primary key(学号)
alter table 课程 add primary key(课程号)

alter table 选课 add foreign key(学号) references 学生(学号)
alter table 选课 add foreign key(课程号) references 课程(课程号)

*实验创建数据库

--创建数据库SCHOOL 
IF DB_ID('school')IS NOT NULL
	DROP DATABASE school
CREATE DATABASE school
On
(
	NAME='school_data',
	FILENAME='E:\mysql\school_data.mdf',
	SIZE=10MB,
	MAXSIZE=100MB,
	FILEGROWTH=10%
)
LOG ON
(
NAME='school_log',
	FILENAME='E:\mysql\school_log.ldf',
	SIZE=10MB,
	MAXSIZE=UNLIMITED,
	FILEGROWTH=2MB
)
Go

--打开数据库
USE SCHOOL

GO
--创建STUDENT表
IF object_id('student')is not null
	DROP TABLE student
CREATE TABLE student
(
	sno NCHAR(3) NOT NULL,
	sname NVARCHAR(10) NOT NULL,
	ssex NCHAR(1),
	sbirthday DATETIME,
	sclass NCHAR(4)
)
GO
--输入数据
INSERT INTO student (sno,sname,ssex,sbirthday,sclass)
VALUES('101','曹丽','女','1986-2-20','X053')

INSERT INTO student
VALUES('102','陈璐璐','女','1984-6-3','X053')

INSERT INTO student
VALUES('103','陈姝栾','女','1985-10-2','X053')

INSERT INTO student
VALUES('104','程凯','男','1986-1-23','J057')

INSERT INTO student
VALUES('105','储慧','女','1986-9-10','J057')

INSERT INTO student
VALUES('106','邓赵龙','男','1985-2-10','J057')

-- 创建COURSE表
IF object_id('course')is not null
	DROP TABLE course

CREATE TABLE course
(
	cno NCHAR(4) ,
	cname NVARCHAR(20) NOT NULL,
	credit SMALLINT,
	tno NCHAR(3)
)

--输入数据
INSERT INTO course
VALUES('3101','C语言程序设计',4,'801')

INSERT INTO course
VALUES('3102','数据库原理和应用',4,'802')

INSERT INTO course
VALUES('3103','LINUX 系统配置',2,'803')

GO 
--创建SCORE表
IF object_id('score')is not null
	DROP TABLE score

CREATE TABLE score
(
	sno NCHAR(3) ,
	cno NCHAR(4) ,
	degree INT ,
)

--输入数据
INSERT INTO score VALUES('101','3101','85')
INSERT INTO score VALUES('101','3102','64')
INSERT INTO score VALUES('101','3103','85')

INSERT INTO score VALUES('102','3101','92')
INSERT INTO score VALUES('102','3102','86')

INSERT INTO score VALUES('103','3101','88')
INSERT INTO score VALUES('103','3102','75')

INSERT INTO score VALUES('104','3102','91')
INSERT INTO score VALUES('104','3103','79')

INSERT INTO score VALUES('105','3101','78')
INSERT INTO score VALUES('105','3103','81')

INSERT INTO score VALUES('106','3101','76')
INSERT INTO score VALUES('106','3102','68')

GO
--创建TEACHER表
IF object_id('teacher')is not null
	DROP TABLE teacher

CREATE TABLE teacher
(
	tno NCHAR(3) ,
	tname NVARCHAR(10) NOT NULL,
	tsex NCHAR(1),
	tbirthday DATETIME,
	prof NVARCHAR(10),
	depart NVARCHAR(10)
)

--输入数据
INSERT INTO teacher
VALUES ('801','范博','男',50,'教授','信息工程系')

INSERT INTO teacher
VALUES ('802','方磊磊','女',28,'讲师','信息工程系')

INSERT INTO teacher
VALUES ('803','钱海明','女',26,'讲师','管理系')

INSERT INTO teacher
VALUES ('804','高伟','男',49,'副教授','管理系')

INSERT INTO teacher
VALUES ('805','李冰冰','男',60,'教授','数学系')



--------------------------------------------------------------------
select * from student
select * from course
select * from score
select * from teacher 

select * from student,course;

查询语句

查询指定列

select sno,sname,sclass
from student

select tname,depart
from teacher

计算列

select 1000+20/4

查看全局变量

select @@VERSION

select @@LANGUAGE

查看前三列

select top 3 * from student

查看前百分之三十列

select top 30 percent * from student

查看指定列，去除重复项

select distinct depart
from teacher

计算列，别名

select sname,2023-year(sbirthday) as age
from student

select sname,2023-year(sbirthday) age
from student

select sname,age=2023-year(sbirthday)
from student

select sno,cno,degree+5
from score

year month

select *
from student
where year(sbirthday)=1986

select *
from student
where month(sbirthday)=02

between

select *
from student
where  sbirthday  between '19860101'and '19861231'

select *
from score
where degree between 80 and 90

select *
from score
where degree not between 80 and 90

or in

select *
from teacher
where prof='教授' or prof='副教授'

select *
from teacher
where prof in ('教授','副教授')

like

--  %  0-n个字符
--  _  1个字符
select *
from student
where sname like '陈%'

select *
from student
where sname like '%赵%'

select *
from student
where sname like '陈__'

order by

select *  from score order by degree
select top 3 degree from score order by degree desc

avg sum max min count

-- avg
select avg(degree) as 平均成绩 from score

-- sum
select sum(degree) as 总成绩 from score

-- max
select max(degree) as 总成绩 from score

-- min
select min(degree) as 总成绩 from score

-- count
select count(sno) from score

分组

select sno 学号,avg(degree) 学生平均成绩
from score
group by sno

select cno 课程号,avg(degree) 课程平均成绩
from score
group by cno

select ssex 性别,avg(2023-year(sbirthday))
from student
group by ssex

select sclass,ssex,avg(2023-year(sbirthday))
from student
group by sclass,ssex

select sclass,ssex,count(*)
from student
group by sclass,ssex
having count(*)>=2

微软格式和ANSI格式

微软格式

select student.sno,sname,cno,degree
from student,score
where student.sno=score.sno

ANSI格式

select student.sno,sname,cno,degree
from student join score
on student.sno=score.sno

微软格式和ANSI格式对比

select student.sno,sname,course.cno,cname,degree
from student,score,course
where student.sno=score.sno and course.cno=score.cno

select student.sno,sname,course.cno,cname,degree
from student join score
on student.sno=score.sno
join course
on course.cno=score.cno

微软格式和ANSI格式三表对比

--MSANSI格式
select student.sno,sname,score.cno,cname,degree,teacher.tno,tname,prof
from student 
join score
on student.sno=score.sno
join course
on score.cno=course.cno
join teacher
on course.tno=teacher.tno

--MS微软格式
select student.sno,sname,course.cno,cname,teacher.tno,tname,prof
from student,score,course,teacher
where student.sno=score.sno
and score.cno = course.cno
and course.tno = teacher.tno

连接

*实验-添加数据

INSERT INTO student
VALUES ('107','张三','','','')
INSERT INTO course
VALUES ('111','张三','','')

左连接left join

select *
from student 
left join score
on student.sno=score.sno

全连接full join

select *
from student
left join score
on student.sno=score.sno
full join course
on score.cno=course.cno

*实验-添加数据

--创建second表
IF object_id('second')is not null
	DROP TABLE second
CREATE TABLE second
(
	cno NCHAR(3) NOT NULL,
	cname NVARCHAR(10) NOT NULL,
	cpno NCHAR(3),
)
select *
from second

INSERT INTO second VALUES('1','数据库','5')
INSERT INTO second VALUES('2','数学','')
INSERT INTO second VALUES('3','信息系统','1')
INSERT INTO second VALUES('4','操作系统','6')
INSERT INTO second VALUES('5','数据结构','7')
INSERT INTO second VALUES('6','数据处理','')
INSERT INTO second VALUES('7','PASCAL语言','6')

交叉连接 cross join

select first.cname 课程,tu.cname 先修课,f.cname 必修课
from second first 
cross join second tu
cross join second f
where first.cpno=tu.cno and tu.cpno=f.cno

操作结果集

union 合并结果集

select sname from student
union
select cname from course

except

--except    101选102未选
select cno from score where sno='101'
except
select cno from score where sno='102'

intersect 交集

select cno from score where sno='101'
intersect
select cno from score where sno='102'

子查询

对比

select sname
from student,score
where student.sno=score.sno
and score.cno='3101'

select sname
from student
where sno in 
(
select sno from score where cno='3101'
)

三表子查询

--选修了c语言程序设计这门课的学生的姓名
select sname from student where sno in (
	select sno from score where cno in(
		select cno from course where cname='C语言程序设计'
	)
)

--成绩大于3101课程成绩平均值的学生的姓名
select sname from student where sno in (
	select sno from score where degree > 
		(select avg(degree) from score where cno='3101')
	)

局部变量和全局变量

--局部变量
go
declare @x float    --定义局部变量
set @x=100			--赋值
print @x			-输出

go
declare @x int
select @x=100
select @x

go
declare @y nchar(20)
set @y='hello'
print @y

go
declare @name varchar(20)
set @name='陈%'
use school
select *
from student
where sname like @name
go
print @@rowcount  --全局变量--输出最近一次查询涉及的行数

流程控制

条件控制if else

--if else
if not exists
(
select *
from teacher
where prof in ('教授','副教授')
)
begin
print '对不起'
print '没找到'
end
else

select tname,tbirthday,prof
from teacher
where prof in ('教授','副教授')

条件控制case end

select student.sno,sname,course.cno,cname,
case degree/10
	when 10 then '优秀'
	when 9 then '优秀'
	when 8 then '良好'
	when 7 then '良好'
	when 6 then '及格'
	else '不及格'
end as '等级'
from student,score,course
where student.sno=score.sno and score.cno=course.cno

循环控制while

go
declare @i as int,@sum int
select @i=1,@sum=0
while @i<=100
begin
	if(@i%2!=0)
	begin
	set @sum=@sum+@i
	end
	set @i=@i+1
end
print '100以内的奇数和:'+cast(@sum as char(10))
------------------------------------------------------------
go
declare @j float,@num float
select @j=0,@num=0.0
while @j<64
begin
	set @num=@num+power(2.0,@j)
	set @j=@j+1
end
print '2的零次方到2的63次方和:'+cast(@num as char(20))

函数

时间函数

数学函数

函数名	函数功能	函数名	函数功能
ABS()	返回给定数值表达式的绝对值	POWER()	返回给定表达式的指定幂的值
CEILING()	返回大于或等于给定数字表达式的最小整数	RAND()	返回介于0和1之问的随机 float 值
FLOOR()	返回小于或等于给定数字表达式的最大整数	ROUND()	返回一个舍入到指定长度或精度的数字表达式
EXP()	返回给定 fioat 表达式的指数值	SIGN()	返问给定表达式的正号（+1）、零（0)或负号（1)
LOG()	返回给定 float 表达式的自然对数	SQRT()	返回给定表达式的平方根
LOG10()	返回给定float 表达式的以 10 为底的对数	SQUARE()	返回给定表达式的平方

print abs(-10)
print ceiling(3.14)
print floor(3.14)
print floor(-3.14)

print exp(1)
print exp(0)
print log10(2)
print power(3.5,4.1)
print power(2,10)

元数据库函数

函数名	函数功能
DB ID()	返回数据库的标识号
DB Name()	返回数据库标识号所对应的名称
Object ID()	返回对象标识号
object Name()	返回指定对象标识号的对象名称

字符串函数

print ascii('A')--值码互换
print char(65)

go--大写换小写
declare @ch char
set @ch='Z'
print char(ascii(@ch)+32)

go
declare @ch char(40)
set @ch='   陈'
select *
from student
where left(sname,1)=ltrim(@ch)

go
select *
from course
where right(cname,2)='应用'

go
declare @str char(20)
set @str='SQL Server'
print upper(@str)--全大写
print lower(@str)--全小写
print reverse(@str)--原样倒序

go
print len('ABC')--返回字符表达式的字符数
print datalength('ABC')--返回表达式的字节数
print len('数据库')
print datalength('数据库')

print '数据库'
print ''
print space(90)+'数据库'--返回若干个空格组成的表达式
print replicate('*',90)+'数据库'--返回重复指定次数的表达式
print replicate('Hello',3000)

*实验-身份证校验

declare @id char(18)
declare @dqm char(6),@shrm char(8),@xbm char(1),@jym char(1),@shfm char(2)
declare @xb char(2),@sh char(20)
declare @s char(6),@r char(6)

set @id='100101200001011244'
set @dqm=substring(@id,1,6)
set @shfm=left(@dqm,2)
if @shfm='34'
	set @sh='安徽省'
else if @shfm='10'
	set @sh='北京市'
else if @shfm='11'
	set @sh='天津市'
else if @shfm='12'
	set @sh='河北省'

set @s=substring(@id,1,1)*7+substring(@id,2,1)*9+substring(@id,3,1)*10+substring(@id,4,1)*5+substring(@id,5,1)*8+
substring(@id,6,1)*4+substring(@id,7,1)*2+substring(@id,8,1)*1+substring(@id,9,1)*6+substring(@id,10,1)*3+
substring(@id,11,1)*7+substring(@id,12,1)*9+substring(@id,13,1)*10+substring(@id,14,1)*5+substring(@id,15,1)*8+
substring(@id,16,1)*4+substring(@id,17,1)*2

set @r=@s%11
set @jym=
case @r
	when 0 then 1
	when 1 then 0
	when 2 then 'X'
	when 3 then 9
	when 4 then 8
	when 5 then 7
	when 6 then 6
	when 7 then 5
	when 8 then 4
	when 9 then 3
	when 10 then 2
end

set @shrm=substring(@id,7,8)
set @xbm=substring(@id,17,1)

if @xbm%2=1
	set @xb='男'
else
	set @xb='女'



print '省市自治区：'+@sh
print '生日：'+@shrm
print '性别：'+@xb
print '校验码：'+@jym

存储过程和触发器

用户自定义存储过程

--创建存储过程：查询学生的成绩
if exists
(
select * from sysobjects where name='usp_p1' and xtype='p'
)
drop procedure usp_p1

go
create procedure usp_p1
@xh char(3)
with encryption--安全性处理
as
select s.sno,sname,c.cno,cname,degree
from student s,score sc,course c
where s.sno=sc.sno and sc.cno=c.cno
and s.sno=@xh

--执行存储过程
execute usp_p1 '101'
select * from sysobjects

--查看存储过程
sp_helptext usp_p1
sp_help usp_p1

---------------------------------------------
--查询某个学生平均分的存储过程
if exists
(
select * from sysobjects where name='usp_avg' and xtype='p'
)
drop procedure usp_avg

go
create procedure usp_avg
	@xh char(3),
	@pjf float out
as
set @pjf=(select avg(degree) from score where sno=@xh)


--调用存储过程
declare @pjf float
execute usp_avg '103',@pjf out
print @pjf

触发器

创建触发器

--创建触发器
if exists(
select * from sysobjects where name='tri_student_insert' and xtype='TR'
)
drop trigger tri_student_insert

go
create trigger tri_student_insert
on student
for insert
as
print '用户添加了数据'

--触发器是自动执行的
--触发时机：当用户执行语句时
insert student(sno,sname) values ('888','张三')
select * from student

创建带参数触发器

--创建触发器2
if exists(
select * from sysobjects where name='tri_student_delete' and xtype='TR'
)
drop trigger tri_student_delete

go
	create trigger tri_student_delete
	on student
	with encryption --加密，exec sp_helptext无法查看文档
	after delete
	as
		declare @num int
		set @num=@@ROWCOUNT
		print '用户删除了数据'
		print '共删除了'+rtrim(ltrim(cast(@num as char (10))))+'条记录'



delete from student where sname='张三'
select * from student

查看触发器文档

--查看触发器文档
exec sp_helptext tri_student_delete

删除系统的提示文档

--用于删除系统的提示文档
set nocount on

输入约束

if exists(
select * from sysobjects where name='tri_student_insert2' and xtype='TR'
)
drop trigger tri_student_insert2

go
	create trigger tri_student_insert2
	on student
	for insert
	as
		declare @xb nchar(1)
		set @xb=(select ssex from inserted)
		if @xb in ('男','女')
			begin
				commit transaction
				print '插入成功'
			end
		else
			begin
				rollback transaction
				print '插入数据非法'
				print '插入失败'
			end

--测试
insert student(sno,sname,ssex) values ('888','张三','难')
select * from student

*实验（性别正确，年龄大于2000年）

--输入约束
if exists(
select * from sysobjects where name='tri_student_insert2' and xtype='TR'
)
drop trigger tri_student_insert2

go
	create trigger tri_student_insert2
	on student
	for insert
	as
		declare @xb nchar(1)
		declare @sr nchar(20)
		set @xb=(select ssex from inserted)
		set @sr=(select sbirthday from inserted)
		if @xb in ('男','女') and year(@sr)>2000
			begin
				commit transaction
				print '插入成功'
			end
		else
			begin
				rollback transaction
				print '插入数据非法'
				print '插入失败'
			end

--测试
insert student(sno,sname,ssex,sbirthday) values ('888','张三','男','2001-02-23')
select * from student

触发器——插入

触发器执行过程

1、先执行Insert命令，把数据插入到数据表中，无论数据是否符合业务规则。

2、触发器查询Insert表，检查数据的完整性，若符合业务规则，则提交；若不符合业务规则，则回滚。

触发器功能

1、用户插入数据时给予提示；

2、插入的数据进行完整性检查；

3、插入的数据不允许重复；

*实例

if exists(
select * from sysobjects where name='tri_student_insert' and xtype='TR'
)
drop trigger tri_student_insert

go
create trigger tri_student_insert
on student
for insert
as
	--定义内存变量
	declare @num int		--存储插入的记录数量
	declare @xb nchar(1)	--存储插入的记录的性别
	declare @xh int --存储插入的记录的学号
	declare @n int --存储表中学号的数量

	set @num=@@rowcount		--保存插入的记录数量
	--判断插入是否成功
	if @num>0
	begin
		set @xb=(select ssex from inserted)		--到inserted表中查询插入记录的性别
		set @xh=(select sno from inserted)		--到inserted表中查询插入记录的学号
		 --select @xh=sno,@xb=ssex from inserted
		select @n=count(sno) from student where sno=@xh
		if @xb in ('男','女') and @n=1
			begin
				Commit Transaction  --提交业务
				print '插入成功'    --给用户提示
			end
		else
			begin
				Rollback Transaction    --不符合业务规则，回滚事务
				print '插入数据不符合业务规则'
				print '插入失败'
			end
	end

--测试
--插入的记录有可能违反数据的完整性，如插入的性别字段输入了“难”
insert student(sno,sname,ssex) values ('888','张三','难')
select * from student
delete from student where sno='888'
--插入的记录有可能重复，如重复插入了“张三”的数据
insert student(sno,sname,ssex) values ('888','张三','男')

触发器——删除

触发器功能

1、删除数据时给用户提示；

2、删除的记录自动备份；

*实例

if exists(
select * from sysobjects where name='tri_student_delete' and xtype='TR'
)
drop trigger tri_student_delete

go
create trigger tri_student_delete
on student
for delete
as
	--定义内存变量
	declare @num int		--存储插入的记录数量
	set @num=@@rowcount		--把删除记录的个数保存在@num中
	if @num>=1	--存在删除的记录
		print'删除成功'
	else		--删除的记录不存在
		print'删除失败'

	print'开始备份。。。'

	--把删除的记录插入到新表stubak中
	--第一次运行，此表不存在，需要创建此表
	if not exists(
		select * from sysobjects where name='stubak' and xtype='U')
		select * into stubak from deleted
	else
		begin
			insert into stubak select * from deleted
			--insert命令中有一个into子句，可以把查询的结果以表的形式保持起来
			--删除的记录在哪？逻辑表deleted中
			--语法格式如下：
			--insert into 新表名称 select * from 表名
			print'备份数据成功，备份表中的数据为：'
			select * from stubak	--为啥没显示提示信息？在结果中显示

			print'备份完成'
		end

--测试
select * from student
select * from stubak
delete from student where sno='999'	--删除的记录不存在
--因为删除的记录不存在，系统没有删除成功，所以触发器代码不会执行
delete from student where sno='888'	--删除的记录存在
insert student(sno,sname,ssex) values ('888','张三','男')