13 important SQL statements Programmer needs to know

Database is an indispensable part of modern websites. Large or dynamic web sites use the database in some way and when combined with Structured Query Language (SQL), the ability to manipulate data is really endless. If you already know SQL but are still a programmer, make sure you have a firm grasp of the 13 important SQL statements that we covered in this article.

1. 10 simple CSS codes you can study in 10 minutes
2. Install SQL server 2016 from the Installation Wizard (Setup)
3. Use the ALTER DATABASE command to migrate DATABASE in SQL Server

There are many data names that are returned from the data table. Data is often called Rows (rows), Records (records) or Tuples. The terms listed will be used interchangeably throughout this article.

Preface

All examples today will be based on four assumptions. Customers table has the customer's name and age:

13 important SQL statements Programmer needs to know Picture 1

Table heights contains the name and height of any person:

13 important SQL statements Programmer needs to know Picture 2

The staff table has the name and age of the employee - exactly as customers :

13 important SQL statements Programmer needs to know Picture 3

The last table is called people with names and ages, like table customers and staff :

13 important SQL statements Programmer needs to know Picture 4

1. SELECT

The SELECT statement is the simplest, and you need to understand it because it underpins many other commands. Consider practicing writing SQL commands in uppercase, as it makes the statement easier to read and understand.

As its name implies, SELECT is used to select data from the database. This is the simplest usage:

 SELECT * FROM table; 

The above statement has two parts:

1. SELECT * : specify the column you want to select, the * here means you want to select all columns in the table.
2. FROM table : This section tells the database tool where you want to extract the data, replacing "table" with the name of the database table to retrieve.

This SELECT statement is called "select star", using a * sign is a good way to find and calculate data in a table, but this statement is not always used. When using select star, how to present the returned data completely depends on the engine of the database, you can't control the data order returned, so if someone adds a new column to the table , you see variables in your programming language not displaying the correct data. Fortunately, there is another solution to this problem.

You can specify the columns you want to retrieve, as follows:

 SELECT age, name FROM people; 

This query will extract name and age columns from the people table. This may seem a bit boring if you have too much data, but doing so will help reduce many potential problems in the future, as well as make SQL easier to understand with new developers later.

If you want to select additional data, but it is not stored in any table, you can do the following:

 SELECT age, '1234' FROM people; 

13 important SQL statements Programmer needs to know Picture 5

Any string inside the apostrophe will be returned instead of the appropriate column name.

2. WHERE

The SELECT statement is a great option to retrieve data, but if you want to filter the results a bit more, for example, just want to extract people with blue-eyed, January-born and mechanic what to do? This is the time to use the WHERE statement. WHERE allows applying additional conditions to a SELECT, you just need to append it to the end of the statement:

 SELECT age, name FROM people WHERE age > 10; 

13 important SQL statements Programmer needs to know Picture 6

This query is limited to people older than 10. You can combine multiple conditions using the AND operator:

 SELECT age, name FROM people WHERE age > 10 AND age < 20; 

The AND command works exactly like its meaning in English: It applies different conditions to the statement. In the above example, the returned data will be any record that has an age between 10 and 20. Since there are no matching results, no data will be returned.

Another command can be used to combine an OR condition. Here is an example:

 SELECT age, name FROM people WHERE age > 10 OR name = 'Joe'; 

13 important SQL statements Programmer needs to know Picture 7

This query requires returning records that are older than 10 or named Joe. Note that there is only one "=" here, but many programming languages ​​use two equal signs (==) to check for equivalence, which is not necessary for most database engines, so you Should still check carefully on the working environment of the database.

3. ORDER

The ORDER command is used to sort the results, using ORDER is quite simple, just add ORDER to the end of the statement as shown below:

 SELECT name, age FROM people ORDER BY age DESC; 

13 important SQL statements Programmer needs to know Picture 8

If you need to select a specific column and order, you can do the following (ASC is incremental, DESC is descending):

 SELECT name, age FROM people ORDER BY name ASC, age DESC; 

13 important SQL statements Programmer needs to know Picture 9

ORDER BY is probably most useful when combined with other commands. Not all queries will return data in a logical or orderly manner - this command allows you to change that.

4. JOIN

The JOIN command is used to combine the related data stored in one or more tables. You can append the second table to the first table, and specify how the data is connected. Here is a basic example:

 SELECT age, name, height FROM people LEFT JOIN heights USING (name); 

There are a few notes here. You must start with the "LEFT JOIN" syntax, understand that you want to join a table using a LEFT connection type. Next, determine the table you want to join (heights). USING (name) syntax indicates that the "name" column can be found in both tables and this column will be used as a key to combine tables together.

Don't worry if your columns have different names in each table. You can use "ON" instead of "USING":

 SELECT age, name, height FROM people LEFT JOIN heights ON (namea = nameb); 

13 important SQL statements Programmer needs to know Picture 10

The ON command will specify which column is the key to connect. There are many types of connections that you will need some time to learn in detail, this is a quick summary:

1. (INNER) JOIN: Returns the rows contained in both tables.
2. LEFT (OUTTER) JOIN: Returns all rows from the left panel along with the appropriate records in the right panel. If no records match, the records in the left table are still returned.
3. RIGHT (OUTER) JOIN: In contrast to the above connection type, all rows of the right table will be returned with the appropriate rows of the left panel.
4. FULL (OUTER) JOIN: Returns all matching records in two tables.

Syntax INNER or OUTER is optional, it makes everything easier to understand but not necessarily always required to use them.

5. ALIAS

Now that you know the basic commands, try the ALIAS command again.

This statement is used to temporarily rename a table, this new name exists only within the transaction you are running. Here's how to use:

 SELECT A.age FROM people A; 

You can use any matching name you want, in this example I use the letters of the alphabet. Before each column name, ALIAS will be reserved. This ALIAS is assigned to the table immediately after the declaration. The same, similar:

 SELECT people.age FROM people; 

Instead of having to enter a long table name, simply enter a simple, easy-to-remember letter. But here is a small problem, if you choose from multiple tables, it is easy to confuse the columns in the table. In the case that the tables have identical column names, the database query may be corrupted because the exact reference to the table name or ALIAS cannot be obtained correctly. Here is an example with two tables:

 SELECT staff.age, staff.name, customers.age, customers.name FROM staff, customers; 

And this is the same query with the ALIAS:

 SELECT A.age, A.name, B.age, B.name FROM staff A, customers B; 

The staff table is assigned a new name A, the customers table is assigned a new name B. These tables make the code easier to understand and reduce the number of words to type.

If you want to rename the column with ALIAS, you use the AS command:

 SELECT age AS person_age FROM people; 

13 important SQL statements Programmer needs to know Picture 11

When this query is made, the column will be called "person_age" instead of "age".

6. UNION

UNION is a great command. It allows you to join rows together. Unlike the JOIN command that only appends matching columns, UNION can join unrelated rows if there are the same number of columns and column names. Here's how you use it:

 SELECT age, name FROM customers UNION SELECT age, name FROM staff; 

13 important SQL statements Programmer needs to know Picture 12

A UNION statement will only return single row results between two queries, you can use UNION ALL syntax to return all data, including the same ones.

 SELECT age, name FROM customers UNION ALL SELECT age, name FROM staff; 

13 important SQL statements Programmer needs to know Picture 13

Although the results of the above two statements are the same, but you notice the order of the rows has changed, right? UNION works in the most efficient way, so the returned data may differ in order.

Another case can use the UNION as subtotal, you combine a query of the sum (sum total) into the query of the individual totals for a specific situation. . Heard out loud!

7. INSERT

The above 6 statements all help you extract data from the database, how do you want to insert data into the database? This is the time for the INSERT command to show:

 INSERT INTO people(name, age) VALUES('Joe', 102); 

You must specify the people and columns you want to use (name and age). The VALUES syntax is then used to provide the values ​​to insert. The order of the values ​​to be inserted must be set in the same order as the previously specified columns.

You cannot specify WHERE to insert, and need to ensure that the constraints between tables are followed.

8. UPDATE

After inserting data, you need to change specific rows. This is the syntax of the UPDATE command:

UPDATE people SET name = 'Joe' , age = 101 ;

You must specify the table you want to change, then use SET syntax to define their new columns and values. The statement in this example will update all individual records.

To be more specific, you can use WHERE as you would when you execute the SELECT statement:

 UPDATE people SET name = 'Joe', age = 101 WHERE name = 'James'; 

You can even use the AND, OR conditional operators:

 UPDATE people SET name = 'Joe', age = 101 WHERE (name = 'James' AND age = 100) OR name = 'Ryan'; 

Notice how parentheses are used to enforce the conditions.

9. UPSERT

UPSERT sounds strange, but this is quite a useful command. Suppose there is a restriction on the data table that you only store records with a unique name, you do not want to have two identical names appear in the table. If you try to insert many "Joe" values ​​then the database engine will report an error and refuse to do it (almost like that). The UPSERT command allows you to update the record if it already exists. Without this command, you would have to write a lot of logic to check as if it already exists, if it does not already exist, insert, if it already exists, extract its correct primary key then update. I want to go crazy .

Unfortunately, this command is executed differently on different databases. PostgreSQL recently added this command, while MySQL has been around for a long time. This is the UPSERT command syntax on MySQL for your reference:

 INSERT INTO people(name, age) VALUES('Joe', 101) ON DUPLICATE KEY UPDATE age = 101; 

If you are observant, you will notice that this is essentially an update command in conjunction with the insert command, which can be interpreted as "update if the insert failed".

10. DELETE

The DELETE command is used to completely delete records, it can be quite dangerous if abused. The syntax of this command is quite simple:

 DELETE FROM people; 

The above command will delete everything from the people table. If you only want to delete certain records, use more WHERE:

 DELETE FROM people WHERE name = 'Joe'; 

If you are developing a system, the wiser way is to use a "soft delete" command. Specifically, you never actually run a DELETE command, but create a deleted column (transfer data to it), check the column again to avoid unfortunate deletions. This also helps to quickly retrieve the record if it detects an error or problem to check again. Of course, this is not an appropriate backup option. Please make a backup of your system, because you have to be careful.

11. CREATE TABLE

Yes, as the name suggests, this command is used to create the table, and this is its syntax:

 CREATE TABLE people ( name TEXT, age, INTEGER, PRIMARY KEY(name) ); 

Notice how the column names, constraints are in parentheses, and how to assign the data type to the column is written. Primary keys should also be specified, this is the first requirement of a standard database design.

12. ALTER TABLE

The ALTER TABLE statement is used to modify the structure of a table. This is a bit limited, because your database will not allow changing a table if existing data can cause conflicts, for example, changing a string to an integer. In these cases, you need to edit the data first, then modify the table. Here is an example:

 ALTER TABLE people ADD height integer; 

This example adds a column called "height" with an integer data type to the people table. There is no limit to what you can change.

13. DROP TABLE

The final command is DROP TABLE. This command is similar to DELETE but instead of deleting a single record, it deletes every record in the table. Here's how to use it:

 DROP TABLE people; 

This command is quite dangerous, so it should be done manually in most cases, in case of unexpected errors.

Done, 13 commands all, hope you have pocketed some useful tricks when working with databases. Please share with us the other SQL commands and tricks you've discovered!