mysql知识总结

Posted on

MySQL系列总结————相关术语

主键

  • 主键

    对于关系型数据表，通过一个字段来唯一确定一条记录，该字段就叫做主键

    主键的一些要求：

    1. 一旦记录，主键最好不要再修改，因为主键是用来唯一定位记录的
    2. 主键基本原则： 不使用任何和业务相关的字段
    3. 常用的类型有： 自增整数类型， 全局唯一的GUID类型
注意： 

1. GUID算法通过网卡MAC地址、时间戳和随机数保证任意计算机在任意时间生成的字符串都是不同的，大部分编程语言都内置了GUID算法，可以自己预算出主键。
2. 如果使用INT自增类型，那么当一张表的记录数超过2147483647（约21亿）时，会达到上限而出错。使用BIGINT自增类型则可以最多约922亿亿条记录。
3. 自增型到2了，这时删除一个数据，再加入一个，id从3开始增，不会补前面的

  • 联合主键

    关系数据库实际上还允许通过多个字段唯一标识记录，即两个或更多的字段都设置为主键，这种主键被称为联合主键。对于联合主键，允许一列有重复，只要不是所有主键列都重复即可

    可以理解为，是将多个主键连成一个字符串，只要该字符串不相同即可。

    （没有必要的情况下，我们尽量不使用联合主键，因为它给关系表带来了复杂度的上升。）

外键

定义： 可以把数据与另一张表关联起来，这种列称为外键。外键并不是通过列名实现的，而是通过定义外键约束实现的：

适用情况： 多对一
建表时设置外键：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
CREATE TABLE t_class(
    cno INT PRIMARY KEY,
    cname VARCHAR(32) NOT NULL
);

CREATE  TABLE t_students(
    sno INT PRIMARY KEY auto_increment,
    sname VARCHAR(32) NOT NULL,
    classno INT(3),
    /* 删除时会出现删不掉的问题 */
    FOREIGN KEY(classno) REFERENCES t_class(cno)
    /* 级联删除，有点狠，没有余地，一般不使用 */
    -- FOREIGN KEY(classno) REFERENCES t_class(cno) ON DELETE CASCADE
    /* 级联置空，常用 */
    -- FOREIGN KEY(classno) REFERENCES t_class(cno) ON DELETE SET NULL
)

添加外键约束：

1
2
3
4
ALTER TABLE students
ADD CONSTRAINT fk_class_id
FOREIGN KEY (class_id)
REFERENCES classes (id);

删除外键约束：

1
2
ALTER TABLE students
DROP FOREIGN KEY fk_class_id;

(删除外键约束并没有删除外键这一列。删除列是通过DROP COLUMN …实现的。)

注意： 由于外键约束会降低数据库的性能，大部分互联网应用程序为了追求速度，并不设置外键约束，而是仅靠应用程序自身来保证逻辑的正确性。这种情况下，class_id仅仅是一个普通的列，只是它起到了外键的作用而已。

多对多

多对多关系实际上是通过两个一对多关系实现的，即通过一个中间表，关联两个一对多关系，就形成了多对多关系。

一对一

方法：

  1. 一个表的记录对应到另一个表的唯一一个记录。
  2. 也可以将两表数据放入一个表（不推荐使用）

注意： 还有一些应用会把一个大表拆成两个一对一的表，目的是把经常读取和不经常读取的字段分开，以获得更高的性能。

索引

定义： 索引是关系数据库中对某一列或多个列的值进行预排序的数据结构。通过使用索引，可以让数据库系统不必扫描整个表，而是直接定位到符合条件的记录，这样就大大加快了查询速度。

创建索引：

  • 创建函数

    1
    CREATE INDEX idx_name ON studnets(name(length));

  • 修改表结构

    1
    2
    ALTER TABLE students
    ADD INDEX idx_name_score (name, score);

  • 创建表时直接指定

    1
    2
    3
    4
    5
    CREATE TABLE students(  
    ID INT NOT NULL,   
    username VARCHAR(16) NOT NULL,  
    INDEX [idx_name] (name(length))  
    );

要点：
* 索引的效率取决于索引列的值是否散列，即该列的值如果越互不相同，那么索引效率越高。
* 对于主键，关系数据库会自动对其创建主键索引。使用主键索引的效率是最高的，因为主键会保证绝对唯一。
* 索引的优点是提高了查询效率，缺点是在插入、更新和删除记录时，需要同时修改索引文件，因此，索引越多，插入、更新和删除记录的速度就越慢
* 数据库索引对于用户和应用程序来说都是透明的。

  • 唯一索引

    列唯一性约束的实现：

    1. 设置唯一性索引（其他几个创建方法同普通索引)

      1
      2
      ALTER TABLE students
      ADD UNIQUE INDEX uni_name (name);

    2. 添加唯一约束，而不添加索引

      1
      2
      ALTER TABLE students
      ADD CONSTRAINT uni_name UNIQUE (name);

查询数据

  • 基本查询(SELECT)

  • 条件查询(WHERE)

  • 投影查询

  • 排序(ORDER BY)

  • 分页查询(LIMIT … OFFSET …)

  • 聚合查询

    函数 说明
    COUNT 计算某一列的满足条件的个数
    SUM 计算某一列的合计值，该列必须为数值类型
    AVG 计算某一列的平均值，该列必须为数值类型
    MAX 计算某一列的最大值
    MIN 计算某一列的最小值

    • 分组
      GROUP BY

      注意：

      • 聚合查询的列中，只能放入分组的列。因为在任意一个分组中，class_id，anme是不同的
      • 也可以使用多个列进行分组。
      • HAVING 过滤（作用和where相同）,经常与GROUP BY结合使用（由于where中不能使用别名和组函数）

  • 多表查询

  • 连接查询

    • INNER JOIN只返回同时存在于两张表的行数据
    • RIGHT OUTER JOIN返回右表都存在的行。如果某一行仅在右表存在，那么结果集就会以NULL填充剩下的字段。
    • LEFT OUTER JOIN则返回左表都存在的行。
    • 我们使用FULL OUTER JOIN，它会把两张表的所有记录全部选择出来，并且，自动把对方不存在的列填充为NULL：

修改数据

  • INSERT， 增

    1
    INSERT TABLE t(name, age) VALUE("hhh", 12);

  • DELETE， 删

    • 一条或多条
    • 没有设置where，会删除所有数据
      1
      2
      DELETE FROM t
      WHERE name = 'hhh';

  • UPDATE， 改

    • UPDATE <表名> SET 字段1=值1, 字段2=值2, ... WHERE ...;
    • 可以一次修改多条数据或一条数据
    • 注意：如果不加where，则会更新所有表项
      1
      2
      3
      UPDATE t
      SET name = 'aaa', age=20
      WHERE name = 'hhh';

select子句顺序

子句 说明 是否必须使用
select 要返回的列或表示式 是
form 从中检索数据的表 仅在从表选择数据时使用
where 行级过滤 否
group by 分组说明 仅在按组计算聚集时使用
having 组级过滤 否
order by 输出排序顺序 否
limit 要检索的行数 否

MySQL的安装配置：

my.ini配置文件，放在mysql的bin同级目录下