Mysql:MySQL Data Management

Foreign Key Management

Foreign key concept

If a public key is the primary key in a relationship, it is called the foreign key in another relationship.Thus, the foreign key represents the correlation between the two relationships.Tables whose primary key is a foreign key to another relationship are called primary tables, and tables with foreign keys are called secondary tables of the primary table.

In practice, the values of one table are placed in the second table to represent the association, using the values of the primary key of the first table (including composite primary key values if necessary).In this case, the attributes that hold these values in the second table are called foreign keys.

Foreign Key Action

To maintain data consistency and integrity, the primary purpose is to control the data stored in foreign key tables, constraints.To associate two tables, a foreign key can only refer to the values of columns in the outer table or use null values.

Create Foreign Key

Specify foreign key constraints when building tables

# One way to create a foreign key is to create a subtable while creating a foreign key

# 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 Sheet
#(School number, name, gender, grade, mobile phone, address, date of birth, mailbox, ID number)

CREATE TABLE `student` (
  `studentno` INT(4) NOT NULL COMMENT 'School 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 building

# Create a foreign key by modifying the subtable to add a foreign key when the subtable is created
ALTER TABLE student
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`gradeid`);

Delete foreign keys

Note: When deleting a table with a primary foreign key relationship, delete the subtable before deleting the main table

# Delete foreign keys
ALTER TABLE student DROP FOREIGN KEY FK_gradeid;
# Find that the index is still there after execution, so delete the index
# Note: This index is generated by default when a foreign key is created
ALTER TABLE student DROP INDEX FK_gradeid;

Database Data Management

Database Meaning

data storage
data management

Methods for managing database data

Manage database data through administrative tools such as SQLyog
Manage database data through DML statements

DML Language

DML (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

Grammar:

INSERT INTO table name [(field 1, field 2, field 3,...)] VALUES('value 1','value 2','value 3')

Be careful:

Separate fields or values with English commas.
Field 1, Field 2... can be omitted, but add values that correspond to the table structure, data columns, order, and number.
Multiple data can be inserted at the same time, separated by English commas after values.

# How do you add statements using statements?
# Syntax: INSERT INTO table name [(field 1, field 2, field 3,...)] VALUES('value 1','value 2','value 3')
INSERT INTO grade(gradename) VALUES ('Freshman');

# The primary key increases itself, 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

# The conclusion is that'Field 1, Field 2...'can be omitted, but the added values must correspond to the table structure, data columns, order and quantity.

# Insert multiple pieces of data at once
INSERT INTO grade(gradename) VALUES ('Junior'),('Senior');

Exercise Title

Use the INSERT statement to add data to the subject of the timetable. Use foreign keys.

Modify data

UPDATE command

Grammar:

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

Be careful:

column_name is the data column to change
value is modified data and can be a variable, specifically an expression, or a nested SELECT result
Condition is a filter condition, modify all column data of the table if not specified

WHERE Conditional Clause

It can be simply understood as conditionally filtering data from a table

Delete data

DELETE command

grammar

DELETE FROM Table Name [WHERE condition];

Note: condition is a filter condition, delete all column data of the table if not specified

TRUNCATE command

Used to completely empty table data, but table structure, indexes, constraints, etc. remain unchanged;
Syntax: TRUNCATE [TABLE] table_name;

Note: This is different from the DELETE command

Same: can delete data without deleting table structure, but TRUNCATE is faster
Different:
- Resetting the AUTO_INCREMENT counter using TRUNCATE TABLE
- Using TRUNCATE TABLE does not affect transactions

# 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 (delete without where condition)
DELETE FROM test;
# CONCLUSION: If Where is not specified, all column data of the table will be deleted and the log will be recorded as the current value increases.

# Delete table data (truncate)
TRUNCATE TABLE test;
# CONCLUSION: truncate deletes data and restarts after increasing the current value; no logs are recorded.

# Also use DELETE to empty database table data for different engines. After restarting the database service
# InnoDB: The auto-increment column starts from the initial value (because it is stored in memory, power loss occurs)
# MyISAM: Self-adding columns still start from the previous self-adding data (there are files, no loss)

Posted by ph0ngwh0ng on Thu, 27 Feb 2020 11:47:50 -0800

Programmer Group