MYSQL-5 Data Retrieval (3)

Calculated fields

• If you want to display both the company's name and the company's address in a field, these two messages are typically included in different table columns
• City, state, and zip codes are stored in different columns, but the mailing label print program needs to retrieve them as a field in the appropriate format
• The Item Order table stores the price and quantity of the item, but does not need to store the total price of each item, in order to print the invoice, the total cost of the item
• Total, average, or other calculations that need to be based on table data

Stitching Fields

Example: The Vendors table contains the vendor name and location information. If you want to generate a vendor report, you need to list the vendor information in the name of the vendor, in a format such as name. This report requires a single value, and the data in the table is stored in two columns Vend_name and Vend_country, and in addition, parentheses are used to enclose the vend_country, which are not explicitly stored in the data table. Let's see how to write a SELECT statement that returns the vendor name and location

(', Vend_country,') ') From vendors ORDER by Vend_name;

Here used the concat function, splicing strings, that is, to connect multiple strings together to form a longer string, concat need one or more specified strings, each string separated by commas

SELECT CONCAT (RTRIM (vend_name),(', RTRIM (vend_country),')' ) from vendors ORDER by Vend_name;

The RTrim function removes all the spaces to the right of the value, and the columns are sorted by using RTrim ().

Using aliases

SELECT CONCAT (RTRIM (vend_id),'  (', RTRIM (vend_country),')' as Vend_title from vendors ORDER by Vend_name;

Perform arithmetic calculations

20005;

SELECT prod_id, Quantity, Item_price, quantity*item_price as Expanded_price  20005;

Using Data processing functions

Upper () function

SELECT Vend_name, UPPER (vend_name) as vend_name_upcase from vendors ORDER by Vend_name;

Function description

Left () returns the character of the string

Length () returns the lengths of the strings

Locate () find a substring of a string

Lower () Converts a string to lowercase

LTrim () Remove the left space of the string

Right () returns the character to the left of the string

RTrim () Remove the space to the right of the string

Soundex () returns the Soundex value of the string

SubString () returns the character of a substring

Upper () Convert string to uppercase

SELECT Cust_name, cust_contact from Customers WHERE SOUNDEX (cust_contact) =soundex ('Y lie') ;

Find columns with cust_contact similar to lie pronunciation

Date and time processing functions

' 2005-09-01 ';

' 2005-09-01 ';

' 2005-09-01 ' ' 2005-09-30 ';

2005 9;

All orders for September

SELECT CONCAT (Vend_name,' (', Vend_country,')') from vendors ORDER by Vend_name; SELECT CONCAT (RTRIM (vend_name),' (', RTRIM (Vend_country),')') from vendors ORDER by Vend_name; SELECT CONCAT (RTRIM (vend_id),' (', RTRIM (Vend_country),')'As vend_title from vendors ORDER by Vend_name; SELECT prod_id, Quantity, item_price from OrderItems WHERE order_num=20005; SELECT prod_id, quantity, Item_price, quantity*item_price as Expanded_price from orderitems WHERE order_num =20005; SELECT Vend_name, UPPER (vend_name) as vend_name_upcase from vendors ORDER by Vend_name; SELECT Cust_name, cust_contact from Customers WHERE SOUNDEX (cust_contact)=soundex ('Y Lie'); SELECT Cust_name, cust_contact from customers; SELECT cust_id, order_num from orders WHERE order_date='2005-09-01'; SELECT cust_id, order_num from Orders WHERE DATE (order_date)='2005-09-01'; SELECT cust_id, Order_num, order_date from Orders WHERE date (order_date) between'2005-09-01'and'2005-09-30'; SELECT cust_id, order_num,order_date from Orders WHERE year (order_date)=2005and MONTH (order_date) =9;

MYSQL-5 Data Retrieval (3)