Basic SQL

数据库语言可分为两个部分，DDL和DML。

DDL（Data Definitoin Language）：描述数据库中要存储的现实世界实体（操作表），操作数据库的结构等
DML（Data Manipulation Language）：操作数据库存储的对象或记录
DQL（Data Query Language）：查询数据库中记录

SQL DDL:数据定义

数据库命令

查询数据库

--显示当前mysql服务器中所有数据库
SHOW DATABASES;
--显示创建某一数据库时的语句
SHOW CREATE DATABASE 数据库名称;

创建新数据库

--字符集为utf-8(默认就是)，校验规则utf8_general_ci(不区分大小写，默认就是)
--创建数据库名称是使用反引号规避关键字
--数据库的字符集和校对规则会成为该数据库下表的默认规则
--utf8_bin区分大小写，大小写指的是数据库中记录的字符大小写 
CREATE DATABASE 数据库名称 CHARACTER SET 字符集 COLLATE 校验规则;

删除数据库
1
DROP DATABASE 数据库名称;
备份和恢复数据库
- 备份数据库即将数据库导出至其他文件，恢复数据库即从备份文件恢复至某一DBMS，备份与恢复操作不是sql指令需要在shell执行，mysql提供的程序在mysql目录中的bin文件夹下，备份其实就是备份数据库生命周期内从头到尾对应的sql语句。
- 备份：./mysqldump -u 用户名 -p -B 数据库1 数据库2 ... > 备份文件的路径
- 恢复：直接用dbms终端打开备份sql文件并执行或者打开mysql命令行执行：source 备份文件路径

表命令

查询表

--显示当前数据库中全部表
SHOW TABLES;
--显示某一张表的信息
DESC 表名

创建新表

   create table 表名
   (
字段名称1 数据类型(容量) 约束，
字段名称2 数据类型(容量) 约束，
字段名称3 数据类型(容量) 约束,
...    
   )CHARACTER SET 字符集 COLLATE 校验规则 ENGINE 引擎;

删除表
1
DROP TABLE 表名称;

修改表

# 添加列
ALTER TABLE 表名
ADD (
字段名 数据类型(容量) 约束,
字段名 数据类型(容量) 约束,
...
);

#修改列
ALTER TABLE 表名
MODIFY 字段名 数据类型(容量) 约束;

#修改列名
ALTER TABLE 表名
CHANGE 旧字段名 新字段名 数据类型(容量) 约束;

#删除列
ALTER TABLE 表名
DROP 字段名;

# 修改表名
RENAME TABLE 旧表名 TO 新表名;

备份及恢复表
- 同样使用mysqldump,使用命令：
  mysqldump -u 用户名 -p密码数据库名表1 表2... > 备份文件路径
- 恢复表同恢复数据库的操作，要注意切换到目标数据库之后再执行备份语句

SQL DML:数据操作

插入

-- 向表中插入一条或多条记录
INSERT INTO <表名> 
    (字段1, 字段2, ...) 
    VALUES (值1, 值2, ...)
           (值1, 值2, ...)
           (值1, 值2, ...)
        ...;

修改

-- 修改表中某一条记录
UPDATE <表名> SET 字段1=值1, 字段2=值2, ... WHERE ...;

-- 在UPDATE语句中，更新字段时可以使用表达式。例如，把所有80分以下的同学的成绩加10分：
UPDATE <表名> SET score=score+10 WHERE score<=80;
-- 如果WHERE条件没有匹配到任何记录，UPDATE语句不会报错，也不会有任何记录被更新
-- UPDATE语句不指定WHERE条件时，会作用于表中所有记录
UPDATE <表名> SET 字段1=值1, 字段2=值2...;

删除

-- 删除表中指定条件的记录
DELETE FROM <表名> WHERE ...;

-- 当DELETE语句没有找到匹配的where不会报错，也不会删除任何记录
-- 不指定where时，DELETE会删除表中所有记录
DELETE FROM <表名>;
-- 在使用DELETE之前，最好先使用SELECT查看WHERE是否选中了期望的记录集，再执行DELETE

SQL DQL:数据查询

基本查询

-- 最普通查询语句
SELECT * FROM <表名>;
-- SELECT 语句也可以不带FROM语句，一般用于检查数据库连接状态
-- 或是用于计算
SELECT 1;
SELECT 1+1;
-- 带条件的投影查询
SELECT 字段1,字段2 ... FROM <表名> WHERE 条件;

-- 使用SELECT进行查询时，查询结果默认按主键排序
-- 通过ORDER BY子句指定排序依据的字段，DESC表示倒叙
-- 当字段x相同时，再按字段y排序
SELECT 字段1,字段2 ... FROM <表名> WHERE 条件 ORDER BY 字段x DESC, 字段y;

分页查询

-- 使用SELECT查询时，如果结果集数据量很大，放在一个页面显示的话数据量太大，此时应考虑分页显示
-- M表示一页最多M条记录，N表示跳过前N条记录
SELECT 字段1,字段2 ...
FROM <表名>
LIMIT M OFFSET N;

-- 注意，查询结果只会显示一页，所以实际上时用OFFSET控制显示第几页

聚合查询

-- 聚合查询即使用MySQL提供的聚合函数进行查询
SELECT 聚合函数(字段) 别名 FROM <表名> WHERE 条件;

-- 分组聚合使用GROUP BY
-- 按多个字段分组
SELECT 聚合函数(字段) 别名 FROM <表名> GROUP BY 字段x,字段y...;

多表查询

-- SELECT可以同时从多张表查询数据，最后的查询结果是多表的笛卡尔积
-- 多个表中有字段名相同的情况，可以使用 "." 运算符明确字段
SELECT 表1.字段1,表2.字段2 ... FROM 表1,表2 ... WHERE 条件;

-- 不指定条件的表1和表2多表查询最终得到的记录条数为M x N
-- 其中M为表1记录条数，N为表2记录条数

连接查询

-- 连接查询的查询结果是多表查询的子集，可以看作特殊的多表查询

-- 内连接，查询结果是符合连接条件，且在两表中均有的条件字段值的记录集
SELECT 别名1.字段1,别名2.字段1 ...
FROM 表1 别名1
INNER JOIN 表2 别名2
ON 连接条件（等指连接，非等值连接）
WHERE 查询条件;

-- 外连接，以连接的表为基准，查询的记录要包含某一表或所有表的所有记录
-- 分为左外连接，右外连接和全外连接
SELECT 别名1.字段1,别名2.字段1 ...
FROM 表1 别名1
LEFT OUTER JOIN 表2 别名2
ON 连接条件（等指连接，非等值连接）
WHERE 查询条件;

UNION操作

-- UNION 操作符用于合并两个或多个 SELECT 语句的结果集。
-- UNION 内部的每个 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每个 SELECT 语句中的列的顺序必须相同。

-- UNION默认最终保留所有不重复的记录
SELECT 字段1,字段2 ... FROM 表1
UNION
SELECT 字段1,字段2 ... FROM 表2;

-- 若要保留所有重复记录使用UNION ALL
SELECT 字段1,字段2 ... FROM 表1
UNION
SELECT 字段1,字段2 ... FROM 表2;

HAVING子句

-- HAVING子句可以为分组或聚合指定过滤条件
SELECT 
    字段1,
    聚合函数1,
    聚合函数2
    ...
FROM
    表
GROUP BY 分组条件
HAVING 筛选条件;
-- HAVING子句将过滤条件应用于每组，而WHERE子句将过滤条件应用于每个单独的行。
-- 如果省略GROUP BY子句，则HAVING子句的行为与WHERE子句类似。

MySQL中的三值逻辑
- 在关系型数据库中，由于NULL值的存在，导致逻辑表达式存在三种值：TRUE/FALSE/UNKNOWN。
- 在筛选操作中（WHERE-查询、ON-连接、HAVING-分组/聚合）UNKNOWN当作FALSE，即只有TRUE才会被选中
- 任何值和NULL做比较运算都返回UNKNOWN（包括NULL自身）
- 判断是否为NULL时想返回TRUE或FALSE使用IS
- 安全等于<=>也可以在比较普通类型值和NULL时返回TRUE或FALSE