SQL Syntax Value Oracle Introduction

In 1977, a relational database was proposed, and the large database products were Oracle, DB2 and Sybase, medium right SQL Server and MySQL, and small representative for access.
Body Common terminology for databases:DBMS is a database management system; DBMS is a relational database management system and DBA is the consciousness of database administrators Oracle installationThe installation process is simply skipped.Once installed, there is a database instance SID, which is the library name.
1) Remember to turn on both the oraclexxx (SID) Tnslistener service and the oracleservicexxx (SID) service before connecting to the database. The oraclexxx (SID) Tnslistener service, which is the client listener oracleservicexxx (SID), is the server service for the database.
Execute SQL statement tools, such as PlSqlDev.exe, are often used.The conn command is used to connect to a DB instance. Note:The Oracle database is provided by default for Scott/tiger as a test and learning use. The default is to provide test tables such as common emp,dept. You can use the DESC table name to view the table structure. Manipulating data

1. How data is stored (index files are more important than data files)

In the form of instruments, the data is stored in a two-dimensional table. A row is a record, and a column is a field.

2. The operational database uses the SQL (structure query language) Structured Query language.

The international standards specified in 1999 are currently in use. Database Vendor General (SQL99)

3. According to the function of SQL divided into:

DQL: (Data Query Language, select) is used to complete the query of data in the database.

DDL (data definition language, create,drop,truncate truncate table) operation table structure.

DML (Database manipulation language, insert,delete,update) operational data

TCL (transaction control language, transient controls language) Commit/rollback/savepoint operational transactions

4. Simple query statement:SELECT [Distinct] field names, where the child is not differentiated from the From table name-the keyword and the field casing each time. Note: 1). Oracle provides the Scott account by defaultThe default table for learning and testing is available under this account. 2). On the Plsql tool, frequently used commands: set pagesize 2 values;--Sets the number of columns to display per row. The column field name, Format a9--, shows the field given 9 word lengths, for the String column field name, format 9999--with 4 for the field to represent the number. /represents the execution of the last command stored in the cache. 5. Alias as keyword during query Select Field Name alias form table name;--as can be omitted, alias is separated from field name 6. Connection Fields | |Select Field 1| | Field 2 from EMP; 7. While querying the field for arithmetic operations +*-/ Select ename, sal*12 from EMP;1) NULL operation with any data is null
8.DESC table name query table structure

Show fields, types of fields, and lengths and constraints

Types of data commonly used in 9.oracle

1) number (n)

Numeric type, which does not differentiate between floating-point numbers and N for length.

2) Number (N,M)

Indicates that there are n bits in total and the decimal bit is M

3) Varchar2 string

4) Date Type

Conditional query Statements

Select field One, field two from table name where qualification

1. Filter results by comparison operator >, <,=,<>,!=

2. Logical operator And,or, not generally used in and with

SELECT * Form emp where sal>100 and comm>100;

3.between smaller number and larger number

4. Value binding & variable name dynamic transfer value

SELECT * from emp where Sal between &no1 and &no2

SELECT * from emp where job= ' &name ';--if you do not add single quotes, you can add single quotes to the input.

5.in is often used in combination with not

SELECT * * from EMP where Sal in (1500,3000);

7.is null and is NOT NULL

SELECT * FROM EMP where comm is null;

8.like fuzzy query efficiency is low, generally full table scan. It is not recommended to put% in the front of the query criteria

Wildcard%, is the character that matches any of the characters, and the wildcard _ matches one character.

SELECT * from emp where ename like ' a\_% ' escape ' \ ';--escape is used to declare escape characters. If the field value contains wildcards, you can use escape to set the escape character.

The 9.DISTINCT keyword is used to remove duplicate records, followed by select only

SELECT DISTINCT deptno from EMP;

10. Query sort order by defaults to natural order.

The SELECT * from emp ORDER by 1;--is sorted by the first field, also note that the year is sorted by size.

Single-line function

Returns the result row, and the input parameter is also a record.

Character functions

Dual virtual table, which is a row of columns, without any data, often used for testing. It is unique to Oracle for function testing.

1.upper (parameter) converts the target string to uppercase

SELECT * from emp where ename=upper (' &name ');

2.lower (parameter) displays the target string as lowercase

3.length (parameter) Gets the length of the current string

4.substr (Mu (target string, starting position of intercept, length of intercept)

Select substr (' Hello ', dual) from;--small mark starting from 1

5.initcap (parameter) capitalizes the first letter of a word in the current string

Select Initcap (' Hello Zhangkunnan ') from dual;

6.concat Connection string

numeric functions

1.round (parametric) rounding

2.ceil ()--Rounding up

3.floor ()--Rounding down

4.trunc (number,l)-intercept decimal digits;

When L is positive, it represents the number of digits to the right of the decimal point, and the number of digits to the left of the decimal point when L is negative. When l do not write, the effect is the same as l=0.

Date function

1.sysdate. Get current system time

Column sysdate for a20;== setting field display format

2.next_day (reference date, character/number) The date of the next one weeks

Selectnext_day (sysdate,1); From dual--next Sunday date

Select Next_day (' Sunday ') from dual;--provided that Oracle's character set supports Chinese

The second parameter is a number and can be executed in both English and Chinese environments.

3.add_months () to be added and reduced in the month

Select Add_months (sysdate,3) from dual;

4.months_between (termination time, even time) two time month difference

5.last_may (); Gets the date of the last day of the month

Select Last_day (sysdate) from dual;

Conversion functions

1.to_date (string, date format) function, converts a string into a date type.

SELECT * from emp where hiredate =to_date (' 1980-12-17 ', ' yyyy-mm-dd ');

2.to_char (date, display date format) function, convert date or other type to string

SELECT * from emp where TO_CHAR (hiredate, ' yyyy-mm-dd ') = ' 1980-12-17 ';

Select To_char (sysdate, ' mm/dd/yyyy hh:mi:ss ') from dual;

Select To_char (sysdate, ' mm/dd/yyyy hh24:mi:ss ') from Dual;--hh24 represents a 24-hour

Select To_char (sysdate, ' Yyyy-mm-dd Day is ') from Dual;--day for day of the week

Select To_char (1234) from dual;

Select To_char (1234, ' $999.99 ') from dual;

Select To_char (1234, ' l999.99 ') from dual;

Select To_char (1234, ' l0,0099 ') from dual;

The 3.to_number function converts a string into a number

Select To_number (' 1234 ', ' 9999.99 ') from dual;--999.99 input format for strings

Select To_number (' 1234 ') from dual;

Select To_number (' $1234.56 ', ' $999.99 ') from dual;

Select To_number (' ￥1,234.56 ', ' l9,999.99 ') from dual;--use ￥ or RMB to represent the amount, depending on how the system is represented. l9,999.99 can only use 0 and 9. It can also be separated by commas and dot numbers.

General functions

1.NVL (field, default)

Returns the value of the field itself if the field is empty and returns the specified value if it is empty.

Select ENAMEL,SAL+NVL (comm,0) from EMP;

2.decode Function Branch Judgment

Select Decode (&no,1, ' value 1 ', 2, ' Value 2 ') from dual

Summarize

Multi-Table Query

Select White Oh table name 1. field name 1, table name 2. field Name 2 from table name 1 join table Name 2 on join condition

Select Emo.ename,dept.dnaem from EMP Join dept on Emp.deptno=dept.deptno;

Simple analysis: Take the first record from the primary table, use the join condition to match the field from the record of the table, and if the match succeeds, make a result in the result set; continue to compare to the next record from the table, sweep it again, and then execute the second record of the main table, in turn.

Note:

1) The length of the Cartesian product is the number of all records in the primary table multiplied by all records from the table

The SELECT * Form emp Cross join dept;=-Cartesian product is the display of each record of the primary table connected to all records from the table.