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).一对一关系【基本没有】：