Sql基础

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;

我们先分析，上面语句执行执行顺序

1. FROM 员工表，拿到数据源
2. WHERE 奖金 IS NOT NULL，筛选出有奖金的员工
3. GROUP BY 工种编号，按照工种分好组
4. HAVING MAX(工资)>12000 ，对结果集再一次筛选，最后得到了每个工种 有奖金的 员工的 最高工资 >12000的 工种编号 和 最高工资
5. 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 表名;

完结！这是一篇看哔哩哔哩视频的一篇学习笔记