<MySQL>入门一

1. 数据库表

1.1 员工表

 1 Create Table
 2 
 3 CREATE TABLE `employees` (
 4   `employee_id` int(6) NOT NULL AUTO_INCREMENT,
 5   `first_name` varchar(20) DEFAULT NULL,
 6   `last_name` varchar(25) DEFAULT NULL,
 7   `email` varchar(25) DEFAULT NULL,
 8   `phone_number` varchar(20) DEFAULT NULL,
 9   `job_id` varchar(10) DEFAULT NULL,
10   `salary` double(10,2) DEFAULT NULL,
11   `commission_pct` double(4,2) DEFAULT NULL,
12   `manager_id` int(6) DEFAULT NULL,
13   `department_id` int(4) DEFAULT NULL,
14   `hiredate` datetime DEFAULT NULL,
15   PRIMARY KEY (`employee_id`),
16   KEY `dept_id_fk` (`department_id`),
17   KEY `job_id_fk` (`job_id`),
18   CONSTRAINT `dept_id_fk` FOREIGN KEY (`department_id`) REFERENCES `departments` (`department_id`),
19   CONSTRAINT `job_id_fk` FOREIGN KEY (`job_id`) REFERENCES `jobs` (`job_id`)
20 ) 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\');

1.2 部门表

 1 Create Table
 2 
 3 CREATE TABLE `departments` (
 4   `department_id` int(4) NOT NULL AUTO_INCREMENT,
 5   `department_name` varchar(3) DEFAULT NULL,
 6   `manager_id` int(6) DEFAULT NULL,
 7   `location_id` int(4) DEFAULT NULL,
 8   PRIMARY KEY (`department_id`),
 9   KEY `loc_id_fk` (`location_id`),
10   CONSTRAINT `loc_id_fk` FOREIGN KEY (`location_id`) REFERENCES `locations` (`location_id`)
11 ) 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);

1.3 工作表

CREATE TABLE `jobs` (
  `job_id` varchar(10) NOT NULL,
  `job_ ` 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_ `,`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);

1.4 位置表

Create Table

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\');

2. 基础查询（select）

2.1 查询单个字段

SELECT last_name FROM employees

2.2 查询多个字段

SELECT last_name,email,salary FROM employees

2.3 查询所有字段

SELECT * FROM employees

2.4 查询常量值、表达式

SELECT 100;
SELECT \'john\';
SELECT 100*8;
SELECT 100%98;

2.5 查询函数

SELECT VERSION();  -- 查询数据库版本

2.6 起别名（如果查询的字段有重名，可以使用起别名区分，别名中有特殊符号，用引号引起来）

SELECT last_name AS 姓,first_name AS 名 FROM employees;

SELECT last_name 姓,first_name 名 FROM employees;

SELECT salary AS \'out put\' FROM employees;

2.7 去重（distinct）

# 查询员工表中涉及到的所有部门编号，需要去除重复
SELECT DISTINCT department_id FROM employees;

2.8 +号的作用（concat、ifnull）

/*
java中+号
1.运算符，两个都为数值
2.连接符，只要有一个操作数为字符串

mysql中
仅仅：运算符
*/
SELECT 100+90; -- 做加法运算
SELECT \'100\'+90; -- 其中一方为字符串，试图将字符串转换成数值型，如果成功，继续做加法
SELECT \'john\'+90; -- 如果转换失败，则将字符串变为0，再运算
SELECT NULL+10; -- 其中一个为null,则结果肯定为null

# 将员工的姓和名连接成一个字段，并显示为姓名
SELECT CONCAT(\'a\',\'b\',\'c\') AS 结果; 
SELECT CONCAT(last_name,first_name) AS 姓名 FROM employees;

-- concat中拼接null值为null
SELECT CONCAT(100,NULL);

-- ifnull(需要转换的字段，转换后的值)
SELECT IFNULL(`commission_pct`,0) 奖金率,`commission_pct` FROM employees;

3. 条件查询

where

条件运算符：>，<， = ，!=， <>， >= ，<=

逻辑运算符：and（&&），or（||）， not（！）

模糊查询：like，between and，in，is null

3.1 简单的条件查询

# 查询工资>12000的员工信息 ( > )
SELECT * FROM employees WHERE salary > 12000;

# 查询部门编号不等于90号的员工名和部门编号 ( !=   <> )
SELECT last_name 名字,`department_id` 部门编号 FROM employees WHERE `department_id` <> 90;

# 查询工资在10000~20000之间的员工名，工资以及奖金  ( and ) 
SELECT last_name 名字,salary 工资,`commission_pct` 奖金 FROM employees WHERE salary >10000 AND salary <20000;

# 查询部门编号不在90到110之间，或者工资高于15000的员工信息 ( OR > < )
SELECT * FROM employees WHERE department_id >110 OR department_id < 90 OR salary > 15000

3.2 模糊查询

-- 通配符： % 任意多个字符（包括空字符）
--         _ 任意单个字符
-- 查询员工名字包含a的员工信息
SELECT * FROM employees WHERE last_name LIKE \'%a%\';

-- 查询员工第三个字符为e,第五个字符为a
SELECT * FROM employees WHERE last_name LIKE \'__n_l%\'

-- 查询第二个字符为_的员工名
SELECT * FROM employees WHERE last_name LIKE \'_\\_%\'  -- \\ 转义
SELECT * FROM employees WHERE last_name LIKE \'_$_%\' ESCAPE \'$\'  -- escape \'任意字符\' 进行转义 推荐

3.3 其他查询

between and

-- 查询员工编号在100到120之间的员工信息
-- 包含临界值
-- 两个临界值不要颠倒顺序
SELECT * FROM employees WHERE employee_id BETWEEN 100 AND 120;
SELECT * FROM employees WHERE employee_id >=100 AND employee_id<=120;

-- 查询员工工种编号是IT_PROG、AD_VP、AD_PRES之间
-- in:判断某字段的值是否属于in列表中的某一项

SELECT * FROM employees WHERE job_id IN (\'IT_PROG\',\'AD_VP\',\'AD_PRES\');

is null

-- 查询没有奖金的
-- =或<>不能判断 null 值 
-- 所以需要使用 is null ,is not null
SELECT * FROM employees WHERE commission_pct IS NULL;

-- 查询有奖金的
SELECT * FROM employees WHERE commission_pct IS NOT NULL;

安全等于

-- 查询没有奖金的
-- 安全等于：<=>
-- 可以当 is 或者 = 使用
SELECT * FROM employees WHERE commission_pct <=> NULL;

SELECT * FROM employees WHERE salary <=> 12000;

4. 排序查询

order by

-- select 查询字段 from 表名 where 筛选条件 order by [ASC,DESC]
-- asc 升序， desc 降序 ，不写默认是asc
-- order by 子句 支持单个字段，多个字段（，隔开），表达式，函数，别名
-- order by 一般放最后面，除了 limit

-- 查询员工信息 工资由高到低
SELECT * FROM employees ORDER BY salary DESC;
-- 查询员工信息 工资由低到高 默认asc
SELECT * FROM employees ORDER BY salary;

-- 查询部门编号>=90的员工信息，按入职时间排序
SELECT * FROM employees WHERE department_id >= 90 ORDER BY hiredate;

-- 按年薪由高到低 显示员工信息及年薪
SELECT *,salary*12*(1+IFNULL(\'commssion_pct\',0)) 年薪 FROM employees ORDER BY salary*12*(1+IFNULL(\'commssion_pct\',0)) ;
-- order by 后面支持别名
SELECT *,salary*12*(1+IFNULL(\'commssion_pct\',0)) 年薪 FROM employees ORDER BY 年薪 ;

-- 按照名字字母的长度 排序
-- length() 函数
SELECT * FROM employees ORDER BY LENGTH(last_name);

-- 查询员工信息，先按工资升序排序，再按员工编号降序排序
SELECT * FROM employees ORDER BY salary ASC,  employee_id DESC;

5.小练习

-- 查询员工姓名，部门编号，年薪，按年薪降序，姓名升序
SELECT last_name 姓名,department_id 部门编号,salary*12*IFNULL(commission_pct,0) 年薪 
FROM employees ORDER BY 年薪 DESC,姓名 ASC

-- 选择工资不在8K到17K的员工姓名和工资，按工资降序
SELECT last_name 姓名,salary 工资 FROM employees WHERE salary NOT BETWEEN 8000 AND 17000 ORDER BY 工资 DESC

-- 查询邮件中包含e的员工，并且按邮箱字节降序， 再按部门号升序
SELECT * FROM employees WHERE `email` LIKE \'%e%\' ORDER BY LENGTH(email) DESC,department_id;

6.常见函数

将一组逻辑封装在方法中，对外暴露方法名

　　1.隐藏了实现细节

　　2.提高代码的重用性

　　函数名（实参列表）

分类：

　　1.单行函数：concat 、length 、ifnull等

　　2.分组函数：做统计使用，聚合函数。

6.1 字符函数

-- length 获取参数值的字节个数
SELECT LENGTH(\'maple\');
-- utf-8中文三个字节，gbk中文字符2个字节
SELECT LENGTH(\'麦克雷\'); 

-- concat 拼接字符串
SELECT CONCAT (last_name,\'_\',first_name) 姓名 FROM employees;

-- upper、lower
SELECT UPPER(\'maple\');
SELECT LOWER(\'Maple\');
-- 将姓变大写，名变小写
SELECT CONCAT(UPPER(last_name),\'_\',LOWER(first_name)) FROM employees;

-- substr、substring
-- mysql 索引从1开始,包括索引位置，例如4包括\'在\'
SELECT SUBSTR(\'麦克雷在高台读午时已到\',4);  -- 在高台读午时已到
-- (str,index,len) 指定索引处，截取字符长度
SELECT SUBSTR(\'麦克雷在高台读午时已到\',1,3); -- 麦克雷
-- 案例：姓名中首字母字符大写，其他小写，然后用_拼接
SELECT CONCAT(UPPER(SUBSTR(last_name,1,1)),\'_\',LOWER(SUBSTR(last_name,2))) FROM employees;

-- instr 子串在字符串中的第一次出现索引，如果找不到返回0
SELECT INSTR(\'双眼失明丝毫不影响我追捕敌人\',\'追捕敌人\')；  -- 11

-- trim 
SELECT LENGTH(TRIM(\'    源氏   \')) AS out_put ; -- 6
SELECT TRIM(\'a\' FROM \'aaaaaaaaa天aaa使aaaaaaaa\');  -- 天aaa使

-- lpad 用指定字符实现左填充指定长度
SELECT LPAD(\'全场最佳\',10,\'6\'); -- 666666全场最佳
-- rpad 用指定字符实现左填充指定长度
SELECT RPAD(\'全场最佳\',10,\'6\'); -- 全场最佳666666

-- replace 替换字符
SELECT REPLACE(\'源氏开大杀杀杀杀杀\',\'杀\',\'送\'); -- 源氏开大送送送送送

6.2 数学函数

-- round 四舍五入
SELECT ROUND(1.65); -- 2
SELECT ROUND(1.4);  -- 1
SELECT ROUND(-1.4); -- -1
-- 保留位数
SELECT ROUND(1.567,2);  -- 1.57

-- ceil 向上取整 返回大于等于该参数的最小整数
SELECT CEIL(1.00001); -- 2
SELECT CEIL(-1.00001); -- -1

-- floor 向下取整
SELECT FLOOR(9.99);  -- 9
SELECT FLOOR(-0.15); -- -1

-- truncat 小数点后截断几位
SELECT TRUNCATE(1.6999,1); -- 1.6

-- mod取余
SELECT MOD(10,3); -- 1

6.3 日期函数

-- now 返回当前系统日期+时间
SELECT NOW(); -- 2018-12-07 17:18:57

-- curdate 返回当前系统日期，不包含时间
SELECT CURDATE(); -- 2018-12-07

-- curtime 返回当前时间，不包含日期
SELECT CURTIME(); -- 17:20:10

-- 可以获取指定的部分，年、月、日、时、分、秒
SELECT YEAR(NOW()) 年；        -- 2018
SELECT YEAR(\'1998-10-12\') 年;  -- 1998
SELECT YEAR(hiredate) FROM employees;

SELECT MONTH(NOW()) 月;      -- 12 
SELECT MONTHNAME(NOW()) 月;  -- December

-- str_to_date 将字符通过指定的格式转换成日期
SELECT STR_TO_DATE(\'1992-2-3\',\'%Y-%c-%d\');
SELECT * FROM employees WHERE hiredate = \'1992-4-3\';
-- 但是前台可能传过来的值为 3-4-1992 ,此时需要格式化字符串
SELECT * FROM employees WHERE hiredate = STR_TO_DATE(\'4-3-1992

					
						继续阅读与本文标签相同的文章
						
														无标签