MySQL Crash Course #02# Chapter 3. 4 通配符. 分页

索引

• 查看表.文档操作
• 检索必须知道的两件事
• 数据演示由谁负责
• 通配符.非必要不用
• 检索不同的行
• 限制结果集.分页查找
• 运用数据库.表全名
• 命令后加分号对于很多 DBMS 都不是必要的，但是加了也没有坏处

 

Learning About Databases and Tables 

Learning More About SHOW In the mysql command-line utility, execute command HELP SHOW; to display a list of allowed SHOW statements.

查看表结构.创建表信息

DESC table_name;
SHOW CREATE TABLE table_name;

 

The SELECT Statement

To use SELECT to retrieve table data you must, at a minimum, specify two pieces of information what you want to select, and from where you want to select it.

Presentation of Data

SQL statements typically return raw, unformatted data. Data formatting is a presentation issue, not a retrieval issue. Therefore, presentation (for example, alignment and displaying the price values as currency amounts with the currency symbol and commas) is typically specified in the application that displays the data. Actual raw retrieved data (without application-provided formatting) is rarely displayed as is.

 

Using Wildcards 

As a rule, you are better off not using the * wildcard unless you really do need every column in the table. Even though use of wildcards might save you the time and effort needed to list the desired columns explicitly, retrieving unnecessary columns usually slows down the performance of your retrieval and your application.

 

Retrieving Distinct Rows

在表中，可能会包含重复值。这并不成问题，不过，有时您也许希望仅仅列出不同（distinct）的值。

SELECT DISTINCT vend_id tells MySQL to only return distinct (unique) vend_id rows, and so only 4 rows are returned, as seen in the following output. If used, the DISTINCT keyword must be placed directly in front of the column names.

select distinct name, id from A

 更多详细内容可以参阅 MySQL中distinct的使用方法

 

Limiting Results

mysql> SELECT manga_id, manga_name
    -> FROM manga
    -> LIMIT 3;
+----------+-----------------------+
| manga_id | manga_name            |
+----------+-----------------------+
|     1000 | 至不死的你            |
|     1001 | 烙印勇士              |
|     1002 | 幸福（happiness）     |
+----------+-----------------------+

mysql> SELECT manga_id, manga_name
    -> FROM manga
    -> LIMIT 3,3;
+----------+-----------------+
| manga_id | manga_name      |
+----------+-----------------+
|     1003 | 东京食尸鬼      |
|     1004 | dasda           |
|     1005 | 2asdasds        |
+----------+-----------------+

返回的行是第 4， 5 ，6 行。

When There Aren't Enough Rows The number of rows to retrieve specified in LIMIT is the maximum number to retrieve. If there aren't enough rows (for example, you specified LIMIT 10,5, but there were only 13 rows), MySQL returns as many as it can.

 

Using Fully Qualified Table Names

mysql> SELECT manga.manga_id
    -> FROM mangast.manga;
+----------+
| manga_id |
+----------+
|     1000 |
|     1001 |

在某些情况下应用全名是必要的。

 

转载于:https://www.cnblogs.com/xkxf/p/8653183.html