3.1 SQL 概述

结构化查询语言（Structured Query Language, SQL）是关系数据库的标准语言，其功能不仅仅是查询，而是包括数据库模式创建、数据库数据的插入与修改、数据库安全性完整性定义与控制等一系列功能。

有一些计算可用通用的程序设计语言来表达，但无法通过SQL来表达；SQL还不支持诸如从用户那输入、输出到显示器，或者通过网络通信这样的动作。这样的计算和动作必须用一种宿主语言来写，比如C、C++或Java，在其中使用嵌入式的SQL查询来访问数据库中的数据。

3.1.1 SQL语句分类

  1. DDL（Data Definition Language，数据定义语言）用来定义数据库对象：数据库，表，列等。关键字：CREATE，DROP ，ALTER 等
  2. DML（Data Manipulation Language，数据操纵语言）用来对数据库中表的数据进行增删改。关键字：INSERT，DELETE，UPDATE 等
  3. DQL（Data Query Language，数据查询语言）用来查询数据库中表的记录。关键字：SELECT，WHERE 等
  4. DCL（Data Control Language，数据控制语言）用来定义数据库的访问权限和安全级别，及创建用户。关键字：GRANT，REVOKE等

目前，没有一个数据库系统能够支持SQL标准的所有概念和特性。各个关系数据库管理系统产品在实现标准SQL时各有差别，本文主要围绕MySQL相关的语句。

3.1.2 SQL通用语法

  1. SQL语句以分号结尾，故可单行或多行书写，并使用空格和缩进来增强语句可读性；

  2. MySQL数据库的SQL语句不区分大小写，关键字建议使用大写。

  3. 几种注释：

    • 单行注释：-- 注释内容 或 # 注释内容（MySQL特有）

    • 多行注释：/* 注释内容 */

3.2 DDL：操作数据库、表

3.2.1 操作数据库

一、C（Create）：创建数据库

  • 创建数据库：

    1
    CREATE DATABASE 数据库名称;

  • 判断某个数据库是否存在，若不存在则创建该数据库：

    1
    CREATE DATABASE IF NOT EXISTS 数据库名称; /* 注意's' */

  • 创建数据库的同时指定字符集：

    1
    CREATE DATABASE 数据库名称 CHARACTER SET 字符集名称;

    综合使用：

    1
    CREATE DATABASE IF NOT EXISTS db_1 CHARACTER SET utf8;

二、R（Retrieve）：查询数据库

  • 查询所有数据库的名称

    1
    SHOW DATABASES; /* 注意databases是复数形式的! */

  • 查询某个数据库的字符集：查询某个数据库的创建语句

    1
    SHOW CREATE DATABASE 数据库名称;

三、U（Update）：修改

  • 修改数据库的字符集

    1
    ALTER DATABASE 数据库名称 CHARACTER SET 字符集名称;

四、D（Delete）：删除

  • 删除数据库

    1
    DROP DATABASE 数据库名称;

  • 判断数据库存在，存在再删除

    1
    DROP DATABASE IF EXISTS 数据库名称;

五、使用数据库

  • 查询当前正在使用的数据库名称

    1
    SELECT DATABASE();

  • 使用数据库

    1
    USE 数据库名称;

3.2.2 MySQL数据类型

一、数值类型

类型大小( bytes)范围（有符号下）用途
TINYINT1 byte(−128，+127)(-128，+127)小整数值
SMALLINT2 bytes(−32768，+32767)(-32 768，+32 767)大整数值
MEDIUMINT3 bytes(−8.3E+6，+8.3E+6)(-8.3E+6，+8.3E+6 )大整数值
INT或INTEGER4 bytes(−2E+9，+2E+9)(-2E+9，+2E+9)大整数值
BIGINT8 bytes(−9E+18，+9E+18)(-9E+18，+9E+18)极大整数值
FLOAT4 bytes(−3E+38，−1.2E−38)，0，(+1.2E−38，+3.4E+38)(-3E+38，-1.2E-38)，0，(+1.2E-38，+3.4E+38)单精度 浮点数值
DOUBLE8 bytes(−1.8E+308，−2.2E−308)，0，(+2.2E−308，+1.8E+308)(-1.8E+308，-2.2E-308)，0，(+2.2E-308，+1.8E+308)双精度 浮点数值
DECIMAL对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2依赖于M和D的值小数值

若在float(7, 4)列插入 999.00009，则存进的结果为 999.0001（总共7位，保留4位小数）

二、日期和时间类型

类型大小 ( bytes)格式用途
DATE3YYYY-MM-DD日期值
TIME3HH:MM:SS时间值或持续时间
YEAR1YYYY年份值
DATETIME8YYYY-MM-DD HH:MM:SS混合日期和时间值
TIMESTAMP4YYYY-MM-DD HH:MM:SS混合日期和时间值，时间戳，若不给这个字段赋值，则会使用当前的系统时间

MySQL不会给TIMESTAMP设置默认值，除非显式设置DEFAULT约束，比如默认值可设为 NOW() 或 CURRENT_TIMESTAMP()，又或者显示设置可空 NULL。

❗︎ 除了数值类型，其他类型一定一定要记得用引号引起来。

三、字符串类型

对于MySQL 5.0 以上的版本，一个汉字占多少长度与编码有关：

  • UTF-8：一个汉字占3个字节；
  • GBK：一个汉字占2个字节；
类型大小用途
CHAR0-255 bytes定长字符串，若实际长度不够会用空格填充，但查询时返回的数据尾部无空格
VARCHAR0-65535 bytes变长字符串，而 VARCHAR(n) 表示的是 n 个字符（无论汉字、英文），只是实际字节长度有所区别
TINYBLOB0-255 bytes不超过 255 个字符的二进制字符串
TINYTEXT0-255 bytes短文本字符串
BLOB0-65 535 bytes二进制形式的长文本数据
TEXT0-65 535 bytes长文本数据
…

3.2.3 操作表：CRUD

一、C（Create）：创建表

  • 直接创建

    1
    2
    3
    4
    5
    CREATE TABLE 表名(
    	列名1 数据类型1,
        列名2 数据类型2,
        ...
    );

    举例使用：

    1
    2
    3
    4
    5
    6
    7
    CREATE TABLE `timestampTest` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(20) DEFAULT NULL,
      `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, /* 创建时间 */
      `last_modify_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, /* 最后更新时间 */
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

  • 复制表：

    1
    CREATE TABLE 表名 like 被复制的表名;

二、R（Retrieve）：查询表

  • 查询某个数据库中所有表的名称

    1
    SHOW TABLES; /* 注意tables是复数形式*/

  • 查询表的结构（查看属性名 即列名）

    1
    DESC 表名;

三、U（Update）：修改表

  • 修改表名

    1
    ALTER TABLE 表名 RENAME TO 新的表名;

  • 修改表的字符集

    1
    ALTER TABLE 表名 CHARACTER SET 字符集名称;

  • 添加一列

    1
    ALTER TABLE 表名 ADD 列名 数据类型;

  • 修改列名称、类型

    1
    2
    ALTER TABLE 表名 CHANGE 原列名 新列名 新数据类型;
    ALTER TABLE 表名 MODIFY 列名 新数据类型;

  • 删除列

    1
    ALTER TABLE 表名 DROP 列名;

四、D（Delete）：删除表

  • 直接删除

    1
    DROP TABLE 表名;

  • 判断该表存在，存在再删除

    1
    DROP TABLE IF EXISTS 表名;

3.3 DML：增删改表中的数据

3.3.1 添加数据

1
INSERT INTO 表名(列名1, 列名2, ...) VALUES(值1, 值2, ...);

注意：

  1. 列名要与值一一对应，若表名后不加列名，则默认给所有值添加值。
  2. 除数字类型，其他类型需使用（单/双）引号引起来。
  3. 插入的数据大小应在列的规定范围内（如，不能将长度为80的字符串加入到长度为40的列）

3.3.2 复制数据

从 表1中的特定列的数据 复制到 表2中的特定列 中 。

1
INSERT INTO 表名2(列名1, 列名2) SELECT 列名1, 列名 FROM 表名1;

举例使用：

表 STU2 初始时记录信息表 STU3 初始时记录信息

执行下列语句后：

1
2
INSERT INTO STU2(name) SELECT house FROM STU3; 
/* 也就说将表STU3的house列的所有数据，插入到表STU2的name列后面 */

3.3.3 删除数据

1
DELETE FROM 表名 WHERE 条件;

注意：

  1. 若不加条件，则会删除表中所有记录。

  2. 若要删除所有记录，

    1
    2
    DELETE FROM 表名; /* 不推荐使用 */
    TRUNCATE TABLE 表名; /* 推荐使用，它实际上是先删除表，然后再创建一个同名的表 */

3.3.4 修改数据

1
UPDATE 表名 SET 列名1 = 值1, 列名2 = 值2, .... WHERE 条件; /* 若不加条件，则会将表中所有记录全部修改 */

3.4 DCL：管理、授权用户

默认情况下，使用的是 root 用户，超级管理员，拥有全部的权限。在特定情况下，我们可根据不同的项目建立不同的用户，分配不同的权限来管理和维护数据库。

3.4.1 创建用户

1
CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';

用户创建之后，默认没有什么权限，需使用命令进行授权。

  • 主机名，指定该新用户在哪个主机上可以登录，若是本地用户则可用 localhost，若希望该用户可从任意远程主机登录，则使用通配符 % 。

    1
    CREATE USER 'user1'@'%' IDENTIFIED BY '123456';

  • 密码允许为空，此时该用户无需密码即可登录服务器。

3.4.2 删除用户

1
DROP USER '用户名'@'主机名';

3.4.3 查询用户

  1. 切换到 mysql 数据库

    1
    USE mysql;

  2. 查询 user 表

    1
    SELECT * FROM USER;

3.4.4 root用户修改普通用户密码

如果这位 需要修改密码 的用户，是刚刚创建的，没有权限的话，需为该用户 授予权限。否则，无法成功修改密码，会报错显示，无法找到该用户。

1
SET PASSWORD FOR '用户名'@'主机名' = PASSWORD('新密码');

若要为 超级管理员 root 用户修改密码，请参阅我的这篇博文：「MySQL8.0」ERROR 1045 (28000):Access denied for user ‘ODBC’@'localhost’解决方案

3.4.5 为用户授权

1
GRANT 权限1, 权限2, ... ON 数据库名称.表名 TO '用户名'@'主机名';

授予用户的权限，可填 CREATE、ALTER、SELECT、INSERT、UPDATE 等，若要授权所有权限，则可使用 ALL 。举例如下：

1
2
3
4
GRANT CREATE,ALTER,UPDATE ON db1.* TO 'user1'@'localhost';

/* 给user2用户对所有数据库的所有表分配所有权限 */
GRANT ALL ON *.* TO 'user2'@'%';

3.4.6 撤销授权

1
REVOKE 权限1, 权限2, ... ON 数据库名称.表名 FROM '用户名'@'主机名';

与3.4.5 用户授权同理。