当前位置：网站首页>Tutorial on principles and applications of database system (034) -- data integrity of MySQL (VII): default
Tutorial on principles and applications of database system (034) -- data integrity of MySQL (VII): default
2022-07-21 01:28:00 【Rsda DBA_ WGX】
Database system principle and Application Tutorial （034）—— MySQL Data integrity （ 7、 ... and ）： The default value is （DEFAULT）
Catalog
Strictly speaking , The default value is not a constraint , Because when a column is set to the default value , It does not limit the value of this column .
If you specify a default value for a column , When a new record is inserted into the table , If no data is specified for this column , The system will automatically insert default values for this column . for example ： The political outlook in the student table is listed , Because of the vast majority of students 【 Political affiliation 】 by 【 communist youth league member 】, Then you can put 【 Political affiliation 】 The default value of the column is set to 【 communist youth league member 】, When inserting student data , If not for 【 Political affiliation 】 Column specifies data , Then automatically 【 communist youth league member 】 Insert this column .
The default value is usually used for columns with non empty constraints .
One 、 Create tables and set default values
When creating a table, you can use DEFAULT Set the default value for a column , The grammar is as follows ：
create table Table name (
< Field name > < data type > DEFAULT < The default value is >,
....
);
for example ：
（1） Create table t32, Set Columns 【gender】 The default value is 【 male 】
/* create table t32( id int primary key, name char(20) not null, gender char(1) not null default ' male ', birth datetime not null, salary int not null ); */
mysql> create table t32(
-> id int primary key,
-> name char(20) not null,
-> gender char(1) not null default ' male ',
-> birth datetime not null,
-> salary int not null
-> );
Query OK, 0 rows affected (0.03 sec)
insert data ：
-- Insert data normally
mysql> insert into t32 values(1001,' Zhang Qiang ',' male ','1989-1-22',5500);
Query OK, 1 row affected (0.00 sec)
mysql> insert into t32 values(1002,' Liu Yun ',' Woman ','1992-10-3',5200);
Query OK, 1 row affected (0.00 sec)
-- When inserting data gender Columns do not specify data values , The result is 【 male 】
mysql> insert into t32(id,name,birth,salary) values(1003,' Liu Gang ','1990-8-8',5000);
Query OK, 1 row affected (0.02 sec)
-- Insert data using default Substitute data value , The result is 【 male 】
mysql> insert into t32 values(1004,' Liu peng ',default,'1997-10-9',4500);
Query OK, 1 row affected (0.00 sec)
mysql> select * from t32;
+------+--------+--------+---------------------+--------+
| id | name | gender | birth | salary |
+------+--------+--------+---------------------+--------+
| 1001 | Zhang Qiang | male | 1989-01-22 00:00:00 | 5500 |
| 1002 | Liu Yun | Woman | 1992-10-03 00:00:00 | 5200 |
| 1003 | Liu Gang | male | 1990-08-08 00:00:00 | 5000 |
| 1004 | Liu peng | male | 1997-10-09 00:00:00 | 4500 |
+------+--------+--------+---------------------+--------+
4 rows in set (0.00 sec)
（2） Create table t33, Set Columns createtime The default value of is the current time
/* create table t33( id int primary key, name char(20) not null, gender char(1) not null, birth datetime not null, salary int not null, createtime datetime default now() ); */
mysql> create table t33(
-> id int primary key,
-> name char(20) not null,
-> gender char(1) not null,
-> birth datetime not null,
-> salary int not null,
-> createtime datetime default now()
-> );
Query OK, 0 rows affected (0.03 sec)
insert data ：
mysql> insert into t33(id,name,gender,birth,salary) values(1001,' Zhang Qiang ',' male ','1989-1-22',5500);
Query OK, 1 row affected (0.01 sec)
mysql> insert into t33(id,name,gender,birth,salary) values(1002,' Zhang Jingjing ',' Woman ','1992-6-9',5000);
Query OK, 1 row affected (0.01 sec)
mysql> insert into t33(id,name,gender,birth,salary) values(1003,' Liu tao ',' Woman ','1990-10-15',5200);
Query OK, 1 row affected (0.01 sec)
mysql> insert into t33(id,name,gender,birth,salary) values(1004,' Zheng Qiang ',' male ','1994-8-13',4800);
Query OK, 1 row affected (0.00 sec)
mysql> select * from t33;
+------+-----------+--------+---------------------+--------+---------------------+
| id | name | gender | birth | salary | createtime |
+------+-----------+--------+---------------------+--------+---------------------+
| 1001 | Zhang Qiang | male | 1989-01-22 00:00:00 | 5500 | 2022-07-18 17:24:27 |
| 1002 | Zhang Jingjing | Woman | 1992-06-09 00:00:00 | 5000 | 2022-07-18 17:24:27 |
| 1003 | Liu tao | Woman | 1990-10-15 00:00:00 | 5200 | 2022-07-18 17:24:27 |
| 1004 | Zheng Qiang | male | 1994-08-13 00:00:00 | 4800 | 2022-07-18 17:24:28 |
+------+-----------+--------+---------------------+--------+---------------------+
4 rows in set (0.00 sec)
Two 、 Specify default values for columns in the table
Specify default values for columns that already exist in the table , The syntax is as follows ：
-- Add columns and specify default values
ALTER TABLE < Table name >
ADD < Field name > < data type > DEFAULT < The default value is >;
-- Specify default values for existing columns
ALTER TABLE < Table name >
MODIFY < Field name > < data type > DEFAULT < The default value is >;
-- perhaps
ALTER TABLE < Table name > ALTER COLUMN < Field name > SET DEFAULT < The default value is >;
for example ：
（1） Definition table t41 And insert data
/* create table t41( id int primary key, name char(20) not null, gender char(1) not null, birth datetime not null, salary int not null, createtime datetime not null ); insert into t41 values(1001,' Zhang Qiang ',' male ','1989-1-22',5500,now()); insert into t41 values(1002,' Zhang Jingjing ',' Woman ','1992-6-9',5000,now()); insert into t41 values(1003,' Liu tao ',' Woman ','1990-10-15',5200,now()); insert into t41 values(1004,' Zheng Qiang ',' male ','1994-8-13',4800,now()); */
mysql> create table t41(
-> id int primary key,
-> name char(20) not null,
-> gender char(1) not null,
-> birth datetime not null,
-> salary int not null,
-> createtime datetime not null
-> );
Query OK, 0 rows affected (0.04 sec)
mysql> insert into t41 values(1001,' Zhang Qiang ',' male ','1989-1-22',5500,now());
Query OK, 1 row affected (0.06 sec)
mysql> insert into t41 values(1002,' Zhang Jingjing ',' Woman ','1992-6-9',5000,now());
Query OK, 1 row affected (0.04 sec)
mysql> insert into t41 values(1003,' Liu tao ',' Woman ','1990-10-15',5200,now());
Query OK, 1 row affected (0.00 sec)
mysql> insert into t41 values(1004,' Zheng Qiang ',' male ','1994-8-13',4800,now());
Query OK, 1 row affected (0.00 sec)
mysql> desc t41;
+------------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+----------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | char(20) | NO | | NULL | |
| gender | char(1) | NO | | NULL | |
| birth | datetime | NO | | NULL | |
| salary | int(11) | NO | | NULL | |
| createtime | datetime | NO | | NULL | |
+------------+----------+------+-----+---------+-------+
6 rows in set (0.00 sec)
mysql> select * from t41;
+------+-----------+--------+---------------------+--------+---------------------+
| id | name | gender | birth | salary | createtime |
+------+-----------+--------+---------------------+--------+---------------------+
| 1001 | Zhang Qiang | male | 1989-01-22 00:00:00 | 5500 | 2022-07-18 17:48:57 |
| 1002 | Zhang Jingjing | Woman | 1992-06-09 00:00:00 | 5000 | 2022-07-18 17:50:04 |
| 1003 | Liu tao | Woman | 1990-10-15 00:00:00 | 5200 | 2022-07-18 17:50:13 |
| 1004 | Zheng Qiang | male | 1994-08-13 00:00:00 | 4800 | 2022-07-18 17:50:19 |
+------+-----------+--------+---------------------+--------+---------------------+
4 rows in set (0.00 sec)
（2） In the table t41 Add columns nation And specify the default value as 【 The han nationality 】
mysql> alter table t41 add nation varchar(20) not null default ' The han nationality ' after birth;
Query OK, 0 rows affected (0.10 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc t41;
+------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | char(20) | NO | | NULL | |
| gender | char(1) | NO | | NULL | |
| birth | datetime | NO | | NULL | |
| nation | varchar(20) | NO | | The han nationality | |
| salary | int(11) | NO | | NULL | |
| createtime | datetime | NO | | NULL | |
+------------+-------------+------+-----+---------+-------+
7 rows in set (0.01 sec)
mysql> select * from t41;
+------+-----------+--------+---------------------+--------+--------+---------------------+
| id | name | gender | birth | nation | salary | createtime |
+------+-----------+--------+---------------------+--------+--------+---------------------+
| 1001 | Zhang Qiang | male | 1989-01-22 00:00:00 | The han nationality | 5500 | 2022-07-18 17:48:57 |
| 1002 | Zhang Jingjing | Woman | 1992-06-09 00:00:00 | The han nationality | 5000 | 2022-07-18 17:50:04 |
| 1003 | Liu tao | Woman | 1990-10-15 00:00:00 | The han nationality | 5200 | 2022-07-18 17:50:13 |
| 1004 | Zheng Qiang | male | 1994-08-13 00:00:00 | The han nationality | 4800 | 2022-07-18 17:50:19 |
+------+-----------+--------+---------------------+--------+--------+---------------------+
4 rows in set (0.00 sec)
（3） Is a table t41 Column in createtime Set the default value to now()
mysql> alter table t41 modify createtime datetime not null default now();
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc t41;
+------------+-------------+------+-----+-------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+-------------------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | char(20) | NO | | NULL | |
| gender | char(1) | NO | | NULL | |
| birth | datetime | NO | | NULL | |
| nation | varchar(20) | NO | | The han nationality | |
| salary | int(11) | NO | | NULL | |
| createtime | datetime | NO | | CURRENT_TIMESTAMP | |
+------------+-------------+------+-----+-------------------+-------+
7 rows in set (0.02 sec)
（4） Is a table t41 Column in gender Set the default value to 【 male 】
mysql> alter table t41 alter column gender set default ' male ';
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc t41;
+------------+-------------+------+-----+-------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+-------------------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | char(20) | NO | | NULL | |
| gender | char(1) | NO | | male | |
| birth | datetime | NO | | NULL | |
| nation | varchar(20) | NO | | The han nationality | |
| salary | int(11) | NO | | NULL | |
| createtime | datetime | NO | | CURRENT_TIMESTAMP | |
+------------+-------------+------+-----+-------------------+-------+
7 rows in set (0.00 sec)
3、 ... and 、 Delete the default value of a column
When the columns in a table do not need to set default values , You need to remove it from the table .
The syntax format for deleting the default value constraint is as follows ：
-- Redefine the type of column , No default key word
ALTER TABLE < Table name > MODIFY < Field name > < data type >;
-- Redefine the type of column , Add ：default null
ALTER TABLE < Table name > MODIFY < Field name > < data type > DEFAULT NULL;
-- Use alter column drop default
alter table < Table name > alter column < Field name > drop default;
（1） Delete table t41 in gender Default value of column
mysql> desc t41;
+------------+-------------+------+-----+-------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+-------------------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | char(20) | NO | | NULL | |
| gender | char(1) | NO | | male | |
| birth | datetime | NO | | NULL | |
| nation | varchar(20) | NO | | The han nationality | |
| salary | int(11) | NO | | NULL | |
| createtime | datetime | NO | | CURRENT_TIMESTAMP | |
+------------+-------------+------+-----+-------------------+-------+
7 rows in set (0.00 sec)
mysql> alter table t41 modify gender char(1) not null;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc t41;
+------------+-------------+------+-----+-------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+-------------------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | char(20) | NO | | NULL | |
| gender | char(1) | NO | | NULL | |
| birth | datetime | NO | | NULL | |
| nation | varchar(20) | NO | | The han nationality | |
| salary | int(11) | NO | | NULL | |
| createtime | datetime | NO | | CURRENT_TIMESTAMP | |
+------------+-------------+------+-----+-------------------+-------+
7 rows in set (0.01 sec)
（2） Delete table t41 in nation Default value of column
mysql> alter table t41 modify nation varchar(20) default null;
Query OK, 0 rows affected (0.10 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc t41;
+------------+-------------+------+-----+-------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+-------------------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | char(20) | NO | | NULL | |
| gender | char(1) | NO | | NULL | |
| birth | datetime | NO | | NULL | |
| nation | varchar(20) | YES | | NULL | |
| salary | int(11) | NO | | NULL | |
| createtime | datetime | NO | | CURRENT_TIMESTAMP | |
+------------+-------------+------+-----+-------------------+-------+
7 rows in set (0.00 sec)
（3） Delete table t41 in createtime Default value of column
mysql> alter table t41 alter column createtime drop default;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc t41;
+------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | char(20) | NO | | NULL | |
| gender | char(1) | NO | | NULL | |
| birth | datetime | NO | | NULL | |
| nation | varchar(20) | YES | | NULL | |
| salary | int(11) | NO | | NULL | |
| createtime | datetime | NO | | NULL | |
+------------+-------------+------+-----+---------+-------+
7 rows in set (0.00 sec)
边栏推荐
猜你喜欢
随机推荐
A survey of the theory and application of digital knowledge management
Xing No
Unity-word文档点击按钮下载
LeetCode_ 78_ subset
类和对象（上）
infraversion和superaversion
百度飞桨EasyDL助力制造企业智能化转型
数据库系统原理与应用教程（034）—— MySQL 的数据完整性（七）：默认值（DEFAULT）
数据库系统原理与应用教程（037）—— MySQL 的索引（三）：删除索引
STM32 learning ---spi
二值化神经网络权重的分布规则
Frontier and application of data security technology from the perspective of compliance
数据库系统原理与应用教程（026）—— MySQL 修改表中数据（二）：删（delete from）
Difficulties and solutions of project management in complete electrical equipment manufacturing enterprises
Solution of univariate cubic equation of Luogu p1024 [noip2001 improvement group]
3 种缓存更新策略是怎样的？
Isecops intelligent security operation technology system framework
What are the three cache update strategies?
Iptables prevent nmap port scanning
洛谷 P1024 [NOIP2001 提高组] 一元三次方程求解