MySQL 12: Data Management

Foreign key:

Method 1: add constraints when creating tables (troublesome and 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

-- target:Create a school database
-- Create student tables (columns, fields) using SQL establish
-- Student number int,Login password varchar(20),Name and gender varchar(2),Date of birth( datatime),Home address email

-- Note: use English() Try to use nouns and fields in the table `` Enclose 
-- AUTO_INCREMENT Self increasing
-- Strings are enclosed in single quotes
-- All statements are followed by  , (The last one need not be added
-- DEFAULT  default
-- PRIMARY KEY A primary key usually has only one unique annotation for a table
CREATE TABLE IF NOT EXISTS `student`( 
	`id`  INT(4) NOT NULL AUTO_INCREMENT COMMENT 'Student number',
	`name`  VARCHAR(30) NOT NULL DEFAULT 'anonymous' COMMENT 'full name',
	`pwd`  VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT 'password',
	`sex` VARCHAR(2) NOT NULL DEFAULT 'male' COMMENT 'Gender',
	`birthday` DATETIME DEFAULT NULL COMMENT  'date of birth',
	-- Student table gradeid Field to reference the grade table gradeid
	-- Define foreign keys key
	-- Add constraints (execute references) to this foreign key references
	`gradeid` INT(10) NOT NULL COMMENT 'Student grade', 
	`address` VARCHAR(100)  DEFAULT NULL COMMENT 'Home address',
	`email` VARCHAR(50) DEFAULT NULL COMMENT 'mailbox',
	PRIMARY KEY(`id`),
	KEY `FK_gradeid` (`gradeid`),
	CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`gradeid`)

)ENGINE=INNODB DEFAULT CHARSET=utf8;

When deleting a table with a foreign key relationship, you must first delete the referenced table, and then delete the referenced table

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

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

-- target:Create a school database
-- Create student tables (columns, fields) using SQL establish
-- Student number int,Login password varchar(20),Name and gender varchar(2),Date of birth( datatime),Home address email

-- Note: use English() Try to use nouns and fields in the table `` Enclose 
-- AUTO_INCREMENT Self increasing
-- Strings are enclosed in single quotes
-- All statements are followed by  , (The last one need not be added
-- DEFAULT  default
-- PRIMARY KEY A primary key usually has only one unique annotation for a table
CREATE TABLE IF NOT EXISTS `student`( 
	`id`  INT(4) NOT NULL AUTO_INCREMENT COMMENT 'Student number',
	`name`  VARCHAR(30) NOT NULL DEFAULT 'anonymous' COMMENT 'full name',
	`pwd`  VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT 'password',
	`sex` VARCHAR(2) NOT NULL DEFAULT 'male' COMMENT 'Gender',
	`birthday` DATETIME DEFAULT NULL COMMENT  'date of birth',
	-- Student table gradeid Field to reference the grade table gradeid
	-- Define foreign keys key
	-- Add constraints (execute references) to this foreign key references
	`gradeid` INT(10) NOT NULL COMMENT 'Student grade', 
	`address` VARCHAR(100)  DEFAULT NULL COMMENT 'Home address',
	`email` VARCHAR(50) DEFAULT NULL COMMENT 'mailbox',
	PRIMARY KEY(`id`)
	-- key `FK_gradeid` (`gradeid`),
	-- constraint `FK_gradeid` Foreign key (`gradeid`) references `grade` (`gradeid`)

)ENGINE=INNODB DEFAULT CHARSET=utf8;


-- There is no foreign key relationship when creating a table
ALTER TABLE `student` 
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY(`gradeid`) REFERENCES `grade` (`gradeid`);
--  ALTER TABLE Table name(`student` )  ADD CONSTRAINT  Constraint name(`FK_gradeid`) FOREIGN KEY (`Columns as foreign keys`) REFERENCES Which watch (Which field);

The above foreign keys are physical foreign keys, that is, they are all database level foreign keys, which is not recommended!!! (avoid trouble caused by too many databases)

Best practices

A database is a simple table. It only stores data, only rows (data) and columns (fields)
We want to use the data of multiple tables and use foreign keys (programs to implement them)

DML language

**Database: * * data storage, data management

DML: Data Manipulation Language

Insert
update
delete

add to

insert -- insert

-- -----------------------------------------------------
-- Insert language (add)
-- insert into  Table name([Field name 1, field name 2, field name 3... ...]) values('Value 1'),('Value 2'),('Value 3')... ...
INSERT INTO `grade` (`gradename`)  VALUES ('Sophomore');
-- Since the primary key is self incremented, it can be omitted (if the table fields are not written, they will match one by one)
INSERT INTO `grade`   VALUES ('Sophomore');
-- Generally, when writing insert statements, data and fields should correspond one by one!

-- Insert multiple fields
INSERT INTO `grade` (`gradename`) VALUES ('Freshman'),('Sophomore'),('Junior');


INSERT INTO `student`(`name`) VALUES ('Zhang San');

INSERT INTO `student`(`name`,`pwd`,`sex`)
 VALUES ('Zhang San','13524699','male'),('Li Si','13524699','male'),('Wang Wu','13524699','male');
-- -----------------------------------------------------

matters needing attention:

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. Values (), (), ()

INSERT INTO `student`
 VALUES (7,'Xiao Liu','13524699','male','2001-01-01' ,'Xi'an','email');

modify

update -- modify

-- Modify student name
UPDATE `student` SET `name`='ym' WHERE id=1;

-- All tables will be changed without specifying conditions
UPDATE `student` SET `name`='yyds' ;  -- No choice id All is selected by default	

-- Modify multiple properties
UPDATE `student` SET `name`='ym',`email`='306668@qq.com'  WHERE id=1;



-- grammar 
-- updata Table name set colnum_name=value where condition=

Condition: where clause operator, id equal to a value, greater than a value, modified in a certain interval

Operator	meaning	Range	result
=	be equal to	5=6	false
< > or=	Not equal to	5<>6	true
>
>
>=
<=
between... and ...	Within a certain range	[2,5]
and	&&	5>1 and 1>2	false
or	\|\|	5>1 or 1>2	true

-- Locate data through multiple conditions

UPDATE `student` SET `name` ='ds' WHERE `name`='yyds' AND sex ='female';

delete

delete -- command

-- Delete data

-- Avoid writing like this, it will be deleted completely
DELETE FROM `student`

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

Syntax: delete from table name [where condition]

Truncate command
-- Function: completely empty a database table, and the table structure and index constraints will not change!


-- empty student surface
TRUNCATE `student`

delete and truncate are different:

Same point: data can be deleted without deleting the table structure
Different:
- truncate resets the auto increment calculator to zero
- truncate does not affect transactions

 -- test delete and truncate Differences between:
 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');

DELETE FROM `test`;  -- It will not affect self increment
TRUNCATE TABLE `test`;  -- Self increasing to zero

Note: the problem of DELETE deletion is that the database is deleted again

INNODB auto incrementing will start from 1 (it exists in memory and will be lost when powered off)
MyISAM continues from the previous increment (if it exists in the file, it will not be lost)

Tags: MySQL Database SQL index

Posted by Daveg on Tue, 04 Jan 2022 18:03:28 +1030

Programs Wiki

MySQL 12: Data Management