Python: 数据库

DROP DATABASE IF EXISTS test;
CREATE DATABASE IF NOT EXISTS test CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
USE test;

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for departments
-- ----------------------------
DROP TABLE IF EXISTS `departments`;
CREATE TABLE `departments` (
  `dept_no` char(4) NOT NULL,
  `dept_name` varchar(40) NOT NULL,
  PRIMARY KEY (`dept_no`),
  UNIQUE KEY `dept_name` (`dept_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of departments
-- ----------------------------
INSERT INTO `departments` VALUES ('d009', 'Customer Service');
INSERT INTO `departments` VALUES ('d005', 'Development');
INSERT INTO `departments` VALUES ('d002', 'Finance');
INSERT INTO `departments` VALUES ('d003', 'Human Resources');
INSERT INTO `departments` VALUES ('d001', 'Marketing');
INSERT INTO `departments` VALUES ('d004', 'Production');
INSERT INTO `departments` VALUES ('d006', 'Quality Management');
INSERT INTO `departments` VALUES ('d008', 'Research');
INSERT INTO `departments` VALUES ('d007', 'Sales');

-- ----------------------------
-- Table structure for dept_emp
-- ----------------------------
DROP TABLE IF EXISTS `dept_emp`;
CREATE TABLE `dept_emp` (
  `emp_no` int(11) NOT NULL,
  `dept_no` char(4) NOT NULL,
  `from_date` date NOT NULL,
  `to_date` date NOT NULL,
  PRIMARY KEY (`emp_no`,`dept_no`),
  KEY `dept_no` (`dept_no`),
  CONSTRAINT `dept_emp_ibfk_1` FOREIGN KEY (`emp_no`) REFERENCES `employees` (`emp_no`) ON DELETE CASCADE,
  CONSTRAINT `dept_emp_ibfk_2` FOREIGN KEY (`dept_no`) REFERENCES `departments` (`dept_no`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of dept_emp
-- ----------------------------
INSERT INTO `dept_emp` VALUES ('10001', 'd005', '1986-06-26', '9999-01-01');
INSERT INTO `dept_emp` VALUES ('10002', 'd007', '1996-08-03', '9999-01-01');
INSERT INTO `dept_emp` VALUES ('10003', 'd004', '1995-12-03', '9999-01-01');
INSERT INTO `dept_emp` VALUES ('10004', 'd004', '1986-12-01', '9999-01-01');
INSERT INTO `dept_emp` VALUES ('10005', 'd003', '1989-09-12', '9999-01-01');
INSERT INTO `dept_emp` VALUES ('10006', 'd005', '1990-08-05', '9999-01-01');
INSERT INTO `dept_emp` VALUES ('10007', 'd008', '1989-02-10', '9999-01-01');
INSERT INTO `dept_emp` VALUES ('10008', 'd005', '1998-03-11', '2000-07-31');
INSERT INTO `dept_emp` VALUES ('10009', 'd006', '1985-02-18', '9999-01-01');
INSERT INTO `dept_emp` VALUES ('10010', 'd004', '1996-11-24', '2000-06-26');
INSERT INTO `dept_emp` VALUES ('10010', 'd006', '2000-06-26', '9999-01-01');
INSERT INTO `dept_emp` VALUES ('10011', 'd009', '1990-01-22', '1996-11-09');
INSERT INTO `dept_emp` VALUES ('10012', 'd005', '1992-12-18', '9999-01-01');
INSERT INTO `dept_emp` VALUES ('10013', 'd003', '1985-10-20', '9999-01-01');
INSERT INTO `dept_emp` VALUES ('10014', 'd005', '1993-12-29', '9999-01-01');
INSERT INTO `dept_emp` VALUES ('10015', 'd008', '1992-09-19', '1993-08-22');
INSERT INTO `dept_emp` VALUES ('10016', 'd007', '1998-02-11', '9999-01-01');
INSERT INTO `dept_emp` VALUES ('10017', 'd001', '1993-08-03', '9999-01-01');
INSERT INTO `dept_emp` VALUES ('10018', 'd004', '1992-07-29', '9999-01-01');
INSERT INTO `dept_emp` VALUES ('10018', 'd005', '1987-04-03', '1992-07-29');
INSERT INTO `dept_emp` VALUES ('10019', 'd008', '1999-04-30', '9999-01-01');
INSERT INTO `dept_emp` VALUES ('10020', 'd004', '1997-12-30', '9999-01-01');

-- ----------------------------
-- Table structure for employees
-- ----------------------------
DROP TABLE IF EXISTS `employees`;
CREATE TABLE `employees` (
  `emp_no` int(11) NOT NULL,
  `birth_date` date NOT NULL,
  `first_name` varchar(14) NOT NULL,
  `last_name` varchar(16) NOT NULL,
  `gender` smallint(6) NOT NULL DEFAULT 1 COMMENT 'M=1, F=2',
  `hire_date` date NOT NULL,
  PRIMARY KEY (`emp_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8; 

-- ----------------------------
-- Records of employees
-- ----------------------------
INSERT INTO `employees` VALUES ('10001', '1953-09-02', 'Georgi', 'Facello', '1', '1986-06-26');
INSERT INTO `employees` VALUES ('10002', '1964-06-02', 'Bezalel', 'Simmel', '2', '1985-11-21');
INSERT INTO `employees` VALUES ('10003', '1959-12-03', 'Parto', 'Bamford', '1', '1986-08-28');
INSERT INTO `employees` VALUES ('10004', '1954-05-01', 'Chirstian', 'Koblick', '1', '1986-12-01');
INSERT INTO `employees` VALUES ('10005', '1955-01-21', 'Kyoichi', 'Maliniak', '1', '1989-09-12');
INSERT INTO `employees` VALUES ('10006', '1953-04-20', 'Anneke', 'Preusig', '2', '1989-06-02');
INSERT INTO `employees` VALUES ('10007', '1957-05-23', 'Tzvetan', 'Zielinski', '2', '1989-02-10');
INSERT INTO `employees` VALUES ('10008', '1958-02-19', 'Saniya', 'Kalloufi', '1', '1994-09-15');
INSERT INTO `employees` VALUES ('10009', '1952-04-19', 'Sumant', 'Peac', '2', '1985-02-18');
INSERT INTO `employees` VALUES ('10010', '1963-06-01', 'Duangkaew', 'Piveteau', '2', '1989-08-24');
INSERT INTO `employees` VALUES ('10011', '1953-11-07', 'Mary', 'Sluis', '2', '1990-01-22');
INSERT INTO `employees` VALUES ('10012', '1960-10-04', 'Patricio', 'Bridgland', '1', '1992-12-18');
INSERT INTO `employees` VALUES ('10013', '1963-06-07', 'Eberhardt', 'Terkki', '1', '1985-10-20');
INSERT INTO `employees` VALUES ('10014', '1956-02-12', 'Berni', 'Genin', '1', '1987-03-11');
INSERT INTO `employees` VALUES ('10015', '1959-08-19', 'Guoxiang', 'Nooteboom', '1', '1987-07-02');
INSERT INTO `employees` VALUES ('10016', '1961-05-02', 'Kazuhito', 'Cappelletti', '1', '1995-01-27');
INSERT INTO `employees` VALUES ('10017', '1958-07-06', 'Cristinel', 'Bouloucos', '2', '1993-08-03');
INSERT INTO `employees` VALUES ('10018', '1954-06-19', 'Kazuhide', 'Peha', '2', '1987-04-03');
INSERT INTO `employees` VALUES ('10019', '1953-01-23', 'Lillian', 'Haddadi', '1', '1999-04-30');
INSERT INTO `employees` VALUES ('10020', '1952-12-24', 'Mayuko', 'Warwick', '1', '1991-01-26');

-- ----------------------------
-- Table structure for salaries
-- ----------------------------
DROP TABLE IF EXISTS `salaries`;
CREATE TABLE `salaries` (
  `emp_no` int(11) NOT NULL,
  `salary` int(11) NOT NULL,
  `from_date` date NOT NULL,
  `to_date` date NOT NULL,
  PRIMARY KEY (`emp_no`,`from_date`),
  CONSTRAINT `salaries_ibfk_1` FOREIGN KEY (`emp_no`) REFERENCES `employees` (`emp_no`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of salaries
-- ----------------------------
INSERT INTO `salaries` VALUES ('10001', '60117', '1986-06-26', '1987-06-26');
INSERT INTO `salaries` VALUES ('10001', '62102', '1987-06-26', '1988-06-25');
INSERT INTO `salaries` VALUES ('10001', '66074', '1988-06-25', '1989-06-25');
INSERT INTO `salaries` VALUES ('10001', '66596', '1989-06-25', '1990-06-25');
INSERT INTO `salaries` VALUES ('10001', '66961', '1990-06-25', '1991-06-25');
INSERT INTO `salaries` VALUES ('10001', '71046', '1991-06-25', '1992-06-24');
INSERT INTO `salaries` VALUES ('10001', '74333', '1992-06-24', '1993-06-24');
INSERT INTO `salaries` VALUES ('10001', '75286', '1993-06-24', '1994-06-24');
INSERT INTO `salaries` VALUES ('10001', '75994', '1994-06-24', '1995-06-24');
INSERT INTO `salaries` VALUES ('10001', '76884', '1995-06-24', '1996-06-23');
INSERT INTO `salaries` VALUES ('10001', '80013', '1996-06-23', '1997-06-23');
INSERT INTO `salaries` VALUES ('10001', '81025', '1997-06-23', '1998-06-23');
INSERT INTO `salaries` VALUES ('10001', '81097', '1998-06-23', '1999-06-23');
INSERT INTO `salaries` VALUES ('10001', '84917', '1999-06-23', '2000-06-22');
INSERT INTO `salaries` VALUES ('10001', '85112', '2000-06-22', '2001-06-22');
INSERT INTO `salaries` VALUES ('10001', '85097', '2001-06-22', '2002-06-22');
INSERT INTO `salaries` VALUES ('10001', '88958', '2002-06-22', '9999-01-01');
INSERT INTO `salaries` VALUES ('10002', '65828', '1996-08-03', '1997-08-03');
INSERT INTO `salaries` VALUES ('10002', '65909', '1997-08-03', '1998-08-03');
INSERT INTO `salaries` VALUES ('10002', '67534', '1998-08-03', '1999-08-03');
INSERT INTO `salaries` VALUES ('10002', '69366', '1999-08-03', '2000-08-02');
INSERT INTO `salaries` VALUES ('10002', '71963', '2000-08-02', '2001-08-02');
INSERT INTO `salaries` VALUES ('10002', '72527', '2001-08-02', '9999-01-01');
INSERT INTO `salaries` VALUES ('10003', '40006', '1995-12-03', '1996-12-02');
INSERT INTO `salaries` VALUES ('10003', '43616', '1996-12-02', '1997-12-02');
INSERT INTO `salaries` VALUES ('10003', '43466', '1997-12-02', '1998-12-02');
INSERT INTO `salaries` VALUES ('10003', '43636', '1998-12-02', '1999-12-02');
INSERT INTO `salaries` VALUES ('10003', '43478', '1999-12-02', '2000-12-01');
INSERT INTO `salaries` VALUES ('10003', '43699', '2000-12-01', '2001-12-01');
INSERT INTO `salaries` VALUES ('10003', '43311', '2001-12-01', '9999-01-01');
INSERT INTO `salaries` VALUES ('10004', '40054', '1986-12-01', '1987-12-01');
INSERT INTO `salaries` VALUES ('10004', '42283', '1987-12-01', '1988-11-30');
INSERT INTO `salaries` VALUES ('10004', '42542', '1988-11-30', '1989-11-30');
INSERT INTO `salaries` VALUES ('10004', '46065', '1989-11-30', '1990-11-30');
INSERT INTO `salaries` VALUES ('10004', '48271', '1990-11-30', '1991-11-30');
INSERT INTO `salaries` VALUES ('10004', '50594', '1991-11-30', '1992-11-29');
INSERT INTO `salaries` VALUES ('10004', '52119', '1992-11-29', '1993-11-29');
INSERT INTO `salaries` VALUES ('10004', '54693', '1993-11-29', '1994-11-29');
INSERT INTO `salaries` VALUES ('10004', '58326', '1994-11-29', '1995-11-29');
INSERT INTO `salaries` VALUES ('10004', '60770', '1995-11-29', '1996-11-28');

-- ----------------------------
-- Table structure for titles
-- ----------------------------
DROP TABLE IF EXISTS `titles`;
CREATE TABLE `titles` (
  `emp_no` int(11) NOT NULL,
  `title` varchar(50) NOT NULL,
  `from_date` date NOT NULL,
  `to_date` date DEFAULT NULL,
  PRIMARY KEY (`emp_no`,`title`,`from_date`),
  CONSTRAINT `titles_ibfk_1` FOREIGN KEY (`emp_no`) REFERENCES `employees` (`emp_no`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of titles
-- ----------------------------
INSERT INTO `titles` VALUES ('10001', 'Senior Engineer', '1986-06-26', '9999-01-01');
INSERT INTO `titles` VALUES ('10002', 'Staff', '1996-08-03', '9999-01-01');
INSERT INTO `titles` VALUES ('10003', 'Senior Engineer', '1995-12-03', '9999-01-01');
INSERT INTO `titles` VALUES ('10004', 'Engineer', '1986-12-01', '1995-12-01');
INSERT INTO `titles` VALUES ('10004', 'Senior Engineer', '1995-12-01', '9999-01-01');
INSERT INTO `titles` VALUES ('10005', 'Senior Staff', '1996-09-12', '9999-01-01');
INSERT INTO `titles` VALUES ('10005', 'Staff', '1989-09-12', '1996-09-12');
INSERT INTO `titles` VALUES ('10006', 'Senior Engineer', '1990-08-05', '9999-01-01');
INSERT INTO `titles` VALUES ('10007', 'Senior Staff', '1996-02-11', '9999-01-01');
INSERT INTO `titles` VALUES ('10007', 'Staff', '1989-02-10', '1996-02-11');
INSERT INTO `titles` VALUES ('10008', 'Assistant Engineer', '1998-03-11', '2000-07-31');
INSERT INTO `titles` VALUES ('10009', 'Assistant Engineer', '1985-02-18', '1990-02-18');
INSERT INTO `titles` VALUES ('10009', 'Engineer', '1990-02-18', '1995-02-18');
INSERT INTO `titles` VALUES ('10009', 'Senior Engineer', '1995-02-18', '9999-01-01');
INSERT INTO `titles` VALUES ('10010', 'Engineer', '1996-11-24', '9999-01-01');
INSERT INTO `titles` VALUES ('10011', 'Staff', '1990-01-22', '1996-11-09');
INSERT INTO `titles` VALUES ('10012', 'Engineer', '1992-12-18', '2000-12-18');
INSERT INTO `titles` VALUES ('10012', 'Senior Engineer', '2000-12-18', '9999-01-01');
INSERT INTO `titles` VALUES ('10013', 'Senior Staff', '1985-10-20', '9999-01-01');
INSERT INTO `titles` VALUES ('10014', 'Engineer', '1993-12-29', '9999-01-01');
INSERT INTO `titles` VALUES ('10015', 'Senior Staff', '1992-09-19', '1993-08-22');

-- 返回5条记录， [1,5]左闭右闭
SELECT * FROM employees as emp LIMIT 5;

-- 返回5条记录，偏移3条，limit3, 5 ---  (3,3+5]左开右闭 start:end start:len 分页用到
SELECT * FROM employees as emp LIMIT 3, 5;
-- 等价于
SELECT * FROM employees as emp LIMIT 5 offset 3; 

--  条件
SELECT emps.emp_no as id, last_name FROM employees emps WHERE not (emps.emp_no = 10008 or emps.emp_no = 10010);
SELECT emps.emp_no as id, last_name FROM employees emps WHERE not emp_no <> 10008 and emp_no <> 10010;
SELECT emps.emp_no as id, last_name FROM employees emps WHERE not emp_no in (10008, 10010);
SELECT emps.emp_no as id, last_name FROM employees emps WHERE not emp_no not in (10008, 10010);
-- 范围
SELECT * FROM employees WHERE emp_no < 10015 and last_name LIKE 'P%';  -- mysql中str默认忽略大小写
SELECT * FROM employees WHERE emp_no < 10015 and last_name LIKE 'P__%';  -- 下划线 _ 代表单个匹配
SELECT * FROM employees WHERE emp_no BETWEEN 10010 AND 10015 AND last_name LIKE 'P%';

EXPLAIN SELECT emp_no AS id, birth_date, concat(FIRST_name, ' ', last_name) as name
  FROM employees as emp WHERE last_name='Sluis';


EXPLAIN SELECT emp_no AS id, birth_date, concat(FIRST_name, ' ', last_name) as name
  FROM employees as emp WHERE emp_no BETWEEN 10010 and 10015 and last_name='Sluis';

NULL >system > const >eq_ref > ref > fulltext > ref_or_null > index_merge >unique_subquery > index_subquery > range >index > ALL //最好到最差
备注：掌握以下10种常见的即可
NULL >system > const >eq_ref >ref > ref_or_null > index_merge >range >index > ALL

-- 升序
SELECT * FROM employees WHERE emp_no in (10001, 10002, 10010) ORDER BY emp_no;

-- 降序
SELECT * FROM employees WHERE emp_no in (10001, 10002, 10010) ORDER BY emp_no DESC

-- OROER BY 先执行，在执行 LIMIT
SELECT * FROM employees WHERE emp_no in (10001, 10002, 10010) 
 ORDER BY birth_date, emp_no DESC LIMIT 1, 2;  -- 以这2个字段排序，其中以birth_date升序，emp_no 降序

SELECT DISTINCT dept_no FROM dept_emp;

SELECT DISTINCT emp_no FROM dept_emp;

SELECT dept_no,emp_no FROM dept_emp;
-- 会将dept_no与emp_no看成二元祖来筛选

-- 聚合为一行
SELECT COUNT(*), AVG(emp_no), sum(emp_no), min(emp_no), max(emp_no) FROM employees;

mysql> SELECT COUNT(*), AVG(emp_no), sum(emp_no), min(emp_no), max(emp_no) FROM employees;
+----------+-------------+-------------+-------------+-------------+
| COUNT(*) | AVG(emp_no) | sum(emp_no) | min(emp_no) | max(emp_no) |
+----------+-------------+-------------+-------------+-------------+
|       20 |  10010.5000 |      200210 |       10001 |       10020 |
+----------+-------------+-------------+-------------+-------------+

SELECT emp_no, COUNT( emp_no ), sum( emp_no ), avg( emp_no ) AS sal_avg 
  FROM employees  WHERE emp_no > 10001 
  GROUP BY emp_no HAVING sal_avg > 10005
  ORDER BY sal_avg DESC LIMIT 1;

-- ----------------------------
-- Table structure for salaries
-- ----------------------------
DROP TABLE IF EXISTS `salaries`;
CREATE TABLE `salaries` (
  `emp_no` int(11) NOT NULL,
  `salary` int(11) NOT NULL,
  `from_date` date NOT NULL,
  `to_date` date NOT NULL,
  PRIMARY KEY (`emp_no`,`from_date`),
  CONSTRAINT `salaries_ibfk_1` FOREIGN KEY (`emp_no`) REFERENCES `employees` (`emp_no`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of salaries
-- ----------------------------
INSERT INTO `salaries` VALUES ('10001', '60117', '1986-06-26', '1987-06-26');
INSERT INTO `salaries` VALUES ('10001', '62102', '1987-06-26', '1988-06-25');
INSERT INTO `salaries` VALUES ('10001', '66074', '1988-06-25', '1989-06-25');
INSERT INTO `salaries` VALUES ('10001', '66596', '1989-06-25', '1990-06-25');
INSERT INTO `salaries` VALUES ('10001', '66961', '1990-06-25', '1991-06-25');
INSERT INTO `salaries` VALUES ('10001', '71046', '1991-06-25', '1992-06-24');
INSERT INTO `salaries` VALUES ('10001', '74333', '1992-06-24', '1993-06-24');
INSERT INTO `salaries` VALUES ('10001', '75286', '1993-06-24', '1994-06-24');
INSERT INTO `salaries` VALUES ('10001', '75994', '1994-06-24', '1995-06-24');
INSERT INTO `salaries` VALUES ('10001', '76884', '1995-06-24', '1996-06-23');
INSERT INTO `salaries` VALUES ('10001', '80013', '1996-06-23', '1997-06-23');
INSERT INTO `salaries` VALUES ('10001', '81025', '1997-06-23', '1998-06-23');
INSERT INTO `salaries` VALUES ('10001', '81097', '1998-06-23', '1999-06-23');
INSERT INTO `salaries` VALUES ('10001', '84917', '1999-06-23', '2000-06-22');
INSERT INTO `salaries` VALUES ('10001', '85112', '2000-06-22', '2001-06-22');
INSERT INTO `salaries` VALUES ('10001', '85097', '2001-06-22', '2002-06-22');
INSERT INTO `salaries` VALUES ('10001', '88958', '2002-06-22', '9999-01-01');
INSERT INTO `salaries` VALUES ('10002', '65828', '1996-08-03', '1997-08-03');
INSERT INTO `salaries` VALUES ('10002', '65909', '1997-08-03', '1998-08-03');
INSERT INTO `salaries` VALUES ('10002', '67534', '1998-08-03', '1999-08-03');
INSERT INTO `salaries` VALUES ('10002', '69366', '1999-08-03', '2000-08-02');
INSERT INTO `salaries` VALUES ('10002', '71963', '2000-08-02', '2001-08-02');
INSERT INTO `salaries` VALUES ('10002', '72527', '2001-08-02', '9999-01-01');
INSERT INTO `salaries` VALUES ('10003', '40006', '1995-12-03', '1996-12-02');
INSERT INTO `salaries` VALUES ('10003', '43616', '1996-12-02', '1997-12-02');
INSERT INTO `salaries` VALUES ('10003', '43466', '1997-12-02', '1998-12-02');
INSERT INTO `salaries` VALUES ('10003', '43636', '1998-12-02', '1999-12-02');
INSERT INTO `salaries` VALUES ('10003', '43478', '1999-12-02', '2000-12-01');
INSERT INTO `salaries` VALUES ('10003', '43699', '2000-12-01', '2001-12-01');
INSERT INTO `salaries` VALUES ('10003', '43311', '2001-12-01', '9999-01-01');
INSERT INTO `salaries` VALUES ('10004', '40054', '1986-12-01', '1987-12-01');
INSERT INTO `salaries` VALUES ('10004', '42283', '1987-12-01', '1988-11-30');
INSERT INTO `salaries` VALUES ('10004', '42542', '1988-11-30', '1989-11-30');
INSERT INTO `salaries` VALUES ('10004', '46065', '1989-11-30', '1990-11-30');
INSERT INTO `salaries` VALUES ('10004', '48271', '1990-11-30', '1991-11-30');
INSERT INTO `salaries` VALUES ('10004', '50594', '1991-11-30', '1992-11-29');
INSERT INTO `salaries` VALUES ('10004', '52119', '1992-11-29', '1993-11-29');
INSERT INTO `salaries` VALUES ('10004', '54693', '1993-11-29', '1994-11-29');
INSERT INTO `salaries` VALUES ('10004', '58326', '1994-11-29', '1995-11-29');
INSERT INTO `salaries` VALUES ('10004', '60770', '1995-11-29', '1996-11-28');

-- 聚合所有
SELECT SUM(salary), avg(salary), count(emp_no) from salaries;
-- 聚合被选择的记录
SELECT SUM(salary), avg(salary), count(emp_no) from salaries WHERE emp_no < 10003;

-- 分组
SELECT emp_no FROM salaries GROUP BY emp_no;
SELECT emp_no FROM salaries WHERE emp_no < 10003 GROUP BY emp_no;

-- 按照不同emp_no分组，每组分别聚合
SELECT emp_no, SUM(salary), AVG(salary), count(emp_no) 
  from salaries WHERE emp_no < 10003 GROUP BY emp_no;

-- HAVING子句对分组结果过滤
SELECT emp_no, SUM(salary), AVG(salary), count(emp_no) 
  from salaries GROUP BY emp_no HAVING AVG(salary) > 45000 -- 员工平均工资大于45000的有谁
-- 使用别名
SELECT emp_no, SUM(salary), AVG(salary) as sal_avg, count(emp_no) 
  from salaries GROUP BY emp_no HAVING sal_avg > 45000

-- 最后对分组过滤后的结果排序
SELECT emp_no, SUM(salary), AVG(salary) AS sal_avg, COUNT(emp_no) 
  from salaries GROUP BY emp_no HAVING sal_avg > 60000 ORDER BY sal_avg; 

#聚合所有
mysql> SELECT SUM(salary), avg(salary), count(emp_no) from salaries;
+-------------+-------------+---------------+
| SUM(salary) | avg(salary) | count(emp_no) |
+-------------+-------------+---------------+
|     2491668 |  62291.7000 |            40 |
+-------------+-------------+---------------+

#聚合被选择的记录
mysql> SELECT SUM(salary), avg(salary), count(emp_no) from salaries WHERE emp_no < 10003;
+-------------+-------------+---------------+
| SUM(salary) | avg(salary) | count(emp_no) |
+-------------+-------------+---------------+
|     1694739 |  73684.3043 |            23 |
+-------------+-------------+---------------+

#分组查询
mysql> select emp_no from salaries group by emp_no;
+--------+
| emp_no |
+--------+
|  10001 |
|  10002 |
|  10003 |
|  10004 |
+--------+
mysql> SELECT emp_no FROM salaries WHERE emp_no < 10003 GROUP BY emp_no;
+--------+
| emp_no |
+--------+
|  10001 |
|  10002 |
+--------+

#按照不同emp_no分组，每组分别聚合
#分组不管是否聚合，最后每组出一行
mysql> SELECT emp_no,sum(salary) FROM salaries WHERE emp_no in (10002,10004) GROUP BY emp_no;
+--------+-------------+
| emp_no | sum(salary) |
+--------+-------------+
|  10002 |      413127 |
|  10004 |      495717 |
+--------+-------------+
mysql> SELECT emp_no, SUM(salary) sum, AVG(salary) avg, count(emp_no) cnt from salaries WHERE emp_no < 10003 GROUP BY emp_no;
+--------+---------+------------+-----+
| emp_no | sum     | avg        | cnt |
+--------+---------+------------+-----+
|  10001 | 1281612 | 75388.9412 |  17 |
|  10002 |  413127 | 68854.5000 |   6 |
+--------+---------+------------+-----+

-- 分组
SELECT emp_no, MAX(salary) FROM salaries; -- 10001 88958
SELECT emp_no, MIN(salary) FROM salaries; -- 10001 40006

SELECT emp_no, MIN(salary) FROM salaries GROUP BY emp_no;

SELECT
  emp_no,
  AVG( salary ) AS avg_sal 
FROM
  salaries 
WHERE
  salary > 70000 
GROUP BY
  emp_no 
HAVING
  avg( salary ) > 50000 
ORDER BY
  avg_sal DESC 
  LIMIT 1;

SELECT
  * 
FROM
  employees 
WHERE
  emp_no IN ( SELECT emp_no FROM employees WHERE emp_no > 10015 ) 
ORDER BY
  emp_no DESC;


-- 当表用
SELECT
  emp.emp_no,
  emp.first_name,
  gender 
FROM
  ( SELECT * FROM employees WHERE emp_no > 10015 ) AS emp 
WHERE
  emp.emp_no < 10019 
ORDER BY
  emp_no DESC;

-- 工资40行
SELECT * FROM salaries;
-- 20行
SELECT * FROM employees;
-- 800行
SELECT * FROM employees CROSS JOIN salaries;
-- 隐式连接，800行
SELECT * FROM employees, salaries;

-- 内连接，笛卡尔乘积 800 行
SELECT * FROM employees JOIN salaries;
SELECT * FROM employees INNER JOIN salaries;

-- on等值连接 40行
SELECT * FROM employees JOIN salaries ON employees.emp_no = salaries.emp_no;
-- 自然连接，去掉了重复列，且自行使用 employees.emp_no = salaries.emp_no的条件
SELECT * FROM employees NATURAL JOIN salaries;

-- 左连接 （56条记录）
SELECT * FROM employees LEFT JOIN salaries ON employees.emp_no = salaries.emp_no;
-- 右连接 （40 条记录）
SELECT * FROM employees RIGHT JOIN salaries ON employees.emp_no = salaries.emp_no;
-- 这个右连接等价于上面的左连接（56条记录）
SELECT * FROM salaries RIGHT JOIN employees ON employees.emp_no = salaries.emp_no;

SELECT
  employees.*
FROM
  salaries
  RIGHT JOIN employees ON employees.emp_no = salaries.emp_no 
WHERE
  salaries.emp_no IS NULL

select manager.* from emp manager,emp worker where 
manaer.empno=worker.mgr and worker.empno=1;

select manager.* from emp manager inner join emp 
worker on manaer.empno=worker.mgr where worker.empno=1;

/*
假设有表manager，字段和记录如下
empno  name  mgr
1      tom  
2      jerry 1
3      ben   2
*/

-- 有领导的员工
select * from manager where mgr is not null

-- 所有有领导的员工及领导名字
select worker.*, mgr.name from manager worker INNER JOIN manager mgr ON mgr.id = worker.mgr

SELECT Name,Age FROM students UNION SELECT Name,Age FROM teachers;

Cursor.execute(query, args=None)

import pymysql
import simplejson #pip install simplejson
import os
from pymysql.cursors import DictCursor

with open(os.path.join(os.path.dirname(__file__), 'config.json')) as f:
    conf = simplejson.load(f)  #反序列化，字典
print(conf)

#connection #从配置文件来. ini, json
#user = None, password = "",host = None,database = None,port = 0,
conn = None
cursor = None
try:
    conn = pymysql.connect(**conf)
    #cursor 游标
    cursor = conn.cursor(DictCursor)

    userid = input('>>>') # 2 or 1=1
    print(type(userid), userid)
    # sql = "select * from student where name = '{}'".format(userid)
    # print(sql)
    #参数化查询，sql字符串必须使用c printf风格
    sql = "select * from student where id=%s"
    #会转换成 select * from student where id='2 or 1=1'
    x = cursor.execute(sql, userid) #args 可以是元组、列表、字典

    print(cursor.fetchall())

finally:
    if cursor:
        cursor.close()
    if conn:
        conn.close()

#返回结果
{'host': '192.168.226.128', 'password': '123456', 'database': 'test', 'user': 'root', 'port': 3306}
>>>2 or 1=1
<class 'str'> 2 or 1=1
[{'id': 2, 'name': 'tom', 'age': 18}]


#sql执行
mysql> select * from student where id='2 or 1=1'
    -> ;
+----+------+------+
| id | name | age  |
+----+------+------+
|  2 | tom  |   18 |
+----+------+------+

import pymysql
from pymysql.cursors import DictCursor

IP = '192.168.1.6'
USERNAME = 'lqx'
PASSWORD = 'lqx'
DBNAME ='test'
PORT = 3306

conn = None
currsor = None # 对结果集的操作
try:
    conn = pymysql.connect(IP, USERNAME, PASSWORD, DBNAME, PORT)
    currsor = conn.cursor()

    sql = "insert into student (name,age) values(%s, %s)"
    currsor.executemany(sql, (
        ('ben{}'.format(i), 20+i) for i in range(5)
    ))

    conn.commit()
except Exception as e:
    print(e)
    conn.rollback()
finally:
    if currsor:
        currsor.close()
    if conn:
        conn.close()

import sqlalchemy
from sqlalchemy import create_engine, Column, String, Integer
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base

IP = '192.168.1.6'
USERNAME = 'lqx'
PASSWORD = 'lqx'
DBNAME ='test'
PORT = 3306
engine = create_engine("mysql+pymysql://{}:{}@{}:{}/{}".format(
    USERNAME, PASSWORD, IP, PORT, DBNAME
), echo=True) # lazy 懒

# ORM Mapping
Base = declarative_base() # 基类

# 创建实体类
class Student(Base):
    # 指定表名
    __tablename__= 'student'
    # 定义类属性对应字段
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(64), nullable=False)
    age = Column(Integer)

    def __repr__(self):
        return "<{} id={}, name={}, age={}>".format(
            __class__.__name__, self.id, self.name, self.age
        )

# 删除表
Base.metadata.drop_all(engine)
# 创建表
Base.metadata.create_all(engine)

# 创建seesion
Session = sessionmaker(bind=engine)
session = Session()

s = Student(name='tom') # 构造时传入
s.age = 20 # 属性赋值
print(s)

session.add(s)
print(s)
session.commit()
print(s)
print('~~~~~~')

try:
    session.add_all([s])
    print(s)
    session.commit() # 能提交成功吗？
    print(s)
except:
    session.rollbake()
    print('roll back')
    raise

s.name = 'jerry' # 修改
session.add_all([s])

import sqlalchemy
from sqlalchemy import create_engine, Column, String, Integer
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base

IP = '192.168.1.6'
USERNAME = 'lqx'
PASSWORD = 'lqx'
DBNAME ='test'
PORT = 3306
engine = create_engine("mysql+pymysql://{}:{}@{}:{}/{}".format(
    USERNAME, PASSWORD, IP, PORT, DBNAME
), echo=True) # lazy 懒

# ORM Mapping
Base = declarative_base() # 基类

# 创建实体类
class Student(Base):
    # 指定表名
    __tablename__= 'student'
    # 定义类属性对应字段
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(64), nullable=False)
    age = Column(Integer)

    def __repr__(self):
        return "<{} id={}, name={}, age={}>".format(
            __class__.__name__, self.id, self.name, self.age
        )

# # 删除表
# Base.metadata.drop_all(engine)
# 创建表
# Base.metadata.create_all(engine)

# 创建seesion
Session = sessionmaker(bind=engine)
session = Session()

students = session.query(Student)
print(students.count())
for student in students:
    print(student)
print('~~~~~~~~~~~')

student = session.query(Student).get(1) # 通过主键查询
print(student)

import sqlalchemy
from sqlalchemy import create_engine, Column, String, Integer
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base

IP = '192.168.1.6'
USERNAME = 'lqx'
PASSWORD = 'lqx'
DBNAME ='test'
PORT = 3306
engine = create_engine("mysql+pymysql://{}:{}@{}:{}/{}".format(
    USERNAME, PASSWORD, IP, PORT, DBNAME
), echo=True) # lazy 懒

# ORM Mapping
Base = declarative_base() # 基类

# 创建实体类
class Student(Base):
    # 指定表名
    __tablename__= 'student'
    # 定义类属性对应字段
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(64), nullable=False)
    age = Column(Integer)

    def __repr__(self):
        return "<{} id={}, name={}, age={}>".format(
            __class__.__name__, self.id, self.name, self.age
        )

# # 删除表
# Base.metadata.drop_all(engine)
# 创建表
# Base.metadata.create_all(engine)

# 创建seesion
Session = sessionmaker(bind=engine)
session = Session()

student = session.query(Student).get(1) # 通过主键查询
print(student)
student.name = 'ben'
student.age = 30
print(student)
session.add(student)
session.commit()

1 ben 30

import sqlalchemy
from sqlalchemy import create_engine, Column, String, Integer
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base

IP = '192.168.1.6'
USERNAME = 'lqx'
PASSWORD = 'lqx'
DBNAME ='test'
PORT = 3306
engine = create_engine("mysql+pymysql://{}:{}@{}:{}/{}".format(
    USERNAME, PASSWORD, IP, PORT, DBNAME
), echo=True) # lazy 懒

# ORM Mapping
Base = declarative_base() # 基类

# 创建实体类
class Student(Base):
    # 指定表名
    __tablename__= 'student'
    # 定义类属性对应字段
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(64), nullable=False)
    age = Column(Integer)

    def __repr__(self):
        return "<{} id={}, name={}, age={}>".format(
            __class__.__name__, self.id, self.name, self.age
        )


Session = sessionmaker(bind=engine)
session = Session()

try:
    student = Student(id=1, name='ben', age=30)

    session.delete(student)
    session.commit()
except Exception as e:
    session.rollback()
    print('roll back')
    print(e)

# 执行结果
roll back
Instance '<Student at 0x1779fea9b70>' is not persisted

import sqlalchemy
from sqlalchemy import create_engine, Column, String, Integer
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm.state import InstanceState

IP = '192.168.1.6'
USERNAME = 'lqx'
PASSWORD = 'lqx'
DBNAME ='test'
PORT = 3306
engine = create_engine("mysql+pymysql://{}:{}@{}:{}/{}".format(
    USERNAME, PASSWORD, IP, PORT, DBNAME
), echo=True) # lazy 懒

# ORM Mapping
Base = declarative_base() # 基类

# 创建实体类
class Student(Base):
    # 指定表名
    __tablename__= 'student'
    # 定义类属性对应字段
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(64), nullable=False)
    age = Column(Integer)

    def __repr__(self):
        return "<{} id={}, name={}, age={}>".format(
            __class__.__name__, self.id, self.name, self.age
        )


Session = sessionmaker(bind=engine)
session = Session()

def getstate(instance, i):
    inp:InstanceState = sqlalchemy.inspect(student)
    states = "{}: key={}\nid={}, attached={}, transient={}," \
             "pending={}, \npersistant={}, deleted={}, detached={}".format(
        i ,inp.key,
        inp.session_id, inp._attached, inp.transient,
        inp.pending, inp.persistent, inp.deleted, inp.detached
    )
    print(states, end='\n~~~~~~~~~\n')

student = session.query(Student).get(1)
getstate(student, 1) # persistent

try:
    student = Student(id=1, name='ben', age=30)
    getstate(student, 2) # transit

    student = Student(name='tom', age=20)
    getstate(student, 3) # transit
    session.add(student) # add后变成pending
    getstate(student,4) # pending
    # session.delete(student) # 异常，删除的前提必须是persistent，也就是说先查后删
    session.commit() # 提交后，变成persistent
    getstate(student, 6) # persistent
except Exception as e:
    session.rollback()
    print('roll back')

import sqlalchemy
from sqlalchemy import create_engine, Column, String, Integer
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm.state import InstanceState

IP = '192.168.1.6'
USERNAME = 'lqx'
PASSWORD = 'lqx'
DBNAME ='test'
PORT = 3306
engine = create_engine("mysql+pymysql://{}:{}@{}:{}/{}".format(
    USERNAME, PASSWORD, IP, PORT, DBNAME
), echo=True) # lazy 懒

# ORM Mapping
Base = declarative_base() # 基类

# 创建实体类
class Student(Base):
    # 指定表名
    __tablename__= 'student'
    # 定义类属性对应字段
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(64), nullable=False)
    age = Column(Integer)

    def __repr__(self):
        return "<{} id={}, name={}, age={}>".format(
            __class__.__name__, self.id, self.name, self.age
        )


Session = sessionmaker(bind=engine)
session = Session()

def getstate(instance, i):
    inp:InstanceState = sqlalchemy.inspect(student)
    states = "{}: key={}\nid={}, attached={}, transient={}," \
             "pending={}, \npersistant={}, deleted={}, detached={}".format(
        i ,inp.key,
        inp.session_id, inp._attached, inp.transient,
        inp.pending, inp.persistent, inp.deleted, inp.detached
    )
    print(states, end='\n~~~~~~~~~\n')

student = session.query(Student).get(1)
getstate(student, 7) # persistent

try:
    session.delete(student) # 删除的前提是persistent

    getstate(student, 8) # persistent
    session.flush()
    getstate(student, 9) # deleted
    session.commit()
    getstate(student, 13) # detached
except Exception as e:
    session.rollback()
    print('roll back')

7: key=(<class '__main__.Student'>, (1,), None)
id=1, attached=True, transient=False,pending=False, 
persistant=True, deleted=False, detached=False
~~~~~~~~~
8: key=(<class '__main__.Student'>, (1,), None)
id=1, attached=True, transient=False,pending=False, 
persistant=True, deleted=False, detached=False
~~~~~~~~~
2019-06-27 16:49:04,554 INFO sqlalchemy.engine.base.Engine DELETE FROM student WHERE student.id = %(id)s
2019-06-27 16:49:04,554 INFO sqlalchemy.engine.base.Engine {'id': 1}
9: key=(<class '__main__.Student'>, (1,), None)
id=1, attached=True, transient=False,pending=False, 
persistant=False, deleted=True, detached=False
delete后flush，状态变成deleted，不过是附加的
~~~~~~~~~

2019-06-27 16:49:04,556 INFO sqlalchemy.engine.base.Engine COMMIT
一旦提交后
13: key=(<class '__main__.Student'>, (1,), None)
id=None, attached=False, transient=False,pending=False, 
persistant=False, deleted=False, detached=True
状态转为detached
~~~~~~~~~

import sqlalchemy
from sqlalchemy import create_engine, Column, String, Integer, Date, Enum, ForeignKey
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm.state import InstanceState
import enum

Base = declarative_base() # 基类

IP = '192.168.1.6'
USERNAME = 'lqx'
PASSWORD = 'lqx'
DBNAME ='test'
PORT = 3306
engine = create_engine("mysql+pymysql://{}:{}@{}:{}/{}".format(
    USERNAME, PASSWORD, IP, PORT, DBNAME
), echo=True)

Session = sessionmaker(bind=engine)
session = Session()

class MyEnum(enum.Enum):
    M = 'M'
    F = 'F'


class Employee(Base):
    # 指定表名
    __tablename__ = 'employees'

    # 定义类属性对应字段
    emp_no = Column(Integer, primary_key=True)
    birth_date = Column(Date, nullable=False)
    first_name = Column(String(14), nullable=False)
    last_name = Column(String(16), nullable=False)
    gender = Column(Enum(MyEnum), nullable=False)
    hire_date = Column(Date, nullable=False)

    def __repr__(self):
        return "<{} no={}, name={}, gender={}>".format(
            __class__.__name__, self.emp_no, "{} {}".format(
                self.first_name, self.last_name), self.gender.value
        )

# 打印函数
def show(emps):
    for x in emps:
        print(x)
    print('~~~~~~~~~~~~\n')

# 简单条件查询
emps = session.query(Employee).filter(Employee.emp_no > 10015)
show(emps)

#升序
emps = session.query(Employee).filter(Employee.emp_no > 10010).order_by(Employee.emp_no)
emps = session.query(Employee).filter(Employee.emp_no > 10010).order_by(Employee.emp_no.asc())
show(emps)

#降序
emps = session.query(Employee).filter(Employee.emp_no > 10010).order_by(Employee.emp_no.desc())
show(emps)

#多列排序
emps = session.query(Employee).filter(Employee.emp_no >
    10010).order_by(Employee.last_name).order_by(Employee.emp_no.desc())
show(emps)

emps = session.query(Employee).limit(4)
show(emps)

emps = session.query(Employee).limit(4).offset(18)
show(emps)

# 总行数
emps = session.query(Employee)
print(emps.count()) # 聚合函数count(*)的查询

# 取所有数据
print(emps.all()) # 返回列表，查不到返回空列表

# 取首行
print(emps.first()) # 返回首行，查不到返回None，等价limit

# 有且只能有一行
print(emps.one()) #如果查询结果是多行抛异常
print(emps.limit(1).one())

# 删除 delete by query
session.query(Employee).filter(Employee.emp_no > 10018).delete()
session.commmit # 提交则删除

# count
from sqlalchemy import func

query = session.query(func.count(Employee.emp_no)) 
print(query.all()) # 列表中一个元素
print(query.first) # 一个只有一个元组的元组
print(query.one()) # 只能有一行返回，一个元组
print(query.scalar()) # 取one()的第一个元素

# max/min/avg
print(session.query(func.max(Employee.emp_no)).scalar())
print(session.query(func.min(Employee.emp_no)).scalar())
print(session.query(func.avg(Employee.emp_no)).scalar())

query = session.query(Employee.gender,
    func.count(Employee.emp_no)).group_by(Employee.gender).all()
for g,y in query:
    print(g.value, y)

-- 员工表
CREATE TABLE `employees` (
  `emp_no` int(11) NOT NULL,
  `birth_date` date NOT NULL,
  `first_name` varchar(14) NOT NULL,
  `last_name` varchar(16) NOT NULL,
  `gender` smallint(6) NOT NULL DEFAULT 1 COMMENT 'M=1, F=2',
  `hire_date` date NOT NULL,
  PRIMARY KEY (`emp_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

-- 部门表
CREATE TABLE `departments` (
  `dept_no` char(4) NOT NULL,
  `dept_name` varchar(40) NOT NULL,
  PRIMARY KEY (`dept_no`),
  UNIQUE KEY `dept_name` (`dept_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

-- 第三张表
CREATE TABLE `dept_emp` (
  `emp_no` int(11) NOT NULL,
  `dept_no` char(4) NOT NULL,
  `from_date` date NOT NULL,
  `to_date` date NOT NULL,
  PRIMARY KEY (`emp_no`,`dept_no`),
  KEY `dept_no` (`dept_no`),
  CONSTRAINT `dept_emp_ibfk_1` FOREIGN KEY (`emp_no`) REFERENCES `employees` (`emp_no`) ON DELETE CASCADE,
  CONSTRAINT `dept_emp_ibfk_2` FOREIGN KEY (`dept_no`) REFERENCES `departments` (`dept_no`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

import sqlalchemy
from sqlalchemy import create_engine, Column, String, Integer, Date, Enum, ForeignKey
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm.state import InstanceState
from sqlalchemy import or_, and_, not_
import enum
from sqlalchemy import func

Base = declarative_base() # 基类

IP = '192.168.1.6'
USERNAME = 'lqx'
PASSWORD = 'lqx'
DBNAME ='test'
PORT = 3306
engine = create_engine("mysql+pymysql://{}:{}@{}:{}/{}".format(
    USERNAME, PASSWORD, IP, PORT, DBNAME
), echo=True)

Session = sessionmaker(bind=engine)
session = Session()

class MyEnum(enum.Enum):
    M = 'M'
    F = 'F'

# 打印函数
def show(emps):
    for x in emps:
        print(x)
    print('~~~~~~~~~~~~\n')

class Employee(Base):
    # 指定表名
    __tablename__ = 'employees'

    # 定义类属性对应字段
    emp_no = Column(Integer, primary_key=True)
    birth_date = Column(Date, nullable=False)
    first_name = Column(String(14), nullable=False)
    last_name = Column(String(16), nullable=False)
    gender = Column(Enum(MyEnum), nullable=False)
    hire_date = Column(Date, nullable=False)

    def __repr__(self):
        return "<{} no={}, name={}, gender={}>".format(
            __class__.__name__, self.emp_no, "{} {}".format(
                self.first_name, self.last_name), self.gender.value
        )

class Department(Base):
    __tablename__ = 'departments'

    dept_no = Column(String(4), primary_key=True)
    dept_name = Column(String(40), nullable=False, unique=True)

    def __repr__(self):
        return "{} no={} name={}".format(
            type(self).__name__, self.dept_no, self.dept_name)

class Dept_emp(Base):
    __tablename__ = 'dept_emp'

    emp_no = Column(Integer, ForeignKey('employees.emp_no',
                    ondelete='CASCADE'), primary_key=True)
    dept_no = Column(String(4), ForeignKey('depatments.dept_no', ondelete='CASCADE'),
                     primary_key=True)
    from_date = Column(Date, nullable=False)
    to_date = Column(Date, nullable=False)

    def __repr__(self):
        return "{} empno={} deptno={}".format(
            type(self).__name__, self.emp_no, self.dept_no)

# 查询10010员工的所在的部门编号及员工信息
results = session.query(Employee, Dept_emp).filter(
    Employee.emp_no == Dept_emp.emp_no).filter(Employee.emp_no == 10010).all()

show(results)

# 查询结果2行
(<Employee no=10010, name=Duangkaew Piveteau, gender=F>, Dept_emp empno=10010 deptno=d004)
(<Employee no=10010, name=Duangkaew Piveteau, gender=F>, Dept_emp empno=10010 deptno=d006)

SELECT * 
FROM employees, dept_emp 
WHERE employees.emp_no = dept_emp.emp_no AND employees.emp_no = 10010

# 查询10010员工的所在的部门编号及员工信息
# 第一种写法
results = session.query(Employee).join(Dept_emp).filter(Employee.emp_no == 10010).all()

第二种写法
results = session.query(Employee).join(Dept_emp,
            Employee.emp_no == Dept_emp.emp_no).filter(Employee.emp_no == 10010).all()

show(results)

from sqlalchemy.orm import sessionmaker,relationship

class Employee(Base):
    # 指定表名
    __tablename__ = 'employees'

    # 定义类属性对应字段
    emp_no = Column(Integer, primary_key=True)
    birth_date = Column(Date, nullable=False)
    first_name = Column(String(14), nullable=False)
    last_name = Column(String(16), nullable=False)
    gender = Column(Enum(MyEnum), nullable=False)
    hire_date = Column(Date, nullable=False)

    departmens = relationship('Dept_emp') #

    def __repr__(self): # 注意增加self.dept_emps
        return "<{} no={}, name={}, gender={} depts={}>".format(
            __class__.__name__, self.emp_no, "{} {}".format(
            self.first_name, self.last_name), self.gender.value,
            self.departmens
        )

# 查询10010员工的所在的部门编号及员工信息
# 第一种
results = session.query(Employee).join(Dept_emp).filter(
            Employee.emp_no == Dept_emp.emp_no).filter(Employee.emp_no == 10010)

# 第二种
results = session.query(Employee).join(Dept_emp,
            Employee.emp_no == Dept_emp.emp_no).fiter(Employee.emp_no == 10010)

# 第三种
results = session.query(Employee).join(Dept_emp, 
            (Employee.emp_no == Dept_emp.emp_no) & (Employee.emp_no == 10010))
show(results.all()) # 打印结果

results = session.query(Employee).join(Dept_emp).filter(
            Employee.emp_no == Dept_emp.emp_no).filter(Employee.emp_no == 10010)

for x in results:
    print(x.emp_no)

import os
import django

#参考wsgi.py 加载初始环境
os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'salary.settings')
django.setup()

#访问数据
from employee.models import Employee
from django.db.models import Q, Max, Min, Avg, Count, Sum

mgr = Employee.objects
print(mgr.filter(pk__gt=10010).count()) # 单值。将所有数据看做一行出结果
print(mgr.filter(pk__gt=10010).aggregate(Count('pk'), Max('pk'), Min('pk'), Sum('pk'), Avg('pk'))) # aggregate 聚合函数,出结果，字典。
print(mgr.aggregate(Max('pk'), min=Min('pk'), sum=Sum('pk'))) # 别名

#aggregate 聚合函数，返回字典。默认key命名为 '字段名__聚合函数名'

(0.001) 
                SELECT VERSION(),
                       @@sql_mode,
                       @@default_storage_engine,
                       @@sql_auto_is_null,
                       @@lower_case_table_names,
                       CONVERT_TZ('2001-01-01 01:00:00', 'UTC', 'UTC') IS NOT NULL
            ; args=None; alias=default
(0.001) SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; args=None; alias=default
(0.001) SELECT COUNT(*) AS `__count` FROM `employees` WHERE `employees`.`emp_no` > 10010; args=(10010,); alias=default
10
(0.004) SELECT COUNT(`employees`.`emp_no`) AS `pk__count`, MAX(`employees`.`emp_no`) AS `pk__max`, MIN(`employees`.`emp_no`) AS `pk__min`, SUM(`employees`.`emp_no`) AS `pk__sum`, AVG(`employees`.`emp_no`) AS `pk__avg` FROM `employees` WHERE `employees`.`emp_no` > 10010; args=(10010,); alias=default
{'pk__count': 10, 'pk__max': 10020, 'pk__min': 10011, 'pk__sum': 100155, 'pk__avg': 10015.5}
(0.001) SELECT MIN(`employees`.`emp_no`) AS `min`, SUM(`employees`.`emp_no`) AS `sum`, MAX(`employees`.`emp_no`) AS `pk__max` FROM `employees`; args=(); alias=default
{'min': 10001, 'sum': 200210, 'pk__max': 10020}

import os
import django

#参考wsgi.py 加载初始环境
os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'salary.settings')
django.setup()

#访问数据
from employee.models import Employee
from django.db.models import Q, Max, Min, Avg, Count, Sum

mgr = Employee.objects
print(mgr.filter(pk__gt=10010).count()) # 单值。将所有数据看做一行出结果
print(mgr.filter(pk__gt=10010).aggregate(Count('pk'), Max('pk'), Min('pk'), sum=Sum('pk'), avg=Avg('pk'))) # aggregate 聚合函数,出结果，字典。
print(mgr.filter(pk__gt=10010).annotate(Count('pk')))
#aggregate 聚合函数，出统计函数的结果。返回字典。默认key命名为 '字段名__聚合函数名'
#annotate 返回查询集，元素是对象。用聚合函数，聚合函数会分组，没有指定分组使用pk分组，行行分组。

s = mgr.filter(pk__gt=10010).annotate(Count('pk'))
for x in s:
    print(x)
    print(x.__dict__) #里面多了一个属性pk_count

-- 等价SQL语句
SELECT
  `employees`.`emp_no`,
  `employees`.`birth_date`,
  `employees`.`first_name`,
  `employees`.`last_name`,
  `employees`.`gender`,
  `employees`.`hire_date`,
  COUNT(`employees`.`emp_no`) AS `pk__count`
FROM
  `employees`
WHERE
  `employees`.`emp_no` > 10010
GROUP BY
  `employees`.`emp_no`;

mgr.filter(pk__lt=10005).values('gender').annotate(c=Count('gender'),a=Sum('pk'))

-- 等价SQL语句
SELECT
    `gender`,
    COUNT(`gender`) AS `c`,
    SUM(`emp_no`) AS `a`
FROM
    `employees`
WHERE
    `emp_no` < 10005
GROUP BY
    `gender`

mgr.filter(pk__lt=10005).annotate(c=Count('gender'),a=Sum('pk')).values('gender','c','a','first_name')

-- 等价SQL语句
SELECT
    `gender`,
    `first_name`,
    COUNT(`gender`) AS `c`,
    SUM(`emp_no`) AS `a`
FROM
    `employees`
WHERE
    `emp_no` < 10005
GROUP BY
    `emp_no`

mgr.filter(pk__lt=10005).values().annotate(c=Count('gender'),a=Sum('pk')).values('c','a','first_name')

-- 等价SQL语句
SELECT
    `first_name`,
    COUNT(`gender`) AS `c`,
    SUM(`emp_no`) AS `a`
FROM
    `employees`
WHERE
    `emp_no` < 10005
GROUP BY
    `emp_no`;

mgr.filter(pk__lt=10005).values('gender').annotate(c=Count('gender'),a=Sum('pk')).values()

-- 等价SQL语句
SELECT
    `emp_no`,
    `birth_date`,
    `first_name`,
    `last_name`,
    `gender`,
    `hire_date`,
    COUNT(`gender`) AS `c`,
    SUM(`emp_no`) AS `a`
FROM
    `employees`
WHERE
    `emp_no` < 10005
GROUP BY
    `emp_no`;

mgr.filter(pk__lt=10005).values('gender').annotate(c=Count('gender'),a=Sum('pk')).values('c','a','first_name','emp_no')

-- 等价SQL语句
SELECT
    `first_name`,
    `emp_no`,
    COUNT(`gender`) AS `c`,
    SUM(`emp_no`) AS `a`
FROM
    `employees`
WHERE
    `emp_no` < 10005
GROUP BY
    `emp_no`;

mgr.filter(pk__lt=10005).values('gender').annotate(c=Count('gender'),a=Sum('pk')).values('c','a')
#前面的value控制分组，后面的values控制投影

-- 等价SQL语句
SELECT
    COUNT(`gender`) AS `c`,
    SUM(`emp_no`) AS `a`
FROM
    `employees`
WHERE
    `emp_no` < 10005
GROUP BY
    `gender`;

mgr.filter(pk__lt=10005).values('gender').annotate(c=Count('gender'),a=Sum('pk')).values('c','a','first_name','last_name')

-- 等价SQL语句
SELECT
    `first_name`,
    `last_name`,
    COUNT(`gender`) AS `c`,
    SUM(`emp_no`) AS `a`
FROM
    `employees`
WHERE
    `emp_no` < 10005
GROUP BY
    `gender`,
    `first_name`,
    `last_name`;

mgr.filter(pk__lt=10005).values('gender').annotate(c=Count('gender'),a=Sum('pk')).values('c','a','first_name').order_by('emp_no')

-- 等价SQL语句
SELECT
    `first_name`,
    COUNT(`gender`) AS `c`,
    SUM(`emp_no`) AS `a`
FROM
    `employees`
WHERE
    `emp_no` < 10005
GROUP BY
    `emp_no`
ORDER BY
    `emp_no` ASC;

mgr.filter(pk__lt=10005).values('gender').annotate(c=Count('gender'),a=Sum('pk')).values('c','a','first_name').order_by('last_name')

-- 等价SQL语句
SELECT
    `first_name`,
    COUNT(`gender`) AS `c`,
    SUM(`emp_no`) AS `a`
FROM
    `employees`
WHERE
    `emp_no` < 10005
GROUP BY
    `gender`,
    `first_name`,
    `last_name`
ORDER BY
    `last_name` ASC;