SQL：Structured Query Language

速查语法

查询

实用语句：https://www.liaoxuefeng.com/wiki/1177760294764384/1246617682185952

常规查询

• SELECT 列1 别名1, 列2 别名2, 列3 别名3 FROM students（表） WHERE(后跟条件） score >= 80
  可以加上DESC表示“倒序”：

SELECT id, name, gender, score
FROM students
WHERE class_id = 1
ORDER BY score DESC;
LIMIT <N-M> OFFSET <M>

条件运算：按照NOT、AND、OR的优先级进行，即NOT优先级最高，其次是AND，最后是OR。加上括号可以改变优先级。

分页查询：确定每页需要显示的结果数量pageSize（这里是3），然后根据当前页的索引pageIndex（从1开始），确定LIMIT和OFFSET应该设定的值： LIMIT总是设定为pageSize；OFFSET计算公式为pageSize * (pageIndex - 1)。

条件表达式（字符串需要使用单引号引起来）：
=：判断相等
>：判断大于
>=：判断大于或相等
<：判断小于
<=：判断小于或相等
<>：判断不相等
LIKE：判断相似
BETWEEN XX AND XX：数值范围
IN (60, 90)：表示在括号内的值，此处只有60和90

聚合查询

• COUNT(*)
• SUM()
• AVG()
• MAX()
• MIN()

查询数量
SELECT COUNT(*) FROM students;
其他方法
SELECT AVG(score) average FROM students WHERE gender = 'M';

分组查询

• GROUP BY

SELECT class_id, gender, COUNT(*) num FROM students GROUP BY class_id, gender;

多表查询

乘积，中间使用逗号分开

• 慎用，会生成一个很大的表格，100*100=10000
• 查询的时候不能直接使用别名，可用c.id，不能用cid
• 使用别名不是必须的，但可以更好地简化查询语句

SELECT
    s.id sid,
    s.name,
    s.gender,
    s.score,
    c.id cid,
    c.name cname
FROM students s, classes c
WHERE c.id = 1;

连接查询

• INNER JOIN：只返回同时存在于两张表的行数据
• RIGHT OUTER JOIN：返回右表都存在的行
• LEFT OUTER JOIN：返回左表都存在的行
• FULL OUTER JOIN：把两张表的所有记录全部选择出来
  先确定主表，仍然使用FROM <表1>的语法；
  再确定需要连接的表，使用INNER JOIN <表2>的语法；
  然后确定连接条件，使用ON <条件...>，这里的条件是s.class_id = c.id，表示students表的class_id列与classes表的id列相同的行需要连接；
  可选：加上WHERE子句、ORDER BY等子句。

SELECT s.id, s.name, s.class_id, c.name class_name, s.gender, s.score
FROM students s
INNER JOIN classes c
ON s.class_id = c.id;

增、删、改

• INSERT：插入新记录；
  INSERT INTO <表名> (字段1, 字段2, ...) VALUES (值1, 值2, ...);
• UPDATE：更新已有记录；
  UPDATE <表名> SET 字段1=值1, 字段2=值2, ... WHERE ...;
• DELETE：删除已有记录。
  DELETE FROM <表名> WHERE ...;
• 没有选中不会报错（mysql会有提示），如果没有加WHERE会更新整表，高风险操作

INSERT INTO students (class_id, name, gender, score) VALUES
  (1, '大宝', 'M', 87),
  (2, '二宝', 'M', 81);
UPDATE students SET name='小牛', score=77 WHERE id>=5 AND id<=7;
DELETE FROM students WHERE id>=5 AND id<=7;

MySQL

登录

• mysql -u root -p：
• SHOW DATABASES：显示数据库
  nformation_schema、mysql、performance_schema和sys是系统库，不要去改动它们。
• CREATE DATABASE test;
• DROP DATABASE test;
• USE test;
• SHOW TABLES;
• DESC students; 查看表结构
• SHOW CREATE TABLE students;查看创建表的SQL语句
• DROP TABLE students;
• ALTER TABLE students ADD COLUMN birth VARCHAR(10) NOT NULL;新增一列
• ALTER TABLE students CHANGE COLUMN birth birthday VARCHAR(20) NOT NULL;要修改birth列，例如把列名改为birthday，类型改为VARCHAR(20)：
• ALTER TABLE students DROP COLUMN birthday;
• exit

其他

• 不带from可以直接进行运算，一般会通过SELECT 1;来判断是否正常连接到数据库
• 通常情况下，字段应该避免允许为NULL。不允许为NULL可以简化查询条件，加快查询速度，也利于应用程序读取数据后无需判断是否为NULL。
• 身份证号、手机号、邮箱地址这些看上去可以唯一的字段，均不可用作主键。作为主键最好是完全业务无关的字段，我们一般把这个字段命名为id，可以使用自增整数类型或全局唯一GUID类型（一般程序可生成）

数据类型

名称	类型	说明
INT	整型	4字节整数类型，范围约+/-21亿
BIGINT	长整型	8字节整数类型，范围约+/-922亿亿
REAL	浮点型	4字节浮点数，范围约+/-1038
DOUBLE	浮点型	8字节浮点数，范围约+/-10308
DECIMAL(M,N)	高精度小数	由用户指定精度的小数，例如，DECIMAL(20,10)表示一共20位，其中小数10位，通常用于财务计算
CHAR(N)	定长字符串	存储指定长度的字符串，例如，CHAR(100)总是存储100个字符的字符串
VARCHAR(N)	变长字符串	存储可变长度的字符串，例如，VARCHAR(100)可以存储0~100个字符的字符串
BOOLEAN	布尔类型	存储True或者False
DATE	日期类型	存储日期，例如，2018-06-22
TIME	时间类型	存储时间，例如，12:20:59
DATETIME	日期和时间类型	存储日期+时间，例如，2018-06-22 12:20:59

键与索引

外键：通过定义外键约束实现，会降低性能，一般通过程序保证正确，不设置外键约束

添加：
ALTER TABLE students
ADD CONSTRAINT fk_class_id
FOREIGN KEY (class_id)
REFERENCES classes (id);
删除：（并没有删除外键列）
ALTER TABLE students
DROP FOREIGN KEY fk_class_id;

索引：索引是关系数据库中对某一列或多个列的值进行预排序的数据结构。通过使用索引，可以让数据库系统不必扫描整个表，而是直接定位到符合条件的记录，这样就大大加快了查询速度。
索引的效率取决于索引列的值是否散列，即该列的值如果越互不相同，那么索引效率越高。可以对一张表创建多个索引。索引的优点是提高了查询效率，缺点是在插入、更新和删除记录时，需要同时修改索引，因此，索引越多，插入、更新和删除记录的速度就越慢。

创建索引：
ALTER TABLE students
ADD INDEX idx_score (name，score);
创建唯一索引：
ALTER TABLE students
ADD UNIQUE INDEX uni_name (name);
创建唯一约束不创建索引：
ALTER TABLE students
ADD CONSTRAINT uni_name UNIQUE (name);

事务

在执行SQL语句的时候，某些业务要求，一系列操作必须全部执行，而不能仅执行一部分。

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
主动让事务失败：
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
ROLLBACK;

搬运自

廖雪峰的官方网站：https://www.liaoxuefeng.com/