MYSQL II - Data Management (Foreign Key, Add or Delete)

Mysql: MySQL Data Management

Foreign Key Management

Foreign key concept

If the public key is the primary key in a relationship, then the public key is called the foreign key in another relationship. Thus, foreign keys represent the correlation between the two relationships. A table with another foreign key as the primary key is called a primary table, and a table with another key is called a subordinate table of the primary table.

In practice, the value of a 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 values if necessary). At this point, the attributes that hold these values in the second table are called foreign keys.

Foreign bonding

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

Create foreign keys

Specify foreign key constraints when creating tables

One way to create foreign keys is to create subtables while creating 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 Table
#(Student number, name, gender, grade, mobile phone, address, date of birth, mailbox, ID number)

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

Amendment after Form Establishment

The second way to create foreign keys is to modify subtables and add foreign keys after creating subtables.

ALTER TABLE student
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`gradeid`);

Database Data Management

Significance of database

    data storage
    data management

Method of managing database data

  Managing database data through management tools such as SQLyog
  Managing database data through DML statements

DML Language
DML (Data Operating 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:

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 columns, order and quantity.
Multiple data can be inserted at the same time. values are separated by English commas.
How do you add statements with 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 by itself, can it be omitted?
INSERT INTO grade VALUES ('Sophomore');

# Query: INSERT INTO grade VALUE ('sophomore') error code: 1136
# Column count doesn`t match value count at row 1

# It is concluded that:'Field 1, Field 2...'can be omitted, but the added values must correspond to the table structure, data column and order, and the number is the same.

# Insert multiple data at a time
INSERT INTO grade(gradename) VALUES ('Junior'),('Senior');

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 be changed
value is modified data and can be a variable, specifically, an expression, or nested SELECT results.
Condition is a filter condition, if not specified, modify all column data of the table

Delete data

DELETE command

grammar

DELETE FROM Table name [WHERE condition];

Note: condition is a filter condition. If not specified, delete all column data in the table

TRUNCATE command

Used to completely empty table data, but table structure, index, constraints, etc. remain unchanged;

**grammar :**  TRUNCATE [TABLE] table_name;

Note: Different from the DELETE command

Same: You can delete data without deleting table structures, but TRUNCATE is faster
Different:
Use TRUNCATE TABLE to reset the AUTO_INCREMENT counter
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 current value of self-increment will be kept on the original basis, and the log will be recorded.

# Delete table data (truncate)
TRUNCATE TABLE test;
# CONCLUSION: truncate deletes data and restarts from the current value to the initial value; it does not log.

# DELETE is also used to clear database table data from different engines. After restarting database services
# InnoDB: The self-adding column restarts from the initial value (because it is stored in memory, power-off is lost)
# MyISAM: Self-adding is still based on the previous self-adding data (existing files, not lost)

Tags: Database MySQL Mobile

Posted on Sat, 27 Jul 2019 00:33:38 -0700 by schajee