【MySQL】DDL语句详解：列类型、列约束及自增列_Unit03

文档中心

文章目录

- - 一、计算机如何存储字符
  - - 1. 如何存储英文字符
    - 2. 如何存储中文字符
    - 3. 解决mysql中文乱码
  - 二、列类型
  - - 1. 数值型——引号可加可不加
    - 2. 日期时间型——必须加引号
    - 3. 字符串型——必须加引号
  - 三、列约束
  - - 1. 主键约束——primary key
    - 2. 非空约束——NOT NULL
    - 3. 唯一约束——unique
    - 4. 默认值约束——default
    - 5. 检查约束
    - 6. 外键约束
  - 四、自增列

一、计算机如何存储字符

1. 如何存储英文字符

ASCII 总共有128个，对英文字母及其符号进行了编码。如a b c为97 98 99
Latin-1 总共有256个，对欧洲字符进行了编码，兼容ASCII

2. 如何存储中文字符

GB2312 对常用的6千多汉字进行了编码，兼容ASCII
GBK 对两万多汉字进行了编码，兼容GB2312
BIG5 台湾繁体字编码，兼容ASCII
Unicode 对世界上主流国家的常用语言进行了编码，兼容ASCII，具体包括3种存储方案 UTF-8 UTF-16 UTF-32

扩展: 🔗

GBK与UTF-8、UTF-16
(1) GBK编码，一个汉字占两个字节。
(2) UTF-16编码，通常汉字占两个字节，CJKV扩展B区、扩展C区、扩展D区中的汉字占四个字节（一般字符的Unicode范围是U+0000至U+FFFF，而这些扩展部分的范围大于U+20000，因而要用两个UTF-16）。
(3) UTF-8编码是变长编码，通常汉字占三个字节，扩展B区以后的汉字占四个字节。

扩展: 🔗

简单介绍下GBK和UTF-8这两种编码的区别：
(1) GBK编码：是指中国的中文字符，其它它包含了简体中文与繁体中文字符，另外还有一种字符“GB2312”，这种字符仅能存储简体中文字符。
(2) UTF-8编码：它是一种全国家通过的一种编码，如果你的网站涉及到多个国家的语言，那么建议你选择UTF-8编码。

3. 解决mysql中文乱码

MySQL默认使用latin-1编码

解决：

(1) 脚本文件另存为的编码为UTF-8

(2) 客户端连接服务器端的编码为UTF-8

# UTF后不能加-SET  NAMES  UTF8;

(3) 服务器端创建数据库使用的编码为UTF-8

# UTF后不能加-CREATE  DATABASE  xz  CHARSET=UTF8;

二、列类型

在创建数据表的时候，指定的列可以存储的数据类型

CREATE  TABLE  n1(   nid  列类型);

1. 数值型——引号可加可不加

tinyint 微整型，占1个字节，范围-128~127

(正数范围0~127 负数范围-1~-128)

🌱Tips:
最大数7个1 最小数7个0 （最大数为8个1最小数为8个0，由于要留出一位做符号位，即存储正负号，所以最大数为7个1最小数为7个0）

smallint 小整型，占2个字节，范围-32768~32767

(正数范围0~32767 负数范围-1~-32768)

int 整型，占4个字节，范围-2147483648~2147483647

(正数范围0~2147483647 负数范围-1~-2147483648)

Bigint 大整型，占8个字节，范围很大

float 单精度浮点型，占4个字节，范围比int大的多，以牺牲小数点后的数字为代价，精度会受影响，最大值3.4E38，即3.4*10的38次方

double 双精度浮点型，占8个字节，范围比bigint大的多，精度也会受到影响

decimal (M,D) 定点小数，小数点位置不会发生变化，占8个字节，M代表总的有效位数，D代表小数点后的有效位数

boolean/bool 布尔型，通常只有两个值，分别是true或false，往往存储只有两个值的数据，例如性别，是否在线… ，在使用的时候会转为tinyint，true转为1，false转为0

true和false使用的时候是关键字，不能加引号

2. 日期时间型——必须加引号

日期时间型，必须加引号，否则会被视为减法运算2020-08-03为2009，而定义的日期型会错乱为0000-00-00

date 日期型 2020-08-03

time 时间型 15:20:30

datetime 日期时间型 2020-08-03 15:20:30

3. 字符串型——必须加引号

varchar(M) 变长字符串，几乎不会产生空间浪费，数据操作速度相对慢，M的最大值是65535，常用于存储长度变化的字符串，例如文章的标题，内容，一个人的姓名，家庭住址…

char(M) 定长字符串，可能产生空间浪费，数据操作速度比较快，M的最大值是255，常用于存储长度固定的数据，例如一个人的电话号码，身份证号码…

text(M) 大型变长字符串， M的最大值是2G

字符个数	char(5)	Varchar(5)
a	a\0\0\0\0（需补充4个空格）	a\0（只需加一个空格）
ab	ab\0\0\0	ab\0
abcde	abcde	abcde
周一见	周一见\0\0	周一见\0

Mysql里空格用\0表示

题外话：
1TB MB KB Byte(字节） Bit(位)
以上每一级进制为1024

位：计算机只能存1或0，即所谓的二进制数

1Byte=8Bit(位)

2进制由1或者0组成
1 2 3 4 5 十进制数
1 10 11 100 101 二进制数（逢1）

浮点型float、double，如下：
123456.789E-1
12345.6789
1234.56789E1
123.456789E2

定点小数，如：3.14

选择合理的列类型,，例如：

CREATE  TABLE  t1(Id INT, # 比如京东的商品号，经过多年的运营，商品号会很长age TINYINT,phone  CHAR(11),sex BOOLEANprice DECIMAL(7,2), # 99999.99ctime data);

ex：编写脚本文件02_xuezi.sql，先丢弃再创建数据库xuezi，设置编码为UTF-8，进入数据库，创建保存商品数据的表laptop，包含有编号lid，标题title，价格price，库存量stockCount，上架时间shelfTime，是否为首页推荐isIndex；插入若干条数据，在交互模式下查询数据。

#设置客户端连接服务器端编码SET NAMES UTF8;#丢弃数据库，如果存在DROP DATABASE IF EXISTS xuezi;#创建新的数据库，设置存储编码CREATE DATABASE xuezi CHARSET=UTF8;#进入该数据库USE xuezi;#创建保存商品数据的表CREATE TABLE laptop(  lid INT PRIMARY KEY,  title VARCHAR(64),  price DECIMAL(7,2) NOT NULL,  #99999.99  stockCount SMALLINT,  shelfTime DATE,  isIndex BOOLEAN);#插入数据INSERT INTO laptop VALUES('4','小米Air','3199','100','2020-8-1',true);INSERT INTO laptop VALUES('2','外星人','56000','12','2020-1-1',false);INSERT INTO laptop VALUES(1,'ThinkpadE470',3199,6,'2016-10-1',NULL);INSERT INTO laptop VALUES('3','戴尔燃7000','4199','200','2019-10-1','130');

三、列约束

mysql可以对要插入的数据进行特定的验证，只有满足条件才允许插入，否则认为是非法插入，例如禁止插入重复的编号，性别只能是男或者女，工资只能是正数…

CREATE  TABLE  t1( lid  INT 列约束 );

1. 主键约束——primary key

声明了主键约束的列上禁止重复的值，一个表中只能有一个主键约束，通常加载编号列，会加快数据的查找速度，查询的时候，会按照编号从小到大排序。声明了主键约束后，主键约束禁止插入NULL。如果设置了自增列AUTO_INCREMENT，主键约束列就可以插入NULL

NULL 表示为空，在插入数据的时候，表示无法确定的值，例如无法确定一个商品的价格，是否为首页推荐…
NULL是关键字，不能加引号。

2. 非空约束——NOT NULL

声明了非空约束的列上禁止插入NULL

ex：编写脚本文件xz.sql，先丢弃再创建数据库xz，进入数据库，设置编码为UTF8；创建保存笔记本分类的表family，包含fid，分类名称fname；插入以下数据：10 联想，20 戴尔，30 小米。创建保存笔记本数据的表latop，包含lid，标题title，价格price，规格spec，详情detail，上架时间shelfTime，是否在售isOnsale，所属分类编号familyld；插入若干条数据

#设置客户端连接服务器端的编码SET NAMES UTF8;#丢弃数据库，如果存在DROP DATABASE IF EXISTS xz;#创建新的数据库，设置存储的编码CREATE DATABASE xz CHARSET=UTF8;#进入该数据库USE xz;#创建保存笔记本家族分类的表CREATE TABLE family(  fid INT PRIMARY KEY,  fname VARCHAR(8) DEFAULT '未知');#插入数据INSERT INTO family VALUES(10,'联想'),(20,'戴尔'),(30,'小米');INSERT INTO family VALUES(40,'华为');INSERT INTO family VALUES(50,NULL);INSERT INTO family VALUES(60,NULL);INSERT INTO family VALUES(70,DEFAULT);INSERT INTO family(fid) VALUES(80);#创建保存笔记本数据的表CREATE TABLE laptop(  lid INT PRIMARY KEY AUTO_INCREMENT,  title VARCHAR(64) UNIQUE NOT NULL,  price DECIMAL(7,2) DEFAULT 3000,  #99999.99  spec VARCHAR(32),  detail VARCHAR(5000),  shelfTime DATE,  isOnsale BOOLEAN,  familyId INT,  #设置familyId为外键约束，取值范围到分类表family中的fid下去寻找，如果没有出现就会报错  foreign key(familyId) references family(fid));#插入数据INSERT INTO laptop VALUES(1,'小米Air',3799,'旗舰版','详情1','2020-5-1',1,30);INSERT INTO laptop VALUES(2,'ThinkpadE470','2899','入门版','详情2','2018-1-1',0,10);INSERT INTO laptop VALUES(3,'灵越燃7000','4199','商务版','详情3','2019-12-1',1,20);INSERT INTO laptop VALUES(4,'灵越燃8000','5199','商务版2','详情4','2018-12-1',0,20);INSERT INTO laptop VALUES(5,'小新500',DEFAULT,DEFAULT,'详情5','2018-12-1',0,10);INSERT INTO laptop(lid,title) VALUES(56,'小米Pro');INSERT INTO laptop(lid,title) VALUES(NULL,'小米Pro2');INSERT INTO laptop(lid,title) VALUES(NULL,'小米Pro3');

3. 唯一约束——unique

声明了唯一约束的列上，不允许出现重复值，允许插入NULL，甚至多个NULL（因为NULL表示为空，是未知的含义）；唯一约束可能会影响查询的默认排序
练习：在笔记本表laptop中，设置标题title为唯一约束，并插入数据测试

4. 默认值约束——default

没有出现的列会应用默认值

可以使用default关键字来设置默认值，具体应用方式有两种

(1) INSERT INTO laptop VALUES(1,’小米1 ‘,DEFAULT,…);

#向表中所有的列去插入数据，可直接忽略列名，直接采用INSERT INTO 表名 VALUES(值…）

#数据表中对应字段没有赋值的，必须写上设置DEFAULT关键字

#如果表中该字段设置了DEFAULT约束，该字段对应的values值就是设置的默认值

#如果表中该字段没设置DEFAULT约束，该字段对应的values值就是默认NULL值

(2) INSERT INTO laptop(lid,title) VALUES(2,’小米2 ‘);

#指定列(字段)插入方式：没有出现的列（字段）会应用默认值（默认NULL，除非设#置了默认约束值）。对应的VALUES值不用写上DEFAULT

扩展: 🔗

向数据库中插入数据的三种方式

在数据库中，向数据库中插入数据，使用insert into关键字。在数据库中插入数据有三种方法。

1）插入单个字段的情况

INSERT INTO table_name(列名) VALUES(值);

2）插入多个字段的情况

2.1）插入两个字段以上

INSERT INTO table_name (列名1，列名2，列名3,)  VALUES(值1,值2，值3);

2.2）插入全部所有字段

INSERT INTO table_name  VALUES(值1，值2，值3); # 值要和表中的字段顺序、类型保持一致

3）使用子查询向表中插入数据

# 需要注意的是不能违反表table_name的约束条件，以及需要和table_name的字段一致，或者少于它，但是不能多于它INSERT INTO table_name 子查询select语句;

扩展: 🔗

关于默认值DEFAULT设置

1）设置默认值

使用 DEFAULT 关键字设置默认值约束，具体的语法规则如下：
DEFAULT ;

2）修改表中的默认值

DEFAULT ;

3）删除默认值约束

删除，就是把默认值设为空就好了
DEFAULT ;

5. 检查约束

也称为自定义约束，但mysql不支持，会影响（降低）数据的插入速度，可能会对服务器造成较大的压力

CREATE TABLE student(score TINYINT  CHECK(score>=0  AND  score<=100));

6. 外键约束

声明了外键约束的列，取值范围到另一个表的主键列中去取，允许插入NULL。外键列和对应的主键列两者列类型要保持一致。

外键约束通常用来建立两个表之间的关联

Tips：

1）必须是另一个表格的主键；

2）列类型必须和另一个表格的一样；

3）需要加了逗号写到下一行。

声明了外键约束的列，取值必须在另一个表的主键列上出现过，两者的列类型要保持一致，允许使用NULL或者多个NULL

使用方法： FOREIGN KEY (列名) REFERENCES 数据表（主键列）

FOREIGN  KEY (外键列)  REFERENCES  另一个表(主键列)

在这里插入图片描述

四、自增列

AUTO INCREMENT 自动增长，如果设置了自增列，在插入数据的时候，只需要设置为NULL，就会获取当前的最大值然后加1插入。

🌱Tips：

1）自增列只能添加在整数类型的主键列上；

2）自增列允许手动赋值；

3）手动赋值后中间跳跃有缺失，还会按最大值继续增长；

4）使用方便，可以直接给上面写NULL,数字自动加1；

自动增长，假如一个列声明了自增列，无需手动赋值，直接赋值为NULL，会获取当前的最大值，然后加1插入

ex：编写脚本文件01_tedu.sql，先丢弃再创建数据库tedu，设置编码为UTF-8，进入数据库，创建保存部门数据的表dept，包含部门编号did(主键、自增列)，部门名称dname(唯一约束)，插入以下数据：
10 研发部 20 市场部 30 运营部 40 测试部
创建保存员工数据的表emp，包含编号eid(主键，自增列)，员工姓名ename，性别sex（默认值约束 1），生日birthday，工资salary(非空约束)，所属部门编号deptId(外键约束)，插入若干条数据。

#设置客户端连接服务器端的编码SET NAMES UTF8;#丢弃数据库，如果存在DROP DATABASE IF EXISTS tedu;#创建新的数据库，设置存储编码CREATE DATABASE tedu CHARSET=UTF8;#进入该数据库USE tedu;#创建保存部门数据的表CREATE TABLE dept(  did INT PRIMARY KEY AUTO_INCREMENT,  dname VARCHAR(8) UNIQUE);#插入数据INSERT INTO dept VALUES(10,'研发部');INSERT INTO dept VALUES(20,'市场部');INSERT INTO dept VALUES(30,'运营部');INSERT INTO dept VALUES(40,'测试部');#创建保存员工数据的表CREATE TABLE emp(  eid INT PRIMARY KEY AUTO_INCREMENT,  ename VARCHAR(8),  sex BOOLEAN DEFAULT 1,  birthday DATE,  salary DECIMAL(7,2) NOT NULL,  #99999.99  deptId INT,  FOREIGN KEY(deptId) REFERENCES dept(did));#插入数据INSERT INTO emp VALUES(NULL,'Tom',1,'1990-5-5',6000,20);INSERT INTO emp VALUES(NULL,'Jerry',0,'1991-8-20',7000,10);INSERT INTO emp VALUES(NULL,'David',1,'1995-10-20',3000,30);INSERT INTO emp VALUES(NULL,'Maria',0,'1992-3-20',5000,10);INSERT INTO emp VALUES(NULL,'Leo',1,'1993-12-3',8000,20);INSERT INTO emp VALUES(NULL,'Black',1,'1991-1-3',4000,10);INSERT INTO emp VALUES(NULL,'Peter',1,'1990-12-3',10000,10);INSERT INTO emp VALUES(NULL,'Franc',1,'1994-12-3',6000,30);INSERT INTO emp VALUES(NULL,'Tacy',1,'1991-12-3',9000,10);INSERT INTO emp VALUES(NULL,'Lucy',0,'1995-12-3',10000,20);INSERT INTO emp VALUES(NULL,'Jone',1,'1993-12-3',8000,30);INSERT INTO emp VALUES(NULL,'Lily',0,'1992-12-3',12000,10);INSERT INTO emp VALUES(NULL,'Lisa',0,'1989-12-3',8000,10);INSERT INTO emp VALUES(NULL,'King',1,'1988-12-3',10000,10);INSERT INTO emp VALUES(NULL,'Brown',1,'1993-12-3',22000,NULL);

回顾上一单元，你可以点击👉【MySQL】常用的SQL命令_Unit02

【MySQL】DDL语句详解：列类型、列约束及自增列_Unit03

文章目录

一、计算机如何存储字符

1. 如何存储英文字符

2. 如何存储中文字符

3. 解决mysql中文乱码

二、列类型

1. 数值型——引号可加可不加

2. 日期时间型——必须加引号

3. 字符串型——必须加引号

三、列约束

1. 主键约束——primary key

2. 非空约束——NOT NULL

3. 唯一约束——unique

4. 默认值约束——default

5. 检查约束

6. 外键约束

四、自增列

公告

标签

【MySQL】DDL语句详解：列类型、列约束及自增列_Unit03

文章目录

一、计算机如何存储字符

1. 如何存储英文字符

2. 如何存储中文字符

3. 解决mysql中文乱码

二、列类型

1. 数值型——引号可加可不加

2. 日期时间型——必须加引号

3. 字符串型——必须加引号

三、列约束

1. 主键约束——primary key

2. 非空约束——NOT NULL

3. 唯一约束——unique

4. 默认值约束——default

5. 检查约束

6. 外键约束

四、自增列

相关问题

公告

标签