Shulou(Shulou.com)05/31 Report--

How to realize the basic operation of data table in MySQL? in view of this problem, this article introduces the corresponding analysis and solution in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible method.

Basic operation of data table

1. The primary key constraint requires that the data of the primary key column is unique and is not allowed to be empty. The primary key can uniquely identify a record in the table, can be combined with foreign keys to define the relationship between different data tables, and can speed up the database query. The relationship between primary keys and records is like the relationship between ID cards and people.

two。 The foreign key of the word table must be associated with the primary key of the parent table, and the data types of the associated fields must match. If the type is different. When you create a child table, an error occurs: ERROR 1005 (HY000): can't create table 'databases.tablesname' (error:150)

3. A non-null constraint means that the value of a field cannot be empty.

4. Uniqueness constraint: the column is required to be unique and allowed to be empty, but only one null value can occur. Unique constraints ensure that one or more columns do not have duplicate values.

The difference between 5.unique and primary key: a table can have multiple fields declared as unique, but only one primary key declaration: columns declared as keywords are not allowed to be null values, but fields declared as unique allow the existence of null values null.

[example. 1] create the employee table tb_emp1

Select the database in which to create the table:

USE test_db

Create the tb_ emp1 table:

CREATE TABLE tb_emp1 (id int (11), name varchar (25), deptId int (11), salary float

[example. 2] define the data table tb_emp 2, whose primary key is id:

CREATE TABLE tb_emp2 (id int (11) PRIMARY KEY,name varchar (25) deptId int (11), salary float)

[example .3] specify the primary key after all columns are defined

Define the data table tb_emp 3, whose primary key is id:

CREATE TABLE tb_emp3 (id int (11), name varchar (25), deptId int (11), salary float,PRIMARY KEY (id)

[example .4] define the data table tb_emp4 and create a multi-field federated primary key SQL statement as follows:

CREATE TABLE tb_emp4 (name varchar (25), deptId int (11), salary float,PRIMARY KEY (name,deptId)

[example .5] define the datasheet tb_emp5 and create a foreign key constraint on the tb_ emp5 table

Create a department table tb_dept1:

CREATE TABLE tb_dept1 (id int (11) PRIMARY KEY,name varchar (22) NOT NULL,location varchar (50))

Define the data table tb_emp5 and associate its key deptId as a foreign key to the primary key id of tb_dept1:

CREATE TABLE tb_emp5 (id int (11) PRIMARY KEY,name varchar (25), deptId int (11), salary float,CONSTRAINT fk_emp_dept1 FOREIGN KEY (deptId) REFERENCES tb_dept1 (id)

[example .6] define the data table tb_emp6, specify that the name of the employee cannot be empty, and the SQL statement is:

CREATE TABLE tb_emp6 (id int 11) PRIMARY KEY,name varchar 25 NOT NULL,deptId int 11, salary float,CONSTRAINT fk_emp_dept2 FOREIGN KEY (deptId) REFERENCES tb_dept (id))

[example .7] define the data table tb_dept2. The name of the specified department is unique. The SQL statement is as follows:

CREATE TABLE tb_dept2 (id int (11) PRIMARY KEY,name varchar (22) UNIQUE,location varchar (50))

[example .8] unique constraints after all columns are defined

Define the data table tb_dept3. The name of the specified department is unique. The SQL statement is:

CREATE TABLE tb_dept3 (id int (11) PRIMARY KEY,name varchar (22), location varchar (50), CONSTRAINT STH UNIQUE (name)

[example. 9] define the data table tb_emp7, and specify that the department number of the specified employee is 1111, and the SQL statement is:

CREATE TABLE tb_emp7 (id int (11) PRIMARY KEY,name varchar (25) NOT NULL,deptId int (11) DEFAULT 1111 float,CONSTRAINT fk_emp_dept3 FOREIGN KEY (deptId) REFERENCES tb_dept (id))

[example .10] define the data table tb_emp8, and specify that the employee number is automatically incremented. The SQL statement is as follows:

CREATE TABLE tb_emp8 (id int (11) PRIMARY KEY AUTO_INCREMENT,name varchar (25) NOT NULL,deptId int (11), salary float,CONSTRAINT fk_emp_dept5 FOREIGN KEY (deptId) REFERENCES tb_dept (id)); INSERT INTO tb_emp8 (name,salary) VALUES ('Lucy',1000), (' Lura',1200), ('Kevin',1500); select * from tb_emp8

[example .11] use DESCRIBE and DESC to view the table structure of table tb_dept and table TB _ emp1, respectively. The procedure is as follows:

DESCRIBE tb_dept;DESC tb_emp1

[example .12] use SHOW CREATE TABLE to view the details of the table tb_emp1. The SQL statement and the corresponding execution results are shown below:

SHOW CREATE TABLE tb_emp1;SHOW CREATE TABLE tb_emp1\ G; view the structure of the data table

1. Look at the table basic structure statement describe or desc + table name.

two。 View the table detailed structure statement show create table. If you do not add the'\ G' parameter, the display result may be very confusing, and adding the parameter'\ G' can make the display result more intuitive and easy to view.

Modify the data table

1. Modify table name

Rename the datasheet tb_dept3 to tb_deptment3

ALTER TABLE tb_dept3 RENAME tb_deptment3

two。 Modify the data type of a field

[example. 14] modify the data type of name field in data table tb_dept1 from VARCHAR (22) to VARCHAR (30)

ALTER TABLE tb_dept MODIFY name VARCHAR (30)

3. Modify the field name

[example .15] change the name of the location field in the data table tb_dept1 to loc, and the data type remains the same.

ALTER TABLE tb_dept1 CHANGE location loc varchar (50)

[example .16] change the name of the loc field in the data table tb_dept1 to location, while keeping the data type to varchar (60).

ALTER TABLE tb_dept1 CHANGE loc location varchar (60)

4. Add a field

[example .17] add an int field of type managerId (department manager number) without integrity constraints to the data table tb_dept, and enter the following SQL statement:

ALTER TABLE tb_dept ADD managerId int (10)

[example .18] add a field column1 of type varchar (12) that cannot be empty to the datasheet tb_dept1. Enter the following SQL statement and execute:

ALTER TABLE tb_dept1 ADD column1 varchar (12) not null

[example .19] add a field of type int column2 to the data table tb_dept. Enter the following SQL statement and execute:

ALTER TABLE tb_dept ADD column2 int (11) FIRST

[example .20] add a field of type int column3 after the name column in the data table tb_dept1. Enter the following SQL statement and execute:

ALTER TABLE tb_dept1 ADD column3 int (11) AFTER name

5. Delete a field

[example .21] delete the column2 field in the data table tb_ dept1 table

ALTER TABLE tb_dept1 DROP column2

6. Modify the arrangement position of the field

[example .22] modify the column1 field in the data table tb_dept1 to the first field of the table

ALTER TABLE tb_dept1 MODIFY column1 varchar (12) FIRST

[example .23] insert the column1 field in the data table tb_dept1 after the location field

ALTER TABLE tb_dept1 MODIFY column1 varchar (12) AFTER location

7. Delete foreign key constraints for data

[example .25] Delete foreign key constraints in data table tb_emp9

CREATE TABLE tb_emp9 (id int (11) PRIMARY KEY,name varchar (25), deptId int (11), salary float,CONSTRAINT fk_emp_dept FOREIGN KEY (deptId) REFERENCES tb_dept1 (id)); ALTER TABLE tb_emp9 DROP FOREIGN KEY fk_emp_dept; delete data table

[example .26] Delete the data table tb_dept2, enter the following SQL statement and execute:

DROP TABLE IF EXISTS tb_dept2

[example .27] Delete the data table tb_dept2 associated with the data table tb_emp

CREATE TABLE tb_dept2 (id int 11) PRIMARY KEY,name varchar 22, location varchar 50); CREATE TABLE tb_emp id int 11 PRIMARY KEY,name varchar 25, deptId int 11, salary float,CONSTRAINT fk_emp_dept FOREIGN KEY (deptId) REFERENCES tb_dept2 (id))

Delete the parent table tb_dept2 directly

DROP TABLE tb_dept2

Release the foreign key constraint of the associated child table tb_emp

This is the answer to ALTER TABLE tb_emp DROP FOREIGN KEY fk_emp_dept; 's question about how to realize the basic operation of data table in MySQL. I hope the above content can be of some help to you. If you still have a lot of doubts to be solved, you can follow the industry information channel to learn more about it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.