MySQL is a widely used open source relational database that supports rich query and operation functions, and the most commonly used query method is to query data through primary keys. This article will introduce how to use primary keys to query data in MySQL, including the definition, use, query, optimization, etc. of primary keys.
1. Definition of primary key
The primary key is the unique identifier used to identify each record in the table in MySQL. The primary key can be composed of a single field or a combination of multiple fields, but it must meet the following conditions:
When defining the primary key, you can use MySQL's auto-increment function. For example:
CREATE TABLE user
(
id
int(11) NOT NULL AUTO_INCREMENT,
name
varchar(50) NOT NULL,
password
varchar(50) NOT NULL,
email
varchar(50) NOT NULL,
PRIMARY KEY (id
)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
In the above code, the id
field is defined as the primary key, and the auto-increment function of MySQL is used. When new data is inserted, the value of the id
field is automatically incremented by 1 to ensure that the primary key value of each record is unique.
2. Use of primary key
INSERT INTO user
(name
, password
, email
) VALUES ('Tom' , '123456', 'tom@example.com');
UPDATE user
SET password
='654321' WHERE id
=1;
DELETE FROM user
WHERE id
=1;
3. Primary key query
Using the primary key to query data is The most common and basic query method in MySQL. When performing a primary key query, you can use the following two methods:
SELECT * FROM user
WHERE id
=1;
SELECT * FROM user
WHERE id
=1;
CREATE TABLE user
(
id
int(11) NOT NULL AUTO_INCREMENT,
name
varchar (50) NOT NULL,
password
varchar(50) NOT NULL,
email
varchar(50) NOT NULL,
PRIMARY KEY (id
),
INDEX idx_user_name
(name
)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
In the above code , by creating an index on the name
field, you can improve the query speed when querying the name
field.
ALTER TABLE user
ADD INDEX idx_user_email
(email
);
In the above code, Added index for email
field.
Summary
This article introduces the definition, use and query method of primary keys in MySQL. In practical applications, primary keys should be optimized and indexed according to actual needs to improve query efficiency and performance.
The above is the detailed content of How to query data using primary key in MySQL. For more information, please follow other related articles on the PHP Chinese website!