16.6实战演练2——数据表综合查询案例

　　CREATE TABLE dept
　　(
　　      d_no         INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
　　      d_name       VARCHAR(50),
　　      d_location  VARCHAR(100)
　　);

　　CREATE TABLE employee
　　(
　　      e_no        INT NOT NULL PRIMARY KEY,
　　      e_name      VARCHAR(100) NOT NULL,
　　      e_gender    CHAR(2) NOT NULL,
　　      dept_no    INT NOT NULL,
　　      e_job       VARCHAR(100) NOT NULL,
　　      e_salary   SMALLINT NOT NULL,
　　      hireDate   DATE,
　　      CONSTRAINT dno_fk FOREIGN KEY(dept_no)
　　      REFERENCES dept(d_no)
　　);

　　INSERT INTO dept
　　VALUES (10, 'ACCOUNTING', 'ShangHai'),
　　             (20, 'RESEARCH ', 'BeiJing '),
　　             (30, 'SALES ', 'ShenZhen '),
　　             (40, 'OPERATIONS ', 'FuJian ');

　　INSERT INTO employee
　　VALUES (1001, 'SMITH', 'm',20, 'CLERK',800,'2005-11-12'),
　　            (1002, 'ALLEN', 'f',30, 'SALESMAN', 1600,'2003-05-12'),
　　            (1003, 'WARD', 'f',30, 'SALESMAN', 1250,'2003-05-12'),
　　            (1004, 'JONES', 'm',20, 'MANAGER', 2975,'1998-05-18'),
　　            (1005, 'MARTIN', 'm',30, 'SALESMAN', 1250,'2001-06-12'),
　　            (1006, 'BLAKE', 'f',30, 'MANAGER', 2850,'1997-02-15'),
　　            (1007, 'CLARK', 'm',10, 'MANAGER', 2450,'2002-09-12'),
　　            (1008, 'SCOTT', 'm',20, 'ANALYST', 3000,'2003-05-12'),
　　            (1009, 'KING', 'f',10, 'PRESIDENT', 5000,'1995-01-01'),
　　            (1010, 'TURNER', 'f',30, 'SALESMAN', 1500,'1997-10-12'),
　　            (1011, 'ADAMS', 'm',20, 'CLERK', 1100,'1999-10-05'),
　　            (1012, 'JAMES', 'm',30, 'CLERK', 950,'2008-06-15');

　　SELECT e_no, e_name, e_salary FROM employee;

mysql> SELECT e_no, e_name, e_salary FROM employee;

12 rows in set (0.00 sec)

　　SELECT * FROM employee WHERE dept_no IN (10, 20);

mysql> SELECT * FROM employee WHERE dept_no IN (10, 20);

6 rows in set (0.00 sec)

　　SELECT * FROM employee WHERE e_salary BETWEEN 800 AND 2500;

mysql> SELECT * FROM employee WHERE e_salary BETWEEN 800 AND 2500;

8 rows in set (0.00 sec)

　　SELECT * FROM employee WHERE dept_no = 20;

mysql> SELECT * FROM employee WHERE dept_no = 20;

4 rows in set (0.00 sec)

　　SELECT dept_no, MAX(e_salary) FROM employee GROUP BY dept_no;

mysql> SELECT dept_no, MAX(e_salary) FROM employee GROUP BY dept_no;

3 rows in set (0.00 sec)

　　SELECT d_no, d_location FROM dept WHERE d_no=
　　　　　　(SELECT dept_no FROM employee WHERE e_name='BLAKE');

mysql> SELECT e_name,d_no, d_location

-> FROM dept WHERE d_no=

-> (SELECT dept_no FROM employee WHERE e_name='BLAKE');

1 row in set (0.00 sec)

　　SELECT e_no, e_name, dept_no, d_name,d_location
　　FROM employee, dept WHERE dept.d_no=employee.dept_no;

mysql> SELECT e_no, e_name, dept_no, d_name,d_location

-> FROM employee, dept WHERE dept.d_no=employee.dept_no;

12 rows in set (0.00 sec)

　　SELECT dept_no, COUNT(*) FROM employee GROUP BY dept_no;

mysql> SELECT dept_no, COUNT(*) FROM employee GROUP BY dept_no;

3 rows in set (0.00 sec)

　　SELECT e_job, SUM(e_salary) FROM employee GROUP BY e_job;

mysql> SELECT e_job, SUM(e_salary) FROM employee GROUP BY e_job;

5 rows in set (0.00 sec)

　　SELECT dept_no, AVG(e_salary) FROM employee GROUP BY dept_no;

mysql> SELECT dept_no, AVG(e_salary) FROM employee GROUP BY dept_no;

3 rows in set (0.00 sec)

　　SELECT * FROM employee WHERE e_salary < 1500;

mysql> SELECT * FROM employee WHERE e_salary < 1500;

5 rows in set (0.00 sec)

　　SELECT e_name,dept_no, e_salary
　　FROM employee ORDER BY dept_no DESC, e_salary DESC;

mysql> SELECT e_name,dept_no, e_salary

-> FROM employee ORDER BY dept_no DESC, e_salary DESC;

12 rows in set (0.00 sec)

　　SELECT * FROM employee WHERE e_name REGEXP '^[as]';

mysql> SELECT * FROM employee WHERE e_name REGEXP '^[as]';

4 rows in set (0.00 sec)

　　SELECT * FROM employee where YEAR(CURDATE()) -YEAR(hireDate) >= 10;

mysql> SELECT * FROM employee where YEAR(CURDATE()) -YEAR(hireDate) >= 10;

6 rows in set (0.01 sec)