This is the 28th day of my participation in the August Challenge

preface

MySQL related knowledge is very important for our programmers, from simple add, delete, change and check, to stored procedures and other complex operations, we have to understand, the following continue to share some of my learning MySQL summary

The CREATE INDEX statement is used to CREATE indexes in a table.

Indexes make it faster for database applications to find data without reading the entire table.

Note: Updating a table with an index takes more time than updating a table without an index because the index itself needs to be updated. Therefore, it is ideal to create indexes only on columns (and tables) that are often searched.

SQL CREATE INDEX syntax

Create a simple index on the table. Duplicate values are allowed:

CREATE INDEX index_name
ON TABLE_NAME (column_name)
Copy the code

Note: “column_name” specifies the column to be indexed.

SQL CREATE UNIQUE INDEX syntax

Create a unique index on the table. A unique index means that two rows cannot have the same index value.

CREATE UNIQUE INDEX index_name
ON TABLE_NAME (column_name)
Copy the code

SQL DROP TABLE statement

The DROP TABLE statement is used to DROP a TABLE (the structure, attributes, and indexes of the TABLE are also dropped)

DROP TABLE Indicates the name of a TABLECopy the code

The DROP DATABASE statement is used to DROP a DATABASE:

DROP DATABASE Indicates the DATABASE nameCopy the code

SQL TRUNCATE TABLE statement

What if we only need to remove the data in the table, but not the table itself?

Use the TRUNCATE TABLE command (only delete data from the TABLE) :

TRUNCATE TABLE NameCopy the code

SQL ALTER TABLE syntax

To add columns to a table, use the following syntax:

ALTER TABLE TABLE_NAME
ADD column_name datatype
Copy the code

To delete a column from a table, use the following syntax:

ALTER TABLE TABLE_NAME 
DROP COLUMN column_name
Copy the code

AUTO INCREMENT field

We typically want to create the value of the primary key field automatically every time a new record is inserted.

We can create an auto-INCREMENT field in the table.

CREATE TABLE Persons
(
P_Id INT NOT NULL AUTO_INCREMENT,
LastName VARCHAR(255) NOT NULL,
FirstName VARCHAR(255),
Address VARCHAR(255),
City VARCHAR(255),
PRIMARY KEY (P_Id)
)
Copy the code

SQL CREATE VIEW statement

What is a view?

In SQL, a view is a visual table based on the result set of an SQL statement.

A view contains rows and columns, just like a real table. Fields in a view are fields in real tables from one or more databases. We can add SQL functions, WHERE, and JOIN statements to the view, and we can submit data as if it came from a single table.

Note: The design and structure of the database is not affected by functions, WHERE, or JOIN statements in the view.

Note: Views always display the most recent data. Each time the user queries the view, the database engine reconstructs the data by using SQL statements.

Views can be used from within a query, from within a stored procedure, or from within another view. By adding functions, joins, and so on to the view, we can submit exactly what we want to submit to the user. ### Summary Above is my learning MySQL some records, if there is any error welcome to point out, gogogo