Mysql-5 data retrieval (3), mysql-5 Data Retrieval

Source: Internet
Author: User
Tags rtrim

Mysql-5 data retrieval (3), mysql-5 Data Retrieval
Calculated Field

  • If you want to display both the company name and the company address in a field, the two information is generally included in different columns
  • The city, state, and zip codes are stored in different columns, but the mail label print program needs to retrieve them as an appropriate format Field
  • 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 price of the item is required.
  • Calculate the total number, average number, or other values based on the table data.
Spliced Field

Example: The vendors table contains the Supplier name and location information. If you want to generate a supplier report, you need to list supplier information in the Supplier name in the format of name (location. This report requires a single value, and the data in the table is stored in the vend_name and vend_country columns. In addition, You Need To enclose vend_country in parentheses. None of these items are explicitly stored in the data table, let's take a look at how to write a select statement that returns the Supplier name and location.

SELECT CONCAT(vend_name, ' (',vend_country,')') FROM vendors ORDER BY vend_name;

The concat function is used to concatenate multiple strings to form a long string. concat requires one or more specified strings, separated by commas (,).

SELECT CONCAT(RTRIM(vend_name),' (',RTRIM(vend_country),')') FROM vendors ORDER BY vend_name;

The rtrim function removes all spaces on the right of the value. By using rtrim (), all columns are sorted.

Use alias
SELECT CONCAT(RTRIM(vend_id),' (',RTRIM(vend_country),')') AS vend_title FROM vendors ORDER BY vend_name;

Execute arithmetic calculation
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;

Use 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 on the Left of the string.

Length () returns the Length of the string.

Locate a substring of a string

Lower () converts string to lowercase

LTrim () removes spaces on the left of the string

Right () returns the character on the Right of the string.

RTrim () removes spaces on the right of the string

Soundex () returns the soundex value of the string

SubString () returns the character of the SubString.

Upper () converts string to uppercase

 

SELECT cust_name , cust_contact FROM customers WHERE SOUNDEX(cust_contact)=SOUNDEX('Y lie');

Query the columns of cust_contact with similar pronunciation to lie.

Date and time processing functions

 

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)= 2005 AND MONTH(order_date)= 9;

All orders for March

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)= 2005 AND MONTH(order_date)= 9;

 

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.