• notice
  • Congratulations on the launch of the Sought Tech site

The primary key in mysql and the usage tutorial set it

1.The method of declaring the primary key:
You can add a primary key to the table when you create the table, such as:
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, such as:

ALTER TABLE tbl_name ADD PRIMARY KEY (index_col_name,…);
/*
Create a qq table, set qq_id as the primary key, and no 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, set the qq number to 10000 (let's also imagine it), and set the nickname to "simaopig"
*/
INSERT INTO qq(qq_id, nick_name)
VALUES (
'10000','simaopig');

The primary key is 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 mentioned earlier, the primary key is actually an index, even in MySQL terminology, "key" is equivalent to "index", so "foreign key" must be set to "index" first.Therefore, the primary key should be the same as the index, which can act on a single field or on multiple fields.
Let’s take a simple example.I live in Unit 3, Room 501, and my name is Xiaozi.Only Unit 3, Room 501 can uniquely identify my home in this community list.Because unit 2 and room 501 may also be a kid, only two fields can uniquely identify me, that is to say, the combination of the two can be used as the primary key.For the combined primary key, each column will implicitly define the NOT NULL constraint, and the two together are defined as the UNIQUE unique constraint.

/*
Create a firewall table, set the host and port combination as the primary key, and note that I did not set the port as the 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, there is no problem
1 row(s) inserted.
*/
INSERT INTO firewall (
host,
port,
access)
VALUES (
'202.65.3.87', '21','deny');

3, set the primary key auto-increment
Below we use an example to explain the method of setting the primary key auto-increment:
First create a database, create 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 id of the primary key in the user table above can be incremented.

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

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 gets the system 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 set the primary key not to be empty, but also to grow automatically (there is no default value, but the default is 1, grow from 1.), and get the system default date:

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)
 

Tags

Technical otaku

Sought technology together

Related Topic

0 Comments

Leave a Reply

+