Library and table-related commands for MySQL database

Creation and deletion of databases

I. Database-related commands

Creation of a database

Create {Database|schema} db_name [character set=] [collate =]; If the database is present, an error will be

Create {Database|schema} [if not exists] db_name [character set=] [collate =]; indicates that if this database does not exist it is created

where [Character set =] is used to set the default character set

[COLLATE =] used to set collation

Deletion of the database

drop {Database|schema} [if EXISTX] db_name; Delete a database

Modify the default character set and collation for a database

ALTER {DATABASE | SCHEMA} [Db_name] [DEFAULT] CHARACTER SET [=] Charset_name
| [DEFAULT] COLLATE [=] Collation_name

View all databases

Show {Databases|schemas};

Use db_name Select a database

If you want to get more information about how to create and delete a database, you can use the following command:

Mysql>help KEYWORD

For example: Help create database; or Help drop database;

Ii. commands related to the table

Creation of tables

There are three ways of creating a table :

1, directly define an empty table

CREATE TABLE Tb_name (Colum1,colum2...date_type [primary key] [NOT NULL] [unsigned] [default value] [Unique key]) [table S_options]; Because creating a table is the process of creating a column, the parameters inside the parentheses are the property fields of the column.

Tip: If you have multiple fields together as primary key (primary key) and unique key (unique key) when creating a table, you can define it separately when creating the table. Of course, a field can also be defined separately as a primary key and a unique key.

For example: CREATE TABLE tb2 (id INT UNSIGNED NOT NULL auto_increment, Name CHAR (a) Not NULL, age TINYINT NOT NULL, PRIMARY KEY (ID , name), INDEX (age)

2. Use SELECT query statements to isolate data from other tables and create them as tables

CREATE [temporary] TABLE [IF not EXISTS] Tbl_name
[(Create_definition,...)]
[Table_options]
Select_statement

The table structure of the table created with this method will change and will not be the same as the table structure of the table in select_statement

3. Create an empty table as a template in another table

CREATE [temporary] TABLE [IF not EXISTS] Tbl_name
{Like Old_tbl_name | (like Old_tbl_name)

The table structure of the table created by using this method is the same as the table structure of the Template table

[Tables_options]

When creating a table, you can specify some options. Among them, the common options are these:

engine [=] Engine_name the storage engines for the specified table

[DEFAULT] CHARACTER Set [=] Charset_name Set default character set

[DEFAULT] COLLATE [=] Collation_name Set default collation

max_rows [=] value specifies the maximum number of rows allowed for table creation

Delete a table

DROP [temporary] TABLE [IF EXISTS] Tbl_name

Modify Table

ALTER TABLE Tb_name

MODIFY [COLUMN] col_name column_definition: Represents the Modified field's property parameter (the parameter after the field)

Change [COLUMN] old_col_name new_col_name column_definition: Indicates modified field name

ADD [COLUMN] (col_name column_definition,...) : Indicates that some columns are added

ADD {index| KEY} [Index_name] [Index_type] (Index_col_name,...) [Index_option]

Increase Index Field

Drop [COLUMN] col_name Delete a field
| Drop PRIMARY Key Delete primary key
| DROP {index| KEY} index_name Delete Index
| Drop FOREIGN Key Fk_symbol Delete foreign key

Renaming a table

Mysql> RENAME Tbale old_table to New_tables;

View related commands for a table

Show tables from db_name; View the tables in the library

Desc tb_name; View the structure of a table

For more relevant commands, use the Help keyword to get

Create an index

CREATE INDEX index_name [Index_type] on Tb_name (col_name [(length)] [ASC | DESC],...);

Where length indicates the ability to index, [ASC | DESC] Indicates the arrangement in ascending or descending order.

Show index from Tb_name: Displays indexes on the specified table

Keys, also known as constraints, can be used as indexes and belong to special indexes (with special qualification): The default index type is B+tree

Index types are: Btree and hash index

This article is from the "Linux Learning path" blog, so be sure to keep this source http://xslwahaha.blog.51cto.com/4738972/1576682

Library and table-related commands for MySQL database