How to retrieve data from a MySQL database table

Source: Internet
Author: User

In the "Create Database and database table with MySQL" article, how do we create a database and database table and how to add records to the database table. How can we retrieve data from database tables?
1. Retrieve Information from a database table
In fact, we have used the SELECT statement to retrieve information from database tables.
The select statement format is generally:
SELECT search keyword FROM the retrieved table WHERE search condition (optional)
The previously used "*" indicates Selecting All columns. Next we will continue to use the mytable table created in the previous article.
2. query all data:

     
      mysql> select * from mytable; +----------+------+------------+----------+ | name | sex | birth | birthaddr | +----------+------+------------+--------+ | abccs |f | 1977-07-07 | china | | mary |f | 1978-12-12 | usa | | tom |m | 1970-09-02 | usa | +----------+------+------------+----------+ 3 row in set (0.00 sec)
     


3. Corrected error records: 
If the birth date of tom is incorrect, it should be, you can use the update statement to correct it: mysql> update mytable set birth = "1973-09-02" where name = "tom "; use the statement in step 2 to check whether it has been corrected.
4. Select a Specific Row 

We modified the birth date of tom above. We can select tom to see if there have been any changes:

     
      mysql> select * from mytable where name = "tom"; +--------+------+------------+------------+ | name |sex | birth | birthaddr | +--------+------+------------+------------+ | tom |m | 1973-09-02 | usa | +--------+------+------------+------------+ 1 row in set (0.06 sec)
     

The above WHERE parameter specifies the search condition. We can also use combination conditions for queries:
      
       mysql> SELECT * FROM mytable WHERE sex = "f" AND birthaddr = "china"; +--------+------+------------+------------+ | name |sex | birth | birthaddr | +--------+------+------------+------------+ | abccs |f | 1977-07-07 | china | +--------+------+------------+------------+ 1 row in set (0.06 sec)
      

5. Select a specific column
If you want to view the names of all users in the table, you can perform the following operations:
      
       mysql> SELECT name FROM mytable; +----------+ | name | +----------+ | abccs | | mary | | tom | +----------+ 3 row in set (0.00 sec)
      

If you want to list the names and Gender columns, you can use commas to separate the keyword name and birth: myaql> select name, birth from mytable;
6. Sort rows
We can sort the records in the table by birthday size:
      
       mysql> SELECT name, birth FROM mytable ORDER BY birth; +----------+------------+ | name | birth | +----------+------------+ | tom | 1973-09-02 | | abccs | 1977-07-07 | | mary | 1978-12-12 | +----------+------------+ 3 row in set (0.00 sec)
      

We can use DESC for reverse sorting:
      
       mysql> SELECT name, birth FROM mytable ORDER BY birth DESC; +----------+------------+ | name | birth | +----------+------------+ | mary | 1978-12-12 | | abccs | 1977-07-07 | | tom | 1973-09-02 | +----------+------------+ 3 row in set (0.00 sec)
      

7. Row count
The database often needs to collect some data, such as the number of employees in the table, we need to use the row counting function COUNT (). The COUNT () function is used to COUNT records with non-NULL results:
      
       
Mysql> select count (*) FROM mytable; + ---------- + | COUNT (*) | + ---------- + | 3 | + ---------- + 1 row in set (0.06 sec) number of male and female employees: mysql> SELECT sex, COUNT (*) FROM mytable group by sex; + ------ + ---------- + | sex | COUNT (*) | + ------ + ---------- + | f | 2 | m | 1 | + ------ + ---------- + 2 row in set (0.00 sec)
      

Note that we use group by to group sex.

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.