数据库数据查询与统计-PPT.ppt

,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,单击此处编辑母版标题样式,欢迎大家,数据库数据查询与统计,掌握表数据的简单查询,掌握多表数据的连接查询,掌握子查询的建立和使用,学习目标,任务,1,：简单查询,基本查询、条件查询、结果排序,任务,2,：各种统计,函数的使用、使用分组、使用筛选,任务,3,：复杂查询,连接查询、子查询,示例数据库说明,客户信息表,(customer),：,员工信息表,(employee),：,产品信息表,(product),：,示例数据库说明,产品类别表：,订单信息表：,示例数据库说明,驾驶员信息表,(,jsy,),：,报刊订阅信息表（,bk,）：,任务,1,简单查询,基本查询,最简单的,SELECT,语句,条件查询,查询结果排序,返回,任务,1,简单查询,基本查询,最简单的,SELECT,语句,条件查询,查询结果排序,常规使用方式,*表示所有的列,使用,TOP,关键字,使用,DISTINCT,关键字,操作查询的列名,在结果中显示字符串,列为表达式,Into,子句,任务,1,简单查询,基本查询,最简单的,SELECT,语句,条件查询,查询结果排序,算术表达式,逻辑表达式,范围表达式,使用,IN,关键字,使用通配符,返回,任务,1,简单查询,基本查询,最简单的,SELECT,语句,SELECT,语句的常规使用方式,SELECT,列名,1,列名,2,列名,n,FROM,表名,例 在,companyinfo,数据库的,employee,表中查询所有员工的姓名、性别、出生日期。,SELECT,姓名,性别,出生日期,FROM employee,任务,1,简单查询,练习：在,companyinfo,数据库的,BK,表,(,报刊征订数据表,),中查询所有用户的姓名、地址、电话信息。,SELECT v_Name,Address,Tel FROM jsy,任务,1,简单查询,基本查询,最简单的,SELECT,语句,用*表示表中所有的列,例 在,employee,表中查询员工的所有信息。,SELECT *,FROM employee,练习：,查询驾驶员数据表中全部列的信息：,SELECT *FROM jsy,任务,1,简单查询,基本查询,最简单的,SELECT,语句,使用,TOP,关键字,SELECT TOP n|TOP n PERCENT,列名,1,列名,2,FROM,表名,例 从,companyinfo,数据库的,employee,表中返回前面,10,行的记录。,SELECT TOP 10*,FROM employee,练习：,查询驾驶员数据表中前,10,行的信息：,SELECT top 10*FROM jsy,任务,1,简单查询,基本查询,最简单的,SELECT,语句,使用,DISTINCT,关键字,例 从,companyinfo,数据库的订单表（,p_order,）中，检索已经被订购的产品。,SELECT DISTINCT,产品,ID,FROM p_order,练习：,查询驾驶员有那些邮政编码：,SELECT distinct postcode FROM jsy,去掉,distinct,的效果,任务,1,简单查询,基本查询,最简单的,SELECT,语句,操作查询的列名,用,AS,关键字来连接列表达式和指定的列名。,任务,1,简单查询,基本查询,最简单的,SELECT,语句,操作查询的列名,:,重命名列名,例 在,employee,表中查询每个人的薪水降低,30%,后的信息。,SELECT,姓名,薪水,as,原薪水,薪水,-,薪水*,0.3 ,目前薪水,FROM employee,任务,1,简单查询,基本查询,最简单的,SELECT,语句,在查询结果中显示字符串：将要增加的字符串用单引号括起来，然后和列名写在一起，中间用逗号分隔开。,例查询,employee,表的信息，要求显示结果为：姓名 实发工资为：薪水。,SELECT,姓名,实发工资为：,薪水,FROM employee,任务,1,简单查询,基本查询,最简单的,SELECT,语句,列为表达式,例 显示每种产品的价格降低,30%,的产品信息。,SELECT,产品名,单价,单价*,0.7 AS,新价格,FROM product,任务,1,简单查询,基本查询,最简单的,SELECT,语句,Into,子句：创建新表并将查询的结果插入新表中,例创建一个包含,employee,表中“姓名”和“薪水”字段，且名称为,new_ employee,的新表。,SELECT,姓名,薪水,INTO new_employee,FROM employee,任务,1,简单查询,基本查询,最简单的,SELECT,语句,练习,（,1,）查询,product,表的产品名；,（,2,）查询,product,表的所有信息；,（,3,）将,product,表的产品打,8,折后，以下面形式输出：,产品,ID,产品名,原单价,新单价,打折价格：打折价格,（,4,）分别输出,product,表的前,3,行和前,10%,的记录；,（,5,）创建一个包含,product,表中“产品名”和“单价”字段，且名称为,new_ product,的新表。,返回小节,任务,1,简单查询,（,6,）查询,bk,表的用户姓名；,（,7,）查询,bk,表的所有信息；,（,8,）将,bk,表的订购份数,(n_fs),乘,10,倍，以下面形式输出：,姓名,报刊名称,原份数,新份数：新份数,（,9,）分别输出,bk,表的前,3,行和前,10%,的记录；,（,10,）创建一个包含,bk,表中“,v_grxm,”和“,v_fs,”字段，且名称为,new_ bk,的新表。,返回小节,任务,1,简单查询,基本查询,带条件的查询,基本语法：,SELECT,列,1,，列,2,列,n】,FROM,表名,WHERE,查询条件,WHERE,子句常用的查询条件,查 询 条 件,运 算 符 号,算术表达式,=,、,、,=,、,、,!=3000,任务,1,简单查询,基本查询,带条件的查询,使用逻辑表达式,例在雇员表,employee,中查询特长为“书法”和“钢琴”的所有雇员信息。,SELECT *,FROM employee,WHERE,特长,=,书法,OR,特长,=,钢琴,任务,1,简单查询,基本查询,带条件的查询,使用范围表达式,例在雇员表,employee,中查询薪水在,3000,元至,4000,元的雇员的姓名和薪水。,SELECT,姓名,薪水,FROM employee,WHERE,薪水,BETWEEN 3000 AND 4000,想一想：若用算术表达式实现呢？,任务,1,简单查询,基本查询,带条件的查询,使用范围表达式,例在,product,表中查询库存量大于,200,而小于,100,的产品名、库存量和单价。,SELECT,产品名,库存量,单价,FROM product,WHERE,库存量,NOT BETWEEN 200 AND 100,想一想：若用算术表达式实现呢？,任务,1,简单查询,基本查询,带条件的查询,使用,IN,关键字,语法：表达式,NOT IN (,表达式,1,表达式,2,表达式,n),例 查询雇员表,employee,中所有特长为“计算机”、“钢琴”、“书法”的雇员的姓名、特长。,SELECT,姓名,特长,FROM employee,WHERE,特长,IN(,计算机,唱歌,书法,),想一想：若用逻辑表达式实现呢？,任务,1,简单查询,基本查询,带条件的查询,通配符的使用,，其含义见下表：,符 号,含 义,%,（百分号）,0,N,个任意字符,_,（下划线）,单个的任意字符,（封闭方括号）,方括号中列出的任意一个字符,任意一个没有在方括号中列出的字符,任务,1,简单查询,基本查询,带条件的查询,通配符的使用,例 列出雇员表,employee,中所有姓“章”的雇员的信息。,SELECT *,FROM employee,WHERE,姓名,LIKE,章,%,任务,1,简单查询,基本查询,带条件的查询,通配符的使用,例,6.18,查询雇员表,employee,中所有姓名中第二个字为“利”字的雇员的姓名和出生日期。,SELECT *,FROM employee,WHERE,姓名,LIKE _,立,%,任务,1,简单查询,基本查询,查询结果排序（,ORDER BY,子句）,语法：,ORDER BY,表达式,1 ASC|DESC,表达式,2 ASC|DESC,n,例 在订单表,p_order,中，查询产品,ID,、数量和订货日期，并按订货日期降序给产品排序。,SELECT,产品,ID,数量,订货日期,FROM p_order,ORDER BY,订货日期,desc,返回小节,任务,1,简单查询,基本查询,带条件的查询,练习 请写出下列各题的命令,（,1,）查询,1,号产品的信息；,（,2,）查询出单价在,10,元以上,20,元以下的产品,ID,和,产品名，并按产品,ID,降序排列；,（,3,）查询打印机和墨盒的库存量；,（,4,）查询联系方式中以,0311,打头的客户信息；,（,5,）查询特长是读书、旅游和音乐的雇员信息。,返回小节,任务,2,各种统计,聚合函数,聚 合 函 数,结 果,SUM(),数字表达式中所有值的和,AVG(),数字表达式中所有值的平均值,COUNT(),表达式中值的个数,COUNT(*),选定的行数,MAX(),表达式中的最大值,MIN(),表达式中的最小值,任务,2,各种统计,例 计算订单表,p_order,中所有产品已订购总额。,SELECT SUM(,数量,),FROM p_order,任务,2,各种统计,分组,GROUP BY,子句,在,SELECT,子句中的选项列表中出现的列，包含在聚合函数中或者包含在,GROUP BY,子句中。,例 在订单表,p_order,中按产品,ID,，查询每件产品的订购总和。,SELECT,产品,ID,sum(,数量,)as,总数量,FROM p_order,GROUP BY,产品,ID,任务,2,各种统计,例 在订单表,p_order,中按产品,ID,的种类分类，求出各种类型产品的平均单价以及各类产品的数量。,SELECT,产品,ID,avg(,单价,),平均单价,count(*),FROM product,GROUP BY,产品,ID,任务,2,各种统计,筛选,HAVING,子句,例 从,product,表查询平均价格超过,10,元的产品的种类，并按平均价格升序排列。,SELECT,类别,ID,avg(,单价,),平均价格,FROM product,GROUP By,类别,ID,HAVING avg(,单价,)10,ORDER BY avg(,单价,),返回,任务,2,各种统计,练习,（,1,）从,pruduct,表中查询共有多少类产品；,（,2,）从,pruduct,表中查询库存量最大和最小的产品；,（,3,）从,pruduct,表中查询总库存量超过,500,的产品类别，并按类别,ID,升序排列。,返回,任务,2,各种统计,练习,（,1,）从,bk,表中查询：订阅份数大于,10,岁的用户有那些，并按年龄从大到小排序；,（,2,）从,bk,表中查询每位订户订阅的份数；,（,2,）从,bk,表中查询订阅的份数大于,10,的用户姓名和份数；,（,3,）从,bk,表中查询订阅报刊大于,2,类的姓名和地址。,返回,任务,3,复杂查询,连接查询,内连接,内连接也叫自然连接，将两个表中的列进行比较，返回每对匹配的行，废弃两个表中不匹配的行。如果未指定连接类型，则默认设置为内连接。,返回,任务,3,复杂查询,连接查询,内连接,语法结构,语法一：,SELECT,列,FROM,表,1 inner JION,表,2,ON,表,1.,列,=,表,2.,列,语法二：,SELECT,列,FROM,表,1,，表,2,WHERE,表,1.,列,=,表,2.,列,返回,任务,3,复杂查询,连接查询,内连接,例 查询已订购了产品的公司的公司名称、联系人姓名和所订产品的产品,ID,和数量。,SELECT,公司名称,联系人姓名,产品,ID,数量,FROM customer JOIN p_order,ON customer.,客户,ID=p_order.,客户,ID,返回,练习,查询牛奶这种产品的类别名。,查询王孔若的所有订单。,查询东南实业公司的订单信息。,查询王孔若的所有客户信息,返回,任务,3,复杂查询,连接查询,外连接,外连接分为左外连接、右外连接和全外连接。,左外连接是对连接条件中左边的表不加以限制；,右外连接是对右边的表不加以限制；,全外连接是对两个表都不加以限制，所有两个表中的行都包括在结果集中。,返回,任务,3,复杂查询,连接查询,外连接,外连接的语法如下。,SELECT,列,FROM,表,1 LEFT/RIGHT/FULL JOIN,表,2,ON,表,1.,列,1=,表,2.,列,2,返回,任务,3,复杂查询,连接查询,外连接,INSERT INTO product(,产品,ID,产品名,),VALUES(5,鱼缸,),INSERT INTO category(,类别,ID,类别名,),VALUES(8,纺织品,),例 分别用左连接、右连接和全外连接查询产品名和类别名，比较查询结果。,返回,select,产品,ID,产品名,类别名,from product left join category,on product.,类别,ID=category.,类别,ID,练习,查询所有产品名和类别名；,查询所有雇员及其订单信息。,返回,任务,3,复杂查询,子查询,几条说明：,一个,SELECT-FROM-WHERE,语句称为一个查询块，有时一个查询块无法完成查询任务，需要一个子查询的结果作为主查询语句的条件。,子查询在,SELECT,、,INSERT,、,UPDATE,、,DELETE,语句中都可以使用。,嵌套在另一个查询块的条件子句中的查询被称为嵌套查询，,SQL Server,允许多层嵌套查询，嵌套查询的求解方法一般是由里向外进行处理。,子查询的返回结果是一个值的嵌套查询称为单值嵌套查询。是一列值的嵌套查询称为多值嵌套查询。,返回,任务,3,复杂查询,子查询,单值子查询,可以使用,、,、,=,、,=,、,!=,或,等比较运算符。,例 查询“鼠标”所属的类别名和相应的说明。,SELECT,类别名,说明,FROM category,WHERE,类别,ID=(SELECT,类别,ID,FROM product,WHERE,产品名,=,鼠标,),返回,任务,3,复杂查询,子查询,单值子查询,例 查询所有订购了“打印机”产品的公司信息。,SELECT *,FROM customer,WHERE,客户,ID in(SELECT,客户,ID,FROM p_order,WHERE,产品,ID=(SELECT,产品,ID,FROM product,WHERE,产品名,=,打印机,),返回,任务,3,复杂查询,子查询,多值子查询,可以使用,NOTIN,、,NOTEXISTS,等操作符,例,6.28,查询类别,ID,为“,1”,的所有订单的信息。,SELECT,订单,ID,产品,ID,数量,订货日期,FROM P_ORDER,WHERE,产品,ID IN (SELECT,产品,ID,FROM PRODUCT,WHERE,类别,ID=1),思考：如果本体命令中,IN,前加,NOT,，查询的是什么信息？,返回,任务,3,复杂查询,子查询,多值子查询,例,6.29,查询所有订购了“鼠标”产品的公司的公司名称和联系方式。,SELECT,公司名称，联系方式,FROM customer,WHERE,客户,ID in(SELECT,客户,ID,FROM p_order,WHERE,产品,ID=(SELECT,产品,ID,FROM product,WHERE,产品名,=,鼠标,),思考：如果本体命令中,IN,前加,NOT,，查询的是什么信息？,返回,任务,3,复杂查询,子查询,多值子查询,例 查询客户,ID,为,1,的公司订购的所有订单,ID,和数量。,SELECT,订单,ID,数量,FROM p_order,WHERE EXISTS(SELECT*,FROM customer,WHERE,客户,ID=1),返回,任务,3,复杂查询,子查询,练习,（,1,）查询东南实业的订单情况；,（,2,）查询打印机属于哪类产品；,返回,数据查询是数据库系统中最基本也是最重要的操作。本项目主要介绍了各种查询方法，包括单表条件查询、单表多条件查询、多表多条件查询、嵌套查询，并对查询结果进行排序、分组和汇总等操作。,函数的应用！,小结,任务,4,子查询语句在,insert.update.delete,中的应用：,表数据操作（插入、更新、删除）,图形界面表数据插入、更新和删除（环境操作）,SQL,语句表数据插入,例 在,companyinfo,数据库的,customer,表中插入一条记录,(,全部列,),。,INSERT INTO customer,VALUES,（,11,嘉年实业,王亮,4573166,东方红大道,226,号,467000,）,（新建表,customer,）,任务,3,表及其维护,表数据操作（插入、更新、删除）,SQL,语句表数据插入,例 在表,customer,中插入部分列，只输入客户,ID,、公司名称和联系人姓名。,INSERT INTO customer,（客户,ID,公司名称,联系人姓名）,VALUES,（,11,阳光科技,张靖）,任务,3,表及其维护,表数据操作（插入、更新、删除）,SQL,语句表数据更新（一列、多列，指定行、全部行）,例 将,customer,表中东南实业公司的联系人姓名改为“施华”。,UPDATE customer,SET,联系人姓名,=,施华,WHERE,公司名称,=,东南实业,任务,3,表及其维护,表数据操作（插入、更新、删除）,SQL,语句表数据更新（一列、多列，指定行、全部行）,例 将,customer,表中东南实业公司的联系人姓名改为“施华”。,UPDATE customer,SET,联系人姓名,=,施华,公司名称,=,东南建工,WHERE,公司名称,=,东南实业,任务,3,表及其维护,表数据操作（插入、更新、删除）,SQL,语句表数据更新（一列、多列，指定行、全部行）,例 将,customer,表中东南实业公司的联系人姓名改为“施华”。,UPDATE customer,SET,联系人姓名,=,施华,WHERE,公司名称,=,东南实业,任务,3,表及其维护,表数据操作（插入、更新、删除）,SQL,语句表数据更新（一列、多列，指定行、全部行）,例 将,customer,表中东南实业公司的联系人姓名改为“施华”。,UPDATE customer,SET,联系人姓名,=,施华,任务,3,表及其维护,表数据操作（插入、更新、删除）,SQL,语句删除表数据,例 删除,customer,表中公司名称为“森通”的记录。,DELETE FROM customer,WHERE,公司名称,=,森通,例 删除,customer,表中所有的记录。,DELETE FROM customer,任务,3,表及其维护,