Mysql的查詢語句的使用

1. 簡單查詢

查詢所有字段: SELECT * FROM 表名
查詢特定字段: SELECT 字段列表 FROM 表名

2. 查詢顯示行號

- 在字段列表中加入(@rownum := @rownum + 1) AS rownum
- 在FROM子句中加入 (select @rownum:=0)r
- 示例如下:

select (@rownum := @rownum + 1) AS rownum,book_name from (select @rownum:=0)r, book;
查詢結(jié)果:
+--------+--------------------+
| rownum | book_name |
+--------+--------------------+
| 1 | 數(shù)據(jù)庫系統(tǒng)設(shè)計(jì) |
| 2 | 從實(shí)模式到保護(hù)模式 |
+--------+--------------------+

3. 在查詢語句中可以有算術(shù)表達(dá)式，它將形成一個(gè)新列，用于顯示計(jì)算的結(jié)果，通常稱為計(jì)算列。

實(shí)例:select book_price/2 AS new_book_price,author_id from book;

4. 在SELECT子句中使用AS關(guān)鍵字給某個(gè)字段起別名

- 基本使用

mysql> select book_price AS New_book_price,author_id as my_idea from book;
+----------------+---------+
| New_book_price | my_idea |
+----------------+---------+
| 214            | 1 |
| 34.5           | 2 |
+----------------+---------+
2 rows in set (0.07 sec)

- 別名中如果出現(xiàn)空格或者()之類的敏感字符,需要使用雙引號將別名引起

mysql> select book_name as "the new book name" from book;
+--------------------+
| the new book name |
+--------------------+
| 數(shù)據(jù)庫系統(tǒng)設(shè)計(jì) |
| 從實(shí)模式到保護(hù)模式 |
+--------------------+
2 rows in set (0.06 sec)

mysql> select book_name as the new book name from book;
1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'new book name from book' at line 1

5. 使用DISTINCT消除重復(fù)行顯示

mysql> select volume FROM book;
+--------+
| volume |
+--------+
| 45 |
| 45 |
+--------+
2 rows in set (0.07 sec)

mysql> select DISTINCT volume FROM book;
+--------+
| volume |
+--------+
| 45 |
+--------+
1 row in set (0.06 sec)

6. 查詢結(jié)果的排序,order by 字段 (asc,desc),asc表示升序,desc表示降序,如果不指明的話,就會默認(rèn)使用升序

mysql> SELECT * FROM author ORDER BY id;
+----+--------------+---------+
| id | name | address |
+----+--------------+---------+
| 1 | chenchen | |
| 2 | chenqingyang | |
| 5 | 666 | 廣州市 |
| 14 | gafv | gfgd |
+----+--------------+---------+
4 rows in set (0.05 sec)

mysql> select * from author order by id desc;
+----+--------------+---------+
| id | name | address |
+----+--------------+---------+
| 14 | gafv | gfgd |
| 5 | 666 | 廣州市 |
| 2 | chenqingyang | |
| 1 | chenchen | |
+----+--------------+---------+
4 rows in set (0.05 sec)

7. 需要注意的是,在order by子句中,如果該字段是字符類型的話,那將使用字符表的順序來進(jìn)行比較

mysql> select * from author order by name ;
+----+--------------+---------+
| id | name | address |
+----+--------------+---------+
| 1 | chenchen | |
| 2 | chenqingyang | |
| 14 | gafv | gfgd |
+----+--------------+---------+
3 rows in set (0.05 sec)

8. 索引的創(chuàng)建與刪除

- 創(chuàng)建: CREATE INDEX 索引名 on table_name(字段名)
- 刪除: DROP INDEX 索引名 on table_name

創(chuàng)建索引示例:
mysql> create index name_index on author(name);
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0

刪除代碼:
mysql> DROP index name_index on author;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0

> 本文由博客一文多發(fā)平臺 [OpenWrite](https://openwrite.cn?from=article_bottom) 發(fā)布！

posted @ 2019-12-12 13:35 菜鳥額閱讀(393) 評論(0) 收藏舉報(bào)

刷新頁面返回頂部

菜鳥額

Mysql的查詢語句的使用

公告