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','[email protected]',1),

('李四',0,'15656288499','CZU','[email protected]',2),

('王五',0,'15656288597','SHU','[email protected]',3),

('刘七',1,'15656288596','BJU','[email protected]',4),

('赵八',0,'13030118598','JPU','[email protected]',1),

('钱二',0,'13022112198','WZU','[email protected]',2),

('吴一',1,'15623028590','CYU','[email protected]',3)

--***更新信息***

update studentinfo

set sname='周九',sphone='13113016396',saddress='SGU',semail='[email protected]'

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%'

--**联合查询,将两个数据表(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