MySQL statement 1-Creating libraries and tables

A. DDL data definition Language

is the language of creating, deleting, modifying, and manipulating objects inside the library.

Keyword: Create drop alter

1. Connect to the database

Mysql-u user name-p-h specified host (default is native not specified)

2. Create a database:

mysql> CREATE database test1;

Query OK, 1 row Affected (0.00 sec)

Delete a library

mysql> drop datbases test1;

(0.00 sec): This indicates the time that the operation was executed.

3. Querying the database

Mysql> show databases;

The default four libraries:

4. Select the database you want to use

Use library name

View the tables in the library

show tables;

If you do not implement the Select library with use, you can write:

Show tables from library name;

5. Create a table

CREATE TABLE Table name (

Column name 1 column data type 1 constraints for this column,

Column Name 2 column data type 2 constraints for this column,

................

Column name n column of the data type N column of the constraint conditions,

）

to view the definition of a table:

desc Table name , if you first select the library name with use

DESC library name. Table name;

To view more details:

Mysql> Show create table library name. Table name ;

Delete a table

drop table name

Modify Table: Modify the type of a field in a table

The format is:ALTER TABLE name modify field to change the type;

mysql> ALTER TABLE pangbing modify name varchar;

Modify Table: Modify field names

Syntax: ALTER TABLE name change old field name new field Name field type (constraint);

Note:both modify and change can vary the type of field, but the change changes the name of the field, modify cannot.

modifying tables: Inserting fields into a table

The format is: ALTER TABLE name add column new field Name field type (constraint);

column can be omitted without writing. This inserts the default in the last row.

Increment by field sort order

ALTER TABLE name Add new Field field type after field (who is behind who to write)

NOTE: Adding constraints in this way cannot be added at the same time

To sort an existing field,

ALTER TABLE name modify already has field field type position

Example 1:

ALTER TABLE pangbing modify nianling int first;

Place the Nianling field in the Pangbing table at the front.

Example 2:

ALTER TABLE pangbing modify ningling int after name;

Place the Nianling field in the Pangbing table behind the Name field.

Modify a table: Remove a field from a table

The format is: ALTER TABLE name drop column field name;

COLUNM can be omitted

6. Modify the table name

ALTER TABLE name rename new table name;

MySQL statement 1-Creating libraries and tables