数据库例题1

在数剧库中运行代码创建好表格和插入好数据,可以运行

drop table if exists employee;
create table if not exists employee
(
    id int,
    name varchar(20),
    sex varchar(10),
    birthday date,
    salary float,    
    resume text
);


insert into employee values (1,'zhangsan','male','1980-11-25',2000,'good body');
insert into employee values(2,"lisi","male","1980-04-25",1000,"good body");
insert into employee values(3,"xiaohong","female","1978-11-25",4000,"good girl");

将所有员工薪水修改为5000元。
Update employee set salary='5000'


将姓名为’zhangsan’的员工薪水修改为3000元。
Update employee set salary='3000' where name='张三'

将姓名为’lisi’的员工薪水修改为4000元,sex改为female。
Update employee set salary='4000',sex='female' where name='List'

将xiaohong的薪水在原有基础上增加1000元。
Update employee set salary=salary+'1000' where name='xiaohong'



select 练习

create table student(
    id int,
    name varchar(20),
    chinese float,
    english float,
    math float
);

insert into student(id,name,chinese,english,math) values(1,'张小明',89,78,90);
insert into student(id,name,chinese,english,math) values(2,'李进',67,53,95);
insert into student(id,name,chinese,english,math) values(3,'王五',87,78,77);
insert into student(id,name,chinese,english,math) values(4,'李一',88,98,92);
insert into student(id,name,chinese,english,math) values(5,'李来财',82,84,67);
insert into student(id,name,chinese,english,math) values(6,'张进宝',55,85,45);
insert into student(id,name,chinese,english,math) values(7,'黄蓉',75,65,30);



查询表中所有学生的信息。
select * from student

查询表中所有学生的姓名和对应的英语成绩。;
select name,english from student

统计每个学生的总分。
select sum(chinese+english+math) from student group by id;

使用别名表示学生分数。
select chinese 语文,English 英语, math 数学 from student

查询姓名为李一的学生成绩
select chinese,English, math from student where name='李一'

查询英语成绩大于90分的同学
select name from student where english>90

查询总分大于200分的所有同学
select name from student group by id having sum(chinese+english+math)>200

查询英语分数在 80－90之间的同学。
select name from student where english>=80 and english<=90

查询数学分数为89,90,91的同学。
select name from student where math in(89,90,91)

查询所有姓李的学生英语成绩。
select name,english from student where name like '李%'

查询数学分>80并且语文分>80的同学。
select * from(select * from student where math>80) p where p.chinese>80;

查询英语>80或者总分>200的同学
select * from student group by id having english>80 and sum(chinese+english+math)>200 

查询所有学生的信息 数学成绩排序后输出。
select * from student order by math desc

查询所有学生的信息对总分排序后输出，然后再按从高到低的顺序输出
 select *,sum(chinese+english+math) from student group by id order by sum(chinese+english+math) desc

对姓李的学生成绩排序输出
select * from ( select *,sum(chinese+english+math) from student group by id order by sum(chinese+english+math) desc
)s  where s.name like '李%'

=========================================================================

数据库例题2

DROP TABLE DEPT;
--部门表
CREATE TABLE DEPT(
    DEPTNO int  PRIMARY KEY,
    DNAME VARCHAR(14) , -- 部门名称
    LOC VARCHAR(13)  -- 部门地址
    ) ;
CREATE TABLE EMP(
    EMPNO int  PRIMARY KEY, -- 员工编号
    ENAME VARCHAR(10), -- -员工姓名
    JOB VARCHAR(9), -- 员工工作
    MGR int, -- --员工直属领导编号
    HIREDATE DATE, -- --入职时间
    SAL double, -- - 工资
    COMM double, -- 奖金
    DEPTNO int  REFERENCES DEPT);  -- 关联dept表

CREATE TABLE SALGRADE( 
    GRADE int,-- 等级
    LOSAL double, -- 最低工资
    HISAL double ); -- 最高工次
   
1、返回拥有员工的部门名、部门号。
select dname,dept.deptno from dept,emp where dept.deptno=emp.deptno GROUP BY dname

+------------+--------+
| dname      | deptno |
+------------+--------+
| ACCOUNTING |     10 |
| RESEARCH   |     20 |
| SALES      |     30 |
+------------+--------+


2、工资水平多于smith的员工信息。

select * from emp where sal>(select sal from emp where ename='smith') and ename!='smith'
    
3、返回员工和所属经理的姓名。
select e.ename eename,m.ename mename from emp e, emp m where e.mgr=m.empno

+--------+--------+
| eename | mename |
+--------+--------+
| SMITH  | FORD   |
| ALLEN  | BLAKE  |
| WARD   | BLAKE  |
| JONES  | KING   |
| MARTIN | BLAKE  |
| BLAKE  | KING   |
| CLARK  | KING   |
| SCOTT  | JONES  |
| KING   | NULL   |
| TURNER | BLAKE  |
| ADAMS  | SCOTT  |
| JAMES  | BLAKE  |
| FORD   | JONES  |
| MILLER | CLARK  |


     
4、返回雇员的雇佣日期早于其经理雇佣日期的员工及其经理姓名。

select e.ename eename,m.ename mename from emp e, emp m where e.mgr=m.empno and e.HIREDATE>m.HIREDATE

5、返回员工姓名及其所在的部门名称。
select ename,dname from emp,dept where dept.deptno=emp.deptno
+--------+------------+
| ename  | dname      |
+--------+------------+
| CLARK  | ACCOUNTING |
| KING   | ACCOUNTING |
| MILLER | ACCOUNTING |
| SMITH  | RESEARCH   |
| JONES  | RESEARCH   |
| SCOTT  | RESEARCH   |
| ADAMS  | RESEARCH   |
| FORD   | RESEARCH   |
| ALLEN  | SALES      |
| WARD   | SALES      |
| MARTIN | SALES      |
| BLAKE  | SALES      |
| TURNER | SALES      |
| JAMES  | SALES      |
+--------+------------+


6、返回从事clerk工作的员工姓名和所在部门名称。
select ename,dname from emp, dept where dept.deptno=emp.deptno and job='clerk';

+--------+------------+
| ename  | dname      |
+--------+------------+
| MILLER | ACCOUNTING |
| SMITH  | RESEARCH   |
| ADAMS  | RESEARCH   |
| JAMES  | SALES      |
+--------+------------+


7、返回部门号及其本部门的最低工资。
select deptno,min(sal) from emp GROUP BY deptno
+--------+----------+
| deptno | min(sal) |
+--------+----------+
|     10 |     1300 |
|     20 |      800 |
|     30 |      950 |
+--------+----------+

8、返回销售部(sales)所有员工的姓名。
select ename from emp where deptno=(select deptno from dept where dname='sales')
+--------+
| ename  |
+--------+
| ALLEN  |
| WARD   |
| MARTIN |
| BLAKE  |
| TURNER |
| JAMES  |
+--------+


9、返回工资水平多于平均工资的员工。
select * from emp where sal>(select avg(sal) from emp)
+-------+-------+-----------+------+------------+------+------+--------+
| EMPNO | ENAME | JOB       | MGR  | HIREDATE   | SAL  | COMM | DEPTNO |
+-------+-------+-----------+------+------------+------+------+--------+
|  7566 | JONES | MANAGER   | 7839 | 1981-04-02 | 2975 | NULL |     20 |
|  7698 | BLAKE | MANAGER   | 7839 | 1981-05-01 | 2850 | NULL |     30 |
|  7782 | CLARK | MANAGER   | 7839 | 1981-06-09 | 2450 | NULL |     10 |
|  7788 | SCOTT | ANALYST   | 7566 | 1987-07-03 | 3000 | NULL |     20 |
|  7839 | KING  | PRESIDENT | NULL | 1981-11-17 | 5000 | NULL |     10 |
|  7902 | FORD  | ANALYST   | 7566 | 1981-12-03 | 3000 | NULL |     20 |
+-------+-------+-----------+------+------------+------+------+--------+

10、返回与SCOTT从事相同工作的员工。

select ename from emp where job=(select job from emp where ename='scott')  and ename!='scott'





12、返回工资高于30部门所有员工工资水平的员工信息。
select * from emp where sal>all(select sal from emp where deptno=30) and deptno!=30
+-------+-------+-----------+------+------------+------+------+--------+
| EMPNO | ENAME | JOB       | MGR  | HIREDATE   | SAL  | COMM | DEPTNO |
+-------+-------+-----------+------+------------+------+------+--------+
|  7566 | JONES | MANAGER   | 7839 | 1981-04-02 | 2975 | NULL |     20 |
|  7788 | SCOTT | ANALYST   | 7566 | 1987-07-03 | 3000 | NULL |     20 |
|  7839 | KING  | PRESIDENT | NULL | 1981-11-17 | 5000 | NULL |     10 |
|  7902 | FORD  | ANALYST   | 7566 | 1981-12-03 | 3000 | NULL |     20 |
+-------+-------+-----------+------+------------+------+------+--------+


13、返回部门号、部门名、部门所在位置及其每个部门的员工总数。    
select dept.deptno,dname,loc,c from dept,(select emp.deptno,count(empno) c from emp group by deptno) p where p.deptno=dept.deptno


14、返回员工的姓名、所在部门名及其工资。

select ename,dname,sal from dept, emp where emp.deptno=dept.deptno

+--------+------------+------+
| ename  | dname      | sal  |
+--------+------------+------+
| CLARK  | ACCOUNTING | 2450 |
| KING   | ACCOUNTING | 5000 |
| MILLER | ACCOUNTING | 1300 |
| SMITH  | RESEARCH   |  800 |
| JONES  | RESEARCH   | 2975 |
| SCOTT  | RESEARCH   | 3000 |
| ADAMS  | RESEARCH   | 1100 |
| FORD   | RESEARCH   | 3000 |
| ALLEN  | SALES      | 1600 |
| WARD   | SALES      | 1250 |
| MARTIN | SALES      | 1250 |
| BLAKE  | SALES      | 2850 |
| TURNER | SALES      | 1500 |
| JAMES  | SALES      |  950 |
+--------+------------+------+

15、返回员工的详细信息。(包括部门名)
select emp.*,salgrade.* from dept,emp,salgrade where dept.deptno=emp.deptno and sal BETWEEN losal and hisal


+-------+--------+-----------+------+------------+------+------+--------+--------+------------+----------+-------+-------+-------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL  | COMM | DEPTNO | DEPTNO | DNAME      | LOC      | GRADE | LOSAL | HISAL |
+-------+--------+-----------+------+------------+------+------+--------+--------+------------+----------+-------+-------+-------+
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800 | NULL |     20 |     20 | RESEARCH   | DALLAS   |     1 |   700 |  1200 |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600 |  300 |     30 |     30 | SALES      | CHICAGO  |     3 |  1401 |  2000 |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250 |  500 |     30 |     30 | SALES      | CHICAGO  |     2 |  1201 |  1400 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975 | NULL |     20 |     20 | RESEARCH   | DALLAS   |     4 |  2001 |  3000 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250 | 1400 |     30 |     30 | SALES      | CHICAGO  |     2 |  1201 |  1400 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850 | NULL |     30 |     30 | SALES      | CHICAGO  |     4 |  2001 |  3000 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450 | NULL |     10 |     10 | ACCOUNTING | NEW YORK |     4 |  2001 |  3000 |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-07-03 | 3000 | NULL |     20 |     20 | RESEARCH   | DALLAS   |     4 |  2001 |  3000 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000 | NULL |     10 |     10 | ACCOUNTING | NEW YORK |     5 |  3001 |  9999 |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500 |    0 |     30 |     30 | SALES      | CHICAGO  |     3 |  1401 |  2000 |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-07-13 | 1100 | NULL |     20 |     20 | RESEARCH   | DALLAS   |     1 |   700 |  1200 |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950 | NULL |     30 |     30 | SALES      | CHICAGO  |     1 |   700 |  1200 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000 | NULL |     20 |     20 | RESEARCH   | DALLAS   |     4 |  2001 |  3000 |
|  7934 | MILLER | CLERK     | 7782 | 1981-01-23 | 1300 | NULL |     10 |     10 | ACCOUNTING | NEW YORK |     2 |  1201 |  1400 |
+-------+--------+-----------+------+------------+------+------+--------+--------+------------+----------+-------+-------+-------+

16、返回员工工作及其从事此工作的最低工资。
select ename,job,min(sal) from emp group by job
+-----------+----------+
| job       | min(sal) |
+-----------+----------+
| ANALYST   |     3000 |
| CLERK     |      800 |
| MANAGER   |     2450 |
| PRESIDENT |     5000 |
| SALESMAN  |     1250 |
+-----------+----------+
17、返回不同部门经理的最低工资。
select deptno,min(sal) from emp where job='manager' GROUP BY deptno

+--------+----------+
| deptno | min(sal) |
+--------+----------+
|     10 |     2450 |
|     20 |     2975 |
|     30 |     2850 |
+--------+----------+



18、计算出员工的年薪，并且以年薪排序。
select sal*12 from emp order by sal*12 desc



19、返回工资处于第四级别的员工的姓名。
select ename from emp,salgrade where grade=4 and sal BETWEEN losal and hisal



+-------+
| ename |
+-------+
| JONES |
| BLAKE |
| CLARK |
| SCOTT |
| FORD  |
+-------+

20、返回工资为二等级的职员名字、部门所在地、和二等级的最低工资和最高工资
select ename,loc,minsal,maxsal from dept,

(select max(sal) maxsal,min(sal) minsal from emp,salgrade where sal between losal and hisal) p1,

(select deptno,ename from emp where ename in(
select ename from emp,salgrade where grade=2 and sal BETWEEN losal and hisal
)) p2 where dept.deptno=p2.deptno

21、返回工资为二等级的职员名字、部门所在地、二等级员工工资的最低工资和最高工资
select ename,loc,minsal,maxsal from dept,

(select max(sal) maxsal,min(sal) minsal from emp where ename in(
select ename from emp,salgrade where grade=2 and sal BETWEEN losal and hisal
)) p1,

(select deptno,ename from emp where ename in(
select ename from emp,salgrade where grade=2 and sal BETWEEN losal and hisal
)) p2 where dept.deptno=p2.deptno


+--------+----------+--------+--------+
| ename  | loc      | minsal | maxsal |
+--------+----------+--------+--------+
| WARD   | CHICAGO  |   1250 |   1300 |
| MARTIN | CHICAGO  |   1250 |   1300 |
| MILLER | NEW YORK |   1250 |   1300 |
+--------+----------+--------+--------+

22.工资等级多于smith的员工信息。
select emp.*,salgrade.* from dept,emp,salgrade where dept.deptno=emp.deptno and sal BETWEEN losal
 and hisal and ename!='smith' and grade>(select grade from salgrade,emp where ename='smith' and sal between losal and hisal)

+-------+--------+-----------+------+------------+------+------+--------+-------+-------+-------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL  | COMM | DEPTNO | GRADE | LOSAL | HISAL |
+-------+--------+-----------+------+------------+------+------+--------+-------+-------+-------+
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600 |  300 |     30 |     3 |  1401 |  2000 |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250 |  500 |     30 |     2 |  1201 |  1400 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975 | NULL |     20 |     4 |  2001 |  3000 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250 | 1400 |     30 |     2 |  1201 |  1400 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850 | NULL |     30 |     4 |  2001 |  3000 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450 | NULL |     10 |     4 |  2001 |  3000 |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-07-03 | 3000 | NULL |     20 |     4 |  2001 |  3000 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000 | NULL |     10 |     5 |  3001 |  9999 |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500 |    0 |     30 |     3 |  1401 |  2000 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000 | NULL |     20 |     4 |  2001 |  3000 |
|  7934 | MILLER | CLERK     | 7782 | 1981-01-23 | 1300 | NULL |     10 |     2 |  1201 |  1400 |
+-------+--------+-----------+------+------------+------+------+--------+-------+-------+-------+

数据库例题3

DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
  `Cid` varchar(10) DEFAULT NULL,
  `Cname` varchar(10) DEFAULT NULL,
  `Tid` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of course
-- ----------------------------
INSERT INTO `course` VALUES ('01', '语文', '02');
INSERT INTO `course` VALUES ('02', '数学', '01');
INSERT INTO `course` VALUES ('03', '英语', '03');

-- ----------------------------
-- Table structure for sc
-- ----------------------------
DROP TABLE IF EXISTS `sc`;
CREATE TABLE `sc` (
  `Sid` varchar(10) DEFAULT NULL,
  `Cid` varchar(10) DEFAULT NULL,
  `score` decimal(18,1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of sc
-- ----------------------------
INSERT INTO `sc` VALUES ('01', '01', '80.0');
INSERT INTO `sc` VALUES ('01', '02', '90.0');
INSERT INTO `sc` VALUES ('01', '03', '99.0');
INSERT INTO `sc` VALUES ('02', '01', '70.0');
INSERT INTO `sc` VALUES ('02', '02', '60.0');
INSERT INTO `sc` VALUES ('02', '03', '80.0');
INSERT INTO `sc` VALUES ('03', '01', '80.0');
INSERT INTO `sc` VALUES ('03', '02', '80.0');
INSERT INTO `sc` VALUES ('03', '03', '80.0');
INSERT INTO `sc` VALUES ('04', '01', '50.0');
INSERT INTO `sc` VALUES ('04', '02', '30.0');
INSERT INTO `sc` VALUES ('04', '03', '20.0');
INSERT INTO `sc` VALUES ('05', '01', '76.0');
INSERT INTO `sc` VALUES ('05', '02', '87.0');
INSERT INTO `sc` VALUES ('06', '01', '31.0');
INSERT INTO `sc` VALUES ('06', '03', '34.0');
INSERT INTO `sc` VALUES ('07', '02', '89.0');
INSERT INTO `sc` VALUES ('07', '03', '98.0');
INSERT INTO `sc` VALUES ('09', '03', '98.0');

-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `Sid` varchar(10) DEFAULT NULL,
  `Sname` varchar(10) DEFAULT NULL,
  `Sage` datetime DEFAULT NULL,
  `Ssex` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES ('01', '赵雷', '1990-01-01 00:00:00', '男');
INSERT INTO `student` VALUES ('02', '钱电', '1990-12-21 00:00:00', '男');
INSERT INTO `student` VALUES ('03', '孙风', '1990-05-20 00:00:00', '男');
INSERT INTO `student` VALUES ('04', '李云', '1990-08-06 00:00:00', '男');
INSERT INTO `student` VALUES ('05', '周梅', '1991-12-01 00:00:00', '女');
INSERT INTO `student` VALUES ('06', '吴兰', '1992-03-01 00:00:00', '女');
INSERT INTO `student` VALUES ('07', '郑竹', '1989-07-01 00:00:00', '女');
INSERT INTO `student` VALUES ('08', '王菊', '1990-01-20 00:00:00', '女');

-- ----------------------------
-- Table structure for teacher
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
  `Tid` varchar(10) DEFAULT NULL,
  `Tname` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of teacher
-- ----------------------------
INSERT INTO `teacher` VALUES ('01', '张三');
INSERT INTO `teacher` VALUES ('02', '李四');
INSERT INTO `teacher` VALUES ('03', '王五');

1.查询”李”姓老师的数量
select count(tid) from teacher where tname like '李%'

2.查询学过”张三”老师授课的同学的信息
select s.* from student s,sc c,teacher t,course cs where s.sid=c.sid and c.cid=cs.cid and cs.tid=t.tid and tname='张三'

3.查询没学过”张三”老师授课的同学的信息
select * from student where sid not in(select sid from sc where cid in(select cid from course where tid=(
select tid from teacher where tname='张三')))


4.查询学过编号为”01”并且也学过编号为”02”的课程的同学的信息
select * from student where sid in(
select sid from sc where sid in(select sid from sc where cid=1) and cid=2)

5.查询学全所有课程的同学的信息
select * from student where sid in (select sid from sc group by sid having count(cid)=3)


6.查询没有学全所有课程的同学的信息
select * from student where sid in (select sid from sc group by sid having count(cid)<3)

7.查询至少有一门课与学号为”01”的同学所学相同的同学的信息
select sid from student where sid in(select sid from sc where cid in(select cid from sc where sid=1))

8.查询课程名称为”数学”，且分数低于60的学生姓名和分数
select sname, score from student,course,sc where student.sid=sc.sid and sc.cid = course.cid AND
course.cname='数学' and score<60

9.查询本月过生日的学生
select * from student where month(sage)=month(now())

10.查询下月过生日的学生

select * from student where month(sage)=(month(now())+13)%12