sql数据库教程_最好SQL数据库教程

sql数据库教程

SQL stands for Structured Query Language. It is the most common tool used to manipulate and manage data in a relational database (often referred to as a “SQL database”).

SQL代表结构化查询语言。 它是用于处理和管理关系数据库(通常称为“ SQL数据库”)中数据的最常用工具。

SQL is commonly pronounced “sequel.” Its most popular variants are MySQL, PostgreSQL, and SQLite - a version of SQL which is commonly used for prototyping. It introduced the concept of accessing many records with one single command, using SQL Queries.

SQL通常被称为“续集”。 它最受欢迎的变体是MySQL，PostgreSQL和SQLite-SQL的一种版本，通常用于原型制作。 它介绍了使用SQL查询通过一个命令访问许多记录的概念。

We recommend starting with freeCodeCamp's 4 hour SQL database tutorial.

我们建议从freeCodeCamp的4小时SQL数据库教程开始 。

We also recommend Harvard CS50's course on databases and SQL.

我们还推荐哈佛CS50的数据库和SQL课程 。

And if you're feeling up for it, here's an entire 9-hour tutorial on relational database design so you can build your own RDBMS system using SQL.

如果您对此感到满意，这里有一个长达9个小时的关系数据库设计教程，您可以使用SQL构建自己的RDBMS系统。

一些常见SQL语句和查询 (Some common SQL statements and queries)

SQL Select语句 (The SQL Select Statement)

Select和From子句 (Select and From clauses)

The SELECT part of a query is normally to determine which columns of the data to show in the results. There are also options you can apply to show data that is not a table column.

查询的SELECT部分​​通常是确定要在结果中显示数据的哪几列。 您还可以应用其他选项来显示不是表列的数据。

This example shows three columns selected from the “student” table and one calculated column. The database stores the studentID, FirstName, and LastName of the student. We can combine the First and the Last name columns to create the FullName calculated column.

本示例显示了从“学生”表中选择的三列和一个计算出的列。 该数据库存储该学生的studentID，FirstName和LastName。 我们可以组合名字和姓氏列来创建FullName计算列。

select studentID, FirstName, LastName, FirstName + ' ' + LastName as FullName
from student;

+-----------+-------------------+------------+------------------------+
| studentID | FirstName         | LastName   | FullName               |
+-----------+-------------------+------------+------------------------+
|         1 | Monique           | Davis      | Monique Davis          |
|         2 | Teri              | Gutierrez  | Teri Gutierrez         |
|         3 | Spencer           | Pautier    | Spencer Pautier        |
|         4 | Louis             | Ramsey     | Louis Ramsey           |
|         5 | Alvin             | Greene     | Alvin Greene           |
|         6 | Sophie            | Freeman    | Sophie Freeman         |
|         7 | Edgar Frank "Ted" | Codd       | Edgar Frank "Ted" Codd |
|         8 | Donald D.         | Chamberlin | Donald D. Chamberlin   |
|         9 | Raymond F.        | Boyce      | Raymond F. Boyce       |
+-----------+-------------------+------------+------------------------+
9 rows in set (0.00 sec)

The CHECK constraint is used to limit the value range that can be placed in a column.

CHECK约束用于限制可以放置在列中的值范围。

If you define a CHECK constraint on a single column it allows only certain values for this column.

如果在单个列上定义CHECK约束，则该列仅允许某些值。

If you define a CHECK constraint on a table it can limit the values in certain columns based on values in other columns in the row.

如果在表上定义CHECK约束，则可以基于行中其他列中的值来限制某些列中的值。

在CREATE TABLE上执行SQL检查 (SQL CHECK on CREATE TABLE)

The following SQL creates a CHECK constraint on the “Age” column when the “Persons” table is created. The CHECK constraint ensures that you can not have any person below 18 years:

创建“人员”表时，以下SQL在“年龄”列上创建CHECK约束。 CHECK约束确保您不能有18岁以下的任何人：

MySQL:

MySQL：

CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    CHECK (Age>=18)
);

SQL Server / Oracle / MS Access:

SQL Server / Oracle / MS Access：

CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int CHECK (Age>=18)
);

To allow naming of a CHECK constraint, and for defining a CHECK constraint on multiple columns, use the following SQL syntax:

若要命名CHECK约束，并在多个列上定义CHECK约束，请使用以下SQL语法：

MySQL / SQL Server / Oracle / MS Access:

MySQL / SQL Server / Oracle / MS Access：

CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    City varchar(255),
    CONSTRAINT CHK_Person CHECK (Age>=18 AND City='Sandnes')
);

对ALTER TABLE进行SQL检查 (SQL CHECK on ALTER TABLE)

To create a CHECK constraint on the “Age” column when the table is already created, use the following SQL:

要在表已创建时在“年龄”列上创建CHECK约束，请使用以下SQL：

MySQL / SQL Server / Oracle / MS Access:

MySQL / SQL Server / Oracle / MS Access：

ALTER TABLE Persons
ADD CHECK (Age>=18);

To allow naming of a CHECK constraint, and for defining a CHECK constraint on multiple columns, use the following SQL syntax:

若要命名CHECK约束，并在多个列上定义CHECK约束，请使用以下SQL语法：

MySQL / SQL Server / Oracle / MS Access:

MySQL / SQL Server / Oracle / MS Access：

ALTER TABLE Persons
ADD CONSTRAINT CHK_PersonAge CHECK (Age>=18 AND City='Sandnes');

放弃检查约束 (DROP a CHECK Constraint)

To drop a CHECK constraint, use the following SQL:

要删除CHECK约束，请使用以下SQL：

SQL Server / Oracle / MS Access:

SQL Server / Oracle / MS Access：

ALTER TABLE Persons
DROP CONSTRAINT CHK_PersonAge;

MySQL:

MySQL：

ALTER TABLE Persons
DROP CHECK CHK_PersonAge;

SQL Where子句 (SQL Where Clause)

WHERE子句(和/或IN ， BETWEEN和LIKE ) (WHERE Clause (and/or, IN, BETWEEN, and LIKE))

The WHERE clause is used to limit the number of rows returned.

WHERE子句用于限制返回的行数。

In this case all five of these will be used is a somewhat ridiculous WHERE clause.

在这种情况下，将使用所有这五个都是有点荒谬的WHERE子句。

Here is the current full student list to compare to the WHERE clause result set:

这是当前的完整学生列表，可与WHERE子句结果集进行比较：

select studentID, FullName, sat_score, rcd_updated from student;

+-----------+------------------------+-----------+---------------------+
| studentID | FullName               | sat_score | rcd_updated         |
+-----------+------------------------+-----------+---------------------+
|         1 | Monique Davis          |       400 | 2017-08-16 15:34:50 |
|         2 | Teri Gutierrez         |       800 | 2017-08-16 15:34:50 |
|         3 | Spencer Pautier        |      1000 | 2017-08-16 15:34:50 |
|         4 | Louis Ramsey           |      1200 | 2017-08-16 15:34:50 |
|         5 | Alvin Greene           |      1200 | 2017-08-16 15:34:50 |
|         6 | Sophie Freeman         |      1200 | 2017-08-16 15:34:50 |
|         7 | Edgar Frank "Ted" Codd |      2400 | 2017-08-16 15:35:33 |
|         8 | Donald D. Chamberlin   |      2400 | 2017-08-16 15:35:33 |
|         9 | Raymond F. Boyce       |      2400 | 2017-08-16 15:35:33 |
+-----------+------------------------+-----------+---------------------+
9 rows in set (0.00 sec)

Rows will be presented that…

将显示以下行：

• WHERE Student IDs are between 1 and 5 (inclusive)

  WHERE学生ID是1和5(含)之间

• OR studentID = 8

  OR学生ID = 8

Here’s an updated query, where any record that has an SAT score that’s in this list (1000, 1400) will not be presented:

这是一个更新的查询，其中不会显示此列表中(1000，1400)具有SAT分数的任何记录：

select  studentID, FullName, sat_score, recordUpdated
from    student
where   (studentID between 1 and 5 or studentID = 8)
        and
        sat_score NOT in (1000, 1400);

+-----------+----------------------+-----------+---------------------+
| studentID | FullName             | sat_score | rcd_updated         |
+-----------+----------------------+-----------+---------------------+
|         1 | Monique Davis        |       400 | 2017-08-16 15:34:50 |
|         2 | Teri Gutierrez       |       800 | 2017-08-16 15:34:50 |
|         4 | Louis Ramsey         |      1200 | 2017-08-16 15:34:50 |
|         5 | Alvin Greene         |      1200 | 2017-08-16 15:34:50 |
|         8 | Donald D. Chamberlin |      2400 | 2017-08-16 15:35:33 |
+-----------+----------------------+-----------+---------------------+
5 rows in set (0.00 sec)

*As with all of these SQL things there is MUCH MORE to them than what’s in this introductory guide.

*与所有这些SQL事物一样，它们比本入门指南中的内容要多得多。

I hope this at least gives you enough to get started.

我希望这至少能给您足够的入门。

Please see the manual for your database manager and have fun trying different options yourself.

请参阅数据库管理员的手册，并尝试自己尝试其他选项，这很有趣。

SQL更新语句 (SQL Update Statement)

To update a record in a table you use the UPDATE statement.

要更新表中的记录，请使用UPDATE语句。

Be careful. You can update all records of the table or just a few. Use the WHERE condition to specify which records you want to update. It is possible to update one or more columns at a time. The syntax is:

小心。 您可以更新表中的所有记录或仅更新其中的一些记录。 使用WHERE条件指定要更新的记录。 可以一次更新一个或多个列。 语法为：

UPDATE table_name
SET column1 = value1, 
    column2 = value2, ...
WHERE condition;

Here is an example updating the Name of the record with Id 4:

这是一个使用ID 4更新记录名称的示例：

UPDATE Person
SET Name = “Elton John”
WHERE Id = 4;

You can also update columns in a table by using values from other tables. Use JOIN clause to get data from multiple tables. The syntax is:

您还可以通过使用其他表中的值来更新表中的列。 使用JOIN子句从多个表中获取数据。 语法为：

UPDATE table_name1
SET table_name1.column1 = table_name2.columnA
    table_name1.column2 = table_name2.columnB
FROM table_name1
JOIN table_name2 ON table_name1.ForeignKey = table_name2.Key

Here is an example updating Manager of all records:

这是所有记录的更新管理器的示例：

UPDATE Person
SET Person.Manager = Department.Manager
FROM Person
JOIN Department ON Person.DepartmentID = Department.ID

更新查询可以做什么 (What an Update query can do)

An update query gives the DBA or SQL-using programmer the ability to update many records with one command.

更新查询使DBA或使用SQL的程序员能够使用一个命令更新许多记录。

Important Safety Tip! Always have a backup copy of what you are about to change BEFORE you change it!

重要安全提示！ 更改之前，请始终保留要更改内容的备份副本！

This section will:

本节将：

• add a new field to the student table
  向学生表添加新字段
• test the logic to update that field with a school assigned email address
  测试用学校分配的电子邮件地址更新该字段的逻辑
• update the new field.
  更新新字段。

Here is the student table as we start this process:

这是我们开始此过程时的学生表：

SELECT * FROM student;

+-----------+------------------------+-----------+------------------+---------------------+---------------------+
| studentID | FullName               | sat_score | programOfStudy   | rcd_Created         | rcd_Updated         |
+-----------+------------------------+-----------+------------------+---------------------+---------------------+
|         1 | Monique Davis          |       400 | Literature       | 2017-08-16 15:34:50 | 2017-08-16 15:34:50 |
|         2 | Teri Gutierrez         |       800 | Programming      | 2017-08-16 15:34:50 | 2017-08-16 15:34:50 |
|         3 | Spencer Pautier        |      1000 | Programming      | 2017-08-16 15:34:50 | 2017-08-16 15:34:50 |
|         4 | Louis Ramsey           |      1200 | Programming      | 2017-08-16 15:34:50 | 2017-08-16 15:34:50 |
|         5 | Alvin Greene           |      1200 | Programming      | 2017-08-16 15:34:50 | 2017-08-16 15:34:50 |
|         6 | Sophie Freeman         |      1200 | Programming      | 2017-08-16 15:34:50 | 2017-08-16 15:34:50 |
|         7 | Edgar Frank "Ted" Codd |      2400 | Computer Science | 2017-08-16 15:35:33 | 2017-08-16 15:35:33 |
|         8 | Donald D. Chamberlin   |      2400 | Computer Science | 2017-08-16 15:35:33 | 2017-08-16 15:35:33 |
|         9 | Raymond F. Boyce       |      2400 | Computer Science | 2017-08-16 15:35:33 | 2017-08-16 15:35:33 |
+-----------+------------------------+-----------+------------------+---------------------+---------------------+
9 rows in set (0.00 sec)

更改表并添加新字段 (Alter the table and add a new field)

ALTER TABLE `fcc_sql_guides_database`.`student` 
	ADD COLUMN `schoolEmailAdr` VARCHAR(125) NULL AFTER `programOfStudy`;

The student table after the alter is executed.

执行更改后的学生表。

mysql> SELECT FullName, sat_score, programOfStudy, schoolEmailAdr FROM student;
+------------------------+-----------+------------------+----------------+
| FullName               | sat_score | programOfStudy   | schoolEmailAdr |
+------------------------+-----------+------------------+----------------+
| Monique Davis          |       400 | Literature       | NULL           |
| Teri Gutierrez         |       800 | Programming      | NULL           |
| Spencer Pautier        |      1000 | Programming      | NULL           |
| Louis Ramsey           |      1200 | Programming      | NULL           |
| Alvin Greene           |      1200 | Programming      | NULL           |
| Sophie Freeman         |      1200 | Programming      | NULL           |
| Edgar Frank "Ted" Codd |      2400 | Computer Science | NULL           |
| Donald D. Chamberlin   |      2400 | Computer Science | NULL           |
| Raymond F. Boyce       |      2400 | Computer Science | NULL           |
+------------------------+-----------+------------------+----------------+
9 rows in set (0.00 sec)

测试逻辑(非常重要的一步！) (TESTING the logic (VERY important step!))

SELECT FullName, instr(FullName," ") AS firstSpacePosition, 
concat(substring(FullName,1,instr(FullName," ")-1),"@someSchool.edu") AS schoolEmail
FROM student;

+------------------------+--------------------+------------------------+
| FullName               | firstSpacePosition | schoolEmail            |
+------------------------+--------------------+------------------------+
| Monique Davis          |                  8 | Monique@someSchool.edu |
| Teri Gutierrez         |                  5 | Teri@someSchool.edu    |
| Spencer Pautier        |                  8 | Spencer@someSchool.edu |
| Louis Ramsey           |                  6 | Louis@someSchool.edu   |
| Alvin Greene           |                  6 | Alvin@someSchool.edu   |
| Sophie Freeman         |                  7 | Sophie@someSchool.edu  |
| Edgar Frank "Ted" Codd |                  6 | Edgar@someSchool.edu   |
| Donald D. Chamberlin   |                  7 | Donald@someSchool.edu  |
| Raymond F. Boyce       |                  8 | Raymond@someSchool.edu |
+------------------------+--------------------+------------------------+
9 rows in set (0.00 sec)

A note about concat(): in MySQL this command is used to combined strings, not so in other SQL versions (check your manual). In this usage it works like this: The substring of the FullName field up to but not including the first space is combined with “@someSchool.edu”. In the real world this would HAVE TO be much more complex and you would need to ensure that the email address is unique.

关于concat()的注释：在MySQL中，此命令用于组合字符串，而在其他SQL版本中则不是这样(请查看手册)。 在这种用法中，它的工作方式如下：FullName字段的子字符串，直到但不包括第一个空格，都与“ @ someSchool.edu”组合在一起。 在现实世界中，这将变得更加复杂，并且您需要确保电子邮件地址是唯一的。

进行更新 (Doing the update)

We’ll pretend that this is what we want and update the table with this information:

我们将假装这就是我们想要的，并使用以下信息更新表：

UPDATE student SET schoolEmailAdr = concat(substring(FullName,1,instr(FullName," ")-1),"@someSchool.edu")
WHERE schoolEmailAdr is NULL;

Success!

成功！

翻译自: https://www.freecodecamp.org/news/best-sql-database-tutorial/

sql数据库教程