MySQL table-to-table relationship (many-to-many, one-to-many)

#创建数据库
CREATE DATABASE day15;
#使用
Use DAY15;
#创建表
CREATE TABLE Test1 (
ID INT PRIMARY KEY auto_increment, #这是主键
Num INT UNIQUE, #这个唯一键不起作用?
NAME VARCHAR (22)
);
#存入数据
INSERT into Test1 (NAME) VALUES (' AAA ');

#删除数据但是不重置自动增长数
DELETE from Test1;

#删除数据重置行数 (equivalent to deleting the entire table and then creating it)
TRUNCATE TABLE test1;

########### #表与表之间的关系 #####################
# # #一对多 (b-Table foreign key field with a table's unique primary key indicates a row is referenced by a table)
#主表user
CREATE TABLE T_user (
ID VARCHAR (22),
Username VARCHAR (22),
PASSWORD VARCHAR (32)
);
#主表的主键
ALTER TABLE t_user ADD CONSTRAINT PRIMARY KEY (ID);

#从表: Book table
CREATE TABLE T_book (
ID VARCHAR (22),
Title VARCHAR (55),
Author VARCHAR (50),
user_id VARCHAR (+) #外键
);

#从表外键引用主表主键
#格式: ALTER table from the table name ADD CONSTRAINT FOREIGN key (from the table key) REFERENCES The primary table name (primary table primary key);
ALTER TABLE t_book ADD CONSTRAINT FOREIGN KEY (user_id) REFERENCES t_user (ID);

# # #测试 (query)
#主表存入数据
INSERT into T_user (Id,username,password) VALUES (' u001 ', ' Linda ', ' 1234 ');
INSERT into T_user (Id,username,password) VALUES (' u002 ', ' Ask me ', ' 1234 ');
INSERT into T_user (Id,username,password) VALUES (' u003 ', ' nn ', ' 1234 ');

#从表存入数据
INSERT into T_book (id,title,author,user_id) VALUES (' b001 ', ' dagger ', ' Lili ', ' u001 ');
INSERT into T_book (id,title,author,user_id) VALUES (' b002 ', ' dorm ', ' nn ', ' u003 ');
INSERT into T_book (id,title,author,user_id) VALUES (' b003 ', ' method ', ' forehead ', ' u001 ');
INSERT into T_book (id,title,author,user_id) VALUES (' b004 ', ' overlay ', ' u002 ');

#主表主键不能被删除或更新, thinking that from the table in use
DELETE from T_user WHERE id= ' u001 ';

############ #查询
#1, Cartesian product, two-table product set
SELECT * from T_user,t_book;
SELECT COUNT (*) from T_user,t_book;

#2 implicit in-connection
SELECT * from T_user,t_book WHERE t_user.id = t_book.user_id;
SELECT t_user.username,t_book.title from T_user,t_book WHERE t_user.id=t_book.user_id;

#3内连接
SELECT u.username,b.title from T_user u INNER joins T_book b on U.id =b. ' user_id ';

# # #4外链接
#左外连接: Query All the contents of a table, set conditions to display the contents of table B
SELECT * from T_user u left OUTER joins T_book b on u.id = b.user_id;
#右外链接: Query all the contents of table B to display the content of table A that meets the criteria
SELECT * from T_user U-OUTER JOIN t_book b on u.id = b.user_id;

###### #多对多 (Many-to-many is B-table multiple items are a-table multiple references)
# #主表: Student table
CREATE TABLE M_student (
ID VARCHAR (PRIMARY KEY), #主键
NAME VARCHAR (50),
Age INT
);
# #主表: Intermediate table, Student timetable
CREATE TABLE M_course (
ID VARCHAR (PRIMARY KEY), #主键
Content VARCHAR (50),
Teacher VARCHAR (50)
);
# #从表: Intermediate table, Student timetable
CREATE TABLE M_student_course (
student_id VARCHAR (+), #学生对应外键
course_id VARCHAR (+) #课程表对应外键
);

###### #关系
# # #中间表与学生表: Primary foreign key relationship
ALTER TABLE m_student_course ADD CONSTRAINT FOREIGN KEY (student_id) REFERENCES m_student (ID);

# # #中间表与课程表: Primary key relationship
ALTER TABLE m_student_course ADD CONSTRAINT FOREIGN KEY (course_id) REFERENCES m_course (ID);

# # #联合主键
ALTER TABLE m_student_course ADD CONSTRAINT PRIMARY KEY (student_id,course_id);

# # #外键删除
ALTER table name DROP FOREIGN key foreign key name;

# # #测试
INSERT into M_student (id,name,age) VALUES (' s001 ', ' dorm ', ' 22 ');
INSERT into M_student (id,name,age) VALUES (' s002 ', ' Homestay 33 ', ' 32 ');

INSERT into M_course (id,content,teacher) VALUES (' c001 ', ' go to Your sister ', ' ask me ');
INSERT into M_course (id,content,teacher) VALUES (' c002 ', ' sad ', ' nn ');
INSERT into M_course (id,content,teacher) VALUES (' c003 ', ' fdsa ', ' forehead ');

INSERT into M_student_course (student_id,course_id) VALUES (' s001 ', ' c001 ');
INSERT into M_student_course (student_id,course_id) VALUES (' s002 ', ' c001 ');
INSERT into M_student_course (student_id,course_id) VALUES (' s001 ', ' c002 ');
INSERT into M_student_course (student_id,course_id) VALUES (' s002 ', ' c003 ');

# #查询: Someone learns a lesson
#隐式内连接
SELECT s.name, content from M_student s, M_student_course SC, m_course C
WHERE s.id = sc.student_id and sc.course_id = c.id;

#内连接
SELECT s.name, c.content from M_student s
INNER JOIN M_student_course sc on s.id = sc.student_id
INNER JOIN m_course c on sc.course_id = C.id;

MySQL table-to-table relationship (many-to-many, one-to-many)