MySQL is simple to complex

Tram Ho

MySQL is very popular, used a lot, but people often forget its syntax, when you need to use it again, go to google. So this article will summarize the syntax, from basic to advanced, so that everyone can use it whenever needed.

Connect to MySQL Server

The first step in working with the MySQL database is to connect to it. In the terminal, type the command:

If the server and client are both running on the same machine, there’s no need to add -h . db_name also optional, because in the next section we will see that it is possible to create and change the current database with just one statement. To end the connection to MySQL, we can type q , quit or Ctrl + D

Create User and Database

MySQL works with the default user as root, but it should only be used for database management, not for data manipulation. That is why we should create users.

In the above code, we do not create 2 users, but 1 user that can be used even when the client is inside or outside the server. Now we will see how to create a database and authorize different users.

Create Table

Tables are the core concept of database management systems such as MySQL. We’ll learn how to create a simple table, and how to set primary keys, restrictions, foreign keys, and default values.

Add a record

After creating the table, the next step is to add data. The next example shows how to add 1 record and multiple records at the same time

Execute the query

The query will be completed with just one SELECT , which allows us to retrieve data from the database. MySQL allows us to execute complex queries, pull data from multiple tables, or create logical operators from the results of other queries.

Create View

View allows us to wrap SELECT into a view like a new table. We can then set permissions for users to that view. However, if the SELECT is wrapped with calculating functions such as SUM , MIN , … or using GROUP BY , DISTINCT , …, it is not possible to perform INSERT , UPDATE , or DELETE with that view. .

Make Transactions

Transaction is a group of commands, if an instruction in the transaction fails, MySQL will undo the previous commands.

Create Stored Procedure

A Stored Procedure is a sequence of SQL statements that can be invoked at any time from the console or from a .sql file. It is similar to function in programming languages.

Play with variables

Variables are a useful way to temporarily store the results of the query or the value of a column in a record for later use. There are two main types of variables in MySQL: local and user-defined (also known as session variables). The first is declared prior to use, and their scope is limited to the stored procedure where they are defined. Type 2 is not declared in advance, its value can be used at any time, but only in the session created by the client.

Play with Cursors

A Cursor is a loop-like tool that runs through each record in the result of a query. It is very useful when you want to convert each record into an object, for example. The way to declare and use is also very easy:

Flow control

Inside the stored procedure, we can manipulate the flow, just like in other programming languages

Create Trigger

Trigger is a method that is run before or after an action, such as insert, update, or delete in a table or view. We need to be careful with triggers as it can consume quite a lot of server resources.

Create a Scheduled Event

Scheduled Event is a trigger that is executed at a specific time. There are events that happen once, scheduled on a specific date, time, or recurring events, run every minute, hour, day, … in a fixed time period.

MySQL is very complicated and has so many things to say that it cannot be covered in this article. However the above should be quite enough for most general purposes, if you need more, you can consult this official document .


Share the news now

Source : Viblo