PATR FOUR 命令的顺序

Complete SELECT query

SELECT DISTINCT column, AGG_FUNC(column_or_expression), …
FROM mytable
    JOIN another_table
      ON mytable.column = another_table.column
    WHERE constraint_expression
    GROUP BY column
    HAVING constraint_expression
    ORDER BY column ASC/DESC
    LIMIT count OFFSET COUNT;

PATR FIVE 表格内的操作

Step 1 : 插入

关键词:INSERT

格式:

INSERT INTO mytable
(column, another_column, …)
VALUES (value_or_expr, another_value_or_expr, …),
      (value_or_expr_2, another_value_or_expr_2, …),
      …;

举例:

A movie had a rating of 8.7, and made 340 million domestically and 270 million internationally. Add the record to the BoxOffice table

the BoxOffice table:

Movie_idRatingDomestic_salesInternational_sales
37.9245852179239163000
18.3191796233170162503
27.2162798565200600000
INSERT INTO boxoffice 
VALUES (4, 8.7, 340000000, 270000000);

结果:

Movie_idRatingDomestic_salesInternational_sales
37.9245852179239163000
18.3191796233170162503
27.2162798565200600000
48.7340000000270000000

Step 2 :更改

关键词 : SET(常与WHERE连用)

格式:

用WHERE判断什么条件下更新,SET后面接具体更新什么

UPDATE mytable
SET column = value_or_expr, 
    other_column = another_value_or_expr, 
    …
WHERE condition;

举例:

Both the title and director 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";

Step 3 : 删除整行

关键词:DELETE(常与WHERE连用)

格式:

DELETE FROM mytable
WHERE condition;

举例:

Andrew Stanton has left the studio, so please remove all movies directed by him.

很容易理解:就是把director = “Andrew Stanton”的删去

DELETE FROM movies
where director = "Andrew Stanton";

PATR SIX 对表格的操作

Step 1 :创建

关键词:CREATE TABLEIF NOT EXIT

格式:

CREATE TABLE IF NOT EXISTS mytable (
    column DataType TableConstraint DEFAULT default_value,
    another_column DataType TableConstraint DEFAULT default_value,
    …
);

数据类型:

Data typeDescription
INTEGER, BOOLEANThe 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, REALThe 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), TEXTThe 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, DATETIMESQL 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.
BLOBFinally, 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.

表的约束:(我还不会用)

ConstraintDescription
PRIMARY KEYThis means that the values in this column are unique, and each value can be used to identify a single row in this table.
AUTOINCREMENTFor integer values, this means that the value is automatically filled in and incremented with each row insertion. Not supported in all databases.
UNIQUEThis 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 NULLThis means that the inserted value can not be NULL.
CHECK (expression)This allows you to run a more complex expression to test whether the values inserted are valid. For example, you can check that values are positive, or greater than a specific size, or start with a certain prefix, etc.
FOREIGN KEYThis 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.

举例:

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
);

Step 2 : 修改数据表

添加列

格式:

ALTER TABLE mytable
ADD column DataType OptionalTableConstraint 
    DEFAULT default_value;

举例1:

Add a column named Aspect_ratio with a FLOAT data type to store the aspect-ratio each movie was released in.

ALTER TABLE movies
ADD 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.

这里要初始化为“English”

ALTER TABLE movies
ADD Language TEXT
    DEFAULT English;

删除列

格式:

ALTER TABLE mytable
DROP column_to_be_deleted;

重命名列

格式:

ALTER TABLE mytable
RENAME TO new_table_name;

Step 3 :删除数据库

关键词:DROP

格式:

DROP TABLE IF EXISTS mytable;

举例:

We've sadly reached the end of our lessons, lets clean up by removing the Movies table.

(这是网页的原话,但是看到这里我非常想哭,不知道为什么,我真傻....我怎么这么傻,啊烦人的多愁善感)

DROP TABLE IF EXISTS movies;

无关紧要的碎碎念

至此大致了解了数据库的一些操作命令,网站很好,让我在短时间内有所收获。
感觉SQL确实不难,甚至命令都很好写,可能是我接触的太少,等开始了实践实践吧。

哎不知道还有没有人会看了,虽然都不是难知识但是我整理的这么认真哈哈哈哈,算了,自己看叭等以后要上数据库的课还可以考前回顾下~

最近为了项目在学习Android的Kotlin开发,还要继续学习机器学习,训练模型这些乱七八糟的。
几天没学习算法知识了?从KMP到现在了叭,哎,我不是ACMer为什么喜欢学算法呢,可能就是喜欢吧,动脑子多有意思。
刚刚信息化处开了一个会,我们的一卡通也要写需求,之前全是人工,看来要大改动了

明天又要开会,早上还有启动仪式..

脑子有点乱,明天死活要找时间做点算法题,啊。

胃疼,但是今天晚上想唱歌....

碎碎念结束,写需求去了,我为什么这么能自言自语....

Last modification:July 9th, 2020 at 10:53 pm
请赏我杯奶茶,让我快乐长肉