SQL 进阶视频课程。Udacity: Intro to Relational Databases和 PostgreSQL语法文档。

Udacity: Intro to Relational Databases

 

 

The syntax of the select statement with a where clause:

 

select columns from tables where condition ;

keyword  + columns+  keyword +tables  + row restriction;  

Columns are separated by commas，; use * to select all columns.

 

where 条件查询可以用and or not;  != 代表不等于

We can switch between the expression form (not X) and (not Y) and the form not (X or Y)

例子

select name from users where name !='ihower' and name !="roy"; 

select name from users where not(name = 'ihower') and not(name = 'roy'); 

 

comparison operators 

< less than

> greater than

!= not equal

<= less than or equal

SQL uses = instead of == to represent equality. 

---

用SQL创建表格：例子： 

Create table animals (

name text,

species text,

birthdate date

); 

⚠️ ：In SQL we always put string and date values inside single quotes. 

 

---

SELECT clauses 

where :表示🚫条件restrictions

filtering a table for rows that follow a particular rule. WHERE supports equalities, inequalities, and boolean operators(among other things):

• where species = 'gorilla' — return only rows that have 'gorilla' as the value of the species column.

• where name >= 'George' — return only rows where the name column is alphabetically after 'George'.

• where species != 'gorilla' and name != 'George' — return only rows where species isn't 'gorilla' and name isn't 'George'.

limit count  offset skip 例子： limit 10 offset 150

The limit clause sets a limit on how many rows to return in the result table. 

The optional offset clause says how far to skip ahead into the results. So limit 10 offset 100 will return 10 results starting with the 101st.

 

order by column_name DESC/ASC

Sort the rows using the columns (one or more, separated by commas) as the sort key. Numerical columns will be sorted in numerical order; string columns in alphabetical order. 

The optional desc modifier tells the database to order results in descending order — for instance from large numbers to small ones, or from Z to A.

  

group by

The group by clause is only used with aggregations. 

Change the behavior of aggregations such as max, count, and sum. With group by, the aggregation will return one row for each distinct value in columns.

例子：

select name, count(*) as num from animals group by name; 

---

 

Insert :Adding Rows to a Table 

 The basic syntax for the insert statement:

insert into table ( column1, column2, ... ) values ( val1, val2, ... );

if the values are in the same order as the table's columns(starting with the first column), you don't have to specify the columns in the insert statement;

insert into table values ( val1, val2, ... );

 

---

 

Joining 

selete columns from table_name1 inner join  table_name2 on 关联的列 where 一系列条件。。

---

Having:

The having clause works like the where clause,but it applies after group by aggregations take place. ⚠️ ：having前面肯定有group by.

 select columns from tables group by column having condition ;

Usually, at least one of the columns will be an aggregate function such as count, max,or sum on one of the table's columns, you'll want to give it a name using AS.

例子：

select name, count(*) as num from sales group by name having num > 5;

 

---

 

 多表连接：

 例子：

https://classroom.udacity.com/courses/ud197/lessons/3423258756/concepts/33885287240923

 

 

select ordernames.name, count(*) as num

  from (animals join taxonomy 

                on animals.species = taxonomy.name)

                as ani_tax

        join ordernames

             on ani_tax.t_order = ordernames.t_order

  group by ordernames.name

  order by num desc

---

Normalized Design  

数据库标准化。

What's Normalized?

Rules for normalized tables:

1.Every row has the same number of columns. 

⚠️ ：如果有时候某些字段会产生空null，最好拆表，这有利于数据的比较和聚合。

2.There is a unique key and everything in a row says something about the key. 

The key maybe one or more than one column. 主键key和外键。

3. Facts that don't relate to the key belong in different tables.

理解：和主key不直接相关的column拆出去，独立列表。 

The example here was the items table, which had items, their locations, and the location's street addresses in it. The address isn't a fact about the item; it's a fact about the location. Moving it to a separate table saves space and reduces ambiguity, and we can always reconstitute the original table using a JOIN.

4. Tables shouldn't imply relationships that don't exist. 

 理解：非key列之间如没有实际的关系，但会造成歧义，应当拆分。例子：如果一个人会开车和会使用电脑，这两个技能是不相关的，标准化要求拆分这两个技能到不同的tables中。

---

PostgreSQL

实操：

Look up these commands in the PostgreSQL documentation:

Create Database

CREATE DATABASE name

pasting

 

Drop Database  

DROP DATABASE [ IF EXISTS ] name 

Create Table

create table tabel_name(column_name data_type, ...) 

 

Drop Table

DROP TABLE [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ] 

彻底删除表和表数据。 如果只是清空表中数据用delete.

 

---

加入主key

单一：

  create table students(id serial primary key, name text, birthdate date) ;

多重： 

  create table postal_places(

postal_code text,

country text,

name text,

primary key(postal_code, country)

  );

---

Foreign keys

如Rails的多对多中设置的 users_id, product_id.

---

Self Joins:

同一个表内的查找连接。

下表是一个学生的房间号，找出下表中同宿舍的学生。

residences: 

+--------+----------+------+

| id | building | room |

+========+==========+======+

| 104131 | Dolliver | 14 |

| 105540 | Kendrick | 3B |

| 118199 | Kendrick | 1A |

| 161282 | Dolliver | 7 |

| 170267 | Dolliver | 1 |

| 231742 | Kendrick | 3B |

 

select a.id, b.id, a.building, a.room

       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;

 

 

---

 

inner join的简写是join

left outer join的简写是left join. 

两个表连接经常用到left join. 

select products.name, count(bugs.fileanme) as sum

from products left join bugs

on products.name = bugs.name

group by products.name

order by sum desc; //没有bug的product也显示，sum列中是0；

---

 Subquery

嵌套查询📖。 

 每个查询都产生一个新的表格，因此新的表格也可以使用SQL语法进行查询。join it ,aggregate it and so on.

加个大括号（） 

https://www.postgresql.org/docs/9.4/static/functions-subquery.html  PostgreSQL的Expressions.

All of the expression forms documented in this section return Boolean(true/false) results.

 例子：

EXISTS(subquery)

 

 view

create view view_name as select...

把搜索结果储存为一个table，就叫view.如果涉及aggregation的function则不能更新或删除，如果仅仅设计增加个column则可以。

 

---

No matter which language you're using, the database and its data will most likely out-live  most of the application code in your program. 

                           ⬇︎ 

to continue to exist after something else has ended or disappeared