数据库进阶1-3 基础查询、条件查询以及排序查询 基础语句

查询语句

进阶一、基础查询

语法：

select 查询列表 from 表名;

特点：

1. 查询的结果集 是一个虚拟表。

2. select 查询列表 类似于 System.out.println(内容)

   区别在于：

   • select后面的查询列表可以有多个部分组成。例如：

   ​ select 字段1,字段2,表达式 from 表名

   • System.out.println（）的打印内容是一个部分。

3. 执行顺序

   1. 先执行from子句
   2. 再执行select子句

4. 查询列表可以是：字段、表达式、常量、函数、变量等。

#一、查询常量
SELECT 50;
#二、查询表达式
SELECT 100%20;
#三、查询单个字段
SELECT last_name FROM employees;
#四、查询多个字段
SELECT email,first_name FROM employees;
#五、查询所有字段
SELECT * FROM employees;
SELECT email,salary,manager_id,department_id,hiredate,phone_number
FROM employees;

#六、查询函数(调用函数，获取返回值)
SELECT DATABASE();
SELECT VERSION();
SELECT USER();

#七、起别名
#方式1：使用 as 关键字
SELECT USER() AS 用户名;
SELECT USER() AS "用户名";
SELECT USER() AS '用户名';
SELECT VERSION() AS 版本号;

SELECT USER() AS "用 户 名";
#别名中可以不加双引号或者单引号。但在含有空格等其他字符时，需要用双引号或者单引号。

#方式2：使用空格
SELECT USER() 用户名;

#八、+号的作用
-- 需求：查询 first_name 和 last_name 拼接成的全名，并起别名为  姓  名
# 方法1：使用+号   错误 pass
SELECT first_name+last_name AS '姓 名' FROM employees;

# 方法2：使用函数  cancat     成功
SELECT CONCAT(first_name,last_name) AS '姓 名' FROM employees;

/*
 mysql中 +号的作用
 1.加法运算。
-两个操作数都是数值型： 100+23.4
-其中一个操作数为字符型，将字符型数据强制转换为数值型。若无法转换，直接当做0处理：
 '张三'+100  ===>100
-其中一个操作数为null
 null+null  ===>null
 null+10    ===>null
 */

#九、distinct的使用
-- 需求：查询员工所在的的部门编号有哪几种 （用distinct去掉重复的部门编号）
SELECT department_id FROM employees;

SELECT DISTINCT department_id FROM employees;

#十、查看表的结构   DESC  或者   SHOW COLUMNS FROM
DESC employees;
SHOW COLUMNS FROM employees;

基础案例

#显示出表employees的一些列，各个列之间用逗号连接，列头显示成OUT_PUT
SELECT CONCAT(employee_id,',',first_name,',',last_name,',',salary,',',commission_pct) AS
OUT_PUT FROM employees;

注意：

将列拼接时，如果有一列为null，则输出也为null。 commission_pct中一些值为null，所以拼接后输出为null

IFNULL 函数 ：

IFNULL(表达式1，表达式2)

• 表达式1：可能为null的字段或者表达式
• 表达式2：如果表达式1为null，最终会显示的内容

**函数功能：**如果表达式1为null，则显示表达式2，否则显示表达式1

SELECT commission_pct,IFNULL(commission_pct,'空的') FROM employees;

案例最终格式应为

SELECT CONCAT  (employee_id,',',first_name,',',last_name,',',salary,',',IFNULL(commission_pct,'')) AS OUT_PUT FROM employees;

进阶二、条件查询

语法：

select 查询列表 from 表名 where 筛选条件；

执行顺序

1. 先执行from子句
2. 再执行where子句
3. 最后执行select子句

SELECT first_name,last_name FROM employees WHERE salary>20000;

特点

1. 按关系表达式筛选

   关系运算符：> < >= <= = <>（不等于）

   != 也能使用，但不建议

2. 按逻辑表达式筛选

   逻辑运算符：and or not

   && || ！ 也能使用，但不建议

3. 模糊查询

   like

   in

   between and

   is null

一、按关系表达式查询

#案例1：查询部门编号不是100的员工信息
SELECT * FROM employees WHERE department_id<>100;
#案例2：查询工资小于15000的名字和工资
SELECT last_name,salary FROM employees WHERE salary<15000;

二、按逻辑表达式查询

#案例1：查询部门编号不是50到100之间的员工名字，部门编号和邮箱
#方式1
SELECT last_name,department_id,email
FROM employees
WHERE department_id<50 OR department_id>100;
#方式2   更易读的代码
SELECT last_name,department_id,email
FROM employees
WHERE NOT (department_id>=50 AND department_id<=100);
#查询奖金率大于0.03或者员工编号在60到110之间的员工信息
SELECT * FROM employees
WHERE commission_pct>0.03
OR (employee_id>=60 AND employee_id<=110);

三、模糊查询

like

1. like

   功能：一般和通配符搭配使用，对字符型数据进行部分匹配查询

   常见通配符：

   _ （下划线）任意单个字符

   %（百分号）任意多个字符, 支持0个到多个。

   not like

#1. like
#案例1：查询名字中包含 a 的员工信息
SELECT * FROM employees WHERE last_name LIKE '%a%';
/*
 _  表示任意单个字符
 %  表示任意多个字符
 */
#案例2：查询名字中最后一个字符为 a 的员工信息
SELECT * FROM employees WHERE last_name LIKE '%a';
#案例3：查询名字中第一个字符为 a 的员工信息
SELECT * FROM employees WHERE last_name LIKE 'a%';
#案例4：查询名字中第三个自=字符为 x 的员工信息
SELECT * FROM employees WHERE last_name LIKE '__x%';

注意 转义字符的使用

设置一个字符为转义字符 例如 $,则$后面的字符转义为单纯下划线。

#案例5：查询名字中第二个字符为 _ 的员工信息
#方式1；用转义字符  \  可以但不建议
SELECT * FROM employees WHERE last_name LIKE '_\_%';
#方式2：设置一个字符为转义字符  例如 $,则$后面的字符转义为单纯下划线。
SELECT * FROM employees WHERE last_name LIKE '_$_%' ESCAPE '$';

in

2. in

   功能：查询某字段的值是否属于指定的列表之内。

   a in(常量值1,常量值2,常量值3, …)

   即 a 字段的值是不是跟常量1、2、3…相等。

   not in

   a not in(常量值1,常量值2,常量值3, …)

   即 a 字段的值是不是 不跟常量1、2、3…相等。

   #查询部门编号是30/50/80的员工编号和邮箱和部门编号
   SELECT employee_id,email,department_id FROM employees
   WHERE department_id IN(30,50,80);
   #查询工种编号不是IT_PROG和AD_VP的员工信息
   SELECT * FROM employees WHERE job_id NOT IN('IT_PROG','AD_VP');
   

   注意：除了数值型常量，其他必须加上单引号或者双引号

between and

3. between and

   功能：判断某个字段的值是否介于 XXX之间。

   not between and

#案例1：查询部门编号是30-80之间的部门编号、员工姓名
SELECT department_id,last_name FROM employees
WHERE department_id BETWEEN 30 AND 80;
#案例2：查询年薪不是100000到200000之间的员工姓名、薪资和年薪
SELECT last_name,salary,salary*12*(1+IFNULL(commission_pct,0)) 年薪
FROM employees
WHERE salary*12*(1+IFNULL(commission_pct,0)) NOT BETWEEN 100000 AND 200000;

is null

4. is null/ is not null

#案例：查询没有奖金的员工信息
SELECT * FROM employees WHERE commission_pct IS NULL;
#案例：查询有奖金的员工信息
SELECT * FROM employees WHERE commission_pct IS NOT NULL;

= 只能判断普通的内容

is null 只能判断为null值

<=> 安全等于：既能判断普通内容，也能判断null值。

进阶三、排序查询

语法：

select 查询列表 from 表名 【where 筛选条件】 order by 排序列表

执行顺序

1. from子句
2. where子句
3. select子句
4. order by 子句

特点

1. 排序列表可以是单个字段、多个字段、表达式、函数、列数以及以上的组合。

2. 升序：关键字 asc 按升序是默认行为.

   降序：关键字 desc

一、按单个字段排序

#案例1 将员工编号>120的员工工资按升序排序       默认是升序，可以不写ASC
SELECT salary,employee_id FROM employees
WHERE employee_id>120 ORDER BY salary ASC;
#案例2 将员工编号>120的员工工资按降序排序
SELECT salary,employee_id FROM employees
WHERE employee_id>120 ORDER BY salary DESC;

二、按表达式排序

#案例1 对有奖金的员工 按年薪排序
SELECT *,salary*12*(1+IFNULL(commission_pct,0)) 年薪
FROM employees WHERE commission_pct IS NOT NULL
ORDER BY salary*12*(1+IFNULL(commission_pct,0)) DESC;

三、按别名排序

#案例1 对有奖金的员工 按年薪排序
SELECT *,salary*12*(1+IFNULL(commission_pct,0)) 年薪
FROM employees
#WHERE commission_pct IS NOT NULL
ORDER BY 年薪 DESC;

四、按函数的结果排序

#案例1  按姓名的字数长度进行排序
SELECT last_name FROM employees
ORDER BY LENGTH(last_name);
# LENGTH 函数：获取字符串长度
SELECT last_name,LENGTH(last_name) 姓名长度
FROM employees
ORDER BY 姓名长度;

五、按多个字段排序

#案例1  查询员工的工资、姓名、部门编号，先按工资升序，再按部门编号降序
SELECT last_name,salary,department_id FROM employees
ORDER BY salary,department_id DESC;

六、了解 （按列数排序）

SELECT * FROM employees
ORDER BY 2;
SELECT * FROM employees
ORDER BY first_name;