Examples of adding, modifying, deleting and clearing values ​​in MySQL database

-- The gradeid field of the student table should reference the gradeid field of the grade table.
-- Define foreign key
-- Add constraints to this foreign key, (execute reference), REFERENCES reference key `FK_gradeid`(`gradeid`)
CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade`(`gradeid`)

-- After successfully creating the table, add a foreign key constraint -- There is no foreign key relationship when creating the table ALTER TABLE `student`
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY(`gradeid`) REFERENCES `grade`(`gradeid`);
-- ALTER TABLE table ADD CONSTRAINT `` FOREIGN KEY (``) REFERENCES `field`

-- Insert statement INSERT INTO table name ([field 1, field 2, field 2])
values(''),(''),('')

-- INSERT INTO 'grade'('gradename') VALUES ('Senior Year')
-- Generally, when writing insert statements, the data and the fields to be inserted must correspond one to one -- Add multiple fields to a field INSERT INTO `indicates`(`attribute column field`, ...) 
VALUES ('matched value 1', 'matched value 2', 'matched value 3')
/*
If you do not want to write the field names of the attribute columns, then match each attribute column exactly and write the following values ​​one by one*/

-- Insert multiple data <i.e. tuple>, multiple rows of data INSERT INTO `s`(`SNO`,`SNAME`,`SEX`,`Sage`) VALUES ('180109','王五','男','76'),('180108','李四','男','88')

-- Modify the student's name UPDATE `student` SET `name`='CJ7' WHERE id = 1; -- Conditional-- If not specified, all tables will be changed! ! ! ! !
UPDATE `student` SET `name` = 'CJ7'
-- Syntax -- UPDATE table name SET colum_name = value, [colum_name = value, ....] where [condition]

-- Example of modifying the statement UPDATE `student` SET `birthday` = CURRENT_TIME WHERE `name` = 'CJ7' AND SEX = 'female'

-- Delete data (use with caution, it is easy to delete the database and run away)
DELETE FROM `STUDENT`

-- Delete specified data DELETE FROM `student` WHERE id = 1;

-- Clear the data table, for example, clear the student table TRUNCATE `student`
-- Safe writing, delete table, prevent deletion of database named `student`
TRUNCATE TABLE `student`

-- Test CREATE TABLE `test`(
    `id` INT(4) NOT NULL AUTO_INCREMENT,
    `COLL` VARCHAR(20) NOT NULL,
    PRIMARY KEY('id')
)ENGINE=INNODB DEFAULT CHARSET=UTF8

INSERT TNTO `test` (`coll`) VALUES('1')('2')('3')

DELETE FROM `test` -- will not affect auto-increment TRUNCATE TABLE `test` -- will reset to zero

-- An example of adding a tuple insert into `Student` values('180103','对象三','女','22','CS')

-- Test adding multiple tuples, separated by commas INSERT INTO `Student` VALUES('180105','对象五','男','19','IS'),('180106','对象六','女','20','CS'),('180107','对象七','女','20','CS')

-- Because the table structure is set to non-empty, there is no test here to only add the value of a certain attribute column 

UPDATE `student` SET `sname` = 'Operation modification object 1' WHERE `sno` = 180102;
-- Remember not to use commas when adding. My problem was that I used commas below -- UPDATE `student` SET `sname` = 'Operation modification object 1', WHERE `sno` = 180102;

-- Delete a single record <Delete this record if it meets the WHERE condition>
DELETE FROM `student` WHERE sname = 'Operation modification object 1';
-- Use TRUNCATE to clear the data table TRUNCATE Table `student`
-- Or use delete to delete all databases DELETE FROM `student`
-- Will not affect auto-increment