use Books

--------------------------------------------------------------------------------------------------------------------创建数据库 1

create database BookShop

on

(

name='BookShop.mdf',

filename='E:\Data\BookShop.mdf',

size=10mb,

maxsize=1024MB,

filegrowth =10%

)

log on

(

name='BookShop_log.ldf',

filename='E:\Data\BookShop_log.ldf'

)

use bookshop

go

------------------------------------------------------------------------------------------------------------------------创建表 2

----------一一个主键

create table Users

(

Id int identity(1,1) primary key(Id),

UName nvarchar(50) not null,

UPwd varchar(50) not null,

UDelFlag int not null,

)

go

--------------组合主键

create table Users1

(

UName nvarchar(50) not null,

UName1 nvarchar(50) not null,

primary key(UName,UName1),

UPwd varchar(50) not null,

UDelFlag int not null,

)

go

------------------------------------------------------------------------------------------------------------------------备份表 3

--------新表不存在，在复制的时候，自动创建新表

select * into newStudent from student;

--------新表存在，在复制之前，表必须建好,注意：创建的新表主键必须不是自动增长，否则报错

insert into newStudent select * from student;

--------复制表结构

select top 0,* into newstudnet  form student; --效率比下面效率高，优先使用

select * into newstudnet  form student where 1<>1;-效率低

------------------------------------------------------------------------------------------------------------------------删除表 4

--删除表中的所有数据，表还在，主键自增不变

delete from Users;

--删除表，表不存在

drop table Users;

--删除表中所有数据，主键自增重置默认值，不触发delete触发器，速度快

truncate table Users;

------------------------------------------------------------------------------------------------------------------------修改表 5

-------------------------手动（增删）一列，及修改数据类型

--增加一列

alter table Users add  URegistTime datetime;

--删除一列

alter table Users drop column URegistTme;

--修改某列的数据类型

alter table Users alter column URegistTime datetime;

--------------------------------------------------------------------------------------------------------------查询出重复的数据 6

select Name from Users group by Name having count(Name) > 1;

--------------------------------------------删除重复数据，保留一条，某个字段数据重复

--删除主键小的，保留大的

delete from Grade

where grade in

(select Grade, from Grade group by Grade having count(*)>1) and id

not in (select min(Id) from Grade group by Grade having count(Grade)>1)

--备份表的方式,删除重复数据，保留重复数据的一条，这是指的记录重复，而不是仅仅某个字段重复

select distinct * into Users1 from Users

drop table Users

----------------------------------------------------------------------------------------------------------------------增删改查 7

--插入

insert into Users( UName, UPwd,UDelFlag) values( '李四','lisi',0)

----------一次插入多条数据

insert into Score( Name, Score)

select '',110 union all

select '',120 Union all

select'',130 Union all

select '',140Union all

select '',150

--删除

delete from  Users where Id=2

--修改

update Users set UName='张三' where Id=1

-----------------------------------------------------------查询

select * from users--简单查询

----------------------------------------纵表转横表查询

select Name

,sum(case Course when '语文' then Score else 0 end) as 语文

,sum(case Course when '数学' then Score else 0 end) as 数学

,sum(case Course when '英语' then Score else 0 end) as 英语

from Test group by Name

----------------------------------------横表转纵表查询

select Name as 姓名,'语文' as 科目,Chineses as 分数 from Test1 union all

select Name as 姓名,'数学' as 科目,Math as 分数 from Test1 union all

select Name as 姓名,'英语' as 科目,English as 分数 from Test1

go

---------------------分页查询

select top 2 * from Users  where Id not in (select top (2 * 3) Id from Users order by Id) order by Id 

go

---------------------------------子查询

--独立子查询,切记：子查询的结果只能是一个值

--一个表

select * from Score where Name=(select Name from Score where Score=80 )

select * from Score where Name in(select Name from Score where Score=80 )

select * from Score where Name not in(select Name from Score where Score=80 )

--两个表

select * from Score where Name in (select Name from Grade where name='' or Name='')

select * from Score where Name not in (select Name from Grade where name='' or Name='')

--相关子查询

select * from Score as s   where  exists(select Name from Grade as g where s.Name=g.Name and  g.Name='')

select * from Score as s   where not  exists(select Name from Grade as g where s.Name=g.Name and  g.Name='')

--------------------带条件查询

--between and   已优化，效率高，优先使用； id>2 and id<4

select * from UserInfo where Id between 2 and 4

--in ;id=1 or id=2 or id=3

select * from UserInfo where Id in(1,2,3)

--------------------模糊查询(主要针对字符串操作)

--通配符：_    、   %   、  []   、  ^

--like  , not like

--只能匹配一个任意字符

select * from UserInfo where UName like '张_王';

--匹配单个字符王字的，只有一个字符

select * from UserInfo where UName like '王';

--匹配后面以王字结尾的

select * from UserInfo where UName like '%王';

--匹配前面以王字开头的

select * from UserInfo where UName like '王%';

--匹配包含王字的

select * from UserInfo where UName like '%王%';

--只能匹配一个字符 ,必须是：a-z,0-9

select * from UserInfo where UName like '[王]';

--不像

select * from UserInfo where UName like  '[^张]';

----------------------------------------------------------------------------------------------------------------------添加约束 8

--主键约束（一个主键）

alter table Users add  constraint PK_Users primary key(Id);

--主键约束(组合主键)

alter table Users add  constraint PK_Users primary key(UName,UName1);

--外键约束

alter table Users add  constraint FK_Users foreign key(UsersInfoId) references UsersInfo(UsersInfoId);

--非空约束

alter table Users  alter column UPwd varchar(50) not null ;

--唯一约束

alter table Users add  constraint UQ_Users unique(UName);

--默认约束

 alter table Users add  constraint DK_Users default(getdate()) for UTime;--时间默认值

 alter table Users add  constraint DK_Users default(0) for age;--年龄默认值

------------------------------------------------------------------------------------------------------------------分页存储过程 9

 create procedure usp_GetPage

--当前页码

@pageIndex int,

--每页条数

@pageSize int,

--总页码数

@pageCount int output

as

begin

set @pageCount=(ceiling((select count(*) from Users)*1.0/@pageSize));

select * from

(select ROW_NUMBER() over(order by Id asc) as num,* from Users)as u

where u.num

between

@pageSize*(@pageIndex-1)+1

and

@pageSize*@pageIndex

end

declare @count int

exec usp_GetPage 11,10,@count output

-------------------------------------------------------------------------------------------------------------------------排序 10

--order by 子句位于SELECT语句的末尾，带where的放在where的后面,默认是asc排序，

--可以根据多个列排序,前提是,第一个列都一样时,则会以第二个列排序

select * from UserInfo  order by Age desc

--带where

select * from UserInfo where age<20  order by Age desc

--没有出现在GROUP BY子句中的列是不能放到SELECT语句后的列名列表中的 （聚合函数中除外）

select UName from UserInfo group by UName

--having 相当于where 对分组后，但赛选的列必须是分组的列，才能进行赛选，必须放在 group by  后面

select UName from UserInfo group by UName having UName='张三'

---------------------------------------------------------------------------------------------------------------------类型转换 11

--cast 类型转换

select cast('张三' as varchar);

--转换成int,然后可以进行运算

select cast(right('',3) as int);

select cast(right('',3) as int)+1;

select cast(right('',3) as int)-1;

select cast(right('',3) as int)*5;

select cast(right('',3) as int)/5;

--convert 将日期转换成指定格式的字符串

select  convert(varchar(50),getdate(),120);

-----------------------------------------------------------------------------------------------------------------------表连接 12

--内联

select Grade,Score.Score from Grade inner join  Score on  Score.Name=Grade.Name

--左外联

select Grade,Score.Score from Grade left join  Score on  Grade.Name=Score.Name

--右外联

select Grade,Score.Score from Score right join  Grade on  Score.Name=Grade.Name

-------------------------------------------------------------------------------------------------------------------------事务 13

declare @sumError int=0 --错误

--打开事务

begin transaction

update score set score=score+1 where id=1

set @sumError=@sumError+@@ERROR

update score set score=score-1 where id=10

set @sumError=@sumError+@@ERROR

if(@sumError>0)

begin

--事务回滚

rollback transaction

end

--事务提交

commit transaction

---------------------------------------------------------------------------------------------------------------获取数据库信息 14

-------------------------------------------------------------通过视图获取数据库信息，表信息，字段信息

--字段类型：xtype=编号，如忘记可以通过   select*from syscolumns或者select * from systypes   查看

 --查看数据库中的所有类型对应的信息

 select * from systypes 

34 image

35 text

36 uniqueidentifier

48 tinyint

52 smallint

56 int

58 smalldatetime

59 real

60 money

61 datetime

62 float

98 sql_variant

99 ntext

104 bit

106 decimal

108 numeric

122 smallmoney

127 bigint

165 varbinary

167 varchar

173 binary

175 char

189 timestamp

231 sysname

231 nvarchar

239 nchar

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

--查询服务器

--远程连接数据库

select * from opendatasource('SQLOLEDB','Data Source=远程ip;User

ID=sa;Password=密码').库名.dbo.表名

--查询所有的用户

islogin=''表示帐户

islogin=''表示角色

status=''表示用户帐户

status=''表示糸统帐户

select * from sys.sysusers

 --查询出所有的数据库,status

  select * from master..sysdatabases

  --查询出所有的表,根据需求筛选;xtyep='u'

  select *from sysobjects

  --查询出去表中所有字段，根据需求筛选：id

  select*from syscolumns

  --指定的表名中的所有字段

  select * from syscolumns where id = object_id('grade')

  --主键

  select * from syscolumns where id = object_id('grade') and colstat = 1

-------------------------------------------------通过存储过程获取数据库信息，表信息，字段信息

--获取所有数据库名

exec sp_databases

--获取所有的表名,当前选中的表名

exec sp_tables

--获取表中所有字段名

exec sp_columns books

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

----------------------------------------------------------------------------------------------------------------------sql函数 15

-----------------------------------------------------聚合函数

--总数

select count(Age) as 总数 from UserInfo

--和

select 和= sum(Age) from UserInfo

--最大

select max(Age)as 最大值 from UserInfo

--最小

select min(Age) as 最小值 from UserInfo

--平均值

select avg(Age) as 平均值 from UserInfo

-------------------------------------------------------日期函数

--当前时间

select getdate();

--指定部分

select datepart(month,getdate());

--返回指定部分的===上面的

year(),month(),day(),hour(),minute(),second();

--为指定的时间进行加值，减值操作

select dateadd(MONTH,3,getdate());

--时间差

select datediff(MONTH,getdate(),getdate());

----------------------------------------------------字符串函数

--计算字符串字符个数

select len('张立平');

--计算字符串所占字节数，一个汉字两个字节

select datalength('张三王五');--不是字符串函数

--转大写

select upper('abc');

--转小写

select lower('ABC');

--去掉左边的空格

select ltrim('    abcaba');

--去掉右边的空格

select rtrim('abcaba    ');

--截取左边的一个字符串

select left('abc',1)

--截取右边的一个字符串

select right('abc',1);

--截取字符串的从指定位置开始，截取指定的字符个数，而不是字节个数

select substring('张三李四王无为',1,3);

-------------------------------------------------------------------------------------------------------------------------游标(指针) 16

declare test_cursor3 cursor global--定义

 for select TJJLID,Id from PersonalInfo--查询

open test_cursor3--打开游标

declare @tjjlid int--定义变量

declare @Id int--定义变量

while @@fetch_status=0

begin

fetch next from test_cursor3 into @tjjlid,@Id--下一行

if not exists(select HBSAG from TJJL where Id=@tjjlid)--是否存在

UPDATE  PersonalInfo SET HBSAG=3 where Id=@Id--修改

else

UPDATE  PersonalInfo SET HBSAG=(select HBSAG from TJJL where Id=@tjjlid) where Id=@Id--修改

end

close test_cursor3--关闭游标

deallocate test_cursor3--释放游标