SQLserver数据库创表、操作表以及存储过程、游标等操作

--创建数据库表以及插入数据    15:40:34

USE master
go
if exists(select * from sysdatabases where name='EmployeeSys')
drop database EmployeeSys
go

CREATE DATABASE EmployeeSys
 ON 
(
  NAME = ' EmployeeSys_data',                             --主数据文件的逻辑名
  FILENAME = 'D:\EmployeeSys_data.mdf' ,  --主数据文件的物理名
  SIZE = 10 MB,  --主数据文件初始大小
  FILEGROWTH = 20 % 
)                                    
 LOG ON  
(
  NAME = 'EmployeeSys_log', 
  FILENAME = 'D:\EmployeeSys_log.ldf' ,
  SIZE = 3MB, 
  MAXSIZE = 20MB,
  FILEGROWTH = 1MB
)
GO

USE EmployeeSys
go

if exists(select * from sysobjects where name='Employee')
drop table Employee
go
create table Employee
(
    EmpId int identity(1,1) primary key not null,
    EmpName varchar(50) not null,
    Age int not null,
    Sex char(2) not null,
    AddTime datetime not null,
    Wage money not null,
    DepId int not null,
    State int not null 
)

if exists(select * from sysobjects where name='Department')
drop table Department
go
create table Department
(
    DepId int identity(1,1) primary key not null,
    DepName varchar(50) not null
)

if exists(select * from sysobjects where name='Users')
drop table Users
go
create table Users
(
    Uid int identity(1,1) primary key not null,
    UName varchar(50) not null,
    UPwd varchar(50) not null
)

ALTER TABLE Employee --默认约束
ADD CONSTRAINT DF_State DEFAULT ('未删除') FOR State

ALTER TABLE Employee --添加外键约束
ADD CONSTRAINT FK_Department          
    FOREIGN KEY(DepId) REFERENCES Department(DepId)

insert into Department values('人事部');
insert into Department values('产品开发部');
insert into Department values('开发部');
insert into Department values('经理部');
insert into Department values('空运部');

insert into Employee values('张三',23,'男','2011-9-1',5000,1,0);
insert into Employee values('李四',29,'男','2010-6-1',7000,2,1);
insert into Employee values('王五',15,'男','2012-5-1',6000,3,0);
insert into Employee values('李丽',25,'女','2000-3-1',2000,4,1);
insert into Employee values('李宁',18,'女','2011-10-1',4000,5,1);

insert into Users values('admin','admin');


select * from Employee;
select * from Department;
select * from Users;

--数据库表的基本增删改查操作

select * from Department;
select * from Employee;
select * from Users;
select * from student;

--问题:当 IDENTITY_INSERT 设置为 OFF 时，不能为表 'student' 中的标识列插入显式值。
--解决:其中student为表名。意思是允许将显示值插入到标识列中。
set identity_insert student on;    

--增
insert into Department(DepName) values('');                                    --插入单行数据  insert into 表名(列名) values (列值)
select EmpId,EmpName into student from Employee;                            --直接拿现有表数据创建一个新表并填充  select 新建表列名 into 新建表名 from 原表名
insert into student(EmpId,EmpName) select Uid,UName from Users;                --将现有表数据添加到一个已有表    insert into 已有的新表(列名) select 原表列名 from 原表名
insert student(EmpId,EmpName) select '11','tom' union select '12','like';    --使用union关键字合并数据进行插入多行    insert 表名(列名) select 列值 union select 列值

--删
drop table student;                        --删除该表
truncate table student;                    --注意：删除表数据，但表的结构、列、约束、索引等不会被删除；不能用于有外建约束引用的表  truncate table <表名>
delete from Department where DepId=8;    --删除<满足条件的>行 delete from 表名 where 删除条件

--改
update Department set DepName='空姐部' where DepId=5;                --根据条件修改表数据  update <表名> set <列名=更新值> [where <更新条件>]

--查
select * from Employee where EmpName='李四';                            --精确查询
select * from Employee where EmpName like '%李%' order by Age asc;    --使用like进行模糊查询  desc降序 asc升序
select * from Department where DepName is null;                        --查询null行
select * from Department where DepName is not null;                    --查询非null的行
select * from Employee where EmpId between 1 and 3;                    --使用between在某个范围内进行查询 1-3条数据
select * from Employee where Age in('23','15');                        --in查询  查询表Employee中age为23和15的数据
select EmpId as ID,EmpName as 姓名 from Employee;                    --在查询中使用ＡＳ更改列名
select EmpId,'1' as ID2 from Employee;                                --在查询中使用常量    查询表Employee，显示EmpId列，并添加地址列为ID2，其列值都为'1'
select sex as 性别,AVG(Age) as 平均年龄 from Employee group by sex;    --使用group by进行分组查询 在表Employee中查询，按sex字段分组
select sex as 性别,AVG(Age) as 平均年龄 from Employee group by Sex having COUNT(Age)>1;    --使用having子句进行分组筛选 显示分组后count(Age)>1的行，由于where只能在没有分组时使用，分组后只能使用having来限制条件。
select top 3 * from Employee;                                        --查询前3行的所有数据
select top 3 percent * from Employee;                                --查询该表3%的数据，percent为关键字
select d.DepName,e.EmpName from Department d,Employee e where d.DepId=e.DepId;          --多表连接查询
select e.EmpName,d.DepName from Employee e inner join Department d on e.DepId=d.DepId;--内连接 如果表中有至少一个匹配，则返回行  select * from 表1 inner join 表2 on 表1.id=表2.id
select * from Employee e left join Department d on e.DepId=d.DepId;                      --左连接    即使右表中没有匹配，也从左表返回所有的行    left join
select * from Employee e right join Department d on e.DepId=d.DepId;                  --右连接    即使左表中没有匹配，也从右表返回所有的行    right join
select * from Employee e full join Department d on e.DepId=d.DepId;                      --完全连接 只要其中一个表中存在匹配，则返回行        full join
select * from Department where DepId in(select DepId from Employee where Sex='男');      --子查询
select top 2 * from Employee where EmpId not in (select top 2 EmpId from Employee order by Age asc)order by EmpId--分页 查询根据Age排序后3、4条数据
select EmpName from student group by EmpName having COUNT(EmpName)>1;--查询EmpName字段记录重复1条以上的数据
--查询EmpName字段记录重复1条以上的数据,并且删除EmpId最大的那个数据  (Min删除EmpId最大的数据,Max删除EmpId最小的数据)
delete from student where EmpName in(select EmpName from student group by EmpName having COUNT(EmpName)>1) 
    and EmpId not in(select Min(EmpId) from student group by EmpName having COUNT(EmpName)>1)

--内外连接定义
--SQL内链接：将2张表按照on的条件，返回公共部分
--SQL外连接: 包含左链接和右连接

--INNER JOIN：如果表中有至少一个匹配，则返回行
--LEFT JOIN：即使右表中没有匹配，也从左表返回所有的行
--RIGHT JOIN：即使左表中没有匹配，也从右表返回所有的行
--FULL JOIN：只要其中一个表中存在匹配，则返回行

--存储过程、游标以及临时表等操作

select * from Department;
select * from Employee;
select * from Users;
select * from student;

--存储过程
--===============================================================================================
--简单赋值
declare @a int
set @a=5
print @a

--使用select语句赋值
declare @b nvarchar(50)
select @b='张三'
print @b

declare @b1 nvarchar(50)
select @b1=EmpName from student where EmpId=18
print @b1

--使用update语句赋值
declare @b2 nvarchar(50)
update student set @b2=EmpName where EmpId=18
print @b2

--===============================================================================================
--表、临时表、表变量
--删除临时表
drop table #DB_U;;
drop table #DB_U2;
drop table #DB_U3;

--创建临时表
create table #DB_U(
    [id][int]not null,
    [name][nvarchar](5)not null
);

insert into #DB_U(id,name) values(1,'tom');--向临时表插入信息
select * into #DB_U2 from #DB_U where id<8;--从#DB_U表查询数据填充到新生成的#DB_U2表
select * from #DB_U2 where id<3 union select * from #DB_U;--两临时表联合查询
insert into #DB_U select * from student;--将查询表的数据插入到临时表中
alter table #DB_U add [myid] int not null identity(1,1);--添加一个新列myid,自增长字段
alter table #DB_U add[myid1] uniqueidentifier not null default(newid());--添加一个新列myid1,默认全球唯一标识

--给查询结果集增加自增长列
select IDENTITY(int,1,1) as id into #DB_U3 from student;--无主键时
select(select SUM(1) from student where EmpId<=a.id) as myid from #DB_U a order by myid;--有主键时
--===============================================================================================
--定义表变量
declare @t table(
    id int not null,
    msg nvarchar(50) null
)
insert into @t values(1,'1');
insert into @t values(2,'2');
select * from @t


select * from #DB_U;
select * from #DB_U2;
select * from #DB_U3;
--===============================================================================================
--循环
--while循环
declare @a int
declare @sum int
set @a=1
set @sum =0
while @a<=100
begin
set @sum +=@a
set @a+=1
end
print @sum
--===============================================================================================
--条件语句
--if,else条件分支
if(1+1=2)
begin
print '对'
end
else
begin
print '错'
end

--when then条件分支
declare @today int
declare @week nvarchar(3)
set @today=3
set @week= case
when @today=1 then '星期一'
when @today=2 then '星期二'
when @today=3 then '星期三'
when @today=4 then '星期四'
when @today=5 then '星期五'
when @today=6 then '星期六'
when @today=7 then '星期日'
else '值错误'
end
print @week
--===============================================================================================
--游标
declare @EmpId int
declare @EmpName varchar (50)

--定义一个游标
declare user_cur cursor for select EmpId,EmpName from student

--打开游标
open user_cur
while @@fetch_status=0
begin
--读取游标
fetch next from user_cur into @EmpId,@EmpName
print @EmpId
end
--关闭游标
close user_cur
--摧毁游标
deallocate user_cur
--===============================================================================================
--触发器
--创建触发器
--在student上创建<strong>INSERT触发器</strong>stu_insert，
--要求在student表中插入记录时(要求每次只能插入一条记录)，这个触发器都将更新Users表中的UName列。并测试触发器stu_insert。
create trigger stu_insert
on student
for insert
as
update Users set UName=UName+1
where Uid=(select Uid from inserted)

--插入日志表
insert into student values ('嘻嘻嘻')    

--删除触发器
drop trigger stu_insert
--===============================================================================================
--存储过程
--创建带output参数的存储过程
CREATE PROCEDURE PR_Sum
@a int,
@b int,
@sum int output
AS
BEGIN
set @sum =@a+@b
END

--创建Return返回值存储过程
CREATE PROCEDURE PR_Sum2
@a int ,
@b int
AS
BEGIN
Return @a+@b
END

--执行存储过程获取output型返回值
declare @mysum int
execute PR_Sum 21,22,@mysum output
print @mysum

--执行存储过程获取Return型返回值
declare @mysum2 int
execute @mysum2= PR_Sum2 1,2
print @mysum2
--===============================================================================================
--自定义函数
--新建标量值函数
create function FUNC_Sum1(
@a int,
@b int
)
returns int
as
begin
return @a+@b
end

--调用标量值函数
declare @s int
set @s=dbo.FUNC_Sum1(100,50)
print @s

--删除标量值函数
drop function FUNC_Sum1
--------------------------------------------
--新建内联表值函数
create function FUNC_UserTab_1(
@myId int
)
returns table
as
return ( select * from student where EmpId<@myId)

--调用表值函数
select * from dbo.FUNC_UserTab_1(15)
--------------------------------------------
--新建多语句表值函数
create function FUNC_UserTab_2(
@myId int
)
returns @t table(
[EmpId] [int] NOT NULL ,
[EmpName] [nvarchar](8) NOT NULL
)
as
begin
insert into @t select * from student where EmpId<@myId
return
end

--调用多语句表值函数
select * from dbo.FUNC_UserTab_2(15)

--更改字段类型长度
ALTER TABLE 表名 ALTER COLUMN 列名 VARCHAR(255)