DQL语句学习

莫语\t

于 2022-08-31 09:04:11 发布

阅读量145

点赞数

文章标签：学习数据库 sql

本文链接：https://blog.csdn.net/weixin_50496806/article/details/126587126

版权

sql学习练习（2022.8.29）

DQL语句（Data Query Language 数据库查询语言）

针对数据表中数据的查询操作，使用DQL。

1.数据库准备

/*
 Navicat MySQL Data Transfer

 Source Server         : yzr
 Source Server Type    : MySQL
 Source Server Version : 80029
 Source Host           : localhost:3306
 Source Schema         : companydb

 Target Server Type    : MySQL
 Target Server Version : 80029
 File Encoding         : 65001

 Date: 30/08/2022 09:40:52
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for category
-- ----------------------------
DROP TABLE IF EXISTS `category`;
CREATE TABLE `category`  (
  `cid` int(0) NULL DEFAULT NULL,
  `cname` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of category
-- ----------------------------

-- ----------------------------
-- Table structure for departments
-- ----------------------------
DROP TABLE IF EXISTS `departments`;
CREATE TABLE `departments`  (
  `department_id` int(0) NOT NULL AUTO_INCREMENT,
  `department_name` varchar(3) CHARACTER SET gb2312 COLLATE gb2312_chinese_ci NULL DEFAULT NULL,
  `manager_id` int(0) NULL DEFAULT NULL,
  `location_id` int(0) NULL DEFAULT NULL,
  PRIMARY KEY (`department_id`) USING BTREE,
  INDEX `loc_id_fk`(`location_id`) USING BTREE,
  CONSTRAINT `loc_id_fk` FOREIGN KEY (`location_id`) REFERENCES `locations` (`location_id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 270 CHARACTER SET = gb2312 COLLATE = gb2312_chinese_ci ROW_FORMAT = Compact;

-- ----------------------------
-- Records of departments
-- ----------------------------
INSERT INTO `departments` VALUES (10, 'Adm', 200, 1700);
INSERT INTO `departments` VALUES (20, 'Mar', 201, 1800);
INSERT INTO `departments` VALUES (30, 'Pur', 114, 1700);
INSERT INTO `departments` VALUES (40, 'Hum', 203, 2400);
INSERT INTO `departments` VALUES (50, 'Shi', 121, 1500);
INSERT INTO `departments` VALUES (60, 'IT', 103, 1400);
INSERT INTO `departments` VALUES (70, 'Pub', 204, 2700);
INSERT INTO `departments` VALUES (80, 'Sal', 145, 2500);
INSERT INTO `departments` VALUES (90, 'Exe', 100, 1700);
INSERT INTO `departments` VALUES (100, 'Fin', 108, 1700);
INSERT INTO `departments` VALUES (110, 'Acc', 205, 1700);
INSERT INTO `departments` VALUES (120, 'Tre', NULL, 1700);
INSERT INTO `departments` VALUES (130, 'Cor', NULL, 1700);
INSERT INTO `departments` VALUES (140, 'Con', NULL, 1700);
INSERT INTO `departments` VALUES (150, 'Sha', NULL, 1700);
INSERT INTO `departments` VALUES (160, 'Ben', NULL, 1700);
INSERT INTO `departments` VALUES (170, 'Man', NULL, 1700);
INSERT INTO `departments` VALUES (180, 'Con', NULL, 1700);
INSERT INTO `departments` VALUES (190, 'Con', NULL, 1700);
INSERT INTO `departments` VALUES (200, 'Ope', NULL, 1700);
INSERT INTO `departments` VALUES (210, 'IT ', NULL, 1700);
INSERT INTO `departments` VALUES (220, 'NOC', NULL, 1700);
INSERT INTO `departments` VALUES (230, 'IT ', NULL, 1700);
INSERT INTO `departments` VALUES (240, 'Gov', NULL, 1700);
INSERT INTO `departments` VALUES (250, 'Ret', NULL, 1700);
INSERT INTO `departments` VALUES (260, 'Rec', NULL, 1700);
INSERT INTO `departments` VALUES (270, 'Pay', NULL, 1700);

-- ----------------------------
-- Table structure for employees
-- ----------------------------
DROP TABLE IF EXISTS `employees`;
CREATE TABLE `employees`  (
  `employee_id` int(0) NOT NULL AUTO_INCREMENT,
  `first_name` varchar(20) CHARACTER SET gb2312 COLLATE gb2312_chinese_ci NULL DEFAULT NULL,
  `last_name` varchar(25) CHARACTER SET gb2312 COLLATE gb2312_chinese_ci NULL DEFAULT NULL,
  `email` varchar(25) CHARACTER SET gb2312 COLLATE gb2312_chinese_ci NULL DEFAULT NULL,
  `phone_number` varchar(20) CHARACTER SET gb2312 COLLATE gb2312_chinese_ci NULL DEFAULT NULL,
  `job_id` varchar(10) CHARACTER SET gb2312 COLLATE gb2312_chinese_ci NULL DEFAULT NULL,
  `salary` double(10, 2) NULL DEFAULT NULL,
  `commission_pct` double(4, 2) NULL DEFAULT NULL,
  `manager_id` int(0) NULL DEFAULT NULL,
  `department_id` int(0) NULL DEFAULT NULL,
  `hiredate` datetime(0) NULL DEFAULT NULL,
  PRIMARY KEY (`employee_id`) USING BTREE,
  INDEX `dept_id_fk`(`department_id`) USING BTREE,
  INDEX `job_id_fk`(`job_id`) USING BTREE,
  CONSTRAINT `dept_id_fk` FOREIGN KEY (`department_id`) REFERENCES `departments` (`department_id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `job_id_fk` FOREIGN KEY (`job_id`) REFERENCES `jobs` (`job_id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 206 CHARACTER SET = gb2312 COLLATE = gb2312_chinese_ci ROW_FORMAT = Compact;

-- ----------------------------
-- Records of employees
-- ----------------------------
INSERT INTO `employees` VALUES (100, 'Steven', 'K_ing', 'SKING', '515.123.4567', 'AD_PRES', 24000.00, NULL, NULL, 90, '1992-04-03 00:00:00');
INSERT INTO `employees` VALUES (101, 'Neena', 'Kochhar', 'NKOCHHAR', '515.123.4568', 'AD_VP', 17000.00, NULL, 100, 90, '1992-04-03 00:00:00');
INSERT INTO `employees` VALUES (102, 'Lex', 'De Haan', 'LDEHAAN', '515.123.4569', 'AD_VP', 17000.00, NULL, 100, 90, '1992-04-03 00:00:00');
INSERT INTO `employees` VALUES (103, 'Alexander', 'Hunold', 'AHUNOLD', '590.423.4567', 'IT_PROG', 9000.00, NULL, 102, 60, '1992-04-03 00:00:00');
INSERT INTO `employees` VALUES (104, 'Bruce', 'Ernst', 'BERNST', '590.423.4568', 'IT_PROG', 6000.00, NULL, 103, 60, '1992-04-03 00:00:00');
INSERT INTO `employees` VALUES (105, 'David', 'Austin', 'DAUSTIN', '590.423.4569', 'IT_PROG', 4800.00, NULL, 103, 60, '1998-03-03 00:00:00');
INSERT INTO `employees` VALUES (106, 'Valli', 'Pataballa', 'VPATABAL', '590.423.4560', 'IT_PROG', 4800.00, NULL, 103, 60, '1998-03-03 00:00:00');
INSERT INTO `employees` VALUES (107, 'Diana', 'Lorentz', 'DLORENTZ', '590.423.5567', 'IT_PROG', 4200.00, NULL, 103, 60, '1998-03-03 00:00:00');
INSERT INTO `employees` VALUES (108, 'Nancy', 'Greenberg', 'NGREENBE', '515.124.4569', 'FI_MGR', 12000.00, NULL, 101, 100, '1998-03-03 00:00:00');
INSERT INTO `employees` VALUES (109, 'Daniel', 'Faviet', 'DFAVIET', '515.124.4169', 'FI_ACCOUNT', 9000.00, NULL, 108, 100, '1998-03-03 00:00:00');
INSERT INTO `employees` VALUES (110, 'John', 'Chen', 'JCHEN', '515.124.4269', 'FI_ACCOUNT', 8200.00, NULL, 108, 100, '2000-09-09 00:00:00');
INSERT INTO `employees` VALUES (111, 'Ismael', 'Sciarra', 'ISCIARRA', '515.124.4369', 'FI_ACCOUNT', 7700.00, NULL, 108, 100, '2000-09-09 00:00:00');
INSERT INTO `employees` VALUES (112, 'Jose Manuel', 'Urman', 'JMURMAN', '515.124.4469', 'FI_ACCOUNT', 7800.00, NULL, 108, 100, '2000-09-09 00:00:00');
INSERT INTO `employees` VALUES (113, 'Luis', 'Popp', 'LPOPP', '515.124.4567', 'FI_ACCOUNT', 6900.00, NULL, 108, 100, '2000-09-09 00:00:00');
INSERT INTO `employees` VALUES (114, 'Den', 'Raphaely', 'DRAPHEAL', '515.127.4561', 'PU_MAN', 11000.00, NULL, 100, 30, '2000-09-09 00:00:00');
INSERT INTO `employees` VALUES (115, 'Alexander', 'Khoo', 'AKHOO', '515.127.4562', 'PU_CLERK', 3100.00, NULL, 114, 30, '2000-09-09 00:00:00');
INSERT INTO `employees` VALUES (116, 'Shelli', 'Baida', 'SBAIDA', '515.127.4563', 'PU_CLERK', 2900.00, NULL, 114, 30, '2000-09-09 00:00:00');
INSERT INTO `employees` VALUES (117, 'Sigal', 'Tobias', 'STOBIAS', '515.127.4564', 'PU_CLERK', 2800.00, NULL, 114, 30, '2000-09-09 00:00:00');
INSERT INTO `employees` VALUES (118, 'Guy', 'Himuro', 'GHIMURO', '515.127.4565', 'PU_CLERK', 2600.00, NULL, 114, 30, '2000-09-09 00:00:00');
INSERT INTO `employees` VALUES (119, 'Karen', 'Colmenares', 'KCOLMENA', '515.127.4566', 'PU_CLERK', 2500.00, NULL, 114, 30, '2000-09-09 00:00:00');
INSERT INTO `employees` VALUES (120, 'Matthew', 'Weiss', 'MWEISS', '650.123.1234', 'ST_MAN', 8000.00, NULL, 100, 50, '2004-02-06 00:00:00');
INSERT INTO `employees` VALUES (121, 'Adam', 'Fripp', 'AFRIPP', '650.123.2234', 'ST_MAN', 8200.00, NULL, 100, 50, '2004-02-06 00:00:00');
INSERT INTO `employees` VALUES (122, 'Payam', 'Kaufling', 'PKAUFLIN', '650.123.3234', 'ST_MAN', 7900.00, NULL, 100, 50, '2004-02-06 00:00:00');
INSERT INTO `employees` VALUES (123, 'Shanta', 'Vollman', 'SVOLLMAN', '650.123.4234', 'ST_MAN', 6500.00, NULL, 100, 50, '2004-02-06 00:00:00');
INSERT INTO `employees` VALUES (124, 'Kevin', 'Mourgos', 'KMOURGOS', '650.123.5234', 'ST_MAN', 5800.00, NULL, 100, 50, '2004-02-06 00:00:00');
INSERT INTO `employees` VALUES (125, 'Julia', 'Nayer', 'JNAYER', '650.124.1214', 'ST_CLERK', 3200.00, NULL, 120, 50, '2004-02-06 00:00:00');
INSERT INTO `employees` VALUES (126, 'Irene', 'Mikkilineni', 'IMIKKILI', '650.124.1224', 'ST_CLERK', 2700.00, NULL, 120, 50, '2004-02-06 00:00:00');
INSERT INTO `employees` VALUES (127, 'James', 'Landry', 'JLANDRY', '650.124.1334', 'ST_CLERK', 2400.00, NULL, 120, 50, '2004-02-06 00:00:00');
INSERT INTO `employees` VALUES (128, 'Steven', 'Markle', 'SMARKLE', '650.124.1434', 'ST_CLERK', 2200.00, NULL, 120, 50, '2004-02-06 00:00:00');
INSERT INTO `employees` VALUES (129, 'Laura', 'Bissot', 'LBISSOT', '650.124.5234', 'ST_CLERK', 3300.00, NULL, 121, 50, '2004-02-06 00:00:00');
INSERT INTO `employees` VALUES (130, 'Mozhe', 'Atkinson', 'MATKINSO', '650.124.6234', 'ST_CLERK', 2800.00, NULL, 121, 50, '2004-02-06 00:00:00');
INSERT INTO `employees` VALUES (131, 'James', 'Marlow', 'JAMRLOW', '650.124.7234', 'ST_CLERK', 2500.00, NULL, 121, 50, '2004-02-06 00:00:00');
INSERT INTO `employees` VALUES (132, 'TJ', 'Olson', 'TJOLSON', '650.124.8234', 'ST_CLERK', 2100.00, NULL, 121, 50, '2004-02-06 00:00:00');
INSERT INTO `employees` VALUES (133, 'Jason', 'Mallin', 'JMALLIN', '650.127.1934', 'ST_CLERK', 3300.00, NULL, 122, 50, '2004-02-06 00:00:00');
INSERT INTO `employees` VALUES (134, 'Michael', 'Rogers', 'MROGERS', '650.127.1834', 'ST_CLERK', 2900.00, NULL, 122, 50, '2002-12-23 00:00:00');
INSERT INTO `employees` VALUES (135, 'Ki', 'Gee', 'KGEE', '650.127.1734', 'ST_CLERK', 2400.00, NULL, 122, 50, '2002-12-23 00:00:00');
INSERT INTO `employees` VALUES (136, 'Hazel', 'Philtanker', 'HPHILTAN', '650.127.1634', 'ST_CLERK', 2200.00, NULL, 122, 50, '2002-12-23 00:00:00');
INSERT INTO `employees` VALUES (137, 'Renske', 'Ladwig', 'RLADWIG', '650.121.1234', 'ST_CLERK', 3600.00, NULL, 123, 50, '2002-12-23 00:00:00');
INSERT INTO `employees` VALUES (138, 'Stephen', 'Stiles', 'SSTILES', '650.121.2034', 'ST_CLERK', 3200.00, NULL, 123, 50, '2002-12-23 00:00:00');
INSERT INTO `employees` VALUES (139, 'John', 'Seo', 'JSEO', '650.121.2019', 'ST_CLERK', 2700.00, NULL, 123, 50, '2002-12-23 00:00:00');
INSERT INTO `employees` VALUES (140, 'Joshua', 'Patel', 'JPATEL', '650.121.1834', 'ST_CLERK', 2500.00, NULL, 123, 50, '2002-12-23 00:00:00');
INSERT INTO `employees` VALUES (141, 'Trenna', 'Rajs', 'TRAJS', '650.121.8009', 'ST_CLERK', 3500.00, NULL, 124, 50, '2002-12-23 00:00:00');
INSERT INTO `employees` VALUES (142, 'Curtis', 'Davies', 'CDAVIES', '650.121.2994', 'ST_CLERK', 3100.00, NULL, 124, 50, '2002-12-23 00:00:00');
INSERT INTO `employees` VALUES (143, 'Randall', 'Matos', 'RMATOS', '650.121.2874', 'ST_CLERK', 2600.00, NULL, 124, 50, '2002-12-23 00:00:00');
INSERT INTO `employees` VALUES (144, 'Peter', 'Vargas', 'PVARGAS', '650.121.2004', 'ST_CLERK', 2500.00, NULL, 124, 50, '2002-12-23 00:00:00');
INSERT INTO `employees` VALUES (145, 'John', 'Russell', 'JRUSSEL', '011.44.1344.429268', 'SA_MAN', 14000.00, 0.40, 100, 80, '2002-12-23 00:00:00');
INSERT INTO `employees` VALUES (146, 'Karen', 'Partners', 'KPARTNER', '011.44.1344.467268', 'SA_MAN', 13500.00, 0.30, 100, 80, '2002-12-23 00:00:00');
INSERT INTO `employees` VALUES (147, 'Alberto', 'Errazuriz', 'AERRAZUR', '011.44.1344.429278', 'SA_MAN', 12000.00, 0.30, 100, 80, '2002-12-23 00:00:00');
INSERT INTO `employees` VALUES (148, 'Gerald', 'Cambrault', 'GCAMBRAU', '011.44.1344.619268', 'SA_MAN', 11000.00, 0.30, 100, 80, '2002-12-23 00:00:00');
INSERT INTO `employees` VALUES (149, 'Eleni', 'Zlotkey', 'EZLOTKEY', '011.44.1344.429018', 'SA_MAN', 10500.00, 0.20, 100, 80, '2002-12-23 00:00:00');
INSERT INTO `employees` VALUES (150, 'Peter', 'Tucker', 'PTUCKER', '011.44.1344.129268', 'SA_REP', 10000.00, 0.30, 145, 80, '2014-03-05 00:00:00');
INSERT INTO `employees` VALUES (151, 'David', 'Bernstein', 'DBERNSTE', '011.44.1344.345268', 'SA_REP', 9500.00, 0.25, 145, 80, '2014-03-05 00:00:00');
INSERT INTO `employees` VALUES (152, 'Peter', 'Hall', 'PHALL', '011.44.1344.478968', 'SA_REP', 9000.00, 0.25, 145, 80, '2014-03-05 00:00:00');
INSERT INTO `employees` VALUES (153, 'Christopher', 'Olsen', 'COLSEN', '011.44.1344.498718', 'SA_REP', 8000.00, 0.20, 145, 80, '2014-03-05 00:00:00');
INSERT INTO `employees` VALUES (154, 'Nanette', 'Cambrault', 'NCAMBRAU', '011.44.1344.987668', 'SA_REP', 7500.00, 0.20, 145, 80, '2014-03-05 00:00:00');
INSERT INTO `employees` VALUES (155, 'Oliver', 'Tuvault', 'OTUVAULT', '011.44.1344.486508', 'SA_REP', 7000.00, 0.15, 145, 80, '2014-03-05 00:00:00');
INSERT INTO `employees` VALUES (156, 'Janette', 'K_ing', 'JKING', '011.44.1345.429268', 'SA_REP', 10000.00, 0.35, 146, 80, '2014-03-05 00:00:00');
INSERT INTO `employees` VALUES (157, 'Patrick', 'Sully', 'PSULLY', '011.44.1345.929268', 'SA_REP', 9500.00, 0.35, 146, 80, '2014-03-05 00:00:00');
INSERT INTO `employees` VALUES (158, 'Allan', 'McEwen', 'AMCEWEN', '011.44.1345.829268', 'SA_REP', 9000.00, 0.35, 146, 80, '2014-03-05 00:00:00');
INSERT INTO `employees` VALUES (159, 'Lindsey', 'Smith', 'LSMITH', '011.44.1345.729268', 'SA_REP', 8000.00, 0.30, 146, 80, '2014-03-05 00:00:00');
INSERT INTO `employees` VALUES (160, 'Louise', 'Doran', 'LDORAN', '011.44.1345.629268', 'SA_REP', 7500.00, 0.30, 146, 80, '2014-03-05 00:00:00');
INSERT INTO `employees` VALUES (161, 'Sarath', 'Sewall', 'SSEWALL', '011.44.1345.529268', 'SA_REP', 7000.00, 0.25, 146, 80, '2014-03-05 00:00:00');
INSERT INTO `employees` VALUES (162, 'Clara', 'Vishney', 'CVISHNEY', '011.44.1346.129268', 'SA_REP', 10500.00, 0.25, 147, 80, '2014-03-05 00:00:00');
INSERT INTO `employees` VALUES (163, 'Danielle', 'Greene', 'DGREENE', '011.44.1346.229268', 'SA_REP', 9500.00, 0.15, 147, 80, '2014-03-05 00:00:00');
INSERT INTO `employees` VALUES (164, 'Mattea', 'Marvins', 'MMARVINS', '011.44.1346.329268', 'SA_REP', 7200.00, 0.10, 147, 80, '2014-03-05 00:00:00');
INSERT INTO `employees` VALUES (165, 'David', 'Lee', 'DLEE', '011.44.1346.529268', 'SA_REP', 6800.00, 0.10, 147, 80, '2014-03-05 00:00:00');
INSERT INTO `employees` VALUES (166, 'Sundar', 'Ande', 'SANDE', '011.44.1346.629268', 'SA_REP', 6400.00, 0.10, 147, 80, '2014-03-05 00:00:00');
INSERT INTO `employees` VALUES (167, 'Amit', 'Banda', 'ABANDA', '011.44.1346.729268', 'SA_REP', 6200.00, 0.10, 147, 80, '2014-03-05 00:00:00');
INSERT INTO `employees` VALUES (168, 'Lisa', 'Ozer', 'LOZER', '011.44.1343.929268', 'SA_REP', 11500.00, 0.25, 148, 80, '2014-03-05 00:00:00');
INSERT INTO `employees` VALUES (169, 'Harrison', 'Bloom', 'HBLOOM', '011.44.1343.829268', 'SA_REP', 10000.00, 0.20, 148, 80, '2014-03-05 00:00:00');
INSERT INTO `employees` VALUES (170, 'Tayler', 'Fox', 'TFOX', '011.44.1343.729268', 'SA_REP', 9600.00, 0.20, 148, 80, '2014-03-05 00:00:00');
INSERT INTO `employees` VALUES (171, 'William', 'Smith', 'WSMITH', '011.44.1343.629268', 'SA_REP', 7400.00, 0.15, 148, 80, '2014-03-05 00:00:00');
INSERT INTO `employees` VALUES (172, 'Elizabeth', 'Bates', 'EBATES', '011.44.1343.529268', 'SA_REP', 7300.00, 0.15, 148, 80, '2014-03-05 00:00:00');
INSERT INTO `employees` VALUES (173, 'Sundita', 'Kumar', 'SKUMAR', '011.44.1343.329268', 'SA_REP', 6100.00, 0.10, 148, 80, '2014-03-05 00:00:00');
INSERT INTO `employees` VALUES (174, 'Ellen', 'Abel', 'EABEL', '011.44.1644.429267', 'SA_REP', 11000.00, 0.30, 149, 80, '2014-03-05 00:00:00');
INSERT INTO `employees` VALUES (175, 'Alyssa', 'Hutton', 'AHUTTON', '011.44.1644.429266', 'SA_REP', 8800.00, 0.25, 149, 80, '2014-03-05 00:00:00');
INSERT INTO `employees` VALUES (176, 'Jonathon', 'Taylor', 'JTAYLOR', '011.44.1644.429265', 'SA_REP', 8600.00, 0.20, 149, 80, '2014-03-05 00:00:00');
INSERT INTO `employees` VALUES (177, 'Jack', 'Livingston', 'JLIVINGS', '011.44.1644.429264', 'SA_REP', 8400.00, 0.20, 149, 80, '2014-03-05 00:00:00');
INSERT INTO `employees` VALUES (178, 'Kimberely', 'Grant', 'KGRANT', '011.44.1644.429263', 'SA_REP', 7000.00, 0.15, 149, NULL, '2014-03-05 00:00:00');
INSERT INTO `employees` VALUES (179, 'Charles', 'Johnson', 'CJOHNSON', '011.44.1644.429262', 'SA_REP', 6200.00, 0.10, 149, 80, '2014-03-05 00:00:00');
INSERT INTO `employees` VALUES (180, 'Winston', 'Taylor', 'WTAYLOR', '650.507.9876', 'SH_CLERK', 3200.00, NULL, 120, 50, '2014-03-05 00:00:00');
INSERT INTO `employees` VALUES (181, 'Jean', 'Fleaur', 'JFLEAUR', '650.507.9877', 'SH_CLERK', 3100.00, NULL, 120, 50, '2014-03-05 00:00:00');
INSERT INTO `employees` VALUES (182, 'Martha', 'Sullivan', 'MSULLIVA', '650.507.9878', 'SH_CLERK', 2500.00, NULL, 120, 50, '2014-03-05 00:00:00');
INSERT INTO `employees` VALUES (183, 'Girard', 'Geoni', 'GGEONI', '650.507.9879', 'SH_CLERK', 2800.00, NULL, 120, 50, '2014-03-05 00:00:00');
INSERT INTO `employees` VALUES (184, 'Nandita', 'Sarchand', 'NSARCHAN', '650.509.1876', 'SH_CLERK', 4200.00, NULL, 121, 50, '2014-03-05 00:00:00');
INSERT INTO `employees` VALUES (185, 'Alexis', 'Bull', 'ABULL', '650.509.2876', 'SH_CLERK', 4100.00, NULL, 121, 50, '2014-03-05 00:00:00');
INSERT INTO `employees` VALUES (186, 'Julia', 'Dellinger', 'JDELLING', '650.509.3876', 'SH_CLERK', 3400.00, NULL, 121, 50, '2014-03-05 00:00:00');
INSERT INTO `employees` VALUES (187, 'Anthony', 'Cabrio', 'ACABRIO', '650.509.4876', 'SH_CLERK', 3000.00, NULL, 121, 50, '2014-03-05 00:00:00');
INSERT INTO `employees` VALUES (188, 'Kelly', 'Chung', 'KCHUNG', '650.505.1876', 'SH_CLERK', 3800.00, NULL, 122, 50, '2014-03-05 00:00:00');
INSERT INTO `employees` VALUES (189, 'Jennifer', 'Dilly', 'JDILLY', '650.505.2876', 'SH_CLERK', 3600.00, NULL, 122, 50, '2014-03-05 00:00:00');
INSERT INTO `employees` VALUES (190, 'Timothy', 'Gates', 'TGATES', '650.505.3876', 'SH_CLERK', 2900.00, NULL, 122, 50, '2014-03-05 00:00:00');
INSERT INTO `employees` VALUES (191, 'Randall', 'Perkins', 'RPERKINS', '650.505.4876', 'SH_CLERK', 2500.00, NULL, 122, 50, '2014-03-05 00:00:00');
INSERT INTO `employees` VALUES (192, 'Sarah', 'Bell', 'SBELL', '650.501.1876', 'SH_CLERK', 4000.00, NULL, 123, 50, '2014-03-05 00:00:00');
INSERT INTO `employees` VALUES (193, 'Britney', 'Everett', 'BEVERETT', '650.501.2876', 'SH_CLERK', 3900.00, NULL, 123, 50, '2014-03-05 00:00:00');
INSERT INTO `employees` VALUES (194, 'Samuel', 'McCain', 'SMCCAIN', '650.501.3876', 'SH_CLERK', 3200.00, NULL, 123, 50, '2014-03-05 00:00:00');
INSERT INTO `employees` VALUES (195, 'Vance', 'Jones', 'VJONES', '650.501.4876', 'SH_CLERK', 2800.00, NULL, 123, 50, '2014-03-05 00:00:00');
INSERT INTO `employees` VALUES (196, 'Alana', 'Walsh', 'AWALSH', '650.507.9811', 'SH_CLERK', 3100.00, NULL, 124, 50, '2014-03-05 00:00:00');
INSERT INTO `employees` VALUES (197, 'Kevin', 'Feeney', 'KFEENEY', '650.507.9822', 'SH_CLERK', 3000.00, NULL, 124, 50, '2014-03-05 00:00:00');
INSERT INTO `employees` VALUES (198, 'Donald', 'OConnell', 'DOCONNEL', '650.507.9833', 'SH_CLERK', 2600.00, NULL, 124, 50, '2014-03-05 00:00:00');
INSERT INTO `employees` VALUES (199, 'Douglas', 'Grant', 'DGRANT', '650.507.9844', 'SH_CLERK', 2600.00, NULL, 124, 50, '2014-03-05 00:00:00');
INSERT INTO `employees` VALUES (200, 'Jennifer', 'Whalen', 'JWHALEN', '515.123.4444', 'AD_ASST', 4400.00, NULL, 101, 10, '2016-03-03 00:00:00');
INSERT INTO `employees` VALUES (201, 'Michael', 'Hartstein', 'MHARTSTE', '515.123.5555', 'MK_MAN', 13000.00, NULL, 100, 20, '2016-03-03 00:00:00');
INSERT INTO `employees` VALUES (202, 'Pat', 'Fay', 'PFAY', '603.123.6666', 'MK_REP', 6000.00, NULL, 201, 20, '2016-03-03 00:00:00');
INSERT INTO `employees` VALUES (203, 'Susan', 'Mavris', 'SMAVRIS', '515.123.7777', 'HR_REP', 6500.00, NULL, 101, 40, '2016-03-03 00:00:00');
INSERT INTO `employees` VALUES (204, 'Hermann', 'Baer', 'HBAER', '515.123.8888', 'PR_REP', 10000.00, NULL, 101, 70, '2016-03-03 00:00:00');
INSERT INTO `employees` VALUES (205, 'Shelley', 'Higgins', 'SHIGGINS', '515.123.8080', 'AC_MGR', 12000.00, NULL, 101, 110, '2016-03-03 00:00:00');
INSERT INTO `employees` VALUES (206, 'William', 'Gietz', 'WGIETZ', '515.123.8181', 'AC_ACCOUNT', 8300.00, NULL, 205, 110, '2016-03-03 00:00:00');

-- ----------------------------
-- Table structure for job_grades
-- ----------------------------
DROP TABLE IF EXISTS `job_grades`;
CREATE TABLE `job_grades`  (
  `grade_level` varchar(3) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `lowest_sal` int(0) NULL DEFAULT NULL,
  `highest_sal` int(0) NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

-- ----------------------------
-- Records of job_grades
-- ----------------------------
INSERT INTO `job_grades` VALUES ('A', 1000, 2999);
INSERT INTO `job_grades` VALUES ('B', 3000, 5999);
INSERT INTO `job_grades` VALUES ('C', 6000, 9999);
INSERT INTO `job_grades` VALUES ('D', 10000, 14999);
INSERT INTO `job_grades` VALUES ('E', 15000, 24999);
INSERT INTO `job_grades` VALUES ('F', 25000, 40000);

-- ----------------------------
-- Table structure for jobs
-- ----------------------------
DROP TABLE IF EXISTS `jobs`;
CREATE TABLE `jobs`  (
  `job_id` varchar(10) CHARACTER SET gb2312 COLLATE gb2312_chinese_ci NOT NULL,
  `job_title` varchar(35) CHARACTER SET gb2312 COLLATE gb2312_chinese_ci NULL DEFAULT NULL,
  `min_salary` int(0) NULL DEFAULT NULL,
  `max_salary` int(0) NULL DEFAULT NULL,
  PRIMARY KEY (`job_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = gb2312 COLLATE = gb2312_chinese_ci ROW_FORMAT = Compact;

-- ----------------------------
-- Records of jobs
-- ----------------------------
INSERT INTO `jobs` VALUES ('AC_ACCOUNT', 'Public Accountant', 4200, 9000);
INSERT INTO `jobs` VALUES ('AC_MGR', 'Accounting Manager', 8200, 16000);
INSERT INTO `jobs` VALUES ('AD_ASST', 'Administration Assistant', 3000, 6000);
INSERT INTO `jobs` VALUES ('AD_PRES', 'President', 20000, 40000);
INSERT INTO `jobs` VALUES ('AD_VP', 'Administration Vice President', 15000, 30000);
INSERT INTO `jobs` VALUES ('FI_ACCOUNT', 'Accountant', 4200, 9000);
INSERT INTO `jobs` VALUES ('FI_MGR', 'Finance Manager', 8200, 16000);
INSERT INTO `jobs` VALUES ('HR_REP', 'Human Resources Representative', 4000, 9000);
INSERT INTO `jobs` VALUES ('IT_PROG', 'Programmer', 4000, 10000);
INSERT INTO `jobs` VALUES ('MK_MAN', 'Marketing Manager', 9000, 15000);
INSERT INTO `jobs` VALUES ('MK_REP', 'Marketing Representative', 4000, 9000);
INSERT INTO `jobs` VALUES ('PR_REP', 'Public Relations Representative', 4500, 10500);
INSERT INTO `jobs` VALUES ('PU_CLERK', 'Purchasing Clerk', 2500, 5500);
INSERT INTO `jobs` VALUES ('PU_MAN', 'Purchasing Manager', 8000, 15000);
INSERT INTO `jobs` VALUES ('SA_MAN', 'Sales Manager', 10000, 20000);
INSERT INTO `jobs` VALUES ('SA_REP', 'Sales Representative', 6000, 12000);
INSERT INTO `jobs` VALUES ('SH_CLERK', 'Shipping Clerk', 2500, 5500);
INSERT INTO `jobs` VALUES ('ST_CLERK', 'Stock Clerk', 2000, 5000);
INSERT INTO `jobs` VALUES ('ST_MAN', 'Stock Manager', 5500, 8500);

-- ----------------------------
-- Table structure for locations
-- ----------------------------
DROP TABLE IF EXISTS `locations`;
CREATE TABLE `locations`  (
  `location_id` int(0) NOT NULL AUTO_INCREMENT,
  `street_address` varchar(40) CHARACTER SET gb2312 COLLATE gb2312_chinese_ci NULL DEFAULT NULL,
  `postal_code` varchar(12) CHARACTER SET gb2312 COLLATE gb2312_chinese_ci NULL DEFAULT NULL,
  `city` varchar(30) CHARACTER SET gb2312 COLLATE gb2312_chinese_ci NULL DEFAULT NULL,
  `state_province` varchar(25) CHARACTER SET gb2312 COLLATE gb2312_chinese_ci NULL DEFAULT NULL,
  `country_id` varchar(2) CHARACTER SET gb2312 COLLATE gb2312_chinese_ci NULL DEFAULT NULL,
  PRIMARY KEY (`location_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 3200 CHARACTER SET = gb2312 COLLATE = gb2312_chinese_ci ROW_FORMAT = Compact;

-- ----------------------------
-- Records of locations
-- ----------------------------
INSERT INTO `locations` VALUES (1000, '1297 Via Cola di Rie', '00989', 'Roma', NULL, 'IT');
INSERT INTO `locations` VALUES (1100, '93091 Calle della Testa', '10934', 'Venice', NULL, 'IT');
INSERT INTO `locations` VALUES (1200, '2017 Shinjuku-ku', '1689', 'Tokyo', 'Tokyo Prefecture', 'JP');
INSERT INTO `locations` VALUES (1300, '9450 Kamiya-cho', '6823', 'Hiroshima', NULL, 'JP');
INSERT INTO `locations` VALUES (1400, '2014 Jabberwocky Rd', '26192', 'Southlake', 'Texas', 'US');
INSERT INTO `locations` VALUES (1500, '2011 Interiors Blvd', '99236', 'South San Francisco', 'California', 'US');
INSERT INTO `locations` VALUES (1600, '2007 Zagora St', '50090', 'South Brunswick', 'New Jersey', 'US');
INSERT INTO `locations` VALUES (1700, '2004 Charade Rd', '98199', 'Seattle', 'Washington', 'US');
INSERT INTO `locations` VALUES (1800, '147 Spadina Ave', 'M5V 2L7', 'Toronto', 'Ontario', 'CA');
INSERT INTO `locations` VALUES (1900, '6092 Boxwood St', 'YSW 9T2', 'Whitehorse', 'Yukon', 'CA');
INSERT INTO `locations` VALUES (2000, '40-5-12 Laogianggen', '190518', 'Beijing', NULL, 'CN');
INSERT INTO `locations` VALUES (2100, '1298 Vileparle (E)', '490231', 'Bombay', 'Maharashtra', 'IN');
INSERT INTO `locations` VALUES (2200, '12-98 Victoria Street', '2901', 'Sydney', 'New South Wales', 'AU');
INSERT INTO `locations` VALUES (2300, '198 Clementi North', '540198', 'Singapore', NULL, 'SG');
INSERT INTO `locations` VALUES (2400, '8204 Arthur St', NULL, 'London', NULL, 'UK');
INSERT INTO `locations` VALUES (2500, 'Magdalen Centre, The Oxford Science Park', 'OX9 9ZB', 'Oxford', 'Oxford', 'UK');
INSERT INTO `locations` VALUES (2600, '9702 Chester Road', '09629850293', 'Stretford', 'Manchester', 'UK');
INSERT INTO `locations` VALUES (2700, 'Schwanthalerstr. 7031', '80925', 'Munich', 'Bavaria', 'DE');
INSERT INTO `locations` VALUES (2800, 'Rua Frei Caneca 1360 ', '01307-002', 'Sao Paulo', 'Sao Paulo', 'BR');
INSERT INTO `locations` VALUES (2900, '20 Rue des Corps-Saints', '1730', 'Geneva', 'Geneve', 'CH');
INSERT INTO `locations` VALUES (3000, 'Murtenstrasse 921', '3095', 'Bern', 'BE', 'CH');
INSERT INTO `locations` VALUES (3100, 'Pieter Breughelstraat 837', '3029SK', 'Utrecht', 'Utrecht', 'NL');
INSERT INTO `locations` VALUES (3200, 'Mariano Escobedo 9991', '11932', 'Mexico City', 'Distrito Federal,', 'MX');

SET FOREIGN_KEY_CHECKS = 1;

基础查询(单表查询)

查询employees表的内容

-- 查询的内容由字段【变量】组成
-- 需求：查询所有员工的姓名 邮箱以及部门编号
SELECT first_name,last_name,email,department_id
FROM employees

SELECT *
FROM employees

SELECT *查询全部

SELECT *
FROM employees

AS起别名

*为一列查询数据定义一个列名

-- 查询的内容也可以包含一个固定的值【常量】
-- 查询每个员工的编号，姓名 工资以及对应的国籍
【假设所有的员工的国籍都是中国】
SELECT employee_id,first_name,last_name,salary,'中国' AS country
FROM employees

DISTINCT(distinct 不同的)

-- 可以使用distinct短语对查询结果的重复值进行排重处理
-- 从员工表中查询所有员工都有哪些工种
SELECT DISTINCT job_id 
FROM employees

WHERE(添加条件，筛选查询结果)

-- 可以在查询语句中使用where短语进行条件的筛选
-- 查询工资在15000以上的所有员工的信息
SELECT *
FROM employees
WHERE salary>=15000

逻辑运算符

多条件之间使用逻辑运算符进行连接 and 且 or 或 not 非逻辑取反

AND(and)

-- 查询工资在15000以上20000以下的所有员工的信息
SELECT *
FROM employees
WHERE salary>=15000 AND salary<=20000

OR(or)

-- 查询工资在15000以下和20000以上的所有员工的信息
SELECT *
FROM employees
WHERE salary<15000 OR salary>20000

NOT(not)

-- 查询非90号部门的员工信息
-- 不等于比较运算符:<>
SELECT *
FROM employees
WHERE department_id<>90
SELECT *
FROM employees
WHERE NOT department_id=90

枚举（in）

-- 查询10号 50号 以及80 号部门的员工信息
-- 使用in短语进行条件的表达
SELECT *
FROM employees
WHERE department_id=10 OR department_id=50 OR department_id=80
-- 使用in后
SELECT *
FROM employees
WHERE department_id IN(10,50,80)

BETWEEN 操作符

在 WHERE 子句中使用，作用是选取介于两个值之间的数据范围。
他的范围是包含10000和15000的，即等价于salary>=10000 AND salary<=15000

-- 查询工资在10000-15000之间的员工信息
-- 第一种写法：
SELECT *
FROM employees
WHERE salary>=10000 AND salary<=15000
-- 第二种写法
SELECT *
FROM employees
WHERE salary BETWEEN 10000 AND 15000

关于NULL元素的处理

-- 查询暂时没有部门的员工
-- NULL值【不确定】在SQL中不能参与相等或不相等的比较
-- 错误的写法
SELECT *
FROM employees
WHERE department_id = NULL
-- 正确的写法
SELECT *
FROM employees
WHERE department_id IS NULL 

-- 查询所有有绩效的员工
SELECT *
FROM employees
WHERE commission_pct IS NOT NULL

like操作符

SELECT *
FROM employees
WHERE phone_number LIKE '650%'
-- 查询所有电话以650开头并且尾号为6的员工信息
-- %可以代替一个和多个字符
SELECT *
FROM employees
WHERE phone_number LIKE '650%6'
-- 查询所有电话以650开头并且尾号为6同时倒数第四个为1的员工信息
-- 可以在匹配的文本常量中使用_来描述任何一个字符
SELECT *
FROM employees
WHERE phone_number LIKE '650%1__6'

模糊查询

SELECT *
FROM employees
WHERE phone_number LIKE '%650%'

排序

-- 查询所有50号部门的员工信息，要求对查询的结果按照工资降序排列
-- 查询中可以使用order by 短语按照某个列的值的大小进行顺序排列
-- 排序的方式有两种：1.ASC 升序 也是默认的方式 2.DESC 降序
SELECT * FROM employees
WHERE department_id=50
ORDER BY salary DESC
-- 排序的依据可以指定多项
-- 查询所有员工的信息【不包含没有部门的员工】
-- 然后对查询的结果按照部门编号进行升序排列，部门编号相同的记录
-- 再按照工资降序排列
-- 第二排序依据针对的是第一排序字段值相同的那些记录
SELECT *
FROM employees
WHERE department_id IS NOT NULL
ORDER BY department_id , salary DESC

limit关键字

-- 查询工资最高的前三名员工的信息
-- 使用limit n 短语限定只取结果的前n条记录
-- limit m,n m表示跳过前m条 n表示截取的连续的记录条数
-- 如果m为0 可以省略不写
SELECT *
FROM employees
ORDER BY salary DESC
LIMIT 3
-- 查询工资第四名的员工信息
SELECT *
FROM employees
ORDER BY salary DESC
LIMIT 3,1

mysql的内置函数

-- 函数：类似于JAVA中方法，用来对某一个计算过程进行封装
-- 函数的调用语法：函数名(参数列表,....) 得到返回的结果
-- 内置函数的分类：
-- 数值类【了解】：用来对数值进行计算的函数
SELECT ROUND(12.34567,3)-- 四舍五入
SELECT FLOOR(3.5) -- 向下取整
SELECT CEILING(4.8)-- 向上取整
SELECT SQRT(49)

文本类函数

LENGTH (截取文本左边的部分内容)

SELECT LENGTH('abcdefg11')
SELECT LENGTH(first_name) FROM employees
SELECT LEFT(phone_number,3) FROM employees
 -- 截取文本左边的部分内容
SELECT * FROM employees WHERE LEFT(phone_number,3)='650'

相加和拼接

SELECT '12' + '34' -- mysql中的+一定是进行相加运算
SELECT CONCAT('12','34') -- 文本的拼接运算
SELECT employee_id,CONCAT(first_name,'-' ,last_name) AS full_name
FROM employees

日期类

SELECT NOW() -- 获取数据库服务器当前系统的日期时间
SELECT CURDATE() -- 获取系统当前的日期
SELECT CURTIME() -- 获取系统当前的时间
SELECT YEAR(NOW()) -- 获取指定日期的年份 结果为一个数值
SELECT YEAR('1998-10-05')
SELECT employee_id ,YEAR(hiredate) AS hire_year
FROM employees
WHERE department_id=50
SELECT MONTH(NOW())
SELECT DAY(NOW())
SELECT DAYOFWEEK(NOW())-- 获取星期

DATEDIFF(日期相减)

SELECT DATEDIFF('2019-10-09','2020-10-09') -- 两个日期的相减
-- 查询最近一周的入职员工信息
SELECT *
FROM employees
WHERE DATEDIFF(NOW(),hiredate)<=7

DATE_ADD（给指定日期添加指定时间）

语法：DATE_ADD(date,INTERVAL expr type)
date:参数是合法的日期表达式,指定的日期，即要被操作的日期
INTERVAL：关键字（间隔）
expr: 参数是您希望添加的时间间隔。表达式，可以是数字，也可以是表达式
type：类型，例如 year，day

type值
day
MICROSECOND
SECOND
MINUTE
HOUR
DAY
WEEK
MONTH
QUARTER
YEAR
SECOND_MICROSECOND
MINUTE_MICROSECOND
MINUTE_SECOND
HOUR_MICROSECOND
HOUR_SECOND
HOUR_MINUTE
DAY_MICROSECOND
DAY_SECOND
DAY_MINUTE
DAY_HOUR
YEAR_MONTH

-- 查询入职已满10年的员工信息
SELECT *
FROM employees
WHERE DATE_ADD(hiredate,INTERVAL 10 YEAR)<=NOW()

流程处理函数

IF()

IF(条件表达式,值1,值2)

-- 需求：查询每个员工的姓名、工资以及对应的工资等级【高 一般】
SELECT CONCAT(first_name,'-',last_name) AS full_name,
salary,IF(salary>=10000,'高','一般') AS sal_level
FROM employees
-- 查询每个员工的编号和绩效【】
SELECT employee_id,IF(commission_pct IS NULL,0.0,commission_pct)
AS commission_pct
FROM employees
-- 简化版本[针对为null的条件]
-- 如果指定的字段值为null则取第二个参数的值，否则取该字段原来的值
SELECT employee_id,IFNULL(commission_pct,0.0) AS commission_pct
FROM employees

CASE

CASE
	WHEN 条件表达式 THEN 返回的结果
	ELSE  返回的结果
END

1.简单Case函数

CASE sex	
     WHEN '1' THEN '男'
     WHEN '2' THEN '女' ELSE '其他' END

2.Case搜索函数

CASE WHEN sex = '1' THEN '男'
     WHEN sex = '2' THEN '女'ELSE '其他' END

3.简单case函数 VS case搜索函数
这两种方式，可以实现相同的功能。简单Case函数的写法相对比较简洁，但是和Case搜索函数相比，功能方面会有些限制，比如写判断式。
还有一个需要注意的问题，Case函数只返回第一个符合条件的值，剩下的Case部分将会被自动忽略。
–比如说，下面这段SQL，你永远无法得到"第二类"这个结果

CASE 
	WHEN col_1 IN ( 'a', 'b') THEN '第一类'
	WHEN col_1 IN ('a')  THEN '第二类'
	ELSE'其他' END


-- 查询员工姓名和工资等级
-- 20000以上A 15000 -20000 B 10000-15000 C 5000-10000D 5000以下E
SELECT CONCAT(first_name,'-',last_name) AS full_name,
salary,
CASE
WHEN salary>=20000 THEN 'A'
WHEN salary>=15000 THEN 'B'
WHEN salary>=10000 THEN 'C'
WHEN salary>=5000 THEN 'D'
ELSE 'E'
END AS sal_level
FROM employees

mysql聚合函数的使用

mysql的内置函数分为两大类：
1.单值函数:针对某一列下面的每个值进行分别的计算
2.聚合函数将某个字段下的所有值聚合到一起进行一次计算
mysql中一共提供了5个聚合函数:sum 求和 avg 求平均值 max 求最大 min
求最小值 count 统计个数

sum(求和)

-- 查询所有50号部门的员工的总工资【求和计算】
SELECT SUM(salary) AS sum_sal
FROM employees
WHERE department_id=50

Avg（求平均值）

AVG 函数返回数值列的平均值,NULL 值不包括在计算中。
– 注意：默认情况下在进行avg平均值计算的时候会
– 自动的将为null的记录排除在外
SQL AVG() 语法

SELECT AVG(column_name) FROM table_name

*avg是写在SELECT 后面的，可以使用AS为求出的结果列命名
*

-- 统计SH_CLERK工种的员工的平均工资
-- 注意：select后面的内容最后执行
SELECT AVG(salary) AS avg_sal
FROM employees
WHERE job_id='SH_CLERK'
-- 查询所有学生的考试平均分
-- 注意：默认情况下在进行avg平均值计算的时候会
-- 自动的将为null的记录排除在外
SELECT AVG(score) AS avg_score
FROM scores
-- 希望按照这种方式：如果没有参加考试按0分处理
-- 使用if语句使当scores为null时可以得到0来参加平均值的计算
SELECT AVG(IFNULL(score,0)) AS avg_score
FROM scores

min和max

-- 查询所有员工的最高工资和最低工资
SELECT MAX(salary) AS max_sal,MIN(salary) AS min_sal
FROM employees

count()

查询选中数据的条数



-- 统计公司PU_CLERK工种的员工人数
-- 统计记录的行数
SELECT COUNT(*)
FROM employees
WHERE job_id='PU_CLERK'
-- 推荐写法,效率更高
SELECT COUNT(1)
FROM employees
WHERE job_id='PU_CLERK'
-- 统计参加考试的学生人数[如果指定的字段值为null则不参与个数统计]
SELECT COUNT(score) FROM scores
-- 统计80号部门的所有员工有几种工种
SELECT COUNT(DISTINCT job_id)
FROM employees
WHERE department_id=80

分组聚合(GROUP BY)

group by 针对的是分组后得到的表进行筛选，where是对分组前的原表进行筛选，因此where要写在group by前面

-- 分组聚合
-- 需求：查询每个部门的平均工资【分类汇总】
-- 步骤描述：
-- 1.先对整个表按照指定的列进行分组操作
-- 2.对分组得到的每一组进行分别的汇总计算
-- 3.将每一组的统计结果汇总到一个结果表中
-- SQL语句中通过使用group by 分组的依据【字段】进行分组操作
SELECT department_id,AVG(salary) AS avg_sal
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id
-- 查询平均工资在5000以上的每个部门的部门编号和平均工资
-- having短语是用来对分组汇总后的结果进行进一步的筛选
-- 注意where条件和having条件的区别：
-- 1.筛选的时机不同：where的筛选发生在分组之前 having的筛选发生在分组汇总之
后
-- 2.筛选的对象不同：where 筛选针对的是原始表的每一条记录 having筛选针对的是
分组汇总的结果表
SELECT department_id,AVG(salary) AS avg_sal
FROM employees
WHERE department_id IS NOT NULL -- 对分组前的表中每一条记录进行筛选
GROUP BY department_id
HAVING avg_sal>=5000
-- 查询在2000年以后入职的工种人数在5人以上【包含】每种工种分别的员工人数
SELECT job_id,COUNT(1) AS reshu
FROM employees
WHERE YEAR(hiredate)>=2000
GROUP BY job_id
HAVING reshu>=5 -- 对组进行筛选
-- 查询至少有两名员工工资过万的每个部门的部门编号和符合条件的员工人数
SELECT department_id,COUNT(1) AS renshu
FROM employees
WHERE department_id IS NOT NULL AND salary>=10000
GROUP BY department_id
HAVING renshu>=2
-- 查询每个部门每种工种各自的人数
-- group by 后面可以指定多个分组依据
-- 第二次分组是在第一次分组的基础上
SELECT department_id,job_id,COUNT(1)
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id,job_id

高级查询（多表查询）

语法：
join 表名 on条件

select 内容 from 表1 join 表2 on 连接的条件

– SQL标准中规定两张表联接一共有两种方式【细分为4种】：
– 1.inner join：内联接【默认的联接方式】
获得两表的公共部分
– 2.left join : 左外联接
获得左表的全部，如果在右表没有对应，则获得null
– 3.right join :右外联接
获得右表的全部，如果在左表没有对应，则获得null
– 4.full join : 全外联接
获得左表和右表的独立部分，及公共部分

UNION – 将两个查询的结果和合并到一个结果中【自动排重处理】

-- 多表联接查询
-- 需求：查询每个员工的编号、姓名、电话以及对应的部门名称
-- 分析：查询的内容涉及到数据库中的两张表
-- 语法：
-- SQL99标准【推荐】：select 内容 from 表1 join 表2 on 连接的条件
-- 注意：
-- 1.如果多表联查的时候涉及到多表的相同字段，需要通过指定表名加以限定
SELECT e.employee_id,CONCAT(e.first_name,'-',e.last_name) AS
full_name,e.phone_number,e.department_id,d.department_name
FROM employees e JOIN departments d
ON e.department_id=d.department_id
-- 2. 在查询语句中我们可以使用AS短语为表在查询语句中指定一个简单形式的别名
-- SQL92的标准：select 内容 from 表1 , 表2 where 连接的条件
SELECT employee_id,CONCAT(first_name,'-',last_name) AS
full_name,phone_number,department_name
FROM employees , departments
WHERE employees.department_id=departments.department_id
-- 多表联查的过程【原理】
-- 查询每个员工的姓名 性别 出生日期以及对应的部门信息
-- join 短语在SQL语句中描述的是一种运算【笛卡尔积】
-- 笛卡尔积【A表*B表】：将A表的每一条记录依次和B表的每一条记录进行组合
查询AB的公有部分 对应为内联接
-- 然后通过on短语指定一个条件删除无效的笛卡尔积结果
SELECT e.name,e.gender,e.birth,d.deptname
FROM employees e JOIN departments d
ON e.deptid=d.deptid
-- 多条联接的方式：
-- SQL标准中规定两张表联接一共有两种方式【细分为4种】：
-- 1.inner join：内联接 【默认的联接方式】
-- 2.left join : 左外联接
-- 3.right join :右外联接
-- 4.full join : 全外联接
-- 联接方式说明：
==================================================================
-- inner join 的特点：严格的按照on指定的条件进行筛选
-- 对于两表独有的数据不会出现在查询结果中
SELECT *
FROM employees e INNER JOIN departments d
ON e.deptid=d.deptid
-- 查询所有员工以及对应的部门信息，要求每个员工必须全部出现在查询结果中
-- 可以保证左表的所有记录全部出现在查询结果中，对于右表中字段填NULL
SELECT *
FROM employees e LEFT JOIN departments d
ON e.deptid=d.deptid
-- 查询所有的部门以及对应的员工信息
SELECT *
FROM employees e RIGHT JOIN departments d
ON e.deptid=d.deptid
SELECT *
FROM departments d LEFT JOIN employees e
ON e.deptid=d.deptid
查询AB的公有+A的独有部分 对应为左外连接
查询AB的公有+B的独有 对应的右外联接方式
SELECT *
FROM employees e INNER JOIN departments d
ON e.deptid=d.deptid
SELECT *
FROM employees e LEFT JOIN departments d
ON e.deptid=d.deptid
SELECT *
FROM employees e RIGHT JOIN departments d
ON e.deptid=d.deptid
查询A表的独有部分
查询B表的独有部分
查询AB的公有+A的独有+B的独有
SELECT *
FROM employees e LEFT JOIN departments d
ON e.deptid=d.deptid
WHERE d.deptid IS NULL
SELECT *
FROM employees e RIGHT JOIN departments d
ON e.deptid=d.deptid
WHERE e.id IS NULL
注意：mysql不支持full join 全外联接，它是通过左外和右外结果的联合union实现
全外的效果
查询A的独有+B的独有
SELECT *
FROM employees e LEFT JOIN departments d
ON e.deptid=d.deptid
UNION -- 将两个查询的结果和合并到一个结果中【自动排重处理】
SELECT *
FROM employees e RIGHT JOIN departments d
ON e.deptid=d.deptid
SELECT *
FROM employees e LEFT JOIN departments d
ON e.deptid=d.deptid
WHERE d.deptid IS NULL
UNION
SELECT *
FROM employees e RIGHT JOIN departments d
ON e.deptid=d.deptid
WHERE e.id IS NULL
-- 查询50号部门的所有员工的编号 姓名 邮箱 以及部门名称和工种名称
-- 以上查询涉及到3张表的联查
SELECT
e.employee_id,e.first_name,e.email,d.department_name,j.job_title
FROM employees e JOIN departments d
ON e.department_id=d.department_id
JOIN jobs j
ON e.job_id=j.job_id
WHERE e.department_id=50
-- 查询每个员工的姓名以及他的上级领导的姓名
-- 自联接
SELECT CONCAT(e1.first_name,'-',e1.last_name) AS emp_name,
CONCAT(e2.first_name,'-',e2.last_name) AS leader_name
FROM employees e1 LEFT JOIN employees e2
ON e1.manager_id=e2.employee_id
=====================================================================
-- 子查询：在一个查询的某个部分【select from where having】又完整的包含
了另一个查询，这样就形成了两个查询之间的嵌套，分外层查询和内层查询，按照内外查询
之间的关系又可以将所有的子查询分为两种：
-- 1.非相关子查询
-- 案例1：查询比Abel工资高的员工编号、姓名和工资
-- 第一步：先查询名字叫Abel的员工工资
SELECT salary FROM employees WHERE last_name ='Abel'
-- 第二步：根据第一步的结果进行条件筛选
SELECT *
FROM employees
WHERE salary>11000
-- 使用子查询的语法实现：先执行内层查询【子查询】
SELECT *
FROM employees
WHERE salary>
(SELECT salary FROM employees WHERE last_name ='Abel')
-- 注意：如果是将子查询的结果参与到外层查询条件的> < >= = <= <>
的比较时，要
求子查询的结果
-- 必须返回一个值，否则报错：Subquery returns more than 1 row
-- 练习：查询工资最低的员工信息（可能存在多个）
SELECT * FROM employees
WHERE salary=(
SELECT MIN(salary) FROM employees)
-- 案例2:查询平均工资高于100号部门的每个部门的部门编号和平均工资
SELECT department_id,AVG(salary) AS avg_sal
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id
HAVING avg_sal>(SELECT AVG(salary) FROM employees WHERE
department_id=100)
-- 案例3:查询location_id为1700的部门中的所有员工信息
-- 第一步：根据location_id查询在这个区域的所有部门编号
-- 第二步：根据第一步查询到的部门编号筛选在这些部门的所有员工
SELECT * FROM employees
WHERE department_id IN
(SELECT department_id FROM departments WHERE location_id=1700)
-- 查询所有的主管信息
-- 先查询所有的主管编号
SELECT * FROM employees
WHERE employee_id IN (SELECT DISTINCT manager_id FROM employees)
-- 查询所有的普通员工信息【非主管】
-- 注意：在使用not in子查询时，如果子查询的结果中包含了null值
-- 则not in 失效
SELECT * FROM employees
WHERE employee_id NOT IN
(SELECT DISTINCT manager_id FROM employees WHERE manager_id IS
NOT NULL)
-- 案例4 查询其他工种中工资比"IT_PROG"工种所有员工工资都高的员工信息
SELECT *
FROM employees
WHERE job_id <>'IT_PROG'
AND salary>(SELECT MAX(salary) FROM employees WHERE
job_id='IT_PROG')
SELECT *
FROM employees
WHERE job_id <>'IT_PROG'
AND salary>ALL(SELECT salary FROM employees WHERE
job_id='IT_PROG')
-- 案例5：查询所有部门中最低的平均工资
-- 我们可以将一个查询的结果作为另一个查询的表来使用
-- 第一步：查询每个部门的平均工资
SELECT MIN(avg_sal) FROM
(
SELECT department_id,AVG(salary) AS avg_sal
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id
)temp
-- 案例6：查询每个部门的平均工资和对应的等级
SELECT temp.department_id,temp.avg_sal,jg.grade_level FROM
(
SELECT department_id,AVG(salary) AS avg_sal
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id
)temp JOIN job_grades jg
ON temp.avg_sal BETWEEN jg.lowest_sal AND jg.highest_sal



-- 案例7:查询工资比本部门平均工资高的所有员工信息
-- 通过非相关子查询的方式实现：
-- 非相关的特点：
-- 1.内层查询不依赖外层【可以独立执行】
-- 2.在整个查询过程中内层查询只执行一次


-- 案例7:查询工资比本部门平均工资高的所有员工信息
SELECT e.*
FROM employees e
JOIN
(
SELECT department_id,AVG(salary) AS avg_sal
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id
)temp
ON e.department_id=temp.department_id
WHERE e.salary>temp.avg_sal


=====================================================================
-- 案例7:查询工资比本部门平均工资高的所有员工信息
-- 使用相关子查询的方式实现：
-- 相关子查询:
-- 1.内层查询执行的时候需要获取外层查询的某个条件【不能独立执行】
-- 2.执行多次
SELECT *
FROM employees e
WHERE salary>
(
SELECT AVG(salary)
FROM employees
WHERE department_id = e.department_id
)
-- 2.相关子查询
-- 案例8:查询每个部门的信息以及对应的员工人数（了解）
SELECT *,
(SELECT COUNT(1) FROM employees WHERE
department_id=d.department_id) AS renshu
FROM departments d
-- 案例9：查询有员工的部门信息
-- exists相关子查询【返回的结果要么是0 要么返回1】
SELECT * FROM departments d
WHERE EXISTS(SELECT * FROM employees WHERE
department_id=d.department_id)