SQL初步语法

#Hello world

use world;

Select ID,NAME,COUNTRYCODE,DISTRICT,POPULATION

FROM world.city LIMIT 10;



#了解mysql

show databases;

show tables;

show columns from city;



#排序



use world;

Select ID,NAME,COUNTRYCODE,DISTRICT,POPULATION

FROM world.city order by CountryCode DESC,Population asc LIMIT 10;



# 过滤

Select ID,NAME,COUNTRYCODE,DISTRICT,POPULATION

FROM world.city where population >= 100000 and population <= 500000 AND countrycode in ("NLD","AFG")

;

# 通配符

Select ID,NAME,COUNTRYCODE,DISTRICT,POPULATION

FROM world.city where name(列名) like "a c%";



# 处理函数（函数一般放在select,where语句里面）

Select ID,NAME,COUNTRYCODE,DISTRICT,POPULATION,length(name) as len_name,#重命名为len_name

substring(name,1,3),left(name,3),upper(name)#substring是选取子集，就是取name的前3个字母，从左边取，并且大写

FROM world.city;

# 

show columns from sys.sys_config;



select * from sys.sys_config;



select datediff(now(),last_update) from sakila.film ;



# 函数

select max(population),min(population),avg(population),sum(Population),count(population) from world.city;



select count(*) from world.city;



select count(distinct countrycode) from world.city;



# 分组计算

select CountryCode,District,avg(population) as avg_popu,sum(Population) as sum_pop

from world.city  group by countrycode,District;



# 子查询

select *

from

(select CountryCode,District,avg(population) as avg_popu,sum(Population) as sum_pop

from world.city  group by countrycode,District) a

where a.avg_popu >= 1000000;



select * from world.city  where population >= (select avg(population)  

from world.city);



# 表联结

use world;

select * from country;



select a.*

from

(select * from world.city) a

left join

(select * from country limit 100) b

on a.countrycode = b.code

left join

(select * from countrylanguage) c

on c.countrycode = a.countrycode;

#



select id,name from world.city where population >= 1000000

union all

select id,name from world.city where population >= 100000

order by id desc;



#

use world;

create table if not exists world.xiaoming_test1

as

select a.id,a.name as name_city,a.countrycode as code_city,a.district,a.population as popu_city,b.*,c.*

from

(select * from world.city) a

left join

(select * from country limit 100) b

on a.countrycode = b.code

left join

(select * from countrylanguage) c

on c.countrycode = a.countrycode;



select * from xiaoming_test1 limit 100;



drop table if exists world.xiaoming_test1;

# 创建表

id,name,countrycode,population,gnp,language;



drop table if exists world.xiaoming_test2;

create table if not exists world.xiaoming_test2

(

id int not null,

name char(40) not null,

countrycode char(5),

population int,

gnp float,

language char(30)

);



# 插入表

INSERT INTO world.xiaoming_test2

select a.id,a.name,a.countrycode as code_city,

A.POPULATION,b.gnp,c.language

from

(select * from world.city) a

inner join

(select * from country limit 100) b

on a.countrycode = b.code

inner join

(select * from countrylanguage) c

on c.countrycode = a.countrycode;



alter table world.xiaoming_test2

add new_name char(80);



select * from world.xiaoming_test2;



delete  from world.xiaoming_test2

where new_name is null;



alter table world.xiaoming_test2

drop gnp;





select count(*),count(code) from world.xiaoming_test1