MySQL basics-create a table

MySQL basics-create a table

1. CREATE a new table create table (Domain Name Data Type column option [...]);

Mysql> create table customer (c_id char (5) primary key, c_name varchar (20), c_birth date, c_sex char (1)

DEFAULT '0 ');

Main options available in the create table command

Option	Description
AUTO_INCREMENT	Custom Auto-incrementing Sequence
DEFAULT 'default'	Define the default value of a column
INDEX	Define Indexes
[NOT] NULL	Allow/disable NULL values
PRIMARY KEY	Define column primary keys
UNIQUE	Define uniqueness
CHECK	Define the range/option of values that can be entered

Specify character set when creating a table

Mysql> create table customer (c_id char (5) primary key, c_name varchar (20), c_birth date, c_sex char (1) DEFAULT '0') CHARSET = UTF-8;

2. show tables for all tables;

Mysql> show tables;

3. display the DESC Table Name of the table structure;

Mysql> DESC customer;

4. Delete the drop table name;

Mysql> drop table customer;

5. Data insertion and display

Insert data into Table Name (column name 1, column name 2... column name n) values (data 1, data 2... data n) into table );

Displays the data in the table. SELECT column name 1, column name 2... column name n

6. AUTO_INCREMENT auto-incrementing Sequence

There are three necessary conditions for customizing the auto-increment sequence in mysql:

1. The data type must be INT, TINYINT, SMALLINT, etc.

2. Use [PRIMARY_KEY] and so on to set its uniqueness (the auto-increment sequence number is generally UNIQUE and generally serves as the table's primary key or a UNIQUE column (UNIQUE ))

3. The attachment AUTO_INCREMENT keyword after column Definition

Mysql> create table goods (id INT auto_increment primary key, name varchar (30 ));
// Note: the value of AUTO_INCREMENT can be initialized. alter table name: AUTO_INCREMENT = 0;