数据库之MySQL 查询与约束

查询

排序 order by  asc(默认 升序)  desc(降序)
SELECT 列名 FROM 表名 order by 列名 asc(升序) | desc(降序);

过滤空值 
SELECT IFNULL(字段名,0) FROM 表名

聚合函数 sum avg max min count

聚合函数是用来做纵向运算的函数：

COUNT()：统计指定列不为NULL的记录行数；
MAX()：计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算；
MIN()：计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算；
SUM()：计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为0；
AVG()：计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为0；

分组查询

当需要分组查询时需要使用GROUP BY子句

查询每个部门的部门编号和每个部门的工资和：
    SELECT deptno,SUM(sal) FROM emp GROUP BY deptno;
查询每个部门的部门编号以及每个部门的人数:
    SELECT deptno,COUNT(*) FROM emp GROUP BY deptno;
查询每个部门的部门编号以及每个部门工资大于1500的人数:
    SELECT deptno,COUNT(*) FROM emp WHERE sal>1500 GROUP BY deptno;
注意:凡是和聚合函数同时出现的字段名,一定要写在GROUP BY之后

HAVING子句(分组后使用的条件)

HAVING与WHERE的区别:
1.HAVING是在分组之后对数据进行筛选,WHERE是在分组之前对数据进行筛选
2.HAVING后面可以使用聚合函数,WHERE后面不可以使用聚合函数

-- 分页查询
SELECT
    *
FROM
    emp
LIMIT 0,-- 代表起始页
      3; -- 代表一共显示几条记录

模糊查询

模糊查询需要使用关键字LIKE。
通配符: 
    _ 任意一个字符
    // 代表查询到的是以w开头的两个字符
    SELECT * FROM 表名 WHERE 字段名 LIKE 'w_'; 

    %：任意0~n个字符
    // 代表查询到的是包含w的结果
    SELECT * FROM 表名 WHERE 字段名 LIKE '%w%'; 

数据的完整性

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

实体完整性

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

实体完整性的作用：标识每一行数据不重复。

约束类型： 主键约束（primary key) 唯一约束(unique) 自动增长列(auto_increment)

主键约束（primary key）
注：每个表中要有一个主键。
特点：数据唯一，且不能为null
例：
第一种添加方式：
    CREATE TABLE student(
        id int primary key,
        name varchar(50)
    );
第二种添加方式:(好处:可以添加联合主键)
    CREATE TABLE student(
        id int,
        name varchar(50),
        PRIMARY KEY(id)
    );
联合主键是一个主键 只要两个字段不完全一致就可以都插入到数据库中
    CREATE TABLE student(
        classid int,
        stuid int,
        name varchar(100),
        PRIMARY KEY(classid,stuid)
    );
第三种添加方式:
    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 VALUES(NULL,'张三');
    INSERT INTO student VALUES(NULL,'李四');

域完整性

域完整性的作用：限制此单元格的数据正确，不对照此列的其它单元格比较

非空约束 (not null) 和 默认值 (default)

CREATE TABLE student(
    id int PRIMARY KEY,
    name varchar(50) not null,
    sex varchar(50) DEFAULT '男'
);
    // 错误插入 值不能为空
    // INSERT INTO student VALUES(1,NULL,'男');
    // 性别默认为男
    INSERT INTO student (id,name) VALUES(3,'李四');
    // 给null 会把null插入进去
    INSERT INTO student VALUES(2,'张三',NULL);

引用完整性 (外键约束 让表与表之间通过字段建立联系)

外键约束: FOREIGN KEY

 CREATE TABLE student(
        sid int PRIMARY KEY,
        name varchar(100)
    );
    CREATE TABLE score(
        score int,
        sid int,
        // 第一种创建外键
        // 建立表联系 FOREIGN KEY(当前表的字段) 
        CONSTRAINT fk_score_sid FOREIGN KEY(sid) REFERENCES student(sid)
    );
    第二种创建方式:
    CREATE TABLE score1(
        score int,
        sid int
    );
    ALTER TABLE score1 ADD CONSTRAINT fk_score_sid FOREIGN KEY(sid) REFERENCES student(id);