MySQL Tutorial

MySQL Installation and Configuration

MySQL Database Operations

Database Design

MySQL Data Types

MySQL Storage Engines

MySQL Basic Operations of Tables

MySQL Constraints

MySQL Operators

MySQL Function

MySQL Function Overview

MySQL Manipulate Table Data

MySQL View

MySQL Indexes

MySQL Stored Procedure

MySQL Trigger

MySQL Transactions

MySQL Character Set

MySQL User Management

MySQL Database Backup and Recovery

MySQL Log

MySQL Performance Optimization

MySQL DEFAULT: Default Value

The DEFAULT keyword in MySQL is used to set a default value for a column in a table. This default value is used when an INSERT statement is executed without specifying a value for this column.

Prerequisites:

A MySQL server up and running
Access to a MySQL user account with privileges to create and modify tables

Tutorial:

Connect to the MySQL server:

To start the mysql command-line client, open a terminal or command prompt, and enter:

mysql -u [username] -p

Replace [username] with your MySQL username and enter your password when prompted.

Select a database:

Select the database where you want to create your table:

USE [database_name];

Replace [database_name] with the name of your database.

Create a table with a DEFAULT value:

Suppose you want to create a users table with a status column that defaults to 'active':

CREATE TABLE users (
    id INT AUTO_INCREMENT,
    username VARCHAR(255),
    status VARCHAR(50) DEFAULT 'active',
    PRIMARY KEY (id)
);

In this example, if you don't specify a value for the status column when inserting a new user, it will be set to 'active'.

Insert data into the table:

When you insert data into the table, MySQL will use the default value for any column that is not specified in the INSERT statement:

INSERT INTO users (username) VALUES ('johndoe');

If you select this user with SELECT * FROM users;, you will see that the status for this user is 'active'.

Change the DEFAULT value for a column:

You can change the default value for a column using the ALTER TABLE statement:

ALTER TABLE users MODIFY status VARCHAR(50) DEFAULT 'inactive';

After running this statement, any new users that are inserted without a specified status will be set to 'inactive'.

Exit the MySQL command-line client:

EXIT;

By using the DEFAULT keyword in MySQL, you can ensure that your columns always have a value, even if one is not specified when inserting data. This can make your data more consistent and your queries more predictable.

Setting default values in MySQL tables:

Default values are specified for columns to provide a fallback when no explicit value is provided.

CREATE TABLE users (
  user_id INT PRIMARY KEY,
  username VARCHAR(50) DEFAULT 'guest',
  email VARCHAR(255) DEFAULT 'user@example.com',
  registration_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

How to use DEFAULT in MySQL columns:

Use DEFAULT to assign a default value to a column.

CREATE TABLE products (
  product_id INT PRIMARY KEY,
  product_name VARCHAR(100) DEFAULT 'Untitled',
  price DECIMAL(10, 2) DEFAULT 0.00
);

MySQL DEFAULT and NULL values:
- DEFAULT and NULL are distinct concepts. DEFAULT sets a value when NULL is explicitly provided.
```
CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  order_status VARCHAR(20) DEFAULT 'Pending' NOT NULL
);
```

Specifying default values for different data types in MySQL:

Default values can be specified for various data types.

CREATE TABLE preferences (
  user_id INT PRIMARY KEY,
  preferred_color VARCHAR(20) DEFAULT 'Blue',
  notification_enabled BOOLEAN DEFAULT true
);

DEFAULT values in CREATE TABLE statement in MySQL:

Specify DEFAULT values during table creation.

CREATE TABLE tasks (
  task_id INT PRIMARY KEY,
  task_name VARCHAR(100) DEFAULT 'Untitled',
  completion_status BOOLEAN DEFAULT false
);

Changing default values for existing columns in MySQL:
- Alter existing columns to change their default values.
```
ALTER TABLE your_table
MODIFY COLUMN column_name datatype DEFAULT new_default_value;
```

MySQL DEFAULT expression examples:

Use expressions as default values.

CREATE TABLE logs (
  log_id INT PRIMARY KEY,
  log_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  log_message VARCHAR(255) DEFAULT 'No message'
);

Using DEFAULT in INSERT statements in MySQL:
- DEFAULT can be used in INSERT statements to use the column's default value.
```
INSERT INTO users (user_id, username, email) VALUES (1, DEFAULT, 'newuser@example.com');
```

Setting default values for TIMESTAMP and DATETIME in MySQL:

Specify default values for temporal data types.

CREATE TABLE events (
  event_id INT PRIMARY KEY,
  event_name VARCHAR(100) DEFAULT 'Unnamed Event',
  event_date DATETIME DEFAULT CURRENT_TIMESTAMP
);

MySQL DEFAULT for auto-incremented columns:
- Auto-incremented columns typically don't need default values as they are generated automatically.
```
CREATE TABLE messages (
  message_id INT PRIMARY KEY AUTO_INCREMENT,
  sender_id INT,
  message_text VARCHAR(255) DEFAULT 'No message'
);
```

Setting default values for ENUM and SET types in MySQL:

ENUM and SET types can have default values assigned.

CREATE TABLE survey_responses (
  response_id INT PRIMARY KEY,
  user_id INT,
  rating ENUM('Poor', 'Average', 'Excellent') DEFAULT 'Average'
);

MySQL DEFAULT and foreign key constraints:

DEFAULT can be used in columns with foreign key constraints.

CREATE TABLE invoices (
  invoice_id INT PRIMARY KEY,
  customer_id INT,
  total_amount DECIMAL(10, 2) DEFAULT 0.00,
  FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

Handling DEFAULT values in UPDATE statements in MySQL:
- Update statements can include or exclude DEFAULT values.
```
UPDATE your_table
SET column_name = DEFAULT
WHERE your_condition;
```