mysql表一对多关系,mysql表与表之间的关系(多对多,一对多)-CSDN博客

#创建数据库

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;

原文：http://www.cnblogs.com/linjiqian/p/4488959.html