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