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.
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.
- It mainly deals with the operation of database objects (database, table, view, index).
DDL is commonly named as follows:
-- 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;
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.