Data definition

The main function of DDL is to define database objects (such as database, data table, view, index, etc.).

DATABASE (DATABASE)

--Create database
CREATE DATABASE test;

--Delete database
DROP DATABASE test;

--Select database
USE test;

Data TABLE (TABLE)

--Create data table
--Normal creation
CREATE TABLE user (
  id int(10) unsigned NOT NULL COMMENT 'Id',
  username varchar(64) NOT NULL DEFAULT 'default' COMMENT 'user name',
  password varchar(64) NOT NULL DEFAULT 'default' COMMENT 'password',
  email varchar(64) NOT NULL DEFAULT 'default' COMMENT 'mailbox'
) COMMENT='User table';
--Create a new table from an existing table
CREATE TABLE vip_user AS
SELECT * FROM user;

--Delete data table
DROP TABLE user;

--Modify data table
--Add column
ALTER TABLE user
ADD age int(3);
--Delete column
ALTER TABLE user
DROP COLUMN age;
--Modify column
ALTER TABLE `user`
MODIFY COLUMN age tinyint;
--Add primary key
ALTER TABLE user
ADD PRIMARY KEY (id);
--Delete primary key
ALTER TABLE user
DROP PRIMARY KEY;

VIEW (VIEW)

Definition:

• A view is a visual table based on the result set of an SQL statement.

• A view is a virtual table. It does not contain data, so it cannot be indexed. The operation on the view is the same as that on the ordinary table.

  effect:

• Simplify complex SQL operations, such as complex joins;

• Use only part of the data of the actual table;

• Ensure the security of data by only giving users access to the view;

• Change the data format and presentation.

--Create view
CREATE VIEW top_10_user_view AS
SELECT id, username
FROM user
WHERE id < 10;

--Delete view
DROP VIEW top_10_user_view;

INDEX (INDEX)

Function:

• Through the index, you can query data more quickly and efficiently.
• The user cannot see the indexes. They can only be used to speed up the query.

Note: updating a table with an index takes more time than updating a table without an index, because the index itself also needs to be updated. Therefore, it is ideal to create indexes only on columns (and tables) that are often searched.

Unique index: a unique index indicates that each index value of this index only corresponds to a unique data record.

--Create index
CREATE INDEX user_index
ON user (id);

--Create unique index
CREATE UNIQUE INDEX user_index
ON user (id);

--Delete index
ALTER TABLE user
DROP INDEX user_index;

constraint

SQL constraints are used to specify data rules in tables.

• If there is a data behavior that violates the constraint, the behavior will be terminated by the constraint.

• Constraints can be specified when the table is created (through the CREATE TABLE statement) or after the table is created (through the ALTER TABLE statement).

  Constraint type:

• NOT NULL - indicates that a column cannot store NULL values.

• UNIQUE - ensure that each row of a column must have a UNIQUE value.

• Primary key - a combination of not null and UNIQUE. Ensuring that a column (or a combination of two columns and multiple columns) has a UNIQUE identification helps to find a specific record in the table more easily and quickly.

• FOREIGN KEY - ensures referential integrity that data in one table matches values in another table.

• CHECK - ensures that the values in the column meet the specified criteria.

• DEFAULT - specifies the DEFAULT value when no value is assigned to the column.

--Use constraints when creating tables:
CREATE TABLE Users (
  Id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Self increasing Id',
  Username VARCHAR(64) NOT NULL UNIQUE DEFAULT 'default' COMMENT 'user name',
  Password VARCHAR(64) NOT NULL DEFAULT 'default' COMMENT 'password',
  Email VARCHAR(64) NOT NULL DEFAULT 'default' COMMENT 'e-mail address',
  Enabled TINYINT(4) DEFAULT NULL COMMENT 'Is it valid',
  PRIMARY KEY (Id)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COMMENT='User table';