和PHP相互配合的时候，经常会听到说建一个表吧，导数据咯，等等之类的。
所以自己在闲暇时也会去看看到底是怎么回事。
数据库顾名思义就是存储数据的，但是你选择那种数据库是最重要的，现在比较流行的数据库是MySQL, MongDB, PostgreSQL等等之类的。在国内现在常用的是MySQL，所以就先看看这个数据库的操作是怎么回事的。
首先是数据库的名字，你会新建一个database，数据库里面会包含很多张表tables，表里面又包含表头table header和表的主体table body。那么我们可以思考一下，表跟表是怎么联系的，表里面又会有什么特别的地方么。
Rules for normalized tables:
1. Every row has the same number of columns.
In practice, the database system won't let us literally have different numbers of columns in different rows. But if we have columns that are sometimes empty (null) and sometimes not, or if we stuff multiple values into a single field, we're bending this rule.
The example to keep in mind here is the diet table from the zoo database. Instead of trying to stuff multiple foods for a species into a single row about that species, we separate them out. This makes it much easier to do aggregations and comparisons.
2. There is a unique key and everything in a row says something about the key.
The key may be one column or more than one. It may even be the whole row, as in the diet table. But we don't have duplicate rows in a table.
More importantly, if we are storing non-unique facts — such as people's names — we distinguish them using a unique identifier such as a serial number. This makes sure that we don't combine two people's grades or parking tickets just because they have the same name.
3. Facts that don't relate to the key belong in different tables.
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.
The example here was the job_skills table, where a single row listed one of a person's technology skills (like 'Linux') and one of their language skills (like 'French'). This made it look like their Linux knowledge was specific to French, or vice versa ... when that isn't the case in the real world. Normalizing this involved splitting the tech skills and job skills into separate tables.
然后来讲一下简单的SQL查询语句1
QUERY = "select max(name) from animals;"
QUERY = "select * from animals limit 10;"
QUERY = "select * from animals where species = 'orangutan' order by birthdate;"
QUERY = "select name from animals where species = 'orangutan' order by birthdate desc;"
QUERY = "select name, birthdate from animals order by name limit 10 offset 20;"
QUERY = "select species, min(birthdate) from animals group by species;"
QUERY = '''
select name, count(*) as num from animals
group by name
order by num desc
limit 5;
'''
这些语句有一些相同的地方，可以来分析一下啊
where
The where clause expresses 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 / offset
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
The order by clause tells the database how to sort the results — usually according to one or more columns. So order by species, name says to sort results first by the species column, then by name within each species.
Ordering happens before limit/offset, so you can use them together to extract pages of alphabetized results. (Think of the pages of a dictionary.)
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, such as max or sum. Without a group by clause, a select statement with an aggregation will aggregate over the whole selected table(s), returning only one row. With a group by clause, it will return one row for each distinct value of the column or expression in the group by clause.
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, ... );
For instance, if a table has three columns (a, b, c) and you want to insert into a and b, you can leave off the column names from the insert statement. But if you want to insert into b and c, or a and c, you have to specify the columns.
A single insert statement can only insert into a single table. (Contrast this with the select statement, which can pull data from several tables using a join.)
select products.name, products.sku, count(*) as num
from products join sales
on products.sku = sales.sku
group by products.sku;
select products.name, products.sku, count(sales.sku) as num
from products left join sales
on products.sku = sales.sku
group by products.sku;
This query will give us a row for every product in the products table, even the ones that have no sales in the sales table.
最后就是怎么去创建一个数据库了1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18create table programs (
name text,
filename text
);
create table bugs (
filename text,
description text,
id serial primary key
);
QUERY = '''
select programs.name, count(bugs.filename) as num
from programs left join bugs
on programs.filename = bugs.filename
group by programs.name
order by num;
'''
一段完整的Python代码1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20import sqlite3
# Fetch some student records from the database.
db = sqlite3.connect("students")
c = db.cursor()
query = "select name, id from students order by name asc;"
c.execute(query)
rows = c.fetchall()
# First, what data structure did we get?
print "Row data:"
print rows
# And let's loop over it too:
print
print "Student names:"
for row in rows:
print " ", row[0]
db.close()