server新手引导 sql_sql新手全套

--【数据库】

go

create database DB_MGG  --添加数据库

on

(

name=DB_MGG --逻辑名称

,filename='C:\MDB\DB_MGG.mdf' --物理名称  逻辑名和物理名可以不一样

,size=3MB --大小

,filegrowth=1MB --增长多少

,maxsize=100MB --最大大小

),

filegroup DB_group --文件组

(

name=DB_MGN1

,filename='D:\NDB\DB_MGN1.ndf'

,size=3MB

,filegrowth=1MB

,maxsize=100MB

)

log on

(

name=DB_MGL

,filename='F:\LDB\DB_MGL.ldf'

,size=1MB

,filegrowth=10%

,maxsize=10MB

)

go

use DB_MGG

go

--更改_添加一个新文件

alter database DB_MGG

add file

(

name=DB_MGN2

,filename='E:\NDB\DB_MGN2.ndf'

,size=3MB

,filegrowth=1MB

,maxsize=100MB

)

--更改_删除一个数据库文件

alter database DB_MGG

remove file DB_MGN2

--更改_新建一个文件组

alter database DB_MGG

add filegroup DB_group

--更改_添加一个新文件 到文件组

alter database DB_MGG

add file

(

name=DB_MGN2

,filename='E:\NDB\DB_MGN2.ndf'

,size=3MB

,filegrowth=1MB

,maxsize=100MB

)to filegroup DB_group --放到文件组里

--更改_添加一个日志文件

alter database DB_MGG

add log file

(

name=DB_MGL_T

,filename='F:\LDB\DB_MGL_T.ldf'

,size=1MB

,filegrowth=10%

,maxsize=10MB

)

--更改_更改一个数据库文件

alter database DB_MGG

modify file

(

name=DB_MGN1

,filename='D:\NDB\DB_MGN1.ndf'

,size=5

,filegrowth=10%

,maxsize=50MB

)

--更改_更改数据库名称

alter database DB_MGG

modify name=DB_mgg

--删除文件组

alter database DB_MGG

remove filegroup DB_group --ps:文件组里有文件不能删除

use master

drop database DB_MGG  --删除数据库

-----------------------------------------------

--【数据表】

use DB_MGG

create table TB1_student --创建数据表

(

id int,

name nchar(4),

--ps:char 固定大小查询方便; nchar 无论中文和英文用unicode编码统一占2个字节;varchar 大小会变储存小

birthday date,

mark decimal(4,1)

)

--更改 添加一列数据表字段

alter table TB1_student

add sex nchar(1)

--更改 修改一列数据表字段

alter table TB1_student

alter column sex char(2)

--更改 删除一列数据表字段

alter table TB1_student

drop column sex

--更改 修改名称

go

sp_rename 'TB1_student',TB_cjb --修改表名

go

sp_rename 'TB1_student.id',ID --修改字段名

go

-----------------------------------------

--【数据表内容】

--添加数据

insert into TB1_student values(1,'mzmz','1995-1-1',99,'男')

insert into TB1_student values(2,'mzmz','1997/2/2',77,'女')

insert into TB1_student values(3,'mmmm','1991.3.3',55,'男'),(4,'maaz','1999 4 4',66,'女')

--连续添加多行数据 ps:数据间用，隔开 日期型用. /或-隔开

select * from TB1_student --查询

update TB1_student set name='名字名字' where id=1 --更改数据

delete  from TB1_student --删除数据表内容

drop table TB1_student --删除数据表

--添加 insert into 表名 values (值1,值2)

--查询 select COUNT(*) 统计[统计=count(*)/count as 统计] from 表名 order by [desc/asc(默认升序)]

--子查询select 字段 from 表名 where 字段=(select max(字段)  from 表名)

--复制数据表 select * into 新的表名[不需要预先创建该表] from 要复制的表名[已有的表]   只拷贝数据

--更新 update 表名 set 字段=内容 [where 条件表达式(字段=值)]  若没有where条件,那么全都会更改

--删除 delete from 表名 [where 条件表达式(字段[=/is 备注:null要用is/like/>/

--     删除的是记录,逐条删除，可恢复

--删除 truncate 每个block删除 速度快 但不能逐条恢复

--分组 group by  --去前n个 top n

--去重复 distinct

-- null是空值 是个空盒子 不知道里面有什么

--like 模糊查询%表示一个或多个 _表示单个

--查询范围 between 数值 and 数值 包涵头尾数值

--count 统计 max 最大 min最小 avg 平均值 sum 和

--select * from  where group by having

--联合查询

create database db_xs

use db_xs

create table tb_xs

(

id char(8) not null

,name char(8)

,constraint pk_id primary key(id)

)

insert into tb_xs values ('00000001','林艺')

insert into tb_xs values ('00000002','灵儿')

insert into tb_xs values ('00000003','凌珊')

insert into tb_xs values ('00000004','零时')

create table tb_kc

(

kcid char(2) not null

,kcname char(20)

,constraint pk_kcid primary key(kcid)

)

insert into tb_kc values ('01','林艺语文')

insert into tb_kc values ('02','灵儿数学')

insert into tb_kc values ('03','凌珊英语')

insert into tb_kc values ('04','零时美术')

create table tb_xk

(

id char(8)foreign key references tb_xs(id)

,kcid char(2) foreign key references tb_kc(kcid)

,xksj datetime default(getdate())

,constraint pk_zhid primary key(id,kcid)

)

insert into tb_xk(id,kcid) values('00000001','01')

insert into tb_xk(id,kcid) values('00000001','02')

insert into tb_xk(id,kcid) values('00000002','02')

insert into tb_xk(id,kcid) values('00000003','01')

insert into tb_xk(id,kcid) values('00000004','03')

select * from tb_xs

select * from tb_kc

select * from tb_xk

select tb_xs.id,tb_xs.name,tb_kc.kcid,tb_kc.kcname from tb_xs,tb_kc ,tb_xk

where tb_xs.id = tb_xk.id and tb_kc.kcid=tb_xk.kcid and tb_xk.id='00000001'

----------------------------------

--【学生表】

--

-- primary key 主键 not null 非空 default 默认 unique 唯一性 check 检查  identity(初始值,增长值) 标识符

create database mgg

use mgg

drop table Tb_xs

create table Tb_xs

(

id char(8) primary key --主键 唯一可以区分不同记录的字段 只能有一个 不能为空

,name char(8) not null

,gender char(2) default('男') check(gender in ('男','女','x'))

,mobile char(11) unique check(len(mobile)=11 and mobile like '1[3,5,7,8][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]')

--,mobile char(11) unique check(mobile like '[0-9]{11}')

)

unique--unique 唯一性可以多个，可以出现空值，但空值只能出现一次

in--in 必须是这三个中的一个

like--like 正则表达式

len--函数 len(字段名或变量)结果给出长度(字符串的个数)

insert into Tb_xs values('1','pite','女','15345678910')

insert into Tb_xs values('2','tom','男','13345678920')

insert into Tb_xs(id,name) values('3','didi')

insert into Tb_xs(id,name,gender,mobile) values('4','didi','女','18181818181')

delete from Tb_xs

select * from Tb_xs

------------------------------

--【学生信息】

create database bb

go

use bb

go

--drop table Tb_xsxx

create table Tb_xsxx

(

xsid char(10) primary key,--学生学号

xsname char(10) not null,--学生姓名

)

insert into Tb_xsxx values('201401001','pig')

insert into Tb_xsxx values('201401002','god')

---------------------

--【课程信息】

use bb

--drop table Tb_xskc

create table Tb_xskc

(

cid char(2) primary key,--课程编号

cname char(20) not null,--编程名称

)

insert into Tb_xskc values ('01','sql数据库')

insert into Tb_xskc values ('02','web前端')

-------------------------------

--【课程成绩】

use bb

--drop table Tb_xscj

create table Tb_xscj

(   --foreign key 可以省略,正规写法要加上

xsid char(9) foreign key references Tb_xsxx(xsid) --on update cascade on delete 为学生学号创建外键

,cid char(2),--课程编号

xsmark decimal(4,1)--学生成绩

primary key(xsid,cid)--组合主键

)

insert into Tb_xscj values ('201401001','01',95.5)

insert into Tb_xscj values ('201401002','02',100)

insert into Tb_xscj values ('201401001','02',100)

select xsid as 学生学号,xsname as 学生姓名 from Tb_xsxx

select cid as 课程编号,cname as 课程名称 from Tb_xskc

select xsid as 学生学号,cid as 课程编号,xsmark as 学生成绩 from Tb_xscj

--外键所引用的必须为主键

create database abc

use abc

create table a

(

a1 varchar(100) primary key

,a2 varchar(100)

)

create table b

(

b1 varchar(100) primary key

,b2 varchar(100) foreign key references a(a1/*a1必须为主键或唯一性(唯一性不规范)*/)-- on update cascade on delete

,b3 varchar(100)

)

----------------------------------------

--【修改约束】

create database DB_XS

use db_xs

drop table tb_xsxx

create table tb_xsxx

(

id int

,name char(8)

,sr date

,constraint /*constraint 约束*/ pk_id primary key(id)

,constraint ck_name check(len(name)>=2)

,constraint ck_sr check(sr>'1990-1-1')

--,constraint df_sr default('1990-1-1')----确定添加不了

)

--drop 删除

alter table tb_xsxx drop constraint ck_name

alter table tb_xsxx drop constraint pk_id

--add 添加

alter table tb_xsxx

add constraint ck_name check(len(name)>=2 and len(name)<=6)

alter table ta_xsxx

alter column c1 int not null

alter table ta_xsxx

add constraint pk_id primary key (id)

/*创建视图来自多个基表，若有相同字段则要精确定义*/

/*如果视图创建来自单个基表，那么通过视图

可以操作(查询、增加、删除、更改)基表*/

/*如果视图创建来自多个基表，那么通过视图

操作(查询、增加、删除、更改)只能其中的一个基表，

不可以同时操作的字段来自多个基表*/

--声明和定义

--(1)系统的全局变,@@开头,只能用不能改,用户不能定义

select @@CONNECTIONS

select @@ROWCOUNT--影响到的行数

--(2)用户自定义变量 @开头

declare @strfirst varchar(50)

set @strfirst ='helle,sql'--select或者set赋值 set 不能一次赋多个值

print @strfirst

select @@ROWCOUNT

go

declare @iNum1 int, @iNum2 int,@@myint int

select @iNum1=5,@iNum2=6

set @@myint=8

print @iNum1+@iNum2+@@myint

go

-----顺序 选择 循环

/*

if(表达式)

begin

end

else

begin

end

*/

declare @x int

set @x=5

if(@x<0)

begin

print('小于0')

end

else if(@x=0)

begin

print('0')

end

else

begin

print('大于0')

end

--------创建自定义函数--------------------

----格式

create function 函数名

(形参 as 数据类型,.....)

returns 返回值的数据类型

begin

end

----阶乘实例

----显示每一个

go

declare @i int,@n int,@sum decimal(38,0)

select @i=1 ,@n=10,@sum=1

while(@i<=@n)

begin

set @sum=@sum*@i

print  @sum

set @i+=1

end

----显示最后一个

go

declare @n int,@sum decimal(38,0)

select @n=10,@sum=1

while(0

begin

set @sum=@sum*@n

set @n-=1

end

print  @sum

-----阶乘函数实例

go

create function jiechen

(@m as int)

returns bigint

begin

declare @i int,@n int,@sum decimal(38,0)

select @i=1 ,@n=@m,@sum=1

while(@i<=@n)

begin

set @sum=@sum*@i

set @i+=1

end

return @sum

end

go

print dbo.jiechen(10)

----函数

/*

getdate()--获取当前日期

substring(str,i,n)--取几个开始

charindex(str1,str2)--查询str2在str1里的第几个

cast(表达式 as 数据类型)--强制转换

convert(数据类型,表达式)--强制转换

ltrim(字段) --从左边开始去空格

rtrim(字段) --从右边开始去空格

replace(字段,' ','')--替换

*/

select ROUND(RAND()*10+1,0)--随机

case 字段

when 值 then 值

else 值

end

--------【索引】---------

--聚集索引只能有一个，因为一个基本表只能按照一个字段排序

--一般用在那些字段？经常用来查询  主键 外键 经常用于连接的字段

------创建索引

create clustered index 索引表名 on 表格(字段)

create clustered index sy_sy on tb_sy(mz)

------删除索引

alter table tb_sy

drop constraint PK__tb_sy__32167A847F60ED59

-----【存储过程】

--优点： 对数据库立即访问的功能，可以加快程序的运行速度，实现模块化，

------可以减少网络流程，有利加密保护代码的安全性,可以提高数据库的安全性

-----创建存储过程

/*

create procedure 过程名称

参数=null

as

*/

use Northwind

go

create procedure up_getProductname

@pid int

as

select productname from Products where ProductId=@pid

go

--在一个go区域中如果是首行直接用存储过程名称执行，如果是非首行，存储过程名称前+exec

exec up_getproductname 2

go

---删除存储过程

drop procedure  up_getproductname

go

---可为空的参数

create procedure up_getProductnamenull

@pid int=null

as

if(@pid is null)

select 'id不能为空'

else

select productname from Products where ProductId=@pid

go

exec up_getproductnamenull

-----有输出值的存储过程

go

create procedure up_getProductnameoutput

@pid int=null,@productCount int output

as

begin

if(@pid is null)

select 'id不能为空'

else

select productname from Products where ProductId=@pid

set @productCount=(select COUNT(*) from Products)

end

go

declare @pcount int

exec up_getProductnameoutput 1,@pcount output

select @pcount

if OBJECT_ID('Northwind.dbo.up_getProductnameoutput','p') is not null

------【触发器】    trigger    书本P214

--概念 它是一种特殊类型的存储过程，不能直接调用 要通过时间 只能在当前表创建

--优点 它是自动执行  可以通过数据库中的相关表进行级联更改 触发器可以强制限制 可以引用其他表中的列

create database xg

use xg

create table cfq(c1 int)

insert into cfq values(1)

select * from cfq

/*

create trigger trigger_name

on{table|view}

[with encryption]

{

after insert|instead of    insert

as

}

*/

drop trigger tri_cfq

go

create trigger tri_cfqin

on cfq

after insert

as

print '一条新的数据写入'

go

create trigger tri_cfqup

on cfq

after update

as

print '一条新的数据更新'

go

create trigger tri_cfqdel

on cfq

after delete

as

print '一条新的数据删除'

go

----SQL Server触发器判断当前操作类型(insert/update/delete)

create trigger tri_cfq

on cfq

after insert,update,delete

as

begin

declare @inserted int, @deleted int

select @inserted=COUNT(*) from inserted --刚插入的数据所在表

select @deleted=COUNT(*) from deleted    --刚删除的数据所在表

if @inserted>0 and @deleted=0

begin

print '一条新的数据写入'

end

if @inserted>0 and @deleted>0

begin

print '一条新的数据更新'

end

if @inserted=0 and @deleted>0

begin

print '一条新的数据删除'

end

end

go

insert into cfq values(4)

update cfq set c1=3 where c1=4

delete from cfq where c1=2

select * from cfq

go

create table tb1_user

(

xh int,

cj decimal(4,1)

)

drop trigger cfq_xs

go

create trigger  cfq_xs

on tb1_user

after insert

as

begin

declare @cj int,@xh int

select @cj= cj from inserted

select @xh=xh from inserted

if @cj>100 or @cj<0

begin

print '不能打印'

delete from tb1_user where cj>100 or cj<0

end

end

go

create trigger cfq_xsdel

on tb1_user

instead of delete

as

print '就是不让你删哦'

go

go

insert into tb1_user values (1,1)

insert into tb1_user values(1,102),(2,100),(3,111)

update tb1_user set cj=22 where xh=1

delete from tb1_user

select * from tb1_user

-----【以下都是登录用户】(可以登录到引擎上即数据库服务器，此时还不能使用数据库)

--window身份验证模式的登录帐号创建

create login "ClOUD\201408225" from windows

--sqlserver身份验证 登录帐号创建

drop login aiai

create login aiai with password='123',check_policy=off

use DB_StudentManager

go

drop user db_aiai

create user db_aiai for login aiai with default_schema=dbo

--授权 aiai 可以创建表 grant

grant create table to db_aiai

grant select to db_aiai

grant insert to db_aiai

grant update to db_aiai

grant delete to db_aiai

--拒绝权限 deny

deny delete to db_aiai

--创建架构

create schema schema_aiai authorization db_aiai

SQL 技巧

--字符串去连续空格

select string = replace(replace(replace(' select single spaces',' ','<>'),'>',' ')