SQL technology Daquan (2)
SQL step gradual (12) ------ Having clause Having clause gives the grammar for the Having clause: select column1, sum (column2) from "list-of-table" group by "column-list" haVing "Condition"; this Having clause allows you to specify conditions for each group, in other words, you can choose the line according to the conditions you specify. If you want to use the Having clause, it should be re-enabled after the gerre. The Having clause will be explained in an example. Suppose our Employee table contains Name, Departmentmen, Salary, and Age of the employee. If you want to choose average salary for each employee in each department, you can use the following SQL statement: select dept, avg (salary) from employeegroup by dept; of course, if you still want to calculate and display Salry greater than 2000 If you have average wage, you can also add a Having clause: Select DEPT, AVG (Salary) from EmployeeGroup by DePthaving Avg (SALARY)> 20000; SQL step by step (13) ----- Order by clause [Order By The syntax of the Order By clause is: select column1, sum (column2) from "list-of-table" Order by "column-list" [ASC | DESC]; [] = OptionalOrder By is an optional clause, It allows you to display the results of the query based on the columns you want to Order By. For example: ASC = Ascending ORDER - This is the default desc = descending ORDER: SELECT Employee_ID, DEPT, NAME, AGE, SALARYFROM EMPLOYEE_INFOWHERE DEPT = 'Sales'RDer by Salary; This SQL statement will be from the Employee_info table Column DEPT is equal to 'sales' Select Employee_ID,, DEPT, NAME, AGE, and SALARY, and list the retrieval result according to their SALARY in ascending order. If you want to sort multiple columns, then add a comma between columns and columns, such as: select Employee_ID, DEPT, NAME, AGE, SALARYFROM EMPLOYEE_INFOHERE DEPT = 'Sales'RDer by Salary, Age DESC; SQL step by step (14 ------ Combination conditions and Boolean operator combination conditions and SQL statements below Boolean operators contain combination conditions: select column1, sum (column2) from "list-of-table" where "condition1" and " Condition2 "; And operator can connect two or more conditions in the WHERE clause. On both sides of the AND condition must be True (true), the two conditions will be displayed when both conditions are met. Of course, you can also use the OR operator, which can also connect two or more conditions in the WHERE clause. However, as long as there is one of the OR operator, the condition is satisfied, so the line will be displayed.
So when you use the OR operator, you can be the OR operator only one side of TRUE or both. The following give an example: SELECT employeeid, firstname, lastname, title, salaryFROM employee_infoWHERE salary> = 50000.00 AND title = 'Programmer'; this SQL statement is selected salary from employee_info table greater than or equal 50000.00 and title equal to 'Programmer' columns Employeeid, Firstname, Lastname, Title and Salary. At this time, the conditions must be true on both sides of the AND operator, and the row will return. If there is one of the conditions as a false, then nothing is displayed. You can use parentheses to enclose the conditions, although they don't necessarily have to be, but it seems clearer, this is a problem of program habits. For example: SELECT employeeid, firstname, lastname, title, salaryFROM employee_infoWHERE (salary> = 50000.00) AND (title = 'Programmer'); another example below: SELECT firstname, lastname, title, salaryFROM employee_infoWHERE (title = 'Sales') OR (Title = 'Programmer'); this statement will select Title, which is equal to 'Sales' or equal to 'programer', from the Employee_info table. SQL step gradual (15) ------ in and Between Conditions Operator IN and Between Conditions Operator The following is the SQL statement of the IN condition operator: Select Column1, SUM (Column2) from "list-of-table" Where column3 In (List-of-Values); below is the SQL statement of the BetWeen Conditional Operator: Select Column1, SUM (Column2) from "List-of-Tables" Where column3 betWeen value1 and value2; actually, the Incondition operator is a Set the member test operator, that is, it is used to test whether a value is provided after the IN keyword. For example as follows: SELECT employeeid, lastname, salaryFROM employee_infoWHERE lastname IN ( 'Hernandez', 'Jones', 'Roberts', 'Ruiz'); this statement is selected lastname from employee_info table is equal to Hernandez, Jones, Roberts or Ruiz Columns Employeeid, Lastname, and Salary of the name. If it will return it in it.
The IN condition operator can use mix conditions to replace, such as you can use an equal number operator or use the OR operator, but the result is the same, for example: select EmployeID, LastName, SalaryFrom Employee_infowhere LastName = 'Hernandez' or lastname = 'Jones' or lastname = 'ruiz'; you can observe that the statement is more short and easy to read when using the in operator, especially when you test two or three values, especially . Of course, you can also use Not in to exclude rows in your list. The BetWeen Condition operator is the middle of the specified value in the BetWeen keyword, such as: select EmployeeID, AGE, LastName, SalaryFrom Employee_infowhere agnware, and 40; AGE is in columns Employeeid, Age, Lastname, and Salary between 30 to 40 years old (including 30 and 40). This statement can also use a BETWEEN operator to replace, for example: Select Employeid, Age, Lastname, SalaryFrom Employee_infowhere age> = 30 and age <= 40; Of course, you can also be similar to Not in. Use Not Between to exclude some data. SQL step gradual (16) ------------------------------------------------------------------------------------------------------------------ The remainder of the division. This operator is not supported by ANSI SQL, however, most databases support him. Here are some useful mathematics functions because it may be used, so I have to set it out here. These functions are not supported in ANSI SQL-92, but they may be valid for some special RDBMs. However, they are valid for several major database systems. Let's talk about these mathematical functions: ABS (X) returns the absolute value SIGN (X) of X) When X is negative, zero, the number returns X, 1, 0 or 1MOD (X, Y) returns The x is returned to the maximum integer CEILING (X) or CEIL (x) equal to X or equal to X or CEIL (X) returns the maximum integer (x, y) that is greater than equal to X or equal to X. The secondary value Round (x) Returns the number of Round (X, D) that is close to X (X, D) Returns the number of decimal points 4 Number of sqrt (x) returns to the square root of X. The following example: SELECT ROALY The first statement above the firstnamefrom employee_info will select Salary closest to the Employee_info table and the firstname column. SQL step gradual (17) ------ Join clause [Join clause IPA I don't know if you find it until now we use the SELECT statement to retrieve it, you can only do it from a table. What should I do if you want to retrieve from two tables or more tables? Fortunately, we can use a very useful feature of SQL and relational database systems, ie "join". For the sake of illustration, "join" is actually related to the relational database system. "Join" allows you to perform data retrieval from two tables or more tables, but only need to take advantage of a SELECT statement.
If there are multiple tables after the FROM key, "Join" can identify them in the SQL SELECT statement. The following example: select "list-of-color" from table1, table2where "search-condition (s)" "join" When you only handle a table, what happens to make "join" explanation more Simple, so I didn't use "join" here. This single database is also known as "FLAT TABLE". Now you have a table's database to retrieve all customers information and what they bought from your store, the following is all of this table: ID First Last Address City State Zip Date Item Price is inserted each time a new line is inserted In the table, all columns will be updated so that unnecessary "extra data" is caused. For example, every time Jenny buy things, the following rows are inserted into the table: id first last address city state zip date item price10982 Wolfgang Jenny 300 N. 1st Ave Yuma AZ 85002 032299 snowboard 25.0010982 Wolfgang Jenny 300 N. 1st Ave Yuma AZ 85002 032299 shovel 15.0010982 Wolfgang Jenny 300 N. 1st Ave Yuma AZ 85002 032299 gloves 15.0010982 Wolfgang Jenny 300 N. 1st Ave Yuma AZ 85002 032299 lantern 35.0010982 Wolfgang Jenny 300 N. 1st Ave Yuma AZ 85002 032299 tent 95.00SQL step by step (18) - ----- The index index index allows DBMS to access data faster. The system creates this internal data structure (ie index), which causes the query to search when the query is listed as an index. This index notifies DBMS to find a certain line in the table when the column index value is given, this is a bit of a book's index, which tells you which page you can find for a given word. Let us create indexes for OwnerID in AntiqueOwners column: CREATE INDEX OID_IDX ON ANTIQUEOWNERS (OWNERID); The following statement is created as the name of it: CREATE INDEX NAME_IDX ON ANTIQUEOWNERS (OWNERLASTNAME, OWNERFIRSTNAME); To delete the index, you can use DROP: DROP INDEX OID_IDX; as in the previous tutorial, we can also "DROP" a table. In the second example, it is created on two columns. Some DBMS does not force the demary key. In other words, the uniqueness of the class will not be automatically enforced. This means, seems to sound in the clouds in the clouds. Ok, give you an example, if you like inserting another row to the Antiqueowners table, such as this Ownerid is 02, some systems can make you do this, even if we ask all the values of all rows.
