- 首页 > it技术 > >
- 简单查询
查询所有字段: SELECT * FROM 表名
查询特定字段: SELECT 字段列表FROM 表名
- 查询显示行号
- 在字段列表中加入(@rownum := @rownum + 1) AS rownum
- 在FROM子句中加入(select @rownum:=0)r
- 示例如下:
select (@rownum := @rownum + 1) AS rownum,book_name from(select @rownum:=0)r, book;
查询结果:
+--------+--------------------+
| rownum | book_name|
+--------+--------------------+
|1 | 数据库系统设计|
|2 | 从实模式到保护模式 |
+--------+--------------------+
- 在查询语句中可以有算术表达式，它将形成一个新列，用于显示计算的结果，通常称为计算列。
实例:select book_price/2 AS new_book_price,author_id from book;
- 在SELECT子句中使用AS关键字给某个字段起别名
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)- 别名中如果出现空格或者()之类的敏感字符,需要使用双引号将别名引起mysql> select book_name as "the new book name" from book;
+--------------------+
| the new book name|
+--------------------+
| 数据库系统设计|
| 从实模式到保护模式 |
+--------------------+
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
- 使用DISTINCT消除重复行显示
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)
- 查询结果的排序,order by 字段 (asc,desc),asc表示升序,desc表示降序,如果不指明的话,就会默认使用升序
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)
- 需要注意的是,在order by子句中,如果该字段是字符类型的话,那将使用字符表的顺序来进行比较
mysql> select * from author order by name ;
+----+--------------+---------+
| id | name| address |
+----+--------------+---------+
|1 | chenchen||
|2 | chenqingyang ||
| 14 | gafv| gfgd|
+----+--------------+---------+
3 rows in set (0.05 sec)
- 索引的创建与删除
- 创建: CREATE INDEX 索引名 on table_name(字段名)
- 删除: DROP INDEX 索引名 on table_name
创建代码:
mysql> create index name_index on author(name);
Query OK, 0 rows affected (0.05 sec)
Records: 0Duplicates: 0Warnings: 0删除代码:
mysql> DROP index name_index on author;
Query OK, 0 rows affected (0.02 sec)
Records: 0Duplicates: 0Warnings: 0
【Mysql的查询语句的使用】本文由博客一文多发平台 OpenWrite 发布！
推荐阅读
