说到MySQL中的查询语句,就不得不说说SQL语句的分类,一共分为三大类或者四大类。
三大类主要为:
DDL(数据库定义语言,不可被ROLLBACK回滚) | CREATE \ ALTER \ DROP |
DML(数据库操作语言,可以被ROLLBACK回滚) | INSERT \ DELETE \ UPDATE \ SELECT |
DCL(数据库控制语言) | COMMIT \ ROLLBACK |
四大类则是将DML中的SELECT查询单独列为DQL(数据库查询语言)
最基本的SELECT语句:
1. SELECT 字段1 , 字段2 , .... , FROM 表名;
mysql> SELECT bid , bname , pubDate FROM book;
+---------+---------+---------------------+
| bid | bname | pubDate |
+---------+---------+---------------------+
| ISBN001 | java | 2002-02-09 00:00:00 |
| ISBN002 | .net | 2003-03-09 00:00:00 |
| ISBN003 | java | 2002-02-09 00:00:00 |
| ISBN004 | MySQL | 2003-03-09 00:00:00 |
| ISBN005 | jav | 2002-02-09 00:00:00 |
| ISBN006 | 42.net | 2003-03-09 00:00:00 |
| ISBN007 | java1.8 | 2002-02-09 00:00:00 |
| ISBN003 | java | 2002-02-09 00:00:00 |
| ISBN004 | MySQL | 2003-03-09 00:00:00 |
| ISBN005 | jav | 2002-02-09 00:00:00 |
| ISBN006 | 42.net | 2003-03-09 00:00:00 |
| ISBN007 | java1.8 | 2002-02-09 00:00:00 |
| ISBN008 | .net45 | 2003-03-09 00:00:00 |
+---------+---------+---------------------+
13 rows in set (0.00 sec)
2. 查询表中所有字段(或列):
SELECT * FROM 表名 ;
mysql> SELECT * FROM book;
+---------+---------+-----------------+-------------+---------------------+--------+-------+
| bid | bName | author | pubComp | pubDate | bCount | price |
+---------+---------+-----------------+-------------+---------------------+--------+-------+
| ISBN001 | java | cay s.horstmann | jixiegongye | 2002-02-09 00:00:00 | 100 | 200 |
| ISBN002 | .net | cay s.horstmann | jixiegongye | 2003-03-09 00:00:00 | 200 | 240 |
| ISBN003 | java | cay s.horstmann | jixiegongye | 2002-02-09 00:00:00 | 100 | 200 |
| ISBN004 | MySQL | cay s.horstmann | jixiegongye | 2003-03-09 00:00:00 | 210 | 245 |
| ISBN005 | jav | cay s.horstmann | jixiegongye | 2002-02-09 00:00:00 | 100 | 200 |
| ISBN006 | 42.net | cay s.horstmann | jixiegongye | 2003-03-09 00:00:00 | 200 | 240 |
| ISBN007 | java1.8 | cay s.horstmann | jixiegongye | 2002-02-09 00:00:00 | 100 | 200 |
| ISBN003 | java | cay s.horstmann | jixiegongye | 2002-02-09 00:00:00 | 100 | 200 |
| ISBN004 | MySQL | cay s.horstmann | jixiegongye | 2003-03-09 00:00:00 | 210 | 245 |
| ISBN005 | jav | cay s.horstmann | jixiegongye | 2002-02-09 00:00:00 | 100 | 200 |
| ISBN006 | 42.net | cay s.horstmann | jixiegongye | 2003-03-09 00:00:00 | 200 | 240 |
| ISBN007 | java1.8 | cay s.horstmann | jixiegongye | 2002-02-09 00:00:00 | 100 | 200 |
| ISBN008 | .net45 | cay s.horstmann | jixiegongye | 2003-03-09 00:00:00 | 200 | 240 |
+---------+---------+-----------------+-------------+---------------------+--------+-------+
13 rows in set (0.00 sec)
3. 列(或字段)的别名
SELECT 列原名1 列别名1 , 列原名2 列别名2 , ...... FROM 表名;
mysql> SELECT bid 编号 , bName 姓名 FROM book;
+---------+---------+
| 编号 | 姓名 |
+---------+---------+
| ISBN001 | java |
| ISBN002 | .net |
| ISBN003 | java |
| ISBN004 | MySQL |
| ISBN005 | jav |
| ISBN006 | 42.net |
| ISBN007 | java1.8 |
| ISBN003 | java |
| ISBN004 | MySQL |
| ISBN005 | jav |
| ISBN006 | 42.net |
| ISBN007 | java1.8 |
| ISBN008 | .net45 |
+---------+---------+
13 rows in set (0.00 sec)
4. 去除重复行
SELECT DISTINCT 字段1 FROM 表名 (即去除字段1中的重复数据);
mysql> SELECT DISTINCT bid 编号 , bName 姓名 FROM book;
+---------+---------+
| 编号 | 姓名 |
+---------+---------+
| ISBN001 | java |
| ISBN002 | .net |
| ISBN003 | java |
| ISBN004 | MySQL |
| ISBN005 | jav |
| ISBN006 | 42.net |
| ISBN007 | java1.8 |
| ISBN008 | .net45 |
+---------+---------+
8 rows in set (0.03 sec)
5. 查询时添加常数或固定内容
SELECT '内容1' , '内容2' , 字段1 , .... FROM 表名;
mysql> SELECT '出版社出版','发行中',bid,bName FROM book;
+-----------------+-----------+---------+---------+
| 出版社出版 | 发行中 | bid | bName |
+-----------------+-----------+---------+---------+
| 出版社出版 | 发行中 | ISBN001 | java |
| 出版社出版 | 发行中 | ISBN002 | .net |
| 出版社出版 | 发行中 | ISBN003 | java |
| 出版社出版 | 发行中 | ISBN004 | MySQL |
| 出版社出版 | 发行中 | ISBN005 | jav |
| 出版社出版 | 发行中 | ISBN006 | 42.net |
| 出版社出版 | 发行中 | ISBN007 | java1.8 |
| 出版社出版 | 发行中 | ISBN003 | java |
| 出版社出版 | 发行中 | ISBN004 | MySQL |
| 出版社出版 | 发行中 | ISBN005 | jav |
| 出版社出版 | 发行中 | ISBN006 | 42.net |
| 出版社出版 | 发行中 | ISBN007 | java1.8 |
| 出版社出版 | 发行中 | ISBN008 | .net45 |
+-----------------+-----------+---------+---------+
13 rows in set (0.00 sec)
6. 显示表结构: DESC 表名;
mysql> DESC book;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| bid | varchar(50) | YES | | NULL | |
| bName | varchar(50) | YES | | NULL | |
| author | varchar(20) | YES | | NULL | |
| pubComp | varchar(20) | YES | | NULL | |
| pubDate | datetime | YES | | NULL | |
| bCount | int | YES | | NULL | |
| price | float | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+
7 rows in set (0.02 sec)
7.过滤信息
SELECT 字段 FROM 表 WHERE 要过滤的信息;
mysql> SELECT DISTINCT * FROM book WHERE bid = 'ISBN001' ;
+---------+-------+-----------------+-------------+---------------------+--------+-------+
| bid | bName | author | pubComp | pubDate | bCount | price |
+---------+-------+-----------------+-------------+---------------------+--------+-------+
| ISBN001 | java | cay s.horstmann | jixiegongye | 2002-02-09 00:00:00 | 100 | 200 |
+---------+-------+-----------------+-------------+---------------------+--------+-------+
1 row in set (0.00 sec)