MariaDB学习笔记(2)—-检索数据

 edited by 蘑菇@HDUMaker

Overview

MariaDB [test]> SHOW tables;

Tables_in_test authors books series

3 rows in set (0.00 sec)

MariaDB [test]> describe books;

Field Type Null Key Default Extra BookID int(11) NO PRI NULL auto_increment Title varchar(100) NO NULL SeriesID int(11) YES NULL AuthorID int(11) YES NULL

4 rows in set (0.00 sec)

SQL语句说明

在MariaDB和MySQL语句下，单行SQL语句以“；”结尾

SQL语句不区分大小写，但是表名，列名可能有所不同（取决于DBMS及其配置）

在处理SQL语句时，空格都被忽略，将SQL语句分成多行更容易阅读和调试

SELECT语句

【Hint】‘*’是通配符 ，表示选检索books中的全部内容

MariaDB [test]> SELECT * FROM books;

BookID Title SeriesID AuthorID 1 The Fellowship of the Ring 1 1 2 The Two Towers 1 1 3 The Return of the King 1 1 4 The Sum of All Men 2 2 5 Brotherhood of the Wolf 2 2 6 Wizardborn 2 2 7 The Hobbit 0 1 8 Lair of Bones 2 2

检索books中的单列

MariaDB [test]> SELECT SeriesID FROM books;

SeriesID 1 1 1 2 2 2 0 2

8 rows in set (0.00 sec)

使用DISTINCT关键字检索出不同值

MariaDB [test]> SELECT DISTINCT SeriesID FROM books;

SeriesID 1 2 0

3 rows in set (0.00 sec)

检索books中的多列

MariaDB [test]> SELECT BookID ,Title FROM books;

BookID Title 1 The Fellowship of the Ring 2 The Two Towers 3 The Return of the King 4 The Sum of All Men 5 Brotherhood of the Wolf 6 Wizardborn 7 The Hobbit 8 Lair of Bones

8 rows in set (0.00 sec)

检索不同的值

【warning】如果使用DISTINCT关键字，它必须直接放在列名后面。另外不能部分使用DISTINCT，除非制定两列完全相同，否则都会被检索出来

MariaDB [test]> SELECT DISTINCT SeriesID,title FROM books;

SeriesID title 1 The Fellowship of the Ring 1 The Two Towers 1 The Return of the King 2 The Sum of All Men 2 Brotherhood of the Wolf 2 Wizardborn 0 The Hobbit 2 Lair of Bones

8 rows in set (0.00 sec)

限制结果

【waring】在MariaDB和MySQLdb下，TOP关键字并不能用
需要使用LIMIT子句

LIMIT

【注】这里的子句我特意用了小写，果然能用哦

MariaDB [test]> select BookID, title    -> from books    -> limit 5；

BookID title 1 The Fellowship of the Ring 2 The Two Towers 3 The Return of the King 4 The Sum of All Men 5 Brotherhood of the Wolf

5 rows in set (0.00 sec)

OFFSET

OFFSET用于从哪指定开始检索数据
这里是从第2行开始 找满5行
【waring】虽然我们的BookID编码是从1开始的，但是第一个会被检索的是第0行而不是第一行

   MariaDB [test]> SELECT BookID,title     -> FROM books    -> LIMIT 5 OFFSET 2

BookID title 3 The Return of the King 4 The Sum of All Men 5 Brotherhood of the Wolf 6 Wizardborn 7 The Hobbit

5 rows in set (0.00 sec)

MySQL和MariaDB的快捷键

MySQL和MariaDB支持简化版的 LIMIT 4 OFFSET 3语句，即LIMIT 3,4

MariaDB [test]> SELECT BookID,title FROM books    -> LIMIT 3,4；

BookID title 4 The Sum of All Men 5 Brotherhood of the Wolf 6 Wizardborn 7 The Hobbit

4 rows in set (0.00 sec)

如果我们吧OFFSET后面的数据增大 会有什么后果吗(^o^)/~

MariaDB [test]> SELECT BookID,title  FROM books LIMIT 5 OFFSET 5;

BookID title 6 Wizardborn 7 The Hobbit 8 Lair of Bones

3 rows in set (0.00 sec)

然而并没有影响

使用注释

行内注释

MariaDB [test]> #我是一条注释

【warning】MariaDB并不支持这种

MariaDB [test]> SELECT title --我是一条注释    -> FROM books;ERROR 1054 (42S22): Unknown column '我是一条注释' in 'field list'

多行注释

MariaDB [test]> /*Hi   /*> 我是一条注释   /*> 我是一条可爱的注释*/