sql語句語法多表關聯_SQL創建表語句-帶有示例語法

sql語句語法多表關聯

SQL is one of the most reliable and straightforward querying languages around. It provides clear cut syntax that reads easily without abstracting away too much of the functionality's meaning.

SQL是最可靠，最直接的查詢語言之一。它提供了清晰易懂的語法，易于閱讀，而無需抽象過多的功能含義。

If you'd like some history on the language as well as some interesting facts, check out the introduction portion of my SQL Update Statement article. ?

如果您想了解某種語言的歷史以及一些有趣的事實，請查看我的《 SQL更新語句》文章的介紹部分。

In this article, we're going to go through the important parts of creating a table in SQL. ?My preferred "flavor" of SQL is SQL Server but the information about creating a table is fairly ubiquitous across all SQL variations. ?

在本文中，我們將介紹在SQL中創建表的重要部分。我最喜歡SQL“風味”是SQL Server，但是有關創建表的信息在所有SQL變體中都非常普遍。

If you've never used SQL or don't know what a table is, fear not! Briefly (and broadly), a table is a database object that holds, or contains, all of the data within that portion of the database. It stores this data in named columns and numbered rows which is not unfamiliar if you've ever used any spreadsheet program. Each row represents a whole database record.

如果您從未使用過SQL或不知道表是什么，請不要擔心！簡要地(廣義上)，表是一個數據庫對象，用于保存或包含數據庫該部分內的所有數據。它將這些數據存儲在命名列和編號行中，如果您曾經使用過任何電子表格程序，這些都不是不熟悉的。每行代表一個完整的數據庫記錄。

If data were in box form then a table would be a section of the warehouse shelving we store those boxes in.

如果數據是盒式的，那么表格將是我們將這些盒存儲在其中的倉庫貨架的一部分。

I'm simplifying the explanation greatly and there is much more to SQL tables but that's outside the scope of this article. ?If you're itching for a more in-depth explanation on tables, I encourage you to dive into the Microsoft Database Design documentation.

我正在極大地簡化說明，SQL表還有很多其他內容，但這不在本文的討論范圍之內。如果您想對表進行更深入的說明，建議您深入閱讀Microsoft數據庫設計文檔。

Before we learn how to create the table, it's important that we learn what types of data these columns and rows can store.

在學習如何創建表之前，重要的是要了解這些列和行可以存儲什么類型的數據。

資料類型 (Data Types)

SQL tables can hold text, numbers, a combination of text and numbers, as well as images and links.

SQL表可以保存文本，數字，文本和數字的組合以及圖像和鏈接。

When creating our table, we designate the type of data its rows and columns will hold. Here are the overarching classifications of data:

在創建表時，我們指定其行和列將保存的數據類型。以下是數據的總體分類：

Approximate Numerics
近似數值
Strings
弦樂
Date & Time
約會時間
Unicode Character Strings
Unicode字符串
Exact Numerics
精確數值
Other
其他

I'll list some of the more commonly used data types below, but if you'd like a more on all data types, I invite you to check out this exhaustive article on each type from Microsoft.

我將在下面列出一些更常用的數據類型，但是如果您想在所有數據類型上都有更多的信息，我邀請您查看Microsoft每種類型的詳盡文章。

Here are the more commonly used types of data from my experience, in no particular order:

根據我的經驗，以下是一些最常用的數據類型，不分先后順序：

char(size) - fixed length string that can contain letters, numbers, special characters
char(size)- 固定長度的字符串，可以包含字母，數字和特殊字符
varchar(size) - variable length string that can contain letters, numbers, & special characters
varchar(size)- 可變長度的字符串，可以包含字母，數字和特殊字符
boolean - Zero (or values that equate to 0) is false, non-zero is true
布爾值-零(或等于0的值)為false，非零為true
int(size optional) - a number up to 10 characters in length, accepts negative & positive numbers
int( size可選 )-長度最大為10個字符的數字，可接受負數和正數
bigint(size optional) - a number up to 19 characters in length, accepts negative & positive numberrs
bigint( size可選 )-長度最大為19個字符的數字，可接受負號和正號
float(size, d) - a number with total number size represented by size and the number of characters after the decimal represented by the d
float(size，d)-一個數字，其總大小由size表示，而小數點后的字符數由d表示
date - date in the format of YYYY-MM-DD
date-日期，格式為YYYY-MM-DD
datetime - date time in the format of YYY-MM-DD hh:mm:ss
datetime-日期時間，格式為YYY-MM-DD hh：mm：ss
time - time in the format of hh:mm:ss
時間-時間，格式為hh：mm：ss

Alright, now that we know what types of data the rows and columns can contain let's get into the fun parts!

好了，現在我們知道行和列可以包含哪些數據類型，讓我們進入有趣的部分！

建立表格 (Creating a Table)

Before we start it's important to note that I'll be providing all of my examples independent of any program.

在開始之前，請務必注意，我將提供與所有程序無關的所有示例。

However, if you'd like to start writing queries and you aren't sure where to start, take a look at SQL Server Management Studio. It's a free, robust program that's widely used and supported in the community.

但是，如果您想開始編寫查詢并且不確定從哪里開始，請查看SQL Server Management Studio。這是一個免費，強大的程序，在社區中得到廣泛使用和支持。

Alternatively, there are several options including DB Fiddle that allow you to build schemas and write queries right in your browser. ?

另外，還有一些選項，包括DB Fiddle ，這些選項使您可以在瀏覽器中構建模式并編寫查詢。

Let's start with a simple statement to create a basic table:

讓我們從創建基本表的簡單語句開始：

CREATE TABLE table_name ( column1_name datatype, column2_name datatype, column3_name datatype, column4_name datatype, column5_name datatype,)

CREATE TABLE table_name ( column1_name datatype, column2_name datatype, column3_name datatype, column4_name datatype, column5_name datatype,)

There are other parameters we can add after the datatype to augment the columns:

我們可以在datatype之后添加其他參數以增加列：

NOT NULL - passing this parameter will ensure the column cannot hold a NULL value
NOT NULL傳遞此參數將確保列不能包含NULL值
UNIQUE - passing this parameter will prevent the column from holding the same value more than once
UNIQUE傳遞此參數將防止該列多次保存相同的值
UNIQUE KEY - passing this parameter will designate that column as a unique identifier. It is essentially a combination of the previous two parameters.
UNIQUE KEY傳遞此參數將將該列指定為唯一標識符。它本質上是前兩個參數的組合。

Now, we're going to create a table (named doggo_info which must adhere to the identifier standards for databases) to hold information on the residents of Woof Woof Retreat, a fictional doggy daycare I just thought of :)

現在，我們將創建一個表(名為doggo_info，該表必須遵守數據庫的標識符標準 )，以保存有關Woof Woof Retreat居民的信息，Woof Woof Retreat是我剛剛想到的虛構的小狗日托：)

CREATE TABLE doggo_info ( ID int UNIQUE KEY, Name varchar(50) NOT NULL, Color varchar(50), Breed varchar(50), Age int, Weight int, Height int, Fav_Food varchar(100), Fav_Toy varchar(100), Dislikes varchar(500), Allergies varchar(500) NOT NULL )

CREATE TABLE doggo_info ( ID int UNIQUE KEY, Name varchar(50) NOT NULL, Color varchar(50), Breed varchar(50), Age int, Weight int, Height int, Fav_Food varchar(100), Fav_Toy varchar(100), Dislikes varchar(500), Allergies varchar(500) NOT NULL )

And here is the brand new table we just created:

這是我們剛剛創建的全新表：

Name	Color	Breed	Age	Weight	Height	Fav_Food	Fav_Toy	Dislikes	Allergies

名稱	顏色	品種	年齡	重量	高度	最喜歡的食物	收藏玩具	不喜歡	過敏癥

You'll notice that our table is completely empty and this is because we haven't added any data to it yet. Doing so is beyond the scope of this article but I wanted you to be aware of that tidbit.

您會注意到我們的表完全為空，這是因為我們尚未向其添加任何數據。這樣做不在本文的討論范圍之內，但是我希望您意識到這一點。

從現有表創建表 (Create A Table From An Existing Table)

It is also possible to create a new table based off of an existing table.

也可以基于現有表創建新表。

It's pretty easy and doesn't require that much more syntax. We need to select the table and columns to "copy" from:

這非常簡單，不需要太多語法。我們需要從中選擇要“復制”的表和列：

CREATE TABLE new_table_name AS SELECT column1, column2, column3, column4 (use * to select all columns to be added to the new_table) FROM current_table_name WHERE conditions_exist

CREATE TABLE new_table_name AS SELECT column1, column2, column3, column4 (use * to select all columns to be added to the new_table) FROM current_table_name WHERE conditions_exist

So, expediency's sake, I've added some data to our doggo_info table and it now looks like the example below:

因此，為了方便起見，我已經在我們的doggo_info表中添加了一些數據，現在看起來像下面的示例：


Name	Color	Breed	Age	Weight	Height	Fav_Food	Fav_Toy	Dislikes	Allergies
daisy	red	standard dachshund	1	14	6	salmon flavored kibble	squeeky ball	birds flying over the yard	cats, baths, cleanliness
chief	black/tan	rottweiler	3	41	17	literally anything	rope tug	staying off the couch	listening, behaving, not slobbering on everything
sammie	light honey	golden retriever	9	46	19	beef flavored kibble	her bed	rambutcious puppies	none known


名稱	顏色	品種	年齡	重量	高度	最喜歡的食物	收藏玩具	不喜歡	過敏癥
雛菊	紅	標準臘腸犬	1個	14	6	鮭魚粗磨	粘球	鳥兒飛過院子	貓，浴室，清潔
首席	黑色/棕褐色	羅威納犬	3	41	17	幾乎任何東西	繩拖船	不在沙發上	聆聽，表現，不流連忘返
薩米	淡蜂蜜	金毛尋回犬	9	46	19	牛肉粗磨	她的床	貪婪的小狗	沒有人知道

Now we can create another table based off of the data we have in our doggo_info table by running the query below:

現在，我們可以通過運行以下查詢，基于doggo_info表中的數據創建另一個表：

CREATE TABLE puppies_only AS SELECT * FROM doggo_info WHERE Age < 4 CREATE TABLE puppies_only AS SELECT * FROM doggo_info WHERE Age < 4

We want to create a new table with all of the columns from the doggo_info table but only where the Age is less than 4. After running this query, our new table will look like this:

我們要創建一個新表，其中包含doggo_info表中的所有列，但僅Age小于4。運行此查詢后，新表將如下所示：


Name	Color	Breed	Age	Weight	Height	Fav_Food	Fav_Toy	Dislikes	Allergies
daisy	red	standard dachshund	1	14	6	salmon flavored kibble	squeeky ball	birds flying over the yard	cats, baths, cleanliness
chief	black/tan	rottweiler	3	41	17	literally anything	rope tug	staying off the couch	listening, behaving, not slobbering on everything


名稱	顏色	品種	年齡	重量	高度	最喜歡的食物	收藏玩具	不喜歡	過敏癥
雛菊	紅	標準臘腸犬	1個	14	6	鮭魚粗磨	粘球	鳥兒飛過院子	貓，浴室，清潔
首席	黑色/棕褐色	羅威納犬	3	41	17	幾乎任何東西	繩拖船	不在沙發上	傾聽，表現，不流連忘返

I hope you can see just how powerful this statement can be. ?With a few lines in our query we have essentially copied data from one table into another but only the rows that we wanted. ?

希望您能看到此語句的功能。在查詢中只有幾行，我們基本上已將數據從一個表復制到另一個表中，但僅復制了所需的行。

This is not only a handy tool to have in your developer tool belt – it'll save you untold amounts of time when you need to move data around tables.

這不僅是開發人員工具帶中的便捷工具，而且還可以在需要在表中移動數據時為您節省大量時間。

結語 (Wrapping Up)

Now that you know how to create (or copy) a table in SQL no matter what situation you're presented with, you can start filling the columns and rows with data to store!

既然您知道了如何在SQL中創建(或復制)表，無論遇到什么情況，都可以開始用要存儲的數據填充列和行！

The CREATE TABLE statement is extremely useful and powerful. You're ready to start putting it to good use.

CREATE TABLE語句非常有用且功能強大。您已經準備好開始充分利用它。

If you found this article helpful check out my blog where I frequently post articles about web development, life, and learning.

如果您覺得這篇文章對您有幫助，請訪問我的博客，我經常在其中發布有關Web開發，生活和學習的文章。

While you're there why not sign up for my newsletter? You can do that at the top right of the main blog page. I like to send out interesting articles (mine and others), resources, and tools for ?developers every now and then.

當您在那里時，為什么不注冊我的時事通訊？您可以在博客主頁面的右上角進行操作。我喜歡不時為開發人員發送有趣的文章(我的和其他文章)，資源和工具。

If you have questions about this article or just in general let me know – come say hi on Twitter or any of my other social media accounts which you can find below the ?newsletter sign up on the main page of my blog or on my profile here at fCC :)

如果您對本文有疑問，或者只是一般而言，請告訴我–在Twitter或我的任何其他社交媒體帳戶上打個招呼，您可以在新聞快訊下方找到該郵件，并在我的博客主頁或此處的個人資料上進行注冊： fCC :)

Have an awesome day! Happy learning and happy coding, friend!

祝你有美好的一天！祝您學習愉快，編碼愉快，朋友！