SQL核心知识.doc_第1页
SQL核心知识.doc_第2页
SQL核心知识.doc_第3页
SQL核心知识.doc_第4页
SQL核心知识.doc_第5页
已阅读5页,还剩9页未读, 继续免费阅读

下载本文档

版权说明:本文档由用户提供并上传,收益归属内容提供方,若内容存在侵权,请进行举报或认领

文档简介

SQL核心知识SQL主要分为三类:DCL:grant revoke commit rollbackDDL:create alter dropDML:insert delete update (select )SQL语言基础-alter alter table tableName add columnName varchar2(30); alter table tableName drop column columnName; alter table tableName modify columnName varchar2(30);-修改数据类型 alter table tableName rename column columnName to newColumnName;主表和从表主从表是一种数据关系模型,主表约束从表。以学员信息表stuInfo和考试成绩表stuMarks为例, 我们知道它们是一对多的关系,当然一是主,多是从。(譬如我们的胡主席是主,我们大家都要听主席的话!)所以stuInfo就是主表,stuMarks就是从表。那么,一般我们会在从表中做一个外键字段,引用主表中的主键。(为什么呢?大家想想是13亿人民记住主席的名字容易还是主席记住13亿人民名字呢?)这一点,已经在stuMarks表中得到了体现。那么,为了保持数据的完整性。主从表我们要尽量遵循以下原则:1、当主表中没有对应的记录时,不能将记录添加到子表 成绩表中不能出现在学员信息表中不存在的学号;2、不能更改主表中的值而导致子表中的记录孤立 把学员信息表中的学号改变了,学员成绩表中的学号也应当随之改变;3、子表存在与主表对应的记录,不能从主表中删除该行 不能把有成绩的学员删除了4、删除主表前,先删子表 先删学员成绩表、后删除学员信息表理解查询的机制 查询产生一个虚拟表,看到的是表形式显示的结果,但结果并不真正存储,每次执行查询只是先从数据表中提取数据,并按照表的形式显示出来查询语法SELECT FROM WHERE ORDER BY ASC或DESC例子:SELECT SCode,SName,SAddress FROM Students WHERE SSEX = 0 ORDER BY SCode 数据查询-基础查询全部的行和列SELECT * FROM Students 数据查询-给列取别名(3种方式)使用AS来命名列(其中as可以省略)SELECT FirstName+.+LastName AS 姓名 FROM Employees使用=来命名列SELECT 姓名 = FirstName+.+LastName FROM Employees 数据查询-查询部分行SELECT SCode,SName,SAddress FROM StudentsWHERE SAddress 河南新乡 数据查询-查询NULL值SELECT SName FROM Students WHERE SEmail IS NULL 数据查询-使用常量列SELECT 姓名=SName,地址= SAddress,河北新龙 AS 学校名称FROM Students 数据查询-限制行数1. 限制固定行数SELECT TOP 5SName, SAddressFROM Students WHERE SSex = 0 2. 返回百分之多少行SELECT TOP 20 PERCENT SName, SAddressFROM Students WHERE SSex = 0 数据查询-排序注意排序中的字段可以使用表达式:1. 升序SELECT StudentID As 学员编号,(Score*0.9+5) As 综合成绩FROM ScoreWHERE (Score*0.9+5)60ORDER BY Score2. 降序SELECT Au_Lname +. +Au_fName AS EMP From Authors UnionSELECT fName +.+ LName AS EMP From EmployeeORDER BY EMP DESC 3.按多列排序SELECT StudentID As 学员编号, Score As 成绩FROM ScoreWHERE Score60ORDER BY Score,CourseID数据查询-使用函数(这个在前面已经介绍过了)SELECT ListNumberFROM SellRecordORDER BY Convert(int, Left(ListNumber, CharIndex(-, ListNumber)-1),Convert(int, Stuff(ListNumber,1, Charindex(-, ListNumber), )4. 基本条件查询比较运算符:,=,=60分组查询GROUP BYgroup by 字段名 having 组过滤条件SELECT CourseID, AVG(Score) AS 课程平均成绩FROM ScoreGROUP BY CourseID 分组查询多列分组SELECT StudentID AS 学员编号,CourseID AS 内部测试, AVG(Score) AS 内部测试平均成绩 FROM ScoreGROUP BY StudentID,CourseID 分组查询HAVINGSELECT StudentID AS 学员编号,CourseID AS 内部测试, AVG(Score) AS 内部测试平均成绩 FROM ScoreGROUP BY StudentID,CourseIDHAVING COUNT(Score)1 分组查询对比1. WHERE子句从数据源中去掉不符合其搜索条件的数据2. GROUP BY子句搜集数据行到各个组中,统计函数为各个组计算统计值3.HAVING子句去掉不符合其组搜索条件的各组数据行理解一下下面的查询语句:SELECT 部门编号, COUNT(*)FROM 员工信息表WHERE 工资 = 2000GROUP BY 部门编号HAVING COUNT(*) 1 子查询(思考这个地方为什么使用in,使用=也可以)SELECT * FROM BOOKS B WHERE B.PRICE IN (SELECT MAX(PRICE) FROM BOOKS); 并集(Union)和交集(Intersect)(SELECT * FROM BOOKS B, BORROW C WHERE B.BNO=C.BNO AND B.BNAME = 计算方法 INTERSECT SELECT * FROM BOOKS B, BORROW C WHERE B.BNO=C.BNO AND B.BNAME != 计算方法习题集)ORDER BY C.CNO DESC; 多表联接查询分类多表联接查询内联接-1SELECT Students.SName, Score.CourseID, Score.ScoreFROM Students,ScoreWHERE Students.SCode = Score.StudentID 多表联接查询内联接-2SELECT S.SName,C.CourseID,C.Score From Students AS SINNER JOIN Score AS CON C.StudentID = S.Scode 多表联接查询三表联接SELECT S.SName AS 姓名, CS.CourseName AS 课程, C.Score AS 成绩FROM Students AS S INNER JOIN Score AS C ON (S.SCode = C.StudentID)INNER JOIN Course AS CS ON (CS.CourseID = C.CourseID) .多表联接查询左外联接SELECT S.SName,C.CourseID,C.Score From Students AS SLEFT JOIN Score AS CON C.StudentID = S.Scode .多表联接查询右外联接(和左外连接相反)SELECT Titles.Title_id, Titles.Title, Publishers.Pub_nameFROM titles RIGHT OUTER JOIN Publishers ON Titles.Pub_id = Publishers.Pub_id案例分析要求一家银行发行了新的信用卡,刚开始的时候推广得很好,但是逐渐废卡也越来越多(卡上的余额少于2元,并且用户长时间不使用该卡),因此银行在二月份把这些少于2元的卡从都数据库表中删除了,但是很快问题就来了,用户发现他的卡再也不能使用而投诉,因此只能再把这些卡恢复。 编写SQL,把CardID为23、30等在子表中没有的记录插入到子表中,插入的数据Score项值为2 案例分析分析 这是一个数据插入的操作,因此要使用INSERT语句来进行 这种方式的数据插入,不是固定的数据项,而是从其它表中筛选数据再插入,因此要使用到INSERT INTOSELECT接构 插入的数据项是子表中没有的,需要找到子表中没有的这些数据,我们可以把整个语句的INSERT(前半部分)不考虑,先只考虑SELECT(后半部分),SELECT的任务就是找出两个表中不同的项 在前面的联接查询中,使用INNER JOINON可以找出相同的项,编写以下T-SQL:SELECT M.CardID,2 FROM M INNER JOIN S ON (S.CardIDM.CardID) 以上把“!=”简单地改为“”,不能找出不同的项,所找到的项比原来的要多很多,因为两个表之间是进行“交叉”对比的,而不是一一对比,所以会对比出很多“不同”的项来;因此,这种方法不可行 考虑我们在上面的左外连接查询,能够查询出左表中存在而相关表不存在的数据项 最后,子表中不存在的数据项,可以再加WHERE条件来进行筛选,S.CardID IS NULL可以判断其是否存在子表中存在 什么是子查询?问题:查看年龄比“张三”大的学员,要求显示这些学员的信息 ?分析:第一步:求出“张三”的年龄;第二步:利用WHERE语句,筛选年龄比“张三”大的学员; 实现方法一:采用T-SQL变量实现DECLARE age INT -定义变量,存放张三的年龄SELECT age=stuAge FROM stuInfo WHERE stuName=张三 -求出张三的年龄-筛选比张三年龄大的学员SELECT * FROM stuInfo WHERE stuAgeage GO 有没有更简洁的语句呢?有,我们可以合并上述两步 。因为:1.除了“”号外,还可以使用其他运算符号,习惯上,外面的查询称为父查询,括号中嵌入的查询称为子查询。2.SQL Server执行时,先执行子查询部分,求出子查询部分的值,然后再执行整个父查询。它的执行效率比采用SQL变量实现的方案一要高,所以推荐采用子查询。3.子查询作为WHERE条件的一部分,还可以和UPDATE、INSERT、DELETE一起使用,语法类似于SELECT语句。 实现方法二:采用子查询实现SELECT * FROM stuInfoWHERE stuAge( SELECT stuAge FROM stuInfo where stuName=张三)GO 使用子查询替换表连接问题:查询笔试刚好通过(60分)的学员。我们可以根据学员信息表和成绩表,查询笔试刚好通过考试的学员。那么就有两种实现方案:1.因为涉及到两张表(学员信息表和成绩表,前面我们已经建立好了),所以可以采用曾学过的连接查询。2. 使用子查询。 实现方法一:采用表连接SELECT stuName FROM stuInfo INNER JOIN stuMarks ON stuInfo.stuNo=stuMarks.stuNo WHERE writtenExam=60GO 实现方法二:采用子查询 SELECT stuName FROM stuInfo WHERE stuNo=(SELECT stuNo FROM stuMarks WHERE writtenExam=60)GO注意:1.一般来说,表连接都可以用子查询替换,但反过来说就不一定。有的子查询却不能用表连接替换。2.子查询比较灵活、方便、形式多样,常作为增、删、改、查的筛选条件,适合于操纵一个表的数据。3. 表连接更适合于查看多表的数据,一般用于SELECT查询语句。 问题:查询笔试刚好通过的学员名单。提问: 如果笔试成绩中有多个人刚好通过,即都为60分(例如我们再插入一条60分的数据)。使用我们刚才的子查询语句,会出现什么问题呢?(注意等号部分。)我们演示一下就会发现出现了编译错误。SQL Server要求:“”,“”等比较运算符号后的子查询,返回的值不能多于一个,即记录条数不能超过1条。那么如何解决呢?我们可以使用IN子查询。 IN子查询SELECT stuName FROM stuInfo WHERE stuNo IN (SELECT stuNo FROM stuMarks WHERE writtenExam=60)GO1. IN后面的子查询可以返回多条记录2. 常用IN替换等于()的比较子查询 问题:查询参加考试的学员名单 分析:判断一个学员是否参加考试其实很简单,只需要查看该学员对应的学号是否在考试成绩表stuMarks中出现即可 /*-采用IN子查询参加考试的学员名单-*/SELECT stuName FROM stuInfo WHERE stuNo IN (SELECT stuNo FROM stuMarks)GO NOT IN子查询问题:查询未参加考试的学员名单 分析:加上否定的NOT 即可 EXISTS子查询EXISTS语句我们并不陌生,我们在学习建库和建表语句时曾提前用过,它是一个存在检测的子查询语句 。IF EXISTS(SELECT * FROM sysDatabases WHERE name=stuDB) DROP DATABASE stuDBCREATE DATABASE stuDB.建库代码略 EXISTS子查询的语法:IF EXISTS (子查询)语句1. 如果子查询的结果非空,即记录条数1条以上,则EXISTS (子查询)将返回真(true),否则返回假(false) 2. EXISTS也可以作为WHERE 语句的子查询,但一般都能用IN子查询替换 问题:检查本次考试,本班如果有人笔试成绩达到80分以上,则每人提2分;否则,每人允许提5分 分析:是否有人笔试成绩达到80分以上,可以采用EXISTS检测 /*-采用EXISTS子查询,进行酌情加分-*/IF EXISTS (SELECT * FROM stuMarks WHERE writtenExam80) BEGIN print 本班有人笔试成绩高于80分,每人加2分,加分后的成绩为: UPDATE stuMarks SET writtenExam=writtenExam+2 SELECT * FROM stumarks ENDELSE BEGIN print 本班无人笔试成绩高于80分,每人可以加5分,加分后的成绩: UPDATE stuMarks SET writtenExam=writtenExam+5 SELECT * FROM stumarks ENDGO 问题:检查本次考试,本班如果没有一人通过考试(笔试和机试成绩都60分),则试题偏难,每人加3分,否则,每人只加1分 分析:没有一人通过考试,即不存在“笔试和机试成绩都60分”,可以采用NOT EXISTS检测 IF NOT EXISTS (SELECT * FROM stuMarks WHERE writtenExam60 AND labExam60) BEGIN print 本班无人通过考试,试题偏难,每人加3分,加分后的成绩为: UPDATE stuMarks SET writtenExam=writtenExam+3,labExam=labExam+3 SELECT * FROM stuMarks ENDELSE BEGIN print 本班考试成绩一般,每人只加1分,加分后的成绩为: UPDATE stuMarks SET writtenExam=writtenExam+1,labExam=labExam+1 SELECT * FROM stuMarks ENDGO sql经典题目(1)一.有下列三张表:CARD 借书卡。 CNO卡号,NAME姓名,CLASS班级BOOKS 图书。 BNO 书号,BNAME 书名,AUTHOR 作者,PRICE 单价,QUANTITY 库存册数 BORROW 借书记录。 CNO 借书卡号,BNO 书号,RDATE 还书日期备注:限定每人每种书只能借一本;库存册数随借书、还书而改变。要求实现如下15个处理:1.写出建立BORROW表的SQL语句,要求定义主码完整性约束和引用完整性约束。2.找出借书超过5本的读者,输出借书卡号及所借图书册数。3.查询借阅了水浒一书的读者,输出姓名及班级。4.查询过期未还图书,输出借阅者(卡号)、书号及还书日期。5.查询书名包括网络关键词的图书,输出书号、书名、作者。6.查询现有图书中价格最高的图书,输出书名及作者。7.查询当前借了计算方法但没有借计算方法习题集的读者,输出其借书卡号,并按卡号降序排序输出。8.将C01班同学所借图书的还期都延长一周。9.从BOOKS表中删除当前无人借阅的图书记录。10.如果经常按书名查询图书信息,请建立合适的索引。11.在BORROW表上建立一个触发器,完成如下功能:如果读者借阅的书名是数据库技术及应用,就将该读者的借阅记录保存在BORROW_SAVE表中(注ORROW_SAVE表结构同BORROW表)。12.建立一个视图,显示力01班学生的借书信息(只要求显示姓名和书名)。13.查询当前同时借有计算方法和组合数学两本书的读者,输出其借书卡号,并按卡号升序排序输出。14.假定在建BOOKS表时没有定义主码,写出为BOOKS表追加定义主码的语句。15.对CARD表做如下修改: a.将NAME最大列宽增加到10个字符(假定原为6个字符)。b.为该表增加1列NAME(系名),可变长,最大20个字符。 答案: 1.写出建立BORROW表的SQL语句,要求定义主码完整性约束和引用完整性约束。 CREATE TABLE BORROW (CNO CHAR(20), BNO CHAR(20), RDATE DATETIME); ALTER TABLE BORROW ADD CONSTRAINT BORROW_CNO_BNO_PK PRIMARY KEY (CNO,BNO); ALTER TABLE BORROW ADD CONSTRAINT BORROW_CNO_FK FOREIGN KEY(CNO) REFERENCES CARD(CNO);ALTER TABLE BORROW ADD CONSTRAINT BORROW_BNO_FK FOREIGN KEY(BNO) REFERENCES BOOKS(BNO); 2.找出借书超过5本的读者,输出借书卡号及所借图书册数。SELECT C.CNO,COUNT(C.CNO) FROM BORROW C GROUP BY(C.CNO); 3.查询借阅了水浒一书的读者,输出姓名及班级。SELECT A.NAME,A.CLASS FROM CARD A,BOOKS B ,BORROW C WHERE A.CNO=C.CNOAND B.BNO=C.BNO AND B.BNAME=水浒; 4.查询过期未还图书,输出借阅者(卡号)、书号及还书日期。 ORACLE:SELECT * FROM BORROW C WHERE RDATE SYSDATE;SQL SERVER:SELECT * FROM BORROW C WHERE RDATEGETDATE() ; 5.查询书名包括网络关键词的图书,输出书号、书名、作者。 SELECT B.BNO,B.BNAME,B.AUTHOR FROM BOOKS B WHERE B.BNAME LIKE %网络%; 6.查询现有图书中价格最高的图书,输出书名及作者。SELECT * FROM BOOKS B WHERE B.PRICE IN (SELECT MAX(PRICE) FROM BOOKS); 7.查询当前借了计算方法但没有借计算方法习题集的读者,输出其借书卡号,并按卡号降序排序输出。SELECT C.CNO FROM BOOKS B, BORROW C WHERE B.BNO=C.CNO AND B.BNAME LIKE 计算方法 AND B.BNAME NOT LIKE 计算方法习题集 ORDER BY C.CNO DESC; 8.将C01班同学所借图书的还期都延长一周。 UPDATE BORROW SET RDATE = DATEADD(WEEK,1,RDATE) WHERE CNO IN(SELECT CNO FROM CARD WHERE CLASS = C01) 或者UPDATE BORROW SET RDATE= (SELECT C.RDATE+7 FROM CARD A ,BORROW C WHERE C.BNO=A.BNO AND A.CLASS=C01); 9.从BOOKS表中删除当前无人借阅的图书记录。 DELETE FROM Books WHERE Books.BNO NOT IN (SELECT DISTINCT C.BNO from BORROW C ) 10.如果经常按书名查询图书信息,请建立合适的索引。 CREATE INDEX BOOKS_INDEX on BOOKS(BNAME); 11.在BORROW表上建立一个触发器,完成如下功能:如果读者借阅的书名是数据库技术及应用, 就将该读者的借阅记录保存在BORROW_SAVE表中(注ORROW_SAVE表结构同BORROW表)。 CREATE OR REPLACE TRIGGER BORROW_Insert_Trigs BEFORE INSERT ON BORROW REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW DECLARE V_BAME BOOKS.BNAME%TYPE; BEGIN SELECT BNAME INTO V_BNAME FROM BOOKS WHERE BNO=:NEW.BNO; IF V_NAME=数据库技术及应用 THENINSERT INTO ORROW_SAVE VALUES(:NEW.BNO,:NEW.BNAME,:NEW.AUTHOR,:NEW.PRICE,:NEW.QUANTITY) END; 12.建立一个视图,显示C01班学生的借书信息(只要求显示姓名和书名)。CREATE OR REPLACE VIEW ABC_VIEW AS SELECT A.NAME,B.BNAME FROM CARD A,BOOKS B,BORROW C WHERE A.CNO=C.CNO AND B.BNO=C.BNO AND A.CLASS=C01) 13.查询当前同时借有计算方法和组合数学两本书的读者,输出其借书卡号,并按卡号升序排序输出。 SELECT A.CNO FROM CARD A WHERE A.CNO IN (SELECT DISTINCT C.CNO FROM BORROW C,BOOKS B WHERE C.BNO=B.BNO AND B.BNAME IN(计算方法,组合数学) ) ORDER BY A.CNO; 14.假定在建BOOKS表时没有定义主码,写出为BOOKS表追加定义主码的语句 ALTER TABLE BOOKS ADD CONSTRAINT BOOKS_BNO_PK PRIMARY KEY(BNO); 15.对CARD表做如下修改: a.将NAME最大列宽增加到10个字符(假定原为6个字符)。 b.为该表增加1列NAME(系名),可变长,最大20个字符。 ALTER TABLE CARD MODIFY (NAME CHAR(10); ALTER TABLE CARD ADD NAME_P VARCHAR(20); 二.有下列三张表: S (SNO,SNAME) 学生关系。SNO 为学号,SNAME 为姓名C (CNO,CNAME,CTEACHER) 课程关系。C

温馨提示

  • 1. 本站所有资源如无特殊说明,都需要本地电脑安装OFFICE2007和PDF阅读器。图纸软件为CAD,CAXA,PROE,UG,SolidWorks等.压缩文件请下载最新的WinRAR软件解压。
  • 2. 本站的文档不包含任何第三方提供的附件图纸等,如果需要附件,请联系上传者。文件的所有权益归上传用户所有。
  • 3. 本站RAR压缩包中若带图纸,网页内容里面会有图纸预览,若没有图纸预览就没有图纸。
  • 4. 未经权益所有人同意不得将文件中的内容挪作商业或盈利用途。
  • 5. 人人文库网仅提供信息存储空间,仅对用户上传内容的表现方式做保护处理,对用户上传分享的文档内容本身不做任何修改或编辑,并不能对任何下载内容负责。
  • 6. 下载文件中如有侵权或不适当内容,请与我们联系,我们立即纠正。
  • 7. 本站不保证下载资源的准确性、安全性和完整性, 同时也不承担用户因使用这些下载资源对自己和他人造成任何形式的伤害或损失。

评论

0/150

提交评论