MySQL数据信息库联表查寻及联表删掉的SQL句子如何

联表查寻 1、内联接--等价联接 当然联接,2个表相符合的行才在結果集中化出現 Select 內容 from 表1 inner join 表2 on 等价标准 Select * from student inner join grade on grade.stuid = student.stuid; 2、外连...


联表查寻

1、内联接--等价联接

当然联接,2个表相符合的行才在結果集中化出現

Select 內容 from 表1 inner join 表2 on 等价标准

Select * from student inner join grade on grade.stuid = student.stuid;


2、外联接

左联接:select 內容 from 表1 left  outer join 表2 on 等价标准

右联接:select 內容 from 表1 right  outer join 表2 on 等价标准

左联接按左表內容所有显示信息,右表数据信息不相干联则显示信息null

右联接按右表內容所有显示信息,左表数据信息不相干联则显示信息null

Select * from student right outer join grade on student.stuid = grade.stuid;


3、多表关系

Select 內容 from 表1,表2,表3  where 标准

Select * from student,course,grade where student.stuid = grade.stuid and course.couid = grade.couid  and coursegrade

Select  avg(coursegrade) from student,grade where student.stuid =grade.stuid and sex =’男’ and coursegrade is not null;


4.子查寻--嵌套循环查寻

当某一个查寻标准确实另外一个查寻結果集里时,应用嵌套循环查寻

Select * from grade where stuid = (select stuid from student where stname = ‘李四’);

Select * from grade where stuid in (select stuid from student where stname =’张三’or stname =’李四’);

Select * from grade where stuid = (select stuid from student where stname =’张三’) or stuid = (select  stuid from student where stname =’李四’ );

Select stname from student where stuid in (select stuid from grade where coursegrade between 70 and 90);

Select stname,sex,stel  from student where sno in (select sno from sc group by sno having o)=(select o) from course));

timg.jpg

联表删掉

1、从数据信息表t1 中把这些id值在数据信息表t2 里有配对的纪录全删掉掉

DELETE t1 FROM t1,t2 WHERE t1.id=t2.id    或

DELETE  FROM t1 USING t1,t2 WHERE t1.id=t2.id


2、从数据信息表t1里在数据信息表t2里沒有配对的纪录搜索出去并删掉掉

DELETE t1 FROM t1 LEFT JOIN T2 ON t1.id=t2.id WHERE t2.id IS NULL  或

DELETE  FROM t1,USING t1 LEFT JOIN T2 ON t1.id=t2.id WHERE t2.id IS NULL


3、从2个表格中找到同样纪录的数据信息并把2个表格中的数据信息都删掉掉

DELETE t1,t2 from t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t1.id=25

留意这里的delete t1,t2 from 中的t1,t2不可以是别称

如:delete t1,t2 from table_name as t1 left join table2_name as t2 on t1.id=t2.id where table_name.id=25 在数据信息里边实行是不正确的(MYSQL 版本号很大于5.0在5.0中是能够的)

delete table_name,table2_name from table_name as t1 left join table2_name as t2 on t1.id=t2.id where table_name.id=25 在数据信息里边实行是不正确的(MYSQL 版本号低于5.0在5.0中是能够的)


此外填补DELETE句子基本使用方法 :   

1.删掉表格中某行    DELETE FROM 表名字 WHERE 列名字 = 值

2.删掉全部行,不在删掉表的状况下删掉全部的行     DELETE FROM table_name / DELETE * FROM table_name


文中重要词: 数据信息库 mysql 联表查寻 sql 没经容许不可转截:郑州市企业网站建设|郑州市网站建设|荥阳企业网站建设|巩义企业网站建设|seo优化-全栈互联网