[大数据]多 表 查 询

day02-多表查询

课前回顾:
  1.数据库的操作:
    a.创建数据库: create database 数据库名字 charset utf8;
    b.删除数据库: drop database 数据库名字
    c.切库:use 数据库名字
  2.表的操作:
    a.创建表:
      create table 表名(
        列名 数据类型 [约束],
        列名 数据类型 [约束]
      );
    b.删除表: drop table 表名
    c.修改表结构: alter table 表名 add/modify
    d.注意:不管是列名,表名,库名,如果名字和关键字冲突,加上``
  3.数据操作:
    a.添加数据:
      insert into 表名(列名,列名) values (值,值)
      insert into 表名 values (值,值)
      insert into 表名(列名,列名) values (值,值),(值,值),(值,值),(值,值)-> 批量添加
      如果主键是自增长的话,添加的时候就不用带主键了
    b.删除数据:
      delete from 表名[where 条件]
    c.修改数据:
      update 表名 set 列名 = 新值 [where 条件]
    d.查询数据
      select * from 表名 [where 条件] -> 查询所有,结果展示所有列
      select 列名 from 表名 [where 条件]-> 查询,结果展示指定的列
   4.约束:
     a.主键约束:primary key 每个表都应该有一个主键列
     b.自增长:auto_increment 都是和主键联合使用
     c.非空约束:NOT NULL 不能为null
     d.唯一约束:UNIQUE 此列中的数据不能重复
   
   5.truncate 和 delete区别
     truncate 表名 -> 摧毁表结构,自增长列的数据从头开始编号
     delete->删除之后自增长列的数据不会从头开始编号
           
今日重点:         
   1.所有查询(单表,多表)
   2.会数据库的备份和还原
   3.知道表和表之间的关系

第一章.单表查询

#创建商品表：
create table product(
	pid int primary key,
	pname varchar(20),
	price double
);
INSERT INTO product(pid,pname,price) VALUES(1,'联想',5000);
INSERT INTO product(pid,pname,price) VALUES(2,'海尔',3000);
INSERT INTO product(pid,pname,price) VALUES(3,'雷神',5000);
INSERT INTO product(pid,pname,price) VALUES(4,'JACK JONES',800);
INSERT INTO product(pid,pname,price) VALUES(5,'真维斯',200);
INSERT INTO product(pid,pname,price) VALUES(6,'花花公子',440);
INSERT INTO product(pid,pname,price) VALUES(7,'劲霸',2000);
INSERT INTO product(pid,pname,price) VALUES(8,'香奈儿',800);
INSERT INTO product(pid,pname,price) VALUES(9,'相宜本草',200);
INSERT INTO product(pid,pname,price) VALUES(10,'面霸',5);
INSERT INTO product(pid,pname,price) VALUES(11,'好想你枣',56);
INSERT INTO product(pid,pname,price) VALUES(12,'香飘飘奶茶',1);
INSERT INTO product(pid,pname,price) VALUES(13,'果9',1);

1.聚合查询

1.作用:纵向操作数据
2.聚合函数:
  count(列名):统计表中有多少条数据
  sum(列名):针对指定列进行求和
  avg(列名):针对指定列求平均值
  max(列名):求指定列的最大值
  min(列名):求指定列的最小值
3.格式:
  select 聚合函数(列名) from 表名 where 条件

-- 查询product的总条数
SELECT COUNT(*) FROM product;
SELECT COUNT(pid) FROM product;

-- 查询所有商品的价格总和
SELECT SUM(price) FROM product;

-- 查询pid为1,3,7 商品的价格平均值
SELECT AVG(price) FROM product WHERE pid IN(1,3,7);

-- 查询商品的最高价格以及最低价格
SELECT MAX(price),MIN(price) FROM product;

-- 创建一个新表
CREATE TABLE student(
  sid INT,
  sname VARCHAR(10)
);

SELECT COUNT(*) FROM student;-- 包含null
SELECT COUNT(1) FROM student;-- 包含null
SELECT COUNT(0) FROM student;-- 包含null

SELECT COUNT(sid) FROM student;-- 不包含null

2.分组查询

1.关键字:group by 列名  -> 根据哪一列进行分组
2.语法:
  select 列名,列名... from 表名 group by 分组字段 having 分组条件
3.分组按照哪个字段去分,小窍门:
  相同字段名为一组,不同字段名的单独为一组展示,我们就可以用该字段进行分组
4.关键字:在分组之后进行条件筛选
  having 条件
5.having和where的区别:
  having:是在分组之后查询
  where:在分组之前查询

书写sql语句关键字的顺序
select 
from 
where 
group by 
having 
order by

执行顺序:
from 
where 
group by 
having 
select 
order by

先定位到要查询哪个表,然后根据什么条件去查,表确定好了,条件也确定好了,开始利用select查询
查询得出一个结果,在针对这个结果进行一个排序

-- 查询相同商品的价格总和
SELECT pname,SUM(price) FROM product;

-- 查询相同商品的价格总和->用分组进行正确查询
SELECT pname,SUM(price) FROM product GROUP BY pname;

-- 查询相同商品的价格总和并排序

/*
  1.问题:
    如果排序时用的字段是price,排序失败了
  2.原因:
    排序,是对查询之后的结果进行排序
    那么查询之后的结果中价格列的列名叫 SUM(price),所以我们应该按照SUM(price)进行排序
*/
-- SELECT pname,SUM(price) FROM product GROUP BY pname order by price desc;
SELECT pname,SUM(price) FROM product GROUP BY pname ORDER BY SUM(price) DESC;

SELECT pname,SUM(price) 'newprice' FROM product GROUP BY pname ORDER BY newprice DESC;


-- 查询相同商品的价格总和,再展示出价格总和大于等于2000的商品
/*
  下面的sql有错误,因为要写先where,再写group by ,下面的错误属于关键字书写顺序问题
*/
SELECT pname,SUM(price) 'newprice' FROM product GROUP BY pname WHERE newprice>=2000;


/*
   下面的sql有错误,因为先执行where,再执行select,执行where的时候newprice这个别名还没出现
*/
SELECT pname,SUM(price) 'newprice' FROM product WHERE newprice>=2000 GROUP BY pname;


/*
  下面的sql查询结果不对
   因为where是在分组之前执行,也就是说条件是在分组之前用原来的
   price进行的筛选,直接将分组求和之前的果9筛掉了
*/
SELECT pname,SUM(price) 'newprice' FROM product WHERE price>=2000 GROUP BY pname;


/*
  下面的sql是正确的
  having是在分组之后进行条件筛选
*/
SELECT pname,SUM(price) 'newprice' FROM product GROUP BY pname HAVING newprice>=2000;

3.分页查询

1.语法
  select * from 表名 limit m,n
    m:代表每页的起始位置 -> 从0开始
    n:页面显示的条数
    
2.每一页的起始位置算法:
   (当前页-1)*每页显示条数

-- 后台计算出页码、页数(页大小)
-- 分页需要的相关数据结果分析如下,
-- 注意:下面是伪代码不用于执行
int curPage = 2; -- 当前页数
int pageSize = 5; -- 每页显示数量
int startRow = (curPage - 1) * pageSize; -- 当前页, 记录开始的位置(行数)计算
int totalSize = select count(*) from products; -- 记录总数量
int totalPage = Math.ceil(totalSize * 1.0 / pageSize); -- 总页数
                总页数 = (总记录数/每页显示条数)向上取整

/*
  select * from 表名 limit m,n
    m:代表每页的起始位置 -> 从0开始
    n:页面显示的条数
*/
SELECT * FROM product LIMIT 0,5; -- 第一页

SELECT * FROM product LIMIT 5,5; -- 第二页

SELECT * FROM product LIMIT 10,5; -- 第三页

SELECT * FROM product LIMIT 15,5; -- 第四页

第二章.数据库的备份与还原

1.用命令去操作数据库的备份与还原

1.1.命令操作备份

mysqldump  -u用户名 -p密码 数据库名>生成的脚本文件路径

生成的脚本文件路径:指定备份的路径,写路径时最后要指明备份的sql文件名,命令后不要加;

1.2.命令操作还原

mysql  -uroot  -p密码 数据库名 < 文件路径

注意:我们利用命令备份出来的sql文件中没有单独创建数据库的语句,所以如果利用命令去还原的话,需要我们自己手动先创建对应的库
    命令后不要加;

2.利用点击去操作数据库的备份与还原

2.1.利用点击去备份

2.2.利用点击去还原

第三章.数据库三范式

好的数据库设计对数据的存储性能和后期的程序开发，都会产生重要的影响。建立科学的，规范的数据库就需要满足一些规则来优化数据的设计和存储，这些规则就称为范式。

1第一范式: 确保每列保持原子性

第一范（1NF）式是最基本的范式。如果数据库表中的所有字段值都是不可分解的原子值，就说明该数据库表满足了第一范式。

第一范式的合理遵循需要根据系统的实际需求来定。比如某些数据库系统中需要用到“地址”这个属性，本来直接将“地址”属性设计成一个数据库表的字段就行。但是如果系统经常会访问“地址”属性中的“城市”部分，那么就非要将“地址”这个属性重新拆分为省份、城市、详细地址等多个部分进行存储，这样在对地址中某一部分操作的时候将非常方便。这样设计才算满足了数据库的第一范式，如下表所示。

如果不遵守第一范式，查询出数据还需要进一步处理（查询不方便）。遵守第一范式，需要什么字段的数据就查询什么数据（方便查询）

列名:详细地址手机号
    
    北京市昌平区北七家镇宏福苑小区19号楼1501087xxxx  -> 不行,因为数据可以拆分,不符合第一范式原子性

2 第二范式: 确保表中的每行都能唯一区分

第二范式（2NF)第二范式（2NF）是在第一范式（1NF）的基础上建立起来的，即满足第二范式（2NF）必须先满足第一范式（1NF）。第二范式（2NF）要求数据库表中的每个实例或行必须可以被惟一的区分。为实现区分通常需要为表加上一个列，以存储各个实例的惟一标识。

3 第三范式: 3NF:非主键字段不能相互依赖

比如student表，班级编号受人员编号的影响，如果在这个表中再插入班级的班主任、数学老师等信息，你们觉得这样合适吗？肯定不合适，因为学生有多个，这样就会造成班级有多个，那么每个班级的班主任、数学老师都会出现多条数据，而我们理想中的效果应该是一条班级信息对应一个班主任和数学老师，这样更易于我们理解，

第四章.多表之间的关系

1.一对多关系
  从主表看:一对多
  从从表看:多对一
  
  结果:一对多
  
  a.举例:学生表和成绩表
  b.如何判断谁是主表,谁是从表->就看哪个表的数据约束哪个表的数据,被约束的就是从表
    学生表->主表
    成绩表->从表
  c.分析关系:
    从主表看(学生表):1个学生可以有多个成绩  -> 1对多
    从从表看(成绩表):多个成绩可以是1个人考出来 -> 多对一
    结果:学生表和成绩表->  一对多关系
    
2.多对多关系:
  从主表看还是从从表看都是一对多
  结果:多对多
  
  a.举例:商品表和订单表:
  b.分析关系:
    从商品表看:一个商品可以在多个订单中-> 一对多 
    从订单表看:一个订单可以有多个商品->一对多
    结果:多对多

3.一对一关系:
  怎么看都是一对一
  a.举例:人和身份证
  b.关系:一个人对应一个身份证;一个身份证对应一个人

第五章.创建外键约束

格式:alter table 从表 add [constraint 外键名称(自定义)] foreign key 从表(外键列名) references 主表(主键列名)

1.一对多的表创建外键约束

    # 分类表->主表
    CREATE TABLE category (
      cid VARCHAR(32) PRIMARY KEY ,
      cname VARCHAR(50)
    );

    #商品表->从表
    CREATE TABLE products(
      pid VARCHAR(32) PRIMARY KEY ,
      pname VARCHAR(50),
      price DOUBLE,
      category_id VARCHAR(32)-- 外键  存储的是主表的主键内容
    );
    
        /*
     格式:alter table 从表 add [constraint 外键名称(自定义)] foreign key 从表(外键列名) references 主表(主键列名)    
    */
    
    ALTER TABLE products ADD CONSTRAINT cp1 FOREIGN KEY products(category_id) REFERENCES category(cid);

2.多对多的表创建外键约束

CREATE DATABASE 210906_java3 CHARSET utf8;    
    #商品表
    CREATE TABLE products(
      pid VARCHAR(32) PRIMARY KEY ,
      pname VARCHAR(50),
      price DOUBLE
    );
    
   
   # 订单表 
  CREATE TABLE `orders`(
  `oid` VARCHAR(32) PRIMARY KEY ,
  `totalprice` DOUBLE 	#总计
  );
   
  #订单项表->中间表
CREATE TABLE orderitem(
  pid VARCHAR(50),-- 商品id->外键
  oid VARCHAR(50)-- 订单id ->外键
);

/*
  商品表:主表
  订单项表:从表
*/

    /*
     格式:alter table 从表 add [constraint 外键名称(自定义)] foreign key 从表(外键列名) references 主表(主键列名)    
    */
    
ALTER TABLE orderitem ADD CONSTRAINT cp1 FOREIGN KEY orderitem(pid) REFERENCES products(pid);


/*
  主表:订单表
  从表:订单项表

*/
ALTER TABLE orderitem ADD CONSTRAINT cp2 FOREIGN KEY orderitem(oid) REFERENCES orders(oid);

第六章.多表查询

    # 分类表
    CREATE TABLE category (
      cid VARCHAR(32) PRIMARY KEY ,
      cname VARCHAR(50)
    );

    #商品表
    CREATE TABLE products(
      pid VARCHAR(32) PRIMARY KEY ,
      pname VARCHAR(50),
      price DOUBLE,
      flag VARCHAR(2), #是否上架标记为：1表示上架、0表示下架
      category_id VARCHAR(32), -- 外键
      CONSTRAINT products_fk FOREIGN KEY (category_id) REFERENCES category (cid)
    );
    #分类
INSERT INTO category(cid,cname) VALUES('c001','家电');
INSERT INTO category(cid,cname) VALUES('c002','服饰');
INSERT INTO category(cid,cname) VALUES('c003','化妆品');
#商品
INSERT INTO products(pid, pname,price,flag,category_id) VALUES('p001','联想',5000,'1','c001');
INSERT INTO products(pid, pname,price,flag,category_id) VALUES('p002','海尔',3000,'1','c001');
INSERT INTO products(pid, pname,price,flag,category_id) VALUES('p003','雷神',5000,'1','c001');

INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p004','JACK JONES',800,'1','c002');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p005','真维斯',200,'1','c002');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p006','花花公子',440,'1','c002');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p007','劲霸',2000,'1','c002');

INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p008','香奈儿',800,'1','c003');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p009','相宜本草',200,'1','c003');

1.交叉查询

1.格式:
  select 列名 from 表A,表B;
2.注意:
  交叉查询会出现"笛卡尔乘积"(所有的情况都组合一遍)

-- 查询商品的详细信息,出现了笛卡尔乘积,查询结果错误
SELECT * FROM category,products;


SELECT * FROM category,products WHERE category.cid = products.category_id;

-- 给表起别名

SELECT * FROM category c,products p WHERE c.cid = p.category_id;

2.内连接查询

1.关键字:inner join -> inner 可以干掉
2.分类:
  显示内连接:select 列名 from 表A inner join 表B on 条件
  隐式内连接:select 列名 from 表A,表B where 条件

-- 查询具体的商品信息->隐式内连接
SELECT * FROM category c,products p WHERE c.cid = p.category_id;

-- 查询具体的商品信息->显示内连接
SELECT * FROM category c INNER JOIN products p ON c.`cid` = p.`category_id`;

-- 用显示内连接的方式查询"化妆品"的商品信息

/*
   on...条件1...and...条件2 -> 认为条件1和条件2是一个整体,是一个大的条件,要同时满足
   
   on...条件1...where...条件2-> 先根据on查询出一个结果,查询之后用where进行筛选
*/
SELECT * FROM category c INNER JOIN products p ON c.`cid` = p.`category_id` AND cname = '化妆品';

SELECT * FROM category c INNER JOIN products p ON c.`cid` = p.`category_id` WHERE cname = '化妆品';

3.外连接

1.关键字:outer join -> outer 可省略
2.语法:
  a.左外连接:select 列名 from 表A left outer join 表B on 条件
  b.右外连接:select 列名 from 表A right outer join 表B on 条件
3.如何区分谁是左表,谁是右表
  看join这个单词
  
  在join左边的就是左表
  在join右边的就是右表
  
4.左外连接,右外连接,内连接

  a.左外连接:查询的是和右表的交集,以及左表的全部(和右表的交集,以及除交集之外的其他左表数据)
  b.右外连接:查询的是和左表的交集,以及右表的全部(和左表的交集,以及除交集之外的其他右表数据)
  c.内连接:查询的是两个表的交集

-- 查询所有的商品信息->左外连接
/*
   左表:category
   右表:products
*/
SELECT * FROM category c LEFT OUTER JOIN products p ON c.`cid` = p.`category_id`;

-- 省略outer
SELECT * FROM category c LEFT JOIN products p ON c.`cid` = p.`category_id`;

-- 查询所有的商品信息->右外连接

/*
   左表:category
   右表:products
*/
SELECT * FROM category c RIGHT JOIN products p ON c.`cid` = p.`category_id`;


-- 内连接

SELECT * FROM category c,products p WHERE c.`cid` = p.`category_id`;

4.union联合查询实现全外连接查询（了解）

首先要明确，联合查询不是多表连接查询的一种方式。联合查询是将多条查询语句的查询结果合并成一个结果并去掉重复数据。
全外连接查询的意思就是将左表和右表的数据都查询出来，然后按照连接条件连接
    
只要将两个结果一连接,左表和右表没有交叉的部分也就多查出来了

1.union的语法:
  查询语句1 union 查询语句2 union 查询语句3 ...

SELECT * FROM category c LEFT JOIN products p ON c.`cid` = p.`category_id`

UNION

SELECT * FROM category c RIGHT JOIN products p ON c.`cid` = p.`category_id`;

5.子查询

1.概述:一条查询语句作为另外一条查询语句的条件使用
2.语法:
  select 列名 from 表名 where (select 列名 from 表名)

-- 查询products表中'化妆品'的商品信息
SELECT * FROM products WHERE category_id = 'c003';
/*
  1.如果直接用category_id = 'c003'查询化妆品是不好的
  2.因为:我们单纯的看products表,我们其实不能断定c003就一定是化妆品
         因为products表中的c003是根据category表来的
  3.思考:products表中的category_id列中数据都是根据category表来的
         虽然分类的编号不确定到底代表啥,但是分类的名称,"化妆品"这三个字是确定的吧
  4.所以:我们的思路应该是先根据确定的"化妆品"这三个字查询出对应的编号(cid)
         然后作为我们最终的查询条件使用
*/

-- 先根据"化妆品"这三个字查询对应的id
SELECT cid FROM category WHERE cname = '化妆品';

SELECT * FROM products WHERE category_id = (SELECT cid FROM category WHERE cname = '化妆品');


-- 查询products表中化妆品和家电的商品信息
SELECT * FROM products WHERE category_id = 'c001' OR category_id = 'c003';
SELECT * FROM products WHERE category_id IN ('c001','c003');

/*
  products表中的category_id列中数据是根据category表来的
  所以我们也不确定products表中的c001和c003到底代表啥分类
  
  所以,我们可以通过确定的"家电"和"化妆品"这几个字来将对应的cid查询出来
  
  在将查询出来的结果作为条件使用
*/

SELECT cid FROM category WHERE cname IN('家电','化妆品');

SELECT * FROM products WHERE category_id IN (SELECT cid FROM category WHERE cname IN('家电','化妆品'));

6.子查询作为伪表使用

1.查询出来的结果是以表的形式呈现,这个表是只读权限,这个查询出来的表就是一张伪表

-- 查询化妆品的所有商品信息
SELECT * FROM category c,products p WHERE c.`cid` = p.`category_id` AND c.`cname` = '化妆品';

-- 先从category表中将"化妆品"查出来,作为伪表
SELECT * FROM category WHERE cname = '化妆品';

-- 将上面的伪表和products做查询
SELECT * FROM (SELECT * FROM category WHERE cname = '化妆品') c,products p WHERE c.`cid` = p.`category_id`

-- 查询所有化妆品和家电的商品信息
SELECT * FROM category c,products p WHERE c.`cid` = p.`category_id` AND c.`cname` IN ('化妆品','家电');

-- 先查询category表中的家电和化妆品
SELECT * FROM category WHERE cname IN ('化妆品','家电');
-- 将上面查询结果作为伪表使用

SELECT * FROM (SELECT * FROM category WHERE cname IN ('化妆品','家电')) c,products p WHERE c.`cid` = p.`category_id`;

第七章.mysql函数

mysql中的函数都是针对指定列中的数据进行操作

1.字符串函数

1.1.1 字符串函数列表概览

1.1.2 环境准备

-- 用户表
CREATE TABLE t_user (
  id int(11) NOT NULL AUTO_INCREMENT,
  uname varchar(40) DEFAULT NULL,
  age int(11) DEFAULT NULL,
  sex int(11) DEFAULT NULL,
  PRIMARY KEY (id)
);

insert  into t_user values (null,'zs',18,1);
insert  into t_user values (null,'ls',20,0);
insert  into t_user values (null,'ww',23,1);
insert  into t_user values (null,'zl',24,1);
insert  into t_user values (null,'lq',15,0);
insert  into t_user values (null,'hh',12,0);
insert  into t_user values (null,'wzx',60,null);
insert  into t_user values (null,'lb',null,null);

1.1.3 字符串连接函数

字符串连接函数主要有2个：

练习1：使用concat函数显示出 你好uname 的结果

/*
   concat(str1, str2, ...)
   字符串连接函数，可以将多个字符串进行连接
*/
SELECT CONCAT('a','b');


-- 练习1：使用concat函数显示出 你好uname 的结果
SELECT CONCAT('你好',uname),age FROM t_user;

练习2：使用concat_ws函数显示出 你好,uname 的结果

/*
  concat_ws(separator, str1, str2, ...)
  可以指定间隔符将多个字符串进行连接；
*/

SELECT CONCAT_WS(',','你好',uname),age FROM t_user;

1.1.4 字符串大小写处理函数

字符串大小写处理函数主要有2个：

练习1： 将字符串 hello 转换为大写显示

/*
  upper(str)得到str的大写形式
*/
-- 练习1： 将字符串 hello 转换为大写显示
SELECT UPPER('hello') ;
-- 查询t_user的uname和age列,显示结果将uname中的字符变成大写
SELECT UPPER(uname),age FROM t_user;

练习2：将字符串 heLLo 转换为小写显示

/*
lower(str)得到str的小写形式
*/

-- 练习2：将字符串 heLLo 转换为小写显示
SELECT LOWER('HELLO') ;

-- 查询t_user的uname和age列,显示结果将uname中的字符变成小写
SELECT LOWER(uname),age FROM t_user;

1.1.5 移除空格函数

可以对字符串进行按长度填充满、也可以移除空格符

练习1： 将用户id为8的用户的姓名的两边空白符移除

/*
  trim(str)将str两边的空白符移除
*/
-- 将用户id为8的用户的姓名的两边空白符移除

SELECT TRIM(uname),age FROM t_user WHERE id = 8;

1.1.6 子串函数

字符串也可以按条件进行截取，主要有以下可以截取子串的函数;

/*
  1：substr(str, pos)-> 从pos开始,且到最后
     substring(str, pos)-> 从pos开始,且到最后
     
     以上两个函数功能一样
     
  2.substr(str, pos, len)
    substring(str, pos, len)
    
    参数说明:
       str:要截取的字符串
       pos:从第几个字符开始切(不是字符串索引)
       len:切多少个字符
*/
SELECT SUBSTR('abcdefg',3);

SELECT SUBSTR('abcdefg',3,3);

练习1：获取 hello,world 从第二个字符开始的完整子串

-- 练习1：获取 hello,world 从第二个字符开始的完整子串
SELECT SUBSTR('hello,world',2);

练习2：获取 hello,world 从第二个字符开始但是长度为4的子串

-- 练习2：获取 hello,world 从第二个字符开始但是长度为4的子串
SELECT SUBSTR('hello,world',2,4);

第八章.sql练习

1.创建数据库

CREATE DATABASE mytest01;
USE mytest01;

2.创建表以及添加数据

# 创建部门表dept  部门表中包含 部门id 部门名称  
CREATE TABLE dept(
  id INT PRIMARY KEY AUTO_INCREMENT,
  NAME VARCHAR(20)
)

INSERT INTO dept (NAME) VALUES ('开发部'),('市场部'),('财务部');  

# 创建员工表
CREATE TABLE emp (
  id INT PRIMARY KEY AUTO_INCREMENT,
  NAME VARCHAR(10),
  gender CHAR(1),   -- 性别
  salary DOUBLE,   -- 工资
  join_date DATE,  -- 入职日期
  dept_id INT,
  FOREIGN KEY (dept_id) REFERENCES dept(id) -- 外键，关联部门表(部门表的主键)
)  

INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('小松松','男',7200,'2013-02-24',1);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('鱼小鱼','女',3600,'2015-12-02',2);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('小霈霈','男',8000,'2013-12-02',3);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('亮仔','男',5000,'2017-11-11',2);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('坤仔','男',8000,'2012-02-02',1);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('福姐','女',6500,'2011-09-12',3);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('熊姐','女',10500,'2018-12-02',3);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('猛哥','男',9500,'2016-07-08',2);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('栋栋','男',8500,'2018-06-28',2);

3.练习

-- 1.查询员工和部门的名字
SELECT emp.`name`, dept.`name` FROM emp,dept WHERE emp.`dept_id` = dept.`id`;

-- 2.查询鱼小鱼的信息，显示员工id，姓名，性别，工资和所在的部门名称(使用显式内连接)
SELECT * FROM emp e INNER JOIN dept d ON e.`dept_id` = d.`id` WHERE e.`name`='鱼小鱼';

-- 3.将上面查到的内容 表头使用别名的形式展示 比如显示id为员工id  name为姓名 等
SELECT e.id 编号,e.name 姓名,e.gender 性别,e.salary 工资,d.name 部门名字 FROM emp e INNER JOIN dept d ON e.dept_id = d.id WHERE e.name='鱼小鱼';

-- 4.在部门表中增加一个销售部 
INSERT INTO dept (NAME) VALUES ('销售部');
SELECT * FROM dept;

-- 5.查询所有的部门信息关联查询出该部门中的所有员工信息 
SELECT * FROM dept d LEFT JOIN emp e ON d.`id` = e.`dept_id`;

-- 6.查询所有的部门信息关联查询出该部门中的所有员工的名字  部门 以及 工资 
SELECT e.name 姓名,d.name 部门, e.salary 工资 FROM dept d LEFT JOIN emp e ON d.id = e.dept_id;

-- 7.统计出 每个部门的员工人数   查询显示 部门名称 人数 
SELECT d.name 部门,COUNT(e.name) 人数 FROM dept d LEFT JOIN emp e ON d.id = e.dept_id GROUP BY d.name;

-- 8.统计出 每个部门员工 平均薪资 按照 薪资排序 查询显示 部门名称 平均薪资 
SELECT d.name 部门,AVG(e.salary) 人数 FROM dept d LEFT JOIN emp e ON d.id = e.dept_id GROUP BY d.name ORDER BY salary;

-- 9.统计出，每个部门的平均薪资 按照薪资排序 并且筛选出平均薪资>7000的部门
SELECT d.name 部门,AVG(e.salary) 人数 FROM dept d LEFT JOIN emp e ON d.id = e.dept_id 
GROUP BY d.name HAVING AVG(e.salary)>7000 ORDER BY salary;

-- 10.查询最高工资是多少
SELECT MAX(salary) FROM emp;

-- 11.根据最高工资到员工表查询到对应的员工信息
SELECT * FROM emp WHERE salary=(SELECT MAX(salary) FROM emp)

-- 12.查询工资小于平均工资的员工有哪些
SELECT * FROM emp WHERE salary < (SELECT AVG(salary) FROM emp);

-- 13.查询工资大于5000的员工，来自于哪些部门的名字  
 SELECT dept.name FROM dept WHERE dept.id IN (SELECT dept_id FROM emp WHERE salary > 5000

-- 14.查询开发部与财务部所有的员工信息
SELECT * FROM emp WHERE dept_id IN (SELECT id FROM dept WHERE NAME IN('开发部','财务部'));

-- 15.查询出2011年以后入职的员工信息，包括部门名称
SELECT * FROM dept d, (SELECT * FROM emp WHERE join_date > '2011-1-1') e WHERE e.dept_id = d.id;