-- group by 分组查询
-- 按照性别分组
mysql > select sex, count(1) -- 查询的内容
from person -- 从哪里查
group by sex; -- 如何分组
-- 按照性别分组,找出人数大于5的结果
mysql > select sex, count(1) -- 查询的内容
from person -- 从哪里查
group by sex -- 如何分组
having count(1) > 5; -- 分组之后条件约束
-- 需按照性别分组,但是需要找出年龄大于35岁的数据
mysql > select sex, count(1) -- 查询的内容
from person -- 从哪里查
where age > 35 -- 分组之前条件约束
group by sex; -- 如何分组
-- 需按照性别分组,但是需要找出年龄大于35岁的数据,并且人数大于5人
mysql > select sex, count(1) -- 查询的内容
from person -- 从哪里查
where age > 35 -- 分组之前约束查询添加
group by sex -- 按照性别分组
having count(1) > 5; -- 分组之后条件约束
-- 排序查询
-- order by
-- asc 升序 desc 降序
-- 年龄升序
mysql > select *
from person
order by age asc;
-- 工资降序
mysql > select *
from person
order by salary desc ;
-- 第一条件是年龄升级,如果年龄一致,工资也升序
mysql > select *
from person
order by age asc, salary asc;
2. 数据约束【重点】
2.1 默认值
-- default
-- 当前数据表中对应字段在没有赋值的情况下,存在一个默认值
mysql > create table person1
(
id int,
name varchar(30),
country varchar(30) default '河南'
);
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(30) | YES | | NULL | |
| country | varchar(30) | YES | | 河南 | |
+---------+-------------+------+-----+---------+-------+
-- 插入数据,但是默认数据不做任何操作
mysql > insert into person1(id, name) value(1, '杰克');
-- 插入数据,带有默认值的数据给予当前所需数据
mysql > insert into person1(id, name, country) value(2, '杰瑞', '地球');
-- 插入数据,带有默认值的数据给予null
mysql > insert into person1(id, name, country) value(3, 汤姆', null);
2.2 非空
-- not null
-- 要求当前字段不能为空
mysql > create table person2
(
id int not null,
name varchar(30) not null,
info text
);
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | | NULL | |
| name | varchar(30) | NO | | NULL | |
| info | text | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
-- 插入数据
mysql > insert into person2 value(1, '熊大', '熊二他哥');
-- 没有给予ID赋值,导致报错!!!
mysql > insert into person2(name, info) value('李白', '青莲剑歌');
-- ERROR 1364 (HY000): Field 'id' doesn't have a default value
-- 没有给予name赋值,导致报错
mysql > insert into person2(id, info) value(1, '青莲剑歌');
-- ERROR 1364 (HY000): Field 'name' doesn't have a default value
-- ID,name使用not null修饰之后,不能给予null
mysql > insert into person2(id, name, info) value(null, null, '随便');
-- ERROR 1048 (23000): Column 'id' cannot be null
2.3 唯一
-- unique
-- 唯一
mysql > create table person3
(
id int unique,
name varchar(30) not null,
info text
);
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | UNI | NULL | |
| name | varchar(30) | NO | | NULL | |
| info | text | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
-- 插入数据
mysql > insert into person3(id, name, info) value(1, '杰克', '狂炫酷帅叼');
-- 插入数据 重复ID无法添加到当前数据表中
mysql > insert into person3(id, name, info) value(1, '杰瑞', '抓不住的耗子');
-- ERROR 1062 (23000): Duplicate entry '1' for key 'id'
-- 唯一索引可以是null, 而且在数据参考中,null不作为唯一索引的约束条件
mysql > insert into person3(id, name, info) value(null, '灰太狼', '好惨一只狼');
mysql > insert into person3(id, name, info) value(null, '迪迦', '童年的回忆');
2.4 主键
-- 非空 + 唯一
-- primary key
mysql > create table person4
(
id int primary key,
name varchar(30),
info text
);
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(30) | YES | | NULL | |
| info | text | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
-- 插入数据
mysql > insert into person4 value(1, '盲僧', '闪现锤空气');
-- 插入数据,但是ID重复,无法添加
mysql > insert into person4 value(1, '哪吒', '闹海');
-- ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
-- 插入数据,但是ID为null,无法添加
mysql > insert into person4 value(null, '美猴王', '不许叫我弼马温');
-- ERROR 1048 (23000): Column 'id' cannot be null
2.5 自增长
-- auto_increment
-- 实现数据自增长
-- 要求自增长数据是需要主键约束,或者唯一约束
mysql > create table person5
(
id int primary key auto_increment,
name varchar(30) not null,
info text
);
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(30) | NO | | NULL | |
| info | text | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
-- 插入数据 非指定ID数据,ID会随着自增长计数,给予对应ID值
mysql > insert into person5(name, info) value('郭德纲', '流氓头子');
mysql > insert into person5(name, info) value('于谦', '相声皇后');
-- 指定ID值之后,自增长会随之改变
mysql > insert into person5(id, name, info) value(3, '高峰', '德云社总教习');
mysql > insert into person5(id, name, info) value(4, '栾云平', '怼怼');
mysql > insert into person5(name, info) value('岳云鹏', '贱贱');
mysql > insert into person5(id, name, info) value(10, '孙越', '把大象养瘦的人');
mysql > insert into person5(name, info) value('郭麒麟', '少班主');
mysql > insert into person5(id, name, info) value(9, '阎鹤祥', '壮壮');
mysql > insert into person5(name, info) value('朱云峰', 'SB');
-- delete操作不会影响自增长值
mysql > delete from person5;
mysql > insert into person5(name, info) value('小李子', '岁月是把杀猪刀');
-- truncate 清空整个数据表数据,并且自增长【重置】
mysql > truncate person5;
mysql > insert into person5(name, info) value('强森', '可以打我十个');
2.6 外键
-- 设计数据表
mysql > create table employee
(
id int primary key auto_increment,
name varchar(30) not null,
deptName varchar(30) not null
);
-- 插入数据
insert into employee(name, deptName) value('杰克', 'JavaEE开发部');
insert into employee(name, deptName) value('杰瑞', 'JavaEE开发部');
insert into employee(name, deptName) value('汤姆', '帅部');
insert into employee(name, deptName) value('爱丽丝', '恐部');
insert into employee(name, deptName) value('喜羊羊', '帅部');
insert into employee(name, deptName) value('小鲤鱼泡泡', 'iOS开发部');
insert into employee(name, deptName) value('沸羊羊', '黑部');
insert into employee(name, deptName) value('磁力超人', '也黑部');
insert into employee(name, deptName) value('美羊羊', '美部');
insert into employee(name, deptName) value('费玉清', '污部');
-- 删除原数据表
drop table employee;
-- 设计两张数据表
-- 员工表 【从表】
mysql > create table employee
(
id int primary key auto_increment,
name varchar(30) not null,
deptId int not null
);
-- 部门表 【主表】
mysql > create table dept
(
id int primary key auto_increment,
deptName varchar(30) not null
);
-- 插入部门数据
insert into dept(deptName) value('JavaEE研发部');
insert into dept(deptName) value('帅部');
insert into dept(deptName) value('恐部');
insert into dept(deptName) value('iOS研发部');
insert into dept(deptName) value('黑部');
-- 插入员工数据
insert into employee(name, deptId) value('杰克', 1);
insert into employee(name, deptId) value('杰瑞', 1);
insert into employee(name, deptId) value('汤姆', 2);
insert into employee(name, deptId) value('爱丽丝', 3);
insert into employee(name, deptId) value('喜羊羊', 2);
insert into employee(name, deptId) value('小鲤鱼泡泡', 4);
insert into employee(name, deptId) value('沸羊羊', 5);
insert into employee(name, deptId) value('磁力超人, 5);
-- 目前存在问题,当前employee员工数据添加的过程中,deptId不存在,依然可以添加成功!!!【不合适】
insert into employee(name, deptId) value('小马宝莉', 10);
-- 删除原数据表
drop table employee;
drop table dept;
-- 设计带有【外键约束】两张数据表
-- 部门表 【主表】
mysql > create table dept
(
id int primary key auto_increment,
deptName varchar(30) not null
);
-- 插入部门数据
insert into dept(deptName) value('JavaEE研发部');
insert into dept(deptName) value('帅部');
insert into dept(deptName) value('恐部');
insert into dept(deptName) value('iOS研发部');
insert into dept(deptName) value('黑部');
-- 员工表 【从表】
mysql > create table employee
(
id int primary key auto_increment,
name varchar(30) not null,
deptId int not null,
-- 声明外键约束
-- fk_emp_dept 外键名
-- foreign key (deptId) 表示从表中哪一个字段作为外键约束字段选择字段为deptId
-- references dept(id) 表示主表中哪一个字段作为外键约束目标字段,选择主表dept中id字段为目标字段
constraint fk_emp_dept foreign key (deptId) references dept(id)
);
-- 插入员工数据
insert into employee(name, deptId) value('杰克', 1);
insert into employee(name, deptId) value('杰瑞', 1);
insert into employee(name, deptId) value('汤姆', 2);
insert into employee(name, deptId) value('爱丽丝', 3);
insert into employee(name, deptId) value('喜羊羊', 2);
insert into employee(name, deptId) value('小鲤鱼泡泡', 4);
insert into employee(name, deptId) value('沸羊羊', 5);
insert into employee(name, deptId) value('磁力超人, 5);
-- 从表添加数据,主表中不存在对应的deptId, 添加失败
insert into employee(name, deptId) value('小马宝莉', 10);
-- ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`javaee1911`.`employee`, CONSTRAINT `fk_emp_dept` FOREIGN KEY (`deptId`) REFERENCES `dept` (`id`))
-- 主表无法删除被从表已经外键约束的数据行
delete from dept where id = 5;
-- ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`javaee1911`.`employee`, CONSTRAINT `fk_emp_dept` FOREIGN KEY (`deptId`) REFERENCES `dept` (`id`))
-- 主表不能修改被从表已经使用的数据表关键数据,外键约束目标字段数据
update dept set id = 10 where id = 5;
-- ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`javaee1911`.`employee`, CONSTRAINT `fk_emp_dept` FOREIGN KEY (`deptId`) REFERENCES `dept` (`id`))
-- 可以修改和没有外键约束的非目标字段数据
update dept set deptName = '包青天部' where id = 5;
2.7 级联操作
-- 在外键使用的情况下,更好的操作主表数据,而且从表会随着修改对应的数据
-- 级联删除 主表数据删除,从表对应使用该外键约束目标字段数据行随着删除
-- on delete cascade
-- 级联修改 主表修改外键约束目标字段数据,从表中所有使用该数据的数据行全部修改
-- on delete cascade
-- 删除原数据
-- 删除原数据表
drop table employee;
drop table dept;
-- 设计带有【外键约束】两张数据表
-- 部门表 【主表】
mysql > create table dept
(
id int primary key auto_increment,
deptName varchar(30) not null
);
-- 插入部门数据
insert into dept(deptName) value('JavaEE研发部');
insert into dept(deptName) value('帅部');
insert into dept(deptName) value('恐部');
insert into dept(deptName) value('iOS研发部');
insert into dept(deptName) value('黑部');
-- 员工表 【从表】
mysql > create table employee
(
id int primary key auto_increment,
name varchar(30) not null,
deptId int not null,
-- 声明外键约束
-- fk_emp_dept 外键名
-- foreign key (deptId) 表示从表中哪一个字段作为外键约束字段选择字段为deptId
-- references dept(id) 表示主表中哪一个字段作为外键约束目标字段,选择主表dept中id字段为目标字段
constraint fk_emp_dept foreign key (deptId) references dept(id)
on delete cascade -- 级联删除
on update cascade -- 级联修改
);
-- 插入员工数据
insert into employee(name, deptId) value('杰克', 1);
insert into employee(name, deptId) value('杰瑞', 1);
insert into employee(name, deptId) value('汤姆', 2);
insert into employee(name, deptId) value('爱丽丝', 3);
insert into employee(name, deptId) value('喜羊羊', 2);
insert into employee(name, deptId) value('小鲤鱼泡泡', 4);
insert into employee(name, deptId) value('沸羊羊', 5);
insert into employee(name, deptId) value('磁力超人, 5);
-- 修改主表操作
update dept set id = 10 where id = 5;
-- 删除主表操作
delete from dept where id = 3;