数据库Note

陌上小孩纸

已于 2024-03-09 17:01:52 修改

阅读量130

点赞数

分类专栏：中间件文章标签： mysql 数据库 sql

于 2023-01-04 11:34:19 首次发布

本文链接：https://blog.csdn.net/qq_27630263/article/details/128544558

版权

中间件专栏收录该内容

5 篇文章 0 订阅

订阅专栏

文章目录

SQL语句
Hive使用
- 1 查看表字段
优化方法
- 1 对于单条删除或者更新的操作，在delete和update后面加上limit 1
运维
- 1 删除表中上千万的大数据
1 创建用户
2 Mysql Workbench入库时间设置自动生成
3 Navicat使用问题记录
- 3.1 Navicat连接Oracle数据库时报错：`ORA-28547:connection to server failed, probable Oracle Net admin error`的解决方案

SQL语句

major
专业表：专业id、专业名称

mno	mname
1	计算机科学与技术
2	软件工程

stu
学生表：学生id、学生姓名、年纪、性别、专业id

sno	sname	age	sex	mno
sno	sname	age	sex	mno

cou
课程表：课程id、课程名称、学时、学分

cno	cname	ctime	ccredit
cno	cname	ctime	ccredit

sc
成绩表：学生id、课程id、成绩

sno	cno	grade
sno	cno	grade

1 建库、建表

建库：create database stuinfo
引库：use stuinfo
建表：

--major: mno、mname
create table major{
	mno int,
	mname varchar(20),
	primary key(mno)
}
--stu: sno、sname、age、sex、mno
create table stu{
	sno varchar(20) primary key,
	sname varchar(30) not null,
	age smallint,
	sex bit,
	mno int,
	foreign key(mno) references major(mno)
}
-- cou: cno、cname、ctime、ccredit
create table cou{
	cno varchar(20),
	cname varchar(30),
	ctime smallint,
	ccredit decimal(4,2),
	primary key(cno)
}
-- sc: sno、cno、grade
create table sc{
	sno varchar(20),
	cno varchar(20),
	grade decimal(5,2),
	primary key(sno, cno)
	foreign key(sno) references stu(sno)
}
-- 对sc增加一个外键
alter table sc add constraint fk_sc foreign key(cno) references cou(cno)

-- 删表
drop table temptable

2 增删字段、数据增删改

-- 学生表增加、删除qq字段
alter table stu add qq varchar(20)
alter table stu drop qq column qq

-- 增加信息
insert into major(mno, mname) values(1, '计算机科学与技术')
insert into major(mno, mname) values(2, '软件工程')
-- 0:女, 1:男
insert into stu values('2023211001', 'kk', 20, 0, null)
insert into stu values('2023211002', 'dd', 20, 1, 1)

-- 删除
delete from stu where sno = '2023211001'
-- 删除一个专业
delete from major where mno = 1,执行错误-外键冲突
-- 别的表有外键的先置为null
update stu set mno = null where mno = 1
delete from major where mno = 1,执行成功

-- 更新
update stu set sname = '康康' where sno = '2023211002'

补充

# 插入检索出来的数据
insert into mytable1(col1, col2) 
select col1,col2 from mytable2

# 将一个表的内容插入到一个新表
create table newtable as
select * from mytable

# truncate table 可以清空表，也就是删除所有行
# 使用更新和删除操作时一定要使用where子句，不然会把整张表的数据破坏。可以先使用select语句进行测试，防止错误删除。
truncate table mytable

3 单表查询

在这里插入图片描述

-- 查询全部学生信息
select * from stu
-- 查询全部学生的学号sno和姓名sname
select sno,sname from stu
-- 查询全部学生的姓名和出生年份
select sname,2023-age as birth_date from stu
-- 查询有选修课的学生学号（distinct 去重）
select distinct sno from sc  
-- 查询姓名是‘小十’的学生全部信息
select * from stu where sname = '小十'
select * from stu where sname like '小十'

-- 查询课号是‘20201’且学生成绩不低于80的学生学号
--（like 或 =，如果like后面的字符串不含有通配符（%，用于模糊查询），那么like等价于=）
select sno from sc where cno = '20201' and grade >= 80
select sno from sc where cno like '20201' and grade >= 80
-- 查询年龄在18-19之间的学生姓名、年龄
select sname,age from stu where age >= 18 and age <= 19
select sname,age from stu where age between 18 and 19

-- 查询专业号01、02、04的学生信息
select * from stu where mno = 1 or mno = 2 or mno = 4
select * from stu where mno != 3
select * from stu where mno in(1,2,4)
-- 查询专业号不是01、02、04的学生信息（not in）
select * from stu where mno != 1 and mno != 2 and mno != 4
select * from stu where mno not in(1,2,4)

-- 查询所有姓‘彭’的学生信息（模糊查询）
select * from stu where sname like '彭%'
-- 查询名字中第二个字为‘小’的学生信息（_：1个占位符，%：多个占位符）
select * from stu where sname like '_小%'
-- 查询名字中有‘小’的学生信息
select * from stu where sname like '%小%'

-- 查询有选课记录，没有考试成绩的选课信息
select * from sc where grade is null

补充

# distinct 相同值只会出现一次。它作用于所有列，也就是说所有列的值都相同才算相同
select distinct col1,col2 from mytable

# limit 限制返回的行数。可以有两个参数，第一个参数为起始行，从0开始；第二个参数为返回的总函数
# 返回前5行
select * from mytable limit 5
select * from mytable limit 0 5

# asc:升序 desc:降序
# 可以按多个列进行排序，并且为每个列指定不同的排序方式
select * from mytable order by col1 desc, col2 asc

# 通配符用在过滤语句中，但是它只能用于文本字段
# %: 匹配 >=0个任意字符
# _: 匹配 ==1个任意字符
# []: 可以匹配集合内的字符，例如[ab]将匹配字符a或者b。用脱字符^可以对其进行否定，也就是不匹配集合内的字符
# 使用like来进行通配符匹配，不要滥用通配符，通配符位于开头处匹配会非常慢
select * from mytable where col like '[^AB]%' --不以A和B开头的任意文本

4 order by、聚集函数、group by

-- 查询学生成绩，要求按照成绩由低到高（升序，默认升序）
select * from sc order by grade
-- 查询学生成绩，要求按照成绩由高到低（降序, desc）
select * from sc order by grade desc

-- count(*)：一个表统计有多少行
-- count(sno)：一个表sno值的个数（不为空）
-- sum、avg、max、min
-- 查询学生总人数
select count(*) from stu
-- 查询选修了课程的学生人数
select count(distinct sno) from sc
-- 查询选修了‘20201’课程的学生平均成绩
select avg(grade) from sc where cno = '20201'
-- 查询选修‘20201’课程的学生最高成绩
select max(grade) from sc where cno = '20201'

-- 求各个课程号以及相应的选修人数
select cno,count(sno) as num from sc group by cno
-- 查询平均成绩大于等于90的学生学号和平均成绩
select sno,avg(grade) from sc where avg(grade) >= 90, 执行错误，聚集函数不能出现在where中
select sno,avg(grade) from sc having avg(grade) >= 90, 执行错误，having需和group by联合使用
select sno,avg(grade) from sc group by sno having avg(grade) >= 90, 执行成功

# WHERE 过滤行，HAVING 过滤分组，行过滤应当先于分组过滤。
SELECT col, COUNT(*) AS num
FROM mytable
WHERE col > 2
GROUP BY col
HAVING num >= 2;
# 分组规定：
# 1 GROUP BY 子句出现在 WHERE 子句之后，ORDER BY 子句之前；
# 2 除了汇总字段外，SELECT 语句中的每一字段都必须在 GROUP BY 子句中给出；
# 3 NULL 的行会单独分为一组；
# 4 大多数 SQL 实现不支持 GROUP BY 列具有可变长度的数据类型。

补充：各个 DBMS 的函数都是不相同的，因此不可移植，以下主要是 MySQL 的函数。

# concat()用于连接两个字段。trim()用于去除首尾空格
select concat(trim(col1), '(', trim(col2), ')') as new_col from mytable

# 汇总
AVG()	返回某列的平均值，AVG() 会忽略 NULL 行。
COUNT()	返回某列的行数
MAX()	返回某列的最大值
MIN()	返回某列的最小值
SUM()	返回某列值之和

# 文本处理
LEFT()	左边的字符
RIGHT()	右边的字符
LOWER()	转换为小写字符
UPPER()	转换为大写字符
LTRIM()	去除左边的空格
RTRIM()	去除右边的空格
LENGTH()	长度
SOUNDEX()	转换为语音值，SOUNDEX() 可以将一个字符串转换为描述其语音表示的字母数字模式。

# 日期和时间处理
# 日期格式：YYYY-MM-DD
# 时间格式：HH:<zero-width space>MM:SS
ADDDATE()	增加一个日期（天、周等）
ADDTIME()	增加一个时间（时、分等）
CURDATE()	返回当前日期
CURTIME()	返回当前时间
DATE()	返回日期时间的日期部分
DATEDIFF()	计算两个日期之差
DATE_ADD()	高度灵活的日期运算函数
DATE_FORMAT()	返回一个格式化的日期或时间串
DAY()	返回一个日期的天数部分
DAYOFWEEK()	对于一个日期，返回对应的星期几
HOUR()	返回一个时间的小时部分
MINUTE()	返回一个时间的分钟部分
MONTH()	返回一个日期的月份部分
NOW()	返回当前日期和时间
SECOND()	返回一个时间的秒部分
TIME()	返回一个日期时间的时间部分
YEAR()	返回一个日期的年份部分
select now() -- 2023-07-08 17:58:49

# 数值出来
SIN()	正弦
COS()	余弦
TAN()	正切
ABS()	绝对值
SQRT()	平方根
MOD()	余数
EXP()	指数
PI()	圆周率
RAND()	随机数

5 多表查询

连接用于连接多个表，使用 JOIN 关键字，并且条件语句使用 ON 而不是 WHERE。
连接可以替换子查询，并且比子查询的效率一般会更快。
可以用 AS 给列名、计算字段和表名取别名，给表名取别名是为了简化 SQL 语句以及连接相同表。

1. 内连接
# 内连接又称等值连接，使用 INNER JOIN 关键字。
SELECT A.value, B.value
FROM tablea AS A INNER JOIN tableb AS B
ON A.key = B.key;
# 可以不明确使用 INNER JOIN，而使用普通查询并在 WHERE 中将两个表中要连接的列用等值方法连接起来。
SELECT A.value, B.value
FROM tablea AS A, tableb AS B
WHERE A.key = B.key;

2. 自连接
# 自连接可以看成内连接的一种，只是连接的表是自身而已。
# 一张员工表，包含员工姓名和员工所属部门，要找出与 Jim 处在同一部门的所有员工姓名。
# 子查询版本
SELECT name
FROM employee
WHERE department = (
      SELECT department
      FROM employee
      WHERE name = "Jim");
# 自连接版本
SELECT e1.name
FROM employee AS e1 INNER JOIN employee AS e2
ON e1.department = e2.department
      AND e2.name = "Jim";

3. 自然连接
# 自然连接是把同名列通过等值测试连接起来的，同名列可以有多个。
# 内连接和自然连接的区别：内连接提供连接的列，而自然连接自动连接所有同名列。
SELECT A.value, B.value
FROM tablea AS A NATURAL JOIN tableb AS B;

4. 外连接
# 外连接保留了没有关联的那些行。分为左外连接，右外连接以及全外连接，左外连接就是保留左表没有关联的行。
# 检索所有顾客的订单信息，包括还没有订单信息的顾客。
SELECT Customers.cust_id, Customer.cust_name, Orders.order_id
FROM Customers LEFT OUTER JOIN Orders
ON Customers.cust_id = Orders.cust_id;

-- 连接查询
-- 1 等值查询
-- 查询每个学生的信息和选修课程的信息
select stu.*,sc.* from stu,sc where stu.sno = sc.sno 
-- 查询选修‘20201’学生的姓名
select sname from stu,sc where stu.sno = sc.sno and sc.cno = '20201' 
-- 多表连接
-- 查询每个学生的信息和选秀课程的信息和学时
select stu.*,sc.*,ctime from stu,sc,cou where stu.sno = sc.sno and sc.cno = cou.cno
-- 2 左外连接
-- 查询所有的学生信息和选课信息，但是没有选修的学生需要显示出来
select stu.*,sc.* from stu left join sc on stu.sno = sc.sno
-- 查询每个专业的学生人数，假设每个专业都有人
select mno,count(sno) from stu group by mno
select mno,count(sno) from stu group by mno having mno between 1 and 4
-- 查询每个专业的学生人数，但是有的专业可能没有人
select major.mno,count(sno) from major left outer join stu on major.mno = stu.mno group by major.mno

-- 嵌套查询
-- 1 不相关嵌套查询（子查询不依赖父查询）
-- 查询选修‘20201’学生的姓名
select sname from stu,sc where stu.sno = sc.sno and sc.cno = '20201'
select sname from stu where sno in (select sno from sc where sc.cno = '20201'),当（）中返回结果只有1个时，in可以替换为=
-- 2 相关嵌套查询（将连接放在子查询里面）
-- 查询选修‘20201’学生姓名
select sname from stu where '20201' in (select cno from sc where stu.sno = sc.sno)
-- 查询选择‘C语言’课程的学生学号
select sno from sc where 'C语言' in (select cname from cou where sc.cno = cou.cno)
-- 查询每个学生超过他平均分的课程号（第二种方法用派生实现）
-- select avg(grade) from sc group by sno
select sno,cno from sc x where grade > (select avg(grade) from sc y group by y.sno having x.sno = y.sno)
select sno,cno from sc,(select sno, avg(grade) from sc group by sno) as avg_sc(avg_sno, avg_grade) where sc.sno=avg_sc.avg_sno and grade > avg_grade

-- 带有exists（涉及两个表也需要连接）
-- 查询选修‘20201’学生的姓名（exists后返回true or false，每次取一个sno链接）
select sname from stu where exists (select * from sc where cno = '20201' and stu.sno = sc.sno)

-- 集合查询 union并 intersect交 except差
-- 查询年龄是18且mno=1的学生学号 intersect
select sno from stu where age = 18 and mno = 1
select sno from stu where age = 18 intersect select sno from stu where mno = 1
-- 查询年龄是18且mno=1的学生学号 except
select sno from stu where age = 18 except select sno from stu where mno != 1
-- 查询选修‘20201’或‘20203’号课程的学生学号
select distinct sno from sc where cno in ('20201','20203')
select sno from sc where cno = '20201' union select sno from sc where cno = '20203'
-- 查询选修‘20201’和‘20203’号课程的学生学号
select distinct sno from sc where cno = '20201' and cno = '20203', 返回结果错误
select distinct sno from sc where cno = '20201' intersect select distinct sno from sc where cno = '20203'

6 视图

视图是从一个或几个基本表（或视图）导出的表。不同的是，它只是一个虚表，数据库中只存放视图的定义，而不存放视图对应的数据，这些数据仍然存放在原本的基本表中。所以一旦基本表发生变化，从视图中查询的数据也随之改变。

作用：

视图更加方便用户的查询
简化复杂的sql操作，比如复杂的连接
只是用实际的一部分
通过只给用户访问视图的权限，保证数据的安全性
更改数据格式和表示

-- 创建、删除视图
create/drop view viewname

-- 查询学生的信息（sno,sname,age）
create view v_stu1 
as 
select sno,sname,age from stu

select * from v_stu1
-- 查询学生的信息（sno,sname,mname）
create view v_stu2 
as 
select sno,sname,mname from stu,major where stu.mno = major.mno
-- 查询学生的信息（sno,avg(grade)）
create view v_stu3 
as 
select sno,avg(grade) as avg_grade from sc group by sno
-- 查询每个同学较自己平均分高的课程
-- 1 每个同学的平均分视图
-- 2 开始视图
select sc.sno,cno from sc,v_stu3 where sc.sno = v_stu3.sno and sc.grade > v_stu3.avg_grade

7 存储过程

存储过程是事先经过编译并保存在数据库中的一段 sql 语句集合，使用时调用即可

使用存储过程的好处：

代码封装，保证了一定的安全性；
代码复用；
由于是预先编译，因此具有很高的性能

-- 创建、执行、删除存储过程
create proc p1
exec p1
drop proc p1

-- 返回学号2020005学生的成绩情况 存储名 p1
create proc p1 
as
begin
	select * from sc where sno = '2020005'
end 

exec p1
-- 查询某学生指定课程号的成绩和学分 alter 存储名 p1 (alter是修改，因前面已经创建p1)
alter proc p1 @sno varchar(20), @cno varchar(20)
as
begin
	select sc.*,cou.ccredit from sc,cou where sno = @sno and sc.cno = @cno and sc.cno = cou.cno
end

exec p1 '20200004','20203'

8 触发器

监视某种情况，并触发某种操作，当对一个表格进行增删改就有可能自动激活执行它。
after 执行后触发
instead of 执行前触发
declare 声明变量

-- 结构 create/drop trigger ti
create trigger ti on stu 
after instead of
update insert delete
as
begin

end
-- 学生人数不能大于17
--（当向表中不断插入数据时，若插入1条数据之后数据量大于17，则打印error，并自动回滚删除插入的数据；若数据量不大于17，则打印right）
create trigger ti on stu after insert
as
begin
	if (select count(*) from stu) > 17
	begin
		print 'error'
		rollback tran
	end
	else
	begin
		print 'right'
	end
end

alter trigger ti on stu instead of insert
as
begin
	select * from inserted
	select * from deleted
	if (select count(*) from stu) > 16
	begin
		print 'error'
		rollback tran
	end
	else
	begin
		print 'right'
		declare @sno varchar(20)
		declare @sname varchar(30)
		declare @age int
		select @sno = sno from inserted
		select @sname = sname from inserted
		select @age = age from inserted
		insert into stu(sno, sname, age) values (@sno, @sname, @age)
	end
end

-- 学生人数不能小于16
-- （执行删除操作时，若删除数据之后，数据量小于16，则打印error，并回滚；。。。）
create trigger t2 on stu after delete
as
begin
	if (select count(*) from stu) < 16
	begin
		print 'error'
		rollback tran
	end
	else
	begin
		print 'right'
	end
end

-- 当新增学生成绩为55-59时，改为60
create trigger t3 on sc instead of insert
as
begin
	declare @sno varchar(20)
	declare @cno varchar(20)
	declare @grade decimal(5,2)
	select @sno = sno from inserted
	select @cno = cno from inserted
	select @grade = grade from inserted
	if @grade >= 55 and @grade <= 59
	begin
		set @grade = 60
	end
	insert into sc(sno,cno,grade) values(@sno, @cno, @grade)
end

9 函数

自定义函数：函数和之前讲过的存储过程很像，不同之处就是函数多了一个return

-- 计算某门课程的平均分
create function fun1(@cno varchar(20)) 
returns int
as
begin
	declare @avgscore int
	select @avgscore = avg(grade) from sc where cno = @cno
	return @avgscore 
end

select dbo.fun1('20201') 


-- 输入专业号，返回该专业的学生学号和姓名
create function fun2(@mno int)
returns @snoSname table(
	sno varchar(20),
	sname varchar(30)
)
as
begin
	--declare @sno varchar(20)
	--declare @sname varchar(30)
	--select @sno = sno,@sname = sname from stu where mno = @mno
	insert into @snoSname(sno, sname) select sno, sname from stu where mno = @mno
	return
end

select * from dbo.fun2(1)


-- 输入专业号，返回这个专业所有学生的每个课程对应成绩的一个表
create function fun3(@mno int)
returns @mSc table(
	sno varchar(20),
	cno varchar(20),
	grade decimal(5,2)
)
as
begin
	insert into @mSc select stu.sno,cno,grade from major,stu,sc where major.mno=stu.mno and stu.sno = sc.sno and stu.mno = @mno
	return
end

select * from dbo.fun3(1)

10 索引

索引是对数据库表中的一列或多列值进行排序的一种结构
目的：加快查询速度（目录） select
但是，会占用一定的存储空间，需要更新和维护
不创建：
1 频繁更新的字段或者经常增删改的表，不适合创建索引
2 表记录太少，不需要创建索引
3 如果某些数据包含大量重复数据，因此建立索引就没有太大的效果，例如性别字段，只有男(0)女(1)，不适合创建索引

聚集索引： SQLServer默认为主键聚集索引。聚集索引是指数据行的物理顺序与列值（一般是主键那一列）的逻辑顺序相同，一个表中只能拥有一个聚集索引

非聚集索引：数据行的物理顺序与列值（一般是主键那一列）的逻辑顺序可能不同，一个表中可拥有一个或多个聚集索引

-- sc表按学号升序和课程号降序建唯一索引
-- 创建索引
create index scno on sc (sno asc, cno desc)
-- 创建唯一索引
create unique index scno on sc (sno asc, cno desc)
-- 删除索引
drop index scno on sc

11 游标

用来操作查询的结果集，是一个用户数据缓冲区

优点：保存查询结果，以便以后使用。游标的结果集是select执行结果，需要的时候，只需一次，不用重复查询

缺点：数据缓冲区，如果游标数据量大则会造成内存不足。所以，在数据量小的时候才使用游标。

语法： 游标名 cursor for select ...

步骤：声明游标、打开游标、取数据（循环）、关闭游标或者 deallocate

-- 声明游标
declare my_cursor cursor for select mname from major
declare @mname varchar(30)
-- 打开游标
open my_cursor
--取数据（循环）
fetch next from my_cursor into @mname
while @@fetch_status = 0
begin
	select @mname as 'mname'
	fetch next from my_cursor into @mname
end
--关闭游标 或者 deallocate
close my_cursor
deallocate my_cursor

题目：

-- 对sc表添加一个等级列sc_rank，若学生成绩80及以上等级A，70-79分为B，其余为C，null仍为null
declare my_sursor cursor for select cno,sno,grade from sc
declare @cno varchar(20)
declare @sno varchar(20)
declare @grade decimal

open my_cursor

fetch next from my_cursor into @cno,@sno,@grade 
while @@fetch_status = 0
begin
	if @grade >= 80
		update sc set sc_rank = 'A' where cno=@cno and sno = @sno
	else if @grade >= 70
		update sc set sc_rank = 'B' where cno=@cno and sno = @sno
	else if @grade >= 0
		update sc set sc_rank = 'C' where cno=@cno and sno = @sno		
	fetch next from my_cursor into @cno,@sno,@grade 
end

deallocate my_cursor


-- 查询所有学生的专业名和姓名
select sname,mname from stu left outer join major on stu.mno = major.mno

declare my_cursor cursor for select sname,mname from stu left outer join major on stu.mno = major.mno
declare @sname varchar(30)
declare @mname varchar(30)

open my_cursor

fetch next from my_cursor into @sname,@mname
while @@fetch_status = 0
begin
	select @sname as 'sname', @mname as 'mname'
	fetch next from my_cursor into @sname,@mname
end

--close:关闭    deallocate:销毁   
deallocate my_cursor

与视图比较

本质不同：一个是作为指针操作，一个是作为数据库对象
占用资源：多和少
工作方式：一个行处理，一个整个表（查询结果）
数据操作：

12 事务

基本术语：

事务（transaction）指一组 SQL 语句；
回退（rollback）指撤销指定 SQL 语句的过程；
提交（commit）指将未存储的 SQL 语句结果写入数据库表；
保留点（savepoint）指事务处理中设置的临时占位符（placeholder），你可以对它发布回退（与回退整个事务处理不同）。

不能回退 SELECT 语句，回退 SELECT 语句也没意义；也不能回退 CREATE 和 DROP 语句。

MySQL 的事务提交默认是隐式提交，每执行一条语句就把这条语句当成一个事务然后进行提交。当出现 START TRANSACTION 语句时，会关闭隐式提交；当 COMMIT 或 ROLLBACK 语句执行后，事务会自动关闭，重新恢复隐式提交。

设置 autocommit 为 0 可以取消自动提交；autocommit 标记是针对每个连接而不是针对服务器的。

如果没有设置保留点，ROLLBACK 会回退到 START TRANSACTION 语句处；如果设置了保留点，并且在 ROLLBACK 中指定该保留点，则会回退到该保留点。

START TRANSACTION
// ...
SAVEPOINT delete1
// ...
ROLLBACK TO delete1
// ...
COMMIT

13 SQL优化

查询sql时尽量不要使用select * ，而是select具体字段。

# 反例：
select * from employee
# 正例
select id.name from employee

这样可以节省资源、减少网络开销

Hive使用

1 查看表字段

describe formatted tablename

优化方法

1 对于单条删除或者更新的操作，在delete和update后面加上limit 1

为什么这样做？

比如在执行删除中，如果第一条就命中了删除行，如果Sql中有limit 1；这时候就直接return了，否则还会执行完全表扫描才return。如果表的数据量大，会做很多无用功。

那么如果删除大数据量的sql，能否用limit达到意向不到的效果。

delete from t where sex = 1 limit = 100;

在一般场景下，我们对delete后是否需要加limit的问题很陌生，也不知道有多大区别，今天一起学习下：

如果我们清空表数据，建议直接使用truncate，效率上truncate远高于delete。
truncate不走事务，不会锁表，也不会产生大量日志写入日志文件，我们访问log执行日志可以发现每次delete都有记录。
truncate table table_name 会立刻释放磁盘空间，并重置auto_increment的值，delete 删除不释放磁盘空间，insert会覆盖之前的数据上，因为我们创建表的时候有一个创建版本号。

以下只讨论delete场景，首先我们看delete命令的参数信息

delete [low_priority] [quick] [ignore] from tbl_name
[where ...]
[order by ...]
[limit row_count]

delete后面是可以跟limit关键词的，但仅支持单个参数，用于告诉服务器在控制命令被返回到客户端前被删除的行的最大值。

如果要用order by 必须要和 limit 联用，否则被优化掉。

讨论这条sql

delete from t where sex = 1;

如果加上limit ，会有哪些改变？

降低写错sql的代价，即使我们删除错误，只会删除我们limit后面那个数字，limit 10 ，最多丢失10条数据，当然可以通过binlog日志恢复数据，这里面设计到版本号，有兴趣的可以自己了解。
避免了长事务，delete执行时会加锁，无论你有没有加锁，默认涉及到的行加写锁和Gap锁，所有相关的行都会被锁定，如果数据量大直接导致其他连接无法访问该表数据，导致业务无法使用。
delete数据量大的时候，如果不加limilt，容易cpu打满，导致越删越慢。

以上三点的背景是，sex是有索引的，如果sex字段没索引，就会扫描主键索引，即使只有一条数据，也会锁表。

对于delete limit 的使用，MySQL大佬丁奇有一道题：

如果你要删除一个表里面的前 10000 行数据，有以下三种方法可以做到：
第一种，直接执行 delete from T limit 10000;
第二种，在一个连接中循环执行 20 次 delete from T limit 500;
第三种，在 20 个连接中同时执行 delete from T limit 500。

第一个方案，一次占用锁的时间比较长，可能导致其他客户端一致等待资源。
第二个方案，分多次占用锁，串行化执行，不占有锁的间隙，其他客户端可以工作，每次执行不同片段的数据，我理解为分段锁concurrentHashmap
第三个方案。自己制造锁竞争，加剧并发。可能锁住同一记录导致死锁的可能性增大。

总结：在删除数据的时候尽量加limit，这样不仅可以控制删除数据的条数，让操作更安全，可以缩小加锁范围。

运维

1 删除表中上千万的大数据

1.1 方法一 delete加limit，批量删

假设有一个表(syslogs)有1000万条记录，需要在业务不停止的情况下删除其中statusid=1的所有记录，差不多有600万条，直接执行 DELETE FROM syslogs WHERE statusid=1 会发现删除失败，因为lock wait timeout exceed的错误。

因为这条语句所涉及的记录数太多，因此我们通过LIMIT参数分批删除，比如每10000条进行一次删除，那么我们可以利用 MySQL这样的语句来完成:

DELETE FROM syslogs WHERE status=1 ORDER BY statusid LIMIT 10000;

如果要用order by 必须要和 limit 联用，否则被优化掉。然后分多次执行就可以把这些记录成功删除。

注意：
执行大批量删除的时候注意要使用上limit。因为如果不用limit，删除大量数据很有可能造成死锁。
如果delete的where语句不在索引上，可以先找主键，然后根据主键删除数据库。
平时update和delete的时候最好也加上limit 1 来防止误操作。

1.2 方法二表替换

一个表有1亿6000万的数据，有一个自增ID。最大值就是1亿6000万，需要删除大于250万以后的数据，有什么办法可以快速删除？

看到mysql文档有一种解决方案：http://dev.mysql.com/doc/refman/5.0/en/delete.html

If you are deleting many rows from a large table, you may exceed the lock table size for an InnoDB table. To avoid this problem, or simply to minimize the time that the table remains locked, the following strategy (which does not use DELETE at all) might be helpful:

#Select the rows not to be deleted into an empty table that has the same structure as the original table:
INSERT INTO t_copy SELECT * FROM t WHERE ... ;

#Use RENAME TABLE to atomically move the original table out of the way and rename the copy to the original name:
RENAME TABLE t TO t_old, t_copy TO t;

#Drop the original table:
DROP TABLE t_old;

删除大表的多行数据时，会超出innod block table size的限制，最小化的减少锁表的时间的方案是：
1.选择不需要删除的数据，并把它们存在一张相同结构的空表里
2.重命名原始表，并给新表命名为原始表的原始表名
3.删掉原始表

1.3 方法三 TRUNCATE TABLE

通过TRUNCATE TABLE来删除表中百万条数据的情况

创建临时表备份数据

比如只想保留2022.05.13号的数据，其他之前的数据都删除。

SELECT id FROM tablename WHERE report_time > '2022-05-13' LIMIT 1;
# 得到ID = 4109823；
CREATE TABLE tablename_old SELECT * FROM tablename WHERE id > 4109823;
# 创建临时表tablename_old 存放’2022-05-13’的数据。

截断表

TRUNCATE TABLE tablename ;

备份的数据插入到表中

INSERT INTO tablename  SELECT * FROM tablename_old ;

可能出现的问题：
执行 TRUNCATE TABLE 可能会出现Waiting for table metadata lock 锁表解决方案
解决方法：
1.登录数据库执行 SHOW PROCESSLIST;
2.select * from information_schema.innodb_trx\G; 查询当前事务，里面会出现：trx_mysql_thread_id进程ID。
3.kill 进程ID; 杀死当前进程

注：
1、此方法的表内没有设置“索引”，如果是有索引的表，建议先删除索引，再开始删除表中数据。
2、mysql临时表，属于session级别，当session退出时，临时表被删除。也就是说，临时表将在你连接mysql期间存在。当断开时，mysql将自动删除表并释放所用的空间。当然你可以在仍然连接的时候删除表并释放空间。
3、TRUNCATE TABLE 不能用于参与了索引视图的表。

delete和truncate区别：

delete删除数据的原理：(delete属于DML语句)

表中的数据被删除了，但是这个数据在硬盘上的真实存储空间不会被释放！
这种删除表的优点是：支持回滚，后悔了可以恢复数据，可以删除单条数据
缺点：删除效率比较低

delete from user;  //删除user表中的数据，但是这种删除数据的方式有点慢。

truncate删除数据的原理：(DDL)

效率比较高，表被一次截断，物理删除
优点：快速，不走事务，不会锁表，也不会产生大量日志写入日志文件
缺点：不支持回滚，只能删除表中所有数据，不能删单条数据

如果说公司项目里面有一张大表，数据非常多，几亿条记录：
删除的时候，使用delete，也许执行一个小时才能删除完，效率极其低；
可以选择使用truncate删除表中的数据。只需要不到1s的时间就能删除结束，效率较高。
但是使用truncate之前，必须仔细询问客户是否真的需要删除，并警告删除之后不可恢复！！！

truncate table user; //删除user表中的数据，快速。

删除表操作：

drop table 表名；// 删除表，不是删除表中的数据

truncate和delete是删除表中的数据，表还在。

1 创建用户

使用Navicat

1.1 Mysql

1. 首先使用root用户登录数据库
1. 点击 “用户” 选项，新建用户
1. 在 “常规选项卡” 中填入需要创建用户的用户名、密码

在这里插入图片描述
需要注意的是，主机这一选项，若是填安装MySQL机器的IP地址，那么只能在这台机器的本地登陆这个新创建的这个用户，其他机器上将无法。若要在任何机器上登陆，需要填写%（百分号）

可能另外会有：
插件：mysql_native_password
密码过期策略：DEFAULT

1. 切换到 “服务器权限” 选项卡，若要只展示某个数据库（我们新建用户的目的，就是想将部分数据库、表展示给该用户），则这些选项一律不勾选
  
  如果不小心勾选了这一页的某个选项，那么新创建的用户将会看到该机器上MySQL里的所有的数据库、表
1. 切换到 “权限选项卡” ，点击添加权限
  
  如下图所示：设置需要展示的库（表），以及分配给该用户的权限（查询、展示该视图）
1. 最后点击 “保存” 按钮，就成功了
1. 使用刚才创建的用户名密码，就可以登陆

1.1.1 ERROR 1819 (HY000): Your password does not satisfy the current policy requirements解决方案

只是由于mysql安装了validate_password密码校验插件，导致要修改的密码不符合密码策略的要求。

查看当前的密码策略是：SHOW VARIABLES LIKE 'validate_password%';

validate-password=ON/OFF/FORCE/FORCE_PLUS_PERMANENT: 决定是否使用该插件(及强制/永久强制使用)。
validate_password_dictionary_file：插件用于验证密码强度的字典文件路径。
validate_password_length：密码最小长度。
validate_password_mixed_case_count：密码至少要包含的小写字母个数和大写字母个数。
validate_password_number_count：密码至少要包含的数字个数。
validate_password_policy：密码强度检查等级，0/LOW、1/MEDIUM、2/STRONG。
validate_password_special_char_count：密码至少要包含的特殊字符数。
其中，关于validate_password_policy-密码强度检查等级：
0/LOW：只检查长度。
1/MEDIUM：检查长度、数字、大小写、特殊字符。
2/STRONG：检查长度、数字、大小写、特殊字符字典文件。

第一种方式：修改全局变量，但重启mysql后会失效
set global validate_password.policy=0;

第二种方式： my.cnf配置文件添加参数
validate_password_policy=0
重启mysql，让配置生效
systemctl stop mysqld
systemctl start mysqld

解决方案三： my.cnf配置文件修改参数，决定不使用该插件
validate-password=OFF
重启mysql

解决方案四： 卸载插件
uninstall plugin validate_password;

1.2 Oracle

1. 首先登陆Oracle
1. 新建用户
  
  1、用户名必须大写
  2、默认表空间根据想要的展示的表进行选择
1. 授权
  
  DBA权限最高，一般不选择这个。
  实例中选择：CONNECT、RESOURCE
  配额不用管
  服务器权限选择：CREATE VIEW、UNLIMITED TABLESPACE
  权限：选择共享的表，跟MySQL一样，一般只有SELECT权限
1. 保存即可，然后直接使用navicat测试一下新用户能不能连接成功，权限是否设置正确

2 Mysql Workbench入库时间设置自动生成

选择NN，默认值设置为CURRENT_TIMESTAMP

在这里插入图片描述

3 Navicat使用问题记录

navicat安装激活工具包:https://download.csdn.net/download/qq_27630263/88934771

3.1 Navicat连接Oracle数据库时报错：`ORA-28547:connection to server failed, probable Oracle Net admin error`的解决方案

问题原因： Navicat版本与Oracle版本不匹配，需在Navicat安装目录下新增配置

解决方案：

1. 查看Oracle版本命令：select * from v$VERSION，在Oracle官网下载Instant Client压缩包，解压后放到Navicat安装目录下；
1. 启动Navicat客户端，找到【工具】->【选项】->【环境】，修改oci.dll文件的位置；
1. 重启即可。