MySQL学习02

一、where子句
select 字段 from 表名 where 子句;
在where子句中可以使用关系运算符、逻辑运算符，当子句的条件为真的数据才会显示对应的字段数据
where 子句可以有：
a、关系运算符
= != > < >= <=
注意：因为在SQL中无需、也不能定义变量，因此=运算符只能用来判断关系是否相等
练习：公司打算给工资低于1000的员工补贴200块，显示补贴员工的名字和补贴前后工资
select first_name,salary,salary+200
from s_emp
where salary < 1000;

补充：
    in 和 = 都是SQL中的用于比较值的运算符，但是是有区别的：
    区别：  
    1、= 运算符用于比较两个值是否是相等的，但是当使用 = 运算符时，子查询就必须要返回一个唯一的值，
    如果子查询返回的值是多个的话，那么便会抛出错误
    2、in 运算符用于检查某个值是否存在于一组值中，与 = 不一样，in是可以处理子查询中返回的多个值

    也就是说 = 用于比较两个值是否相等，而IN则是用于检查某个值是否存在于一组值中，在处理子查询返回
    多个值的时候，应该使用in运算符


b、逻辑运算符
    and or not
    && || ! 还可以继续使用
    练习：查询属于部门41的工资低于1000的员工的信息
    select *
        from s_emp
            where dept_id=41 and salary<1000;
    注意：在SQL中 not(!) 比关系运算符的优先级要低

c、特殊条件
    is null\is not null:
    对于null状态，只能通过 is null、is not null 来判断该字段是否为空状态，在SQL中null不是一个特定的值，而是一种状态，因此不能直接使用关系运算符比较
    select * from 表名 where 字段 is null;
    select * from 表名 where 字段 is not null;

    between a and b 
    当字段的值出现在[a,b]之间时为真，如果使用关系、逻辑配合也可以达到同样效果
        select * from 表名 where 字段 between a and b;
    
    in(a,b,c,...)
    当字段的值出现在in后面的数值列表中，为真
        select * from 表名 where 字段 in(a,b,c,...);
    
    like '%str'
    进行模糊查询，%可以通配任意多个字符 -可以通配任意1个字符
    注意：一般字符型字段比较适合模糊查询，但是数值型也可以，数据库会自动转换成字符串比较

    例子：查询名字以M开头的员工姓名
    select first_name
        from s_emp
            where first_name like "M%";

d、子查询
    把一条select语句的查询结果作为另一条select语句where子句的数据源

    min(字段)   查询出该字段最小值

    注意：min组函数不能直接用在where子句中

    练习：找出比最低工资高的员工姓名和工资
    select last_name,first_name,salary
        from s_emp
            where salary > ( select min(salary) from s_emp);

二、排序
select 字段 from 表名 order by 排序字段 [asc/desc];
//asc 升序 默认
//desc 降序
//MySQL排序中把null当作最小值，Oracle中当作最大值
select 字段
from 表名
where 条件
order by 排序字段 [asc/desc]；

三、多表查询(连接查询)
查询每个员工所属部门名？
select first_name,name from s_emp,s_dept;

当需要的数据分布在不同的表中，就需要把多张表进行连接查询，如果无任何
限制条件的进行多表查询，会产生"笛卡尔积"有海量的无效数据，需要配合where
子句进行多表查询，也称为交叉连接查询。

select  表1.字段，表2.字段 from 表1，表2 where 表1.字段 = 表2.字段
例如：
    select first_name,name
        from s_emp,s_dept
            where dept_id = s_dept.id;

注意：多表中的字段名可能重名，需要 表名.字段名 进行区分

注意：交叉连接查询是先产生"笛卡尔积"，然后再从海量的结果中根据where条件筛选出
合适的结果，如果"笛卡尔积"非常大时，交叉连接查询的效率就很低。

所谓的"笛卡尔积"是指将一个表中的每一行与另一张表中的每一行进行组合，例如第一个表有m行，
第二个表有n行，那么笛卡尔积也就是m*n

根据连接方式和where子句，进行不同的连接查询:
内连接：
    主要通过设置连接条件的方式，来移除查询结果中无效的数据行的交叉连接结果，
    就是消除了无效的"笛卡尔积"，也称为"等值连接"。
    格式：
        select 字段
            from 表1 inner join 表2 on 连接条件;
外连接：
    左连接：left join 
        1、以表1为主表，表2为副表，会把表1的数据都查询出来，表2只查询符合连接条件
        的数据
        2、把表1的数据只显示不符合连接条件的数据
    右连接：right join
        类似左连接
    全连接: full outer join
        注意：MySQL中不支持 full outer join
            但是可以使用union 替代全连接的效果
        (左连接1) unoin (右连接1) = 全连接1；
        (左连接2) unoin (右连接2) = 全连接2；
    
    练习：查询出每个部门的部门id、部门名、所属地区名(不能产生笛卡尔积)
    select s_dept.id,s_dept.name,s_region.name
        from s_dept inner join s_region
            on s_dept.region_id = s_region.id;

四、取别名
1、一次查询一张表无法查询出结果，有时需要同一张表查询多次，进行自连接查询

2、自连接的表名是相同的，所以需要取别名进行区分，或者表名太长也可以通过取别名
来简化表名
3、如果两张表的字段数据有重复，需要通过排重筛选，以及需要通过内连接
解决"笛卡尔积"问题
    练习：查询出所有领导的名字和id
    方法1：
        select distinct s2.id,s2.first_name
            from s_emp as s1 inner join s_emp as s2
                on s1.manager_id = s2.id;

    方法2：通过子查询
        select id,first_name
            from s_emp
                where id in (select manager_id from s_emp);

五、分组查询
1、把表中的数据按照分组标准划分成不同的组

select 分组标准字段或组函数处理过的字段 
    from 表名
        group by 字段；

注意：在分组查询语句中，select后面的字段要么是分组标准字段、要么是
经过合适的组函数处理后的字段

group_concat(字段a) 把每个分组中的字段a的所有值显示
练习：分组查询出相同部门的员工姓名、部门id
select dept_id,group_concat(first_name)
    from s_emp
        group by dept_id;

常用的组函数：
    min(字段)   求该字段的最小值
    max(字段)   求该字段的最大值
    count(字段) 求该字段分组后数据的数量
    sum(字段)   求该字段分组后数据的和
    avg(字段)   求该字段分组后数据的平均值
    group_concat(字段)  把该字段分组后每个组中的所有值显示

select dept_id,group_concat(first_name),count(first_name),max(salary)
    from s_emp
        group by dept_id;

    注意：组函数不能出现在where条件中

2、如何有条件地过滤分组后的数据？
    select 分组标准字段或组函数处理过的字段 
        from 表名
            group by 字段；
                having 过滤条件;

    注意：having支持where子句的所有语法和操作符

练习：查询出平均工资高于1100的部门id以及平均薪资
select dept_id,group_concat(salary),avg(salary)
    from s_emp
        group by dept_id
            having avg(salary) > 1100;//改为where则会报错

where和having的差异：
    1、一般情况下，where用于过滤分组前数据行,having 用于分组后的
    数据。
    2、where子句中不能使用组函数，而having则是可以使用组函数。

六、复杂的select 语句的顺序要求：
select [distinct] 字段、分组标准字段、组函数
from 表名 [连接方式] [表名] [as 别名]
where/on 条件/连接标准
group by 分组标准字段
having 分组过滤条件[组函数]
order by 排序的字段 [desc/asc]

练习：查询各个部门工资高于1000的员工的部门的平均薪资，只降序显示
平均薪资高于1100的部门id、部门员工名、平均薪资

七、表的三范式
1NF、要保证每个字段具有原子性不可再拆分，没有重复字段
2NF、表中要有主键且所有字段不能与主键部分依赖，如果不满足可以通过拆表来满足
3NF、确保每个字段都与主键直接相关，而不是间接相关，如果是可以通过消除冗余
项或者拆表来满足

在使用关系型数据库的过程中总结的三项建立表的准则，如果遵守它能够最大效率的使用
数据库

学生表
    学号 姓名 性别 身份证号 班级编号
    create table student(
        id int primary key,
        name varchar(30) not null,
        sex char default 'w',
        id_card char(18) unique,
        class_id int
        foreign key(class_id) references class(id)
    ); 

成绩表
    学号 总分 语文 数学 英语
create table grade(
    id int primary key,
    total double check (total >= 0 && total <300),
    c_grade double check (c_grade >= 0 && c_grade <300),
    m_grade double check (m_grade >= 0 && m_grade <300),
    e_grade double check (e_grade >= 0 && e_grade <300)
);

班级表
    班级编号 教室 班主任 语文老师 数学老师 英语老师
create table class(
    id int,
    room_id int,
    class_tch int,
    c_tch int,
    m_tch int,
    c_tch int,
    foreign key(class_tch) references teacher(id);
    foreign key(c_tch) references teacher(id);
    foreign key(m_tch) references teacher(id);
    foreign key(e_tch) references teacher(id);
);

教师表
    工号 姓名 性别 身份证号 科目 职称
create table teacher(
    id int prim
    ary key,
    name varchar(30) not null,
    sex char default 'w',
    id_card char(18) unique,
    subject char(20),
    title char(20)
);

八、表的约束
约束是对表和表中数据的限制，可以提高表中数据的准确性和可靠性，一般在创建表、修改表时使用
约束的分类：
not null 非空约束 字段的数据不能为空，例如学生姓名
default 默认值约束 给字段设置默认值后，当插入数据不提供该字段的数据时，数据库会自动填充为默认值，例如学生性别
unique 唯一约束 该字段数据不能重复，但是可以为null，例如身份证号
primary key 主键约束 非空且唯一，只有一个，例如学号
check 检查约束 设置一个条件，当插入数据不满足该条件时会失败，但是MySQL不支持\Oracle支持
foreign key 外键约束 用表A中的数据来确保表B中数据的准确性，例如表B中的某个字段值必须在表A中的某个字段的值中出现过，否则无法插入，例如教师表中的教师号必须在教师表中出现过

约束的设置方式：
    创建表时设置约束：
    create table 表名(
        字段名 类型 约束名,
        ...
    );

    修改表时设置约束：只有not null\default 有效，可以增加、删除约束
        alter table 表名 modify 原字段名 类型名 约束;
        --  增加约束
        alter table 表名 modify 原字段名 类型名;
        --  删除约束
    删除约束：只有unique\primary key有效
        alter table 表名 drop constraint 约束名;

外键约束：
    一张表(子表)的值必须引用自另一张表(父表),并且被引用的父表的字段必须具备唯一性，子表中被外键约束的字段值必须来自父表或者为null
    一般在创建表的外键约束时，需要先创建父表，再创建子表
    create table 父表(
        字段a 类型 primary key/unique;
    );
    create table 子表(
        字段b 类型;
        ...
        foreign key(字段b) references 父表(字段a);
    );

    也可以在后期添加外键，不建议，可能非法值已经存在
    alter table 子表 add foreign key(子表字段) references 父表(父表字段);

    注意：应该先插入父表数据，再插入子表数据
    
    父表更新、删除数据：
        1、先修改\删除子表，再删除父表
        2、设置级联删除、级联更新
            on delete cascade 级联删除
                父表数据删除，子表对应数据也随之删除
            on update cascade 级联更新
                父表数据更新，子表对应数据也随之更新
        
        create table 子表(
            ...
            foreign key(字段b) references 父表(字段a) on delete/update cascade;
        );