5.13上午数据库学习笔记

模糊查询与分组查询

增删改语句

insert 语句

INSERT  [INTO]  table_name  [(COLUMN1 [,COLUMN2 [,......]])]
VALUES (VALUE1 [,VALUE2 [,......]);

省略列名：前提是按顺序插入所有列，值和列要一一对应一次录入多行数据

INSERT  [INTO]  table_name  [(COLUMN1 [,COLUMN2 [,......]])]
VALUES 
(VALUE1 [,VALUE2 [,......]),
(VALUE1 [,VALUE2 [,......]),
(VALUE1 [,VALUE2 [,......]);

#在innodb存储引擎下，会到自增断层

INSERT  [INTO]  table_name  [(COLUMN1 [,COLUMN2 [,......]])]
select VALUE1,VALUE1,[,......] union
select VALUE1,VALUE1,[,......] 

update 语句

UPDATE table_name SET 
COLUMN1={}, COLUMN2={}......
WHERE<表达式>

delete 删除语句

DELETE FROM table_name WHERE <表达式>

删除表中的数据：

delete: 根据条件删除，逐行删除。

truncate:整表删除，无法设定条件，效率高，自增列会重新计数，无法恢复数据；无法直接删除有外键引用的表。

#删除数据
#以数据行为单位进行逐行删除,删除速度较慢
# 安全，可以记录日志，需要的时候可以通过日志进行恢复
delete from 表 [where 条件]
#truncate 截断 、截取 
# 直接删除整张表中的数据，相当于先把物理表删了，然后又创建了一个新表# 执行效率高，但是无法设定条件
# 自增列会重新计数
truncate table 表名;

级联

涉及到有主外键关联的数据表：

1、删除数据时：需要先删外键表，再删主键表

2、更新数据时：

3、录入数据时：先录入主键，再录入外键

级联：当更新(删除）主键表中的数据时，外键表中引用的数据会自动更新(删除）

cascade:删除主键时自动删除外键表中引用的数据，修改时自动更新外键表中引用的数据

# on delete CASCADE  ：开启 删除 级联
# on update CASCADE : 开启 更新 级联
alter table emp 
add CONSTRAINT fk_deptno FOREIGN key (deptno)
REFERENCES dept(deptno)
on delete CASCADE 
on update CASCADE;

查询

select 列名1,列名2 
# 查询列from 表名 
# 从 某张表中
#可选where 条件表达式
group by 列名
having 条件表达式
order by 列名
limit ?,? 分页

说明：

我们想查询该表的那些列 可以直接写列名 ，顺序可以自定义

SELECT `name`,age,address,sex from student;

可以使用 select * 查询所有列。 *号代表所有列。但是这种查询效率低。

SELECT * from student;

列和表都可以起别名

SELECT s.`name`,s.age+3 a,s.address from student s;

查询中列支持运算

SELECT s.`name`,s.age+3 a,s.address from student s;

去重查询

去除查询结果中的重复数据

select distinct 列名 from 表名;

条件查询where语句

SQL语句中的条件表达式：查询条件、判断条件、修改条件

条件运算符：=    > < >= <= 不等：<> 和  !=

算术运算符：+ - * /     %（mod)    不支持：++  -- 复合赋值 += -=

逻辑运算符：and(&&) , not(!) , or(||)

• 多条件查询 where and
• 范围查询: between and
• 范围查询: in 的使用

模糊查询

正则通配符

正则模式REGEXP操作符

“.” 匹配任意单个的字符。

“[ ]” 匹配在[]内的任意一个字符

［^］ 匹配不在[]内的任意一个字符

“ * ” 匹配零个或多个在它前面的字符

“+” 匹配+前面的字符1次或多次

“{n}” 匹配前面的字符至少n次

“^” 匹配文本的开始字符

“$” 匹配文本的结尾字符

//匹配名字以a开头 c结尾的名字
select * from student where `name` REGEXP'^a.c$';

模糊查询like

• 与'%'结合使用：匹配任意长度任意内容

• 与'_'结合使用：匹配一个长度任意内容

'%a' //以a结尾的数据

'a%' //以a开头的数据

'%a%' //含有a的数据,三位且中间字母是a的

'_a' //两位且结尾字母是a的

'a_' //两位且开头字母是a的

%：表示任意 0 个或多个字符。可匹配任意类型和长度的字符，有些情况下若是中文，请使用两

个百分号（%%）表示。

_：表示任意单个字符。匹配单个任意字符，它常用来限制表达式的字符长度语句。

排序操作

order by 列名 asc 默认升序 desc 降序, 列名 升序,降序...

分页查询limit(要和排序结合使用)

每次我们直接 select * from t_people 会将数据全部查询出来，如果一张表数据量比较大 ，此时 容易爆炸，所以真正开发的时候，都是只查一部分数据，所以都是进行分页操作的。

聚合函数(汇总函数)

就是对某一列的数据进行运算获取某些列的最大值、最小值、平均值等统计分析信息，有时候还需要计算出究竟查询到多少行数据项。分别有：sum(列),count(注意非空值) 计数、max(列)、min(列)、avg(列)，count(空值)不计入数据。

count(列名/*) 如果列名存在null值，不计入总数。

分组查询

group by 列名    是将 某一列 相同数据的行当成一组

having   对分组后的数据做过滤，注意分组查询添加条件  group by  列名  having  条件；

where 用在分组之前的条件过滤

多表查询（重难点）

为什么要多表联查

数据来源多张表的时候需要进⾏关联查询 。在实际开发过程中很多时候需要同时使⽤多个表中的数据

多表关联前提: 表之间有对应的数据列或外键约束。

内连接（等值连接:where,[inner] join）

内连接和外连接一样都是最常用的连接查询，它根据表中共同的列进行匹配，特别是两个表存在主外键关系时，通常会使用到内连接查询 内连接也称为等值连接，返回的结果集是两个表中所有相匹配的数据，舍弃不匹配的数据。

1）where等值连接（隐式内连接）：简单，但是碰到问题不好调。

select 列名...... 
from 表1，表2，表3，表4....
where 关联条件、还可以过滤条件  

2）inner join内连接（显式内连接）on ，扩展方便

select 列名,......
from 表1 
[inner] join 表2 on 关联条件     
[inner] join 表3 on 关联条件     
[inner] join 表4 on 关联条件      
where 过滤条件;

注意:内连接时，如果表中的数据找不到另外一个表的对应记录，则数据不会被查询出来

外连接

外连接是至少返回一个表中的所有记录，根据匹配条件有选择性地返回另一张表的记录 外连接就是在满足表连接关系的情况下不但可以查找出匹配的数据，而且还可以包含左表，右表或是两表中的所有的数据行

• 左外连接

左外保证左表完整、如果不匹配null值填充

SELECT * from 表1 
LEFT JOIN 表2 
ON 关联条件;

• 右外连接

右外保证右表完整、如果不匹配null值填充

SELECT * from 表1 
RIGHT JOIN 表2 
ON 关联条件;

交叉连接 cross join

交叉联接返回左表中的所有行，左表中的每一行与右表中的所有行再一一组合，相当于两个表“相乘”左表中每一行的数据都和右表组合一次，相当于两表的排列组合，返回的条数其实就是两表的成绩交叉连接没有关联条件

#交叉连接(笛卡尔积): 两表相乘，没有关联条件
select 	*from studentInfo s
cross join classInfo c
#一般在考勤业务中使用#生成一个学生的31天记录

自关联

MySQL函数

https://www.runoob.com/mysql/mysql-functions.html