简介：1.创建表（1）外键：FOREIGN KEY(ordersid) references orders(id)在建表过程中create table team(id int primary key auto_increment,name varchar(40));create table star(id int ,name varchar(40),team_id int,f ...
1.创建表（1）外键：FOREIGN KEY(ordersid) references orders(id)在建表过程中create table team(id int primary key auto_increment,name varchar(40));create table star(id int ,name varchar(40),team_id int,foreign key (team_id) references team(id));insert into team values(null,"Toronto Raptors"),(null,"Milwaukee Bucks"),(null,"Boston Celtics"),(null,"Golden State Warriors"),(null,"Oklahoma City Thunder"),(null,"Dallas Mavericks");insert into star values(2,"科怀-伦纳德",1),(7,"洛瑞",1),(34,"阿德托昆博",2),(22,"米德尔顿",2),(11,"欧文",3),(20,"海沃德",3),(35,"杜兰特",4),(30,"库里",4),(0,"威斯布鲁克",5),(13,"保罗-乔治",5),(77,"卢克-东契奇",6),(41,"诺维斯基",6);（2）在表已经存在，通过修改表的语句增加外键ALTER TABLE 表名 ADD constraint FK_ID#外键名 foreign key(外键字段名) references 外表表名（主字段名）（3）删除外键alter table 表名 drop foreign key 外键名;(4)操作关联表多对多关系：新建一张第三方关系表，保存两张表的主键作为外键，存储两张表主键主键之间的对应关系，来保存两张表之间的关系一对一：在从表建立外键2.连接查询（1）多表设计多表查询select * from team,star; #两张表相乘结果select * from team,star where team.id = star.team_id; #过滤~内连接：自然连接SELECT 查询字段 FROM 表1 [INNER] JOIN 表2 ON 表1.关系字段 = 表2.关系字段select * from team inner join star on team.id = star.team_id;~左外连接查询：在内链接的基础上增加上左边表有而右边表没有的记录select * from team left join star on team.id = star.team_id;~右外连接查询，在内链接的基础上增加上右边表有而左边表没有的记录select * from team right join star on team.id = star.team_id;~全连接查询：select * from team full join star on team.id = star.team_id;#mysql不支持但是支持unionselect from team left join star on team.id = star.team_idunionselect from team right join star on team.id = star.team_id;~查询3号球队的名称和其中的球员的姓名select * from team inner join star on team.id = star.team_id where team_id = 3;select team.name 队名,star.name 球员 from team inner join star on team.id = star.team_id where team_id = 3;3.子查询（1）带IN关键字的子查询，内层查询语句仅仅返回一个数据列，这个数据列中的值将供外层查询语句语句进行比较操作，即嵌套查询~查询号码大于20的球员所属的部门select name from team where id in (select team_id from star where id>20);~查询不大于20的球员所属的部门select name from team where id not in (select team_id from star where id = 20);(2)带EXISTS关键字的子查询：子查询不返回任何数据，只返回Ture or False ,为Ture时才执行外查询select * from team where exists (select team_id from star where id > 20); #两张表的字段名要一样（3）带any关键字的子查询：ANY关键字表示满足其中任意一个条件即可select * from team where id > any (select team_id from star where id = 20);(4) 带ALL关键字的子查询：需要同时满足内查询所有条件select * from team where id > all (select team_id from star where id > 20);select * from team where id > all (select team_id from star where id < 10);表内数据不能为空（5）带比较运算符的子查询 < > <= >= = <>select * from team where id > (select team_id from star where id =0);本文仅代表作者个人观点，不代表巅云官方发声，对观点有疑义请先联系作者本人进行修改，若内容非法请联系平台管理员，邮箱2522407257@qq.com。更多相关资讯，请到巅云www.rzxsoft.cn学习互联网营销技术请到巅云建站www.rzxsoft.cn。 |
