MySQL笔记(DQL、约束)

目录

DQL

排序

聚合函数

group by

having

limit

约束

主键约束

非空约束

唯一约束

外键约束

---

DQL

排序

select * from emp order by sal desc;--降序排序
select * from emp order by sal asc;--升序排序，默认asc
select * from emp order by comm desc;--如果有null值，要么全在上面，要么全在下面

聚合函数

也叫分组函数,使用时null值会被自动去除掉后计算

select avg(sal) from emp;--计算平均值
select max(sal) from emp;--计算最大值
select min(sal) from emp;--计算最小值
select sum(sal) from emp;--计算和值
select count(ename) frome emp;--统计次数

group by

select deptno,count(ename) frome emp group by deptno;--根据deptno进行分组后再计算

having

having的效率很低，尽量少用

select deptno,count(ename) from emp group by deptno having count(ename) > 3;--先分组再筛选

limit

用来分页显示，是mysql方言

select * from emp limit 0,2;--从第0条记录开始，每页显示两行
select * from emp limit 90,10;--会将总共100条数据全查出来，再显示10条，效率很低，需要优化

约束

约束是一种表的规范

主键约束

一个表只能存在一个主键约束，可以是单列，也可以是组合列，主键的值是唯一且非空的

• 自然主键：表中本身存在一个列的特点是非空且唯一，比如身份证号、账号等
• 代理主键：和表中其他的字段没有任何关系，唯一的作用就是为了标识记录，通常叫id

--创建表时定义主键
create table users(
    id int primary key,--一般主键的类型都是int或者varchar(32)
    username varchar(20),
    password varchar(20)
);
--表被创建后添加主键约束
alter table emp add primary key(`id`,`username`);--括号里也可以只填一个字段
--删除主键约束(只是删除约束，不会删除字段和记录)
alter table emp drop primary key;
--设置int类型的主键约束自动增长
alter table emp mofify id int auto_increment;--自动增长不可逆
--当主键是varchar(32)时
insert table Stringid values(replace(uuid(),'-',''),other...)

非空约束

限制字段不能为空

--创建表时定义
create table test(
    id int primary key auto_increment,
    name varchar(20) not null,
    password varchar(10) not null default 'XXX'--添加新数据时，如果不给该字段传值则填入默认字段
);
--创建表后修改
alter table emp modify ename char(20) not null;--使用前提是使用前该字段不能有null
alter table emp add password char(20) not null default 'XXX';--设置默认值,只能用在添加字段

唯一约束

唯一的，允许有空值

--创建表时定义
create table test(
    id int primary key auto_increment,
    name varchar(20) unique,--允许空值，但不允许别的记录和它重复
    password varchar(10) 
);
--建表后添加唯一约束
alter table test add unique index(name,password);
--删除唯一约束
alter table test drop index name;--用index关键字取消唯一约束

外键约束

外键：在一个表中的外键，引用另一张表的主键

举例：emp表中有个字段dept_id，引用dept表的主键deptno，emp称为从表，dept称为主表

从表中外键的值必须在主表的主键中可以找到

添加了外键后，从表不能添加主表主键中不存在的值，主表主键不能删改值

--添加外键
alter table emp add constraint `dept_no` foreign key  (deptno) references  dept(deptno);
--删除外键
alter table emp drop constraint `dept_no`;
--级联更新
alter table emp add constraint `dept_no` foreign key  (deptno) references  dept(deptno) on update cascade;--主表更新记录时，从表对应的记录会被一起更新
--级联删除
alter table emp add constraint `dept_no` foreign key  (deptno) references  dept(deptno) on delete cascade;--主表删除记录时，从表对应的记录会被一起删除
--级联删除+级联更新
alter table emp add constraint `dept_no` foreign key  (deptno) references  dept(deptno) on update cascade on delete cascade;
--级联更新+删除置空
alter table emp add constraint `dept_no` foreign key  (deptno) references  dept(deptno) on delete cascade on delete set null;--删除主表记录后，从表的对应外键会被置为null
--同理还有update set null