学习 MySQL（一） - Githubissues

第 1 章初涉 MySQL

MySQL 概述
- MySQL 是由瑞典 MySQL AB 公司开发的，目前被 Oracle 公司收购了
- MySQL 是一款开源的关系型数据库管理系统
- MySQL 分为社区版和企业版
MySQL 的安装与配置
- Mac OS 使用 HomeBrew 安装：brew install mysql
- MySQL 目录结构：
  - bin - 存储可执行文件
  - lib - 存储库文件
  - share - 存储错误消息和字符集文件
  - scripts - 存储脚本文件
  - include - 存储包含的头文件
  - mysql-test
  - support-files
启动与停止 MySQL 服务
- 启动 MySQL 服务：launchctl load -w ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist"
- 停止 MySQL 服务：launchctl unload -w ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist"
<a id="1.4>登录与退出
- 登录
  - mysql 命令
    - -D, --database=[name] : 指定数据库
    - -P, --port=[number] ：指定端口号
    - -u, --user=[name] ：指定登录用户
    - -h, --host=[name] ：指定服务器
    - -p, --password ：密码
    - -V, --version ：查看版本
    - --delimiter=[name] ：指定分隔符
    - --prompt=[name] ：设置提示符
  - mysql 命令例子
    - mysql -uroot -p
    - mysql --version
    - mysql -uroot -P6606 -p
- 退出
  - mysql CLI 环境中输入
    - exit;
    - quit:
    - \q;
修改 MySQL 提示符
- 连接客户端时通过参数指定：shell>mysql -uroot -proot --prompt 提示符
- 连接上客户端后，通过设置 prompt 提示符指定：prompt 提示符
- prompt 提示符参数：
  - \D 完整的日期
  - \d 当前数据库
  - \h 服务器名称
  - \u 当前用户
- prompt 提示符参数用法：prompt \h-\u-\p-\D
MySQL 常用命令以及语法规范
- 常用命令：
  - 查看数据库版本 - SELECT VERSION();
  - 查看数据库当前时间 - SELECT NEW();
  - 查看数据库当前用户 - SELECT USER();
  - 查看数据库列表 - SHOW DATABASES;
  - 查看警告日志 - SHOW WARINGS;
- 语法规范：
  - 关键字与函数名称全部大写
  - 数据库名称、表名称、字段名称全部小写
  - SQL 语句必须以分号结尾
操作数据库
- 创建数据库 - CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name [DEFAULT] CHARACTER SET [=] charset_name;
- 修改数据库 - ALTER {DATABASE | SCHEMA} [db_name] [DEFAULT] CHARACTER SET [=] charset_name;
- 删除数据库 - DROP {DATABASE | SCHEMA} [IF EXISTS] db_name

第 2 章数据类型与操作数据表

数据类型之整型
- 数据类型的概念：数据类型是指列、存储过程参数、表达式和局部变量的数据特征，它决定了数据的存储格式，代表了不同的信息类型。
- 整数类型的长度：为整数类型指定长度，例如：INT(11) 对大多数应用是没有意义的，它不会限制值的合法范围，只会影响显示字符的个数,声明 zerofill 后，会自动在数值填充 0 以达到指定长度。
- 整型类型：
  - TINYINT
    - 有符号值：-128 ~ 127 （- 2）
    - 无符号值：0 ~ 255
    - 字节：1
  - SMALLINT
    - 有符号值：-32768 ~ 32767
    - 无符号值：0 ~ 65535
    - 字节：2
  - MEDIUMINT
    - 有符号值：-8388608 ~ 8388607
    - 无符号值：0 ~ 16777215
    - 字节：3
  - INT
    - 有符号值：-2147483648 ~ 2147483647
    - 无符号值：0 ~ 4294967295
    - 字节：4
  - BIGINT
    - 有符号值：-9223372036854775808 ~ 9223372036854775807
    - 无符号值：0 ~ 18446744073709551615
    - 字节：8
数据类型之浮点型
- FLOAT [(M,D)]：M 是数字总位数，D 是小数点后面的位数。如果 M 和 D 被省略，根据硬件允许的限制来保存值。单精度浮点数精确到大约 7 位小数位。
- DOUBLE [(M,D)]
- DECIMAL [(M,D)]：可存储比 BIGINT 更大的整数，也可以用于存储精确的小数。
数据类型之日期时间型
- YEAR
- TIME
- DATE
- DATETIME
- TIMESTAMP
- 日期时间型注意项
  - 尽量使用 TIMESTAMP，比 DATETIME 空间效率高
  - 用整数保存时间戳的格式通常不方便处理
  - 如果需要存储微秒，可以使用 BIGINT 存储
数据类型之字符型
- CHAR：M 个字节，0 <= M <= 255
  - 用于储存定长字符串，根据定义的字符串长度分配足够的空间。
  - 根据需要采用空格进行填充以方便比较。
  - 适合存储很短的字符串，或者所有值都接近同一个长度。
  - 如果存储内容超出指定长度，会被截断。
- VARCHAR：L + 1 个字节，其中 L <= M 且 0 <= M <= 65535
  - 用于储存可变长字符串
  - 使用 1 或 2 个额外字节记录字符串的长度，列长度小于 255 字节，使用 1 个字节表示，否则用 2 个字节表示。
  - 如果存储内容超出指定长度，会被截断。
- TINYTEXT：L + 1 个字节，其中 L < 2^8
- TEXT：L + 2 个字节，其中 L < 2^16
- MEDIUMTEXT：L + 3 个字节，其中 L < 2^24
- LONGTEXT：L + 4 个字节，其中 L < 2^32
- ENUM('value1','value2',...)：1 或 2 个字节，取决于枚举值的个数（最多 65535 个值）
- SET('value1','value2',...)：1、2、3、4 或者 8 个字节，取决于 set 成员的数目（最多64个）
- 字符型注意项
  - 对于经常变更的数据，CHAR 比 VARCHAR 更好，CHAR 不容易产生碎片。
  - 对于非常短的列，CHAR 比 VARCHAR 在存储空间上更有效率。
  - 只分配真正需要的空间，更长的列会消耗更多的内存。
  - 尽量避免使用 BLOB/TEXT 类型，查询会使用临时表，导致严重的性能开销。
创建数据表
- 数据表概念：数据表是数据库最重要的组成部分一直，是其他对象的基础。
- 选择数据库 - use {database}
- 创建数据表 - CREATE TABLE [IF NOT EXISTS] table_name(column_name data_type,....)
查看数据表
- SHOW TABLES [FROM db_name] [LIKE 'pattern'|WHERE expr]
查看数据表结构
- SHOW COLUMNS FROM tbl_name
记录的插入与查找
- 插入：INSERT [INTO] tal_name[(col_name)VALUES(val,..)]
- 插入省略字段需要为所有字段都要赋值，否则数据库报错
- 查找：SELECT expr,... FROM tbl_name
空值与非空
- NULL，字段值可以为空
- NOT NULL，字段值禁止为空
自动编号
- AUTO_INCREMENT，自动编号，且必须与主键组合使用
- 默认情况下，起始值为 1，每次的增量为 1
初涉主键约束
- PRIMARY KEY
  - 主键约束
  - 主键自动为 NOT NULL
  - 主键保证记录的唯一性
  - 每张数据表只能存在一个主键
  - AUTO_INCREMENT 必须和 PRIMARY KEY 一起使用，PRIMARY KEY 不必须和 AUTO_INCREMENT 一起使用
初涉唯一约束
- UNIQUE KEY
  - 唯一约束
  - 唯约束可以保证记录的唯一性
  - 唯一约束的字段可以为空值（NULL）
  - 每张数据表可以存在多个唯一索引
初涉默认约束
- DEFAULT
  - 默认值
  - 当插入记录时，如果没有明确为字段赋值，则自动赋予默认值

第 3 章约束以及修改数据表

外键约束的要求解析
- 约束
  - 约束保证数据的完整性和一致性
  - 约束分为表级约束和列级约束
  - 约束类型：
    - DEFAULT（默认约束）
    - NOT NULL（非空约束）
    - UNIQUE KEY（唯一约束）
    - PRIMARY KEY（主键约束）
    - FOREIGN KEY（外键约束）
  - FOREIGN KEY - 外键约束
    - 保持数据一致性，完整性
    - 实现一对一或一对多关系
  - 使用外键约束的要求
    - 父表和子表必须使用相同的存储引擎，而且禁止使用临时表
    - 数据表的存储引擎只能为 InnoDB
    - 外键列和参照列必须具有相似的数据类型。其中数字的长度或是是否有符号位必须相同；而字符的长度则可以不同
    - 外键列和参照列必须创建索引，如果参照列不存在索引的话，MySQL 将自动创建索引
  - 编辑数据表的默认存储引擎：default-storage-engine=INNODB
外键约束的参照操作
- CASCADE：从父表删除或更新且自动删除或更新子表中匹配的行
- SET NULL：从父表删除或更新行，并设置子表中的外键列为 NULL。如果使用该选项，必须保证子表列没有指定 NOT NULL
- RESTRICT：拒绝对父表的删除或更新操作
- NO ACTION：标准 SQL 的关键字，在 MySQL 中与 RESTRICE 相同
表级约束与列级约束
- 对一个数据列建立的约束，称为列级约束
- 对多个数据列建立的约束，称为表级约束
- 列级约束既可以在列定义时声明，也可以在列定义后声明
- 表级约束只能在列定义后声明
修改数据表-添加/删除列
- 添加单列：ALTER TABLE tbl_name ADD [COLUMN] col_name column_definition [FIRST | AFTER col_name]
- 添加多列：ALTER TABLE tbl_name ADD [COLUMN] (col_name column_definition,...)
- 删除单列：ALTER TABLE tbl_name DROP [COLUMN] col_name
修改数据表--添加约束
- 添加主键约束：ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] PRIMARY KEY [index_type](index_col_name,...)
- 添加唯一约束：ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY][index_name][index_type](index_col_name,...)
- 添加外键约束：ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] FOREIGN KEY [index_name](index_col_name,...) reference_definition
- 添加/删除默认约束：ALTER TABLE tbl_name ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
修改数据表--删除约束
- 删除主键约束：ALTER TABLE tbl_name DROP PRIMARY KEY
- 删除唯一约束：ALTER TABLE tbl_name DROP {INDEX | KEY} index_name
- 删除外键约束：ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol
修改数据表--修改列定义和更名数据表
- 修改表名称
  - ALTER TABLE tbl_name RENAME [TO|AS] new_tbl_name
  - RENAME TABLE tbl_name TO new_tbl_name [,tbl_name2 To ewn_tbl_name2,...]
- 修改列定义：ALTER TABLE tbl_name MODIFY [COLUMN] col_name column_definition [FIRST | AFTER col_name]
- 修改列名称：ALTER TABLE tbl_name CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST|AFTER col_name]
小结
- 约束
  - 功能
    - NOT NULL（非空约束）
    - PRIMARY KEY（主键约束）
    - UNIQUE KEY（唯一约束）
    - DEFAULT（默认约束）
    - FOREIGN KEY（外键约束）
  - 数据列的数目
    - 表级约束
    - 列级约束
- 修改数据表
  - 针对字段的操作：添加 / 删除字段、修改列定义，修改列名称等
  - 针对约束的操作：添加 / 删除各种约束
  - 针对数据表的操作：数据表更名（两种方式）

第 4 章操作数据表中的记录

插入记录 INSERT
- INSERT
  - INSERT [INFO] tbl_name [(col_name,...)] {VALUES | VALUE}({expr|DEFAULT},...),(...)
插入记录 INSERT SET-SELECT
- INSERT SET 与 INSERT 的区别在于，此方法可以使用子查询（SubQuery）
  - INSERT [INTO] tbl_name SET col_name={expr | DEFAULT},...
- INSERT SELECT 与 INSERT 的区别在于，此方法可以将查询结果插入到指定数据表
  - INSERT [INTO] tbl_name [(col_name,...)] SELECT ...
单表更新记录 UPDATE
- UPDATE
  - UPDATE [LOW_PRIORITY] [IGNORE] table_reference SET col_name1={expr | DEFAULT}[,col_name2={expr2|DEFAULT}]...[WHERE where_condition]
单表删除记录 DELETE
- DELETE
  - DELETE FROM tbl_name [WHERE where_condition]
查询表达式解析
- SELECT
  - SELECT select_expr[,select_expr ...]
  - [
    - FROM table_references
    - [WHERE where_condition]
    - [GROUP BY {col_name | position} [ASC | DESC], ...]
    - [HAVING where_condition]
    - [ORDER BY {col_name | expr | position} [ASC | DESC],...]
    - [LIMIT {[offset,] row_count | row_count OFFSET offset}]
  - ]
- select_expr - 查询表达式
  - 每一个表达式表示想要的一列，必须有至少一个。
  - 多个列之间以英文逗号分隔
  - 星号（）表示所有列。tbl_name. 可以表示命名表的所有列
  - 查询表达式可以使用 [ AS ] alias_name 为其赋予别名
  - 别名可用于 GROUP BY，ORDER BY 或 HAVING 子句
WHERE 语句进行条件查询
- WHERE - 条件表达式
  - 对记录进行过滤，如果没有指定 WHERE 子句，则显示所有记录
  - 在 WHERE 表达式中，可以使用 MySQL 支持的函数或者运算符
GROUP BY 语句对查询结果分组
- [GROUP BY {col_name | position} [ASC | DESC], ...]
HAVING 语句设置分组条件
- [HAVING where_condition]
- 请使用聚合函数或 select_column，否则报错
ORDER BY 语句对查询结果排序
- [ORDER BY {col_name | expr | position} [ASC | DESC],...]
LIMIT 语句限制查询数量
- [LIMIT {[offset,] row_count | row_count OFFSET offset}]
- 想要获取第三、第四条记录，写法是 LIMIT 2 OFFSET 2

第 5 章子查询与连接

子查询简介
- 简介
  - 子查询（Subquery）是指出现在其他 SQL 语句内的 SELECT 子句
  - 子查询指嵌套在查询内部，且必须始终出现在圆括号内
  - 子查询可以包含多个关键字或条件，如 DISTINCT、GROUP BY、ORDER BY、LIMIT、函数等
  - 子查询的外层查询可以：SELECT、INSERT、UPDATE、SET、DO 等
  - 子查询可以返回标量、一行、一列或子查询
- 例子
  - SELECT * FROM t1 WHERE col1 = (SELECT col2 FROM t2);
    - 其中 SELECT * FROM t1，称为 Outer Query / Outer Statement
    - SELECT col2 FROM t2，称为 SubQuery
由比较运算符引发的子查询
- 使用比较运算符的子查询：=，>，<，>=，<=，<>，!=，<=>
- 语法结构：operand comparison_operator subquery
- 比较运算符
  - operand comparison_operator ANY(subquery)
    - >、>= 最小值
    - <、<= 最大值
    - = 任意值
    - <>、!=
  - operand comparison_operator ALL(subquery)
    - >、>= 最大值
    - <、<= 最小值
    - =
    - <>、!= 任意值
  - operand comparison_operator SOME(subquery)
    - >、>= 最小值
    - <、<= 最大值
    - = 任意值
    - <>、!=
由 [NOT] IN/EXISTS 引发的子查询
- 语法结构：operand comparison_operator [NOT] IN (subquery)
  - =ANY 运算符与 IN 等效
  - !=ALL 或 <>ALL 运算符与 NOT IN 等效
- 语法结构：operand comparison_operator [NOT] EXISTS
  - 如果子查询返回任何行，EXISTE 将返回 TRUE，否则为 FALSE
使用 INSERT...SELECT 插入记录
- INSERT..SELECT
  - 将查询结果写入数据表：INSERT [INTO] tbl_name [(col_name)] SELECT...
<a id="5.5"多表更新>
- UPDATE table_references
- SET col_name1={expr1|DEFAULT}
- [,col_name2={expr2|DEFAULT}]
- [WHERE where_condition]
多表更新之一步到位
- CREATE...SELECT
  - 创建数据表同时将查询结果写入到数据表：CREATE TABLE [IF NOT EXISTS] tbl_name [(create_definition,...)] select_statement
连接的语法结构
- 连接
  - MySQL 在 SELECT 语句、多表更新、多表删除语句中支持 JOIN 操作
  - 语法结构
    - table_reference - 数据表参照
      - tbl_name [[AS] alias] | table_subquery [AS] alias
        
        数据表可以使用 tbl_name As alias_name 或 tbl_name alias_name 赋予别名
        
        table_subquery 可以作为子查询使用在 FROM 子句中，这样的子查询必须为其赋予别名
    - {[INNER | CROSS] JOIN | {LEFT | RIGHT} [OUTER] JOIN}
    - table_reference
    - ON conditional_expr
连接类型
- 内连接
  - INNER JOIN
- 外连接
  - LEFT JOIN
  - RIGHT JOIN
- 交叉连接
  - CROSS JOIN
- 全连接
  - FULL JOIN
- 联合查询
  - UNION
  - UNION ALL
关于连接的几点说明
- 外连接
  - SELECT * FROM A LEFT JOIN B [WHERE where_condition]
    - 数据表 B 的结果集依赖数据表 A
    - 数据表 A 的结果集根据左连接条件依赖所有数据表
    - 左外连接条件决定如何检索数据表 B
    - 如果数据表 A 的某条记录符合 WHERE 条件，但是在数据表 B 不存在符合连接条件的记录，将生成一个所有列为空的额外的行
- 交叉连接
  - SELECT * FROM A,B,(,C)或者SELECT * FROM A CROSS JOIN B (CROSS JOIN C)
    - 没有任何关联条件，结果是笛卡尔积，结果集会较大，适用场景较少。
- 内连接
  - SELECT * FROM A,B WHERE A.id = B.id或者SELECT * FROM A INNER JOIN B ON A.id = B.id
    - 数据表 A 结果集和数据表 B 结果集的交集。
  - 内连接类型
    - 不等值连接：ON A.id = B.id
    - 等值连接：ON A.id > B.id
    - 自连接：SELECT * FROM A T1 INNER JOIN A T2 ON T1.id = T2.pid
- 联合查询
  - SELECT * FROM A UNION SELECT * FROM B UNION ...
  - 把多个结果集结合在一起，UNION 前的结果为基准，需要注意的是联合查询的列数要相等。
  - UNINO 会合并相同的记录行。
  - UNION ALL 不会合并重复的记录行。
- 全连接
  - MySQL 不支持全连接
  - 可以使用 LEFT JOIN 和 UNION 和 RIGHT JOIN 联合使用 SELECT * FROM A LEFT JOIN B ON A.id = B.id UNION SELECT * FROM A RIGHT JOIN B ON A.id = B.id
多表删除
- DELETE tbl_name[.*] [,tbl_name[.*]] ... FROM table_references [WHERE where_condition]

luisedware / Archives

学习 MySQL（一） #5

目录

<a id="1.4>登录与退出

<a id="5.5"多表更新>