SQL server增删改查

--**创建数据库

create database test
on primary
(
    name='testsql',
    filename='E:\test1.mdf',
    size=5mb,
    maxsize=100mb,
    filegrowth=15%
)
log on 
(
    name='testlog',
    filename='E:\testlog.ldf',
    size=5mb,
    maxsize=50mb,
    filegrowth=1mb
)
use test

--**创建table
create table classinfo
(
    cid int not null primary key identity(1,1),
    ctitle nvarchar(5)
)
create table scoreinfo
(
    scid int not null primary key identity(1,1),
    score varchar(4)
)

create table studentinfo
(
    sid int not null primary key identity(1,1),
    sname nvarchar(10),
    sgender bit default(0),
    sphone char(11),
    saddress nvarchar(20),
    semail varchar(15),
    Cid int not null,
    foreign key (Cid) references classinfo(cid),
    foreign key (Cid) references scoreinfo(scid)
)
--***插入信息***
select * from classinfo 
insert into classinfo
(ctitle)
values
('1班'),('2班'),('3班'),('4班'),('5班')
select * from scoreinfo
insert into scoreinfo
(score)
values
('99'),('34'),('21'),('45'),('50'),('55'),('60'),('70'),('80'),('90'),('100')

select * from studentinfo 
insert into studentinfo
values
('张三',0,'15656282598','TLU','8530@qq.com',1),
('李四',0,'15656288499','CZU','9651@qq.com',2),
('王五',0,'15656288597','SHU','1782@qq.com',3),
('刘七',1,'15656288596','BJU','9253@qq.com',4),
('赵八',0,'13030118598','JPU','7653@qq.com',1),
('钱二',0,'13022112198','WZU','3015@qq.com',2),
('吴一',1,'15623028590','CYU','2030@qq.com',3)

--***更新信息***
update studentinfo
set sname='周九',sphone='13113016396',saddress='SGU',semail='1301@qq.com'
where sid=3

--***删除信息***
delete from studentinfo
where sid=11

--***查询信息***
select sname 
from studentinfo

--*起别名*
select sname as '姓名' ,  sgender as Gender
from studentinfo

--one 查询前N部分
select top 5 sname
from studentinfo
--one 查询前N部分
select top 5 sname , sgender
from studentinfo
--one 查询前N部分
select top 5 sname as '姓名' , sgender as Gender
from studentinfo

--two 查询前N部分
select top 20 percent *
from studentinfo
--two 查询前N部分
select top 20 percent sname as '姓名'
from studentinfo
--two 查询前N部分
select top 30 percent *
from studentinfo
where sid=3
--*排序*
select top 90 percent *
from studentinfo
order by sid ASC

select top 90 percent *
from studentinfo
order by sid DESC , semail ASC

--*消除重复行*
select distinct *
from studentinfo

--***条件查询 写在where后***
--*比较运算符*
select *
from studentinfo
where sid>5

select *
from studentinfo
where sid> 5 and sid< 10

select top 20 percent sname as '姓名' ,sid
from studentinfo
where sid != 2

--*连续型between ..and..*
select *
from studentinfo
where sid between 3 and 8 and sid !=4

--*间断型in*
select *
from studentinfo
where sid in(3,8)

--*逻辑运算符and not or*
select *
from studentinfo
where sid>2 and sid<10

select *
from studentinfo
where not sid between 3 and 8 

select *
from studentinfo
where sid between 3 and 8 or sgender=0

--*模糊查询针对字符串 like % _ ^ []*
select * 
from studentinfo
where sname like '_[^三]' and sname like '_[四]'

select *
from studentinfo
where sphone like '___130%'

select * 
from studentinfo
where semail like '%@qq%'

--***两表数据连接查询***并不需要有主外键，有相同的字段即可
select *
from studentinfo
inner join  classinfo on  studentinfo.cid=classinfo.cid
inner join scoreinfo on studentinfo.cid=scoreinfo.scid

select *
from studentinfo as si
full join  classinfo on si.cid= classinfo.cid

select si.sname,si.sphone,ci.ctitle,sci.score
from studentinfo as si
full join classinfo as ci on si.Cid=ci.cid
left join scoreinfo as sci on si.Cid=sci.scid

--***聚合函数sum，avg，count，max，min
select count(*) as count_numb
from studentinfo

select count(*)
from studentinfo
where sphone is null

select max(score)
from scoreinfo

--AVG必须是int类型
select AVG(score)
from scoreinfo
full join studentinfo as si on scoreinfo.scid=si.Cid

select MIN(score)
from scoreinfo
full join studentinfo as si on scoreinfo.scid=si.Cid

select MAX(score)
from scoreinfo
full join studentinfo as si on scoreinfo.scid=si.Cid
where sgender=0

--***聚合函数***over()通常和聚合函数,排名函数使用
--sci.score是N行，而avg(score)是一行，合并要用over()函数
select sci.score ,AVG(score) over(  order by  score ) as '平均值'
from studentinfo as si
inner join scoreinfo as sci on si.Cid=sci.scid
 

--***分组函数***group by()
select sgender,count(*)
from studentinfo
group by sgender

use test
select * from studentinfo

select sname,COUNT(*)
from studentinfo
group by sname

select sname,sphone,COUNT(*)
from studentinfo
group by sname,sphone

--*分组过后又想再进行分组having,就是从分好的数据中再摘取想要的数据*
--*但是having的条件应该是原select里的
--group by 后面的决定select后面的
select sname,sphone,count(*)
from studentinfo
where sid>4
group by sname,sphone 
having count(*)>1

select sname,sphone,count(*)
from studentinfo
where sid>4
group by sname,sphone having sphone like '130%'

/**查询总结

select distinct top 6 |percent * 
from studentinfo
inner join -- on --
left join -- on -- 
right join -- on --
where --
group by 
order by 
**/
--**联合查询,将两个数据表(or 各表的查询结果连接在新的数据集中，
--**但要保证各集中查询结果的列数和类型要保持相同)
--union、union all、except、intersect
select sid from studentinfo
union            --去掉两个数据集中重复的字段
select cid from classinfo

select sid from studentinfo
union all            --bu 去掉两个数据集中重复的字段
select cid from classinfo

select sid from studentinfo
except            --1与2交集的补集（1）
select cid from classinfo

select sid from studentinfo
intersect            --交集
select cid from classinfo

--**快速备份**新表不会包含原表的约束(主键、外键)
--select 列名 into 备份表 from 原表
select *  into test1 from studentinfo
--只要表的结构不要数据(加一个不true的where语句)
select * into test2 from classinfo where 1=2
--**快速备份**向已存在的表插入数据
--insert into 已存在备份表（列） select 列 from 原表
insert into test2(ctitle)   select sname from studentinfo
--**类型转换 cast  convert
select cast(89.0000 as decimal(4,1))
select  convert(decimal(4,1),89.000)

--**创建视图** 视图只用来储存sql语句
select * from studentinfo
inner join classinfo on studentinfo.Cid=classinfo.cid
where studentinfo.sname like '_[^三]'

create view student_class
as
select studentinfo.*,classinfo.ctitle from studentinfo
inner join classinfo on studentinfo.Cid=classinfo.cid
 

select sname from student_class

where studentinfo.sname like '_[^三]'

 

--修改alter
alter view student_class
as
select studentinfo.*,classinfo.ctitle from studentinfo
inner join classinfo on studentinfo.Cid=classinfo.cid

--删除drop
drop view student_class

--**子查询** in /exists 后者效率高
use test
select * from studentinfo
where Cid in (select cid from  classinfo)

select * from studentinfo
where exists
(select * from classinfo where studentinfo.Cid=classinfo.cid)

--*分页查询   将sid重编码为index（因为sid可能会丢失跳段，如删除）
select * from 
(select *,ROW_NUMBER() over(order by sid desc) as rowindex 
from studentinfo) as t1
where rowindex between 2 and 7