SQL语句笔记

SQL

1. 什么是SQL？
    Structured Query language:结构化查询语言
    其实就是定义了操作所有关系型数据库的规则。
    每一种数据库操作的方式存在不一样的地方，成为“方言”。
2. SQL通用语法
    1. SQL语句可以单行活多行书写，以分号结尾。
    2. 可使用空格和缩进来增强语句的可读性。
    3. MySQL数据库的SQL语句不区分大小写，关键字建议使用大写。
    4. 3种注释
        - 单行注释: -- 注释内容/# 注释内容（mysql特有）
        - 多行注释: /* 注释 */
3. SQL分类
    1）DDL(Data Definition Language)数据定义语言
        用来定义数据库对象:数据库，表，字段等。
        关键字:create, drop, alter等
    2）DML(Data Manipulation Language)数据操作语言
        用来对数据库中表的数据进行增删改。
        关键字:inset, delete, update等
    3) DQL(Data Query Language)数据查询语言
        用来查询数据库中表的记录(数据)。
        关键字:select, where等
    4）DCL(Data Control Language)数据控制语言(了解)
        用来定义数据库的访问权限和安全级别，及创建用户。
        关键字:grant, revoke等

DDL:操作数据库、表

1. 操作数据库
    - 数据库中常用的几个关键字:
        - Row:行
        - Column:列(field/字段)
    - 创建数据库:
        - 基本语法:create database 数据库名字 [库选项];
        - 库选项:数据库的相关属性
            - 字符集:charset 字符集;
                - 代表着当前数据库下的所有表储存的数据默认指定的字符集
                  如果当前不指定，那么采用DBMS默认的
            - 校对集:collate 校对集;
            - 例：create database mydatabase2 charset gbk;
    - 显示全部数据库:
        show databases;
    - 显示部分数据库:
        - show databases like '匹配模式';
        - -:匹配当前位置单个字符
        - %:匹配指定位置多个字符
        - 例:
            - 获取以my开头的全部数据库:'my%';
            - 获取m开头，后面第一个字母不确定，最后为database的数据库:'m_database';
            - 获取以database结尾的数据库:'%database';
    - 显示数据库创建语句
        - 基本语法:show create database 数据库名称;
2. 操作表
    - 创建数据表:
        - 基本语法:  
            create [temporary] table 表名 (
                字段名1 数据类型 [列级完整性约束条件] [默认值]
                [,字段名2 数据类型 [列级完整性约束条件][默认值]]
                [,······]
                [,表级完整性约束条件]
            )[engine=引擎类型]
        - 列级完整性约束条件:
            1. 主键约束:PRIMARY KEY(primary key)
                单字段主键约束、多字段主键约束
            2. 外键约束:
                - 格式:
                CONSTRAINT(constraint) 外键名称 FOREIGN KEY(foreign key)(被外键约束的字段名称
                - 特点:
                    (1). 外键约束可以描述任意一个字段(包括主键)，可以为空，并且一个表中可以有多个外键。但是外键字段中的值必须是另一张表中的主键。  
                    (2). 这样被外键关联的两种表的关系可以称为父子表或者主从表。子表(从表)拥有外键字段的表，父表(主表)被外键字段所指向的表。  
                    (3). 子表被外键约束修饰的字段必须和父表的主键字段的类型一样。
                - 注意:
                    一个表中有被外键修饰的字段，就称该表有外键(是“有外键”。而不是“是外键”)，
                    并会给该表中的外键约束取一个名称，所以我们常说的这个表有没有外键，
                    指的不是被外键约束修饰的字段名，而是指这个表是否有存在外键约束。
                    也就是说，不能说这个表的外键是xxx(该表中被外键约束修饰的字段名)，这种说法是错误的。
            3. 非空约束:NOT NULL(not null)
                被该约束修饰了的字段不能为空。
            4. 唯一约束:UNIQUE(unique)
                被该约束修饰了的字段中的值唯一，不能有相同的值。
            5. 默认约束:DEFAULT(default)
                指定这一列的默认值是多少。
            6. 自动增加:AUTO_INCREMENT(auto_increment)
                该约束一个表中只能有一个字段使用;
                并且使用这个约束的字段只能是整数类型(TINYINT,SMALLIN,INT,BIGINT);
                默认值是1，也就是说从1开始增加的。
                一般就是给主键使用的。
    - 查询表结构:
        - 查看所有表:
            show tables;
        - 查看匹配数据表:
            show table like '匹配模式';
        - 查看表基本结构语句:
            DESCRIBE 表名(desc 表名);
        - 查看表创建语句:
            show create 表名;  
            要看清楚一些可以用: show create 表名\G;
    - 设置表属性:
        - 表属性指的就是表选项:engine,charset和collate
        - 基本语法:alter table 表名 表选项 [=] 值;
        - 注意:  
            如果数据库已经确定了，里面有很多数据了，
            就不要轻易修改表选项(字符集影响不大)。
    - 修改表结构:
        - 修改表名:
            rename table 旧表名 to 新表名;
        - 新增字段:
            alter table 表名 add [column] 新字段名 新字段类型 [属性] [位置 first/after 字段名];
            - 特点:
                默认是加到表的末尾位置。
            - 字段位置:字段想要存放的位置。
                - first: 放到某一个字段之前(最前面),第一个字段。
                - after 字段名: 放在某个具体的字段之后(默认的)。
        - 修改字段名:
            alter table 表名 change 旧字段名 新字段名 新字段类型 [列属性] [新位置];
        - 修改字段类型(属性):
            alter table 表名 modify 字段名 新字段类型 [新属性] [新位置];
        - 修改字符集:
            alter table 表名 character set 字符集名称;
        - 删除字段:
            alter table 表名 drop 字段名;
        - 删除表结构:
            - 基础语法:drop table 表名[,表名2...];
            - 判断存在后删除:drop table if exists 表名;
            - 可以同时删除多个数据表。
        - 复制表:
            create table 表名 like 被复制表名;

DML:增删改表中数据

1. 添加数据:
    - 基本语法:
        insert into 表名(列名1, 列名2,···列名n) values(值1, 值2, 值n);
    - 注意:
        1. 列名和值要一一对应。
        2. 如果表名后不定义列名，则默认给所有列添加值，缺一不可。
            insert into 表名 values(值1, 值2, 值n);
        3. 除了数字类型，其他的类型需要使用引号(单双引号都可以)引起来
2. 删除数据:
    - 基本语法:
        delete from 表名 [where 条件];
    - 注意:
        (1). 如果不加条件，则删除表中所有记录。
        (2). 删除表中所有数据语法: 
            1) delete from 表名;
                不推荐使用。有多少条记录就会执行多少次删除操作。
            2) truncate table 表名;
                推荐使用。先删除表，然后再创建一张一样的表，效率更高。
3. 修改数据:
    - 基本语法:
        update 表名 set 列名1 = 值1,列名2 = 值2,...[where 条件];
    - 注意:
        如果不加任何条件，则会将表中所有记录全部修改。

DQL:查询表中的记录

1. 语法:
    select 
        字段列表
    from
        表名列表
    where
        条件列表
    group by
        分组字段
    having
        分组之后的条件
    order by
        排序
    limit
        分页限定
​
2. 基础查询
    (1). 多个字段查询
        - select 字段名1,字段名2,...from 表名;
        - 注意:查询所有字段，则可以使用*来代替字段列表。
            select * from 表名；
    (2). 去重
        select distinct 字段名1,字段名2,...from 表名;
    (3). 计算列
        - 一般可以使用四则运算计算一些列的值。(一般只会进行数值型的计算)
        - ifnull(stp1,stp2): 参数一是字段名，参数二是赋值。
            如果参与运算字段中有null值，则需要使用此函数为null值字段赋值，
            然后才可以参与运算，否则计算结果为null。
​
            格式: select distinct 字段名1,ifnull(字段名2,0), 字段名1+字段名2 from 表名;
    (4). 起别名
        - 格式: select distinct 字段名1 as 别名,字段名2 as 别名, 字段名1+字段名2 as 别名 from 表名;
        
        - as也可以用空格替换。
    
3. 条件查询
    (1). where 子句后跟条件
    (2). 运算符
        - '>'、 '<'、 '<='、 '>='、 '='、 '<>'
        -  between...and
            基本语法:select * from 表名 where 字段名 between 条件1 and 条件2;
            注意: 这里的查询区间是包含条件1和条件2的。
        -  in(集合)
            基本语法:select * from 表名 where 字段名 in(条件集);
        -  like: 模糊查询
            -  占位符:
                _:单个任意字符
                %:多个任意字符
        -  is null(is not null): 判断是否为null只能用这个。
        -  and 或 &&
        -  or 或 ||
        -  not 或 !
        
    (3). 格式: select * from 表名 where 条件;
​
4. 排序查询
    - 基本语法:order by 子句;
        - order by 排序字段1 排序方式1, 排序字段2 排序方式2...
    - 排序方式:
        - asc: 升序，默认的。
        - desc: 降序。
    - 注意:
        如果有多个查询条件，则当前面的条件相同时，才会判断下一个条件。
​
5. 聚合函数
    - 将一列数据作为一个整体，进行纵向的计算。
    
    - 注意: 
        - 所有聚合函数的计算不包括null值。
        - 解决方案:
            - 选择not null的字段进行计算。
            - 用ifnull函数。
​
    (1). count(字段名): 计算个数
        - 一般选择非空的列(主键)进行count计算。
        - select count(*) from 表名;
    (2). max(字段名): 计算最大值
    (3). min(字段名): 计算最小值
    (4). sum(字段名): 计算和
    (5). avg(字段名): 计算平均值
        
6. 分组查询
    - 基本语法: group by 分组字段;
    - 注意:
        (1). 分组之后查询的字段: 分组字段或聚合函数。其他字段没有意义。
        (2). where和having的区别:
            - where在分组之前进行限定，如果不满足条件，则不参与分组。
            - having在分组之后进行限定，如果不满足结果，则不会被查询出来。
            - where后面不可以跟聚合函数，having可以对聚合函数进行判断。
        
7. 分页查询
    - 基本语法: limit 开始的索引,每页的条数;
    - 公式: 开始的索引 = (当前的页码 - 1) * 每页显示的条数
    - MySQL中用limit进行的分页操作是一个“方言”

约束

- 概念: 对表中的数据进行限定，保证数据的正确性、有效性和完整性。
- 分类: 
    1. 主键约束: primary key
        - 删除主键: alter table 表名 drop primary key;
        - 删除自动增长: alter table 表名 modify 字段名 字段类型;
​
    2. 非空约束: not null
        - 创建表时添加约束
            create table 表名(
                id int not null,
                name varchar(5)
            );
        - 在已有表上添加约束
            alter table 表名 modify 字段名 字段类型 not null;
        - 删除某个字段的约束
            alter table 表名 modify 字段名 字段类型;
​
    3. 唯一约束: unique
        - 删除唯一约束: alter table 表名 drop index 字段名;
​
    4. 外键约束: foreign key
        - 让表与表产生关系，从而保证数据的正确性。
        - 基本语法:
            - 在创建表时
                create table 表名(
                    ···
                    外键字段
                    constraint 外键名称 foreign key(外键字段名) references 主表名称(主表字段名)
                );
            - 删除外键
                alter table 表名 drop foreign key 外键名称;
            - 创建表之后，添加外键
                alter table 表名 add constraint 外键名称 foreign key(外键字段名) references 主表名称(主表字段名);
        - 级联操作
            - 添加级联操作
                - 基本语法: alter table 表名 add constraint 外键名称 foreign key(外键字段名) references 主表名称(主表字段名)
                            on update cascade on delete cascade;
            - 分类:
                - 级联更新: on update cascade
                - 级联删除: on delete cascade
            - 注意: 级联操作需谨慎使用！

数据库的设计

多表之间的关系

1. 分类
    (1).一对一(了解):
        如: 人和身份证
        分析: 一个人只有一个身份证，一张身份证只能对应一个人。
    (2).一对多(多对一):
        如: 部门和员工
        分析: 一个部门有多个员工，一个员工只能对应一个部门。
    (3).多对多:
        如: 学生和课程
        分析: 一个学生可以选择很多门课程，一个课程也可以被很多学生选择
​
2. 实现关系
    (1).一对多(多对一):
        实现方式: 在多的一方建立外键，指向一的一方的主键。
    (2).多对多:
        实现方式: 多对多关系的实现需要借助于第三张中间表。
                  中间表至少要包含两个字段，这两个字段作为第三张表的外键，分别指向两张表的主键。
    (3).一对一:
        实现方式: 一对一关系的实现，可以在任意一方添加唯一(unique)外键指向另一方的主键。

数据库设计的范式

- 概念: 设计数据库是，需要遵循的一些规范。要遵循后边的范式要求，必须先遵循前边的所有范式要求。
    
    - 设计关系数据库时，遵从不同的规范要求，设计出合理的关系型数据库，这些不同的规范要求被称为不同的范式，各种范式呈递次规范，越高的范式数据库冗余越小。
    - 目前关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式(4NF）和第五范式（5NF，又称完美范式）。
​
- 分类:
    1. 第一范式(1NF): 每一列都是不可分割的原子数据项
    2. 第二范式(2NF): 在1NF的基础上，非码属性必须完全依赖于候选码(在1NF基础上消除非主属性对主码的部分函数依赖)
    3. 第三范式(3NF): 在2NF的基础上，任何非主属性不依赖于其他非主属性(在2NF基础上消除传递依赖)

数据库的备份和还原