mysql< basic operations for databases and tables >

basic operations for databases and tables Database Basics

Create a database

is to partition a piece of data in a database system.

CREATE DATABASE Itcast;

View Database

SHOW CREATE database name;

Modify Database

ALTER database name DEFAULT CHARACTER SET encoding method COLLATE encoding _bin;

Deleting a database

DROP database name;

Data Type

Integer type

Floating-point type and fixed-point number type

Date and Time type

string and binary types

basic operation of the data table

Create a data table

View Data Sheet

Show create table name \g;

describe table name;

modifying data tables

Modify Table Name

ALTER TABLE old table name rename [to] new table name;

modifying field values

ALTER TABLE name change old field name new data type for new field name;

Modify the data type of a field

ALTER TABLE name modify field name data type;

Add Field

ALTER TABLE name add new field name data type [constraint][first|after field name already exists];

"First" is an optional parameter that is used to set the newly added field as the number one field in the table.

After is an optional parameter that is used to add a newly added field after the specified existing field name.

Delete a field

ALTER TABLE name drop field name;

To modify the sort position of a field

ALTER TABLE name modify field name 1 data type first|after field Name 2;

Delete a data table

drop table name;

constraints on tables

PRIMARY KEY constraint (primary key)

Uniquely identifies a record in a table

Field name data type primary key

Primary KEY (field name 1, field Name 2, ...)

FOREIGN KEY constraint (foreign key)

Non-null constraint (NOT NULL)

The value of a field cannot be null

Field name data type NOT NULL;

Unique constraint (unique)

Ensure the uniqueness of the fields in the data table, that is, the values of the fields in the table cannot recur

Field name data type unique

Default constraint (defaults)

Field name data type default defaults

Set table field values to automatically increase

Automatically generate a unique ID for new records inserted in a table

Field name data type Auto_increment

Index The concept of an index

It is like the sequencer table of the Xinhua dictionary, which is a structure that sorts the values of one or more columns in a database table, and its function is to improve the query speed of the data in the table.

6 Types of indexes

A universal index is an index defined by key or index, which is the basic index type in MySQL, which can be created in any data type, whether its value is unique or not NULL, as determined by the constraints of the field itself.

A uniqueness index is an index defined by a unique, and the value of the field in which the index resides must be unique.

A full-text index is an index defined by fulltext that can only be created on a field of char, varchar, or text type, and now only the MyISAM storage engine supports full-text indexing.

A single-column index refers to the creation of an index on a single field in a table, which can be a normal index, a unique index, or a full-text index, as long as the index is guaranteed to correspond to only one field in the table.

A multicolumn index is an index that is created on more than one field in a table and is used only if the first field in those fields is used in the query criteria.

A spatial index is an index defined by spatial, which can only be created on a field of a spatial data type.

Create an index

1. Create an index when creating a table

CREATE Table table name (field name data type [integrity constraint],

Field name data type [integrity constraint],

．．．．．．

Field name data type

unique| Fulltext| SPATIAL] Index| KEY

Alias (Field name 1 [(length)]) [asc| DESC])

);

Unique: Optional parameter, which represents a unique index

Fulltext: Optional parameter, indicating full-text index

Spatial: optional parameter, representing spatial index

Index and key: The indexes used to represent the fields, select one

ASC and DESC: Optional parameters, ASC for ascending order, desc for descending order

Alias: Optional parameter that represents the name of the created index

Field Name 1: Specify the name of the corresponding field for the index

Length: Optional parameter that represents the length of the index

2. Create an index on a table that already exists by using the CREATE INDEX statement

CREATE [unique| Fulltext| SPATIAL] Index Name

On table name (field name [(length)] [asc| DESC]);

3. Use the ALTER TABLE statement to create an index on an already existing table

ALTER table name ADD [unique| Fulltext| SPATIAL] INDEX

Index name (field name [(length)] [asc| DESC])

Delete Index

1. Use ALTER TABLE to delete an index

ALTER Table name DROP INDEX field name

2. Delete index using DROP index

DROP index index name on table name;

mysql< basic operations for databases and tables >