Mysql数据库基础操作加强之DQL查询语句拓展

DQL查询语句

1.排序查询

1.排序查询
    语法：ORDER BY
        ORDER BY 列名1 排序方式1，列名2 排序方式2...
    两种排序方式：
        ASC：升序，默认
        DESC：降序
    注意：多个排序条件，当第一个条件的值相同时，才会按照第二个条件排序
    格式：
    SELECT * FROM 表名 ORDER BY 列名 ASC; -- 按照列名升序排列
    SELECT * FROM 表名 ORDER BY 列名1 ASC, 列名2 DESC；　-- 先按照列名1升序，列名1的值相同是再按照列名2降序     

2.聚合函数
    将一列数据作为一个整体，进行纵向的计算。
    1. COUNT:计算个数
    2. MAX：计算最大值
    3. MIN：计算最小值
    4. SUM：计算和
    5. AVG:计算平均 

    注意：聚合函数会排除为NULL的列
    解决方案：
        不采用为NULL的列
        例如：SELECT COUNT(主键) FROM 表名;
        函数IFNULL(列名,0)
        例如：SELECT AVG(列名,0) FROM 表名;

3.分组查询
    格式：GROUP BY 分组字段
    注意：
        分组之后，查询的结果是分组字段 或者是 聚合函数
        WHERE 和 HAVING 区别
        WHERE 在分组之前，不满足条件，不参与分组，后面不能接聚合函数
        HAVING 在分组之后，不满足条件不会查询出来，后面可以接聚合函数
    举例：
        SELECT 性别,COUNT(主键id) FROM 学生表 GROUP BY 性别; --  根据性别进行分组,查询学生表当中的性别和对应的人数
        SELECT 性别，COUNT(主键id) FROM 成绩>80 GROUP BY 性别 HAVING COUNT(主键id) >2; -- 从学生表中查询成绩大于80，按照性别进行分组并且人数大于2的组

4.分页查询
    格式：LIMIT 起始位置,每页条数
    计算公式：起始位置：(当前页数-1)*每页显示条数；
    举例：SELECT * FROM student LIMIT 0,3;  -- 第1页显示3条数据
           

2.约束

1.非空约束（NOT NULL）
    效果：不能添加为空的数据

    1.创建表的时候添加约束
        CREATE TABLE 表名(列名 数据类型 NOT NULL ...);
    2.表创建完，再添加非空约束
        ALTER TABLE 表名 MODIFY 列名 数据类型 NOT NULL;
    3.删除非空
        ALTER TABLE 表名 MODIFY 列名 数据类型

2.唯一约束（UNIQUE）
    效果:不能添加重复的数据，NULL只能有一个

    1.创建表的时候添加唯一约束
        CREATE TABLE 表名（列名 数据类型 UNIQUE）;
    2.表创建完，再添加唯一约束(表里面的数据不能有重复)
        ALTER TABLE 表名 MODIFY 列名 数据类型 UNIQUE;
    3.删除唯一约束
        ALTER TABLE 表名 DROP INDEX 列名

3.主键约束（KEY AUTO_INCREMENT）

    效果：非空并且唯一，而且一张表中只能有一个主键
    注意：
        KEY:非空且唯一，一张表中只能有一个主键
        AUTO_INCREMENT：根据上一行的数据自动增长
    1.创建表的时候添加主键
        CREATE TABLE 表名(列名1 数据类型1 PRIMARY KEY AUTO_INCREMENT,列名2 数据类型2....)
    2.删除主键
        ALTER TABLE 表名 DROP PRIMARY KEY;
        ALTER TABLE 表名 MODIFY 自动增长的列名 自动增长的数据类型; -- 删除自动增长的效果
    3.表创建完，再添加主键(表中不能有重复的主键)
        ALTER TABLE 表名称 MODIFY 列名称 数据类型  PRIMARY KEY AUTO_INCREMENT;
4.外键约束
    名称：FOREIGN KEY
    效果：让两个表产生关联，自己表"外键"连上别的表"主键"
        1.创建表的时候添加主键
            CREATE TABLE 表名(
                列名1 数据类型1,
                列名2 数据类型2,
                CONSTRAINT 外键别名 FOREIGN KEY (外键列名称) REFERENCES 其他表名称(其他表主键)
            );
        2.表创建完，再添加主键
            ALTER TABLE 表名称 ADD CONSTRAINT 外键别名 FOREIGN KEY (外键列名称) REFERENCES 其他表名称(其他表主键);
        3.删除外键
            ALTER TABLE 表名称 DROP FOREIGN KEY 外键别名;
    外键级联操作
        多个表主外键相互联系，在删除和修改的时候，彼此受到影响
        级联更新：ON UPDATE CASCADE
        级联删除：ON DELETE CASCADE
        添加级联操作
            ALTER TABLE 表名称 ADD CONSTRAINT 外键别名 FOREIGN KEY (外键列名称) REFERENCES 其他表名称(其他表主键) ON UPDATE CASCADE ON DELETE CASCADE;
           

3.多表关系

1.一对多
    A.生活实例: 
        部门和员工之间的关系
    B.建表原则:
        一是主键,多是外键.多的外键指向一的主键
2.多对多
    A.生活实例:
        学生和课程之间的关系
    B.建表原则:
        设计一个"中间表",至少包含两个字段(两个表的主键)
3.一对一 （了解即可）
    A.生活实例:
        人和身份证的关系
    B.建表原则:
        在任意一方添加外键（唯一约束 UNIQUE ）指向另一方的主键
           

4.范式

1.范式含义
    设计数据库的时候,遵循的规范
2.类型
    -第一范式(1NF) 每一项都是不可以分割的原子数据.不能存在合并项
    -第二范式(2NF) 抽取了核心直接依赖关系,其他间接关系放在一边
    -第三范式(3NF) 再次抽取间接表当中的依赖关系.
    -巴斯-科德范式(BCNF)
    -第四范式(4NF)
    -第五范式(5NF)
           

5.数据库备份还原

使用命令行备份还原
    1.备份数据库
        mysqldump -u用户名 -p密码 数据库名称 > 保存的路径
    2.还原数据库
        -登录数据库
        -创建数据库
        -使用数据库
        -执行文件  source 文件路径