MySQL data management

Foreign key

Foreign key concept

If a public keyword is the primary keyword in a relationship, the public keyword is called the foreign key of another relationship. Thus, foreign keys represent the relevant relationship between the two relationships. A table with a foreign key of another relationship as the primary key is called the master table, and a table with this foreign key is called the slave table of the master table.

In practice, the value of one table is put into the second table to represent the association. The value used is the primary key value of the first table (including composite primary key value if necessary). At this time, the attribute that holds these values in the second table is called foreign key.

Foreign key function: maintain data consistency and integrity. The main purpose is to control the data and constraints stored in the foreign key table. To associate two tables, foreign keys can only refer to the values of columns in the appearance or use null values.

Create foreign key

Specify foreign key constraints when creating tables

-- How to create a foreign key : Create child tables and foreign keys

-- Grade table (id\Grade name)
CREATE TABLE `grade` (
`gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT 'grade ID',
`gradename` VARCHAR(50) NOT NULL COMMENT 'Grade name',
PRIMARY KEY (`gradeid`)
) ENGINE=INNODB DEFAULT CHARSET=utf8

-- Student information form (Student number,full name,Gender,grade,mobile phone,address,date of birth,mailbox,ID number)
CREATE TABLE `student` (
`studentno` INT(4) NOT NULL COMMENT 'Student number',
`studentname` VARCHAR(20) NOT NULL DEFAULT 'anonymous' COMMENT 'full name',
`sex` TINYINT(1) DEFAULT '1' COMMENT 'Gender',
`gradeid` INT(10) DEFAULT NULL COMMENT 'grade',
`phoneNum` VARCHAR(50) NOT NULL COMMENT 'mobile phone',
`address` VARCHAR(255) DEFAULT NULL COMMENT 'address',
`borndate` DATETIME DEFAULT NULL COMMENT 'birthday',
`email` VARCHAR(50) DEFAULT NULL COMMENT 'mailbox',
`idCard` VARCHAR(18) DEFAULT NULL COMMENT 'ID number',
PRIMARY KEY (`studentno`),
KEY `FK_gradeid` (`gradeid`),
CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade`
(`gradeid`)
) ENGINE=INNODB DEFAULT CHARSET=utf8

Modify after table creation

-- Create foreign key mode 2 : After creating the sub table,Modify sub table and add foreign key
ALTER TABLE `student` 
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade`
(`gradeid`);

Delete foreign key

Note: when deleting a table with primary foreign key relationship, delete the sub table first and then the primary table, otherwise the following error messages will appear

-- Delete foreign key
ALTER TABLE student DROP FOREIGN KEY FK_gradeid;
-- Found that the above,The index is still there,So you have to delete the index
-- notes:This index is generated by default when creating foreign keys
ALTER TABLE student DROP INDEX FK_gradeid;

DML language

Significance of database: data storage and data management

How to manage database data:

• Manage database data through management tools such as SQLyog
• Manage database data through DML statements

DML language: data operation language

• Used to manipulate data contained in database objects
• include:
  • INSERT (add data statement)
  • UPDATE (UPDATE data statement)
  • DELETE (DELETE data statement)

Add data

INSERT command

Syntax:

INSERT INTO Table name[(Field 1,Field 2,Field 3,...)] VALUES('Value 1','Value 2','Value 3') 

be careful:

• Fields or values are separated by English commas.
• 'field 1, field 2...' This part can be omitted, but the added values must correspond to the table structure, data column and order, and the quantity must be consistent.
• Multiple pieces of data can be inserted at the same time, separated by English commas after values.

-- grammar : INSERT INTO Table name[(Field 1,Field 2,Field 3,...)] VALUES('Value 1','Value 2','Value 3')
INSERT INTO grade(gradename) VALUES ('Freshman');

-- Primary key auto increment,Can you omit it?
INSERT INTO grade VALUES ('Sophomore');

-- query:INSERT INTO grade VALUE ('Sophomore')Error code: 1136
Column count doesn`t match value count at row 1

-- conclusion:'Field 1,Field 2...'This part can be omitted , However, the added value must be consistent with the table structure,Data column,Sequence correspondence,And the quantity is consistent.

-- Insert multiple pieces of data at one time
INSERT INTO grade(gradename) VALUES ('Junior'),('Senior');

Modify data

UPDATE command

Syntax:

UPDATE Table name SET column_name=value [,column_name2=value2,...] [WHERE condition];

be careful:

• column_name is the data column to be changed
• value is the modified data, which can be variables, specifically expressions or nested SELECT results
• Condition is the filter condition. If it is not specified, all column data of the table will be modified

where conditional clause

It can be simply understood as: conditionally filter data from the table

operator	meaning	example	result
=	be equal to	1=2	false
< > or=	Not equal to	1!=2	true
>	greater than	1>2	false
<	less than	1<2	true
>=	Greater than or equal to	2>=3	false
<=	Less than or equal to	2<=3	true
BETWEEN	Between a certain range	BETWEEN 5 AND 10
AND	also	2>1 AND 1<2	true
OR	or	1>2 OR 2>3	true

 -- Modify grade information                                                
 UPDATE grade SET gradename = 'high school' WHERE gradeid = 1;     

Delete data

DELETE command

Syntax:

DELETE FROM Table name [WHERE condition]; 

Note: condition is a filter condition. If it is not specified, all column data of the table will be deleted

-- Delete last data
DELETE FROM grade WHERE gradeid = 5

TRUNCAT command

Function: used to completely empty table data, but the table structure, index, constraints, etc. remain unchanged;

Syntax:

TRUNCATE [TABLE] table_name;

-- Empty grade table
TRUNCATE grade

Note: it is different from the DELETE command

• Same: data can be deleted without deleting the table structure, but TRUNCATE is faster

• Different:

  • Reset auto using TRUNCATE TABLE_ Increment counter
  • Using TRUNCATE TABLE will not affect the transaction (which will be described later)

-- Create a test table
CREATE TABLE `test` (
`id` INT(4) NOT NULL AUTO_INCREMENT,
`coll` VARCHAR(20) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8

-- Insert several test data
INSERT INTO test(coll) VALUES('row1'),('row2'),('row3');

-- Delete table data(No where Conditional delete)
DELETE FROM test;

-- conclusion:If not specified Where Delete all column data of the table,The current value of self increment is still based on the original value,Will log.
-- Delete table data(truncate)
TRUNCATE TABLE test;

-- conclusion:truncate Delete data,The current value of auto increment will return to the initial value and start again;No logging.
-- Same use DELETE Clear database table data of different engines.After restarting the database service
-- InnoDB : The auto increment column starts from the initial value (Because it is stored in memory,Loss of power)
-- MyISAM : The auto increment column still starts from the previous auto increment data (Exist in file,Not lost)