Shulou(Shulou.com)06/01 Report--

View which tables Oracle users can use Data Dictionary tables (Manage user all tables)

desc user_tables;(View tables that can be manipulated)select tables_name from user_tables;(SCOTT users can use four tables) Character types are all left-aligned, numeric types are all right-aligned NULL is unavailable, unassigned, unknown, or unused NULL is different from zero or space

Set display line spacing

set linesize 200; width is 200set pagesize 50; reality 50 lines per page create table test(hiredate varchar(20)); create table

View tables available to the current user

select table_name from user_tables;

Default settings for titles

Character and date column headings are aligned as: Left alignment Numeric column headings are aligned as: Right alignment Default heading display: Upper case null values are not equal to zero or spaces null values are brought in four operations are still null values

define aliases

Column aliases have the following characteristics: rename the title, help with calculations, immediately follow the column name (optional keyword AS can also be added between column name and alias) Double quotes are required if the alias includes spaces or special characters, or case sensitivity

webpage identifier

select ename|| job from emp; combine two column names and display them together

duplicate rows

select distinct deptno from emp;distinct ()

Show only null values

select * from emp where mgr is null ;

logical judgment

AND returns true if both conditions are true(returns a value if both conditions are true)OR if one of the conditions is true. trueNOT Returns true if the condition is false

sqlplus clear screen

priority rule

operator meaning addition subtraction multiplication division arithmetic operator pipe symbolic linking operator where comparison operator null IS,[not], null, like,[not], in5[not] DETWEEN!= not equal to condition false returns trueNOT logical condition and AND logical condition or OR logical condition

Use ORDER BY sentences

Use the order by clause to sort the searches ASC: ascending (default ascending) from small to large DESC: descending order by clause The last sort in the select statement can be an alias or a number select ename, sal gz from emp order by 2; the result is that wages are sorted in descending order or can be sorted by chapter in multiple columns select ename,sal,deptno from emp order by 3,2 desc; sal descending (from large to small)

Close database and open

shutdown normal startup mount

substitution variable

Use single ampersand (&) and double (&&)(multiple times) instead of temporarily storing values where condition order by clause column expressions, table names, entire select statements cache column names, expressions, and text variables specified in memory Character types are enclosed in single quotes

Using the DEFIN command

Use the define command to create variables and assign values to them Use undefined to delete variables Define abc=20select * from emp where depton= Delete variables Define abc=20 and then search for input

SQL functions

Single-row functions (each returning a result) Multi-row functions (each returning a result) Process data items, accept parameters and return a value, process each return, return a result for each row, may modify data types, may nest, accept parameters, these parameters can be columns or expressions Single-row function characters, numbers, general, dates, conversions

character functions

Case conversion functions LOWER, UPPER, INITCAP, character processing functions CONCAT, SUDSTR, LENGTH, function results LOWER ('SQL Course')(sql course) all lowercase UPPER ('SQL Course')(SQL COURSE) all uppercase INITCAP ('SQL Course')(Sql Course) all uppercase

dual pseudo-table (exists in memory does not exist in the database)

character processing

Function Results CONCAT ('Hello ',' World') HelloWorld(spliced together) supports two strings SUDSTR ('HelloWorld', 1,5)Hello(1 to 5) String truncation LENGTH ('HelloWorld ')10(string length)INSTR ('HelloWorld',' W') 6 (position of locating character)LPAD(salary,10,'*')**24000(left padding)RPAD(salary,10,'*')24000**(right padding)REPLACE ('JACK and JUE','J',' BL') BLACK and BLUETRIM ('H 'FROM ' HelloWorld') elloWorld

digital function

ROUND: rounds a value to the specified decimal place (-1 exact units)select round(45.923,2) from dual; returns 45.92TRUNC: truncates a value to the specified decimal place returns 40MOD: returns the remainder of a division operation select mob(10,3) from dual; returns 1

process date

oracle DB stores dates in internal numeric formats: century, year, month, day, hour, minute, second Default date format DD(2-digit character month)-MON(month January to December)-RR(two-digit dotting) Century 21st century or 20th century By specifying only the last two digits of the year, you can store dates from the 21st century in the 20th century, or from the 21st century.

RR Date Format

Date specified for current year RR format YY format 199527-OCT-951995199527-OCT-1720171997200127-OCT-172017200127-OCT-9519952095 If the two digits of current year are 0-49 Return date in current century If the two digits of current year are 50- 99 Returns the date in the century next to the current century if the specified two-digit year is 0-49 Returns the date in the current century if the specified two-digit year is 50-99 Returns the date in the century next to the current century

sysdate is a function that returns the following object: -date-time

date processing function

Function Result MONTHS_DETWEEN Number of months between two dates ADD_MONTHS Adds calendar month to date NEXT_DAY Next date after specified date LAST_DAY Last day of month Round date TRUNC Truncate date MONTHS_BETWEEN ('01-sep-95','11-jan-94')19.6774194ADD_MONTHS ('31-jan-96',1)29-feb-96NEXT_DAY ('01-SEP-95',FRIDAY)08-SEP-95LAST_DAY ('01-FEB-95')28-FEB-95ROUND (SYSDATE,'MONTH')(month month) Assuming sysdate='25-jul-03'01-AUG-03 (15 or more months go forward)ROUND(SYSDATE,'YEAR')(year)01-JAN-04(year goes forward one month) 6 or more years go forward January is greater than etc.)TRUNC(SYSDATE,' MONTH') 01-JUL-03TRUNC(SYSDATE,'YEAR') 01-JAN-03

Conversion functions and conditional expressions

Implicit data type conversion Display data type conversion Conversion of numeric type, character type, time type

Use the TO_CHAR function to process dates must be enclosed in single quotes case-sensitive can contain any valid date format element with an fm element, Use to remove filled spaces or hide comma-separated elements between leading zeros and date values Result YYYY Full Year Numbered YEAR Year MM Two-digit Month MONTH Full Name of Month MON Three-digit Abbreviations DY Three-letter Abbreviations DAY Full Name of Day of Week DD Numeric Month Day 9 Represents a Number 0 Force 0$Place a floating dollar symbol L Use a floating local currency symbol. Display decimal point, display comma as thousand-digit indicator

nested function

Single-line functions can be nested to any level. The calculation order of nested functions is from the innermost layer to the outermost layer. The normal function, and the null value is used appropriately, converts the null value to the actual value. The data types that can be used are dates, characters and numbers. The data types must match: NVL(expr1,exrp2)NVL2(EXPR1,EXRP2,EXRP3)NULLIF(expr1,expr2)

Group function reports aggregate data

AVG calculates the average count how many rows there are in a field max max min sum of a field count shows the number of rows distinct remove duplicate rows

Create a data set

group by grouping select deptno,avg(sal) from emp group by deptno; grouping average select deptno,avg(sal),job from emp group by deptno,job;HAVING clause limit group results select deptno,max(sal) from emp group by hashing max(sal)>=3000; using joins to display data from multiple tables using outer joins querying data that usually does not satisfy join conditions to generate Cartesian product of all rows in two or more tables join type natural link using clause outer link left outer join right outer join outer join cross linking creates natural links natural join clause based on all columns with the same name in two tables It selects which rows have the same value in all columns from two tables If columns with the same name have different data types an error is returned select deptno from dept natural join emp two queries have the same content show common values use using statement create link if more than one column has the same name but the data types do not match use using clause specify the columns of the equivalence join when more than one column wants to match use using clause can match only one column natural loin and using statements are mutually exclusive. select ename,deptno feom emp join dept using(deptno); Use table aliases in the using clause Do not qualify columns used in the using clause If the same column is used in another place in the sql statement, then do not alias it Use the ON clause to create a link The basic join condition for a natural join is to link all columns with the same name equivalently Use the ON clause to specify any condition or to specify columns to join Linkage conditions independent of other search conditions Use the ON clause to make code easy to understand

Inner join and outer join

In sql:1999, if a join of two tables returns only matching rows, it is called an inner join. A direct join of two tables returns not only the results of an inner join, but also unmatched rows in the left (or right) table. The join is called a left (or right) outer link. A join between two tables returns not only the results of an inner link, but also the results of a left and right join. The link is called a full outer join. A subquery is executed first (inner query) and then the main query (outer query). The main query uses the results of the subquery when a row subquery returns only one row.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.