sql语句的创建关联表和简单的跨表查询

今天简单的看了下sql语法的基础，并写了这篇博客，如有不对，求评论指点

简单介绍

练习的是 班级表，老师表，学生表
班级表和老师表多对多，班级表和学生表一对多

表的创建

创建数据库

create database exercise;

创建一个用户并授权

grant select,DELETE ON exercise.* to sunxiao@localhost identified by '123456';

指定使用某个数据库

use exercise;

老师表、班级表、学生表

#创建老师表
create table teacher(
	id int auto_increment PRIMARY key,  #主键 自增
	tea_name varchar(50),               #老师名
	tea_sex int CHECK(tea_sex='0' or tea_sex='1'),    #老师性别
	tea_ID_card VARCHAR(18)             #老师身份账号
)default charset=utf8;  #解码方式

#创建学生表
create table student(
	id int auto_increment PRIMARY key,
	stu_name VARCHAR(20),
	stu_sex INT CHECK(stu_sex='0' or stu_sex='1'),   #设置性别只能为0或1，0为男，1为女
	stu_age int,
	stu_ID_card VARCHAR(18)
)default charset=utf8;

#创建班级表
CREATE table class(
	id int auto_increment PRIMARY key,
	class_name VARCHAR(30)
);

#创建班级和老师的关联表（多对多中间表）
create table cla_teacher(
	ct_id int AUTO_INCREMENT PRIMARY KEY,
	class_id int,
	teacher_id int
);

给老师表创建一个唯一索引

CREATE UNIQUE INDEX ID on teacher(ID_card);   #在身份证一列创建唯一索引

#查看已创建的索引
show index from teacher;

唯一索引可以使这一列的数据不可重复

添加外键，使表关联

#老师和班级多对多，创建一个多对多的关联表，再关联表内创建两个外键，分别绑定老师和班级


#添加班级外键
ALTER TABLE cla_teacher ADD CONSTRAINT class_teacher FOREIGN KEY(class_id) REFERENCES class(id);
#ALTER TABLE主表名 ADD CONSTRAINT 外键名 FOREIGN KEY（主表作为外键的列名）REFERENCES 从表名（从表列名）

#添加老师外键
ALTER TABLE cla_teacher ADD CONSTRAINT teacher_class FOREIGN KEY(teacher_id) REFERENCES teacher(id);
#ALTER TABLE主表名 ADD CONSTRAINT 外键名 FOREIGN KEY（主表作为外键的列名）REFERENCES 从表名（从表列名）


#添加老师外键（一对多） 老师一班级多，外键放在班级里面
ALTER TABLE student ADD CONSTRAINT classes_foreign_key FOREIGN KEY(stu_classes) REFERENCES class(id)
#ALTER TABLE主表名 ADD CONSTRAINT 外键名 FOREIGN KEY（主表作为外键的列名）REFERENCES 从表名（从表列名）  #一对多外键添加方式

往表内插入数据的方式

#插入数据
insert into teacher(`tea_name`,`tea_sex`,`tea_ID_card`) values('teacher_1','0','142727199706011011');

单表查询

#单表查询所有数据
SELECT * FROM student

#单表查询满足条件的数据
SELECT * from student WHERE stu_age > 18

where 后面跟判断条件

一对多跨表查询

#跨表查询（查询班级为1804的有哪些学生）（一对多跨表查询，通过外键查询）

SELECT * FROM student 
INNER JOIN class on student.stu_classes = class.id AND class.class_name = '1804'

#or

SELECT * from student,class 
where student.stu_classes = class.id 
AND class.class_name = '1804'

多对多跨表查询

#跨表查询（查询班级为1804的有哪些老师）(多对多跨表查询，通过中间表查询)

SELECT * FROM teacher  
INNER JOIN cla_teacher ON cla_teacher.teacher_id = teacher.id 
INNER JOIN class ON class.id = cla_teacher.class_id and class.class_name = '1804'

#or

SELECT * FROM teacher,cla_teacher,class 
where cla_teacher.teacher_id = teacher.id 
AND class.id = cla_teacher.class_id 
AND class.class_name = '1805'

这些时内连接查询

外连接查询

#左连接  显示左表全部和右表满足条件的数据
SELECT * FROM student LEFT OUTER JOIN class on student.stu_classes = class.id AND class.class_name = '1804'

#右连接  显示右表全部和左表满足条件的数据
SELECT * FROM student RIGHT OUTER JOIN class on student.stu_classes = class.id AND class.class_name = '1804'