Oracle fragmentation highlights-Multi-table joint query to collect basic database information

Oracle fragmentation key points-Multi-table joint query, collect basic database information 1. multi-table join query for oracle: www.2cto.com select. sname, B. stu_xb from student a, studentinf B where. stu_id = B. stu_id syntax 2. SQLStructur query language structured query language, which is an object used to operate relational databases. DDL (Data definition language) is used to create or Delete tables, and to modify the create table, alter table, and drop table statements to add or delete table structures. DML (Data manipulation language), insert records into a table, modify records insert, update, delete, mergetransaction, transaction control language, composed of DML statements, commit;, rollback; select query statement dcl authorization statement grant www.2cto.com 3. The OracleDBMS database management system is provided by Oracle and also provides, application Server DBA Database Administrator IV. Related Operations 1. sqlplus database access commands (local access/remote access) and database connection commands, it is the sqlplus user name/password in the database operation environment. 2. show user shows that the user name of the current user changes the identity. You can connect the user name/password directly. This is the sqlplus command available in sqlplus! You can switch between shell and sqlplus ,! You can use shell commands in sqlplus. In fact, sqlplus runs a sub-process to execute shell commands. 3. There are two types of tables in the Oracle database: User tables (User-operated tables) and system tables (tables maintained by the database system, also called data dictionaries) DDL operations on user tables start with DML operations on system tables! 5. Basic Syntax 1. select query statement select table_name from user_tables; (Query System Table) the preceding query statement queries the names of all tables owned by the user. Projection operation. Only the information of the selected field is displayed. Select Operation to view specific information in the field. Join Operation, multi-Table query, query information in multiple tables through table connection (1) select table_name from user_tables; (query system table) the preceding query statement is used to query the names of all tables owned by a user. (2) The buffer of sqlplus caches the last SQL statement. You can use "/" to execute the last SQL statement, or use the edit command to edit the last SQL statement. L command (list) (sqlplus command) can display the last command in buffer. The sqlplus command can be abbreviated as (3) desc [Table name]. This is a sqlplus command. Note that it is not an SQL statement. This command is used to view the table structure. The abbreviation of descript [field name] [field type] is the table structure displayed after the desc command is used. (4) select [Table field name 1], [Table field name 2],... from table name; select * from table name; query information of all fields in the table (5) Non-sharding keywords, SQL statements, and table names. Field names are case-insensitive. The SQL statement ends with ";". If ";" is not added, the system does not execute this SQL statement and prompts you. In Oracle, the characters are left-aligned and the values are right-aligned. (6) mathematical expressions can be used in select statements. Select [expression (this table field name must be included)], [...],... from table name; the first multiplication, division, and addition and subtraction of the Operation priority. The left-to-right operation of the same level changes the priority of the brackets. (7) alias select [field name or expression] ["alias"], [...] ["... "],... from table name. You can add a space "alias" after the field name or expression to give the column or the alias of the expression result. The alias must be enclosed in double quotation marks. (8) Use of String concatenation | symbol select target field name | "" | target field name from table name; Note: Use '.. for strings in Oracle '.. 'space is required in the alias or case sensitive ".. "include. Exercise: Write an SQL statement by yourself. The execution result is select * from ...; where... is the name of each system table, that is, "select * from" is added before the name of each system table, and ";" select 'select * from' | table_name | '; 'from user_tables; 2. handling error (1 )! Oerr ora [Error Code]. The system displays the cause of the error and how to modify it. If the command is incorrectly entered, you can use edit or ed to modify the input error. It is actually the last SQL statement in the cache file editing. You can also use (change) c/error field/correct field to modify the replacement operation. Only used on Linux platform! It is equivalent to the host. The connection is not disconnected. After switching, execute the shell command (2) edit command to edit the last SQL statement. 3. set pause on sqlplus and press enter to respond. split-screen display: only set pause off is valid in this session to disable split-screen display. Set pause "..." sets the prompt information displayed on the split screen. Set pause on first outputs the prompt information and press enter to respond. The split screen displays the set head off header output Closed set feed off end output closed set echo off write back close spool file name. SQL writes to the specified file spool off to disable writing. 4. An SQL script is a file with SQL statements in the file. It can be run in sqlplus. Introduce SQL script sqlplus user name/password @ SQL script (Note: after entering the user name and password, add a space and then write @ SQL script) and write "exit" in the last line of the script ", after the script is run, return to shell. 5. The null values in Oracle will be infinitely processed. In fact, null values will not be stored at all, but will be regarded as infinite. In Oracle, the Control Handler NVL (field name, value) replaces the null value with the specified value. If it is not null, the original value is returned. Example: select (salary * 12) * (NVL (commission_pct, 0)/100 + 1) salary, first_name from s_emp; distinct keyword, remove duplicate rows (this keyword will trigger the sorting operation) Example: select distinct dept_id, title from s_emp; the Union of dept_id and title is not unique Note: distinct, after the keyword is entered, the fields before the from statement will be duplicated. 6. column command --- sqlplus command column format definition column target column name check whether this class defines the format column target column name format a .. set the column width. Column last_name heading 'employee | name' FORMAT A15 sets '|' is the linefeed column salary justify left format $99,990.00 defines the number display FORMAT. Note: If the display format is not met, the data is displayed as "#" column salary justify left format $00,000.00 and $00,928.00 is displayed. Fill the column clear (clear column format definition) with 0. Note: only the sqlplus command can be abbreviated, And the sqlplus command cannot end with a plus sign. 6. Select Operation 1 and order by sort clause ASC (default, ascending) DESC (descending) order by target column name (alias) sort order (not write sort order, the default value is ascending.) For example, select first_name from s_emp order by first_name; select first_name from s_emp order by first_name desc. Note: The ascending null value is at the end of the result, in descending order, the null value is at the beginning of the result. 2. where clause is used in select... from... followed by the where clause used to select the desired (qualified) record followed by the expression XXX = XXX, XXX between X and X, XXX in (X, X, X) like '... 'wildcard query... and ..., indicates that the result is between them. between and is a closed interval, which is equivalent... <=... and...> = ....! =, <>, ^ =, These three operators are not equal to, <=, >=, =, and can all be used .... In (va1, val2 ,...) determine whether the result exists like 'In this enumeration '... 'string wildcard query, '%' indicates multiple characters, '_' indicates a single character. Note: escape usage: like's \ _ % 'escape '\'... and... it indicates that only two conditions are met at the same time... or... it indicates that the condition can be all as long as only one of them is met... yes. All requirements are met. Not..., it can be reversed with the above conditions. The null value will affect not in, that is, it is not equal to any value, but the null value is not .... Is null is used to determine whether the value is null. Note: strings in Oracle are case sensitive. (1) Pay attention to the data type. The number type is directly written, and the character is '...... ', the default format of Date can be '...... '. Only aliases can contain. (2) Select the appropriate operators 7. Single-line functions 1. the character function character is case-sensitive to lower-case lower (field name)-the parameter can be a String constant or a field name to upper-case upper (field name) to upper-case initcap (field name) string concatenation concat (Field 1, Field 2) intercepts the substr (field name, start position, number of characters) dual table for Function Testing and computation, he only has one record string to splice concat (...,....) returns the substring substr (..., start position, number of characters) You can use "-" to retrieve from the right to the left. You can extract from the left to the friends. Example: select substr (first_name,-) sub from s_emp; (the second bit starts to take two) select substr (first_name,) sub from s_emp; (Take two from the last two digits.) select substr (ename, 2, 2), substr (ename,-3, 2), ename from scott. empselect concat (initcap (ename), deptno) from scott. emp; 2, numeric function rounded to round (data, retain the digits after the decimal point) can use a negative number to indicate the first digit after the decimal point, that is, retain a single digit, -1 indicates a single position (retained to ten digits ). Example: select round (15.36, 1) from dual; --- 15.4 select round (15.36, 0) from dual; --- 15 select round (15.36,-1) from dual; --- 20 trunc (data, number of digits retained (number of digits after decimal point). After one digit is intercepted, add 0. Example: select trunc (123.456, 1) from dual; --- 123.4 3, date function date format, Century information in full date format, year, month, day, hour, minute, and second. The default date format is "day-month-year dd-mon-rr". To modify the date format of the current session, the date alter session set nls_date_format = 'yyyy mm dd hh24 is output according to the specified format: mi: ss'; return the current date sysdate. Example: select sysdate from dual; select sysdate + 1 from dual; get the date tomorrow, add 1, the Unit is that the day date is in a format sensitive. Calculate the number of months months_between (date1, date2) separated by the two days select months_between (sysdate, sysdate + 61) from dual; add_months (date, number of months) of the specified number of months, the number of months can be negative, and the negative value is minus the corresponding number of months. For the first FriDay next_day (date, FriDay) starting from date, return the date last_day (date) at the end of the month trunc (date, 'year, month, day, or hour, second ') Example: select next_day (sysdate, 1) from dual; --- 1 Sunday 2 Monday ...... 7. Saturday example: select trunc (add_months (sysdate, 1), 'month') from dual; ROUND ('25-MAY-95 ', 'month ') 01-JUN-95ROUND ('25-MAY-95 ', 'Year') 01-JAN-95TRUNC ('25-MAY-95', 'month') 01-MAY-95TRUNC ('25-MAY-95 ', 'Year') 01-JAN-95select trunc (sysdate, 'day') from dual? Select TRUNC ('25-MAY-95 ', 'Year') from dual exercise: returns the date of the first day of the next MONTH select round (last_day (sysdate), 'month ') from dual; select add_months (trunc (sysdate, 'month'), 1) from dual; 4. The conversion function converts a date to a character tochar (date, 'date format') the date format must be in a valid format. The format is case sensitive: 'yyyy mm dd hh24: mi: ss', 'Year' (year of full competition ), 'mm' (the number indicates the month) 'month' (the month of the full spelling), 'day' (the full spelling of the week), and 'ddspth' (the full spelling of the date) 'yy mm dd' example: select to_char (sysdate, 'ddspth') from dual; convert the character into a number to_number ('... ') convert numbers Convert to_char (number, 'fmt') fmt to convert string to date to_date ('... ', 'date format') Example: select to_char (to_date ('1970 11 03', 'yyyy mm dd'), 'dd-month-yy') from dual; 1. equijoin select [table alias 1. field name 1], [table alias 2. field name 2],... from table 1 table alias 1, Table 2 table alias 2 where table alias 1. field name 3 = table alias 2. field name 4; When a table is connected, when there is a field with the same name between the table and the table, you can add the table name or the table alias to distinguish it, use the table name. field name or table alias. field name (column name ). When the field name of a table is unique, you do not need to add the table name or table alias. Note: When an alias is set for a table, you cannot use the table name. Field name. Example: select. first_name,. last_name, B. name from s_emp a, s_dept B where. dept_id = B. id; 2. Non-equivalent join select [table alias 1. field name 1], [table alias 2. field name 2],... from table 1 table alias 1, Table 2 table alias 2 where table alias 1. field name 3 ..... table alias 2. field name 4 .... the comparison operator can be used, or other operators except '=' can be used as an example: select e. ename, d. grade, e. sal from emp e, salgrade d where e. sal between d. losal and d. hisal; 3. Use an alias to split the data in a table into two parts, and then use conditional filtering. Select [table alias 1. field name 1], [table alias 2. field name 2],... from table 1 table alias 1, Table 1 table alias 2 where table alias 1. field name 3 = table alias 2. field name 4; example: select. first_name ename, B. first_name cname from s_emp a, s_emp B where. manager_id = B. id; the table join mentioned above is called an inner join, which strictly matches the records of the two tables. 4. The outer join will use all records in one table to match the records in another table by conditions, and the null values will also match. All records in this table will be displayed, the database simulates records to match those records that do not match. Example: select. first_name enamei,. id, B. first_name cname, B. id from s_emp a, s_emp B where. manager_id = B. id (+); that is, use the data in Table a to match Table B. If table B has null, the system simulates the record to match it. Note: all records of that party should be displayed, and the conditions (+) should be followed by the Peer to be selected. Application of Outer Join: list which department has no employee select e. deptno, d. deptnofrom emp e, dept dwhere e. deptno (+) = d. deptnoand e. deptno is null; 3. group by clause of the group function group, which is grouped by the specified group rules. The group by clause can be followed by the select statement or having clause. The group by clause also performs sorting and sorts by group fields. Select [Group function or group field name],... from table name group by [field name 1], [field name 2],...; for example, select avg (salary) from s_emp group by dept_id. Note: A group function can process a group of data and return a value. The group function ignores NULL values. Avg (...), calculate the average value, sum (...), and sum. The parameters of the two functions can only be of the number type. The following functions can be used as parameters of any type. Count (...) is used to count the number of records. You can use the deduplication command. Count (...) uses all by default. Max (...), min (...) calculate the maximum and minimum values, count (*), and the number of records in the statistical table. Example: select max (B. name), avg (. salary), max (c. name) from s_emp a, s_dept B, s_region c where. dept_id = B. id and B. region_id = c. id group by B. dept_id; Note: as long as the group by clause is written, *** select can only use the field or group function after group. * ** The where clause can only filter records and put single row functions. The having clause can filter group function results or group information and write it after the group by clause. Example: select max (B. name), avg (. salary), max (c. name) from s_emp a, s_dept B, s_region c where. dept_id = B. id and B. region_id = c. id group by B. id having sum (. salary)> 4000; column can also define the format of Columns with aliases. Column "alias" format definition Note: You must filter out unnecessary records before grouping to improve efficiency. 4. subquery A subquery is a select statement that can be embedded in any SQL statement. When a subquery is embedded in a select statement, the subquery is executed first. Generally, the subquery is placed on the right of the operator. Note: When using a subquery, note whether this operator is a single row (that is, it can only be a single value) or a multi-row operator (range, multi-value, in ). The results returned by the subquery must comply with the operator usage. Example: select first_name, title from s_emp where title = any (select title from s_emp where last_name = 'Smith ') and upper (last_name )! = 'Smith '; select first_name, title from s_emp where title in (select title from s_emp where last_name = 'Smith') and upper (last_name )! = 'Smith '; five, the business needs to be converted into operational table 1: Demand Analysis 2: drawing a E-R Figure 3: converted into a table relationship 4: cutover (new and old system handover) Five: e-R chart properties: * mandatory and non-empty properties o optional properties (either value or not) # * indicates the property's unique and non-empty entity relationship: mastbean maybean qty relationship: multiple-to-one relationship one-to-multiple relationship one-to-one relationship the first paradigm of multiple-to-many relations. All attributes must be single values, that is, attributes only represent a single meaning. (Records can be repeated without any restrictions) in the second paradigm, attributes must be unique and non-empty (records cannot be duplicated, but data may be redundant ). In the third paradigm, non-primary attributes can only depend on primary attributes, but cannot depend on other non-primary attributes. (Solving data redundancy issues) 6. constraints are defined for fields in the table. Primary key (primary key constraint PK) ensures the integrity of the object and the unique primary key constraint of the record. It is unique and not empty. In addition, each table can have only one primary key, and two fields are combined as the primary key, only two fields are put together with the unique identification record, called the joint primary key. Foreign key (external creation constraint FK) ensures the integrity of the reference. The foreign key is restricted. The value of the foreign key is subject to the primary key or unique value constraints in another table and cannot take other values, the referenced table is called the parent table (parent table), and the referenced table is called the child table (child table). To create a child table, you must first create a parent table and then create a sub-table. This is also true for record insertion. to delete a sub-table, you must first Delete the sub-Table record and then delete the parent table record, to modify a record, make sure that the record of the parent table is not referenced by the quilt table. To delete a table, you must first Delete the child table and then delete the parent table. Unuque key (unique key). The unique index value is a unique type of object in the database. A typical one-to-many class of view corresponds to multiple students. Student table class table _____________________________________________________________ | id | name | address | class_id | id | class_desc | class_num | (PK) | ______ | ________ | ___ (FK) __| | (pk) | __________ | _________ | one-to-one student tabel shenfenzheng table ________________ detail | id | name | address | s_id | shenfen_desc | shenfen_num | (PK) | ______ | ________ | (PK, FK) | ____ ________ | ___________ | Many-to-many student tabel zhongjian table kecheng table ________________ __________________ | id | name | address | s_id | shenfen_desc | shenfen_num | kid | kechengname | (PK) | ______ | ________ | (FK, FK) | ____________ | ___________ | (PK) | ____________ | joint primary key | reference the primary key of the table of the other party as its primary key. Therefore, the primary key of the table, it is both a primary key and External table creation, and other related operation DDL statements to create a table: create table Name (field name 1 type (Data Length) (default ...) constraints, field name 2 type (Data Length) constraints); Data Type varchar (length) in Oracle Database, variable length string, char (length) fixed Length number (..,..), number indicates a floating point or an integer long large object. It is a large object of clog characters. It is equivalent to storing only one large object in a text file in the table, which is equivalent to a large object that only targets the binary value of the blog, it is also stored as a pointer. Primary key constraint: primary key constraint definition: first definition form: create table test (c number primary key); column-level constraint select * from test second definition form: create table test (c number, primary key (c); table-level constraint create table test (c1 number constraints pkc1 primary key); this constraint has a name: pkc1create table test (c number, c2 number, primary key (c, c1); table-level constraints can be used to implement the Union primary key foregin key (fk) foreign key constraint: (define the parent table first, define the sub-table) create table parent (c1 number primary key); create table chil D (c number primary key, c2 number references parent (c1); or table-level constraint definition: create table child (c number primary key, c2 number, foreign key (c2) references parent (c1); if both fields are unique and NOT empty, you can define them as UK + not null (PK or UK) one-to-many (FK) (PK + UK) one-to-one (FK) or (PK) one-to-one (PK) many-to-many relationships, generally, an intermediate table is decomposed into two one-to-multiple tables. create table [schema] tableschema: A user corresponding to a schema tables of different users cannot view each other select count (*) from s_dept; <==> select count (*) from sd0611.s _ dept; One table can only store one LONG type CLOB to store large text objects BLOB to store large binary objects create table test (c1 number primary key ); set the primary key create table test (c1 number constraints test_c1 primary key); Define the constraint name, the default constraint name is SYS _. The constraint defined after the column is the create table test (c1 number primary key (c1) constraint )); after all columns are defined, they are further defined as table-level constraints (joint primary keys can be defined) cretae table test (c1 number, c2 number, priary key (c1, c2 )); define the joint primary key create table child (c1 number primary key); first define the parent table create table child (c1 number pri Mary key, c2 number references parent (c1); then define the child table references parent and define the foreign key create table child (c1 number primary key, c2 number references parent (c1) on delete cascate); on delete cascate is a cascade delete create table child (c1 number primary key, c2 number references parent (c1) on delete set null ); on delete set null after deletion, leave the foreign key blank create table child (c1 number primary key, c2 number, foreignkey (c2) references parent (c1 )); Ii. constraint 1. non-null constraint (not null) This is a column-level constraint. When creating a table, add not null after the data type, that is, the null value cannot be inserted during insertion. Example: create table student (id number primary key, name varchar2 (32) not null, address varchar2 (32); 2. unique constraint of unique, which ignores null values, unique constraint. The value of the inserted record must be one. Example: create table student (id number, name varchar2 (32), address varchar2 (32), primary key (id), unique (address); If you create a uk, the system automatically creates a unique index. 3. pk and ukOralce support cascading deletion. Cascade update is not supported. 4. check constraints. You can check the insertion of records according to specified conditions. Check does not support tail columns, functions, and other fields. Example: create table sal (a1 number, check (a1> 1000 ));