创建表:

1 DROP TABLE DEPT; 2 --部门表 3 CREATE TABLE DEPT( 4     DEPTNO int  PRIMARY KEY, 5     DNAME VARCHAR(14) , --部门名称 6     LOC VARCHAR(13)  ---部门地址 7     ) ; 8      9 CREATE TABLE DEPT(10     DEPTNO int  PRIMARY KEY,11     DNAME VARCHAR(14) , 12     LOC VARCHAR(13) 13     ) ;14     15     16     INSERT INTO DEPT VALUES    (10,'ACCOUNTING','NEW YORK');17     INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');18     INSERT INTO DEPT VALUES    (30,'SALES','CHICAGO');19     INSERT INTO DEPT VALUES    (40,'OPERATIONS','BOSTON');20     21     22     23 DROP TABLE EMP;24 --员工表25 CREATE TABLE EMP(26     EMPNO int  PRIMARY KEY, --员工编号27     ENAME VARCHAR(10), ---员工姓名28     JOB VARCHAR(9), --员工工作29     MGR int, ----员工直属领导编号30     HIREDATE DATE, ----入职时间31     SAL double, ---工资32     COMM double, --奖金33     DEPTNO int  REFERENCES DEPT);  --关联dept表34     35     36     CREATE TABLE EMP(37         EMPNO int  PRIMARY KEY,38         ENAME VARCHAR(10),39         JOB VARCHAR(9),40         MGR int, 41         HIREDATE DATE, 42         SAL double,43         COMM double,44         DEPTNO int ,45         foreign key EMP(DEPTNO) REFERENCES DEPT(DEPTNO)46     );  47     48 49 INSERT INTO EMP VALUES(7369,'SMITH','CLERK',7902,"1980-12-17",800,NULL,20);50 INSERT INTO EMP VALUES(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30);51 INSERT INTO EMP VALUES(7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30);52 INSERT INTO EMP VALUES(7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20);53 INSERT INTO EMP VALUES(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30);54 INSERT INTO EMP VALUES(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30);55 INSERT INTO EMP VALUES(7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10);56 INSERT INTO EMP VALUES(7788,'SCOTT','ANALYST',7566,'1987-07-03',3000,NULL,20);57 INSERT INTO EMP VALUES(7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10);58 INSERT INTO EMP VALUES(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30);59 INSERT INTO EMP VALUES(7876,'ADAMS','CLERK',7788,'1987-07-13',1100,NULL,20);60 INSERT INTO EMP VALUES(7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30);61 INSERT INTO EMP VALUES(7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20);62 INSERT INTO EMP VALUES(7934,'MILLER','CLERK',7782,'1981-01-23',1300,NULL,10);63 64 DROP TABLE SALGRADE;65 //工资等级表66 CREATE TABLE SALGRADE( 67     GRADE int,--等级68     LOSAL double, --最低工资69     HISAL double ); --最高工次70     71     CREATE TABLE SALGRADE( 72     GRADE int,73     LOSAL double, 74     HISAL double );75 INSERT INTO SALGRADE VALUES (1,700,1200);76 INSERT INTO SALGRADE VALUES (2,1201,1400);77 INSERT INTO SALGRADE VALUES (3,1401,2000);78 INSERT INTO SALGRADE VALUES (4,2001,3000);79 INSERT INTO SALGRADE VALUES (5,3001,9999);

 

emp表

dep表

salgrade表

练习1:

1 1.安装mysql数据库  2   3 2.创建一个mydb1数据库，并查看。  4 create database mydb1;  5   6 show mydb1;  7 show create database mydb1;  8   9 3.创建一个mydb2数据库，指定字符编码为gb2312; 10 create database mydb2 chracter set gb2312 collate gb2312_bin; 11  12 4.创建一个mydb3数据库，指定字符编码为utf8,并且指定校验规则; 13 create database mydb3 character utf8 collate utf8_bin; 14  15  16 update练习 17  18 drop table if exists employee; 19 create table if not exists employee 20 ( 21     id int, 22     name varchar(20), 23     sex varchar(10), 24     birthday date, 25     salary float,     26     resume text 27 ); 28  29  30 insert into employee values (1,'zhangsan','male','1980-11-25',2000,'good body'); 31 insert into employee values(2,"lisi","male","1980-04-25",1000,"good body"); 32 insert into employee values(3,"xiaohong","female","1978-11-25",4000,"good girl"); 33  34 将所有员工薪水修改为5000元。 35 --alter table employee salary  "alter这个关键字仅仅是用来修改表的结构的,对表中的内容无法修改,修改表中的内容要用update" 36 --update table employee set salary = 5000; update 是修改表中的数据,就不用在其后加上talbe关键字了. 37 update employee set salary = 5000; 38  39  40 将姓名为’zhangsan’的员工薪水修改为3000元。 41 update employee set salary = 3000 where name = 'zhangsan'; 42  43 将姓名为’lisi’的员工薪水修改为4000元,sex改为female。 44 --update employee set salary = 4000 and sex = female where name = 'lisi' ; 有多个列要修改的时候不用and连接列,用逗号.!!! 45 update employee set salary = 4000,sex = 'female' where name = 'lisi' ; 46  47 将xiaohong的薪水在原有基础上增加1000元。 48 update employee set salary = salary + 1000 where name = 'xiaohong'; 49  50  51 select 练习 52  53 create table student( 54     id int, 55     name varchar(20), 56     chinese float, 57     english float, 58     math float 59 ); 60  61 insert into student(id,name,chinese,english,math) values(1,'张小明',89,78,90); 62 insert into student(id,name,chinese,english,math) values(2,'李进',67,53,95); 63 insert into student(id,name,chinese,english,math) values(3,'王五',87,78,77); 64 insert into student(id,name,chinese,english,math) values(4,'李一',88,98,92); 65 insert into student(id,name,chinese,english,math) values(5,'李来财',82,84,67); 66 insert into student(id,name,chinese,english,math) values(6,'张进宝',55,85,45); 67 insert into student(id,name,chinese,english,math) values(7,'黄蓉',75,65,30); 68  69  70  71 查询表中所有学生的信息。 72 select * from student; 73  74 查询表中所有学生的姓名和对应的英语成绩。; 75 select name,english from student ; 76  77  78 过滤表中重复数据。 79  80 select distinct * from student; 81  82 统计每个学生的总分。 83 --select sum(chinese+english+math) as Total from student; 这个是所有学生的总分, 貌似sum()函数是对以整列求和,对一个记录内的求和是要单独加 84 select name ,(chinese+english+math) as Total from student; 85 select name ,chinese+english+math as Total from student;--这两个都对. 86  87 在所有学生总分数上加10分特长分。 88 --select name , chinese+english+math+10 as Total from student;是所有学生分数上,并不是学生总成绩上. 89 select name , chinese+10,english+10,math+10 as Total from student; 90  91  92 使用别名表示学生分数。 93 select name,chinese ch ,english en,math ma from student;--这个地方别名 加不加as都对. 94  95  96 查询姓名为李一的学生成绩 97 select name,chinese, math,english from student where name = '李一'; 98  99 100 查询英语成绩大于90分的同学101 select name from student where english > 90 ;102 103 查询总分大于200分的所有同学104 select name from student where english+chinese+math>200;105 106 查询英语分数在 80－90之间的同学。107 --select name from student where english>=80 & english<=90; &这个符号不能代表and!!!!108 select name from student where english>=80 and english<=90;109 select name from student where english between 80 and 90;110 111 112 查询数学分数为89,90,91的同学。113 select name from student where math in(89,90,91);114 --select name from student where math=89 and math=90 and math=91;这个地方用and就不正确.检索不到东西.可以用关键字OR115 select name from student where math=89 or math=90 or math=91;116 117 118 119 查询所有姓李的学生英语成绩。120 select name,english from student where name like "李%";121 122 123 124 查询数学分>80并且语文分>80的同学。125 select name from student where math>80 and chinese >80;126 127 查询英语>80或者总分>200的同学128 select name from student where english > 80 OR english+math+chinese>200;129 130 对数学成绩排序后输出。131 select * from student order by math ;--默认是升序排列.132 select * from student order by math desc;133 select * from student order by math asc;134 135 136 对总分排序后输出，然后再按从高到低的顺序输出137 select * from student order by chinese+math+english desc;138 139 对姓李的学生成绩排序输出140 select * from student where name like "李%" order by chinese+math+english desc;

 

练习2:

1 1、查找部门30中员工的详细信息。 2 --select * from emp where deptno=30; 3 select * from emp where deptno=30; 4 2、找出从事clerk工作的员工的编号、姓名、部门号。 5 -- select empno,ename,deptno from emp where job = 'clerk'; 6  7 select empno,ename,deptno from emp where job='clerk'; 8  9 3、检索出奖金多于基本工资的员工信息。10 --select * from emp where comm>sal;11 select * from emp where comm >sal12 13 4、检索出奖金多于基本工资60%的员工信息。14 --select * from emp where comm>sal*0.6;15 select *from emp where comm>sal*0.6;16 17 5、找出10部门的经理(job=Manger)、20部门的职员(job=clerk) 的员工信息。18 --select * from dept,emp where dept.deptno=10 and emp.deptno=20;19 20 --select * from emp where deptno=10 and job = 'manager' and deptno=20 and job='clerk';21 --这个地方找出10号部门的经理20部门的职员的员工信息.中间用OR连接.别用AND,用and是找不到这么个条件的值的.22 23 select * from emp where deptno=10 and job='MANAGER' or   deptno=20 and job='CLERK'24 25 6、找出10部门的经理、20部门的职员 或者既不是经理也不是职员但是工资高于2000元的员工信息。26 27 --select * from emp where deptno=10 and job='MANAGER' or   deptno=20 and job='CLERK' or 最后这个条件不会写,原来用not 外加in28 29 select * from emp where (deptno=10 and job='MANAGER') or  ( deptno=20 and job='CLERK') or  ( job not in ('MANAGER','CLERK') and sal>2000)30 31 7、找出获得奖金的员工的工作。32 --select * from emp where comm not null; 用is not null.33 select * from emp where comm is not null.34 35 select * from emp where comm is not null;36 37 8、找出奖金少于100或者没有获得奖金的员工的信息。38 select * from emp where comm<100 or comm is null;39 40 select * from emp where comm is null or comm<100;41 42 9、找出姓名以A、B、S开始的员工信息。43 --select * from emp where ename like "A%","B%","S%";要写多个like语句.44 45 select * from emp where ename like 'A%' OR ENAME LIKE 'B%' OR ENAME LIKE 'S%';46 47 48 10、找到名字长度为7个字符的员工信息。49 --select * from emp where ename.length= 7;50 --报错:ERROR 1054 : Unknown column 'ename.length' in 'where clause'51 --用到了length()函数,还有就是弄like中引入对应数字的空格' '.52 53 select * from emp where length(ename)=7;54 select * from emp where ename like '______';55 56 11、名字中不包含R字符的员工信息。57 58 --利用 not like '%R%'59 select * from emp where ename not like '%R%';60 61 12、返回员工的详细信息并按姓名排序。62 select * from emp order by ename;63 64 65 13、返回员工的信息并按工作降序工资升序排列。66 select * from emp order by job desc,sal asc;67 68 69 14、计算员工的日薪(按30天)。70 select sal/30 from emp71 72 73 15、找出姓名中包含A的员工信息。74 select * from emp where ename like '%A%';

 

练习3:

1 //多表与分组练习  2   3 1、返回拥有员工的部门名、部门号。  4 --不知道这个具体干什么的.通过看部门dept表和emp表可以看出来,员工并不是都是在dept表中存在的地方的.  5 --dept表中可能有四个部门号,但是员工emp表中可能只占其中的三个部门号码  6 --题干的需求就是干这个的.(即有的部门可能没有"员工")  7 select dname,deptno from dept where deptno in (select deptno from emp);  8 +------------+--------+  9 | dname      | deptno | 10 +------------+--------+ 11 | ACCOUNTING |     10 | 12 | RESEARCH   |     20 | 13 | SALES      |     30 | 14 +------------+--------+ 15  16  17 2、工资水平多于smith的员工信息。 18 select * from emp where sal>(select sal from emp where ename = 'smith'); 19  20      21 3、返回员工和所属经理的姓名。 22 --这个感觉很简单就是没有想出来怎么办,看答案才理解了 23 --复用表emp,再加上使用外连接. 24  25 --select ee.ename as eename ,mm.ename as mename from emp ee left join emp mm where ee.mgr=mm.empno;  26 --看完答案理解了之后也写错了,注意外连接是left/right join on 27 select ee.ename as eename ,mm.ename as mename from emp ee left join emp mm on ee.mgr=mm.empno; 28  29 --现在左外连接还有另外的一种写法,就是用加号"+";仔细查了一下,并且纳闷为什么＋的SQL语句外连接在navicate中没有效果. 30 --得知加号的外连接只有在oracle中才是用. 31  32  33 +--------+--------+ 34 | eename | mename | 35 +--------+--------+ 36 | SMITH  | FORD   | 37 | ALLEN  | BLAKE  | 38 | WARD   | BLAKE  | 39 | JONES  | KING   | 40 | MARTIN | BLAKE  | 41 | BLAKE  | KING   | 42 | CLARK  | KING   | 43 | SCOTT  | JONES  | 44 | KING   | NULL   | 45 | TURNER | BLAKE  | 46 | ADAMS  | SCOTT  | 47 | JAMES  | BLAKE  | 48 | FORD   | JONES  | 49 | MILLER | CLARK  | 50  51  52       53 4、返回雇员的雇佣日期早于其经理雇佣日期的员工及其经理姓名。 54 --感觉这个也是要复用emp表,确实复用emp表 55  --select ee.ename as eename,mm.ename as mename from emp ee,emp mm where ee.hiredate
     
       avg(sal);136 --一开始这样写的,但是报错,提示信息为:ERROR 1111 : Invalid use of group function137 --在这种情况下要把聚合函数放在select语句中138 select * from emp where sal > (select avg(sal) from emp);139 +-------+-------+-----------+------+------------+------+------+--------+140 | EMPNO | ENAME | JOB       | MGR  | HIREDATE   | SAL  | COMM | DEPTNO |141 +-------+-------+-----------+------+------------+------+------+--------+142 |  7566 | JONES | MANAGER   | 7839 | 1981-04-02 | 2975 | NULL |     20 |143 |  7698 | BLAKE | MANAGER   | 7839 | 1981-05-01 | 2850 | NULL |     30 |144 |  7782 | CLARK | MANAGER   | 7839 | 1981-06-09 | 2450 | NULL |     10 |145 |  7788 | SCOTT | ANALYST   | 7566 | 1987-07-03 | 3000 | NULL |     20 |146 |  7839 | KING  | PRESIDENT | NULL | 1981-11-17 | 5000 | NULL |     10 |147 |  7902 | FORD  | ANALYST   | 7566 | 1981-12-03 | 3000 | NULL |     20 |148 +-------+-------+-----------+------+------------+------+------+--------+149 150 10、返回与SCOTT从事相同工作的员工。151 --只用到表emp152 --select * from emp where job = '(select job from emp where ename = 'scott')';153 --上一句在语法上是没有问题的,但是有个小问题就是job是varchar字符串类型的,但是在其后是select语句154 -- 的时候其后是不能加上单引号,或者是单引号的,否则都会报错的.155 --还要把搜索到的结果去掉scott他本身这条记录.156 157 select * from emp where job = (select job from emp where ename = 'scott') and ename<> 'scott';158 159 160 161 11、返回与30部门员工工资水平相同的员工姓名与工资。162 --这个也只用到了emp表163 164 select ename ,sal from emp where sal in( select sal from emp where deptno = 30);165 +--------+------+166 | ename  | sal  |167 +--------+------+168 | ALLEN  | 1600 |169 | WARD   | 1250 |170 | MARTIN | 1250 |171 | BLAKE  | 2850 |172 | TURNER | 1500 |173 | JAMES  |  950 |174 +--------+------+175 176 177 178 12、返回工资高于30部门所有员工工资水平的员工信息。179 --从题意中就可以看出要使用到关键字all 也只用到了emp表180 --all关键字怎么使用.181 select * from emp where sal>all(select sal from emp where deptno = 30);182 +-------+-------+-----------+------+------------+------+------+--------+183 | EMPNO | ENAME | JOB       | MGR  | HIREDATE   | SAL  | COMM | DEPTNO |184 +-------+-------+-----------+------+------------+------+------+--------+185 |  7566 | JONES | MANAGER   | 7839 | 1981-04-02 | 2975 | NULL |     20 |186 |  7788 | SCOTT | ANALYST   | 7566 | 1987-07-03 | 3000 | NULL |     20 |187 |  7839 | KING  | PRESIDENT | NULL | 1981-11-17 | 5000 | NULL |     10 |188 |  7902 | FORD  | ANALYST   | 7566 | 1981-12-03 | 3000 | NULL |     20 |189 +-------+-------+-----------+------+------------+------+------+--------+190 191 192 13、返回部门号、部门名、部门所在位置及其每个部门的员工总数。    193 --需要用到emp和dept两个表194 select deptno ,dname ,loc ,count(*) from emp ee,dept dd  where ee.deptno = dd.deptno 195 196 14、返回员工的姓名、所在部门名及其工资。197 --需要用到emp表和dept表.198 select ee.ename , dd.dname ,ee.sal from emp ee,dept dd where ee.deptno = dd.deptno;199 +--------+------------+------+200 | ename  | dname      | sal  |201 +--------+------------+------+202 | CLARK  | ACCOUNTING | 2450 |203 | KING   | ACCOUNTING | 5000 |204 | MILLER | ACCOUNTING | 1300 |205 | SMITH  | RESEARCH   |  800 |206 | JONES  | RESEARCH   | 2975 |207 | SCOTT  | RESEARCH   | 3000 |208 | ADAMS  | RESEARCH   | 1100 |209 | FORD   | RESEARCH   | 3000 |210 | ALLEN  | SALES      | 1600 |211 | WARD   | SALES      | 1250 |212 | MARTIN | SALES      | 1250 |213 | BLAKE  | SALES      | 2850 |214 | TURNER | SALES      | 1500 |215 | JAMES  | SALES      |  950 |216 +--------+------------+------+217 218 15、返回员工的详细信息。(包括部门名)219 --需要用到两个表emp和dept220 --在用select * 的基础上还要加上部门名这一列,这咋办????221 --看了答案明白了既然是还要加上部门名并且是详细信息,那么就对这三个表都select* 操作222 --select * from emp ee,dept dd,salgrade ss where ee.deptno = dd.deptno;223 --一开始没有加上工资的限制.224 select * from emp ee,dept dd,salgrade ss where ee.deptno = dd.deptno and sal between losal and hisal;225 226 +-------+--------+-----------+------+------------+------+------+--------+--------+------------+----------+-------+-------+-------+227 | EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL  | COMM | DEPTNO | DEPTNO | DNAME      | LOC      | GRADE | LOSAL | HISAL |228 +-------+--------+-----------+------+------------+------+------+--------+--------+------------+----------+-------+-------+-------+229 |  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800 | NULL |     20 |     20 | RESEARCH   | DALLAS   |     1 |   700 |  1200 |230 |  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600 |  300 |     30 |     30 | SALES      | CHICAGO  |     3 |  1401 |  2000 |231 |  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250 |  500 |     30 |     30 | SALES      | CHICAGO  |     2 |  1201 |  1400 |232 |  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975 | NULL |     20 |     20 | RESEARCH   | DALLAS   |     4 |  2001 |  3000 |233 |  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250 | 1400 |     30 |     30 | SALES      | CHICAGO  |     2 |  1201 |  1400 |234 |  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850 | NULL |     30 |     30 | SALES      | CHICAGO  |     4 |  2001 |  3000 |235 |  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450 | NULL |     10 |     10 | ACCOUNTING | NEW YORK |     4 |  2001 |  3000 |236 |  7788 | SCOTT  | ANALYST   | 7566 | 1987-07-03 | 3000 | NULL |     20 |     20 | RESEARCH   | DALLAS   |     4 |  2001 |  3000 |237 |  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000 | NULL |     10 |     10 | ACCOUNTING | NEW YORK |     5 |  3001 |  9999 |238 |  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500 |    0 |     30 |     30 | SALES      | CHICAGO  |     3 |  1401 |  2000 |239 |  7876 | ADAMS  | CLERK     | 7788 | 1987-07-13 | 1100 | NULL |     20 |     20 | RESEARCH   | DALLAS   |     1 |   700 |  1200 |240 |  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950 | NULL |     30 |     30 | SALES      | CHICAGO  |     1 |   700 |  1200 |241 |  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000 | NULL |     20 |     20 | RESEARCH   | DALLAS   |     4 |  2001 |  3000 |242 |  7934 | MILLER | CLERK     | 7782 | 1981-01-23 | 1300 | NULL |     10 |     10 | ACCOUNTING | NEW YORK |     2 |  1201 |  1400 |243 +-------+--------+-----------+------+------------+------+------+--------+--------+------------+----------+-------+-------+-------+244 245 16、返回员工工作及其从事此工作的最低工资。246 --需要用到表emp,很明显需要 用到分组group by.247 select job ,min(sal) from emp group by job;248 --原来可以在from前面直接用聚合函数.249 +-----------+----------+250 | job       | min(sal) |251 +-----------+----------+252 | ANALYST   |     3000 |253 | CLERK     |      800 |254 | MANAGER   |     2450 |255 | PRESIDENT |     5000 |256 | SALESMAN  |     1250 |257 +-----------+----------+258 17、返回不同部门经理的最低工资。259 --需要用到一个表emp,需要用到group by260 select deptno , min(sal) from emp where job = 'manager' group by deptno;261 +--------+----------+262 | deptno | min(sal) |263 +--------+----------+264 |     10 |     2450 |265 |     20 |     2975 |266 |     30 |     2850 |267 +--------+----------+268 269 270 271 18、计算出员工的年薪，并且以年薪排序。272 --需要用到一个表emp273 select sal*12 as ysal from emp order by ysal asc;274 --上面的语句没有问题,关键是年薪的计算方法,这里还牵扯到奖金的问题.275 276 277 278 19、返回工资处于第四级别的员工的姓名。279 --这里需要两个表,一个是emp一个是salgrade280 --select ename form emp ee,salgrade where grade =4;281 --这句话仔细一想就可以看出不会有任何结果,where过滤不掉任何东西282 283 select ename from emp ,salgrade where grade =4 and sal>=losal and sal <= hisal;284 select ename from emp ,salgrade where grade =4 and sal between losal and  hisal;285 +-------+286 | ename |287 +-------+288 | JONES |289 | BLAKE |290 | CLARK |291 | SCOTT |292 | FORD  |293 +-------+294 295 20、返回工资为二等级的职员名字、部门所在地、和二等级的最低工资和最高工资296 --从题意中可知需要用到三个表 emp dept salgrade297 --二等级的最低工资和最高工资.并不是二等级员工的工资.这个是和第21问的区别.298 --select ename ,loc ,min(sal) ,max(sal) from emp ee,dept dd, salgrade ss  where grade = 2 and sal between losal and hisal;299 --二等级的最低工资和最高工资并不是二等级员工的最低工资和最高工资.300 301 select ename ,loc ,losal,hisal from emp ee,dept dd,salgrade ss where grade = 2 and sal between losal and hisal;302 303 304 21、返回工资为二等级的职员名字、部门所在地、二等级员工工资的最低工资和最高工资305 --需要用到salgrade , dept表和emp表306 mysql> select ename , loc ,min(sal) ,max(sal) from emp ee, dept dd, salgrade ss where grade = 2 and sal between losal and hisal;307 +-------+----------+----------+----------+308 | ename | loc      | min(sal) | max(sal) |309 +-------+----------+----------+----------+310 | WARD  | NEW YORK |     1250 |     1300 |311 +-------+----------+----------+----------+312 313 --这样写是错误的.到底在select后加上聚合函数的应用?????314 315 --select e.ename,loc,max(m.sal) ,min(m.sal),grade from emp e,emp m ,dept salgrade where e.deptno=dept.deptno and e.sal beteeen losal and hissal316 --m.sal between losal and hisal and grade = 2 group by e.ename , loc, grade;317 318 319 select ename , loc ,(select min(sal) from emp,salgrade where sal between losal and hisal and grade = 2) minsal,(select max(sal) from emp,salgrade320  where sal between losal and hisal and grade = 2) maxsal from emp,salgrade ,dept where emp.deptno = dept.deptno and sal between321  losal and hisal and grade = 2;322 +--------+----------+--------+--------+323 | ename  | loc      | minsal | maxsal |324 +--------+----------+--------+--------+325 | WARD   | CHICAGO  |   1250 |   1300 |326 | MARTIN | CHICAGO  |   1250 |   1300 |327 | MILLER | NEW YORK |   1250 |   1300 |328 +--------+----------+--------+--------+329 330 22.工资等级多于smith的员工信息。331 --获得员工信息,三个表中有的信息都获取出来,就 用到三个表.332 --需要用到两个表 emp表和salgrade 表333 --select * from emp,salgrade where grade >(select sal from emp,salgrade where sal between losal and hisal);334 --select * from emp ee,salgrade ss,dept dd where grade > (select grade from salgrade, emp  where ename = 'smith' and sal between losal and ss.hisal ) 335 --and sal > (select sal from em );336  select * from emp,salgrade where sal between losal and hisal and grade >(select grade from emp,salgrade where sal between losal337  and hisal and ename = 'smith');338 +-------+--------+-----------+------+------------+------+------+--------+-------+-------+-------+339 | EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL  | COMM | DEPTNO | GRADE | LOSAL | HISAL |340 +-------+--------+-----------+------+------------+------+------+--------+-------+-------+-------+341 |  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600 |  300 |     30 |     3 |  1401 |  2000 |342 |  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250 |  500 |     30 |     2 |  1201 |  1400 |343 |  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975 | NULL |     20 |     4 |  2001 |  3000 |344 |  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250 | 1400 |     30 |     2 |  1201 |  1400 |345 |  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850 | NULL |     30 |     4 |  2001 |  3000 |346 |  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450 | NULL |     10 |     4 |  2001 |  3000 |347 |  7788 | SCOTT  | ANALYST   | 7566 | 1987-07-03 | 3000 | NULL |     20 |     4 |  2001 |  3000 |348 |  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000 | NULL |     10 |     5 |  3001 |  9999 |349 |  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500 |    0 |     30 |     3 |  1401 |  2000 |350 |  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000 | NULL |     20 |     4 |  2001 |  3000 |351 |  7934 | MILLER | CLERK     | 7782 | 1981-01-23 | 1300 | NULL |     10 |     2 |  1201 |  1400 |352 +-------+--------+-----------+------+------------+------+------+--------+-------+-------+-------+