Web前端之SQL（4）——多表查询

一、复习

1. Web前端

2. 数据库
关系型数据库RDBMS

  Server -> Database ->Table ->Row -> Column

3.MySQL连接

  mysql.exe -h127.0.0.1 -P3306 -uroot -p  服务器连接
  mysql -uroot   简写模式
  mysql -uroot < C:/xampp/…/xz. sql   脚本连接模式

4. 常用管理命令

   quit;
   show databases;
   use 数据库名称;
   show  tables;
   desc  表名称;  #表里面有哪些列

5.sql语句
DDL 定义 CREATE /DROP/ALTER
DML 操作数据 INSERT/UPDATE/DELETE
DQL 查询 SELECT
DCL 控制用户权限 GRANT/REVOKE

6.列类型
数值类型

TINYINT   SMALLINT   INT   BIGINT  FLOAT  DOUBLE  DECIMAL（定点小数） BOOL

日期时间类型

 DATE   TIME   DATETIME 

字符串类型

  VARCHAR    CHAR  TEXT   

7.列约束
主键约束 PRIMARY KEY
唯一约束 UNIQUE
非空约束 NOT NULL
默认值约束 DEFAULT
检查约束 CHECK
外键约束 FOREGIN KEY（列名）REFERENCES表（主键列）

8.简单查询
查询特定的列
查询所有的列 *
给列取别名 AS
查询不同的值 DISTINCT
执行计算
结果集排序 ORDER BY ASC升/DESC降
条件查询 WHERE/IS NULL/NULL/AND/OR/BETWEEN AND/IN( )
模糊条件查询 **%任意多 _单个 LIKE**
分页查询 LIMIT START,COUNT start=(当前页码-1)*count

9. 复杂查询

练习：
查询出工资为8000以上的女员工的姓名、性别、生日，结果集按照工资降序排序，取前3个人。
SELECT ename,sex,birthday,salary FROM emp
WHERE salary>=8000 AND sex=0
ORDER BY salary DESC
LIMIT 0,3;

二、复杂查询

1.复杂查询——聚合查询/分组查询
示例：查询出所有员工的数量

 SELECT COUNT(eid) FROM emp;    #15

练习：使用员工的姓名获取员工数量

 SELECT COUNT(ename) FROM emp;   #14

练习：使用员工部门编号获取员工数量

 SELECT COUNT(deptId) FROM emp;    #14
 SELECT COUNT(*) FROM emp;         #15  推荐写法

函数：功能体，接收若干个数据，返回特定的结果 —饺子机
聚合函数

COUNT( )/SUM( )/AVG( )/MAX( )/MIN( )

练习：查询出所有员工工资的总和

 SELECT SUM(salary) FROM emp;

练习：查询出所有男员工的平均工资

 SELECT SUM(salary)/COUNT(*) FROM emp WHERE sex=1;
 SELECT AVG(salary) FROM emp WHERE sex=1;

练习：查询出工资最高的员工的工资

 SELECT MAX(salary) FROM emp;

练习：查询出工资最低的员工的工资

 SELECT MIN(salary) FROM emp;

练习：查询年龄最大的员工的生日

SELECT MIN(birthday) FROM emp;

分组查询：只能查询分组条件和聚合函数 GROUP BY*

示例：查询出每个部门员工的最高工资

SELECT deptId,MAX(salary) FROM emp GROUP BY deptId;

练习：查询出男女员工的平均工资，最高工资，最低工资

 SELECT sex,AVG(salary),MAX(salary),MIN(salary) FROM emp GROUP BY sex;

YEAR() 获取日期中的年份
MONTH() 获取日期中的月份
示例：查询1991年出生的员工

SELECT * FROM emp WHERE YEAR(birthday)='1991';

示例：查询3月份出生的员工

 SELECT * FROM emp WHERE MONTH(birthday)='3';

2.复杂查询——子查询
本质上就是一个SQL语句的查询结果作为另一个SQL语句的查询条件
示例：查询出研发部员工的所有信息
步骤1：查询研发部的部门编号

SELECT did FROM dept WHERE dname='研发部';           #10

步骤2：使用部门编号查询员工——10

 SELECT * FROM emp WHERE deptId=10;

综合：

 SELECT * FROM emp WHERE  deptId=(
    SELECT did FROM dept WHERE dname='研发部'
  );

练习：查询比tom工资高的员工所有信息
步骤1：查询tom的工资

  SELECT salary FROM emp WHERE ename='tom';            #6000

步骤2：查询工资比6000高的员工

SELECT * FROM emp WHERE salary>6000;

综合：

  SELECT * FROM emp WHERE salary>(
    SELECT salary FROM emp WHERE ename='tom'
  );

练习：查询出和tom同一年出生的员工所有信息
步骤1：查询tom的生日的年份 ——1990

SELECT YEAR(birthday) FROM emp WHERE ename='tom';

步骤2：查询出1990年出生的员工

SELECT * FROM emp WHERE YEAR(birthday)=1990;

综合：

  SELECT * FROM emp WHERE YEAR(birthday)=(
 SELECT YEAR(birthday) FROM emp WHERE ename='tom'
  );

3.复杂查询——多表查询
示例：查询出所有员工的姓名及其所在部门名称

 SELECT ename,dname FROM emp,dept;

笛卡尔积！
如何避免笛卡尔积，为多表查询添加条件。

SELECT ename,dname FROM emp,dept WHERE deptId=did;

上述语法无法查询出没有部门的员工，也不能查询没有员工的部门——SQL92
(1)内连接 INNER JOIN … ON ——和之前的结果一样

 SELECT ename,dname FROM emp INNER JOIN dept ON deptId=did;

(2)左外连接 LEFT OUTER JOIN…ON

 SELECT ename,dname FROM emp LEFT OUTER JOIN dept ON deptId=did;

查询结果是左侧表中所有的记录，即使右侧没有对应的记录。
OUTER可以省略
(3)右外连接 RIGHT OUTER JOIN…ON

 SELECT ename,dname FROM emp RIGHT OUTER JOIN dept ON deptId=did;

显示右侧表中所有的记录，OUTER可以省略
(4)全连接 FULL JOIN
显示左侧和右侧表中所有的记录——MySQL不支持

 (SELECT ename FROM emp_bj)
 UNION
 (SELECT ename FROM emp_sh);
 MySQL中的全连接
 (SELECT ename,dname FROM emp LEFT JOIN dept ON deptId=did)
 UNION
 (SELECT ename,dname FROM emp RIGHT JOIN dept ON deptId=did);

全连接还有UNION语句
UNION 合并相同的记录
UNION ALL 不合并相同的记录

4.项目中如何保存日期、时间
2018-12-6 14:40:30
2018年12月6月 2018/12/6 12/6/2018
存储的是距离计算机元年(1970-1-1)的毫秒数
48365246060*1000
1513728000000 使用BIGINT的列类型
参考新闻部分
http://www.gree.com.cn/pczwb/xwzx/index.shtml