創(chuàng)建表:

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, ----員工直屬領(lǐng)導(dǎo)編號 30 HIREDATE DATE, ----入職時間 31 SAL double, ---工資 32 COMM double, --獎金 33 DEPTNO int REFERENCES DEPT); --關(guān)聯(lián)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表

練習(xí)1:

1 1.安裝mysql數(shù)據(jù)庫 2 3 2.創(chuàng)建一個mydb1數(shù)據(jù)庫，并查看。 4 create database mydb1; 5 6 show mydb1; 7 show create database mydb1; 8 9 3.創(chuàng)建一個mydb2數(shù)據(jù)庫，指定字符編碼為gb2312; 10 create database mydb2 chracter set gb2312 collate gb2312_bin; 11 12 4.創(chuàng)建一個mydb3數(shù)據(jù)庫，指定字符編碼為utf8,并且指定校驗規(guī)則; 13 create database mydb3 character utf8 collate utf8_bin; 14 15 16 update練習(xí) 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這個關(guān)鍵字僅僅是用來修改表的結(jié)構(gòu)的,對表中的內(nèi)容無法修改,修改表中的內(nèi)容要用update" 36 --update table employee set salary = 5000; update 是修改表中的數(shù)據(jù),就不用在其后加上talbe關(guān)鍵字了. 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的薪水在原有基礎(chǔ)上增加1000元。 48 update employee set salary = salary + 1000 where name = 'xiaohong'; 49 50 51 select 練習(xí) 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 查詢表中所有學(xué)生的信息。 72 select * from student; 73 74 查詢表中所有學(xué)生的姓名和對應(yīng)的英語成績。; 75 select name,english from student ; 76 77 78 過濾表中重復(fù)數(shù)據(jù)。 79 80 select distinct * from student; 81 82 統(tǒng)計每個學(xué)生的總分。 83 --select sum(chinese+english+math) as Total from student; 這個是所有學(xué)生的總分, 貌似sum()函數(shù)是對以整列求和,對一個記錄內(nèi)的求和是要單獨加 84 select name ,(chinese+english+math) as Total from student; 85 select name ,chinese+english+math as Total from student;--這兩個都對. 86 87 在所有學(xué)生總分?jǐn)?shù)上加10分特長分。 88 --select name , chinese+english+math+10 as Total from student;是所有學(xué)生分?jǐn)?shù)上,并不是學(xué)生總成績上. 89 select name , chinese+10,english+10,math+10 as Total from student; 90 91 92 使用別名表示學(xué)生分?jǐn)?shù)。 93 select name,chinese ch ,english en,math ma from student;--這個地方別名 加不加as都對. 94 95 96 查詢姓名為李一的學(xué)生成績 97 select name,chinese, math,english from student where name = '李一'; 98 99 100 查詢英語成績大于90分的同學(xué) 101 select name from student where english > 90 ; 102 103 查詢總分大于200分的所有同學(xué) 104 select name from student where english+chinese+math>200; 105 106 查詢英語分?jǐn)?shù)在 80－90之間的同學(xué)。 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 查詢數(shù)學(xué)分?jǐn)?shù)為89,90,91的同學(xué)。 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就不正確.檢索不到東西.可以用關(guān)鍵字OR 115 select name from student where math=89 or math=90 or math=91; 116 117 118 119 查詢所有姓李的學(xué)生英語成績。 120 select name,english from student where name like "李%"; 121 122 123 124 查詢數(shù)學(xué)分>80并且語文分>80的同學(xué)。 125 select name from student where math>80 and chinese >80; 126 127 查詢英語>80或者總分>200的同學(xué) 128 select name from student where english > 80 OR english+math+chinese>200; 129 130 對數(shù)學(xué)成績排序后輸出。 131 select * from student order by math ;--默認(rèn)是升序排列. 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 對姓李的學(xué)生成績排序輸出 140 select * from student where name like "李%" order by chinese+math+english desc;

?

練習(xí)2:

1 1、查找部門30中員工的詳細(xì)信息。 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 >sal 12 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部門的經(jīng)理(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號部門的經(jīng)理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部門的經(jīng)理、20部門的職員 或者既不是經(jīng)理也不是職員但是工資高于2000元的員工信息。 26 27 --select * from emp where deptno=10 and job='MANAGER' or deptno=20 and job='CLERK' or 最后這個條件不會寫,原來用not 外加in 28 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()函數(shù),還有就是弄like中引入對應(yīng)數(shù)字的空格' '. 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、返回員工的詳細(xì)信息并按姓名排序。 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 emp 71 72 73 15、找出姓名中包含A的員工信息。 74 select * from emp where ename like '%A%';

?

練習(xí)3:

1 //多表與分組練習(xí) 2 3 1、返回?fù)碛袉T工的部門名、部門號。 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、返回員工和所屬經(jīng)理的姓名。 22 --這個感覺很簡單就是沒有想出來怎么辦,看答案才理解了 23 --復(fù)用表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 --現(xiàn)在左外連接還有另外的一種寫法,就是用加號"+";仔細(xì)查了一下,并且納悶為什么＋的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、返回雇員的雇傭日期早于其經(jīng)理雇傭日期的員工及其經(jīng)理姓名。 54 --感覺這個也是要復(fù)用emp表,確實復(fù)用emp表 55 --select ee.ename as eename,mm.ename as mename from emp ee,emp mm where ee.hiredate<mm.hiredate; 56 --這句話沒有加上是其經(jīng)理的條件 57 --select ee.ename as eename,mm.ename as mename from emp ee left join emp mm on ee.mgr = mm.empno and ee.hiredate < mm.hiredate; 58 --上面這句僅僅是把第三句中加上了日期小于的條件. 59 select ee.ename as eename,mm.ename as mename from emp ee ,emp mm where ee.mgr = mm.empno and ee.hiredate < mm.hiredate; 60 --這句為什么不用left join? 第三句用where可不可以? 注意時間老的日期大小是小于時間老的日期的. 61 (select ee.ename as eename ,mm.ename as mename from emp ee left join emp mm where ee.mgr=mm.empno;)第三句的寫法. 62 63 64 5、返回員工姓名及其所在的部門名稱。 65 --這句有點像第三句,不過這個牽扯到了兩個表 66 --select ee.ename as ename, ee.deptno as dptname from emp ee, dept dd where ee.deptno=dd.deptno; 67 --最開始寫的,寫錯了.還是沒有把最基本的需求高清,人家要部門名. 68 69 select ename as ename ,dname from emp ee,dept dd where ee.deptno=dd.deptno; 70 +--------+------------+ 71 | ename | dname | 72 +--------+------------+ 73 | CLARK | ACCOUNTING | 74 | KING | ACCOUNTING | 75 | MILLER | ACCOUNTING | 76 | SMITH | RESEARCH | 77 | JONES | RESEARCH | 78 | SCOTT | RESEARCH | 79 | ADAMS | RESEARCH | 80 | FORD | RESEARCH | 81 | ALLEN | SALES | 82 | WARD | SALES | 83 | MARTIN | SALES | 84 | BLAKE | SALES | 85 | TURNER | SALES | 86 | JAMES | SALES | 87 +--------+------------+ 88 89 90 6、返回從事clerk工作的員工姓名和所在部門名稱。 91 select ename ,dname from emp ee, dept dd where ee.job='clerk' and ee.deptno = dd.deptno; 92 +--------+------------+ 93 | ename | dname | 94 +--------+------------+ 95 | MILLER | ACCOUNTING | 96 | SMITH | RESEARCH | 97 | ADAMS | RESEARCH | 98 | JAMES | SALES | 99 +--------+------------+ 100 101 102 7、返回部門號及其本部門的最低工資。 103 --這個語句不用where條件進行過濾. 104 --找出部門的最低工資 ----想不起來.提示一下用到了min()函數(shù). 105 --select min(sal) from emp ; 106 --select deptno , min(sal) from deptno ,emp 107 --首先想需求中要用到哪幾個表.然后再寫. 108 --因為是按照部門為單位的所以用到分組 109 select deptno ,min(sal) from emp group by deptno; 110 +--------+----------+ 111 | deptno | min(sal) | 112 +--------+----------+ 113 | 10 | 1300 | 114 | 20 | 800 | 115 | 30 | 950 | 116 +--------+----------+ 117 118 8、返回銷售部(sales)所有員工的姓名。 119 --根據(jù)需求用到了emp和dept兩個表 120 select ename from emp ee,dept dd where dd.dname='sales' and ee.deptno = (select deptno from dept where dname = 'sales'); 121 +--------+ 122 | ename | 123 +--------+ 124 | ALLEN | 125 | WARD | 126 | MARTIN | 127 | BLAKE | 128 | TURNER | 129 | JAMES | 130 +--------+ 131 132 133 9、返回工資水平多于平均工資的員工。 134 --這個用到了avg()函數(shù). 用到表emp 135 --select * from emp where sal > avg(sal); 136 --一開始這樣寫的,但是報錯,提示信息為:ERROR 1111 : Invalid use of group function 137 --在這種情況下要把聚合函數(shù)放在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 --只用到表emp 152 --select * from emp where job = '(select job from emp where ename = 'scott')'; 153 --上一句在語法上是沒有問題的,但是有個小問題就是job是varchar字符串類型的,但是在其后是select語句 154 -- 的時候其后是不能加上單引號,或者是單引號的,否則都會報錯的. 155 --還要把搜索到的結(jié)果去掉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 --從題意中就可以看出要使用到關(guān)鍵字all 也只用到了emp表 180 --all關(guān)鍵字怎么使用. 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、返回部門號、部門名、部門所在位置及其每個部門的員工總數(shù)。 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、返回員工的詳細(xì)信息。(包括部門名) 219 --需要用到兩個表emp和dept 220 --在用select * 的基礎(chǔ)上還要加上部門名這一列,這咋辦???? 221 --看了答案明白了既然是還要加上部門名并且是詳細(xì)信息,那么就對這三個表都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前面直接用聚合函數(shù). 249 +-----------+----------+ 250 | job | min(sal) | 251 +-----------+----------+ 252 | ANALYST | 3000 | 253 | CLERK | 800 | 254 | MANAGER | 2450 | 255 | PRESIDENT | 5000 | 256 | SALESMAN | 1250 | 257 +-----------+----------+ 258 17、返回不同部門經(jīng)理的最低工資。 259 --需要用到一個表emp,需要用到group by 260 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 --需要用到一個表emp 273 select sal*12 as ysal from emp order by ysal asc; 274 --上面的語句沒有問題,關(guān)鍵是年薪的計算方法,這里還牽扯到獎金的問題. 275 276 277 278 19、返回工資處于第四級別的員工的姓名。 279 --這里需要兩個表,一個是emp一個是salgrade 280 --select ename form emp ee,salgrade where grade =4; 281 --這句話仔細(xì)一想就可以看出不會有任何結(jié)果,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 salgrade 297 --二等級的最低工資和最高工資.并不是二等級員工的工資.這個是和第21問的區(qū)別. 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后加上聚合函數(shù)的應(yīng)用????? 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 hissal 316 --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,salgrade 320 where sal between losal and hisal and grade = 2) maxsal from emp,salgrade ,dept where emp.deptno = dept.deptno and sal between 321 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 losal 337 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 +-------+--------+-----------+------+------------+------+------+--------+-------+-------+-------+

?

轉(zhuǎn)載于:https://www.cnblogs.com/DreamDrive/p/6193530.html

總結(jié)

以上是生活随笔為你收集整理的sql练习(针对Mysql)的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。