编程知识 cdmana.com

Learn MySQL database from 0 Foundation (5) -- final chapter!

MySQL Database technology

sixteen 、 Process control structure

1. classification :

Sequential structure :

The process of executing a program from top to bottom

Branching structure :

A process in which a program chooses one of two or more paths to execute

Loop structure :

On the basis of satisfying certain conditions , Repeat a piece of code

2. Branching structure :

① if( expression 1, expression 2, expression 3) function

** function :** Implement a simple two branch selection structure

** Execution order :** If the expression 1 establish , be if Function returns the expression 2 Value , Otherwise return the expression 3 Value

select if(3>10,'true','false');
② case structure

** Situation 1 :** Be similar to java Medium switch sentence , Generally used to realize the equivalence judgment , The syntax is as follows :

case  Variable |  expression  |  Field 
	when  Value to judge   then  The value returned  1  Or words 1;
	when  Value to judge   then  The value returned  2  Or words 2;
	when  Value to judge   then  The value returned  3  Or words 3;
	……
	else  Value to return  n  Or words n;
end case;

** Situation two :** Be similar to java The multiple in if sentence , It is generally used to realize the judgment of distinction , The grammar is as follows :

case
	when  The conditions to judge 1  then  The value returned  1  Or words 1;
	when  The conditions to judge 2  then  The value returned  2  Or words 2;
	when  The conditions to judge 3  then  The value returned  3  Or words 3;
	……
	else  Value to return  n  Or words n;
end case;	
Case study :
--  Create stored procedure , According to the incoming grades , To show the level 
-- 90-100 good ,80-89 good ,70-79 Pretty good ,60-69 pass , Or you'll fail 
DELIMITER $
CREATE PROCEDURE myp1(IN score INT)
BEGIN
	CASE
	 	WHEN score>=90 AND score <=100 THEN SELECT' good ';
	 	WHEN score>=80 AND score <90 THEN SELECT' good ';
	 	WHEN score>=70 AND score <80 THEN SELECT' Pretty good ';
	 	WHEN score>=60 AND score <70 THEN SELECT' pass ';
	 	ELSE SELECT' fail, ';
	END CASE;
END;$

--  call 
CALL myp1(84);

 Insert picture description here

③ if structure
--  grammar 
if  Conditions 1 then  sentence 1;
	elseif  Conditions 2 then  sentence 2;
	elseif  Conditions 2 then  sentence 2;
	……
	else  sentence n;
end if;
--  Scenarios are applied to begin end in 


--  Case study : Create a function , According to the incoming grades , To show the level 
-- 90-100 good ,80-89 good ,70-79 Pretty good ,60-69 pass , Or you'll fail 
DELIMITER $
CREATE FUNCTION myfun1(score INT) RETURNS VARCHAR(20)
BEGIN
	IF score>=90 AND score <=100 THEN RETURN' good ';
	 	ELSEIF score>=80  THEN RETURN' good ';
	 	ELSEIF score>=70  THEN RETURN' Pretty good ';
	 	ELSEIF score>=60  THEN RETURN' pass ';
	 	ELSE RETURN' fail, ';
	END IF;
END;$
--  Call function 
SELECT myfun1(76);

 Insert picture description here

3. Loop structure

Loop structure :while\loop\repeat

Cycle control :

​ iterate Be similar to java Medium continue, continue , End this cycle , So let's go to the next loop

​ leave Be similar to java Medium break, Jump out of , End the current cycle

while loop

First judge , After execution

--  grammar 
 label :while  The loop condition  do
	 The loop body 
end while  label ;

--  Case study : Bulk insert data , Add... To a table based on the number of inserts 10 Bar record 
--  First create an empty table 
create table magic(id int,mname varchar(50));

--  Create a stored procedure 
DELIMITER $
CREATE PROCEDURE mypro1(IN num INT)
BEGIN
	DECLARE i INT DEFAULT 1;--  Defined local variables 
	WHILE i<=num DO
	INSERT INTO magic VALUES(i,CONCAT('magic_',i)); --  Splicing 
	SET i = i+1;
	END WHILE;
END;$

--  Query the data in the table 
CALL mypro1(10);
SELECT * FROM magic;

 Insert picture description here

--  Case study 2: Bulk insert data , To add multiple records to a table based on the number of inserts , If the number of times is equal to 10 Stop the next time ,leave Use 
DELIMITER $
CREATE PROCEDURE mypro2(IN num INT)
BEGIN
	DECLARE i INT DEFAULT 11;
	a:WHILE i<=num DO
		INSERT INTO magic VALUES(i,CONCAT('m_',i));
		IF i>=20 THEN LEAVE a;-- leave Use 
		END IF;
		SET i=i+1;
	END WHILE a;
END;$

--  Call query 
CALL mypro2(100);
SELECT * FROM magic;

 Insert picture description here

It can be seen from the picture that : The parameter passed in is 100, But it only inserts 10 Data

--  Case study : Batch insert , towards magic Add multiple pieces of data to the table , Insert only even columns ,iterate Use 
--  I'll delete it first magic The data in the table 
delete from magic;
--  Create storage 
DELIMITER $
CREATE PROCEDURE mypro3(IN num INT)
BEGIN
	DECLARE i INT DEFAULT 0;
	a:WHILE i<=num DO
	SET i = i+1;
		IF MOD(i,2) != 0 THEN ITERATE a;
		END IF;
		INSERT INTO magic VALUES(i,CONCAT(' ha-ha _',i));
	END WHILE a;
END;$
--  test 
CALL mypro3(10);
SELECT * FROM magic;

 Insert picture description here

loop loop
--  grammar :
 label  loop
	 The loop body 
	end loop  label ;
--  It's an unconditional loop 

Case study :

--  Case study : Sum by accumulation 1-100
delimiter $
create procedure mypro4(in num int)
begin
	declare sum int default 0;--  and 
	declare i int default 1;
	loop_name:loop
	--  Judge 
		if i> num then
			leave loop_name; --  If the judgment condition is established, the current cycle will be ended 
		end if;
		set sum=sum+i;--  Add up 
		set i=i+1;
	end loop loop_name;
	select sum;--  Output 
end;$

--  call 
CALL mypro4(100);

 Insert picture description here

repeat loop

Be similar to java Inside do-while Execute before judge

--  grammar 
 label  repeat
	 The loop body 
	until  end   The loop condition 
end repeat;


--  Case study : Sum by accumulation 1-100
delimiter $
create procedure mypro5(in num int)
begin
	declare sum int default 0;--  and 
	declare i int default 1;
	repeat
	set sum=sum+i;
	set i=i+1;
	until  i> num end repeat;--  If the judgment condition is established, the current cycle will be ended 
	select sum;
end;$
--  test 
CALL mypro5(100);

 Insert picture description here

seventeen 、MySQL trigger

1. Principle concept

mysql A trigger for is similar to a stored procedure , It's a special stored procedure , The difference is , Use... In executing stored procedures call The statement calls , And triggers don 't need to use call Statement to call , It doesn't need to be started by hand , It's a predefined event , Only when this event is triggered will it be mysql Automatic database call

2. classification

INSERT

UPDATE

DELETE

3. grammar

--  Create trigger 
create trigger  Trigger Name 
	before | after
	insert | update | delate
	on  Table name 
	for each row
		 Trigger body ;
trigger: keyword 
before | after : When the trigger was triggered , Trigger before or after the statement that activates it 
insert | update | delate
	insert: Activate trigger when a new row is inserted into the table 
	update: Activate trigger when changing data in table 
	delete: Activate trigger when data is deleted from the table 
 Table name : The name of the table associated with the trigger 
for each row: Line level triggers , Affect every line 
 Trigger body : The action to be performed when activated ,mysql sentence ;

4. Specific operation

Case study 1:

--  establish before  Type of trigger 
--  Create an employee table 
create table emp(
	eid int primary key,
	ename varchar(30) not null,
    salary float
)

--  Create a tri1  The trigger of , The trigger condition is to send the data table emp Before inserting data into , For newly inserted salary field value   In sum 
create trigger mytri1
	before insert on emp
	for each row
	set @sum=@sum+new.salary
	
--  Use 
--  Declare variables first 
set @sum =0;
-- insert data 
insert into emp values(1,'admin',5000),(2,'magic',4800);
--  Query variables 
SELECT @sum;

[ Failed to transfer the external chain picture , The origin station may have anti-theft chain mechanism , It is suggested to save the pictures and upload them directly (img-vksiEWVB-1608645755886)(C:\Users\24582\AppData\Roaming\Typora\typora-user-images\image-20201222162903077.png)]

Case study 2:

--  establish after  Type of trigger 
--  Create an employee table 
create table emp2(
	eid int primary key,
	ename varchar(30) not null,
    salary float
)
--  Create an employee table 
create table emp3(
	eid int primary key,
	ename varchar(30) not null,
    salary float
)
--  Create a trigger , The trigger condition : To the data table emp2 After inserting data in , To the data table emp3 Insert the same data in 
create trigger mytri2
	after insert on emp2
	for each row
	insert into emp3 values(new.eid,new.ename,new.salary)
-- new: Add a column of data in a row , The new value 
-- old: A column of data when deleting or modifying a row , The old value 

--  insert data 
INSERT INTO emp2 VALUES(1,'tom',5000),(2,'rose',4500);
--  test 
SELECT * FROM emp2;
SELECT * FROM emp3;

 Insert picture description here

Case study 3:

-- old Use 
--  Create a trigger , The trigger condition : To the data table emp2 After modifying the data in , To the data table emp3 Insert the data before 
create trigger mytri3
	after update on emp2
	for each row
	insert into emp3 values(old.eid,old.ename,old.salary)
	
--  Modifying data 
update emp2 set ename='jack',salary=500 where eid=3;

-- test 
SELECT * FROM emp2;
SELECT * FROM emp3;

emp2:

 Insert picture description here

emp3:
 Insert picture description here

Here I'll explain : I'm doing a test old When , Put the trigger 2 Deleted , And added in advance bob, Prevent triggers 2 The role of interference

eighteen 、 Three paradigms of database

1. theory

Theoretically speaking, the core of database design paradigm is that it can design convenient extension , And store precise data structures , And effectively meet the needs of our program development

All the given design paradigms can only be used as a reference , In the initial structure, we can design according to the paradigm , But at the end of the delivery, all the paradigms are almost broken , Reduce the number of multi table queries or complex queries as much as possible according to business needs

So if you look at it from another level , There is no concrete schema for database design , All said , There is only one core : Ensure the effective storage of data , Just guarantee the performance of the query

2. The first design paradigm ( Operation list )

The design requirements :“ Each field in the data table is indivisible ”

--  for example : Now we have the following user data table 
create table  user (
	 The user id  int,
     User name  varchar(30),
     Contact information  varchar(200),
    constraink pk_ The user id  primary key( The user id )
)

Contact information can be further split , It can be determined by address 、 mailbox 、 WeChat 、qq、email、 Mobile phone etc. , So we found that a field can still be split , Then such a design does not conform to the first design paradigm , Because some fields can be split ;

--  for example : Now we have the following user data table 
create table  user (
	 The user id  int,
     User name  varchar(30),
    email varchar(200),
     contact number  varchar(200),
     Address  varchar(100),
    constraink pk_ The user id  primary key( The user id )
)

At this time, the fields of the table can no longer be divided , So it's in line with the first design paradigm ;

But what needs to be reminded is , The first paradigm is strictly a table defined using standard data types , But some data types cannot be split , such as :

create table  user (
	 The user id  int,
     User name  varchar(30),
    email varchar(200),
     contact number  varchar(200),
     Address  varchar(100),
     Birthday _ year  varchar(4),
     Birthday _ month  varchar(2),
     Birthday _ year  varchar(2),
    constraink pk_ The user id  primary key( The user id )
)

We can use a data type Date To express , So data types like this are no longer split

3. Second normal form

Design principles :“ There is no partial functional dependence of non key fields on any candidate fields in the data table ”

To understand this sentence , Let's start with !!!

① Function relation : The so-called functional relationship , We can easily understand that there is no mathematical relationship between two columns in the data table

--  for example : Now there's an order form 
create table  Order (
	 The order no.  int,
     Order date  date,
	 item pricing  int,
     The number  int,
     Commodity price  int,
)

This is the time , We found the following relationships in the designed table : Product summary = item pricing * The number

② Function dependency : It refers to the unique field that can be successfully pushed through several fields , For example, there is now

handsome guy 、 The sun 、 Good technology 、 Have an ambition 、 Good to learn ——> You can push a result , Isn't that me 🤭. But we have only one field , Can we find the only result ?

--  for example : Design a data table of students' course selection , A student can choose to take more than one course , A course can have more than one student , And each student has his own score after each course :
--  Now it is required to design the following operation : If we design according to the first paradigm at this time , The design is as follows :
create table  Students' course selection (
	 Student number  int,
     The student's name  varchar(30),
     Student age  int,
     Course name  varchar(50),
     course credit  int,
     Test results  float,
)

This design conforms to the first paradigm , But what are the problems if it is used like this ? We add some data to observe

insert into  Students' course selection  values(1,' Zhang San ',18,'mysql',2,90);
insert into  Students' course selection  values(2,' Li Si ',19,'java',1,89);
insert into  Students' course selection  values(3,' Wang Wu ',17,'oracle',3,86);
insert into  Students' course selection  values(1,' Zhang San ',18,'java',1,94);

If we design according to the first paradigm , The current code will have the following problems :

  • Unable to set a suitable field as the primary key , Because a student can choose multiple courses ;
  • Duplicate data , The course information is repeated , Because the name of a course will not be changed , Credit information is the same
  • Data update trouble , When the name of a course needs to be updated , After updating, then update n rows
  • If there is a course no one can choose , Then the course information is complete

Obviously, the relationship is a many to many relationship , Because a student can take more than one course , A course can be chosen by more than one student , This is an obvious many to many relationship , So the design above is too bad

improvement :

--  Student list 
create table  Student (
	 Student number  int primary key,
     The student's name  varchar(30),
     Student age  int
)

--  The curriculum 
create table  Course (
     Course number  int primary key,
     Course name  varchar(50),
     course credit  int
)

--  Student selection table 
create table  Students' course selection (
	 Student number  int references  Student ( Student number ),
	 Course number  int references  Course ( Course number ),
     Test results  float
)

--  Add data 
insert into  Student  values(1,' Zhang San ',18);
insert into  Student  values(2,' Zhao si ',20);
insert into  Student  values(3,' Wang Wu ',19);

insert into  Course  values(001,'mysql',2);
insert into  Course  values(002,'java',1);
insert into  Course  values(003,'oracle',3);

insert into  Students' course selection  values(1,001,90);
insert into  Students' course selection  values(2,002,89);
insert into  Students' course selection  values(3,003,86);
insert into  Students' course selection  values(1,002,94);

This kind of design solves the previous problems , If you want to change the name of a course, you can modify the table , Easy to modify , It's also very convenient to inquire , There will be no data redundancy , All data can be maintained in their own entity table

4. Third normal form

Design principles :“ There is no transfer function dependence of non key fields on any candidate key fields in the data table ”

In fact, transfer function dependencies are like hierarchical relationships

Employee number full name Position Department name Location Wages
0001 Zhang San The manager Technology Department Xi'an 30000.00

This design conforms to the first design paradigm , Can not be further divided , But there is a function transfer relationship in the above paradigm ;

The name of his department can be known by his employee number , But through the name of the Department, you can find the location of the Department , Then there is a transitive relationship between these three fields , So this kind of design does not conform to the third paradigm

--  Case study : Ask to design a data table of the relationship between students and school , Obviously, there are multiple students in a school 

--  analysis : First normal form : If the school is large , Create table time , School name 、 The school address has to be repeated many times , There will be redundancy , If the school changes its name, the number of changes will be considerable 

--  Second normal form : A school can have more than one student , One student can be in more than one student , Obviously not !

--  Take the third paradigm :
--  There are many students in a school , Multiple students belong to one school , There is a foreign key constraint in the employee table 

This process belongs to a one to many relationship !!!

5. summary

The first paradigm is the single table design principle

The second paradigm is the many to many relationship

The third paradigm is the one to many relationship

In development , We generally adopt the third paradigm

版权声明
本文为[osc_ q7wo0k6s]所创,转载请带上原文链接,感谢
https://cdmana.com/2020/12/20201224094617711e.html

Scroll to Top