数据库mysql单表查询语句_mysql数据库常用的查询语句(单表查询)

曾经终败给现在 2023-01-11 01:23 152阅读 0赞

1   使用select语句查询一个数据表

select\* from 数据表名;

2  查询表中的一个字段或多个字段

select 字段1,字段2 from 数据表;

3  查询表中指定的数据信息

select\* from数据表名order by id desc limit 2,1;  //查询的数据按id倒序排序

4  查询指定路径

Select\* from 数据表 where 属性 = ‘…’;

5  带IN 关键字的查询

Select\* from 数据表 where 字段 \[NOT\] IN (字段值1，字段值2…字段值n);

例如：

mysql>select \* from library where price in (99,50);

\+----+--------------+--------+-------+

\+----+--------------+--------+-------+

|  1 | java范例大全 | 张帆  | 99    |

|  2 | mySQL        | 潘凯华 | 50    |

\+----+--------------+--------+-------+

mysql>select \* from library where price NOT IN(99,50);

\+----+---------------+--------+-------+

\+----+---------------+--------+-------+

|  3 | SQLserver2005 | 刘智勇 | 80    |

\+----+---------------+--------+-------+

6 带BETWEEN and 的范围查询

select \* from 数据表 where 字段 \[NOT\] BETWEEN 取值1 and 取值2；

例如：

mysql> select \* from country where idBETWEEN 2 and 4;

\+----+----------+------------+------+----------+

\+----+----------+------------+------+----------+

|  2 | American | 489        | 60  | English  |

|  3 | Japan    | 89        | 30   | Jpanese  |

|  4 | England  | 2         | 300  | English  |

\+----+----------+------------+------+----------+

3 rows in set(0.09 sec)

mysql> select\* from country where id NOT BETWEEN 2 and 4;

\+----+-------+------------+------+----------+

\+----+-------+------------+------+----------+

|  1 | china | 13         | 960 | chinese  |

\+----+-------+------------+------+----------+

7  带like的字符匹配查询

(1)   select \* from 数据表 where 属性 like '%SQL%'；//查询属性中包含SQL字符的数据

(2)   select \* from 数据表 where 属性 like 'a%b';// //查询属性中以a开头以b结尾的字符串的数据

(3)   select \* from 数据表 where 属性 like 'm\_n';//查询属性中以m开头以n结尾的3个字符的数据，中间的‘\_’只能代表一个字符

例如：

mysql> select \* from library where name like '%SQL%';

\+----+---------------+--------+-------+

\+----+---------------+--------+-------+

|  2 | mySQL         | 潘凯华 | 50    |

|  3 | SQLserver2005 | 刘智勇 | 80    |

\+----+---------------+--------+-------+

mysql> select \* from library where name like 'myS\_L';

\+----+-------+--------+-------+

\+----+-------+--------+-------+

|  2 | mySQL | 潘凯华 | 50    |

\+----+-------+--------+-------+

8  带AND的多条件查询

mysql> select \* from library whereprice=50 and name like 'myS\_L';

\+----+-------+--------+-------+

\+----+-------+--------+-------+

| 2 | mySQL | 潘凯华 | 50    |

\+----+-------+--------+-------+

9  带OR的多条件查询

mysql> select \* from library where price=50or price=99;

\+----+--------------+--------+-------+

\+----+--------------+--------+-------+

| 1 | java范例大全 | 张帆  | 99    |

| 2 | mySQL        | 潘凯华 | 50    |

\+----+--------------+--------+-------

10 用DISTINCT关键字去除结果中的重复行

例如：

原表：mysql> select \* from library;

\+----+---------------+--------+-------+

\+----+---------------+--------+-------+

| 1 | java范例大全  | 张帆   | 99    |

| 2 | mySQL         | 潘凯华 | 50    |

| 3 | SQLserver2005 | 刘智勇 | 80    |

| 4 | mySQL         | 李慧  | 50    |

\+----+---------------+--------+-------+

mysql> select distinct name fromlibrary;

\+---------------+

| name          |

\+---------------+

| java范例大全 |

| mySQL         |

| SQLserver2005 |

\+---------------+

11 用ORDER BY 关键字对查询结果排序

mysql> select \* from library orderby id desc;//倒序排列

\+----+---------------+--------+-------+

\+----+---------------+--------+-------+

| 4 | mySQL         | 李慧  | 50    |

| 3 | SQLserver2005 | 刘智勇 | 80    |

| 2 | mySQL         | 潘凯华 | 50    |

| 1 | java范例大全  | 张帆   | 99    |

\+----+---------------+--------+-------+

mysql> select \* from library orderby id asc;//正序排列

\+----+---------------+--------+-------+

\+----+---------------+--------+-------+

| 1 | java范例大全  | 张帆   | 99    |

| 2 | mySQL         | 潘凯华 | 50    |

| 3 | SQLserver2005 | 刘智勇 | 80    |

| 4 | mySQL         | 李慧  | 50    |

\+----+---------------+--------+-------+

12  用GROUP BY关键字分组查询

(1)    用GROUP BY关键字分组查询

例如：mysql> select name,price from library GROUP BY price;

\+---------------+-------+

| name          | price |

\+---------------+-------+

| mySQL         | 50    |

| SQLserver2005 | 80    |

| java范例大全  | 99   |

\+---------------+-------+

3 rows in set (0.00 sec)

mysql> select name,price from library GROUP BY name;

\+---------------+-------+

| name          | price |

\+---------------+-------+

| java范例大全  | 99   |

| mySQL         | 50    |

| SQLserver2005 | 80    |

\+---------------+-------+

(2)    GROUP BY 关键字与 GROU\_CONCAT函数一起使用

mysql> selectname,GROUP\_CONCAT(price) from library GROUP BY name;

\+---------------+---------------------+

| name          |GROUP\_CONCAT(price) |

\+---------------+---------------------+

| java范例大全  | 99                  |

| mySQL         | 50,50               |

| SQLserver2005 | 80                 |

\+---------------+---------------------+

(3)按多个字段进行分组

mysql> selectid,name,price from library GROUP BY name,price;//当price字段的值相等时，再按照name字段分组

\+----+---------------+-------+

| id |name          | price |

\+----+---------------+-------+

|  1 | java范例大全  | 99    |

|  2 | mySQL         | 50   |

|  3 | SQLserver2005 | 80    |

\+----+---------------+-------+

13  用LIMIT限制查询结果的数量

mysql> select \* from library orderby id asc limit 2,3; //取两条数据，正序，从第三条开始

\+----+---------------+--------+-------+

\+----+---------------+--------+-------+

| 3 | SQLserver2005 | 刘智勇 | 80    |

| 4 | mySQL         | 李慧  | 50    |

\+----+---------------+--------+-------+

2 rows in set (0.00 sec)

mysql> select \* from libraryorder by id desc limit 2,3; //取两条数据，倒序，从倒数第三条结束，只显示倒数前两个

\+----+--------------+--------+-------+

\+----+--------------+--------+-------+

| 2 | mySQL        | 潘凯华 | 50    |

| 1 | java范例大全 | 张帆  | 99    |

\+----+--------------+--------+-------+

数据库mysql单表查询语句_mysql数据库常用的查询语句(单表查询)

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关单表查询语句

相关 mysql 表单列表_MySQL中select语句用法-单表单条件查询

相关数据库mysql单表查询语句_mysql数据库常用的查询语句(单表查询)

相关 MySQL数据库基础——表单查询2

相关 MySQL数据库基础——单表查询1

相关 MySQL单表查询

相关 MySQL 单表查询

相关数据库的单表查询

相关 MySql-单表查询

相关数据库单表查询