SQL学习笔记--1

flask web development 代码写到了用户注册，注册之后要用数据库保存数据，今天该学习SQL了，也没学什么东西，就把书上的内容抄了一遍。
a字母经常出现，小拇指要切换大小写，打字弄得小拇指痛。
明天看flask-mysqldb文档，然后可以继续往下写代码了

A database stores application data in an organized way. The application then issues queries to retrieve specific portions as they are needed.
query 这里理解成查询功能，SQL中间那个Q就是query的首字母

1. 
   
2. countable noun
   A query is a question, especially one that you ask an organization, publication, or expert.
   
3. verb
   If you query something, you check it by asking about it because you are not sure if it is correct.
   
4. verb
   To query means to ask a question.

The most commonly used databases for web applications are those based on the relational model, also called SQL databases in reference to the Structured Query Language they use. But in recent years document-oriented and key-value databases, informally known together as NoSQL databases, have become popular alternatives.

SQL Databases

Relational databases store data in tables, which model the different entities in the application
‘s domain. For example, a database for an order management application will likely have customers, products, and orders tables.

A table has a fixed number of columns and a variable number of rows.
The columns define the data attributes of the entity represented by the table.
Each row in the table defines an actual data element that consists of values for all the columns.

Tables have a special column called the primary key, which holds a unique identifier for each row stored in the table. Tables can also have columns called foreign keys, which reference the primary key of another row from the same or another table. These links between rows are called relationships and are the foundation of the relational database model.

Figure 1 shows a diagram of a simple database with two tables that store users and user roles. The line that connects the two tables represents a relationship between the tables.

Figure 1 Relational database example

In this database diagram, the roles table stores the list of all possible user roles, each identified by a unique id value— the table’s primary key. The users table contains the list of users, each with its own unique id as well. Besides the id primary keys, the roles table has a name column and the users table has username and password columns. The role_id column in the users table is a foreign key that references the id of a role, and in this way the role assigned to each user is established.

As seen in the example , relational databases store data efficiently ans avoid duplication. Renaming a user role in this databases is simple because role names exist in a single place. Immediately after a role name is changed in the roles table, all users that have a role_id that references the changed role will see the update.

On the other hand, having the data split into multiple tables can be a complication. Producing a listing of users with their roles presents a small problem, because users and user roles need to be read from two tables and joined before they can be presented together. Relational database engines provide the support to perform join operations between tables when necessary.

NoSQL Databases

Databases that do not follow the relational model described in the previous section are collections instead of tables and documents instead of records. NoSQL databases are designed in a way that makes joins difficult, so most of them do not support this operation at all. For a NoSQL database structured as in Figure 1, listing the users with their roles requires the application itself to the perform the join operation by reading the role_id field of each user and then searching the roles table for it.

A more appropriate design for a NoSQL database is shown in Figure 2. This is the result of applying an operation called denormalization, which reduces the number of tables at the expense of data duplication.

Figure 2. NoSQL database example

A database with this structure has the role name explicitly stored with each user. Renaming a role can then turn out to be an expensive operation that may requie updating a large number of documents.

But it isn’t all bad news with NoSQL databases. Having the data duplicated allows for faster querying. Listing users and their roles is straightforward because no joins are needed.

SQL or NoSQL?

SQL databases excel at storing structured data in an efficient and compact form. These databases go to great lengths to preserve consistency. NoSQL databases relax some of the consistency requirements and as a result can sometimes get a performance edge.

A full analysis and comparison of database types is outside the scope of this book. For small- to medium-size applications, both SQL and NoSQL databases are perfectly capable and have practically equivalent performance.

python 有flask-mysqldb 包，明天仔细翻看flask-mysqldb 文档就好了。