MySQL的建库、建表、建约束与存储引擎
一、MySQL建库、建表
1、创建数据库
创建数据库是在系统磁盘上划分?块区域用于数据的存储和管理，如果管理员在设置权限的时候为用户创建了数据库，则可以直接使用，否则，需要自己创建数据库。
语法格式：
CREATE DATABASE [IF NOT EXISTS] 数据库名
示例：
IF NOT EXISTS：在创建数据库之前进行判断，只有该数据库目前尚不存在时才能执行操作。
此选项可以用来避免数据库已经存在而重复创建的错误。
# 创建myschool数据库 create database myschool;
IF NOT EXISTS：在创建数据库之前进行判断，只有该数据库目前尚不存在时才能执行操作。
此选项可以用来避免数据库已经存在而重复创建的错误。
2、创建表
语法格式：
CREATE TABLE [IF NOT EXISTS] 表名 ( 字段1 数据类型 [字段属性|约束][索引][注释], …… 字段n 数据类型 [字段属性|约束][索引][注释] )[表类型][表字符集][注释];
示例：
#创建学生表 CREATE TABLE `student`（ `studentNo` INT(4) PRIMARY KEY, ` name` CHAR(10), ……）;
注意：
多字段使用逗号分隔 保留字用撇号括起来 单行注释：#…… 多行注释：/*……*/
（1）字段的约束及属性
主键
CREATE TABLE student（ `studentNo` INT(4) PRIMARY KEY, ……）;
注释
CREATE TABLE test ( `id` int(11) UNSIGNED COMMENT ‘编号’ )COMMENT=‘测试表’ ;
设置字符集编码
CREATE TABLE [IF NOT EXISTS] 表名（ #省略代码 ）CHARSET = 字符集名;
（2）在myschool数据库中创建学生表
所需执行的命令
create databases myschool;
use myschool;
create table student(
`studentNo` int(4) not null comment ‘学号‘ primary key,
`loginPwd` varchar(20) not null comment ‘密码‘,
`studentName` varchar(50) not null comment ‘姓名‘,
`sex` char(2) not null default ‘男‘ comment ‘性别‘,
`gradeID` int(4) unsigned comment ‘年级编号‘,
`phone` varchar(50) comment ‘电话‘,
`address` varchar(255) default ‘地址不详‘ comment ‘地址‘,
`bornDate` datetime comment ‘出生日期‘,
`email` varchar(50) comment ‘邮件账号‘,
`identityCard` varchar(18) comment ‘身份证号‘ unique key
)charset=‘utf8‘ comment=‘学生表‘;查看一下表结构
mysql> desc student; +--------------+-----------------+------+-----+----------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------+-----------------+------+-----+----------+-------+ | studentNo | int(4) | NO | PRI | NULL | | | loginPwd | varchar(20) | NO | | NULL | | | studentName | varchar(50) | NO | | NULL | | | sex | char(2) | NO | | 男 | | | gradeID | int(4) unsigned | YES | | NULL | | | phone | varchar(50) | YES | | NULL | | | address | varchar(255) | YES | | 地址不详 | | | bornDate | datetime | YES | | NULL | | | email | varchar(50) | YES | | NULL | | | identityCard | varchar(18) | YES | UNI | NULL | | +--------------+-----------------+------+-----+----------+-------+ 10 rows in set (0.00 sec)
3、查看表
（1）查看表是否存在
use myschool; show tables;
（2）查看表定义
语法格式：
use myschool; desc `student`;
示例：
use myschool; desc `student`;
4、删除表
语法格式：
drop table [if exists] 表名;
示例：
use myschool; drop table if exists `student`;
在删除表之前，先使用 if exists 语句验证表是否存在
5、删除数据库
删除数据库是将已经存在的数据库从磁盘空间上清除，清除之后，数据库中的所有数据也将除。
删除数据库语句和创建数据库的命令相似，MySQL中删除数据库的基本语法格式为：
drop database if exists 数据库名;
示例：
drop database if exists myschool;
6、上机练习
（1）myschool数据库中创建科目表(subject)
create table subject(
`subjectNo` int(4) comment ‘课程编号‘ primary key auto_increment,
`subjectName` varchar(50) comment ‘课程名称‘,
`classHour` int(4) comment ‘学时‘,
`gradeID` int(4) comment ‘年级编号‘
);查看一下表结构
mysql> desc subject; +-------------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+-------------+------+-----+---------+----------------+ | subjectNo | int(4) | NO | PRI | NULL | auto_increment | | subjectName | varchar(50) | YES | | NULL | | | classHour | int(4) | YES | | NULL | | | gradeID | int(4) | YES | | NULL | | +-------------+-------------+------+-----+---------+----------------+ 4 rows in set (0.00 sec)
（2）myschool数据库中创建成绩表（result）
create table result(
`studentNo` int(4) comment ‘学号‘ not null,
`subjectNo` int(4) comment ‘课程编号‘ not null,
`examDate` datetime(0) comment ‘考试日期‘ not null,
`studentResult` int(4) comment ‘考试成绩‘ not null
);查看一下表结构
mysql> desc result; +---------------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------------+----------+------+-----+---------+-------+ | studentNo | int(4) | NO | | NULL | | | subjectNo | int(4) | NO | | NULL | | | examDate | datetime | NO | | NULL | | | studentResult | int(4) | NO | | NULL | | +---------------+----------+------+-----+---------+-------+ 4 rows in set (0.00 sec)
二、MySQL的存储引擎
1、存储引擎简介
数据库存储引擎是数据库底层软件组件，数据库管理系统（DBMS）使用数据引擎进行创建、查询、更
新和删除数据操作。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能。使用不同的存
储引擎，还可以获得特定的功能。
现在许多不同的数据库管理系统都支持多种不同的数据引擎。MySQL的核心就是存储引擎。
2、存储引擎的类型
mysql有多种存储引擎，它们分别为：
MyISAM InnoDB MERGE MEMORY EXAMPLE FEDERATED ARCHIVE CSV BLACKHOLE
3、存储引擎的主要区别
（1）MyISAM 存储引擎特点
- MySQL 5.5 之前使用 MyISAM 引擎，MySQL 5.5 之后使用 InnoDB 引擎
- MyISAM 引擎读取速度较快，占用资源相对较少，不支持事务，不支持外键约束，但支持全文索引
- 读写互相阻塞，也就是说读数据的时候你就不能写数据，写数据的时候你就不能读数据
- MyISAM 引擎只能缓存索引，而不能缓存数据
（2）InnoDB 存储引擎特点
- 事务型数据库的首选引擎，支持事务安全表，支持行锁定和外键，MySQL5.5.5 版本之后，InnoDB作为默认存储引擎
- 具有提交、回滚和崩溃恢复能力的事务安全存储引擎，能处理巨大数据量，性能及效率高，完全支持外键完整性约束
- 具有非常高效的缓存特性，能缓存索引也能缓存数据，对硬件要求比较高
- 使用InnoDB时，将在MySQL数据目录下创建一个名为ibdata1的10MB大小的自动扩展数据?文件，以及两个名为 ib_logfile0 和 ib_logfile1 的 5MB ?大?小的日志?文件
（3）Memory 存储引擎特点
- Memory存储引擎将表中的数据存储到内存中，为查询和引用其他表数据提供快速访问
- Memory存储引擎执行 HASH 和 BTREE 索引，不支持 BLOB 和 TEXT 列，支持AUTO_INCREMENT列和对可包含 NULL 值得列的索引
- 当不再需要 Memory 表的内容时，要释放被 Memory 表使用的内存，应该执行DELETE FROM 或
TRUNCATE TABLE ，或者删除整个表
4、存储引擎适用场合
（1）MyISAM 适?用场景
- 不需要事务支持的业务，例如：转账就不行
- 适用于读数据比较多的业务，不适用于读写频繁的业务
- 并发相对较低、数据修改相对较少的业务
- 硬件资源比较差的机器可以考虑使用 MyISAM 引擎
（2）InnoDB 适?用场景
- 需要事务?持的业务、?并发的业务
- 数据更新较为频繁的场景，?如：BBS、SNS、微博等
- 数据?致性要求较?的业务，?如：充值转账、银?卡转账
（3）总结
使用MyISAM: 不需事务，空间小，以查询访问为主 使用InnoDB: 多删除、更新操作，安全性高，事务处理及并发控制
5、查看当前默认存储引擎
mysql> show variables like ‘%storage_engine‘; +----------------------------------+--------+ | Variable_name | Value | +----------------------------------+--------+ | default_storage_engine | InnoDB | | default_tmp_storage_engine | InnoDB | | internal_tmp_disk_storage_engine | InnoDB | +----------------------------------+--------+ 3 rows in set, 1 warning (0.02 sec)
6、修改默认存储引擎
（1）MySQL 5.5
修改my.ini配置文件
default_storage_engine=InnoDB
（2）MySQL 5.7
最简单的方法，就是通过命令直接修改表的存储引擎，如下所示：
alter table 表名 ENGINE = 引擎名;
示例：
ALTER TABLE student ENGINE = InnoDB;
7、设置表的存储引擎
语法格式：
CREATE TABLE 表名( #省略代码 )ENGINE=存储引擎;
示例：
CREATE TABLE `myisam` ( id INT(4) )ENGINE=MyISAM;
三、MySQL补充知识
在mysql中，每个数据库最多可创建20亿个表，一个表允许定义1024列，每行的最大长度为8092字节（不包括?本和图像类型的长度）。
当表中定义有varchar、nvarchar或varbinary类型列时，如果向表中插入的数据行超过8092字节时，将导致语句失败，并产生错误信息。
SQL Server对每个表中行的数量没有直接限制，但它受数据库存储空间的限制。每个数据库的最大空间1048516TB，所以一个表可用的最大空间为1048516TB减去数据库类系统表和其它数据库对象所占用的空间。理论上无限大，就看你硬盘够不够大，大多数情况先是你的硬盘不够。