What is data definition language (DDL) in MySQL series?

If you are just learning about MySQL, please read the following articles before you read this article. Some knowledge may be a little difficult for you to master, but please believe me, according to the learning process I provide, you can read it again and again, so that you won't know where to start when you don't know some knowledge at the end.

MySQL detailed installation tutorial
MySQL complete uninstall tutorial
How to get started with MySQL? "
Do you really understand the principle of charset=utf8? "
Details of MySQL data types

1. Introduction to the basic functions of SQL

SQL is a structured query language with the following functions:

  • Data Definition Language: the full name is Data Definition Language, referred to as DDL;
  • Data Manipulation Language: the full name is Data Manipulation Language, DML upon completion;
  • Data Control Language: the full name is Data Control Language, DCL upon completion;

The most important one is data manipulation language (DML), which contains our common functions (add, delete, change, query). As a data analyst, we only need to know how to use data definition language (DDL) and data control language (DCL). Today, I will start with DDL language and take you to learn.

2. The purpose of data definition language.

  • It mainly deals with the operation of database objects (database, table, view, index).

DDL is commonly named as follows:

establish modify Destruction
create alter drop

3. Creation and destruction of database

-- Creating databases: Creating student database
create database if not exists student;
-- Destroy database(Less use)
drop database if exists student;

-- After the database is created, the database must be used before the operation on the table.
use student;

4. Operation of database table (all demonstrations take student table as an example)

1) Create table

1)Creating tables: Creating student surface
-- Field information to be described when creating a table structure
create table student(
     sid int,
     sname varchar(20),
     age int
)charset=utf8;

2)"Complete table creation statement"It should be written like this
create table student(
     sid int,
     sname varchar(20),
     age int
)engine=InnoDB default charset=utf8;
//Note: due to the defaultenginenamelyInnoDB,This can be left blank when you see the table. Because for you who are learning, use this
//The default engine is enough. howevercharset=utf8 This is better to add, especially in CMD When entering Chinese in the black window,
//If you do not write this sentence, you will get the following errors:
ERROR 1366 (HY000): Incorrect string value: '\xD5\xC5' for column 'sname' at row 1

View the SQL statement of the complete table creation as follows:

2) Modification table

① Modify table name
-- Table name student Change to stu. 
rename table student to stu;
-- Modify the database name.
rename database Old name to New name;
② Modify table structure
  • Modifying the table structure includes adding a new field to the table, modifying a field in the table, and deleting a field in the table;
I add a new field to the table
"Add fields: to student Table, add a new field."
1)The default is append, which means adding new fields in the last column.
alter table student add sex varchar(20);

2)Add a new field first, usingfirstkeyword.
alter table student add classid int first;

3)Add a new field at the specified location usingafterkeyword(Add a new field after the specified field). 
alter table student add birthday date after age;

"View table structure: it can help us understand table structure information"
desc student;
Ⅱ modify a field in the table
1)"Modify field name": change
-- Modify field age The name of sage. 
-- modify Field name cannot be modified directly, others can be used change You can use modify. 
alter table student change age sage int;

2)"Modify field type": Both can be used change,You can also usemodify. 
"It can also be modified varchar(m)Middle one m Length of". 
-- modify sname The data type of the field is determined by varchar(20)by varchar(50). 
-- There are two ways to do this:
alter table student change sname sname varchar(50);
alter table student modify sname varchar(50);

3)"Modify field location": Can be used togetherfirst,afterkeyword.
-- take sname Fields, placing to age Back. There are two ways to do this:
alter table student change sname sname varchar(50) after age;
alter table student modify sname varchar(50) after age;
Ⅲ delete a field in the table
-- delete classid This field
alter table student drop classid;
③ Clear table: clear all data in the table.
  • truncate only deletes data but not table structure;
  • Note that the difference between truncate and delete is used to delete data in the table. What's the difference? You can view it yourself.
truncate table stu;

3) Destruction form

drop table stu;

  
  

   

Focus on WeChat official account "beauty of data analysis and statistics", and return to the background of "entering the group" to pull you into the group communication.

Tags: Database MySQL SQL less

Posted on Tue, 16 Jun 2020 02:03:50 -0400 by rmelino