SQL 学习指南-数据库使用

1、缺失子句

now() 是MySQL的内建函数，返回当前的日期和时间。在MySQL中可以直接使用下列语句查询：

SELECT NOW();

但是某些数据库规定查询语句必须包含 from 子句，并在其中至少指定一个表名，比如广泛使用的 Oracle 数据库。

这时如果仅仅需要调用一个函数，Oracle 为此提供了一个特殊的表 dual，该表只包含一个名为 dummy（虚拟的）的列，并且只会有一个数据行。

为了能与 Oracle 数据库保持兼容，MySQL 也提供了 dual 表，所以当前时间查询还可以写成下面的形式：

SELECT NOW() FROM DUAL;

2、字符型数据

字符类型

字符型数据可以使用定长或可变长字符串来实现，固定长度的字符串使用空格向右填充，以保证占用同样的字节数，变长字符串不需要向右填充，字节数可变。

在所有主流数据库中，char 和 varchar 的使用方式都是类似的，注意，Oracle 数据库对 varchar 的使用是个特例，使用 varchar2 类型表示可变长字符串。

char 列可以设置的最大长度为 255 个字节，varchar 最大长度为 65535 个字节。

如果需要存储最大长度不超过 20 个字符的字符串，可以这样定义：

char(20)
varchar(20)

字符集

对于拉丁系语言，比如英语，每个字母只需要 1 个字节来存储，其他一些语言，比如汉语，日语，韩语等，每个字符的存储需要多个字节，这类字符集被称为多字符集。

查看数据库服务器支持的字符集，查询结果中第四列 maxlen 大于 1，那么该字符集为多字符集。

SHOW CHARACTER SET;

可以看到有两个 utf8 和 utf8mb4，参考 MySQL 数据库字符集 utf8 和 utf8mb4 的区别

可以单独为表中某一列设置专用字符集，在类型定义的后边加上字符集名称即可，比如修改 username 列为 utf8mb4：

`username` varchar(10) CHARACTER SET utf8mb4 NOT NULL,

文本类型

如果需要存储的数据超过 64KB（varchar 列所能允许的最大值），就需要使用文本类型。下面是 MySQL 的文本类型。

文本类型	最大长度字节
thinytext	255
text	65535
mediumtext	16777215
longtext	4294967295

注意事项：

（1）保存到文本类型列中的数据超出了该类型最大长度，数据会被截断。

（2）向文本列保存数据的时候，不会消除数据的尾部空格。

（3）当使用文本列排序或分组的时候，只会使用前 1024 个字节，当然在需要时可以放宽该限制。

（5）MySQL 中 varchar 最大为 65535 字节，所以不需要使用 thinytext 和 text 类型了。

其他数据库：

（4）SQLServer 只提供 text 类型，DB2 和 Oracle 的文本类型为 clob。

（5）Oracle 中 char 列最多 2000 字节，varchar2 列最大4000 字节，SQLServer 中 char 和 varchar 都能最大容纳 8000 字节。

3、数值型数据

整数类型

使用整数类型的时候，MySQL将为存储数据分配合适大小的空间，从1个字节到8个字节不等，所以我们选择整数类型的时候只需要确保能够容纳预期的最大数字即可。

可以在这些整数类型前面加上 UNSIGNED 关键字，指定该列数据 >=0。

在创建表时，输入的 id int（11），后面的 11 表示的是该数据类型指定的显示宽度。显示宽度与数据类型的取值范围无关，显示宽度只是指明 mysql 最大可能显示的数字个数。最大的合法的显示宽度是 255，当数值的位数小于指定宽度时会由空格填充，如果插入的值大于显示宽度，只要该值不超过该类型的取值范围，数值依然可以插入，也可以完整显示。如果不指定数值，则系统为每一种类型指定默认的宽度值。

浮点类型

使用浮点类型时，可以指定其精度（小数点左边到右边所允许的总位数）和有效位数（小数点右边所允许的数字位数），不是必须的。

如果数字位数超过了该列所定义的精度或者有效位数，该数据将被四舍五入。

可以在这些浮点类型前面加上 UNSIGNED 关键字，指定该列数据 >=0，不过和整型的区别是并没有改变该列所存储数据的范围。

一个定义为 float(4,2) 的列将会存储四位有效数字，其中两位在小数点左边，两位在小数点右边。

如果向该列中添加数据 27.44 和 8.19 是允许的，但是添加 17.8675 将会被四舍五入为 17.87， 添加 178.375 则会报错。 

4、时间型数据

TIME 类型的默认格式为 HHH:MI:SS

DATETIME 类型的默认格式为 YYYY-MM-DD HHH:MI:SS

TIMESTAMP 类型的默认格式为 YYYY-MM-DD HHH:MI:SS

组成部分	定义	范围
YYYY	年份，包括世纪	1000~9999
MM	月份	01~12
DD	日	01~31
HH	小时	00~23
HHH	小时（过去的）	-838~838
MI	分钟	00~59
SS	秒	00~59

Oracle 允许的日期范围：公元前4712年~公元9999年。

MySQL 允许的日期范围：公元前1000年~公元9999年。

SQLServer 允许的日期范围：公元前1753年~公元9999年。

时间日期操作

MysQL数据库涉及到日期的操作，可以直接按照默认格式提供字符串，也可以显式的指定字符串格式，下面两句SQL效果相同

（1）使用默认格式。例如 person 表中 birth_date 字段类型为 date，则默认格式为 YYYY-MM-DD

UPDATE person SET birth_date = '1980-12-21' WHERE person_id = 1;

（2）使用 str_to_date 函数指定字符串格式。

UPDATE person SET birth_date = STR_TO_DATE('DEC-21-1980', '%b-%d-%Y') WHERE person_id = 1;

详细格式参考：MySQL 日期、字符串、时间戳互转

5、创建表

CREATE TABLE person(
    person_id SMALLINT UNSIGNED,
    fname VARCHAR(20),
    lname VARCHAR(20),
    #gender CHAR(1) CHECK(gender IN ('M','F')),
    gender ENUM('M','F'),
    birth_date DATE,
    street VARCHAR(20),
    city VARCHAR(20),
    state VARCHAR(20),
    country VARCHAR(20),
    postal_code VARCHAR(20),
    CONSTRAINT pk_person PRIMARY KEY (person_id)
);

喜欢的食物表

CREATE TABLE favorite_food(
    person_id SMALLINT UNSIGNED,
    food VARCHAR(20),
    CONSTRAINT pk_favorite_food PRIMARY KEY (person_id, food),
    CONSTRAINT fk_fav_food_person_id FOREIGN KEY (person_id) REFERENCES person (person_id)
);

（1）UNSIGNED 表示该列为大于等于 0 的整数

person_id SMALLINT UNSIGNED

（2）性别，只有两个值，所以使用了检查约束。

gender CHAR(1) CHECK(gender IN ('M','F'))

对于大多数数据库检查约束都是可以正常工作的，但是对于 MySQL 数据库，虽然允许定义检查约束，但是并不强制使用它，所以在 MySQL 中我们使用枚举 enum 字符数据类型，实现检查约束的功能。所以可以这样定义性别

gender ENUM('M','F')

（3）复合主键，该主键包含了两列，person_id 和 food。

CONSTRAINT pk_favorite_food PRIMARY KEY (person_id, food)

（4）外键，限制了 favorite_food 表中的 person_id 只能来自于 person 表

注意：外键约束只能在使用 InnoDB 存储引擎建表时才起作用。

CONSTRAINT fk_fav_food_person_id FOREIGN KEY (person_id) REFERENCES person (person_id)

（5）创建完表之后，可以使用 describe 命令检查表定义，可简写为 desc。

DESC person;

DESCRIBE favorite_food;

NULL 用于各种不能赋值的情况，例如业务上不可行，不知道应赋何值，集合为空等情况。

（4）通过查看 Navicate 对象信息中的 DDL 中SQL语句，可以看出和原始建表语句有一点点区别

CREATE TABLE `person` (
  `person_id` smallint(5) unsigned NOT NULL,
  `fname` varchar(20) DEFAULT NULL,
  `lname` varchar(20) DEFAULT NULL,
  `gender` enum('M','F') DEFAULT NULL,
  `birth_date` date DEFAULT NULL,
  `street` varchar(20) DEFAULT NULL,
  `city` varchar(20) DEFAULT NULL,
  `state` varchar(20) DEFAULT NULL,
  `country` varchar(20) DEFAULT NULL,
  `postal_code` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`person_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `favorite_food` (
  `person_id` smallint(5) unsigned NOT NULL,
  `food` varchar(20) NOT NULL,
  PRIMARY KEY (`person_id`,`food`),
  CONSTRAINT `fk_fav_food_person_id` FOREIGN KEY (`person_id`) REFERENCES `person` (`person_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

6、表中数据操作

（1）生成主键

Oracle 数据库提供了使用序列号（sequence）生成数字型主键

MySQL 只需要为主键列打开自增特性（aoto-increment），默认从1开始自增。

使用 alter table 修改已存在的表的定义。

ALTER TABLE person MODIFY person_id SMALLINT UNSIGNED auto_increment;

提示以下错误，提示说 person_id 为 favorite_food 表中的外键，不能修改。所以我先把 favorite_food 删掉，等修改完 person_id 后再生成 favorite_food 表。

[SQL]ALTER TABLE person MODIFY person_id SMALLINT UNSIGNED auto_increment;
[Err] 1833 - Cannot change column 'person_id': used in a foreign key constraint 'fk_fav_food_person_id' of table 'bank.favorite_food'

插入数据的时候可以简单的将 person_id 列赋值为 null，MySQL 会为该列提供下一个可用的数字主键。

（2）插入数据

INSERT INTO person(person_id, fname, lname, gender, birth_date) VALUES (NULL, 'William', 'Turner', 'M', '1972-05-27');

INSERT INTO person(person_id, fname, lname, gender, birth_date, street, city, state, country, postal_code) 
VALUES (NULL, 'Susan', 'Smith', 'F', '1975-11-02', '23 Maple st.', 'Arlington', 'VA', 'USA', '20220');

可以看出，自动生成了主键，接着插入喜爱的食物

INSERT INTO favorite_food(person_id, food) VALUES (1, 'pizza'),(1, 'cookies'),(1, 'nachos');

（3）更新数据

UPDATE person SET street = '1225 Tremont St.', city = 'Boston', state = 'MA', country = 'USA', postal_code = '02138' WHERE person_id = 1;

（4）删除数据

DELETE FROM person WHERE person_id = 2;

（5）查询数据

SELECT * FROM person;

7、表操作

查看数据库中可用的表

SHOW TABLES;

删除表

DROP TABLE person;