连接数据库

PLSQL连接oracle数据库

用户名、密码、数据库地址、端口、数据库实例名称、oracle instant-client、odbc.jar

plsql通过instant-client与oracle进行连接，

stant-client主页：https://www.oracle.com/database/technologies/instant-client.html

oracle instant-client下载地址：https://www.oracle.com/database/technologies/instant-client/downloads.html，根据操作系统版本选择不同的版本进行安装。高版本的instant-client支持连接低版本的oracle数据库，例如19的instant-client可以连接11.2版本的oracle数据库

操作步骤

1、配置PLSQL中 instant-client的安装位置。

2、打开plsq对话框，输入用户名、密码、连接串（连接串由数据库地址：端口/数据库实例名称组成，例如10.12.11.12:1521/orcl）。

MySQL Workbench 连接mysql数据库

需要准备

用户名、密码、数据库地址、端口、数据库实例名称、mysql-connector.jar

Mysql的访问ip限制

mysql在用户授权是会限制用户的访问ip，

如果允许某个ip访问，需要像如下方式授权给改用户某ip的访问权限。

GRANT ALL PRIVILEGES ON *.* TO 'root'@'10.251.226.172' IDENTIFIED BY 'czp' WITH GRANT OPTION;

flush privileges

如果允许所有ip访问，将ip地址更换为 % ，例如：

update user set host='%' where host='localhost' and user ='%'

操作步骤

1、打开MySQL Workbench

2、点击页面MySQL Connections 下方的卡片连接已经添加好的数据库连接。

3、或点击页面MySQL Connections 后面的加号，新增加链接。

4、需要输入连接名称、主机名称、端口、用户名、密码就可以完成数据库连接的建立。关于默认schema可以不进行设置。填写完毕信息后可以通过TestConnection进行连接的测试。

5、如果未设置默认schema则需要在进入后选择需要操作的schema。新建sql窗口后即可进行相应操作。

新建schema

CREATE DATABASE `world` 
/*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ 
/*!80016 DEFAULT ENCRYPTION='N' */;

在workbench中切换shcema

双击需要进行操作的视图名称，视图名称会加粗，即表示sql脚本将会使用该视图。

或者使用下面的方式切换语句

use world;

不同的工具可能切换schema的方式不一样，在执行语句前（尤其是删除、插入、更新操作）要确认好自己当前的schema，或者在表名前面加上模式的名称。

新建数据库表

新建普通数据库表

下面新建三张表city、country、contrylanguage

CREATE TABLE `city` (
  `ID` int NOT NULL AUTO_INCREMENT,
  `Name` char(35) NOT NULL DEFAULT '',
  `CountryCode` char(3) NOT NULL DEFAULT '',
  `District` char(20) NOT NULL DEFAULT '',
  `Population` int NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`),
  KEY `CountryCode` (`CountryCode`),
  CONSTRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `country` (`Code`)
) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=latin1;

CREATE TABLE `country` (
  `Code` char(3) NOT NULL DEFAULT '',
  `Name` char(52) NOT NULL DEFAULT '',
  `Continent` enum('Asia','Europe','North America','Africa','Oceania','Antarctica','South America') NOT NULL DEFAULT 'Asia',
  `Region` char(26) NOT NULL DEFAULT '',
  `SurfaceArea` float(10,2) NOT NULL DEFAULT '0.00',
  `IndepYear` smallint DEFAULT NULL,
  `Population` int NOT NULL DEFAULT '0',
  `LifeExpectancy` float(3,1) DEFAULT NULL,
  `GNP` float(10,2) DEFAULT NULL,
  `GNPOld` float(10,2) DEFAULT NULL,
  `LocalName` char(45) NOT NULL DEFAULT '',
  `GovernmentForm` char(45) NOT NULL DEFAULT '',
  `HeadOfState` char(60) DEFAULT NULL,
  `Capital` int DEFAULT NULL,
  `Code2` char(2) NOT NULL DEFAULT '',
  PRIMARY KEY (`Code`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `countrylanguage` (
  `CountryCode` char(3) NOT NULL DEFAULT '',
  `Language` char(30) NOT NULL DEFAULT '',
  `IsOfficial` enum('T','F') NOT NULL DEFAULT 'F',
  `Percentage` float(4,1) NOT NULL DEFAULT '0.0',
  PRIMARY KEY (`CountryCode`,`Language`),
  KEY `CountryCode` (`CountryCode`),
  CONSTRAINT `countryLanguage_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `country` (`Code`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

新建分区数据库表

我们新建一个数据库表，根据日期建立分区。

select distinct continent from country ;

查询所有的国家位于以下几个州，我们建立一个country的分区表.

可以通过工具的可视化界面建立，也可以使用下面的语句建立。

在Oracle中可以直接使用字符串作为分区的value，但是在mysql中list分区的value只能设为整型，所以需要通过函数进行转换。为了方便演示，我们将所在州转换为整型

Africa：0
Antarctica：1
Asia：2
Europe：3
North America：4
Oceania：5
South America：6

同时还需要注意，分区字段要包含在主键当中。作为联合主键。

create table country_part
(
    Code           char(3)      default ''     not null ,
    Name           char(52)     default ''     not null,
    Continent      int  default 0 not null,
    Region         char(26)     default ''     not null,
    SurfaceArea    float(10, 2) default 0.00   not null,
    IndepYear      smallint                    null,
    Population     int          default 0      not null,
    LifeExpectancy float(3, 1)                 null,
    GNP            float(10, 2)                null,
    GNPOld         float(10, 2)                null,
    LocalName      char(45)     default ''     not null,
    GovernmentForm char(45)     default ''     not null,
    HeadOfState    char(60)                    null,
    Capital        int                         null,
    Code2          char(2)      default ''     not null,
    PRIMARY KEY(`Code`,`Continent`)
)
    PARTITION BY list (Continent ) PARTITIONS 7
    ( PARTITION part0 VALUES IN (0),-- 'Africa'
    PARTITION part1 VALUES IN (1),-- 'Antarctica'
    PARTITION part2 VALUES IN (2),-- 'Asia'
    PARTITION part3 VALUES IN (3),-- 'Europe'),
    PARTITION part4 VALUES IN (4),-- 'North America'
    PARTITION part5 VALUES IN (5),-- 'Oceania'
    PARTITION part6 VALUES IN (6)-- 'South America'
    )

;

初始化country_parth的数据。

insert into country_part
select Code, Name,
       (case when Continent = 'Africa' then 0
            when Continent='Antarctica'then 1
            when Continent='Asia'then 2
            when Continent='Europe'then 3
            when Continent='North America'then 4
            when Continent='Oceania'then 5
            when Continent='South America'then 6
            else '0'
          end ) as Continent
       , Region, SurfaceArea, IndepYear, Population, LifeExpectancy, GNP,
                           GNPOld, LocalName, GovernmentForm, HeadOfState, Capital, Code2
from world.country;
commit ;

我们可以看到part0分区中都是continent 字段为0的数据。

select distinct continent from country_part partition(part0)

新建包含子分区数据库表

我们新建一个数据库表，根据人口数量字段对城市建立几个分区，分别是100w以下人口城市，500w以下人口城市，1000w以下人口城市，和1000w以上人口城市。

另外分区内根据continent建立子分区。

create table city_subpartition
(
    ID          int,
    Name        char(35) default '' not null,
    CountryCode char(3)  default '' not null,
    District    char(20) default '' not null,
    Population  int      default 0  not null,
    Continent   int      default 0  not null,
    primary key (ID, Population ,Continent)
)
    PARTITION BY RANGE (`Population`)
    SUBPARTITION BY HASH (`Continent`)(
        PARTITION p0 VALUES LESS THAN ( 1000000)
        (
        SUBPARTITION sp00,
        SUBPARTITION sp01,
        SUBPARTITION sp02,
        SUBPARTITION sp03,
        SUBPARTITION sp04,
        SUBPARTITION sp05,
        SUBPARTITION sp06
        ),
        PARTITION p1 VALUES LESS THAN ( 5000000)
        (
        SUBPARTITION sp10,
        SUBPARTITION sp11,
        SUBPARTITION sp12,
        SUBPARTITION sp13,
        SUBPARTITION sp14,
        SUBPARTITION sp15,
        SUBPARTITION sp16
        ),
        PARTITION p2 VALUES LESS THAN ( 10000000)
        (
        SUBPARTITION sp20,
        SUBPARTITION sp21,
        SUBPARTITION sp22,
        SUBPARTITION sp23,
        SUBPARTITION sp24,
        SUBPARTITION sp25,
        SUBPARTITION sp26
        ),
        PARTITION p3 VALUES LESS THAN MAXVALUE
        (
        SUBPARTITION sp30,
        SUBPARTITION sp31,
        SUBPARTITION sp32,
        SUBPARTITION sp33,
        SUBPARTITION sp34,
        SUBPARTITION sp35,
        SUBPARTITION sp36
        )
        );

初始化该表数据

insert into city_subpartition
select a.* , b.Continent   from city  a left join country_part  b on  a.CountryCode = b.Code;
commit ;

我们可以通过直接查询分区获得相应的数据。需要注意mysql查询子分区，直接使用partition加上子分区名称即可。在oracle中，使用subpartition 关键字进行查询。

select * from city_subpartition partition(p2) ; -- 500w~1000w人口的城市

select * from city_subpartition partition (sp20); -- 500w~1000w人口的城市 中的亚洲城市

向其他用户授权

我们使用的用户具备读写权限，但是有其他需要查询我数据库表数据，我们不能把我们自己的用户提供给对方使用，或者想限制用户只能查询某几个表。这种情况我们需要为其建立一个用户，并且将我们自己的表的查询权限赋予给这个用户。

一般为 grant 权限 on 表名 to 用户。

grant select on city to userb ;

同理也可以把插入删除更新的权限赋予给对应的用户。

grant insert,update,delete on city to userC;

索引

新建索引

创建合适的索引可以加快数据得检索速度，但是相应在大量数据插入时，对于索引的维护会增加大量的时间。

索引可以使用单个字段，也可以多个字段建立联合索引。

create index idx_city_country_code1  on city (CountryCode);
create index idx_city_country_code2  on city (CountryCode,Population);

数据修改

INSERT INTO 使用

INSERT INTO 语句用于向表中插入新记录。

INSERT INTO 语句可以有2种编写形式。

第一种形式无需指定要插入数据的列名，只需提供被插入的值即可：

INSERT INTO table_name VALUES (value1,value2,value3,...);

第二种形式需要指定列名及被插入的值：

INSERT INTO table_name (column1,column2,column3,...) VALUES (value1,value2,value3,...);

还有一种情况insert的数值来源于另外一个查询的结果集，这时候需要结果数据集的列数与所插入的表要对应。

INSERT INTO table_name (column1,column2,column3,...) select value1,value2,value3,... from table2 ;

插入DEMO数据

向city、country、countrylanguage中插入数据。

city数据

country数据

countrylanguage数据

DELETE 使用

DELETE 语句用于删除表中的记录。

DELETE FROM table_name
WHERE some_column=some_value;

删除该表所有数据（风险操作）：

DELETE FROM table_name;
DELETE * FROM table_name;

TRUNCATE TABLE tablename；-- 清除后的数据无法恢复

delete删除大量数据时可能会导致数据库undo表空间撑满，而引发回滚。
truncate不占用undo表空间，但是无法恢复删除数据。
可采用分批delete数据，或者在表设计时合理划分表空间，采用truncate表空间方式清理数据。

UPDATE 使用

Update 语句用于修改表中的数据。

UPDATE table_name
SET column1=value1,column2=value2,...
WHERE some_column=some_value;

请注意 SQL UPDATE 语句中的 WHERE 子句！

WHERE 子句规定哪条记录或者哪些记录需要更新。如果您省略了 WHERE 子句，所有的记录都将被更新！

数据查询（重要常用操作）

SELECT 使用

SELECT 语句用于从数据库中选取数据。

结果被存储在一个结果表中，称为结果集。

SELECT column_name1,column_name2 FROM table_name;

SELECT * FROM table_name;

SELECT DISTINCT 使用

SELECT DISTINCT 语句用于返回唯一不同的值。

SELECT DISTINCT column_name FROM table_name;

SELECT DISTINCT column_name1,column_name2 FROM table_name; -- 多个字段效果等同于下面的语句
SELECT column_name1,column_name2 FROM TABLE_NAME GROUP BY column_name1,column_name2

数据过滤

WHERE 使用

WHERE 子句用于提取那些满足指定条件的记录。

SELECT column_name,column_name
FROM table_name
WHERE column_name operator value;

WHERE 子句中的运算符：

in 中间包含的数值数量不应超过1000


=	等于
<>	不等于。注释：在 SQL 的一些版本中，该操作符可被写成 !=
>	大于
<	小于
>=	大于等于
<=	小于等于
BETWEEN	在某个范围内
LIKE	搜索某种模式
IN	指定针对某个列的多个可能值

AND & OR 运算符

AND & OR 运算符用于基于一个以上的条件对记录进行过滤。

如果第一个条件和第二个条件都成立，则 AND 运算符显示一条记录。

如果第一个条件和第二个条件中只要有一个成立，则 OR 运算符显示一条记录。

限制返回行数

limit

LIMIT 子句是mysql的一个关键字，在mysql中被用于强制 SELECT 语句返回指定的记录数。LIMIT 接受一个或两个数字参数。参数必须是一个整数常量。如果给定两个参数，第一个参数指定第一个返回记录行的偏移量，第二个参数指定返回记录行的最大数目。初始记录行的偏移量是 0(而不是 1)：为了与 PostgreSQL 兼容，MySQL 也支持句法： LIMIT # OFFSET #。

mysql> SELECT * FROM table LIMIT 5,10; // 检索记录行 6-15   
//为了检索从某一个偏移量到记录集的结束所有的记录行，可以指定第二个参数为 -1：   

mysql> SELECT * FROM table LIMIT 95,-1; // 检索记录行 96-last.   
//如果只给定一个参数，它表示返回最大的记录行数目：    

mysql> SELECT * FROM table LIMIT 5; //检索前 5 个记录行   
//换句话说，LIMIT n 等价于 LIMIT 0,n。

rownum

rownum是oracle特有的一个关键字。

　　（1）对于基表，在insert记录时，oracle就按照insert的顺序，将rownum分配给每一行记录，因此在select一个基表的时候，rownum的排序是根据insert记录的顺序显示的。

　　（2）对于子查询，则rownum的顺序是根据子查询的查询顺序进行动态分配的。

select * from emp where rownum=1;--emp表中的第一条记录：

select * from emp where rownum<=2;--查看emp中的前2条记录：

select * from emp where rownum=2;--查看emp中的第二条记录

数据排序

ORDER BY

ORDER BY 关键字用于对结果集进行排序。

ORDER BY 关键字用于对结果集按照一个列或者多个列进行排序。

ORDER BY 关键字默认按照升序对记录进行排序。如果需要按照降序对记录进行排序，您可以使用 DESC 关键字。

ORDER BY 字段 ASC

默认是升序排列，可以不加asc

ORDER BY 字段 DESC

字段降序排列

ORDER BY 多个字段

多个字段排序使用逗号分割

SELECT * FROM Websites ORDER BY country,alexa;

数据分组

group by

在使用聚集函数是需要使用 group by字句来标注分组的条件。

根据那个字段进行分组，则在group by 后面增加这个字段名。

常用的聚集函数
函数	说明
COUNT()	总行数
MAX()	最大值
MIN()	最小值
SUM()	求和
AVG()	平均值

需要说明的是 AVG、MAX、MIN 等聚集函数会自动忽略值为 NULL 的数据行，MAX 和 MIN 函数也可以用于字符串类型数据的统计，如果是英文字母，则按照 A—Z 的顺序排列，越往后，数值越大。如果是汉字则按照全拼拼音进行排列。

SELECT COUNT(*) FROM tables -- 查询所有记录条数

SELECT col,COUNT(*) FROM tables  group by col -- 根据col分区查询所有分组的记录条数

having

having字句根据聚合函数的数值进一步对结果数据进行过滤。HAVING 的作用和 WHERE 一样，都是起到过滤的作用，只不过 WHERE 是用于数据行，而 HAVING 则作用于分组。

SELECT col,COUNT(*) FROM tables  group by col having count(1)>100  -- 根据col分区查询所有分组的记录条数,并且只显示记录数大于100的分组。

数据表连接查询

笛卡尔积

笛卡尔乘积是一个数学运算。假设我有两个集合 X 和 Y，那么 X 和 Y 的笛卡尔积就是 X 和 Y 的所有可能组合，也就是第一个对象来自于 X，第二个对象来自于 Y 的所有可能。

X表37条记录

Y表3条记录

两个表做笛卡尔积连接后将会有37*3=111条记录

select * from x , y

等值连接

两张表的等值连接就是用两张表中都存在的列进行连接。我们也可以对多张表进行等值连接。在笛卡尔积的基础上使用where的中的条件过滤出id值相等的内容。

SELECT x.*,y.* FROM x, y WHERE x.id = y.id

非等值连接

当我们进行多表查询的时候，如果连接多个表的条件是等号时，就是等值连接，其他的运算符连接就是非等值查询。

SELECT p.player_name, p.height, h.height_level
FROM player AS p, height_grades AS h
WHERE p.height BETWEEN h.height_lowest AND h.height_highest

外连接

除了查询满足条件的记录以外，外连接还可以查询某一方不满足条件的记录。两张表的外连接，会有一张是主表，另一张是从表。如果是多张表的外连接，那么第一张表是主表，即显示全部的行，而第剩下的表则显示对应连接的信息。在 SQL92 中采用（+）代表从表所在的位置，而且在 SQL92 中，只有左外连接和右外连接，没有全外连接。

左外连接：LEFT JOIN 或 LEFT OUTER JOIN

右外连接：RIGHT JOIN 或 RIGHT OUTER JOIN

全外连接：FULL JOIN 或 FULL OUTER JOIN

SELECT * FROM x LEFT JOIN y on x.id = y.id -- 左外连接


SELECT * FROM x RIGHT JOIN y on x.id = y.id -- 右外连接

内连接

进查询出两个数据集都有的操作。仅返回x表与y表中id相等的数据。

select * from x inner join y  on x.id= y.id

视图

新建视图

如果需要查看每个城市的官方语言，这时候需要将city表和countrylanguage表进行关联。如果建立新表还需要经常进行数据同步，更新数据，这时候可以建立视图，在使用时直接查询视图当做表去使用。

create view v_city_language as 
select a.ID, a.Name, a.CountryCode, a.District, a.Population
, b.Language, b.IsOfficial, b.Percentage
from city a left join countrylanguage b on a.CountryCode = b.CountryCode
where  IsOfficial = 'T'

视图的使用

像使用表一样使用，直接查询视图，输入查询条件h

select * from v_city_language where name = 'Kabul'--

--------------------------------------------------------
1,Kabul,AFG,Kabol,1780000,Dari,T,32.1   
1,Kabul,AFG,Kabol,1780000,Pashto,T,52.4

视图修改

如果需要对试图进行修改，打算删除官方语言的约束条件，在视图的查询语句前加上create or replace view 即可u

create or replace view v_city_language as
select a.ID, a.Name, a.CountryCode, a.District, a.Population
, b.Language, b.IsOfficial, b.Percentage
from city a left join countrylanguage b on a.CountryCode = b.CountryCode

函数

函数使用

select max(Population) from city ;
select avg(Population) from city ;
select count(a.CountryCode) from city a ;

select count(a.Language ) from countrylanguage  a  ;
select count(distinct a.Language ) from countrylanguage  a  ;

其他特殊用法

ORACLE中的dual

如果像计算两个值，可以使用oracle的一个虚拟的表，他的用途还是比较多。

select 1+3 from dual

Mysql中类似oracle dual查询

mysql 中如果需要计算两个数值，达到oracle dual 的类似效果，可以使用下面的方式

select 1+8*2

数据库与SQL使用Demo

连接数据库

PLSQL连接oracle数据库

操作步骤

MySQL Workbench 连接mysql数据库

需要准备

Mysql的访问ip限制

操作步骤

新建schema

新建schema

在workbench中切换shcema

新建数据库表

新建普通数据库表

新建分区数据库表

新建包含子分区数据库表

向其他用户授权

索引

新建索引

数据修改

INSERT INTO 使用

插入DEMO数据

DELETE 使用

UPDATE 使用

数据查询（重要常用操作）

SELECT 使用

SELECT DISTINCT 使用

数据过滤

WHERE 使用

AND & OR 运算符

限制返回行数

limit

rownum

数据排序

ORDER BY

ORDER BY 字段 ASC

ORDER BY 字段 DESC

ORDER BY 多个字段

数据分组

group by

having

数据表连接查询

笛卡尔积

等值连接

非等值连接

外连接

内连接

视图

新建视图

视图的使用

视图修改

函数

函数使用

其他特殊用法

ORACLE中的dual

Mysql中类似oracle dual查询

导航菜单

搜索