day-18-数据库-MySQL-查询和多表

01.SQL语句DQL查询语句简单查询

1.查询的基本语法： select 字段列表 from 表名 [where 条件]

2.简单查询：

1).查询product表中的所有字段的所有数据：
        select*from product；
2).查询product表中部分字段的所有数据：查询结果中只需要显示商品名称和价格：
        select pname，price from product；
    注意：“字段列表”的顺序可以跟表中字段的循序不一致。
3).别名查询，关键字是as：
        select pname as ’商品名称‘ from product；或
        select pname  ’商品名称‘ from product；
4).去掉重复值：
        例如：查询所有商品的价格，相同的价格只显示一个即可；
        select distinct price from product；
5).对查询的结果列进行计算：
        例如：查询所有商品的商品名称和商品价格，要求查询结果中的价格+100显示；

3.编写顺序：select….from….

执行顺序：1).from 2）.select

02.SQL语句DQL查询语言条件查询：select…from…where….

1.比较运算符：

1).">":大于；不能用于中文字段查询；
        例如：查询价格大于2000元的 商品信息；
        select*from product where price >2000；
                    表名            列名
2)."<":小于；
        例如：查询价格小于2000元的 商品信息；
        select*from product where price <2000；
                    表名            列名
3).">=":同上
4)."<=":同上
5)."="：同上，可以用于中文字段查询；
6)."!="或者“<>”:不等于；同上

2.逻辑运算符：

1）.“and”：与，语义“并且”
    例如：查询价格在2000到5000元之间的商品
        select*from product where price>=2000 and price<=5000;
2）.“or”：或，语义“或者”;同上
    注意：and和or混合查询，可以使用（）来表示优先级；
3）.“not”:非，语义“不是....”;同上

3.范围查询： between..（小值）（包含）.and..（大值）（包含）.

例如：
    查询商品价格在2000到5000元之间的所有商品：
        select*from product where price between 2000 and 5000;

4.多个值的等性条件：in（值列表）——代替多个or的等性判断；

例如：
    查询商品价格等于5000元或者等于2000元，或者等于3000元，或者等于1元；
    使用or语句；
    使用in语句：
        select*from produce where price in（5000,2000,3000,1）；

5.模糊查询：like

例如：
    查询商品名称中包含“花”的商品：
        select*from produce where pname like '%花%';
    查询商品名称以“花”开头的商品：
        select*from produce where pname like '花%';
    查询商品名称一共四个字，以“花花”开头：
        select*from produce where pname like '花花_';

6.null状态查询：

例如：
    添加数据时：
        insert into product values（14，’篮球‘，300，null，’2017-10-10‘）；
    查询没有类别的商品（类别为null）
        select*from product where caregory_id=null；
        //错误
        select*from product where caregory_id is null； // 正确

03.SQL语句DQL查询语句排序查询

1.需求：查询所有商品，查询结果要求按照商品价格从低到高排序：

select*from product order by price asc； //升序
select*from product order by price desc； //降序

2.对多列进行排序：

例如：要求查询所有商品信息，结果按照商品价格降序排列，如价格相同按照生产日期升序排列；
    select*from product order by price desc，proDate asc；
注意：对于多排排序，是先对第一个排序列进行排序，如果第一个排序列的值不同，就不按照第二排序列排序。
只有当第一序列中的值相同，才会按第二排序列进行排序；

3.目前我们学习的语句的编写顺序：

select...from...where..order by...

执行顺序：
    1.from   2.where  3.select  4.order by

04.SQL语句DQL查询语句聚合查询

1.count：统计指定列不为NULL的记录行数；

count（字段名/*）：统计指定列不为null 的记录“行数”；
select count（*）from product；

2.sum（列名）：计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为0；—数值类型列

select  sum（price） form product where category_id=’服装‘；

3.max：计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算；—数值类型，日期类型，英文字母；

select  max（price） form product where category_id=’服装‘；

4.min：计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算；—数值类型，日期类型，英文字母；

select  min（price） form product where category_id=’服装‘；

5.avg：计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为0；如果此列的值有null ，将不参与运算，很可能导致结果不正确；

select  avg（price） form product where category_id=’服装‘；

注意：
    聚合函数的结果是“计算”出来的结果，与任何记录无关，它可能跟某行的这一列的值相同，但绝不是这个值。
    聚合函数的查询结果中，只能包含聚合函数的结果，不能包含其他列，否则是无意义的；
    也可以同时查询多个局和函数；

05.SQL语句DQL查询语句分组查询

1.分组查询的语句：group by 分组字段；

1).需求：一条SQL语句，查出每种商品的最高价格；
        商品类别    最高价格
        服装      5000
        食品      1000
        ...     ....
    这就需要对："商品类别"进行"分组"
2).分组的语句：group by 分组字段
    例如：
        select category_id,max(price) from product group by category_id;

2.注意：

1).分组查询的结果中，最多只能出现：分组字段、聚合结果字段。不能包含其他字段了，如果包含了也是无意义的；
2).分组 的执行方式： 将这列中相同的值作为一组，在这个分组的基础上在进行聚合查询；
3).注意：
    1)."分组"查询的结果中，最多只能出现：分组字段、聚合结果字段。不能包含其他字段了，如果包含也是无意义的。
    2)."分组"的执行方式：将这列中相同的值作为一组。在这个分组的基础上再进行聚合查询。
    3).练习：
        student表：
        id        name     sex   age     km
        1         张三      男    20    JavaEE
        2         李四      男    22    JavaEE
        3         王五      女    21    IOS
        4         周六      女    18    JavaEE
        5         赵奇      男    22    IOS

        1).需要：各科目有多少学员
        select km,count(*) from student group by km;
        结果：
                km     count（*）
                JavaEE   3
                IOS      2
        2). 对多列进行分组：
        需求：查询各科目中男女学员个多少个？
            select km,sex,count(*) from student group by km,sex;
        结果：
            km      sex    count（*）

06.SQL语句DQL查询语句对聚合后的结果进行筛选_having 语句

1.之前我们学过的语句的编写顺序：

    select ...from..where...group by...order by...
执行顺序：
    1.from 2.where 3.group by 4.聚合函数 5.select 6.order by
使用：having语句：
select category_id,max(price) from product group by category_id having max(price) >= 2000;

07.SQL语句多表概念：

1..需求：要大家做个表格，存储学员以下信息：姓名、性别、年龄、科目;

教师信息表：姓名、性别、年龄、科目
学员信息表：
id 姓名 性别 年龄 科目ID
1 张三 男 20 1
2 李四 女 22 1
3 王五 男 22 2
4 周六 女 18 1

    教师信息表：
    id  姓名  性别  年龄  科目
    1   章子怡 女   28  1
    2   周润发 男   29  2
    3   汪峰  男   24  1
    4   撒贝宁 男   26  10

    科目表：
    id  科目名称
    1   Java企业级开发
    2   IOS

2.需要分表存储，将”科目信息”单独存储到一个表中，将”科目信息”与”学员信息”和”教师信息”的紧耦合解开。通过科目表的Id字段进行关联。之后，要修改”科目信息”，直接修改”科目表”即可，其它表的数据不需要改。提高后期的可维护性。

08.SQL语句_多表-主表和从表的概念

1.当分表后，一个表的一个字段引用另外一个表的一个字段；有两个概念：

    1).主表：被其它表引用的表(科目表)
    2).从表：引用其它表的表(学员信息表、教师信息表)

09.SQL语句_多表-外键的概念：

1).从表引用主表中的某个字段的值：
    主表：被引用的字段通常是"主键字段";
    从表：引用了主表中"主键字段"的字段，叫：外键字段；

2).外键字段的值的特点：必须参考主表的主键字段的值，不能出现主表的主键字段中没有的值。
3).注意：
    1).外键字段的名称：可以跟主表中主键字段的名不同；
    2).外键字段的数据类型：要跟主表中主键字段的数据类型相同，或者比之更宽。

10.SQL语句_多表-外键约束：

1).外键约束的作用：要求数据库软件为我们监控"外键字段"的值，必须参考主表中主键字段的值，如果出现主表中主键字段中没有的值，将会报错。
                   一句话：强制外键字段的值必须是主表的主键字段中的值。
2).建立外键约束：
    alter table 从表 add [constraint] [外键名称] foreign key (从表外键字段名) references 主表 (主表的主键);
    示例：
    ALTER TABLE student ADD CONSTRAINT fk_student FOREIGN KEY (kmid) REFERENCES km (id);
3).要会在SQLYog中用"架构设计器"来管理约束(连线)

11.SQL语句多表表和表之间的关系：

1).一对多关系【比例最高】：
    应用场景：学员表和科目表(前提：一个学员只能选一门课程)
        主表：科目表
        从表：学员表
        主-->从：一对多，从-->主：一对一，这时就可以确定：主表到从表：1对多关系
2).多对多关系【比例较少】：
    应用场景：学生和课程(前提：一个学生可以选择多个课程)
        学员表：
        id  name 性别 年龄  身高
        1   张三              

        课程表：
        id  name    
        1
        2
        3

        学员_课程(关系表)
        学员ID        课程ID
        1       1
        1       2
        1       3
        2       1
        2       2

建表原则：创建一个"中间表"作为关系表，里面至少要有两列，各自引用两个主表的主键字段。
3).一对一关系【基本没有】：