MySql数据库之排序 聚合函数 完整性

排序 order by 列名 asc(默认) desc(降序)

查询所有学生记录 按年龄升序排列
select * from student order by age;
或者
select * from student order by age ASC;

查询所有学生记录,按年级降序排序
select * from student order by age DESC;

查询所有学生,按班级数字降序排序,如果班级相同,按学号降序排序
select * from emp order by classId DESC,sid DESC;

聚合函数

聚合函数是用来做纵向运算的函数
COUNT(*):统计指定列不为NULL的记录行数;
MAX():计算指定列的最大值,如果指定列是字符串类型,那么使用字符串排序运算;
MIN():计算指定列的最小值,如果指定列是字符串类型,那么使用字符串排序运算;
SUM():计算指定列的数值和,如果指定列类型不是数值类型,那么计算结果为0;
AVG():计算指定列的平均值,如果指定列类型不是数值类型,那么计算结果为0;

COUNT

当需要纵向统计时,需要COUNT()
查询emp表中的记录数
select count(*) as total from emp;

查询emp表中拥有成绩的人数
select count(grade) as total from emp;
注意:因为count()函数中给出的是grade列,那么只统计grade列中非null的函数

查询emp表中成绩大于60的人数
select count(*) from emp where grade>=60;

统计月薪和薪金之和大于2500的人数
select count(*) from emp where salary+IFNULL(comm,0)>2500;

查看有佣金,有领导的人数
select count(comm),count(mgr) from emp;

SUM和AVG(自动过滤空值)

当需要纵向求和时,使用sum()

求所有学生的成绩之和
select sum(grade) from emp;

查询所有雇员月薪之和,佣金之和
select sum(salary),sum(somm) from emp;

查询所有雇员月薪加薪金之和
select sum(salary + IFNULL(comm,0)) from emp;

统计所有员工的平均薪资
select avg(salary) from emp;

MAX和MIN

查询最高工资和最低工资
select max(salary),min(salary) from emp;

分组查询

当需要分组查询时,需要使用group by语句,例如查询每个部门的薪资和,这说明要
使用部门来分组
凡是和聚合函数同时出现的列名,一定要写在group by之后

GROUP BY

查询每个部门的部门编号和每个部门的薪资和
select deptno,sum(salary) from emp group by deptno;

查询每个部门的部门编号和每个部门的人数和
select deptno,count(*) from emp group by deptno;

查询每个部门的部门编号,和每个部门薪资大于1500的人数
select deptno,count(*) from emp where salary > 1500 group by deptno;

HAVING(分组后使用的条件)

查询部门工资总和大于9000的部门编号以及工资和
select deptno,sum(salary) from emp group by deptno HAVING sum(salary) > 9000;

where 和 having的区别

1.where是在分组前对数据进行过滤
  having实在分组后对数据进行过滤

2.where后面不可以使用聚合函数
  having后面可以使用聚合函数

where是对分组前记录的条件,如果某行记录没有满足where子句的条件,
那么这行记录不会参加分组;
而having是对分组后的数据的约束

LIMIT

LIMIT用来限定查询结果的起始行,以及总行数

查询5行记录,起始行从0开始
SELECT * FROM emp LIMIT 0,5;
注意:起始行从0开始,也就是第一行开始

查询10行记录,起始行从3开始
SLECT * FROM emp LIMIT 3,10;

分页查询
如果一页记录为10条，希望查看第3页记录
第一页记录起始行为0，一共查询10行；
第二页记录起始行为10，一共查询10行；
第三页记录起始行为20，一共查询10行；

模糊查询

当查询姓名中包含a字母的学生时就需要使用模糊查询了,模糊查询需要
使用关键字like.
通配符:
_任意一个字符
%:任意0-n个字符
'%张%' '张_'

数据的完整性

作用:保证用户的数据,保存到数据库中时是正确的
确保数据的完整性 = 在创建表时给表中添加约束

完整性的分类:
实体完整性:
域完整性:
引用完整性:

实体完整性

实体:即表中的一行(一条记录)代表一个实体(entity)

实体完整性的作用:标示每一行数据不重复
约束类型:主键约束(primary key) 唯一约束(unique) 自动增长列(auto_increment)

主键约束(primary key)

注:每个表中要有一个主键
特点:数据唯一,且不能为null
第一种添加方式:
create table student(
    id int primary key,
    name varchar(100)
);

第二种添加方式:此种添加方式优势在于可以创建联合主键
create table student(
    id int,
    name varchar(100),
    primary key(id)
);

联合主键是一个主键 只要两个字段不完全一样就可以
create table student(
    classId int,
    studentId int,
    name varchar(100),
    primary key(classId,studentId)
);
例如:
classId  studentId
1          1
1          2
2          1
2          2

第三种添加方式ADD CONSTRAINT(添加约束):
CREATE TABLE student(
    id int,
    name varchar(100)
);
ALTER table student add constraint primary key(id); 

唯一约束(unique)

特点:数据不能重复,但可以是空值
create table student(
    id int primary key,
    name varchar(100) unique 
);

字动增长列(auto_increment)

给主键添加自动增长的数值,列只能是整数类型
(注意数据被删除了 也会安装原来的数继续增长 不是主键也能添加自动增长列)
CREATE TABLE student(
    id int primary key auto_increment,
    name varchar(100)
)
INSERT INTO student(name) values('TOM');
自动增长的主键给null,也会自动增长

域完整性

域完整性的作用:限制此单元格的数据正确,不对照此列的其他单元格比较
域代表当前单元格
域完整性约束:非空约束(not null) 默认值约束(default)

非空约束(not null)

create table student(
    id int primary key,
    name varchar(100) not null,
    sex varchar(100)
);
INSERT INTO STUDENT VALUES(1,'TOM',NULL);

默认值约束(default)

CREATE TABLE STUDENT(
    id int primary key,
    name varchar(100) not null,
    sex varchar(100) default '男'
);
默认约束 插入的时候给null也可以 会把null插进去,

引用完整性(参照完整性)

第一种外键添加方式:
CREATE TABLE student(
    sid int primary key,
    name varchar(100) not null,
    sex varchar(100) default '男'
);

create table score(
    id int,
    score int,
    sid int, // 外键列的数据类型一定要和主键的类型一致
    CONSTRAINT fk_score_sid foreign key(sid) references student(sid)
);

第二种外键添加方式
ALTER TABLE score ADD CONSTRAINT fk_stu_score FOREIGN KEY(sid) REFERENCES stu(id);