文章目录
前言
本文跟随B站MySQL 基础+高级篇- 数据库 -sql -尚硅谷学习并记录相关笔记。如有需要,小伙伴们可自行移步。
相关脚本
employees.sql,小伙伴们可以自行运行一遍,形成所需数据库。本文所有示例均以该脚本生成数据库为对象。如果需要完整资料,可去上方B站链接置顶评论下载。文件内部代码如下:
/*
SQLyog Ultimate v10.00 Beta1
MySQL - 5.5.15 : Database - myemployees
*********************************************************************
*/
/*!40101 SET NAMES utf8 */;
/*!40101 SET SQL_MODE=''*/;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@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 CHARACTER SET gb2312 */;
USE `myemployees`;
/*Table structure for table `departments` */
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;
/*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),(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);
/*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 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;
/*Data for the table `employees` */
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');
/*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 table `jobs` */
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);
/*Table structure for table `locations` */
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;
/*Data for the table `locations` */
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');
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
一、DQL语言的学习
DQL:Data Query Language(数据查询语句)。
1. 基础查询
1.1 语法
SELECT 查询列表 FROM 表名;
1.2 特点
- 查询列表可以是:表中的字段、常量值、表达式、函数等;
- 查询的结果是一个虚拟的表格。
1.3 分类
1.3.1 查询表中的单个字段
SELECT last_name FROM employees;
1.3.2 查询表中的多个字段
SELECT last_name,salary,email FROM employees;
1.3.3 快捷查询表中的所有字段
1.3.3.1 方式一
SELECT
[双击表单内的所需字段]
/* 可以不按顺序,注意“,”分隔;
两端会自带着重号,如:`last_name`,无影响,可去除;
着重号一般用于区分字段和关键字,如:`name`和NAME。
*/
FROM
empolyees;
关于着重号的特别例子:
SELECT NAME FROM employees; (❌)
SELECT `name` FROM employees; (✅)
1.3.3.2 方式二
SELECT * FROM employees;# 罗列按照原始顺序
1.3.4 查询常量值
SELECT 100;
SELECT 'john';
输出:
100 | john |
---|---|
100 | ‘john’ |
1.3.5 查询表达式
SELECT 100*98;
SELECT 100%98;
输出:
100*98 | 100%98 |
---|---|
9800 | 2 |
1.3.6 函数
SELECT VERSION();
输出:
version() |
---|
5.5.15 |
1.3.7 起别名
- 便于理解;
- 如果要查询的字段幼虫的情况,使用别名可以区分开来。
1.3.7.1 方式一:AS
SELECT last_name AS 姓,first_name AS 名 FROM employees;
输出:内容过多,四行往后省略。
姓 | 名 |
---|---|
K_ing | Steven |
Kochhar | Neena |
De Haan | Lex |
…… | …… |
1.3.7.2 方式二:空格
SELECT last_name 姓,first_name 名 FROM employees;
输出:内容过多,四行往后省略。
姓 | 名 |
---|---|
K_ing | Steven |
Kochhar | Neena |
De Haan | Lex |
…… | …… |
1.3.7.3 特殊案例
查询salary,显示为 out put
SELECT salary AS "out put" FROM employees;
遇到字段中含有关键字,可使用着重号、单引号、双引号进行特殊标识,建议采用双引号。
1.3.8 去重
案例:查询员工表中涉及到的所有的部门编号
SELECT DISTINCT department_id FROM employees;
1.3.9 +号的作用
案例:查询员工名和姓连接成一个字段,并显示为姓名
SELECT
last_name + first_name AS 姓名
FROM
employees;
输出如下:
姓名 |
---|
0 |
0 |
0 |
0 |
0 |
0 |
0 |
…… |
说明:
java中的+
号
- 运算符:两个操作数都为数值型
- 连接符:只要有一个操作数为字符串
mysql中的+
号:
仅仅只有一个功能:运算符
- 两个操作数都为数值型,则做加法运算;
- 其中一个为字符型,试图将字符型数值转换成数值型,
- 如果转换成功,则继续做加法运算;
- 如果转换失败,则将字符型数值转换成0;
- 如果其中出现
null
,则结果必定为null
。
我们测试以下代码:
SELECT 100+90;
SELECT '123'+90;
SELECT 'john'+90;
SELECT null+90;
输出如下:
100+90 | ‘123’+90 | ‘john’+90 | null+90 |
---|---|---|---|
190 | 213 | 90 | null |
那么这里的案例当中,字符段的拼接应该如下操作:
SELECT
CONCAT(last_name,first_name) AS 姓名
FROM
employees;
输出如下:
姓名 |
---|
K_ingSteven |
KochharNeen |
KochharNeena |
De HaanLex |
HunoldAlexander |
ErnstBruce |
…… |
2. 条件查询
2.1 语法
SELECT
要查询的字段|表达式|常量值|函数 # 执行顺序:3
FROM
表名 # 执行顺序:1
WHERE
筛选条件; # 执行顺序:2
2.2 分类
2.2.1 按条件表达式筛选
条件运算符:>
,<
,=
,<>
(不等于),>=
,<=
;
案例2-1:查询工资大于12000的员工信息
SELECT
*
FROM
employees
WHERE
salary > 12000;
案例2-2:查询本门编号不等于90号的员工名和部门编号
SELECT
last_name,
dapartment_id
FROM
employees
WHERE
dapartment_id <> 90;
2.2.2 按逻辑表达式筛选
逻辑运算符:and
,or
,not
(&&
,||
,!
)。
作用:用于连接条件表达式。
2.2.2.1 与
and
或&&
:两个条件都为true
,结果为true
,反之为false
。
案例2-3:查询工资在10000到20000之间的员工名、工资及奖金
SELECT
last_name,
salary,
commission_pot
FROM
employees
WHERE
salary >= 10000 AND salary <= 20000;
2.2.2.2 或
or
或||
:只要有一个条件为true
,结果为true
,反之为false
。
案例2-4:查询本门编号不是在90到110之间,或者工资高于15000的员工信息
SELECT
*
FROM
employees
WHERE
department_id < 90 OR department_id > 110 OR salary > 15000;
2.2.2.3 非
not
或!
:如果连接的条件本身为false
,结果为true
,反之为false
。
还是案例2-4:
SELECT
*
FROM
employees
WHERE
NOT (department_id >= 90 AND department_id <= 110) OR salary>15000;
2.2.3 模糊查询
关键字有:like
,between and
,in
,is null
/is not null
。
2.2.3.1 like
案例2-5:查询员工名中包含a的员工信息
SELECT
*
FROM
employees
WHERE
last_name LIKE '%a%';
案例2-6:查询员工名中第三个字符为e,第五个字符为a的员工信息
SELECT
last_name,
salary
FROM
employees
WHERE
last_name LIKE '__a_e%';
案例2-7:查询员工名中第二个字符为_的员工名
SELECT
last_name
FROM
employees
WHERE
last_name LIKE '_\_%';# '\':转义字符
#----------------------------------------------------
还支持以下方式转义:
--snip-- # 表示之前相同的代码省略(仅为书面表达,实际操作不可)
WHERE
last_name LIKE '_$_%' ESCAPE '$';
特点:一般与通配符搭配
%
:任意多个字符,包含0个字符;_
:任意单个字符。
2.2.3.2 between and
案例2-8:查询员工编号在100到120之间的员工信息
# 之前的写法
SELECT
*
FROM
employees
WHERE
employees_id >= 100 AND employees <= 120;
#--------------------------------------------
# 使用 between and
--snip--
WHERE
employees_id BETWEEN 100 AND 120;
特点:
- 可以提高语句的简洁度;
- 包含临界值;
- 两个临界值不要调换顺序。
2.2.3.3 in
案例2-9:查询员工的工种编号是 IT_PROG、AD_VP、AD_PRES中的一个员工名和工种编号
# 之前的写法
SELECT
last_name,
job_id
FROM
employees
WHERE
job_id = 'IT_PROG' OR job_id = 'AD_VP' OR job_id = 'AD_PRES';
#----------------------------------------------------------------
--snip--
WHERE
job_id in ('IT_PROG', 'AD_VP', 'AD_PRES');
- 含义:判断某字段的值是否属于
in
列表中的某一项; - 特点:
- 提高语句简洁度;
- 列表内的值类型必须一致或兼容(可转换,如:‘123’ 和123);
- 列表内不支持通配符(
in
等价于=
的判断,不同于like
)。
2.2.3.4 is (not) null
案例2-10:查询(没)有奖金的员工名和奖金率
# 没奖金
SELECT
last_name,
commission_pct
FROM
employees
WHERE
commission_pct IS NULL;# 不能写成'commission_pct = NULL'
#--------------------------
# 有奖金
--snip--
WHERE
commission_pct IS NOT NULL;
说明:=
或<>
不能用于判断null
值,is (not) null
可以判断null
值。
强调:is
同样不可进行类似=
的判断,如commission_pct IS 12000
的写法是错误的!
2.2.3.5 安全等于 <=>
还是案例2-10:
# 没奖金
--snip--
WHERE
commission_pct <=> NULL;
案例2-11:查询工资为12000的员工信息
SELECT
*
FROM
employees
WHERE
salary <=> 12000;
is null
VS <=>
:
is null
:仅仅可以判断null
值,可读性较高,建议使用;
<=>
:既可以判断null
值,也可以判断普通的数值,可读性较低。
3. 排序查询
3.1 语法
SELECT
查询列表 # 3
FROM
表 # 1
[WHERE
筛选列表 # 2
ORDER BY
排序列表 [ASC|DESC] # 4
案例3-1:查询员工信息,要求工资从低到高排序
SELECT
*
FROM
employees
ORDER BY
salary ASC;
3.2 特点
ASC
代表升序,DESC
代表降序,如果不写,默认升序;ORDER BY
字句中可以支持单个字段、多个字段、表达式、函数、别名;ORDER BY
一般是放在查询语句的最后面,但LIMIT
子句除外。
3.3 分类
3.3.1 添加筛选条件
案例3-2:查询部门编号>=90的员工信息,按入职时间的先后进行排序
SELECT
*
FROM
employees
WHERE
department_id >= 90
ORDER BY
hiredate ASC;
3.3.2 按表达式排序
案例3-3:按年薪的高低显示员工的信息和年薪
SELECT
*, salary * 12 * (1 + IFNULL(commission_pct,0))
FROM
employees
ORDER BY
salary * 12 * (1 + IFNULL(commission_pct,0)) DESC;
3.3.3 按别名排序
案例3-3:
SELECT
*, salary * 12 * (1 + IFNULL(commission_pct,0)) 年薪
FROM
employees
ORDER BY
年薪 DESC;
3.3.4 按函数排序
案例3-4:按姓名的长度显示员工的姓名和工资
SELECT
LENGTH(last_name) 字节长度, last_name, salary
FROM
employees
ORDER BY
LENGTH(last_name) DESC;
3.3.5 按多个字段排序
案例3-5:查询员工信息,要求先按工资升序排序,再按员工编号降序排序
SELECT
*
FROM
employees
ORDER BY
salary ASC,
employee_id DESC;
4. 常见函数
4.1 概念
类似于java的方法、python中的函数,将一组逻辑语句封装在函数体中,对外暴露函数名。
4.2 优点
- 隐藏了实现细节;
- 提高代码的重(chong)用性。
4.3 调用
SELECT
函数名(实参列表)
[FROM # 需要用到表中的字段则调用表
表];
4.4 使用须知
- 叫什么(函数名);
- 干什么(函数功能)。
4.5 分类
4.5.1 单行函数
如:CONCAT()
、LENTH()
、IFNULL()
等。
4.5.1.1 字符函数
4.5.1.1.1 LENTH(str)
用于获取参数值的字节
SELECT LENTH('john');
SELECT LENTH('张三丰hahaha');
# 结果
4
15 # utf8下,中文汉字占三个字节,英文字母占一个字节。
4.5.1.1.2 CONCAT(str1,str2,…)
拼接字符串。
SELECT
CONCAT(last_name, '_', first_name) 姓名
FROM
employees;
4.5.1.1.3 UPPER(str) / LOWER(str)
大小写转变。
案例4-1:将姓变大写,名变小写,然后拼接。
SELECT
CONCAT(UPPER(last_name), LOWER(first_name)) 姓名
FROM
employees;
# 函数的返回值可以做其他函数的参数。
4.5.1.1.4 SUBSTR / SUBSTRING
截取字符,注意:索引从1开始。
# 特别注意:四种形式
SUBSTR(str,pos)
SUBSTR(str,pos,len)
SUBSTR(str FROM pos)
SUBSTR(str FROM pos FOR len)
SELECT
SUBSTR('人生苦短,我用SQL',6); # 截取从指定索引处后面的所用字符
结果:
我用SQL
SELECT
SUBSTR('李莫愁爱上了陆战元',1,3) # 截取从指定索引处指定字符长度的字符
结果:
李莫愁
案例4-2:姓名中首字符大写,其他字符小写然后用"_"拼接,显示出来
SELECT
CONCAT(UPPER(SUBSTR(last_name,1,1)), '_', LOWER(SUBSTR(last_name,2))) out_put
FROM
employees;
4.5.1.1.5 INSTR(str,subst)
返回子串第一次出现的索引,如果找不到,返回0
。
4.5.1.1.6 TRIM([remstr FROM] str)
去除字符串前后空格,或指定字符。
SELECT TRIM(' awesome ');
结果:
awesome
SELECT TRIM('b' FROM 'bbbbbbbbbbbawebbbsomebbbbbb');
结果:
awebbbsome
4.5.1.1.7 LPAD(str,len,padstr) / RPAD(str,len,padstr)
用指定的字符实现在左(右)填充指定长度,不足则从右截断。
SELECT LPAD('awesome',10,'*');
SELECT LPAD('awesome',5,'*');
SELECT RPAD('awesome',10,'*');
SELECT RPAD('awesome',5,'*');
结果分别:
***awesome
aweso
awesome***
aweso
4.5.1.1.8 REPLACE(str,from_str,to_str)
替换。
SELECT REPLACE('aaabbbccc','b','c');
结果:
aaacccccc
4.5.1.2 数学函数
4.5.1.2.1 ROUND(X[,D])
四舍五入,以及是否指定位数。
SELECT ROUND(1.567);
SELECT ROUND(1.567,2);
结果:
2
1.57
4.5.1.2.2 CEIL(X)
向上取整,返回大于等于该参数的最小整数。
SELECT CEIL(1.002);
结果:
2
4.5.1.2.3 FLOOR(X)
向下取整,返回小于等于该参数的最小整数。
SELECT FLOOR(1.002);
结果:
1
4.5.1.2.4 TRUNCATE(X,D)
截断。即:保留小数点后D
位
SELECT TRUNCATE(1.6999999,1);
结果:
1.6
4.5.1.2.5 MOD(N,M)
取余函数,类似%
算法:MOD(N,M) = N - N/M * M,其中 N/M 自动取整。
SELECT MOD(10,3);
SELECT 10%3;
结果:
1
1
4.5.1.3 日期函数
NOW()
:返回当前系统日期+时间;CURDATE()
:返回当前系统日期,不包含时间;CURTIME()
:返回当前时间,不包含日期;- 获取指定部分:年、月、日、小时、分钟、秒:
YEAR()
:年;MONTH()
:月;(MONTHNAME()
:返回英文月名)DAY()
:日;HOUR()
:小时;MINUTE()
:分钟;SECOND()
:秒。
STR_TO_DATE(str,format)
:将日期格式的字符转化成指定格式的日期;DATE_FORMAT(date,format)
:将日期转换成字符。
序号 | 格式符 | 功能 |
---|---|---|
1 | &Y | 四位的年份 |
2 | &y | 两位的年份 |
3 | &m | 月份(01,02…11,12) |
4 | &c | 月份(1,2…11,12) |
5 | &d | 日(01,02…) |
6 | &H | 小时(24小时制) |
7 | &h | 小时(12小时制) |
8 | &i | 分钟(00,01…59) |
9 | &s | 秒(00,01…59) |
SELECT NOW();
SELECT CURDATE();
SELECT CURTIME();
SELECT YEAR(NOW());
SELECT MONTH('2020-06-05');
# 其余DAY(),HOUR(),MINUTE(),SECOND()类似,以下省略。
------------------------------------------------------
SELECT STR_TO_DATE('9-13-1999','%m-%d-%Y');
结果:
2020-06-05 09:19:51
2020-06-05
09:24:30
2020
6
------------------------------------------------------
1999-09-13
案例4-3:查询有奖金的员工名和入职日期(xx月/xx日 xx年)。
SELECT
last_name,
DATE_FORMAT(hiredate,'%m月/%d日 %y年') 入职日期
FROM
employees
WHERE
commission_pct IS NOT NULL;
4.5.1.4 其他函数
VERSION()
:当前版本号;DATABASE()
:当前数据库;USER()
:当前用户;- 暂且这些,遇到再补充。
4.5.1.5 流程控制函数
4.5.1.5.1 IF(expr1,expr2,expr3)
SELECT
first_name,
commission_pct,
IF
( commission_pct IS NULL, '没奖金,呵呵', '有奖金,嘻嘻' ) 备注
FROM
employees;
4.5.1.5.2 CASE
CASE case_value
WHEN when_value THEN
statement_list
ELSE
statement_list
END CASE;
4.5.2 分组函数
功能:做统计使用,又称为统计函数、聚合函数、组函数。
5. 分组函数
6. 分组查询
7. 连接查询
8. 子查询
9. 分页查询
10. union查询
二、DML语言的学习
DML:Data Manipulation Language(数据操作语言),是对数据库中表记录操作的语言,很多书籍将DQL和DML统称为DML。
1. 插入语句
2. 修改语句
3. 删除语句
三、DDL语言的学习
DDL:Data Definition Language(数据库模式定义语言),是用于描述数据库中要存储的现实世界实体的语言。
1. 库和表的管理
2. 常见数据类型介绍
3. 常见约束
四、TCL语言的学习
TCL:Transaction Control Language(事务控制语言)。