再续数据库链表

数据库相关查询
CREATE TABLE t_grade(
id INT PRIMARY KEY AUTO_INCREMENT,
gname VARCHAR(20),
gsubject VARCHAR(20),
grade INT
);
INSERT INTO t_grade(gname,gsubject,grade)
VALUES('张三','数学',98),('张三','语文',85),('张三','英语',85),
      ('李四','数学',85),('李四','语文',90),('李四','英语',65),
      ('王五','数学',90),('王五','语文',53),('王五','英语',75),
      ('赵六','数学',56),('赵六','语文',65),('赵六','英语',80);
    SELECT*FROM t_grade;

 -- 查询数学比李四考的好的学生   
SELECT*FROM t_grade WHERE gsubject='数学' AND grade> 
 (SELECT grade FROM t_grade WHERE gname='李四' AND gsubject='数学');
 
-- 查询语文成绩在班上平均语文成绩以下的学生
SELECT*FROM t_grade WHERE gsubject='语文' AND grade<
(SELECT AVG(grade)FROM t_grade WHERE gsubject='语文');
 
 -- 查询每门科目的最高分的学生
 SELECT*FROM t_grade g1 WHERE grade IN
 (SELECT  MAX(grade) FROM t_grade g2  WHERE g1.gsubject=g2.gsubject  GROUP BY gsubject);

数据库

CREATE TABLE t_people(
id INT PRIMARY KEY AUTO_INCREMENT,
pname VARCHAR(20),
sex ENUM('男','女'),
birthday DATE
);
INSERT INTO t_people(pname,sex,birthday)
VALUES('张三','男','1980-03-04'),('刘欢','男','1985-03-26'),
('李四','女','1985-09-20'),('张无忌','女','1993-02-27'),
('王五','男','1993-04-18'),('李世民','男','1991-09-04'),
('赵六','女','1973-06-30'),('李元霸','男','1996-03-14');
SELECT*FROM t_people;

CREATE TABLE t_bike(
id INT PRIMARY KEY AUTO_INCREMENT,
bikeType VARCHAR(20),
price INT,
peopleId INT
);

ALTER TABLE t_bike ADD CONSTRAINT fk_2 FOREIGN KEY(peopleId)
REFERENCES t_people(id);

INSERT INTO t_bike(bikeType, price,peopleId)
VALUES('永久',600,1),('凤凰',450,1),
('大运',1200,2),('捷安特',2100,2),('摩拜',500,2),
('黄蜂',1000,3),('马刺',700,3),
('公牛',650,4),('爵士',700,4),('火箭',500,4),('永久',1200,4),
('奇才',600,5);
SELECT*FROM t_people;
SELECT*FROM t_bike;

DROP TABLE t_people;
DROP TABLE t_bike;

-- 列出所有的自行车，并显示该自行车主人名称 join链接
SELECT b.*,p.pname FROM t_bike b,t_people p WHERE b.peopleId=p.id;-- 我








SELECT b.*,p.pname FROM t_bike b JOIN t_people p ON b.peopleId=p.id;


SELECT b.*,p.pname FROM t_bike b,t_people p WHERE b.peopleId=p.id;


-- 查询张无忌拥有的自行车信息
SELECT b.* FROM t_bike b  WHERE b.peopleId=
(SELECT    p.id FROM t_people  p WHERE p.pname='张无忌')-- 我






SELECT b.* FROM t_bike b,t_people p WHERE b.peopleId=p.id AND p.pname='张无忌';    
SELECT b.* FROM t_bike b JOIN t_people p ON b.peopleId=p.id WHERE p.pname='张无忌';


-- 删除王五
UPDATE t_bike  b SET b.peopleId=NULL WHERE b.peopleId=
(SELECT p.id FROM t_people p WHERE p.pname='王五' );

DELETE FROM t_people   WHERE pname='王五';--  我 






UPDATE t_bike b SET  b.peopleId=NULL WHERE peopleId=
(SELECT id FROM t_people WHERE pname='王五');

DELETE  FROM t_people WHERE pname='王五';

 


-- 查询没有主人的自行车
SELECT b.* FROM t_bike b WHERE b.peopleId IS NULL;-- 我







SELECT*FROM t_bike b WHERE b.peopleId IS NULL;

-- 统计每个人拥有自行车、

SELECT p.*,COUNT(b.peopleId) bikeNumber FROM  t_people p LEFT JOIN t_bike b ON b.peopleId=p.id
GROUP BY p.id; -- 我




SELECT p.*,COUNT(b.id) bikenum FROM t_people p LEFT JOIN t_bike b ON p.id=b.peopleId
GROUP BY p.id;


SELECT p.*,COUNT(b.id) FROM t_people p JOIN t_bike b  ON p.id=b.peopleId GROUP BY p.id;

-- 查询所有的自行车，以及该车该车主人的姓名
-- 如果p.pname为空，返回“没有主人”
SELECT b.*,IFNULL(p.pname,'没主人') bikeInfo  FROM t_bike b LEFT JOIN t_people p ON p.id=b.peopleId; -- 我







SELECT b.*,IFNULL(p.pname,'没有主人') bikeInfo FROM t_bike b 
LEFT JOIN t_people p ON b.peopleId=p.id;

-- 统计拥有自行车的用户，有几辆自行车 ，并统计总表格
SELECT p.*,COUNT(b.peopleId) bikeNum,SUM(b.price)  FROM t_people p JOIN t_bike b ON b.peopleId=p.id GROUP BY p.id;-- 我




  
 SELECT p.pname,COUNT(b.id) bikeNum, SUM(b.price) bikeprice
 FROM t_bike b ,t_people p WHERE b.peopleId=p.id GROUP BY p.id;

 

CREATE TABLE t_people(id INT PRIMARY KEY AUTO_INCREMENT,pname VARCHAR(20),sex ENUM('男','女'),birthday DATE);INSERT INTO t_people(pname,sex,birthday)VALUES('张三','男','1980-03-04'),('刘欢','男','1985-03-26'),('李四','女','1985-09-20'),('张无忌','女','1993-02-27'),('王五','男','1993-04-18'),('李世民','男','1991-09-04'),('赵六','女','1973-06-30'),('李元霸','男','1996-03-14');SELECT*FROM t_people;
CREATE TABLE t_bike(id INT PRIMARY KEY AUTO_INCREMENT,bikeType VARCHAR(20),price INT,peopleId INT);
ALTER TABLE t_bike ADD CONSTRAINT fk_2 FOREIGN KEY(peopleId)REFERENCES t_people(id);
INSERT INTO t_bike(bikeType, price,peopleId)VALUES('永久',600,1),('凤凰',450,1),('大运',1200,2),('捷安特',2100,2),('摩拜',500,2),('黄蜂',1000,3),('马刺',700,3),('公牛',650,4),('爵士',700,4),('火箭',500,4),('永久',1200,4),('奇才',600,5);SELECT*FROM t_people;SELECT*FROM t_bike;
DROP TABLE t_people;DROP TABLE t_bike;
-- 列出所有的自行车，并显示该自行车主人名称 join链接SELECT b.*,p.pname FROM t_bike b,t_people p WHERE b.peopleId=p.id;-- 我







SELECT b.*,p.pname FROM t_bike b JOIN t_people p ON b.peopleId=p.id;

SELECT b.*,p.pname FROM t_bike b,t_people p WHERE b.peopleId=p.id;

-- 查询张无忌拥有的自行车信息SELECT b.* FROM t_bike b  WHERE b.peopleId=(SELECTp.id FROM t_people  p WHERE p.pname='张无忌')-- 我





SELECT b.* FROM t_bike b,t_people p WHERE b.peopleId=p.id AND p.pname='张无忌';SELECT b.* FROM t_bike b JOIN t_people p ON b.peopleId=p.id WHERE p.pname='张无忌';

-- 删除王五UPDATE t_bike  b SET b.peopleId=NULL WHERE b.peopleId=(SELECT p.id FROM t_people p WHERE p.pname='王五' );
DELETE FROM t_people   WHERE pname='王五';--  我 





UPDATE t_bike b SET  b.peopleId=NULL WHERE peopleId=(SELECT id FROM t_people WHERE pname='王五');
DELETE  FROM t_people WHERE pname='王五';
 

-- 查询没有主人的自行车SELECT b.* FROM t_bike b WHERE b.peopleId IS NULL;-- 我






SELECT*FROM t_bike b WHERE b.peopleId IS NULL;
-- 统计每个人拥有自行车、
SELECT p.*,COUNT(b.peopleId) bikeNumber FROM  t_people p LEFT JOIN t_bike b ON b.peopleId=p.idGROUP BY p.id; -- 我



SELECT p.*,COUNT(b.id) bikenum FROM t_people p LEFT JOIN t_bike b ON p.id=b.peopleIdGROUP BY p.id;

SELECT p.*,COUNT(b.id) FROM t_people p JOIN t_bike b  ON p.id=b.peopleId GROUP BY p.id;
-- 查询所有的自行车，以及该车该车主人的姓名-- 如果p.pname为空，返回“没有主人”SELECT b.*,IFNULL(p.pname,'没主人') bikeInfo  FROM t_bike b LEFT JOIN t_people p ON p.id=b.peopleId; -- 我






SELECT b.*,IFNULL(p.pname,'没有主人') bikeInfo FROM t_bike b LEFT JOIN t_people p ON b.peopleId=p.id;
-- 统计拥有自行车的用户，有几辆自行车 ，并统计总表格SELECT p.*,COUNT(b.peopleId) bikeNum,SUM(b.price)  FROM t_people p JOIN t_bike b ON b.peopleId=p.id GROUP BY p.id;-- 我



   SELECT p.pname,COUNT(b.id) bikeNum, SUM(b.price) bikeprice FROM t_bike b ,t_people p WHERE b.peopleId=p.id GROUP BY p.id;