MySQL开发手册(八) 数据表查询

(1)查询指定列

  • 查询通用语法格式
SELECT select_expr [, select_expr ...]
[
     FROM table_references
     [WHERE where_condition]
     [GROUP BY {col_name | position} [ASC | DESC], ...]
     [HAVING where_condition]
     [ORDER BY {col_name | position | expr} [ASC | DESC], ...]
     [LIMIT {[offset,] row_count | row_count OFFSET offset}]
]

* select_expr 查询表达式
    * 每一个表达式表示想要的一列,必须至少有一个,多个列之间以英文逗号分隔
    * 表示所有列 tb1_name.*表示某表的所有列
    * 查询表达式可以使用[AS] alias_name为其赋予别名
    * 别名可用于GROUP BY,ORDER BY或HAVING子句
  • 查询所有列
mysql> create table if not exists book(
    -> id int unsigned primary key auto_increment,
    -> title varchar(100) not null,
    -> author varchar(40) not null,
    -> date date
    -> );
Query OK, 0 rows affected (0.17 sec)

mysql> insert book (title,author,date) values('java','US',NOW());
Query OK, 1 row affected, 1 warning (0.05 sec)

mysql> insert book (title,author,date) values('mysql','Oracle','2018-11-11');
Query OK, 1 row affected (0.03 sec)

mysql> insert book (title,author,date) values('android','google',NOW());
Query OK, 1 row affected, 1 warning (0.07 sec)

mysql> select * from book;
+----+---------+--------+------------+
| id | title   | author | date       |
+----+---------+--------+------------+
|  1 | java    | US     | 2018-11-12 |
|  2 | mysql   | Oracle | 2018-11-11 |
|  3 | android | google | 2018-11-12 |
+----+---------+--------+------------+
3 rows in set (0.01 sec)
  • 查询指定列
mysql> select title,author from book;
+---------+--------+
| title   | author |
+---------+--------+
| java    | US     |
| mysql   | Oracle |
| android | google |
+---------+--------+
3 rows in set (0.00 sec)
  • 别名查询(as可不写)
mysql> select title as book_title,author as book_author from book;
+------------+-------------+
| book_title | book_author |
+------------+-------------+
| java       | US          |
| mysql      | Oracle      |
| android    | google      |
+------------+-------------+
3 rows in set (0.01 sec)

(2)where条件查询


  • and、or指定多个条件
mysql> select * from book where id>1;
+----+---------+--------+------------+
| id | title   | author | date       |
+----+---------+--------+------------+
|  2 | mysql   | Oracle | 2018-11-11 |
|  3 | android | google | 2018-11-12 |
+----+---------+--------+------------+
2 rows in set (0.04 sec)

mysql> select * from book where id>1 and author='google';
+----+---------+--------+------------+
| id | title   | author | date       |
+----+---------+--------+------------+
|  3 | android | google | 2018-11-12 |
+----+---------+--------+------------+
1 row in set (0.03 sec)

mysql> select * from book where id>1 or author='US';
+----+---------+--------+------------+
| id | title   | author | date       |
+----+---------+--------+------------+
|  1 | java    | US     | 2018-11-12 |
|  2 | mysql   | Oracle | 2018-11-11 |
|  3 | android | google | 2018-11-12 |
+----+---------+--------+------------+
3 rows in set (0.00 sec)

(3)like模糊匹配


  • LIKE 子句中使用百分号 %字符来表示任意字符
mysql> select * from book where author like '%le';
+----+---------+--------+------------+
| id | title   | author | date       |
+----+---------+--------+------------+
|  2 | mysql   | Oracle | 2018-11-11 |
|  3 | android | google | 2018-11-12 |
+----+---------+--------+------------+
2 rows in set (0.02 sec)

(4)limit/offset


  • 查询个数和偏移
  • [LIMIT { [offset,] row_count | row_count OFFSET offset}]
mysql> select * from book limit 2;
+----+-------+--------+------------+
| id | title | author | date       |
+----+-------+--------+------------+
|  1 | java  | US     | 2018-11-12 |
|  2 | mysql | Oracle | 2018-11-11 |
+----+-------+--------+------------+
2 rows in set (0.00 sec)

mysql> select * from book limit 2 offset 1;
+----+---------+--------+------------+
| id | title   | author | date       |
+----+---------+--------+------------+
|  2 | mysql   | Oracle | 2018-11-11 |
|  3 | android | google | 2018-11-12 |
+----+---------+--------+------------+
2 rows in set (0.00 sec)

mysql> select * from book limit 1,2;
+----+---------+--------+------------+
| id | title   | author | date       |
+----+---------+--------+------------+
|  2 | mysql   | Oracle | 2018-11-11 |
|  3 | android | google | 2018-11-12 |
+----+---------+--------+------------+
2 rows in set (0.00 sec)

(5)排序


  • [ORDER BY {col_name} [ASC/DESC] ]
  • 默认:升序
  • ASC:升序
  • DESC:降序
mysql> select * from book order by id ASC;
+----+---------+--------+------------+
| id | title   | author | date       |
+----+---------+--------+------------+
|  1 | java    | US     | 2018-11-12 |
|  2 | mysql   | Oracle | 2018-11-11 |
|  3 | android | google | 2018-11-12 |
+----+---------+--------+------------+
3 rows in set (0.02 sec)

mysql> select * from book order by id DESC;
+----+---------+--------+------------+
| id | title   | author | date       |
+----+---------+--------+------------+
|  3 | android | google | 2018-11-12 |
|  2 | mysql   | Oracle | 2018-11-11 |
|  1 | java    | US     | 2018-11-12 |
+----+---------+--------+------------+
3 rows in set (0.00 sec)

mysql> select * from book order by id;
+----+---------+--------+------------+
| id | title   | author | date       |
+----+---------+--------+------------+
|  1 | java    | US     | 2018-11-12 |
|  2 | mysql   | Oracle | 2018-11-11 |
|  3 | android | google | 2018-11-12 |
+----+---------+--------+------------+
3 rows in set (0.00 sec)

mysql> select * from book order by date asc,id desc;
+----+---------+--------+------------+
| id | title   | author | date       |
+----+---------+--------+------------+
|  2 | mysql   | Oracle | 2018-11-11 |
|  3 | android | google | 2018-11-12 |
|  1 | java    | US     | 2018-11-12 |
+----+---------+--------+------------+
3 rows in set (0.02 sec)

(6)分组


  • 查询结果分组—[GROUP BY {col_name | position} [ASC | DESC], ...] (ASC升序、DESC降序)
  • 筛选结果按GROUP之后列排序,相同的只保留第一个,其它的略去
  • 可指定列名分组、也可写1、2、3….指定按第几列分组
mysql> select * from book;
+----+---------+--------+------------+
| id | title   | author | date       |
+----+---------+--------+------------+
|  1 | java    | US     | 2018-11-12 |
|  2 | mysql   | Oracle | 2018-11-11 |
|  3 | android | google | 2018-11-12 |
+----+---------+--------+------------+
3 rows in set (0.16 sec)

mysql> select * from book group by date;
+----+-------+--------+------------+
| id | title | author | date       |
+----+-------+--------+------------+
|  2 | mysql | Oracle | 2018-11-11 |
|  1 | java  | US     | 2018-11-12 |
+----+-------+--------+------------+
2 rows in set (0.03 sec)

mysql> select * from book group by 1;
+----+---------+--------+------------+
| id | title   | author | date       |
+----+---------+--------+------------+
|  1 | java    | US     | 2018-11-12 |
|  2 | mysql   | Oracle | 2018-11-11 |
|  3 | android | google | 2018-11-12 |
+----+---------+--------+------------+
3 rows in set (0.01 sec)

mysql> select * from book group by 4;
+----+-------+--------+------------+
| id | title | author | date       |
+----+-------+--------+------------+
|  2 | mysql | Oracle | 2018-11-11 |
|  1 | java  | US     | 2018-11-12 |
+----+-------+--------+------------+
2 rows in set (0.00 sec)

(7)having


  • HAVING 语句中的字段在选择条件中必须有,此处为date,选择语句中只有id,所以不符合
mysql> select id from book having id > 1;
+----+
| id |
+----+
|  2 |
|  3 |
+----+
2 rows in set (0.06 sec)

mysql> select id from book having date='2018-11-11’;  
//* HAVING 语句中的字段在选择条件中必须有,此处为date,选择语句中只有id,所以不符合
ERROR 1054 (42S22): Unknown column 'date' in 'having clause'

mysql> select id,date from book having date='2018-11-11';
+----+------------+
| id | date       |
+----+------------+
|  2 | 2018-11-11 |
+----+------------+
1 row in set (0.00 sec)

笔记资源下载链接 ,支持PDF、ENEX印象笔记、HTML,方便大家整理导入

© 著作权归作者所有
这个作品真棒,我要支持一下!
主要帮助小白掌握MySQL数据库的增删改查操作,最终提供一个工具类语句手册和完善的学习笔记
0条评论
top Created with Sketch.