我们都需要的mysql速查表-CSDN博客

As passionate about data science as I am, I love databases. My first contact with them was in a course in my third year of university, where we learned to create and manage databases with MySQL. I learned a lot, and all that knowledge has been very useful for me these last years. However, SQL syntax can be easy to forget. In order to avoid spending more time googling how to do this or that than actually doing it, I preferred to make a little cheat sheet, which I want to share with you today.

像我一样对数据科学充满热情，我喜欢数据库。我第一次与他们接触是在大学三年级的一门课程中，我们在那里学习了如何使用MySQL创建和管理数据库。我学到了很多东西，这些年来所有这些知识对我非常有用。但是，SQL语法很容易忘记。为了避免花费更多的时间来搜索如何执行此操作或不执行该操作，我宁愿制作一些备忘单，今天在此与大家分享。

连接到MySQL服务器 (Connect to a MySQL Server)

The first step to start working with a MySQL database is to connect to it. From a terminal window, type:

开始使用MySQL数据库的第一步是连接到它。在终端窗口中，键入：

mysql [-h machine] -u <user> -p [db_name]

If the server and the client are running on the same machine, the -h flag is not needed. db_name is also optional, since as we’ll see now, you can create or change the current database with a single command.

如果服务器和客户端在同一台计算机上运行，则不需要-h标志。 db_name也是可选的，因为我们现在将看到，您可以使用单个命令来创建或更改当前数据库。

Once connected to the server, there are some interesting commands we can run at any moment from the shell:

连接到服务器后，您可以随时从外壳运行一些有趣的命令：

\T: prints all the output into a text file
\T ：将所有输出打印到文本文件中
\s: prints some info about the MySQL server
\s ：输出有关MySQL服务器的一些信息
\c: removes the current sentence
\c ：删除当前句子
\.somefile.sql: runs all the SQL sentences stored into a file
\.somefile.sql ：运行存储在文件中的所有SQL语句
help: shows the manual pages
help ：显示手册页
help create table: shows manual page about the “CREATE TABLE” command
help create table ：显示有关“CREATE TABLE”命令的手册页
\q finishes connection with the MySQL server
\q完成与MySQL服务器的连接

创建用户和数据库 (Create a User and a Database)

MySQL comes with a default root user, but it should be used just for managing the database, not for data manipulation. That’s why we should create a standard user.

MySQL带有默认的root用户，但它仅应用于管理数据库，而不应用于数据操作。这就是为什么我们应该创建一个标准用户。

CREATE USER 'mike' IDENTIFIED BY 'difficultpassword'CREATE USER 'mike'@'localhost' IDENTIFED BY 'difficultpassword'

In this snippet, we’re not creating two users but one which can be used whether the client is inside or outside the server.

在此代码段中，我们不是创建两个用户，而是创建一个无论客户端在服务器内部还是服务器外部均可使用的用户。

Now we’ll see how to create a database and assign different permissions to different users, including the one we have just created.

现在，我们将看到如何创建数据库并将不同的权限分配给不同的用户，包括刚刚创建的数据库。

建立表格 (Create a Table)

Tables are the core concept of relational databases like MySQL. In the following snippet, we’ll learn to create a basic table and then how to do it with primary keys, restrictions, foreign keys, and default values.

表是关系数据库(如MySQL)的核心概念。在以下代码段中，我们将学习创建一个基本表，然后使用主键，限制，外键和默认值来创建表。

插入行 (Insert a Row)

After we have created our tables, the next step will be to fill them with some data. The following example shows how to insert a single row and then multiple rows, at the same time:

创建表之后，下一步将是在其中填充一些数据。下面的示例演示如何同时插入单行然后插入多行：

进行查询 (Do a Query)

A query is done with the SELECT command, which allows us to retrieve data from the database. MySQL allows us to make complex queries, retrieving information from several tables or making logical operations from the results of different queries.

使用SELECT命令进行查询，这使我们能够从数据库中检索数据。 MySQL允许我们进行复杂的查询，从多个表中检索信息，或根据不同查询的结果进行逻辑运算。

创建一个视图 (Create a View)

A view allows us to wrap a SELECT statement into a table-like view. Then we can assign special permissions to users for this view. However, if the wrapped SELECT statement references aggregated functions (like SUM, MIN, and so on) or uses GROUP BY, DISTINCT,etc., the operations INSERT, UPDATE, or DELETE can’t be performed over the view.

视图允许我们将SELECT语句包装到类似表的视图中。然后，我们可以为此视图的用户分配特殊权限。但是，如果包装的SELECT语句引用聚合函数(例如SUM ， MIN等)或使用GROUP BY ， DISTINCT等，则无法在视图上执行INSERT ， UPDATE或DELETE操作。

执行交易 (Perform a Transaction)

A transaction is a sequence of atomic operations, i.e., the whole group of sentences succeeds or the whole fails. If one sentence of a transaction fails, the MySQL engine will undo the previous sentences of that transaction.

事务是原子操作的序列，即，整个语句组成功或全部失败。如果一个事务的句子失败，MySQL引擎将撤消该事务的先前句子。

创建一个存储过程 (Create a Stored Procedure)

A stored procedure is a sequence of SQL sentences that can be called in any moment from the console or a .sql file. It’s similar to a function in a standard programming language.

存储过程是一系列SQL语句，可以随时从控制台或.sql文件中调用它们。它类似于标准编程语言中的函数。

玩变数 (Play With Variables)

Variables are a useful way of temporarily store the result of a query or the value of a column in a row to use it later. There are two main types of variables in MySQL: local and user-defined (aka session variables). The first ones must be declared before using them, and their scope is limited to the stored procedure where they are defined. The second ones don’t have to be declared beforehand and their value can be accessed at any moment, but only inside the session created by the client.

变量是一种临时存储查询结果或行中的列值以供以后使用的有用方法。 MySQL中有两种主要的变量类型：局部变量和用户定义变量(又名会话变量)。必须在使用前先声明它们，并且它们的范围限于定义它们的存储过程。第二个值不必事先声明，并且可以随时访问它们的值，而只能在客户端创建的会话中访问。

玩游标 (Play With Cursors)

A cursor is a tool for iterating through all the results of a query, tuple by tuple. This is very useful when developing a client application that needs to transform each row into an object of a certain class. Declaring a cursor and iterating over it is very easy in MySQL:

游标是一种用于遍历一个查询的所有结果的工具。这在开发需要将每一行转换为特定类的对象的客户端应用程序时非常有用。在MySQL中，声明一个游标并对其进行迭代非常简单：

流量控制 (Flow Control)

Inside a stored procedure, we can do flow control, like in any other programming language:

在存储过程中，我们可以像其他任何编程语言一样进行流控制：

创建触发器 (Create a Trigger)

A trigger is a procedure that is run before or after a certain event happens, like an insert, update, or delete in a table or view. We must be careful with triggers since they can spend a lot of our server’s resources.

触发器是在特定事件发生之前或之后运行的过程，例如在表或视图中的插入，更新或删除。我们必须谨慎对待触发器，因为它们会占用我们服务器的大量资源。

创建预定事件 (Create a Scheduled Event)

A scheduled event is a procedure triggered in a specific moment of time. There are one-time events, scheduled for a day and hour, and periodic events, run every minute, hour, day, and so on, after a certain day and until (optionally) an end date.

预定事件是在特定时间触发的过程。在某一天之后(直到(可选))结束日期，有一次计划为一天和一个小时的一次性事件，以及定期的事件，每分钟，每小时，一天等等运行一次。

MySQL is a very sophisticated DMBS, so as you might imagine, there are so many topics I can’t cover here because this article would become too long. However, with the points covered here, it should be enough for most users, and if you need something else, the official documentation will probably have the answer to your questions.

MySQL是一个非常复杂的DMBS，因此，您可能会想像，这里有太多主题我无法涵盖，因为本文可能会太长。但是，在这里涵盖了要点，对于大多数用户来说，这应该足够了。如果您还需要其他内容，则官方文档可能会回答您的问题。