<>MySql Fundamentals

<> Multi table design :

When storing different kinds of information
Reduce data redundancy

How do tables relate to each other
Database design paradigm : 1. Atomicity of columns ( No more Division ) 2. Have a primary key ( Unique identity column ), Other information in the table depends on the primary key 3. A table stores a kind of information , Associate other tables , Eliminate data redundancy
Student information sheet : Student number , full name , Gender , birthday , cell-phone number , grade ID, Registration time Grade table : grade ID, Grade name -- Teacher information sheet Job number , full name , Gender , grade ID Course information sheet --
Create grade table CREATE TABLE grade(id INT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(10))
-- Create student table CREATE TABLE student( num INT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(
10) NOT NULL, sex CHAR(1), birthday DATE, phone CHAR(11), grade_id INT, -- Foreign key constraints
reg_timeDATETIME ) -- Weak relationship , When a table is associated with a table , There are no constraints -- Strong relationship , There are constraints between tables -- Modify table , Add foreign key constraint --
Foreign key constraints : The foreign key corresponds to the primary key in another table ALTER TABLE student ADD CONSTRAINT grade_fk FOREIGN KEY(
grade_id) REFERENCES grade(id) /* Relationship between tables one-on-one : A person has a student number , One student number corresponds to one person
One to many : A person belongs to a grade Many to one : More than one student belongs to one grade Many to many : One can take more than one course , A course can be chosen by more than one student */ CREATE TABLE course(
idINT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(10) ) -- Many to many design a relationship table to store the relationship between students and courses
CREATE TABLE student_course( stu_num INT, course_id INT, CONSTRAINT stu_fk
FOREIGN KEY(stu_num) REFERENCES student(num), CONSTRAINT course_fk FOREIGN KEY(
course_id) REFERENCES course(id) )
<> Subquery
-- Subquery : Appears in other statements (insert update delete select) In select sentence , Become a subquery or inner query --
External query statements , It is called primary query or external query -- stay insert Using subqueries in statements INSERT INTO stu SELECT * FROM student --
stay update Using subqueries in UPDATE student SET sex=' male ' WHERE num IN (SELECT num FROM stu WHERE
score>80) -- stay delete Using subqueries in statements DELETE FROM student WHERE num IN(SELECT num FROM
stuWHERE score>=90) -- Using subqueries in query statements -- select behind : Only scalar subqueries are supported ( Line by line ) SELECT ( SELECT ts
.num FROM student ts WHERE ts.num = t.num ),t.name FROM student t --
stay where Use column subquery later SELECT * FROM student WHERE score IN (SELECT score FROM student
WHERE score>60) -- stay where Use row subquery later Multiple conditions are satisfied at the same time SELECT * FROM student WHERE (num,
score)=( SELECT MIN(num),MAX(score) FROM student ) -- stay from Post use subquery Only table subqueries are supported ( Multi row multi column )
-- The result of a query , It can be used as a temporary watch SELECT * FROM( SELECT COUNT(*)c ,sex FROM student GROUP
BY sex) t WHERE t.c >= 2

Technology
©2019-2020 Toolsou All rights reserved,
Huawei 2021 session Hardware Engineer Logical post (FPGA) Super detailed surface !!!Vue-element-admin upgrade ui edition virtual machine VMware Download and install the most detailed tutorial !C++ Move constructor and copy constructor sound of dripping water java Backstage interview pygame Realize full screen mode and adjustable window size mysql Database setting character set configuration modification my.ini file (windows)30 What's the experience of being a junior programmer at the age of 20 C++ Multithreading programming ( Summary of common functions and parameters )python_ cherry tree