MySQL入门学习：DDL和DML

一、基本操作-DDL

​ DDL，即Data Definition Language，数据定义语言，内容主要包括：

（一）对数据库的操作

​ 对数据库的操作包括以下内容：

功能	SQL
查看所有的数据库	show databases；
创建数据库	create database [if not exists] mydb1 [charset=utf8]
切换 (选择要操作的) 数据库	use mydb1；
删除数据库	drop database [if exists] mydb1；
修改数据库编码	alter database mydb1 character set utf8;

（二）对表结构的操作

1、表创建语句

create table [if not exists]表名(
  字段名1 类型[(宽度)] [约束条件] [comment '字段说明'],
  字段名2 类型[(宽度)] [约束条件] [comment '字段说明'],
  字段名3 类型[(宽度)] [约束条件] [comment '字段说明']
)[表的一些设置];

2、数据类型

​ 数据类型是指在创建表的时候为表中字段指定数据类型，只有数据符合类型要求才能存储起来，使用数据类型的原则是够用就行，尽量使用取值范围小的，而不用大的，这样可以更多的节省存储空间。

​ MySQL中常用数据类型：

​ 1）数值类型

类型	大小	范围（有符号）	范围（无符号）	用途
TINYINT	1 byte	(-128，127)	(0，255)	小整数值
SMALLINT	2 bytes	(-32 768，32 767)	(0，65 535)	大整数值
MEDIUMINT	3 bytes	(-8 388 608，8 388 607)	(0，16 777 215)	大整数值
INT或INTEGER	4 bytes	(-2 147 483 648，2 147 483 647)	(0，4 294 967 295)	大整数值
BIGINT	8 bytes	(-9,223,372,036,854,775,808，9 223 372 036 854 775 807)	(0，18 446 744 073 709 551 615)	极大整数值
FLOAT	4 bytes	(-3.402 823 466 E+38，3.402 823 466 351 E+38)	0，(1.175 494 351 E-38，3.402 823 466 E+38)	单精度 浮点数值
DOUBLE	8 bytes	(-1.797 693 134 862 315 7 E+308，1.797 693 134 862 315 7 E+308)	0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	双精度 浮点数值
DECIMAL		依赖于M和D的值	依赖于M和D的值	小数值

​ 2）日期和时间类型

类型	大小(bytes)	范围	格式	用途
DATE	3	1000-01-01/9999-12-31	YYYY-MM-DD	日期值
TIME	3	‘-838:59:59’/‘838:59:59’	HH:MM:SS	时间值或持续时间
YEAR	1	1901/2155	YYYY	年份值
DATETIME	8	1000-01-01 00:00:00/9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	混合日期和时间值
TIMESTAMP	4	1970-01-01 00:00:00/2038 结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日 凌晨 03:14:07	YYYYMMDD HHMMSS	混合日期和时间值，时间戳

​ 3）字符串类型

类型	大小	用途
CHAR	0-255 bytes	定长字符串
VARCHAR	0-65535 bytes	变长字符串
TINYBLOB	0-255 bytes	不超过 255 个字符的二进制字符串
TINYTEXT	0-255 bytes	短文本字符串
BLOB	0-65 535 bytes	二进制形式的长文本数据
TEXT	0-65 535 bytes	长文本数据
MEDIUMBLOB	0-16 777 215 bytes	二进制形式的中等长度文本数据
MEDIUMTEXT	0-16 777 215 bytes	中等长度文本数据
LONGBLOB	0-4 294 967 295 bytes	二进制形式的极大文本数据
LONGTEXT	0-4 294 967 295 bytes	极大文本数据

​ datetime和timestamp的异同点：

​ 相同点：datetime和timestamp都可以表示 YYYY-MM-DD HH:MM:SS 这种年月日时分秒格式的数据。

​ 不同点：a.存储范围不同——datetime的存储范围是 1000-01-01 00:00:00.000000 到 9999-12-31 23:59:59.999999，而timestamp的范围是 1970-01-01 00:00:01.000000到 2038-01-19 03:14:07.999999(准备的来讲应该是UTC范围)；

​ b.时区相关——datetime存储与时区无关（准备来说是datetime只支持一个时区，就是存储时当前服务器的时区），而timestamp存储的是与时区有关。MySQL在存储TIMESTAMP时，会先将时间从当前服务器的时区转换为UTC（世界协调时）以进行存储，然后查询时从UTC转换为当前时区以进行返回。也就是说使用timestamp进行存储的时间返回的时候会随着数据库的时区而发生改变。而datetime的存储则与时区无关，数据是什么就存储什么，也就返回什么。

3、其他操作

功能	SQL
查看当前数据库的所有表名称	show tables;
查看指定某个表的创建语句	show create table 表名；
查看表结构	desc 表名
删除表	drop table 表名

（三）修改表结构

• 表中增加新列：

  alter table 表名 add 列名 类型(长度) [约束]
  

• 删除表中的指定列：

  alter table 表名 drop 列名
  

• 修改列名和类型：

  alter table 表名 change 旧列名 新列名 类型(长度) 约束
  

• 修改表名：

  rename table 表名 to 新表名
  

二、基本操作-DML

​ DML，即Data Manipulation Language，数据操作语言，内容包括：

（一）插入insert

insert into 表 (列名1,列名2,列名3...) values (值1,值2,值3...)； //向表中插入某些
insert into 表 values (值1,值2,值3...);     //向表中插入所有列

（二）删除delete

delete from 表名 [where 条件];
truncate table 表名
truncate 表名

（三）更新update

update 表名 set 字段名=值,字段名=值...;
update 表名 set 字段名=值,字段名=值... where 条件;

三、Mysql的约束

（一）简介

​ 在设计表的时候，为了保证表中数据的完整性、有效性，比如用户表有些列的值（手机号）不能为空，有些列的值（身份证号）不能重复。

​ MySQL中常用的约束有以下几类：

• 主键约束(primary key) PK
• 自增长约束(auto_increment)
• 非空约束(not null)
• 唯一性约束(unique)
• 默认约束(default)
• 零填充约束(zerofill)
• 外键约束(foreign key) FK

（二）主键约束

主键约束的特点：

• MySQL主键约束是一个列或者多个列的组合，其值能唯一地标识表中的每一行,方便在RDBMS中尽快的找到某一行。
• 主键约束相当于 唯一约束 + 非空约束 的组合，主键约束列不允许重复，也不允许出现空值。
• 每个表最多只允许一个主键
• 主键约束的关键字是：primary key
• 当创建主键的约束时，系统默认会在所在的列和列组合上建立对应的唯一索引。

（三）单列主键

创建主键的语法：

语法1：

create table 表名(
   ...
   <字段名> <数据类型> primary key 
   ...
)

语法2：

create table 表名(
   ...
   [constraint <约束名>] primary key [字段名]
);

（四）联合主键

联合主键也就是一张表中的多个字段共同构成一个主键；

语法：

create table 表名(
   ...
   primary key （字段1，字段2，…,字段n)
);

（五）通过修改表结构添加主键

语法：

create table 表名(
   ...
);
alter table <表名> add primary key（字段列表);

删除主键语法：

alter table <数据表名> drop primary key;

（六）自增长约束

1、基本内容

​ 在 MySQL 中，当主键定义为自增长后，这个主键的值就不再需要用户输入数据了，而由数据库系统根据定义自动赋值。每增加一条记录，主键会自动以相同的步长进行增长，通过给字段添加 auto_increment 属性来实现主键自增长。

语法：

字段名 数据类型 auto_increment

自增长约束的特点：

• 默认情况下，auto_increment的初始值是 1，每新增一条记录，字段值自动加 1。
• 一个表中只能有一个字段使用 auto_increment约束，且该字段必须有唯一索引，以避免序号重复（即为主键或主键的一部分）。
• auto_increment约束的字段必须具备 NOT NULL 属性。
• auto_increment约束的字段只能是整数类型（TINYINT、SMALLINT、INT、BIGINT 等。
• auto_increment约束字段的最大值受该字段的数据类型约束，如果达到上限，auto_increment就会失效。

2、指定自增字段初始值

​ 如果第一条记录设置了该字段的初始值，那么新增加的记录就从这个初始值开始自增。

方式1，创建表时指定：

create table 表名 ( 
  字段1 int primary key auto_increment, 
  字段2 varchar(20)
)auto_increment=100;

方式2，创建表之后指定：

create table 表名 ( 
  字段1 int primary key auto_increment, 
  字段2 varchar(20)
);
alter table 表名 auto_increment=100;

3、delete和truncate在删除后自增列的变化

• delete数据之后自动增长从断点开始
• truncate数据之后自动增长从默认起始值开始

（七）非空约束

​ MySQL 非空约束（not null）指字段的值不能为空。对于使用了非空约束的字段，如果用户在添加数据时没有指定值，数据库系统就会报错。

语法：

方式1：<字段名><数据类型> not null;
方式2：alter table 表名 modify 字段 类型 not null;

删除非空约束：

alter table 表名 modify 字段 类型 

（八）唯一约束

​ 唯一约束（Unique Key）是指所有记录中字段的值不能重复出现。例如，为 id 字段加上唯一性约束后，每条记录的 id 值都是唯一的，不能出现重复的情况。

语法：

方式1：<字段名> <数据类型> unique
方式2： alter table 表名 add constraint 约束名 unique(列)

删除唯一约束的语法：

alter table <表名> drop index <唯一约束名>;

（九）默认约束

​ MySQL 默认值约束用来指定某列的默认值。

语法：

方式1： <字段名> <数据类型> default <默认值>;
方式2: alter table 表名 modify 列名 类型 default 默认值; 

删除默认约束：

alter table <表名> modify column <字段名> <类型> default null;