Basic knowledge

A data storage procedure:

Create data - > confirm field - > create data table - > insert data

MySQL database system from large to small is: database server, database, data table, row and column.

Naming rules for identifiers

• The database name and table name shall not exceed 30 characters, and the variable name shall be limited to 29 characters
• Must contain only a-z,A-Z,0-9, '' '
• Do not include spaces between object names such as database name, table name and field name
• In the same MySQL software, the database cannot have the same name; In the same library, tables cannot have the same name; Fields in the same table cannot have the same name
• You must ensure that your fields do not conflict with reserved words, database systems, or common methods. If you insist on using it, please use ` (emphasis sign) in the SQL statement
• Keep the consistency of field name and type: when naming a field and specifying its data type, be sure to ensure consistency. If the data type is an integer in one table, don't become a character in another table

Common data types in MySQL

data type	describe
INT	Integer data from - 231 to 231-1. The storage size is 4 bytes
CHAR(size)	Fixed length character data. If not specified, the default is 1 character and the maximum length is 255
VARCHAR(size)	Variable length character data is saved according to the actual length of the string, and the length must be specified
FLOAT(M,D)	Single precision, occupying 4 bytes, M = integer bits + decimal places, d = decimal places. D < = m < = 255,0 < = d < = 30, default m + d < = 6
DOUBLE(M,D)	Double precision, 8 bytes, d < = m < = 255,0 < = d < = 30, default m + d < = 15
DECIMAL(M,D)	High precision decimal, occupying M+2 bytes, d < = m < = 65, 0 < = d < = 30, and the maximum value range is the same as DOUBLE.
DATE	Date data, format 'YYYY-MM-DD'
BLOB	Long text data in binary form, up to 4G
TEXT	Long text data, up to 4G

Creating and modifying databases

Create database

• Judge whether the database already exists. If it does not exist, create the database (recommended)

#If there is no database with the same name, it will be created. If there is a database with the same name, it will not be created

CREATE DATABASE IF NOT EXISTS Database name; 

#You can also specify the character set of the database

CREATE DATABASE IF NOT EXISTS Database name CHARACTER SET character set;

Note: the database cannot be renamed

Use database

Use a database

USE Database name;

View all databases

SHOW DATABASES;

View the database currently in use

#DATABASE() is a function
SELECT DATABASE();

View all tables under the specified library

SHOW TABLES FROM Database name;

View database creation information

#It is not difficult to find that what you are looking at is the information about creating the database
SHOW CREATE DATABASE Database name;

Note: if you want to operate on tables in each database, you need to USE this table first, that is, the USE table. If you want to USE tables in other databases, you can add the database name before the table name.

modify the database

As mentioned above, the name of the database cannot be changed. Modify the character set of the database

 # For example: gbk, utf8, etc
ALTER DATABASE Database name CHARACTER SET character set; 

Delete database

Deletes the specified database

#Delete if database exists
DROP DATABASE IF EXISTS Database name;

Create table

Mode 1

• Add field manually

Syntax format:

[constraint] [default value] [table constraint] may not be added.

#If the table does not exist, create the table 
CREATE TABLE IF NOT EXISTS Table name(
	Field 1, data type [constraint condition] [Default value],
	Field 2, data type [constraint condition] [Default value],
	Field 3, data type [constraint condition] [Default value],
	......
	[Table constraints]
);

Mode II

• Use the AS subquery option to combine creating tables and inserting data

• It should be noted that the specified columns and subquery columns should correspond to each other one by one

• Define columns by column names and default values

#Use the result of the query as the field and data of the new table
CREATE TABLE emp1 
AS 
SELECT * FROM employees;
#An empty table was created because the contents of the query are empty, but the fields are reserved
CREATE TABLE emp2 
AS 
SELECT * FROM employees WHERE 1=2; -- Created emp2 Is an empty table

View table structure

View the structure of the table;

DESC Table name;

SHOW CREATE TABLE Table name\G

Use the SHOW CREATE TABLE statement to view not only the detailed statements when the table is created, but also the storage engine and character encoding.

Modify table

Modifying a table refers to modifying the structure of an existing data table in the database

Use the ALTER TABLE statement to:

• Add columns to an existing table
• Modify columns in an existing table
• Delete columns from an existing table
• Rename a column in an existing table

Add column

[FIRST|AFTER field name] specifies that the added column is before or after that field

ALTER TABLE Table name ADD [COLUMN] Field name field type[ FIRST|AFTER [field name];

Modify column

You can modify the data type, length, default value and position of the column

ALTER TABLE Table name 
MODIFY [COLUMN] Field name 1 field type[ DEFAULT [default][ FIRST|AFTER [field name 2];

Heavy life list

ALTER TABLE Table name 
CHANGE [column] Column name new column name new data type;

Delete column

It should be noted that if a column is associated with other tables, it cannot be deleted.

ALTER TABLE Table name DROP [COLUMN]Field name

rename table

Mode 1

RENAME TABLE Table name
TO New table name;

Mode II

ALTER table Table name
RENAME [TO] New table name;  -- [TO]Can be omitted

Delete table

be careful:

• In MySQL, when a data table is not associated with any other data table, the current data table can be deleted directly.

• Data and structures are deleted

• All running related transactions are committed

• All related indexes are deleted

  Syntax format:

DROP TABLE [IF EXISTS] Data sheet 1 [, Data sheet 2, ..., data sheet n];

Empty table

TRUNCATE TABLE statement:

• Delete all data in the table and keep the table structure
• Free up storage space for tables

TRUNCATE TABLE Table name;

DELETE FROM Table name; 

TRUNCAT cannot roll back data. If DELETE is set, data can be rolled back only without automatic submission.

Therefore, it is recommended to use DELETE to prevent accidents.