目录：
创建测试数据库和表
执行下面的 SQL 脚本文件，用于后面的 SQL 命令的测试（可以保存到一个 .sql 文件然后运行脚本文件或者直接粘贴运行）。
employees.sql
/*!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*/`employee_db` /*!40100 DEFAULT CHARACTER SET utf8 */;
-- 创建数据库时指定字符集编码
CREATE DATABASE `mydb` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- 使用（切换）数据库
USE employee_db;
-- 创建部门表
DROP TABLE IF EXISTS `departments`;
CREATE TABLE IF NOT EXISTS `departments` (
`department_id` int(4) NOT NULL AUTO_INCREMENT COMMENT '部门id',
`department_name` varchar(20) DEFAULT NULL COMMENT '部门名称',
`manager_id` int(6) DEFAULT NULL COMMENT '部门经理id',
`location_id` int(4) DEFAULT NULL COMMENT '部门所在地id',
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=utf8mb4 COMMENT '部门表';
-- 添加部门表数据
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 IF NOT EXISTS `employees` (
`employee_id` int(6) NOT NULL AUTO_INCREMENT COMMENT '员工id',
`first_name` varchar(20) DEFAULT NULL COMMENT '员工名',
`last_name` varchar(25) DEFAULT NULL COMMENT '员工姓',
`email` varchar(30) DEFAULT NULL COMMENT '邮箱地址',
`phone_number` varchar(20) DEFAULT NULL COMMENT '手机号码',
`job_id` varchar(10) DEFAULT NULL COMMENT '职位id',
`salary` double(10, 2) DEFAULT NULL COMMENT '薪水',
`commission_pct` double(4, 2) DEFAULT NULL COMMENT '奖金率',
`manager_id` int(6) DEFAULT NULL COMMENT '经理id',
`department_id` int(4) DEFAULT NULL COMMENT '部门id',
`hiredate` datetime DEFAULT NULL COMMENT '入职日期',
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=300 DEFAULT CHARSET=utf8mb4 COMMENT '员工表';
-- 添加员工表数据
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 IF NOT EXISTS `jobs` (
`job_id` varchar(10) NOT NULL COMMENT '职位id',
`job_title` varchar(35) DEFAULT NULL COMMENT '职位名称',
`min_salary` int(6) DEFAULT NULL COMMENT '最低工资',
`max_salary` int(6) DEFAULT NULL COMMENT '最高工资',
PRIMARY KEY (`job_id`)
) ENGINE=InnoDB AUTO_INCREMENT=300 DEFAULT CHARSET=utf8mb4 COMMENT '职位表';
-- 添加职位表数据
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 IF NOT EXISTS `locations` (
`location_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '地址id',
`street_address` varchar(40) DEFAULT NULL COMMENT '街道地址',
`postal_code` varchar(10) DEFAULT NULL COMMENT '邮政码',
`city` varchar(30) DEFAULT NULL COMMENT '所在城市',
`state_province` varchar(25) DEFAULT NULL COMMENT '省或州',
`country_id` varchar(10) DEFAULT NULL COMMENT '国家',
PRIMARY KEY (`location_id`)
) ENGINE=InnoDB AUTO_INCREMENT=300 DEFAULT CHARSET=utf8mb4 COMMENT '地址表';
-- 添加地址表数据
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');
-- 创建工资级别表
DROP TABLE IF EXISTS `job_grades`;
CREATE TABLE `job_grades` (
`grade_level` varchar(4) COMMENT '工资级别',
`lowest_sal` int(11) COMMENT '最低工资',
`highest_sal` int(11) COMMENT '最高工资'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '工资级别表';
-- 添加工资级别表数据
INSERT INTO `job_grades`
(`grade_level`, `lowest_sal`, `highest_sal`)
VALUES
('A', 1000, 2999), ('B', 3000, 5999), ('C', 6000, 9999), ('D', 10000, 14999), ('E', 15000, 24999), ('F', 25000, 40000);
/*!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 */;
girls.sql
/*!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*/`girls` /*!40100 DEFAULT CHARACTER SET utf8 */;
-- 切换数据库
USE `girls`;
-- 创建管理员表
DROP TABLE IF EXISTS `admin`;
CREATE TABLE `admin` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(10) NOT NULL,
`password` varchar(10) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
-- 添加管理员表数据
insert into `admin`(`id`,`username`,`password`)
values (1,'john','8888'),(2,'lyt','6666');
-- 创建女神表
DROP TABLE IF EXISTS `beauty`;
CREATE TABLE `beauty` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(50) NOT NULL,
`sex` char(1) DEFAULT '女',
`borndate` datetime DEFAULT '1987-01-01 00:00:00',
`phone` varchar(11) NOT NULL,
`photo` blob,
`boyfriend_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;
-- 添加女神表数据
insert into `beauty`
(`id`,`name`,`sex`,`borndate`,`phone`,`photo`,`boyfriend_id`)
values
(1,'柳岩','女','1988-02-03 00:00:00','18209876577',NULL,8),(2,'刘亦菲','女','1987-12-30 00:00:00','18219876577',NULL,9),(3,'Angelababy','女','1989-02-03 00:00:00','18209876567',NULL,3),(4,'热巴','女','1993-02-03 00:00:00','18209876579',NULL,2),(5,'周冬雨','女','1992-02-03 00:00:00','18209179577',NULL,9),(6,'周芷若','女','1988-02-03 00:00:00','18209876577',NULL,1),(7,'岳灵珊','女','1987-12-30 00:00:00','18219876577',NULL,9),(8,'小昭','女','1989-02-03 00:00:00','18209876567',NULL,1),(9,'双儿','女','1993-02-03 00:00:00','18209876579',NULL,9),(10,'王语嫣','女','1992-02-03 00:00:00','18209179577',NULL,4),(11,'夏雪','女','1993-02-03 00:00:00','18209876579',NULL,9),(12,'赵敏','女','1992-02-03 00:00:00','18209179577',NULL,1);
-- 添加男神表
CREATE TABLE `boys` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`boyName` varchar(20) DEFAULT NULL,
`userCP` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
-- 添加男神数据表
insert into `boys`
(`id`,`boyName`,`userCP`)
values
(1,'张无忌',100),(2,'鹿晗',800),(3,'黄晓明',50),(4,'段誉',300);
/*!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 */;
基础查询
-- 查询表中的单个字段
SELECT `last_name` FROM employees;
-- 查询表中的多个字段
SELECT `last_name`, `salary`, `email` FROM employees;
-- 查询表中的所有字段
SELECT * FROM employees;
-- 查询常量值
SELECT 100;
SELECT 'john';
-- 查询表达式
SELECT 100 % 99;
-- 查询函数
SELECT version();
-- 起别名：1、便于理解；2、如果要查询的字段有重名的情况，使用别名可以区分开来
-- 关键字 AS 可以省略
SELECT `last_name` AS 姓, `first_name` AS 名 FROM employees;
SELECT `email` 邮箱, `phone_number` 手机号码 FROM employees;
SELECT `salary` AS "out put" FROM employees;
-- 查询结果去重
SELECT DISTINCT `department_id` FROM employees;
-- 字符串拼接符 `+`：MySQL 中的 `+` 号只有一个功能：运算符
SELECT 100 + 90; # 两个操作数都为数值型，则做加法运算
SELECT '123' + 20; # 如果其中一个操作数为字符型，则将字符串转换成数值型
SELECT 'john' + 90; # 如果字符串无法转为数值，则默认转换为 0
SELECT NULL + 10; # 只要其中一方为 null，则结果一定我 null
-- 字符串拼接函数
SELECT concat('a', 'b', 'c') as result;
SELECT concat(`last_name`, ' ', `first_name`) as 姓名 FROM employees;
条件查询
-- 条件运算符：> < = != <> >= <=
-- 逻辑运算符：&& 或 and、|| 或 or、! 或 not
-- 模糊查询：like、between and、in、is null、is not null
-- 通配符：'%' 匹配0个或多个字符; '_' 匹配任意单个字符；如果要匹配通配符本身，可以使用 ESCAPE 指定转义字符
-- between and 是包含临界值的，并且两个临界值不可调换顺序
-- 查询工资在10000到20000之间的员工名和工资
SELECT `last_name`, `salary` FROM employees
WHERE `salary` >= 10000 and `salary` <= 20000;
-- 查询部门内编号不在90到100之间，或者工资高于15000的员工信息
SELECT * FROM employees
WHERE NOT(`department_id`>=90 and `department_id`<=100) OR `salary`>15000;
-- 查询员工名中包含字符a的员工信息
SELECT * FROM employees WHERE `last_name` LIKE '%a%';
-- 查询员工名中第三个字符为 n，第五个字符为 l 的员工名
SELECT `last_name` FROM employees WHERE `last_name` LIKE '__n_l%';
-- 查询员工名中第二个字符为 '_' 的员工名
-- 指定字符 '$' 为转义字符，此时转义字符后面紧跟的字符会转变为普通字符
SELECT `last_name` FROM employees WHERE `last_name` LIKE '_$_%' ESCAPE '$';
-- 查询员工编号在100到120之间的员工信息
SELECT * FROM employees WHERE `employee_id` BETWEEN 100 and 120;
-- 查询员工的工种编号是 IT_PROG、AD_VP、AD_PRES 中的一个员工名和工种编号
SELECT `last_name`, `job_id` FROM employees
WHERE `job_id` IN ('IT_PROG', 'AD_VP', 'AD_PRES');
-- = 或 <> 不能用于判断 null 值
-- is null 或 is not null 可以判断 null 值
-- is 不能用于普通数值判断
-- 查询没有奖金的员工名
SELECT `last_name` FROM employees WHERE `commission_pct` IS NULL;
-- 查询有奖金的员工名
SELECT `last_name` FROM employees WHERE `commission_pct` IS NOT NULL;
-- 下面是错误的 is 用法
SELECT last_name FROM employees WHERE salary IS 12000;
-- 安全等于：<=>
-- <=> 既可以判断 null 值也可以判断普通的数值，但可读性较低
-- is null 虽然只能用于null值判断，但可读性较高，建议使用
-- 查询没有奖金的员工名
SELECT last_name FROM employees WHERE commission_pct <=> NULL;
-- 查询工资为 12000 的员工信息
SELECT * FROM employees WHERE salary <=> 12000;
常见函数
-- 函数分为单行函数和分组函数
-- 单行函数包括：字符函数、数学函数、日期函数、控制函数、其他函数等
-- 分组函数的功能是做统计使用，所以又称为统计函数、聚合函数、组函数
-- ------------------------- 字符函数 -------------------------
-- length 获取参数值的字节个数，一个中文占三个字节
SELECT length('john');
SELECT length('王廷云');
-- 查看字符编码
SHOW VARIABLES LIKE '%char%';
-- concat 拼接字符串
SELECT concat(last_name, ' ', first_name) AS fullname FROM employees;
-- upper、lower 大小写字母转换
SELECT upper('john');
SELECT lower('JACK');
SELECT concat(lower(last_name), ' ', lower(first_name)) AS fullname FROM employees;
-- substr、substring 从字符串中截取指定的字符串
-- 字符串索引下表从 1 开始
SELECT substr('1234567890', 7) AS output;
SELECT substr('1234567890', 3, 9) AS output;
SELECT concat(upper(substr(last_name, 1, 1)), '-', lower(substr(last_name, 2))) AS output FROM employees;
-- instr 返回子串在字符串中第一次出现的索引，如果找不到则返回0
SELECT instr('123456789', '345') AS output;
-- trim 从字符串的两边移除指定的字符或字符串，默认为空格
-- ltrim 移除字符串左边的空格；rtrim 移除字符串右边的空格
SELECT trim(' 123 ') AS output;
SELECT ltrim(' 123') AS output;
SELECT rtrim(' 123 ') AS output;
SELECT trim('aa' FROM 'aaaaa王aaaaa廷云aaaaaa') AS output;
-- lpad 使用指定的字符串在左边填充空白位置
-- rpad 使用指定的字符串在右边填充空白位置
SELECT lpad('王廷云', 10, '-') AS output;
SELECT rpad('王廷云', 10, 'ab') AS output;
-- replace 字符串替换
SELECT replace('222yy333yy555', 'yy', '--') AS output;
-- ------------------------- 数学函数 -------------------------
-- round 四舍五入，可以指定精度
SELECT round(-1.55);
SELECT round(1.567, 2);
-- ceil 向上取整，返回 >= 该参数的最小整数
SELECT ceil(-1.02);
-- floor 向下取整，返回 <= 该参数的最大整数
SELECT floor(-9.99);
-- truncate 截断，可以指定精度
SELECT truncate(1.6999, 2);
-- mod 取余，算法为 mod(a, b) : a - a/b * b
SELECT mod(10, -3);
SELECT 10 / 3;
-- ------------------------- 日期函数 -------------------------
-- now 返回当前系统的 日期+时间
SELECT now();
-- curdate 返回当前系统的日期，不包含时间
SELECT curdate();
-- curtime 返回当前系统的时间，不包含日期
SELECT curtime();
-- 可以获取日期和时间中指定的部分：年、月、日、小时、分钟、秒
SELECT year(now()) AS 年;
SELECT year('1995-08-30') AS 年;
SELECT year(hiredate) AS 年 FROM employees;
SELECT month(now()) AS 月;
SELECT monthname(now()) AS 月;
SELECT date(now()) AS 日;
SELECT hour(now()) AS 小时;
SELECT minute(now()) AS 分钟;
SELECT second(now()) AS 秒;
-- str_to_data 将字符串通过指定格式转换为日期
SELECT str_to_date('1995-8-30', '%Y-%m-%d') AS output;
-- 查询入职日期为 1992-4-3 的员工信息
SELECT * FROM employees WHERE hiredate='1992-4-3';
SELECT * FROM employees WHERE hiredate=str_to_date('4-3 1992', '%m-%d %Y');
-- date_format 将日期转换成字符串
SELECT date_format(now(), '%Y年/%m月/%d日') AS output;
-- 查询有奖金的员工名和入职日期（xx月/xx日 xx年）
SELECT last_name, date_format(hiredate, '%m月/%d日 /%Y年') AS 入职日期
FROM employees WHERE commission_pct IS NOT NULL;
-- ------------------------- 流控函数 -------------------------
-- if 函数：相当于 if else 的效果
SELECT if(10 > 6, '大', '小');
SELECT if(commission_pct IS NULL, '没有奖金', '有奖金') AS 备注 FROM employees;
-- case 函数：相当于 switch case 的效果
-- case 要判断的字段或表达式
-- when 常量1 then 要显示的值1或语句1
-- when 常量2 then 要显示的值2或语句2
-- else 要显示的值n或语句n
-- end
-- 案例：查询员工的工资，要求如下
-- 部门号=30，显示的工资为1.1倍
-- 部门号=40，显示的工资为1.2倍
-- 部门号=50，显示的工资为1.3倍
-- 其他部门，显示原来的工资
SELECT department_id, salary AS 原来的工资,
CASE department_id
WHEN 30 THEN salary*1.1
WHEN 40 THEN salary*1.2
WHEN 50 THEN salary*1.3
ELSE salary
END AS 新工资
FROM employees;
-- 如果把 case 的判断条件放在 when 中，相当于 if {} else if {} else {}
-- 案例：查询员工的工资情况
SELECT salary,
CASE
WHEN salary>20000 THEN 'A'
WHEN salary>15000 THEN 'B'
WHEN salary>10000 THEN 'C'
ELSE 'D'
END AS 工资级别
FROM employees;
-- ------------------------- 分组函数 -------------------------
-- 分类：sum 求和、avg 平均值、max 最大值 、min 最小值 、count 计算个数
-- 特点：
-- 1、sum、avg 一般用于处理数值型，max、min、count可以处理任何类型；
-- 2、以上分组函数都忽略 null 值；
-- 3、可以和distinct搭配实现去重的运算；
-- 4、一般使用count(*)用作统计行数；
-- 5、和分组函数一同查询的字段要求是 group by 分组之后的字段
-- 简单使用
SELECT sum(salary) FROM employees;
SELECT avg(salary) FROM employees;
SELECT min(salary) FROM employees;
SELECT max(salary) FROM employees;
SELECT count(salary) FROM employees;
SELECT sum(salary) 总和, round(avg(salary), 2) 平均值, max(salary) 最高值, min(salary) 最低值, count(salary) 个数
FROM employees;
SELECT count(*) FROM employees;
-- 和 distinct 搭配使用
SELECT sum(DISTINCT salary), sum(salary) FROM employees;
SELECT count(DISTINCT salary), count(salary) FROM employees;
-- count(*)、count(1)、count(字段) 的区别
-- 1、count(字段) 会忽略该字段值为 null 的记录，count(*) 和 count(1) 不会
-- 2、在 MYISAM 存储引擎下 ，COUNT(*)的效率高
-- 3、在 INNODB 存储引擎下，COUNT(*)和COUNT(1)的效率差不多，比COUNT(字段)要高一些
SELECT count(salary) FROM employees;
SELECT count(*) FROM employees;
SELECT count(1) FROM employees;
-- ------------------------- 其他函数 -------------------------
-- 当前 mysql 版本
SELECT version();
-- 当前使用的数据库
SELECT database();
-- 当前用户
SELECT user();
排序查询
-- 1、asc 代表升序，默认排序行为，可以省略；desc 代表降序
-- 2、order by 子句可以支持单个字段、多个字段、别名、表达式、函数
-- 3、order by 子句放在查询语句的最后面，除了 limit 子句
-- 单个字段排序
SELECT * FROM employees ORDER BY salary DESC;
-- 添加筛选条件在排序
-- 案例：查询部门编号>=90的员工信息，并按员工编号降序
SELECT * FROM employees WHERE department_id>=90 ORDER BY employee_id DESC;
-- 按表达式排序
-- 案例：查询员工信息，按年薪降序
SELECT *, salary*12*(1+ifnull(commission_pct, 0)) AS 年薪
FROM employees
ORDER BY salary*12*(1+ifnull(commission_pct, 0)) DESC;
-- 按表别名排序
-- 案例：查询员工信息，按年薪升序
SELECT *, salary*12*(1+ifnull(commission_pct, 0)) AS 年薪
FROM employees
ORDER BY 年薪 ASC;
-- 按函数排序
-- 案例：查询员工名，并按名字的长度降序
SELECT last_name, length(last_name)
FROM employees
ORDER BY length(last_name) DESC;
-- 多个字段排序
-- 案例：查询员工信息，要求先按工资降序，再按employee_id升序
SELECT * FROM employees
ORDER BY salary DESC, employee_id ASC;
分组查询
-- 使用 group by 子句进行分组
-- 和分组函数一同查询的字段必须是 group by 后出现的字段
-- 筛选分为两类：分组前筛选 where 和分组后筛选 having ： where -- group by -- having
-- 一般来讲，能用分组前筛选的，尽量使用分组前筛选，提高效率
-- 分组可以单个字段也可以按多个字段，可以搭配着排序使用
-- 查询部门id为30的员工个数
SELECT count(*)
FROM employees
WHERE department_id=30;
-- 查询每个部门的员工个数
SELECT department_id, count(*)
FROM employees
GROUP BY department_id;
-- 查询每个工种的员工平均工资
SELECT job_id, avg(salary)
FROM employees
GROUP BY job_id;
-- 查询每个位置的部门个数
SELECT location_id, count(*)
FROM departments
GROUP BY location_id;
-- 分组前进行筛选
-- 查询邮箱中包含a字符的每个部门的最高工资
SELECT department_id, max(salary)
FROM employees
WHERE email LIKE '%a%'
GROUP BY department_id;
-- 查询有奖金的每个领导手下员工的平均工资
SELECT manager_id, avg(salary)
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY manager_id;
-- 分组后进行筛选
-- 查询员工个数 >5 的部门
SELECT department_id, count(*)
FROM employees
GROUP BY department_id
HAVING count(*)>5;
-- 查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资
SELECT job_id, max(salary)
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY job_id
HAVING max(salary)>12000;
-- 查询领导编号>102的每个领导手下的最低工资大于5000的领导编号和最低工资
SELECT manager_id, min(salary)
FROM employees
WHERE manager_id>102
GROUP BY manager_id
HAVING min(salary)>5000;
-- 添加排序，排序条件放在分组和分组过滤后面
-- 查询每个工种中有奖金的员工的最高工资>6000的工种编号和最高工资，并按最高工资升序
SELECT job_id, max(salary)
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY job_id
HAVING max(salary)>6000
ORDER BY max(salary) ASC;
-- 多个字段分组
-- 查询每个工种每个部门的最低工资，并按最低工资降序
SELECT `job_id`, department_id, min(salary)
FROM employees
GROUP BY job_id, department_id
ORDER BY min(salary) DESC;
多表查询
-- 多表查询又称连接查询，当查询的字段来自多个表时，就需要用到多表查询
-- 笛卡尔乘积现象：表1有m行，表2有n行，结果=m*n行
-- 发生原因：没有有效的连接条件；解决方法：添加有效的连接条件
-- 按功能分类：
-- 内连接：等值连接、非等值连接、自连接
-- 外连接：左外连接、右外链接、全外连接
-- 交叉连接
-- 按标准分类
-- sql92标准:仅仅支持内连接
-- sql99标准【推荐】：支持内连接+外连接（左外和右外）+交叉连接
-- ------------------------- sql92标准 -------------------------
-- 1、等值连接
-- 查询员工名和对应的部门名
SELECT last_name, department_name
FROM employees, departments
WHERE employees.department_id=departments.department_id;
-- 为表起别名：提高语句的简洁度，区分多个重名的字段
-- 如果为表起了别名，则查询的字段就不能使用原来的表名去限定
-- 案例：查询员工名、工种号、工种名
SELECT e.last_name, j.job_id, j.job_title
FROM employees e, jobs j
WHERE e.job_id=j.job_id;
-- 表的顺序可以调换
SELECT e.last_name, j.job_id, j.job_title
FROM jobs j, employees e
WHERE e.job_id=j.job_id;
-- 可以添加筛选条件
-- 查询有奖金的员工名、部门名
SELECT e.last_name, d.department_name, e.commission_pct
FROM employees e, departments d
WHERE e.department_id=d.department_id AND e.commission_pct IS NOT NULL;
-- 查询城市名中第二个字符为 o 的部门名和城市名
SELECT d.department_name, l.city
FROM departments d, locations l
WHERE d.location_id=l.location_id AND l.city LIKE '_o%';
-- 可以添加分组
-- 查询每个城市的部门个数
SELECT l.city, count(*)
FROM departments d, locations l
WHERE d.location_id=l.location_id
GROUP BY l.city;
-- 查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资
SELECT d.department_name, d.manager_id, min(e.salary)
FROM departments d, employees e
WHERE d.department_id=e.department_id AND e.commission_pct IS NOT NULL
GROUP BY d.department_name, d.manager_id;
-- 可以添加排序
-- 查询每个工种的工种名和员工个数，并按员工个数降序
SELECT j.job_title, count(*)
FROM jobs j, employees e
WHERE j.job_id=e.job_id
GROUP BY j.job_title
ORDER BY count(*) DESC;
-- 三表连接查询
-- 查询员工名、部门名和所在的城市
SELECT e.last_name, d.department_name, l.city
FROM employees e, departments d, locations l
WHERE e.department_id=d.department_id AND d.location_id=l.location_id;
-- 2、非等值连接
-- 查询员工的工资和工资级别
SELECT e.salary, g.grade_level
FROM employees e, job_grades g
WHERE e.salary BETWEEN g.lowest_sal AND g.highest_sal;
-- 3、自连接
-- 查询员工名和上级的名称（员工的上级也是一个员工）
SELECT e.employee_id, e.manager_id, e.last_name, m.employee_id, m.last_name
FROM employees e, employees m
WHERE e.manager_id=m.employee_id;
-- ------------------------- sql99标准 -------------------------
-- 内连接：inner join，inner 可以省略，效果和 sql92 的等值连接一样
-- 连接条件使用 on 关键字，而 where 尽作为筛选条件放在 on 之后
-- 1、等值连接
-- 查询员工名、部门名
SELECT e.last_name, d.department_name
FROM employees e
INNER JOIN departments d
ON e.department_id = d.department_id;
-- 查询名字中包含 e 的员工名和工种名（添加筛选条件）
SELECT e.last_name, j.job_title
FROM employees e
INNER JOIN jobs j
ON e.job_id = j.job_id
WHERE e.last_name LIKE '%e%';
-- 查询哪个部门的员工个数>3的部门名和员工个数，并按个数降序（添加分组和排序）
SELECT d.department_name, count(*)
FROM employees e
INNER JOIN departments d
ON e.department_id = d.department_id
GROUP BY d.department_name
HAVING count(*) > 3
ORDER BY count(*) DESC;
-- 查询员工名、部门名、工种名，并按部门名降序（三表连接）
SELECT e.last_name, d.department_name, j.job_title
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id
INNER JOIN jobs j ON e.job_id = j.job_id
ORDER BY d.department_name DESC;
-- 2、非等值连接
-- 查询员工的工资级别
SELECT e.last_name, e.salary, g.grade_level
FROM employees e
INNER JOIN job_grades g
ON e.salary BETWEEN g.lowest_sal AND g.highest_sal;
-- 查询工资级别个数>20的级别及其个数，并按工资级别降序
SELECT g.grade_level, count(*)
FROM employees e
INNER JOIN job_grades g
ON e.salary BETWEEN g.lowest_sal AND g.highest_sal
GROUP BY g.grade_level
HAVING count(*) > 20
ORDER BY g.grade_level DESC;
-- 3、自连接
-- 查询员工的名字、上级的名字
SELECT e.last_name, m.last_name
FROM employees e
INNER JOIN employees m
ON e.manager_id = m.employee_id;
-- 外连接：用于查询一个表中有，另一个表没有的记录
-- 特点：
-- 1、外连接的查询结果为主表中的所有记录。
-- 如果从表中有和它匹配的，则显示匹配的值；如果没有则显示 null
-- 外连接的查询结果=内连接结果+主表中有而从表中没有的记录
-- 2、左外连接 left join 中左边是主表；右外连接 right join 中右边是主表
-- 3、左外和右外交换两个表的顺序，可以实现同样的效果
-- 4、全外连接 = 内连接的结果 + 表1中有但表2没有的 + 表2中有但表1没有的
-- 查询哪个部门没有员工
-- 使用左外连接
SELECT d.*, e.employee_id
FROM departments d
LEFT OUTER JOIN employees e
ON d.department_id = e.department_id
WHERE e.employee_id IS NULL;
-- 使用右外连接
SELECT d.*, e.employee_id
FROM employees e
RIGHT OUTER JOIN departments d
ON e.department_id = d.department_id
WHERE e.employee_id IS NULL;
-- 查询哪个城市没有部门
SELECT l.city
FROM locations l
LEFT OUTER JOIN departments d
ON d.location_id = l.location_id
WHERE d.department_id IS NULL;
-- 交叉连接
SELECT e.*, d.*
FROM employees e
CROSS JOIN departments d;
分页查询
-- 应用场景：当要显示的数据，一页显示不全，需要分页提交sql请求
-- 语法：limit [offset,] size; offset 为起始索引（默认从0开始），size 为记录条数
-- limit 语句放在查询语句的最后
-- 查询公式为：要显示的页数 page，每页的条数 size => limit (page-1)*size, size;
-- 查询前五条员工信息
SELECT * FROM employees LIMIT 0, 5;
SELECT * FROM employees LIMIT 5;
-- 查询第 11 - 25 条员工信息
SELECT * FROM employees LIMIT 10, 15;
-- 查询有奖金的员工信息，并显示工资最高的前10条记录
SELECT * FROM employees
WHERE commission_pct IS NOT NULL
ORDER BY salary DESC
LIMIT 10;
子查询
/**
含义：
出现在其他语句的 select 语句，称为子查询或内查询；外部的查询语句称为主查询或外查询。
子查询的执行优先于主查询，因为主查询的条件用到了子查询的结果
分类：
1、按结果集的行列数不同分为：
* 标量子查询：结果集只有一行一列，一般搭配单行操作符使用 > < >= <= = <>
* 列子查询：结果集为一列多行，即该字段有多条记录值；一般搭配多行操作符使用 in、any/some、all
* 行子查询：结果集为一行多列，即完整的一条记录
* 表子查询：结果集为多行多列
2、按子查询出现的位置分为：
* select 后面：仅仅支持标量子查询
* from 后面：支持表子查询
* where 或 having 后面：标量子查询、列子查询、行子查询
* exists 后面（相关子查询）：表子查询
**/
-- ------------------------- where 或 having 后面 -------------------------
-- 1、标量子查询
-- 案例：查询工资比 Abel 高的员工信息
# ①查询 Abel 的工资
SELECT salary
FROM employees
WHERE last_name = 'Abel';
# ②查询员工信息，满足 salary > ① 的结果
SELECT * FROM employees
WHERE salary > (
SELECT salary
FROM employees
WHERE last_name = 'Abel'
);
-- 案例：查询公司工资最少的员工的last_name,job_id和salary
# ①查询公司的最低工资
SELECT min(salary)
FROM employees;
# ②查询last_name,job_id和salary，要求salary=①
SELECT last_name, job_id, salary
FROM employees
WHERE salary = (
SELECT min(salary)
FROM employees
);
-- 案例：查询 job_id 与 141 号员工相同，salary 比 143 号员工多的员工姓名、job_id 和工资
# ①查询 141 号员工的 job_id
SELECT job_id
FROM employees
WHERE employee_id = 141;
# ②查询 143 号员工的 salary
SELECT salary
FROM employees
WHERE employee_id = 143;
# ③查询员工的姓名，job_id 和工资，要求job_id=①并且salary>②
SELECT last_name, job_id, salary
FROM employees
WHERE job_id = (
SELECT job_id
FROM employees
WHERE employee_id = 141
) AND salary > (
SELECT salary
FROM employees
WHERE employee_id = 143
);
-- 案例：查询最低工资大于50号部门最低工资的部门id及其最低工资
# ① 查询50号部门的最低工资
SELECT min(salary)
FROM employees
WHERE department_id = 50;
# ② 查询每个部门的最低工资
SELECT department_id, min(salary)
FROM employees
GROUP BY department_id;
# ③ 在②基础上筛选，满足min(salary)>①
SELECT department_id, min(salary)
FROM employees
GROUP BY department_id
HAVING min(salary) > (
SELECT min(salary)
FROM employees
WHERE department_id = 50
);
-- 非法使用标量子查询（结果不唯一）
SELECT department_id, min(salary)
FROM employees
GROUP BY department_id
HAVING min(salary) > (
SELECT salary
FROM employees
WHERE department_id = 50
);
-- 2、列子查询（多行子查询）
-- 案例：查询location_id是1400或1700的部门中的所有员工姓名
# ①查询location_id是1400或1700的部门编号
SELECT department_id
FROM departments
WHERE location_id IN (1400, 1700)
ORDER BY department_id;
# ②查询员工姓名，要求部门编号为①列表中的一个
SELECT last_name, department_id
FROM employees
WHERE department_id IN (
SELECT department_id
FROM departments
WHERE location_id IN (1400, 1700)
)
ORDER BY department_id;
-- 案例：返回其它工种中比'IT_PROG'工种中任意一个工资都低的员工的员工号、姓名、job_id 以及 salary
# ①查询 job_id 为 'IT_PROG' 的工资
SELECT salary
FROM employees
WHERE job_id = 'IT_PROG';
# ②查询员工号、姓名、job_id 以及 salary，要求 salary < ①的任意一个
SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary < ANY (
SELECT salary
FROM employees
WHERE job_id = 'IT_PROG'
) AND job_id <> 'IT_PROG';
# 或者
SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary < (
SELECT max(salary)
FROM employees
WHERE job_id = 'IT_PROG'
) AND job_id <> 'IT_PROG';
-- 案例：返回其它部门内比job_id为'IT_PROG'的部门内所有工资都低的员工的员工号、姓名、job_id 以及 salary
SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary < ALL (
SELECT DISTINCT salary
FROM employees
WHERE job_id = 'IT_PROG'
) AND job_id <> 'IT_PROG';
# 或者
SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary < (
SELECT min(salary)
FROM employees
WHERE job_id = 'IT_PROG'
) AND job_id <> 'IT_PROG';
-- 3、行子查询（一行多列）
-- 案例：查询员工编号最小并且工资最高的员工信息
SELECT *
FROM employees
WHERE (employee_id, salary)=(
SELECT MIN(employee_id),MAX(salary)
FROM employees
);
# ①查询最小的员工编号
SELECT min(employee_id)
FROM employees;
# ②查询最高工资
SELECT max(salary)
FROM employees;
# ③查询员工信息
SELECT * FROM employees
WHERE employee_id = (
SELECT min(employee_id)
FROM employees
) AND (
SELECT max(salary)
FROM employees
);
-- 经典案例：查询平均工资最低的部门信息
-- 方式一：
# ① 查询部门的平均工资
SELECT department_id, avg(salary)
FROM employees
GROUP BY department_id;
# ② 查询①结果中的最低平均工资
SELECT min(ag)
FROM (
SELECT department_id, avg(salary) ag
FROM employees
GROUP BY department_id
) ag_dep;
# ③ 查询最低工资的部门编号
SELECT department_id
FROM employees
GROUP BY department_id
HAVING avg(salary) = (
SELECT min(ag)
FROM (
SELECT department_id, avg(salary) ag
FROM employees
GROUP BY department_id
) ag_dep
);
# ④ 查询部门信息
SELECT d.*
FROM departments d
WHERE department_id = (
SELECT department_id
FROM employees
GROUP BY department_id
HAVING avg(salary) = (
SELECT min(ag)
FROM (
SELECT department_id, avg(salary) ag
FROM employees
GROUP BY department_id
) ag_dep
)
);
-- 方式二：
# ① 查询最低平均工资的部门编号
SELECT department_id
FROM employees
GROUP BY department_id
ORDER BY avg(salary) ASC
LIMIT 1;
# ② 查询部门信息
SELECT * FROM departments
WHERE department_id = (
SELECT department_id
FROM employees
GROUP BY department_id
ORDER BY avg(salary) ASC
LIMIT 1
);
-- ------------------------- select 后面 -------------------------
-- 仅仅支持标量子查询
-- 案例：查询每个部门的员工个数
SELECT d.*, (
SELECT count(*)
FROM employees e
WHERE e.department_id = d.department_id
) 个数
FROM departments d;
-- ------------------------- from 后面 -------------------------
-- 将子查询结果充当一张表，要求必须起别名
-- 案例：查询每个部门的平均工资的工资等级
# ① 查询每个部门的平均工资
SELECT department_id, avg(salary) ag
FROM employees
GROUP BY department_id;
# ② 连接①的结果集进行多表查询
SELECT ag_dep.*, g.grade_level
FROM (
SELECT department_id, avg(salary) ag
FROM employees
GROUP BY department_id
) ag_dep
INNER JOIN job_grades g
ON ag_dep.ag BETWEEN g.lowest_sal AND g.highest_sal;
-- ------------------------- exists 后面 -------------------------
-- 语法：exists(完整的查询语句)，结果返回1或0
-- 查询是否存在salary=24000的员工
SELECT exists(SELECT employee_id FROM employees WHERE salary=24000);
-- 查询有员工的部门名
SELECT department_name
FROM departments d
WHERE exists(
SELECT *
FROM employees e
WHERE e.department_id = d.department_id
);
联合查询
/**
union 联合、合并：将多条查询语句的结果合并成一个结果
语法：
查询语句1
union
查询语句2
union
...
应用场景：
要查询的结果来自于多个表，且多个表没有直接的连接关系，但查询的信息一致时
特点：
1、要求多条查询语句的查询列数是一致的！
2、要求多条查询语句的查询的每一列的类型和顺序最好一致
3、union 关键字默认去重，如果使用 union all 可以包含重复项
**/
-- 案例：查询部门编号>90或邮箱包含a的员工信息
SELECT * FROM employees
WHERE email LIKE '%a%' OR department_id>90;
SELECT * FROM employees WHERE email LIKE '%a%'
UNION
SELECT * FROM employees WHERE department_id>90;
库和表的管理
-- 查看所有数据库
SHOW DATABASES;
-- 创建数据库
CREATE DATABASE IF NOT EXISTS books;
-- 修改数据库字符集
ALTER DATABASE books CHARACTER SET gbk;
-- 删除数据库
DROP DATABASE IF EXISTS books;
-- 选择数据库
USE books;
-- 1、表的创建
-- 查看所有数据表
SHOW TABLES;
-- 创建表 book
CREATE TABLE book (
`id` int(11) COMMENT '编号',
`bName` varchar(20) COMMENT '图书名',
`price` double(10, 2) COMMENT '价格',
`authorId` int(11) COMMENT '作者编号',
`publishDate` datetime COMMENT '出版日期'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '图书表';
-- 查看表结构
DESC book;
DESCRIBE book;
-- 创建表 author
CREATE TABLE IF NOT EXISTS author(
`id` int(11) COMMENT '编号',
`auName` varchar(20) COMMENT '作者姓名',
`nation` varchar(20) COMMENT '国家'
);
DESC author;
-- 2、表的修改：语法 alter table 表名 add|drop|modify|change column 列名【列类型 约束】;
-- 修改列名
ALTER TABLE book CHANGE COLUMN publishDate pubDate DATETIME;
-- 修改列的类型或约束
ALTER TABLE book MODIFY COLUMN pubDate timestamp;
-- 移动列的位置（类型也要一起写上）
ALTER TABLE book MODIFY COLUMN bName VARCHAR(20) COMMENT '图书名' AFTER price;
-- 添加新列
ALTER TABLE author ADD COLUMN annual DOUBLE(10, 2);
-- 在指定的列后面添加新列
ALTER TABLE author ADD COLUMN enName DOUBLE(10, 2) COMMENT '英文名' AFTER auName;
-- 删除列
ALTER TABLE author DROP COLUMN annual;
-- 修改表名
ALTER TABLE author RENAME TO book_author;
-- 修改表的注释
ALTER TABLE author COMMENT '作者表';
-- 3、表的删除
-- 删除整个表，包括表数据和结构
DROP TABLE IF EXISTS book_author;
-- 4、表的复制
INSERT INTO
author
VALUES
(1,'村上春树','日本'), (2,'莫言','中国'), (3,'冯唐','中国'), (4,'金庸','中国');
-- 仅仅复制表的结构
CREATE TABLE copy LIKE author;
-- 复制表的结构和数据
CREATE TABLE copy2 SELECT * FROM author;
-- 复制部分结果和数据
CREATE TABLE copy3 SELECT id, auName FROM author WHERE nation='中国';
-- 仅复制某些字段
CREATE TABLE copy4 SELECT id, auName FROM author WHERE 0;
-- 复制表的数据（两个表的结构一样）
INSERT INTO table_1 SELECT * FROM table_2;
-- 复制表的数据（两个表的结构不一样）
INSERT INTO table_1(field_1, field_2, ...) SELECT field_1, field_2, ... FROM table_2;
数据的增删改
-- ------------- 1、插入数据 -------------
-- 插入的值的类型要与列的类型一致或兼容
INSERT INTO beauty(`id`, `name`, `sex`, `borndate`, `phone`, `photo`, `boyfriend_id`)
VALUES (13,'唐艺昕','女','1990-4-23','1898888888',NULL,2);
-- 默认值可以为 null 的列可以省略
INSERT INTO beauty(`id`, `name`, `sex`, `phone`)
VALUES(15,'娜扎','女','1388888888');
-- 列的顺序可以调换，只要值保持一一对应即可
INSERT INTO beauty(`name`, `sex`, `id`, `phone`)
VALUES('蒋欣','女',16,'110');
-- 列的个数和值的个数必须保持一致
INSERT INTO beauty(`name`, `sex`, `id`, `phone`)
VALUES('关晓彤','女',17,'110');
-- 可以省略列名，默认为所有列，而且列的顺序和表中列的顺序一致
INSERT INTO beauty
VALUES (18,'雪儿','女','1995-10-23','1898888888',NULL,2);
-- 使用子查询插入
INSERT INTO beauty(`name`, `phone`)
SELECT `name`, `phone` FROM beauty WHERE id<3;
-- 插入值的方式还有另外一种
-- 但这种不能插入多条，并且也不支持子查询
INSERT INTO beauty
SET id=19, name='刘涛', phone='13454464424';
-- ------------- 2、修改语句 -------------
-- 修改单表记录
UPDATE beauty SET phone = '13899888899'
WHERE name LIKE '唐%';
-- 修改多表记录
UPDATE boys bo
INNER JOIN beauty bu ON bo.id = bu.boyfriend_id
SET bu.phone='119', bo.userCP=1000
WHERE bo.boyName='张无忌';
-- ------------- 3、删除语句 -------------
-- delete 语句
-- 单表的删除
DELETE FROM beauty WHERE phone LIKE '%9';
-- 多表的删除
DELETE bu
FROM beauty bu
INNER JOIN boys bo ON bo.id=bu.boyfriend_id
WHERE bo.boyName='张无忌';
-- truncate 语句
-- 删除表中的所有数据
TRUNCATE TABLE boys;
-- delete 和 truncate 的区别：
-- 1、delete 可以加 where 条件，truncate 不能加
-- 2、truncate 删除，效率比 delete 高一点
-- 3、假如要删除的表中有自增长列：
-- 如果用delete删除后，再插入数据，自增长列的值从断点开始；
-- 而truncate删除后，再插入数据，自增长列的值从1开始。
-- 4、truncate删除没有返回值，delete删除有返回值
-- 5、truncate删除不能回滚，delete删除可以回滚
事务
/**
TCL: Transaction Control Language 事务控制语言
事务：
一个或一组sql语句组成一个执行单元，这个执行单元要么全部执行，要么全部不执行。
事务的特性(ACID)：
原子性：一个事务不可再分割，要么都执行要么都不执行
一致性：一个事务执行会使数据从一个一致状态切换到另外一个一致状态
隔离性：一个事务的执行不受其他事务的干扰
持久性：一个事务一旦提交，则会永久的改变数据库的数据.
事务的创建
隐式事务：事务没有明显的开启和结束的标记，比如insert、update、delete语句
显式事务：事务具有明显的开启和结束的标记，前提：必须先设置自动提交功能为禁用 set autocommit=0;
开启事务
set autocommit=0;
start transaction; 可选的
结束事务
commit; 提交事务
rollback; 回滚事务
savepoint 节点名; 设置保存点
事务的隔离级别：
脏读 不可重复读 幻读
read uncommitted： √ √ √
read committed： × √ √
repeatable read： × × √
serializable × × ×
mysql中默认 第三个隔离级别 repeatable read
oracle中默认第二个隔离级别 read committed
查看隔离级别
select @@tx_isolation;
设置隔离级别
set session|global transaction isolation level 隔离级别;
**/
-- 查看是否开启自动提交
SHOW VARIABLES LIKE 'autocommit';
-- 查看所有存储引擎
SHOW ENGINES;
-- 事务的使用步骤
# 开启事务
SET autocommit=0;
START TRANSACTION;
# 编写一组事务的语句
UPDATE account SET balance = 1000 WHERE username='张无忌';
UPDATE account SET balance = 1000 WHERE username='赵敏';
# 结束事务：提交 commit 或回滚 rollback
COMMIT;
-- 对 delete 进行回滚
SET autocommit=0;
START TRANSACTION;
DELETE FROM account;
ROLLBACK;
-- 演示savepoint 的使用
SET autocommit=0;
START TRANSACTION;
DELETE FROM account WHERE id=25;
SAVEPOINT a; # 设置保存点
DELETE FROM account WHERE id=28;
ROLLBACK TO a; # 回滚到保存点
常见约束
/**
含义：
一种限制，用于限制表中的数据，为了保证表中的数据的准确和可靠性.
分类(六大约束)：
* NOT NULL：非空，用于保证该字段的值不能为空
* DEFAULT：默认，用于保证该字段有默认值
* PRIMARY KEY：主键，用于保证该字段的值具有唯一性，并且非空
* UNIQUE：唯一，用于保证该字段的值具有唯一性，可以为空
* CHECK：检查约束【mysql中不支持】
* FOREIGN KEY：外键，用于限制两个表的关系，用于保证该字段的值必须来自于主表的关联列的值
添加约束的时机：
1.创建表时
2.修改表时
约束的添加分类：
列级约束：
六大约束语法上都支持，但外键约束没有效果
表级约束：
除了非空、默认，其他的都支持
主键和唯一的区别：
保证唯一性 是否允许为空 一个表中可以有多少个 是否允许组合
主键 √ × 至多有1个 √，但不推荐
唯一 √ √ 可以有多个 √，但不推荐
外键：
1、要求在从表设置外键关系
2、从表的外键列的类型和主表的关联列的类型要求一致或兼容，名称无要求
3、主表的关联列必须是一个key（一般是主键或唯一）
4、插入数据时，先插入主表，再插入从表；删除数据时，先删除从表，再删除主表
语法：
CREATE TABLE 表名(
字段名 字段类型 列级约束,
字段名 字段类型,
表级约束
)
**/
CREATE DATABASE IF NOT EXISTS students;
USE students;
-- 添加列级约束
-- 语法：直接在字段名和类型后面追加 约束类型即可；只支持：默认、非空、主键、唯一
DROP TABLE IF EXISTS stuinfo;
CREATE TABLE stuinfo(
`id` INT PRIMARY KEY,# 主键
`stuName` VARCHAR(20) NOT NULL UNIQUE,# 非空
`gender` CHAR(1) CHECK(gender='男' OR gender ='女'),# 检查
`seat` INT UNIQUE,# 唯一
`age` INT DEFAULT 18,# 默认约束
`majorId` INT REFERENCES major(id)# 外键
);
CREATE TABLE major(
id INT PRIMARY KEY, # 主键
majorName VARCHAR(20)
);
-- 查看stuinfo中的所有索引，包括主键、外键、唯一
SHOW INDEX FROM stuinfo;
-- 添加表级约束
-- 语法：在各个字段的最下面 【constraint 约束名】 约束类型(字段名)
DROP TABLE IF EXISTS stuinfo;
CREATE TABLE stuinfo(
`id` INT,
`stuname` VARCHAR(20),
`gender` CHAR(1),
`seat` INT,
`age` INT,
`majorid` INT,
CONSTRAINT `pk` PRIMARY KEY(id),# 主键
CONSTRAINT `uq` UNIQUE(seat),# 唯一键
CONSTRAINT `ck` CHECK(gender ='男' OR gender = '女'),# 检查
CONSTRAINT `fk_stuinfo_major` FOREIGN KEY(`majorid`) REFERENCES major(`id`)# 外键
);
-- 1、修改表时添加约束
-- 添加列级约束：alter table 表名 modify column 字段名 字段类型 新约束;
-- 添加表级约束：alter table 表名 add 【constraint 约束名】 约束类型(字段名) 【外键的引用】;
DROP TABLE IF EXISTS stuinfo;
CREATE TABLE stuinfo(
id INT,
stuname VARCHAR(20),
gender CHAR(1),
seat INT,
age INT,
majorid INT
);
DESC stuinfo;
SHOW INDEX FROM stuinfo;
-- 添加非空约束
ALTER TABLE stuinfo MODIFY COLUMN stuName VARCHAR(20) UNIQUE;
-- 添加默认约束
ALTER TABLE stuinfo MODIFY COLUMN age INT DEFAULT 18;
-- 添加主键约束
# ①列级主键
ALTER TABLE stuinfo MODIFY COLUMN id INT PRIMARY KEY;
# ②表级主键
ALTER TABLE stuinfo ADD PRIMARY KEY(id);
-- 添加唯一约束
# ①列级唯一
ALTER TABLE stuinfo MODIFY COLUMN seat INT UNIQUE;
# ②表级唯一
ALTER TABLE stuinfo ADD UNIQUE(seat);
-- 添加外键
ALTER TABLE stuinfo ADD CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id);
-- 2、修改表时删除约束
-- 删除非空约束
ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NULL;
-- 删除默认约束
ALTER TABLE stuinfo MODIFY COLUMN age INT;
-- 删除主键
ALTER TABLE stuinfo DROP PRIMARY KEY;
-- 删除唯一约束
ALTER TABLE stuinfo DROP INDEX seat;
-- 删除外键
ALTER TABLE stuinfo DROP FOREIGN KEY fk_stuinfo_major;
-- ----------------- 标识列 -----------------
/**
标识列又称为自增长列
含义：
可以不用手动的插入值，系统提供默认的序列值
特点：
1、标识列必须和主键搭配吗？不一定，但要求是一个key
2、一个表可以有几个标识列？至多一个！
3、标识列的类型只能是数值型
4、标识列可以通过 SET auto_increment_increment=3 来设置步长
5、可以通过手动插入值来设置起始值
**/
-- 创建表时设置标识列
DROP TABLE IF EXISTS tab_identity;
CREATE TABLE tab_identity(
`id` INT PRIMARY KEY AUTO_INCREMENT,
`name` VARCHAR(20) UNIQUE ,
`seat` INT
);
-- 清空表数据
TRUNCATE TABLE tab_identity;
-- 添加数据
INSERT INTO tab_identity(id, name) VALUES(NULL,'john');
INSERT INTO tab_identity(name) VALUES('lucy');
INSERT INTO tab_identity(name) VALUES('marry');
-- 查看数据
SELECT * FROM tab_identity;
-- 查看自动增长相关信息
SHOW VARIABLES LIKE '%auto_increment%';
-- 设置自动增长步长
SET auto_increment_increment=3;
变量
-- 变量分为系统变量和自定义变量
-- 系统变量是由系统定义的，不是用户定义的，属于服务器层面。系统变量分为：全局变量和会话变量
-- 全局变量需要添加global关键字，会话变量需要添加session关键字，如果不写，默认会话级别
-- 自定义变量分为：用户变量和局部变量
-- 1、全局变量：作用域为针对所有会话有效，但不能跨重启
-- 查看所有全局变量
SHOW GLOBAL VARIABLES;
-- 查看满足条件的部分系统变量
SHOW GLOBAL VARIABLES LIKE '%char%';
-- 查看指定的系统变量值
SELECT @@global.autocommit;
SELECT @@global.tx_isolation;
-- 修改系统变量值
SET @@global.autocommit = 0;
SET GLOBAL AUTOCOMMIT = 1;
-- 2、会话变量：作用域为针对当前会话有效
-- 查看所有会话变量
SHOW SESSION VARIABLES;
-- 查看满足条件的部分会话变量
SHOW SESSION VARIABLES LIKE '%char%';
-- 查看指定的会话变量值
SELECT @@session.autocommit;
SELECT @@session.tx_isolation;
SELECT @@autocommit; -- 不写默认为 session
-- 设置会话变量值
SET @@session.tx_isolation = 'read-uncommitted';
SET SESSION TX_ISOLATION = 'read-committed';
-- 3、用户变量：作用域为针对当前会话有效，和会话变量的作用域相同
-- 声明并初始化变量（声明变量的同时必须初始化）：赋值操作符为 = 或 :=
SET @m = 1;
SET @n := 2;
SET @sum = @m + @n;
-- 使用select声明变量，但必须使用 := 进行赋值
SELECT @sum2 := 6;
SELECT @sum3 := @m + @n;
-- 查看变量值
SELECT @m, @n;
SELECT @sum;
-- 修改变量值
SET @m = 3;
SET @n := 4;
SELECT @sum := 5; -- 同样必须使用 :=
-- 4、局部变量：仅仅在定义它的 begin end 块中有效，应用在 begin end 中的第一句话
-- 声明局部变量：要在 begin end 块中进行声明
-- 语法：DECLARE 变量名 类型; DECLARE 变量名 类型 【DEFAULT 值】;
DECLARE m INT DEFAULT 1;
DECLARE n INT DEFAULT 1;
DECLARE SUM INT;
-- 局部变量赋值
SET m = 2;
SET n := 3;
SET SUM = m + n;
-- 使用局部变量
SELECT SUM;
-- 用户变量和局部变量的对比
-- 作用域 定义位置 语法
-- 用户变量 当前会话 会话的任何地方 加@符号，不用指定类型
-- 局部变量 定义它的BEGIN END中 BEGIN END的第一句话 一般不用加@,需要指定类型
视图
/**
视图的含义：
虚拟表，和普通表一样使用，是在 mysql5.1 版本出现的新特性，是通过表动态生成的数据
视图和表：
语法关键字 是否占用物理空间 用途
视图 create view 只是保存了 sql 逻辑 一般用于查询，不用于增删改
表 create table 保存了数据 增删改查
**/
-- 1、视图的创建
-- 案例：查询姓名中包含a字符的员工名、部门名和工种名称
# ①创建
CREATE VIEW myView1
AS
SELECT last_name, department_name, job_title
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id
INNER JOIN jobs j ON e.job_id = j.job_id;
# ②使用
SELECT * FROM myView1 WHERE last_name LIKE '%a%';
-- 案例：查询各部门的平均工资级别
# ①创建视图查看每个部门的平均工资
CREATE VIEW myView2
AS
SELECT department_id, avg(salary) ag
FROM employees
GROUP BY department_id;
# ②使用
SELECT myView2.department_id, myView2.ag, g.grade_level
FROM myView2
INNER JOIN job_grades g
ON myView2.ag BETWEEN g.lowest_sal AND g.highest_sal;
-- 案例：查询平均工资最低的部门名和工资
CREATE VIEW myView3
AS
SELECT * FROM myView2 ORDER BY ag LIMIT 1;
SELECT d.*, m.ag
FROM myView3 m
INNER JOIN departments d
ON m.department_id = d.department_id;
-- 2、视图的修改
-- 方式一：create or replace view 视图名 as 查询语句
CREATE OR REPLACE VIEW myView3
AS
SELECT job_id, avg(salary)
FROM employees
GROUP BY job_id;
-- 方式二：alter view 视图名 as 查询语句
ALTER VIEW myview3
AS
SELECT * FROM employees;
-- 查看修改后的视图内容
SELECT * FROM myView3;
-- 3、视图的删除
DROP VIEW myview1, myview2, myView3;
-- 4、视图的查看
-- 这里指的是查看视图的结构等信息
DESC myView1;
SHOW CREATE VIEW myView1;
-- 5、视图的更新
CREATE OR REPLACE VIEW myv1
AS
SELECT last_name, email
FROM employees;
SELECT * FROM myv1;
-- 插入
INSERT INTO myv1 VALUES ('张飞', 'zf@qq.com');
-- 修改
UPDATE myv1 SET last_name='张无忌' WHERE last_name='张飞';
-- 删除
DELETE FROM myv1 WHERE last_name='张无忌';
-- 注意：具备以下特点的视图不允许更新
-- 1、包含以下关键字的sql语句：分组函数、distinct、group by、having、union或者union all
CREATE OR REPLACE VIEW myv1
AS
SELECT MAX(salary) m, department_id
FROM employees
GROUP BY department_id;
SELECT * FROM myv1;
-- 尝试更新将报错
UPDATE myv1 SET m=9000 WHERE department_id=10;
-- 2、常量视图
CREATE OR REPLACE VIEW myv2
AS
SELECT 'john' NAME;
SELECT * FROM myv2;
-- 尝试更新将报错
UPDATE myv2 SET NAME='lucy';
-- 3、Select中包含子查询
CREATE OR REPLACE VIEW myv3
AS
SELECT department_id,(SELECT MAX(salary) FROM employees) 最高工资
FROM departments;
SELECT * FROM myv3;
-- 尝试更新将报错
UPDATE myv3 SET 最高工资=100000;
-- 4、包含 join 的多表查询
CREATE OR REPLACE VIEW myv4
AS
SELECT last_name,department_name
FROM employees e
JOIN departments d
ON e.department_id = d.department_id;
-- 尝试更新将报错
SELECT * FROM myv4;
UPDATE myv4 SET last_name = '张飞' WHERE last_name='Whalen';
INSERT INTO myv4 VALUES('陈真','xxxx');
-- 5、from一个不能更新的视图
CREATE OR REPLACE VIEW myv5
AS
SELECT * FROM myv3;
SELECT * FROM myv5;
-- 尝试更新将报错
UPDATE myv5 SET 最高工资=10000 WHERE department_id=60;
-- 6、where子句的子查询引用了from子句中的表
CREATE OR REPLACE VIEW myv6
AS
SELECT last_name,email,salary
FROM employees
WHERE employee_id IN(
SELECT manager_id
FROM employees
WHERE manager_id IS NOT NULL
);
SELECT * FROM myv6;
-- 尝试更新将报错
UPDATE myv6 SET salary=10000 WHERE last_name = 'k_ing';
存储过程
/**
存储过程：
一组预先编译好的SQL语句的集合，理解成批处理语句
好处是：
1、提高代码的重用性
2、简化操作
3、减少了编译次数并且减少了和数据库服务器的连接次数，提高了效率
存储过程和函数类似于java中的方法
一、存储过程的创建
语法为：
CREATE PROCEDURE 存储过程名(参数列表)
BEGIN
存储过程体（一组合法的SQL语句）
END
参数列表包含三部分：
参数模式 参数名 参数类型
例如：in stuname varchar(20)
参数模式：
in：该参数可以作为输入，也就是该参数需要调用方传入值
out：该参数可以作为输出，也就是该参数可以作为返回值
inout：该参数既可以作为输入又可以作为输出，也就是该参数既需要传入值，又可以返回值
如果存储过程体仅仅只有一句话，begin end可以省略；存储过程体中的每条sql语句的结尾要求必须加分号。
存储过程的结尾可以使用 delimiter 重新设置：
语法：
delimiter 结束标记
比如：delimiter $
二、存储过程的调用
调用语法为：CALL 存储过程名(实参列表)
三、存储过程的删除
删除语法为：DROP PROCEDURE 存储过程名
**/
-- 切换测试数据库
USE girls;
-- 1、创建空参列表的存储过程
-- 案例：往 admin 表中插入五条记录
DELIMITER $ -- 设置结尾符号
CREATE PROCEDURE mypro1()
BEGIN
INSERT INTO admin(`username`, `password`)
VALUES ('john1','0000'),('lily','0000'),('rose','0000'),('jack','0000'),('tom','0000');
END $
DELIMITER ; -- 恢复结尾符号
-- 调用
CALL mypro1();
-- 2、创建带 in 模式参数的存储过程
-- 案例：创建存储过程实现，根据女神名，查询对应的男神信息
DELIMITER $
CREATE PROCEDURE mypro2(IN beautyName VARCHAR(20))
BEGIN
SELECT bo.*
FROM beauty bu
LEFT OUTER JOIN boys bo ON bu.boyfriend_id=bo.id
WHERE bu.name=beautyName;
END $
DELIMITER ;
CALL mypro2('王语嫣');
-- 案例：创建存储过程实现，用户是否登录成功
DELIMITER $
CREATE PROCEDURE mypro3(IN `username` VARCHAR(20), IN `password` VARCHAR(20))
BEGIN
-- 声明查找结果变量
DECLARE result INT DEFAULT 0;
-- 查找结果
SELECT count(*) INTO result
FROM admin
WHERE admin.username = `username` AND admin.password = `password`;
-- 输入结果
SELECT if(result > 0, '成功', '失败');
END $
DELIMITER ;
CALL mypro3('john', '8888');
-- 3、创建 out 模式参数的存储过程
-- 案例：根据输入的女神名，返回对应的男神的名字和魅力值
DELIMITER $
CREATE PROCEDURE mypro4(IN beautyName VARCHAR(20), OUT boyName VARCHAR(20), OUT usercp INT)
BEGIN
SELECT bo.boyName, bo.userCP INTO boyName, usercp
FROM boys bo
RIGHT OUTER JOIN beauty bu ON bu.boyfriend_id = bo.id
WHERE bu.name=beautyName;
END $
DELIMITER ;
-- 调用
CALL mypro4('王语嫣', @name, @cp);
SELECT @`name`, @cp;
-- 4、创建带 inout 模式参数的存储过程
-- 案例：传入a和b两个值，最终a和b都翻倍并返回
DELIMITER $
CREATE PROCEDURE mypro5(INOUT a INT, INOUT b INT)
BEGIN
SET a = a*2;
SET b := b*2;
END $
DELIMITER ;
-- 调用
SET @m = 10;
SET @n = 20;
CALL mypro5(@m, @n);
SELECT @m, @n;
-- 删除存储过程
-- 不能批量删除
DROP PROCEDURE mypro1;
-- 查看存储过程信息
SHOW CREATE PROCEDURE mypro2;
函数
/**
函数的含义：
一组预先编译好的SQL语句的集合，理解成批处理语句
好处：
1、提高代码的重用性
2、简化操作
3、减少了编译次数并且减少了和数据库服务器的连接次数，提高了效率
存储过程和函数的区别：
存储过程：可以有0个返回，也可以有多个返回，适合做批量插入、批量更新
函数：有且仅有1个返回，适合做处理数据后返回一个结果
创建语法：
CREATE FUNCTION 函数名(参数列表) RETURNS 返回类型
BEGIN
函数体
END
调用语法：
SELECT 函数名(参数列表)
注意：
1.参数列表 包含两部分：参数名 参数类型
2.函数体：肯定会有return语句，如果没有会报错；
如果return语句没有放在函数体的最后也不报错，但不建议 return 值;
3.函数体中仅有一句话，则可以省略begin en
4.使用 delimiter语句设置结束标记
**/
-- 切换测试数据库
USE employee_db;
-- 1、创建无参有返回的函数
-- 案例：返回公司的员工个数
DELIMITER $
CREATE FUNCTION myfun1() RETURNS INT
BEGIN
-- 声明局部变量
DECLARE c INT DEFAULT 0;
-- 赋值
SELECT count(*) INTO c
FROM employees;
-- 返回值
RETURN c;
END $
DELIMITER ;
-- 调用
SELECT myfun1();
-- 2、创建有惨有返回的函数
-- 案例：根据员工名，返回他的工资
DELIMITER $
CREATE FUNCTION myfun2(empName VARCHAR(20)) RETURNS DOUBLE
BEGIN
-- 创建用户变量
SET @sal = 0;
-- 查询并赋值
SELECT salary INTO @sal
FROM employees
WHERE empName=employees.last_name
LIMIT 1;
-- 返回结果
RETURN @sal;
END $
DELIMITER ;
SELECT myfun2('k_ing');
-- 案例：根据部门名，返回该部门的平均工资
DELIMITER $
CREATE FUNCTION myfun3(deptName VARCHAR(20)) RETURNS DOUBLE
BEGIN
-- 局部变量保存结果
DECLARE sal DOUBLE ;
-- 查询并赋值
SELECT AVG(salary) INTO sal
FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE d.department_name=deptName;
-- 返回结果
RETURN sal;
END $
DELIMITER ;
SELECT myfun3('IT');
-- 3、查看函数
SHOW CREATE FUNCTION myfun2;
-- 4、删除函数
DROP FUNCTION myfun2;
流程控制结构
/**
流程控制结构：顺序、分支、循环
一、分支结构
1.if函数
语法：if(条件, 值1, 值2)
功能：实现双分支
应用在 begin end 中或外面
2.case结构
语法：
情况1：类似于 switch
case 变量或表达式
when 值1 then 语句1;
when 值2 then 语句2;
...
else 语句 n;
end
情况2：
case
when 条件1 then 语句1;
when 条件2 then 语句2;
...
else 语句 n;
end
应用在 begin end 中或外面
3.if结构
语法：
if 条件1 then 语句1;
elseif 条件2 then 语句2;
....
else 语句n;
end if;
功能：类似于多重if
只能应用在 begin end 中
**/
-- 案例：创建函数，实现传入成绩，如果成绩>90,返回A，如果成绩>80,返回B，如果成绩>60,返回C，否则返回D
DELIMITER $
CREATE FUNCTION test_if(score FLOAT) RETURNS CHAR
BEGIN
DECLARE ch CHAR DEFAULT 'A';
IF score > 90 THEN SET ch = 'A';
ELSEIF score > 80 THEN SET ch = 'B';
ELSEIF score > 60 THEN SET ch = 'C';
ELSE SET ch = 'D';
END IF;
RETURN ch;
END $
DELIMITER ;
SELECT test_if(87);
-- 案例：创建存储过程，如果工资<2000,则删除，如果5000>工资>2000,则涨工资1000，否则涨工资500
DELIMITER $
CREATE PROCEDURE test_if_pro(IN sal DOUBLE)
BEGIN
IF sal < 2000 THEN DELETE FROM employees WHERE salary = sal;
ELSEIF sal > 2000 AND sal < 5000 THEN UPDATE employees SET salary = salary + 1000
WHERE employees.salary = sal;
ELSE UPDATE employees SET salary = salary + 500 WHERE employees.salary = sal;
END IF;
END $
DELIMITER ;
CALL test_if_pro(2100);
-- 案例1：创建函数，实现传入成绩，如果成绩>90,返回A，如果成绩>80,返回B，如果成绩>60,返回C，否则返回D
DELIMITER $
CREATE FUNCTION test_case(score FLOAT) RETURNS CHAR
BEGIN
DECLARE ch CHAR DEFAULT 'A';
CASE
WHEN score > 90 THEN SET ch = 'A';
WHEN score > 80 THEN SET ch = 'B';
WHEN score > 60 THEN SET ch = 'C';
ELSE SET ch = 'D';
END CASE;
RETURN ch;
END $
DELIMITER ;
SELECT test_case(56);
/**
二、循环结构
分类：
while、loop、repeat
循环控制：
iterate 类似于 continue，继续，结束本次循环，继续下一次
leave 类似于 break，跳出，结束当前所在的循环
1.while
语法：
【标签:】while 循环条件 do
循环体;
end while【 标签】;
2.loop
语法：
【标签:】loop
循环体;
end loop 【标签】;
可以用来模拟简单的死循环
3.repeat
语法：
【标签：】repeat
循环体;
until 结束循环的条件
end repeat 【标签】;
**/
-- 切换测试数据库
USE girls;
-- 1、没有添加循环控制语句
-- 案例：批量插入，根据次数插入到admin表中多条记录
DELIMITER $
CREATE PROCEDURE pro_while(IN insertCount INT)
BEGIN
DECLARE i INT DEFAULT 1;
WHILE i <= insertCount DO
INSERT INTO admin(`username`,`password`) VALUES(CONCAT('Rose',i),'666');
SET i = i+1;
END WHILE;
END $
DELIMITER ;
-- 调用
CALL pro_while(10);
-- 查看结果
SELECT * FROM admin;
-- 2、添加leave语句
-- 案例：批量插入，根据次数插入到admin表中多条记录，如果次数>20则停止
DELIMITER $
CREATE PROCEDURE pro_while_leave(IN insertCount INT)
BEGIN
DECLARE i INT DEFAULT 1;
a:WHILE i <= insertCount DO
INSERT INTO admin(`username`,`password`) VALUES(CONCAT('Rose',i),'666');
IF i >= 20 THEN LEAVE a;
END IF;
SET i = i+1;
END WHILE a;
END $
DELIMITER ;
CALL pro_while_leave(100);
-- 3、添加iterate语句
-- 案例：批量插入，根据次数插入到admin表中多条记录，只插入偶数次
DELIMITER $
CREATE PROCEDURE pro_while_iterate(IN insertCount INT)
BEGIN
DECLARE i INT DEFAULT 0;
a:WHILE i <= insertCount DO
SET i = i+1;
IF MOD(i, 2) != 0 THEN ITERATE a;
END IF;
SELECT i;
INSERT INTO admin(`username`,`password`) VALUES(CONCAT('Rose',i),'666');
END WHILE a;
END $
DELIMITER ;
CALL pro_while_iterate(10);
-- 流程控制经典案例：已知表stringcontent，向该表插入指定个数的，随机的字符串
-- stringcontent 表
DROP TABLE IF EXISTS stringcontent;
CREATE TABLE stringcontent(
id INT PRIMARY KEY AUTO_INCREMENT,
content VARCHAR(20)
);
-- 创建存储过程
DELIMITER $
CREATE PROCEDURE test_randstr_insert(IN insertCount INT)
BEGIN
DECLARE i INT DEFAULT 1;
DECLARE str VARCHAR(26) DEFAULT 'abcdefghijklmnopqrstuvwxyz';
DECLARE startIndex INT; # 代表初始索引
DECLARE len INT; # 代表截取的字符长度
WHILE i<=insertcount DO
SET startIndex=FLOOR(RAND()*26+1); # 代表初始索引，随机范围1-26
SET len=FLOOR(RAND()*(20-startIndex+1)+1); # 代表截取长度，随机范围1-（20-startIndex+1）
INSERT INTO stringcontent(content) VALUES(SUBSTR(str,startIndex,len));
SET i=i+1;
END WHILE;
END $
DELIMITER ;
-- 测试
CALL test_randstr_insert(10);
-- 查看结果
SELECT * FROM stringcontent;
175
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
