SQL拾遗

修改表结构：
1.增加列（字段）
alter table employee add name varchar(20) not null unique first username;
2.修改一列类型：
alter table employee modify age int after id;
3.修改列名：
alter table employee change age Age int default 28 first;

表记录之增删改：
1.增加一条记录：
insert into employee(id,name,birthday,salary) values(1,'yuan','1990-09-09',9000);
  或者
insert into employee values(1,'yuan','1990-09-09',9000);
  或者
insert into employee_new (name,salary) values ('xialv',1000);
或者多条
 insert into employee_new values (4,'alvin1','1993-04-20',3000),(5,'alvin2','1995-05-12',5000);
2.修改表记录：
UPDATE语法可以用新值更新原有表行中的各列。SET子句指示要修改哪些列和要给予哪些值。
WHERE子句指定应更新哪些行。如没有WHERE子句，则更新所有的行
update employee set birthday='1989-10-24' where id=1
update employee_new set salary=salary+4000 where name='yuan';

3.删除表记录：
如果不跟where语句则删除整张表中的数据
            delete只能用来删除一行记录
            delete语句只能删除表中的内容，不能删除表本身，想要删除表，用drop
            TRUNCATE TABLE也可以删除表中的所有数据，词语句首先摧毁表，再新建表。此种方式删除的数据不能在
            事务中恢复
delete from employee where name='alex';
删除所有记录：
delete from employee;
TRUNCATE table employee;

表记录之查：
-- 准备表
CREATE TABLE ExamResult(

   id INT PRIMARY KEY  auto_increment,
   name VARCHAR (20),
   JS DOUBLE ,
   Django DOUBLE ,
   OpenStack DOUBLE
);


INSERT INTO ExamResult VALUES  (1,"yuan",98,98,98),
                               (2,"xialv",35,98,67),
                               (3,"alex",59,59,62),
                               (4,"wusir",88,89,82),
                               (5,"alvin",88,98,67),
                               (6,"yuan",86,100,55);

1.过滤掉表中的重复数据
select distinct JS,name from ExamResult;
2.使用where，进行过滤查询
select * from ExamResult where name='yuan';
注：
如果是%则表示任意多字符，此例如唐僧,唐国强
如果是_则表示一个字符唐_，只有唐僧符合。两个_则表示两个字符：__

select * from ExamResult where name like'王%';

3.order by 指定排序的列
ASC升序，desc降序，其中ASC为默认值
 select * from ExamResult order by JS;

 4.group by 分组查询
 注意,按分组条件分组后每一组只会显示第一条记录，
group by字句，其后可以接多个列名，也可以跟having子句,对group by 的结果进行筛选

select class,SUM(price)from order_menu group by class HAVING SUM(price)>150;

having 和 where两者都可以对查询结果进行进一步的过滤，差别有：
                     <1>where语句只能用在分组之前的筛选，having可以用在分组之后的筛选；
                     <2>使用where语句的地方都可以用having进行替换
                     <3>having中可以用聚合函数，where中就不行

-- GROUP_CONCAT() 函数
SELECT id,GROUP_CONCAT(name),GROUP_CONCAT(JS) from ExamResult GROUP BY id;

mysql> select * from aa; 
+------+------+ 
| id| name | 
+------+------+ 
|1 | 10| 
|1 | 20| 
|1 | 20| 
|2 | 20| 
|3 | 200 | 
|3 | 500 | 
+------+------+ 
6 rows in set (0.00 sec) 
以id分组，把name字段的值打印在一行，逗号分隔(默认) 
mysql> select id,group_concat(name) from aa group by id; 
+------+--------------------+ 
| id| group_concat(name) | 
+------+--------------------+ 
|1 | 10,20,20| 
|2 | 20 | 
|3 | 200,500| 
+------+--------------------+ 

聚合函数： 先不要管聚合函数要干嘛，先把要求的内容查出来再包上聚合函数即可。
--(一般和分组查询配合使用)

-- （7） 重点：Select from where group by having order by
                -- Mysql在执行sql语句时的执行顺序：
                -- from  where  select group by  having order by
                -- 分析:
                select JS as JS成绩 from ExamResult where JS成绩 >70; ---- 不成功
                select JS as JS成绩 from ExamResult having JS成绩 >90; --- 成功

--  (8) limit
                SELECT * from ExamResult limit 1;
                SELECT * from ExamResult limit 2,5;--跳过前两条显示接下来的五条纪录
                SELECT * from ExamResult limit 2,2;


--- (9) 使用正则表达式查询
        SELECT * FROM employee WHERE emp_name REGEXP '^yu';

        SELECT * FROM employee WHERE emp_name REGEXP 'yun$';

        SELECT * FROM employee WHERE emp_name REGEXP 'm{2}';


多表查询：
      create table employee(
      emp_id int auto_increment primary key not null,
      emp_name varchar(50),
      age int,
      dept_id int
      );

      insert into employee(emp_name,age,dept_id) values
        ('A',19,200),
        ('B',26,201),
        ('C',30,201),
        ('D',24,202),
        ('E',20,200),
        ('F',38,204);


    create table department(
       dept_id int,
       dept_name varchar(100)
      );

    insert into department values
      (200,'人事部'),
      (201,'技术部'),
      (202,'销售部'),
      (203,'财政部');

mysql> select * from employee;
+--------+----------+------+---------+
| emp_id | emp_name | age  | dept_id |
+--------+----------+------+---------+
|      1 | A        |   19 |     200 |
|      2 | B        |   26 |     201 |
|      3 | C        |   30 |     201 |
|      4 | D        |   24 |     202 |
|      5 | E        |   20 |     200 |
|      6 | F        |   38 |     204 |
+--------+----------+------+---------+

mysql> select * from department;
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
|     200 | 人事部    |
|     201 | 技术部    |
|     202 | 销售部    |
|     203 | 财政部    |
+---------+-----------+

多表查询之连接查询
1.笛卡尔积查询
mysql> SELECT * FROM employee,department;

--        select employee.emp_id,employee.emp_name,employee.age,
--        department.dept_name from employee,department;

+--------+----------+------+---------+---------+-----------+
| emp_id | emp_name | age  | dept_id | dept_id | dept_name |
+--------+----------+------+---------+---------+-----------+
|      1 | A        |   19 |     200 |     200 | 人事部    |
|      1 | A        |   19 |     200 |     201 | 技术部    |
|      1 | A        |   19 |     200 |     202 | 销售部    |
|      1 | A        |   19 |     200 |     203 | 财政部    |
|      2 | B        |   26 |     201 |     200 | 人事部    |
|      2 | B        |   26 |     201 |     201 | 技术部    |
|      2 | B        |   26 |     201 |     202 | 销售部    |
|      2 | B        |   26 |     201 |     203 | 财政部    |
|      3 | C        |   30 |     201 |     200 | 人事部    |
|      3 | C        |   30 |     201 |     201 | 技术部    |
|      3 | C        |   30 |     201 |     202 | 销售部    |
|      3 | C        |   30 |     201 |     203 | 财政部    |
|      4 | D        |   24 |     202 |     200 | 人事部    |
|      4 | D        |   24 |     202 |     201 | 技术部    |
|      4 | D        |   24 |     202 |     202 | 销售部    |
|      4 | D        |   24 |     202 |     203 | 财政部    |
|      5 | E        |   20 |     200 |     200 | 人事部    |
|      5 | E        |   20 |     200 |     201 | 技术部    |
|      5 | E        |   20 |     200 |     202 | 销售部    |
|      5 | E        |   20 |     200 |     203 | 财政部    |
|      6 | F        |   38 |     204 |     200 | 人事部    |
|      6 | F        |   38 |     204 |     201 | 技术部    |
|      6 | F        |   38 |     204 |     202 | 销售部    |
|      6 | F        |   38 |     204 |     203 | 财政部    |
+--------+----------+------+---------+---------+-----------+

2.内连接
-- 查询两张表中都有的关联数据,相当于利用条件从笛卡尔积结果中筛选出了正确的结果。

  select * from employee,department where employee.dept_id = department.dept_id;
--select * from employee inner join department on employee.dept_id = department.dept_id;

      +--------+----------+------+---------+---------+-----------+
      | emp_id | emp_name | age  | dept_id | dept_id | dept_name |
      +--------+----------+------+---------+---------+-----------+
      |      1 | A        |   19 |     200 |     200 | 人事部    |
      |      2 | B        |   26 |     201 |     201 | 技术部    |
      |      3 | C        |   30 |     201 |     201 | 技术部    |
      |      4 | D        |   24 |     202 |     202 | 销售部    |
      |      5 | E        |   20 |     200 |     200 | 人事部    |
      +--------+----------+------+---------+---------+-----------+

3.外连接

--（1）左外连接：在内连接的基础上增加左边有右边没有的结果

 select * from employee left join department on employee.dept_id = department.dept_id;

     +--------+----------+------+---------+---------+-----------+
    | emp_id | emp_name | age  | dept_id | dept_id | dept_name |
    +--------+----------+------+---------+---------+-----------+
    |      1 | A        |   19 |     200 |     200 | 人事部    |
    |      5 | E        |   20 |     200 |     200 | 人事部    |
    |      2 | B        |   26 |     201 |     201 | 技术部    |
    |      3 | C        |   30 |     201 |     201 | 技术部    |
    |      4 | D        |   24 |     202 |     202 | 销售部    |
    |      6 | F        |   38 |     204 |    NULL | NULL      |
    +--------+----------+------+---------+---------+-----------+

 --（2）右外连接：在内连接的基础上增加右边有左边没有的结果

 select * from employee RIGHT JOIN department on employee.dept_id = department.dept_id;

        +--------+----------+------+---------+---------+-----------+
        | emp_id | emp_name | age  | dept_id | dept_id | dept_name |
        +--------+----------+------+---------+---------+-----------+
        |      1 | A        |   19 |     200 |     200 | 人事部    |
        |      2 | B        |   26 |     201 |     201 | 技术部    |
        |      3 | C        |   30 |     201 |     201 | 技术部    |
        |      4 | D        |   24 |     202 |     202 | 销售部    |
        |      5 | E        |   20 |     200 |     200 | 人事部    |
        |   NULL | NULL     | NULL |    NULL |     203 | 财政部    |
        +--------+----------+------+---------+---------+-----------+

 --（3）全外连接：在内连接的基础上增加左边有右边没有的和右边有左边没有的结果

    -- mysql不支持全外连接 full JOIN
    -- mysql可以使用此种方式间接实现全外连接
    
   select * from employee RIGHT JOIN department on employee.dept_id = department.dept_id
   UNION
   select * from employee LEFT JOIN department on employee.dept_id = department.dept_id;

        

        +--------+----------+------+---------+---------+-----------+
        | emp_id | emp_name | age  | dept_id | dept_id | dept_name |
        +--------+----------+------+---------+---------+-----------+
        |      1 | A        |   19 |     200 |     200 | 人事部    |
        |      2 | B        |   26 |     201 |     201 | 技术部    |
        |      3 | C        |   30 |     201 |     201 | 技术部    |
        |      4 | D        |   24 |     202 |     202 | 销售部    |
        |      5 | E        |   20 |     200 |     200 | 人事部    |
        |   NULL | NULL     | NULL |    NULL |     203 | 财政部    |
        |      6 | F        |   38 |     204 |    NULL | NULL      |
        +--------+----------+------+---------+---------+-----------+

      -- 注意 union与union all的区别：union会去掉相同的纪录

多表查询之复合条件连接查询

-- 查询员工年龄大于等于25岁的部门

    SELECT DISTINCT department.dept_name
    FROM employee,department
    WHERE employee.dept_id = department.dept_id
    AND age>25;


--以内连接的方式查询employee和department表，并且以age字段的升序方式显示

    select employee.emp_id,employee.emp_name,employee.age,department.dept_name
    from employee,department
    where employee.dept_id = department.dept_id
    order by age asc;

多表查询之子查询

-- 子查询是将一个查询语句嵌套在另一个查询语句中。
-- 内层查询语句的查询结果，可以为外层查询语句提供查询条件。
-- 子查询中可以包含：IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等关键字
-- 还可以包含比较运算符：= 、 !=、> 、<等


-- 1. 带IN关键字的子查询

   ---查询employee表，但dept_id必须在department表中出现过

   select * from employee
            where dept_id IN
            (select dept_id from department);


+--------+----------+------+---------+
| emp_id | emp_name | age  | dept_id |
+--------+----------+------+---------+
|      1 | A        |   19 |     200 |
|      2 | B        |   26 |     201 |
|      3 | C        |   30 |     201 |
|      4 | D        |   24 |     202 |
|      5 | E        |   20 |     200 |
+--------+----------+------+---------+
5 rows in set (0.01 sec)



-- 2. 带比较运算符的子查询
      --      =、!=、>、>=、<、<=、<>

     -- 查询员工年龄大于等于25岁的部门
     select dept_id,dept_name from department
           where dept_id IN
          (select DISTINCT dept_id from employee where age>=25);

-- 3. 带EXISTS关键字的子查询

-- EXISTS关字键字表示存在。在使用EXISTS关键字时，内层查询语句不返回查询的记录。
-- 而是返回一个真假值。Ture或False
-- 当返回Ture时，外层查询语句将进行查询；当返回值为False时，外层查询语句不进行查询

     select * from employee
              WHERE EXISTS
              (SELECT dept_name from department where dept_id=203);

      --department表中存在dept_id=203，Ture


     select * from employee
                WHERE EXISTS
              (SELECT dept_name from department where dept_id=205);

     -- Empty set (0.00 sec)


    ps:  create table t1(select * from t2);

--1.where后加限制条件，需要满足多个条件时不同条件之间加and
--like 模糊匹配要加%，单引号
'G%'，以G开头
select * 
from spm_order
where 
city in ('beijing','shanghai')
and profit >0
and product_name like "%笔%"；

--2.拼接字段（concat）
select* , concat(字段1，字段2) as '新字段名'
from 表名

select *，concat(city,"-",sale_name)
from spm_order;

--3.数据分组（group by）数据透视表
select 字段，计算字段
from 表名
where 条件
group by 字段

--计算销售人员在2018年的销售额和销售利润
select sale_name,sum(sales),sum(profit)
from spm_order
where order_date between "2018-01-01" and "2018-12-31"
group by sale_name;
--注意：like一般是对文字类（varchar)的字段使用，时间类型（date）的字段用between

--4.对数据结果过滤（having）
select 字段，计算字段
from 表名
where 条件
group by 字段
having 条件

--计算销售人员在2018年的销售额和利润，并且只显示利润大于10万的销售人员
select sale_name,sum(sales),sum(profit)
from spm_order
where order_date between "2018-01-01" and "2018-12-31"
group by sale_name
having sum(profit)>100000;

--having和where有什么区别？
where是对整个表，就是from从这个表里选的数据，是限制这张表的一个条件，
having是对上述查询的结果做一个筛选

--5.结果排序（order by ...desc）
select 字段，计算字段
from 表名
where 条件（非必须）
group by 字段
having 条件（非必须）
order by 字段 desc
默认升序，降序是DESC

--计算销售人员在2018年的销售额和利润，并且只显示利润大于10万的销售人员,按销售额从高到底降序排列
select sale_name,sum(sales),sum(profit)
from spm_order
where order_date between "2018-01-01" and "2018-12-31"
group by sale_name
having sum(profit)>100000
order by sum(sales) desc;

--一般分组之后排序，group by，order by

--数据类型：int整数，float小数，浮点数，varchar文本,date日期

--数值型：int（整数），float（小数）double（小数，比较大）

--日期型date()
year()

--订单表里有多少行
select count(*) from spm_order;
--这一列有多少行
select count(product_name) from spm_order;
--订单表里有多少城市
select count(distinct city) from spm_order;

select now(); -- 返回现在的时间
select datediff('2019-01-01','2019-09-30') --算两个日期相隔多久

select order_date,ship_date,datediff(ship_date,order_date) as "送货天数"
from spm_order;
select order_date,date_format(order_date,'%Y-%m') as '订单月份'
from spm_order;  --只保留月份

-- 在订单表中筛选出订单日期在2018年的所有订单
select * 
from spm_order
where order_date between "2018-01-01" and "2018-12-31";

内容：主要介绍复杂查询语言和SQL增删改语言（从入门到掌握）
1.掌握SQL多表联结查询（join，left join，right join等）
2.掌握SQL复杂查询：嵌套查询，子查询，组合查询，分组排序函数等
3.掌握SQL定义和控制语言（增删改）：update，delete，create，alter等

内连接：join和inner join 一样；表1 join表2，返回表1和表2共同的行

--join
select a.*,b.province,b.area,c.category,c.small_category
from spm_order a
left join spm_area b on a.city=b.city
left join spm_product c on a.product_id = c.product_id;

SQL复杂查询   
1.条件函数（case when）
-- 计算出盈利订单，不盈利订单和不盈不亏订单的销售额

SELECT CASE
           WHEN profit > 0 THEN '盈利'
           WHEN profit = 0 THEN '不盈不亏'
           ELSE '亏损'
           END    AS '订单类型',
       sum(sales) AS '销售额'
FROM spm_order
GROUP BY CASE
             WHEN profit > 0 THEN '盈利'
             WHEN profit = 0 THEN '不盈不亏'
             ELSE '亏损'
             END;

--分组排序函数(row_number)
1.无分组排序
row_number() over(order by 字段 desc)
例如：row_number() over(order by 学生成绩 desc)
表示不分班级，所有学生的成绩从高到低排序
2.分组排序 
row_number() over(partition by 字段1 order by 字段2 desc)
表示根据字段1分组，在分组内根据字段2排序，这个函数计算的值就表示每组内部排序后的顺序编号
例如：row_number() over(partition by 班级 order by 学生成绩 desc)
表示根据"班级"分组，在每个"班级"内部根据"学生成绩"排序，这个函数计算的值就表示每组内部排序后的顺序编号
解释：
row_number() 起到编号的功能
partition by 将相同数据进行分区
order by 使得数据按一定顺序排序

--计算销售人员的销售额，结果按从高到低排序，查询结果中要包含销售的排名
select sales_name,sum(sales),row_number() over(order by sum(sales) desc) as 'rank'
from spm_order
group by sales_name

结果：

--计算销售人员在不同城市的销售额；要求：结果根据销售人员在不同城市的销售额进行分组排序(降序)，并且查询结果要包含分组功能
select sales_name,city,sum(sales),row_number() over(partition by sales_name order by sum(sales) desc) as 'rank'
from spm_order
group by sales_name,city;

--分组一般和嵌套查询同时使用
嵌套查询：
-- 计算每个销售人员销售额排名top5的城市
select t1.* 
from
(select sales_name,city,sum(sales),row_number() over(partition by sales_name order by sum(sales) desc) as 'rank'
from spm_order
group by sales_name,city) t1
where t1.rank<=5;

--子查询(in)
select 字段
from 表名
where 字段 in (select 字段 from 表名 where 条件)
where city in ('北京'，'上海','广州','深圳','杭州')

--组合查询(union,两个表的结构一样)
union:去重，返回去重后的结果
union all：不去重，返回所有

select 字段
from 表名

union
select 字段
from 表名

union
select 字段
from 表名
--
select * 
from spm_order
where profit>0
union all 

select * 
from spm_order
where city in ('北京'，'上海','广州','深圳','杭州');

select 789456+416

--更新表内数据(update)
update order_2017 set date="2017-08-19" where order_id = "2017-07-09";
--更改表结构
alter table order_2017 add spend DOUBLE(10,2) --定义数据类型
alter table order_2017 drop spend；

--删除行
delete from 表名 where 条件;
--删除表
drop table 表名;
--删除数据库
drop database 数据库名;

--delete与drop的区别
delete删除表中的内容，不删除表；
drop删除表或者数据库；