mysql tutorial for beginners (5/8) : CRUD

Показать описание

mysql tutorial for beginners (5/8) : CRUD

To add data to a table, use the INSERT command. Let’s see this in action by populating the table students with the data.

INSERT INTO students, this line, tells MySQL where to insert the following data.
Then, within parentheses, the four column names are listed—id_studnet, name, surname, and email—all separated by commas. This tells MySQL that these are the fields into which the data is to be inserted.
You could skip fields that are autoincremented, and fields with a default value if you wish to use that same default value.
Since id_student is autoincremented field, there is no need to specify value for it.

The second line of each INSERT command contains the keyword VALUES followed by three strings within parentheses, and separated by commas. This supplies MySQL with the three values to be inserted into the columns previously specified.

Remember this, each item of data will be inserted into the corresponding column, in a one-to-one correspondence.
If you accidentally listed the columns in a different order from the data, the data would go into the wrong columns. And the number of columns must match the number of data items.
INSERT INTO students (name, surname, email) VALUES
Now, to display the table’s contents type the following Command.
SELECT * FROM students;
Don’t worry about the SELECT command for now—we’ll cover it later before the end of this tutorial.

Now, to update existing data, use UPDATE Command:
Followed by table name “students”,
SET this keyword is used to specify which field will be updated?
In this case, all entries in the table will be updated, because, I didn’t provide a condition, that will match the elements you want to update,

WHERE keyword is used as condition, that will match all the elements you want to update, and it will change the specified columns for all the matches found.

The WHERE keyword is very powerful, and important to enter correctly; an error could lead a command to the wrong rows (or have no effect in cases where nothing matches the WHERE clause). WHERE Clause considered to be the heart and soul of SQL.

Also, if you do not provide a limit, all matches will be updated. So, the LIMIT qualifier enables you to choose how many rows to return or update or delete in a query, and where in the table to start returning, deleting or updating them.

UPDATE students SET surname = “AL-Hayali” WHERE name
=”Safaa” LIMIT 1

When you need to remove a row from a table, use the DELETE command. Its syntax is similar to the SELECT command and allows you to narrow down the exact row or rows to delete using qualifiers such as WHERE and LIMIT.

Let’s remove the entry whose surname is AL-Hayali:
DELETE FROM students WHERE surname = “AL-HAYALI”

This example issues a DELETE command for all rows whose surname column contains the string AL-Hayali, so, it’s better to limit the result to one entry.

So far, we’ve created a MySQL database and tables, populated them with data. Now it’s time to look at how these searches are performed, and the various commands and qualifiers available.
The operation you will use more often is called SELECT. A SELECT is used to look for information in one or more tables that matches specific criteria.

The basic syntax is as follows:
SELECT something FROM tablename;
The something can be an * (asterisk), which means every column, or you can choose to select only certain columns.
SELECT surname,name FROM students;

ORDER BY sorts returned results by one or more columns in ascending or descending order.
Note that an ORDER BY statement can accept several table fields that allow you to create several levels of ordering. For example, ORDER BY surname, name
ASC would do a sorting of the data by surname, and if there were several entries with the same surname, those entries would be sorted by name.

The GROUP BY section allows you to group results by a specific field. Which is good for retrieving information about a group of data. For example, if you want to know how many students have the surname AL-Hayali, you can issue the following query:
SELECT surname, COUNT(surname) FROM students GROUP BY surname HAVING surname = “AL-HAYALI”;

The option HAVING is quite similar to WHERE but runs at the end of the query. The HAVING field allows you to use functions, whereas WHERE does not.

In the next tutorial, we will learn about indexes, the types of indexes, and the important role of the indexes.

Subscribe for more:
----------------------------------------------------------------------------

SWE.Safaa Al-Hayali - saf3al2a