sql-Basic Learning 3--wildcard character: like,%, (_); Splicing: +,| |,concat;

lesson sixth filtering with wildcard characters 6.1 Like operatorThe wildcard itself is actually a character that has special meanings in the WHERE clause of SQL, and SQL supports several wildcard characters. To use wildcard characters in a search clause, you must use the LIKE operator. The like indicates the DBMS, followed by a search pattern that uses wildcard matching instead of a simple equality match for comparison. Wildcard searches can only be used with text fields (strings), non-Text data type fields cannot be searched with wildcards. 6.1.100 semicolon (%) wildcard characterThe most commonly used wildcard character is the percent sign (%). In the search string,% indicates any number of occurrences of any character. For example, to find all products that start with the word fish, you can publish the following SELECT statement:

Enter SELECT prod_id, Prod_namefrom productswhere prod_name like ' fish% '; Output prod_id     prod_name-------     ------------------   BNBG01      Fish bean bag Toy

Analysis of this example uses the search pattern ' fish% '. When the sentence is executed, any word that begins with fish will be retrieved. % tells the DBMS to accept any character after fish, regardless of how many characters it has. Note: Wildcards can be used anywhere in search mode, and multiple wildcard characters can be used. Special Note: In addition to matching one or more characters,% can also match 0 characters. % represents 0, one, or more characters at a given position in the search pattern. Note: Be aware that many of the DBMS followed by spaces, including access, use spaces to fill in the contents of the field. For example, if a column has 50 characters and the stored text is Fish bean bag toy (17 characters), filling the column requires 33 spaces appended to the text. This generally has no effect on the data and its use, but may have a negative impact on the SQL statements described above. The clause where prod_name like ' f%y ' matches only prod_name that begins with F and ends with Y. If the value is followed by a space, it does not end with Y, so fish bean bag toy is not retrieved. The simple solution is to add a% number to the search pattern: ' f%y% ' also matches the character (or space) after Y. A better solution is to use a function to remove spaces. Please refer to lesson 8th. Warning: Note that the null wildcard% looks like it can match anything, but with an exception, this is null. The clause where prod_name like '% ' does not match the line where the product name is NULL. 6.1.2 Underscore (_) wildcard characterAnother useful wildcard character is an underscore (_). The underscore is used in the same way as%, but it matches only a single character, not more than one character. Description: The wildcard character _ is not supported by the DB2 wildcard DB2. Description: Access wildcard If you are using Microsoft Access, you need to use the? Instead of _. 6.1.3 square brackets ([]) wildcard charactersThe square brackets ([]) wildcard is used to specify a character set, which must match one character of the specified position (the position of the wildcard character). However, MySQL does not support the square brackets wildcard, so you can use the and, or logical operators, for example: Find all names with J or M beginning of the contact, can make the following query:

Enter SELECT cust_contactfrom customerswhere cust_contact like ' j% ' OR cust_contact like ' M% ' ORDER by Cust_contact; Output cust_contact-----------------Jim Jonesjohn Smithmichelle Green

Note: When using logical operators such as and, or, you should follow the complete conditional statement, not with the preceding shared column name, or any other character, as in the where statement above: where cust_contact like ' j% ' or ' M% ' or where cust_cont The act like ' j% ' OR the like ' M% ' are all wrong;   6.2 Tips for using wildcard charactersThere is a cost to using wildcards, which means that wildcard searches typically take longer to process than other searches discussed earlier. Here are some tips to keep in mind when using wildcard characters:

• Do not use wildcard characters excessively. If other operators can achieve the same purpose, other operators should be used.
• When you do need to use wildcards, try not to use them at the beginning of the search pattern. The wildcard character is placed at the beginning and the search is the slowest.
• Pay close attention to the position of the wildcard character. If misplaced, you may not return the data you want.

  seventh lesson creating calculated fields 7.1 Calculated FieldsThe data stored in a database table is generally not the format required by the application, and here are a few examples.

• The company name needs to be displayed and the company's address is displayed, but the two information is stored in a different table column.
• City, state, and zip codes are stored in separate columns (this should be the case), but the mailing label printing program needs to retrieve them as a field with the appropriate format.
• The column data is mixed in case, but the report program needs to have all the data in uppercase.
• The Item Order table stores the price and quantity of the item and does not store the total price of each item (multiplied by the price). But for the printed invoice, the total price of the item is required.
• Calculations such as totals and averages are required based on the table data.

In each of these examples, the data stored in the table is not required by the application. Instead of retrieving data and reformatting it in the client application, we need to retrieve the converted, computed, or formatted data directly from the database. That's where the computational field comes in handy. Unlike the columns introduced in the previous lessons, calculated fields do not actually exist in the database tables. Calculated fields are created at run time within a SELECT statement. Fields (field) are basically the same meaning as columns (column) and are often used interchangeably, although database columns are generally called columns, and term fields are usually used with calculated fields. Special Note: Only the database knows which columns in the SELECT statement are actual table columns, and which columns are calculated fields. From a client (such as an application), the data for the calculated field is returned in the same way as the data for the other columns.   7.2 Stitching FieldsIn the SELECT statement in SQL, you can use a special operator to stitch together two columns. Depending on the DBMS you are using, this operator can be used with a plus (+) or two vertical bar (| | Said In MySQL and MARIADB, special functions must be used. Description: Yes + or | |? Access and SQL Server use the + sign. DB2, Oracle, PostgreSQL, SQLite, and open Office base use | |. For details, refer to the specific DBMS documentation. Here is an example of using the plus sign (most DBMS uses this syntax):

Enter SELECT Vend_name + ' (' + vend_country + ') ' from Vendorsorder by vend_name; output---------------------------------------- -------------------Bear Emporium                                (USA        ) Bears R Us                                   (USA        ) Doll House Inc.                              (USA        ) Fun and Games                                (England    ) Furball Inc.                                 (USA        ) Jouets et ours                               (France     )

Analyze the above two SELECT statements to stitch the following elements: the names stored in the Vend_name column, the country containing a space and a left parenthesis in the Vend_country column; A string containing a closing parenthesis. As you can see from the above output, the SELECT statement returns a column (calculated field) that contains the four elements above. Then look at the output returned by the SELECT statement above. Two columns combined into one calculated field are filled with spaces. Many databases (not all) hold text values that are populated with column widths, but you actually don't need them for the results you want. To correctly return formatted data, these spaces must be removed. This can be done using the RTrim () function of SQL, as follows:

Enter SELECT RTRIM (vend_name) + ' (' + RTRIM (vend_country) + ') ' from Vendorsorder by vend_name; output-------------------------- ---------------------------------Bear Emporium (USA) bears R Us (USA) Doll House Inc. (USA) fun and Games (England) Furball in C. (USA) Jouets et ours (France)

Analyze the RTRIM () function to remove all the spaces to the right of the value. Each column is organized by using RTrim (). Using aliases from the previous output, you can see that the SELECT statement is a good way to stitch the address fields. But what is the name of this new computed column? Actually it doesn't have a name, it's just a value. If you look at the results only in the SQL query tool, there's nothing wrong with that. However, an unnamed column cannot be used in a client app because the client has no way to reference it. To resolve this issue, SQL supports column aliases. An alias is an alternate name for a field or value. Aliases are given with the AS keyword. Take a look at the following SELECT statement:

Enter SELECT RTRIM (vend_name) + ' (' + RTRIM (vend_country) + ') ' as       vend_titlefrom vendorsorder by vend_name; output vend_titl E-----------------------------------------------------------Bear Emporium (USA) bears R Us (USA) Doll House Inc. (USA) Fun and Games (England) Furball Inc. (USA) Jouets et ours (France)

Here are the statements used in MySQL and mariadb:

Enter SELECT Concat (vend_name, ' (', Vend_country, ') ') as       vend_titlefrom Vendorsorder by Vend_name;

7.3 Performing arithmetic calculationsThe Orders table contains all orders received, and the OrderItems table contains the items in each order. The following SQL statement retrieves all the items in order number 20008:

Input SELECT prod_id, quantity, item_pricefrom orderitemswhere order_num = 20008; output prod_id quantity item_        Price----------     -----------     ---------------------RGAN01         5               4.9900br03              5               11.9900bnbg01              3.4900bnbg02 Ten              3.4900bnbg03              3.4900

The Item_price column contains the unit price for each item in the order. Price of items summarized below (unit price multiplied by order quantity):

Input SELECT prod_id,       quantity,       item_price,       quantity*item_price as Expanded_pricefrom orderitemswhere Order_num = 20008, output prod_id        quantity        item_price       expanded_price----------     -----------     ------ ------     -----------------RGAN01         5               4.9900           24.9500br03              5               11.9900          59.9500bnbg01         Ten              3.4900           34.9000bnbg02              3.4900           34.9000bnbg03              3.4900           34.9000

The Expanded_price column shown in the analysis output is a calculated field, which is calculated as Quantity*item_price. The client app can now use this new computed column, just as with other columns.

sql-Basic Learning 3--wildcard character: like,%, (_); Splicing: +,| |,concat;