mysql表与表之间的关系(多对多,一对多)

#创建数据库
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;

#删除数据重置行数(相当于删除掉整个表,然后再创建)
TRUNCATE TABLE test1;

############表与表之间的关系#####################
###一对多(B表外键字段存着A表的唯一主键表示拿一行被A表引用)
#主表user
CREATE TABLE t_user(
id VARCHAR(22),
username VARCHAR(22),
PASSWORD VARCHAR(32)
);
#主表的主键
ALTER TABLE t_user ADD CONSTRAINT PRIMARY KEY (id);

#从表:book表
CREATE TABLE t_book(
id VARCHAR(22),
title VARCHAR(55),
author VARCHAR(50),
user_id VARCHAR(32) #外键
);

#从表外键引用主表主键
#格式:ALTER TABLE 从表名 ADD CONSTRAINT FOREIGN KEY (从表外键) REFERENCES 主表名 (主表主键);
ALTER TABLE t_book ADD CONSTRAINT FOREIGN KEY (user_id) REFERENCES t_user (id);

###测试(查询)
#主表存入数据
INSERT INTO t_user(id,username,PASSWORD) VALUES(‘u001‘,‘琳琳‘,‘1234‘);
INSERT INTO t_user(id,username,PASSWORD) VALUES(‘u002‘,‘问我‘,‘1234‘);
INSERT INTO t_user(id,username,PASSWORD) VALUES(‘u003‘,‘恩恩‘,‘1234‘);

#从表存入数据
INSERT INTO t_book(id,title,author,user_id) VALUES(‘b001‘,‘短剑‘,‘丽丽‘,‘u001‘);
INSERT INTO t_book(id,title,author,user_id) VALUES(‘b002‘,‘宿舍‘,‘恩恩‘,‘u003‘);
INSERT INTO t_book(id,title,author,user_id) VALUES(‘b003‘,‘方法‘,‘额额‘,‘u001‘);
INSERT INTO t_book(id,title,author,user_id) VALUES(‘b004‘,‘覆盖‘,‘44‘,‘u002‘);

#主表主键不能被删除或更新,以为从表在使用
DELETE FROM t_user WHERE id=‘u001‘;

#############查询
#1,笛卡尔积,两个表的乘积集合
SELECT * FROM t_user,t_book;
SELECT COUNT(*) FROM t_user,t_book;

#2 隐式内连接
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 JOIN t_book b ON u.id =b.`user_id`;

###4外链接
#左外连接:查询A表所有内容,设定条件显示B表内容
SELECT * FROM t_user u LEFT OUTER JOIN t_book b ON u.id = b.user_id;
#右外链接:查询B表所有内容,显示符合条件的A表内容
SELECT * FROM t_user u RIGHT OUTER JOIN t_book b ON u.id = b.user_id;

#######多对多(多对多是B表多项被A表多项引用)
##主表:学生表
CREATE TABLE m_student(
id VARCHAR(32) PRIMARY KEY, #主键
NAME VARCHAR(50),
age INT
);
##主表:中间表,学生课程表
CREATE TABLE m_course(
id VARCHAR(32) PRIMARY KEY, #主键
content VARCHAR(50),
teacher VARCHAR(50)
);
##从表:中间表,学生课程表
CREATE TABLE m_student_course(
student_id VARCHAR(32), #学生对应外键
course_id VARCHAR(32) #课程表对应外键
);

#######关系
###中间表与学生表:主外键关系
ALTER TABLE m_student_course ADD CONSTRAINT FOREIGN KEY (student_id) REFERENCES m_student (id);

###中间表与课程表:主键关系
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 表名 DROP FOREIGN KEY 外键名称;

###测试
INSERT INTO m_student(id,NAME,age) VALUES(‘s001‘,‘宿舍‘,‘22‘);
INSERT INTO m_student(id,NAME,age) VALUES(‘s002‘,‘宿33‘,‘32‘);

INSERT INTO m_course(id,content,teacher) VALUES(‘c001‘,‘去你妹‘,‘问我‘);
INSERT INTO m_course(id,content,teacher) VALUES(‘c002‘,‘sad‘,‘恩恩‘);
INSERT INTO m_course(id,content,teacher) VALUES(‘c003‘,‘fdsa‘,‘额额‘);

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‘);

##查询:某人学某课
#隐式内连接
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;