?
?SQL子查询典型例子
1、单行子查询
select ename,deptno,sal
from emp
where deptno=(select deptno from dept where loc='NEW YORK')；
2、多行子查询
SELECT ename,job,sal
FROM EMP
WHERE deptno in ( SELECT deptno FROM dept WHERE dname LIKE 'A%')；
3、多列子查询
SELECT deptno,ename,job,sal
FROM EMP
WHERE (deptno,sal) IN (SELECT deptno,MAX(sal) FROM EMP GROUP BY deptno)；
4、内联视图子查询
(1)SELECT ename,job,sal,rownum
FROM (SELECT ename,job,sal FROM EMP ORDER BY sal)；
(2)SELECT ename,job,sal,rownum
FROM ( SELECT ename,job,sal FROM EMP ORDER BY sal)
WHERE rownum<=5；
5、在HAVING子句中使用子查询
SELECT deptno,job,AVG(sal) FROM EMP GROUP BY deptno,job HAVING AVG(sal)>(SELECT sal FROM EMP WHERE ename='MARTIN')；
?基本条件查询
比较运算符:>,>=,<,<=,=,!=
between a and b,in(a,b,c),not exists,is null,like …%_?,or,and, any,all等
把某一字段中内容在特定范围内的记录查询出来
SELECT StudentID, Score FROM SCore WHERE Score BETWEEN 60 AND 80把某一字段中内容与所列出的查询内容列表匹配的记录查询出来
SELECT SName AS 学员姓名,SAddress As 地址 FROM Students WHERE SAddre ss IN ('北京','广州','上海')
?SQL数据查询语句
SELECT查询语句是由七个子句构成，其中SELECT和FROM子句是一个完整SELECT查询语句必须要有的，其它的子句可以根据具体需要任选。上述的每个子句功能说明如下：
（1）SELECT子句
列出所有要求SELECT语句进行检索的数据项，这些项可能取自数据库中关系表的列，也可以是SQL在执行查询时进行计算的表达式。这里的ALL和DISTINCT选项，表示查询出的结果中是否容许有内容重复的行出现，缺省时是ALL项，表示容许有重复的行出现，而*则表示查询出所指定关系表中所有的列。
（2）FROM子句
FROM子句列出包含所要查询的数据关系表。
（3）WHERE子句
WHERE子句告诉SQL只查询某些关系表中满足一定要求的行的数据，查询要求由WHERE子句中的查询条件确定。
（4）GROUP BY子句
GROUP BY子句指定当前查询是汇总查询，即不是对根据每行产生一个查询结果，而是对相似的行进行分组，然后再对每组产生一个汇总查询的结果。
（5）HAVING子句
HAVING子句告诉SQL只对由GROUP BY所得到的某些行组的结果进行过滤，选择出满足分组条件的分组。
（6）ORDER BY子句
ORDER BY子句确定是否将查询出的结果按一列或多列中的数据进行排序，缺省时是不排序的。
（7）INTO子句
INTO子句确定是否将查询出的结果存入一张新的关系表中，缺省时只将查询出现的结果显示在屏幕上。这是非标准SELECT语句中的子句，但目前绝大多数实际应用的SQL数据库系统的SQL语言提供了这一选项。
下面是一些使用SELECT查询语句进行数据查询的示例。
例1：列出销售额超过6000元的销售人员的姓名、销售目标和超过销售目标的销售额。
SELECT NAME,QUOTA,（SALES_QUOTA）
FROM SALESREPS
WHERE SALES>6000
例2：查出1999年最后一个季度的订单情况。
SELECT ORDER_NUM,ORDER_DATE,PRODUCT,AMOUNT
FROM ORDERS
WHERE ORDER_DATE BETWEEN "01-OCT-99" AND "31-DEC-99"
例3：查出名称是以ABC开头的产品订单情况。
SELECT ORDER_NUM,ORDER_DATE,PRODUCT,AMOUNT
FROM ORDERS
WHERE PRODUCT LIKE "ABC%"
例4：列出所有的销售处，按区域名字母顺序排列。
SELECT CITY,REGION
FROM OFFICE
ORDER BY REGION ASC
在这里ASC表示升序，如果表示降序可用DESC。
例5：列出每个销售人员以及他们工作的城市和区域的情况。
SELECT NAME,CITY,REGION
FROM SALESREPS,OFFICE
WHERE REP_OFFICE=OFFICE
例6：查出有多少销售员的销售额超过了其目标额，以及他们的销售额总和。
SELECT COUNT(NAME),SUM(SALES)
FROM SALESREPS
WHERE SALES>QUOTA
注意本例中使用了函数，与前面介绍的查询不同之处在于：它不仅仅是从数据库中查出某些数据，并且对查出的数据进行多种计算处理，并将结果作为查询结果输出。SQL语言提供了6种处理函数，帮助完成不同的处理工作。这6种函数是：
▲SUM(),用于计算表中某一列的总和。
▲AVG(),用于计算表中某一列的平均值。
▲MIN(),用于选择表中某一列的最小值。
▲MAX(),用于选择表中某一列的最大值。
▲COUNT(),用于计算表中某一列中值的个数。
▲COUNT(*),用于计算某张表的行数。
例7：对于每两个或更多的销售员的销售点，计算其中所有销售员的总销售员的总销售目标值和总销售员金额。
SELECT CITY,SUM(QUOTA),SUM(SQLESREPS.SALES)
FROM OFFICE,SALESREPS
GROUP BY CITY
HAVING COUNT(*)>=2
SELECT查询处理工作过程描述总结：
▲形成FROM子句所指定的目标表（表的乘积）。如果FROM子句仅指定一个表，则此表为目标表。
▲如果有WEHER子句，则将其搜索条件用于目标表中的每一行，保留使搜索条件为TRUE的行，剔除使搜索条件为FALSE或NULL的行。若WHERE子句中包含另一个SELECT 查询语句，先去完成另一个SELECT查询语句操作，然后将其查询结果用到本次查询的搜索条件中，进行检测。
▲若有GROUP BY子句，则对目标表中所保留的行进行分组，以使得每组中的各行具有相同的分组列的列值。
▲若有HAVING子句，则将其搜索条件作用于每个行组，保留那些使搜索条件为TRUE 的组，剔除使搜索条件为FALSE或NULL的行。若HAVING子句中包含另一个SELECT查询语句，先去完成另一个SELECT查询语句操作，然后将其查询结果用到本次查询的HAVING 子句搜索条件中，进行检测。
▲对于所保留的每一行（或行组），计算由SELECT子句所指定的每项值，对于简单的列指定，取当前行（或行组）中该列的值；对于列函数，若有GROUP BY子句，则用当前
行组作为参数；否则，用所有行作为其参数；剔除SELECT子句中没有指定的各列。本文来自编程入门网https://www.doczj.com/doc/ae11416603.html,
▲若SELECT子句后选择了DISTINCE，则将所生成结果中的所有内容重复的行剔除掉。
▲若有ORDER BY子句，则按相应的排序设置对结果进行排序。
有三种基本的子查询。它们是：
●在通过IN 或由ANY 或ALL 修改的比较运算符引入的列表上操作。
●通过未修改的比较运算符引入且必须返回单个值。
●通过EXISTS 引入的存在测试。
1.带in的嵌套查询
select emp.empno,emp.ename,emp.job,emp.sal from scott.emp where sal in (select sal from scott.emp where ename=??WARD??);
上述语句完成的是查询薪水和WARD相等的员工，也可以使用not in来进行查询。
2.带any的嵌套查询
通过比较运算符将一个表达式的值或列值与子查询返回的一列值中的每一个进行比较，只要有一次比较的结果为TRUE，则ANY测试返回TRUE。
select emp.empno,emp.ename,emp.job,emp.sal from scott.emp where sal >any(select sal from scott.emp where job=??MANAGER??);
等价于下边两步的执行过程：
（1）执行“select sal from scott.emp where job=??MANAGER??”
（2）查询到3个薪水值2975、2850和2450，父查询执行下列语句：
select emp.empno,emp.ename,emp.job,emp.sal from scott.emp where sal >2975 or
sal>2850 or sal>2450;
3.带some的嵌套查询
select emp.empno,emp.ename,emp.job,emp.sal from scott.emp where sal =some(select sal from scott.emp where job=??MANAGER??);
等价于下边两步的执行过程：
（1）子查询,执行“select sal from scott.emp where job=??MANAGER??”。
（2）父查询执行下列语句。
select emp.empno,emp.enam e,emp.job,emp.sal from scott.emp where sal =2975 or sal=2850 or sal=2450;
带【any】的嵌套查询和【som e】的嵌套查询功能是一样的。早期的SQL仅仅允许使用【any】，后来的版本为了和英语的【any】相区分，引入了【som e】，同时还保留了【any】关键词。
4.带all的嵌套查询
通过比较运算符将一个表达式的值或列值与子查询返回的一列值中的每一个进行比较，只要有一次比较的
结果为FALSE，则ALL测试返回FALSE。
select emp.empno,emp.ename,emp.job,emp.sal from scott.emp where sal >all(select sal from scott.emp where job=??MANAGER??);
等价于下边两步的执行过程：
（1）子查询,执行“select sal from scott.emp where job=??MANAGER??”。
（2）父查询执行下列语句。
select emp.empno,emp.ename,emp.job,emp.sal from scott.emp where sal >2975 and sal>2850 and sal>2450;
5.带exists的嵌套查询
select emp.empno,emp.ename,emp.job,emp.sal from scott.emp,scott.dept where exists (select * from scott.emp where scott.emp.deptno=scott.dept.deptno);
Sql 语句答案： 1. select姓名,单位 from读者 where姓名like'李%' 2. select书名,出版单位 from图书 3. select出版单位,书名,单价 from图书 where出版单位='高等教育出版社' order by单价desc 4. select书名,出版单位,单价 from图书 where单价between 10.00 and 20.00 order by出版单位,单价asc 5. select书名,作者 from图书 where书名like'计算机%' 6. select借阅.总编号,借书证号 from图书,借阅 where图书.总编号=借阅.总编号and借阅.总编号in('112266','449901') 7.select distinct姓名,单位 from读者inner join借阅 on借阅.借书证号=读者.借书证号 8. select书名,姓名,借书日期 from图书inner join借阅 on图书.总编号=借阅.总编号 join读者 on借阅.借书证号=读者.借书证号 where读者.姓名like'李%' 9. select distinct读者.借书证号,姓名,单位 from借阅inner join读者 on借阅.借书证号=读者.借书证号 where借阅.借书日期>='1997-10-1' 10. select借书证号
from借阅 where总编号in(select总编号 from图书 where书名='FoxPro大全') 11. select姓名,单位,借书日期 from借阅,读者 where借阅.借书证号=读者.借书证号and借书日期=(select借书日期 from借阅,读者 where借阅.借书证号=读者.借书证号and姓名='赵正义') 12. select distinct借书证号,姓名,单位 from读者 where借书证号not in(select借书证号 from借阅 where借书日期>='1997-07-01') 13. select max(单价)最高单价,min(单价)as最低单价,avg(单价)as平均单价from图书 where出版单位='科学出版社' 14. select count(借书证号) from借阅 where借书证号in(select借书证号 from读者 where单位='信息系') 15. select出版单位,max(单价)最高价格,min(单价)as最低价格,count(*)册数from图书 group by出版单位 16. select单位,count(借阅.借书证号) from借阅,读者 where借阅.借书证号in(select借书证号 from读者) group by单位 17. select姓名,单位 from读者 where借书证号in(select借书证号 from借阅 group by借书证号 having count(*)>=2)
--（1）查询20号部门的所有员工信息。 select * from emp e where e.deptno=20; --（2）查询奖金（COMM）高于工资（SAL）的员工信息。 select * from emp where comm>sal; --（3）查询奖金高于工资的20%的员工信息。 select * from emp where comm>sal*0.2; --（4）查询10号部门中工种为MANAGER和20号部门中工种为CLERK的员工的信息。select * from emp e where (e.deptno=10 and e.job='MANAGER') or (e.deptno=20 and e.job='CLERK') --（5）查询所有工种不是MANAGER和CLERK， --且工资大于或等于2000的员工的详细信息。 select * from emp where job not in('MANAGER','CLERK') and sal>=2000; --（6）查询有奖金的员工的不同工种。 select * from emp where comm is not null; --（7）查询所有员工工资和奖金的和。 select (e.sal+nvl(https://www.doczj.com/doc/ae11416603.html,m,0)) from emp e; --（8）查询没有奖金或奖金低于100的员工信息。 select * from emp where comm is null or comm<100; --（9）查询员工工龄大于或等于10年的员工信息。 select * from emp where (sysdate-hiredate)/365>=10; --（10）查询员工信息，要求以首字母大写的方式显示所有员工的姓名。 select initcap(ename) from emp; select upper(substr(ename,1,1))||lower(substr(ename,2)) from emp; --（11）显示所有员工的姓名、入职的年份和月份，按入职日期所在的月份排序， --若月份相同则按入职的年份排序。 select ename,to_char(hiredate,'yyyy') year,to_char(hiredate,'MM') month from emp order by month,year; --（12）查询在2月份入职的所有员工信息。 select * from emp where to_char(hiredate,'MM')='02' --（13）查询所有员工入职以来的工作期限，用“**年**月**日”的形式表示。 select e.ename,floor((sysdate-e.hiredate)/365)||'年' ||floor(mod((sysdate-e.hiredate),365)/30)||'月' ||floor(mod(mod((sysdate-e.hiredate),365),30))||'日' from emp e; --（14）查询从事同一种工作但不属于同一部门的员工信息。
实验3 SQL的高级查询 一、实验目的 1．继续掌握基本的SELECT查询及其相关子句的使用 2．掌握复杂的SELECT查询、如多表查询，子查询，连接，分组和嵌套查询 3．掌握SQL中的集合并运算union 4．掌握SQL中元组的插入、修改、删除操作（insert,update,delete）。 二、预备知识： SQL中的连接操作： 假设R与K是基本数据表。基本表的连接操作可以分为五类： ●内连接 R inner join K on <条件> 只返回满足条件的行 ●左外连接 R left join K on<条件>返回满足条件的行及左表R中所有的行。如果左表的某条 记录在右表中没有匹配记录，则在查询结果中右表的所有选择属性列用NULL填充。 ●右外连接 R right join K on <条件>返回满足条件的行及右表K中所有的行 ●完全外连接 R full join K on <条件>返回满足条件的行及左右表R，K所有的行。当某条记录 在另一表中没有匹配记录，则在查询结果中对应的选择属性列用NULL填充。 ●交叉连接R CROSS JOIN Ｋ：相当于广义笛卡尔积。不能加筛选条件。 三、实验环境 1.个人计算机或局域网 2.Windows 2000操作系统 3.SQL Server 2000数据库管理系统 四、实验步骤 1.启动查询分析器，选择学生选课数据库 2.在学生选课数据库中分别使用SQL命令完成指定任务。 五、实验内容： 1．查询所有学生的情况以及他们选修的课程和得分 2．查询所有学生的姓名以及他们选修的课程名和得分 3．检索已经选了课程的学生的姓名、选修课程和成绩（提示：使用inner join） 4．检索每个学生的姓名，选修课程和成绩，没有选修的同学也列出。 5．列出所有学生所有可能的选课情况（提示：使用cross join） 6．检索每个学生的姓名、选修课程，没有选修的同学和没有被选修的课程也列出 7．求学生的总人数和平均年龄 8．求选修了各课程的学生的人数 9．对计算机系的学生按课程列出选修了该课程的学生的人数 10．查询每个学生的平均成绩 11．求选修课程超过或等于2门的学生的学号 12．查询学生的学号、姓名、所有学生所有课程的最高成绩 13．查询学生的学号、课程号及对应成绩与所有学生中所有课程的最高成绩的差值 14．查询陈小红同学的学号及所选修的课程号 15．查询没有选修C02课程的学生姓名
实验六高级查询 【实验目的与要求】 1、熟练掌握IN子查询 2、熟练掌握比较子查询（尤其要注意ANY、ALL谓词如何用集函数代替） 3、熟练掌握EXISTS子查询（尤其是如何将全称量词和逻辑蕴含用EXISTS谓词代替） 4、熟练掌握复杂查询的select语句 【实验准备】 1．准备好测试数据 2．熟悉多表查询与嵌套查询的用法。 【实验内容】 5.1.嵌套子查询 以下实验在前面实验中创建的CPXS数据库中完成，请根据前面实验创建的表结构和数据，完成如下嵌套查询：（也可以不按指导书给出的思路写查询语句，只要是正确的即可，有疑问时可以和同学及老师商量你的查询语句是否正确） 查询在2004年3月18日没有销售的产品名称（不允许重复）。 用IN子查询： 写出对应SQL语句并给出查询结果： USE CPXS SELECT产品名称 FROM CP WHERE产品编号not IN (SELECT产品编号FROM CPXSB WHERE销售日期='2004-3-12') select distinct 产品名称 from CP where 产品编号 not in ( select 产品编号 from CPXSB where 销售日期='2004-3-18' );
用EXISTS子查询： 写出对应SQL语句并给出查询结果： select distinct 产品名称 from CP where 产品名称!=all ( select 产品名称 from CP where exists ( select 产品编号 from CPXSB where 销售日期!='2004-03-18' and CP.产品编号=CPXSB.产品编号 ) ) 查询名称为“家电市场”的客户在2004年3月18日购买的产品名称和数量。 用IN子查询： 写出对应SQL语句并给出查询结果： select 产品名称,数量 from CPXSB left join CP on(CPXSB.产品编号=CP.产品编号) where 客户编号 in ( select 客户编号 from XSS
实验五高级查询 【实验目的与要求】 1、熟练掌握IN子查询 2、熟练掌握比较子查询（尤其要注意ANY、ALL谓词如何用集函数代替） 3、熟练掌握EXISTS子查询（尤其是如何将全称量词和逻辑蕴含用EXISTS谓词代替） 4、熟练掌握复杂查询的select语句 【实验准备】 1．准备好测试数据 2．熟悉多表查询与嵌套查询的用法。 【实验内容】 5.1.准备工作 1.创建测试用数据库XSGL，并在其中创建三个表 本实验需用到student、course和SC表，其结构和约束如下： Student表结构及其约束为： 表5-1 student表结构和约束 列名称类型宽度允许空值缺省值主键说明 Sno char 8 否是学号 Sname varchar 8 否学生姓名 Sex char 2 否男性别 Birth datetime 否出生年月Classno char 3 否班级号Entrance_date datetime 否入学时间Home_addr varchar 40 是家庭地址 Course表结构及其约束为： 表5-2 course表结构和约束 列名称类型宽度允许空值缺省值主键说明 cno Char 3 否是课程号 Cname varchar 20 否课程名称Total_perior int 是总学时credit int 是学分 SC表结构及其约束为： 表5-3 SC表结构和约束 列名称类型宽度允许空值缺省值主键外键说明
sno Char 8 否是学号,参照student表 cno char 3 否是课程号,参照course表 grade int 是否成绩其中成绩为百分制。 2.对表添加、修改、删除数据 向student表中插入如下数据: 表5-4 student表 Sno sname sex birth classno Entrance_date Home_addr sdept postcode 20050001 张虹男1984/09/011 051 2005/09/01 南京CS 200413 20050002 林红女1983/11/12 051 2005/09/01 北京CS 100010 20050003 赵青男1982/05/11 051 2005/09/01 上海MA 200013 向course表中插入数据: 表5-5 course表 cno Cname Total_perior credit 001 高数68 3 002 C语言程序设计75 4 003 JAVA语言程序设计68 3 向SC表中插入数据: 表5-6 SC表 Sno Cno grade 20050001 001 89 20050001 002 78 20050001 003 89 20050002 002 60 20050003 001 80 为达到更好的测试效果，请自行向数据库表中添加其它数据，使表中数据量达10条以 上，并使每个字段值表现出多样性。 5.2.复杂查询 （1）查询比“林红”年纪大的男学生信息。 SQL语句： select * from Student where birth< ( select birth from Student where Sname='林虹'
C顾客cidcnamecity discnt c001 李广天津10.00 c002 王开基北京12.00 c003 安利德北京8.00 c004 曹士雄天津8.00 c006 曹士雄广州0.00 P商品pidpname city quantity price p01 梳子天津111400 0.50 p02 刷子成都203000 0.50 p03 刀片西安150600 1.00 p04 钢笔西安125300 1.00 p05 铅笔天津221400 1.00 p06 文件夹天津123100 2.00 p07 盒子成都100500 1.00 A代理aidanamecity percent a01 史可然北京 6 a02 韩利利上海 6 a03 杜不朗成都7 a04 甘瑞北京 6 a05 敖斯群武汉 5 a06 史可然天津 5 O订单ordnomonthcidaid pidqtydollars 1011 01 c001 a01 p01 1000 450.00 1012 01 c001 a01 p01 1000 450.00 1019 02 c001 a02 p02 400 180.00 1017 02 c001 a06 p03 600 540.00 1018 02 c001 a03 p04 600 540.00 1023 03 c001 a04 p05 500 450.00 1022 03 c001 a05 p06 400 720.00 1025 04 c001 a05 p07 800 720.00
1013 01 c002 a03 p03 1000 880.00 1026 05 c002 a05 p03 800 704.00 1 查询所有定购了至少一个价值为0.50的商品的顾客的名字。 https://www.doczj.com/doc/ae11416603.html,ame from client wherecid in (select cid from order1 where pid in(select pid from product where price='0.5')) 2 找出全部没有在代理商a03处订购商品的顾客cid值。 selectclient.cid from client wherecid not in (select cid from order1 where aid in(select aid from agend where aid='a03')) 3 找出只在代理商a03处订购商品的顾客。 selectclient.cid from client wherecid not in (select cid from order1 where aid in(select aid from agend where aid!='a03') )AND cid in (select cid from order1 where aid in(select aid from agend where aid='a03')) 4 找出没有被任何一个在北京的顾客通过在上海的代理商订购的所有商品。 select *from product wherepid not in (select pid from client,agend,order1 where client.cid=order1.cid and agend.aid = order1.aid and client.city='北京' and agend.city = '上海')
高级理论部分 简单子查询语法： select......from表where列>(子查询) 其中：子查询语句必须放置在一对圆括号内 ********* ?习惯上，外面的查询成为父查询，圆括号中嵌入的查询称为子查询 ?注意：SQL编译时：先执行父查询，后执行子查询，即先父后子 ●SQL执行时：先执行子查询，后执行父查询，即先子后父 ********* 表命名别名(alias) 的方法： ----1.使用AS 关键字，符合ANSI标准 select列表from表名as表的别名 ----2.使用空格，简便的方法 select列表from表名表的别名 ●提示： ◆1>.般来说，表连接都可以用子查询替换，但反过来却不一定，有的子查询不能用表连接来 替换 ◆2>.查询比较灵活，方便，形式多样，适用于作为查询的筛选条件，而表连接更适合于查看 多表的数据 ●注意： ?当某个表命名别名后，在select 语句中出现该表的列需要指定表名时，就必须统一使用该表的 别名， ?否则将产生语法错误 IN 子查询： ◆后面可跟随返回多条记录的子查询，用于检测某列的值是否存在于某个范围中NOT IN 子查询： ◆后面可跟随返回多条记录的子查询，用于检测不存在于某个范围中的某列的值EXISTS ：
◆关键字能够检测数据是否存在 ◆语法： if exists (子查询) 语句 其中：如果子查询的结果非空，则Exists将返回真(true)，否则返回假(false) NOT EXISTS子句: EXISTS和IN 一样，同样允许添加not关键字实现取反操作，not exists 表示不存在子查询注意事项： ?子查询语句可以嵌套在SQL 语句中任何表达式出现的位置 ●嵌套在select 语句的select 子句中的子查询语句如下： ?select (子查询)from表名 子查询结果为单行单列，但不必指定列的别名 ●嵌套在select 语句的from 子句中的子查询语句如下： ?select*from (子查询)as表的别名 必须为表指定别名 ?在子查询的select 子句中不能出现TEXT 、NTEXT、IMAGE数据类型的列 ?目前大部分数据库管理系统的编译器不支持在子查询语句中查询二 进制类型的数据 ?只出现在子查询中而没有出现在父查询中的表不能包含在输出列中 SQL为列命名别名的方法 ◆使用AS 关键字： ?select列名as列的别名from表名 ◆使用空格： ?select列名列的别名from表名 ◆使用赋值运算符，SQL Server 支持的方式： ?select列的别名=列名from表名 列命名别名时注意的问题： ?当使用中文列名时，可不写引号，但千万不能使用全角引号，否则查询会出错 ?当使用的英文列名超过两个单词时，必须使用引号将列名括起来 合并多个表中的数据的方法： ◆联合——合并多个数据集中的行 ◆子查询——将一个查询嵌套在另一个查询中 ◆连接——合并多个数据表中的列
SQL Server SQL高级查询语句小结 ? 基本常用查询 --select select * from student; --all 查询所有select all sex from student; --distinct 过滤重复select distinct sex from student; --count 统计 select count(*) from student; select count(sex) from student; select count(distinct sex) from student; --top 取前N条记录 select top 3 * from student; --alias column name 列重命名 select id as 编号, name '名称', sex 性别from student; --alias table name 表重命名 select id, name, s.id, https://www.doczj.com/doc/ae11416603.html, from student s; --column 列运算 select (age + id) col from student; select https://www.doczj.com/doc/ae11416603.html, + '-' + https://www.doczj.com/doc/ae11416603.html, from classes c, student s where s.cid = c.id; --where 条件 select * from student where id = 2; select * from student where id > 7; select * from student where id < 3; select * from student where id <> 3; （不等于）!=（不等于）
--------------------------------------装--------------------------------------订------------------------------线----------------- **学院课程考试试卷 课程名称：《SQLServer：数据库设计和高级查询》（A）卷 年级：班级： 姓名：_______________ 学号：_________________ 考试(考查) 闭卷 1. 下列哪个SQL 语句属于DDL 语句（ A ）。 A. Create （DDL：create drop truncate） B. Select (DML: insert update delete select) C. Grant (DCL:grant rovoke ) D. Insert 2. SQL Server 2008 的BEGIN TRANS 和COMMIT TRANS,ROOLBACK TRANS 语句可用在（BD ）中来支持操 作的事务特性。【选两项】 A. Select 语句 B. 存储过程 C. https://www.doczj.com/doc/ae11416603.html, 连接对象的Transaction 属性 D. 触发器 3. 在SQL Server2008数据库中，从Product表中查询出Price（价 格）高于Pname （产品名称）为“网通IP电话卡”的所有记录中 的最高价格的查询语句是（ D ）。 A. SELECT * FROM Product WHERE max(Price)>?网通IP电话卡? B. SELECT * FROM Product WHERE Price>(SELECT max（*） FROM Product WHERE Pname=? 网通IP电话卡?） C. SELECT * FROM Product WHERE EXISTS Pname=?网通IP电 话卡? D. SELECT * FROM Product WHERE Price>（SELECT max （Price）FROM Product WHERE Pname=? 网通IP电话卡?） 中，声明一个最多可以存储10个字符的变 B ）。 B. DECLARE @pwd VACHAR(10) C. pwd VARCHAR(10) D. @pwd VARCHAR(10) 5. 在SQL Server 2008中，已知Student表中有一个age列，数据 类型是int，如果要限制该列的取值范围在18到28之间，可以使用 以下哪个SQL语句（ A ）。 A. alter table Student add check(age>=18 and age<=28) B. alter table Student add defult(age>=18 and age<=28) C. alter table Student add unique(age>=18 and age<=28) D. alter table Student add set(age>=18 and age<=28) 6. SQL Server 2008数据库中，创建books表的语句如下，这个创 建命令执行时出错，错误原因有（BE ）。【选两项】 Cerate table books(bookid int primary key,title varchar(20) not null,author char not null,loc int identity(1,1),depid int foreign
练习6 复杂的结构化查询语句 6．1 上机目的 1、掌握SELECT语句的统计函数的作用和使用方法 2、通过练习SELECT语句的GROUP BY和ORDER BY字句的用法，理解其作用，掌握语句的写法。 3、通过练习涉及多张表的连接查询，掌握它的作用和写法 6．2 上机练习预备知识点 说明： ①函数中DISTINCT的作用是统计计算的过程中去掉重复值。 ②函数中ALL的作用是统计计算全部的值包括重复值。可省略。 6．2．2 GROUP BY 子句 作用：将记录根据GROUP BY后所跟字段的值分成多个组，进行分组计算。一般情况GROUP BY 子句与汇总函数连用。 格式：GROUP BY（字段，…n） 例14 按照班级把学生信息表的数据分组,并且统计每个班级的人数 分析：Students表中班级人数这一列，因此需要计算才能得到。因此需要先按class分组，class列中有几个不同的值就要分成几组，再按各组进行统计计算。 SELECT class,人数= count(*) FROM Students GROUP BY class 注意： ①分组也可以根据多个字段； ②不能对数据类型为ntext,text,image或bit的字段使用GROUP BY 1、HAVING 子句 作用：HAVING子句将对GROUP BY 子句选择出来的结果进行再次筛选，最后输出符合HAVING 子句条件的结果。HAVING子句必须与GROUP BY子句连用。 例15 查询平均入学总分在350分以上的班级 分析：Students表中没有平均分这一列，因此需要计算才能得到。因此需要先按class分组，class列中有几个不同的值就要分成几组，再进行统计计算，最后用HAVING子句筛选出AVG(mgrade) >=350的记录。 SELECT class,AVG(mgrade) FROM Students GROUP BY class
现有数据库结构及数据如下： 学生表(students) st_id st_name(姓名) sex(性别) st001 张杰男 st002 公孙燕飞男 st003 王楠女 st004 王伟男 st005 李燕纹女 st006 孙武男 老师表(teachers) t_id(主键,编号) t_name(姓名) t_lesson(课程) t001 张老师数学 t002 李老师英语 成绩表(results) r_id r_fenshu(int) r_stid r_tid r001 90 st001 t002 r002 68 st005 t001 r003 92 st003 t001 r004 82 st006 t002 r005 70 st002 t002 r006 86 st002 t001 r007 57 st003 t002 r008 76 st006 t001 r009 55 st001 t001 r010 77 st004 t002 r011 58 st005 t002 以上数据库结构中字段未标明具体类型的，皆为varchar类型。 基础题： 1）查询出王伟同学的学生编号。 2）查询出名子第三个字是“燕”字的学生的编号和姓名。 3）查询显示出所有男学生的姓名及其名子的长度。 4）查出数学考试成绩的最低分。 5）查出所有女学生的各科成绩。 6）查出英语考试成绩的平均分。 7）在全部男学生中查询出学生编号最后两名的所有信息，并以学生编号降序显示。8）统计出王楠同学在这次考试中的所有课程成绩的合计分。 9）查询所有课程考试中成绩及格，但未达到90分的学生的姓名。（不显示重复姓名）
Oracle函数与高级查询 二Oracle的函数 2.1 什么是函数 函数：指对一堆逻辑的打包，能够支持传入的参数，根据参数得到一定的结果； 那么,在oracle中，都有什么函数，有什么类型的函数，函数有什么分类？ 2.2 函数的分类 2.2.1 单行与多行 除了聚合(组)函数，其它全是单行函数 Oracle的函数咱们可以简单分成再种：多行函数与单行函数，如下图: 函数 单行函数多行函数 2.2.1.1 什么是单行函数 简单理解：对单行数据进行是操作，得到单行数据; 比如concat连接函数concat 连接函数得到的效果; 使用concat 不会影响行数，以前100行，使用concat之后得到的结果也是100行。 单行函数：在执行获得结果后。可以支持传入参数【列和表达式】，针对每一行单独做运算。最终不会影响结果的行数。 单行函数特点: 1接收参数返回一个值
2对每一行返回行起作用 3每一行返回一个结果 4可以修改数据类型 5可以使用嵌套concat(concat(col1,col2),col2) 除了concat 这样的单行函数以外，还有其他的单行函数，比如字符处理函数； 比如有时要求用户名全部大写或者小写，或者需要得到一个人的姓，这些都需要字符函数进行处理，字符函数中，也存在分类 2.2.1.2 什么是多行函数 多行函数：简单理解对多行数据进行操作，最后返回一个数据 比如count函数： Count 针对过滤后的结果支持传入表的列名或者常量，对所有行的记录统一处理。最终只会得到一个结果,会影响结果行数; 多行函数: 在执行获得结果后。可以支持传入参数【列和表达式】，针对每一行整体统计做运算。最终只会产生1行数据 咱们所说的单行与多行是指这个函数操作的数据是单行还是多行(不是说的结果) 2.2.1.3 单行、多行分别用在哪？ 扩展理解与练习 比如concat 用在哪里? 可以放在select 后，把它的姓和名连接起来； 单行函数: select 关键字之后 where 之后的条件上 （where 之后单行函数如下） 姓名中有e或者a的员工? 把姓名组合起来查询 select*from employees where first_name || last_name like'%e%' or first_name || last_name like'%a%' 使用函数 Select * from employees Where concat(first_name,last_name) like ‘%e%’ Or first_name || last_name like ‘%a%’; 多行函数: 针对Select 关键之后；比如count函数统计所有行，放在select之后
? ?SQL子查询典型例子 1、单行子查询 select ename,deptno,sal from emp where deptno=(select deptno from dept where loc='NEW YORK')； 2、多行子查询 SELECT ename,job,sal FROM EMP WHERE deptno in ( SELECT deptno FROM dept WHERE dname LIKE 'A%')； 3、多列子查询 SELECT deptno,ename,job,sal FROM EMP WHERE (deptno,sal) IN (SELECT deptno,MAX(sal) FROM EMP GROUP BY deptno)； 4、内联视图子查询 (1)SELECT ename,job,sal,rownum FROM (SELECT ename,job,sal FROM EMP ORDER BY sal)； (2)SELECT ename,job,sal,rownum FROM ( SELECT ename,job,sal FROM EMP ORDER BY sal) WHERE rownum<=5； 5、在HAVING子句中使用子查询 SELECT deptno,job,AVG(sal) FROM EMP GROUP BY deptno,job HAVING AVG(sal)>(SELECT sal FROM EMP WHERE ename='MARTIN')； ?基本条件查询 比较运算符:>,>=,<,<=,=,!= between a and b,in(a,b,c),not exists,is null,like …%_?,or,and, any,all等 把某一字段中内容在特定范围内的记录查询出来 SELECT StudentID, Score FROM SCore WHERE Score BETWEEN 60 AND 80把某一字段中内容与所列出的查询内容列表匹配的记录查询出来 SELECT SName AS 学员姓名,SAddress As 地址 FROM Students WHERE SAddre ss IN ('北京','广州','上海') ?SQL数据查询语句
实验三 SQL 数据高级查询 一、实验目的: 熟练掌握数据表的连接查询、嵌套查询和集合查询，为后继学习作准备。 二、实验属性 了解并掌握SQL企业控制管理器的使用; 掌握基本表的数据查询。 三、实验仪器设备及器材 a)计算机。 b)S QL SERVER 2005数据库。 四、实验要求(预习、实验前、实验中、实验后等要求) 1．预习教材第三章，熟悉SQL语句。 2．掌握多表查询语句的一般格式。 3．掌握多表无条件、有条件查询及查询结果排序与分组。 4．掌握多表连接、嵌套和集合查询语句的一般格式及各种使用方法。 五、实验原理 SQL语言应用。 六、实验步骤： (1) 启动SQL查询分析器； (2) 选择SQL SERVER后，按确认； (3) 选择数据库； (4) 验证如下例子：
1、集函数的使用 1.1 查询学生总人数。select count(*) from student 1.2 查询选修了课程的学生人数。 select count(distinct sno) from sc 1.3 查询选修C01号课程的学生平均成绩。 select avg(grade) from sc where cno='3' 1.4 查询选修C01号课程的学生最高分数。 select max(grade) from sc where cno='3' 2、查询结果分组 2.1 求每门课的课程号(Cno)及相应的选课人数。 select cno,count(sno) 选课人数from sc group by cno 2.2 查询选修了3门或3门以上课程的学生学号(Sno)。select sno 学号from sc group by sno having count(*)>2 3 、不同表之间的连接查询 3.1 查询每个学生及其选修课程的情况。 select student.*,sc.* from student,sc where student.sno=sc.sno
SQL高级查询——50句查询(含答案) --一个题目涉及到的50个Sql语句 --（下面表的结构以给出,自己在数据库中建立表.并且添加相应的数据,数据要全面些. 其中Student表中,SId为学生的ID） ------------------------------------表结构-------------------------------------- --学生表tblStudent（编号StuId、姓名StuName、年龄StuAge、性别StuSex） --课程表tblCourse（课程编号CourseId、课程名称CourseName、教师编号TeaId） --成绩表tblScore（学生编号StuId、课程编号CourseId、成绩Score） --教师表tblTeacher（教师编号TeaId、姓名TeaName） --------------------------------------------------------------------------------- --问题： --1、查询“001”课程比“002”课程成绩高的所有学生的学号； Select StuId From tblStudent s1 Where (Select Score From tblScore t1 Where t1.StuId=s1.stuId And t1.CourseId='001')> (Select Score From tblScore t2 Where t2.StuId=s1.stuId And t2.CourseId='002') --2、查询平均成绩大于60分的同学的学号和平均成绩； Select StuId,Avg(Score) as AvgScore From tblScore Group By StuId Having Avg(Score)>60 --3、查询所有同学的学号、姓名、选课数、总成绩； Select StuId,StuName, SelCourses=(Select Count(CourseId) From tblScore t1 Where t1.StuId=s1.StuId), SumScore=(Select Sum(Score) From tblScore t2 Where t2.StuId=s1.StuId) From tblStudent s1 --4、查询姓“李”的老师的个数； Select Count(*) From tblTeacher Where TeaName like '李%' --5、查询没学过“叶平”老师课的同学的学号、姓名； Select StuId,StuName From tblStudent Where StuId Not In
实验五高级查询 姓名：学号： 专业：班级： 同组人：实验日期： 【实验目的与要求】 1、熟练掌握IN子查询 2、熟练掌握比较子查询（尤其要注意ANY、ALL谓词如何用集函数代替） 3、熟练掌握EXISTS子查询（尤其是如何将全称量词和逻辑蕴含用EXISTS谓词代替） 4、熟练掌握复杂查询的select语句 【实验准备】 1．准备好测试数据 2．熟悉多表查询与嵌套查询的用法。 【实验内容】 5.1.准备工作 1.创建测试用数据库XSGL，并在其中创建三个表 本实验需用到student、course和SC表，其结构和约束如下： Student表结构及其约束为： 表5-1 student表结构和约束 列名称类型宽度允许空值缺省值主键说明 Sno char 8 否是学号 Sname varchar 8 否学生姓名Sex char 2 否男性别 Birth datetime 否出生年月Classno char 3 否班级号Entrance_date datetime 否入学时间Home_addr varchar 40 是家庭地址 Course表结构及其约束为： 表5-2 course表结构和约束 列名称类型宽度允许空值缺省值主键说明 cno Char 3 否是课程号Cname varchar 20 否课程名称Total_perior int 是总学时
credit int 是学分 SC表结构及其约束为： 表5-3 SC表结构和约束 列名称类型宽度允许空值缺省值主键外键说明 sno Char 8 否是学号,参照student表 cno char 3 否是课程号,参照course表 grade int 是否成绩其中成绩为百分制。 2.对表添加、修改、删除数据 向student表中插入如下数据: 表5-4 student表 Sno sname sex birth classno Entrance_date Home_addr sdept postcode 20050001 张虹男1984/09/011 051 2005/09/01 南京CS 200413 20050002 林红女1983/11/12 051 2005/09/01 北京CS 100010 20050003 赵青男1982/05/11 051 2005/09/01 上海MA 200013 向course表中插入数据: 表5-5 course表 cno Cname Total_perior credit 001 高数68 3 002 C语言程序设计75 4 003 JAVA语言程序设计68 3 向SC表中插入数据: 表5-6 SC表 Sno Cno grade 20050001 001 89 20050001 002 78 20050001 003 89 20050002 002 60 20050003 001 80 为达到更好的测试效果，请自行向数据库表中添加其它数据，使表中数据量达10条以 上，并使每个字段值表现出多样性。 5.2.复杂查询 （1）查询比“林红”年纪大的男学生信息。 SQL语句： select* from Student where Sex ='男'and
实验五 T-SQL高级查询（2） [实验目的] 通过本实验掌握使用INSERT/UPDATE/DELETE语句和SELECT语句中CASE函数和子查询的技能，掌握存储过程编写和使用https://www.doczj.com/doc/ae11416603.html,调用存储过程的技能。 [实验内容] 1.在上次实验的基础上使用CASE函数和子查询实现如下功能的查询语句：(根据实验4) a)返回所有课程的课程号、课程名和参加考核（即有成绩）的人数； b)返回所有具有不及格学生的课程号、课程名及不及格人数； c)返回所有不及格学生人数大于3人的课程号、课程名及不及格人数； d)返回计算机系学生各课程的课程号、课程名、不及格人数以及不及格率； 2.在上次实验的基础上使用INSERT/UPDATE/DELETE语句实现如下功能： a)建立一个结构与Course表相同的CrsBak表，将Course表中的记录全部复制到CrsBak中；并在该 表中完成下面几个功能： b)将所有有计算机系学生选修的课程学分数增加1； c)将所有有计算机系学生选修、且课程名称前三字不为“计算机”的课程名称前面增加“计算机”； d)删除没有学生选修的课程； 3.将上述1.d、2.d两个功能设计为存储过程： a)直接实现上述两个功能； b)利用参数将1.d修改为可由参数传入系别，返回指定系学生各课程的课程号、课程名、不及格人数 以及不及格率；利用参数将1.d修改为可由参数传入课程号，如指定课程号没有学生选修则删除， 否则将所有该课程的学生成绩改为NULL； c)使用EXEC语句调用存储过程，验证你的实现是否正确； d)模仿TestDB.cs通过https://www.doczj.com/doc/ae11416603.html,调用存储过程，并验证是否正确完成相应功能。 2.1 create table CrsBak ( Cno CHAR(6)PRIMARY KEY, Cname NVARCHAR(20)NOT NULL, Credit NUMERIC(3,1)CHECK(Credit>0), Semester TINYINT ) insert into CrsBak select*from course 2.2 update sc set grade=grade+1 where sno in(select sno from student where sdept='计算机')and grade<>100 2.3 update course set cname=case when left(cname,3)<>'计算机'then'计算机'+cname else cname end from course where cno in(select distinct cno from sc where sno in(select sno from student where sdept='计算机'))