单表操作与多表操作

目录

• 单表操作
  • 分组
    • 分组含义
    • 分组语法
    • 例子
  • 聚合函数
  • having
    • having语法
    • having例子
  • 排序
    • 排序语法
    • 例子
  • 分页
    • 分页语法
    • 例子
  • 单表操作小结
• 多表操作
  • 外键
    • 一对多
    • 多对多
    • 一对一
  • 多表联查
    • 两表联查
    • 三表联查

单表操作

分组

分组含义

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

分组语法

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;