Select 基础查询

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)