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