sqlserver基础篇

use JDBCTest;

--创建t_user表,并设置主键自增长identity(1,1)
create table t_user(
    id int primary key not null identity(1,1),
    username varchar(20) not null,
    password varchar(20) not null
);

create table t_userinfo(
    userid int primary key not null identity(1,1),
    userName varchar(20) not null,
    userPwd varchar(20) not null,
    sex char(1),
    age int,
    address varchar(100),
    hobby varchar(100) 
);

--插入单条信息
insert into t_user(username,password) values ('ruige','123456');
insert into t_userinfo(userName,userPwd,sex,age,address,hobby) values ('ruige','123456','1',18,'广东汕头','足球');

select * from t_user;

select * from t_userinfo;

--批量插入信息
insert into t_user(username,password) values 
('test1','123456'),
('test2','123456'),
('test3','123456');

insert into t_user values ('test11','123456');

--查询前3条信息
select top 3 id,username,password from t_user;

--查询第1条信息到第3条信息
select top 3 id,username,password from t_user where id not in (select top 0 id from t_user);

--查询第2条到第5条信息
select top (5-2+1) id,username,password from t_user  
where id not in (  
  select top (2-1) id from t_user  
)

--去除重复项
select distinct username,password from t_user;
select distinct password from t_user;

--利用order by 关键字对数据进行分组,并进行递减排序
select * from t_user order by id desc;
select * from t_user order by id desc,password asc;

insert into t_user(username,password) values 
('test4','12312'),
('testUser5','1234545'),
('ghhs6','123452'),
('tedsj','12345'),
('tesd','1236566'),
('tesads','12344545');

select * from t_user;

--根据条件修改信息
update t_user set password = 'ghxjdcha' where id = 3;
select * from t_user where id = 3;

--根据条件删除信息
--删除单条信息
delete from t_user where id = 3;
--批量删除信息
delete from t_user where id in (select id from t_user where id >10);
--清空数据
delete from t_user;

--模糊查询
select * from t_user where username like '%e%';
--别名查询
select a.username,b.username from t_user a,t_user b;

--创建t_course表
create table t_course(
id int primary key not null identity(1,1),
number int not null,
coursename varchar(20) not null,
userid int not null
);

--设置t_course的userid作为t_user的外键
alter table t_course add foreign key (userid) references t_user (id);
insert into t_course values
(001,'java',18),
(002,'c语言',20),
(003,'Python',26);

select * from t_course;

--连接查询
--join内连接
select a.username as 学生姓名 ,a.password as 密码 ,b.coursename as 课程名 from t_user a,t_course b where a.id = b.userid; 
select a.username as 学生姓名 ,a.password as 密码 ,b.coursename as 课程名 from t_user a inner join t_course b on a.id = b.userid;

--左外连接
select a.id,a.username as 学生姓名 ,a.password as 密码 ,b.coursename as 课程名 from t_user a left join t_course b on a.id = b.userid;
select a.id,a.username as 学生姓名 ,a.password as 密码 ,b.coursename as 课程名 from t_user a left outer join t_course b on a.id = b.userid;

--右外连接
select a.id,a.username as 学生姓名 ,a.password as 密码 ,b.coursename as 课程名 from t_course b right join t_user a  on a.id = b.userid;
select a.id,a.username as 学生姓名 ,a.password as 密码 ,b.coursename as 课程名 from t_course b right outer join t_user a  on a.id = b.userid;

--全外连接
select a.username as 学生姓名 ,a.password as 密码 ,b.coursename as 课程名 from t_user a full outer join t_course b on a.id = b.userid;

--联合查询
--不允许重复
select a.id,a.coursename from t_course a union
select b.id,b.username from t_user b;
--允许重复
select a.id,a.coursename from t_course a union all
select b.id,b.username from t_user b;

--将t_user表中的所有数据copy到t_user_copy中
select * into t_user_copy from t_user;
select a.id,a.username into t_user_copy1 from t_user a;
select * from t_user_copy1;

select * from t_user_copy;

--将数据插入存在的数据库
delete from t_user_copy1;
set IDENTITY_INSERT t_user_copy1 on
insert into t_user_copy1(id,username) select a.id,a.username from t_user a;
set IDENTITY_INSERT t_user_copy1 off
set IDENTITY_INSERT t_user off

--创建一个索引
create index index_t_user_copy on t_user(id);
drop index index_t_user_copy on t_user;

--创建一个唯一非聚集的索引
create unique index index_t_user_copy_unique on t_user(id);
drop index index_t_user_copy_unique on t_user;

--格式化时间
--2018/01/04
select convert(varchar(30),GETDATE(),111) as date;

--01  4 2018  5:44PM \  01  4 2018  5:44PM
select convert(varchar(30),GETDATE(),0) as date;
select convert(varchar(30),GETDATE(),100) as date;
--01/04/18  \  01/04/2018
select convert(varchar(30),GETDATE(),1) as date;
select convert(varchar(30),GETDATE(),101) as date;
--18.01.04  \  2018.01.04
select convert(varchar(30),GETDATE(),2) as date;
select convert(varchar(30),GETDATE(),102) as date;
--04/01/18  \  04/01/2018
select convert(varchar(30),GETDATE(),3) as date;
select convert(varchar(30),GETDATE(),103) as date;
--04.01.18  \  04.01.2018
select convert(varchar(30),GETDATE(),4) as date;
select convert(varchar(30),GETDATE(),104) as date;
--04-01-18  \  04-01-2018
select convert(varchar(30),GETDATE(),5) as date;
select convert(varchar(30),GETDATE(),105) as date;
--04 01 18  \  04 01 2018
select convert(varchar(30),GETDATE(),6) as date;
select convert(varchar(30),GETDATE(),106) as date;
--01 04, 18 \  01 04, 2018
select convert(varchar(30),GETDATE(),7) as date;
select convert(varchar(30),GETDATE(),107) as date;
--17:32:58  \  17:51:18
select convert(varchar(30),GETDATE(),8) as date;
select convert(varchar(30),GETDATE(),108) as date;
--01  4 2018  5:27:20:947PM \ 01  4 2018  5:51:59:760PM
select convert(varchar(30),GETDATE(),9) as date;
select convert(varchar(30),GETDATE(),109) as date;
--01-04-18  \ 01-04-2018
select convert(varchar(30),GETDATE(),10) as date;
select convert(varchar(30),GETDATE(),110) as date;
--18/01/04  \ 2018/01/04
select convert(varchar(30),GETDATE(),11) as date;
select convert(varchar(30),GETDATE(),111) as date;
--180104   \  20180104
select convert(varchar(30),GETDATE(),12) as date;
select convert(varchar(30),GETDATE(),112) as date;
--04 01 2018 17:37:44:543  \  04 01 2018 17:53:54:063
select convert(varchar(30),GETDATE(),13) as date;
select convert(varchar(30),GETDATE(),113) as date;
--17:38:18:907  \  17:54:19:740
select convert(varchar(30),GETDATE(),14) as date;
select convert(varchar(30),GETDATE(),114) as date;

create view userinfo 
as 
select a.username as 学生姓名 ,a.password as 密码 ,b.coursename as 课程名 from t_user a left join t_course b on a.id = b.userid;
drop view userinfo;

select * from userinfo;

--创建存储过程
--有输入的存储过程
create proc GetUser
(@userid int)
as 
select * from t_user where id = @userid;
--调用
exec GetUser 18;
--删除存储过程
drop procedure GetUser;

--有输入与输出参数的存储过程
create proc GetUserCount
@username varchar(20),
@count int output
as 
select @count = COUNT(*) from t_user where username = @username;
--调用
declare @count int
exec GetUserCount test1,@count output;
print @count;
--删除存储过程
drop procedure GetUserCount;

--返回单个值的标量值函数
create function myfunction
(@username varchar(20))
returns int 
as 
begin
declare @count int 
select @count = COUNT(*) from t_user where username = @username
return @count
end
--调用函数
declare @count int
exec @count = myfunction test1
print @count
--删除
drop function myfunction;

--返回值为表的函数
create function GetFunctionTable
(@id int)
returns table 
as
return 
(select * from t_user where id = @id );
--调用函数
select * from GetFunctionTable(18);
--删除
drop function GetFunctionTable;