多表
-- 多对多
-- 班级表
CREATE TABLE banji(
id INT PRIMARY KEY AUTO_INCREMENT,
`name` VARCHAR(10) NOT NULL
);
INSERT INTO banji(`name`) VALUES('java1807'),('java1812');
SELECT * FROM banji;
-- 学生表 reference:参考,引用
CREATE TABLE student(
id INT PRIMARY KEY AUTO_INCREMENT,
`name` VARCHAR(10) NOT NULL,
age INT,
gender CHAR(1),
banji_id INT,
FOREIGN KEY(banji_id) REFERENCES banji(id)
);
INSERT INTO student(`name`,age,gender,banji_id)
VALUES('张三',20,'男',1),('李四',21,'男',2),('王五',20,'女',1);
-- Cannot add or update a child row: a foreign key constraint fails (`java1812`.`student`, CONSTRAINT `student_ibfk_1` FOREIGN KEY (`banji_id`) REFERENCES `banji` (`id`))
INSERT INTO student(`name`,age,gender,banji_id)
VALUES('张三',20,'男',3);
SELECT * FROM student;
-- 课程表
CREATE TABLE course(
id INT PRIMARY KEY AUTO_INCREMENT,
`name` VARCHAR(10) NOT NULL,
credit INT COMMENT '学分'
);
INSERT INTO course(`name`,credit) VALUES('Java',5),('UI',4),('H5',4);
SELECT * FROM course;
-- 班级课程表
CREATE TABLE banji_course(
-- id int PRIMARY KEY AUTO_INCREMENT,
banji_id INT,
course_id INT,
PRIMARY KEY(banji_id,course_id), -- 联合主键
FOREIGN KEY(banji_id) REFERENCES banji(id), -- banji_id既是联合主键又是外键
FOREIGN KEY(course_id) REFERENCES course(id) -- course_id既是联合主键又是外键
);
INSERT INTO banji_course(banji_id,course_id) VALUES(1,1),(1,3),(2,1),(2,2),(2,3);
SELECT * FROM banji_course;
-- 子查询:嵌套查询,一个查询语句结果是另一个查询语句的条件
-- 查询班级是Java1812班所有学生信息
SELECT * FROM student WHERE banji_id=2;
SELECT id FROM banji WHERE `name`='Java1812';
SELECT * FROM student WHERE banji_id=(SELECT id FROM banji WHERE `name`='Java1812');
-- 班级是Java1807班或者Java1812班所有学生信息
SELECT * FROM student WHERE banji_id=1 OR banji_id=2;
SELECT * FROM student WHERE banji_id IN(1,2);
SELECT id FROM banji WHERE `name`='Java1807' OR `name`='Java1812'; -- 1,2
SELECT * FROM student WHERE banji_id IN(SELECT id FROM banji WHERE `name`='Java1807' OR `name`='Java1812');
-- 计算字段使用子查询:班级id 班级名字 班级人数
-- 执行这条查询遵循下面的步骤:
-- 1、从banji表检索班级列表
-- 2、对检索出的每个banji,统计其在student表中的数量
SELECT id,
`name`,
(SELECT COUNT(*)
FROM student
WHERE student.banji_id=banji.id) AS total_count
FROM banji
ORDER BY `name`;
total_count是一个计算字段,它是由括号中的子查询建立的,该子查询对检索出的每个banji执行一次
总结:
1、"=":要求子查询只有一个结果。 "in":子查询可以有多个结果。
2、子查询的SELECT语句只能查询单个列,企图检索多个列将返回错误。
3、能嵌套的子查询的数目没有限制,不过在实际使用时由于性能的限制,不能嵌套太多的子查询。
子查询也可以使用下面的联结来实现
等值连接
从左表中取出每一条记录,去右表中与所有的记录进行匹配:匹配必须是某个条件在左表中与右表中相同最终才会保留结果,否则不保留
笛卡尔积:
由没有联结条件的表关系返回的结果为笛卡尔积。检索出的行的数目将时第一个表中的行数乘以第二个表中的行数。通俗的说就是查询所得的结果行数是两张表行数的乘积。
返回笛卡尔积的联结,也称为叉联结cross join。
等值连接:
注意:联结查询非常消耗资源,因此应该注意,不要联结不必要的表。联结的表越多,性能下降越厉害。
内联结
内连接有两种写法,一种是inner join,另一种是join,这两种写法都是一样的,可以理解为join是inner join的缩写。还可以看出,等值连接和内连接的效果一样,但是开发中建议使用内连接
-- 学生姓名 班级名称 课程名称 学分
SELECT *
FROM student s,banji b,banji_course bc,course c
WHERE s.banji_id=b.id AND b.id=bc.banji_id AND bc.course_id=c.id;
总结:多表查询主要是注意下面两点
1、整个查询涉及到几张表,涉及到几张表就连接这几张表。
2、如果涉及到这几张表的关系搞不清楚,画一下ER图,弄清楚表和表之间的关系(就是根据外键建立的关系)
-- 班级id 班级名字 班级人数
SELECT b.id,b.`name`,COUNT(*)
FROM student AS s INNER JOIN banji AS b
ON s.banji_id=b.id
GROUP BY b.id;
inner join on、left join on、right join on区别
inner join on 只有左右两个表有关联的才查询出来
left join on 左表中都显示出来,右表没有显示空
right join on 右表都显示,左表没有显示空
左连接,也成为左外连接:从左表那里返回所有的行,即使在右表中没有匹配的行
SELECT *
FROM student as s LEFT JOIN banji as b
on s.banji_id=b.id;
模糊查找
语法形式:字段 like '要查找字符'
说明:
1、like模糊查找用于对字符类型的字段进行字符匹配查找。
2、要查找的字符中,有两个特殊含义的字符:% , _:
2.1: %含义是:代表0或多个的任意字符
2.2: _含义是:代表1个任意字符
3、语法:like '%关键字%'
SELECT * FROM student WHERE `name` LIKE '张%'; -- 以张开头 SELECT * FROM student WHERE `name` LIKE '张_'; -- 以张开头,而且名字是两个字 SELECT * FROM student WHERE `name` LIKE '%张%'; -- 名字里面只要有张就可以
注意: NULL
通配符%看起来像是可以匹配任何东西,但有个例外,这就是NULL,
SELECT * FROM student WHERE `name` LIKE '%';
不会匹配name为NULL的行
注意:
SQL的通配符很有用,但这种功能是有代价的,即通配符搜索要消耗更长的处理时间,使用通配符的技巧:
1、不要过度使用通配符。如果其他操作符能达到相同的目的,应该使用其他操作符。
2、在确实需要使用通配符时,也尽量不要把它们用在搜索模式的开始处 '%张'。把通配符置于开始处,搜索起来是最慢的。