Sql查询语法学习
数据库概述
一、数据库的好处
- 可以持久化数据到本地
- 结构化查询
二、数据库常见概念 - DB：数据库，存储数据的容器
- DBMS：数据库管理系统，又称为数据库软件或数据库产品，用于创建或管理DB
- SQL：结构化查询语句，用于和数据库通讯的语言，不是某个数据库软件特有的，而是几乎所有主流数据库软件通用的语言
三、数据库存储数据的特点 - 数据存放到表中，然后表再放到库中
- 一个库中可以有多张表，每张表具有唯一的表名来标识自己
- 表中有一个或多个列，列又称为“字段”
- 表中的每一行数据，相当于jave中的对象
- 函数：类似于java的方法
单行函数
length
concat
substr
instr
trim
upper
lower
ipad
read
replace
一、字符函数
- length 获取参数数值的字节字数
- concat 拼接字符串
- upper、lower 转大写小写
某个函数的返回值可以充当另一个函数的参数
- substr、substring 截取字符串
SELECT CONCAT(UPPER(SUBSTR(LAST_NAME,1,1)),'_',LOWER(SUBSTR(LAST_NAME,2))) OUT_PUT FROM EMPLOYEES;
- instr 返回子串第一次出现的索引，如果找不到返回0
- trim 去掉前后空格
SELECT LENGTH(TRIM(' 王思扬 ')) AS OUT_PUT;
SELECT TRIM('A' FROM 'AAAA王思扬AAAAA');
- lpad 用指定的字符实现左填充指定长度
SELECT LPAD('殷素素',2,'*') AS out_put;
- rpad 用指定的字符实现右填充指定长度
- replace 替换
二、数学函数
- round 四舍五入
- ceil 向上取整，返回>=该参数的最小整数
- floor 向下取整，返回<=该参数的最大整数
- truncate 截断
- mod 取余 公式： a -⌊ a/b⌋ * b
三、日期函数
- now 返回当前系统日期+时间
- curdate
- curtime
- YEAR MONTH 年月日时分秒
- str_to_date 将字符通过指定的格式转换成日期
- date_format 将日期转换成字符
四、其他函数
- VERSION();
- DATABASE();
- USER();
五、流程控制函数
-
if函数； if else的效果
-
case函数
case 要判断的字段或表达式 when 常量1 then 要显示的值1或语句2 when 常量2 then 要显示的值2或语句2 ··· else 要显示的值n或语句n; end SELECT salary 原始工资, department_id, CASE department_id WHEN 30 THEN salary*1.1 WHEN 40 THEN salary*1.2 WHEN 50 THEN salary*1.3 ELSE salary END AS 新工资 FROM employees;case when 条件1 then 要显示的值1或者语句1 when 条件2 then 要显示的值2或者语句2 ··· else 要显示的值n或语句n end SELECT salary, CASE WHEN salary>20000 THEN 'A' WHEN salary>15000 THEN 'B' WHEN salary>10000 THEN 'C' ELSE 'D' END
分组函数
功能：用作统计使用，又称为聚合函数或统计函数或组函数
分类：
sum 求和 、avg 平均数、max 最大值、 min 最小值 、 count 计算个数
count函数的详细介绍
匹配多列中值不为null或者0的都+1 主要是跟表的性质有关，一般使用count(*)来做统计
特点：
1.分组查询中的筛选条件分为两类
数据源 位置 关键字
分组前筛选 原始表 group by 子句的前面 where
分组后筛选 分组后的结果集 group by 子句的后面 having
①分组函数做条件肯定是放在having子句中
②能用分组前筛选的，就有限考虑分组前筛选
2.group by 子句支持单个字段分组，多个字段分组
3.也可以添加排序(放在最后)
分组查询
语法：
select 分组函数,列 (要求出现在group by的后面)
from 表
【where 筛选条件】
group by 分组列表
【having 分组后的筛选】
【order by 子句】
注意：
查询列表必须特殊，要求是分组函数和group by后出现的字段
连接查询
含义：又称多表查询，当查询的字段来至多个表时，就会用到连接查询。
笛卡尔乘积现象：表1 有m行，表2有n行，结果=m*n行
发生原因：没有有效的连接条件
如何避免：添加有效的连接条件
sql92语法
内连接
1.等值
select 查询列表
from 表1 别名，表2 别名
where 表1.key=表2.key
【and 筛选条件】
【group by 分组字段】
【having 分组后的筛选】
【order by 排序字段】
特点：
①一般为表起别名
②多表的顺序可以调换
③n表连接至少需要n-1个连接条件
④等值连接的结果是多表的交集部分
2.非等值
select 查询列表
from 表1 别名，表2 别名
where 非等值连接条件
【and 筛选条件】
【group by 分组字段】
【having 分组后的筛选】
【order by 排序字段】
3.自连接
select 查询列表
from 表 别名1，表 别名2
where 等值连接条件
【and 筛选条件】
【group by 分组字段】
【having 分组后的筛选】
【order by 排序字段】
外连接
4.左外
5.右外
6.全外
交叉连接
sql99语法
语法：
select 查询列表
from 表1 别名 【连接类型】
join 表2 别名 on 连接条件
【where 筛选条件】
【group by 分组】
【having 筛选条件】
【order by 排序列表】
分类：
内连接：inner语法：
select 查询列表
from 表1 别名
inner join 表2 别名 表2也可以在括号内加上子查询
on 连接条件
等值
特点：
①添加排序、分组、筛选
②inner可以省略
③筛选条件放在where后面，连接条件放在on后面，提高分离性，便于阅读
④ 和sql92语法一样都是查询表的交集结果
非等值
自连接
外连接：
应用场景：用于查询一张表中有，另一个表没有的记录。
特点：
1、外连接的查询结果为主表中的所有记录
如果从表中有和它匹配的，则显示匹配的值
如果从表中没有和它匹配的，则显示null
外连接查询结果=内连接结果+主表中有而从表没有的记录
2、左外连接，left join左边的是主表
右外连接，right join右边的是主表
3、左外和右外交换连个表的顺序，可以实现同样的效果
4、全外连接=内连接的结果+表1中有但表2没有的+表2中没有但表1中没有的
左外 left【outer】
右外 right【outer】
全外 full【outer】
交叉连接：cross
笛卡尔乘积
子查询
含义：
出现在其他语句中的select语句，称为子查询或内查询
外部的查询语句，称为主查询或外查询
分类：
按子查询出现的位置
- select后面
- 仅仅支持标量子查询
- from后面
- 表子查询
- where或having后面
- 标量子查询
- 列子查询==
- 行子查询
- exists
- 表子查询
按结果集的行数列不同：
- 标量子查询（结果集只有一行一列）
- 列子查询（结果集只有一列多行）
- 行子查询（结果集有一行多列）
- 表子查询（结果集一般为多行多列）
一、where或having后面
标量子查询（单行子查询）
案例1：谁的工作比Abel高
①查询Abel的工资
SELECT salary
FROM employees
WHERE last_name = 'Abel'
②查询员工的信息，满足 salary>①结果
SELECT *
FROM employees
WHERE salary>(SELECT salary
FROM employees
WHERE last_name = 'Abel'
);
案例2：返回job_id与141员工相同，salary比143号员工多的员工 姓名，job_id 和工资
①查询141好员工job_id
SELECT job_id
FROM empoyees
WHERE employee_id = 141
② 查询143号员工的salary
SELECT salary
FROM empoyees
WHERE employee_id = 143
③查询员工的姓名，job_id 和工资 要求job_id=①并且salary>②
SELECT laste_name,job_id,salary
FROM employees
WHERE job_id = (SELECT job_id
FROM empoyees
WHERE employee_id = 141
) AND salary>(SELECT salary
FROM empoyees
WHERE employee_id = 143
);
案例3：返回工资工资最少的员工的last_name,job_id和salary
①查询公司的最低工资
SELECT MIN(salary)
FROM employees
②查询last_name,job_id和salary 要求salary=①
SELECT last_name,job_id,salary
FROM employees
WHERE salary=(
SELECT MIN(salary)
FROM employees
);
案例4：查询最低工资大于50号部门最低工资的部门id和其最低工资
①查询50号部门的最低工资
SELECT MIN(salary)
FROM employees
WHERE department_id = 50
②查询每个部门的最低工资
SELECT MIN(salary),department_id
FROM employees
GROUP BY department_id
③ 在二的基础上筛选②，满足min(salary)>①
SELECT MIN(salary),department_id
FROM employees
GROUP BY department_id
HAVING MIN(salary)>(
SELECT MIN(salary)
FROM employees
WHERE department_id = 50
);
④子查询的执行优先于主查询执行，著查询的条件用到子查询的结果
列子查询寻，一般搭配着多行操作符使用
2. 列子查询（多行子查询）
**多行操作符：**
"IN:在范围内的值，只要有就true
ALL: 与子查询返回的所有值比较为true 则返回true
ANY/SOME:与子查询返回的任何值比较为true 则返回true"
案例1：返回location_id是1400或1700部门中的所有员工姓名
①查询location_id是1400或1700的部门编号
SELECT department_id DISTINCT去重
FROM departments
WHERE location_id IN(1400,1700)
②查询员工姓名，要求部门号是①列表中的某一个
SELECT last_name
FROM employess
WHERE department_id(
SELECT department_id
FROM departments
WHERE location_id IN(1400,1700)
);
- 行字查询（多列多行）
特点：
①子查询放在小括号内
②子查询一般放在条件右侧
③标量子查询，一般搭配着当行操作符使用 "> < <= >= = <>"
分页查询
应用场景：当要显示的数据，一页显示不全，需要分页提交sql请求
语法：
select 查询列表 7
from 表 1
【join type】 join 表2 2
on 连接条件 3
where 筛选条件 4
group by 分组字段 5
having 分组后的筛选 6
order by 排序的字段 8
limit offset,size 9
offset要显示条目的起始索引（从0开始）
size要显示的条目个数
select * from employees limit 0,5;
DDL
数据库定义语言，对库和表的管理
- 创建：create
create database 库名; - 修改：alter
- 删除：drop
drop database 库名;
创建表
create table 表名(
列名 列的类型 【(长度) 约束】,
列名 列的类型 【(长度) 约束】,
列名 列的类型 【(长度) 约束】,
列名 列的类型 【(长度) 约束】,
...
列名 列的类型 【(长度) 约束】
)
修改表名
修改列的类型或约束
添加心列
删除列
修改表名
复习sql查询语句
SELECT
last_name,
department_id,
salary*12*(1+IFNULL(commission_pct,0)) AS 年薪,
FROM
employees;
- 一般白勺的后面就是要查询的东西，白勺的前面是条件
- or或者等于取并集
DQL查询语言
基础查询语法
select 查询列表
from 表明;
特点：1.查询列表可以说字段、常量、表达式、函数，也可以多个
2.查询结果是一张虚拟表
3.字符型和日期型的常量值必须用单引号引起来，数值型不需要
4.简单的数学运算 > < <= >= = <>
distinct
select 函数名(实参列表);
+
select 数值+数值 直接运算
select 字符+数值 先试图转换字符为数值，成功的话就运算，否则转换成0
select null+值 都为null
concat函数：拼接函数
select concat(字符1,字符2,) 多个字段显示为一行
ifnull函数：判断某字段或表达式是否为null，如果为null返回指定的值，否则返回原本的值
select ifnull(commission_pct,0) from employees;
isnull函数 为null返回1=是，不为null返回0=否
条件查询
select 查询列表 3
from 表明 1
where 筛选条件 2
筛选条件分类
1、简单条件运算符
> < = <> != <=> >= <=
2、逻辑运算符
&& and
|| or
! not
3、模糊查询
like:一般搭配通配符使用，可以判断字符型和数值型
select * from employees where department_id like '1__';
通配符： % 任意多个 _任意单个
between and
in
is null /is not null
排序查询
语法：
select 查询列表 3
from 表 1
【where 筛选条件】 2
order by 排序列表 【asc|desc】默认升序 4
特点：1. order by 后面支持表达式、单个字段、别名、函数、多个字段排序。
2. 多个字段排序的关系：先排第一个字段、然后再排
3. order by 一般都放在查询语句的后面，limit字句除外
select last_name,salary
from employees
where salary not between 8000 and 17000 | NOT(salary between 8000 and 17000)
order by salry DESC;
常见函数
函数：类似于java的方法，将一组逻辑语句封装在方法中，对外暴露方法名
1. 隐藏了实现细节 2. 提升代码重要性
调用：select 函数名(实参列表) 【from 表】;
特点：叫什么，实现什么效果。
分类：
1. 当行函数
如 concat、length、ifnull等
2. 分组函数，又称为统计函数、聚合函数。
做统计使用
