【WEEK5】 【DAY5】DML Language【English Version】

2024.3.29 Friday

Contents

• 3. DML Language
• • 3.1. Foreign Keys (For Understanding)
  • • 3.1.1. Concept
    • 3.1.2. Purpose
    • 3.1.3. Several Methods to Add (Write) Foreign Keys
    • • 3.1.3.1. Creating the Table with Direct Reference Inside (the part being referenced of the referenced table)
      • 3.1.3.2. Creating the Table First, Then Modifying It to Add Foreign Keys
      • 3.1.3.3. All the above operations are physical foreign keys, database-level foreign keys, which are not recommended to use excessively -> To avoid too many databases
    • 3.1.4. Deletion
  • 3.2. DML Language (Data Manipulation Language)
  • • 3.2.1. Database Significance
    • 3.2.2. Methods to Manage Database Data
    • 3.2.3. DML Language: Data Manipulation Language
    • 3.2.4. Adding
    • 3.2.5. Modification
    • 3.2.6. Deletion

3. DML Language

3.1. Foreign Keys (For Understanding)

3.1.1. Concept

• If a common key is the primary key in one relationship, then this common key is called the foreign key in another relationship. It can be seen that the foreign key represents the correlation between two relationships. The table with the foreign key acting as the primary key is called the primary table, and the table that possesses this foreign key is called the foreign table of the primary table.
• In practice, a table’s values are placed into a second table to represent the association, using the primary key value(s) of the first table (including composite primary key values when necessary). At this time, the attribute in the second table that stores these values is called a foreign key.

3.1.2. Purpose

Maintains data consistency and integrity, with the main purpose being to control the data stored in the foreign key table, constraining it. It forms an association between two tables, where the foreign key can only refer to the values of columns in the external table or use null values.

3.1.3. Several Methods to Add (Write) Foreign Keys

3.1.3.1. Creating the Table with Direct Reference Inside (the part being referenced of the referenced table)

In this example, grade is the referenced (primary) table, and student is the referencing (foreign) table.

-- Adding a foreign key part 1 --
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 AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

-- Student information table (student number, name, gender, grade, phone, address, birthdate, email, ID number)
CREATE TABLE `student` (
	`studentno` INT(4) NOT NULL COMMENT 'student number',
	`studentname` VARCHAR(20) NOT NULL DEFAULT 'Anonymous' COMMENT 'name',
	`sex` TINYINT(1) DEFAULT '1' COMMENT 'gender',

	# This line is not for linking the 'grade' table, but indicates "to reference"
	`gradeid` INT(10) DEFAULT NULL COMMENT 'grade',

	`phoneNum` VARCHAR(50) NOT NULL COMMENT 'phone',
	`address` VARCHAR(255) DEFAULT NULL COMMENT 'address',
	`borndate` DATETIME DEFAULT NULL COMMENT 'birthday',
	`email` VARCHAR(50) DEFAULT NULL COMMENT 'email',
	`idCard` VARCHAR(18) DEFAULT NULL COMMENT 'ID number',
	PRIMARY KEY (`studentno`),
	
	KEY `FK_gradeid` (`gradeid`),		-- Where FK_ is the default name, the purpose (of defining a foreign key) is to reference grade table's gradeid
	
	CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`gradeid`)
	# Adds constraint to the foreign key and references a specific field in another table
) ENGINE=INNODB DEFAULT CHARSET=utf8

Opening the student table design view to see the foreign key will show the following

3.1.3.2. Creating the Table First, Then Modifying It to Add Foreign Keys

Note: Different from the previous method, everything related to the foreign key needs to be retained in the foreign table’s first writing except for the last 2 lines (placed in the later addition).

-- Adding a foreign key part 2 --
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 AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

-- Student information table (student number, name, gender, grade, phone, address, birthdate, email, ID number)
CREATE TABLE `student` (
	`studentno` INT(4) NOT NULL COMMENT 'student number',
	`studentname` VARCHAR(20) NOT NULL DEFAULT 'Anonymous' COMMENT 'name',
	`sex` TINYINT(1) DEFAULT '1' COMMENT 'gender',
	`gradeid` INT(10) DEFAULT NULL COMMENT 'grade',
	`phoneNum` VARCHAR(50) NOT NULL COMMENT 'phone',
	`address` VARCHAR(255) DEFAULT NULL COMMENT 'address',
	`borndate` DATETIME DEFAULT NULL COMMENT 'birthday',
	`email` VARCHAR(50) DEFAULT NULL COMMENT 'email',
	`idCard` VARCHAR(18) DEFAULT NULL COMMENT 'ID number',
	PRIMARY KEY (`studentno`)		-- Cannot add a comma as the last line in brackets
) ENGINE=INNODB DEFAULT CHARSET=utf8

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

3.1.3.3. All the above operations are physical foreign keys, database-level foreign keys, which are not recommended to use excessively -> To avoid too many databases

Best Practice

• The database is simply a table used for data storage, only containing rows (data) and columns (fields).
• To use data from multiple tables (foreign keys) --> Switch to implementing with programs

3.1.4. Deletion

When deleting tables with a primary-foreign key relationship, the foreign table must be deleted first, followed by the primary table, otherwise the primary table cannot be deleted.
For example, deleting the grade table mentioned above:

3.2. DML Language (Data Manipulation Language)

3.2.1. Database Significance

Data storage, data management

3.2.2. Methods to Manage Database Data

• Through management tools like Navicat, SQLyog, etc., to manage database data
• Through DML statements to manage database data

3.2.3. DML Language: Data Manipulation Language

• Used to operate the data contained within database objects
• Includes:
  • INSERT (Statement to add data)
  • UPDATE (Statement to update data)
  • DELETE (Statement to delete data)

3.2.4. Adding

-- Insert statement (adding) --
INSERT INTO table_name([column_name1, column_name2, column_name3, ...]) VALUES ('value1'), ('value2'), ('value3'), ('value4'), ...) or in the form of ('value1', 'value2', 'value3', ...)

1. The simplest case: the table has only one column, essentially whether you write (gradename) or not, it will automatically add the value to this column.

INSERT INTO `grade` (`gradename`) VALUES ('undergraduate')

2. To add multiple rows of data for the same field simultaneously, each piece of data must be separated by parentheses and commas.

-- To add multiple rows of data for the same field simultaneously, each piece of data must be separated by parentheses and commas
INSERT INTO `grade` (`gradename`) VALUES ('sophomore'), ('junior')

(corresponding to ids 4~5)

3. If adding data for different fields in the same row simultaneously, you don’t need to separate each piece of data with parentheses, but pay attention to the one-to-one correspondence between data and field names.

-- If adding data for different fields in the same row simultaneously, no need to separate each piece of data with parentheses, but ensure the data matches the field names, as follows
INSERT INTO `student` (`studentname`, `phoneNum`) VALUES ('DL','1234567')
INSERT INTO `student` (`studentno`, `studentname`, `phoneNum`, `borndate`) VALUES ('1','DL','9876','1021')
INSERT INTO `student` (`studentno`, `studentname`, `phoneNum`, `borndate`) VALUES ('2','DL','9876','2222-10-21')

4. Notes

• Separate fields or values with commas.
• The part ‘field1, field2…’ can be omitted, but the values added must correspond to the table structure, data columns, and their order, and the quantity must be consistent.
• Multiple pieces of data can be inserted simultaneously, separated by commas after values.

3.2.5. Modification

-- Modification statement --
UPDATE table_name SET column_name=value [,column_name2=value2,...] [WHERE condition];

1. If no condition is specified, all data in that field will be changed.

-- If no condition is specified, all data in that field will be changed
UPDATE `student` SET `studentname` = 'momo'

2. Use a specific field value as a filter to modify the value of that row’s field.

-- Use a specific field value as a filter to modify the value of that row's field
UPDATE `student` SET `studentname` = 'DL' where `studentno` = 1;

3. To modify multiple field attributes, just follow SET with multiple assignments.

-- To modify multiple field attributes, just follow SET with multiple assignments
UPDATE `student` SET `studentname` = 'QW', `address` = 'quagmire' where `studentno` = 2;

4. Locate data through multiple conditions.

-- Locate data through multiple conditions
UPDATE `student` SET `studentname` = 'dl' WHERE studentno = 0 AND phoneNum = 1234567;

5. Insert the current time.

-- Insert the current time
UPDATE `student` SET `borndate` = CURRENT_TIMESTAMP WHERE studentno = 0 OR phoneNum = 1234567;

6. Notes

• column_name is the data column to be changed.
• value is the data after modification, which can be a variable, specific value, expression, or nested SELECT result.
• condition is the filtering condition. If not specified, it modifies all column data in the table.

7. Where condition statement
   
   For example:

UPDATE `student` SET `studentname` = 'as', `address` = 'quagmire' WHERE studentno BETWEEN 0 AND 2;

3.2.6. Deletion

1. DELETE

DELETE FROM table_name [WHERE condition];

condition is the filtering condition. If not specified, it deletes all column data in the table.
For example:

-- Delete statement --
-- Not recommended to write like this, it deletes everything
DELETE FROM `student`
-- Selectively delete
DELETE FROM `student` WHERE studentno = 1;

2. TRUNCATE

TRUNCATE [TABLE] table_name;

Purpose: Completely clears table data, but table structure, indexes, constraints remain unchanged.
For example:

TRUNCATE `student`

3. Differences between DELETE and TRUNCATE

• Similarities: Both can delete data, not the table structure, but TRUNCATE is faster.
• Differences:
  • Using TRUNCATE TABLE will reset the AUTO_INCREMENT counter.
  • Using TRUNCATE TABLE does not affect transactions.

4. Test

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

#Insert test data
INSERT INTO `test`(`coll`) VALUES('row1'),('row2'),('row3');
#Delete table data
DELETE FROM test;
#Insert again, notice id starts from 4 not 1
INSERT INTO `test`(`coll`) VALUES('row1'),('row2'),('row3');

TRUNCATE TABLE test;
#Insert again, notice id starts from 1 not 7
INSERT INTO `test`(`coll`) VALUES('row1'),('row2'),('row3');

Conclusion: Using truncate to delete data resets the auto-increment value to start over; does not log the operation.
Similarly, using DELETE to clear data from tables with different engine types. After restarting the database service, using different data engines:
InnoDB: Auto-increment columns start over from the initial value (stored in memory, lost on power off)
MyISAM: Auto-increment columns continue from the last auto-increment value (stored in files, not lost)