SQL语言

SQL语言的兴起与语法标准

• 20世纪70年代，IBM开发出SQL，用于DB2
• 1981年，IBM推出SQL/DS数据库
• 业内标准微软和Sybase的T-SQL，Oracle的PL/SQL
• SQL作为关系型数据库所使用的标准语言，最初是基于IBM的实现在1986年被批准的。1987年，“国际标准化组织（ISO）”把ANSI（美国国家标准化组织）SQL作为国际标准
• SQL：ANSI SQL
  SQL-86，SQL-89，SQL-92，SQL-99，SQL-03

SQL语言规范

• 在数据库系统中，SQL语句不区分大小写（建议用大写）
• 但字符串常量区分大小写
• SQL语句可单行或多行书写，以 " ; " 结尾
• 关键词不能跨多行或简写
• 用空格和缩进来提高语句的可读性
• 子句通常位于独立行，便于编辑，提高可读性
• 注释：
  • SQL标准：
    /*注释内容*/：多行注释
    -- 注释内容：单行注释，注意有空格
  • MySQL注释：
    #

---

数据库对象

数据库的组件（对象）：

数据库、表、索引、视图、用户、存储过程、函数、触发器、事件调度器等

命名规则：

• 必须以字母开头
• 可包括数字和三个特殊字符（#_$）
• 不要使用 MySQL 的保留字
• 同一 database（Schema）下的对象不能同名

---

SQL语句分类

DDL：Data Defination Language

• CREATE
• ALTER
• DROP

DML：Data Manipulation Language

• INSERT
• DELETE
• UPDATE

DCL：Data Control Language

• GRANT
• REVOKE

DQL：Data Query Language

• SELECT

---

SQL语句构成

keyword 组成 clause
多条 clause 组成语句

• 示例：
  SELECT *：SELECT子句
  FROM products：FROM子句
  WHERE price>400：WHERE子句

说明：一组SQL语句，由三个子句构成，SELECT，FROM和WHERE是关键字

---

数据类型

MySQL支持多种列类型：

• 数值类型
• 日期/时间类型
• 字符串（字符）类型

选择正确的数据类型对于获得高性能至关重要，三大原则：

1. 更小的通常更好，尽量使用可正确存储数据的最小数据类型
2. 简单就好，简单数据类型的操作通常需要更少的CPU周期
3. 尽量避免NULL，包含为NULL的列，对MySQL更难优化

1. 整型

• tinyint(m)：1个字节，范围（-128~127）
• smallint(m)：2个字节，范围（-32768~32767）
• mediumint(m)：3个字节，范围（-8388608~8388607）
• int(m)：4个字节，范围（-2147483648~2147483647）
• bigint(m)：8个字节，范围（+-9.22*10的18次方）
• BOOL，BOOLEAN：布尔型，是 tinyint(1) 的同义词。 zero 值被视为假，非 zero 值视为真

注意：

1. 取值范围如果加了unsigned，则最大值翻倍，如tinyint unsigned的取值范围为（0~255）
2. int(m)括号里的m是表示SELECT查询结果集中的显示宽度，并不影响实际的取值范围，规定了 MySQL 的一些交互工具（例如 MySQL 命令行客户端）用来显示字符的个数。对于存储和计算来说，int(1) 和 int(20) 是相同的

2. 浮点型（float和double），近似值

• float(m,d)：单精度浮点型，8位精度（4字节），m总个数，d小数位
• double(m,d)：双精度浮点型，16位精度（8字节），m总个数，d小数位
• 设一个字段定义为float(6,3)，如果插入一个数123.45678，实际数据库里存的是123.456，但总个数还以实际为准，即6位

3. 定点数

• 在数据库中存放的是精确值，存为十进制
• decimal(m,d)：参数m<65是总个数，d<30且d<m是小数位
• MySQL5.0 和更高版本将数字打包保存到一个二进制字符串中（每4个字节存9个数字）。例如：decimal(18,9)小数点凉拌将各存储9各数字，一共使用9各字节；小数点前的数字用4各字节，小数点后的数字用4各字节，小数点本身占1个字节
• 浮点类型在存储同样范围的值时，通常比 decimal 使用更少的空间。float 使用4个字节存储。double 占用8个字节
• 因为需要额外的空间和计算开销，所以应该尽量只在对小数进行精确计算时才使用decimal，例如存储财务数据。但在数据量比较大的时候，可以考虑使用 bigint 代替 decimal

4. 字符串（char，varchar，text）

• char(n)：固定长度，最多255个字符
• varchar(n)：可变长度，最多65535个字符
• tinytext：可变长度，最多255个字符
• text：可变长度，最多65535个字符
• mediumtext：可变长度，最多2的24次方-1个字符
• longtext：可变长度，最多2的32次方-1个字符
• BINARY(M)：固定长度，可存二进制或字符，长度为0~M字节
• VARBINARY(M)：可变长度，可存二进制或字符，允许长度为0~M字节
• 内建类型：ENUM枚举，SET集合

字符串区别

• char 和 varchar
  1. char(n) 若存入字符数小于n，则以空格补于其后，查询之时再将空格去掉。所以char类型存储的字符串末尾不能有空格，varchar不限于此
  2. char(n) 固定长度，char(4) 不管是存入几个字符，都将占用4个字节，varchar 是存入的实际字符数+1个字节（n<n>255），所以varchar(4)，存入3个字符将占用4个字节
  3. char类型的字符串检索书店要比varchar类型的快
• varchar 和 text
  1. varchar 可指定n，text不能指定，内部存储 varchar 是存入的实际字符数+1个字节（n<n>255），text 是实际字符数+2个字节
  2. text 类型不能有默认值
  3. varchar 可直接创建索引，text 创建索引要指定前多少个字符。varchar 查询速度快于 text

5. 二进制数据：BLOB

• BLOB 和 text 存储方式不同，TEXT以文本方式存储，英文存储区分大小写，而BLOB是以二进制方式存储，不区分大小写
• BLOB存储的数据只能整体读出
• TEXT可以指定字符集，BLOB不用指定字符集

6. 日期时间类型

• date：日期 '2008-12-2'
• time：时间 '12:25:36'
• datetime：日期时间 '2008-12-2 22:06:44'
• timestamp：自动存储记录修改时间
• YEAR(2)，YEAR(4)：年份

timestamp字段里的时间数据会随其他字段修改的时候自动刷新，这个数据类型的字段可以存放这条记录最后被修改的时间

---

修饰符

所有类型

• NULL：数据列可包含NULL值
• NOT NULL：数据列不允许包含NULL值
• DEFAULT：默认值
• PRIMARY KEY：主键
• UNIQUE KEY：唯一键
• CHARACHER SET name：指定一个字符集

数值型

• AUTO_INCREMENT：自动递增，适用于整数类型
• UNSIGNED：无符号

---

数据库操作

• 创建数据库
  CREATE DATABASE db_name CHARACTER SET utf8 COLLATE utf8_general_ci;
• 删除数据库
  DROP DATABASE|SCHEMA [IF EXISTS] 'DB_NAME';
• 查看支持所有字符集
  SHOW CHARACTER SET;
• 查看支持所有排序规则
  SHOW COLLATION;
• 查看数据库列表
  SHOW DATABASES;

---

表

• 二维关系
• 设计表时，需要遵循规范

表的定义：

字段，索引

• 字段：字段名，字段数据类型，修饰符
• 约束，索引：应该创建在经常用作查询条件的字段上

字段

• Storage Engine 是指表类型，也即在表创建时指明其使用的存储引擎，同一库中不同表可以使用不同的存储引擎
• 同一个库中表建议要使用同一种存储引擎类型
• 字段信息：
  • col type1
  • PRIMARY KEY(clo1,...)
  • INDEX(col1,...)
  • UNIQUE KEY(col1,...)
• 表选项：
  • ENGINE [=] engine_name
SHOW ENGINES;:查看支持的engine类型
  • ROW_FORMAT [=] {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT}

表操作

• 获取帮助：
  help CREATE TABLE
• 查看所有的引擎：
  SHOW ENGINES
• 查看表：
  SHOW TABLES [FROM db_name]
• 查看表结构：
  DESC [db_name.]tbl_name
• 删除表：
  DROP TABLE [IF EXISTS] tbl_name
• 查看表创建命令：
  SHOW CREATE TABLE tbl_name
• 查看表状态：
  SHOW TABLE STATUS LIKE 'tbl_name'
• 查看库中所有表状态：
  SHOW TABLE STATUS FROM db_name