Default Values

Default values can be assigned when a table is created using the DEFAULT keyword followed by the default value:

mysql> CREATE TABLE employees (Name CHAR(10) NOT NULL, PIN INT(5) DEFAULT NULL, Privilege varchar(10) DEFAULT 'User');
Query OK, 0 rows affected (0.35 sec)

 

Example

Insert some limited data:

mysql> INSERT INTO employees (Name, PIN) VALUES ('Tom', 3078), ('Alison', 1252), ('Ian', 4876);
Query OK, 3 rows affected (0.05 sec)
Records: 3 Duplicates: 0 Warnings: 0

 

Insert some even more limited data:

mysql> INSERT INTO employees (Name) VALUES ('Sue');
Query OK, 1 row affected (0.04 sec)

 

View the table:

mysql> select * from employees;
+--------+------+-----------+
| Name   | PIN  | Privilege |
+--------+------+-----------+
| Tom    | 3078 | User      |
| Alison | 1252 | User      |
| Ian    | 4876 | User      |
| Sue    | NULL | User      |
+--------+------+-----------+
4 rows in set (0.00 sec)

 

Insert full record:

mysql> INSERT INTO employees (Name, PIN, Privilege) VALUES ('Richard', 2375, 'Admin');
Query OK, 1 row affected (0.05 sec)

 

View the table:

mysql> select * from employees;
+---------+------+-----------+
| Name    | PIN  | Privilege |
+---------+------+-----------+
| Tom     | 3078 | User      |
| Alison  | 1252 | User      |
| Ian     | 4876 | User      |
| Sue     | NULL | User      |
| Richard | 1252 | Admin     |
+---------+------+-----------+
5 rows in set (0.00 sec)

Leave a Reply