读SQL学习指南（第3版）笔记03_创建和填充数据库

2023-08-23 06:34 由躺柒发表于 #数据库

1. 创建数据表

1.1. sql

CREATE TABLE person
 (person_id SMALLINT UNSIGNED,
  fname VARCHAR(20),
  lname VARCHAR(20),
  eye_color CHAR(2),
  birth_date DATE,
  street VARCHAR(30),
  city VARCHAR(20),
  state VARCHAR(20),
  country VARCHAR(20),
  postal_code VARCHAR(20),
  CONSTRAINT pk_person PRIMARY KEY (person_id)
 );

1.2. eye_color CHAR(2) CHECK (eye_color IN ('BR','BL','GR')),

1.2.1. MySQL允许在定义列时关联检查约束

1.3. eye_color ENUM('BR','BL','GR'),

1.3.1. MySQL提供了另一种名为enum的字符数据类型，将检查约束并入了数据类型定义

1.4. sql

CREATE TABLE person
 (person_id SMALLINT UNSIGNED,
  fname VARCHAR(20),
  lname VARCHAR(20),
  eye_color ENUM('BR','BL','GR'),
  birth_date DATE,
  street VARCHAR(30),
  city VARCHAR(20),
  state VARCHAR(20),
  country VARCHAR(20),
  postal_code VARCHAR(20),
  CONSTRAINT pk_person PRIMARY KEY (person_id)
 );

1.5. 查看数据表定义

1.5.1. mysql＞ desc person;

2. 什么是null？

2.1. 不适用

2.2. 未知

2.3. 空集

3. 数值型主键数据

3.1. 找到当前数据表中的最大值，然后加1

3.2. 由数据库服务器提供

3.2.1. 所有的数据库服务器都提供了一种安全稳健的方法来生成数值型主键

3.2.2. Oracle Database，使用称为序列（sequence）的独立模式对象（schema object）

3.2.3. MySQL中，只需简单地为主键列启用自增（auto-increment）特性

3.2.3.1. ALTER TABLE person MODIFY person_id SMALLINT UNSIGNED AUTO_INCREMENT;

4. XML格式的数据

4.1. 对于MySQL，可以在调用mysql工具时使用--xml选项，所有查询的输出都会自动转换成XML格式

4.1.1. sql

C:\database＞ mysql -u lrngsql -p --xml bank
Enter password: xxxxxx
Welcome to the MySQL Monitor...
Mysql＞ SELECT * FROM favorite_food;
＜?xml version="1.0"?＞
＜resultset statement="select * from favorite_food"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"＞
  ＜row＞
        ＜field name="person_id"＞1＜/field＞
        ＜field name="food"＞cookies＜/field＞
  ＜/row＞
  ＜row＞
        ＜field name="person_id"＞1＜/field＞
        ＜field name="food"＞nachos＜/field＞
  ＜/row＞
  ＜row＞
        ＜field name="person_id"＞1＜/field＞
        ＜field name="food"＞pizza＜/field＞
  ＜/row＞
＜/resultset＞
3 rows in set (0.00 sec)

4.2. 对于SQL Server

4.2.1. SELECT * FROM favorite_food FOR XML AUTO, ELEMENTS

5. 常见错误及响应

5.1. 非唯一的主键

5.1.1. ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

5.1.2. 由于数据表定义中包含主键约束，因此MySQL会确保重复的主键值不会被插入数据表中

5.2. 不存在的外键

5.2.1. ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraintfails ('sakila'.'favorite_food', CONSTRAINT 'fk_fav_food_person_id' FOREIGNKEY('person_id') REFERENCES 'person' ('person_id'))

5.2.2. 仅当使用InnoDB存储引擎创建数据表时，外键约束才是强制的

5.3. 列值违规

5.3.1. ERROR 1265 (01000): Data truncated for column 'eye_color' at row 1

5.3.2. 服务器对所提供的eye_color列的值不满意

5.4. 无效的日期转换

5.4.1. ERROR 1292 (22007): Incorrect date value: 'DEC-21-1980' for column 'birth_date' at row 1

5.4.2. 最好是明确指定格式化字符串，而不是依赖默认格式

5.4.3. str_to_date函数指定了格式化字符串

5.4.3.1. sql

mysql＞ UPDATE person
    -＞ SET birth_date = str_to_date('DEC-21-1980' , '%b-%d-%Y')
    -＞ WHERE person_id = 1;
Query OK, 1 row affected (0.12 sec)
Rows matched: 1  Changed: 1  Warnings: 0

5.4.3.2. %a 星期几的简写，比如Sun、Mon、...

5.4.3.3. %b 月份名称的简写，比如Jan、Feb、...

5.4.3.4. %c 月份的数字形式（0…12）

5.4.3.5. %d 月份中的天数（00…31）

5.4.3.6. %f 微秒数（000000…999999）

5.4.3.7. %H 24小时制中的小时（00…23）

5.4.3.8. %h 12小时制中的小时（01…12）

5.4.3.9. %i 小时中的分钟数（00…59）

5.4.3.10. %j 一年中的天数（001…366）

5.4.3.11. %M 月份的全称（January…December）

5.4.3.12. %m 月份的数值形式

5.4.3.13. %p AM或PM

5.4.3.14. %s 秒数（00…59）

5.4.3.15. %W 星期几的全称（Sunday…Saturday）

5.4.3.16. %w 一星期中的天数（0=周日；6=周六）

5.4.3.17. %Y 4位数字表示的年份