MySQL data definition language (DDL)

Time:2021-8-30

Write in front

  • In this paper[content]On behalf of ah, optional, you can write or not.

Introduction to the basic functions of SQL language

SQL is a structured query language with the following functions:

  • Data definition language (DDL): full name: data definition language
  • Data manipulation language (DML): full name: data manipulation language
  • Data query language (DQL): full name: data query language
  • Data control language (DCL): full name: data control language
  • Transaction control language (TCL): full name: transaction control language
  • ……

Purpose of data definition language

DDLIt is mainly the operation of database objects (database, table, view and index). Common commands are as follows:

establish modify Destroy
create alter drop

Operation statement of database

Show all current libraries

--Show Libraries 
show databases;

Create Library

--Create Library
--Create database [if not exists] database name [charset = utf8];
--An error will be reported if the creation is repeated. You can add if not exists
create database if not exists student;

Destruction Library

--Destruction Library
--Drop database [if exists] database name;
--If you don't know whether the database exists, remember to add if exists
drop database if exists student;

Use library

--Use library
--Use database name;
--After creating a database, you must first use the database before operating on the table.
use student;

other

--View the current library
select database();

--Modify database name
Rename database old name to new name;

Operation of data table

View which tables are in the current library

--View which tables are in the current library
show tables;

Create table

--Format
--Note: table names and field names should be enclosed by ` (back quotation marks) as far as possible 
Crite table [if not exists] ` table name`(
	`Field name ` field type [attribute] [index] [comment],
    `Field name ` field type [attribute] [index] [comment],
    ......
    `Field name ` field type [attribute] [index] [comment]
)[table search engine] [character encoding] [comments];

Simple example:

create table if not exists `table`(
	`sid` int,
    `sname` varchar(20),
    `age` int
)charset=utf8;

Note: since the default engine is InnoDB, this table can be created without writing. However, charset = utf8 should be added, especially when inputting Chinese in the CMD black window,
If you don’t write this sentence, the following errors will appear

ERROR 1366 (HY000): Incorrect string value: '\xD5\xC5' for column 'sname' at row 1

View table structure and create table statement

--View table structure
--Desc table name;
desc student;

--View table creation statement
--Show create table name;
show create table stu;

Modify table name

--Rename table old name to new name;
rename table student to stu;

Modify table structure

Modify table structure to includeAdd a new field to the tableModify a field in a tableDelete a field in the table

  • Add a new field to the table usingaddkeyword

    1. The default is append, that is, add a new field in the last column
    --Alter table table name add field name field type;
    alter table `stu` add `cid` int;
    1. Add a new field in the first place, usingfirstkeyword
    --Alter table table name add field name field type first;
    alter table `stu` add `cname` varchar(20) first;
  • To modify a field in a table, usechangeormodifykeyword

    1. Modify the field name usingchangekeyword
    --Alter table name change old field name new field name field type;
    --Modify the name of the field age to sage
    alter table `stu` change `age` `sage` int;
    1. Modify the field type, you can usechange, you can also usemodify
    --Modify the data type of sname field from varchar (20) to varchar (50)
    --There are two ways
    --Alter table table name change field name field name field name field type;
    alter table `stu` change `sname` `sname` varchar(50);
    
    --Alter table name modify field name field type;
    alter table `stu` modify `sname` varchar(50);
    1. Modify the field position, which can be used togetherfirstafterkeyword
    --Place the sname field after sage. You can use the following two methods:
    alter table `stu` change `sname` `sname` varchar(50) after `sage`;
    alter table `stu` modify `sname` varchar(50) after `sage`;
    
    --Place the SID field first
    alter table `stu` change `sid` `sid` int first;
    alter table `stu` modify `sid` int first;

    Modify cannot directly modify the field name. You can use modify in other places where change can be used.

  • To delete a field in a table, usedropkeyword

    --Alter table name DROP field name;
    
    --Delete the CID field
    alter table `stu` drop `cid`;

Empty table

Truncate deletes only the data but not the table structure

--Truncate table name;
truncate table `stu`;

Destruction form

--Drop table name;
drop table `stu`;

Note: with the general table structure and data, do not easily modify the table structure, add, delete and modify columns

Recommended Today

Java Engineer Interview Questions

The content covers: Java, mybatis, zookeeper, Dubbo, elasticsearch, memcached, redis, mysql, spring, spring boot, springcloud, rabbitmq, Kafka, Linux, etcMybatis interview questions1. What is mybatis?1. Mybatis is a semi ORM (object relational mapping) framework. It encapsulates JDBC internally. During development, you only need to pay attention to the SQL statement itself, and you don’t need to […]