Skip to main content

SQL学习指南

第二版

前言​

以SQL92标准为蓝本。

  • SQL模式语句：用于创建数据库对象（表、索引、约束）的语句。
  • SQL数据语句：用于创建、操作和检索保存在数据库中的数据。

1 背景知识​

非关系数据库​

  • 层次数据库（树形结构）
  • 网状数据库

这两种数据库仍然存在，尽管主要在大型机领域中使用。层次数据库系统与XML相结合，在目录服务方面获得了新的应用，比如微软的Active Directory和Red Hat的Directory Server。

关系数据库​

1970年后提出了关系型数据库。

“冗余数据”虽然是一种浪费的做法，但是在某些情况下使用冗余数据能够更清晰地体现关系模型。

SQL​

SQL2006则聚焦于SQL和XML的集成，并定义了XQuery语言以用于XML文档中查询数据。

需要注意一点：SQL并不是任何短语的缩写（尽管许多人都认为是Structured Query Language）。当天此语言时，可以使用独立的其母（S.Q.L）或使用单词sequel。

分类：

  • SQL方案（schema）语句
  • SQL数据语句
  • SQL事务语句

SQL：非过程化语句

过程化语言对所期望的结果和产生这些结果的执行机制或过程都进行了定义。非过程化语言同样定义了期望结果，但产生结果的过程留给外部代理来定义。

2 创建和使用数据库​

创建数据库​

创建用户并赋予bank数据库权限

grant all privileges on bank.* to 'lrngsql@localhost' identified by 'xyz';

使用命令行工具​

mysql -u lrngsql -p bank

这样可以省去输入use bank

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2024-01-24 21:29:30 |
+---------------------+
1 row in set (0.00 sec)

缺失的子句：比如Oracle数据库规定查询语句中必须包含from子句，可以使用dual表：SELECT now() FROM dual;

数据类型​

字符型数据

char列可以设置最大长度255个字节，而varchar列最多可以存储65535个字节（64KB）。

更长采用mediumtext和longtext。

Oracle数据库使用varchar2表示变长字符串。

mysql> show character set;
+----------+---------------------------------+---------------------+--------+
| Charset  | Description                     | Default collation   | Maxlen |
+----------+---------------------------------+---------------------+--------+
| armscii8 | ARMSCII-8 Armenian              | armscii8_general_ci |      1 |
| ascii    | US ASCII                        | ascii_general_ci    |      1 |
| utf16le  | UTF-16LE Unicode                | utf16le_general_ci  |      4 |
| utf32    | UTF-32 Unicode                  | utf32_general_ci    |      4 |
| utf8     | UTF-8 Unicode                   | utf8_general_ci     |      3 |
| utf8mb4  | UTF-8 Unicode                   | utf8mb4_0900_ai_ci  |      4 |
+----------+---------------------------------+---------------------+--------+
41 rows in set (0.01 sec)

安装MySQL服务器时，Latin1字符集为默认字符集。

varchar(20) character set utf8
create database dbname character set utf8;
文本类型最大字节数
tinytext255
text65535（64KB）
mediumtext16777215（16MB）
longtext4294967295（4GB）
  • 如果文本超出了最大长度会被截断
  • 向文本列装载数据时不会消除数据的尾部空格。
  • 使用文本列排序或者分组时只会使用前1024个字节，也可以放宽这个限制。
  • varchar列最大容纳65536个字节（64KB）。

对于一般的notes列一般使用varchar就足够了，如果遇到文档，可以选择mediumtext或longtext类型。

数值型数据

整数：tinyint、smallint、mediumint、int、bigint

浮点数：float、double。

整数除此之外，每种类型提供了unsigned类型，例如 int unsigned

float(4,2)将会存储4位数字，其中两位整数，两位小数。

时间类型

类型默认格式
dateYYYY-MM-DD
datetimeYYYY-MM-DD HH:MI:SS
timestampYYYY-MM-DD HH:MI:SS
yearYYYY
timeHHH:MI:SS

日期格式

组成部分定义范围
YYYY1000-9999
MM月份
DD日
HH小时00-23
HHH小时（过去的）-838~838
MI分钟
SS秒

表的创建​

CREATE TABLE person
(
	person_id SMALLINT UNSIGNED,
    fname VARCHAR(20),
    gender CHAR(1) CHECK(gender IN ('M', 'F')),
    ...,
    postal_code VARCHAR(20),
    CONSTRAINT pk_person PRIMARY KEY (person_id)
);

MySQL中提供了另一种方法检查类型。

gender ENUM('M', 'F'),

describe命令（简写desc）查看表定义。