MySQL关键字、约束和多表查询

MySQL

5.MySQL关键字

5.1排序

排序是对已经查出来的结果进行排序的

关键词：order by / desc（降序） asc（升序默认的）

# 需求：按编号进行降序排列
select * from emp order by eno desc;
# 需求：根据部门编号升序排列，如果部门一致，那就按照日期降序排列
select * from emp order by dno asc,birthday desc;

5.2运算符

# 需求：找出性别为男的员工
select * from emp where sex='男';

5.3 连接符

# 需求：性别为男且部门为20的员工
select * from emp where sex='男' and dno=20;

# 需求：性别为男或部门编号为20的
select * from emp where sex='男' or dno=20;

# 需求：性别为男且部门编号为20的员工或员工id<5
select * from emp where sex='男' and dno=20 or eno<5;

5.4 in/not in

在in的括号中可以给定多个参数值，参数值之间的条件是或关系，且不一定非要满足

# 需求：查询指定部门的员工信息
select * from emp where dno in(11,19,20,22);

5.5聚合函数

#需求：求部门编号的和
mysql> select sum(dno) from emp;

# 需求：求员工编号与部门编号的和
select eno + dno from emp;

行求和（如果列的值存在null，结果也是null，此时可以使用ifnull()进行运算时默认值的设置）

# ifnull(arg0,arg1) arg0代表可能为null的列，arg1代表如果为空要进行代替的值
select eno + ifnull(dno,0) from emp;

# 需求：求总行数 *代表以行数最多的结果为导向
select count(*) from emp;

# count也可以指定列，如果列中有null值，则不计入总数
select count(dno) from emp;

求最大值（max()）

# 求最大的部门编号
select max(dno) from emp;

求平均值（avg()）

# 求部门的平均值
select avg(dno) from emp;

5.6 分组（group by）

根据数据中的某一个特征对数据进行划分，这种形式叫分组

# 需求：求男生和女生的人数
select sex,count(sex) from emp group by sex;

having条件（结合group by使用，在分组的基础上再次进行筛选）

# 需求：求男生女生人数在3个以上的员工信息
select sex,count(sex) from emp group by sex having count(sex)>3;

having和where有什么区别？

答：1.where是直接跟在表的后面的条件，having是跟在group by后面的条件

​ 2.having的使用需要结合group by，group by在进行分组的时候是很耗性能的

5.7别名

别名仅仅只是在当前要显示的结果中生效，起到简化及通俗易懂的效果

关键词：as

# 需求：显示员工的信息(as可以省略不写)
select eno as 员工编号,ename as 员工姓名,dno 部门编号 from emp;

5.8 分页（limit）

limit在MySQL中称为方言（该关键词只能在MySQL中使用）

语法：limit arg0,arg1; arg0代表起始下标（数据库中的下标是从0开始的），arg1代表要查询的条数

# 需求：查询前三条数据
select * from emp limit 0,3;
# 需求：查询第二页的数据
select * from emp limit 3,3;
# 需求：查询第三页的数据
select * from emp limit 6,3;

6.约束

约束就是给字段添加对应的规则，用户在进行数据的操作时必须要符合对应的规则，才能进行操作，可以有效的保证数据的安全性

# 指定手机号不能为空
alter table emp modify phone varchar(11) not null;

# 指定手机号是唯一的（null值是可以重复的）
alter table emp modify phone varchar(11) unique;

# 指定手机号不能为null且唯一
alter table emp modify phone varchar(11) not null unique;

# 指定员工编号为主键
alter table emp modify eno int primary key;

思考：表中一定要存在主键吗？

答：建议每张表都要有一个主键，因为其他的字段中的数据可能存在重复的，就没有办法区分数据，所以主键的存在就是一个唯一标识

一般用户（程序员）会给每张表都设置一个id字段，作为主键，该字段如果是数字类型，为了方便管理，可以让其自增长（auto_increment）

# 让员工编号自增长
alter table emp modify eno int auto_increment;

可以在不添加id字段的情况下，自动填充id

可以指定id

指定后再进行填充就会接着上面的11开始往后填充

外键用于多张表之间的关联关系（主从关系）

# 给员工表中的部门编号设置外键约束
alter table 表名 add constraint 约束名 foreign key(当前表中约束的字段) references 主表表名(要约束的字段名);
alter table emp add constraint fk_emp_dno foreign key(dno) references depart(dno);

注意：1.主表中作为外键使用的字段要求一定是非空且唯一的

​ 2.如果此时要删除主表中的数据，前提是该外键字段的值在从表中没有被使用

​ 3.实现关联之前需要两个表都设置的有主键

7.多表查询

当多张表存在关联关系时，根据需求的不同可能会同时需要多张表一起使用

7.1 子查询

注意：每一个查询的SQL语句其结果本身就是一张表

将某个SQL的查询结果作为另一个SQL的查询条件

# 查询员工编号为7的员工所在的部门信息
select dno,dname,loc from depart where dno=(select dno from emp where eno=7);

注意：= > <等等后面只能跟一个值（子查询的结果必须是唯一的）

# 查询性别为男的员工对应的部门信息
select dno,dname,loc from depart where dno in(select dno from emp where sex='男');

7.2 多表查询

# 查询员工编号为7的员工信息及其所在的部门信息
select depart.dno,dname,loc,eno,ename,sex,birthday,phone,emp.dno from depart,emp where eno=7 and emp.dno=depart.dno;