数据库的基本操作
创建数据库
SHOW DATABASES;
创建数据库是在系统磁盘上划分一块区域用于数据的存储和管理，如果管理员在设置权限的时候为用户创建了数据库，就可以直接使用，否则需要自己创建数据库:
CREATE DATABASE 数据库名;
数据库创建好之后，可以查看数据库的定义:
SHOW CREATE DATABASE 数据库名;
删除数据库
DROP DATABASE 数据库名;
数据表的基本操作
创建数据表
CREATE TABLE 表名
(
字段名1 数据类型 列级别约束条件 默认值,
字段名2 数据类型 列级别约束条件 默认值,
...
表级别约束条件
);
主键约束
主键又称主码，是表中一列或多列的组合。
主键约束(Primary Key Constraint)要求主键列的数据存在且唯一。主键能够唯一的标识表中的一条记录，可以结合外键来定义不同数据表之间的关系，并且可以加快数据库查询的速度。
单字段主键
- 在定义列的同时指定主键
CREATE TABLE 表名
(
字段名1 数据类型 PRIMARY KEY,
字段名2 数据类型,
...
);
- 在定义完所有列之后指定主键
CREATE TABLE 表名
(
字段名1 数据类型,
字段名2 数据类型,
...,
PRIMARY KEY(字段名)
);
多字段联合主键
CREATE TABLE 表名
(
字段名1 数据类型,
字段名2 数据类型,
...,
PRIMARY KEY(字段名i,字段名j,...)
);
外键约束
外键约束用来在两个表的数据之间建立链接,它可以是一列或者多列。一个表可以有一个或多个外键。外键对应的是参照完整性，一个表的外键可以为空值，若不为空值，则每一个外键值必须等于另外一个表中主键的某个值。
外键：首先是表中的一个字段，它可以不是本表的主键，但对应另一个表的主键。外键的主要作用是保证数据引用的完整性，定义外键后，不允许删除在另一个表中具有关联关系的行。外键的作用是保持数据的一致性、完整性。
主表(父表)：对于两个具有关联关系的表而言，相关联字段中主键所在的那个表即是主表。
从表(子表)：对于两个具有关联关系的表而言，相关联字段中外键所在的那个表即是从表。
CREATE TABLE 从表
(
字段名1 数据类型,
字段名2 数据类型,
...
CONSTRAINT 外键名 FOREIGN KEY(字段名i,字段名k,...) REFERENCES 主表名(主键列)
);
删除表的外键约束
ALTER TABLE 表名 DROP FOREIGN KEY 外键约束名;
非空约束
非空约束(Not Null Constraint)指字段的值不能为空。对于使用了非空约束的字段，如果用户在添加数据时没有指定值，数据库就会报错。
CREATE TABLE 表名
(
字段名1 数据类型 not null,
字段名2 数据类型,
...
);
唯一约束
唯一约束(Unique Constraint)要求该列唯一，允许为空，但是仅能出现一个空值。唯一约束可以确保一列或者多列不出现重复值，多列时多列同时相同判定为相同。
CREATE TABLE 表名
(
字段名1 数据类型,
字段名2 数据类型,
...,
CONSTRAINT 约束名 UNIQUE(字段名i,字段名j,...)
);
UNIQUE和PRIMARY KEY的区别：
一个表中可以有多个字段声明为UNIQUE,但只能有一个PRIMARY KEY声明；声明为PRIMARY KEY的列不允许有空值，但声明为UNIQUE的字段允许空值存在。
默认约束
默认约束(Default Constraint)指定某列的默认值。如果插入一条新的记录时没有为这个值赋值，则自动补充为默认值。
CREATE TABLE 表名
(
字段名1 数据类型,
字段名2 数据类型,
...,
字段名i 数据类型 DEFAULT 默认值,
...
);
设置表的属性值自动增加
在数据库中，可以通过设置属性值自动增加来实现系统自动生成字段的主键值。一个表只能有一个字段使用AUTO_INCREMENT约束，且该字段必须为主键的一部分。AUTO_INCREMENT约束的字段可以是任何整数类型(TINYINT、SMALLIN、INT、BIGINT等)。
CREATE TABLE 表名
(
id INT PRIMARY KEY AUTO_INCREAMENT,
字段名2 数据类型,
...
);
查看数据表结构
DESCRIBE 表名;
DESC 表名;
输出结果释义:
| 列名 | 含义 |
|---|---|
| NULL | 表示该列是否可以存储NULL值 |
| Key | 表示该列是否已编制索引。PRI表示该列是表主键的一部分;UNI表示该列是UNIQUE索引的一部分;MUL表示在列中某个给定值允许出现多次 |
| Default | 表示该列是否有默认值，如果有的话值是多少 |
| Extra | 表示可以获取的与给定列有关的附加信息，例如AUTO_INCREMENT等 |
查看表详细结构
SHOW CREATE TABLE 表名;
表的修改
修改表名
ALTER TABLE 旧表名 RENAME TO 新表名;
其中TO为可选参数，使用与否不影响结果。
修改字段的数据类型
ALTER TABLE 表名 MODIFY 字段名 数据类型;
修改字段名
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 新数据类型;
添加字段
ALTER TABLE 表名 ADD 新字段名 数据类型 约束条件 FIRST|AFTER已存在的字段名;
其中FIRST为可选参数，作用是将新添加的字段设置为表的第一个字段；AFTER为可选参数，作用是将新添加的字段添加到指定已存在的字段名的后面。
删除字段
ALTER TABLE 表名 DROP 字段名;
修改字段的排列位置
ALTER TABLE 表名 MODIFY 字段名1 数据类型 FIRST|AFTER 字段名2;
字段名1指要修改该位置的字段；数据类型指字段名1的数据类型；FIRST为可选参数，可将字段名1修改为表的第一个字段；AFTER 字段名2，可将字段名1放到字段名2的后面。
更改表的存储引擎
ALTER TABLE 表名 ENGINE=存储引擎名;
MySQL支持的存储引擎
| 引擎名 | 是否支持 |
|---|---|
| FEDERATED | 否 |
| MRG MYISAM | 是 |
| MyISAM | 是 |
| BLACKHOLE | 是 |
| CSV | 是 |
| MEMORY | 是 |
| ARCHIVE | 是 |
| InnoDB | 是，默认的存储引擎 |
| PERFORMANCE_SCHEMA | 是 |
删除数据表
删除没有被关联的表
DROP TABLE IF EXISTS 表1,表2,..,表n;
参数IF EXISTS用于在删除前判断被删除的表是否存在，加上参数后，在删除表时，如果表不存在，SQL语句可以顺利执行，但是会发出警告。
删除被其他表关联的主表
数据表存在外键关联的情况下，如果直接删除父表，结果会显示失败，原因是直接删除将破坏表的参照完整性。如果必须删除，可先删除子表再删除父表。但很多情况需要保留子表，所以就需要先将关联的表的外键约束条件取消，就可以正常操作了。
ALTER TABLE 子表 DROP FOREIGN KEY 外键名;
DROP TABLE 父表;
MySQL的数据类型
MySQL支持多种数据类型:
| 数据类型 | 类型 | 内容 |
|---|---|---|
| 数值数据类型 | 整数类型 | TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT |
| 浮点小数 | FLOAT、DOUBLE | |
| 定点小数 | DECIMAL | |
| 日期/时间类型 | YEAR、TIME、DATE、DATETIME、TIMESTAMP | |
| 字符串类型 | CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM、SET |
整数类型
| 类型名称 | 说明 | 存储需求 | 有符号 | 无符号 |
|---|---|---|---|---|
| TINYINT | 很小的整数 | 1字节 | -128~127 | 0~255 |
| SMALLINT | 小的整数 | 2字节 | -32768~32767 | 0~65535 |
| MEDIUMINT | 中等大小的整数 | 3字节 | -8388608~8388607 | 0~16777215 |
| INT(INTEGER) | 普通大小的整数 | 4字节 | -2147483648~2147483647 | 0~4294967295 |
| BIGINT | 大整数 | 8字节 | -9223372036854775808~9223372036854775807 | 0~18446744073709551615 |
浮点数类型和定点数类型
| 类型名称 | 说明 | 存储需求 | 有符号 | 无符号 |
|---|---|---|---|---|
| FLOAT | 单精度浮点数 | 4字节 | -3.402823466E+38~-1.175494351E-38 | 0~3.402823466E+38 |
| DOUBLE | 双精度浮点数 | 8字节 | -1.7976931348623157E+308~-2.2250738585072014E-308 | 0和2.2250738585072014E-308~-1.7976931348623157E+308 |
| DECIMAL(M,D) | 压缩的“严格”定点数 | M+2字节 |
DECIMAL类型不同于FLOAT和DOUBLE，DECIMAL实际是以串存放的，DECIMAL可能的最大取值范围与DOUBLE一样，但是起有效的取值范围是由M和D决定的。
日期与时间类型
| 类型名称 | 日期格式 | 日期范围 | 存储需求 | 介绍 |
|---|---|---|---|---|
| YEAR | YYYY | 1901~2155 | 1字节 | (1)以4位字符串或4位数字格式表示的YEAR，范围为'1901''2155'输入'YYYY'或YYYY均可<br>(2)以2位字符串格式表示的YEAR，范围为'00''99'，'00''69'被转换为20002069，'70''99'被转换为'1970''1999' |
| TIME | HH:MM:SS | -838:59:59~838:59:59 | 3字节 | (1)'D HH:MM:SS'格式的字符串可以以以下任何一种非严格的语法表示:'HH:MM:SS'、'HH:MM'、'D HH:MM'、'D HH'或'SS'。这里D表示日，可以取0~34之间的值，在插入数据库时，D被转换为小时保存，格式为"D*24+HH" (2)'HHMMSS'格式的、没有间隔字符的字符串或者HHMMSS格式的数值，假定是有意义的时间可以被插入，不合法的存储为00:00:00 |
| DATE | YYYY-MM-DD | 1000-01-01~9999-12-3 | 3字节 | (1)以'YY-MM-DD'或者'YYMMDD'字符串格式表示的日期，MySQL对于两位年值得规则:'0069'表示'20002069','7099'表示'19701999' (2)使用CURRENT_DATE或者NOW()插入当前系统日期 |
| DATETIME | YYYY-MM-DD HH:MM:SS | 1000-01-01 00:00:00~9999-12-31 23:59:59 | 8字节 | 同上时间2位年值转换方法 |
| TIMESTAMP | YYYY-MM-DD HH:MM:SS | 1970-01-01 00:00:01 UTC~2038-01-19 03:14:07 UTC | 4字节 | 除了与DATETIME存储字节和支持范围不同外，DATETIME存储日期数据时，按实际输入格式存储与时区无关；TIMESTAMP存储以UTC(世界标准时间)格式存储，存储时对当前时区进行转换 |
文本字符串类型
| 类型名称 | 说明 | 存储需求 |
|---|---|---|
| CHAR(M) | 固定长度非二进制字符串 | M字节，1<=M<=255 |
| VARCHAR(M) | 变长非二进制字符串 | L+1字节，在此L<=M和1<=M<=255 |
| TINYTEXT | 非常小的非二进制字符串 | L+1字节，在此L<2^8 |
| TEXT | 小的非二进制字符串 | L+2字节，在此L<2^16 |
| MEDIUMTEXT | 中等大小的非二进制字符串 | L+3字节，在此L<2^24 |
| LONGTEXT | 大的非二进制字符串 | L+4字节，在此L<2^32 |
| ENUM | 枚举类型，只能有一个枚举字符串值 | 1或2个字节，取决于枚举值的数目(最大值为65535) |
| SET | 一个设置，字符串对象可以有零个或多个SET成员 | 1、2、3、4或8字节，取决于集合成员的数量(最多64个成员) |
CHAR和VARCHAR类型
CHAR(M)为固定长度字符串，在定义时指定字符串列长。VARCHAR(M)是长度可变的字符串，M表示最大列长度。
CHAR(4)与VARCHAR(4)存储的区别
| 插入值 | CHAR(4) | 存储需求 | VARCHAR(4) | 存储需求 |
|---|---|---|---|---|
| '' | ' ' | 4字节 | '' | 1字节 |
| 'ab' | 'ab ' | 4字节 | 'ab' | 3字节 |
| 'abc' | 'abc' | 4字节 | 'abc' | 4字节 |
| 'abcd' | 'abcd' | 4字节 | 'abcd' | 5字节 |
| 'abcdef' | 'abcd' | 4字节 | 'abcd' | 5字节 |
ENUM类型
ENUM是一个字符串对象，其值为表创建时规定中枚举的一列值。
CREATE TABLE 表名
(
字段名 ENUM('值1','值2','值3',...,'值n'),
...
);
ENUM类型的取值范围
| 值 | 索引 |
|---|---|
| NULL | NULL |
| '' | 0 |
| 值1 | 1 |
| 值2 | 2 |
| 值3 | 3 |
| ... |
SET类型
SET是一个字符串对象，可以有零个或多个值，SET列最多可以有64个成员，其值为创建时规定的一列值。
CREATE TABLE 表名
(
字段1 SET('值1','值2',...,'值n')，
...
);
例：
CREATE TABLE tmp1(s SET('a','b,'c','d'));
INSERT INTO tmp1 values('a'),('a,b,a'),('c,a,d');
INSERT INTO tmp1 values('a,x,b,y');
-- 插入'a'结果为;'a,b,a'结果为'a,b';'c,a,d'结果为'a,c,d';'a,x,b,y'直接报错
二进制字符串类型
| 类型名称 | 说明 | 存储需求 |
|---|---|---|
| BIT(M) | 位字段类型 | 大约(M+7)/8字节 |
| BINARY(M) | 固定长度二进制字符串 | M字节 |
| VARBINARY(M) | 可变长度二进制字符串 | M+1字节 |
| TINYBLOB(M) | 非常小的BLOB | L+1字节，在此L<2^8 |
| BLOB(M) | 小BLOB | L+2字节，在此L<2^16 |
| MEDIUMBLOB(M) | 中等大小的BLOB | L+3字节，在此L<2^24 |
| LONGBLOB(M) | 非常大的BLOB | L+4字节，在此L<2^32 |
网友评论