Pen QuestionsWith the relationship emp (ENO,ENAME,SALARY,DNO) The meanings of each of these attributes are employee number, name, salary, department number, and Relationship dept (Dno,dname,manager) which are in order department number, department name, Department manager's employee number 1. Create a table emp, DEPT, using SQL statements. CREATE TABLE EMP (ENO number (5) primary key, Ename VARCHAR2 (5), salary number (8), DNO number (3)), CREATE TABLE dept (DNO number (3) Primary key,dname VARCHAR2 (Ten), manager number (5); 2. Try the SQL statement to complete the following query: Lists the average wage of employees in each department with a salary of not less than 600 yuan. Select AVG (Salary) from EMP where salary>=600;3. Write the relationship of "query number NO. 001 Department name" Algebraic expression select Dname from dept where DNO in (select D No from EMP where eno= ' 001 ') 4. Please use the SQL statement to increase the salary of the "sales department" to 10% of the employees ' wages below 600 yuan. Update emp set salary=salary*1.1 where sal<600 and dno= (select DNO from dept where dname= ' Sales ') 5. Use SQL to query the number of "Sales" employees Sele CT Count (*) from EMP GROUP by DNO where DNO in (select DNO from dept where dname= "sales Department")
Advanced QueryRandomly returns 5 Records SELECT * FROM (select Ename,job from emp ORDER BY
Dbms_random.value ()where rownum<=5; handles NULL ordering SELECT * FROM emp ORDER BY comm DESC
Nulls last (first) The query skips the even lines in the table select Ename from (select Row_number () over (order by name) Rn,ename from EMP) x where mod (rn,2) = 1; Query employee information and the salary High Minimum employee Select Enmae,sal,max (SAL) over (), Min (sal) over () from EMP, continuous summation Select Ename,sal,sum (SAL) over (),
sum (SAL) over (order by name) From Emp;//sum (SAL) through (order by ename) refers to sequential sums, sorted by ename. If there are two such window functions, the following sorting is the main branch of the continuous summation select Deptno,sal,
sum (SAL) over (partition by Deptno ORDER BY ename
) s from EMP; Gets the data for a row or row on the current line select Enamel,sal,lead (SAL) over (order by Sal) Aaa,lag (SAL) over (order by Sal) BBB from EMP; Sub-string Grouping select To_char (hiredate, ' yyyy '), avg (SAL) from the EMP Group by To_char (HireDate, ' yyyy '); Determine the number of days in a year select Add_months ( Trunc (sysdate, ' y '), x)-trunc (sysdate, ' y ') time intercept function from Dual;trunc (sysdate, ' X '), x represents the parameter dd--intercept today D-intercept the first day of the week mm-intercept the first day of the month Y-year hh-hours mi-minutes Query the EMP Employee table employee information for the first two employees in each department select Deptno,ename,sal from EMP E1 where ( Select COUNT (*) from EMP E2 where E2.deptno=e1.deptno and E1.ename!=e2.ename and E2.sal>e1.sal) <2 ORDER by Deptno,sa L Desc;select * FROM (select Deptno,ename,sal,row_number () over (partition by deptno ORDER BY Sal Desc) rn from EMP) where r N<3;rownum and Row_number () difference Http://wang09si.blog.163.com/blog/static/1701718042012101424439858/rownum is a pseudo-column ID in a table has multiple records, all records of this ID are detected, and the number of records in total is displayed. (Huawei interview) Select ID, COUNT (*) from TB GROUP by ID have count (*) >1;
Data DictionaryQuery a user under All tables Select table_name fom all_tables where owner= ' Scott ' Queries all fields (columns) in the EMP table select * FROM All_tab_columns where table_ Name= ' emp ' lists the index column of the table select * from sys.all_ind_columns where table_name= ' emp '; list constraints in table select * from All_comstraints where Table_name= ' EMP '; Describe the data dictionary view in Oracle Select TABLE_NAME, comments from dictionary where table_name like '%table% ';
Oracle data type because char is fixed-length, it is much faster than VARCHAR2! But the program to deal with a little trouble, to use a function such as trim to remove both sides of the space varchar2 generally applicable to English and numbers, Nvarchar2 for Chinese and other characters, where n represents a Unicode constant, you can resolve the conversion problem between the multilingual character Set number ( 4,2) refers to integers accounted for two bits, decimals accounted for 2 digits by default to 38-bit data type number (P[,s]) p for valid data bits, s for decimal places VARCHAR2 () better compatibility in Oracle, basic without Varchardate to_date (' 2015-01-01 13:14:15 ', ' yyyy-mm-dd hh24:mi:ss ') minutes to MI because of the case-insensitive data type in SQL Summary: http://www.cnblogs.com/yshb/archive/2012/ 06/19/2554279.html
Oracle Architecture
DDL (change table structure)Build Tables CREATE TABLE Test (ID number (5) primary key,--primary Key name VARCHAR2 (TEN) NOT null,--non-empty birthday date,email varchar2 (+) unique,- -Unique Age number check,--check constraint (age between 0 and)deptno number references emp (ID) ON DELETE Cascade --FOREIGN key Cascade delete) CREATE TABLE Emp3 as SELECT * from EMP where Sal >1000; Show table structure:describe TestDelete tables: Drop table Test Modify table name: Rename test to testing--------------------add a column to the field Operation-----------------------------------Alter Table test Add address varchar2 (40); Delete column ALTER TABLE test drop columns address; Modify column name ALTER TABLE TEST address insert Data inserts INT O Test values (1, ' Ling ', 3000,1) insert INTO Test (eno,salary) VALUES (3,3000) Modify Data Update test set Ename= ' Zhang San ' where eno=3; Delete data Delete test (unconditionally delete all data, delete all) truncate test (no fallback information, fast) Delete from TES t where eno=3; Delete a table duplicate record, age,name the same is the duplicate record, the ID is self-increment unique delete from test where the ID not in (select min (id) from the test group by name , age);
DML (change data structure)Inter-table data copy INSERT INTO dept (id,name) Select Deptno,dnamem from Dept;update myemp set (job,mgr) = (select Job Mgr from Myemp wher e empno=7556) where empno=7779;
Merge into Test2 using Test1on (Test1.eid=test2.eid) while matched then update set name=test1.name,birth=test1.birth,sal= Test1.salwhen not matched then insert (eid,name,birth,sal) values (test1.eid,test1.name,test1.birth,test1.sal);
Constraint not null--non-null constraint primary key--PRIMARY KEY constraint (cannot be duplicated, cannot be empty) unique--unique constraint, value cannot be duplicated (except null value) check--condition constraint, inserted data must meet certain conditions foreign key--foreign key add primary key A Lter table person Add constraint person_pid_pk PRIMARY KEY (PID) Add unique constraint ALTER TABLE person add constraint Person_tel_uk UNIQ UE (tel) Add CHECK Constraint ALTER TABLE person add constraint person _age_ck CHECK (age between 0 and 150) Add primary-FOREIGN KEY constraint requiring cascade delete ALTER TABLE Bo OK add constraint person_book_pid_fk FOREIGN KEY (PID) REFERENCES person (PID) on delete CASCADE; Delete constraint ALTER TABLE student DR OP Unique (tel) ALTER TABLE book drop CONSTRAINT PERSON_BOOK_PID_FK enable constraint ALTER TABLE book enable CONSTRAINT Person_book_pid_f k; disable constraint ALTER TABLE book disable CONSTRAINT PERSON_BOOK_PID_FK;
ViewCreate View name (field) as sub-query creates views EMPV20 (Empno,ename,sal) as select Empno,ename,sal from EMP where deptno=20; advanced View Create or Replace view EMPV20 (deptno,msal) as (select Deptno,min (SAL) from EMP Group by Deptno have min (sal) > (sele CT min (sal) from EMP where deptno=20)) with CHECK option constraint Empv20_ck;
SQL Optimization1. Use the in operator as little as possible2. Try to replace the not-in operator with not EXISTS or outer joins3. Try not to use the "<>" or "! =" operator4. When designing the table, set the index column to not NULL5. Try not to use the wildcard "%" or "_" as the first character of a query stringavoid using calculations on indexed columns in 6.Where sentences7. Replace ">" with ">= "8. Use the SGA shared pool to avoid the parse phaseRequirements for conditional order after 9.where10. Use table aliases as a prefix for each column11. An explicit or implicit operation field cannot be indexed12.UNION all instead of UNION13. Other Operationstry to use packagesuse cached sequence to generate primary key as much as possiblegood use of space: such as proxy char with VARCHAR2 data type, etc.using SQL optimization tools: such as Sqlexpert,toad,explain-table,pl/sql;oem14. By changing the size of the Oracle SGA (the system global Zone of the database)generally look at so much first, some of the other modules are also introduced in this documenthttp://pan.baidu.com/s/1o6iK00y
Oracle learns notes from scratch three
