一. 知识点概览:
1.引擎介绍
2.表介绍及先关操作
- 创建及数据插入
- 表内数据查询
- 表结构查询
3.mysql中的数据类型
二. 知识点详解:
1.mysql中的存储引擎:
- 概念: MySQL中的数据用各种不同的技术存储在文件或内存中, 这些技术都使用不同的存储机制, 索引技巧, 锁定水平并且最终提供广泛的不同的功能和能力.这些技术以及配套的相关功能在MySQL中被称为存储引擎.
- MySQL5.6支持的存储引擎 : InnoDB, MyISAM, MEMORY, CSV, BLACKHOLE、FEDERATED、MRG_MYISAM、ARCHIVE、PERFORMANCE_SCHEMA。其中NDB和InnoDB提供事务安全表，其他存储引擎都是非事务安全表。
- InnoDB : 用于事务处理应用程序，支持外键和行级锁。如果应用对事物的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询之外，还包括很多更新和删除操作，那么InnoDB存储引擎是比较合适的。InnoDB除了有效的降低由删除和更新导致的锁定，还可以确保事务的完整提交和回滚，对于类似计费系统或者财务系统等对数据准确要求性比较高的系统都是合适的选择。
- MyISAM : 如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不高，那么可以选择这个存储引擎。
- MEMORY : 将所有的数据保存在内存中，在需要快速定位记录和其他类似数据的环境下，可以提供极快的访问。Memory的缺陷是对表的大小有限制，虽然数据库因为异常终止的话数据可以正常恢复，但是一旦数据库关闭，存储在内存中的数据都会丢失。
- BLACKHOLE : 黑洞存储引擎, 该引擎值接收但却不保存数据. 对这种引擎的表的查询常常返回一个空集.对大规模MySQL服务器集群中, 如果一台主服务, 多台从服务,在繁忙的业务中,主服务器启动N个线程读取二进制日志发送给slave主机, 那么会有大量的IO, 如此slave主机不但不能减轻master的压力还会严重影响其性能. 此时可以采用多级复制方式解决, 设置一台中间主机, 添加一个分布式master, 配置blackhole存储引擎, 他起到一个中继作用, 他接收数据但丢弃而不存储, 他只会把master的二进制日志供下一层的slave读取.
- 存储引擎决定数据库的特性:
- 并发性: 默写应用程序比其他应用程序具有很多的颗粒级锁定要求.(行级锁)
- 事务支持: 并非所有的应用程序都需要事务, 但对的确需要事务的应用程序来说, 有着定义良好的需求, 如ACID兼容等.
- 引用完整性:通过DDL定义外键, 服务器需要强制保持关联数据的应用完整性.
- 物理存储:她包括各种各样的事项, 从表和索引的总的页大小, 到存储数据所需要的格式, 到物理磁盘.
- 索引支持:不同的应用程序倾向于采用不同的索引策略, 每一种存储引擎通常有自己的编制索引方法, 但某些索引方法对几乎所有的存储引擎来说是共同的.
-
内存高速缓冲: 与其他应用程序相比，不同的应用程序对某些内存高速缓冲策略的响应更好，因此，尽管某些内存高速缓冲对所有存储引擎来说是共同的（如用于用户连接的高速缓冲，MySQL的高速查询高速缓冲等），其他高速缓冲策略仅当使用特殊的存储引擎时才唯一定义。
-
性能帮助:包括针对并行操作的多I/O线程，线程并发性，数据库检查点，成批插入处理等。
- 其他目标特性:可能包括对地理空间操作的支持，对特定数据处理操作的安全限制等。
- MySQL中与存储引擎相关的SQL语句:
# 查看当前默认存储引擎
show variable like "default_storage_engine";
# 查询当前数据库支持的存储引擎
show engines \G;
# 建表是制定存储引擎
create table tablename(...) ENGINE=...(存储引擎名)
# 对已建好的表进行存储引擎的修改
alter table tablename engine = ...(存储引擎名)
# 在配置文件中制定默认的存储引擎
[ mysql ]
default-storage-engines=INNODB
2.表(table)概念及相关操作
- 概念: 数据库中的表就相当于一个文件, 表内形成的记录就是所谓的数据.表中一行内容称为一条记录.
- 创建表(create table...)并在表中插入数据(insert)
create table tablename(
字段名1,类型[(宽度), 约束条件]
字段名2,类型[(宽度), 约束条件]
字段名3,类型[(宽度), 约束条件]
);
# 注意:
# 在同一张表中, 字段名是不能相同的
# 宽度与约束条件为可选参数
# 字段名和类型是必须项
# 给表中一条记录的特定项插入值
insert into tablename(items...) values (v1, v2, ...);
# 给表中一条记录的每一项插入值
insert into tablename values(v1, v2, ...); # 值得个数必须与项的个数相对应
# 给表一次插入多条记录:
insert into tablename values (v1, v2, ...), (v1, v2, ...);
- 查看所有的库, 查看库中所有的表, 查询表中的数据, 查看表结构:
#查看库
show databases;
# 查看库中的所有表
show tables # 查看表前应该先进入库中, 使用" use databasename; "命令
# 查询表中的数据
select items from tablename where conditions; # 在tablename中查询条件符合conditions的items项
# 查看表结构
# 查看表的字段信息
# desccrib tablename;
# desc tablename;
# 查看表的更全信息
# show create table tablename ;
- 综合示例: 红色为命令, 绿色为结果
# 创建数据库
mysql > create database testdb;
Query OK, 1 row affected (0.00 sec)
# 查看当前账户中的库
mysql > show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| tast |
| test |
| testdb |
+--------------------+
6 rows in set (0.00 sec)
# 进入数据库 & 创建表
mysql > use testdb;
Database changed
mysql > create table testtb(id int, name char(5), sex enum("male","female"), job char(10), phone char(11));
Query OK, 0 rows affected (0.01 sec)
# 为表插入数据
mysql > insert into testtb values(1, '轩少', 'male', 'stu', '11122233344');
Query OK, 1 row affected (0.00 sec)
# 查看数据库中的表
mysql > show tables;
+------------------+
| Tables_in_testdb |
+------------------+
| testtb |
+------------------+
1 row in set (0.00 sec)
# 查询表中的数据
mysql > select name,job from testtb where id=1;
+--------+------+
| name | job |
+--------+------+
| 轩少 | stu |
+--------+------+
1 row in set (0.00 sec)
#查看表字段信息
mysql > desc testtb;
+-------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | char(5) | YES | | NULL | |
| sex | enum('male','female') | YES | | NULL | |
| job | char(10) | YES | | NULL | |
| phone | char(11) | YES | | NULL | |
+-------+-----------------------+------+-----+---------+-------+
5 rows in set (0.01 sec)
# 查看表更全的信息
mysql > show create table testtb;
+--------+--------------------------------------------------------------------------------------------+
| Table | Create Table |
+--------+--------------------------------------------------------------------------------------------+
| testtb | CREATE TABLE `testtb` (
`id` int(11) DEFAULT NULL,
`name` char(5) DEFAULT NULL,
`sex` enum('male','female') DEFAULT NULL,
`job` char(10) DEFAULT NULL,
`phone` char(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+--------+--------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
3.MySQL支持的数据类型:
- 数值类型
- 日期时间类型
- 字符串类型
- enum类型和set类型
1.数值类型(着重记以下类型)
|
类型
|
大小
|
范围(有符号)
|
范围(无符号)
|
用途
|
|
int
|
4字节
|
(-2 147 483 648，2 147 483 647)
(10位)
|
(0，4 294 967 295)
(10位)
|
大整数值
|
| bigint |
8字节
|
(-9 233 372 036 854 775 808，9 223 372 036 854 775 807)
(19位)
|
(0，18 446 744 073 709 551 615)
(20位)
|
极大整数
|
|
float
|
4字节
|
(255,30), 该浮点数共有255位, 其中小数部分最多有30位, 整部部分最少有225位.
|
单精度浮点数
|
|
|
double
|
8字节
|
(255,30), 该浮点数共有255位, 其中小数部分最多有30位, 整部部分最少有225位.
|
双精度浮点数
|
|
|
decimal
|
有关参数M与D的值
|
依赖M和D的值
|
小数值
|
|
注意:
- 在进行数据插入时, int(5)中的5只是一个显示位数, 并不影响该值得存储,依然符合上表中的范围规则
- 当int类型数据,赋予的值比int类型的最大范围还大, 则mysql会将int的范围的最大值存入表中.
- double的精度比float的精度要高, 如果小数位数较多, 使用decimal是最精确的.
- decimal默认为10位正整数.
2.日期类型
|
类型
|
范围
|
格式
|
备注
|
|
date
|
1000-01-01 / 9999-12-31
|
YYYY-MM-DD | |
|
time
|
'-838:59:59'/'838:59:59'
|
HH:MM:SS
|
|
|
year
|
1901/2155
|
YYYY
|
|
|
datetime
|
1000-01-01 00:00:00/9999-12-31 23:59:59
|
YYYY-MM-DD HH:MM:SS
|
|
|
timestamp
|
1970-01-01 00:00:00/2038
|
YYYYMMDD HHMM
|
默认插入当前时间,并随update语句更新 |
3.字符串类型
| 类型 | 大小 | 用途 | 备注 |
| char | 0-255字节 | 定长字符串 | 定长, 可声明其长度, 存取速度快, 但存在一定的浪费空间问题 |
| varchar | 0-65535字节 | 边长字符串 | 边长, 节约空间, 但存取速度相对char要慢 |
4.enum类型和set类型:
ENUM中文名称叫枚举类型，它的值范围需要在创建表时通过枚举方式显示。ENUM只允许从值集合中选取单个值，而不能一次取多个值。
SET和ENUM非常相似，也是一个字符串对象，里面可以包含0-64个成员。根据成员的不同，存储上也有所不同。set类型可以允许值集合中任意选择1或多个元素进行组合。对超出范围的内容将不允许注入，而对重复的值将进行自动去重。
mysql> create table t10 (name char(20),gender enum('female','male'));
Query OK, 0 rows affected (0.01 sec)
# 选择enum('female','male')中的一项作为gender的值，可以正常插入
mysql> insert into t10 values ('nezha','male');
Query OK, 1 row affected (0.00 sec)
# 不能同时插入'male,female'两个值，也不能插入不属于'male,female'的值
mysql> insert into t10 values ('nezha','male,female');
ERROR 1265 (01000): Data truncated for column 'gender' at row 1
mysql> create table t11 (name char(20),hobby set('抽烟','喝酒','烫头','翻车'));
Query OK, 0 rows affected (0.01 sec)
# 可以任意选择set('抽烟','喝酒','烫头','翻车')中的项，并自带去重功能
mysql> insert into t11 values ('yuan','烫头,喝酒,烫头');
Query OK, 1 row affected (0.01 sec)
mysql> select * from t11;
+------+---------------+
| name | hobby |
+------+---------------+
| yuan | 喝酒,烫头 |
+------+---------------+
row in set (0.00 sec)
# 不能选择不属于set('抽烟','喝酒','烫头','翻车')中的项，
mysql> insert into t11 values ('alex','烫头,翻车,看妹子');
ERROR 1265 (01000): Data truncated for column 'hobby' at row 1
一.知识点概述:
1.表结构操作
2.约束条件
二. 知识点详解:
1. 修改表结构:
语法：
1. 修改表名
ALTER TABLE 表名
RENAME 新表名;
2. 增加字段
ALTER TABLE 表名
ADD 字段名 数据类型 [完整性约束条件…],
ADD 字段名 数据类型 [完整性约束条件…];
3. 删除字段
ALTER TABLE 表名
DROP 字段名;
4. 修改字段
ALTER TABLE 表名
MODIFY 字段名 数据类型 [完整性约束条件…];
ALTER TABLE 表名
CHANGE 旧字段名 新字段名 旧数据类型 [完整性约束条件…];
ALTER TABLE 表名
CHANGE 旧字段名 新字段名 新数据类型 [完整性约束条件…];
5.修改字段排列顺序/在增加的时候指定字段位置
ALTER TABLE 表名
ADD 字段名 数据类型 [完整性约束条件…] FIRST;
ALTER TABLE 表名
ADD 字段名 数据类型 [完整性约束条件…] AFTER 字段名;
ALTER TABLE 表名
CHANGE 字段名 旧字段名 新字段名 新数据类型 [完整性约束条件…] FIRST;
ALTER TABLE 表名
MODIFY 字段名 数据类型 [完整性约束条件…] AFTER 字段名;
2.MySQL表的完整性约束
- 2.1 概念 : 为了防止不符合规范的数据进入数据库, 在用户对数据进行插入, 修改, 删除等操作时,DBMS自动按照一定的约束条件对数据进行监测, 使不符合规范的数据不能进入数据库, 以确保数据库中存储的数据正确, 有效, 相容.
- 2.2 约束条件种类 :
- not null : 非空约束, 指定某列不能为空
- unique : 唯一约束, 指定某列或者几列组合不能重复
- primary : 主键, 指定该列的值可以唯一地标识该列记录
- foreign key : 外键, 指定该行记录从属主表中的一条记录, 主要用于参照完整性
3.表完整性约束详解:
- 3.1 null 约束:
- null : 可空
- not null : 不可空
- 3.2 default : 设置默认值, 在创建列时指定默认值, 如果未输入值则自动为该列添设置的默认值.
- 3.3 unique : 唯一约束, 指定某列或者几列组合不能重复.
- unique的设置有两种方法
- unique 与 not null 的连用表示唯一且不能为空
- 3.4 primary key : 主键为了保证表中的每一条数据的该字段都是表格中的唯一值.
- 主键可以包含一个字段或多个字段. 当主键包含多个栏位时, 称为组合键, 也可以叫联合主键.
- 主键可以在新建表格是设定, 或是以改变现有的表格架构的方式设定
- 主键必须唯一, 主键值非空; 可以是单一字段, 也可以是多字段组合
- 自增属性:auto_increment
- 3.5 foreign key : 多表, 即多个表信息相互关联
# 创建一个数据库
mysql > create database `mydb`;
Query OK, 1 row affected (0.00 sec)
# 创建一个表
mysql > use mydb;
Database changed
mysql > create table mytable(id int primary key auto_increment, name char(12) not null);
Query OK, 0 rows affected (0.02 sec)
# 修改该表的名字
mysql > alter table mytable rename mytb;
Query OK, 0 rows affected (0.01 sec)
mysql> show tables;
+----------------+
| Tables_in_mydb |
+----------------+
| mytb |
+----------------+
1 row in set (0.00 sec)
# 为该表增加一个字段
mysql > alter table mytb add age int(3) not null;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc mytb;
+-------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | char(12) | NO | | NULL | |
| age | int(3) | NO | | NULL | |
+-------+----------+------+-----+---------+----------------+
3 rows in set (0.01 sec)
# 删除一个字段
mysql> alter table mytb drop age;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
# 一次增加两个字段
mysql> alter table mytb add course char(12),add start_time date;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc mytb;
+------------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+----------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | char(12) | NO | | NULL | |
| course | char(12) | YES | | NULL | |
| start_time | date | YES | | NULL | |
+------------+----------+------+-----+---------+----------------+
4 rows in set (0.01 sec)
#修改id字段名为number
mysql> alter table mytb change id number int(11);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
#修改course字段名为class, 修改其长度为15, 且非空
mysql> alter table mytb change course class char(15) not null;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc mytb;
+------------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+----------+------+-----+---------+-------+
| number | int(11) | NO | PRI | 0 | |
| name | char(12) | NO | | NULL | |
| class | char(15) | NO | | NULL | |
| start_time | date | YES | | NULL | |
+------------+----------+------+-----+---------+-------+
4 rows in set (0.01 sec)
# 修改start_time字段为字符串类型数据
mysql> alter table mytb modify start_time char(15);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc mytb;
+------------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+----------+------+-----+---------+-------+
| number | int(11) | NO | PRI | 0 | |
| name | char(12) | NO | | NULL | |
| class | char(15) | NO | | NULL | |
| start_time | char(15) | YES | | NULL | |
+------------+----------+------+-----+---------+-------+
4 rows in set (0.01 sec)
