SQLBolt 课程学习笔记四（13-18 + X 课）

[JackieMium]

SQL Lesson 13: Inserting rows

第 13 课，添加行

We've spent quite a few lessons on how to query for data in a database, so it's time to start learning a bit about SQL schemas and how to add new data.
前面都是在学怎么查询数据库，现在该了解一下数据库 Schema（模式）和如何向数据中添加新的行（即观测）了。

What is a Schema?

We previously described a table in a database as a two-dimensional set of rows and columns, with the columns being the properties and the rows being instances of the entity in the table. In SQL, the database schema is what describes the structure of each table, and the datatypes that each column of the table can contain.
Schema 是什么呢？前面我们描述表格时都是由行列数据组成的二位数据，行代表观测对象，列代表属性。在数据库中，Schema 用来描述表格的结构以及表格中每列数据能包含的数据类型。

Example: Correlated subquery
For example, in our Movies table, the values in the Year column must be an Integer, and the values in the Title column must be a String.
比如在前面用到的 Movies 表格中，Year 这一列的值必须是 Interger 类型，Title 列的值必须是 String。

This fixed structure is what allows a database to be efficient, and consistent despite storing millions or even billions of rows.
这种固定的结构使得数据库十分高效，并且在存储百万甚至上亿行数据时仍然十分稳定。（又是一波吹啊）

Inserting new data

When inserting data into a database, we need to use an INSERT statement, which declares which table to write into, the columns of data that we are filling, and one or more rows of data to insert. In general, each row of data you insert should contain values for every corresponding column in the table. You can insert multiple rows at a time by just listing them sequentially.
向数据库中添加数据时需要使用 INSERT 语句声明所要添加数据的表格以及我们添加数据到哪些列，以及我们要添加的一行或者多行数据。一般的，我们每添加一行都应该包含每一列对应的值。连续写多行数据就可以一次性添加多行。语法：

-- Insert statement with values for all columns
INSERT INTO mytable
VALUES (value_or_expr, another_value_or_expr, …),
       (value_or_expr_2, another_value_or_expr_2, …),
       …;

In some cases, if you have incomplete data and the table contains columns that support default values, you can insert rows with only the columns of data you have by specifying them explicitly.
有时候我们手上只要不完整的数据或者表格中有的列有默认值，添加行数据的时候可以选择通过显式的指定来添加我们有的那些列。语法：

-- Insert statement with specific columns
INSERT INTO mytable
(column, another_column, …)
VALUES (value_or_expr, another_value_or_expr, …),
      (value_or_expr_2, another_value_or_expr_2, …),
      …;

In these cases, the number of values need to match the number of columns specified. Despite this being a more verbose statement to write, inserting values this way has the benefit of being forward compatible. For example, if you add a new column to the table with a default value, no hardcoded INSERT statements will have to change as a result to accommodate that change.
这种情况下，每行添加的值的数目必须和指定的列数相匹配。这种添加数据的方式不仅可读性强，而且还能向后兼容。举个例子，如果现在表格中有新的具有默认值的列，非硬编码的 INSERT 语句就需要根据表格的新结构进行修改了。

In addition, you can use mathematical and string expressions with the values that you are inserting.
This can be useful to ensure that all data inserted is formatted a certain way.
另外，添加数据行时也可以使用数学和字符表达式。这可以用来确保添加的都是以某种方式格式化过的数据。比如：

Example Insert statement with expressions
INSERT INTO boxoffice
(movie_id, rating, sales_in_millions)
VALUES (1, 9.9, 283742034 / 1000000);

---

练习题

In this exercise, we are going to play studio executive and add a few movies to the Movies to our portfolio. In this table, the Id is an auto-incrementing integer, so you can try inserting a row with only the other columns defined.
这次练习我们将向前面用到的电影数据中添加更多电影。这个表格中 Id 列是一个自增整数，所以添加新行的时候可以只添加其他列的值就行了。数据：

1. Add the studio's new production, Toy Story 4 to the list of movies (you can use any director)
   向表格中添加新电影《Toy Story 4》，导演是谁无所谓。

既然 Id 不用指定，那么添加的就是非完整数据，必须显式指定列名咯

INSERT INTO Movies (Title, Director, Year, Length_minutes)
    VALUES ("Toy Story 4", "John Lasseter", 2018, 100)

2. Toy Story 4 has been released to critical acclaim! It had a rating of 8.7, and made 340 million domestically and 270 million internationally. Add the record to the BoxOffice table.
   《Toy Story 4》很火，评分和国内外票房分别是这么多这么多以及这么多，添加到 BoxOffice 中。

有一点点小陷阱，就是票房都是百万为单位，得乘以百万算回去然后添加：

INSERT INTO Boxoffice (Movie_id, Rating, Domestic_sales, International_sales)
    VALUES (15, 8.7, 340*1000000, 270*1000000)

收工。

---

SQL Lesson 14: Updating rows

第 14 课，更新行。其实就是改数据嘛。

In addition to adding new data, a common task is to update existing data, which can be done using an UPDATE statement. Similar to the INSERT statement, you have to specify exactly which table, columns, and rows to update. In addition, the data you are updating has to match the data type of the columns in the table schema.
需要更新数据的时候很常见，这时候就要用到 UPDATE 语句了。和 INSERT 语句类似，这时候需要指定哪个表、哪些列和哪些行。另外，改动也必须符合 Schema 中规定好的数据类型。语法：

-- Update statement with values
UPDATE mytable
SET column = value_or_expr, 
    other_column = another_value_or_expr, 
    …
WHERE condition;

The statement works by taking multiple column/value pairs, and applying those changes to each and every row that satisfies the constraint in the WHERE clause.
这个语句把多个列/值数据更改应用到满足 WHERE 语句的行中。

Taking care

Most people working with SQL will make mistakes updating data at one point or another. Whether it's updating the wrong set of rows in a production database, or accidentally leaving out the WHERE clause (which causes the update to apply to all rows), you need to be extra careful when constructing UPDATE statements.

One helpful tip is to always write the constraint first and test it in a SELECT query to make sure you are updating the right rows, and only then writing the column/value pairs to update.
注意，大多数人在用 SQL 的时候不可避免的会在改数据的时候时不时出现点错误。比如在生产环境不小心改错了很多行，或者不小心把 WHERE 从句给掉了（然后全部数据都被改了），所以用 UPDATE 的时候要多留个心眼。

哈哈哈哈写掉 WHERE，想想都好激动呢

---

练习题

It looks like some of the information in our Movies database might be incorrect, so go ahead and fix them through the exercises below.
数据中有些错误，修改一下吧。

1. The director for A Bug's Life is incorrect, it was actually directed by John Lasseter
   《程序猿的一生》的导演错了，应该是 John Lasseter。

UPDATE Movies SET
	Director = "John Lasseter"
WHERE Title = "A Bugs's Life";

2. The year that Toy Story 2 was released is incorrect, it was actually released in 1999
   《Toy Story 2》年份错了，改成 1999：

UPDATE Movies SET
	Year = 1999
WHERE Title = "Toy Story 2";

3. Both the title and directory for Toy Story 8 is incorrect! The title should be "Toy Story 3" and it was directed by Lee Unkrich
   一个数据错了俩，故意的吧。

UPDATE Movies SET
    Title = "Toy Story 3"， -- 开始掉了这个逗号一直报错不知道错在哪儿，注意！
    Director = "Lee Unkrich"
WHERE Title = "Toy Story 8";

撒花。

---

SQL Lesson 15: Deleting rows

第 15 课，删除行。

When you need to delete data from a table in the database, you can use a DELETE statement, which describes the table to act on, and the rows of the table to delete through the WHERE clause.
想从表格里删除行的话需要用 DELETE 语句，语句里指定操作的表格并用 WHERE 来指定删除哪些行。语法：

-- Delete statement with condition
DELETE FROM mytable
WHERE condition;

If you decide to leave out the WHERE constraint, then all rows are removed, which is a quick and easy way to clear out a table completely (if intentional).
如果不要 WHERE 的话，所有行就都删掉了，这是一种迅速的删除表格的办法。
跑路跑路....

Taking extra care

Like the UPDATE statement from last lesson, it's recommended that you run the constraint in a SELECT query first to ensure that you are removing the right rows. Without a proper backup or test database, it is downright easy to irrevocably remove data, so always read your DELETE statements twice and execute once.
和上节课说的 UPDATE 一样，最好首先用 SELECT 看一下会被删除的行是否正确。在没有备份或者测试数据库的时候，非常容易一不小心把数据给搞没了，所以执行 DELETE 之前一定要仔细检查一下。

---

练习题

The database needs to be cleaned up a little bit, so try and delete a few rows in the tasks below.
下面的表格需要清理一下：

1. This database is getting too big, lets remove all movies that were released before 2005.
   数据库太大了，删掉 2005 年之前的电影。
   好任性的理由....

DELETE FROM Movies WHERE Year < 2005;

2. Andrew Stanton has also left the studio, so please remove all movies directed by him.
   Andrew Stanton 不在这儿干了，把所有他导演的电影删掉。

DELETE FROM Movies WHERE Director = "Andrew Stanton"；

---

SQL Lesson 16: Creating tables

第 16 课，创建表格

When you have new entities and relationships to store in your database, you can create a new database table using the CREATE TABLE statement.
当有新数据要储存到数据库时就要使用 CREATE TABLE 来创建新的表格。语法：

-- Create table statement w/ optional table constraint and default value
CREATE TABLE IF NOT EXISTS mytable (
    column DataType TableConstraint DEFAULT default_value,
    another_column DataType TableConstraint DEFAULT default_value,
    …
);

The structure of the new table is defined by its table schema, which defines a series of columns. Each column has a name, the type of data allowed in that column, an optional table constraint on values being inserted, and an optional default value.
新表格的结构由 Schema 定义，它指定了一系列的列。每一列有列名，允许存储的数据类型，可选的对于插入数据的限制性条件，以及可选的默认值。

If there already exists a table with the same name, the SQL implmentation will usually throw an error, so to suppress the error and skip creating a table if one exists, you can use the IF NOT EXISTS clause.
如果数据库中已经存在相同的表格名，SQL 通常会报错，所以一般为了避免报错和表格已经存在情况下创建同名表格可以使用 IF NOT EXISTS 语句。

Table data types

表格的数据类型

Different databases support different data types, but the common types support numeric, string, and other miscellaneous things like dates, booleans, or even binary data. Here are some examples that you might use in real code.
不同的数据库支持不同的数据类型，但是常见的有数字，字符串和其他类型，比如日期，布尔值甚至是二进制数据。下面是一些可能会用到的常见的例子：

Data type	Description
INTEGER, BOOLEAN	The integer datatypes can store whole integer values like the count of a number or an age. In some implementations, the boolean value is just represented as an integer value of just 0 or 1.
FLOAT, DOUBLE, REAL	The floating point datatypes can store more precise numerical data like measurements or fractional values. Different types can be used depending on the floating point precision required for that value.
CHARACTER(num_chars), VARCHAR(num_chars), TEXT	The text based datatypes can store strings and text in all sorts of locales. The distinction between the various types generally amount to underlaying efficiency of the database when working with these columns. Both the CHARACTER and VARCHAR (variable character) types are specified with the max number of characters that they can store (longer values may be truncated), so can be more efficient to store and query with big tables.
DATE, DATETIME	SQL can also store date and time stamps to keep track of time series and event data. They can be tricky to work with especially when manipulating data across timezones.
BLOB	Finally, SQL can store binary data in blobs right in the database. These values are often opaque to the database, so you usually have to store them with the right metadata to requery them.

Table constraints

表格限制条件

We aren't going to dive too deep into table constraints in this lesson, but each column can have additional table constraints on it which limit what values can be inserted into that column. This is not a comprehensive list, but will show a few common constraints that you might find useful.
我们不打算在这节课深入讲这个，但是要知道，每一列都可以通过限制条件来限制哪些值可以填进这一列。下面列出的仅仅是部分很有用的：

Constraint	Description
PRIMARY KEY	This means that the values in this column are unique, and each value can be used to identify a single row in this table.
AUTOINCREMENT	For integer values, this means that the value is automatically filled in and incremented with each row insertion. Not supported in all databases.
UNIQUE	This means that the values in this column have to be unique, so you can't insert another row with the same value in this column as another row in the table. Differs from the PRIMARY KEY in that it doesn't have to be a key for a row in the table.
NOT NULL	This means that the inserted value can not be NULL.
**CHECK (expression) **	This is allows you to run a more complex expression to test whether the values inserted are value. For example, you can check that values are positive, or greater than a specific size, or start with a certain prefix, etc.
FOREIGN KEY	This is a consistency check which ensures that each value in this column corresponds to another value in a column in another table. For example, if there are two tables, one listing all Employees by ID, and another listing their payroll information, the FOREIGN KEY can ensure that every row in the payroll table corresponds to a valid employee in the master Employee list.

An example

Here's an example schema for the Movies table that we've been using in the lessons up to now.
下面是我们上课一直用的 Movies 这个表的 Schema：

-- Movies table schema
CREATE TABLE movies (
    id INTEGER PRIMARY KEY,
    title TEXT,
    director TEXT,
    year INTEGER, 
    length_minutes INTEGER
);

---

练习

In this exercise, you'll need to create a new table for us to insert some new rows into.
这次练习需要自己建表了：

1. Create a new table named Database with the following columns:

• Name A string (text) describing the name of the database
• Version A number (floating point) of the latest version of this database
• Download_count An integer count of the number of times this database was downloaded
  This table has no constraints.

CREATE TABLE Database (
	Name TEXT,
	Version FLOAT,
	Download_count INTEGER
);

睡个午觉，起来继续。zzzzzzzzz....

---

下一课，继续吧。

SQL Lesson 17: Altering tables

第 17 课，改表格。

As your data changes over time, SQL provides a way for you to update your corresponding tables and database schemas by using the ALTER TABLE statement to add, remove, or modify columns and table constraints.
随着时间我们的数据会变化，SQL 提供了 ALTER TABLE 语句用来通过增删和改动数据列或者表格属性来更新相应的表格和数据库 Schema。

Adding columns 添加列

The syntax for adding a new column is similar to the syntax when creating new rows in the CREATE TABLE statement. You need to specify the data type of the column along with any potential table constraints and default values to be applied to both existing and new rows. In some databases like MySQL, you can even specify where to insert the new column using the FIRST or AFTER clauses, though this is not a standard feature.
添加新的列的语法和 CREATE TABLE 类似，需要指定数据类型和可选的对于已有的和新的行的限制条件及默认值。有的数据库比如 MySQL，我们还能通过 FIRST 或 AFTER 指定新的列添加到哪里，当然这不是数据库的标准特性。语法：

-- Altering table to add new column(s)
ALTER TABLE mytable
ADD column DataType OptionalTableConstraint 
    DEFAULT default_value;

Removing columns 删除列

Dropping columns is as easy as specifying the column to drop, however, many databases (including Postgres, and SQLite) don't support this feature. Instead you may have to create a new table and migrate the data over.
删除列只需要简单的指定删除哪些列就行了。但是，很多数据库（包括 Postgres 和 SQLite）都不支持这一特性，我们只能创建新表格然后迁移数据。（Postgres 竟然不支持的么....这么实用的特征，好麻烦）。语法：

-- Altering table to remove column(s)
ALTER TABLE mytable
DROP column_to_be_deleted;

Renaming the table 重命名表格

If you need to rename the table itself, you can also do that using the RENAME TO clause of the statement.
想重命名表格只需要 RENAME TO 就行了。语法：

-- Altering table name
ALTER TABLE mytable
RENAME TO new_table_name;

---

练习时间到：

Our exercises use an implementation that only support adding new columns, so give that a try below.
练习题只支持添加新的列，试试吧。

还是那个表格：

1. Add a column named Aspect_ratio with a FLOAT data type to store the aspect-ratio each movie was released in.
   添加 FLOAT 类型的列 Aspect_ratio 。

ALTER TABLE Movies
ADD column Aspect_ratio FLOAT;

2. Add another column named Language with a TEXT data type to store the language that the movie was released in. Ensure that the default for this language is English.
   添加新列 Language，类型为 TEXT，默认值为 English。

ALTER TABLE Movies
ADD column Language TEXT
	DEFAULT English;

SQL Lesson 18: Dropping tables

第 18 课，删除表格。

最后一节课了，从入门到删库，终于到了删库跑路了哈哈哈哈。

In some rare cases, you may want to remove an entire table including all of its data and metadata, and to do so, you can use the DROP TABLE statement, which differs from the DELETE statement in that it also removes the table schema from the database entirely.
有时候我们想要删除整个表格及其元数据（然后离职跑路？），这时候就要用 DROP TABLE 了。它和 DELETE 的区别在于表格 Schema 也会同时删掉。语法：

-- Drop table statement
DROP TABLE IF EXISTS mytable;

Like the CREATE TABLE statement, the database may throw an error if the specified table does not exist, and to suppress that error, you can use the IF EXISTS clause.
和 CREATE TABLE 类似，这时候如果表格不存在数据库会报错，解决的办法还是顺手加个 IF EXISTS。

In addition, if you have another table that is dependent on columns in table you are removing (for example, with a FOREIGN KEY dependency) then you will have to either update all dependent tables first to remove the dependent rows or to remove those tables entirely.
另外，如果其他的表格依赖与你想删除的表格（比如 FOREIGN KEY 依赖），那么你得把所有依赖关系事先去掉，要么连同那些表格也一起被删除。

---

练习

We've reached the end of our exercises, so lets clean up by removing all the tables we've worked with.
练习题接近尾声了，直接把表全删了吧。
啊，这就结束了么，有点小伤感呢。

还是那两张表格：

1. We've sadly reached the end of our lessons, lets clean up by removing the Movies table
   好桑心，课程要结束了，把 Movies 删了吧

DROP TABLE IF EXISTS Movies；

2. And drop the BoxOffice table as well
   Boxofice 也删掉吧

DROP TABLE IF EXISTS Boxoffice；

难忘，今宵，难忘今宵，无论....

---

等等，还没完——

---

SQL Lesson X: To infinity and beyond!

You've finished the tutorial!

We hope the lessons have given you a bit more experience with SQL and a bit more confidence to use SQL with your own data.

We've just brushed the surface of what SQL is capable of, so to get a better idea of how SQL can be used in the real world, we'll be adding more articles in the More Topics part of the site. If you have the time, we recommend that you continue to dive deeper into SQL!

If you need further details, it's also recommended that you read the documentation for the specific database that you are using, especially since each database has its own set of features and optimizations.

If you have any suggestions on how to make the site better, you can get in touch using one of the links in the footer below.

And if you found the lessons useful, please consider donating ($4) via Paypal to support our site. Your contribution will help keep the servers running and allow us to improve and add even more material in the future.

后面竟然还有番外 More Topics！

这篇先到这里吧。