Database (mysql oracle) Learning Summary

Database (mysql oracle) Learning summary group: data files, control files, log files oracle instances, its relationship with the database oracle instances: the instance is the memory allocated and created background process after the database is started. after the database is closed, the physical files still exist, but the instance (allocated memory and created process) does not have the database: a collection of physical operating system files or disks. Relationship: an instance is a group of operating system processes (or multi-threaded processes) and some memory. These processes can operate databases, while databases are just a collection of files (including data files, temporary files, redo log files, and control files ). At any time, an instance can only have a group of related files (associated with a database ). In most cases, the opposite is true: only one instance in a database operates on it. However, the Real Application cluster (RAC) of Oracle is an exception. This is an option provided by Oracle that allows operations on multiple computers in the cluster environment, in this way, multiple instances can simultaneously load and open a database (located on a group of shared physical disks ). Data Type in the database: number: mysql db2 ----- short integer long float double oracle -------- number (30), number (30, 3) character/string: char (10) varchar2 (10) date and Time: date (year, month, day, hour, minute, second) timestamp () (timestamp) oracle database mode date format: the primary key of a large object (Characterlob/Binary lob) in the month of 03-3-08 03-Mar-08 should have the following features: 1. cannot be null2. the primary key must be unique where name like '\ % 'escape' \ 'character: upperlowerinitcapsubstrreplaceinstrlengthlpad/rpadtrimconcat -------- | select substr (last_name, 1, 1) | St_name, sbustr (last_name, 2, length (last_name)-1, '*') replace (last_name, substr (last_name, 2, leghth (last_name)-2 ), lpad ('', length (last_name)-2, '*') Date: months_betweenadd_months () next_day () last_day () number: round (2333,2) truncmod round (sysdate, the to_char to_date to_number to_char (number/date, 'fm ') to_char (sysdate, 'fmyyyy-mm-dd "fdfdfd" HH24: MI: SS am ') to_char (234567, 'fm99, 999.00000') to_date ('20 -October 22,-58 ') to_date (' [March] ',' [yyyy-mm-dd] ') RR68 universal function: nvl (commission_pct, 0) ---- if the first expression is null, the value of the second expression is returned; otherwise, the value of the first expression is nvl (commission_pct,) -- the first expression is null, returns the value of the third expression. Otherwise, the nullif (last_name, first_name) value of the second expression is returned. If the value of the first expression is the same as that of the second expression, null is returned, otherwise, the return value of the first expression is decode () lpad ('', round (salary * 12 + salary * 12 * nvl (commission_pct, 0)/1000 ), '*') two ways to query multiple tables: Connection query internal connections: equivalent connections, non-equivalent connections, and natural connections... Outer Join: left, right, and all Outer Join Cartesian set ---- subset select * from users u, orders o where u. id = o. user_id and; select * from users u join orders o on u. id = o. user_id; cross join select. dd, B. dd, c. dd from A a, B B, c where. pk = B. id and B. id = c. fk employee1 A 5002 B 6603 c 10004 d 4000 levelid name minsalary maxsalay1 A 5000 100002 B level 3000 C level 50003 D level 1000 30004 a d level B d level C Level D B level select e. name, l. name from employee e, level L where e. salary between l. minsalary and l. maxsalary; 1 A 500 4 D level 400 B 10002 4 D level 660 C 400 3 C level 10003 1000 d 1000 2 B level 30004 4000 select * from users u left/right/full outer join orders o on u. id = o. user_id; a simple method for Oracle to write external connections: select * from users u, orders o where u. id (+) = o. user_id; select * from orders ments nature join loctions ------ the primary and Foreign keys of the two tables must have the same name, same type, and different types of select * from deparments join l Ocations using (location_id); select * from orders ments d, locations l where d. location_id = l. locations_id; select e. last_name, e. job_id, d. department_name, e. salary, jg. grade_level from employees e, departments d, job_grades jg where e. department_id = d. department_id and e. salary between jg. minsalary and jg. maxsalary; USERS (id, name, birth) insert into users (id, birth) values (1, to_date ('2017-01-01 ', 'fmyyyy-mm- Dd'); insert into users values (1, 'abc', to_date ('2017-01-01 ', 'fmyyyy-mm-dd ')); delete users where last_name = 'hangsan'; SQL: Structured Query Language, selectDML: Data Control Language: insert update deleteDCL: Data Control Language: commit rollback. Transaction: the unit of a group of SQL statements (dominated by DML statements) DDL: Data Definition Language: create table users () insert into student values (3, 'cc', 100.99, null ); DDL: Data Definition Language order byblueuserblueorderblue_userblue_orderoa_user create table users (id number (10), name varchar2 (40), age number (3 )); alter table users add constaint users_pk primary key (id); alter table users disable constraint users_pk; alter table users enable constraint users_pk; alter table users drop constraint users_pk; alter table users set unused (name, age); alter table users et unused column name; alter table users set unused column age; alter table users drop unused columns; drop table users; rename users to oa_users; truncate table users; select e. last_name, e. salary, e. department_id, d. mm from employees e, (select department_id dept_id, max (salary) mm from employees group by department_id) d where e. department_id = d. dept_id; select dfdfd from A a left outer join (select dfa) create or relpace view salary_vuisselect e. last_name, d. department_name, e. salary, j. name from employees e, departments d, job_grades j where e. department_id = d. department_id and e. salary between j. minsalary and j. maxsalary; SELECT * FROM (select. *, rownum rn from (SELECT * FROM TABLE_NAME) a where rownum <= to) where rn> = from