初学数据库1

select * from movies
where name between 'A' AND 'J';

select * from movies where year between 1990 AND 2000;

select * from movies
where year between 1990 and 2000
and genre = 'comedy';

select * from movies
where genre = 'comedy'
or year < 1980;

select * from movies
order by imdb_rating desc;//通过imdb_rating进行降序排序

select * from movies
order by imdb_rating asc
limit 3;//升序排3个

select count(*) from fake_apps;//显示fake_apps这个关系的行数

select count(*) from fake_apps
where price = 0;

select price,count(*) from fake_apps
group by price;//把不同价格分组，显示数目

select price,count(*) from fake_apps
where downloads > 20000
group by price;

select sum(downloads) from fake_apps;//在某一列求和

select category,sum(downloads) from
fake_apps
group by category;//相同category的为一组，求和

select max(downloads) from fake_apps;

SELECT name, category, MAX(downloads) 
FROM fake_apps
GROUP BY category;//相同的category中找最大的downloads并且显示这三个值

select avg(downloads) from fake_apps;//平均值

SELECT price, ROUND(AVG(downloads), 2) //结果保留两位小数
FROM fake_apps
GROUP BY price;

select price,round(avg(downloads))//忽略小数点后面的
from fake_apps
group by price;

//By specifying that the id column is the PRIMARY KEY, SQL makes sure that:

None of the values in this column are NULL
Each value in this column is unique

SELECT * FROM albums
JOIN artists ON
albums.artist_id = artists.id;//把artists表中的一些列加到albums里（条件如上）

select * from albums
left join artists on//left table is simply the first table that appears in the statement
albums.artist_id = artists.id;

select
albums.name as 'Album',
albums.year,
artists.name as 'Artist'
from
albums
join artists on
albums.artist_id = artists.id
where
albums.year > 1980;