MySQL语句复习

一、主键

选取主键的主要原则不与任何业务相关的字段作为主键，不允许为NULL,常见为id,使用BIGINT自增或者GUID类型
1、自增整数类：数据库会在插入数据时自动为每一条记录分配一个自增整数，这样我们就完全不用担心主键重复，也不用自己预先生成主键；

BIGINT NOT NULL AUTO_INCREMENT

2、全局唯一GUID类型：使用一种全局唯一的字符串作为主键，类似8f55d96b-8acc-4636-8cb8-76bf8abc2f57。GUID算法通过网卡MAC地址、时间戳和随机数保证任意计算机在任意时间生成的字符串都是不同的，大部分编程语言都内置了GUID算法，可以自己预算出主键。

如果使用INT自增类型，那么当一张表的记录数超过2147483647（约21亿）时，会达到上限而出错。使用BIGINT自增类型则可以最多约922亿亿条记录。

二、外键

ALTER TABLE students    （alter改变 table）
ADD CONSTRAINT fk_class_id   (add constraint约束)外键约束的名称fk_class_id可以任意
FOREIGN KEY (class_id)   (foreign key)指定了class_id作为外键，
REFERENCES classes (id);   (references) 指定了这个外键将关联到classes表的id列

1. 删除外键约束，删除外键约束并没有删除外键这一列。删除列是通过DROP COLUMN ...实现的。

ALTER TABLE students
DROP FOREIGN KEY fk_class_id;

2. 对于主键，关系数据库会自动对其创建主键索引。使用主键索引的效率是最高的，因为主键会保证绝对唯一。
3. 使用ADD INDEX idx_score (score)就创建了一个名称为idx_score，使用列score的索引。索引名称是任意的，索引如果有多列，可以在括号里依次写上，例如：

ALTER TABLE students
ADD INDEX idx_name_score (name, score);
ADD UNIQUE INDEX uni_name (name); 给该列添加唯一索引

4. 也可以只对某一列添加一个唯一约束而不创建唯一索引：唯一约束用来保证一个列（或一组列）中数据唯一，类似于主键，但跟主键有区别：表可包含多个唯一约束，但只允许一个主键
   唯一约束列可修改或更新
   创建表时，通过unique来设置

ALTER TABLE students
ADD CONSTRAINT uni_name UNIQUE (name);

三、条件查询

1. 条件查询的语法就是：SELECT * FROM <表名> WHERE <条件表达式>
   优先级NOT>AND>OR
   

四、排序

-- 按score从低到高
SELECT id, name, gender, score FROM students ORDER BY score;

如果要反过来，按照成绩从高到底排序，我们可以加上DESC表示“倒序”：

-- 按score从高到低
SELECT id, name, gender, score FROM students ORDER BY score DESC;

如果score列有相同的数据，要进一步排序，可以继续添加列名。例如，使用ORDER BY score DESC, gender表示先按score列倒序，如果有相同分数的，再按gender列排序：

-- 按score, gender排序:
SELECT id, name, gender, score FROM students ORDER BY score DESC, gender;

默认的排序规则是ASC：“升序”，即从小到大。ASC可以省略，即ORDER BY score ASC和ORDER BY score效果一样。
-- 带WHERE条件的ORDER BY:
SELECT id, name, gender, score
FROM students
WHERE class_id = 1
ORDER BY score DESC;

五、分页

LIMIT总是设定为pageSize；
OFFSET计算公式为pageSize * (pageIndex - 1)。

limit 3 offset 6 表示从第6个开始取取三个值，表示第三页
limit 3 offset 3 表示从第3个开始取取三个值，表示第2页

LIMIT 3 OFFSET 0表示，对结果集从0号记录开始，最多取3条。注意SQL记录集的索引从0开始。

SELECT id, name, gender, score
FROM students
ORDER BY score DESC
LIMIT 3 OFFSET 0;

OFFSET是可选的，如果只写LIMIT 15，那么相当于LIMIT 15 OFFSET 0。
在MySQL中，LIMIT 15 OFFSET 30还可以简写成LIMIT 30, 15。
使用LIMIT OFFSET 分页时，随着N越来越大，查询效率也会越来越低。

六、聚合查询，查询表的总数

SELECT COUNT(*) FROM students;
SELECT COUNT(*) num FROM students;  //num=6
SELECT COUNT(*) boys FROM students WHERE gender = 'M'; //boys=5

-- 使用聚合查询计算男生平均成绩:
SELECT AVG(score) average FROM students WHERE gender = 'M';

多表查询

-- 按class_id分组:
SELECT COUNT() num FROM students GROUP BY class_id;
-- 按class_id分组:
SELECT class_id, COUNT() num FROM students GROUP BY class_id;