數(shù)據(jù)庫基礎(chǔ)知識(shí)

將大量數(shù)據(jù)保存起來，通過計(jì)算機(jī)加工而成的可以高效訪問的數(shù)據(jù)集合成為數(shù)據(jù)庫（Database,DB）。用來管理數(shù)據(jù)庫的計(jì)算機(jī)系統(tǒng)成為數(shù)據(jù)庫管理系統(tǒng)（Database Management System, DBMS）。

相比于文本文件或者Excel，數(shù)據(jù)庫的最大優(yōu)勢在于：

? ? * 可多人共享數(shù)據(jù)。

? ? * 提供操作大量數(shù)據(jù)所需格式。

? ? * 實(shí)現(xiàn)讀寫自動(dòng)化需要的編程能力。

? ? * 可應(yīng)對(duì)突發(fā)事故，安全性較高。

DBMS可以分為5個(gè)類型：

? ? * 層次數(shù)據(jù)庫（Hierarchical Database, HDB）:把數(shù)據(jù)通過樹形結(jié)構(gòu)方式表現(xiàn)出來。

? ? * 關(guān)系數(shù)據(jù)庫（Relational Database, RDB）：目前應(yīng)用最廣泛的數(shù)據(jù)庫，采用由行和列組成的二維表來管理數(shù)據(jù)，使用專門的SQL(Structured Query Language, 結(jié)構(gòu)化查詢語言) 對(duì)數(shù)據(jù)進(jìn)行操作。主要的關(guān)系型數(shù)據(jù)庫管理系統(tǒng)（Relational Database Management, RDBMS）有：1）Oracle Database. 2) SQL Server. 3) DB2. 4)? PostgreSQL. 5) MySQL.

? ? * 面向?qū)ο髷?shù)據(jù)庫（Object Oriented Database, OODB）:把數(shù)據(jù)以及對(duì)數(shù)據(jù)的操作集合起來以對(duì)象為單位進(jìn)行管理。

? ? * XML數(shù)據(jù)庫（XML Database, XMLDB）:XML數(shù)據(jù)庫可以對(duì)XML形式的大量數(shù)據(jù)進(jìn)行高速處理。

? ? * 鍵值存儲(chǔ)系統(tǒng)（Key-Value Store,KVS）：單純用來保存查詢所使用的主鍵（Key）和值(Value)的組合數(shù)據(jù)庫。

SQL練習(xí)

題目均取自sqlzoo, 在此只寫下自己的答案。

0. SELECT基礎(chǔ)

? ? 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' (這題結(jié)果雖然對(duì)了，但是應(yīng)該有更準(zhǔn)確的解法)?

? ? 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

本章結(jié)束，下一章會(huì)更有更深入的SQL練習(xí)題。