ORACLE常用的SQL
ORACLE常用命令 spool host 无 -- 使用sqlplus连接数据库-- 标准用法sqlplus username/password@hostname:port/SERVICENAME-- 示例sqlplus payment/password@192.168.117.1/pay-- hostname:port/SERVICENAME 可配置为别名到 tnsnames.ora 文件sqlplus payment
ORACLE常用命令spool
host
-- 使用sqlplus连接数据库 -- 标准用法 sqlplus username/password@hostname:port/SERVICENAME -- 示例 sqlplus payment/password@192.168.117.1/pay -- hostname:port/SERVICENAME 可配置为别名到 tnsnames.ora 文件 sqlplus payment/password@tnsname -- 先进入 sqlplus, 然后使用 conn 方法进行连接 sqlplus /nolog conn payment/password@192.168.117.1/pay -- copy 几条 -- 操作系统认证,不需要listener进程 sqlplus / as sysdba -- 只能连接本机数据库,同样不需要listener进程 sqlplus username/password -- 这种方式需要listener进程处于可用状态。最普遍的通过网络连接。 sqlplus username/password@tnsname
-- 登录服务器, 切换至oracle用户 su - oracle -- 进入文件夹,后续所有对文件的操作都是基于该目录 cd /oracle/database/ -- sqlplus 连接具体的数据库 sqlplus payment/password -- 确定用户是否正确 show user; -- 执行payment.sql -- 所有的内容输出至 payment.log,该文件位于 /oracle/database/ 目录 spool payment.log @payment_indexs_execute.sql spool off -- 使用host 命令可以转为 linux 命令执行 host more payment.log -- 还可使用 conn 命令连接数据库 conn payment/password show user;
-- 查看数据库表的索引 SELECT * FROM USER_INDEXS WHERE TABLE_NAME=’TableName’; -- 查看索引所建立的列信息 SELECT * FROM USER_IND_COLUMNS WHERE INDEX_NAME=UPPER(‘IndexName’); -- 创建主键 --1) 创建表的时候指定主键 CREATE TABLE Persons ( P_ID int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City varchar(255), CONSTRAINT PK_Persons PRIMARY KEY (P_ID,LastName) ) --2) ALTER Table 添加的两种方法 ALTER TABLE TableName ADD PRIMARY KEY(Column1); -- 这种方式是否支持多列? 这种方式没有指定主键名称,主键默认名称是什么 ALTER TABLE TableName ADD CONSTRAINT PK_TabeName PRIMARY KEY(Column1, Column2); -- 创建唯一性索引 ALTER TABLE TableName ADD CONSTRAINT U_INDEX_NAME UNIQUE (Column1, Column2); CREATE UNIQUE INDEX U_INDEX_NAME ON TableName(Column1, Column2); -- 创建普通索引 是否有ALTER 方法创建? CREATE INDEX I_INDEX_NAME ON TableName (Column1, Column2); -- 当索引列存在重复时, 创建唯一性索引会报 “找到重复关键字" /*1. 将重复的记录进行修改 2. 建立 enable novalidate 索引*/ ALTER TABLE TableName ADD CONSTRAINT U_IDX_NAME ENABLE NOVALIDATE; -- 修改索引类型: ENABLE NOVLIDATE —> ENABLE VALIDATE ALTER TABLE TableName ENABLE VALIDATE CONSTRAINT U_IDX_NAME; -- 删除索引 ALTER TABLE TableName DROP CONSTRAINT U_IDX_NAME; -- 将表设置为只读表 ALTER TABLE T READ ONLY; -- 取消只读(设置为读写) ALTER TABLE T READ WRITE;
-- 更新一个字段 /* 括号内的conditions 为 Table1和Table2的关联 括号外的conditions 为 Table1更新的范围 */ UPDAE Table1 SET Column1=( SELECT Column FROM Table2 WHERE Conditions ) WHERE Conditions; -- 更新多个字段,多个字段之间使用逗号 , 分割 UPDATE Table1 SET Column1, Column2=( SELECT Column1, Column2 FROM Table2 WHERE Conditions ) WHERE Conditions; -- 将001和002机构的MCC,MCCDescription 两个字段 关联MCCDetail表 进行更新 UPDATE Merchant t1 SET MCC,MCCDescription=( SELECT MCC,MCCDescription FROM MCCDetail t2 WHERE T1.MCC=T2.MCC ) WHERE T1.MerchantNo IN ('001', '002');

Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Article

Hot Tools

Notepad++7.3.1
Easy-to-use and free code editor

SublimeText3 Chinese version
Chinese version, very easy to use

Zend Studio 13.0.1
Powerful PHP integrated development environment

Dreamweaver CS6
Visual web development tools

SublimeText3 Mac version
God-level code editing software (SublimeText3)

Hot Topics

Solutions to Oracle cannot be opened include: 1. Start the database service; 2. Start the listener; 3. Check port conflicts; 4. Set environment variables correctly; 5. Make sure the firewall or antivirus software does not block the connection; 6. Check whether the server is closed; 7. Use RMAN to recover corrupt files; 8. Check whether the TNS service name is correct; 9. Check network connection; 10. Reinstall Oracle software.

The method to solve the Oracle cursor closure problem includes: explicitly closing the cursor using the CLOSE statement. Declare the cursor in the FOR UPDATE clause so that it automatically closes after the scope is ended. Declare the cursor in the USING clause so that it automatically closes when the associated PL/SQL variable is closed. Use exception handling to ensure that the cursor is closed in any exception situation. Use the connection pool to automatically close the cursor. Disable automatic submission and delay cursor closing.

In Oracle, the FOR LOOP loop can create cursors dynamically. The steps are: 1. Define the cursor type; 2. Create the loop; 3. Create the cursor dynamically; 4. Execute the cursor; 5. Close the cursor. Example: A cursor can be created cycle-by-circuit to display the names and salaries of the top 10 employees.

To stop an Oracle database, perform the following steps: 1. Connect to the database; 2. Shutdown immediately; 3. Shutdown abort completely.

SQL statements can be created and executed based on runtime input by using Oracle's dynamic SQL. The steps include: preparing an empty string variable to store dynamically generated SQL statements. Use the EXECUTE IMMEDIATE or PREPARE statement to compile and execute dynamic SQL statements. Use bind variable to pass user input or other dynamic values to dynamic SQL. Use EXECUTE IMMEDIATE or EXECUTE to execute dynamic SQL statements.

Building a Hadoop Distributed File System (HDFS) on a CentOS system requires multiple steps. This article provides a brief configuration guide. 1. Prepare to install JDK in the early stage: Install JavaDevelopmentKit (JDK) on all nodes, and the version must be compatible with Hadoop. The installation package can be downloaded from the Oracle official website. Environment variable configuration: Edit /etc/profile file, set Java and Hadoop environment variables, so that the system can find the installation path of JDK and Hadoop. 2. Security configuration: SSH password-free login to generate SSH key: Use the ssh-keygen command on each node

An AWR report is a report that displays database performance and activity snapshots. The interpretation steps include: identifying the date and time of the activity snapshot. View an overview of activities and resource consumption. Analyze session activities to find session types, resource consumption, and waiting events. Find potential performance bottlenecks such as slow SQL statements, resource contention, and I/O issues. View waiting events, identify and resolve them for performance. Analyze latch and memory usage patterns to identify memory issues that are causing performance issues.

Triggers in Oracle are stored procedures used to automatically perform operations after a specific event (insert, update, or delete). They are used in a variety of scenarios, including data verification, auditing, and data maintenance. When creating a trigger, you need to specify the trigger name, association table, trigger event, and trigger time. There are two types of triggers: the BEFORE trigger is fired before the operation, and the AFTER trigger is fired after the operation. For example, the BEFORE INSERT trigger ensures that the age column of the inserted row is not negative.
