查看“数据库与SQL使用Demo”的源代码

= 连接数据库 =

== 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<nowiki/>，根据操作系统版本选择不同的版本进行安装。高版本的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
=== 操作步骤 ===
1、打开MySQL Workbench

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

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

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

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

= 新建schema =

=== 新建schema ===
<syntaxhighlight lang="sql" line="1">
CREATE DATABASE `world` 
/*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ 
/*!80016 DEFAULT ENCRYPTION='N' */;

</syntaxhighlight>

=== 在workbench中切换shcema ===
双击需要进行操作的视图名称，视图名称会加粗，即表示sql脚本将会使用该视图。
或者使用下面的方式切换语句<syntaxhighlight lang="sql" line="1">
use world;
</syntaxhighlight>
'''不同的工具可能切换schema的方式不一样，在执行语句前（尤其是删除、插入、更新操作）要确认好自己当前的schema，或者在表名前面加上模式的名称。'''
= 新建数据库表 =

== 新建普通数据库表 ==

下面新建三张表city、country、contrylanguage<syntaxhighlight lang="sql" line="1">
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;

</syntaxhighlight><syntaxhighlight lang="sql" line="1">
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;

</syntaxhighlight><syntaxhighlight lang="sql" line="1">
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;

</syntaxhighlight>


== 新建分区数据库表 ==

我们新建一个数据库表，根据日期建立分区。<syntaxhighlight lang="sql" line="1">
select distinct continent from country ;
</syntaxhighlight>查询所有的国家位于以下几个州，我们建立一个country的分区表.

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

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

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


同时还需要注意，分区字段要包含在主键当中。作为联合主键。<syntaxhighlight lang="sql" line="1">
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'
    )

;
</syntaxhighlight>


初始化country_parth的数据。<syntaxhighlight lang="sql" line="1">
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 ;
</syntaxhighlight>我们可以看到part0分区中都是continent 字段为0的数据。<syntaxhighlight lang="sql" line="1">
select distinct continent from country_part partition(part0)
</syntaxhighlight>

== 新建包含子分区数据库表 ==

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

另外分区内根据continent建立子分区。<syntaxhighlight lang="sql" line="1">

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
        )
        );


</syntaxhighlight>

初始化该表数据<syntaxhighlight lang="sql" line="1">

insert into city_subpartition
select a.* , b.Continent   from city  a left join country_part  b on  a.CountryCode = b.Code;
commit ;
</syntaxhighlight>
我们可以通过直接查询分区获得相应的数据。需要注意mysql查询子分区，直接使用partition加上子分区名称即可。在oracle中，使用subpartition 关键字进行查询。<syntaxhighlight lang="sql" line="1">

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

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

</syntaxhighlight>

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

一般为 grant 权限  on  表名  to  用户。<syntaxhighlight lang="sql" line="1">
grant select on city to userb ; 
</syntaxhighlight>


同理也可以把插入删除更新的权限赋予给对应的用户。<syntaxhighlight lang="sql" line="1">
grant insert,update,delete on city to userC;
</syntaxhighlight>

= 索引 =

== 新建索引 ==

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

索引可以使用单个字段，也可以多个字段建立联合索引。<syntaxhighlight lang="sql" line="1">
create index idx_city_country_code1  on city (CountryCode);
create index idx_city_country_code2  on city (CountryCode,Population);
</syntaxhighlight>

= 数据修改 =

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

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

第一种形式无需指定要插入数据的列名，只需提供被插入的值即可：<syntaxhighlight lang="sql" line="1">
INSERT INTO table_name VALUES (value1,value2,value3,...);
</syntaxhighlight>第二种形式需要指定列名及被插入的值：<syntaxhighlight lang="sql">
INSERT INTO table_name (column1,column2,column3,...) VALUES (value1,value2,value3,...);
</syntaxhighlight>还有一种情况insert的数值来源于另外一个查询的结果集，这时候需要结果数据集的列数与所插入的表要对应。<syntaxhighlight lang="sql" line="1">
INSERT INTO table_name (column1,column2,column3,...) select value1,value2,value3,... from table2 ;
</syntaxhighlight>

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

[[city数据]]

[[country数据]]

[[countrylanguage数据]]

==DELETE 使用==
DELETE 语句用于删除表中的记录。<syntaxhighlight lang="sql" line="1">
DELETE FROM table_name
WHERE some_column=some_value;
</syntaxhighlight>



删除该表所有数据<u>'''（风险操作）'''</u>：<syntaxhighlight lang="sql">
DELETE FROM table_name;
DELETE * FROM table_name;

TRUNCATE TABLE tablename；-- 清除后的数据无法恢复
</syntaxhighlight><blockquote>delete删除大量数据时可能会导致数据库undo表空间撑满，而引发回滚。

truncate不占用undo表空间，但是无法恢复删除数据。

可采用分批delete数据，或者在表设计时合理划分表空间，采用truncate表空间方式清理数据。</blockquote>
==UPDATE 使用==
Update 语句用于修改表中的数据。<syntaxhighlight lang="sql">
UPDATE table_name
SET column1=value1,column2=value2,...
WHERE some_column=some_value;
</syntaxhighlight><blockquote>'''请注意 SQL UPDATE 语句中的 WHERE 子句！'''</blockquote>WHERE 子句规定哪条记录或者哪些记录需要更新。如果您省略了 WHERE 子句，所有的记录都将被更新！





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


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

结果被存储在一个结果表中，称为结果集。<syntaxhighlight lang="sql" line="1">
SELECT column_name1,column_name2 FROM table_name;

SELECT * FROM table_name;

</syntaxhighlight>
==SELECT DISTINCT 使用==
SELECT DISTINCT 语句用于返回唯一不同的值。<syntaxhighlight lang="sql" line="1">
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 
</syntaxhighlight>

= 数据过滤与排序 =

==WHERE 使用==
WHERE 子句用于提取那些满足指定条件的记录。<syntaxhighlight lang="sql" line="1">
SELECT column_name,column_name
FROM table_name
WHERE column_name operator value;
</syntaxhighlight>WHERE 子句中的运算符：

in 中间包含的数值数量不应超过1000
{| class="wikitable"
|+
|=
|等于
|-
|<>
|不等于。'''注释：'''在 SQL 的一些版本中，该操作符可被写成 !=
|-
|>
|大于
|-
|<
|小于
|-
|>=
|大于等于
|-
|<=
|小于等于
|-
|BETWEEN
|在某个范围内
|-
|LIKE
|搜索某种模式
|-
|IN
|指定针对某个列的多个可能值
|}
==AND & OR 运算符==
AND & OR 运算符用于基于一个以上的条件对记录进行过滤。

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

如果第一个条件和第二个条件中只要有一个成立，则 OR 运算符显示一条记录。
==ORDER BY==
ORDER BY 关键字用于对结果集进行排序。

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

ORDER BY 关键字默认按照升序对记录进行排序。如果需要按照降序对记录进行排序，您可以使用 DESC 关键字。
==ORDER BY 字段 ASC==
默认是 升序排列，可以不加asc
==ORDER BY 字段 DESC==
字段降序排列
==ORDER BY 多个字段==
多个字段排序使用逗号分割<syntaxhighlight lang="sql" line="1">

SELECT * FROM Websites ORDER BY country,alexa;

</syntaxhighlight>

= 视图 =

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

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'
</syntaxhighlight>


== 视图的使用 ==
像使用表一样使用，直接查询视图，输入查询条件h<syntaxhighlight lang="sql" line="1">
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
</syntaxhighlight>

== 视图修改 ==

如果需要对试图进行修改，打算删除官方语言的约束条件，在视图的查询语句前加上'''create or replace view''' 即可u<syntaxhighlight lang="sql" line="1">
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
</syntaxhighlight>

= 函数 =
==函数使用==
<syntaxhighlight lang="sql" line="1">
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  ;
</syntaxhighlight>

=其他特殊用法=


==ORACLE中的dual==

如果像计算两个值，可以使用oracle的一个虚拟的表，他的用途还是比较多。<syntaxhighlight lang="sql" line="1">
select 1+3 from dual 
</syntaxhighlight>


==Mysql中类似oracle dual查询==
mysql 中如果需要计算两个数值，达到oracle dual 的类似效果，可以使用下面的方式<syntaxhighlight lang="sql" line="1">
select 1+8*2 
</syntaxhighlight>