数据库基础知识
将大量数据保存起来，通过计算机加工而成的可以高效访问的数据集合成为数据库（Database,DB）。用来管理数据库的计算机系统成为数据库管理系统（Database Management System, DBMS）。
相比于文本文件或者Excel，数据库的最大优势在于：
* 可多人共享数据。
* 提供操作大量数据所需格式。
* 实现读写自动化需要的编程能力。
* 可应对突发事故，安全性较高。
DBMS可以分为5个类型：
* 层次数据库（Hierarchical Database, HDB）:把数据通过树形结构方式表现出来。
* 关系数据库（Relational Database, RDB）：目前应用最广泛的数据库，采用由行和列组成的二维表来管理数据，使用专门的SQL(Structured Query Language, 结构化查询语言) 对数据进行操作。主要的关系型数据库管理系统（Relational Database Management, RDBMS）有：1）Oracle Database. 2) SQL Server. 3) DB2. 4) PostgreSQL. 5) MySQL.
* 面向对象数据库（Object Oriented Database, OODB）:把数据以及对数据的操作集合起来以对象为单位进行管理。
* XML数据库（XML Database, XMLDB）:XML数据库可以对XML形式的大量数据进行高速处理。
* 键值存储系统（Key-Value Store,KVS）：单纯用来保存查询所使用的主键（Key）和值(Value)的组合数据库。
SQL练习
题目均取自sqlzoo, 在此只写下自己的答案。
0. SELECT基础
0.1 select population from world where name = 'Germany'
0.2 select name, gdp/population from world where area>5000000
0.3 select name, population from world where name in ('Ireland','Iceland','Denmark')
0.4 select name, area from world where area between 200000 and 250000
1. SELECT name
1.1 select name from world where name like 'Y%'
1.2 select name from world where name like '%Y'
1.3 select name from world where name like '%x%'
1.4 select name from world where name like'%land'
1.5 select name from world where name like 'C%ia'
1.6 select name from world where name like '%oo%'
1.7 select name from world where name like '%a%a%a%'
1.8 select name from world where name like '_t%' order by name
1.9 select name from world where name like '%o__o%'
1.10 select name from world where name like '____'
1.11 select name from world where name = capital
1.12 select name from world where capital = concat(name,' city')
1.13 select capital, name from world where capital like concat('%',name,'%')
1.14 select name, capital from world where (capital <> name) and (capital like concat('%',name,'%'))
1.15 select name, replace(capital,name,'') from world where capital like concat('%',name,'%') and capital <> name
2. SELECT from World
2.1 select name,continent,population from world
2.2 select name from world where population > 200000000
2.3 select name,gdp/population from world where population > 200000000
2.4 select name, population/1000000 from world where continent = 'South America'
2.5 select name, population from world where name in ('France','Germany','Italy')
2.6 select name from world where name like '%United%'
2.7 select name, population, area from world where area > 3000000 or population > 250000000
2.8 select name, population, area from world where area > 3000000 xor population > 250000000
2.9 select name, round(population/1000000,2), round(gdp/1000000000,2) from world where continent = 'South America'
2.10 select name, round(gdp/population,-3) from world where gdp >= 1000000000000
2.11 SELECT name, CASE WHEN continent='Oceania' THEN 'Australasia'
ELSE continent END FROM world WHERE name LIKE 'N%'
2.12 select name,
CASE WHEN continent = 'Europe' or continent = 'Asia'
THEN 'Eurasia'
WHEN continent = 'North America' or continent = 'South America' or continent = 'Caribbean'
THEN 'America'
ELSE continent END
from world where name like 'A%' or name like 'B%'
2.13 select name,continent, CASE
WHEN continent = 'Oceania' THEN 'Australasia'
WHEN continent = 'Eurasia' THEN 'Europe/Asia'
WHEN name = 'Turkey' THEN 'Europe/Asia'
WHEN continent = 'Caribbean' AND name like 'B%' THEN 'North America'
WHEN continent = 'Caribbean' AND name not like 'B%' THEN 'South America'
ELSE continent END
from world
order by name
3. SELECT from Nobel
3.1 select yr,subject,winner from nobel where yr = 1950
3.2 select winner from nobel where yr=1962 and subject='Literature'
3.3 select yr, subject from nobel where winner = 'Albert Einstein'
3.4 select winner from nobel where yr >= 2000 and subject = 'Peace'
3.5 select * from nobel where yr between 1980 and 1989 and subject = 'Literature'
3.6 select * from nobel where winner in ('Theodore Roosevelt', 'Woodrow Wilson','Jimmy Carter')
3.7 select winner from nobel where winner like 'John%'
3.8 select * from nobel where (yr=1980 and subject='physics') or (yr=1984 and subject='chemistry')
3.9 select * from nobel where subject not in ('Chemistry','Medicine') and yr = 1980
3.10 select * from nobel where (yr < 1910 and subject = 'Medicine') or(yr >=2004 and subject = 'Literature')
3.11 select * from nobel where winner like 'PETER GR_NBERG' (这题结果虽然对了，但是应该有更准确的解法)
3.12 select * from nobel where winner = 'EUGENE O''NEILL'
3.13 select winner,yr,subject from nobel where winner like 'Sir%' order by yr desc, replace(winner,'Sir ','')
3.14 select winner,subject from nobel where yr=1984 order by subject in ('Chemistry','Physics'),subject, winner
网友评论