1、MySQL基礎之SQL練習1.數據腳本導入/*SQLyog Ultimate vlO.OO BetalMySQL - : Database - myemployees*/*!40101 SET NAMES utf8 */;/*!40101 SET SQL_MODE=”*/;/*!40014 SET OLD_UNIQUE_CHECKS=UNIQUE_CHECKS, UNIQUE_CHECKS=O */;/*!40014SETOLD_FOREIGN_KEY_CHECKS=FOREIGN_KEY_CHECKS,FOREIGN_KEY_CHECKS=0 */;/*!40101SETOLD_SQL_MO

2、DE=SQL_MODE,SQL_MODE=NO_AUTO_VALUE_ON_ZERO */;/*!40111 SET OLD_SQL_NOTES=SQL_NOTES, SQL_NOTES=0 */;CREATE DATABASE /*!32312 IF NOT EXISTS*/myemployees /*!40100 DEFAULT CHARACTERSET gb2312 */;USE myemployees;/*Table structure for table departme nts */DROP TABLE IF EXISTS departme nts;CREATE TABLE dep

3、artme nts (departme nt_id i nt(4) NOT NULL AUTO_INCREMENT,departme nt_name varchar(3) DEFAULT NULL,man ager_id i nt(6) DEFAULT NULL,location_id int(4) DEFAULT NULL,PRIMARY KEY (department_id),KEY loc_id_fk (location_id),CONSTRAINT loc_id_fk FOREIGN KEY (location_id) REFERENCES locations (location_id

4、) ENGINE=InnoDB AUTO_INCREMENT=271 DEFAULT CHARSET=gb2312;/*Data for the table departments */insert into departments(department_id,department_name,manager_id,location_id) values(10,Adm,200,1700),(20,Mar,201,1800),(30,Pur,114,1700),(40,Hum,203,2400),(50,Shi,121,1500),(60,IT,103,1400),(70,Pub,204,2700

5、),(80,Sal,145,2500),(90,Exe,100,1700),(100,Fin,108,1700),(110,Acc,205,1700),(120,Tre,NULL,1700),(130,Cor,NULL,1700),(140,Con,NULL,1700),(150,Sha,NULL,1700),(160,Ben,NULL,1700),(170,Man,NULL,1700),(180,Con,NULL,1700),(190,Con,NULL,1700),(200,Ope,NULL,1700),(210,IT,NULL,1700),(220,NOC,NULL,1700),(230,

6、IT,NULL,1700),(240,Gov,NULL,1700),(250,Ret,NULL,1700),(260,Rec,NULL,1700),(270,Pay,NULL,1700);/*Table structure for table employees */DROP TABLE IF EXISTS employees;CREATE TABLE employees (employee_id int(6) NOT NULL AUTO_INCREMENT,first_name varchar(20) DEFAULT NULL,last_name varchar(25) DEFAULT NU

7、LL,email varchar(25) DEFAULT NULL,phone_number varchar(20) DEFAULT NULL,job_id varchar(IO) DEFAULT NULL,salary double(10,2) DEFAULT NULL,commission_pct double(4,2) DEFAULT NULL,manager_id int(6) DEFAULT NULL,department_id int(4) DEFAULT NULL,hiredate datetime DEFAULT NULL,PRIMARY KEY (employee_id),K

8、EY dept_id_fk (department_id),KEY job_id_fk (job_id),CONSTRAINT dept_id_fk FOREIGN KEY (department_id) REFERENCESdepartments(department_id),CONSTRAINT job_id_fk FOREIGN KEY (job_id) REFERENCES jobs (job_id) ENGINE=InnoDB AUTO_INCREMENT=207 DEFAULT CHARSET=gb2312;/*Data for the table employees */inse

9、rtintoemployees(employee_id,first_name,last_name,email,phone_number,job_id,salary,c ommission_pct,manager_id,department_id,hiredate)values,1992-04-0300:00:00),(102,Lex,De60,1992-04-0304-038-03-03998-03-031998-03-03100,1998-03-03,1998-03-0300-09-0900,2000-09-09 00:00:00),(112,Jose090-09-092000-09-092

10、000-09-09-09-090-09-09000-09-0914,30,2000-09-0904-02-0602-062004-02-060,2004-02-060,2004-02-0602-062004-02-06004-02-06004-02-064-02-0650,2004-02-062004-02-0602-064-02-06,2002-12-230,2002-12-232002-12-2302-12-2323-12-2312-2302-12-23002-12-2302-12-230,2002-12-2300,80,2002-12-230,100,80,2002-12-2330,10

11、0,80, 2002-12-23,80,2002-12-230,2014-03-055,80, 2014-03-05-03-055,80, 2014-03-0520,145,80, 2014-03-0580,2014-03-05014-03-0514-03-0580,2014-03-052014-03-052014-03-05,2014-03-0580,2014-03-05,80,2014-03-0547,80, 2014-03-0503-05014-03-05014-03-054-03-058,80, 2014-03-0503-052014-03-05,2014-03-050,2014-03

12、-054-03-0580,2014-03-0580,2014-03-050,2014-03-0549,80, 2014-03-05,2014-03-05-03-050,2014-03-0514-03-051,50,2014-03-0503-05014-03-052014-03-0514-03-05-03-0514-03-052014-03-0503-052014-03-05,2014-03-054-03-0514-03-0514-03-054,50,2014-03-05014-03-052016-03-030,20,2016-03-036-03-036-03-03 0,2016-03-030,

13、2016-03-03 00:00:00);/*Table structure for table jobs */DROP TABLE IF EXISTS jobs;CREATE TABLE jobs (job_id varchar(10) NOT NULL,job_title varchar(35) DEFAULT NULL,min_salary int(6) DEFAULT NULL,max_salary int(6) DEFAULT NULL,PRIMARY KEY (job_id) ENGINE=InnoDB DEFAULT CHARSET=gb2312;/*Data for the t

14、able jobs */insert into jobs(job_id,job_title,min_salary,max_salary) values (AC_ACCOUNT,PublicAccountant,4200,9000),(AC_MGR,Accounting Manager,8200,16000),(AD_ASST,AdministrationAssistant,3000,6000),(AD_PRES,President,20000,40000),(AD_VP,Administration Vice President,15000,30000),(FI_ACCOUNT,Account

15、ant,4200,9000),(FI_MGR,FinanceManager,8200,16000),(HR_REP,HumanResourcesRepresentative,4000,9000),(IT_PROG,Programmer,4000,10000),(MK_MAN,Marketing Manager,9000,15000),(MK_REP,Marketing Representative,4000,9000),(PR_REP,Public Relations Representative,4500,10500),(PU_CLERK,Purchasing Clerk,2500,5500

16、),(PU_MAN,Purchasing Manager,8000,15000),(SA_MAN,Sales Manager,10000,20000),(SA_REP,Sales Representative,6000,12000),(SH_CLERK,Shipping Clerk,2500,5500),(ST_CLERK,Stock Clerk,2000,5000),(ST_MAN,Stock Manager,5500,8500);/*Table structure for table locati ons */DROP TABLE IF EXISTS locatio ns;CREATE T

17、ABLE locations (location_id int(11) NOT NULL AUTO_INCREMENT,street_address varchar(40) DEFAULT NULL,postal_code varchar(12) DEFAULT NULL,city varchar(30) DEFAULT NULL,state_province varchar(25) DEFAULT NULL,country_id varchar(2) DEFAULT NULL,PRIMARY KEY (location_id) ENGINE=InnoDB AUTO_INCREMENT=320

18、1 DEFAULT CHARSET=gb2312;/*Data for the table locations */insertintolocations(location_id,street_address,postal_code,city,state_province,country_id)values (1000,1297 Via Cola di Rie,00989,Roma,NULL,IT),(1100,93091 Calle della Testa,10934,Venice,NULL,IT),(1200,2017Shinjuku-ku,1689,Tokyo,TokyoPrefectu

19、re,JP),(1300,9450Kamiya-cho,6823,Hiroshima,NULL,JP),(1400,2014Jabberwocky Rd,26192,Southlake,Texas,US),(1500,2011 Interiors Blvd,99236,South San Francisco,California,US),(1600,2007 Zagora St,50090,South Brunswick,New Jersey,US),(1700,2004 Charade Rd,98199,Seattle,Washington,US),(1800,147 Spadina Ave

20、,M5V2L7,Toronto,Ontario,CA),(1900,6092BoxwoodSt,YSW9T2,Whitehorse,Yukon,CA),(2000,40-5-12Laogia ngge n,190518,Beiji ng,NULL,CN),(2100,1298Vileparle(E),490231,Bombay,Maharashtra,IN),(2200,12-98 Victoria Street,2901,Syd ney,New South Wales,AU),(2300,198Cleme nti North,540198,Si ngapore,NULL,SG),(2400,

21、8204Arthur St,NULL,Lo ndo n,NULL,UK),(2500,Magdale n Centre, The Oxford Scie nee Park,OX9 9ZB,Oxford,Oxford,UK),(2600,9702ChesterRoad,09629850293,Stretford,Ma nehester,UK),(2700,Sehwa nthalerstr.7031,80925,Mu nich,Bavaria,DE),(2800,RuaFrei Can eea 1360 ,01307-002,SaoPaulo,SaoPaulo,BR),(2900,20Ruedes

22、Corps-Sai nts,1730,Ge neva,Ge neve,CH),(3000,Murte nstrasse921,3095,Bern,BE,CH),(3100,PieterBreughelstraat837,3029SK,Utrecht,Utrecht,NL),(3200,Maria noEscobedo 9991,11932,MexicoCity,Distrito Federal,MX);/*!40101 SET SQL_MODE=OLD_SQL_MODE */;/*!40014 SET FOREIGN_KEY_CHECKS=OLD_FOREIGN_KEY_CHECKS */;/

23、*!40014 SET UNIQUE_CHECKS=OLD_UNIQUE_CHECKS */;/*!40111 SET SQL_NOTES=OLD_SQL_NOTES */;2.SQL練習#MySQL練習/*數據存放到表中，然后表再放到庫中一個庫中可以有多張表，每張表具有唯一的表名用來標識自己mysql注解：#單行注解單行注解（注意有空格）多行注解-注意：字符型和日期型的常量值必須用單引號引起來，數值型不需要*/-0.查詢當前數據庫的版本SELECT VERSION。；-1.查看當前所有數據庫SHOW DATABASES;-2.使用指定的數據庫（myemployees 是數據庫）USE mye

24、mployees;-3.查看當前庫下的所有表SHOW TABLES;-4.查看指定的表結構（employees/departments是表）DESC employees;DESC departme nts;#5.查詢單個字段SELECT last_name FROM employees;-6.查詢多個字段(多個字段之間使用逗號隔開)SELECT last_ name,salary,email FROM employees;-7.查詢所有字段(*)SELECT * FROM employees;-8.查詢所有last_name并起：姓名SELECT last_name AS 姓名 FROM em

25、ployees;SELECT last_name 姓名 FROM employees;-9.查詢所有last_name 并起：NameSELECT last_name Name FROM employees;-10.查詢員工表中涉及到的所有的部門編號(去重distinct)SELECT DISTINCT departme nt_id 部門FROM employees;-11.顯示我的工資是7200 ;CONCAT()連接符SELECT CONCAT(我的工資是salary) FROM employees;-12.顯示公司員工的姓名，獎金率，若獎金率為空的顯示0 ;IFNULL()SELECT

26、last_name AS 姓名，IFNULL(commission_pct,0) AS 獎金率 FROM employees;-13.顯示出表employees 的全部列，各個列之間用逗號連接，列頭顯示成OUT_PUTSELECT CONCAT(last_name,email,salary,hiredate) AS OUT_PUTFROM employees;-14.查詢工資12000的員工信息SELECT * FROM employees WHERE salary12000;-15.查詢部門編號不等于90號的員工名和部門編號SELECT last_name,departme nt_id FR

27、OM employees WHEREdepartme nt_id 90;SELECT last_name,departme nt_id FROM employees WHEREdepartme nt_id !=90;SELECT last_name,departme nt_id FROM employees WHEREdepartme nt_id NOT IN(90);-16.查詢工資在10000到20000之間的員工名、工資及獎金SELECT last_ name,salary,commissi on_pct FROM employeesWHERE salary BETWEEN 10000

28、AND 20000;-17.查詢員工名中包含字符a的員工信息SELECT last_name FROM employees WHERE last_name LIKE %a%;-18.查詢員工中第三個字符為n,第五個字符為l的員工名和工資SELECT last_name,salary FROM employees WHERE last_name LIKE_n_l%;-19.查詢員工名中第二個字符為_（下劃線）的員工名-轉義關鍵字ESCAPE建議使用$，其中使用報錯SELECT last_name FROM employees WHERE last_name LIKE _$_%ESCAPE $;-

29、20.查詢1992年入職的員工姓名和入職時間SELECT last_name,hiredate FROM employees WHERE hiredate LIKE1992%;-21.查詢員工的姓名及部門編號，其中部門編號為1開頭的SELECT last_name,departme nt_id FROM employees WHEREdepartme nt_id LIKE 1%-22.查詢獎金率不是空的并且工資小于 18000員工工資，員工名SELECT salary,last_name FROM employees WHERE commission_pct ISNOT NULL AND sa

30、lary12000;-23.查詢員工號為176的員工姓名和部門號及年薪(commission_pct 有可能 為null,與null運算都為空)SELECTlast_name,departme nt_id,salary*12*(1+IFNULL(commission_pct,0)FROM employeesWHERE employee_id = 176;-24.選擇工資不在5000至U 12000的員工的姓名和工資SELECT last_name,salary FROM employees WHERE salary NOT BETWEEN 5000 AND 12000;-25.選擇在20或50

31、號部門工作的員工姓名和部門號SELECT last_name,departme nt_id FROM employees WHEREdepartme nt_id IN(20,50);-26.選擇公司中沒有管理者的員工姓名及job_idSELECT last_name,job_id FROM employees WHERE man ager_id IS NULL;-27.選擇姓名中有字母a和e的員工姓名SELECT last_name FROM employeesWHERE last_name LIKE %a%e% OR last_name LIKE %e%a%;-28.顯示出表 employe

32、es 表中first_name 以e結尾的員工信息SELECT * FROM employees WHERE first_name LIKE %e;-29.查詢員工的信息并按照工資的從高到低排序(desc降序;asc升序)SELECT * FROM employees ORDER BY salary DESC;-30.按照年薪的高低顯示員工的信息和年薪表達式排序SELECT *,salary*12*(1+IFNULL(commission_pct,0) 年薪FROM employeesORDER BY salary*12*(1+IFNULL(commission_pct,0) DESC;-31

33、.選擇工資不在8000到17000的員工的姓名和工資，按工資降序SELECT last_name,salary FROM employees WHERE salary NOT BETWEEN 8000 AND 17000ORDER BY salary DESC;-32.查詢員工的姓名和部門號和年薪，按年薪降序按姓名升序SELECT last_name AS 姓名,department_id AS部 門,salary*12*(1+IFNULL(commission_pct,0) AS 年薪FROM employees ORDER BY 年薪 DESC,姓名 ASC;-33.選擇工資不在8000到

34、17000的員工的姓名和工資，按工資降序SELECT last_name,salary FROM employees WHERE salary NOT BETWEEN 8000 AND 17000ORDER BY salary DESC;-34.查詢郵箱中包含e的員工信息，并先按郵箱的字節數降序，再按部門號 升序SELECT email, LENGTH(email),departme nt_id FROM employees WHERE email LIKE %e%ORDER BY LENGTH(email) DESC ,departme nt_id ASC;-35.顯示當前日期+時間s;其中

35、DUAL是偽表SELECT NOW();SELECT NOW() FROM DUAL;-36.顯示當前時間SELECT CURTIME();-37.顯示當前日期SELECT CURDATE();-38.顯示2016-02-11 到現在相差天數SELECT DATEDIFF(NOW(),2016-02-11);-39.用英文顯示當前的月份SELECT MONTHNAME(2018-02-04);-40.顯示員工的名字字節數長度SELECT LENGTH(last_nam) 長度 FROM employees;-41.把員工的姓變成大寫名變成小寫連接顯示姓名SELECT CONCAT(UPPER(

36、last_name),LOWER(first_name)姓名 FROMemployees;-42.從我是中國運動員中截取中國顯示出來SELECT SUBSTR(我是中國運動員,3,2);SELECT SUBSTRING(我是中國運動員,3,2);-43.從我是中國運動員我愛中國這個國家中中國首次出現的位置？SELECT INSTR(我是中國運動員我愛中國這個國家,中國);-44.從我是 運動員中去除空格(TRIM只能去除前后空格)SELECT TRIM（我是 運動員）;-45.員工的工資用10位數顯示，不夠的用空格補齊SELECT LPAD(salary,10,*) FROM employee

37、s;SELECT RPAD(salary,10,*) FROM employees;-46.從我是美國運動員我愛美國這個國家中，把美國替換成中國SELECT REPLACE(我是美國運動員我愛美國這個國家,美國,中國);-47.從我是 運動員*中去除空格SELECT REPLACE(我是 運動員 *, ,) AS ji;-48.將員工的姓名按首字母排序，并寫出姓名的長度(length)SELECT last_name,LENGTH(last_name) 長度FROM employeesORDER BY last_name ASC;-49.查詢員工工資在10000到15000的員工姓名、工資，且

38、工資保留兩位小 數(四舍五入),并且按工資降序排列SELECT last_name,ROUND(salary,2) FROM employeesWHERE salary BETWEEN 10000 AND 15000ORDER BY salary DESC;SELECT TRUNCATE(100.85,1);-50.查詢員工表中的最大入職時間和最小入職時間的相差天數(DIFFRENCE)SELECT DATEDIFF(MAX(hiredate),MIN(hiredate) DIFFRENCEFROM employees;/*51.使用case-when，按照下面的條件：job gradeAD_

39、PRES AST_MAN BIT_PROG CSA_REP DST_CLERK E產生以下結果Last_ name Job_id Gradeking AD_PRES A*/SELECT last_ name Last_ name,job_id Job_id,CASE job_id WHEN AD_PRES THEN AWHEN ST_MAN THEN BWHEN IT_PROG THEN CWHEN SA_REP THEN DWHEN ST_CLERK THEN EEND AS gradeFROM employees;-52.查詢每個工種的員工平均工資SELECT AVG(salary),jo

40、b_idFROM employeesGROUP BY job_id;-53.查詢郵箱中包含a字符的 每個部門的最高工資SELECT MAX(salary),departme nt_idFROM employeesWHERE email LIKE %a%GROUP BY departme nt_id;-54.查詢有獎金的每個領導手下員工的平均工資SELECT AVG(salary),ma nager_idFROM employeesWHERE commission_pct IS NOT NULLGROUP BY man ager_id;-55.每個工種有獎金的員工的最高工資12000的工種編號和

41、最高工資SELECT job_id,MAX(salary)FROM employeesWHERE commission_pct IS NOT NULLGROUP BY job_idHAVING MAX(salary)12000;-56.每個工種有獎金的員工的最高工資6000的工種編號和最高工資，按最高 工資升序SELECT job_id,MAX(salary) mFROM employeesWHERE commission_pct IS NOT NULLGROUP BY job_idHAVING m6000ORDER BY m ;-57.查詢每個工種每個部門的最低工資，并按最低工資降序SELE

42、CT MIN(salary),job_id,departme nt_idFROM employeesGROUP BY departme nt_id,job_idORDER BY MIN(salary) DESC;-58.姓名中首字符大寫，其他字符小寫然后用 拼接，顯示出來SELECTCONCAT(UPPER(SUBSTR(last_name,1,1),_ ,L OWER(SUBSTR(last_name,2)out_put FROM employees;-59.查詢有獎金的員工名和入職日期(xx月/xx日xx年)SELECT last_name,DATE_FORMAT(hiredate,%m

43、月 /%d 日 %y 年)入職日FROM employeesWHERE commission_pct IS NOT NULL;/*60.查詢員工的工資，要求部門號=30，顯示的工資為1.1倍部門號=40，顯示的工資為1.2倍部門號=50，顯示的工資為1.3倍其他部門，顯示的工資為原工資*/SELECT salary 原始工資，department_id,CASE departme nt_idWHEN 30 THEN salary*1.1WHEN 40 THEN salary*1.2WHEN 50 THEN salary*1.3ELSE salaryEND AS新工資FROM employees

44、;/*61.查詢員工的工資的情況如果工資20000,顯示A級別如果工資15000,顯示B級別如果工資10000，顯示C級別否則，顯示D級別*/SELECT salary,CASEWHEN salary20000 THEN AWHEN salary15000 THEN BWHEN salary10000 THEN CELSE DEND AS工資級別FROM employees;-62.查詢每個部門的員工個數SELECT COUNT(*) FROM employees WHERE departme nt_id=90;-63.查詢每個工種的員工平均工資SELECT AVG(salary),job_i

45、dFROM employeesGROUP BY job_id;-64.查詢每個位置的部門個數SELECT COUNT(*),locationdFROM departme ntsGROUP BY location_id;-65.查詢郵箱中包含a字符的 每個部門的最高工資SELECT MAX(salary),departme nt_idFROM employeesWHERE email LIKE %a%GROUP BY departme nt_id;-66.每個工種有獎金的員工的最高工資12000的工種編號和最高工資SELECT job_id,MAX(salary)FROM employeesWH

46、ERE commission_pct IS NOT NULLGROUP BY job_idHAVING MAX(salary)12000;-67.領導編號102的每個領導手下的最低工資大于 5000的領導編號和最低 工資SELECT man ager_id,MIN(salary)FROM employeesGROUP BY ma nager_idHAVING MIN(salary)5000;-68.每個工種有獎金的員工的最高工資6000的工種編號和最高工資，按最高 工資升序SELECT job_id,MAX(salary) mFROM employeesWHERE commission_pct

47、 IS NOT NULLGROUP BY job_idHAVING m6000ORDER BY m ;-69.查詢每個工種每個部門的最低工資，并按最低工資降序SELECT MIN(salary),job_id,departme nt_idFROM employeesGROUP BY departme nt_id,job_idORDER BY MIN(salary) DESC;-70查詢員工名和對應的部門名SELECT last_ name,departme nt_n ame FROM employees,departme ntsWHERE employees.departme nt_id =

48、departme nts.departme nt_id;-71.查詢員工名、工種號、工種名SELECT last_ name,j.job_id,job_titleFROM employees e , jobs jWHERE e.job_id = j.job_id;-72.查詢有獎金的員工名、部門名SELECT last_ name,departme nt_n ame,commissio n_ pctFROM employees e,departme nts dWHERE e.departme nt_id = d.departme nt_id AND commissio n_pct ISNOT

49、NULL;-73.查詢城市名中第二個字符為0的部門名和城市名SELECT departme nt_n ame,cityFROM departme nts d,locati ons lWHERE d.location_id = l.locatio nd AND city LIKE _o%;-74.查詢每個城市部門的個數SELECT COUNT(*),city FROM departme nts d,locatio ns lWHERE d.location_id = l.locatio ndGROUP BY city;-75.查詢有獎金的每個部門的部門名和部門的領導編號和該部門的最低工資SELEC

50、T departme nt_n ame,d.ma nager_id,MIN(salary)FROM employees e ,departme nts dWHERE e.departme nt_id = d.departme nt_idAND commission_pct IS NOT NULLGROUP BY departme nt_n ame,d.ma nager_id;-76.查詢每個工種的工種名和員工的個數，并且按員工個數降序SELECT job_title,COUNT(*)FROM employees e,jobs jWHERE e.job_id = j.job_idGROUP BY

51、 job_titleORDER BY COUNT(*) DESC;-77.查詢員工名、部門名和所在的城市SELECT last_ name,departme nt_n ame,cityFROM employees e,departme nts d ,locati ons lWHERE e.departme nt_id = d.departme nt_idAND d.locatio n_id = l.locati on_id;-78.查詢員工的工資和工資級別，并且工資級別為DSELECT salary,grade_levelFROM employees e,job_grades jWHERE s

52、alary BETWEEN j.lowest_sal AND j.highest_salAND j.grade_level=D;-79.查詢工資級別的個數20的個數，并且按工資級別降序SELECT grade_level,COUNT(*)FROM employees e,job_grades jWHERE salary BETWEEN j.lowest_sal AND j.highest_salGROUP BY grade_levelHAVING COUNT(*)20ORDER BY grade_level DESC;-80.查詢員工名和上級的名稱SELECT e.l ast_ name, m.l ast_ nameFROM employees e,employees mWHERE e.ma nager_id = m.employee_id;-81.顯示所有員工的姓名，部門號和部門名稱。SELECT last_ name,d.departme nt_id,departme nt_n ameFROM employees e,departme nts dWHERE e.departme nt_id = d.departm