MySQL database Tutorial: manage databases and tables (DDL)

Manage databases

1. create a database: create database name;

2. Connect to the database: use database name;

3. delete a database: drop the database name;

Manage tables

1. create a table: create table Name (

Table Name Data Type (length ),

Table Name Data Type (length)

);

2. delete a table: drop table name;

3. Create a temporary table:

A. Local temporary table, visible only for the current connection

Create table # table Name (

Table Name Data Type (length ),

Table Name Data Type (length)

);

B. Global temporary tables. All connections are visible.

Create table # table Name (

Table Name Data Type (length ),

Table Name Data Type (length)

);

4. Copy table: select column name into new table name from original table name where condition;
5. modify a table:

A. add a column: Data Type (length) of the add column in the alter table name and data type (length) of the column name );

B. Modify columns: alter table name change column name new column name data type (length );

C. delete a column: alter table name: drop column name, column name;

Manage Indexes

A. indexing is an optimization solution for data query.

B. Primary keys are an index type.

C. The more indexes are created, the faster the query is.

D. indexing will affect DML execution efficiency

1. create an index: create index name on table name (column name 1, column name 2 );

2. delete an index: drop index table name. Column name;

Data Type

1. Integer

Smallint: 2 bytes

Int: 4 bytes

Bigint: 8 bytes

2. Floating Point

Float

Decimal (m, n): m represents the maximum length of the data, and n represents the length after the decimal point

3. String

Char: Specifies the length of characters. The length is 1-16. If the input data is smaller than the specified number of characters, the SQL statement fills up the remaining position. Ex: Input 'hello' to char (10), which is expressed as 'hello' in the database ';

Varchar: variable length. Length cannot exceed 8 KB

Text: more than 8 KB

4. Logic type

True and false

5. Date and Time

Datetime: year, month, day, hour, minute, second

Date: year, month, and day

Year: year