語(yǔ)句注主鍵與外鍵的定義子句)

1、一、建表（create table 語(yǔ)句。注：主鍵與外鍵的定義子句）create table student(student_idchar(6)not null primary key,student_namevarchar(20)not null,classvarchar(16),sextinyint,birthdaydatetime,credittinyint,notesvarchar(50)create table course(course_idchar(3)primary key,course_namevarchar(16),termtinyint,course_timetinyin

2、t,credittinyint)create table result(student_idchar(6),course_idchar(3),gradetinyint,credittinyint,constraint pk_result primary key (student_id,course_id),Constraint fk_result foreign key (course_id) references course (course_id) )二、修改表結(jié)構(gòu)(alter table, drop table語(yǔ)句)1.在表XS中增加1個(gè)新列獎(jiǎng)學(xué)金等級(jí) ALTER TABLE XS AD

3、D 獎(jiǎng)學(xué)金等級(jí) tinyint NULL2.在表XS中刪除名為獎(jiǎng)學(xué)金等級(jí)的列 ALTER TABLE XS DROP COLUMN 獎(jiǎng)學(xué)金等級(jí)3.修改表XS中已有列的屬性 ALTER TABLE XS ALTER COLUMN 姓名 char(10)4.刪除表XS DROP TABLE XS三、修改表數(shù)據(jù)（insert,delete,update語(yǔ)句）1.向XSCJ數(shù)據(jù)庫(kù)的表XS中插入如下的一行： 001112 羅林琳 計(jì)算機(jī) 0 “1/30/1980 0:0:0” 40 INSERT INTO XS VALUES(001112,羅林琳,計(jì)算機(jī), 0 ,1/30/1980 0:0:0, 40,

4、NULL)2.將XS表中總學(xué)分小于39的行刪除 DELETE FROM XS WHERE 總學(xué)分<393.XS表中學(xué)號(hào)為001110的學(xué)生的備注列值改為“三好生” UPDATE XS SET 備注=三好生 WHERE 學(xué)號(hào)=0011104.將XS表中的所有學(xué)生的總學(xué)分都增加10 UPDATE XS SET 總學(xué)分 = 總學(xué)分+105.將姓名為“羅林琳”的同學(xué)的專業(yè)改為“通信工程”，備注改為“轉(zhuǎn)專業(yè)學(xué)習(xí)”，學(xué)號(hào)改為001241。 UPDATE XS SET 專業(yè) = 通信工程, 備注 = 轉(zhuǎn)專業(yè)學(xué)習(xí), 學(xué)號(hào) = 001241 WHERE 姓名 = 羅林琳6.對(duì)XS表進(jìn)行修改，將姓名為“李明

5、”的學(xué)生的總學(xué)分加4，備注改為“提前修完數(shù)據(jù)結(jié)構(gòu)，并獲得學(xué)分” UPDATE XS SET 總學(xué)分 = 總學(xué)分 + 4, 備注 = 提前修完數(shù)據(jù)結(jié)構(gòu)，并獲得學(xué)分 WHERE 姓名= 李明四、查詢語(yǔ)句：(select)1.常規(guī)查詢SELECT 姓名,專業(yè)名,總學(xué)分 FROM X2.帶Where子句的查詢SELECT 學(xué)號(hào),姓名,總學(xué)分FROM XSWHERE 專業(yè)名= '計(jì)算機(jī)'3.查詢所有列SELECT * FROM XS4.別名SELECT 學(xué)號(hào) AS number,姓名 AS name,總學(xué)分 AS mark FROM XS WHERE 專業(yè)名= '計(jì)算機(jī)'

6、5.別名用法之二SELECT number = 學(xué)號(hào),name = 姓名,mark = 總學(xué)分 FROM XS WHERE 專業(yè)名= '計(jì)算機(jī)'6.產(chǎn)生計(jì)算列SELECT CPMC AS '產(chǎn)品名稱',產(chǎn)品總值 = JG * KCL FROM CP7.去除重復(fù)記錄SELECT DISTINCT 專業(yè)名,總學(xué)分 FROM XS8.查出前幾條SELECT TOP 6 姓名,專業(yè)名,總學(xué)分 FROM XS9.不等號(hào)的使用SELECT * FROM CP WHERE KCL!< 50010. 多字段限制條件SELECT * FROM XS WHERE 專業(yè)名= &

7、#39;通信工程' and 總學(xué)分 >= 4211.like的使用SELECT * FROM XS WHERE 專業(yè)名 LIKE '計(jì)算機(jī)'SELECT * FROM CP WHERE CPMC LIKE '%冰箱%'SELECT * FROM XS WHERE 姓名 LIKE '王_'12.between的使用SELECT * FROM CP WHERE JG BETWEEN 2000 AND 400013.in的使用SELECT * FROM XS WHERE 專業(yè)名 IN ('計(jì)算機(jī)','通信工程

8、9;,'無(wú)線電')14.空字段的判斷SELECT * FROM XS WHERE 備注 IS NULL15.多表連接SELECT XS.* , XS_KC.* FROM XS , XS_KC WHERE XS.學(xué)號(hào) = XS_KC.學(xué)號(hào)-查找選修了206課程且成績(jī)?cè)?0分以上的學(xué)生姓名及成績(jī)SELECT 姓名 , 成績(jī) FROM XS , XS_KC WHERE XS.學(xué)號(hào) = XS_KC.學(xué)號(hào) AND 課程號(hào) ='206' AND 成績(jī) >= 80-查找選修了“計(jì)算機(jī)基礎(chǔ)”課程且成績(jī)?cè)?0分以上的學(xué)生學(xué)號(hào)、姓名、課程名及成績(jī)。SELECT XS.學(xué)號(hào),

9、姓名, 課程名, 成績(jī) FROM XS , KC , XS_KC WHERE XS.學(xué)號(hào) = XS_KC.學(xué)號(hào) AND KC.課程號(hào) = XS_KC.課程號(hào) AND 課程名 = '計(jì)算機(jī)基礎(chǔ)' AND 成績(jī) >= 80-查找XSCJ數(shù)據(jù)庫(kù)每個(gè)學(xué)生的情況以及選修的課程情況SELECT * FROM XS INNER JOIN XS_KC ON XS.學(xué)號(hào) = XS_KC.學(xué)號(hào)-查找選修了206課程且成績(jī)?cè)?0分以上的學(xué)生姓名及成績(jī)。SELECT 姓名 , 成績(jī) FROM XS JOIN XS_KC ON XS.學(xué)號(hào) = XS_KC.學(xué)號(hào) WHERE 課程號(hào) ='20

10、6' AND 成績(jī)>=8016.聚合函數(shù)-求選修101課程的學(xué)生的平均成績(jī)。SELECT AVG(成績(jī)) AS '課程101平均成績(jī)' FROM XS_KC WHERE 課程號(hào) = '101'-求選修101課程的學(xué)生的最高分和最低分。SELECT MAX(成績(jī)) AS '課程101的最高分' , MIN(成績(jī)) AS '課程101的最低分' FROM XS_KC WHERE 課程號(hào) = '101'-求學(xué)生的總?cè)藬?shù)。SELECT COUNT(*) AS '學(xué)生總數(shù)' FROM XS-求選

11、修了課程的學(xué)生總?cè)藬?shù)。SELECT COUNT(DISTINCT 學(xué)號(hào)) FROM XS_KC-將XSCJ數(shù)據(jù)庫(kù)中各專業(yè)名輸出。SELECT 專業(yè)名 FROM XS GROUP BY 專業(yè)名-求XSCJ數(shù)據(jù)庫(kù)中各專業(yè)的學(xué)生數(shù)。SELECT 專業(yè)名,COUNT(*) AS '學(xué)生數(shù)' FROM XS GROUP BY 專業(yè)名-求被選修的各門(mén)課程的平均成績(jī)和選修該課程的人數(shù)SELECT 課程號(hào), AVG(成績(jī)) AS '平均成績(jī)' ,COUNT(學(xué)號(hào)) AS '選修人數(shù)' FROM XS_KC GROUP BY 課程號(hào) -查找XSCJ數(shù)據(jù)庫(kù)中平均成績(jī)

12、在85分以上的學(xué)生的學(xué)號(hào)和平均成績(jī)。SELECT 學(xué)號(hào) , AVG(成績(jī)) AS '平均成績(jī)' FROM XS_KC GROUP BY 學(xué)號(hào) HAVING AVG(成績(jī)) > =85-查找選修課程超過(guò)2門(mén)且成績(jī)都在80分以上的學(xué)生的學(xué)號(hào)。SELECT 學(xué)號(hào) FROM XS_KC WHERE 成績(jī) >= 80 GROUP BY 學(xué)號(hào) HAVING COUNT(*) > 2'-查找通信工程專業(yè)平均成績(jī)?cè)?5分以上的學(xué)生的學(xué)號(hào)和平均成績(jī)。SELECT 學(xué)號(hào),AVG(成績(jī)) AS '平均成績(jī)FROM XS_KC WHERE 學(xué)號(hào) IN ( SELECT

13、 學(xué)號(hào) FROM XS WHERE 專業(yè)名 = '通信工程') GROUP BY 學(xué)號(hào) HAVING AVG(成績(jī)) > =8517.排序 -將通信工程專業(yè)的學(xué)生按出生時(shí)間先后排序。 SELECT * FROM XS WHERE 專業(yè)名 = '通信工程' ORDER BY 出生時(shí)間-將計(jì)算機(jī)專業(yè)學(xué)生的“計(jì)算機(jī)基礎(chǔ)”課程成績(jī)按降序排列。 SELECT 姓名,課程名,成績(jī) FROM XS,KC,XS_KC WHERE XS.學(xué)號(hào) = XS_KC.學(xué)號(hào) AND XS_KC.課程號(hào) = KC.課程號(hào) AND 課程名 = '計(jì)算機(jī)基礎(chǔ)' AND 專業(yè)

14、名= '計(jì)算機(jī)' ORDER BY 成績(jī) DESC18.用into子句創(chuàng)建新表-由XS表創(chuàng)建“計(jì)算機(jī)系學(xué)生”表，包括學(xué)號(hào)和姓名。SELECT 學(xué)號(hào),姓名 INTO計(jì)算機(jī)系學(xué)生 FROM XS WHERE 專業(yè)名= '計(jì)算機(jī)'五、創(chuàng)建視圖CREATE VIEW CS_XS AS SELECT * FROM XS WHERE 專業(yè)名 = '計(jì)算機(jī)'六、游標(biāo)的使用游標(biāo)的聲明，打開(kāi)，提取，關(guān)閉，釋放declare total_credit cursor forselect 學(xué)號(hào), total_credit from vw_total_creditopen

15、 total_creditfetch next from total_credit close total_creditdeallocate total_credit七、用SET語(yǔ)句賦值declare var1 varchar(20),var2 varchar(200)set var1='中國(guó)'set var2=var1 + '是一個(gè)偉大的國(guó)家'select var1,var2八、流程控制語(yǔ)句if.elsewhilebreakreturn九、字符串處理函數(shù)select substring(姓名,1,1),substring(姓名,2,len(姓名)-1) fro

16、m xs order by 姓名十、游標(biāo)函數(shù)cursor_rowsfetch_status十一、用戶自定義函數(shù)create function average(cnum char(20) returns intasbegindeclare aver intselect aver=avg(成績(jī)) from xs_kc where 課程號(hào)=cnumreturn averend用戶自定義函數(shù)的調(diào)用declare course1 char(20)declare aver1 intselect course1='101'select aver1=dbo.average(course1)se

17、lect aver1 as '101課程的平均成績(jī)'(測(cè)試select avg(成績(jī)) from xs_kc where 課程號(hào)='101' )十二、游標(biāo)與存儲(chǔ)過(guò)程create procedure prc_update_total_creditasdeclare number char(10),tot_credit intdeclare total_credit cursor forselect 學(xué)號(hào), total_credit from vw_total_creditopen total_creditfetch next from total_credit into number ,tot_creditupdate xs set 總學(xué)分=tot_credit where 學(xué)號(hào)=numberwhile FETCH_STATUS=0begin fetch