【数据库学习】MySQL约束与设计

1 DQL查询语句

1.1排序

​ 查询员工信息, 按照薪资排序 (升序, 降序)，null值也会参与排序, 无穷小

select sal s from emp e where e.sal > 1200 order by s asc; -- 默认升序
select * from emp order by sal desc; -- 降序

1.2 查询的执行先后顺序

-- from
-- where: 不能使用select里面的别名, 在select之前执行, 
--        可以使用from里面的别名, 在from后面执行
-- select
-- order: 能使用select里面的别名, 在select后面执行

1.3 分组函数

单行函数: 时间相关, 字符串相关的, 数字相关等
聚合函数: 也叫分组函数，会自动将 null 值去除后计算

-- 全公司中的所有员工平均薪资, 最高薪资, 最低薪资, 薪资总数是多少, 总共有多少人
-- avg max min sum count
select avg(comm), max(comm), min(comm), sum(comm), count(comm) from emp;

-- 部门20有多少个员工
select count(ename) from emp where deptno = 20;

-- 每个部门有多少个员工(对部门号进行分组)
-- select 后面如果有分组函数, 还有普通的字段/单行函数
-- 注意: 普通字段, 必须出现在 group by 中
select deptno, count(ename) from emp group by deptno;
select ename, deptno, count(ename) from emp group by deptno, ename;

having

-- 查询出人数在3人以上的部门: 需要使用分组函数作为条件, 必须使用having
-- having 效率很低
select deptno no, count(ename) `count` from emp
 group by no having `count` > 3 order by no desc;

1.4 分页查询

limit

-- 分页查询: pageNum pageSize
-- 0: 开始条数的索引 start = pageSize * (pageNum-1)
-- 2: 每页显示多少行 pageSize
select * from emp limit 4, 2;
-- limit原理: 将 前100条全查出来, 再取最后10条
select * from emp limit 90, 10;
-- 单行函数, limit : mysql方言

2 数据库表的约束

2.1 主键约束

主键约束: 一个表中只能存在一个主键约束，可以是一列, 也可以是组合列

特点: 唯一 并且 非空, 通常不会修改主键内容

分类:
自然主键: 表中本身存在一个列的特点是非空并且唯一, 这一列就可以 作为主键
例如: 身份证号 登录用户名
代理主键: 和表中其他的字段没有任何联系, 唯一的作用就是为了标识 唯一性
例如: id
类型: int 或者 varchar(32)
primary key

1. 添加主键约束

   -- 建表是 添加主键约束
   create table users (
   	id int primary key auto_increment,
       username varchar(20) not null,
       password varchar(20),
       gender char(1) not null default '男'
   );
   
   -- 表已经存在的情况下, 删除主键约束
   ALTER TABLE `java2206`.`users` 
   DROP PRIMARY KEY;
   
   -- 表已经存在时, 添加主键约束
   ALTER TABLE `java2206`.`users` 
   ADD PRIMARY KEY (`id`);
   

2. 设置 自动增长

   alter table users modify id int auto_increment;
   -- id 是null, 自动使用自增长填写id
   insert into users values(null, 'tom', '1234');
   -- 表中的id最大值, 自己的序列的下一个值
   

3. String 类型的主键，UUID()

   -- String 类型的主键, 保证唯一性 UUID()
   desc student;
   alter table student modify id varchar(32) primary key;
   select uuid(); 
   -- 36位 四个 -
   select replace(uuid(), '-', '');
   -- varchar类型的id, 插入时必须要指定值
   insert into student values(replace(uuid(), '-', ''), null, '张三', null);
   

2.2 非空约束

非空约束: 不能为空
添加非空约束 username varchar(20) not null
设置非空字段默认值 default value

alter table users modify username varchar(20) not null;
insert into users (username, password) values('lucy', '123');
-- 非空字段, 可以设置默认值
alter table users add gender char(1) not null default '男';

2.3 唯一约束

唯一约束: 唯一的, 允许有空值

-- 建表时添加唯一约束
create table user (
	id int primary key auto_increment,
    name varchar(20) unique
);

-- 有表的时候, 删除唯一约束  (index->索引)
alter table user drop index name;
-- 有表的时候, 添加唯一约束  (index->索引)
ALTER TABLE user 
ADD UNIQUE INDEX `name_age_inde` (name, age) ;

2.4 外键约束

外键约束: foreign key
一个表中的外键, 引用了另一张表的主键
例如: emp表中有个字段dept_id, 引用dept表的主键 deptno
emp称为从表, dept称为主表
创建和删除有顺序上的讲究

注：不能添加一个主表中不存在的数据

​ 主表不能删除被从表引用的数据

例：给emp表中的deptno字段添加外键, 引用的是dept表中的deptno主键

-- 注意: dept表中的deptno 必须是主键
-- 总结: 在多的一方上添加外键, 引用1的一方的主键
alter table emp 
add constraint `dept_fk` foreign key 
(deptno) references dept(deptno) ;

-- 删除外键
alter table emp drop constraint `dept_fk`;

级联更新: 修改主表时, 从表数据一起修改

alter table emp 
add constraint `dept_fk` foreign key 
(deptno) references dept(deptno) on update cascade;

级联删除: 删除主表数据时, 从表数据一起删除

alter table emp 
add constraint `dept_fk` foreign key 
(deptno) references dept(deptno) on delete cascade;

级联更新 + 删除置空(删除主表数据, 从表数据就变为null)

alter table emp 
add constraint `dept_fk` foreign key 
(deptno) references dept(deptno) 
on update cascade
on delete set null;

3 表与表的关系

dept - emp order - user
一个员工属于一个部门, 一个部门可以有多个员工
1-1 1-n -> 1-n
员工属于多的一方, 部门属于1的一方

主播 - 粉丝
一个主播可以有多个粉丝, 一个粉丝可以关注多个主播
1-n 1-n -> n-n

user role
一个用户可以有多个角色
一个角色可以有多个用户

表示n-n关系, 需要第三张表, 存n-n关系

create table user_role (
    id int primary key auto_increment,
    user_id int,
    role_id int,
    constraint `user_fk` foreign key (user_id) references user(id),
    constraint `role_fk` foreign key (role_id) references role(id));
-- 为了让关系数据合理性, 需要给 (user_id, role_id) 添加唯一约束
insert into user_role(user_id, role_id) values(4, 1),(6, 1),(7, 1),(4, 3);
select * from user_role;