最近严重记忆力下降加上一直没有怎么用到数据库,好多命令都回忆不起来了,做个小笔记,mark。
今天建彬师傅(他不是厉不厉害的问题,他真的就是那种,很少见,很牛逼的存在…)给我讲了一个非常有用的小知识点,使我茅塞顿开,放在摘要页,很有用:跨用户访问数据表的方式
本文要点:

1.数据库基础
2.DDL&&DML
3.DCL语句
4.多数据库实际业务解决方案★★★

1.数据库基础

MySQL基础

服务启动

net start 服务器名(Windows)
service start 服务器名(linux)

登录与修改密码

命令 含义
mysql -u root -p -h localhost 登录MySQL(p表示密码;u表示用户名;h表示主机地址)
mysqladmin -u用户名 -p旧密码 password新密码 修改用户名密码

基本命令使用

命令 含义
show databases; 用于显示所有的数据库
use 数据库名; 使用目标数据库
select database()/version()/now()/user(); 显示当前连接的信息
show tables; 用于显示选中数据库下的所有的表
desc table_Name; 查看表结构
show create table table_name \G; 查看到创建时的SQL语句及更多详细信息 \G使记录按照字段竖向排列,易于阅读

创建数据库并指定编码格式:create database if not exists 数据库名 character set utf8;
删除数据库:drop database [if exists] 数据库名;

Oracle基础

Oracle数据库、实例、用户、表空间、表之间的关系★★★

  1. 数据库:Oracle数据库是数据的物理存储。这就包括(数据文件ORA或者DBF、控制文件、联机日志、参数文件)。其实Oracle数据库的概念和其它数据库不一样,这里的数据库是一个操作系统只有一个库。可以看作是Oracle就只有一个大数据库

  2. 实例: 一个Oracle实例(Oracle Instance)有一系列的后台进程和内存结构组成。一个数据库可以有n个实例。实例名同时也叫SID

  3. 用户:用户是在实例下建立的,不同实例可以建相同名字的用户。

  4. 表空间:是一个用来管理数据存储逻辑概念,表空间只是和数据文件(ORA或者DBF文件)发生关系,数据文件是物理的,一个表空间可以包含多个数据文件,而一个数据文件只能隶属一个表空间。

  5. 数据文件(dbf、ora):是数据库的物理存储单位。数据库的数据是存储在表空间中的,真 正是在某一个或者多个数据文件中。而一个表空间可以由一个或多个数据文件组成,一个数据文件只能属于一个表空间。一旦数据文件被加入到某个表空间后,就不能删除这个文件,如果要删除某个数据文件,只能删除其所属于的表空间才行

理解:Oracle数据库可以创建多个实例(但我们一般使用默认的orcl实例),每个实例可以创建多个表空间,每个表空间下可以创建多个用户(同时用户也属于表空间对应的实例)和数据库文件,用户可以创建多个表(每个表随机存储在一个或多个数据库文件中)。
由于Oracle就只有一个大的数据库,我们平时说的dw,bi等都是指代的用户。而MySQL是存在数据库的概念的,而我们平时常说的“entchat”,“mdm”都是指代的数据库

1
2
3
4
5
查询当前数据库实例名:
select instance_name from v$instance;

数据库实例名(instance_name)用于对外部连接。**在操作系统中要取得与数据库的联系,必须使用数据库实例名**。比如我们作开发,要连接数据库,就得连接数据库实例名:
jdbc:oracle:thin:@localhost:1521:orcl(orcl就为数据库实例名)

sqlplus基础命令

使用前提:先安装Oracle,安装完成之后在dos窗口中,输入 sqlplus 再输入用户名和密码即可登录

命令 含义
sqlplus 用户名/密码 启动sqlplus
show user 查看当前连接用户
conn 用户名/密码 切换(连接)用户
exit或quit 退出sqlplus
password [用户名] 更改用户口令

创建表空间

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
create tablespace waterboss
datafile 'c:\waterboss.dbf'
size 100m
autoextend on
next 10m

waterboss 为表空间名称
datafile 用于设置物理文件名称
size 用于设置表空间的初始大小
autoextend on 用于设置自动增长,如果存储量超过初始大小,则开始自动扩容
next 用于设置扩容的空间大小

-- 查看当前用户的每个表所占空间大小
select segment_name,sum(bytes)/1024/1024 size_M
from user_extents
group by segment_name
order by size_M desc;

创建用户

1
2
3
4
5
6
7
create user wateruser
identified by itcast
default tablespace waterboss

wateruser 为创建的用户名
identified by 用于设置用户的密码
default tablesapce 用于指定默认表空间名称

Oracle版本说明

1
2
3
4
5
6
7
8
9
10
Oracle 8i
Oracle 9i

Oracle 10g
Oracle 11g

Oracle 12c
I:i 代表 Internet。8i 版本开启对 Internet 的支持。所以,在版本号之后,添加了标识 i。
G:g 代表 Grid 网格。10g 加入了网格计算的功能,因此版本号之后的标识使用了字母g。
C:c 代表云(cloud)计算设计。12c 版本表示对云计算的支持。

2.DDL&DML

DDL:数据定义语言仅对数据库,表,列,索引起作用(create、drop、alter)
DML:数据操纵语言仅对table中的具体数据起作用(select、insert、delete、update)

DDL数据定义语言

1.创建表

创建表
    create table 表名(

    ​  列名 数据类型  列级约束,

    ​  列名 数据类型  列级约束

    ​ )【表类型】【表字符集】【表注释】
1
2
3
4
5
6
7
8
9
10
#创建学生表,并设置表类型、字符集
CREATE TABLE `student` (
`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '主键、学号',
`psd` VARCHAR(20) COLLATE utf8_estonian_ci NOT NULL DEFAULT '123456' COMMENT '密码',
`name` VARCHAR(30) COLLATE utf8_estonian_ci NOT NULL DEFAULT '匿名' COMMENT '学生姓名',
`sex` VARCHAR(2) COLLATE utf8_estonian_ci NOT NULL DEFAULT '男' COMMENT '性别',
`birsday` DATETIME DEFAULT NULL,
`email` VARCHAR(20) COLLATE utf8_estonian_ci DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COLLATE=utf8_estonian_ci
  • 数据类型

  • 约束条件:
    not null
    unique
    primary key 主键名
    foreign key (Id_P) references Persons(Id_P) :一个表中的 FOREIGN KEY 指向另一个表中的 PRIMARY KEY
    default 默认值
    auto increment:在每次插入新记录时,自动地创建主键字段的值

  • 主键

    对主键的要求,最关键的一点是:记录一旦插入到表中,主键最好不要再修改,因为主键是用来唯一定位记录的,修改了主键,会造成一系列的影响。

    由于主键的作用十分重要,如何选取主键会对业务开发产生重要影响。如果我们以学生的身份证号作为主键,似乎能唯一定位记录。然而,身份证号也是一种业务场景,如果身份证号升位了,或者需要变更,作为主键,不得不修改的时候,就会对业务产生严重影响。
    使用BIGINT,会占用更大的磁盘和内存空间,内存空间毕竟有限,无效的占用会导致更多的数据换入换出,额外增加了IO的压力,对性能是不利的。
因此推荐自增主键使用int unsigned类型,但不建议使用bigint。
    所以,选取主键的一个基本原则是:不使用任何业务相关的字段作为主键,而应该使用INT UNSINGED自增或者GUID类型。主键也不应该允许NULL。可以使用多个列作为联合主键,但联合主键并不常用。
    如果使用INT自增类型,那么当一张表的记录数超过2147483647(约21亿)时,会达到上限而出错。使用BIGINT自增类型则可以最多约922亿亿条记录。

1
2
3
4
5
create table user(
id INT UNSIGNED NOT NULL AUTO_INCREMENT primary key , /*数据类型 非空 自增 主码约束*/
name varchar(200)
/*主码,外码约束条件写法二:primary key(id) */
)

2.删除表:drop table table_name

3.修改表

命令 含义
修改表中列的数据类型 alter table table_name modify 列名 column_definition
增加表字段 alter table table_name add [column] 列名 column_definition
删除表字段表字段 alter table table_name drop [column] 列名
表的字段改名 alter table table_name change [column] old_col_name new_col_name
表字段改名同时也可修改字段类型 alter table table_name change [column] old_col_name new_col_name column_definition
更改表名 alter table table_name rename [to] new_tablename

DML数据操纵语句

命令 含义
插入数据 insert into table_name ( field1, field2,…) values (value1,value2,….);
一次性插入多条数据 insert into table_name ( field1, field2,…) values (value1,value2,..),(value1,value2,..)…;
更新记录 update table_name set field1=value1,field2=value2,… [ WHERE CONDITION ]
删除某一项记录 delete from table_name [WHERE CONDITION ]
排序和限制 select * from table_name [ WHERE CONDITION ] [ order by field1 [ DESC\ASC ], field2 [ DESC\ASC], ..]
表连接 select ename,deptname from emp1 left join dept on emp1.deptno=dept.deptno;
子查询 关键字主要有in、not in、=、!=、exists、not exists,between and等等

1.可以不用指定字段名称,但此时后面的values应该与表的字段顺序保持一致
2.对于某些字段允许空值、非空但是有指定的默认值、自增长等,这些字段在insert时如果没有指定value,那么将会右系统自动配置

3.DCL语句

创建数据库用户,并声明权限

创建操作数据库的专用用户,格式:
grant 权限 on 数据库名.数据表名 to 用户名@主机IP IDENTIFIED BY 密码

1
2
3
4
说明:
权限:ALL PRIVILEGES(所有权限),select,insert,update,delete,create,alter,drop
主机IP: '%' 匹配所有主机,localhost
表名: * 表示所有表

刷新权限

flush privileges

这里向大家拓展一个知识点,那就是主机IP指的是该用户只能在对应的主机IP上进行登录。例如:假如user用户设置的主机IP为localhost,那么其他人就将无法从本地以外的地方使用user用户进行登录。

4.实际业务解决方案★★★

distinct 查询不重复的记录

select distinct id from emp1; 加distinct关键字使得查出的结果中去掉了重复记录

条件查询

判断条件可以使用=、>、<、>=、<=、!=等等,同时多个条件还可以使用and、or等进行连接

聚合函数(它对其应用的每个行集返回一个值)

1
2
3
4
5
6
7
8
9
10
11
select [ field1, field2,...,fieldn] fun_name from tablename [ where condition ] 

      [ group by field1, field2,..., fieldn  [ with rollup ] ]  [ having  condition ]

参数说明:fun_name 聚合函数,表示要做的聚合操作,常用的有sum(求和)、count(*)(计数)、max、min等。

        GROUP BY  表示对后面的字段进行分类聚合(也常叫分组);

        WITH ROLLUP 可选语法,在ORDER BY 语句内出现,表示是否对分类聚合后的结果进行再汇总;

        HAVING 表示对分类后的结果再进行条件过滤;

同样是条件过滤,having和where的区别在于,having是对聚合后的结果进行条件过滤,而where是在聚合前就对记录进行过滤。因此,为了提高聚合效率,一般先用where过滤不相干的记录后,在进行聚合,最后再用having进行二次过滤

1
2
select gender,count(gender) from tab_teacher group by gender having gender="woman";
-- 含义为:按性别分组,查询出女老师人数的总数:先执行group by,再聚合函数,最后having筛选

聚合函数:补充说明

  • AVG(表达式) 返回表达式中所有的平均值。仅用于数字列并自动忽略NULL值。

  • COUNT(表达式) 返回表达式中非NULL值的数量。可用于数字和字符列。

  • COUNT() 返回表中的行数( *包括有NULL值的列** )。

  • MAX(表达式) 返回表达式中的最大值,忽略NULL值。可用于数字、字符和日期时间列。

  • MIN(表达式) 返回表达式中的最小值,忽略NULL值。可用于数字、字符和日期时间列。

  • SUM(表达式) 返回表达式中所有的总和,忽略NULL值。仅用于数字列。
    ————————————————

避免使用通配符“*”

实际开发中,要避免使用通配符”*”(通配符代表所有字段),因为系统要先解析出所有的字段名,将其还原为真实的名字,然后再进行操作。如果记录数据库内容特别多,会影响效率。

查询表的总记录数(推荐使用主键的方式查询)

因为主键唯一,且不能为空,此时id总数则代表总记录数
select count(id) from table_teacher;

别名as的用法

MySQL的列,表别名都可以使用as,但Oracle的表别名禁止使用as

参考StackOverflow的问题回复:

You can use AS for table aliasing on many SQL servers (at least MsSQL, MySQL, PostrgreSQL) but it’s always optional and on Oracle it’s illegal. so just remove the as


分页查询方式(limit与rownum)

查询老师记录的前三条(从0位置开始找出3条): limit后面有两个参数:第一个参数是起始位置,初始记录行的偏移量是 0(而不是 1);第二个参数是偏移量(即数据个数)

select * from tab_teacher limit 0,3;
查询第1条到第10条数据的sql是:select * from table limit 0,10; 
即索引值为[0,9],不同于其它编程语言左闭右开原则
查询第10条到第20条数据的sql是:select * from table limit 10,20;

分页查询计算公式:limit (page-1)*size,size

oracle的sql语句中没有limit,limit是mysql中特有的,在oracle中可用rownum来表示,用于查询结果中的前N行数据。如要查询emp表中的前5行数据,可用如下语句:   select * from emp where rownum<=5;

Oracle 使用|| 将多列合并成一列,字符串连接

使用方法:字段A || '连接符' || 字段B || '连接符' || 字段C :将多列拼接成一列

1
2
3
4
5
6
7
8
9
10
11
12
13
Oracle 将多列合并成一列,字符串连接,直接上查询语句

SELECT
    STU.XH,
    STU.XM,
    GRA. NAME || '-' || CLA.BJ AS CLASS
FROM
    CYT_BASE_STUDENT STU,
    CYT_BASE_GRADE GRA,
    CYT_BASE_CLASS CLA
WHERE
    STU.GRADE_ID = GRA. ID
AND STU.CLASS_ID = CLA. ID

sql的select查询添加一个虚拟字段进去

需求:像下面需要取多张表的数据存合并成一张表的时候,有时候为了区分哪些数据是出自于具体的哪张表,可以加一个表的标识字段进去,这样就方便明了多了

1
2
3
4
5
6
7
8
9
10
select * from ( 
select id,title,uId,fabuTime,'招商' AS name from zhaoshangInfo
union all
select id,title,userId AS uId,createTime AS fabuTime,'求职' AS name from resume
union all
select id,title,uId,fabutime AS fabuTime,'招聘' AS name from zhaopin
union all
select id,title,uId,fabuTime,'加盟' AS name from jiamengInfo
)
WHERE uId IN(1,349) ORDER BY fabuTime DESC

即在zhangsanInfo表中添加name字段,赋初值为‘招商’
在resume表中添加name字段,赋初值为‘求职’

在jiamengInfo表添加name字段,赋初值为‘加盟’。


sql实现替换功能

  • 方式一:replace()字符串替换函数
  • 方式二:case…when…then…end 函数

replace函数定义:replace(original-string,str_a,str_b):查询在sql的字段,用b替换目标字段中的a

参数:

  1. original-string: 被搜索的字符串。可为任意长度。
  2. search-string: 要搜索并被 replace-string 替换的字符串。该字符串的长度不应超过 255 个字节。如果 search-string 是空字符串,则按原样返回原始字符串。
  3. replace-string: 该字符串用于替换 search-string。可为任意长度。如果 replacement-string 是空字符串,则删除出现的所有 search-string。

说明:
用字符串表达式3替换字符串表达式1中出现的所有字符串表达式2的匹配项。返回新的字符串。如果有某个参数为 NULL,此函数返回 NULL

方式二:Case…when..then…else…end

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
--简单Case函数
select
(CASE sex
WHEN '1' THEN '男'
WHEN '2' THEN '女'
ELSE '其他' END) as gender

--Case搜索函数(推荐)
select
(CASE WHEN sex = '1' THEN '男'
WHEN sex = '2' THEN '女'
ELSE '其他' END ) as gender

两种方式,可以实现相同的功能。简单Case函数的写法相对比较简洁,但是和Case搜索函数相比,功能方面会有些限制,
比如写判断式。还有一个需要注意的问题,Case函数只返回第一个符合条件的值,剩下的Case部分将会被自动忽略。

Oracle 通配符匹配字符限制字符长度

length(字段)<Number 限制字符串的长度,如果是数字型的,那么再to_char一下

Oracle截取字符串指定位数

substr(field,start_index,[length]):select截取field字段,索引值从1或者0开始,截取指定length长度

Oracle怎样把查询的null转换为指定值

oracle: nvl(field,Expr2)如果field字段为NULL,返回Expr2的值,否则返回Expr1的值
mysql: ifnull(field,Expr2)如果field字段为NULL,返回Expr2的值,否则返回Expr1的值

多字段关联查询同一张字典表

字典表(数据字典):数据字典(Data dictionary)是一种用户可以访问的记录数据库和应用程序元数据的目录。

假设我们有一个学生表(姓名,学号,性别编号{0-1},年纪{1-3},成绩{A-F}):我们已知了一个学生的基本信息,怎么从一个字典表(比如说性别编号0:男性,1:女性;年纪A:优,B:良,C:中,D:差,E:不合格)中来补全这个表

1
2
3
4
5
6
select a.name, a.id, a.sex_code, a.grade_cdoe, a.score_cdoe,b.sex_name,b.grade_name,b.score_name
from Student a
left join dictionary b
on a.sex_code = b.sex_code
and a.grade_code = b.grade_code
and a.score_code = b.score_code

多个字段查询一张字段表时,就是在原来的on关键字上,多次使用and,多次关联字典表中的目标字段

sql带字符的排序问题

问题:月度字段有:1月,2月…11月,12月等。我们使用order by关键字后,并没有按照预期的1-11排序,而是:10月…12月,1月,…9月。

解决方法:order by case when then end 语句

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
select syear as syear ,speriod as speriod,sum(sdata) as sdata
from (select * from C1DATA.C1_DATAS where STYPE='手机_财务销售')

-- 先按照syear分组,然后按照period分组
group by SYEAR,speriod
-- 先按照syear升序,然后按照period升序
ORDER by syear asc ,

case
when speriod = '1月' then 1
when speriod = '2月' then 2
when speriod = '3月' then 3
when speriod = '4月' then 4
when speriod = '5月' then 5
when speriod = '6月' then 6
when speriod = '7月' then 7
when speriod = '8月' then 8
when speriod = '9月' then 9
when speriod = '10月' then 10
when speriod = '11月' then 11
when speriod = '12月' then 12
end asc

with as 子查询部分

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
--针对一个别名
with tmp as (select * from tb_name)

--针对多个别名
with
tmp as (select * from tb_name),
tmp2 as (select * from tb_name2),
tmp3 as (select * from tb_name3),


--相当于建了个e临时表
with e as (select * from scott.emp e where e.empno=7499)
select * from e;

--相当于建了e、d临时表
with
e as (select * from scott.emp),
d as (select * from scott.dept)
select * from e, d where e.deptno = d.deptno;

就是把一大堆重复用到的sql语句放在with as里面,取一个别名,后面的查询就可以用它,这样对于大批量的sql语句起到一个优化的作用,而且清楚明了。
特别对于union all比较有用。因为union all的每个部分可能相同,但是如果每个部分都去执行一遍的话,则成本太高,所以可以使用with as短语,则只要执行一遍即可。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
with
sql1 as (select to_char(a) s_name from test_tempa),
sql2 as (select to_char(b) s_name from test_tempb where not exists (select s_name from sql1 where rownum=1))
select * from sql1
union all
select * from sql2
union all
selectno recordsfrom dual
where not exists (select s_name from sql1 where rownum=1)
and not exists (select s_name from sql2 where rownum=1);

--又比如创建一个临时表t(t的虚拟字段创建方法参考 9.6 sql的select查询添加一个虚拟字段进去)
with t
as (
select '201912' as m , '单价' as t, 1 as d from dual
union all
select '201912' as m , '数量' as t, 100 as d from dual
union all
select '201912' as m , '金额' as t, 100 as d from dual
)

Oracle的行列转换-行转列

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- 创建一个虚拟表t
with t
as (
select '201912' as m , '单价' as t, '1' as d from dual
union all
select '201912' as m , '数量' as t, '100' as d from dual
union all
select '201912' as m , '金额' as t, '100' as d from dual
union all
select '201912' as m , '收货日期' as t, '2019-12-25' as d from dual
)
-- Oracle的行转列
select
t.m,
max(case when t.t = '收货日期' then t.d else '' end) as d,
sum( case when t.t = '单价' then to_number(t.d) else 0 end) as p,
sum( case when t.t = '数量' then to_number(t.d) else 0 end) as q,
sum( case when t.t = '金额' then to_number(t.d) else 0 end) as m
from t
group by t.m;

涉及文章:oracle 列转行

处理多级部门目录

某张表是包含当前部门节点id,上级部门节点id,怎么找到其完整的组织目录?

字段释意:

  • code:当前部门节点id
  • name:当前部门节点name
  • p_code:上级部门节点code
  • p_name:上级部门节点name
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
--  MySQL:concat(str1, str2,...) 返回结果为连接参数产生的字符串,如果有任何一个参数为null,则返回值为null
SELECT
t1.code AS code,
t1.name AS dept_name1,
t2.name AS dept_name2,
t3.name AS dept_name3,
t4.name AS dept_name4,
t5.name AS dept_name5,
t6.name AS dept_name6,
t7.name AS dept_name7,
concat(
(CASE WHEN isnull( t7.name ) THEN '' ELSE concat( t7.name, '/' ) END ),
(CASE WHEN isnull( t6.name ) THEN '' ELSE concat( t6.name, '/' ) END ),
(CASE WHEN isnull( t5.name ) THEN '' ELSE concat( t5.name, '/' ) END ),
(CASE WHEN isnull( t4.name ) THEN '' ELSE concat( t4.name, '/' ) END ),
(CASE WHEN isnull( t3.name ) THEN '' ELSE concat( t3.name, '/' ) END ),
(CASE WHEN isnull( t2.name ) THEN '' ELSE concat( t2.name, '/' ) END ),
(CASE WHEN isnull( t1.name ) THEN '' ELSE concat( t1.name, '/' ) END )
) AS dept_name_tree

FROM ew_sf t1
LEFT JOIN ew_sf t2 ON t1.p_code = t2.code
LEFT JOIN ew_sf t3 ON t2.p_code = t3.code
LEFT JOIN ew_sf t4 ON t3.p_code = t4.code
LEFT JOIN ew_sf t5 ON t4.p_code = t5.code
LEFT JOIN ew_sf t6 ON t5.p_code = t6.code
LEFT JOIN ew_sf t7 ON t6.p_code = t7.code

效果展示: 双环传动-双环本部/总经理办公室/运营中心/工艺装备精密制造分公司/工装制造部/沙门班/

复制表结构和表中数据(慎用)

重量级操作、耗时操作、事务操作都得好好想想锁的问题、并发的问题
select into from 与 insert into select用法详解:有可能两个表加锁规则不一样

  • Insert into Table2(field1,field2,…) select value1,value2,… from Table1;
  • SELECT vale1,value2 into Table2 from Table1;

判断查询表中字段里数据是否有重复

这里是以Oracle进行举例,sql server、MySQL、sqlite等原理均相同,只是其各自的语法有点大同小异。

判断是否有重复值时,需要知道是否需要统计重复条数是某单个字段,还是组合字段重复情况

  • 重复条数统计
1
2
3
4
5
6
7
8
9
10
11
12
13
-- 单字段
SELECT
Field_NAME,
COUNT(*) -- 重复统计数
FROM TABLE_NAME
GROUP BY Field_NAME
HAVING COUNT(*) > 1

-- 组合字段
SELECT Field_NAME1, Field_NAME2,COUNT(*)
FROM TABLE_NAME
GROUP BY Field_NAME1,Field_NAME2
HAVING COUNT(*) > 1

筛选出来的结果

content count(*)
太阳不起我不起,老子就是了不起 2
猛虎下山苍龙啸,小老弟你别太傲 2
  • 不统计重复条数
1
2
3
4
5
6
7
8
9
-- 表示查找表c1_dim_info中,dim_id拼接dim_type后,找出拼接后的重复行
select *
from c1_dim_info
where dim_id || dim_type in (
select dim_id || dim_type
from c1_dim_info
group by dim_id || dim_type
having count(dim_id || dim_type) > 1
)

truncate,delete,drop区别

一:用法和区别

  1. *drop table 表名:删除内容和定义,并释放空间。执行drop语句,将使此表的结构一起删除

  2. truncate table 表名:删除内容、释放空间但不删除定义(也就是保留表的数据结构,自增id也会被删除)。与drop不同的是,truncate只是清空表数据而已

  3. delete from 表名(where 列名 = 值):delete也只删除内容、不删除定义;可以对行数据或整表数据进行删除

二:注意

  1. delete语句执行删除的过程是每次从表中删除一行,并且同时将该行的删除操作作为事务记录在日志中保存,以便进行进行回滚操作;truncate和drop是数据库定义语言,操作立即生效,不能回滚
  2. 执行速度一般来说:drop>truncate>delete
  3. truncate语句执行以后,id标识列还是按顺序排列(即从1开始排列),保持连续;而delete语句执行后,ID标识列不连续
  4. 如果想删除部分数据用delete,注意带上where子句,回滚段要足够大;如果想删除表,当然用drop;如果想保留表而将所有数据删除,如果和事务无关,用truncate即可

参考文章:


 评论

联系我 | Contact with me

Copyright © 2019-2020 谁知你知我,我知你知深。此恨经年深,比情度日久

博客内容遵循 署名-非商业性使用-相同方式共享 4.0 国际 (CC BY-NC-SA 4.0) 协议