第一章 查询语言,DQL语言
一、基础查询
/*
语法:
SELECT 查询列表 FROM 表名;
特点:
1、查询列表可以是字段、常量、表达式、函数,也可以是多个
2、查询结果是一个虚拟表
*/
1、查询表中单个字段
SELECT last_name FROM employees;
2、查询表中多个字段
SELECT last_name,salary,email FROM employees;
3、查询表中所有字段
SELECT * FROM employees;
4、查询常量值
#注意:字符型和日期型常量值须用单引号引起来
SELECT 100;
SELECT 'john';
5、查询表达式
SELECT 100%98;
6、查询函数
SELECT VERSION();
7、起别名
/*
目的:
①便于理解;
②若查询字段有重名时可用别名区分*/
#方式一:使用AS
SELECT 100%98 as 结果;
SELECT last_name AS 姓,first_name AS 名 FROM employees;
#方式二:使用空格
SELECT last_name 姓,first_name 名 FROM employees;
#案例:查询salary,显示结果为out_put
SELECT salary 'out put' FROM employees;
8、去重
#案例:查询员工表中涉及到的所有部门编号
SELECT DISTINCT department_id FROM employees;
9、+号的作用
/*
java中+号作用
①运算符:两个操作数都为数值型
②连接符:只要有一个操作数为字符串
mysql中+号的作用
①仅仅是运算符功能
select 数值+数值,直接运算
select 字符+数值,先试图将字符转换成数值,若转换成功,则继续运算;否则转换为0,再运算
select null+值,结果都为null
相关知识点:
CONCAT(str1,str2,…),拼接字符
IFNULL(expr1,expr2),判断某字段或表达式是否为null,若是null,返回指定的值,否则返回原本的值
ISNULL(expr),判断某字段或表达式是否为null,若是null,则返回1,否则返回0
*/
#案例:查询员工名和姓连接成一个字段,并显示为姓名
SELECT CONCAT(last_name,first_name) 姓名 FROM employees;
#显示表的全部列,各列用逗号连接,列头显示out_put
SELECT
CONCAT(first_name,',',last_name,',',job_id,',',IFNULL(commission_pct,0))
as 'out_put'
FROM
employees;
#显示department表结构,并查询表
DESC departments;
SELECT * FROM departments;
二、条件查询
/*
语法:
SELECT
查询列表
FROM
表名
WHERE
筛选条件;
分类:
(1)按条件表达式筛选,> < = != <> >= <=
(2)按逻辑表达式筛选,用于连接条件表达式 && || !,and or not
①&&和and:两个条件都为true,结果为true,反之为false
②||和or:只要有一个条件为true,结果为true,反之为false
③!和not:如果连接的条件本身为false,结果为true,反之为false
(3)模糊查询
①LIKE:一般与通配符搭配使用,可以判断字符型或数值型
% 任意多个字符,包含0个字符
- 任意单个字符
②BETWEEN AND
可以提高语句简洁度
包含临界值,且临界值不可调换顺序
③IN
判断某个字段的值是否属于in列表中的某一项
使用更简洁,列表内的值类型须保持一致或兼容
④is NULL is not NULL
=或<>不能判断null值
is null 或者is not null:仅仅可以判断null值,可读性比较高
安全等于:<=>,可以判断null值又可以判断普通数值,可读性低
*/
1、按条件表达式筛选
#案例:查询工资>12000的员工信息
SELECT * FROM employees
WHERE salary > 12000;
#案例:查询部门编号不等于90号的员工名和部门编号
SELECT last_name, department_id FROM
employees WHERE department_id != 90;
2、按逻辑表达式筛选
#案例:查询工资在10000到20000之间的员工名、工资及奖金
SELECT last_name, salary, commission_pct
FROM employees WHERE
salary >= 10000 && salary <= 20000;
#案例:查询部门编号不是在90到110之间,或者工资高于15000的员工信息
SELECT * FROM employees
WHERE
#department_id<90 OR department_id>110 OR salary>15000;
NOT(department_id>=90 AND department_id<=110) OR salary>15000;
三、模糊查询
#案例:查询员工名中包含字符a的员工信息
SELECT * FROM employees
WHERE last_name LIKE '%a%';
#案例:查询员工名第四个字符a,第七个字符为a的员工信息
SELECT * FROM employees
WHERE last_name LIKE '___a__a%';
#案例:查询员工名中第二个字符为_的员工名
SELECT last_name FROM employees
WHERE
#last_name LIKE '_\_%';
last_name LIKE '_$_%' ESCAPE '$';
#案例:查询员工编号在100到120之间的员工信息
SELECT * FROM employees
WHERE
#employee_id >= 100 AND employee_id <= 120;
employee_id BETWEEN 100 AND 120;
#案例:查询员工的工种编号是 IT_PROG、AD_VP、AD_PRES中的一个员工名和工种编号
SELECT last_name, job_id FROM employees
WHERE
#job_id = 'IT_PROG' OR job_id = 'AD_VP' OR job_id = 'AD_PRES';
job_id in ('IT_PROG','AD_VP','AD_PRES');
#案例:查询没有|有奖金的员工名和奖金率
SELECT last_name, commission_pct FROM employees
WHERE
#commission_pct IS NULL;
commission_pct <=> NULL;
#commission_pct IS NOT NULL;
#案例:查询工资为12000的员工信息
SELECT last_name, salary FROM employees
WHERE salary <=> 12000;
#查询员工号为176的员工姓名和部门编号、年薪
SELECT last_name, department_id,
salary*12*(1+IFNULL(commission_pct,0)) as 年薪
FROM employees
WHERE employee_id = 176;
#选择工资不在5000到12000的员工姓名和工资
SELECT last_name, salary FROM employees
WHERE
NOT ( salary BETWEEN 5000 AND 12000 );
四、排序查询
/*
语法:
select 查询列表
from 表名
【where 筛选条件】
ORDER BY 排序列表 【ASC | DESC】
特点:
1、ASC:代表升序,默认,可以不写
DESC:代表降序
2、ORDER BY子句中可以支持单个字段、多个字段、表达式、函数、别名
3、ORDER BY子句一般放在查询语句的最后面,LIMIT子句除外
*/
#案例:查询员工信息,要求工资从高到低排序
SELECT * FROM employees ORDER BY salary DESC;
#案例:查询员工信息,要求工资从低到高排序
#SELECT * FROM employees ORDER BY salary ASC;
SELECT * FROM employees ORDER BY salary;
#案例:查询部门编号>=90的员工信息,按入职时间的先后排序(添加筛选条件)
SELECT * FROM employees
WHERE department_id >= 90
ORDER BY hiredate;
#案例:按年薪的高低显示员工信息和年薪(按表达式排序)
SELECT *,salary*12*(1+IFNULL(commission_pct,0)) as 年薪
FROM employees
ORDER BY salary*12*(1+IFNULL(commission_pct,0)) DESC;
#案例:按年薪的高低显示员工信息和年薪(按别名排序)
SELECT *,salary*12*(1+IFNULL(commission_pct,0)) as 年薪
FROM employees
ORDER BY 年薪 DESC;
#案例:按姓名的长度显示员工姓名和工资(按函数排序)
SELECT LENGTH( last_name ) 字节长度,
last_name, salary FROM employees
ORDER BY LENGTH( last_name ) DESC;
#案例:查询员工信息,先按员工工资排序,再按员工编号排序(按多个字段排序)
SELECT * FROM employees
ORDER BY salary ASC,
employee_id DESC;
五、常见函数
/*
功能:类似Java中的方法,将一组逻辑语句封装在方法体中,对外暴露方法名
好处:
①隐藏实现细节;
②提高代码的重用性
调用:
SELECT 函数名(实参列表) 【from 表名】
特点:
①函数名:叫什么
②函数功能:干什么
分类:
1、单行函数:字符函数、数学函数、日期函数、其他函数、流程控制函数
CONCAT(str1,str2,...)
LENGTH(str)
IFNULL(expr1,expr2)等
2、分组函数:做统计使用,又称统计函数、聚合函数、组函数
sum函数、avg平均值、max最大值、min最小值、count计算个数
*/
1、单行函数
(1)字符函数
①LENGTH
#LENGTH(str)
#用于获取参数值的字节个数
SELECT LENGTH('john');
SELECT LENGTH('张三丰hahaha');
#查看mysql的系统参数
SHOW VARIABLES LIKE '%char%';
②CONCAT
#CONCAT(str1,str2,…)
#拼接字符
SELECT CONCAT(last_name,'_',first_name) 姓名 FROM employees;
③UPPER|LOWER
#大写:UPPER(str)
#小写:LOWER(str)
SELECT UPPER('john');
SELECT LOWER('JOHN');
#案例:将姓变大写,名变小写,然后拼接显示
SELECT CONCAT(UPPER(last_name),'_',LOWER(first_name)) 姓名 FROM employees;
④SUBSTR|SUBSTRING
/*SUBSTR(str,pos,len)
SUBSTRING(str FROM pos FOR len),截取子串
注意,索引从1开始
截取从’指定索引’处后面所有字符
*/
SELECT SUBSTR('数据库和数据管理系统',5) out_put;
#截取从’指定索引’处’指定字符长度’的字符
SELECT SUBSTR('数据库和数据管理系统'