day03 mysql外键 表的三种关系 单表查询 navicat

day03 mysql navicat
 
一.完整性约束之
    外键 foreign key
    一个表(关联表: 是从表)设置了外键字段的值, 对应的是另一个表的一条记录(被关联表: 是主表)
 
    如何建立两张关系表?
    先要创建主表: 因为别人要关联你, 你要先存在
        > create table mas(id int primary key auto_increment, name varchar(20) not null, des varchar(30) not null);
    再创建从表: 
        > create table sla(
            eid int primary key auto_increment,
            name char(10) not null, age int not null,
            mas_id int not null,
            constraint fk_mas foreign key(mas_id) references mas(id) on delete cascade on update cascade           #(constraint 约束)(reference 参考)(cascade 级联)
            #约束外键名 外键(字段) 参考哪个表(哪个字段) 同步删除 同步更新
            );
 
    如何给两张表插入数据:
    先给主表插入数据
        > insert into mas(name,des) values('信息部','信息企划查询'), ('采购部','信息采购查询'), ('技术部','信息技术查询');
    再给从表插入数据
        > insert into sla(name,age,mas_id) values('bajie',12,1),('wukong',13,3),('datang',14,2),('xixi',10,1),('haha',19,3),('lele',16,1);
   
  如何删两个表的记录, 删的时机
    主表的记录
        当主表被从表参考的字段的值, 在从表中没有引用时, 可以删, 否则报错
        当从表设置了同步删, 同步更新后, 那就什么时候删都行,随意了,注意从表中对应的外键的记录也全删了: on delete cascade on update cascade      //(cascade 级联)
            从表关联时用这个: constraint fk_mas foreign key(mas_id) references mas(id) on delete cascade on update cascade       //MUL
            加上这句: 才是建立关联表的完整的方式
    从表的记录
        什么时候删都行, 随意
    当表比较多的时候
        不适合建立太多的外键  
        
二.外键的变种 三种关系
    如何找出两张表之间的关系
    假设有两张表: 左表和右表,分析步骤:
        步骤一: 站在左表的角度, 如果左表的多条记录对应右表的一条,那么左表上有 foreign key
        步骤二: 站在右表的角度, 如果右表的多条记录对应左表的一条,那么右表上有 foreign key
   
  多对一:
        如果步骤一和步骤二: 只有一个成立,那么是 多对一的关系(一对多一样,因为可以理解成左右互换时的状态)
    书和出版社的关系:
        一个出版社可以出版多本书
        但是多个出版社不能出同一种书(除非是盗版)
        如何建表:
            先建立出版社
            再建立书: 给书设置外键
    
    多对多:
        如果步骤一和步骤二: 同时成立,需要定义一个这两张表的关系表来专门存放二者的关系
    作者和书的关系
        一个作者可以写多本书
        多个作者可以共同写一本书
        如何建表:
            创建两张关系表: 为两个普通表
            额外建立第三张表: 设置两个外键分别对应两个关系表
        代码:
            建立三张表
            > create table author(id int primary key auto_increment, name varchar(20));
            > create table book(id int primary key auto_increment, name varchar(20));
            > create table author_book(
                id int primary key auto_increment,
                book_id int not null,
                author_id int not null,
                constraint fk_book foreign key(book_id) references book(id) on delete cascade on update cascade,
                constraint fk_author foreign key(author_id) references author(id) on delete cascade on update cascade
                );
            插入数据
            > insert into author(name) values('八戒'),('悟空'),('大唐'),('溜溜');
            > insert into book(name) values('一本书'),('两本书'),('三本书'),('四本书'),('五本书'),('六本书');
            > insert into author_book(author_id,book_id) values(1,1),(1,2),(1,3),(1,4),(1,5),(1,6),(2,6),(3,4),(3,6),(4,1),(4,3);
   
  一对一:
        如果步骤一和步骤二: 都不成立,这种简单: 就是在左表foreign key基础上,再将外键字段设置唯一"unique"即可
    用户和博客的关系
        一个用户名只能注册同一个网站上的一个博客
    如何建表:
        先建立一张表
        建立另一张表: 设置外键, 并且设置 unique
  
三.单表查询
    1.单表查询的语法
        select 字段1,字段2... from 表名
            where 条件                                //(condition 条件)
            group by field
            having 筛选
            order by field
            limit 显示限制条数
    
    2.关键字的执行优先级
            from          //找到表  
            where         //条件查询
            group by      //如果不分组, 则整体看做一组  
            having        //将分组结果: 二次过滤
            select        //执行select,查询显示出来
            distinct      //去重: (distinct 不同的)
            order by      //按条件排序,然后可选升序或降序(默认升序)
            limit         //限制结果的显示条数
select group_concat(name),salary from employee where salary > 1000 group by salary having count(1) < 10 order by salary desc limit 3;
+--------------------+------------+
| group_concat(name) | salary     |
+--------------------+------------+
| wukong             | 1000000.31 |
| tianchi            |   30000.00 |
| 程咬金             |   20000.00 |
+--------------------+------------+
            sql语句的执行过程:
                找到表 -> 拿着where指定的约束条件,去表中取出一条条记录 -> 将取出的一条条记录进行group by分组, 如果没有group by,则整体作为一组
                 -> 将分组的结果进行having过滤 -> 执行select -> 去重 -> 将结果按条件排序order by -> limit限制结果的显示条数
    
    3.准备公司员工表
    company.employee(公司.雇员)    
        员工id          id                          int                  
        姓名            name                        varchar                                                             
        性别            sex                         enum                                                                  
        年龄            age                         int
        入职日期        hire_date                   date
        岗位            post                        varchar
        职位描述        post_comment             varchar
        薪水            salary                    double
        办公室          office                      int
        部门编号        depart_id                   int
 
    创建表
    create table employee(
        id int primary key auto_increment,
        name varchar(20) not null,
        sex enum('male','female') not null default 'male',     #大部分是男的
        age int(3) unsigned not null default 28,
        hire_date date not null,
        post varchar(50),
        post_comment varchar(100),
        salary  double(15,2),
        office int,                                            #一个部门一个屋    
        depart_id int
        );   
 
    插入记录   
    insert into employee(name ,sex,age,hire_date,post,salary,office,depart_id) values
        ('bajie','male',18,'20170301','visitor',7300.33,401,1),         #以下是教学部
        ('wukong','male',78,'20150302','teacher',1000000.31,401,1),
        ('datang','male',81,'20130305','teacher',8300,401,1),
        ('shaseng','male',73,'20140701','teacher',3500,401,1),
        ('bailong','male',28,'20121101','teacher',2100,401,1),
        ('xizi','female',18,'20110211','teacher',9000,401,1),
        ('tianchi','male',18,'19000301','teacher',30000,401,1),
        ('xingjun','male',48,'20101111','teacher',10000,401,1),
        ('嘻嘻','female',48,'20150311','sale',3000.13,402,2),            #以下是销售部门
        ('呵呵','female',38,'20101101','sale',2000.35,402,2),
        ('嘚嘚','female',18,'20110312','sale',1000.37,402,2),
        ('嘞嘞','female',18,'20160513','sale',3000.29,402,2),
        ('呢呢','female',28,'20170127','sale',4000.33,402,2),
        ('程咬氢','male',28,'20160311','operation',10000.13,403,3),     #以下是运营部门
        ('程咬金','male',18,'19970312','operation',20000,403,3),
        ('程咬银','female',18,'20130311','operation',19000,403,3),
        ('程咬铜','male',18,'20150411','operation',18000,403,3),
        ('程咬铁','female',18,'20140512','operation',17000,403,3)
        ;
 
    查看表
    > select * from employee;
+----+-----------+--------+-----+------------+-----------+--------------+------------+--------+-----------+
| id | name      | sex    | age | hire_date  | post      | post_comment | salary     | office | depart_id |
+----+-----------+--------+-----+------------+-----------+--------------+------------+--------+-----------+
|  1 | bajie     | male   |  18 | 2017-03-01 | visitor   | NULL         |    7300.33 |    401 |         1 |
|  2 | wukong    | male   |  78 | 2015-03-02 | teacher   | NULL         | 1000000.31 |    401 |         1 |
|  3 | datang    | male   |  81 | 2013-03-05 | teacher   | NULL         |    8300.00 |    401 |         1 |
|  4 | shaseng   | male   |  73 | 2014-07-01 | teacher   | NULL         |    3500.00 |    401 |         1 |
|  5 | bailong   | male   |  28 | 2012-11-01 | teacher   | NULL         |    2100.00 |    401 |         1 |
|  6 | xizi      | female |  18 | 2011-02-11 | teacher   | NULL         |    9000.00 |    401 |         1 |
|  7 | tianchi   | male   |  18 | 1900-03-01 | teacher   | NULL         |   30000.00 |    401 |         1 |
|  8 | xingjun   | male   |  48 | 2010-11-11 | teacher   | NULL         |   10000.00 |    401 |         1 |
|  9 | 嘻嘻      | female |  48 | 2015-03-11 | sale      | NULL         |    3000.13 |    402 |         2 |
| 10 | 呵呵      | female |  38 | 2010-11-01 | sale      | NULL         |    2000.35 |    402 |         2 |
| 11 | 嘚嘚      | female |  18 | 2011-03-12 | sale      | NULL         |    1000.37 |    402 |         2 |
| 12 | 嘞嘞      | female |  18 | 2016-05-13 | sale      | NULL         |    3000.29 |    402 |         2 |
| 13 | 呢呢      | female |  28 | 2017-01-27 | sale      | NULL         |    4000.33 |    402 |         2 |
| 14 | 程咬氢    | male   |  28 | 2016-03-11 | operation | NULL         |   10000.13 |    403 |         3 |
| 15 | 程咬金    | male   |  18 | 1997-03-12 | operation | NULL         |   20000.00 |    403 |         3 |
| 16 | 程咬银    | female |  18 | 2013-03-11 | operation | NULL         |   19000.00 |    403 |         3 |
| 17 | 程咬铜    | male   |  18 | 2015-04-11 | operation | NULL         |   18000.00 |    403 |         3 |
| 18 | 程咬铁    | female |  18 | 2014-05-12 | operation | NULL         |   17000.00 |    403 |         3 |
+----+-----------+--------+-----+------------+-----------+--------------+------------+--------+-----------+
 
    4.where约束
    where字句中可以使用
        比较运算符: >    <    >=    <=    =    !=    <>
        between 80 and 100: 值在80到100之间
        in(10,20,30): 值是10或20或30
        like '_bajie%': _代表一个字符, %号代表任意多个
        逻辑运算符: not and or
    代码:
        > select * from employee where age in (18,28,38);
        > select * from employee where name like 'x%';    #隐含条件: x前面没有通配符就是以 x 开头, 若后面也没有就是以什么结尾
   
    5.group by
    注意:
        当有where时, 分组要放在where的后面.
    分组指的是:
        分组是按字段进行分组的
    分组的作用
        取每个部门的员工数    #'每'这个字后面的字段,就是我们分组的依据
        取男女的数量
        取最高,最低工资    
    > select * from employee group by post;        #确实是按字段进行分组了, 但是得到的是每个分组里只有第一条数据展示了, 因为你使用了 * 查询, 其实这样是没有意义的(自己体会下)
    > set global sql_mode='only_full_group_by';    #重启会话后生效: 如果要想分组查询, 这个必须设置, 正常应写到配置文件中(这样你在用 * 就直接报错)
    > select @@global.sql_mode;                    #与select @@sql_mode; 一样
    > select post from employee group by post;     #根据哪个字段分组的, 只能查哪个字段, 其他字段不行: 而且查出来的只有分组的名字,若想查其他字段信息要通过'聚合函数'查询
                                                   #查其他字段的值: 聚合函数:group_concat(sex),用 ,号分隔连接展示
    > select count(id),post from employee group by post;
    > select sum(salary),post from employee group by post;                  #聚合函数: group_concat() count() sum() max() min() avg()
    > select count(1),sex from employee group by sex;                       # 1 是什么, 为了优化性能, 隐含字段, 每条记录这个字段的值都是1
    > select count(1) as '人数',sex as '性别' from employee group by sex;   # 给查询的字段,显示的时候,起一个别名
 
五.可视化工具navicat的使用
    下载地址:
            官网下载:https://www.navicat.com/en/products/navicat-for-mysql
            网盘下载:https://pan.baidu.com/s/1bpo5mqj
 
 
 
内容总结
    1.外键的变种 表和表之间的关系
        一对多或多对一
        多对多: 使用第三张表建立关系
        一对一
    2.数据驱动视图:     Data Controller View:    mysql python服务器 网页展示    (data model)
    3.设计模式:26种
        MVC
        MTV
        MVVM
    4.单表查询
        关键字的优先级
            where 
            group by
            having
            select
            destict
            order by
            limit
        where
            <    >    <=    =>    =    !=    <>
            between .. and ..
            in(1,2,3) 
            not and or
        group by
            sql_mode = only_full_group_by
            只能获取分组的字段, 其他用聚合函数: count(1) sum() avg() max() min() group_concat()函数
 
 
 
 
 
 
上一篇:【VMware虚拟化解决方案】邮箱定时自动接收ESXi警报


下一篇:centos Flash Player插件的安装