MySQL Learning notes-Basic grammar

1. Build statement

Building Tables is the most basic function of the database, the commonly used table statements are as follows:

CREATE TABLE ' staff_info ' (  ' id ' int (one) not null auto_increment COMMENT ' PK ',  ' job_number ' varchar (+) ' NOT null C Omment ' work number ',  ' city ' varchar (+) NOT null default ' Shanghai ' COMMENT ' employees live in cities ',  ' street ' varchar (+) NOT null default ' Xu Sinks ' COMMENT ' employee Living Streets ',  ' salary ' int (one) COMMENT ' salary ',  ' status ' tinyint (4) not NULL DEFAULT 1 COMMENT ' record state, 0: invalid, 1: there  PRIMARY KEY (' id '),
  UNIQUE key ' Unique_jobnumber_status ' (' job_number ', ' STATUS '),  key ' Ix_jobnumber ' (' Job_number '),  key ' Ix_ City_street ' (' City ', ' STREET ')) Engine=innodb auto_increment=10000 DEFAULT Charset=utf8 comment= ' Employee Information Form ';

For the built-in statement, the following points need to be explained:

1, each row of the building statement must specify the data item name and data item type at least;

2, if a data item cannot be NULL, you need to specify a NOT NULL property;

3. If there is a default value for an item, you can use default to specify the defaults;

4, in order to facilitate the use of the table, please add comment comments after each item;

5, if the table is already able to determine the late query format, then you can use the key syntax to specify the table index;

6, if there is a column or a few columns of the content must be unique, then you can use a unique key to specify these columns;

7. In the end of the statement we can use the engine to specify the database engine, DEFAULT charset specifies the table character set;

8, I put an anti-quote on each data item ', below the keyboard ESC, it is to distinguish between the MySQL reserved word and ordinary characters introduced by the symbol, if the data item contains reserved words, you must use anti-quotation marks;

9, in MySQL, the general will create a demand-independent self-increment primary key, I name here as ID. The Auto_increment attribute is added to the ID entry, indicating that the item is incremented from the PRIMARY KET the specified ID entry is the primary key, and the last "auto_increment=10000" description of the build statement starts from 10000.

2. View table structure

If you want to know what data items The table defines, we can use the DESC table name to view the table structure:

However, using the syntax above to view the table structure, we cannot see the comments we added on the data item, so I prefer to use the show create table name to view the table structure:

The Show create TABLE command can see indexes and annotations on the table in addition to the data items and data item types.

3. Change data table structure

The previous section explains the view table structure, part of which we explain how to modify the table structure.

Add a column at the end of the table

The syntax for adding a column at the end of the table is as follows:

　For example, we can use the following statement to add a column at the end of the Staff_info table that indicates the employee's onboarding date:

Add a new column after a column　　

In addition to adding a column at the end by default, we can also specify that a column be added after a column:

Alter table table name add column Name column type column parameter after a column

For example, we can use the following statement to add a column after job_number that indicates the gender of the employee:

Add the new column to the front

We can also add columns to the front of the table, with the following syntax:

Alter table table name add column Name column type column parameter first

For example, we can use the following statement to add a column at the beginning of the Staff_info table:

ALTER TABLE ' staff_info ' Add test VARCHAR (+) not NULL first;

Delete Column

In addition to adding columns, ALTER table can also delete columns, and the syntax for deleting columns is as follows:

Alter table name  drop Column name

For example, the following statement can remove the newly added test column:

ALTER TABLE ' staff_info ' drop test;

modifying column types

ALTER TABLE can modify the column type with the following syntax:

Alter table table name modify column name new type  new parameter

For example, the following statement can modify the column type of city column to varchar (10):

ALTER TABLE ' staff_info ' modify City varchar (ten) not NULL DEFAULT ' Shanghai ' comment ' Employees live in cities ';

Modifying column names and column types

The syntax above can only modify column types, and column names cannot be modified, and we can use the following syntax to modify both the column name and the column type:

Alter Table table name change old column name new column name new type new parameter

The following statement modifies the city column name to Live_city and modifies the column type to varchar (100):

ALTER TABLE ' staff_info ' Change ' city ' live_city ' varchar (+) not NULL DEFAULT ' Shanghai ' comment ' Employees live in cities ';

Modify Table Name

Finally, we introduce the modified table name with the following syntax:

ALTER table "table name" RENAME "Table new name"

The following statement renames the Staff_info table to staff:

ALTER TABLE ' staff_info ' RENAME ' staff ';

MySQL Learning notes-Basic grammar