SQL基础

SQL基础

select food from diet where species='orange';

一句话就能将表diet中的’orange’类的food全部选取出来，也是很神奇的！

查询操作的实现

那么SQL的查询(query)操作是如何实现的呢？

下图给出了很好的解释：

自己写的SQL语句通过网络访问数据库服务器，服务器根据这一指令进行直接查询，结果形成一个表作为返回值。也就是说，SQL语句也只不过是间接查询的途径罢了。

唯一性(uniqueness)

表中的每一个条目都是唯一的，那么如何区分呢？数据库中可以使用id进行区分不同的条目。如果每个条目是一个人的话，那么id就是人的身份证，它是区分人是否不同的权威标准。

Join Table

表animals

表diet

select animals.name,animals.species,diet.food from animals join diet on animals.species=diet.species where food ='fish';

通常由on引导join的条件，也就是将两个表animals和diet关联起来的语句，这里要求的是animals.species 与diet.species相同。这条语句的意思就是查询吃鱼的动物的名字，种类，还有习惯的食物。

SQL中的数据类型

比较运算

select .... from table where statements;

statements表示的是过滤条件(row restriction)
这时写这些条件语句的时候边涉及到了比较运算。

很简单跟编程语言一样。

常用的keywords

max 函数

select max(name) from animals;

max函数返回最大值，对字符串而言，大小的比较是首先比较头字母的大小。‘a’->’z’从大到小。如果首字母相同，依次比较下一位字母，直到比较出大小，”空字符最小。

select * from animals limit 10;

limit value表示显示前value个条目。

select name from animals where species='orangutan' order by birthdate;

order by col 表示依据col列的值进行升序排序。

select name from animals where species='orangutan' order by birthdate desc;

order by col desc 表示按col列的值降序排列。

select name, birthdate from animals order by name limit 10 offset 20;

offset value 表示偏移值，这个sql语句的含义就是:按name排序得到第位20-30个含有name，birthdate的条目。

select species,min(birthdate) from animals gruop by species;

group by col 表示按col列的值进行聚类，所有col列的值相同的条目算一类。
本sql语句的含义是：得到animals的种类以及每个种类中最小的生日值（年龄最大的）。

select name,count(*) as num from animals gruop by name order by num desc limit 5;

count()函数统计数量
统计animals中的重名动物，并按重名的数量降序排列，得到重名数最多的前五个名字，及其数量。

插入新的行到表中

insert into tablename values(42,’staff’);
if the new values aren’t in the same order as the table’s columns:
insert into tablename(col2,col1) values(‘staff’,42);

比如在animals中插入一行2014-11-11日出生的叫wibble的oppssum类动物：
insert into animals values(‘wibble’,’oppssum’,’2014-11-11’);

Join the Table (simple join)

join 操作往往能被where替代：
比如：
假设T,S是两个表
1. select T.thing , S.stuff from T join S on T.target = S.match;
T.target 与 S.match 就像是桥梁将表T与表S连接起来了。
2. select T.thing , S.stuff from T, S where T.target = S.match;
语句1与语句2可以达到同样的效果
再举个例子：
select name from animals,diet where animals.species = diet.species and diet.food=’fish’;

select animals.name from animals join diet on animals.species = diet.species where diet.food=’fish’;

表示得到吃鱼的动物的名字。

练习时间

question1：which species does the zoo have only one of?

select species,count(*) as num from animals group by species where num=1;

好吧，上面的语句是显而易见的答案，但是如果你这么认为的话，你就错了！
因为 where引导的过滤条件在count开始之前就进行过滤了，但是过滤的时候还没有num产生，这时找不到num必然报错。
用英文说一遍就是：the value of num comes from count and group by.
But where always runs before aggregations.

通过对上面错误答案的微小改动就能得到question1的正确答案。
答案是：

select species,count(*) as num from animals group by species having num=1;

那么having与where的区别是？
where is a restriction on the source tables.
having is a restriction on the result after aggregation.
也就是where引导的条件表达式中的变量必须是源表(source table)中的。

question2:Find the one food the is eaten by only one animal.
answer1:

select food,count(*) as num from animals join diet on animals.species = diet group by food having num=1;

answer2:

select food,count(animals.name) as num from diet,animals where nimals.species = diet group by food having num=1;

Normalized Design

in a normalized database, the relationships among the tables match the relationships that are really there among the data.

Greate Table and Types

创建 table 的方法是：
create table tablename(
column1 type [constrains],
column2 type [constains],
…,
…,
[row constrains]);
some systems support abbreviations for long type names.
for example,
timestamptz(PostgreSQL only) = timestamp with time zone (SQL standard type name)

In generally, user facing code doesn’t usually create new tables.
日常生活中，我们一般只是在已有数据库的情况下，对数据库的表进行增删改查操作，并不会直接接触到创建数据库中的表的操作。

Creating and Dropping

Create database name[options];
drop database name[options];
drop table name[options];

在psql中具体进行的就是如下操作：
1.打开terminal：$psql
2.psql=> create database fishies;
3.psql=> \c fishies
4.psql=> create table fish(name text,id serial);
5.psql=> insert into fish values(‘papapa’,1);

Declaring Primary Keys

primary key: a column or columns that uniquely identify what each row in a table is about.
for example,
psql=> create table students(
id serial primary key,
name text,
birthdate date);
有时，也有两个key同时组成primary key.
比如，
psql=> create table post_places(
postal_code text,
country text,
name text,
primary key (post_code,country));
一旦primarykey确定了，当我们输入数据时，如果我们输入的数据中有两个item的primary key 相同，数据库会throws error。

Declaring Relationships

psql=> create table sales(
sku text references products,
sale_date date,
count integer);
products 是一个表
psql=> create table sales(
sku text references products(sku),
sale_date date,
count integer );
products(sku)是一个列
references provides referential integrity-columns that are supporsed to refer to each other are guaranteed to do so.

Foreign Keys

A foreign key is a column or set of columns in one table that uniquely identifies rows in another table.
用法举例
create table students(id serial primary key, name text);
create table courses(id text primary key,name text);
create table grades(student integer references student (id),course text references courses (id), garde text);

下面三个表中哪些列是primary key，哪些列是foreign key？
表users

表posts:

表votes:

表1中，username是作为primary key的。
表2中，id 作为primary key，author作为foreign key 并参考表1的primary key： username
表3中，post_id 作为foreign key并参考表2的primary key：id；voter作为foreign key参考表1的primary key: username.

self joins

table residences

create table residences(id integer references students, building text references buildings(naem), room text);

question:find roommate.

select a.id,b.id from residences as a, residences as b where a.building=b.building and a.room = b.room and a.id<b.id order by a.building, a.room;

left join

**A regular(inner) join returns only those rows where the two tables have entries matching the join condition.
A left join returns all those rows plus the row where the left table has an entry but the right table does not **

create table programs(name text,filename text);
create table bugs(filename text,description text,id serial primary key);

question: count the number of bugs of each program.

select programs.name,count(bugs.id) as num from programs left join bugs on programs.name = bugs.filename group by programs.name order by num;

Subqueries

select avg(bigscore) from (select max(score) as bigscore from mooseball group by team ) as maxer;

the principle is one query, not two!

select name from players,(select avg(weight) as av from players) where weight < av;

or

select name,weight from players,(select avg(weight) as av from players) as subq where weight < av;

Views

A view is a select query stored in the database in a way that lets you use it like a table.

create view viewname as select ......;

create view course_size as select course_id,count(*) as num from enrollment group by course_id;

use view

select * from viewname where ....;

delte view

drop view view_name;