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 | +-----+---------+------+------+------+------+---------+---------------------+ -
注意:
- 查询结果包括
left join语句左边的表中的所有记录, - 如果左表的关联键的值在右表的对应键中没有值, 则在右表对应位置上显示为null
- 查询结果包括