Mysql: Database operations, data table operations, field operations collation

MySQL Login native

Mysql-u root-p Password

Database operations Create a database

Command: Create databases < database name >

For example:mysql> CREATE DATABASE xhkdb;

Display Database

show databases;

Connecting to a database

Use examples;

Create a database and set the encoding Utf-8 multi-language

Create database ' examples ' default character set UTF8 collate utf8_general_ci;

Deleting a database

drop database examples;

Delete a database that is not deterministic

Drop database if exists AAA;

Export the entire database

The export file is present in the Mysql\bin directory by default

Mysqldump-u User name-p password database name > exported file name

For example:

Mysqldump-u username-p 123456 databasename > Outfile_name.sql

Export a database structure

Mysqldump-u username-p-d–add-drop-table databasename > Outfile_name.sql

Data table operations Create a table

Mysql> CREATE TABLE MyClass (

> ID int (4) NOT null primary key auto_increment,

> Name char () NOT NULL,

> Sex int (4) NOT null default ' 0 ',

> Degree double (16,2));

Show Table

Show tables;

Show Table Structure

Describe

SHOW COLUMNS from table_name;

Delete a table

drop table test;

Renaming a table

ALTER TABLE test_old rename test_new;

Mysql> Rename table MyClass to Youclass;

Exporting data tables

Mysqldump-u user name-P database name Table name > exported file name

Mysqldump-u username-p databasename table_name > Outfile_name.sql

Copying tables

(copy structure only, source table name: A new table name: b) (Access available)
Law one: SELECT * into B from a where 1<>1
Law II: SELECT top 0 * into B from a

Copy table

(Copy data, source table name: A target table name: b) (Access available)
Insert into B (A, B, c) select d,e,f from B;

Field action Add column

ALTER TABLE Test add CN int (4) not null;

modifying columns

ALTER TABLE test change ID id1 varchar (ten) not null;

Delete Column

ALTER TABLE Test drop CN;

Create an index

ALTER TABLE test Add index (CN,ID);

Delete Index

ALTER TABLE test DROP INDEX CN

Other operations

Show index from A #查看索引

ALTER TABLE A add primary key (ID) #主键索引

ALTER TABLE A add unique (name) #唯一索引

ALTER TABLE A Add index name (name) #普通索引

ALTER TABLE A add fulltext (name) #全文索引

ALTER TABLE A Add index name (id,name) #多列索引

View Creation View

Create VIEW viewname as Selectstatement

Delete a view

Drop View ViewName

(Lazy to build the environment, so reference collation not verified, or have bugs, haha)

Mysql: Database operations, data table operations, field operations collation