MySQL命令与SQL语句 (5)

9.模糊查询

#不管H在前中后 mysql> mysql> select * from world.city where countrycode like \'%H%\'; +------+-------------------------+-------------+------------------------+------------+ | ID | Name | CountryCode | District | Population | +------+-------------------------+-------------+------------------------+------------+ | 148 | Nassau | BHS | New Providence | 172000 | | 149 | al-Manama | BHR | al-Manama | 148000 | | 201 | Sarajevo | BIH | Federaatio | 360000 | | 202 | Banja Luka | BIH | Republika Srpska | 143079 | ...... #H在最后 mysql> select * from world.city where countrycode like \'%H\'; +------+-------------+-------------+------------------+------------+ | ID | Name | CountryCode | District | Population | +------+-------------+-------------+------------------+------------+ | 201 | Sarajevo | BIH | Federaatio | 360000 | | 202 | Banja Luka | BIH | Republika Srpska | 143079 | | 203 | Zenica | BIH | Federaatio | 96027 | | 756 | Addis Abeba | ETH | Addis Abeba | 2495000 | ...... #H在最前 mysql> select * from world.city where countrycode like \'H%\'; +------+-------------------------+-------------+------------------------+------------+ | ID | Name | CountryCode | District | Population | +------+-------------------------+-------------+------------------------+------------+ | 936 | Kowloon and New Kowloon | HKG | Kowloon and New Kowl | 1987996 | | 937 | Victoria | HKG | Hongkong | 1312637 | | 933 | Tegucigalpa | HND | Distrito Central | 813900 | | 934 | San Pedro Sula | HND | Cortés | 383900 | ......

10.having

#统计中国每个省的总人口数，只打印总人口数小于100 SELECT district,SUM(Population) FROM city WHERE countrycode=\'chn\' GROUP BY district HAVING SUM(Population) < 1000000 ; select 高级用法

# 1. 创建表 mysql> create table stu(id int,name varchar(20)); mysql> create table score(id int,score int); # 2.插数据 mysql> insert into stu values(1,\'qiudao\'),(2,\'qiaodao\'),(3,\'haoda\'); mysql> insert into score values(1,90),(2,110),(3,120); # 3.三张表查需要的数据 mysql> select stu.name,score.score from stu,score where stu..name=\'qiudao\' and stu.id=score.id; +--------+-------+ | name | score | +--------+-------+ | qiudao | 90 | +--------+-------+

1.传统连接

#世界上小于100人的城市，在哪个省，属于哪个国家，人口数量是多少？ 城市名 省名 国家名 人口数量 city.name city.district country.name city.population mysql> select city.name as 城市名,city.district as 省,country.name as 国家,city.population as 城市人口数量 -> from city,country -> where city.population < 100 -> and city.countrycode=country.code; +-----------+-----+----------+--------------------+ | 城市名 | 省 | 国家 | 城市人口数量 | +-----------+-----+----------+--------------------+ | Adamstown | – | Pitcairn | 42 | +-----------+-----+----------+--------------------+ #世界上小于100人的城市，在哪个省，属于哪个国家，人口数量是多少，说的是什么语言？ 城市名 省名 国家名 人口数量 语言 city.name city.district country.name city.population countrylanguage.language mysql> select city.name,city.district,country.name,city.population,countrylanguage.language -> from city,country,countrylanguage -> where city.population < 100 -> and city.countrycode=country.code -> and country.code=countrylanguage.countrycode; mysql> select city.name as 城市名,city.district as 省,country.name as 国家,city.population as 城市人口数量,counuage.language as 语言 -> from city,country,countrylanguage -> where city.population < 100 -> and city.countrycode=country.code -> and country.code=countrylanguage.CountryCode; +-----------+-----+----------+--------------------+-------------+ | 城市名 | 省 | 国家 | 城市人口数量 | 语言 | +-----------+-----+----------+--------------------+-------------+ | Adamstown | – | Pitcairn | 42 | Pitcairnese | +-----------+-----+----------+--------------------+-------------+

2.内连接