Shulou(Shulou.com)06/01 Report--

Server-side commands:

DDL: Data definition language used to manage database components such as tables, indexes, views, users, and stored procedures

CREATE、ALTER、DROP

DML: Data Manipulation Language, mainly used to manage the data in the table, to achieve data addition, deletion, modification, check;

INSERT， DELETE， UPDATE， SELECT

Get Command Help:

mysql> help KEYWORD

Database management:

Create: CREATE {DATABASE| SCHEMA} [IF NOT EXISTS] db_name;

[DEFAULT] CHARACTER SET [=] charset_name Set character set

[DEFAULT] COLATE [=] collation_name Collation

Example:CREATE DATABASE `test2` DEFAULT CHARACTER SET utf8 COLATE utf8_general_ci;

View all supported character sets: SHOW CHARACTER SET

View all supported collations: SHOW COLLATION

Modifications:

ALTER {DATABASE | SCHEMA} [db_name]

[DEFAULT] CHARACTER SET [=] charset_name

[DEFAULT] COLLATE [=] collation_name

Delete:

DROP {DATABASE | SCHEMA} [IF EXISTS] db_name

Table management:

Create:

CREATE TABLE [IF NOT EXISTS] tbl_name (create_definition table definition) [table_options]

create_defination:

Field: col_name data_type

Key:

PRIMARY KEY (col1, col2, ...) primary key constraint

UNIQUE KEY (col1, col2,...) unique key constraint

FOREIGN KEY (column) Foreign key constraint

Index:

KEY|INDEX [index_name] (col1, col2,...)

Example: CREATE TABLE students(id INT UNSIGNED NOT NULL,name CHAR(30) NOT NULL,age TINYINT UNSIGNED,gender ENUM('f','m') );

Create table student (id integer unsigned not empty, name character type (30) not empty, age small integer unsigned, gender enumeration ('f ',' m'));

UNSIGNED must follow the character type in any other order

View all storage engine types supported by the database:

mysql> SHOW ENGINES;

View the storage engine type for a table:

mysql> SHOW TABLES STATUS [LIKE 'tbl_name']

table_options：

ENGINE [=] engine_name Storage engine type

Modifications:

ALTER [ONLINE | OFFLINE] [IGNORE] TABLE tbl_name [alter_specification [, alter_specification] ...] alter_specification: field or attribute

Fields:

ADD [COLUMN] field name col_name data_type [FIRST| AFTER col_name ] Placement location

Delete: DROP [COLUMN] col_name

ALTER TABLE student DROP class;

Modifications:

CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST|AFTER col_name]

Change names and field names

MODIFY [COLUMN] col_name column_definition [FIRST | AFTER col_name]

Change field definition (available as long as name is not changed)

Example: ALTER TABLE student MODIFY calss VARCHAR(100) AFTER age;

Key:

Add: ADD {PRIMARY| UNIQUE| FOREIGN} KEY (col1, col2,...)

Example:ALTER TABLE student ADD UNIQUE KEY (name); add unique key (automatically converted to primary key when NULL is not empty)

Delete:

Primary key: DROP PRIMARY KEY

Foreign key: DROP FOREIGN KEY fk_symbol

Index:

Add: ADD {INDEX| KEY} [index_name] (col1, col2,...)

Example:ALTER TABLE student ADD INDEX index_calss (class); index_name is the name of oneself

Delete: DROP {INDEX| KEY} index_name

Example:ALTER TABLE student DROP INDEX name;

Table Options:

ENGINE [=] engine_name

To view information about indexes on tables:

mysql> SHOW INDEXES FROM tbl_name;

Delete table:

DROP TABLE [IF EXISTS] tbl_name [, tbl_name] ...

Table reference method:

tbl_name is equivalent to relative path must specify current table (USE db_name;)

db_name.tbl_name corresponds to an absolute path

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.