一.select高级用法
1.传统连接(只能内连接,取交集,效率最慢)
1.根据两张表查询张三成绩
select t1.sname,t2.mark from t1,t2 where t1.sid=t2.sid and t1.sname=’zhang3’;
2.世界上小于100人的人口城市是哪个国家的
select city.name,city.countrycode,country.name
from city,country
where city.countrycode=country.code
and city.population<;
2.NATURAL JOIN(自连接的表要有共同的列名字)
1.查询,人口在100以上的城市名字,和所说的语言
SELECT city.name,countrylanguage.language FROM city NATURAL JOIN countrylanguage WHERE population >
3.企业中多表连接查询(内连接)
1.都到这了,还看不懂,自杀去吧
select city.name,city.countrycode,country.name
from city join country on city.countrycode=country.code
where city.population<;
4.外连接(反正都比传统的快)
1.略。。。。
select city.name,city.countrycode,country.name
from city left join country
on city.countrycode=country.code
and city.population<;
5.UNION(合并查询)
1.为什么用union,因为他快啊,虽然打起来贼特么麻烦,但他运行效率非常快,优化语句用就是了
#范围查询OR语句
mysql> select * from city where countrycode='CHN' or countrycode='USA';
#范围查询IN语句
mysql> select * from city where countrycode in ('CHN','USA');
替换为: mysql> select * from city where countrycode='CHN'
union all
select * from city where countrycode='USA' limit