DQL(Data QueryLanguage):数据查询语⾔,通俗点讲就是从数据库获取数据的,按照 DQL的语法给数据库发送⼀条指令,数据库将按需求返回数据。
一、select 基本查询
1.基本语法
select 查询的列 from 表名;
注意: select语句中不区分⼤⼩写,SELECT 和s elect、FROM和from效果⼀样。 查询的结果放在⼀个表格中,表格的第1⾏称为列头,第2⾏开始是数据,类属于⼀个⼆维数组。
2.查询常量
select 常量值1,常量值2,常量值3;
如:
mysql> select 2,'a';
+---+---+
| 2 | a |
+---+---+
| 2 | a |
+---+---+
1 row in set (0.00 sec)
3.查询函数
select 函数;
例:
mysql> select mod(10,4),isnull(null),ifnull(null,'第⼀个参数为空返回这个值'),ifnull(1,'第⼀个参数为空返回这个值,否知返回第⼀个参数');
+-----------+--------------+----------------------------------------+-------------------------------------------------------+
| mod(10,4) | isnull(null) | ifnull(null,'第?个参数为空返回这个值') | ifnull(1,'第?个参数为空返回这个值,否知返回第?个参数') |
+-----------+--------------+----------------------------------------+-------------------------------------------------------+
| 2 | 1 | 第?个参数为空返回这个值 | 1 |
+-----------+--------------+----------------------------------------+-------------------------------------------------------+
1 row in set (0.00 sec)
说明⼀下:
-
mod函数,对两个参数取模运算。
-
isnull函数,判断参数是否为空,若为空返回1,否则返回0。
-
ifnull函数,2个参数,判断第⼀个参数是否为空,如果为空返回第2个参数的值,否则返回第1个参数的值。
4.查询表达式
select 表达式;
如:
mysql> select 1+2,3*10,10/3;
+-----+------+--------+
| 1+2 | 3*10 | 10/3 |
+-----+------+--------+
| 3 | 30 | 3.3333 |
+-----+------+--------+
1 row in set (0.01 sec)
5.查询指定字段
select 字段1,字段2,字段3 from 表名;
例:
mysql> drop table if exists test1;
Query OK, 0 rows affected (0.01 sec)
mysql> create table test1(
> a int not null comment '字段a',
> b varchar(10) not null default '' comment '字段b'
> );
Query OK, 0 rows affected (0.01 sec)
mysql> insert into test1 values(1,'a'),
> (2,'b'),
> (3,'c');
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select a,b from test1;
+---+---+
| a | b |
+---+---+
| 1 | a |
| 2 | b |
| 3 | c |
+---+---+
3 rows in set (0.00 sec)
说明: test1表有两个字段a、b,select a,b from test1;⽤于查询test1中两个字段的数据。
6.查询所有列
select * from 表名 ;
*说明: 表⽰返回表中所有字段。
如:
mysql> select * from test1;
+---+---+
| a | b |
+---+---+
| 1 | a |
| 2 | b |
| 3 | c |
+---+---+
3 rows in set (0.00 sec)
7.查询带有别名列
在创建数据表时,⼀般都会使⽤英⽂单词或英⽂单词缩写来设置字段名,在查询时列名都 会以英⽂的形式显⽰,这样会给⽤户查看数据带来不便,这种情况可以使⽤别名来代替英 ⽂列名,增强阅读性。
语法:
select 列 [as] 别名 from 表;
7.1 双引号创建别名
mysql> select a "列1",b "列2" from test1;
+------+------+
| 列1 | 列2 |
+------+------+
| 1 | a |
| 2 | b |
| 3 | c |
+------+------+
3 rows in set (0.00 sec)
7.2 单引号创建列别名
mysql> select a '列1',b '列2' from test1;;
+------+------+
| 列1 | 列2 |
+------+------+
| 1 | a |
| 2 | b |
| 3 | c |
+------+------+
3 rows in set (0.00 sec)
7.3 不用引号创建列别名
mysql> select a 列1,b 列2 from test1;
+------+------+
| 列1 | 列2 |
+------+------+
| 1 | a |
| 2 | b |
| 3 | c |
+------+------+
3 rows in set (0.00 sec)
7.4 使用as创建列别名
mysql> select a as 列1,b as 列 2 from test1;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '2 from test1' at line 1
mysql> select a as 列1,b as '列 2' from test1;
+------+-------+
| 列1 | 列 2 |
+------+-------+
| 1 | a |
| 2 | b |
| 3 | c |
+------+-------+
3 rows in set (0.00 sec)
注意:别名中有特殊符号的,⽐如空格,此时别名必须⽤引号引起来。
7.5 易错理解
mysql> select 'a' 'b';
+----+
| a |
+----+
| ab |
+----+
1 row in set (0.00 sec)
// 等同于 select 'a' as 'b';
mysql> select 'a' b;
+---+
| b |
+---+
| a |
+---+
1 row in set (0.00 sec)
// 等同于 select 'a' 'b';
mysql> select 'a' "b";
+----+
| a |
+----+
| ab |
+----+
1 row in set (0.00 sec)
// 等同于 select 'a' b;
// 等同于 select 'a' as 'b';
mysql> select 'a' as "b";
+---+
| b |
+---+
| a |
+---+
1 row in set (0.00 sec)
8.查询带有别名表
select 别名.字段,别名.* from 表名 [as] 别名;
如:
mysql> select t.a,t.b from test1 as t;
+---+---+
| a | b |
+---+---+
| 1 | a |
| 2 | b |
| 3 | c |
+---+---+
3 rows in set (0.00 sec)
mysql> select t.a as '列 1',t.b as 列2 from test1 as t;
+-------+------+
| 列 1 | 列2 |
+-------+------+
| 1 | a |
| 2 | b |
| 3 | c |
+-------+------+
3 rows in set (0.00 sec)
mysql> select t.* from test1 as t; ;;
+---+---+
| a | b |
+---+---+
| 1 | a |
| 2 | b |
| 3 | c |
+---+---+
3 rows in set (0.00 sec)
mysql> select * from test1 as t;
+---+---+
| a | b |
+---+---+
| 1 | a |
| 2 | b |
| 3 | c |
+---+---+
3 rows in set (0.00 sec)
总结
• 建议别名前⾯跟上as关键字
• 查询数据的时候,避免使⽤select,建议需要什么字段写什么字段*
二、select 条件查询
语法:
select 列名 from 表名 where 列 运算符 值
说明:
注意关键字where,where后⾯跟上⼀个或者多个条件,条件是对前⾯数据的过滤,只有满⾜where后⾯条件的数据才会被返回。
1.条件查询运算符
等于(=)
select 列名 from 表名 where 列 = 值;
说明:
查询出指定的列和对应的值相等的记录。如果是字符串类型,需要⽤单引号或者双引号引起来。
例:
mysql> create table test1 (a int,b varchar(10));
Query OK, 0 rows affected (0.01 sec)
mysql> insert into test1 values (1,'ab'),(2,'bb');
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from test1;
+------+------+
| a | b |
+------+------+
| 1 | ab |
| 2 | bb|
+------+------+
2 rows in set (0.00 sec)
mysql> select * from test1 where a=2;
+------+------+
| a | b |
+------+------+
| 2 | bb|
+------+------+
1 row in set (0.00 sec)
mysql> select * from test1 where b = 'ab';
+------+------+
| a | b |
+------+------+
| 1 | ab |
+------+------+
1 row in set (0.00 sec)
不等于(<>、!=)
不等于有两种写法:<>或者!=
select 列名 from 表名 where 列 <> 值;
或者
select 列名 from 表名 where 列 != 值;
例:
mysql> select * from test1 where a<>1;
+------+------+
| a | b |
+------+------+
| 2 | bb |
+------+------+
1 row in set (0.00 sec)
mysql> select * from test1 where a!=1;
+------+------+
| a | b |
+------+------+
| 2 | bb |
+------+------+
1 row in set (0.00 sec)
注意:
-
<> 这个是最早的⽤法。
-
!= 是后来才加上的。
-
两者意义相同,在可移植性上前者优于后者 故⽽sql语句中尽量使⽤<>来做不等判断
⼤于(>)
select 列名 from 表名 where 列 > 值;
例:
mysql> select * from test1 where a>1;
+------+------+
| a | b |
+------+------+
| 2 | bb|
+------+------+
1 row in set (0.00 sec)
mysql> select * from test1 where b>'a';
+------+------+
| a | b |
+------+------+
| 1 | ab |
| 2 | bb |
+------+------+
2 rows in set (0.00 sec)
mysql> select * from test1 where b>'ac';
+------+------+
| a | b |
+------+------+
| 2 | bb |
+------+------+
1 row in set (0.00 sec)
说明:
1.数值按照⼤⼩⽐较。
2.字符按照ASCII码对应的值进⾏⽐较,⽐较时按照字符对应的位置⼀个字符⼀个 字符的⽐较。
2.逻辑查询运算符
AND(并且)
select 列名 from 表名 where 条件1 and 条件2;
表⽰返回满⾜条件1和条件2的记录。
mysql> create table test3
> (
> a int not null,
> b varchar(10) not null
> );
Query OK, 0 rows affected (0.01 sec)
mysql> insert into test3 (a,b) values (1,'a'),
(2,'b'),
(2,'c'),
(3,'c');
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> select * from test3;
+---+---+
| a | b |
+---+---+
| 1 | a |
| 2 | b |
| 2 | c |
| 3 | c |
+---+---+
4 rows in set (0.00 sec)
mysql> select * from test3 t where t.a=2 and t.b='c';
+---+---+
| a | b |
+---+---+
| 2 | c |
+---+---+
1 row in set (0.00 sec)
查询出了表中a=2值并且b='c’值的记录,返回了⼀条结果。
OR(或者)
select 列名 from 表名 where 条件1 or 条件2;
满⾜条件1或者满⾜条件2的记录都会被返回。
⽰例:
mysql> select * from test3;
+---+---+
| a | b |
+---+---+| 1 | a |
| 2 | b |
| 2 | c |
| 3 | c |
+---+---+
4 rows in set (0.00 sec)
mysql> select * from test3 t where t.a=1 or t.b='c';
+---+---+
| a | b |
+---+---+
| 1 | a |
| 2 | c |
| 3 | c |
+---+---+
3 rows in set (0.00 sec)
查询出了表中a=1值或者b='c’值的记录,返回了3条记录。
3.LIKE(模糊查询)
有个学⽣表,包含(学⽣id,年龄,姓名),当我们需要查询姓“张”的学⽣的时候,如何查询呢?
此时我们可以使⽤sql中的like关键字。语法:
select 列名 from 表名 where 列 like pattern;
pattern中可以包含通配符,有以下通配符:
%:表⽰匹配任意⼀个或多个字符
_:表⽰匹配任意⼀个字符。
学⽣表,查询名字姓“张”的学⽣,如下:
mysql> create table stu
> (
> id int not null comment '编号',
> age smallint not null comment '年龄',
> name varchar(10) not null comment '姓名'
> );
Query OK, 0 rows affected (0.01 sec)
mysql> insert into stu values (1,22,'张三'),
(2,25,'李四'),
(3,26,'张学友'),
(4,32,'刘德华'),
(5,55,'张学良');
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> select * from stu;
+----+-----+-----------+
| id | age | name |
+----+-----+-----------+
| 1 | 22 | 张三 |
| 2 | 25 | 李四 |
| 3 | 26 | 张学友 |
| 4 | 32 | 刘德华 |
| 5 | 55 | 张学良 |
+----+-----+-----------+
5 rows in set (0.00 sec)
mysql> select * from stu a where a.name like '张%';
+----+-----+-----------+
| id | age | name |
+----+-----+-----------+
| 1 | 22 | 张三 |
| 3 | 26 | 张学友 |
| 5 | 55 | 张学良 |
+----+-----+-----------+
3 rows in set (0.00 sec)
查询名字中带有'学'的学⽣,'学'的位置不固定,可以这么查询,如下:
mysql> select * from stu a where a.name like '%学%'; ;
+----+-----+-----------+
| id | age | name |
+----+-----+-----------+
| 3 | 26 | 张学友 |
| 5 | 55 | 张学良 |
+----+-----+-----------+
2 rows in set (0.00 sec)
查询姓'张',名字2个字的学⽣:
mysql> select * from stu a where a.name like '张_';
+----+-----+--------+
| id | age | name |
+----+-----+--------+
| 1 | 22 | 张三 |
+----+-----+--------+
1 row in set (0.00 sec)
上⾯的_代表任意⼀个字符,如果要查询姓’张’的3个字的学⽣,条件变为了’张 _ _’,2个下划线符号。
4.BETWEEN AND(区间查询)
操作符 BETWEEN … AND 会选取介于两个值之间的数据范围,这些值可以是数值、⽂本或 者⽇期,属于⼀个闭区间查询。
selec 列名 from 表名 where 列名 between 值1 and 值2;
-
返回对应的列的值在[值1,值2]区间中的记录
-
使⽤between…and…可以提⾼语句的简洁度
-
两个临界值不要调换位置,只能是⼤于等于左边的值,并且⼩于等于右边的值。
⽰例: 查询年龄在[25,32]的,如下:
mysql> select * from stu;
+----+-----+-----------+
| id | age | name |
+----+-----+-----------+
| 1 | 22 | 张三 |
| 2 | 25 | 李四 |
| 3 | 26 | 张学友 |
| 4 | 32 | 刘德华 |
| 5 | 55 | 张学良 |
+----+-----+-----------+
5 rows in set (0.00 sec)
mysql> select * from stu t where t.age between 25 and 32;
+----+-----+-----------+
| id | age | name |
+----+-----+-----------+
| 2 | 25 | 李四 |
| 3 | 26 | 张学友 |
| 4 | 32 | 刘德华 |
+----+-----+-----------+
3 rows in set (0.00 sec)
select * from stu t where t.age between 25 and 32;
select * from stu t where t.age >= 25 and t.age <= 32; 效果与上一条一样
5.IN查询
我们需要查询年龄为10岁、15岁、20岁、30岁的⼈,怎么查询呢?可以⽤or查询,如 下:
mysql> create table test6(
> id int,
> age smallint
> );
Query OK, 0 rows affected (0.01 sec)
mysql> insert into test6 values(1,14),(2,15),(3,18),(4,20),(5,28),
(6,10),(7,10),(8,30);
Query OK, 8 rows affected (0.00 sec)
Records: 8 Duplicates: 0 Warnings: 0
mysql> select * from test6;
+------+------+
| id | age |
+------+------+
| 1 | 14 |
| 2 | 15 |
| 3 | 18 |
| 4 | 20 |
| 5 | 28 |
| 6 | 10 |
| 7 | 10 |
| 8 | 30 |
+------+------+
8 rows in set (0.00 sec)
mysql> select * from test6 t where t.age=10 or t.age=15 or t.age=20 or t.age = 30;
+------+------+
| id | age |
+------+------+
| 2 | 15 |
| 4 | 20 |
| 6 | 10 |
| 7 | 10 |
| 8 | 30 |
+------+------+
5 rows in set (0.00 sec)
IN 操作符允许我们在 WHERE ⼦句中规定多个值。
select 列名 from 表名 where 字段 in (值1,值2,值3,值4);
- in 后⾯括号中可以包含多个值,对应记录的字段满⾜in中任意⼀个都会被返回
- in列表的值类型必须⼀致或兼容
- in列表中不⽀持通配符。
上⾯的⽰例⽤IN实现如下:
mysql> select * from test6 t where t.age in (10,15,20,30);
+------+------+
| id | age |
+------+------+
| 2 | 15 |
| 4 | 20 |
| 6 | 10 || 7 | 10 |
| 8 | 30 |
+------+------+
5 rows in set (0.00 sec)
in的使用相对于or而言简洁了很多。
6.NOT IN查询
not in和in刚好相反,in是列表中被匹配的都会被返回,NOT IN是和列表中都不匹配的会被返回。
select 列名 from 表名 where 字段 not in (值1,值2,值3,值4);
如查询年龄不在10、15、20、30之内的,如下:
mysql> select * from test6 t where t.age not in (10,15,20,30);
+------+------+
| id | age |
+------+------+
| 1 | 14 |
| 3 | 18 |
| 5 | 28 |
+------+------+
3 rows in set (0.00 sec)
7.IS NULL(返回值为空的记录)
select 列名 from 表名 where 列 is null;
查询指定的列的值为NULL的记录。如:
mysql> create table test7 (a int,b varchar(10));
Query OK, 0 rows affected (0.01 sec)
mysql> insert into test7 (a,b) values (1,'a'),
(null,'b'),
(3,null),
(null,null),
(4,'c');
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> select * from test7;
+------+------+
| a | b |
+------+------+
| 1 | a |
| NULL | b |
| 3 | NULL |
| NULL | NULL |
| 4 | c |
+------+------+
5 rows in set (0.00 sec)
mysql> select * from test7 t where t.a is null;
+------+------+
| a | b |
+------+------+
| NULL | b |
| NULL | NULL |
+------+------+
2 rows in set (0.00 sec)
mysql> select * from test7 t where t.a is null or t.b is null;
+------+------+
| a | b |
+------+------+
| NULL | b |
| 3 | NULL |
| NULL | NULL |
+------+------+
3 rows in set (0.00 sec)
8.IS NULL(返回值不为空的记录)
select 列名 from 表名 where 列 is not null;
查询指定的列的值不为NULL的记录。
如:
mysql> select * from test7 t where t.a is not null;
+------+------+
| a | b |
+------+------+
| 1 | a |
| 3 | NULL |
| 4 | c |
+------+------+
3 rows in set (0.00 sec)
mysql> select * from test7 t where t.a is not null and t.b is not null;
+------+------+
| a | b |
+------+------+
| 1 | a |
| 4 | c |
+------+------+
2 rows in set (0.00 sec)
9.<=>(安全等于)
<=>:既可以判断NULL值,又可以判断普通的数值,可读性较低,⽤得较少
⽰例:
mysql> create table test8 (a int,b varchar(10));
Query OK, 0 rows affected (0.01 sec)
mysql> insert into test8 (a,b) values (1,'a'),
(null,'b'),
(3,null),
(null,null),
(4,'c');
Query OK, 5 rows affected (0.01 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> select * from test8;
+------+------+
| a | b |
+------+------+
| 1 | a |
| NULL | b |
| 3 | NULL |
| NULL | NULL |
| 4 | c |
+------+------+
5 rows in set (0.00 sec)
mysql> select * from test8 t where t.a<=>null;
+------+------+
| a | b |
+------+------+
| NULL | b |
| NULL | NULL |
+------+------+
2 rows in set (0.00 sec)
mysql> select * from test8 t where t.a<=>1;
+------+------+
| a | b |
+------+------+
| 1 | a |
+------+------+
1 row in set (0.00 sec)
可以看到<=>可以将NULL查询出来。
补充
经典⾯试题
下⾯的2个sql查询结果⼀样么?
select * from students;
select * from students where name like '%';
结果分2种情况:当name没有NULL值时,返回的结果⼀样。
当name有NULL值时,第2个sql查询不出name为NULL的记录。
三、总结
• like中的%可以匹配⼀个到多个任意的字符,_可以匹配任意⼀个字符
• 空值查询需要使⽤IS NULL或者IS NOT NULL,其他查询运算符对NULL值⽆效
• 建议创建表的时候,尽量设置表的字段不能为空,给字段设置⼀个默认值
• <=>(安全等于)玩玩可以,建议少使⽤