单表操作与多表操作

单表操作

分组

分组含义

分组指的是:将所有记录按照某个相同字段进行归类

分组语法

select 聚合函数, 选取的字段 from 表名 group by 分组的字段;

group by : 是分组的关键词
group by 必须和 聚合函数(count) 出现

例子

# 1. 以性别为例, 进行分组, 统计一下男生和女生的人数是多少个:

mysql> select count(id), gender from  employee group by gender;
    +-----------+--------+
    | count(id) | gender |
    +-----------+--------+
    |        10 | male   |
    |         8 | female |
    +-----------+--------+

                    
mysql> select gender, count(id) as total from  employee group by gender;
    +--------+-------+
    | gender | total |
    +--------+-------+
    | male   |    10 |
    | female |     8 |
    +--------+-------+


# 2. 对部门进行分组, 求出每个部门年龄最大的那个人?
mysql> select depart_id,max(age) from employee group by depart_id;
    +-----------+----------+
    | depart_id | max(age) |
    +-----------+----------+
    |         1 |       81 |
    |         2 |       48 |
    |         3 |       28 |
    +-----------+----------+

聚合函数

'''
max():最大值
min():最小值
avg():平均值
sum():和
count():记数'''

having

having语法

表示对group by 之后的数据, 进行再一次的二次筛选

直接写在分组之后

having例子

mysql> select depart_id,avg(age) from employee group by depart_id ;
                +-----------+----------+
                | depart_id | avg(age) |
                +-----------+----------+
                |         1 |  45.2500 |
                |         2 |  30.0000 |
                |         3 |  20.0000 |
                +-----------+----------+
                3 rows in set (0.00 sec)

mysql> select depart_id,avg(age) from employee group by depart_id having avg(age) > 35;
                +-----------+----------+
                | depart_id | avg(age) |
                +-----------+----------+
                |         1 |  45.2500 |
                +-----------+----------+
                1 row in set (0.00 sec)

mysql> select depart_id,avg(age) as pj from employee group by depart_id having pj > 35;
                +-----------+---------+
                | depart_id | pj      |
                +-----------+---------+
                |         1 | 45.2500 |
                +-----------+---------+
                1 row in set (0.00 sec)

排序

排序语法

`select * from 表名 order by 字段名 desc;

不写默认为asc升序 , desc为降序

如果对多个字段进行排序,如:age desc, id asc;
表示: 先对age进行降序, 如果age有相同的行, 则对id进行升序

例子

select * from employee order by age desc, id desc;

分页

分页语法

select * from 表名 limit 索引值,记录数;

limit offset, size

offset: 行数据索引 , 索引从0开始 size: 取多少条数据

例子

mysql> select * from employee limit 0,10;
mysql> select * from employee limit 10,10;

单表操作小结

使用的顺序:
select * from 表名 where 条件 group by 条件 having 条件 order by 条件 limit 条件;

优先级 : where > group by > having > order by > limit

多表操作

外键

使用的原因:
a. 减少占用的空间
b. 只需要修改department表中一次, 其余的表中的数据就会相应的修改

一对多

  • 语法

    constraint 外键名 foreign key (被约束的字段) references 约束的表(约束的字段)

  • 例子

    # 建department表
    create table department(
        id int auto_increment primary key,
        name varchar(32) not null default ''
    )charset utf8;
    
    # 插入记录
    insert into department (name) values ('研发部');
    insert into department (name) values ('运维部');
    insert into department (name) values ('前台部');
    insert into department (name) values ('小卖部');
    
    # 建userinfo表
    create table userinfo (
        id int auto_increment primary key,
        name varchar(32) not null default '',
        depart_id int not null default 1,
        # 将userinfo表的depart_id和department的id建立关系
        constraint fk_user_depart foreign key (depart_id) references department(id)
    )charset utf8;
    
    # 插入信息
    insert into userinfo (name, depart_id) values ('zekai', 1);
    insert into userinfo (name, depart_id) values ('xxx', 2);
    insert into userinfo (name, depart_id) values ('zekai1', 3);
    insert into userinfo (name, depart_id) values ('zekai2', 4);
    insert into userinfo (name, depart_id) values ('zekai3', 1);
    insert into userinfo (name, depart_id) values ('zekai4', 2);
    insert into userinfo (name, depart_id) values ('zekai4', 5);
    

多对多

  • 语法

    constraint 外键名 foreign key (被约束的字段) references 约束的表(约束的字段)

    constraint 外键名 foreign key (被约束的字段) references 约束的表(约束的字段)

  • 例子

    # 建boy表
    create table boy (
        id int auto_increment primary key,
        bname varchar(32) not null default ''
    )charset utf8;
    
    # 插入记录                
    insert into boy (bname) values ('zhangsan'),('lisi'),('zhaoliu');
    
    
    # 建girl表
    create table girl (
        id int auto_increment primary key,
        gname varchar(32) not null default ''
    )charset utf8;
    
    # 插入信息
    insert into girl (gname) values ('cuihua'),('gangdan'),('jianguo');
    
    # 建boy2girl表
    create table boy2girl (
        id int auto_increment primary key,
        bid int not null default 1,
        gid int not null default 1,
      # 建立多对多关系
        constraint fk_boy2girl_boy foreign key (bid) references boy(id),
        constraint fk_boy2girl_girl foreign key (gid) references girl(id)
    )charset utf8;
    
    # 插入信息
    insert into boy2girl (bid, gid) values (1,1),(1,2),(2,3),(3,3),(2,2);
    

一对一

  • 语法

    constraint 外键名 foreign key (被约束的字段) references 约束的表(约束的字段)

  • 例子

    # 建user表
    create table user (
        id int auto_increment primary key,
        name varchar(32) not null default ''
    )charset=utf8;
    
    # 插入信息
    insert into user (name) values ('zhangsan'),('zekai'),('kkk');
    
    # 建priv表
    create table priv(
        id int auto_increment primary key,
        salary int not null default 0,
        uid int not null default 1,
      #  创建关系
        constraint fk_priv_user foreign key (uid) references user(id),
        # 唯一性  不能重复
        unique(uid)
    )charset=utf8;
    
    # 插入信息
    insert into priv (salary, uid) values (2000, 1);
    insert into priv (salary, uid) values (2800, 2);
    insert into priv (salary, uid) values (3000, 3);
    
    '''
    一旦重复就会报错
    insert into priv (salary, uid) values (6000, 1);
    ERROR 1062 (23000): Duplicate entry '1' for key 'uid'
    '''
    

多表联查

两表联查

  • left join....on

    • 语法

      select * from 左表 left join 右表 on 两表有关联的字段的条件

      left join....on 保留左边表的所有字段记录

    • 例子

      mysql> select * from userinfo left join department on depart_id =department.id
      
      
      mysql> select userinfo.name as uname, department.name as dname  from userinfo left join department on depart_id = department.id;
                      +--------+--------+
                      | uname  | dname  |
                      +--------+--------+
                      | zekai  | 研发部 |
                      | zekai3 | 研发部 |
                      | xxx    | 运维部 |
                      | zekai4 | 运维部 |
                      | zekai1 | 前台部 |
                      | zekai2 | 小卖部 |
                      +--------+--------+
                      6 rows in set (0.00 sec)
      
  • right join...on

    • 语法

      select * from 左表 right join 右表 on 两表有关联的字段的条件

      right join...on 保留右边表的所有字段记录

    • 例子

      mysql> select userinfo.name as uname, department.name as dname  from userinfo right join department on depart_id = department.id;
                      +--------+--------+
                      | uname  | dname  |
                      +--------+--------+
                      | zekai  | 研发部 |
                      | zekai3 | 研发部 |
                      | xxx    | 运维部 |
                      | zekai4 | 运维部 |
                      | zekai1 | 前台部 |
                      | zekai2 | 小卖部 |
                      +--------+--------+
                      7 rows in set (0.00 sec)
      
  • inner join

三表联查

三表联查就是在两表的基础上再加一句左连接或者右连接

例子:

mysql> select * from boy left join boy2girl on boy.id = boy2girl.bid left join girl on girl.id = boy2girl.gid;
posted @ 2019-10-31 19:31  ^啷个哩个啷$  阅读(200)  评论(0编辑  收藏  举报