数据库3

目录

null使用注意事项

  • null 与 "" 不等价

  • select * from t where name = ""; # 当name的值为null时查询不到

    select * from t where name is null; # 查询表中name值为null的数据

  • _ 表示一个, * 表示所有

单表操作

分组统计数据

  • 分组指的是将所有记录按照某个字段进行归类, 含有相同的该字段值的数据归为一类, 比如员工信息表的职位分组

  • 使用语法: select 字段名a, 聚合函数 from 表名 group by 字段名a;

    # 1. 以性别进行分组, 统计男生和女生的人数各是多少
select sex, count(sex) as amount from example group by sex;
+--------+--------+
| sex    | amount |
+--------+--------+
| male   |      3 |
| female |      3 |
+--------+--------+

# 2. 对部门进行分组, 求出每个部门年龄最大的人
select dep_id, max(age) from example group by dep_id;
+--------+----------+
| dep_id | max(age) |
+--------+----------+
|    200 |       18 |
|    201 |       48 |
|    202 |       28 |
|    204 |       18 |
+--------+----------+

# 3. min: 求最小的
# 4. sum: 求和
# 5. count: 计数
# 6. avg

  • having 表示对分组后的统计表进行二次筛选

    mysql> select dep_id, sum(age) as sum from example group by dep_id having sum > 20;
+--------+------+
| dep_id | sum  |
+--------+------+
|    200 |   36 |
|    201 |   86 |
|    202 |   28 |
+--------+------+

  • oder by 字段名 asc/desc # 将数据以某个字段为基准进行升序或降序排列

    如果选定了多个字段进行排序: age desc, id desc # 表示先以age为基准进行降序排列, 如果遇到age相同的数据, 则将age相同的数据以id为基准进行降序排列

    select * from example order by age desc, dep_id desc;
+----+------------+--------+------+--------+
| id | name       | sex    | age  | dep_id |
+----+------------+--------+------+--------+
|  2 | alex       | female |   48 |    201 |
|  3 | wupeiqi    | male   |   38 |    201 |
|  4 | yuanhao    | female |   28 |    202 |
|  6 | jingliyang | female |   18 |    204 |
|  1 | egon       | male   |   18 |    200 |
|  5 | liwenzhou  | male   |   18 |    200 |
+----+------------+--------+------+--------+

  • limit offset, size # offset为行数据索引, size为取多少行数据

    select * from example limit 2, 2;  # 从第三行开始取, 取两行数据
+----+---------+--------+------+--------+
| id | name    | sex    | age  | dep_id |
+----+---------+--------+------+--------+
|  3 | wupeiqi | male   |   38 |    201 |
|  4 | yuanhao | female |   28 |    202 |
+----+---------+--------+------+--------+

  • 语句的使用顺序: where > group by > having > order by > limit

    select 字段名a from 表名 where 条件 group by 字段名a having 条件 order by 字段名b desc limit offset, size;

多表操作

外键

  • 使用原因:
    • 减少内存占用
    • 方便修改, 修改一个表中的某个数据, 与之关联的其他表中的数据就会相应的修改

创建多表关系

一对多

  • 外键约束

    # 添加外键方式一: 在子表创建之后添加
alter table foreign_key_child1  # 声明子表名
add CONSTRAINT fk_department foreign key (department)  # 声明子表中的约束名及外键
references foreign_key_parent (id);  # 声明引用的父表名及引用的父表主键

# 添加外键的方式二: 在子表创建时添加
create table foreign_key_child2 (  # 声明子表名
    id int auto_increment primary key,
    name varchar(32) not null default "",
    department int not null default 4,
    CONSTRAINT fk_department2 foreign key (department)  # 声明子表中的约束名及外键
    references foreign_key_parent (id)  # 声明引用的父表名及引用的父表主键
    ) charset utf8;

# 注意1: 子表中的外键只能引用父表的主键(已通过cmd的sql语句验证), 外键只能为int类型
# 注意2: 在子表中插入值时, 子表外键的值只能在父表主键的值中选取, 否则会报错
# 注意3: 约束关键字CONSTRAINT需大写

多对多

  • 在关系表中添加外键约束

    # 创建boys父表
create table boys (
    bid int primary key auto_increment,
    name char not null default "",  # 默认值类型需与字段类型一致
    dt datetime 
	) charset utf8;

# 创建girls父表
create table girls(
    gid int auto_increment primary key,
    name char not null default ""
    ) charset utf8;

# 创建boy_to_girl约会关系子表
create table boy_to_girl (
    id int auto_increment primary key,
    bid int not null default 0,
    gid int not null default 0,
    CONSTRAINT fk_bid foreign key (bid)  # 声明子表中的约束1名及外键1名
    references boys (bid),  # 声明引用的父表1名及引用的父表1主键
    CONSTRAINT fk_gid foreign key (gid)  # 声明子表中的约束2名及外键2名
    references girls (gid)  # 声明引用的父表2名及引用的父表2主键
    ) charset utf8;

一对一

  • 唯一约束 + 外键约束

    # 定义父表
create table qq_user (
    id int auto_increment primary key,
    real_name varchar(32) not null default ''
    ) charset utf8;
    
# 定义子表
create table qq_log_name (
    id int auto_increment primary key,
    log_name varchar(32) not null default '',
    uid int not null default 0,
    CONSTRAINT uqe_uid unique (uid),  # 声明子表中的约束名及唯一字段
    CONSTRAINT fk_uid foreign key (uid)  # 声明子表中的约束名及外键
    references qq_user (id)  # 声明引用的父表名及引用的父表主键
    ) charset utf8;

# 注意: 在子表中插入数据时, 子表外键的值重复或不属于父表主键的值都会报错

删除约束

  • 删除主键约束, 需先删除自增约束

    alter table test_primary_key
modify id int;

alter table test_primary_key
drop primary key;

  • 删除外键约束, 需先查看约束名

    show create table foreign_key_child1G  # G替换封号, 可以格式化输出

alter table foreign_key_child1
drop foreign key fk_department;

  • 删除唯一约束, 需先删除外键约束

    alter table qq_log_name
drop foreign key  fk_uid;

alter table qq_log_name
drop index uqe_uid;  # 删除唯一约束使用index

多表联查

  • 两表联查

    select * from foreign_key_parent left join foreign_key_child2  # 联结父表与子表
on foreign_key_parent.id = foreign_key_child2.department;  # 联结主键与外键
+----+------------+------+------+------------+
| id | department | id   | name | department |
+----+------------+------+------+------------+
|  1 | 开发部     |    1 |      |          1 |
|  1 | 开发部     |    2 |      |          1 |
|  3 | 运维部     |    3 |      |          3 |
|  4 | 销售部     |    4 |      |          4 |
|  2 | 测试部     | NULL | NULL |       NULL |
+----+------------+------+------+------------+

select * from foreign_key_child2 left join foreign_key_parent  # 联结子表与父表
on foreign_key_parent.id = foreign_key_child2.department;  # 联结外键与主键
+----+------+------------+------+------------+
| id | name | department | id   | department |
+----+------+------------+------+------------+
|  1 |      |          1 |    1 | 开发部     |
|  2 |      |          1 |    1 | 开发部     |
|  3 |      |          3 |    3 | 运维部     |
|  4 |      |          4 |    4 | 销售部     |
+----+------+------------+------+------------+

  • 三表联查

    select * from boys left join boy_to_girl  # 联结父表1与关系子表
on boys.bid = boy_to_girl.bid  # 联结父表1主键与关系子表外键1
left join girls  # 联结关系子表与父表2
on boy_to_girl.gid = girls.gid;  # 联结关系表外键2与父表2主键
+-----+---------+---------------------+------+------+------+------+---------+
| bid | name    | dt                  | id   | bid  | gid  | gid  | name    |
+-----+---------+---------------------+------+------+------+------+---------+
|   1 | 蔡启龙1 | 2019-11-02 14:06:09 |    1 |    1 |    1 |    1 | 姜婉婷1 |
|   1 | 蔡启龙1 | 2019-11-02 14:06:09 |    2 |    1 |    2 |    2 | 姜婉婷2 |
|   1 | 蔡启龙1 | 2019-11-02 14:06:09 |    3 |    1 |    4 |    4 | 姜婉婷4 |
|   1 | 蔡启龙1 | 2019-11-02 14:06:09 |    4 |    1 |    3 |    3 | 姜婉婷3 |
|   2 | 蔡启龙2 | 2019-11-02 14:06:25 |    5 |    2 |    3 |    3 | 姜婉婷3 |
|   2 | 蔡启龙2 | 2019-11-02 14:06:25 |    6 |    2 |    2 |    2 | 姜婉婷2 |
|   2 | 蔡启龙2 | 2019-11-02 14:06:25 |    7 |    2 |    1 |    1 | 姜婉婷1 |
|   2 | 蔡启龙2 | 2019-11-02 14:06:25 |    8 |    2 |    4 |    4 | 姜婉婷4 |
|   3 | 蔡启龙3 | 2019-11-02 14:06:33 | NULL | NULL | NULL | NULL | NULL    |
+-----+---------+---------------------+------+------+------+------+---------+

select * from girls left join boy_to_girl
on girls.gid = boy_to_girl.gid
left join boys
on boy_to_girl.bid = boys.bid;
+-----+---------+------+------+------+------+---------+---------------------+
| gid | name    | id   | bid  | gid  | bid  | name    | dt                  |
+-----+---------+------+------+------+------+---------+---------------------+
|   1 | 姜婉婷1 |    1 |    1 |    1 |    1 | 蔡启龙1 | 2019-11-02 14:06:09 |
|   2 | 姜婉婷2 |    2 |    1 |    2 |    1 | 蔡启龙1 | 2019-11-02 14:06:09 |
|   4 | 姜婉婷4 |    3 |    1 |    4 |    1 | 蔡启龙1 | 2019-11-02 14:06:09 |
|   3 | 姜婉婷3 |    4 |    1 |    3 |    1 | 蔡启龙1 | 2019-11-02 14:06:09 |
|   3 | 姜婉婷3 |    5 |    2 |    3 |    2 | 蔡启龙2 | 2019-11-02 14:06:25 |
|   2 | 姜婉婷2 |    6 |    2 |    2 |    2 | 蔡启龙2 | 2019-11-02 14:06:25 |
|   1 | 姜婉婷1 |    7 |    2 |    1 |    2 | 蔡启龙2 | 2019-11-02 14:06:25 |
|   4 | 姜婉婷4 |    8 |    2 |    4 |    2 | 蔡启龙2 | 2019-11-02 14:06:25 |
+-----+---------+------+------+------+------+---------+---------------------+

  • 注意:

    1. 查询结果包括 left join 语句左边的表中的所有记录,
    2. 如果左表的关联键的值在右表的对应键中没有值, 则在右表对应位置上显示为null
【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/-406454833/p/11789926.html