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

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:

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)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:

-- Table name student Change to stu. rename table student to stu; -- Modify the database name. rename database Old name to New name;

• 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;

"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;

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 classid This field alter table student drop classid;

• 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;

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.