mySQL练习题
练习1:
创建表以及数据已经给出,按照下列要求进行查询.
表:student
1 CREATE TABLE student( 2 id INT, 3 NAME VARCHAR(20), 4 chinese FLOAT, 5 english FLOAT, 6 math FLOAT 7 ); 8 9 INSERT INTO student(id,NAME,chinese,english,math) VALUES(1,'张小明',89,78,90); 10 INSERT INTO student(id,NAME,chinese,english,math) VALUES(2,'李进',67,53,95); 11 INSERT INTO student(id,NAME,chinese,english,math) VALUES(3,'王五',87,78,77); 12 INSERT INTO student(id,NAME,chinese,english,math) VALUES(4,'李一',88,98,92); 13 INSERT INTO student(id,NAME,chinese,english,math) VALUES(5,'李来财',82,84,67); 14 INSERT INTO student(id,NAME,chinese,english,math) VALUES(6,'张进宝',55,85,45); 15 INSERT INTO student(id,NAME,chinese,english,math) VALUES(7,'黄蓉',75,65,30); 16 17 -- 查询表中所有学生的信息。 18 select * from student 19 20 -- 查询表中所有学生的姓名和对应的英语成绩。; 21 select name ,english from student 22 23 -- 统计每个学生的总分。 24 select chinese+english+math 总份额 from student; 25 26 -- 在所有学生总分数上加10分特长分。 27 select chinese+english+math+10 总份额 from student; 28 29 -- 使用别名表示学生分数。 30 select chinese 中文,english 英语,math 数学 from student; 31 32 -- 查询姓名为李一的学生成绩 33 select chinese,english,math from student where name ='李一' 34 35 -- 查询英语成绩大于90分的同学 36 select NAME from student where english>90 37 38 -- 查询总分大于200分的所有同学 39 select NAME from student where (chinese+english+math)>200; 40 41 -- 查询英语分数在 80－90之间的同学 42 SELECT NAME from student where english between 80 and 90;
练习2:
部门表/员工表/工资登记表 以及数据已经给出,按照要求查询.
表emp:
表salgrade:
表dept:
1 -- 部门表 2 CREATE TABLE DEPT( 3 DEPTNO INT PRIMARY KEY, 4 DNAME VARCHAR(14), -- 部门名称 5 LOC VARCHAR(13)-- 部门地址 6 ) ; 7 INSERT INTO DEPT VALUES (10,'ACCOUNTING','NEW YORK'); 8 INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS'); 9 INSERT INTO DEPT VALUES (30,'SALES','CHICAGO'); 10 INSERT INTO DEPT VALUES (40,'OPERATIONS','BOSTON'); 11 12 13 -- 员工表 14 DROP TABLE EMP; 15 CREATE TABLE EMP( 16 EMPNO INT PRIMARY KEY, -- 员工编号 17 ENAME VARCHAR(10), -- 员工姓名 18 JOB VARCHAR(9), -- 员工工作 19 MGR INT, -- 员工直属领导编号 20 HIREDATE DATE, -- 入职时间 21 SAL DOUBLE, -- 工资 22 COMM DOUBLE, -- 奖金 23 DEPTNO INT, -- 所在部门 24 FOREIGN KEY (DEPTNO) REFERENCES DEPT(DEPTNO)); -- 关联dept表 25 26 -- ALTER TABLE EMP ADD FOREIGN KEY (DEPTNO) REFERENCES DEPT(DEPTNO); 27 INSERT INTO EMP VALUES(7369,'SMITH','职员',7566,"1980-12-17",800,NULL,20); 28 INSERT INTO EMP VALUES(7499,'ALLEN','销售员',7698,'1981-02-20',1600,300,30); 29 INSERT INTO EMP VALUES(7521,'WARD','销售员',7698,'1981-02-22',1250,500,30); 30 INSERT INTO EMP VALUES(7566,'JONES','经理',7839,'1981-04-02',2975,NULL,20); 31 INSERT INTO EMP VALUES(7654,'MARTIN','销售员',7698,'1981-09-28',1250,1400,30); 32 INSERT INTO EMP VALUES(7698,'BLAKE','经理',7839,'1981-05-01',2850,NULL,30); 33 INSERT INTO EMP VALUES(7782,'CLARK','经理',7839,'1981-06-09',2450,NULL,10); 34 INSERT INTO EMP VALUES(7788,'SCOTT','职员',7566,'1987-07-03',3000,2000,20); 35 INSERT INTO EMP VALUES(7839,'KING','董事长',NULL,'1981-11-17',5000,NULL,10); 36 INSERT INTO EMP VALUES(7844,'TURNERS','销售员',7698,'1981-09-08',1500,50,30); 37 INSERT INTO EMP VALUES(7876,'ADAMS','职员',7566,'1987-07-13',1100,NULL,20); 38 INSERT INTO EMP VALUES(7900,'JAMES','职员',7698,'1981-12-03',1250,NULL,30); 39 INSERT INTO EMP VALUES(7902,'FORD','销售员',7566,'1981-12-03',3000,NULL,20); 40 INSERT INTO EMP VALUES(7934,'MILLER','职员',7782,'1981-01-23',1300,NULL,10); 41 42 -- 工资等级表 43 CREATE TABLE SALGRADE( 44 GRADE INT,-- 等级 45 LOSAL DOUBLE, -- 最低工资 46 HISAL DOUBLE ); -- 最高工资 47 48 INSERT INTO SALGRADE VALUES (1,500,1000); 49 INSERT INTO SALGRADE VALUES (2,1001,1500); 50 INSERT INTO SALGRADE VALUES (3,1501,2000); 51 INSERT INTO SALGRADE VALUES (4,2001,3000); 52 INSERT INTO SALGRADE VALUES (5,3001,9999); 53 54 -- 1、查找部门30中员工的详细信息。 55 select * from EMP where DEPTNO=30; 56 57 -- 2、找出从事职员工作的员工的编号、姓名、部门号。 58 select EMPNO, ENAME ,DEPTNO from EMP where JOB ='职员' 59 60 -- 3、检索出奖金多于基本工资的员工信息。 61 select * from EMP where COMM>SAL 62 63 -- 4、检索出奖金多于基本工资60%的员工信息。 64 select * from EMP where comm>(0.6*SAL) 65 66 -- 5、找出姓名中包含A的员工信息。 67 select * from EMP where ENAME like '%A%' 68 69 -- 6、找出姓名以A、B、S开始的员工信息。 70 71 select * from EMP where ENAME like 'A%' || ENAME like 'B%' || ENAME like'S%' 72 -- 7、找到名字长度为7个字符的员工信息。 73 select * from EMP where ENAME like '_______' 74 75 -- 8、名字中不包e符的员工信息。 76 77 78 select * from EMP where ENAME not in ( 79 select ENAME from EMP where ENAME like '%R%' 80 ); 81 82 select * from EMP where ENAME not like '%R%' 83 84 select * from EMP where !( ENAME like '%R%') 85 86 -- 9、返回员工的详细信息并按姓名升序排序。 87 select * from EMP ORDER BY ENAME 88 89 -- 10、返回员工的信息并按姓名降序,工资升序排列。 90 select * from EMP ORDER BY ENAME DESC ,SAL ASC 91 92 -- 11、计算员工的日薪(按30天)。 93 SELECT ENAME,ROUND(SAL/30, 2) from EMP 94 95 -- 12、找出获得奖金的员工的工作。 96 select JOB from EMP where COMM>0; 97 98 -- 13、找出奖金少于100或者没有获得奖金的员工的信息。 99 select * from EMP where COMM<100 || ISNULL(COMM) 100 101 -- 14、找出10部门的经理、20部门的职员 的员工信息。 102 select *from EMP where (JOB='经理' && DEPTNO=10) || (JOB='职员' && DEPTNO=20) 103 104 -- 15、找出10部门的经理、20部门的职员 或者既不是经理也不是职员但是工资高于2000元的员工信息。 105 select *from EMP where (JOB='经理' && DEPTNO=10) 106 || (JOB='职员' && DEPTNO=20) 107 || (JOB!='经理' && JOB!='职员' && SAL>2000)
练习3:
创建employee表,表结构及内容已经给出:
表employee:
1 CREATE TABLE employee( 2 id INT, 3 NAME VARCHAR(20), 4 sex VARCHAR(10), 5 birthday DATE, 6 salary FLOAT, 7 RESUME TEXT 8 ); 9 INSERT INTO employee VALUES(1,"zhangsan","male","1980-11-25",2000,"good body"); 10 INSERT INTO employee VALUES(2,"lisi","male","1980-04-25",1000,"good body"); 11 INSERT INTO employee VALUES(3,"xiaohong","female","1978-11-25",4000,"good girl"); 12 13 select* from employee; 14 -- 将所有员工薪水修改为5000元。 15 update employee set salary=5000; 16 -- 将姓名为’zhangsan’的员工薪水修改为3000元。 17 update employee set salary=3000 where name='zhangsan' 18 -- 将姓名为’lisi’的员工薪水修改为4000元,sex改为female。 19 update employee set salary=4000,sex='female' where name ='lisi'; 20 -- 将xiaohong的薪水在原有基础上增加1000元。 21 update employee set salary = salary+1000 where name ='xiaohong';
练习4:
创建表employee_test,表结构 以及表数据已经给出:
表employee_test:
1 create table employee_test( 2 id int(11) , 3 name varchar(20), 4 sex varchar(10), 5 birthday date , 6 salary float(10,2) , -- 薪水 7 bonus float(10,2) , -- 奖金 8 department varchar(20), -- 部门 9 resume varchar(200) -- 简介 10 ); 11 12 13 -- 插入员工数据 14 insert into employee_test values 15 (1,'zhangsan','male','1980-11-25',2000,100,'总裁办','good body'), 16 (2,'lisi','male','1980-04-25',1000,200,'研发部','good body'), 17 (3,'xiaohong','female','1978-11-25',4000,100,'财务部','good girl'), 18 (4,'wangwu','male','1981-01-25',4000,400,'人事部','good body'), 19 (5,'zhaoliu','male','1978-12-25',2000,NULL,'人事部','good body'), 20 (6,'tianqi','female','1998-05-25',2000,100,'人事部','good girl'), 21 (7,'renba','male','1968-10-25',3000,500,'财务部','good body'); 22 23 1. 查询表中所有员工的信息。 24 select * from employee_test; 25 2. 查询表中所有员工的姓名和对应的薪水。 26 select name ,salary from employee_test ; 27 3. 使用汉语展示员工信息。(列名翻译成中文展示) 28 select id 编号,name 名字,sex 性别, birthday 生日,salary 薪水, bonus 奖金,department 部门, resume 备注 from employee_test; 29 4. 查询姓名为zhangsan的员工的薪水 30 select salary from employee_test where name='zhangsan'; 31 5. 查询姓名为zhangsan的员工的总收入 32 select salary+bonus 总收入 from employee_test where name='zhangsan'; 33 6. 查询薪水大于3000的员工信息 34 select * from employee_test where (salary+bonus)>3000; 35 7. 查询总收入大于4000的员工的姓名 部门 薪水 奖金 36 select name,department,salary,bonus from employee_test where (salary+bonus)>4000; 37 8. 查询80后的员工 38 select * from employee_test where birthday>'1980-1-1'; 39 9. 查询所有女性薪水大于4000的 40 select * from employee_test where salary>=4000 and sex='female'; 41 10. 查询所有女性薪水大于4000的员工按薪水降序排列 42 select * from employee_test where salary>=4000 and sex='female' order by salary; 43 11. 查询各个部门员工数量 44 select department,COUNT(*) from employee_test group by department; 45 12. 查询各个部门的员工数量及薪水支出 46 select department,COUNT(*), salary from employee_test group by department; 47 13. 查询各个部门的员工数量及总支出 48 select department,COUNT(*), salary+bonus from employee_test group by department; 49 14. 查询公司男性和女性的数量 50 select sex,COUNT(*) from employee_test group by sex; 51 15. 查询公司男性员工和女性员工的收入总和 52 select sex,SUM(salary)+SUM(bonus) from employee_test group by sex; 53 16. 查询公司中男性员工的收入总和 54 select sex,SUM(salary)+SUM(bonus) from employee_test where sex='male'; 55 17. 查询公司中总支出大于9000的部门 56 select department, salary+IFNULL(bonus,0) sumgongzi from employee_test group by department HAVING sumgongzi>=9000; 57 18. 查询公司中所有zhang姓员工的平均工资 58 select AVG(salary) from employee_test where name like 'zhang%'; 59 19. 查询公司中zhang姓员工的工资总和 60 select SUM(salary) from employee_test where name like 'zhang%'; 61 20. 查询公司中zhang姓员工的总收入 62 select SUM(salary)+SUM(bonus) from employee_test where name like 'zhang%';
练习5:
从创建表stu,表的结构以及表的数据已经给出.
1 CREATE TABLE stu ( 2 id INT,-- 编号 3 NAME VARCHAR ( 20 ),-- 姓名 4 age INT,-- 年龄 5 sex VARCHAR ( 5 ),-- 性别 6 address VARCHAR ( 100 ),-- 地址 7 math INT,-- 数学 8 english INT -- 英语 9 10 ); 11 12 INSERT INTO stu ( id, NAME, age, sex, address, math, english ) 13 VALUES 14 ( 1, '马云', 55, '男', '杭州', 66, 78 ), 15 ( 2, '马化腾', 45, '女', '深圳', 98, 87 ), 16 ( 3, '马景涛', 55, '男', '香港', 56, 77 ), 17 ( 4, '柳岩', 20, '女', '湖南', 76, 65 ), 18 ( 5, '柳青', 20, '男', '湖南', 86, NULL ), 19 ( 6, '刘德华', 57, '男', '香港', 99, 99 ), 20 ( 7, '马德', 22, '女', '香港', 99, 99 ), 21 ( 8, '德玛西亚', 18, '男', '南京', 56, 65 ); 22 23 -- 查询stu表中的所有数据 24 select * from stu; 25 -- 查询学生的姓名 及数学分数 26 select name ,math from stu; 27 -- 查询学生的姓名 及数学分数 总分 28 select name ,math,SUM(math)+SUM(english); 29 -- 查询姓名叫马云 30 SELECT name from stu where name ='马云'; 31 -- 查询英语成绩大于90分的学员 32 select * from stu where english>90; 33 -- 查询英语成绩大于60 分小于90分 34 select * from stu where english between 60 and 90; 35 -- 查询英语成绩大于60 分小于90分 的学员的姓名和地址 36 select name ,address from stu where english between 60 and 90; 37 -- 查询姓名叫马云 或者姓名叫做马化腾或 柳青 的学员 38 select name from stu where name='马云' and name ='马化腾' and name='柳青'; 39 -- 查询没有英语成绩的学员 40 select name from stu where ISNULL(english); 41 -- 查询有英语成绩的学员 42 select name from stu where english is not null; 43 -- 查询有英语成绩的学员 并且英语成绩 在60 - 80分之间 44 select name,english from stu where english between 60 and 80; 45 -- 查询有英语成绩的学员 并且英语成绩 在60 - 80分之间 并且来自于杭州 46 47 select name from stu where english between 60 and 80 and address='杭州'; 48 49 50 -- 查询名称中包含青的学员 51 select name from stu where name='%青%'; 52 -- 查询名称中以德结尾的学员 53 select name from stu where name='%德'; 54 -- 查询姓马学员,并且名字只有二位字符 55 select name from stu where name='马%'; 56 -- 查询学员信息,按照数学成绩升序排序 如果数学成绩相同按照英语成绩降序 57 select * from stu order by math,english DESC; 58 -- 查询学生信息,按照英语升序排序 59 select * from stu english; 60 -- 计算学生的个数 61 select count(*) from stu; 62 -- 计算所有学生的数学平均成绩 63 select avg(math) from stu; 64 -- 计算所有学生数学成绩的总和 和英语成绩的总和 65 select sum(math) , sum(english) from stu; 66 -- 计算所有学生数学成绩+英语成绩的总和 67 select sum(math)+sum(english) from stu; 68 -- 查询各个地区的学生的数量 69 select address,COUNT(address) as 地区 from stu group by address; 70 -- 查询各个地区的数学分数在70分以上的学生的数量 71 select address,COUNT(address) from stu where math>70 group by address ; 72 -- 查询各个地区的数学分数在70分以上的学生的数量 并且 学生人数大于2人 73 select address,COUNT(address) as countnum from stu where math>70 group by address HAVING countnum>=2; 74 -- 获取学生数据 获取第二页 每页显示5条 (2-1)*5 75 select * from stu limit 1,5;
练习6
创建部门表,创建员工表,表结构以及数据已经给出.
部门表 :
员工表:
1 -- 创建部门表 2 CREATE TABLE dept( 3 id INT PRIMARY KEY AUTO_INCREMENT, 4 NAME VARCHAR(20) 5 ); 6 7 INSERT INTO dept (NAME) VALUES ('开发部'),('市场部'),('财务部'); 8 9 10 -- 创建员工表 11 CREATE TABLE emp ( 12 id INT PRIMARY KEY AUTO_INCREMENT, 13 NAME VARCHAR(10), 14 gender CHAR(1), -- 性别 15 salary DOUBLE, -- 工资 16 join_date DATE, -- 入职日期 17 dept_id INT, 18 FOREIGN KEY (dept_id) REFERENCES dept(id) -- 外键，关联部门表(部门表的主键) 19 ); 20 21 22 INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('孙悟空','男',7200,'2013-02-24',1); 23 INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('猪八戒','男',3600,'2010-12-02',2); 24 25 INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('唐僧','男',9000,'2008-08-08',2); 26 INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('白骨精','女',5000,'2015-10-07',3); 27 INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('蜘蛛精','女',4500,'2011-03-14',1); 28 29 --查询所有的员工信息 和对应的部门信息 30 select * from emp,dept where emp.`dept_id` = dept.`id`; 31 32 --查询员工表的名称,性别,部门表的名称 33 select 34 t1.name, 35 t1.gender, 36 t2.name 37 FROM 38 emp t1, 39 dept t2 40 WHERE 41 t1.`dept_id` = t2.`id`; 42 43 44 --内连接(取交集) 45 select emp.name,emp.gender,dept.name from emp inner join dept on emp.dept_id=dept.id 46 47 48 --添加一个新的职工 小明 49 insert into emp values(6,'小明','男',null,null,null) 50 51 --左连接查询 52 select * from emp left outer join dept on emp.dept_id=dept.id 53 54 --右连接查询 55 select * from emp right outer join dept on emp.dept_id=dept.id 56 57 --添加一个新的部门 58 select * from dept 59 insert into dept values(4,'公关部') 60 61 --查询工资最高的员工信息 62 select 63 * 64 from 65 emp 66 where 67 emp.salary>=(select MAX(salary) from emp); 68 69 --查询最高的工资是多少 9000 70 select MAX(salary) from emp; 71 72 --查询员工的信息,并且工资等于9000 73 select *from emp where emp.salary =9000; 74 75 --一条sql就完成这个操作. 76 77 select * from emp where emp.salary=(select MIN(salary) from emp); 78 79 --查询员工的工资小于平均工资的人 单行单列 80 81 select emp.`NAME` from emp where emp.salary <(select AVG(salary) from emp); 82 83 --查询财务部所有的员工信息 84 select * from emp where emp.dept_id in (select id from dept where dept.name='财务部'); 85 86 --查询财务部所有的员工信息 和 市场部的员工信息 多行单列 87 select * from emp where emp.dept_id in (select id from dept where dept.name='财务部' or name='市场部'); 88 89 select * from emp where emp.dept_id in(3,2); 90 91 92 --查询 员工的入职日期是2011-11-11 之后的员工信息 和 部门信息 .多行多列 93 94 select emp.id,emp.`NAME`, dept.`NAME`,emp.gender,emp.salary,emp.join_date,emp.dept_id from emp,dept where join_date>'2011-11-11' and emp.dept_id=dept.id; 95 96 select * from emp,dept where join_date>'2011-11-11' and emp.dept_id=dept.id; 97 98 99 select * from dept t1, (select * from emp where emp.join_date > '2011-11-11') t2 where t1.id = t2.dept_id; 100 101 select * from emp t1, dept t2 where t1.dept_id =t2.id and t1.join_date >'2011-11-11'; 102 103 --左外 104 select * from emp a left join dept b on a.dept_id = b.id where a.join_date>'2011-11-11';
练习7:
按照下列图示要求创建数据库表:
1 Create table user( 2 uid int PRIMARY KEY, 3 uname varchar(20) not NULL, 4 pwd varchar(20), 5 email varchar(20) UNIQUE 6 7 ); 8 9 CREATE TABLE product( 10 pid int PRIMARY KEY, 11 pname varchar(20) not NULL, 12 intro varchar(10), 13 price double (10,2) not null 14 ) 15 16 CREATE TABLE orders( 17 oid int PRIMARY KEY, 18 orderno varchar(20) not NULL, 19 ordertime date, 20 totalprice double (10,2) not null 21 22 ) 23 CREATE TABLE order_detail( 24 did int PRIMARY KEY, 25 oid int not null, 26 pid int not null, 27 num int not null, 28 price double (10,2) not null 29 ) 30 31 CREATE TABLE category( 32 cid int PRIMARY key, 33 cname VARCHAR(20) not null 34 35 )
练习8:
题目给定表book/borrow/student 结构以及数据,按照要求进行查询:
表book:
表borrow:
表student:
1 CREATE TABLE book ( 2 BID CHAR(10) NOT NULL, 3 title CHAR(50) DEFAULT NULL, 4 author CHAR(20) DEFAULT NULL, 5 PRIMARY KEY (BID) 6 ); 7 INSERT INTO book VALUES ('B001', '人生若只如初见', '安意如'); 8 INSERT INTO book VALUES ('B002', '入学那天遇见你', '晴空'); 9 INSERT INTO book VALUES ('B003', '感谢折磨你的人', '如娜'); 10 INSERT INTO book VALUES ('B004', '我不是教你诈', '刘庸'); 11 INSERT INTO book VALUES ('B005', '英语四级', '白雪'); 12 CREATE TABLE borrow ( 13 borrowID CHAR(10) NOT NULL, 14 stuID CHAR(10) DEFAULT NULL, 15 BID CHAR(10) DEFAULT NULL, 16 T_time VARCHAR(50) DEFAULT NULL, 17 B_time VARCHAR(50) DEFAULT NULL, 18 PRIMARY KEY (borrowID) 19 ) ; 20 INSERT INTO borrow VALUES ('T001', '1001', 'B001', '2007-12-26 00:00:00', NULL); 21 INSERT INTO borrow VALUES ('T002', '1004', 'B003', '2008-01-05 00:00:00', NULL); 22 INSERT INTO borrow VALUES ('T003', '1005', 'B001', '2007-10-08 00:00:00', '2007-12-25 00:00:00'); 23 INSERT INTO borrow VALUES ('T004', '1005', 'B002', '2007-12-16 00:00:00', '2008-01-07 00:00:00'); 24 INSERT INTO borrow VALUES ('T005', '1002', 'B004', '2007-12-22 00:00:00', NULL); 25 INSERT INTO borrow VALUES ('T006', '1005', 'B005', '2008-01-06 00:00:00', NULL); 26 INSERT INTO borrow VALUES ('T007', '1002', 'B001', '2007-09-11 00:00:00', NULL); 27 INSERT INTO borrow VALUES ('T008', '1005', 'B004', '2007-12-10 00:00:00', NULL); 28 INSERT INTO borrow VALUES ('T009', '1004', 'B005', '2007-10-16 00:00:00', '2007-12-18 00:00:00'); 29 INSERT INTO borrow VALUES ('T010', '1002', 'B002', '2007-09-15 00:00:00', '2008-01-05 00:00:00'); 30 INSERT INTO borrow VALUES ('T011', '1004', 'B003', '2007-12-28 00:00:00', NULL); 31 INSERT INTO borrow VALUES ('T012', '1002', 'B003', '2007-12-30 00:00:00', NULL); 32 33 CREATE TABLE student ( 34 stuID CHAR(10) NOT NULL, 35 stuName VARCHAR(10) DEFAULT NULL, 36 major VARCHAR(50) DEFAULT NULL, 37 PRIMARY KEY (stuID) 38 ); 39 40 INSERT INTO student VALUES ('1001', '林林', '计算机'); 41 INSERT INTO student VALUES ('1002', '白杨', '计算机'); 42 INSERT INTO student VALUES ('1003', '虎子', '英语'); 43 INSERT INTO student VALUES ('1004', '北漂的雪', '工商管理'); 44 INSERT INTO student VALUES ('1005', '五月', '数学'); 45 46 /* 47 1. 查询“计算机”专业学生在“2007-12-15”至“2008-1-8”时间段内借书的学生编号、学生名称、图书编号、图书名称、借出日期； 48 2. 查询所有借过图书的学生编号、学生名称、专业； 49 3. 查询没有借过图书的学生编号、学生名称、专业； 50 4. 查询借过作者为“安意如”的图书的学生姓名、图书名称、借出日期、归还日期； 51 5. 查询借过书但有书未归还的学生编号、学生名称、图书编号、图书名称、借出日期 52 6. 查询目前借书但未归还图书的学生名称及未还图书数量； 53 */ 54 --查询“计算机”专业学生在“2007-12-15”至“2008-1-8”时间段内借书的学生编号、学生名称、图书编号、图书名称、借出日期； 55 select 56 t1.stuID, 57 t1.stuName, 58 t3.BID, 59 t3.title, 60 t2.T_time 61 from 62 student t1, 63 borrow t2, 64 book t3 65 where 66 t1.stuID=t2.stuID and 67 t3.BID=t2.BID and 68 t1.major='计算机' and 69 t2.T_time between '2007-12-15' and '2008-1-8'; 70 --查询所有借过图书的学生编号、学生名称、专业； 71 select 72 DISTINCT 73 t2.stuID, 74 t2.stuName, 75 t2.major 76 from 77 borrow t1, 78 student t2 79 where 80 t1.stuID=t2.stuID 81 --3. 查询没有借过图书的学生编号、学生名称、专业； 82 83 select 84 student.stuID, 85 student.stuName, 86 student.major 87 from 88 student 89 where student.stuID not in( 90 select 91 t1.stuID 92 from 93 borrow t1, 94 student t2 95 where 96 t1.stuID=t2.stuID 97 ); 98 99 select 100 t1.stuID 101 from 102 borrow t1, 103 student t2 104 where 105 t1.stuID=t2.stuID; 106 --4. 查询借过作者为“安意如”的图书的学生姓名、图书名称、借出日期、归还日期； 107 select 108 student.stuName, 109 book.title, 110 borrow.T_time, 111 borrow.B_time 112 from 113 student,borrow,book 114 where 115 student.stuID=borrow.stuID and 116 book.BID=borrow.BID and 117 book.author='安意如'; 118 119 --5. 查询借过书但有书未归还的学生编号、学生名称、图书编号、图书名称、借出日期 120 select 121 t1.stuID, 122 t1.stuName, 123 t3.BID, 124 t3.title, 125 t2.T_time 126 127 from 128 student t1, 129 borrow t2, 130 book t3 131 where 132 t1.stuID=t2.stuID and 133 t3.BID=t2.BID and 134 ISNULL(t2.B_time); 135 136 --6. 查询目前借书但未归还图书的学生名称及未还图书数量； 137 select 138 139 t1.stuID, 140 t1.stuName, 141 count(t2.BID) 142 from 143 student t1, 144 borrow t2, 145 book t3 146 where 147 t1.stuID=t2.stuID and 148 t3.BID=t2.BID and 149 ISNULL(t2.B_time) 150 GROUP BY t2.stuID; 151
