4千字带你踏入SQL的世界 | SQL全面教程一：数据库、SQL概念及使用DDL创建管理数据库表

代码迷途
945 阅读13分钟

数据库

数据库(Database DB)是将大量数据保存起来，通过计算机加工而成的可以进行高效访问的数据集合。

用来管理数据库的计算机系统称为数据库管理系统（Database Management System,DBMS)。

数据库和数据库管理系统经常被混淆。

DBMS的种类

DBMS主要通过数据的保存格式（数据库的种类）来进行分类。主要有5种类型。

  1. 层次数据库（Hierarchical Database, HDB）

最古老的数据库之一，它把数据通过层次结构（树形结构）的方式表现出来。层次数据库曾是数据库的主流，随着关系数据库的出现和普及，已经很少使用了。

  1. 关系数据库（Relational Database, RDB）

关系数据库是现在应用最广泛的数据库。关系数据库在1969年诞生，采用由行和列组成的二维表来管理数据，同时使用专门的SQL(Structured Query Language，结构化查询语言）对数据进行操作。

这种类型的DBMS称为关系数据库管理系统(Relational Database Management System, RDBMS)。常见的有OracIe Database、SQL Server、DB2、PostgreSQL、MySQL

  1. 面向对象数据库(Object Oriented Database, OODB)

借鉴于编程语言当中面向对象语言的概念。把数据以及对数据的操作集合起来以对象为单位进行管理，因此得名。面向对象数据库就是用来保存这些对象的数据库。

  1. XML数据库(XML Database, XMLDB)

XML数据库可以对XML形式的大量数据进行高速处理

  1. 键值存储系统(Key-Value Store, KVS)

是一种单纯用来保存查询所使用的主键(Key)和值(Value)的组合的数据库。即类似编程中的json格式，或者关联数组或者散列(hash)。近年来，随着键值存储系统被应用到Google等需要对大量数据进行超高速查询的Web服务当中，它正逐渐为人们所关注。

数据库和表的结构

RDBMS通常采用客户端/服务器类型(C/S类型)的系统结构

服务器指的是用来接收其他程序发出的请求，并对该请求进行相应处理的程序（软件），或者是安装了此类程序的设备（计算机）。RDBMS就是一种服务器，它从保存在硬盘上的数据库中读取数据并返回，或者更改数据。

对应地，向服务器发出请求的程序(软件)，或者是安装了该程序的设备(计算机)称为客户端。多个客户端可以同时对同一个数据库进行读写操作。

用来管理数据的二维表在关系数据库中简称为表，它存储在由RDBMS管理的数据库中

表（table）：某种特定类型数据的结构化清单。在表中的数据是同一种类型的数据或清单。即某一类数据的集合

根据SQL语句的内容返回的数据同样必须是二维表的形式(关系数据库的特征之一)。

  • 表的列column(垂直方向)称为字段，表示保存在表中的数据项目；
  • 表的行row(水平方向)称为记录,表示一条数据。

关系数据库必须以行为单位进行数据读写

模式（schema）：关于数据库和表的布局及特性的信息。模式定义了数据在表中如何存储，包含存储什么样的数据，数据如何分解，各部分信息如何命名等信息。数据库和表都有模式。也可以翻译为"架构"。

数据类型

创建表时指定的列名的右边，会紧跟该列的数据类型。所有列都必须指定数据类型。

数据类型表示数据的种类，如数字型(INTEGER)、字符型(CHAR，CHARACTER[字符]的缩写，即存储字符串)和日期型(DATE)。

每一列只能存储与该列数据类型相同的数据。

  • 关于CHAR和VARCHAR类型

CHAR类型的列中存储的是定长字符串。定长字符串，就是当列中存储的字符串长度达不到最大长度的时候，使用半角空格进行补足，即列出存储的是固定长度的字符串。

VARCHAR类型的列中存储的是可变长字符串。当存储的字符串没有达到VARCHAR指定的最大长度时，仍以原字符串进行存储。相对char更灵活，且存储空间较小。

OracIe中使用的是VARCHAR2型(也有VARCHAR，但不推荐使用)

OracIe中的DATE型还包含时分秒。

  • 约束

约束是对列中存储的数据进行限制或者追加条件的功能。

比如NOT NULL非空约束、PRIMARY KEY主键约束。

表中每一行都应该有一列（或几列）可以唯一标识自己，并将该列（或几列）设置为主键。原则上，每个表都有主键。

主键列中的值不允许修改或更新。

键是在指定特定数据时使用的列的组合。

SQL

SQL概要

SQL是一种专门用来操作数据库的语言。

国际标准化组织（ISO）为SQL制定了相应的标准，以此为基准的SQL称为标准SQL，使用标准SQL写的语句可以在各个RDBMS通用。

标准SQL由ANSI标准委员会管理，从而称为ANSI SQL。

ANSI全称American National Standards Institute

SQL用关键字、表名、列名等组合成SQL语句来描述操作的内容。

关键字是指事先定义好的有着特殊含义或使用方法的单词，用来组成SQL语句。它是SQL语言中的保留字。

reserved keyword保留关键字

SQL语句分类：DDL、DML、DCL

根据RDBMS执行指令种类的不同，SQL语句可分为三类：

  • DDL（Data Definition Language，数据定义语言）用来创建或者删除存储数据用的数据库以及数据库中的表等对象。DDL包含以下几种指令。

CREATE：创建数据库和表等对象； DROP：删除数据库和表等对象； ALTER：修改数据库和表等对象的结构

  • DML（Data Manipulation Language，数据操作语言）用来查询或者变更表中的记录。DML包含以下几种。

SELECT：查询表中的数据； INSERT：向表中插入新数据； UPDATE：更新表中的数据； DELETE：删除表中的数据

此处还可以细分为：数据查询语言(DQL，Data Query Language——SELECT)和数据操作语言(DML——INSERT、UPDATE、DELETE)

  • DCL（Data Control Language，数据控制语言）用来确认或者取消对数据库中的数据进行的变更，以及设定RDBMS的用户是否有权限操作数据库中的对象。包含以下几种。

COMMIT：确认对数据库中的数据进行的变更； ROLLBACK：取消对数据库中的数据进行的变更； GRANT：赋予用户操作权限； REVOKE：取消用户的操作权限；

SQL语句规则

SQL语句基本规则：

  1. SQL语句要以分号(;)结尾。大多数(不是所有)DBMS在执行单条语句时可以不加分号。

似乎目前只有SQL Server是例外，其语句可以不使用分号，多条语句使用空格或回车分隔即可，但仍推荐使用分号。

省略语句分隔符(statement delimiter)分号;的特性，将会在未来的版本中移除。即不允许省略分号(semicolon)。

  1. SQL语句不区分大小写。关键字、表名、列名等不区分大小写。具体的值肯定要区分大小写，但表名、列名等却(大多)可以通过DBMS的设置实现区分大小写。
  2. 常数的书写方式是固定的。字符串和日期常数使用单引号（'）括起来，数字直接书写即可。
  3. 单词需要用半角空格或者换行来分隔。

ANSI（美国国家标准协会）或ISO（国际标准化组织）等会隔几年修订SQL的标准(修订语法、追加功能)。以这些标准为基准的SQL就是标准SQL。

但是不同的数据库管理系统各自有着只能在自己特定RDBMS中使用的特殊SQL语句

关于关键字、非常规的标识符

标识符(identifier)指的是数据库对象的名字。比如服务器、数据库、以及表、视图、列、索引、触发器、约束、存储过程等数据库对象，任何事物都可以有一个标识符，即有一个名字标识其自身。

标识符的规则一般都是只能包含字母、数字、下划线(_)。大多数编程语言的标识符规则都是如此。

在数据库中称为常规标识符(Regular identifier)，或规则标识符。

但是如果标识符中包含关键字，或空格等字符时，必须要使用界定标识符(Delimited identifier)的形式，将其引起来，才能使用。

比如创建一个名为table或select的表，直接创建将会报语法错误,因为这是保留关键字。

在SQL Server中，界定标识符，需要用双引号"或方括号[]包含起来；

PostgreSQL中，界定标识符，用双引号"包含起来；

MySQL/MariaDB中，界定标识符，用`(反引号，键盘上左上角和波浪线~一块的符号)包含起来。

注：实际测试在PostgreSQL的psql命令行里使用双线号创建受限标识符的数据库时无效，即不报错也没创建成功。但在pgAdmin4中能成功创建。

-- PostgreSQL | SQL Server 
create table "select a"(
	id integer
)

-- SQL Server 
create table [select a](
	id integer
)

-- MySQL/MariaDB
create table `select a`(
	id integer
)

常见DDL操作

创建数据库和表

使用psql登陆PostgreSQL，创建数据库shop

CREATE DATABASE shop;

删除数据库的方法也很简单

DROP DATABASE shop;

CREATE TABLE创建表的语句如下：

CREATE TABLE <表名>
(
    <列名1> <数据类型> <该列约束>,
    <列名2> <数据类型> <该列约束>,
                .
                .
                .
    <表的约束1>,<表的约束2>,...
);

创建表时每一列的列名和数据类型必须要指定，约束可以直接在列类型后指定，也可以在语句末尾设置。

只有字母、数字、下划线(_)可以作为数据库、表和列的名称，且不能以数字开头

如下，创建商品表(Product)：

CREATE TABLE Product
(
    product_id   CHAR(4)        NOT NULL,
    product_name VARCHAR(100)   NOT NULL,
    product_type VARCHAR(32)    NOT NULL,
    sale_price   INTEGER,
    purchase_price  INTEGER,
    regist_date     DATE,
    PRIMARY KEY (product_id)
);

在SQL Server、MySQL/MariaDB中，整型integer一般都用int表示。PostgreSQL通常使用integer。这几种DBMS都支持使用integer和int

由于特殊的历史原因，在MySQL/Mariadb中创建表时，最好指定编码格式为utf8mb4。尤其是需要用到UTF-8编码的汉字或表情符号时。如下，否则插入中文内容时会报错。

CREATE TABLE `Product` (
  `product_id` char(4) COLLATE utf8mb4_unicode_ci NOT NULL,
  `product_name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `product_type` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL,
  `sale_price` int(11) DEFAULT NULL,
  `purchase_price` int(11) DEFAULT NULL,
  `regist_date` date DEFAULT NULL,
  PRIMARY KEY (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

默认的CHARSET=latin1。

比较的做法是：通过修改MySQL/Mariadb的配置文件，设置字符编码为utf8mb4，这样就不用在每个建表语句(包括语句的列)上指定编码。

Latin1是ISO-8859-1的别名，有些环境下写作Latin-1。

ISO-8859-1编码是单字节编码，向下兼容ASCII，其编码范围是0x00-0xFF，0x00-0x7F之间完全和ASCII一致，0x80-0x9F之间是控制字符，0xA0-0xFF之间是文字符号。

Latin1主要针对欧洲地区的文字的编码扩展。

ISO-8859-1是8位(单字节)编码的国际标准。

删除表

DROP TABLE <表名>;

更新表和字段

更新表定义

1. 添加列

ALTER TABLE <表名> ADD COLUMN <列的定义>;

OracIe和SQL Server中不用写COLUMN：

ALTER TABLE <表名> ADD <列的定义>;

OracIe中同时添加多列的时候可以添加如下括号：ALTER TABLE <表名> ADD (<列的定义>,<列的定义>);。 SQL Server等则不需要添加括号，各个列定义逗号,分隔即可。

比如Product表添加一列product_name_pinyin:

-- PostgreSQL | MySQL | DB2
ALTER TABLE Product ADD COLUMN product_name_pinyin VARCHAR(100);

-- SQL Server
ALTER TABLE Product ADD product_name_pinyin VARCHAR(100);

-- Oracle
ALTER TABLE Product ADD (product_name_pinyin VARCHAR(100));

2. 删除列

ALTER TABLE <表名> DROP COLUMN <列名>;

Oracle中不用写COLUMN，ALTER TABLE <表名> DROP <列名>;。删除多个列时，使用括号。

如下，删除列product_name_pinyin

-- SQL Server | DB2 | PostgreSQL | MySQL
ALTER TABLE Product DROP COLUMN product_name_pinyin;

-- Oracle
ALTER TABLE Product DROP (product_name_pinyin);

3. 修改字段名称

修改列名可以使用如下语句

-- PostgreSQL
ALTER TABLE Product RENAME purchase_price TO purchase_price1;

-- SQL Server
EXEC sp_rename '表名.[字段旧名]', '字段新名' , 'COLUMN';

-- MySQL/MariaDB  使用change修改字段名称，新名称必须指定字段类型，否则报错，即相当于重建了一个列。  column关键字可有可无
-- alter table tab_name change [column] old_name new_name DATETYPE [first|after col_name]
alter table Product change column purchase_price purchase_price1 int(11);

MySQL/MariaDB中使用change修改字段名称，新名称必须指定字段类型，否则报错，即相当于重建了一个列。如果想实现仅重命名，需要将旧字段有关的类型、约束等前部赋给新字段名

4. 修改字段类型

-- PostgreSQL
ALTER TABLE Product ALTER COLUMN regist_date TYPE timestamp;  -- 等同 timestamp without time zone

-- Oracle | MySQL/MariaDB modify用来修改列的定义，不仅仅是类型
alter table Product modify regist_date timestamp; 

-- MySQL/MariaDB modify column关键字可有可无
alter table Product modify column regist_date date;

-- SQL Server
alter table Product alter column regist_date datetime null;

-- MySQL/MariaDB 还可以使用change修改字段类型。因为是重建列，不推荐
alter table Product change [column] regist_date regist_date timestamp;

实际测试，使用MySQL/MariaDB中修改字段类型时，默认约束也会变更。其他DBMS未全部测试，但实际修改字段类型时，一定要在测试数据库中提前测试好。

MySQL/MariaDB中也支持 ALTER COLUMN 子句，但是目前 只能用来设置和移除列的默认值。

对于MySQL/MariaDB，使用MODIFY或CHANGE修改表的结构，如果涉及到数据内容变更，会发生数据移动，而如果这张表的数据量特别大时，索引很多，内存又不足等，MODIFY或CHANGE命令的修改或许会花费几个小时甚至几天。

因此在大表情况下，一定做好备份、测试（确定是否会数据移动）、主从同步等，以及尝试其他的修改方法。

5. 修改表名

如果发现表名写错了，或者想要修改表名，可以使用下面语句

-- PostgreSQL | Oracle | MySQL
ALTER TABLE Product RENAME TO Product1;

-- SQL Server
sp_rename 'Product','Product1';

-- MySQL | DB2
RENAME TABLE Product to Product1;

-- MySQL修改表的语句,[to|as]可以省略。 alter table tbl_name rename[to|as] new_tbl_name

标准SQL中并没有RENAME

DML操作：插入数据

INSERT INTO <表名> VALUES (value1, value2 ,...);

-- 或
INSERT INTO <表名>(col1,col2,...) VALUES (value1, value2 ,...);

如下，向Product表中插入数据：

--SQL Server PostgreSQL
-- DML:插入数据
BEGIN TRANSACTION;  -- MySQL为 START TRANSACTION;     -- Oracle DB2 不需要添加此行，删除即可  
INSERT INTO Product VALUES ('0001', 'T恤' ,'衣服', 1000, 500, '2009-09-20');
INSERT INTO Product VALUES ('0002', '打孔器', '办公用品', 500, 320, '2009-09-11');
INSERT INTO Product VALUES ('0003', '运动T恤', '衣服', 4000, 2800, NULL);
INSERT INTO Product VALUES ('0004', '菜刀', '厨房用具', 3000, 2800, '2009-09-20');
INSERT INTO Product VALUES ('0005', '高压锅', '厨房用具', 6800, 5000, '2009-01-15');
INSERT INTO Product VALUES ('0006', '叉子', '厨房用具', 500, NULL, '2009-09-20'),
                            ('0007', '擦菜板', '厨房用具', 880, 790, '2008-04-28'),
                            ('0008', '圆珠笔', '办公用品', 100, NULL, '2009-11-11');
COMMIT;

注：插入大量的多个行时推荐使用多行插入的方式，速度会快很多，后续会介绍

avatar
文章
阅读
粉丝