Oracle (1) ----- basic SQL statements

The SQL language is case insensitive. SQL statements can be written in one or more rows. Keywords cannot be abbreviated or the sub-statements of different branches are generally written in different branches. Use indentation to improve statement readability start: Open the command window, enter sqlplus system orcl [password set during installation] Change Password: // change the password of the System user to systemalteruser system identified by system; alteruser identified by is a keyword. Clear screen: Host CLS; spool command is used to create 1.txt automatically when recording screen spoolc: \ 1.txt in sqlplus, recording screen spoolc: \ 1.txt append add to 1.txt -- meaning comment exit modify SQL statement :( 1) run C/alert/alter (incorrect/correct)> // run the previous command and the user has modified it (2) modify through ed modification --- professional modification> ed pop-up Notepad to modify show all ------- display all commands to switch SQL> conn Scott/Scott; if the switch fails: SQL> alter user Scott identified byscott; SP2-0640: unconnected SQL> show useruser is "" SQL> sqlplus system/orclSP2-0734: Unknown command starting with "sqlplussy... "-the remaining rows are ignored. SQL> exitsql> select ename from EMP; Arithmetic Operators used for numbers and dates: SQL> -- +-*/SQL> select Sal * 2 from EMP; operator priority: the priority of multiplication and division is higher than that of addition and subtraction. The same priority operator is executed from left to right. The operation in parentheses is executed first. Define null values: NULL values are invalid and unspecified. Unknown or unpredictable values are not empty spaces or 0. The values of mathematical expressions containing null values are all null. SQL> select Sal * 12 + nvl (Comm, 0) from EMP; [fill in with 0 when a null value is found in the information.] format: SQL> set linesize 120; SQL> Col Sal for 9999; after finding the information, do not let it automatically display "selected x rows": SQL> set feedback off; query information does not display the column name: SQL> set heading off; column alias: rename a column. Easy to calculate. Keep up with the column name. You can also add the keyword 'as' between the column name and the alias. the alias uses double quotation marks, so that the alias contains spaces or special characters and is case sensitive. Connector: concatenates columns and columns with characters. '|. It can be used to 'composite 'columns. SQL> select last_name | first_name as name from employees; string: A string can be a character, number, or date in the select list. The date and character can only appear in single quotes. Each time a row is returned, the string is output once. SQL> select last_name | 'is a' | job_idfrom employees; select first_name | last_name | ''s' | 'email is '| email from employees result: donaldoconnell's emailisdoconnel duplicate rows: by default, the query returns all rows, including duplicate rows. Delete duplicate rows: Use the keyword 'distinct 'in the select clause to delete duplicate rows. SQL> select distinct deptno from EMP; Result: deptno ------ 30 20 10 when the searched content is duplicated, display only one SQL statement and SQL * Plus command SQL • one language • ANSI standard • keywords cannot be abbreviated • use statements to control the definition information of tables in the database and data SQL * Plus • an environment • one of the features of Oracle • keyword can be abbreviated • command cannot change the value of data in the database • run in a centralized manner • use SQL * Plus can: describes the table structure. Edit an SQL statement. Execute SQL statements. Save the SQL statement in the file and save the SQL statement execution result in the file. Execute the statement in the saved file. Load the text file into the SQL * Plus editing window. Display table structure: Use the describe command to display the table structure SQL> DESC EMP;