MySQL tutorial data definition language DDL example detailed explanation

-- Create database: Create student database create database if not exists student;
-- Destroy the database (rarely used)
drop database if exists student;

-- After creating a database, you must use the database before performing operations on the table.
use student;

1) Create table: Create student table - field information that needs to be described when creating table structure create table student (
     sid int,
     sname varchar(20),
     age int
)charset=utf8;

2) The "complete table creation statement" should be written as create table student(
     sid int,
     sname varchar(20),
     age int
)engine=InnoDB default charset=utf8;
Note: Since the default engine is InnoDB, this can be omitted when viewing the table. Because for you who are learning, using this default engine is enough. But it is best to add charset=utf8, especially when entering Chinese in the CMD black window.
If you don't write this sentence, an error similar to the following will occur:
ERROR 1366 (HY000): Incorrect string value: '\xD5\xC5' for column 'sname' at row 1

-- Change the table name student to stu.
rename table student to stu;
--Change the database name.
rename database old name to new name;

"Add field: Add a new field to the student table."
1) The default is to append, that is, add a new field to the last column.
alter table student add sex varchar(20);

2) Add the new field first, using the first keyword.
alter table student add classid int first;

3) To add a new field at a specified position, use the after keyword (add a new field after the specified field).
alter table student add birthday date after age;

"View table structure: can help us understand the structural information of the table"
desc student;

1) "Modify field name": change
-- Change the name of the field age to sage.
-- modify cannot directly modify the field name. You can use modify in other places where change can be used.
alter table student change age sage int;

2) "Change field type": You can use either change or modify.
"You can also modify the length of m in varchar(m)."
-- Change the data type of the sname field from varchar(20) to varchar(50).
-- You can use the following two methods:
alter table student change sname sname varchar(50);
alter table student modify sname varchar(50);

3) "Modify field position": You can use it with the first and after keywords.
-- Place the sname field after age. 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 the classid field alter table student drop classid;

truncate table stu;

drop table stu;