Primary key in MySQL and its auto-increment usage tutorial _ MySQL

This article mainly introduces the primary key in MySQL and the usage tutorial for setting auto-increment. it is the basic knowledge in MySQL getting started. if you need it, refer 1. primary key declaration method:
You can add a primary key to the table when creating the table, for example:
Create table tbl_name ([field description omitted...], primary key (index_col_name ));
You can also add a primary key to the table when updating the table structure, for example:

Alter table tbl_name add primary key (index_col_name ,...); /* Create a qq table, set qq_id as the primary key, and do NOT impose a NOT NULl constraint on it */create table qq (qq_id int (10), nick_name varchar (255) not null, primary key (qq_id)/* insert a piece of data and set the QQ number to 10000 (I also fantasized about it ), set the nickname to "simaopig" */insert into qq (qq_id, nick_name) VALUES ('20170101', 'simaopig ');

Primary keys are considered to be the best combination of not null and UNIQUE constraints. If these columns are NOT explicitly defined as not null, MySQL will implicitly define these columns.

2. the primary key is also an index:
As I have already said, the primary key is actually an index. even in MySQL terminology, the "key" is equivalent to "index". Therefore, the "foreign key" must be set to "index" first ". Therefore, the primary key should be the same as the index, which can act on individual fields and multiple fields.
For example, if I live in Room 3, Room 501, and my name is Xiaozi, only room 3, Room 501 can be uniquely identified in the community table. Because Unit 2 and room 501 may also have a shard, only two fields can uniquely identify me, that is, they can be combined as the primary key. The primary key of the combination. each column implicitly defines the not null constraint, and the UNIQUE constraint of UNIQUE is defined together.

/* Create a firewall table and set the host and port combination as the primary key. Note that I have NOT set the port not NULL constraint */create table firewall (host varchar (11) NOT null, port smallint (4), access enum ('Deny', 'allow') not null, primary key (host, port)/* insert a new record, no problem. 1 row (s) inserted. */insert into firewall (host, port, access) VALUES ('2017. 65.3.87 ', '21', 'deny ');

3. set primary key auto-increment
The following example shows how to set the auto-increment of a primary key:
First, create a database and a table.

mysql> create database ssh2; 

Query OK, 1 row affected (0.04 sec) 

mysql> use ssh2; 

Database changed 

mysql> create table user(   -> id integer primary key,   -> firstname varchar(200) not null,   -> lastname varchar(200) not null,   -> age integer   -> ); 

Query OK, 0 rows affected (0.46 sec) 

Add an auto-increment function to the primary key:

mysql> alter table user modify id integer auto_increment ; 

Query OK, 1 row affected (0.28 sec) Records: 1 Duplicates: 0 Warnings: 0 

In this way, the primary key and id in the preceding user table can be auto-incremented.

Add the default value and auto-increment function to the primary key id.

mysql> alter table user modify id integer auto_increment ; 

Query OK, 0 rows affected (0.39 sec) Records: 0 Duplicates: 0 Warnings: 0 

mysql> alter table user modify id integer default '1'; 

Query OK, 0 rows affected (0.16 sec) Records: 0 Duplicates: 0 Warnings: 0 

mysql> alter table user modify id integer auto_increment ; 

Query OK, 1 row affected (0.28 sec) Records: 1 Duplicates: 0 Warnings: 0 

MySql system acquisition time:

mysql> alter table user add createtime timestamp default current_timestamp; 

Query OK, 2 rows affected (0.17 sec) Records: 2 Duplicates: 0 Warnings: 0 

MySql cannot set the primary key to be blank, but it also needs to automatically increase (the default value is not set here, but the default value is 1, which starts from 1 .), The system default date is also obtained:

mysql> create table dd(   -> id int primary key not null auto_increment,   -> name varchar(20),   -> time timestamp default current_timestamp   -> ); 

Query OK, 0 rows affected (0.10 sec) 

 mysql> insert into dd(name) values ('fhihgifds');

Query OK, 1 row affected (0.14 sec) 

mysql> insert into dd(name) values ('steven'); 

Query OK, 1 row affected (0.08 sec) 

mysql> select * from dd; 

+----+-----------+---------------------+ | id | name   | time        | +----+-----------+---------------------+ | 1 | fhihgifds | 2011-03-27 01:58:46 | | 2 | steven  | 2011-03-27 01:59:35 | +----+-----------+---------------------+ 2 rows in set (0.08 sec) 

mysql> insert into dd(name) values ('anthony'); 

Query OK, 1 row affected (0.09 sec) 

mysql> select * from dd; 

+----+-----------+---------------------+ | id | name   | time        | +----+-----------+---------------------+ | 1 | fhihgifds | 2011-03-27 01:58:46 | | 2 | steven  | 2011-03-27 01:59:35 | | 3 | anthony  | 2011-03-27 02:00:07 | +----+-----------+---------------------+ 3 rows in set (0.00 sec)