qq群关系数据库查询.docx

1、qq群关系数据库查询qq群关系数据库查询篇一：数据库简单与复杂查询 例5.1 部门的编号、名称和位置 select * from department /星号“*”可以在select子句中表示“所有的属性” 例5.2部门的编号、名称和位置 select dept_no,dept_name,location from department 例5.3 查询位于Dallas的部门编号和名称 select dept_no,dept_name from department WHERE location=Dallas 例5.4 查询预算额的0.51倍大于60000的项目名称 SELECT project

2、_name FROMproject WHERE budget*0.5160000 例5.5查询在部门编号为d1的部门工作且职员编号为25348或职工名称为Matthew 的雇员信息。 SELECT * FROMemployee WHERE emp_no=25348OR emp_fanme=Matthew AND dept_no=d1 例5.6 查询不在部门编号为d1的部门工作的职员编号和姓名 SELECT emp_no,emp_lname FROMemployee WHERE NOT dept_no=d1 例5.7 获取编号既不是10102也不是9031的职员的所有列。 SELECT * FR

3、OMemployee WHERE emp_no NOT IN (10102 ,9031) 例5.8 获取预算在$95 000 $120 000之间的所有项目的名称和预算。 SELECT project_name,budget FROMproject WHERE budget BETWEEN 95000 AND 120000 例5.9 获取预算小于$95 000和大于 $120 000之间的所有项目的名称。 SELECT project_name FROMproject WHERE budget NOT BETWEEN 95000 AND 120000 例5.10 获取为项目2工作的，具有未知工

4、作的所有职员的职员编号和相应的项目编号。 SELECT emp_no,project_no FROMworks_on WHERE project_no=p2 AND job IS NULL 系统函数ISNULL允许在显示时用指定的数值来替代NULL SELECT emp_no,ISNULL(job,Job unknown task ) FROMworks_on WHERE project_no=p2 AND job IS NULL 例5.11 找出所有姓是以字母J开头的职员的名字和编号。 SELECT emp_fname,emp_lname,emp_no WHERE emp_lname LIK

5、E j% 例5.12 找出所有名中第二个字母是a的职员的名字和编号。 SELECT emp_fname,emp_lname,emp_no FROMemployee WHERE emp_fname LIKE _a% 例5.13 获取所在地名从C到F的字母打头的所有部门的详细资料。 SELECT * FROMDEPARTMENT WHERE LOCATION LIKE C-F% 例5.14 获取姓的打头字母不是J,K,L,M,N,O,并且名的开头字母是E或者Z的所有职员的编号和名字。 SELECT * FROMEMPLOYEE WHERE EMP_LNAME LIKE J-O% AND emp_f

6、name LIKE EZ% 例5.15所有被方括号括起来的通配符（%,_,）都表示本来的意思。 SELECT project_no,project_name FROMproject WHERE project_NAME LIKE %_% 例5.16 获取在研究部门工作的所有职员的名和姓。 SELECT emp_fname,emp_lname FROMemployee WHERE dept_no= (SELECT dept_no FROM DEPARTMENT WHERE dept_name=research) 例5.17 获取职员moser参与的项目编号。 SELECT DISTINCT pr

7、oject_no FROMworks_on WHERE emp_no= (SELECT emp_no FROM employee WHERE emp_lname=moser ) 例5.18 获取部门位于Dallas的所有职员的详细信息。 SELECT * FROM employee WHERE dept_no in (SELECT dept_no FROM department WHERE location=Dallas ) 例5.19 获取为Apollo项目工作的所有职员的姓。 SELECT emp_lname FROM employee ( SELECT emp_no FROM works

8、_on WHERE project_no in (select project_no from project where project_name=Apollo ) ) 例5.20 获取不是在项目上花费了最多时间的所有职员的编号、项目编号和工作名称。 SELECT DISTINCT EMP_NO,PROJECT_NO,JOB FROM works_on WHERE enter_date any ( SELECT enter_date FROM works_on ) 例5.21 获取为项目p1工作的所有职员的名和姓。 SELECT DISTINCT emp_fname,emp_lname FR

9、OM employee WHERE emp_no = any ( SELECT emp_no FROM works_on where project_no=p1 ) 例5.22 选择编号大于等于10000的所有职员的名字。 SELECT DISTINCT emp_fname,emp_lname FROM (SELECT * FROM employee WHERE emp_no=10000) AS emp_no10000 例5.23 使用项目编号和工种对所有职员分组。 SELECT project_no,job FROM works_on GROUP BY project_no, job 例5.

10、24 看如下的查询例子，分析正确与否 SELECT emp_lname,MIN(emp_no) FROM employee 例5.25 获取最小的职员编号 SELECT MIN(emp_no) min_employee_number FROM employee 例5.26 获取最小的职员编号和姓 SELECT emp_no,emp_lname FROM employee WHERE emp_no= ( SELECT MIN(emp_no) min_employee_number FROM employee) 例5.26 获取表WORKS_ON中进入时间最晚的经理的职员编号 SELECT emp

11、_no FROM WORKS_ON WHERE enter_date= ( SELECT MAX(enter_date) FROM WORKS_ON WHERE job=Manager ) 例9.27 计算所有项目预算的总和 SELECT SUM(BUDGET) SUM_OF_BUDGETS FROM PROJECT 例5.28 计算每个项目中不同工种的数目 SELECT project_no,COUNT(DISTINCT job)job_count FROM works_on group by project_no 例5.29 每个项目中有多少职员为其工作 SELECT project_no

12、,COUNT(*) emp_count FROM works_on group by project_no 例5.30 获取招募的总人数少于4个的项目 SELECT project_no FROM works_on group by project_no having count(*)4 例5.31 获取职员的名字和职员编号，以职员编号升序排列 SELECT emp_no,emp_fname,emp_lname FROM employee ORDER BY emp_no asc；/ desc表示降序，asc表示升序。Group by默认使用升序 5.32 获取编号小于2000的职员所在的部门和

13、其编号，并以姓和名升序排列 SELECT emp_fname,emp_lname,dept_no FROM employee where emp_no20000 ORDER BY emp_fname,emp_lname 5.33 对于每个项目编号，取得其所有的项目编号以及职员数量，以职员数量降序排列。 SELECT project_no,COUNT(*)emp_quantity FROM works_on group by project_no ORDER BY 2 DESC 5.34 获取属于部门D1或者参加项目的时间早于1998年1月1日的所有职员的编号，并以升序排列。 SELECT em

14、p_no FROM employee where dept_no=d1 UNION SELECT emp_no FROM works_on WHERE enter_date01.01.1998 ORDER BY 1 -建库与建表 /*Department(dept_no, dept_name,location) Employee(emp_no,emp_fname,emp_lname,dept_no) Project(project_no, project_name, budget) Works_on(emp_no, project_no,job,enter_date)*/ CREATE DAT

15、ABASE Branch ON PRIMARY ( NAME = Branch, FILENAME = D:Web信息系统2databasebranch.mdf, SIZE = 100MB, MAXSIZE = 200, FILEGROWTH = 20) LOG ON ( NAME = branchlog1, FILENAME = D:Web信息系统2databasebranchlog.ldf, SIZE = 100MB, MAXSIZE = 200, FILEGROWTH = 20) use Branch go CREATE TABLE Department( dept_namevarcha

16、r(50) NOT NULL, dept_no varchar(8) NOT NULL, location varchar(20), CONSTRAINT prim_DepartmentPRIMARY KEY(dept_no ), ) CREATE TABLE Employee( emp_fname varchar(50) NOT NULL, emp_lname varchar(50) NOT NULL, emp_novarchar(8) NOT NULL, dept_novarchar(8) NOT NULL, CONSTRAINT prim_EmployeePRIMARY KEY(emp_

17、no), CONSTRAINT foreign_Department FOREIGN KEY(dept_no) REFERENCES Department(dept_no) ) CREATE TABLE Project( project_name char(50) NOT NULL, project_no char(8)NOT NULL, city char(20), budgetfloat, CONSTRAINTprim_ProjectPRIMARY KEY(project_no), CONSTRAINTC_NAME CHECK (budget=0) 篇二：数据库查询操作详解 一、 数据查询

18、-单表查询 (1)查询语句格式 Select all|distinct 目标列表达式 ,目标列表达式? From 表名或视图 ,表名或视图? where 条件表达式 group by 列名1 having 条件表达式 order by 列名2 asc|desc ; 注：all|distinct中all为缺省值，取消结果中的重复列则用distinct； asc|desc中asc为缺省值，表示按照升序排列。对于空值，若按照升序排，则含空值的元组显示在最后面；若按降序排，则空值的元组最先显示。 (2)查询指定列 a)查询部门表dept中所有部门的详细信息，并且列名用汉字表示。 select DNO,

19、DNAME,ADDR from dept ; b)查询部门表dept中人力资源部的部门编号。 select DNO from dept where DNAME = 人力资源部 ; 目标列表达式中各个列的先后顺序可以与表中的顺序不一致. (3)查询全部列 查询全体学生的详细记录 Select * From Student ; (4)将查询结果的列名用别名显示 查询部门表dept中所有部门的详细信息，并且列名用汉字表示。 select DNO 部门编号 ,DNAME 部门名称 ,ADDR 部门地址 from dept ; (5)在查询的结果中插入新的一列用来显示指定的内容 Select Sname

20、 NAME ,Year of Birth: BIRTH ,Sbirth BIRTHDAY ,Sdept DEPARTMENT From Stuent ; 则显示的结果中，每个元组的第二列均为”Year of Birth:”，此列在原数据库中是不存在的. (6)查询经过计算的值 Select 子句的目标列表达式 不仅可以是表中的属性列，也可以是表达式。 例：查询全体学生的姓名及其出生年月 Select Sname ,2022 Sage /*当时年份减去年龄为出生年月 From Stufent ; (7)设置查询显示的字母全为大写(或小写) Select Sname ,Year of Birth:

21、 ,2022 Sage ,LOWER(Sdept) From Student ; 此时Sdept显示的结果全为小写 Select Sname ,Year of Birth: ,2022 Sage ,UPPER(Sdept) From Student ; 注：要设置查询表中的属性列名的大小写可以用LOWER,UPPER。 (8)消除取值重复的行 a)查询雇员表empl中出现的所有部门编号，要求无重复。 select distinct DNO from EMPL ; b)查询项目表proj中所有项目名称。 select PNAME from PROJ 等价于 select all PNAME fr

22、om PROJ (9)查询满足条件的元组 注：Between后是范围的下限，and后是范围的上限.查询结果中包含上下限的结果. a) 查询成绩不及格的学生的学号 Select distinct Sno From SC Where Grade 60 ; b) 查询年龄在20到30(包含20及30)之间的学生的姓名、系别和年龄 Select Sname ,Sdept ,Sage From Student Where Sage between 20 and 30 ; c) 查询计算科学系(CS) ,数学系(MA) ,信息系(IS)学生的姓名和性别 Select Sname ,Ssex From St

23、udent Where Sdept in (CS ,MA ,IS) ; (10)含通配符”%”的查询 %(百分号)：代表任意长度(长度可以为0)的字符串，例如a%b代表以a开头且以b结尾的任意长度的字符串，acb ,afdsagasdsab ,ab都满足。 例：查询雇员表empl中姓名以“伟“字结尾的员工信息。 select * from EMPL where ENAME like %伟 ; (11)含通配符”_”的查询 _(下划线)：代表任意单个字符，例a_b代表以a开头且以b结尾的长度为3的字符串。 例：查询姓“欧阳”且全名3个汉字的学生的姓名 Select Sname From Stud

24、ent Where Sname like 欧阳_ ; 注：一个汉字占两个字符的位置 (12)字符串本身含通配符的查询 使用使用escape 换码字符。 查询以”DB_”开头，且倒数第三个字符为i的课程的详细情况。 Select * From Student Where Cname like DB_%i_ escape ; 注：escape 表示”为换码字符，则第一个”_”是普通的字符，后两个”_”表示通配符。 (13)涉及空值的查询 例：查询所有有成绩的学生的学号 Select Sno From SC Where Grade IS NULL ; 注：此处“IS”不能用”=”替代 (14)带排序

25、的查询 查询工作表job中的工作信息，结果按工作天数升序排列。 select * from JOB order by DAYS asc ; 等价于 select * from JOB order by DAYS asc ; 查询雇员表empl中所有员工的详细信息，结果按员工姓名降序排列。 select * from EMPL order by ENAME desc ; (15)含聚集函数的查询 聚集函数主要有： Count (distinct|all *) Count (distinct|all 列名) Sum (distinct|all 列名) Avg (distinct|all 列名) M

26、ax (distinct|all 列名) Min (distinct|all 列名) /统计元组个数 /统计一列中值的个数 /计算一列值的总和(此列必须是数值型) /计算一列值的平均值(此列必须是数值型) /求一列值中的最大值 /求一列值中的最小值 注：distinct|all缺省时为all. 在聚集函数遇到空值时，除count(*)外，都跳过空值而只处理非空值. 例：查询1号课程的学生的最高分数 Select max(Grade) From SC Where Cno = 1 ; (16)到GROUP BY子句的查询 a)求各个课程号及相应的选课人数 select Cno ,count(Sno

27、) from SC group by Cno ; b)查询选修了3们以以上的课程的学生的学号 select Sno from SC croup by Sno having count(*) 3 ; 注：where子句与having子句短语的区别在于作用对象不同。Where子句作用于基本表或视图，从中选择满足条件的元组；having子句作用与组，从中选择满足条件的组。 二、 数据查询-连接查询 若一个查询同时设计两个以上的表，则称之为连接查询。 (1) 等值与非等值连接查询 格式如下： 表名1.列名1 比较运算符 表名2. 列名2 其中主要的比较运算符有：= , ,= ,= ,!=(或) 等.

28、此外连接谓词还可以有如下的形式： 表名1. 列名1 between 表名2. 列名2 and 表名2. 列名2 当连接运算符为=时，称为等值连接，否则称为非等值连接。 注：当属性列在查询的所有表中是唯一的时候则可以去掉前面的表名，否则必须加上表名。 例： 查询每个学生及其选修课程的情况. Select Student.* ,SC.* From Student ,SC Where Student.Sno = SC.Sno ; (2) 自身连接 例：查询每一门课程的间接先修课(即先修课的先修课) 分析：此时为Course表的自身连接，故要为Course表去两个别名，一个是first,一个是seco

29、nd。 Select first.Cno ,second.Cno From Course first ,Course second Where first.Cpno = second.Cno ; (3)外连接 在上例中，没有显示202215123和202215125两个学生的信息，原因在于他们没有选课。有时想以Student表为主体列出每个学生的基本情况及其选课情况，则需要使用外连接。 用外连接做上面的例题： Select Student.Sno ,Sname ,Ssex ,Sage ,Sdept ,Cno ,Cgrade From Student LEFT JOIN SC ON (Student.Sno = Sc.Sno) ; /*也可以用USING来去掉上面结果中的重复值： From Student LEFT JOIN SC USING(Sno) ; */ 注：做链