1、基础查询
格式: select <查询列表> [ from 表名 ]
1、查询列表的内容可以为:表的字段,常量值,表达式,函数.
2、查询的结果是一个虚拟的表格。
1.1 表字段查询
select 字段1,字段2 from 表名; -- 查询指定的字段
select * from 表名; -- 查询所有字段,字段顺序和定义的顺序相同。
1.2 查询常量
mysql> select 100; --- 查询数值常量
+-----+
| 100 |
+-----+
| 100 |
+-----+
1 row in set (0.00 sec)
mysql> select 'join'; --查询字符串常量
+------+
| join |
+------+
| join |
+------+
1 row in set (0.00 sec)
1.3 查询表达式
mysql> select 100%98; -- 查询表达式
+--------+
| 100%98 |
+--------+
| 2 |
+--------+
1 row in set (0.00 sec)mysql>
1.4 查询函数
select verison(); -- 查询版本信息
1.5 起别名
1.5.1 方式一:使用as
mysql> select 100%98 as 结果; -- 对常量结果起别名
+--------+
| 结果 |
+--------+
| 2 |
+--------+
1 row in set (0.01 sec)mysql>
mysql> select user, host from user;
+------------------+-----------+
| user | host |
+------------------+-----------+
| mysql.infoschema | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+------------------+-----------+
4 rows in set (0.00 sec)mysql> select user as 用户名, host as 主机名 from user; -- 用户名和主机名可以用双引号或者单引号括起来
+------------------+-----------+
| 用户名 | 主机名 |
+------------------+-----------+
| mysql.infoschema | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+------------------+-----------+
4 rows in set (0.00 sec)mysql>
1.5.2 方式二:字段后面直接跟别名
mysql> select user 用户名, host 主机名 from user;
+------------------+-----------+
| 用户名 | 主机名 |
+------------------+-----------+
| mysql.infoschema | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+------------------+-----------+
4 rows in set (0.00 sec)
1.6 去重
<1> 去重select的后面只能存在一个字段,
select distinct department_id from employees; -- department_id代码部门编号, employees表示员工数据库,对部门编号去重
1.7 +号的作用
仅仅只有一个功能,普通运算符,用于相加数值类型。
(1)不能用于拼接字符串,如果参数中是字符串,则尝试将字符串转换为对应的数值,如果转换失败,则为0。
(2)如果其中一个参数为null,则结果为null
mysql> select 100 + 90;
+----------+
| 100 + 90 |
+----------+
| 190 |
+----------+
1 row in set (0.29 sec)mysql> select '123' + 100;
+-------------+
| '123' + 100 |
+-------------+
| 223 |
+-------------+
1 row in set (0.28 sec)mysql> select '123' + "av";
+--------------+
| '123' + "av" |
+--------------+
| 123 |
+--------------+
1 row in set, 1 warning (0.00 sec)mysql> select 1 + null;
+----------+
| 1 + null |
+----------+
| NULL |
+----------+
1 row in set (0.00 sec)mysql>
1.8 函数拼接
函数concat(str1, str2, ...)
mysql> select concat("ke", "heyang");
+------------------------+
| concat("ke", "heyang") |
+------------------------+
| keheyang |
+------------------------+
1 row in set (0.00 sec)mysql>
mysql> create table t1(first_name char(10), last_name char(10));
Query OK, 0 rows affected (0.54 sec)
mysql> insert into t1(first_name, last_name) values("heyang", "ke");
Query OK, 1 row affected (0.08 sec)mysql> select * from t1;
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| heyang | ke |
+------------+-----------+
1 row in set (0.00 sec)mysql> select concat(last_name, first_name) as 姓名 from t1; --as可以省略
+----------+
| 姓名 |
+----------+
| keheyang |
+----------+
1 row in set (0.28 sec)mysql> select concat(last_name, first_name) from t1;
+-------------------------------+
| concat(last_name, first_name) |
+-------------------------------+
| keheyang |
+-------------------------------+
1 row in set (0.00 sec)mysql>
注意点:concat 拼接的字符串不能有null,如果有任何一个参数为null,则返回null。
问题,如果要拼接的表字段中有内容为null的项,该如果处理??
使用ifnull(str1, str2),str1不为null,返回str1, str1为null,返回str2;
mysql> select ifnull('str1', 'str2');
+------------------------+
| ifnull('str1', 'str2') |
+------------------------+
| str1 |
+------------------------+
1 row in set (0.00 sec)
mysql> select ifnull(null, 'str2');
+----------------------+
| ifnull(null, 'str2') |
+----------------------+
| str2 |
+----------------------+
1 row in set (0.00 sec)mysql>
2、条件查询
2.1 查询格式
select 查询列表 from 表名 where 筛选条件;
筛选条件 分类:
<1> 按照条件表达式筛选:
条件运算符: >, <, =, != (<>也表示不等于), >=, <=
<2> 安装逻辑表达式筛选:
逻辑运算符: &&,||, !,mysql推荐使用 and,or,not。
<3> 模糊查询
模糊查询关键字:like, between and, in, is null(is not null)
2.2 按照条件表达式筛选
示例:
select * from employees where salary>12000; -- 查询工资大于12000的员工。
2.3 按照逻辑表达式筛选
示例:
select * from employees where salary>=10000 and salary <= 20000; -- 查询工资在10000 到20000之间的员工信息。
select * from employees where not(salary <= 10000 and salary <= 20000); -- 使用not 和and 查询工资小于10000 或者 大于20000的员工信息。
2.3 模糊查询
2.3.1 like
<1> 一般和通配符配合使用,可判断字符型或者数值型:
%:匹配任意多个字符(包含0个字符)
_: 下划线,匹配任意单个字符。
如果要匹配的字符就是%,_,需要使用转义字符 \_, \%,还可以使用 escape指定转义字符(参考后面的例子)。
select * from employees where last_name like '%a%'; -- 查询员工名中包含字符a的员工信息, %号表示通配符。
select * from employees where last_name like '_$_%' escape '$'; --查询员工名字第二个字符为下划线,使用$作为转义字符。
2.3.2 [not] between and
<1> 包含临界值(闭区间)
<2> 左操作数小于右操作数,不能互换。
select * from employees where employee_id between 100 and 120; -- 查询员工编号在100到120之间的员工(包含100和120)。
2.3.3 in
<1> in 列表不能使用通配符。
<2>in列表的值类型必须一致或者兼容。
select * from employees where job_id in('IT', 'AD'); -- 查询工种编号为 IT, AD的员工信息。
2.3.4 is null/is not null
<1> 判断和null相等不能使用条件运算符 =,x=null不能工作,=运算符不能判断是否为null,要判断null,必须用 is null。
示例:
select * from employees where commission IS NULL; -- 查询没有奖金的员工信息(假设没有奖金,奖金列就为null)
select * from employees where commission IS NOT NULL; -- 查询有奖金的员工信息(假设没有奖金,奖金列就为null)
2.3.5 安全等于 <=>
<1> 可以用于判断是否和null相等
select * from employees where commission <=> NULL; -- 查询没有奖金的员工信息(假设没有奖金,奖金列就为null)
3、排序查询
查询格式: select 查询列表 from 表名 [where 筛选条件] order by 排序列表/表达式 [asc | desc]
asc:升序(默认情况)
desc:降序
select * from employees where department_id >=90 order by hiredate asc; ---查询部门编号>=90的员工信息,按照入职时间先后排序
select *, salary*12*(1+ifnull(commission_pct, 0)) 年薪 from employees order by salary*12*(1+ifnull(commission_pct, 0)) desc; --按照年薪高低显示员工的信息和年薪(commission_pct表示奖金率)。
select *, salary*12*(1+ifnull(commission_pct, 0)) 年薪 from employees order by 年薪 desc; --使用别名(年薪为别名)。
select last_name, salary from employees order by length(last_name) desc; -- 按照姓名显示员工的姓名和工资,使用了length(str)函数。
select * from employees order by salary ASC, employee_id desc; --支持多字段排序,这里先按照工资升序,再按照员工ID从降序。