Detailed explanation of basic operation examples of MySQL data table

  • 2021-10-27 09:32:01
  • OfStack

The basic operation of MySQL data table is described with examples in this paper. Share it for your reference, as follows:

Basic operation of data table

1. The primary key constraint requires that the data in the primary key column be only 1 and is not allowed to be null. The primary key can only identify one record in a table, can combine foreign keys to define the relationship between different data tables, and can speed up the database query. The relationship between primary key and record is like the relationship between ID card and person.

2. 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 not 1. 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 null.

4. Uniqueness constraint: This column is required to be only 1, and null is allowed, but only 1 null value can appear. The only 1 constraint ensures that there are no duplicate values in one column or several columns.

5. Difference between unique and primary key: A table can have more than one field declared as unique, but only one primary key Declaration: Columns declared as keywords are not allowed to be null, but fields declared as unique allow null null.

"Example. 1" Creating 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" Defines the data table tb_emp 2 with the primary key id:


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

"Example. 3" Specify primary key after all columns are defined

Defines the data table tb_emp 3 with the primary key 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 joint 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 data table tb_emp5 and create foreign key constraints on the tb_emp5 table

Create 1 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 so that its key deptId is associated as a foreign key with 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" Defines the data table tb_emp6, specifies 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" Defines the data table tb_dept2, specifies that the name of the department is only 1, and the SQL statement is:


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

[Example. 8] There is only one constraint after all columns are defined,

Defines the datasheet tb_dept3, specifies that the name of the department is only 1, and 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" defines the data table tb_emp7, specifies that the department number of the employee defaults to 1111, and the SQL statement is:


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

0

"Example. 10" defines the datasheet tb_emp8, specifying that the number of the employee is automatically incremented, and the SQL statement is:


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" is used separately DESCRIBE And DESC To view the table structure of table tb_dept and table tb_emp1, do the following:


DESCRIBE tb_dept;
DESC tb_emp1;

Use of "Example. 12" SHOW CREATE TABLE Looking at the details of the table tb_emp1, the SQL statement and the corresponding execution results are as follows:


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

3

View the structure of the data table

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

2. View table detail structure statements show create table . If you don't add the '\ G' parameter, the result can be very confusing, and if you add the '\ G' parameter, the result will be more intuitive and easy to view.

Modify data table

1. Modify the table name

[Example. 13] Rename the data table tb_dept3 to tb_deptment3


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

4

2. Modify the data type of the field

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


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

5

3. Modify the field name

"Example. 15" Change the name of the location field in the data table tb_dept1 to loc, leaving the data type unchanged.


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

6

"Example. 16" Change the name of the loc field in the data table tb_dept1 to location while maintaining the data type to varchar (60).


ALTER TABLE tb_dept1 CHANGE loc location varchar(60);

4. Add a field

[Example. 17] Add a field of int type managerId (Department Manager Number) without integrity constraints to the data table tb_dept, and enter the SQL statement as follows:


ALTER TABLE tb_dept ADD managerId int(10);

[Example. 18] Add a field column1 of type varchar (12) that cannot be empty to the data table tb_dept1. Enter the following SQL statement and execute:


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

9

[Example. 19] Add a field column2 of type int 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 column3 of type int 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" Deletes the column2 field in the data table tb_dept1


ALTER TABLE tb_dept1 DROP column2;

6. Modify the Arrangement of Fields

"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" Inserts 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" Removes a foreign key constraint from the 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 data table tb_dept2 associated with 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 parent table tb_dept2 directly


DROP TABLE tb_dept2;

Release the foreign key constraint of the associated child table tb_emp


ALTER TABLE tb_emp DROP FOREIGN KEY fk_emp_dept;

More readers interested in MySQL can check out the topics on this site: "MySQL Common Function Summary", "MySQL Log Operation Skills Collection", "MySQL Transaction Operation Skills Collection", "MySQL Stored Procedure Skills Collection" and "MySQL Database Lock Related Skills Collection"

I hope this article is helpful to everyone's MySQL database.


Related articles: