数据库问题集锦

丢失更新

MySQL问题集锦

查找重复记录

比如表A中

字段1	字段2
1	a
2	a
1	b
1	b
2	a

用sql语句实现查询，查询出

字段1	字段2
1	a
1	b

select distinct x.字段1,x.字段2
from a as x , a as y
where x.字段1=y.字段1 and x.字段2!=y.字段2

链接

mysql自动创建时间和更新时间

`create_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP comment '创建时间',
`update_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP comment '更新时间',

mysql两表联合查询的四种情况

Oracle外连接包括如下：

• 左外连接（左边的表不加限制）
• 右外连接（右边的表不加限制）
• 全外连接（左右两表都不加限制）

一般来说，为了得到更加完整的结果，我们需要从两个或更多的表中获取结果，我们一般都是使用
select xxx,xxx from 表1, 表2 where 表1.xxx = 表2.xxx，我们一般都是进行的这般操作。还可以使用join操作。

请看Persons表：

Id_P	LastName	FirstName	Address	City
1	Adams	John	Oxford Street	London
2	Bush	George	Fifth Avenue	New York
3	Carter	Thomas	Changan Street	Beijing

接下来请看Orders表

Id_O	OrderNo	id_P
1	77895	3
2	44678	3
3	22456	1
4	24562	1
5	34764	65

以上两个表，我们把它们进行连接查询

select Persons.LastName, Persons.FirstName, Orders.OrderNo
from Persons, Orders
where Persons.Id_P = Orders.Id_P 

结果集：

LastName	FirstName	OrderNo
Adams	John	22456
Adams	John	24562
Carter	Thomas	77895
Carter	Thomas	44678

如果使用关键词JOIN来从两个表中获取数据

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
INNER JOIN Orders
ON Persons.Id_P = Orders.Id_P
ORDER BY Persons.LastName

结果集：

LastName	FirstName	OrderNo
Adams	John	22456
Adams	John	24562
Carter	Thomas	77895
Carter	Thomas	44678

则inner join与上面的普通查询没有区别

不同的SQL JOIN

• JOIN：如果表中有至少一个匹配，则返回行
• LEFT JOIN：即使右表中没有匹配，也从左表返回所有的行
• RIGHT JOIN：即使左表中没有匹配，也从右表返回所有的行
• FULL JOIN：只要其中一个表存在匹配，就返回行

LEFT JOIN关键字用法

"Persons"表：

Id_P	LastName	FirstName	Address	City
1	Adams	John	Oxford Street	London
2	Bush	George	Fifth Avenue	New York
3	Carter	Thomas	Changan Street	Beijing

接下来请看Orders表

Id_O	OrderNo	id_P
1	77895	3
2	44678	3
3	22456	1
4	24562	1
5	34764	65

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
LEFT JOIN Orders
ON Persons.Id_P=Orders.Id_P
ORDER BY Persons.LastName

结果集：

LastName	FirstName	OrderNo
Adams	John	22456
Adams	John	24562
Carter	Thomas	77895
Carter	Thomas	44678
Bush	George

SQL RIGHT JOIN关键字

"Persons"表：

Id_P	LastName	FirstName	Address	City
1	Adams	John	Oxford Street	London
2	Bush	George	Fifth Avenue	New York
3	Carter	Thomas	Changan Street	Beijing

接下来请看Orders表

Id_O	OrderNo	id_P
1	77895	3
2	44678	3
3	22456	1
4	24562	1
5	34764	65

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
RIGHT JOIN Orders
ON Persons.Id_P=Orders.Id_P
ORDER BY Persons.LastName

结果集：

LastName	FirstName	OrderNo
Adams	John	22456
Adams	John	24562
Carter	Thomas	77895
Carter	Thomas	44678
		34764

SQL FULL JOIN关键字

"Persons"表：

Id_P	LastName	FirstName	Address	City
1	Adams	John	Oxford Street	London
2	Bush	George	Fifth Avenue	New York
3	Carter	Thomas	Changan Street	Beijing

接下来请看Orders表

Id_O	OrderNo	id_P
1	77895	3
2	44678	3
3	22456	1
4	24562	1
5	34764	65

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
FULL JOIN Orders
ON Persons.Id_P=Orders.Id_P
ORDER BY Persons.LastName

结果集：

LastName	FirstName	OrderNo
Adams	John	22456
Adams	John	24562
Carter	Thomas	77895
Carter	Thomas	44678
Bush	George
		34764

mysql查看和修改端口号

1、登录mysql

mysql -uroot -p
show global variables like 'port';

2、docker下修改默认端口号

docker exec -it 容器ID bash

安装vim修改端口号

apt-get update
apt-get install vim

然后使用vim /etc/mysql/my.cnf修改端口号

[root@test etc]# vi my.cnf
[mysqld]
port=3506
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

"my.cnf" 11L, 261C written
[root@test etc]#

3、重启容器

docker restart 容器ID

Docker下mysql启动

docker run --name mysql5.7 --restart always -p 3306:3306 -e MYSQL_ROOT_PASSWORD=12345 \
-v /home/vagrant/mysql5.7/data:/var/lib/mysql -d mysql:5.7

参数说明

• --name mysql5.7：指定运行容器的名称
• --restart always：容器意外退出后自动重启
• -p 3306:3306：映射主机的3306端口到容器的3306端口
• -e MYSQL_ROOT_PASSWORD：指定mysql root密码
• -v /home/vagrant/mysql5.7/data:/var/lib/mysql：mysql数据持久化，主机的/home/vagrant/mysql5.7/data 目录挂载到容器 /var/lib/mysql 目录

彻底解决mysql中文乱码

查看mysql数据库编码格式：

set character_set_server=utf8;
set character_set_database=utf8;
show variables like '%char%';

修改配置文件/etc/mysql/mysql.cnf

[mysqld]
character-set-server=utf8 
[client]
default-character-set=utf8 
[mysql]
default-character-set=utf8

oracle问题集锦

Oracle JDBC ojdbc6 jar as a Maven Dependency

当在pom.xml文件中添加ojdbc6依赖的时候，会发现找不到相应的jar包

<dependency>
    <groupId>com.oracle</groupId>
    <artifactId>ojdbc6</artifactId>
    <version>11.2.0.3</version>
</dependency>

这时候需要下载jar包之后，手动安装

mvn install:install-file -DgroupId=com.oracle -DartifactId=ojdbc6 -Dversion=11.2.0.3 -Dpackaging=jar -Dfile=<Path where the jar is, example downloads>/ojdbc6.jar -DgeneratePom=true

这时候在pom.xml中添加相应的配置依赖即可。

参考链接

oracle和mysql数据类型对比

Oracle和mysql有一组兼容的数据类型，但是，根据可接受值的范围，在数据库迁移期间可能需要类型映射。在实现或者验证从Oracle到MySQL的数据库迁移时，一定要记住正确的类型映射。

Oracle	MySQL
BFILE	VARCHAR(255)
CHAR(n), CHARACTER(n), n < 256	CHAR(n), CHARACTER(n)
CHAR(n), CHARACTER(n), n > 255	VARCHAR(n)
CLOB	LONGTEXT
DATE	DATETIME
DECIMAL(p,s), DEC(p,s)	DECIMAL(p,s), DEC(p,s)
DOUBLE PRECISION	DOUBLE PRECISION
FLOAT(p)	DOUBLE
INTEGER, INT	INT
INTERVAL YEAR(p) TO MONTH	VARCHAR(30)
INTERVAL DAY(p) TO SECOND(s)	VARCHAR(30)
LONG	LONGTEXT
LONG RAW	LONGBLOB
NCHAR(n), n < 256	NCHAR(n)
NCHAR(n), n > 255	NVARCHAR(n)
NCHAR VARYING(n)	NCHAR VARYING(n)
NCLOB	NVARCHAR(max)
NUMBER(p,0), NUMBER(p), 1 <= p < 3	TINYINT
NUMBER(p,0), NUMBER(p), 3 <= p < 5	SMALLINT
NUMBER(p,0), NUMBER(p), 5 <= p < 9	INT
NUMBER(p,0), NUMBER(p), 9 <= p < 19	BIGINT
NUMBER(p,0), NUMBER(p), 19 <= p <= 38	DECIMAL(p)
NUMBER(p,s), s > 0	DECIMAL(p,s)
NUMBER, NUMBER(*)	DOUBLE
NUMERIC(p,s)	NUMERIC(p,s)
NVARCHAR2(n)	NVARCHAR(n)
RAW(n),n < 256	BINARY(n)
RAW(n), n > 255	VARBINARY(n)
REAL	DOUBLE
ROWID	CHAR(10)
SMALLINT	DECIMAL(38)
TIMESTAMP(p)	DATETIME(p)
TIMESTAMP(p) WITH TIME ZONE	DATETIME(p)
UROWID(n)	VARCHAR(n)
VARCHAR(n),VARCHAR2(n)	VARCHAR(n)
XMLTYPE	LONGTEXT

Oracle to MySQL Types Mapping

容器化部署Oracle11g

这里通过docker快速搭建oracle数据库环境，使用这种方式免去安装配置上的麻烦，方便快捷。

拉取镜像

（1）、首先将镜像下载到本地

docker pull registry.cn-hangzhou.aliyuncs.com/helowin/oracle_11g

（2）、由于镜像比较大，执行如下命令删除所有 dangling 数据卷（即无用的 Volume）,避免空间不足：

docker volume rm $(docker volume ls -qf dangling=true)

启动容器

（1）、执行如下命令实例化Oracle服务

docker run -d -p 1521:1521 --name oracle11g registry.cn-hangzhou.aliyuncs.com/helowin/oracle_11g

（2）、执行docker ps命令确认容器启动成功

进行配置

（1）、首先执行如下命令进入oracle的命令行环境中：

docker exec -it docker容器名称 /bin/bash

（2）、接着执行如下命令切换到oracle数据库的root用户下（密码为：helowin）
su root
（3）、编辑profile文件
vi /etc/profile
（4）、在末尾处添加如下ORACLE环境变量参数后保存退出

注意：作者原先制作这个镜像时，设置的数据库 SID 为 helowin，所以这里的 SID 不做修改（修改了也没用，因为要改变 SID 的话修改多处地方）。

export ORACLE_HOME=/home/oracle/app/oracle/product/11.2.0/dbhome_2
export ORACLE_SID=helowin
export PATH=$ORACLE_HOME/bin:$PATH

（5）、接着执行如下命令创建软连接

ln -s $ORACLE_HOME/bin/sqlplus /usr/bin

（6）、然后执行如下命令切换到oracle用户

注意：一定要写中间的那一横条（-）必须要，否则软连接无效。

su oracle

（7）、接着依次执行如下命令登录sqlplus

sqlplus /nolog
conn /as sysdba

（8）、执行如下命令将system和sys两个用户的密码设为abc：

alter user system identified by abc;
alter user sys identified by abc;
ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

（9）、我们还可以执行如下命令解锁scott用户，同时将其设置为abc。

SCOTT 是 ORACLE 内部的一个示例用户，下面有 emp、dept 等实例表，这些表和表间的关系演示了关系型数据库的一些基本原理。

alter user scott account unlock;
alter user scott identified by abc;
commit;

（10）、配置完毕后，连续执行多个exit即可退出容器，返回到宿主机。

连接测试

使用客户端工具连接这个oracle数据库（其中SID为helowin），即可发现连接成功。

Docker - 通过容器安装部署Oracle 11g数据库教程（第三方镜像）

oracle-sql-查询日期区间的数据

两种方法

两种情况：

1、查询给定时间在开始时间列与结束时间列范围中的数据。
2、查询日期列在开始时间列与结束时间列范围中的数据。

第一种：<,>,<=,>=

  select * from 表名 where 日期列 >= to_date('2015-10-20 00:00:00','yyyy-mm-dd hh24:mi:ss') and t.日期列 <= to_date('2015-10-20 23:59:59','yyyy-mm-dd hh24:mi:ss')

第二种：between and

select * from 表名 where 日期列 between to_date('2015-10-20 00:00:00','yyyy-mm-dd hh24:mi:ss')and to_date('2015-10-20 23:59:59','yyyy-mm-dd hh24:mi:ss')

oracle --sql–查询日期区间的数据

ORACLE SQL Developer日期显示格式设置

ORACLE的SQL Developer工具默认的日期格式DD-MON-RR,在SQL查询中往往你看不到时间信息，此时你必须修改日期格式。具体如下所示

工具->首选项->数据库->NLS->日期格式: DD-MON-RR 修改为: YYYY-MM-DD HH24:MI:SS

修改表空间名称

修改表空间名字

SQL> alter tablespace lims rename to lims_data;
 
表空间已更改。

修改表空间的数据文件名字（联机状态）

SQL> alter tablespace lims_data offline;
 
表空间已更改。

故直接进数据文件所在的物理路径，手工重命名之后，再用命令修改。

修改数据文件名字

SQL> alter tablespace lims_data rename datafile 'E:\OracleWorkspace\lims\LIMS.DBF' to 'E:\OracleWorkspace\lims\LIMS_DATA.DBF';
 
表空间已更改。

将表空间置为online

SQL> alter tablespace lims_data online;
 
表空间已更改。

确认表空间名称已经修改

select name from v$tablespace;
exit