基于不同的操作目的,为提高效率,创建数据库时可使用不同的数据库存储引擎,最常见时事务型InnoDB
- mysql 常见数据类型
字符串: char 固定长度, varchar(n) 可变长度
数值: int , float(m, d) , double(m, d) m为整数位数,d为小数位数
日期/时间: date 日期, time 时间, datetime 日期和时间
创建数据表时,五大完整性约束:
primary key 主键约束,唯一且不能为空
not null 非空约束
unique 唯一性约束
default <值> 默认约束
foreign key 外键约束 foreign key(<字段>) references <表名>(<字段>)
check 检查约束
create table <表名> (
<字段1> <类型1> [约束1],
...
<字段n> <类型n> [约束n],
);
- ### 创建数据表
举例:
- 员工信息表:employees
1. 设计表结构、字段;
字段 :工号 姓名 性别 年龄 部门 薪水
id name gender age department salary
2. 创建员工信息表
mysql> create database pgdb; Query OK, 1 row affected (0.31 sec) mysql> use pgdb; Database changed mysql> show tables; Empty set (0.12 sec) mysql> create table employees( -> id int, -> name varchar(20), -> gender char(1), -> age int, -> department varchar(30), -> salary float(8, 2) -> ); Query OK, 0 rows affected (1.33 sec) mysql> show tables; +----------------+ | Tables_in_pgdb | +----------------+ | employees | +----------------+ 1 row in set (0.01 sec) mysql> desc employees; +------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+-------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(20) | YES | | NULL | | | gender | char(1) | YES | | NULL | | | age | int(11) | YES | | NULL | | | department | varchar(30) | YES | | NULL | | | salary | float(8,2) | YES | | NULL | | +------------+-------------+------+-----+---------+-------+ 6 rows in set (0.01 sec)
- 部门信息表 departments
1. 设计表结构
字段:部门id 部门名称 所在城市
id name location
2. 创建部门信息表
`
mysql> create table departments -> ( -> id int, -> name varchar(100), -> location varchar(50) -> ); Query OK, 0 rows affected (0.46 sec) mysql> show tables; +----------------+ | Tables_in_pgdb | +----------------+ | departments | | employees | +----------------+ 2 rows in set (0.01 sec) mysql> desc departments; +----------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+--------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(100) | YES | | NULL | | | location | varchar(50) | YES | | NULL | | +----------+--------------+------+-----+---------+-------+ 3 rows in set (0.01 sec)
- 查看创建表的语句 show create table employees; show create table departments;
mysql> show create table employees; +-----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | employees | CREATE TABLE `employees` ( `id` int(11) DEFAULT NULL, `name` varchar(20) DEFAULT NULL, `gender` char(1) DEFAULT NULL, `age` int(11) DEFAULT NULL, `department` varchar(30) DEFAULT NULL, `salary` float(8,2) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci | +-----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.19 sec)