Basic statements of MySQL data definition language DDL

MySQL DDL statements

What is DDL, DML.

DDL is data definition language, which is the operation on the database and table level, such as CREATE, ALTER, and DROP. DML is a data manipulation language, which means adding, deleting, modifying and checking data in a table, such as SELECT, UPDATE, INSERT and DELETE.

Assume that there is a database lian_xi with tables user and orders;

1.1 Log in to the MySQL environment

In the cmd window, enter mysql -u root -p to log in to the MySQL environment

1.2 View the database

Show databases;

1.3 Using the Database

Use database name;

Use lian_xi;

1.4 Create a database

Create database database name;

Create database lian_xi;

1.5 Delete the database

Drop database database name;

Drop database lian_xi;

1.6 Create Table

Create table table name(
Column Name 1 Type (Length) [Constraint],
Column name 2 type (length) [constraint],
…
);
CREATE TABLE user(
id INT AUTO_INCREMENT PRIMARY KEY COMMENT 'User id (primary key)',
username VARCHAR(50) COMMENT 'User name',
age CHAR(3) COMMENT 'User age'
);

Notice:

CREATE TABLE orders( 
	`id` INT UNSIGNED AUTO_INCREMENT, 
	`orders_title` VARCHAR(100) NOT NULL, 
	`orders_price` DOUBLE NOT NULL, 
	`create_date` DATE, 
	PRIMARY KEY ( `id` ) 
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

If you don't want the field to be NULL, you can set the field's attribute to NOT NULL. If the data entered into the field is NULL when operating the database, an error will be reported.
AUTO_INCREMENT defines a column as an auto-increment attribute, generally used for primary keys, and the value will automatically increase by 1. The PRIMARY KEY keyword is used to define a column as a primary key.
You can define a primary key using multiple columns, separated by commas. ENGINE sets the storage engine, CHARSET sets the encoding.

Int type: with length, does not affect the access value. Even if the set value exceeds the length range, it can be stored. If it does not reach the set length, it will be automatically filled with spaces to the set length.
Char type: immutable characters. The set length is the specified length. The maximum length that the current field can automatically store. If the length is exceeded, an error will be reported. If the specified length is not reached, it will be filled with spaces.
Varchar type: variable string. The set length also specifies the maximum length that the current field can store. If the length is exceeded, an error will be reported. If the specified length is not reached, spaces will not be used to fill the field. The actual length is the same.

Table constraints

Not null constraint, unique constraint, default constraint, primary key constraint, foreign key constraint
Foreign key check constraint (currently not supported by MySQL, but supported by Oracle)

1.7 Delete Table

Drop table table name;

Drop table user;

1.8 Modify the table and rename the table name

Rename table original table name to new table name;

Rename table user to user_table;

Add a new column to the user table: add a column hobby, (comment is followed by 'comment' or 'description'. It needs to be enclosed in single quotes);

Alter table user add column hobby varchar(100) comment 'hobby' not null;
ALTER TABLE user_table ADD COLUMN hobby VARCHAR(100) NOT NULL COMMENT 'Hobby';

Add a comment to the table user_table [Personal Information Table], the comment needs to be enclosed in single quotes

alter table user_table comment = 'Personal information table';

Delete columns Delete the user_table hobby column (Danger: Do not delete it at will)

alter table user_table drop column hobby;

Modify column name
Alter table table name change column name new column name type;

Alter table user change name username varchar(55);

Modify column type
Alter table table name change column name column name type;

Alter table user change name name char(4);

Modify column type
Alter table table name modify column name new type;

Alter table user modify name char(4);

1.9 Index Adding an index

Add an index to the primary key id of the user table;
UNIQUE is the index type. The replaceable index types are:
UNIQUE|FULLTEXT|SPATIAL,
Other index types supported by MySQL are primary key, non-unique index (default)

create unique index ind_user_id on user(id);

Delete index Delete the ind_user_id index of table user;

drop index ind_user_id on user;

For other MySQL articles, please see the following links

MySQL DDL statements

MySQL CRUD statements

MySQL Aggregate Functions

MySQL multi-table query

END…

This is the end of this article about the basic statements of MySQL data definition language DDL. For more relevant MySQL data definition language DDL content, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!