文章目录
Sql学习笔记
sql语句对数据库的操作无非就创建(Create)、 读取(Read)、 更新(Update)和删除(Delete),也就是我们的CRUD操作,其中最重要的读取操作
查询语句
关键字作用和执行顺序(非常重要):
- (7) SELECT (输出)
- (8) DISTINCT <select_list>(去重)
- (1) FROM <left_table>(获取数据源)
- (3) <join_type> JOIN <right_table>(连接)
- (2) ON <join_condition> (连接条件)
- (4) WHERE <where_condition>(过滤)
- (5) GROUP BY <group_by_list>(分组)
- (6) HAVING <having_condition>(过滤)
- (9) ORDER BY <order_by_list>(排序)
- (10) LIMIT <limit_number>(限定个数)
1.基础查询
SELECT 查询列表 FROM 表名
/*
查询列表可以是:表中的字段、常量值、表达式、函数
*:表示表中的全部字段
100+10:表达式,可以select 100+10
100:常量,可以select 100
*/
字段起别名:
- 字段名+AS关键字+别名
- 字段名+直接空格+别名
selet name as "姓名"
from stu
selet name "姓名"
from stu
/*
查询出来的字段名就会改为姓名
*/
DISTINCT(去重)
SELECT DISTINCT classid
FROM stu
/*
案例:查询学生表涉及到的所有的班级编号,我和你同一个班级,班级编号一样所以去重
*/
2.条件查询
SELECT 查询列表 FROM 表名
WHERE 筛选条件
算术筛选
SELECT *
FROM stu
WHERE classid=1
#查看班级编号为1的同学
/*
运算符表达式
=
!=
<
>
*/
逻辑筛选
SELECT *
FROM stu
WHERE classid=1 and id =1
# 查看班级编号为1且id=1的同学信息
/*
逻辑表达式
not:非
and:与
or:或
*/
模糊筛选
SELECT *
FROM stu
WHERE name LIKE '%a%'
# 查询学生表中名字包含a的学生信息
SELECT *
FROM stu
WHERE classid BETWEEN 0 AND 2;
# 查询班级号0到2的学生信息,包括0和2班
SELECT *
FROM stu
WHERE classid IN(0,1,3);
# 查询班级号0,1,3班的学生信息
SELECT *
FROM stu
WHERE classid IS NULL;
# 查询没有加入班级的学生信息
/*
关键字
like配合 通配符 使用
%:任意多个字符
_:任意单个字符
between and
包含临界值
两个临界值不能随便调换位置
in
in列表的值类型必须一致或兼容
is null
=或<>不能判断null值
is null 或 is not null 可以判断
*/
3.排序查询
select 查询列表
from 表
【where 筛选条件】
order by 排序列表 【asc|desc】
/*
order by语句是放在倒数第二的位置
asc代表升序
desc代表的是降序
默认是升序
*/
SELECT *
FROM stu
ORDER BY classid DESC;
# 查询学生信息,要求班级从高到低排序
SELECT *
FROM stu
ORDER BY classid DESC,id;
# 查询学生信息,要求先按班级从高到低排序,然后班级相同的学生按id升序排序
4.函数
select 函数名(参数列表) 【from 表】
字符函数
# length 获取参数值的字节个数
SELECT LENGTH('classid' )
# concat拼接字符串
SELECT CONCAT(last_name, '_',first_name) 姓名 FROM stu;
数字函数
#round 四舍五入
SELECT ROUND(1.55) #返回2
#ceii 向上取整,返回>=该参数的最小整数
SE工ECT CEIL(-1.02) # 返回-1
#floor 向下取整,返回<=该参数的最大整数
SELECT FLOOR(-9.99) # 返回-10
日期函数
# now 返回当前系统日期+时间
SELECT NOW();
# curdate 返回当前系统日期,不包含时间
SELECT CURDATE( ) ;
#curtime 返回当前时间,不包含日期
SELECT CURTIME();
# str_to_date将日期格式的字符转换成指定格式的日期
STR_TO_DATE('9-13-1999','%m-%d-%Y')
# date_format将日期转换成字符
DATE_FORMAT(2018/6/6','%Y年%m月%d日')
分组函数
功能:用作统计使用,又称为聚合函数
# 下面函数都会忽略null
# sum求和,求全部人的工资总和,值的和
SELECT SUM(salary) FROM employees;
# avg平均值,求人均工资
SELECT AVG (salary) FROM employees;
# max最大值,求最大工资的人
SELECT MAX(salary) FROM employees;
# min最小值,求最小工资的人
SELECT MIN (salary) FROM employees;
# count计算个数,求获得工资的人数,行的和(不是总行数和,如果有人工资为null是不会被计算的)
SELECT COUNT (salary) FROM employees;
和分组函数一同查询的字段要求是group by后的字段,因为分组函数是作用于group by分组好后的各组内的其他字段,比如查各部门内平均工资,select查询列表只能是部门名或分组函数,按题义应该是avg(salary)
5.分组查询
select 查询列表
from 表
【where 筛选条件】
group by 分组的列表
【order by 子句】
# 查询列表必须特殊,要求参数必须是分组函数或group by后出现的字段
# 查下每个班级下年龄最大的学生
SELECT classid,MAX(age)
FROM stu
GROUP BY classid
图示:
先按 GROUP BY 分组,然后在每组里面进行分组函数的运算
HAVING 子句使用
#案例2:查询 每个工种 有奖金的 员工的 最高工资 >12000的 工种编号 和 最高工资
# 分析后第一步先查询 每个工种 有奖金的 员工的 最高工资,查询目标是工种编号和最高工资
# 第二步根据第一步得到的结果集去添加筛选条件,也就是最高工资>12000
select Max(工资),工种编号
FROM 员工表
WHERE 奖金 IS NOT NULL
GROUP BY 工种编号
HAVING MAX(工资)>12000;
我们先分析,上面语句执行执行顺序
- FROM 员工表,拿到数据源
- WHERE 奖金 IS NOT NULL,筛选出有奖金的员工
- GROUP BY 工种编号,按照工种分好组
- HAVING MAX(工资)>12000 ,对结果集再一次筛选,最后得到了每个工种 有奖金的 员工的 最高工资 >12000的 工种编号 和 最高工资
- select Max(工资),工种编号,按照1234筛选得出结果集
数据源 | 位置 | 关键字 | |
---|---|---|---|
分组前筛选 | 原始表 | group by子句前面 | where |
分组后筛选 | 结果集 | group by子句后面 | having |
6.连接查询
内连接
- 等值连接
- 非等值连接
- 自连接
外连接
- 左外连接
- 右外连接
- 全外连接
交叉连接
等值连接(Sql92语法)
# 我们要查询女孩和她们的男朋友,这里涉及了两张表的查询,要建立有效的连接
SELECT name,boyName
FROM 女孩表,男孩表
WHRER 女孩表.boyName=男孩表.id
非等值连接(Sql92语法)
# 查询学生的年龄和处于的年龄时期,也就是青年时期是18-25,老年是>70
SELECT age,age_level
FROM t_stu s,t_age a
WHERE age BETWEEN a.'lowest_age' AND a.'highest_age'
拿年龄去对比年龄时期表中的一个年龄段的临界值,如果符号则返回年龄和该年龄所处的时期
自连接(Sql92语法)
如果要从一张用户表(老师和学生都在用户表)里找到学生的老师,就可以用自连接,把一张表看作两张表去连接查询
SELECT 学生.name,老师.name
FROM 用户表 as 学生,用户表 as 老师
WHERE 学生.teach_id = 老师.id
Sql92语法由于连接条件和筛选条件有点混淆,可读性不高,我们推荐用Sql99语法
Sql99
SELECT 查询列表
FROM 表1 别名
{连接类型} JOIN 表2 别名
ON 连接条件
【WHERE 筛选条件】
【group by 分组】
【HAVING 结果集筛选条件】
【ORDER BY 排序列表】
/*
{连接类型}
内连接(inner)
外连接
左外(left 【outer】)
右外(right 【outer】)
全外(full 【outer】)
交叉连接(cross)
*/
- 内连接(inner)
# 我们要查询女孩和她们的男朋友
SELECT name,boyName
FROM 女孩表
INNER JOIN 男孩表
ON 女孩表.boyName=男孩表.id
这里的意思就是先拿女孩表和男孩表进行 内连接 ,连接条件是(等值连接)女孩表.boyName=男孩表.id,然后从连接结果SELECT,这里非等值连接和自身连接都差不多一个原理
- 外连接
左右外连接分主从表
- 左外连接,left join 左边的是主表
- 右外连接,right join 右边的是主表
SELECT *
FROM `stu`
LEFT JOIN cla
ON stu.classid = cla.classid
学生表(主表):
老师表(从表):
左外连接结果:
过程:拿主表的每一行去匹配从表,如果匹配到则输出,匹配不到保留主表数据,从表全为空
- 全外连接
- 交叉连接
两表的交叉连接就是他们的笛卡儿积
7.子查询
出现在其他语句中的SELECT语句称为子查询:
- 标量子查询(结果集只有一行一列)
- 列子查询(结果集只有一列多行)
- 行子查询(结果集有一行多列)
- 表子查询(结果集一般为多行多列)
- 出现在where或having后面(支持 标量、列、行 子查询)
# 查询我的工资(标量子查询)
SELECT 工资
FROM 员工表
WHERE name=我
# 查询满足 工资大于 我的 员工
SELECT *
FROM 员工表
WHERE 工资>(
SELECT 工资
FROM 员工表
WHERE name=我
);
- 出现在select后面
# 查询每个部门下的员工个数
SELECT 部门,(
SELECT COUNT(*)
FROM 员工表
WHERE 部门表.部门id=员工表.部门id
) 个数
FROM 部门表
- 出现在from后面(一般表子查询)
8.分页查询
SELECT 查询列表
FROM 表
limit offset,size
/*
offset 要显示条目的起始索引(从0开始的)
size 要显示的条目个数
*/
8.联合查询
/*
# union联合合并:将多条查询语句的结果合并成一个结果
查询语句1
union
查询语句2
# 查看在1班的同学或姓名为小李的同学,满足其一即可
SELECT 同学
FROM 班级表
WHERE classid=1
UNION
SELECT 同学
FROM 班级表
WHERE name=小李
插入语句
语法:
insert into 表名 (列名,...) values(值1,... );
/*
字符型要单引号引起了
列名要和值对应
可以省略(列名,...),代表全列名插入
*/
更新语句
# 1.单表修改
update 表名
set 列=新值,列=新值,...
where 筛选条件;
# 2.多表修改,先连接然后当成一张表去更新
update 表1 别名
{连接类型} join 表2 别名
on 连接条件
set 列=值,..
删除语句
# 方式一: delete
# 1、单表的删除【★】
delete from 表名 where 筛选条件
# 2、多表的删除【补充】
delete 表1或2
from 表1
{连接类型} join 表2
on 连接条件
where 筛选条件;
# 方式二: truncate,删除整张表
truncate table 表名;
完结!这是一篇看哔哩哔哩视频的一篇学习笔记