9-2-约束条件、表之间的三种关系、记录相关操作

==============================> 主要概念 <==============================

索引:用来提升查找效率

    一般引擎除了建立结构文件、数据文件外, 还建立一个索引文件;
    而innodb引擎 并没有单独建立一个索引文件, 而是通过 主键 实现了索引


多个表之间的查询, 源于将一个有完整信息的大表拆分成多张表,从而方便管理

用户如果没有自定义主键:

    mysql找一个 not null unique的字段,作为主键
    (因此在mysql中: primary key  <---等效于--->  not null unique)

    如果没有这样的字段:

        则mysql自动生成一个隐藏的字段,作为主键


    所以, 约定俗成: 建一个表, 必须建立一个id ,作为主键

    id int primary key auto_increment

     如此,这个id开始从1开始自增长,自增加1

         可以自己指定从几开始,步长


    通常不会建立联合主键(即选择多个字段一起作为主键)


解耦合: 便于管理

    拆表,也是解耦合

    把本来属于一张表的东西,拆分成多张表

    逻辑层面 建立管关联, 所以最终查询出来的结果是一个虚拟的表
    物理层面 没有建立关联(硬性的关系)



foreign_key机制(用来解耦合):

    有了foreign_key之后, 必须要先建立外部的表,才能建立外部的表
    (例如  员工表employee_table 中的部门信息department_table拆分出去, 则department_table 是外部的表    employ_talbe是上层的表)
        即:  先创建被关联表, 再创建关联表
             先插入被关联表, 再创建关联表

    目的: 确保插入的数据有意义

        级联(同步的意思)更新 数据

            下属的表(即  被关联表)  删除了,foreign机制会自动将  上层的表(即 关联表)  中的相关的记录删除

    使用规则:
        foreign key(dep_id) references department(id) on delete cascade on update cascade //同步删除   同步更新

        cascase :级联的意思



在mysql:
    
    PRI : 主键的意思
    MUL : 外键的意思




归属关系:  左表 ------- 右表

    一对一
    多对一: 例如,多个员工都在同一个部门
    多对多
        双向(即站在左表和右表的两个角度)的多对一, 就是多对多


对于 多对一:
    一个表的多条记录   与  另一个表的多条记录 的关系
    哪个表是多条的    就把外键的字段 写在哪个表里



对于 多对多:
    由于都需要从被关联表开始建立,
    这很矛盾,不知道应该先建立那张表.
    所以,应另外建立一张表(第三张表)

    注意:
        第三张表中 还可以存其他的字段
        例如可以改写第三张表中,成为  成绩表,即再 添加一个 成绩 字段



对于 一对一:
    并非在哪一张表中建立外键都行,
    而是要考虑先往哪个表中建立fk+unique

    解决方式: 看先有谁
             例如: 教育机构是 先有 意向用户 再有 学生  
             就在 学生表中建立外键





单表查询的顺序:  后面一个操作必须等到前面一个操作执行结束才能执行
        要遵循 运行的优先执行顺序;即使不写, mysql也会按照这个顺序调用这些函数 

    where : 分组前的过滤 ,如果没有则使用where 1=1
    
    group by : 分组
        未使用 group by时,msyql就将所有 整体作为一个大分组

    having by : 分组后的过滤
    
    distinct : 去重
    
    order by : 排序
    
    limit n : 最终结果显示几条



聚合函数, 用于统计当期组中的统计信息 (只能和分组 配合使用)

    当还没有分组时, 不能使用统计(聚合)函数
    
    分组之后,是将所有的记录 分别装进若干个 麻袋里
    聚合函数 统计的是  一个麻袋中的 最大值、最小值、平均值等等...

    所以,聚合函数一定是在分完组之后 使用的



sql也是一门语言, 所以也具有其他语言的各种操作, 例如 算数运算 逻辑运算

    注意: 在数据库中, 空字符串''  不是 null

    sql查询语句, 可以结合 正则  使用,
        regexp 正则的规则

        ^x 以x开头
        x$ 以x结尾



配置sql_mode,只是在临时有效, 需要退出重新进入mysql  
如果想永久生效,需要写到配置文件中



as 起别名 (as也可以省略)  


==================================== 1 约束条件=========================

# not null default
    
    create table t1(x int not null);
    insert into t1 values();

    create table t2(x int not null default 111);
    insert into t2 values();

# unique
# 单列唯一

    create table t3(name varchar(10) unique);
    insert into t3 values("egon");
    insert into t3 values("tom");

    mysql> insert into t3 values("egon");
    ERROR 1062 (23000): Duplicate entry 'egon' for key 'name'

# 联合唯一

    create table server(
        id int,
        name varchar(10),
        ip varchar(15),
        port int,
        unique(ip,port),
        unique(name)
    );

    insert into server values (1,"web1","10.10.0.11",8080);
    insert into server values (2,"web2","10.10.0.11",8081);
    mysql> insert into server values(4,"web4","10.10.0.11",8081);
    ERROR 1062 (23000): Duplicate entry '10.10.0.11-8081' for key 'ip'
    mysql>


# not null 和unique的化学反应=>会被识别成表的主键

    create table t4(id int,name varchar(10) not null unique);
    create table t5(id int,name varchar(10) unique);


# 主键primary key
# 特点
#    1、主键的约束效果是not null+unique
#    2、innodb表有且只有一个主键，但是该主键可以是联合主键

    create table t6(
        id int primary key auto_increment,
        name varchar(5)
    );

    insert into t6(name) values
    ("egon"),
    ("tom"),
    ("to1"),
    ("to2");


# 联合主键（了解）

    create table t7(
        id int,
        name varchar(5),
        primary key(id,name)
    );


=============================2 表之间的三种关系============================

# 引入
# 先创建被关联表
    
    create table dep(
        id int primary key auto_increment,
        name varchar(6),
        comment varchar(30)
    );

# 再创建关联表

    create table emp(
        id int primary key auto_increment,
        name varchar(10),
        gender varchar(5),
        dep_id int,
        foreign key(dep_id) references dep(id) on delete cascade on update cascade
    );

# 先往被关联表插入数据

    insert into dep(id,name) values
    (1,'技术部'),
    (2,'人力资源部'),
    (3,'销售部');

# 先往关联表插入数据

    insert into emp(name,gender,dep_id) values
    ('egon',"male",1),
    ('alex1',"male",2),
    ('alex2',"male",2),
    ('alex3',"male",2),
    ('李坦克',"male",3),
    ('刘飞机',"male",3),
    ('张火箭',"male",3),
    ('林子弹',"male",3),
    ('加特林',"male",3)
    ;

# 多对一
# 多对多

    create table author(
        id int primary key auto_increment,
        name varchar(10)
    );
    create table book(
        id int primary key auto_increment,
        name varchar(16)
    );
    create table author2book(
        id int primary key auto_increment,
        author_id int,
        book_id int,
        foreign key(author_id) references author(id) on delete cascade on update cascade,
        foreign key(book_id) references book(id) on delete cascade on update cascade
    );

# 一对一

    create table customer(
        id int primary key auto_increment,
        name varchar(16),
        phone char(11)
    );

    create table student(
        id int primary key auto_increment,
        class varchar(10),
        course varchar(16),
        c_id int unique,
        foreign key(c_id) references customer(id) on delete cascade on update cascade
    );


==============================3 记录相关操作==============================


# 插入

    mysql> create table user(name varchar(16),password varchar(10));
    Query OK, 0 rows affected (0.29 sec)
    mysql>
    mysql> insert into user select user,password from mysql.user;

# 删除

    delete from 表 where 条件;

# 更新

    update 表 set 字段=值 where 条件;

# 单表查询语法

    select distinct 字段1,字段2,字段3,... from 表名
                                where 过滤条件
                                group by 分组的条件
                                having 筛选条件
                                order by 排序字段
                                limit n;

# 简单查询

    select name,sex from emp;
    select name as 名字,sex 性别 from emp;

    select * from emp;

# 避免重复（针对的是记录）

    select distinct post from emp;

# 进行四则运算

    select name as 名字,salary*12 as 年薪 from emp;

# concat()拼接记录的内容

    select name ,concat(salary*12,"$") from emp;
    select name ,concat("annual_salary",':',salary*12) as 年薪 from emp;
    select name ,concat("annual_salary",':',salary*12,':','$') as 年薪 from emp;
    select name ,concat_ws(":","annual_salary",salary*12,'$') as 年薪 from emp;

    select (
        case
        when name="egon" then
            name
        when name="alex" then
            concat(name,"_dsb")
        else
            concat(name,"_sb")
        end
    ) as 名字 from emp;

where

    select * from emp where id >= 3 and id <= 5;
    select * from emp where id between 3 and 5;
    select * from emp where id not between 3 and 5;

    select * from emp where id=3 or id=5 or id=7;
    select * from emp where id in (3,5,7);
    select * from emp where id not in (3,5,7);

    select * from emp where id=3 or id=5 or id=7;

    select * from emp where name like 'jin%';
    select * from emp where name like 'jin___';

    select * from emp where name regexp 'n$';

    mysql> select * from emp where post_comment is not null;
    Empty set (0.00 sec)

    mysql> update emp set post_comment='' where id=3;
    Query OK, 1 row affected (0.07 sec)
    Rows matched: 1  Changed: 1  Warnings: 0

    mysql> select * from emp where post_comment is not null;
    +----+---------+------+-----+------------+---------+--------------+---------+--------+-----------+
    | id | name    | sex  | age | hire_date  | post    | post_comment | salary  | office | depart_id |
    +----+---------+------+-----+------------+---------+--------------+---------+--------+-----------+
    |  3 | wupeiqi | male |  81 | 2013-03-05 | teacher |              | 8300.00 |    401 |         1 |
    +----+---------+------+-----+------------+---------+--------------+---------+--------+-----------+
    1 row in set (0.00 sec)

    mysql>



    select * from emp where name="丫丫";
    select * from emp where name regexp "丫$";
    select * from emp where name like "丫_";
    select * from emp where name regexp "^程";
    select hex(name) from t4  where hex(name) regexp 'e[4-9][0-9a-f]{4}';



group by

    分完组之后只能看到分组的字段以及聚合的结果

    max()
    min()
    avg()
    sum()
    count()

        select depart_id,count(id),avg(salary),max(age),min(salary),sum(salary) from emp group by depart_id;

    # 每个部门都有多少个人
        select depart_id,count(id) from emp group by depart_id;

    # 每个职位男生的平均薪资
        select post,avg(salary) from emp where sex="male" group by post;
        select post, group_concat(name) from emp group by post;
        select post, group_concat(name) from emp where sex="male" group by post;


having
    
    # having与where本质区别就是在于having是在分组之后发生过滤，可以使用聚合函数

        mysql> select max(salary) from emp where max(salary) > 100000;
        ERROR 1111 (HY000): Invalid use of group function
        mysql> select max(salary) from emp having max(salary) > 100000;
        +-------------+
        | max(salary) |
        +-------------+
        |  1000000.31 |
        +-------------+
        1 row in set (0.00 sec)

        mysql>



    # 找出来男生平均薪资大于3000的职位

        select post,avg(salary) from emp where sex="male" group by post having avg(salary) > 8000;



order by排序

    select * from emp order by salary;
     select * from emp order by salary desc;
     select * from emp order by age,id desc;

     select post,avg(salary) from emp where sex="male" group by post having avg(salary) > 3000;
     mysql> select post,avg(salary) from emp where sex="male" group by post having avg(salary) > 3000;
    +-----------------------------------------+---------------+
    | post                                    | avg(salary)   |
    +-----------------------------------------+---------------+
    | operation                               |  16000.043333 |
    | teacher                                 | 175650.051667 |
    | 老男孩驻沙河办事处外交大使                |   7300.330000 |
    +-----------------------------------------+---------------+
    3 rows in set (0.00 sec)

    mysql> select post,avg(salary) from emp where sex="male" group by post having avg(salary) > 3000 order by avg(salary);
    +-----------------------------------------+---------------+
    | post                                    | avg(salary)   |
    +-----------------------------------------+---------------+
    | 老男孩驻沙河办事处外交大使                |   7300.330000 |
    | operation                               |  16000.043333 |
    | teacher                                 | 175650.051667 |
    +-----------------------------------------+---------------+
    3 rows in set (0.00 sec)

    mysql> select post,avg(salary) as v from emp where sex="male" group by post having avg(salary) > 3000 order by v;
    +-----------------------------------------+---------------+
    | post                                    | v             |
    +-----------------------------------------+---------------+
    | 老男孩驻沙河办事处外交大使                |   7300.330000 |
    | operation                               |  16000.043333 |
    | teacher                                 | 175650.051667 |
    +-----------------------------------------+---------------+
    3 rows in set (0.00 sec)

    mysql>



    select * from emp limit 0,5;
    select * from emp limit 5,5;
    select * from emp limit 10,5;
    select * from emp limit 15,5;
    select * from emp limit 20,5;