MySQL Knowledge summary (ii) Summary of BASIC statements

1. Database

View Database

show databases;

Working with databases

Use [database name]

such as: Use MySQL

Create a database

CREATE DATABASE Bruce DEFAULT CHARACTER SET UTF8

Deleting a database

Drop database Bruce

Query MySQL version

Select version ();

Querying the current user

Select User ();

Show all tables for the current library

Show tables;

2. User

Create user

Create User Ysb

granting permissions

GRANT all privileges on * * to ' YSB ' @ ' percent ' identified by ' YSB ' with GRANT OPTION;

3. Table

Create a table

CREATE TABLE' cobar_test ' ('user_id`bigint( -) not NULLauto_increment, 'user_name`varchar( -)DEFAULT NULL, ' password 'varchar( -)DEFAULT NULL,  PRIMARY KEY(`user_id`)) ENGINE=InnoDB auto_increment=5 DEFAULTCHARSET=UTF8;

View table Structure

DESCRIBE Goods

Delete a table

Drop Table goods; Drop Table CASCADE CONSTRAINTS; // cascade Delete Constraint

4. Action table Constraintsincrease the PRIMARY KEY constraint

Syntax: ALTER table "table name" ADD CONSTRAINT "constraint name" PRIMARY KEY (column name)

ALTER TABLE ADD CONSTRAINT PRIMARY KEY (ID);

Delete a PRIMARY KEY constraint

ALTER Table DROP PRIMARY KEY

Adding FOREIGN KEY constraints

ALTER TABLE ADD CONSTRAINT Foreign KEY references goodstype (ID);

Delete a FOREIGN KEY constraint

ALTER Table DROP FOREIGN KEY GOODS_FK

Adding uniqueness constraints

ALTER TABLE ADD CONSTRAINT UNIQUE (Goodsid,orderid);

Remove UNIQUE Constraint

ALTER TABLE DROP INDEX Itemdetail_uk;

5 Action Columns

Add columns

ALTER TABLE File_info add convert_status varchar (50);

Delete Column

ALTER TABLE file_info drop column convert_status;

modifying columns

ALTER TABLE file_info change convert_status convert_status2 int (11);

MySQL Knowledge summary (ii) Summary of BASIC statements