Chapter 1 Basic SQL statements (Basic SQL statements) and Chapter 1 Basic SQL statements

Chapter 1 Basic SQL statements (Basic SQL statements) and Chapter 1 Basic SQL statements

1. query the database system time, which is usually displayed in the default format of the server (depending on the character set of the database );
Note: dual is a virtual table in the database and belongs to the Administrator sys user, but all users can access it. It has no practical significance, act only as the structure of the select statement (dual serves as the statement structure when the system information and temporary results are obtained using the select statement );

TYGER @ ORCL> select sysdate from dual;


SYSDATE
---------
18-jun14


1.1 modify the display format of the system time. (The session indicates that the modification is valid for the current session)
TYGER @ ORCL> alter session set nls_date_format = 'yyyy-mm-dd hh24: mi: ss ';


Session altered.


TYGER @ ORCL> select sysdate from dual;


SYSDATE
-------------------
16:11:37


1.2 add or subtract the system time;
1.2.1 add or subtract a number to indicate the number of days after the given time;
TYGER @ ORCL> select sysdate from dual;


SYSDATE
-------------------
16:11:37


TYGER @ ORCL> select sysdate + 2, sysdate-2 from dual; (2 days, 2 days ago)


SYSDATE-2 SYSDATE + 2
--------------------------------------
16:13:58 16:13:58


1.2.2 how many hours, minutes, And seconds are appended (2 hours, 2 minutes, and 2 seconds)
TYGER @ ORCL> select sysdate, sysdate + 2/24, sysdate + 2/24/60, sysdate + 2/24/60/60 from dual;


SYSDATE + 2/24 SYSDATE + 2/24/60 SYSDATE + 2/24/60/60
----------------------------------------------------------------------------
16:15:50 18:15:50 16:17:50


Note: The modification of the preceding time format is only valid for the current session;
Only temporary data is displayed and the original data values in the table are not changed.

Basic SQL statements

Basic SQL statements are added (data insertion)
Insert into table name values (column name, column name .....)
Delete
Delete from table name WHERE add Condition
Change
UPDATE table name SET column name WHERE add Condition
Query
SELECT column name FROM table name WHERE plus Condition

What are the basic SQL statements?

Add: inser [into] Table Name (column name, column name,...) values (value, value ....)
Delete: delete table name [where column name = value]
Change: update table name: set column name = value, column name = value... [where column name = value]
Query: select column name, column name from table name [where column name = value]
Note: The words in [] in the add statement can be omitted.
If the content in [] is not written in the delete statement, all data in the data table will be deleted. if the content is written, only the qualified data rows will be deleted.
If [] is not written in the change statement, the values of all specified columns in the data table are modified. If it is written, only the values of specified columns in the Data row that meet the conditions are modified.