创建一个sql文件命名为 (myemployees.sql)
;
;
;
;
;
;
CREATE DATABASE ` myemployees` ;
USE ` myemployees` ;
DROP TABLE IF EXISTS ` departments` ;
CREATE TABLE ` departments` (
` department_id` int ( 4 ) NOT NULL AUTO_INCREMENT ,
` department_name` varchar ( 3 ) DEFAULT NULL ,
` manager_id` int ( 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` )
) ENGINE = InnoDB AUTO_INCREMENT = 271 DEFAULT CHARSET = gb2312;
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 ) , ( 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 , 'IT ' , NULL , 1700 ) , ( 240 , 'Gov' , NULL , 1700 ) , ( 250 , 'Ret' , NULL , 1700 ) , ( 260 , 'Rec' , NULL , 1700 ) , ( 270 , 'Pay' , NULL , 1700 ) ;
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 NULL ,
` email` varchar ( 25 ) DEFAULT NULL ,
` phone_number` varchar ( 20 ) DEFAULT NULL ,
` job_id` varchar ( 10 ) 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` ) ,
KEY ` dept_id_fk` ( ` department_id` ) ,
KEY ` job_id_fk` ( ` job_id` ) ,
CONSTRAINT ` dept_id_fk` FOREIGN KEY ( ` department_id` ) REFERENCES ` departments` ( ` department_id` ) ,
CONSTRAINT ` job_id_fk` FOREIGN KEY ( ` job_id` ) REFERENCES ` jobs` ( ` job_id` )
) ENGINE = InnoDB AUTO_INCREMENT = 207 DEFAULT CHARSET = gb2312;
insert into ` employees` ( ` employee_id` , ` first_name` , ` last_name` , ` email` , ` phone_number` , ` job_id` , ` salary` , ` commission_pct` , ` manager_id` , ` department_id` , ` hiredate` ) values ( 100 , 'Steven' , 'K_ing' , 'SKING' , '515.123.4567' , 'AD_PRES' , 24000.00 , NULL , NULL , 90 , '1992-04-03 00:00:00' ) , ( 101 , 'Neena' , 'Kochhar' , 'NKOCHHAR' , '515.123.4568' , 'AD_VP' , 17000.00 , NULL , 100 , 90 , '1992-04-03 00:00:00' ) , ( 102 , 'Lex' , 'De Haan' , 'LDEHAAN' , '515.123.4569' , 'AD_VP' , 17000.00 , NULL , 100 , 90 , '1992-04-03 00:00:00' ) , ( 103 , 'Alexander' , 'Hunold' , 'AHUNOLD' , '590.423.4567' , 'IT_PROG' , 9000.00 , NULL , 102 , 60 , '1992-04-03 00:00:00' ) , ( 104 , 'Bruce' , 'Ernst' , 'BERNST' , '590.423.4568' , 'IT_PROG' , 6000.00 , NULL , 103 , 60 , '1992-04-03 00:00:00' ) , ( 105 , 'David' , 'Austin' , 'DAUSTIN' , '590.423.4569' , 'IT_PROG' , 4800.00 , NULL , 103 , 60 , '1998-03-03 00:00:00' ) , ( 106 , 'Valli' , 'Pataballa' , 'VPATABAL' , '590.423.4560' , 'IT_PROG' , 4800.00 , NULL , 103 , 60 , '1998-03-03 00:00:00' ) , ( 107 , 'Diana' , 'Lorentz' , 'DLORENTZ' , '590.423.5567' , 'IT_PROG' , 4200.00 , NULL , 103 , 60 , '1998-03-03 00:00:00' ) , ( 108 , 'Nancy' , 'Greenberg' , 'NGREENBE' , '515.124.4569' , 'FI_MGR' , 12000.00 , NULL , 101 , 100 , '1998-03-03 00:00:00' ) , ( 109 , 'Daniel' , 'Faviet' , 'DFAVIET' , '515.124.4169' , 'FI_ACCOUNT' , 9000.00 , NULL , 108 , 100 , '1998-03-03 00:00:00' ) , ( 110 , 'John' , 'Chen' , 'JCHEN' , '515.124.4269' , 'FI_ACCOUNT' , 8200.00 , NULL , 108 , 100 , '2000-09-09 00:00:00' ) , ( 111 , 'Ismael' , 'Sciarra' , 'ISCIARRA' , '515.124.4369' , 'FI_ACCOUNT' , 7700.00 , NULL , 108 , 100 , '2000-09-09 00:00:00' ) , ( 112 , 'Jose Manuel' , 'Urman' , 'JMURMAN' , '515.124.4469' , 'FI_ACCOUNT' , 7800.00 , NULL , 108 , 100 , '2000-09-09 00:00:00' ) , ( 113 , 'Luis' , 'Popp' , 'LPOPP' , '515.124.4567' , 'FI_ACCOUNT' , 6900.00 , NULL , 108 , 100 , '2000-09-09 00:00:00' ) , ( 114 , 'Den' , 'Raphaely' , 'DRAPHEAL' , '515.127.4561' , 'PU_MAN' , 11000.00 , NULL , 100 , 30 , '2000-09-09 00:00:00' ) , ( 115 , 'Alexander' , 'Khoo' , 'AKHOO' , '515.127.4562' , 'PU_CLERK' , 3100.00 , NULL , 114 , 30 , '2000-09-09 00:00:00' ) , ( 116 , 'Shelli' , 'Baida' , 'SBAIDA' , '515.127.4563' , 'PU_CLERK' , 2900.00 , NULL , 114 , 30 , '2000-09-09 00:00:00' ) , ( 117 , 'Sigal' , 'Tobias' , 'STOBIAS' , '515.127.4564' , 'PU_CLERK' , 2800.00 , NULL , 114 , 30 , '2000-09-09 00:00:00' ) , ( 118 , 'Guy' , 'Himuro' , 'GHIMURO' , '515.127.4565' , 'PU_CLERK' , 2600.00 , NULL , 114 , 30 , '2000-09-09 00:00:00' ) , ( 119 , 'Karen' , 'Colmenares' , 'KCOLMENA' , '515.127.4566' , 'PU_CLERK' , 2500.00 , NULL , 114 , 30 , '2000-09-09 00:00:00' ) , ( 120 , 'Matthew' , 'Weiss' , 'MWEISS' , '650.123.1234' , 'ST_MAN' , 8000.00 , NULL , 100 , 50 , '2004-02-06 00:00:00' ) , ( 121 , 'Adam' , 'Fripp' , 'AFRIPP' , '650.123.2234' , 'ST_MAN' , 8200.00 , NULL , 100 , 50 , '2004-02-06 00:00:00' ) , ( 122 , 'Payam' , 'Kaufling' , 'PKAUFLIN' , '650.123.3234' , 'ST_MAN' , 7900.00 , NULL , 100 , 50 , '2004-02-06 00:00:00' ) , ( 123 , 'Shanta' , 'Vollman' , 'SVOLLMAN' , '650.123.4234' , 'ST_MAN' , 6500.00 , NULL , 100 , 50 , '2004-02-06 00:00:00' ) , ( 124 , 'Kevin' , 'Mourgos' , 'KMOURGOS' , '650.123.5234' , 'ST_MAN' , 5800.00 , NULL , 100 , 50 , '2004-02-06 00:00:00' ) , ( 125 , 'Julia' , 'Nayer' , 'JNAYER' , '650.124.1214' , 'ST_CLERK' , 3200.00 , NULL , 120 , 50 , '2004-02-06 00:00:00' ) , ( 126 , 'Irene' , 'Mikkilineni' , 'IMIKKILI' , '650.124.1224' , 'ST_CLERK' , 2700.00 , NULL , 120 , 50 , '2004-02-06 00:00:00' ) , ( 127 , 'James' , 'Landry' , 'JLANDRY' , '650.124.1334' , 'ST_CLERK' , 2400.00 , NULL , 120 , 50 , '2004-02-06 00:00:00' ) , ( 128 , 'Steven' , 'Markle' , 'SMARKLE' , '650.124.1434' , 'ST_CLERK' , 2200.00 , NULL , 120 , 50 , '2004-02-06 00:00:00' ) , ( 129 , 'Laura' , 'Bissot' , 'LBISSOT' , '650.124.5234' , 'ST_CLERK' , 3300.00 , NULL , 121 , 50 , '2004-02-06 00:00:00' ) , ( 130 , 'Mozhe' , 'Atkinson' , 'MATKINSO' , '650.124.6234' , 'ST_CLERK' , 2800.00 , NULL , 121 , 50 , '2004-02-06 00:00:00' ) , ( 131 , 'James' , 'Marlow' , 'JAMRLOW' , '650.124.7234' , 'ST_CLERK' , 2500.00 , NULL , 121 , 50 , '2004-02-06 00:00:00' ) , ( 132 , 'TJ' , 'Olson' , 'TJOLSON' , '650.124.8234' , 'ST_CLERK' , 2100.00 , NULL , 121 , 50 , '2004-02-06 00:00:00' ) , ( 133 , 'Jason' , 'Mallin' , 'JMALLIN' , '650.127.1934' , 'ST_CLERK' , 3300.00 , NULL , 122 , 50 , '2004-02-06 00:00:00' ) , ( 134 , 'Michael' , 'Rogers' , 'MROGERS' , '650.127.1834' , 'ST_CLERK' , 2900.00 , NULL , 122 , 50 , '2002-12-23 00:00:00' ) , ( 135 , 'Ki' , 'Gee' , 'KGEE' , '650.127.1734' , 'ST_CLERK' , 2400.00 , NULL , 122 , 50 , '2002-12-23 00:00:00' ) , ( 136 , 'Hazel' , 'Philtanker' , 'HPHILTAN' , '650.127.1634' , 'ST_CLERK' , 2200.00 , NULL , 122 , 50 , '2002-12-23 00:00:00' ) , ( 137 , 'Renske' , 'Ladwig' , 'RLADWIG' , '650.121.1234' , 'ST_CLERK' , 3600.00 , NULL , 123 , 50 , '2002-12-23 00:00:00' ) , ( 138 , 'Stephen' , 'Stiles' , 'SSTILES' , '650.121.2034' , 'ST_CLERK' , 3200.00 , NULL , 123 , 50 , '2002-12-23 00:00:00' ) , ( 139 , 'John' , 'Seo' , 'JSEO' , '650.121.2019' , 'ST_CLERK' , 2700.00 , NULL , 123 , 50 , '2002-12-23 00:00:00' ) , ( 140 , 'Joshua' , 'Patel' , 'JPATEL' , '650.121.1834' , 'ST_CLERK' , 2500.00 , NULL , 123 , 50 , '2002-12-23 00:00:00' ) , ( 141 , 'Trenna' , 'Rajs' , 'TRAJS' , '650.121.8009' , 'ST_CLERK' , 3500.00 , NULL , 124 , 50 , '2002-12-23 00:00:00' ) , ( 142 , 'Curtis' , 'Davies' , 'CDAVIES' , '650.121.2994' , 'ST_CLERK' , 3100.00 , NULL , 124 , 50 , '2002-12-23 00:00:00' ) , ( 143 , 'Randall' , 'Matos' , 'RMATOS' , '650.121.2874' , 'ST_CLERK' , 2600.00 , NULL , 124 , 50 , '2002-12-23 00:00:00' ) , ( 144 , 'Peter' , 'Vargas' , 'PVARGAS' , '650.121.2004' , 'ST_CLERK' , 2500.00 , NULL , 124 , 50 , '2002-12-23 00:00:00' ) , ( 145 , 'John' , 'Russell' , 'JRUSSEL' , '011.44.1344.429268' , 'SA_MAN' , 14000.00 , 0.40 , 100 , 80 , '2002-12-23 00:00:00' ) , ( 146 , 'Karen' , 'Partners' , 'KPARTNER' , '011.44.1344.467268' , 'SA_MAN' , 13500.00 , 0.30 , 100 , 80 , '2002-12-23 00:00:00' ) , ( 147 , 'Alberto' , 'Errazuriz' , 'AERRAZUR' , '011.44.1344.429278' , 'SA_MAN' , 12000.00 , 0.30 , 100 , 80 , '2002-12-23 00:00:00' ) , ( 148 , 'Gerald' , 'Cambrault' , 'GCAMBRAU' , '011.44.1344.619268' , 'SA_MAN' , 11000.00 , 0.30 , 100 , 80 , '2002-12-23 00:00:00' ) , ( 149 , 'Eleni' , 'Zlotkey' , 'EZLOTKEY' , '011.44.1344.429018' , 'SA_MAN' , 10500.00 , 0.20 , 100 , 80 , '2002-12-23 00:00:00' ) , ( 150 , 'Peter' , 'Tucker' , 'PTUCKER' , '011.44.1344.129268' , 'SA_REP' , 10000.00 , 0.30 , 145 , 80 , '2014-03-05 00:00:00' ) , ( 151 , 'David' , 'Bernstein' , 'DBERNSTE' , '011.44.1344.345268' , 'SA_REP' , 9500.00 , 0.25 , 145 , 80 , '2014-03-05 00:00:00' ) , ( 152 , 'Peter' , 'Hall' , 'PHALL' , '011.44.1344.478968' , 'SA_REP' , 9000.00 , 0.25 , 145 , 80 , '2014-03-05 00:00:00' ) , ( 153 , 'Christopher' , 'Olsen' , 'COLSEN' , '011.44.1344.498718' , 'SA_REP' , 8000.00 , 0.20 , 145 , 80 , '2014-03-05 00:00:00' ) , ( 154 , 'Nanette' , 'Cambrault' , 'NCAMBRAU' , '011.44.1344.987668' , 'SA_REP' , 7500.00 , 0.20 , 145 , 80 , '2014-03-05 00:00:00' ) , ( 155 , 'Oliver' , 'Tuvault' , 'OTUVAULT' , '011.44.1344.486508' , 'SA_REP' , 7000.00 , 0.15 , 145 , 80 , '2014-03-05 00:00:00' ) , ( 156 , 'Janette' , 'K_ing' , 'JKING' , '011.44.1345.429268' , 'SA_REP' , 10000.00 , 0.35 , 146 , 80 , '2014-03-05 00:00:00' ) , ( 157 , 'Patrick' , 'Sully' , 'PSULLY' , '011.44.1345.929268' , 'SA_REP' , 9500.00 , 0.35 , 146 , 80 , '2014-03-05 00:00:00' ) , ( 158 , 'Allan' , 'McEwen' , 'AMCEWEN' , '011.44.1345.829268' , 'SA_REP' , 9000.00 , 0.35 , 146 , 80 , '2014-03-05 00:00:00' ) , ( 159 , 'Lindsey' , 'Smith' , 'LSMITH' , '011.44.1345.729268' , 'SA_REP' , 8000.00 , 0.30 , 146 , 80 , '2014-03-05 00:00:00' ) , ( 160 , 'Louise' , 'Doran' , 'LDORAN' , '011.44.1345.629268' , 'SA_REP' , 7500.00 , 0.30 , 146 , 80 , '2014-03-05 00:00:00' ) , ( 161 , 'Sarath' , 'Sewall' , 'SSEWALL' , '011.44.1345.529268' , 'SA_REP' , 7000.00 , 0.25 , 146 , 80 , '2014-03-05 00:00:00' ) , ( 162 , 'Clara' , 'Vishney' , 'CVISHNEY' , '011.44.1346.129268' , 'SA_REP' , 10500.00 , 0.25 , 147 , 80 , '2014-03-05 00:00:00' ) , ( 163 , 'Danielle' , 'Greene' , 'DGREENE' , '011.44.1346.229268' , 'SA_REP' , 9500.00 , 0.15 , 147 , 80 , '2014-03-05 00:00:00' ) , ( 164 , 'Mattea' , 'Marvins' , 'MMARVINS' , '011.44.1346.329268' , 'SA_REP' , 7200.00 , 0.10 , 147 , 80 , '2014-03-05 00:00:00' ) , ( 165 , 'David' , 'Lee' , 'DLEE' , '011.44.1346.529268' , 'SA_REP' , 6800.00 , 0.10 , 147 , 80 , '2014-03-05 00:00:00' ) , ( 166 , 'Sundar' , 'Ande' , 'SANDE' , '011.44.1346.629268' , 'SA_REP' , 6400.00 , 0.10 , 147 , 80 , '2014-03-05 00:00:00' ) , ( 167 , 'Amit' , 'Banda' , 'ABANDA' , '011.44.1346.729268' , 'SA_REP' , 6200.00 , 0.10 , 147 , 80 , '2014-03-05 00:00:00' ) , ( 168 , 'Lisa' , 'Ozer' , 'LOZER' , '011.44.1343.929268' , 'SA_REP' , 11500.00 , 0.25 , 148 , 80 , '2014-03-05 00:00:00' ) , ( 169 , 'Harrison' , 'Bloom' , 'HBLOOM' , '011.44.1343.829268' , 'SA_REP' , 10000.00 , 0.20 , 148 , 80 , '2014-03-05 00:00:00' ) , ( 170 , 'Tayler' , 'Fox' , 'TFOX' , '011.44.1343.729268' , 'SA_REP' , 9600.00 , 0.20 , 148 , 80 , '2014-03-05 00:00:00' ) , ( 171 , 'William' , 'Smith' , 'WSMITH' , '011.44.1343.629268' , 'SA_REP' , 7400.00 , 0.15 , 148 , 80 , '2014-03-05 00:00:00' ) , ( 172 , 'Elizabeth' , 'Bates' , 'EBATES' , '011.44.1343.529268' , 'SA_REP' , 7300.00 , 0.15 , 148 , 80 , '2014-03-05 00:00:00' ) , ( 173 , 'Sundita' , 'Kumar' , 'SKUMAR' , '011.44.1343.329268' , 'SA_REP' , 6100.00 , 0.10 , 148 , 80 , '2014-03-05 00:00:00' ) , ( 174 , 'Ellen' , 'Abel' , 'EABEL' , '011.44.1644.429267' , 'SA_REP' , 11000.00 , 0.30 , 149 , 80 , '2014-03-05 00:00:00' ) , ( 175 , 'Alyssa' , 'Hutton' , 'AHUTTON' , '011.44.1644.429266' , 'SA_REP' , 8800.00 , 0.25 , 149 , 80 , '2014-03-05 00:00:00' ) , ( 176 , 'Jonathon' , 'Taylor' , 'JTAYLOR' , '011.44.1644.429265' , 'SA_REP' , 8600.00 , 0.20 , 149 , 80 , '2014-03-05 00:00:00' ) , ( 177 , 'Jack' , 'Livingston' , 'JLIVINGS' , '011.44.1644.429264' , 'SA_REP' , 8400.00 , 0.20 , 149 , 80 , '2014-03-05 00:00:00' ) , ( 178 , 'Kimberely' , 'Grant' , 'KGRANT' , '011.44.1644.429263' , 'SA_REP' , 7000.00 , 0.15 , 149 , NULL , '2014-03-05 00:00:00' ) , ( 179 , 'Charles' , 'Johnson' , 'CJOHNSON' , '011.44.1644.429262' , 'SA_REP' , 6200.00 , 0.10 , 149 , 80 , '2014-03-05 00:00:00' ) , ( 180 , 'Winston' , 'Taylor' , 'WTAYLOR' , '650.507.9876' , 'SH_CLERK' , 3200.00 , NULL , 120 , 50 , '2014-03-05 00:00:00' ) , ( 181 , 'Jean' , 'Fleaur' , 'JFLEAUR' , '650.507.9877' , 'SH_CLERK' , 3100.00 , NULL , 120 , 50 , '2014-03-05 00:00:00' ) , ( 182 , 'Martha' , 'Sullivan' , 'MSULLIVA' , '650.507.9878' , 'SH_CLERK' , 2500.00 , NULL , 120 , 50 , '2014-03-05 00:00:00' ) , ( 183 , 'Girard' , 'Geoni' , 'GGEONI' , '650.507.9879' , 'SH_CLERK' , 2800.00 , NULL , 120 , 50 , '2014-03-05 00:00:00' ) , ( 184 , 'Nandita' , 'Sarchand' , 'NSARCHAN' , '650.509.1876' , 'SH_CLERK' , 4200.00 , NULL , 121 , 50 , '2014-03-05 00:00:00' ) , ( 185 , 'Alexis' , 'Bull' , 'ABULL' , '650.509.2876' , 'SH_CLERK' , 4100.00 , NULL , 121 , 50 , '2014-03-05 00:00:00' ) , ( 186 , 'Julia' , 'Dellinger' , 'JDELLING' , '650.509.3876' , 'SH_CLERK' , 3400.00 , NULL , 121 , 50 , '2014-03-05 00:00:00' ) , ( 187 , 'Anthony' , 'Cabrio' , 'ACABRIO' , '650.509.4876' , 'SH_CLERK' , 3000.00 , NULL , 121 , 50 , '2014-03-05 00:00:00' ) , ( 188 , 'Kelly' , 'Chung' , 'KCHUNG' , '650.505.1876' , 'SH_CLERK' , 3800.00 , NULL , 122 , 50 , '2014-03-05 00:00:00' ) , ( 189 , 'Jennifer' , 'Dilly' , 'JDILLY' , '650.505.2876' , 'SH_CLERK' , 3600.00 , NULL , 122 , 50 , '2014-03-05 00:00:00' ) , ( 190 , 'Timothy' , 'Gates' , 'TGATES' , '650.505.3876' , 'SH_CLERK' , 2900.00 , NULL , 122 , 50 , '2014-03-05 00:00:00' ) , ( 191 , 'Randall' , 'Perkins' , 'RPERKINS' , '650.505.4876' , 'SH_CLERK' , 2500.00 , NULL , 122 , 50 , '2014-03-05 00:00:00' ) , ( 192 , 'Sarah' , 'Bell' , 'SBELL' , '650.501.1876' , 'SH_CLERK' , 4000.00 , NULL , 123 , 50 , '2014-03-05 00:00:00' ) , ( 193 , 'Britney' , 'Everett' , 'BEVERETT' , '650.501.2876' , 'SH_CLERK' , 3900.00 , NULL , 123 , 50 , '2014-03-05 00:00:00' ) , ( 194 , 'Samuel' , 'McCain' , 'SMCCAIN' , '650.501.3876' , 'SH_CLERK' , 3200.00 , NULL , 123 , 50 , '2014-03-05 00:00:00' ) , ( 195 , 'Vance' , 'Jones' , 'VJONES' , '650.501.4876' , 'SH_CLERK' , 2800.00 , NULL , 123 , 50 , '2014-03-05 00:00:00' ) , ( 196 , 'Alana' , 'Walsh' , 'AWALSH' , '650.507.9811' , 'SH_CLERK' , 3100.00 , NULL , 124 , 50 , '2014-03-05 00:00:00' ) , ( 197 , 'Kevin' , 'Feeney' , 'KFEENEY' , '650.507.9822' , 'SH_CLERK' , 3000.00 , NULL , 124 , 50 , '2014-03-05 00:00:00' ) , ( 198 , 'Donald' , 'OConnell' , 'DOCONNEL' , '650.507.9833' , 'SH_CLERK' , 2600.00 , NULL , 124 , 50 , '2014-03-05 00:00:00' ) , ( 199 , 'Douglas' , 'Grant' , 'DGRANT' , '650.507.9844' , 'SH_CLERK' , 2600.00 , NULL , 124 , 50 , '2014-03-05 00:00:00' ) , ( 200 , 'Jennifer' , 'Whalen' , 'JWHALEN' , '515.123.4444' , 'AD_ASST' , 4400.00 , NULL , 101 , 10 , '2016-03-03 00:00:00' ) , ( 201 , 'Michael' , 'Hartstein' , 'MHARTSTE' , '515.123.5555' , 'MK_MAN' , 13000.00 , NULL , 100 , 20 , '2016-03-03 00:00:00' ) , ( 202 , 'Pat' , 'Fay' , 'PFAY' , '603.123.6666' , 'MK_REP' , 6000.00 , NULL , 201 , 20 , '2016-03-03 00:00:00' ) , ( 203 , 'Susan' , 'Mavris' , 'SMAVRIS' , '515.123.7777' , 'HR_REP' , 6500.00 , NULL , 101 , 40 , '2016-03-03 00:00:00' ) , ( 204 , 'Hermann' , 'Baer' , 'HBAER' , '515.123.8888' , 'PR_REP' , 10000.00 , NULL , 101 , 70 , '2016-03-03 00:00:00' ) , ( 205 , 'Shelley' , 'Higgins' , 'SHIGGINS' , '515.123.8080' , 'AC_MGR' , 12000.00 , NULL , 101 , 110 , '2016-03-03 00:00:00' ) , ( 206 , 'William' , 'Gietz' , 'WGIETZ' , '515.123.8181' , 'AC_ACCOUNT' , 8300.00 , NULL , 205 , 110 , '2016-03-03 00:00:00' ) ;
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;
insert into ` jobs` ( ` job_id` , ` job_title` , ` min_salary` , ` max_salary` ) values ( 'AC_ACCOUNT' , 'Public Accountant' , 4200 , 9000 ) , ( 'AC_MGR' , 'Accounting Manager' , 8200 , 16000 ) , ( 'AD_ASST' , 'Administration Assistant' , 3000 , 6000 ) , ( 'AD_PRES' , 'President' , 20000 , 40000 ) , ( 'AD_VP' , 'Administration Vice President' , 15000 , 30000 ) , ( 'FI_ACCOUNT' , 'Accountant' , 4200 , 9000 ) , ( 'FI_MGR' , 'Finance Manager' , 8200 , 16000 ) , ( 'HR_REP' , 'Human Resources Representative' , 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 ) , ( '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 ) ;
DROP TABLE IF EXISTS ` locations` ;
CREATE TABLE ` 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 = 3201 DEFAULT CHARSET = gb2312;
insert into ` locations` ( ` 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 , '2017 Shinjuku-ku' , '1689' , 'Tokyo' , 'Tokyo Prefecture' , 'JP' ) , ( 1300 , '9450 Kamiya-cho' , '6823' , 'Hiroshima' , NULL , 'JP' ) , ( 1400 , '2014 Jabberwocky 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' , 'M5V 2L7' , 'Toronto' , 'Ontario' , 'CA' ) , ( 1900 , '6092 Boxwood St' , 'YSW 9T2' , 'Whitehorse' , 'Yukon' , 'CA' ) , ( 2000 , '40-5-12 Laogianggen' , '190518' , 'Beijing' , NULL , 'CN' ) , ( 2100 , '1298 Vileparle (E)' , '490231' , 'Bombay' , 'Maharashtra' , 'IN' ) , ( 2200 , '12-98 Victoria Street' , '2901' , 'Sydney' , 'New South Wales' , 'AU' ) , ( 2300 , '198 Clementi North' , '540198' , 'Singapore' , NULL , 'SG' ) , ( 2400 , '8204 Arthur St' , NULL , 'London' , NULL , 'UK' ) , ( 2500 , 'Magdalen Centre, The Oxford Science Park' , 'OX9 9ZB' , 'Oxford' , 'Oxford' , 'UK' ) , ( 2600 , '9702 Chester Road' , '09629850293' , 'Stretford' , 'Manchester' , 'UK' ) , ( 2700 , 'Schwanthalerstr. 7031' , '80925' , 'Munich' , 'Bavaria' , 'DE' ) , ( 2800 , 'Rua Frei Caneca 1360 ' , '01307-002' , 'Sao Paulo' , 'Sao Paulo' , 'BR' ) , ( 2900 , '20 Rue des Corps-Saints' , '1730' , 'Geneva' , 'Geneve' , 'CH' ) , ( 3000 , 'Murtenstrasse 921' , '3095' , 'Bern' , 'BE' , 'CH' ) , ( 3100 , 'Pieter Breughelstraat 837' , '3029SK' , 'Utrecht' , 'Utrecht' , 'NL' ) , ( 3200 , 'Mariano Escobedo 9991' , '11932' , 'Mexico City' , 'Distrito Federal,' , 'MX' ) ;
;
;
;
;
在cmd下导入即可
mysql> source E:\Bigdata\MySQL核心技术\数据库文件\myemployees. sql
employees表名介绍
departments表名介绍
locations表名介绍
jobs表名介绍
基础查询笔记
基础查询
语法:
select 查询列表 from 表名;
类似于:System. out. println ( 打印东西) ;
特点:
1 、查询列表可以是:表中的字段、常量值、表达式、函数
2 、查询的结果是一个虚拟的表格
USE myemployees;
1.查询表中的单个字段
SELECT last_name FROM employees;
2.查询表中的多个字段
SELECT last_name, salary, email FROM employees;
3.查询表中的所有字段
SELECT
` employee_id` ,
` first_name` ,
` last_name` ,
` phone_number` ,
` last_name` ,
` job_id` ,
` phone_number` ,
` job_id` ,
` salary` ,
` commission_pct` ,
` manager_id` ,
` department_id` ,
` hiredate`
FROM
employees ;
SELECT * FROM employees;
4.查询常量值
SELECT 100 ;
SELECT 'john' ;
5.查询表达式
SELECT 100 % 98 ;
6.查询函数
SELECT VERSION( ) ;
7.起别名
#方式一:使用as
SELECT 100 % 98 AS 结果;
SELECT last_name AS 姓, first_name AS 名 FROM employees;
#方式二:使用空格
SELECT last_name 姓, first_name 名 FROM employees;
#案例:查询salary,显示结果为 out put (一定要用双引号包起来,否则会报错)
SELECT salary AS "out put" FROM employees;
8.去重
SELECT DISTINCT department_id FROM employees;
9.+号的作用
SELECT CONCAT( 'a' , 'b' , 'c' ) AS 结果;
SELECT
CONCAT( last_name, first_name) AS 姓名
FROM
employees;