Mysql基础-第6章列的属性

程序员文章站 2022-05-21 12:18:48

第6章列的属性目录第6章列的属性1、简单的查询语句2、简单插入语句单行插入：批量插入3、列的属性默认值NULL属性NOT NULL属性4、主键单列主键多列主键5、UNIQUE属性6、主键和UNIQUE约束的区别7、外键8、AUTO_INCREMENT属性9、列的注释10、影响展示外观的ZEROFILL属性11、一个列同时具有多个属性12、查看表结构时的列属性13、标识符的命名1、简单的查询语句SELECT...

第6章列的属性

1、简单的查询语句

1、简单的查询语句

SELECT * FROM 表名;

2、简单插入语句

MySQL插入数据的时候是以行为单位的，语法格式如下：

单行插入：

INSERT INTO 表名(列1, 列2, ...) VALUES(列1的值，列2的值, ...);

mysql> INSERT INTO first_table(first_column, second_column) VALUES(1, 'aaa');

Query OK, 1 row affected (0.00 sec)

也可以只指定部分的列，没有显式指定的列的值将被设置为NULL，NULL的意思就是此列的值尚不确定。比如这样写：

mysql> INSERT INTO first_table(first_column) VALUES(2);

Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO first_table(second_column) VALUES('ccc');

Query OK, 1 row affected (0.00 sec)

结果如下：

mysql> SELECT * FROM first_table;
+--------------+---------------+
| first_column | second_column |
+--------------+---------------+
|            1 | aaa           |
|            2 | NULL          |
|         NULL | ccc           |
+--------------+---------------+
3 rows in set (0.00 sec)

批量插入

插入一行数据就向服务器提交一个请求远没有一次把所有插入的数据提交给服务器效率高，所以MySQL为我们提供了批量插入记录的语句：

INSERT INTO 表名(列1,列2, ...) VAULES(列1的值，列2的值, ...), (列1的值，列2的值, ...), (列1的值，列2的值, ...), ...;

举个例子：

mysql> INSERT INTO first_table(first_column, second_column) VALUES(4, 'ddd'), (5, 'eee'), (6, 'fff');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM first_table;
+--------------+---------------+
| first_column | second_column |
+--------------+---------------+
|            1 | aaa           |
|            2 | NULL          |
|         NULL | ccc           |
|            4 | ddd           |
|            5 | eee           |
|            6 | fff           |
+--------------+---------------+
6 rows in set (0.01 sec)

mysql>

3、列的属性

表的每个列都有一些属性，这些属性是什么以及怎么在创建表时被定义出来？

默认值

NULL属性

书写INSERT语句插入数据时只指定部分的列，那些没有被显式指定的列，其默认值为NULL，NULL的含义是这个列的值还没有被设置。如果我们不想让默认值为NULL，而是设置成某个有意义的值，可以在定义列的时候给该列增加一个DEFAULT属性，就像这样：

列名列的类型 DEFAULT 默认值

举个例子：

mysql> CREATE TABLE first_table (
    ->     first_column INT,
    ->     second_column VARCHAR(100) DEFAULT 'abc'
    -> );
Query OK, 0 rows affected (0.02 sec)

NOT NULL属性

表的某些列必须有值，不能存放NULL，可以用这样来定义这个列：

列名列的类型 NOT NULL

举个例子：

mysql> CREATE TABLE first_table (
    ->     first_column INT NOT NULL,
    ->     second_column VARCHAR(100) DEFAULT 'abc'
    -> );

这样的话，我们就不能再往这个字段里插入NULL值了，比如这样：

mysql> INSERT INTO first_table(first_column, second_column) VALUES(NULL, 'aaa');
ERROR 1048 (23000): Column 'first_column' cannot be null

看到报了个错，提示first_column列不能存储NULL。

另外，一旦对某个列定义了NOT NULL属性，那这个列的默认值就不为NULL了。上边first_column并没有指定默认值，意味着我们在使用INSERT插入行时必须显式的指定这个列的值，而不能省略它，比如这样就会报错的：

mysql> INSERT INTO first_table(second_column) VALUES('aaa');
ERROR 1364 (HY000): Field 'first_column' doesn't have a default value

4、主键

有时候在我们的表里可以通过某个列或者某些列确定唯一的一条记录，我们就可以把这个列或者这些列称为候选键。

一个表可能有多个候选键，选择其中一个作为表的主键。

一个表最多只能有一个主键，主键的值不能重复，通过主键可以找到唯一的一条记录。如果表中有定义主键的需求，可以选用如下两种方式以指定主键：

单列主键

直接在该列后声明PRIMARY KEY。

列名数据类型 PRIMARY KEY

举个例子：把学生信息表student_info的学号列声明为主键可以这么写：

CREATE TABLE student_info (
    number INT PRIMARY KEY,
    name VARCHAR(5),
    sex ENUM('男', '女'),
    id_number CHAR(18),
    department VARCHAR(30),
    major VARCHAR(30),
    enrollment_time DATE
);

把主键的声明单独提取出来，用这样的形式声明：

PRIMARY KEY (列名1, 列名2, ...)

举个例子：

CREATE TABLE student_info (
    number INT,
    name VARCHAR(5),
    sex ENUM('男', '女'),
    id_number CHAR(18),
    department VARCHAR(30),
    major VARCHAR(30),
    enrollment_time DATE,
    PRIMARY KEY (number)
);

多列主键

以多个列的组合作为主键，必须使用单独声明的形式，

比如student_score表里的学号,科目的列组合作为主键：

CREATE TABLE student_score (
    number INT,
    subject VARCHAR(30),
    score TINYINT,
    PRIMARY KEY (number, subject)
);

在我们创建表的时候就声明了主键的话，MySQL会对我们插入的记录做校验，如果新插入记录的主键值已经在表中存在了，那就会报错。

另外，主键列默认是有NOT NULL属性，也就是必填的，如果填入NULL值会报错。

5、UNIQUE属性

对于不是主键的其他候选键，如果也想让MySQL在我们向表中插入新记录的时候帮助我们校验一下某个列或者列组合的值是否重复，那么我们可以把这个列或列组合添加一个UNIQUE属性，表明该列或者列组合的值是不允许重复的。与我们在建表语句中声明主键的方式类似，为某个列声明UNIQUE属性的方式也有两种：

如果我们想为单个列声明UNIQUE属性，可以直接在该列后填写UNIQUE或者UNIQUE KEY。

比如在学生信息表student_info中，我们不允许两条学生基本信息记录中的身份证号是一样的，

那我们可以为id_number列添加UNIQUE属性：

CREATE TABLE student_info (
    number INT PRIMARY KEY,
    name VARCHAR(5),
    sex ENUM('男', '女'),
    id_number CHAR(18) UNIQUE,
    department VARCHAR(30),
    major VARCHAR(30),
    enrollment_time DATE
);

我们也可以把UNIQUE属性的声明单独提取出来，用以下2种方式声明：

UNIQUE [约束名称] (列名1, 列名2, ...)

UNIQUE KEY [约束名称] (列名1, 列名2, ...)

为某列添加UNIQUE属性可以认为是为该表添加了一个约束，称之为UNIQUE约束。每个约束都有一个名字，主键也是一个约束，其名字是默认的PRIMARY。表中可以为不同列添加多个UNIQUE属性，也就是添加多个UNIQUE约束，每添加一个UNIQUE约束，可以给它起个名，这也是上边的约束名称的含义。约束名称是被中括号[]扩起来的，意味着写不写都可以，不写的话，MySQL自动命名。

对于多个列的组合具有UNIQUE属性的情况，必须使用这种单独声明的形式。

如果表中为某个列或者列组合定义了UNIQUE属性的话，MySQL会对我们插入的记录做校验，如果新插入记录在该列或者列组合的值已经在表中存在了，那就会报错！

6、主键和`UNIQUE`约束的区别

主键和UNIQUE约束都能保证某个列或者列组合的唯一性，但是：

一张表中只能定义一个主键，却可以定义多个UNIQUE约束！
规定：主键列不允许存放NULL，而声明了UNIQUE属性的列可以存放NULL，而且NULL可以重复地出现在多条记录中！

7、外键

插入到学生成绩表student_score中的number(学号)列中的值必须能在学生基本信息表student_info中的number列中找到，否则如果一个学号只在成绩表里出现，而在基本信息表里找不到相应的记录的话，就相当于插入了不知道是哪个学生的成绩，这显然是荒谬的。为了防止这样荒谬的情况出现，MySQL给我们提供了外键约束机制。定义外键的语法是这样的：

CONSTRAINT [外键名称] FOREIGN KEY (列1, 列2, ...) REFERENCES 父表名(父列1, 父列2, ...);

如果A表中的某个列或者某些列依赖于B表中的某个列或者某些列，那么就称A表为子表，B表为父表。子表和父表可以使用外键来关联起来，上边例子中student_score表的number列依赖于student_info的number列，所以student_info就是一个父表，student_score就是子表。我们可以在student_score的建表语句中来定义一个外键：

CREATE TABLE student_score (
    number INT,
    subject VARCHAR(30),
    score TINYINT,
    PRIMARY KEY (number, subject),
    CONSTRAINT FOREIGN KEY(number) REFERENCES student_info(number)
);

8、AUTO_INCREMENT属性

AUTO_INCREMENT翻译成中文可以理解为自动增长，简称自增。

如果一个表中的某个列的数据类型是整数类型或者浮点数类型，那么这个列可以设置AUTO_INCREMENT属性。

当我们把某个列设置了AUTO_INCREMENT属性之后，如果我们在插入新记录的时候不指定该列的值，或者将该列的值显式地指定为NULL或者0，那么新插入的记录在该列上的值就是当前该列的最大值加1后的值。我们可以用这样的语法来定义这个列：

列名列的类型 AUTO_INCREMENT

举个例子：

在first_table表里设置一个名为id的列，把这个列设置为主键，让其拥有AUTO_INCREMENT属性，可以这么写：

mysql> CREATE TABLE first_table (
    ->     id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    ->     first_column INT,
    ->     second_column VARCHAR(100) DEFAULT 'abc'
    -> );

其中 id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY 中 INT UNSIGNED 指的是无符号整数类型。

在插入新记录时可以忽略掉这个列，或者将列值显式地指定为NULL或0，但是它的值将会递增，看：

mysql> INSERT INTO first_table(first_column, second_column) VALUES(1, 'aaa')，(NULL, 1, 'aaa')，(0, 1, 'aaa');

结果如下：
mysql> SELECT * FROM first_table;
+----+--------------+---------------+
| id | first_column | second_column |
+----+--------------+---------------+
|  1 |            1 | aaa           |
|  2 |            1 | aaa           |
|  3 |            1 | aaa           |
+----+--------------+---------------+
3 rows in set (0.01 sec)

mysql>

可以看到，列id是从1开始递增的。在为列定义AUTO_INCREMENT属性的时候需要注意这几点：

一个表中最多有一个具有AUTO_INCREMENT属性的列。

具有AUTO_INCREMENT属性的列必须建立索引。主键和具有UNIQUE属性的列会自动建立索引。

拥有AUTO_INCREMENT属性的列不能再以指定DEFAULT属性来指定默认值。

一般拥有AUTO_INCREMENT属性的列都是作为主键的属性，来自动生成唯一标识一条记录的主键值。

9、列的注释

在每一个列末尾添加COMMENT语句来为列来添加注释，比方说：

CREATE TABLE first_table (
    id int UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT '自增主键',
    first_column INT COMMENT '第一列',
    second_column VARCHAR(100) DEFAULT 'abc' COMMENT '第二列'
) COMMENT '第一个表';

10、影响展示外观的ZEROFILL属性

对于无符号整数类型的列，可以在查询数据的时候在数字左边补0，想实现这个效果需要给该列加一个ZEROFILL属性（这是一个属于数据类型的属性），就像这样：

mysql> CREATE TABLE zerofill_table (
    ->     i1 INT UNSIGNED ZEROFILL,
    ->     i2 INT UNSIGNED
    -> );

我们在zerofill_table表中创建了两个无符号整数列，不同的是i1列具有ZEROFILL属性，下边我们为这个表插入一条记录：

mysql> INSERT INTO zerofill_table(i1, i2) VALUES(1, 1);
Query OK, 1 row affected (0.00 sec)


查询结果如下：
mysql> SELECT * FROM zerofill_table;
+------------+------+
| i1         | i2   |
+------------+------+
| 0000000001 |    1 |
+------------+------+
1 row in set (0.00 sec)

对于具有ZEROFILL属性的i1列，在显示的时候在数字前边补了一堆0，仔细数数发现是9个0，而没有ZEROFILL属性的i2列，

在显示的时候并没有在数字前补0。为什么i1列会补9个0呢？我们查看一下zerofill_table的表结构：

mysql> SHOW CREATE TABLE zerofill_table\G
*************************** 1. row ***************************
       Table: zerofill_table
Create Table: CREATE TABLE `zerofill_table` (
  `i1` int(10) unsigned zerofill DEFAULT NULL,
  `i2` int(10) unsigned DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.01 sec)

可以看到，其实i1和i2列的类型INT后边都加了一个(10)，这个10就是所谓的显示宽度。

显示宽度是指查询结果中显示数字长度，若具有 ZEROFILL 属性的整数列实际值的位数小于显示宽度时，会在实际值的左侧补0，补0的位数和实际值的位数相加等于显示宽度位数。我们也可以自己指定显示宽度，比方说这样：

构造zerofill_table;
mysql> CREATE TABLE zerofill_table (
    ->     i1 INT(5) UNSIGNED ZEROFILL,
    ->     i2 INT UNSIGNED
    -> );

向zerofill_table中插入数据；
mysql> INSERT INTO zerofill_table(i1, i2) VALUES(1, 1);

结果如下：
mysql> SELECT * FROM zerofill_table;
+-------+------+
| i1    | i2   |
+-------+------+
| 00001 |    1 |
+-------+------+
1 row in set (0.00 sec)

新创建的表中，i1字段的显示宽度是5，所以最后的显示结果中补了4个0。

不过在使用ZEROFILL属性时应该注意下边几点

在展示查询结果时，某列数据自动补0的条件有这几个：

该列必须是整数类型的

该列必须有UNSIGNED ZEROFILL的属性

该列的实际值的位数必须小于显示宽度

在创建表的时候，某一列声明了ZEROFILL属性但没有声明UNSIGNED属性，那MySQL会为该列自动生成UNSIGNED属性。

也就是说如果我们创建表语句是这样的：

CREATE TABLE zerofill_table (
    i1 INT ZEROFILL,
    i2 INT UNSIGNED
);

MySQL会自动帮我们为i1列加上UNSIGNED属性，也就是说MySQL现在只支持对无符号整数进行自动补0的操作。如下所示：

CREATE TABLE zerofill_table (
    i1 INT UNSIGNED ZEROFILL,
    i2 INT UNSIGNED
);

每个整数类型都会有默认的显示宽度。

比如TINYINT的默认显示宽度是4，INT的默认显示宽度是(11)...

如果加了UNSIGNED属性，则该类型的显示宽度减1，比如TINYINT UNSIGNED的显示宽度是3，INT UNSIGNED的显示宽度是10。

显示宽度并不会影响实际类型的实际存储空间。

显示宽度的作用是在于是设置查询结果的展示宽度，在整数的位数小于显示宽度的情况下起作用，而不影响该数据类型要求的存储空间以及其存储的数据范围。也就是说INT(1)和INT(10)仅仅在展示时可能有区别，在别的方面没有任何区别。比方说zerofill_table表中i1列的显示宽度是5，而数字12345678的位数是8，它照样可以被填入i1列中（超过显示宽度时按实际位数录入）。

只有列的实际值位数小于显示宽度时才会补0，实际值位数大于显示宽度时按实际位数录入。
对于没有声明ZEROFILL属性的列，显示宽度不发挥作用。

11、一个列同时具有多个属性

每个列可以同时具有多个属性，属性声明的顺序无所谓，各个属性之间用空白隔开。

有的属性是冲突的，一个列不能具有两个冲突的属性，

12、查看表结构时的列属性

上一章我们唠叨了一些可以以表格的形式展示表结构的语句，但是忽略了关于列的属性的一些列，现在我们再看一遍student_info表的结构：

mysql> DESC student_info;
+-----------------+-------------------+------+-----+---------+-------+
| Field           | Type              | Null | Key | Default | Extra |
+-----------------+-------------------+------+-----+---------+-------+
| number          | int(11)           | NO   | PRI | NULL    |       |
| name            | varchar(5)        | YES  |     | NULL    |       |
| sex             | enum('男','女')   | YES  |     | NULL    |       |
| id_number       | char(18)          | YES  | UNI | NULL    |       |
| department      | varchar(30)       | YES  |     | NULL    |       |
| major           | varchar(30)       | YES  |     | NULL    |       |
| enrollment_time | date              | YES  |     | NULL    |       |
+-----------------+-------------------+------+-----+---------+-------+
7 rows in set (0.00 sec)

可以看到：

NULL列代表该列是否可以存储NULL，值为NO时，表示不允许存储NULL，值为YES是表示可以存储NULL。
Key列存储关于所谓的键的信息，当值为PRI是PRIMARY KEY的缩写，代表主键；UNI是UNIQUE KEY的缩写，代表UNIQUE属性。
Default列代表该列的默认值。
Extra列展示一些额外的信息。比方说如果某个列具有AUTO_INCREMENT属性就会被展示在这个列里。

13、标识符的命名

像数据库名、表名、列名、约束名称或者我们之后会遇到的别的名称，这些名称统统被称为标识符。

虽然MySQL中对标识符的命名没多少限制，但是却不欢迎下边的这几种命名：

名称中全都是数字。
名称中有空白字符
名称使用了MySQL中的保留字：比方说CREATE、DATABASE、INT、DOUBLE、DROP、TABLE等等这些单词，这些单词都是供MySQL内部使用的，称之为保留字。如果你自己定义的名称用到了这些词儿也会导致歧义。比如名称create就是非法的。

虽然某些名称可能会导致歧义，但是如果你坚持要使用的话，也不是不行，你可以使用反引号``来将你定义的名称扩起来，这样MySQL的服务器就能检测到你提供的是一个名称而不是别的什么东西，比如说把上边几个非法的名称加上反引号``就变成合法的名称了：

举个例子：

CREATE TABLE `first_table` (
    `id` int UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `first_column` INT,
    `second_column` VARCHAR(100) DEFAULT 'abc'

由于MySQL是C语言实现的，所以在名称定义上还是尽量遵从C语言的规范吧，就是用小写字母、数字、下划线、美元符号等作为名称，如果有多个单词的话，各个单词之间用下划线连接起来，比如student、student_info啥的。

（转：仅供个人学习使用，不作商业用途。）

本文地址：https://blog.csdn.net/wqj_710852747/article/details/107296213

上一篇：达沃斯论坛热议人工智能铜板街以AI全面赋能投资客户

下一篇： Linux中用命令导出数据库(mysql)

Mysql基础-第6章列的属性

第6章列的属性

1、简单的查询语句

2、简单插入语句

单行插入：

批量插入

3、列的属性

默认值

NULL属性

NOT NULL属性

4、主键

单列主键

多列主键

5、UNIQUE属性

6、主键和`UNIQUE`约束的区别

7、外键

8、AUTO_INCREMENT属性

9、列的注释

10、影响展示外观的ZEROFILL属性

11、一个列同时具有多个属性

12、查看表结构时的列属性

13、标识符的命名