Oracle documents I prepared when I was studying at Beijing University

-- Basics: select * from area select * From cgfpmxb/* can contain any expression * // * specifies the alias. You can directly write it or use as AA * // * to combine the two fields into one field. use | * // * to enclose a string in single quotes */select Itemid | itemname | amount | 'hello' taxamount, amount * taxratio/10.00 "AA" from cgfpmxb/* null use */select * From djzhbwhere CA is not null/* when calculating the expression, as long as the expression contains null, the entire result is null. */select amount, amount + null from cgfpmxb/* to prevent the expression from being affected by a null value, you can specify a value for the null value. When calculating the expression, if a null value is encountered, instead of */select amount, amount + nvl (null, 0) from cgfpmxb/* nvl processing in the date, very important * // * how to convert a character date to a date format */select nvl (checkdate, to_date ('2017-01-01 ', 'yyyy-mm-dd ')) from djzhb where (CA is null)/* obtain the system time function */select sysdate -- the duplicate query result records can be eliminated, as long as the two columns we select are the same, it is treated as repeated select distinct distcode. distname from djzhb/* gets the first two rows of Data * // * rownum type system function, which can generate an ID column for the table, in this way, specify the ID in the WHERE clause to determine the number of rows */select * from (select rownum ID, djzhb. * From djzhb) Where id <3/* sort * // * ascending: ASC descending: DESC * // * alias can be specified in the SORT */select distcode code, distname from djzhb order by distcode ASC select distcode code, distname, CA from djzhb order by ca desc/* You can specify multiple columns for sorting. * // * distinct can remove duplicates, check whether the selected columns are the same. Only when the field values of the selected columns are the same */select distinct distcode code, distname, CA from djzhb order by distcode, distname desc -- distinct is generally used for a column ,, in MSSQL, select distinct distcode from djzhb order by distcode/* where usage */select distinct distcode, distname, ordertype from djzhb where distcode = '2013'/* Where processing date */select distcode, distname, ordertype, uploaddate from djzhb where uplorddate = to_date ('2017-09-25 ', 'yyyy-mm-dd ') /* Where can be used with the <><=> = symbol */select * from area where di> = 4/* between and in */select * from area where ID 1 and 4/* discrete numbers are the same as SQL */select * from area where ID in, 5) /* fuzzy query */-- % indicates any character select * from province where name_c like '% hai'/* Indicates searching for the last word sea * // * _ indicates a single character * /select * from province where name_c like 'bei _ '/* conversion character, you must define your own. If the field contains %, you must use a symbolic symbol to search for the line containing this symbol, */select * from area where name_en like '% \ %' escape '\'/* any escape symbol can be specified */select * from area where name_en like '% + %' escape '+'/* specifies + as a conversion symbol, indicates the query of fields ending with % */-- you can use and or to connect multiple query conditions select * from area where ID <4 or name_cn like '% hua'/* function * // * 1 * // * string function: */-- lower -- upper -- initcap -- Concat -- substr -- length -- nvl/* is converted to lowercase uppercase substr to retrieve a field value, starting from the first character, extract the length of a string with 10 characters in a row to determine whether the string contains the Guide. If the Guide contains the guide, return the start position of the field found for the first time. combine the two strings into a new string */select item_number, lower (name_e), initcap (name_e), substr (name_e, 1, 5), length (name_e), instr (name_e, 'guide') Concat (name_e, name_c) from item_master_tb/* 2 * // * numeric function round takes two digits after the decimal point, and the last digit is rounded to the nearest digit to determine the size (if a negative number affects the integer part) trunc takes the specified number of digits after the decimal point and takes the digits after the decimal point directly. It will not be rounded to * // * dual is a false table */select round (43.345, 2), round (43.345, 1), round (43.345,-1), trunc (45.546, 2), trunc (45.3455,-1) from dual/* 3 * // * Time Function sysdate get system time */select sysdate from dual select sysdate + 600 from dual/* default Formula number of days * // * connections the subtraction of time indicates that you can add six months to obtain the first day of the next week, which is the number of the last day of the current month, very important, it can be used in programming to show only to the month by the day rounding only to the day time is discarded by the year rounding only show year indicates to the year rounding dd represents tomorrow * /select months_between (to_date ('1970-01-01 ', 'yyyy-mm-dd'), sysdate), add_months (sysdate, 6), next_day (sysdate, 1), last_day (sysdate ), last_day (to_date ('1970-02-11 ', 'yyyy-mm-dd'), round (sysdate, 'month'), round (sysdate, 'day '), round (sysdate, 'Year'), round (sysdate, 'dd '), from dual/* 4 time conversion yyyy-year English expression Year Number mm-month English expression month English dy-week Chinese dd-week English day date mi minute SS second hh24 24 hour time expression HH the default hour indicates */select to_char (sysdate, 'Year-month-day'), to_char (sysdate, 'Year-MONTH-DY '), to_char (sysdate, 'Year-month-ddspth'), to_char (sysdate, 'yyyy-mm-dd hh24: MI: ss') from dual/* 5 converts a number to a string. The second parameter is converted in the format of 9, which indicates any number of 0-9, 0 indicates the placeholder. If 0 is not enough, 1 indicates 1234. 2. The number of digits of the second digit exceeds the number specified in the format. Therefore, 3 cannot be displayed, therefore, only 12 4 0 indicates placeholder 5 to convert decimal places. The format of 6 L indicates converting to RMB $ indicates USD */select to_char (1234, '123') must be defined before and after the decimal point '), to_char (1245677, '000000'), to_char (12, '000000'), to_char (9999, '000000'), to_char (9999, '99. 0000 '), to_char (334455.33, 'l999, 999,999.00') from dual/* 6 to_date function to convert the string to a date. */select to_date ('10, September 19 92 ', 'dd month yyyy ') from dual/* 7 to_number function converts a string to a numeric format. The string is the best digit */select to_number ('123 ') from dual/* 8 functions can be nested */select to_char (next_day (add_months (sysdate, 6), 1), 'day, month ddth, yyyy 'from dual/* multi-Table query */select * from city/* Outer Join */select C. city_no, P. name_c pname, C. name_c cname from City C inner join province P on C. province_no = P. province_no selcet * From item_master_tb/* inner connection */select. item_number,. description, D. department_name from item_master_tb a inner join dept_master D on. franchise = D. department_code select count (*) from item_master_tb where itemgroup6 is null select count (*) from item_master_tb group by group_code having count (*)> 1 where itemgroup6 is null/* left join * // * The data to be case sensitive * // * the same as the SQL language */select. item_number,. description, G. description from item_master_tb a left join itemgroup_master g on. itemgroup6 = G. group_code where G. group_type = 's6 't/* another way of writing: * // * To be clear + put on the right is left join on the left is right join */select. item_number,. description, G. description from item_master_tb A, itemgroup_master g where. itemgroup6 = G. group_code (+) and G. group_type = 's6 'select * From itemgroup_master select s7.group _ code, s7.description, s6.description from itemgroup_master S7 inner join itemgroup_master S6 on s7.parent _ code = s6.group _ code where s7.parent _ code is not null/* grouping query important select group by having order by count (*) calculate the number of rows of all records count (CA) calculate the number of rows of a field. Only the rows that appear after group by can appear in select, oreder, this indicates that having and order by are optional having conditions for further filtering. It is a frequently used function for further filtering grouping queries After grouping: */AVG sum min max count () /* count () function */select count (*) Total, count (CA) from djzhb select * From item_master_tb/* are generally grouped before calculating the average or total value */select franchise, AVG (dist_price), max (dist_price), min (dist_price ), count (item_number) from item_master_tb group by franchise having AVG (dist_price)> 1000 order by franchise/* subquery super important/* execute the first step of Data nesting in it to filter the outer layer and query the data based on the layer */single row data */ /* directly use '=' */select item_number, last_updated_time from item_master_tb where last_updated_time = (select max (last_updated_time) from item_master_tb) /* to use in for multi-row data, the nested query will return multiple records. In this case, use in */select item_number, franchise from item_master_tb where franchise in (select deparment_code from dept_master where department_type = 0) create user wangshukui identified by "wangshukui" Profile default; grant connect to wangshukui with admin option; grant DBA to wangshukui with admin option; grant resource to wangshukui with admin option; grant unlimited tablespace to wangshukui with admin option; select * From customersselect * from sales/* connects three tables */select S. productid, S. customerid, S. saleprice, P. productname, C. firstname, C. lastname, S. saledatefrom sales s inner join MERs con C. customerid = S. customeridinner join products Pon p. productid = S. productidwhere S. saledate> = to_date ('2017-10-01 ', 'yyyy-mm-dd') and S. saledate <= to_date ('1970-10-30 ', 'yyyy-mm-dd') Select sales s select S. customerid from sales S group by S. customerid having count (customerid)> = 2/* You must learn the key points of UML and design patterns. use tools to express what you want to say in your own minds. Software development is successful. fully mobilize thinking. database design concepts. Create tables *// * Delete table */drop table s_dept/* Create */create table s_dept (/* define primary key */ID number (7) constraint s_dept_pk primary key,/* defines the constraint. The * // * not null constraint */name varchar2 (25) Constraint s_dept_name_nn not null, region_id number (7 ), /* the following constraint table is defined on the columns that are applied to the table. It can act on multiple columns */constraint s_dept_name_region_id_uk unique (name, region_id) /* Define table-level constraints */); Create Table s_emp (ID number (7) primary key, last_name varchar (25) not null, first_name varchar (25 ), /* two constraints, not null, are also constraints */userid varchar2 (8) Constraint s_emp_userid_nn not null constraint s_emp_userid_uk unique,/* default constraint, the default system time */start_date date default sysdate, comments varchar2 (25), manager_id number (7), title varchar2 (25 ), /* Reference constraints */dept_id number (7) Constraint s_emp_dept_id_fk references s_dept (ID), salary number (),/* Check constraints */Commission number) constraint s_emp_commission_ck check (commmision in (10, 12.5,);/* Copy another user's table * // * only copy data, the table's primary key information does not exist */create table distributor_mastor as select * from interface. distributor_master select * From distributor_mastor/* Add constraints on existing tables */Add constraint distributor_masterpk primary key (dist_id)/* constraints in Oracle: not null unique primary key foreign key check * // * default is not a method to restrict the modification of the default values of columns in a table: */alter table s_emp/* modify is used to modify the column */modify start_date default sysdate/* Chapter 9 data dictionary understand the system table user tables */select distinct object_type from user_objects select * From user_objects /* all object names in the query system */select object_name from user_objects where object_type = 'table'/* User table */select * From user_tables select constraint_name, constraint_type, search_condition, r_constrinte_name from user_constraints where table_name ='s _ EMP '/* manipulate data insert update Delete commit savepoint rellback * // * a semicolon is required only for batch table creation, generally, the semicolon */create table lesson_test (std_id int not null, msg_id int not null, msg_text varchar2 (200) not null, updatetime date default sysdate, primary key (std_id, msg_id)/* insert value: */insert into lesson_test values (48, 1, 'my first message', sysdate) select * From lesson_test/* Specifying null can be written directly */insert into lesson_test values (, 'myu', null) /* If the data of some segments inserted into the table is not specified, use the default value instead of */insert into lesson_test (std_id, msg_id, msg_text) values (, 3, 'myu ') insert into lesson_test values (, 'myu', null, sysdate)/* Insert date, by converting to date */insert into lesson_test values (, 'dff ', to_date ('1970-10-10 ', 'yyyy-mm-dd') Select * From lesson_test/* to extract all the data from one table and assign it to another table, the second table must be of the same type as the fields in the first table */create table lesson_test_history (std_id int not null, msg_id int not null, msg_text varchar2 (200) not null, updatetime date default sysdate, primary key (std_id, msg_id)/* copy statement: */insert into lesson_test_history select * From lesson_test select * From lesson_test_history/* modify data update set * // * | equivalent to the + operator */update lesson_test set msg_text = msg_text in C sharp | | 'modified' where std_id = 48 and msg_id = 4 select * From lesson_test/* delete data in a table */delete from/* You can specify the row to be output through where * /delete from lesson_test where updatetime <to_date ('2017-05-01 ', 'yyyy-mm-dd')/* The transaction first deletes the table, and the data is empty */delete from lesson_test/* sets a rollback point */savepoint A for the table; /* Insert a record */insert into lesson_test (std_id, msg_id, msg_text, updatetime) values (2008, 6, 'ddddddddd', to_date ('2017-04-04 ', 'yyyy-mm-dd ')) select * From lesson_test/* roll back to point a without data */rollback to A/* view the table information when it is found to be rolled back to a */select * From lesson_test/* again roll back, the information of the preceding statement is rolled back. The delete statement */rollback/* is used to query the previous data. */select * From lesson_test/* commit is used to submit the data. to modify a table, after the query is executed, only Click Commit to change the data in the table. Because all the data will use logs to save any of our operations, only by clicking the above commit will the data be actually written to the table. When we delete the data in the table, it is actually operated in the virtual table and will not affect the real table, only click the commit button above to write the data into the table */insert into lesson_test (std_id, msg_id, msg_text, updatetime) values (2008, 5, 'ddddddddd', to_date ('2017-04-04 ', 'yyyy-mm-dd ')) commit/* indicates that after the preceding operation data is submitted, all rollback points defined earlier will be deleted */select * From lesson_test order by msg_id/* Modify Table * // * creating a table now adds a column constraint to the modification and modification constraints of an existing table to delete a column constraint delete a table */alter table/* Table */Add modify drop/* column */Enabled /* Add column */select * From lesson_test alter table lesson_test add comments varchar (255) NULL;/* Add a column * // * delete a column */drop column comments/* modify a column to a large value. For example, the field type of the original column is 100 string types, only 200 */alter table lesson_test modify msg_text varchar2 (200) select * From lesson_test/* foreign key */drop table lesson_test create table lesson_test (std_id int not null, msg_id int not null, msg_text varchar2 (200) not null, updatetime date default sysdate, primary key (std_id) Create Table lesson_test_type (msg_type int not null, msg_typename varchar (200), primary key (msg_type) insert into lesson_test_type values (2, 'dfdff') Select * From lesson_test_type/* Add foreign key */alter table lesson_test add constraint s_lesson_text_id_fk foreign key (msg_type) References lesson_test_type (msg_type) /* disable constraints */alter table lesson_test disable constraint s_lesson_text_id_fk/* enable constraints */alter table lesson_test enable constraint s_lesson_text_id_fk/* delete a table, cascade all constraints and table-related cascading deletion */drop table lesson_test cascade constraints/* Modify Table name */rename lesson_test to lessonabc select * From lessonabc Delete table lessonabc rollback/* will not be deleted, rollback will return to the original data and delete the temporary table */truncate table lessonabc/* to delete the data, delete the actual table data * // * sequences is used to generate a sequence of non-repeated numbers to make the column unique */create table dept (ID int not null, name varchar2 (200), primary key (ID) insert into dept values (wangshukui_test_ SQL .nextval, 'test') Select * From DEPT/* Statement for creating sequence: */-- create sequence wangshukui_test_sqlminvalue 1 maxvalue 9999999 start with 201 increment by 1 cache 200 cycle; -- exercise: create a sequencecreate sequence sq_first1increment by 1 start with 6534 maxvalue 34356667777888 cache 200 cycle; insert into dept values (sq_first1.nextval, 'test') Select * From DEPT/* view by yourself: benefits: direct access to the database can be prevented, and data can be protected so that employees can only access basic-level data and encapsulate it in an attempt * // * simple attempt: if the involved table does not have a function, there is only one table, and other complex content, try: Multiple tables, functions, and other information * // * Create a view */create or replace view vw_lwsson_testas select std_id, msg_id, msg_text from lessonabc with read only -- create read-only view select * From vw_lwsson_testselect * From lessonabcinsert into lessonabc values (1, 2, 'dff', sysdate) -- modify view update into msg_text = msg_text | 'mod by view' where std_id = 48 and msg_id = 1 select * From vw_lwsson_test -- complex view -- there are multiple tables create or replace view vw_lesson_test2 asselect rownum no, l. std_id, L. msg_id, L. msg_text, T. msg_typenamefrom lesson_test_history L, lesson_test_type twhere L. msg_id = T. msg_type -- specify the alias: -- It can also be seen as a view -- if a table of another user is frequently used, you can specify a simple name keyword synonymcreate synonym province for interface. province select * from province select * From lessonabc -- of course, you can also give the current user a name for a table -- query the two names to obtain the information of this table create synonym lesson for lessonabc select * from Lesson select * From lessonabc/* */select * From liushuai. lesson_test_type/* the index is the B + tree index in the data structure. You can query data through the index. Some index systems automatically create */automatically unique non-unique Single Column concatenated/* for a table. create an Index */create index temp_lesson_test on lesson_test_type (msg_type) /* Delete Index */drop index temp_lesson_test/* index creation principle: Which columns are indexed generally: Which columns need to be indexed, if a column is frequently displayed in the where condition, if a column has a large value range, if most of the data in a column is null, the index will combine the values with data, in this way, we will first query the data. If two or more columns are often combined and appear in the where condition, we can combine them to create an index. If the table's data is huge * // * in this case, you do not need to create an index: if the table is small, if a column is not frequently used, if the number of rows returned by each query is less than 2-4% of the total number of rows, no index is required. If a table needs to be updated frequently, no index is required */