MySQL 4-1-database query using MySQL

MySQL 4-1 -- database query 1 bitsCN.com 1. Select (Selection) to act on the row! Select a single object operation. The operation object is a table. This operation selects rows from the table that meet the conditions according to the given conditions to form a new table as the calculation result. Select sF (R) as the calculation mark ). S is the selection operator, subscript F is a conditional expression, and R is the table to be operated. To create a new table for the row with gender as the female in the student table, the formula is: sF (student) F: Gender = "female ", the result of this selection operation is as follows: the student ID is the name of the student, and the professional name is the name of the student. do not generate the master score for the student Date. note: 081103 Wang Yan computer girl 1989-10-06 50 2. projection applies to columns! Projection is also a single object operation. this operation selects the specified attribute values from the table to form a new table, which is recorded :? A (R ). A is the attribute name (column name) table, and R is the table name. If the student ID, name, and total credits are projected in the student information table, the formula is :? Student ID, name, total credits (student). The calculation result is as follows: student ID, surname, name, total score, 081101 Wang Lin 50, 081102 Cheng Ming 50, 081103 Wang Yan 50, 3. join joins Concatenates rows in two tables according to the given conditions to form a new table, which is recorded as RS. R and S are the tables to be operated, and F is the condition. The most common condition for connecting two tables is that some columns of the two tables have equal values. Such a join is called an equijoin. "Natural Connections" are the most commonly used in database applications ". To perform a natural join operation, the two tables must have a common attribute (column ), the result table of the natural join operation is a new table that is obtained after the duplicate attribute is removed after the equivalence join is performed on the common attributes of the two tables involved in the operation. The natural join operation is recorded as RS, where R and S are two tables involved in the operation. 4. select basic column query (single table query): select Column 1, column 2 ,... From table name 1) view all fields: select * fromproduct; 2) some fields (select the specified column): select field name 1, field name 2 from product; use the SELECT statement to SELECT certain columns in a table. the column names must be separated by commas) add a column title. when you want to display certain or all columns in the query results and use the selected column title, you can use the AS clause after the column name to change the column alias of the query results. Field name [as] column title if the column title contains spaces, you must use single quotation marks. the content in the brackets can be omitted. Select pnumber as product number, pname as product name from product; note: column aliases cannot be used in the where clause. This is because the column value may not be determined when executing the where code. For example, the following query is invalid: SELECT gender assex from xs where sex = 0; 4) when the data in the query result is replaced, sometimes it is a concept rather than a specific data that you want to obtain for some queried columns. For example, to query the total credits of an XS table, you want to know the overall learning status. in this case, you can use the level to replace the specific number of total credits. Format: case when Condition 1 then expression 1 when condition 2 then expression 2 ...... Else expression end case: replace the query results (case is a row, so press enter in the middle without a semicolon or comma) Example 1: select student ID, name, case when gender = 1 then 'male' when gender = 0 then 'female' end as gender from xs; Example 2: select student ID, name, case when total credits isnull then' not selected 'When total credits <50 then' fail 'When total credits> = 50 and total credits <= 52 then' qualified 'else' excellent (or simple write as follows: when total credits> 52 then 'excellent 'when total credits> = 50 then' qualified 'else' failing': write a large one before writing a small one) end as grade from XS 5) when the select clause is used to query a column, the calculated value of the column value can be output in the result, that is, the select clause can use an expression. As a result. The calculated column values use arithmetic operators: +,-, *,/, and % (remainder. Example 1: select student ID, name, total credits + 5 from xs; (Field total credits + 5 will be displayed in the column title) Example 2: select student ID, course number, score * 1.20 as score 120 from XS_KC 5. the main purpose of using databases and tables is to store data for retrieval, statistics, or organizational output as needed. you can use SQL statement queries to quickly and conveniently retrieve data from tables or views. SQL SELECT statements can be used to SELECT, project, and connect tables. Select statements (the core of SQL) can select specific rows and columns from one or more tables. The result is usually a temporary table (that is, the original table structure is not changed ). During execution, the system selects matching rows and columns from the database based on user standards and places the results in a temporary table. this is a form of selection and projection operations. 6. when the where clause of the row condition is selected, a row and a row in the intermediate result of the from clause are determined based on the condition. when the condition is true, A row is included in the intermediate result of the where clause. The judgment operations include comparison, pattern matching, Range Comparison, null value comparison, and subquery. 1 ). comparison operators are used to compare two expression values. MySQL supports the following comparison operators: = (equal to), <(less than), <= (less than or equal to), and> (greater), >=( greater than or equal to), <=> (equal to or empty), <> (not equal ),! = (Not equal ). The syntax format of the comparison operation is expression {=|<|||>|>|<=>|<>|! =} Expression where expression is a type expression other than TEXT and BLOB. When neither expression value is NULL, other comparison operations except the <=> operator return TRUE or FALSE logical values ); when either of the two expression values is null or both are null, unknown is returned. MySQL has a special equal operator <=>. when two expressions are equal to each other or both are equal to null values, its value is TRUE, either of them is null or both are non-null but are not equal. this condition is FALSE. There is no UNKNOWN. For example, if the remarks in the XS table are empty. SELECT name, student ID, date of birth, total credits from xs where remarks <=> NULL; 2 ). logical Operator: and or not Example 1: female in the computer system recording table uses 0 to indicate select name, major name, gender from xs where major name = 'compute' and gender = 0 Example 2: check the name of a student not born on April 9, 1990; select name fromxs where date of birth <'2017-01-01 'or date of birth> '2017-12-31'; or: select name fromxs where not (date of birth> = '2017-01-01 'and date of birth <= '2017-12-31'); 7. pattern match: fuzzy query match character: % _ %: any number of arbitrary characters; _: any single character like Operator: like operator is used to indicate whether a string matches a specified string, its operation objects can be char, varchar, and t Return the logical values TRUE or FALSE for data of the ext, datetime, and other types. When using LIKE for pattern matching, special symbols _ and % are often used to perform fuzzy queries. "%" Indicates 0 or more characters, and "_" indicates a single character. Escape_character: escape character. escape_character has no default value and must be a single character. When the string to be matched contains the same characters as the special characters (_ and %), the escape character before the character should be used to indicate that it is a matching character in the pattern string. You can use the keyword escape to specify the escape character. Because MySQL is case-insensitive by default, you need to change the collation of character sets when case-insensitive. Example 1: query the student ID, name, and gender of "Wang" in the XS table of XSCJ. SELECT student ID, name, gender from xs where name LIKE 'Wang % '; Example 2: The second to last letter in name is B's name like' % B _ '8. if we want to find one or all of the special symbols (_ and %), we must use an escape character. For example, to query the XS table, the name contains the student ID and name of the offline line. SELECT student ID, name from xs where student id like '% # _ %' ESCAPE '#'; note: After "#" is defined as an ESCAPE character, the "_" after "#" in the statement loses its original special meaning. You can also use any other symbol. 9. regexp operator (modifying the operator is not part of the SQL standard. That is, it may be used in MySQL, but it cannot be used in sever SQL, and the cross-platform performance is not high, so use it with caution) regexp operator is used to execute more complex string comparison operations. Regexp is the abbreviation of a regular expression (regular expression. LIKE the LIKE operator, the regexp operator has multiple functions, but it is not part of the SQL standard. a synonym for the regexp operator is rlike. Syntax format: match_expression [not] [regexp | rlike] the match_expression like operator has two special meanings: "_" and "% ". The regexp operator has more symbols with special meanings. See the following table: special characters special characters beginning of a matched string [abc] matching string abc $ matching the end of a string [a-z] matching in square brackets a ~ One character between z. match any character (including carriage return and New Line) [^ a-z] the matching square brackets do not appear in ~ One character * between z matches any sequence of 0 or more characters before the asterisks | a string that appears on the left or right of the matching symbol + one or more characters before the plus sign any sequence [[...] matching symbols in square brackets (such as spaces, line breaks, parentheses, periods, colons, plus signs, and hyphens )? Match 0 or more characters before the question mark [: <:] and [[: >:]] match the start and end of a word {n} match the content before the brackets appear n times in sequence [[:] match any character in square brackets () match the content in the brackets 10. the where clause must be followed by the from clause. in the where clause, use a condition to select rows from the intermediate results of the from clause. The basic format is where where_definition, where where_definition is the query condition. Syntax format: where_definition: | {And | or} | (Where_definition) | not where_definition where predicate is the judgment operation and the result is true, false, or unknown. : Expression {= | <= | >|>=|<=>|<>|! =} Expression/* comparison operation */| match_expression [NOT] like match_expression [ESCAPE 'escape _ character ']/* like operator */| match_expression [NOT] [regexp | rlike] match_expression/* regexp operator */| expression [not] between expression and expression/* specified range */| expression is [not] null/* null value judgment */| expression [not] in (subquery | expression [,... N])/* in clause */| expression {=|<|<=|>|>=||<>|! ==}{ All | some | any} (subquery)/* comparison subquery */| exist (subquery)/* exist subquery */description: the In keyword can specify a range, it can also represent a subquery. In SQL, operators or keywords that return logical values (TRUE or FALSE) can be called predicates. Author tianyazaiheruanbitsCN.com