Common SQL for databases

1. How to display duplicate data only, or do not display duplicate data

Show Duplicates: SELECT * FROM tablename GROUP by ID has count (*) >1 does not show duplicates: SELECT * FROM tablename GROUP by ID has count (*) =1

2. What are the methods for writing pagination, and what methods do you generally use? Write a page with a SQL statement ?

sqlserver--Use top:   select top 20,n.* from tablename n minus select top 10,m.* from tablename moracle-----Using analytic functions:    SE Lect * from   (select N.*,row_number () up (order by columnname) num from  tablename N)    where num>=10 and num <=20;   SELECT * FROM TB (select RowNum rn,* from TB where rn<=40) where Rn>30;mysql:    select * FROM table where ... LIMIT Ten;   #返回前10行     select * FROM table WHERE ... LIMIT 0, 10; #返回前10行     select * FROM table WHERE ... LIMIT 10, 20; #返回第10-20 rows of data

3.left and right connections in Oracle

Left join: Left joins  right connection: R join   Select N.column,m.column from tablename1 N left joins tablename2 m on   n.column Name=m.columnname is implemented in Where:   select N.column,m.column from tablename1 N, tablename2 m   where n.columnname (+) = M.columnname

4.What are the symbolic links for strings in Oracle?

Use in Oracle | | This symbolic connection string such as ' abc ' | | ' d '; MySQL uses concat ("name", ename)

5.Oralce How to store files and what files can be stored?

Oracle can store Clob, NCLOB, blobs, bfile
Clob variable-length character data, that is, text-type data types that are mentioned in other databases
Nclob data of variable character types, but it stores character data for the Unicode character set
Blob variable-length binary data
Bfile variable binary data stored outside the database

6. Create a View

CREATE view name as select column name [alias] ... from table [Unio [All] Select ...]
Benefits:
1. You can simply interpret the view as a SQL query statement, the greatest benefit of the view is that it does not occupy system space
2. Some high-security systems do not advertise the system's table structure, and may use views to cause some sensitive information to be over-or renamed after the structure is published
3. Simplify your query
You can control permissions, and you need to grant the view permission to the user when you use it.

7. How to create a trigger, the definition of a trigger, how the cursor of a trigger is defined

CREATE [OR REPLACE] Tigger Trigger name trigger Time trigger event
on table name
[for each ROW]
BEGIN
PL/SQL statement
    cursor  cursor name is  SELECT * FROM table name (definition cursor)
END
Where:
Trigger name: The name of the trigger object.
because the trigger is automatically executed by the database, the name is just a name and has no real purpose.
Trigger Time: Indicates when the trigger executes, which is preferable:
Before---indicates that the trigger executes before the database action; The
after---indicates that the departure timer executes after the database action.
Trigger event: Indicates which database actions will trigger this trigger:                       
Insert: Database insert triggers this trigger;  

CREATE TABLE Emp_his as SELECT * from EMP WHERE 1=2; CREATE OR REPLACE TRIGGER tr_del_emp    before Delete--Specifies that the trigger time is triggered before the delete operation on the scott.emp for each    ROW   -- Description creates a row-level trigger BEGIN   -Inserts the pre-modified data into the Log record table del_emp for monitoring use.   INSERT into Emp_his (Deptno, empno, ename, Job, Mgr, Sal, Comm, HireDate)       VALUES (: Old.deptno,: Old.empno,: Old.ename,: Old.job,:old.mgr,: old.sal,: Old.comm,: old.hiredate); End;delete emp WHERE empno=7788;drop TABLE emp_his;drop TRIGGER del_emp;

CREATE or REPLACE TRIGGER tr_dept_timebefore INSERT or DELETE or UPDATE on Departmentsbegin IF (To_char (Sysdate, "Day") in (' Saturday ', ' Sunday ')) OR (To_char (sysdate, ' hh24:mi ') not between ' 08:30 ' and ' 18:00 ') then     raise_application_error (-20001, ' It's not time to work, Cannot modify the Departments table '); END IF; END;

Qualifying only row trigger operations on records with department number 80.

CREATE OR REPLACE TRIGGER tr_emp_sal_commbefore UPDATE of salary, commission_pct       OR deleteon hr.employeesfor each ROWW HEN (old.department_id = $) BEGIN case when     UPDATING (' salary ') then        IF:NEW.salary <: old.salary then           RAI Se_application_error (-20001, ' The wages of the department's 80 personnel cannot be reduced ');        END IF;     When UPDATING (' commission_pct ') then        IF:NEW.commission_pct <: old.commission_pct then           raise_application_ ERROR (-20002, ' the bonus of a department 80 person cannot be lowered ');        END IF;     When DELETING then          raise_application_error (-20003, ' cannot delete department 80 personnel Records ');     END case; END; /* Instance: UPDATE Employees SET salary = 8000 where employee_id = 177;delete from Employees where employee_id in (177,170); */

Building a primary key

1. Create a sequence

Create sequence bar_code_sequence  minvalue 1  maxvalue 999999999999999999999999999  start with 1  Increment by 1  cache 20;  

Note:

MinValue 1-----Minimum value
MaxValue 9999999999999999999999999999------Maximum Value
Increment by 1-----Add a few each time
Start with 1-----from several beginnings
Cache-----What is the cached value?
Noorder-----Keep accumulating, not sorting
Nocycle; --always accumulate without circulation

2. Create a Trigger

CREATE OR REPLACE TRIGGER  bar_code_tg   before insert on Sjk_bar_code for each row   begin     Select Bar_code_ Sequence.nextval Into:new.ID from dual;   End

SQL common to databases