Data management of MySQL

1.DML language (very important)

The meaning of database: data storage and data management

DML: Data Manipulation Language

• insert
• update
• delete

2. Foreign keys

Method 1: add constraints to the primary key when creating a table (more complex)

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 table gradeid Field to reference the grade table gradeid
CREATE TABLE `student` (
  `id` INT(4) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(20) NOT NULL DEFAULT 'Zhang San' COMMENT 'full name',
  `pwd` VARCHAR(10) NOT NULL DEFAULT '123456' COMMENT 'password',
  `sex` VARCHAR(2) NOT NULL DEFAULT 'female' COMMENT 'Gender',
  `gradeid` INT(10) NOT NULL COMMENT 'Student grade',
  `birthday` DATETIME DEFAULT NULL COMMENT 'date of birth',
  `address` VARCHAR(100) DEFAULT NULL COMMENT 'address',
  PRIMARY KEY (`id`),
  KEY `FK_gradeid`(`gradeid`) ,   -- Define foreign keys key
  CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`)  REFERENCES `grade`(`gradeid`)       -- Add constraints to this foreign key
) ENGINE=INNODB DEFAULT CHARSET=utf8

When deleting a table with a foreign key relationship, you must first delete the table referencing others (slave table), and then delete the referenced table (master table)

Method 2: after the table is created successfully, add a foreign key constraint

CREATE TABLE IF NOT EXISTS `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

CREATE TABLE `student` (
  `id` INT(4) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(20) NOT NULL DEFAULT 'Zhang San' COMMENT 'full name',
  `pwd` VARCHAR(10) NOT NULL DEFAULT '123456' COMMENT 'password',
  `sex` VARCHAR(2) NOT NULL DEFAULT 'female' COMMENT 'Gender',
  `gradeid` INT(10) NOT NULL COMMENT 'Student grade',
  `birthday` DATETIME DEFAULT NULL COMMENT 'date of birth',
  `address` VARCHAR(100) DEFAULT NULL COMMENT 'address',
  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8

-- There is no foreign key relationship when creating the table
-- ALTER TABLE surface ADD CONSTRAINT Constraint name FOREIGN KEY(Foreign key column name) REFERENCES Associated foreign key table(Foreign key table fields);
ALTER TABLE `student`
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY(`gradeid`) REFERENCES `grade`(`gradeid`);

The above operations are database level foreign keys, which are not recommended! Avoid the trouble caused by too many databases

A database is a separate table that only stores data, only rows (data) and columns (fields)
If you want to use multiple table data, use the program to realize the foreign key function

3. Add insert

Syntax:
insert into table name ([field name 1, field name 2,...]) values ('value 1 '), ('value 2')

-- Insert statement
-- insert into Table name([Field name 1, field name 2....]) values('Value 1'),('Value 2')....
INSERT INTO `grade`(`gradename`) VALUES('Senior');

-- Since the primary key increases automatically, you can omit the primary key
-- If you do not write the field names of the table, they will match one by one
INSERT INTO `grade` VALUES('Sophomore');   -- error

-- Generally, when writing insert statements, the data must correspond to the field name one by one
-- Insert multiple fields
INSERT INTO `grade`(`gradename`) VALUES('Sophomore'),('third year in high school');
INSERT INTO `student`(`name`,`pwd`) VALUES('Yao Yao','aaaaaa');

be careful:

1. Fields are separated by English commas
2. Fields can be omitted, but the following values must correspond to each other one by one
3. Multiple pieces of data can be inserted at the same time. The values after values need to be separated by values(), ()

4. Modify update

sentence:
update table name set column name 1 = value 1 [, column name 2 = value 2.....] where condition;

-- Modify student name
-- All tables are changed without specifying conditions
UPDATE `student` SET `name`='Wangwang' WHERE id=1;

-- Modify multiple properties
UPDATE `student` SET `name`='Wangwang',`gradeid`='Senior' WHERE id=2;

-- Locate data through multiple conditions
UPDATE `student` SET `name`='shaky' WHERE sex='male' AND `gradeid`='Senior two';

Conditions: where clause
The operator returns a Boolean value

Operator	meaning	Range	result
=	be equal to	5=6	false
< > or=	Not equal to	5!=6	true
>	greater than	5>6	true
<	less than	5<6	true
>=	Greater than or equal to	5>=6	false
<=	Less than or equal to	5<=6	true
between... and ...	Closed interval	[5,6]
and	And, equivalent to&&	5>1 and 1>2	false
or	perhaps	5>1 or 1>2	true

be careful:

• The column name is the column of the database. Try to bring it with you``
• Criteria are filter criteria. If not specified, all columns will be modified
• Value is a concrete value or a variable
  UPDATE student SET birthday=CURRENT_TIME WHERE name ='Oh ';
• The attributes of multiple settings are separated in English

5. delete

5.1.delete

sentence:
delete from table name [where condition];

-- Delete data (avoid this writing method and delete the whole table data)
DELETE FROM `student`;
-- Delete specified data
DELETE FROM `student` WHERE id = 1;

5.2.truncate

Clear the tables in the database, and the table structure and index constraints remain unchanged
sentence:
truncate table name;

Difference between delete and truncate:
Same point: data can be deleted without deleting table structure
difference:

• truncate resets the auto increment column and the counter returns to zero
• truncate does not affect transactions

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

INSERT INTO `test`(`coll`) VALUES('1'),('2'),('3'),('4'),('5');

DELETE FROM `test`;   -- It will not affect self increment
 Then insert the data, id The auto increment field starts from 6

TRUNCATE TABLE `test`;   -- Auto increment will return to zero
 Then insert the data, id The auto increment field starts from 1

※ understand:
delete deletes the problem, restarts the database, and

• innodb auto incrementing will start from 1 (it exists in memory and will be lost when powered off)
• myisam continues from the previous increment (exists in the file and will not be lost)