MySQL基础(二)—操作表记录

最新推荐文章于 2023-01-20 18:00:09 发布
最新推荐文章于 2023-01-20 18:00:09 发布
阅读量395 收藏 1
点赞数
分类专栏： MySql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_15037349/article/details/80390111
版权

上一篇MySQL基础(一)数据类型及数据表操作
这一篇是对表记录操作的笔记，其中操作的数据库在上一篇文章中有代码，可以去看一下。

1.插入

#插入 
#如果是自增的，可以设置为null或者default来实现自增
#默认值的情况  可以直接写默认值，也可以写default
#对于一些数字，可以写数学表达式进去  
INSERT INTO team VALUES(NULL,"马刺",10,100*2-100,90,5,"AT&T中心");
INSERT INTO team VALUES(default,"勇士",10,100,90,default,"甲骨文中心");
INSERT INTO team(teamId,teamName,teamAge,toalMatch,victoryNumber,championNumber) VALUES(NULL,"凯尔特人",10,100,89,4);
INSERT INTO team(teamId,teamName,teamAge,toalMatch,victoryNumber,championNumber) VALUES(NULL,"火箭",10,100,90,5);
insert team set teamName="开拓者",teamAge=10,toalMatch=100,victoryNumber=90,championNumber=2,ballHall="玫瑰花园球馆";

INSERT INTO test VALUES (NULL,"小米1",1),(NULL,"小米2",2),(NULL,"小米3",3),(NULL,"小米4",4),(NULL,"小米5",5);

insert test select *FROM test LIMIT 2;

#批量插入
INSERT INTO team(teamId,teamName,teamAge,toalMatch,victoryNumber,championNumber) VALUES(NULL,"老鹰",10,100,90,5),(NULL,"黄蜂",10,100,90,5),(NULL,"鹈鹕",10,100,90,5);
INSERT INTO team VALUES(NULL,"公牛",10,100,90,5,"联合中心"),(NULL,"骑士",10,100,90,5,"速贷球馆"),(NULL,"灰熊",10,100,90,5,"联邦快递球馆");
#插入教练表
INSERT INTO coach(coachId,coachName,championNumber,teamId) VALUES(NULL,"波波维奇",5,(SELECT teamId FROM team WHERE team.`teamName`='马刺'));
INSERT INTO coach(coachId,coachName,championNumber,teamId) VALUES(NULL,"史蒂文斯",0,(SELECT teamId FROM team WHERE team.`teamName`='凯尔特人'));

2.单表修改和删除

#修改记录(单表更新)
UPDATE team SET teamAge=teamAge+10,championNumber=championNumber+1 WHERE teamName="马刺";
#删除(单表删除)
DELETE FROM team WHERE team.`teamName` = '马刺'

3.简单查询及排序

#查询全部
SELECT * FROM team
#再把删除的那个记录插入进去
INSERT INTO team VALUES(NULL,"马刺",10,100*2-100,90,5,"AT&T中心");
#查询某些列
SELECT team.ballHall,team.teamName,championNumber FROM team
#查询设置别名 这个地方的as可以省略，但是不建议省略避免出现误会
SELECT team.`ballHall`AS BallHall,team.`teamName` AS tName,team.`championNumber` AS tNumber FROM team;
#简单条件查询
SELECT * FROM team WHERE team.`teamName`='马刺'

#分组查询  
SELECT * FROM team GROUP BY championNumber DESC 
#HAVING关键字  两个条件必须满足其一  
#1:having后边的字段必须在结果集中 
SELECT * FROM team GROUP BY championNumber having championNumber>3 
#2:having后边是个函数
SELECT * FROM team GROUP BY championNumber HAVING sum(championNumber)>3 

#排序
#按总冠军的数量排序
select *from team order by championNumber desc
#总冠军数量相同的 按胜利场次多的排序
select *from team order by championNumber desc,victoryNumber desc
#总冠军数量相同的、胜利场次相同，按id小的排序
SELECT *FROM team ORDER BY championNumber DESC,victoryNumber DESC,teamId asc

#限制查询数量 
#查询前2条
SELECT *FROM team limit 2;
#查询从第4个开始，查询2条
SELECT *FROM team LIMIT 3,2;

4.子查询

先准备一大堆数据

#先补充数据(球队信息)
UPDATE team SET ballHall="甲骨文体育馆"  ,teamAge=2018-1946,toalMatch=82,victoryNumber=58,championNumber=2 WHERE teamName="勇士";
UPDATE team SET ballHall="TD花园" ,teamAge=2018-1946,toalMatch=82,victoryNumber=55,championNumber=17 WHERE teamName="凯尔特人";
UPDATE team SET ballHall="丰田中心" ,teamAge=2018-1967,toalMatch=82,victoryNumber=65,championNumber=2   WHERE teamName="火箭";
UPDATE team SET ballHall="菲利普斯球馆"  ,teamAge=2018-1949,toalMatch=82,victoryNumber=24,championNumber=1   WHERE teamName="老鹰";
UPDATE team SET ballHall="时代华纳有线球馆" ,teamAge=2018-2004,toalMatch=82,victoryNumber=36,championNumber=0   WHERE teamName="黄蜂";
UPDATE team SET ballHall="冰沙国王中心"  ,teamAge=2018-1988,toalMatch=82,victoryNumber=48,championNumber=0 WHERE teamName="鹈鹕";
UPDATE team SET ballHall="联合中心球馆"   ,teamAge=2018-1966,toalMatch=82,victoryNumber=27,championNumber=6 WHERE teamName="公牛";
UPDATE team SET ballHall="速贷球馆" ,teamAge=2018-1970,toalMatch=82,victoryNumber=50,championNumber=1  WHERE teamName="骑士";
UPDATE team SET ballHall="联邦快递体育馆"  ,teamAge=2018-1995,toalMatch=82,victoryNumber=22,championNumber=0  WHERE teamName="灰熊";
UPDATE team SET ballHall="摩达中心"  ,teamAge=2018-1970,toalMatch=82,victoryNumber=49,championNumber=1 WHERE teamName="开拓者";
UPDATE team SET ballHall="AT&T中心" ,teamAge=2018-1976,toalMatch=82,victoryNumber=47,championNumber=5  WHERE teamName="马刺";

#先补充数据(教练信息)
ALTER TABLE coach DROP toalMatch, DROP victoryNumber
DELETE FROM coach
INSERT INTO coach VALUES(NULL,"科尔",2,(SELECT teamId FROM team WHERE team.`teamName`='勇士'));
INSERT INTO coach VALUES(NULL,"史蒂文斯",0,(SELECT teamId FROM team WHERE team.`teamName`='凯尔特人'));
INSERT INTO coach VALUES(NULL,"德安东尼",0,(SELECT teamId FROM team WHERE team.`teamName`='火箭'));
INSERT INTO coach VALUES(NULL,"布登霍尔泽",0,(SELECT teamId FROM team WHERE team.`teamName`='老鹰'));
INSERT INTO coach VALUES(NULL,"克利福德",0,(SELECT teamId FROM team WHERE team.`teamName`='黄蜂'));
INSERT INTO coach VALUES(NULL,"金特里",1,(SELECT teamId FROM team WHERE team.`teamName`='鹈鹕'));
INSERT INTO coach VALUES(NULL,"霍伊博格",0,(SELECT teamId FROM team WHERE team.`teamName`='公牛'));
INSERT INTO coach VALUES(NULL,"泰伦-卢",1,(SELECT teamId FROM team WHERE team.`teamName`='骑士'));
INSERT INTO coach VALUES(NULL,"比克斯塔夫",0,(SELECT teamId FROM team WHERE team.`teamName`='灰熊'));
INSERT INTO coach VALUES(NULL,"斯托茨",0,(SELECT teamId FROM team WHERE team.`teamName`='开拓者'));
INSERT INTO coach VALUES(NULL,"波波维奇",5,(SELECT teamId FROM team WHERE team.`teamName`='马刺'));

#创建球员表
CREATE TABLE player(
#球员ID  整型  无符号  自增  主键
playerId INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
#球员名称   字符串  不能为空  唯一
playerName VARCHAR(20) NOT NULL ,
#位置
playerPosition VARCHAR(20) NOT NULL ,
#号码
playerNumber INT UNSIGNED NOT NULL ,
#球队ID
teamId INT UNSIGNED  NOT NULL,

FOREIGN KEY(teamId) REFERENCES team (teamId)  ON DELETE CASCADE
);

#插入球员数据
INSERT INTO player VALUES(NULL,"斯蒂芬-库里","后卫",30,(SELECT teamId FROM team WHERE team.`teamName`='勇士'));
INSERT INTO player VALUES(NULL,"凯文-杜兰特","前锋",35,(SELECT teamId FROM team WHERE team.`teamName`='勇士'));
INSERT INTO player VALUES(NULL,"德雷蒙德-格林","前锋",23,(SELECT teamId FROM team WHERE team.`teamName`='勇士'));
INSERT INTO player VALUES(NULL,"克雷-汤普森","后卫",11,(SELECT teamId FROM team WHERE team.`teamName`='勇士'));
INSERT INTO player VALUES(NULL,"伊格达拉","后卫",9,(SELECT teamId FROM team WHERE team.`teamName`='勇士'));

INSERT INTO player VALUES(NULL,"凯里-欧文","后卫",11,(SELECT teamId FROM team WHERE team.`teamName`='凯尔特人'));
INSERT INTO player VALUES(NULL,"格雷格-门罗","前锋",55,(SELECT teamId FROM team WHERE team.`teamName`='凯尔特人'));
INSERT INTO player VALUES(NULL,"戈登-海沃德","前锋",20,(SELECT teamId FROM team WHERE team.`teamName`='凯尔特人'));
INSERT INTO player VALUES(NULL,"艾尔-霍福德","前锋",42,(SELECT teamId FROM team WHERE team.`teamName`='凯尔特人'));
INSERT INTO player VALUES(NULL,"贝恩斯","中锋",46,(SELECT teamId FROM team WHERE team.`teamName`='凯尔特人'));

INSERT INTO player VALUES(NULL,"詹姆斯-哈登","后卫",13,(SELECT teamId FROM team WHERE team.`teamName`='火箭'));
INSERT INTO player VALUES(NULL,"乔-约翰逊","前锋",7,(SELECT teamId FROM team WHERE team.`teamName`='火箭'));
INSERT INTO player VALUES(NULL,"克里斯-保罗","后卫",3,(SELECT teamId FROM team WHERE team.`teamName`='火箭'));
INSERT INTO player VALUES(NULL,"莱恩-安德森","前锋",33,(SELECT teamId FROM team WHERE team.`teamName`='火箭'));
INSERT INTO player VALUES(NULL,"戈登","后卫",10,(SELECT teamId FROM team WHERE team.`teamName`='火箭'));

INSERT INTO player VALUES(NULL,"普拉姆利","中锋",18,(SELECT teamId FROM team WHERE team.`teamName`='老鹰'));
INSERT INTO player VALUES(NULL,"贝兹莫尔","后卫",24,(SELECT teamId FROM team WHERE team.`teamName`='老鹰'));
INSERT INTO player VALUES(NULL,"施罗德","后卫",17,(SELECT teamId FROM team WHERE team.`teamName`='老鹰'));
INSERT INTO player VALUES(NULL,"穆斯卡拉","前锋",31,(SELECT teamId FROM team WHERE team.`teamName`='老鹰'));
INSERT INTO player VALUES(NULL,"戴德蒙","中锋",14,(SELECT teamId FROM team WHERE team.`teamName`='老鹰'));

INSERT INTO player VALUES(NULL,"霍华德","中锋",12,(SELECT teamId FROM team WHERE team.`teamName`='黄蜂'));
INSERT INTO player VALUES(NULL,"巴图姆","后卫",5,(SELECT teamId FROM team WHERE team.`teamName`='黄蜂'));
INSERT INTO player VALUES(NULL,"肯巴-沃克","后卫",15,(SELECT teamId FROM team WHERE team.`teamName`='黄蜂'));
INSERT INTO player VALUES(NULL,"威廉姆斯","前锋",2,(SELECT teamId FROM team WHERE team.`teamName`='黄蜂'));
INSERT INTO player VALUES(NULL,"吉尔克里斯特","前锋",14,(SELECT teamId FROM team WHERE team.`teamName`='黄蜂'));

INSERT INTO player VALUES(NULL,"考辛斯","中锋",0,(SELECT teamId FROM team WHERE team.`teamName`='鹈鹕'));
INSERT INTO player VALUES(NULL,"奥卡福","中锋",50,(SELECT teamId FROM team WHERE team.`teamName`='鹈鹕'));
INSERT INTO player VALUES(NULL,"霍勒迪","后卫",11,(SELECT teamId FROM team WHERE team.`teamName`='鹈鹕'));
INSERT INTO player VALUES(NULL,"朗多","后卫",9,(SELECT teamId FROM team WHERE team.`teamName`='鹈鹕'));
INSERT INTO player VALUES(NULL,"戴维斯","前锋",23,(SELECT teamId FROM team WHERE team.`teamName`='鹈鹕'));

INSERT INTO player VALUES(NULL,"洛佩兹","中锋",42,(SELECT teamId FROM team WHERE team.`teamName`='公牛'));
INSERT INTO player VALUES(NULL,"阿西克","中锋",3,(SELECT teamId FROM team WHERE team.`teamName`='公牛'));
INSERT INTO player VALUES(NULL,"冯莱","前锋",30,(SELECT teamId FROM team WHERE team.`teamName`='公牛'));
INSERT INTO player VALUES(NULL,"拉文","后卫",8,(SELECT teamId FROM team WHERE team.`teamName`='公牛'));
INSERT INTO player VALUES(NULL,"佩恩","后卫",22,(SELECT teamId FROM team WHERE team.`teamName`='公牛'));

INSERT INTO player VALUES(NULL,"詹姆斯","后卫",23,(SELECT teamId FROM team WHERE team.`teamName`='骑士'));
INSERT INTO player VALUES(NULL,"乐福","前锋",0,(SELECT teamId FROM team WHERE team.`teamName`='骑士'));
INSERT INTO player VALUES(NULL,"格林","前锋",32,(SELECT teamId FROM team WHERE team.`teamName`='骑士'));
INSERT INTO player VALUES(NULL,"希尔","后卫",3,(SELECT teamId FROM team WHERE team.`teamName`='骑士'));
INSERT INTO player VALUES(NULL,"卡尔德隆","后卫",81,(SELECT teamId FROM team WHERE team.`teamName`='骑士'));

INSERT INTO player VALUES(NULL,"加索尔","中锋",33,(SELECT teamId FROM team WHERE team.`teamName`='灰熊'));
INSERT INTO player VALUES(NULL,"帕森斯","前锋",25,(SELECT teamId FROM team WHERE team.`teamName`='灰熊'));
INSERT INTO player VALUES(NULL,"埃文斯","后卫",12,(SELECT teamId FROM team WHERE team.`teamName`='灰熊'));
INSERT INTO player VALUES(NULL,"康利","后卫",11,(SELECT teamId FROM team WHERE team.`teamName`='灰熊'));
INSERT INTO player VALUES(NULL,"查尔莫斯","后卫",6,(SELECT teamId FROM team WHERE team.`teamName`='灰熊'));

INSERT INTO player VALUES(NULL,"阿米努","前锋",8,(SELECT teamId FROM team WHERE team.`teamName`='开拓者')); 
INSERT INTO player VALUES(NULL,"戴维斯","中锋",17,(SELECT teamId FROM team WHERE team.`teamName`='开拓者')); 
INSERT INTO player VALUES(NULL,"特纳","后卫",1,(SELECT teamId FROM team WHERE team.`teamName`='开拓者')); 
INSERT INTO player VALUES(NULL,"利拉德","后卫",0,(SELECT teamId FROM team WHERE team.`teamName`='开拓者')); 
INSERT INTO player VALUES(NULL,"迈尔斯-莱昂纳德","中锋",11,(SELECT teamId FROM team WHERE team.`teamName`='开拓者')); 

INSERT INTO player VALUES(NULL,"阿尔德里奇","前锋",12,(SELECT teamId FROM team WHERE team.`teamName`='马刺')); 
INSERT INTO player VALUES(NULL,"莱昂纳德","前锋",2,(SELECT teamId FROM team WHERE team.`teamName`='马刺')); 
INSERT INTO player VALUES(NULL,"吉诺比利","后卫",20,(SELECT teamId FROM team WHERE team.`teamName`='马刺')); 
INSERT INTO player VALUES(NULL,"托尼-帕克","后卫",9,(SELECT teamId FROM team WHERE team.`teamName`='马刺')); 
INSERT INTO player VALUES(NULL,"鲁迪-盖伊","前锋",22,(SELECT teamId FROM team WHERE team.`teamName`='马刺'));
  • 子查询指的是嵌套在查询内部，并且始终出现在小括号里边
  • 子查询可以包含多个关键字或条件 DISTINCT\group by \order by\函数等等
  • 子查询的外层查询可以是select \ insert\update \set\do
  • 子查询可以返回标量、一行、一列或者子查询
4.1比较运算符引发的子查询 （=、>、<、>=、<=、!= ）
#查询平均值
SELECT AVG(team.`championNumber`) FROM team;
#查询平均值保留两位小数
SELECT ROUND(AVG(team.`championNumber`),2 )FROM team;
#查询总冠军数量在平均值以上的
SELECT * FROM team WHERE team.`championNumber`>(SELECT ROUND(AVG(team.`championNumber`),2 )FROM team);
#使用 any\some、all关键字来修饰运算符
#查询凯尔特人队队员的号码
SELECT player.`playerNumber` FROM player WHERE player.`teamId` = (SELECT team.`teamId` FROM team WHERE team.`teamName`="凯尔特人");
#查询所有球员中，背号>凯尔特人球员的背号的球员
#使用any 和some 的意思是一样的  只要满足返回集合中的任意一个就算满足条件
#在查询凯尔特人队队员的号码  返回的集合是 11，55，20，42，46  也就是说，使用any和some 只要背号>11就满足条件
SELECT *FROM player WHERE player.`playerNumber`> ANY
(SELECT player.`playerNumber` FROM player WHERE player.`teamId` = 
    (SELECT team.`teamId` FROM team WHERE team.`teamName`="凯尔特人")
);

SELECT *FROM player WHERE player.`playerNumber`> SOME
(SELECT player.`playerNumber` FROM player WHERE player.`teamId` = 
    (SELECT team.`teamId` FROM team WHERE team.`teamName`="凯尔特人")
);

#但是使用all关键字修饰的时候，就是要满足所有的值，才算符合条件  就是必须背号>55才满足
SELECT *FROM player WHERE player.`playerNumber`> ALL
(SELECT player.`playerNumber` FROM player WHERE player.`teamId` = 
    (SELECT team.`teamId` FROM team WHERE team.`teamName`="凯尔特人")
);

SELECT *FROM player WHERE player.`playerNumber`>55
4.2由[NOT]IN 引发的子查询
  • =ANY和IN等效
  • !=ALL和NOT IN等效
#查询背号和凯尔特人队的都不一样的
SELECT *FROM player WHERE player.`playerNumber` !=ALL
(SELECT player.`playerNumber` FROM player WHERE player.`teamId` = 
    (SELECT team.`teamId` FROM team WHERE team.`teamName`="凯尔特人")
);
#用这个来验证
SELECT *FROM player WHERE player.`playerNumber` !=11 AND player.`playerNumber` !=55 
AND player.`playerNumber` !=20 AND player.`playerNumber` !=42  AND player.`playerNumber` !=46

5.多表更新

#把球队的总冠军数量赋值给队里的球员
#首先给球员添加总冠军数量的字段
ALTER TABLE player ADD championNumber INT NOT NULL DEFAULT 0;
#多表更新   
#UPDATE 要更新的表名称  连接方式    要连接的表  ON  连接条件                     SET 要修改的值            =   改成哪个数
UPDATE     player       INNER JOIN    team      ON player.`teamId`=team.`teamId` SET player.`championNumber`=team.`championNumber`

#把冠军球队放到一个新的表里边
CREATE TABLE championTeam(
    teamId INT  UNSIGNED AUTO_INCREMENT PRIMARY KEY, 
    teamName VARCHAR(20) NOT NULL
)SELECT team.`teamName` FROM team WHERE team.`championNumber`>0;

6.表连接

语法：A表 连接类型 B表 ON 连接条件
语法：A LEFT JOIN B join_condition
分类：

  • INNER JOIN 内连接 在MySQL中，JOIN,CROSS JOIN 和 INNER JOIN是等价的
  • LEFT[OUTER] JOIN 左外连接
  • RIGHT [OUTER] JOIN 右外连接

注意：

  • B表的结果集依赖A表
  • A表的结果集根据做链接条件依赖所有数据表(B表除外)
  • 左外连接条件决定如何让检索B表(在没有指定where条件的情况下)
  • 如果A表的某条记录符合where条件，到那时B表中没有符合条件的记录，将生成一个所有列位空的额外的B行

具体使用：

#先删除player表和team表的主外键关系
SHOW CREATE TABLE player
#查询出来的结果是  test_ibfk_1
CREATE TABLE `player` (
  `playerId` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `playerName` VARCHAR(20) NOT NULL,
  `playerPosition` VARCHAR(20) NOT NULL,
  `playerNumber` INT(10) UNSIGNED NOT NULL,
  `teamId` INT(10) UNSIGNED NOT NULL,
  `championNumber` INT(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`playerId`),
  KEY `teamId` (`teamId`),
  CONSTRAINT `player_ibfk_1` FOREIGN KEY (`teamId`) REFERENCES `team` (`teamId`) ON DELETE CASCADE
) ENGINE=INNODB AUTO_INCREMENT=56 DEFAULT CHARSET=utf8

#然后用 player_ibfk_1去删除外键
ALTER TABLE player DROP FOREIGN KEY  player_ibfk_1
#然后插入一个球员，该球队的id不在team表中
INSERT INTO player VALUES(NULL,"李小米","后卫",6,999,5); 
#然后插入两个球队，该球队下没有球员
INSERT INTO team VALUES(NULL,"76人",2018-1976,3,"富国银行中心",82,52);
INSERT INTO team VALUES(NULL,"森林狼",2018-1989,0,"标靶中心",82,47);

#用一下内连接  内连接只能查询两个表的交集，都有的部分数据，刚才插入进去的李小米就没有
SELECT team.`teamName`,player.`playerName`,player.`playerPosition`,player.`playerNumber` 
FROM team INNER JOIN player ON team.`teamId`=player.`teamId`

#外连接 左外连接  结果集中包括左边中的全部内容和右表中符合条件的数据
SELECT team.`teamName`,player.`playerName`,player.`playerPosition`,player.`playerNumber` 
FROM team LEFT JOIN player ON team.`teamId`=player.`teamId`

#外连接 右外连接  结果集中包括右边中的全部内容和左表中符合条件的数据
SELECT team.`teamName`,player.`playerName`,player.`playerPosition`,player.`playerNumber` 
FROM team RIGHT JOIN player ON team.`teamId`=player.`teamId`
#多张数据表的连接
SELECT team.`teamName` ,coach.`coachName`,player.`playerName`,player.`playerPosition`,player.`playerNumber`
FROM team INNER JOIN player ON team.`teamId`=player.`teamId` INNER JOIN coach ON team.`teamId`=coach.`teamId`

7.自连接

这里咱们假设教练员是有分类的，比如有3层分类，一般情况下会去设计三个表。但是如果有N层分类呢？怎么设计
这里咱们给教练表增加一个字段 一个上一层的分类id,这样就相当于有了一个N层分类的表了
先改造一下数据表和数据


ALTER TABLE coach ADD parentId INT UNSIGNED  DEFAULT 0;

UPDATE coach SET coach.`parentId`=0 WHERE coach.`coachName`='波波维奇';

UPDATE coach SET coach.`parentId`=(SELECT coachId FROM coach WHERE coachName='波波维奇') 
WHERE coach.`coachName`='科尔';

UPDATE coach SET coach.`parentId`=(SELECT c.coachId FROM (SELECT coach.* FROM coach) AS c  WHERE c.coachName='波波维奇') 
WHERE coach.`coachName`='科尔';

UPDATE coach SET coach.`parentId`=(SELECT c.coachId FROM (SELECT coach.* FROM coach) AS c  WHERE c.coachName='波波维奇') 
WHERE coach.`coachName`='史蒂文斯';

UPDATE coach SET coach.`parentId`=(SELECT c.coachId FROM (SELECT coach.* FROM coach) AS c  WHERE c.coachName='科尔') 
WHERE coach.`coachName`='德安东尼';
UPDATE coach SET coach.`parentId`=(SELECT c.coachId FROM (SELECT coach.* FROM coach) AS c  WHERE c.coachName='科尔') 
WHERE coach.`coachName`='布登霍尔泽';

UPDATE coach SET coach.`parentId`=(SELECT c.coachId FROM (SELECT coach.* FROM coach) AS c  WHERE c.coachName='史蒂文斯') 
WHERE coach.`coachName`='克利福德';
UPDATE coach SET coach.`parentId`=(SELECT c.coachId FROM (SELECT coach.* FROM coach) AS c  WHERE c.coachName='史蒂文斯') 
WHERE coach.`coachName`='金特里';


UPDATE coach SET coach.`parentId`=(SELECT c.coachId FROM (SELECT coach.* FROM coach) AS c  WHERE c.coachName='德安东尼') 
WHERE coach.`coachName`='霍伊博格';
UPDATE coach SET coach.`parentId`=(SELECT c.coachId FROM (SELECT coach.* FROM coach) AS c  WHERE c.coachName='德安东尼') 
WHERE coach.`coachName`='泰伦-卢';
UPDATE coach SET coach.`parentId`=(SELECT c.coachId FROM (SELECT coach.* FROM coach) AS c  WHERE c.coachName='霍伊博格') 
WHERE coach.`coachName`='比克斯塔夫';
UPDATE coach SET coach.`parentId`=(SELECT c.coachId FROM (SELECT coach.* FROM coach) AS c  WHERE c.coachName='泰伦-卢') 
WHERE coach.`coachName`='斯托茨';

然后查询出来自己的id和名称 以及上一层的名称

SELECT s.coachId,s.coachName,p.coachName FROM coach AS s LEFT JOIN coach AS p ON s.`parentId`=p.`coachId`

然后查询出来自己的id和名称 以及下一层的名称

SELECT p.coachId,p.coachName,s.coachName FROM coach AS p RIGHT JOIN coach AS s ON s.`parentId`=p.`coachId`

8.多表删除

#多表删除
DELETE FROM championTeam
INSERT INTO championTeam(championTeam.`teamName`) SELECT team.`teamName`  FROM team ;
#删除总冠军数量为0的球队
DELETE c FROM championTeam AS c LEFT JOIN team AS t ON c.`teamName`=t.`teamName` WHERE t.championNumber =0;

点击查看本节sql文件
下一篇 MySQL基础(三)—函数、自定义函数

我是李小米
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
MySQL学习-表记录的查询操作
qq_48772498的博客
03-03 3067
数据中最常见的操作就是查询操作，也可以称作检索操作，目的是从表中检索出所需要的数据。 select 语句： select 字段列表 from 表名或视图名 [where 条件表达式] [group by 分组字段 [having 条件表达式]] [order by 排序字段 [asc | desc]; where 语句用于指定记录的过滤条件。 group by 子句用于对检索的数据进行分组。 having 子句通常和 group by 子句一起使用，用于过滤分组后的统计信息。 order by
删除所有学生记录mysql_【MySQL】MySQL基础操作语句
weixin_42494628的博客
02-11 3794
mysql基础操作语句，包括数据库的增、删、切换，以及表的增、删、改、查、复制。创建数据库mysql> create database tem; 使用数据库mysql> use tem; 删除数据库mysql> drop database drop_test; 创建数据表student表mysql> CREATE TABLE student (-> id INT...
mysql group by index_mysql GROUP_CONCAT+ GROUP BY + substring_index获取分组的前几名
weixin_42348233的博客
01-19 302
mysql方法来源于：http://www.cnblogs.com/jjcc/p/5896588.html###在网上看到一篇，非常赞的方法＃＃＃比如说要获取班级的前3名，mysql就可以用GROUP_CONCAT +GROUP BY+substring_index实现。考试表DROP TABLE IF EXISTS `test`;CREATE TABLE `test` (`id` int...
select * from t 和select * from t order by id两个有什么区别
Suger Mike的博客
10-13 2615
select * from t 是查询全部的信息； 和select * from t order by id是查询全部的信息并且按照id排序；order by 就是做个排序了 ASC升序 desc降序 例如： select renterId from house.renter order by renterId desc； 对house.renter表的renterId进行查
MySQL 常用命令和方法
心猿码意
11-22 458
Windows服务 启动MySQL net start mysql 创建Windows服务 sc create mysql binPath= mysqld_bin_path(注意：等号与值之间有空格) 连接与断开服务器 mysql -h 地址 -P 端口 -u 用户名 -p 密码 SHOW PROCESSLIST -- 显示哪些线程正在运行 SHOW VARIABLES -- 显...
【MySQL基础】MySQL单表操作详解
小杨的博客
01-13 2407
MySQL单表操作大总结，一文带你掌握单表操作。 该文主要介绍单表中的数据操作，限量，排序，分组，聚合函数，别名设置六部分内容。
【MySQL基础】MySQL多表操作详解
小杨的博客
01-20 2454
MySQL中的多表操作详细讲解，一文让你掌握MySQL多表操作中的三种多表关系，五种多表查询，和四种子查询以及四种子查询关键字的操作和使用。
【MySQL基础】MySQL基本操作详解
小杨的博客
10-29 3万+
大家好，我是小杨！今天我将详细的为大家介绍MySQL数据库的基本操作，希望大家能够从中收获多多！
【MySQL基础】MySQL表的七大约束
小杨的博客
01-07 5429
MySQL表的七大约束大详解，带你掌握MySQL七大约束，即默认约束，非空约束，唯一约束，主键约束，自增约束，检索约束，外键约束。
MySQL索引性能优化分析
热门推荐
张彦峰的博客
04-07 173万+
总结MySQL索引性能优化，主要集中在索引的背景介绍以及索引的高效创建、使用和查询等，最有给出除了索引的一些其他优化思路
MYSQL基础：数据库、表的基本操作.docx
10-31
MySQL 基础：数据库、表的基本操作 MySQL 是一种常用的关系型数据库管理系统，广泛应用于各种 Web 应用和软件开发项目中。在使用 MySQL 时，熟悉数据库和数据表的基本操作是至关重要的。 数据库操作 1. 创建...
MySQL数据库查看数据表占用空间大小和记录数的方法
12-15
除了这些基础操作，MySQL还提供了丰富的数据管理和操作功能，如数据导入导出、表的复制、字段内容的批量修改、数据表的分区等。通过熟练掌握这些操作，你可以更好地管理和优化你的MySQL数据库，确保其高效运行并满足...
mysql基础操作
09-15
### MySQL基础操作详解 #### 一、MySQL安装与配置 **1. 解压缩** - 首先，需要下载MySQL的安装包。通常可以从官方网站或其他可靠的来源获取。 - 下载完成后，将安装包解压到指定目录，例如`C:\MySQL`。 **2. ...
超详细的MySQL基础入门教程 从0讲到数据库操作 共22页.pdf
08-21
8.2 创建第二个表 title （包括作者、文章标题、发表日期） 16 8.3 多表查询 17 9 数据库表和数据库的修改和删除 18 9.1 增加一列 18 9.2 修改记录 18 9.3 增加记录 18 9.4 删除记录 19 9.5 删除表 19 9.6 数据库的...
【Unity精品插件】Easy Save v3.5.16 最新版
09-08
Easy Save：简化 Unity 游戏数据存储与加载 一、Easy Save 简介 Easy Save 是专门为 Unity 开发者设计的一款数据存储和加载工具。它旨在简化数据保存和恢复的过程，使开发者能够专注于游戏的核心逻辑，而不必在数据管理上花费过多的精力。 二、主要特点 （一）多种数据类型支持 支持几乎所有常见的数据类型，包括整数、浮点数、字符串、数组、字典、自定义类等。 例如，可以轻松保存玩家的得分、等级、装备信息以及游戏中的各种配置参数。 （二）简单易用的 API 提供了简洁直观的 API 接口，只需几行代码就能实现数据的保存和加载。 （三）跨平台兼容性 能够在不同的平台上（如 Windows、Mac、Android、iOS 等）保持一致的性能和功能，确保玩家在不同设备上的游戏体验连贯。 （四）加密选项 提供数据加密功能，保障玩家数据的安全性和隐私性。
中南财经政法大学在广东2021-2024各专业最低录取分数及位次表.pdf
09-08
全国各大学在广东省2021~2024年各专业最低录取分数及位次
广西森林（人工和分人工林）30m分辨率.tif
最新发布
09-09
广西森林（人工林和原始森林）.tif 人工林是人为种植的森林，是经过人工干预和管理的，旨在获得经济和生态效益的森林。人工林通常是由一种或几种快速生长的树种组成，种植密度较高，树木生长规律和结构比较均匀。目前，人工林主要用于木材、纸浆和能源木等商业目的。 原始森林是指未受过人类干扰、保持自然生态系统的森林。原始森林具有高度复杂的生态系统，拥有丰富的植物和动物物种，同时还对全球气候具有重要作用。原始森林通常被视为生物多样性的宝库，具有重要的科研和保护价值。 人工林和原始森林在多方面存在差异。人工林是经过人为种植和管理的，树种组成和生长规律比较单一，而原始森林则是自然形成的，具有更高的生物多样性。人工林主要追求经济效益，而原始森林更注重生态系统的保护和科学研究。此外，人工林可以通过种植和砍伐进行经营和利用，而原始森林则需要进行保护和管理以维持其自然状态。
5G中级认证-5G小区搜索.pptx
09-08
5G网络优化
5G中级认证-4G和5G互操作原理及共建共享方案.pptx
09-08
5G网络优化
MySQL基础：显示表结构操作详解
本篇MySQL基础教程详细讲解了如何执行显示表结构的操作。在数据库管理系统的世界中，`DESCRIBE` 或 `DESC` 是两个常用的命令，用于查看MySQL中的表结构，包括字段名、数据类型、长度、是否允许为空以及键约束等信息...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值