sql知識(shí)點(diǎn)總結(jié)完整

1、 Sql 總結(jié) 1.數(shù)據(jù)模型主要有:層次模型,網(wǎng)狀模型,關(guān)系模型, 2.數(shù)據(jù)庫(kù)設(shè)計(jì)的步驟:需求分析,概念結(jié)構(gòu)設(shè)計(jì),邏輯結(jié)構(gòu)設(shè) 計(jì),數(shù)據(jù)庫(kù)物理設(shè)計(jì),數(shù)據(jù)庫(kù)實(shí)施,數(shù)據(jù)庫(kù)運(yùn)行和維護(hù)六個(gè)階段。 3.實(shí)體之間的關(guān)系:一對(duì)一、一對(duì)多、多對(duì)多。 4.數(shù)據(jù)庫(kù)文件主要有:主數(shù)據(jù)文件、次數(shù)據(jù)文件、日志文件 其中次數(shù)據(jù)文件是可選的。 -這是建庫(kù)的過(guò)程 if exists(select*from sysdatabases where name=tt drop database tt create database tt on( name=tt, filename=d:datatt.mdf, size=4mb, max

2、size=50mb, filegrowth=15% log on( name=tt1, filename=d:datatt1.ldf, size=5mb, maxsize=79mb, filegrowth=15% -這是對(duì)數(shù)據(jù)庫(kù)的修改 alter database tt modify file( name=tt1, maxsize=89mb -增加日志文件 alter database tt add log file( name=oo, filename=d:dataoo.ldf, size=5mb, maxsize=79mb, filegrowth=15% -查看數(shù)據(jù)庫(kù) sp_helpdb

3、tt 5.重要的數(shù)據(jù)類型 Int float char(size datetime varchar(size 6.在數(shù)據(jù)庫(kù)中添加表 use tt go if exists(select*from sysobjects where name=t_li drop table t_li create table t_li ( a char(4not null, b int not null, c datetime insert into t_li values(yy,78,2012-5-12 insert into t_li (a,bvalues(ttf,89 select*from t_li -新

4、建一個(gè)表,往表里添加t_li的數(shù)據(jù) create table t_ti1( a char(4not null, b int not null insert into t_ti1 select a,b from t_li -這種方法不用重建 select a,b into t_li2 from t_li select*from t_li2 6.使用union關(guān)鍵字插入多行數(shù)據(jù) -利用union一次插入多行數(shù)據(jù) insert into t_li (a,b,c selectaa,55,2012-8-12 union selectcc,54,2032-5-12 7.對(duì)數(shù)據(jù)表進(jìn)行操作 -對(duì)表的修改 al

5、ter table t_li alter column a char(8 select*from t_li -添加字段 alter table t_li add d char(9 -刪除字段 alter table t_li drop column d -表的查詢 select*from t_li 8.對(duì)字段添加約束 -添加主鍵約束應(yīng)該注意是主鍵約束字段的值不能是重復(fù)的alter table t_li add constraint pk_a primary key(a -添加外鍵約束 alter table t_li add constraint fr_b foreign key(b refe

6、rences t_li4(b -添加唯一約束 alter table t_li add constraint t_li_uq unique(a -添加默認(rèn)約束 alter table t_li add constraint t_li_df default(20for b -添加check約束 alter table t_li add constraint t_li_ck check(b between 0 and 50 -刪除約束 alter table t_li drop constraint t_li_ck 9.對(duì)于表的查詢(單表查詢 select*from Customers selec

7、t c_ID,c_Name,c_TrueName,c_Password from Customers -(查詢WebShop數(shù)據(jù)庫(kù)中會(huì)員信息表Customers中會(huì)員的編號(hào)(c_ID、 -用戶名(c_Name、真實(shí)姓名(c_TrueName、年齡(c_Age和密碼(c_Password。select c_ID,c_Name, c_Truename,year(getdate(-year(c_Birth c_Age,c_Password from Customers select會(huì)員的編號(hào)=c_ID,用戶名=c_Name,c_TrueName as真實(shí)名字,c_Password 名字from Cu

8、stomers select*from Customers where c_Type=VIP -6將VIP客戶的編號(hào)(c_id、姓名(c_name、出生日期(c_birth、籍貫(c_address、- -聯(lián)系電話(c_phone和地址(c_email顯示出來(lái)并以漢字標(biāo)題顯示列名。 select編號(hào)=c_id,姓名=c_name, c_birth as出生年月, c_address 籍貫, c_phone as聯(lián)系電話, c_email 地址from Customers where c_Type=VIP -(將湖南的VIP客戶記錄顯示出來(lái)。 select*from Customers wher

9、e c_Address=湖南株洲市AND C_Type=VIP -(將的客戶記錄顯示出來(lái)。 select*from Customers where c_Email like_x0016_3.com -(將前%的客戶記錄顯示出來(lái)。 select top 10 percent*from Customers -(將姓劉的客戶記錄顯示出來(lái)。 select*from Customers where c_TrueName like劉% -(將客戶按年齡進(jìn)行降序(由大到小排序。 select year(getdate(-year(c_Birthasc_Agefrom Customers order by y

10、ear(getdate(-year(c_Birthdesc -(將客戶按類型升序排序,如果類型相同按年齡的降序進(jìn)行排序。 select*from Customers order by c_Type,year(getdate(-year(c_birthdesc 10.對(duì)表中數(shù)據(jù)的操作 -對(duì)表中數(shù)據(jù)的操作 -修改表中的數(shù)據(jù) -把學(xué)號(hào)為的同學(xué)的名字改為xiaoxin update student set name=xiaoxin where sno=01 -刪除表中數(shù)據(jù) delete from student where sno=01 如果要?jiǎng)h除整個(gè)表中的數(shù)據(jù),還可以使用Truncate table

11、語(yǔ)句它相當(dāng)于與一個(gè)沒(méi)有where子句的delete語(yǔ)句。與delete相比,他在執(zhí)行時(shí)使用的系統(tǒng)資源和事務(wù)日志更少,執(zhí)行速度更快 例如要將圖書(shū)表中的所有數(shù)據(jù)刪除。 Truncate table books Truncate table只能刪除表中的數(shù)據(jù)行,不會(huì)刪除表結(jié)構(gòu)及各種約束。Truncate table 不能刪除具有引用關(guān)系的數(shù)據(jù)表 (引用關(guān)系是兩個(gè)表的主關(guān)鍵字和外關(guān)鍵字的數(shù)據(jù)應(yīng)對(duì)應(yīng)一致,這屬于_引用_完整性 11.sql語(yǔ)句的全稱是structure query language 12.要求一個(gè)人的年齡 year(getdate(-year(birth 13.聚合函數(shù) -計(jì)算所有會(huì)員的

12、積分之和。 select sum(upoint from customers -計(jì)算所有會(huì)員的平均積分。 select avg(upoint from customers -計(jì)算所有會(huì)員的最高積分。 select max(upoint from customers -計(jì)算所有會(huì)員的最低積分。 select min(upoint from customers -統(tǒng)計(jì)會(huì)員表中積分大于的會(huì)員個(gè)數(shù)。 select count(* from customers where upoint300 14.分組 select sex,count(sexas個(gè)數(shù) from customers group by s

13、ex select city,sex,count(sex from customers group by city,sex having count(sex2 having與where的用法一樣,但是having與group by 一塊用15.內(nèi)連接 -查找某位同學(xué)的學(xué)號(hào),姓名以及他的得分 select student.sid,sname,score from student inner join score on student.sid=score.sid select sc.sid,s.sname,sc.score from student s inner join score sc on

14、 s.sid=sc.sid select sc.score,s.sname,sc.sid from score sc inner join student s on s.sid=sc.sid -三個(gè)表的內(nèi)連接 select sc.score,s.sname,ame from score sc inner join student s on s.sid=sc.sid join course c on c.cid=sc.cid -內(nèi)連接需要進(jìn)行條件篩選,直接在后面加where既可 select sc.score,s.sname,s.sgender,sc.cid from score sc

15、inner join student s on s.sid=sc.sid where s.sgender=男 -笛卡爾乘積(交叉連接 select s.sname,sc.score from student s,score sc -查詢不滿足條件的內(nèi)連接(不等值連接結(jié)果集select sc.score,s.sname,sc.cid from score sc inner join student s on s.sidsc.sid -另一種內(nèi)連接查詢方法 -兩個(gè)表的內(nèi)連接(等值連接 select sc.score,s.sname,sc.cid from score sc,student s wh

16、ere s.sid=sc.sid -三個(gè)表的內(nèi)連接(等值連接 select sc.score,s.sname,ame from score sc,student s,course c where s.sid=sc.sid and sc.cid=c.cid select sc.score,s.sname,sc.cid from score sc,student s where s.sidsc.sid 16.外連接 -左外連接 select*from student select*from score select* from student s left outer join score

17、 sc on s.sid=sc.sid -右外連接 select s.sname,sc.cid,sc.score from student s right outer join score sc on s.sid=sc.sid -完全外連接 select s.sname,sc.score,sc.cid from score sc full outer join student s on s.sid=sc.sid -聯(lián)合查詢 select*from testtable union select*from course 17.視圖 -可以創(chuàng)建一個(gè)“熱點(diǎn)”商品的視圖。- create view vw

18、_HotGoods as select g_id as商品號(hào),g_name as商品名稱,t_id as類別號(hào),g_price as價(jià) 格,g_discount as折扣,g_number as數(shù)量 from Goods where g_status=熱點(diǎn) -查看視圖- select*from vw_HotGoods -查看生成視圖代碼- sp_helptext vw_HotGoods *【任務(wù)-2】需要了解所有訂單所訂購(gòu)的商品信息(商品名稱、購(gòu)買價(jià)格和購(gòu)買數(shù)量和訂單日期, 同時(shí)將創(chuàng)建的視圖文本加密。*/ create view vw_allorders with encryption as

19、select orders.o_id as訂單號(hào),o_date as訂單日期,g_name as商品名稱,d_price as購(gòu)買價(jià)格,d_number as購(gòu)買數(shù)量 from orders join orderdetails on orders.o_id=orderdetails.o_id join goods on orderdetails.g_id=goods.g_id -解密- alter view vw_allorders as select orders.o_id as訂單號(hào),o_date as訂單日期,g_name as商品名稱,d_price as購(gòu)買價(jià)格,d_number a

20、s購(gòu)買數(shù)量 from orders join orderdetails on orders.o_id=orderdetails.o_id join goods on orderdetails.g_id=goods.g_id 18.存儲(chǔ)過(guò)程 create proc adder num1 int, num2 int, he int output as select he=num1+num2 go declare result int exec adder 20,30,result output print str(result,3 -4創(chuàng)建一存儲(chǔ)過(guò)程up_getdetailbyname,通過(guò)數(shù)學(xué)參

21、數(shù)學(xué)生姓名 -(如“張然”,篩選出該學(xué)生的基本信息,對(duì)不存在此學(xué)生的輸入值, -必須做一檢測(cè),打印信息“不存在此學(xué)生”。 if exists(select*from sysobjects where name=up_getdetailbyname drop proc up_getdetailbyname go create proc up_getdetailbyname sname char(10 as if sname in(select姓名from student select* from student where姓名=sname else print不存在姓名為+rtrim(ltrim

22、(sname+的同學(xué)! up_getdetailbyname 李明 up_getdetailbyname 張然 19.觸發(fā)器 -向student表插入一條記錄,查看inserted表和deleted表的變化 create trigger tr_student on student for insert as select*from inserted select*from deleted -更新student表中的一條記錄,查看inserted表和deleted表的變化 create trigger tr_student_update on student for update as select*from inserted select*from deleted create trigger tr_student_delete on student for delete as select*from inserted select*from deleted inser