数据库查询

第一节：增删改语句

1、insert语句

语法

INSERT [INTO] table_name [(COLUMN1 [,COLUMN2 [,......]])]

VALUES (VALUE1 [,VALUE2 [,......]);

省略列名：前提条件是按顺序插入所有列

值要和列 一一对应（个数、类型、顺序）

一次录入多行：

语法：

INSERT [INTO] table_name [(COLUMN1 [,COLUMN2 [,......]])]

VALUES

(VALUE1 [,VALUE2 [,......]),

(VALUE1 [,VALUE2 [,......]),

(VALUE1 [,VALUE2 [,......])

2、update语句

语法：

UPDATE table_name SET

COLUMN1={}, COLUMN2={}......

WHERE

3、删除

删除表中的数据：

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

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

#删除数据

#[可选的]

#以数据行为单位进行逐行删除,删除速度较慢

# 安全，可以记录日志，需要的时候可以通过日志进行恢复

delete from 表 [where 条件]

#truncate 截断 、截取

# 直接删除整张表中的数据，相当于先把物理表删了，然后又创建了一个新表

# 执行效率高，但是无法设定条件

# 自增列会重新计数

truncate table 表名;

4、级联

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

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

第二节：查询

sql调优/优化！

语法:

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

特点：

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

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

②：如果需要所有列都被查询 ，可以使用 select * ， *号代表所有列

SELECT * from student;
* 匹配所有
速度慢，一般项目中使用列名直接匹配

③：列和表都可以起别名（别名只有在起别名的那句SQL中才能识别此时表的别名 在多表查询的时候需要用 到）。

SELECT s.`name`,s.age+3 a,s.address from student s;
此时 a 就是 s.age+3这一列的别名 s就是student 表的别名

④：查询中列支持运算

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

1、去重查询

特点：尽量少用，会影响性能

select distinct 列名 from 表名;

2、条件查询 where 语句

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

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

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

逻辑运算符：

and（&&）：并且 ：多个条件同时成立 就true 否则返回false

not（!）：非 :取反

or（||）:或 ，多个条件只要有一个成立，结果：true ，都不成立：false

①：条件运算符

②：多条件查询

 ③：范围查询 ;

and 运算符连接条件

④： between a and b; 根据某个字段值的区间范围来查询符合条件的记录，这个范围内有多少数据并不明确

-- 查询年龄19--22之间的学生
select * FROM studentinfo where age BETWEEN 19 and 21;
-- 查询1990-1991的学生
select * from studentinfo where birthday >='1990-01-01' and birthday<='1991-12-31';
SELECT * from student where birthday BETWEEN '2017-03-03' and '2020-03-03';

⑤：in (在。。内)；用来匹配某个字段的值是几个可数的选项值之一

不是范围，确定的值。

SELECT * from student where age in(15,17,20);
-- 查询2班的周口的学生 或者3班的郑州的学生
SELECT * from student where (classid,address) in ((2,'周口'),(3,'郑州'))

3、模糊查询

3.1 通配符 正则模式 REGEXP 操作符

• “.” 匹配任意单个的字符。
• “[ ]” 匹配在[]内的任意一个字符
• ［^］ 匹配不在[]内的任意一个字符
• “ * ” 匹配零个或多个在它前面的字符
• “+” 匹配+前面的字符1次或多次
• “{n}” 匹配前面的字符至少n次
• “^” 匹配文本的开始字符
• $” 匹配文本的结尾字符

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

  3.2 模糊查询 like 关键字

• 使用通配符

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

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

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

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

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

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

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

案例：

4、排序操作

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

-- 排序操作

-- 查询所有学生，按照年龄排序

SELECT * from student ORDER BY age DESC;

-- 查询所有的学生按照年龄升序，按照班级降序。(在年龄相同的基础上，按照班级降序)

SELECT * from student ORDER BY age asc ,classid desc;

-- 查询姓老的学生，按照年龄升序，按照班级降序

SELECT * from student where name LIKE '老%' ORDER BY age ASC,classid desc;

-- 按照birthday 排序

SELECT * from student ORDER BY birthday;

5、聚合函数和分组查询

5.1 聚合函数

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

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

-- 查询学生的总数

SELECT COUNT(*) from student;

max(列名) 取列名中的最大值

-- 查询年龄最大的学生

 SELECT max(age) from student;

min(列名) 取列中的最小值

-- 取最小年零的学生

SELECT MIN(age) from student;

sum(列名) 求和

-- 全班所有人的年龄和

SELECT sum(age) from student;

avg(列名) 求平均值

-- 求年龄平均值

SELECT avg(age) from student;

5.2 分组查询

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

案例：

-- 分组查询
-- 求出每个班级的平均年龄 按照班级分组
SELECT classid,avg(age) from student GROUP BY classid;
SELECT address,avg(age),COUNT(*) renshu from student GROUP BY address;

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

-- 查询出所有 人数大于5 的班级
SELECT count(*),classid from student GROUP BY classid HAVING count(*)>5;

6、分页查询 limit

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

例如：select * from student limit 索引 , 查询的个数（3，5）；

-- limit 分页查询 0代表索引 第一条数据 5 代表长度 代表查询5条数据

SELECT * from student LIMIT 0,5;

综上所有：常⽤的查询语法：

select 列名 from 表名 where 条件 group by 分组列名 having 过滤条件 order by 排序列名 limit start,length;

7、多表联查 （贼重点）

7.1 为什么需要多表联查

数据来源多张表的时候需要进⾏关联查询

在实际开发过程中很多时候需要同时使⽤多个表中的数据

关联查询：⽬的：我需要数据来源多张表，⼀张⼀张查太麻烦了，所以出现了关联查询。

7.2 内连接 inner join

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

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

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 过滤条件;

7.3 外连接

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

左外连接 left join on条件

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

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

-- 学生表跟班级表左外连接查询
SELECT * from tab_studentinfo stu LEFT JOIN tab_class tc
on stu.classID=tc.classID;

右外连接 right join on条件

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

SELECT * from 表1 right JOIN 表2 ON 条件;

-- 学生表跟班级表右外连接查询
SELECT * from tab_studentinfo stu RIGHT JOIN tab_class tc
on stu.classID=tc.classID;

 7.4 交叉连接（笛卡尔乘积) cross join

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

a[a,b,c]

b[1,2,3]

a*b = {a1,a2,a3,b1,b2,b3,c1,c2,c3}

SELECT * from 表1 CROSS JOIN 表2;

-- 学生表跟班级表交叉连接查询
SELECT * from tab_studentinfo stu CROSS JOIN tab_class tc;