数据库与SQL使用Demo

连接数据库

PLSQL连接oracle数据库

需要准备

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

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

instant-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

操作步骤

1、打开MySQL Workbench

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

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

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

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

新建schema

新建schema

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

在workbench中切换shcema

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

新建数据库表

新建普通数据库表

下面新建三张表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;

数据修改

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、contrylanguage中插入数据。

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 运算符显示一条记录。

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;