Master the four most basic data operation statements of SQL: Insert, Select, Update and Delete. Practicing SQL is a valuable asset for database users. In this article, we will guide you to master the four most basic data operation statements - the core functions of SQL - to introduce comparison operators, selection assertions and three-valued logic in sequence. When you complete these studies, it is obvious that you have begun to be proficient in SQL. Before we begin, use the CREATE TABLE statement to create a table (as shown in Figure 1). DDL statements define database objects such as tables, columns, and views. They do not process rows in the table because DDL statements do not process actual data in the database. These tasks are handled by another type of SQL statement? Data Manipulation Language (DML) statements. There are four basic DML operations in SQL: INSERT, SELECT, UPDATE and DELETE. Since these are commonly used by most SQL users, it is necessary for us to explain them one by one here. In Figure 1 we give a table named EMPLOYEES. Each row corresponds to a specific employee record. Please familiarize yourself with this table as we will use it in later examples. INSERT statement Users can use the INSERT statement to insert a row of records into a specified table. For example, to insert the record of employee John Smith into the table in this example, you can use the following statement: INSERT INTO EMPLOYEES VALUES('Smith','John','1980-06-10','Los Angles',16, 45000); Through such an INSERT statement, the system will try to fill these values ??into the corresponding columns. The columns are arranged in the order we defined when we created the table. In this example, the first value "Smith" will be filled in the first column LAST_NAME; the second value "John" will be filled in the second column FIRST_NAME... and so on. We said that the system will "try" to fill in the values, and in addition to enforcing the rules it will also do type checking. If the types do not match (such as filling a string into a column of type number), the system will reject the operation and return an error message. If SQL rejects a column value that you fill in, the values ??in the other columns in the statement will not be filled in. This is because SQL provides support for transactions. A transaction moves a database from one consistency to another. If one part of the transaction fails, the entire transaction will fail and the system will be restored (or rolled back) to the state before the transaction. Returning to the original INSERT example, please note that all integer decimal numbers do not need to be enclosed in single quotes, while string and date type values ??must be distinguished by single quotes. Inserting commas between numbers to increase readability will cause an error. Remember, commas are the delimiter of elements in SQL. Also be careful to use single quotes when entering literal values. Double quotes are used to encapsulate delimited identifiers. For date types, we must use the SQL standard date format (yyyy-mm-dd), but it can be defined in the system to accept other formats. Of course, as the year 2000 is approaching, you'd better use four digits to represent the year. Now that you understand how the INSERT statement works, let's move on to the rest of the EMPLOYEES table: INSERT INTO EMPLOYEES VALUES('Bunyan','Paul','1970-07-04','Boston',12 ,70000);INSERT INTO EMPLOYEES VALUES('John','Adams','1992-01-21','Boston',20,100000);INSERT INTO EMPLOYEES VALUES('Smith','Pocahontas','1976- 04-06','Los Angles',12,100000);INSERT INTO EMPLOYEES VALUES('Smith','Bessie','1940-05-02','Boston',5,200000);INSERT INTO EMPLOYEES VALUES( 'Jones','Davy','1970-10-10','Boston',8,45000);INSERT INTO EMPLOYEES VALUES('Jones','Indiana','1992-02-01','Chicago', NULL,NULL);In the last item, we don't know Mr. Jones' salary grade and annual salary, so we enter NULL (without the quotes). NULL is a special case in SQL, which we will discuss in detail later. For now we just think that NULL represents an unknown value. Sometimes, like the situation we just discussed, we may want to assign values ??to some but not all columns.

In addition to entering NULL for the columns to be omitted, you can also use another INSERT statement, as follows: INSERT INTO EMPLOYEES(FIRST_NAME, LAST_NAME,HIRE_DATE, BRANCH_OFFICE)VALUE('Indiana','Jones','1992-02-01' ,'Indianapolis');In this way, we first list a series of column names after the table name. Columns not listed will be automatically filled with default values, or NULL if no default value is set. Please note that we changed the order of the columns and the order of the values ??corresponds to the new column order. If the FIRST_NAME and LAST_NAME items are omitted from the statement (these two provisions cannot be empty), the SQL operation will fail. Let's take a look at the syntax diagram of the above INSERT statement: INSERT INTO table[(column { ,column})]VALUES(columnvalue [{,columnvalue}]); As in the previous article, we use square brackets to express Optional, curly braces indicate items that can be repeated any number of times (you cannot use these special characters in the actual SQL statement). Parentheses must be used in the VALUE clause and the optional list of column names. SELECT statement The SELECT statement selects specific rows and columns from one or more tables. Because querying and retrieving data are the most important functions in database management, the SELECT statement is the most workload-intensive part of SQL. In fact, someone who only accesses the database to analyze data and generate reports may know nothing about other SQL statements. The result of a SELECT statement is usually another table. During the execution process, the system selects matching rows and columns from the database according to the user's criteria, and places the results into a temporary table. In direct SQL, it displays the results on the terminal's display or sends the results to a printer or file. It can also be combined with other SQL statements to put the results into a table with a known name. The SELECT statement is powerful. Although on the surface it seems that it is only used to complete the relational algebra operation "selection" (or "restriction") mentioned in the first part of this article, in fact it can also complete two other relational operations? "projection" and "connection" ", the SELECT statement can also complete aggregation calculations and sort data. The simplest syntax of the SELECT statement is as follows: SELECT columns FROM tables; When we execute a SELECT statement in this form, the system returns a result table composed of the selected columns and all specified rows in the table selected by the user. This is a form of implementing relational projection operations. Let's look at some examples using the EMPLOYEES table in Figure 1 (this table is what we will use for all future SELECT statement instances. And we give the actual results of the query in Figures 2 and 3. We will show it in the other These results are used in the examples). Suppose you want to view a list of employees' work departments. Then the following is the SQL query you need to write: SELECT BRANCH_OFFICE FROM EMPLOYEES; the execution of the above SELECT statement will produce the results shown in Table 2 in Figure 2. Since we only specified one column in the SELECT statement, there is only one column in our result table. Notice that there are duplicate rows in the result table. This is because there are multiple employees working in the same department (remember that SQL returns values ??from all rows selected). To eliminate duplicate rows in the results, just add the DISTINCT clause to the SELECT statement: SELECT DISTINCT BRANCH_OFFICEFROM EMPLOYEES; the results of this query are shown in Table 3. Now that the duplicate rows have been eliminated, the results are not in order. What if you want the results listed in alphabetical order? Just use the ORDER BY clause to sort the results in ascending or descending order: SELECT DISTINCT BRANCH_OFFICEFROM EMPLOYEESORDER BY BRANCH_OFFICE ASC; the results of this query are shown in Table 4. Notice how the column name BRANCH _OFFICE is placed after the ORDER BY, this is the column we want to sort on. Why do we have to specify the column name even when there is only one column in the result table? This is because we can also sort by other columns in the table, even if they are not displayed. The keyword ASC after the column name BRANCH_ OFFICE means sorting in ascending order. If you want to sort in descending order, use the keyword DESC. We should also point out that the ORDER BY clause only sorts the results in the temporary table; it does not affect the original table. Suppose we want to get a list sorted by department and arranged from the highest paid employee to the lowest paid employee. In addition to what's in salary brackets, we'd also like to see a list by hire date, starting with the most recently hired employee.

The following are the statements you will use: SELECT BRANCH_OFFICE, FIRST_NAME, LAST_NAME, SALARY, HIRE_DATEFROM EMPLOYEESORDER BY SALARY DESC, HIRE_DATE DESC; Here we select and sort multiple columns. The priority of sorting is determined by the order of column names in the statement. SQL will sort the first column listed first. If there are duplicate rows in the first column, these rows will be sorted according to the second column, and if there are duplicate rows in the second column, these rows will be sorted according to the third column... …and so on. The results of this query are shown in Table 5. It is quite troublesome to write out all the column names in a very long table, so SQL allows the use of * when selecting all columns in the table: SELECT * FROM EMPLOYEES; this query returns the entire EMPLOYEES table, such as As shown in Table 1. Below we update the syntax of the SELECT statement given at the beginning (the vertical line represents an optional option, allowing one to be selected.): SELECT [DISTINCT](column [{, columns}])| *FROM table [ {, table}][ORDER BY column [ASC] | DESC[ {, column [ASC] | DESC }]]; Define selection criteria In the SELECT statement we have introduced so far, we have made a selection for the columns in the result table Selects but returns all rows in the table. Let's look at how to restrict the SELECT statement so that it returns only the rows you want: SELECT columns FROM tables [WHERE predicates]; The WHERE clause sets conditions so that only rows that meet the conditions are included in the result table. These conditions are specified by predicates (predicates that indicate a possible fact about something). If the assertion holds for a given row, the row is included in the result table, otherwise the row is ignored. Assertions are usually expressed through comparisons in SQL statements. For example, if you need to query all employees with the last name Jones, you can use the following SELECT statement: SELECT * FROM EMPLOYEESWHERE LAST_NAME = 'Jones'; The LAST_NAME = 'Jones' part is the assertion. When executing this statement, SQL compares the LAST_NAME column of each row to "Jones". If an employee's last name is "Jones", the assertion is true and the employee's information will be included in the result table (see Table 6). The Six Most Used Comparisons The assertion in our example above includes an "equality" based comparison (LAST_NAME = 'Jones'), but SQL assertions can also contain several other types of comparisons. The most commonly used ones are: equal to = not equal to <> less than < greater than > less than or equal to < = greater than or equal to > = The following is an example that is not based on equality comparison: SELECT * FROM EMPLOYEESWHERE SALARY > 50000; This query Employees whose annual salary is greater than $50,000.00 will be returned (see Table 7). Logical connectors Sometimes we need to define a SELECT statement with more than one assertion. For example, if you only wanted to view information about Davy Jones, the results in Table 6 would be incorrect. To further define a WHERE clause, the user can use the logical connectors AND, OR and NOT. In order to get only the records of employee Davy Jones, the user can enter the following statement: SELECT * FROM EMPLOYEESWHERE LAST_NAME = 'Jones' AND FIRST_NAME = 'Davy'; In this example, we connect the two assertions through the logical connector AND. The entire expression is satisfied only if both assertions are satisfied. If the user needs to define a SELECT statement to satisfy the condition when any of the items is true, the OR connector can be used: SELECT * FROM EMPLOYEESWHERE LAST_NAME = 'Jones' OR LAST_NAME = 'Smith'; Sometimes the best way to define an assertion is It is illustrated by the opposite description. If you want to view the information of all employees except those in the Boston office, you can perform the following query: SELECT * FROM EMPLOYEESWHERE NOT(BRANCH_OFFICE = 'Boston'); The keyword NOT is followed by parentheses comparison expression. The result is a negation of the result.

If an employee's department has an office in Boston, the expression in the brackets returns true, but the NOT operator inverts the value, so the row will not be selected. Assertions can be nested with other assertions. To ensure that they are evaluated in the correct order, you can surround them with parentheses: SELECT * FROM EMPLOYEESWHERE (LAST_NAME = 'Jones'AND FIRST_NAME = 'Indiana')OR (LAST_NAME = 'Smith'AND FIRST_NAME = 'Bessie') ;SQL follows the mathematically standard convention for evaluating expressions? Expressions within parentheses will be evaluated first, and other expressions will be evaluated from left to right.

The logical connectors have been explained above. Before explaining the following content, we once again update the syntax of the SELECT statement: SELECT [DISTINCT](column [{, column } ] )| *FROM table [ { , table } ][ORDER BY column [ASC] | [DESC[{ , column [ASC] | [DESC } ] ]