数据库多表查询 （九）

多表查询

学生表、班级表、课程表、班级课程表

一、表的连接查询（外连接、内连接、全连接等）

1.建立班级、课程等表

-- 班级表
CREATE TABLE banji(
    id INT PRIMARY KEY AUTO_INCREMENT,
    `name` CHAR(10) NOT NULL
);
INSERT INTO banji(`name`) VALUES('java1807'),('java1812');
SELECT * FROM banji;

-- 学生表
CREATE TABLE student0(
   id INT PRIMARY KEY AUTO_INCREMENT,
   `name` CHAR(10),
   age INT,
   gender CHAR(1),
   banji_id INT,
   FOREIGN KEY(banji_id) REFERENCES banji(id)
);

INSERT INTO student0(`name`,age,gender,banji_id)
 VALUES('张三',20,'男',1),('李四',21,'男',2),('王五',20,'女',1);


-- 课程表

CREATE TABLE course(	
   id INT PRIMARY KEY AUTO_INCREMENT,
   `name` CHAR(10) NOT NULL,
   credit INT COMMENT '学分'
);

INSERT INTO course (`name`,credit) VALUES('java',5),('h5',5),('ui',5)

-- 班级课程表

CREATE TABLE banji_course(	
   banji_id INT ,
   course_id INT,
   PRIMARY KEY (banji_id,course_id), -- 联合主键
   FOREIGN KEY (banji_id) REFERENCES banji(id),
   FOREIGN KEY (course_id) REFERENCES course(id)
);

INSERT INTO banji_course(banji_id,course_id) VALUES(1,1),(1,2),(2,1),(2,2),(2,3);
SELECT * FROM banji_course;


SELECT UUID();

2.子查询

SELECT * FROM student0 WHERE banji_id=2;
SELECT id FROM banji WHERE `name`='java1812';
SELECT * FROM student0 WHERE banji_id=(SELECT id FROM banji WHERE `name`='java1812');

-- 班级是java1807班或者java1812班所有学生信息
SELECT * FROM student0 WHERE banji_id=1 OR banji_id=2;
SELECT id FROM banji WHERE `name`='java1807' OR `name`='java1812';
SELECT * FROM student0 WHERE banji_id IN(SELECT id FROM banji WHERE `name`='java1807' OR `name`='java1812');

-- "="：要求子查询只有一个结果。 "in"：子查询可以有多个结果

3.inner join（关联查询）

– inner join on 只有左右两个表有关联的才查询出来
– left join on 左表中都显示出来，右表没有显示空
– right join on 右表都显示，左表没有显示空

SELECT * FROM student0 AS s INNER JOIN   banji AS b ;-- (笛卡尔积式的连接)
SELECT * FROM student0 AS s INNER JOIN   banji AS b ON s.banji_id=b.id;
 
 -- 学生id，学生姓名，年龄，性别，班级名
 SELECT s.id AS '学生id',s.name AS '姓名',s.age AS '年龄',s.gender AS '性别',b.name  AS '班级'		
 FROM student0 AS s INNER JOIN	banji AS b ON s.banji_id = b.id; 


-- 列出所有学生学习的课程名称
-- 学生姓名   班级名称   课程名称    学分
SELECT s.name AS '学生姓名',b.name AS '班级名称',c.name AS '课程名称',c.credit AS '学分'
FROM student0 AS s INNER JOIN banji AS b 
ON s.banji_id=b.id
INNER JOIN banji_course AS bc
ON b.id=bc.banji_id
INNER JOIN course AS c
ON bc.course_id=c.id;
SELECT NAME FROM banji;

 -- 班级名称  学生数量
SELECT b.name AS '班级名称' ,COUNT(*) AS ' 学生数量'
FROM student0 AS s 
INNER JOIN banji AS b
ON s.banji_id=b.id
GROUP BY b.id;



-- 班级人数
SELECT COUNT(*) FROM student0 GROUP BY banji_id;
-- 班级名字  班级人数
SELECT b.name,COUNT(*) FROM student0 AS s INNER JOIN banji AS b ON s.banji_id=b.id GROUP BY b.id;

SELECT * FROM student WHERE NAME LIKE '张%'; -- 以张开头
SELECT * FROM student WHERE NAME LIKE '张_'; -- 以张开头，而且名字是两个字
SELECT * FROM student WHERE NAME LIKE '%张%'; -- 名字里面只要有张就可以

总结：多表查询主要是账务下面两点
1、整个查询涉及到几张表，涉及到几张表就连接这几张表。
2、如果涉及到这几张表的关系搞不清楚，画一下ER图，弄清楚表和表之间的关系（就是根据外键建立的关系）

二、select查询模型

数据库中以表为组织单位存储数据。
表类似我们的Java类，每个字段对应类里面的属性。
那么用我们熟悉的java程序来与关系型数据对比，就会发现以下对应关系。
类--------------------表
表中属性-------------表中字段（列）
对象------------------记录（行）
字段（列）是变量（类中属性时变量）
变量是可以计算（操作）
where是表达式，值为真或者假（true或者false）

SELECT b.name AS ‘班级名称’,COUNT(s.id) as ‘学生数量’
FROM student as s
INNER JOIN banji as b
ON s.banji_id=b.id
GROUP BY s.banji_id;

– 在上面基础上筛选出班级人数>1班级的名称和人数
SELECT b.name AS ‘班级名称’,COUNT(s.id) as ‘学生数量’
FROM student as s
INNER JOIN banji as b
ON s.banji_id=b.id
GROUP BY s.banji_id
HAVING COUNT(s.id)>1;

三、模糊查找：like

语法形式：字段 like ‘要查找字符’
说明：
1、like模糊查找用于对字符类型的字段进行字符匹配查找。
2、要查找的字符中，有两个特殊含义的字符：% , _:
2.1: %含义是：代表0或多个的任意字符
2.2: 含义是：代表1个任意字符
2.3: 这里的字符都是指现实中可见的一个“符号”，而不是字节。
3、语法：like ‘%关键字%’
SELECT * FROM student WHERE NAME LIKE ‘张%’; – 以张开头
SELECT * FROM student WHERE NAME LIKE '张’; – 以张开头，而且名字是两个字
SELECT * FROM student WHERE NAME LIKE ‘%张%’; – 名字里面只要有张就可以

如果要查找的字符里中包含"%",""，
如果要查找的字符中包含“%”或“”，“ ’”，则只要对他们进行转义就可以：
like ‘%ab%cd%’ //这里要找的是： 包含 ab%cd 字符的字符
like ‘_ab%’ //这里要找的是： _ab开头的字符
like ‘%ab’cd%’ //这里要找的是： 包含 ab’cd 字符的字符

练习

create table goods (
goods_id mediumint(8) unsigned primary key auto_increment,
goods_name varchar(120) not null default ‘’,
cat_id smallint(5) unsigned not null default ‘0’,
brand_id smallint(5) unsigned not null default ‘0’,
goods_sn char(15) not null default ‘’,
goods_number smallint(5) unsigned not null default ‘0’,
shop_price decimal(10,2) unsigned not null default ‘0.00’,
market_price decimal(10,2) unsigned not null default ‘0.00’,
click_count int(10) unsigned not null default ‘0’
) ;

insert into goods values (1,‘kd876’,4,8,‘ecs000000’,1,1388.00,1665.60,9),
(4,‘诺基亚n85原装充电器’,8,1,‘ecs000004’,17,58.00,69.60,0),
(3,‘诺基亚原装5800耳机’,8,1,‘ecs000002’,24,68.00,81.60,3),
(5,‘索爱原装m2卡读卡器’,11,7,‘ecs000005’,8,20.00,24.00,3),
(6,‘胜创kingmax内存卡’,11,0,‘ecs000006’,15,42.00,50.40,0),
(7,‘诺基亚n85原装立体声耳机hs-82’,8,1,‘ecs000007’,20,100.00,120.00,0),
(8,‘飞利浦9@9v’,3,4,‘ecs000008’,1,399.00,478.79,10),
(9,‘诺基亚e66’,3,1,‘ecs000009’,4,2298.00,2757.60,20),
(10,‘索爱c702c’,3,7,‘ecs000010’,7,1328.00,1593.60,11),
(11,‘索爱c702c’,3,7,‘ecs000011’,1,1300.00,0.00,0),
(12,‘摩托罗拉a810’,3,2,‘ecs000012’,8,983.00,1179.60,13),
(13,‘诺基亚5320 xpressmusic’,3,1,‘ecs000013’,8,1311.00,1573.20,13),
(14,‘诺基亚5800xm’,4,1,‘ecs000014’,1,2625.00,3150.00,6),
(15,‘摩托罗拉a810’,3,2,‘ecs000015’,3,788.00,945.60,8),
(16,‘恒基伟业g101’,2,11,‘ecs000016’,0,823.33,988.00,3),
(17,‘夏新n7’,3,5,‘ecs000017’,1,2300.00,2760.00,2),
(18,‘夏新t5’,4,5,‘ecs000018’,1,2878.00,3453.60,0),
(19,‘三星sgh-f258’,3,6,‘ecs000019’,12,858.00,1029.60,7),
(20,‘三星bc01’,3,6,‘ecs000020’,12,280.00,336.00,14),
(21,‘金立 a30’,3,10,‘ecs000021’,40,2000.00,2400.00,4),
(22,‘多普达touch hd’,3,3,‘ecs000022’,1,5999.00,7198.80,16),
(23,‘诺基亚n96’,5,1,‘ecs000023’,8,3700.00,4440.00,17),
(24,‘p806’,3,9,‘ecs000024’,100,2000.00,2400.00,35),
(25,‘小灵通/固话50元充值卡’,13,0,‘ecs000025’,2,48.00,57.59,0),
(26,‘小灵通/固话20元充值卡’,13,0,‘ecs000026’,2,19.00,22.80,0),
(27,‘联通100元充值卡’,15,0,‘ecs000027’,2,95.00,100.00,0),
(28,‘联通50元充值卡’,15,0,‘ecs000028’,0,45.00,50.00,0),
(29,‘移动100元充值卡’,14,0,‘ecs000029’,0,90.00,0.00,0),
(30,‘移动20元充值卡’,14,0,‘ecs000030’,9,18.00,21.00,1),
(31,'摩托罗拉e8 ',3,2,‘ecs000031’,1,1337.00,1604.39,5),
(32,‘诺基亚n85’,3,1,‘ecs000032’,4,3010.00,3612.00,9);

– 分类表
create table category (
cat_id smallint unsigned auto_increment primary key,
cat_name varchar(90) not null default ‘’,
parent_id smallint unsigned
);

INSERT INTO category VALUES
(1,‘手机类型’,0),
(2,‘CDMA手机’,1),
(3,‘GSM手机’,1),
(4,‘3G手机’,1),
(5,‘双模手机’,1),
(6,‘手机配件’,0),
(7,‘充电器’,6),
(8,‘耳机’,6),
(9,‘电池’,6),
(11,‘读卡器和内存卡’,6),
(12,‘充值卡’,0),
(13,‘小灵通/固话充值卡’,12),
(14,‘移动手机充值卡’,12),
(15,‘联通手机充值卡’,12);

select where常用运算符:

– 1:主键为32的商品

– 2:不属第3栏目的所有商品（category中id为3）

– 3:本店价格高于3000元的商品

– 4:本店价格低于或等于100元的商品

– 5:取出第4栏目或第11栏目的商品

– 6:取出100<=价格<=500的商品

– BETWEEN AND是能取到开始和结束的值，等价于>= and <=

– 7:取出不属于第3栏目且不属于第11栏目的商品(and,或not in分别实现)

– 8:取出价格大于100且小于300,或者大于4000且小于5000的商品()
– 这种写法很容易出奇异，要加括号，写出有歧义的语句并不能显出你多厉害

– 9:取出第3个栏目下面价格<1000或>3000,并且点击量>5的系列商品

– 10:取出第1个栏目下面的商品(注意:1栏目下面没商品,但其子栏目下有)

– 11:取出名字以"诺基亚"开头的商品
– like 模糊匹配
– % 通配任意字符
– _ 通配单一字符

– 15:把goods表中商品名为’诺基亚xxxx’的商品,改为’HTCxxxx’,
– 提示:大胆的把列看成变量,参与运算,甚至调用函数来处理 .
– substring(),concat()

SELECT goods_id,goods_name FROM goods
WHERE goods_name LIKE ‘诺基亚%’;

SELECT goods_id,SUBSTR(goods_name, 4) FROM goods
WHERE goods_name LIKE ‘诺基亚%’;
SELECT goods_id,CONCAT(‘HTC’,SUBSTRING(goods_name,4)) FROM goods
WHERE goods_name LIKE ‘诺基亚%’;

public static void main(String[] args) {
String goodsName = “诺基亚原装5800耳机”;
String name = “HTC” + goodsName.substring(3);
System.out.println(name);//HTC原装5800耳机
}

group by having:
1、这个店积压的货款：
int goods_num = 17;
double shop_price = 58.0;
System.out.println(goods_num * shop_price);

SELECT goods_number*shop_price FROM goods;

SELECT SUM(goods_number*shop_price) FROM goods;

– 2、查询该店的商品比市场价所节省的价格大于200的产品
– shop_price market_price
select market_price-shop_price
FROM goods WHERE market_price-shop_price>200;

– 3、查询该店每个栏目下挤压的货款
SELECT cat_id,SUM(goods_numbershop_price)
FROM goods GROUP BY cat_id ;
– 4、查询该店每个栏目下挤压的货款 > 20000
SELECT cat_id,SUM(goods_numbershop_price) AS total_price
FROM goods GROUP BY cat_id HAVING total_price>20000;
order by 与 limit：
LIMIT 子句可以被用于强制 SELECT 语句返回指定的记录数。LIMIT 接受一个或两个数字参数。参数必须是一个整数常量。如果给定两个参数，第一个参数指定第一个返回记录行的偏移量，第二个参数指定返回记录行的最大数目。初始记录行的偏移量是 0(而不是 1)
– 1、按照栏目由低到高排序，栏目内部按照价格由高到低排序
SELECT goods_id,cat_id,goods_name,shop_price
FROM goods
ORDER BY cat_id,shop_price DESC;
– 2、取出价格最高的前三名商品
– limit offset,rowcount
– limit 偏移到哪个位置,往下数几个
SELECT goods_id,cat_id,goods_name,shop_price
FROM goods
ORDER BY shop_price DESC LIMIT 0,3; – limit 3
– 3、取出点击量前三名到第五名的商品
SELECT goods_id,cat_id,goods_name,click_count
FROM goods
ORDER BY click_count DESC LIMIT 2,3;