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.
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.
Specify foreign key constraints when creating tables
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`);
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 (Data Operating Language)
Used to manipulate data contained in database objects
INSERT (Add Data Statement) UPDATE (Update Data Statement) DELETE (Delete Data Statement)
INSERT INTO table name [(field 1, field 2, field 3,...)] VALUES('value 1','value 2','value 3')
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');
UPDATE Table name SET column_name=value [,column_name2=value2,...] [WHERE condition];
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 FROM Table name [WHERE condition];
Note: condition is a filter condition. If not specified, delete all column data in the table
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
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)