Select 基础查询
基础查询
select 查询列表 from 表名;
- 查询列表可以是
- 表中的字段
- 常量值
- 表达式
- 函数
- 查询的结果是一个虚拟的表格
查询单个字段
mysql> select * from xx; +------+------+ | id | name | +------+------+ | 1 | haha | | 2 | hee | +------+------+ 2 rows in set (0.00 sec) mysql> select id from xx; +------+ | id | +------+ | 1 | | 2 | +------+ 2 rows in set (0.00 sec)
查询表中多个字段
mysql> select id,name from xx;
+------+------+
| id | name |
+------+------+
| 1 | haha |
| 2 | hee |
+------+------+
2 rows in set (0.00 sec)
起别名
好处:
- 便于理解
- 如果要查询的字段有重名的情况,使用别名可以区分开来
方式一:使用AS起别名
//单个别名
mysql> select 100%93 AS xx;
+------+
| xx |
+------+
| 7 |
+------+
1 row in set (0.00 sec)
//多个别名
mysql> select id AS he,name AS ww from xx;
+------+------+
| he | ww |
+------+------+
| 1 | haha |
| 2 | hee |
+------+------+
2 rows in set (0.00 sec)
方式二:起别名,省略AS,使用空格
mysql> select id he,name ww from xx;
+------+------+
| he | ww |
+------+------+
| 1 | haha |
| 2 | hee |
+------+------+
2 rows in set (0.00 sec)
别名中有特殊符号,使用着重号
mysql> select id AS 'ww qq' from xx;
+-------+
| ww qq |
+-------+
| 1 |
| 2 |
+-------+
2 rows in set (0.00 sec)
去重
- 显示的列每一行都不相同才会去重
distinct
mysql> select distinct * from xx; +------+---------+ | id | name | +------+---------+ | 1 | Beijing | | 2 | TianT | | 3 | XianT | | 4 | WianT | | 4 | 2ianT | +------+---------+ 5 rows in set (0.00 sec) mysql> select distinct id from xx; +------+ | id | +------+ | 1 | | 2 | | 3 | | 4 | +------+ 4 rows in set (0.01 sec)
+号的作用
运算符
查询员工名和姓连接成一个字段,并显示为姓名
mysql> select 100+80; +--------+ | 100+80 | +--------+ | 180 | +--------+ 1 row in set (0.01 sec)
字符串拼接 concat()
concat('a','b','c') AS 别名
把已有字段拼接起来
mysql> select concat(id,name) AS ww from xx;
+----------+
| ww |
+----------+
| 1Beijing |
| 2TianT |
| 3XianT |
| 4WianT |
| 42ianT |
+----------+
5 rows in set (0.00 sec)
把没有的字段信息拼接起来
mysql> select concat('sdf','sad') AS ww from xx;
+--------+
| ww |
+--------+
| sdfsad |
| sdfsad |
| sdfsad |
| sdfsad |
| sdfsad |
+--------+
5 rows in set (0.00 sec)
IFNULL
IFNULL指定为NULL的数据类型显示别的
mysql> select ifnull(id,0) from xx;
select 基础查询练习题
1.显示表的结构,并查询其中所有的数据
mysql> desc xx; select * from xx;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
+------+---------+
| id | name |
+------+---------+
| 1 | Beijing |
| 2 | TianT |
| 3 | XianT |
| 4 | WianT |
| 4 | 2ianT |
+------+---------+
5 rows in set (0.00 sec)
2.显示出表中id的字段,数据不能重复
mysql> select distinct id from xx; +------+ | id | +------+ | 1 | | 2 | | 3 | | 4 | +------+ 4 rows in set (0.00 sec)
3.显示出表中的全部列,各个列之间用逗号连接,猎头显示成OUT_PUT
mysql> select concat(id,',',name) AS OUT_PUT from xx;
+-----------+
| OUT_PUT |
+-----------+
| 1,Beijing |
| 2,TianT |
| 3,XianT |
| 4,WianT |
| 4,2ianT |
+-----------+
5 rows in set (0.00 sec)
继续阅读

我的微信
这是我的微信扫一扫
评论