初学数据库1

select * from movieswhere name between 'A' AND 'J';select * from movies where year between 1990 AND 2000;select * from movieswhere year between 1990 and 2000and genre = 'comedy';select * from movieswhere genre = 'comedy'or year < 1980;select * from moviesorder by imdb_rating desc;//通过imdb_rating进行降序排序select * from moviesorder by imdb_rating asclimit 3;//升序排3个select count(*) from fake_apps;//显示fake_apps这个关系的行数select count(*) from fake_appswhere price = 0;select price,count(*) from fake_appsgroup by price;//把不同价格分组，显示数目select price,count(*) from fake_appswhere downloads > 20000group by price;select sum(downloads) from fake_apps;//在某一列求和select category,sum(downloads) fromfake_appsgroup by category;//相同category的为一组，求和select max(downloads) from fake_apps;SELECT name, category, MAX(downloads) FROM fake_appsGROUP BY category;//相同的category中找最大的downloads并且显示这三个值select avg(downloads) from fake_apps;//平均值SELECT price, ROUND(AVG(downloads), 2) //结果保留两位小数FROM fake_appsGROUP BY price;select price,round(avg(downloads))//忽略小数点后面的from fake_appsgroup by price;//By specifying that the id column is the PRIMARY KEY, SQL makes sure that:None of the values in this column are NULLEach value in this column is uniqueSELECT * FROM albumsJOIN artists ONalbums.artist_id = artists.id;//把artists表中的一些列加到albums里（条件如上）select * from albumsleft join artists on//left table is simply the first table that appears in the statementalbums.artist_id = artists.id;selectalbums.name as 'Album',albums.year,artists.name as 'Artist'fromalbumsjoin artists onalbums.artist_id = artists.idwherealbums.year > 1980;