Simple operation of MySQL database and data table

I. Database additions and deletions

1. Create a new database

CREATE  database name CharSet UTF8;

Database name rule: can be by letter, number, underscore, @, #, $ case-sensitive, cannot use keywords such as create SELECT, cannot use numbers alone, maximum 128 bits

2. View the database

Create  database name;

3. Select a database

 use database name;

4. Deleting a database

DROP  database name;

5. modifying database character encoding

Alter  database name CharSet UTF8;

Two. Data sheet additions and deletions

1. Create a table

Create  table name (field name 1 type [(width) constraint ], field name 2 type [(width) constraint ], ......);

Note: Field names cannot be the same in the same table, field names and types are required, width and constraints are optional

Create Table int varchar (int(3), sex enum (' Male ', ' female '));

Inserting data into the table

Insert  into Values (1,'Tom','male'), (2, ' Jerry ','female');

2. View table structure

Create table test\g; #查看表详细结构, can be added \g display

3. Modify the table structure

A. Modifying table names

ALTER  table name RENAME new table name;

B. Adding a table field

ALTER TABLETable nameADDField name data type[integrity constraint conditions ...],ADDField name data type[integrity constraint conditions ...]; #添加一个或多个字段ALTER TABLETable nameADDField name data type[integrity constraint conditions ...]First ; #在最前面增加字段ALTER TABLETable nameADDField name data type[integrity constraint conditions ...]After field name; #在某个字段后增加字段

Alter TableStudentAddNamevarchar( -) not NULL,AddAgeint(3) not NULL default  A;Alter TableStudentAddStu_numvarchar(Ten) not NULLAfter name;Alter TableStudentAddSex enum ('male','female')default 'male'First

C. Deleting a field

ALTER TABLE DROP field name;

Alter Table drop sex;

D. Modifying fields

 alter  table  table name MODIFY field name data type [  integrity constraint ...   " ;  alter  table  table name change old field name new field name old data type [  integrity constraint ...   " ;  alter  table  table name change old field name new field name New data type [  integrity constraint ...  ] ; 

 alter  table  student Modify ID int  (11 ) not  null  primary  key   auto_increment; #修改id字段类型非空主键自动增长  alter  table  student Modify ID int  (11 ) not  null   alter  table  student drop  primary  key ; #删除主键 

4. Copying a table

复制表结构＋记录 （不会复制: 主键、外键和索引）

Create Table Select *  from service;

Duplicate table structure only

Create Table Select *  from where 1 = 2; #设置条件为假使数据查不到只有结构

5. Delete a table

DROP  table name;

Simple operation of MySQL database and data table