4.Mysql data definition language DDL

Article directory

    • 1. Database definition syntax
      • Create database
      • Delete database
      • Use or switch
      • Modify database
    • 2. Data table definition syntax
      • Create data table
      • Delete data table
      • Modify data table
      • View and use data tables
      • Common temporary table definition syntax
      • Common view definition syntax

DDL (Data Definition Language) is a data definition language used to operate databases, data tables, data fields, etc. Commonly used statements: CREATE, ALTER, DROP. This article will introduce you to the recently commonly used definition syntax operations.

1. Database definition syntax

Take the database sql_test1 as an example to define the following

Create database

/*Syntax
create {database | schema} [if not exists] db_name [create_option];
create_option: character set [=] charset_name
Square brackets indicate optional;
Generally, the default is enough;
If there are no special circumstances, the character set must be set to the character encoding of utf8 or utf8mb4.
*/
create database sql_test1;
create database if not exists sql_test1;
create database if not exists sql_test1 character set 'utf8';
create database if not exists sql_test1 character = 'utf8';

Delete database

/*Syntax
drop {database | schema} [if exists] db_name;
*/
drop database sql_test1; -- delete database
drop database if exists sql_test1; -- Determine whether the database exists, delete it if it exists

Use or switch

use sql_test1; -- use or switch database
show create database sql_test2; -- View database creation information

Modify database

show variables like 'character%'; -- View character encoding
alter database sql_test1 character set 'gbk'; -- Modify the character set of the database

1. The database schema can be changed, add if not exists to create recommendations, and add if exists to delete recommendations;
2. The database cannot be renamed, you can delete it first and then create a new one;
3. Before operating the data in the data table, you need to specify which database to operate on. If you do not specify, you need to write the database table, such as database name.table name.

2. Data table definition syntax

Take the data table products as an example to define the following. For field types, please see the Mysql data type article.

Adding constraints to fields has the following meanings:

  • Ensure data validity
  • Ensure data integrity
  • Ensure data accuracy

Common field constraints:

  • ?Null constraint (not null): restricts the value of this column to be provided and cannot be null (null value)
  • Unique constraint (unique): In multiple pieces of data in the table, the value of this field cannot be repeated;
  • Primary key constraint (primary key): empty + unique, which can uniquely identify each piece of data in the data table;
  • Foreign key constraints (foreign key): establish relationships between different tables;

Create data table

/*Syntax
create table [if exists] table_name (
    Field 1 Field 1 type [Field constraint] [comment Field 1 comment],
    Field 2 Field 2 type [Field constraint] [comment Field 2 comment],
    Field 3 Field 3 type [Field constraint] [comment Field 3 comment],
    ...
    Field n Field n type [Field constraint] [comment Field n comment]
)[comment table comment];
*/
create table if not exists products
(
    prod_id char(10) not null comment 'product number',
    prod_name varchar(255) comment 'product name',
    prod_price decimal(8, 2) comment 'product price',
    prod_desc json comment 'Product description information'
) engine=innodb default charset=utf8
comment 'Product table';
create table products_qa1 like products; -- Create an empty table
create table products_qa2 as select * from products where 1=2;-- Create an empty table
create table products_qa3 as select * from products;-- Create table and copy data

Basic principles for creating tables:

  • Display and field names should comply with the naming convention as much as possible, and it is best to be able to ‘see the meaning of the name’;
  • The data in the table must have a unique identifier, that is, a primary key definition. Unless there are special circumstances, the primary keys can all be numbers and increment automatically;
  • The types corresponding to the fields in the table are set reasonably and the length is limited;
  • The table engine recommends using innodb, and the character encoding is required to be utf8 or utf8mb4 unless there are special circumstances;

Delete data table

/*Syntax
drop table [if exists] table_name;
*/
drop table if exists products_qa3; -- delete table
truncate table products_qa1; -- Clear the table data, which is equivalent to deleting the table and creating a new table

Modify data table

rename table products_qa1 to products_qa4; -- Rename table
alter table products_qa1 rename products_qa5; -- Rename table
/*
alter table table name add field name field type [comment field comment] [constraint] [first|after field name];
alter table table name modify field name new data type;
alter table table name change old field name new field name field type [comment comment] [constraint];
Note: modify: cannot modify the field name, change: can modify the field name.
*/
alter table products
add create_time datetime default CURRENT_TIMESTAMP comment 'Creation time' after prod_desc,
    add id int comment 'quantity' first; -- Add two fields to the table
alter table products modify id int after prod_desc; -- Modify the field position in the table
alter table products change id num int; -- modify table field name
alter table products drop id,drop create_time; -- delete multiple fields in the table

View and use data tables

show create table products; -- View create table statements
desc products; -- View table structure
select * from products limit 2; -- View data

Common temporary table definition syntax

create temporary table products_qa6 select * from products;-- Create temporary table
drop temporary table if exists products_qa6; -- Delete temporary table

The definition syntax is similar to that of an actual table. The temporary table will be automatically deleted when the session ends.

Common view definition syntax

create view products_qa7 as
select * from products;-- create view
create view products_qa7 (prod_id,prod_name,prod_price,price_discount)
as select prod_id,prod_name,prod_price,0.5*prod_price from products;-- Create calculation view
drop view if exists stu_glass;-- delete view
alter view view_t as select prod_id from products;-- modify the view
create or replace view view_t as
select prod_id,prod_name from products;--Modify the view, create or replace the view
desc view name;--View view table structure
show create view test.a; -- View view statement information
select * from information_schema.views;-- View view data

The above are some common data definition syntaxes. In fact, there are others. If you want to know more, you can go to the Mysql official website to expand.