Oracle_DAY 03 operation statement

Oracle_DAY 03 operation statement 1. use a subquery to query who is a common employee: select first_name, id from s_emp where id in (lead id); select first_name, id from s_emp where id in (select distinct manager_id from s_emp); select first_name, id from s_emp where id in (1, 2, 3, black box); select first_name, id from s_emp where id not in (select distinct manager_id from s_emp); Note: when using not in, pay attention to the NULL value select first_name, id from s_emp where id not in (select disti Nct manager_id from s_emp where manager_id is not null); select first_name, id from s_emp where id not in (select distinct nvl (manager_id,-1) from s_emp); select first_name, id from s_emp where id not in (select distinct nvl (manager_id, 1) from s_emp); select first_name, id from s_emp where id not in (select distinct nvl (manager_id, 25) from s_emp); // logic error: select title from s for the employee with the same id as 1. _ Emp where id = 1; select first_name, title from s_emp where title = (select title from s_emp where id = 1) and id! = 1; ---------------------------------------- SQL data type: number numeric type varchar2 (n) variable-length string char (n) fixed-length string 4 k date type CLOB large character type 4g BLOB large binary type 4g date type: expression System current time sysdate and language-related ------- NLS_LANG English default format DD-MON-YY 07-NOV-12 07-11-12 processing date function language environment time expression problem to_date (date string to be converted, format String) table creation fields must follow the definition of the identifier create table name (field name type, field name type, field name type); create table testtype (id number, fname varchar2 (10 ), sname char (10), bi Rday date); insert statement insert into testtype values (1, 'xsy ', 'xsy', sysdate); insert into testtype values (9527, 'zxc ', 'zxc ', '01-NOV-08 '); insert into testtype values (9527, 'zxc', 'zxc ', '01-November-08'); to_date insert into testtype values (1, 'abc', 'abc', to_date ('2017-08-08 08:08:08 ', 'yyyy-mm-dd hh: mi: ss ')); yyyy four-digit year mm two-month dd day hh 12 hours hh24 mi minute ss second day expression day of the week mon abbreviated month to_char (date, 'date format') select to_char (Sysdate, 'yyyy-mm-dd hh24: mi: ss day mon') from dual; to_date is the addition and subtraction of the date in to_char format: select to_char (sysdate-1, 'yyyy-mm-dd hh24: mi: ss day mon') from dual; move one hour forward select to_char (sysdate + 1/24, 'yyyy-mm-dd hh24: mi: ss day mon') from dual; add a month before pushing a month to select to_char (add_months (sysdate, 1 ), 'yyyy-mm-dd hh24: mi: ss day mon') from dual; select to_char (add_months (sysdate, 2), 'yyyy-mm-dd hh24: mi: ss day Mon ') from dual; next_day: select next_day (sysdate, 'Friday') from dual; select next_day (sysdate, 'Friday '), 'Friday') from dual; last_day select to_char (last_day (sysdate), 'yyyy-mm-dd hh24: mi: ss day mon') from dual; supplement: round (date) select to_char (sysdate, 'yyyy-mm-dd hh24: mi: ss day mon'), to_char (round (sysdate + 1/24 ), 'yyyy-mm-dd hh24: mi: ss day mon') from dual; by default, select to_char (sysdat E, 'yyyy-mm-dd hh24: mi: ss day mon'), to_char (round (sysdate, 'mm'), 'yyyy-mm-dd hh24: mi: ss day mon') from dual; select to_char (sysdate, 'yyyy-mm-dd hh24: mi: ss day mon'), to_char (round (sysdate, 'y '), 'yyyy-mm-dd hh24: mi: ss day mon') from dual; trunc (date) select to_char (sysdate, 'yyyy-mm-dd hh24: mi: ss day mon'), to_char (trunc (sysdate, 'mm'), 'yyyy-mm-dd hh24: mi: ss day mon') from dual; select to_char (sysdate, 'yyyy-mm -Dd hh24: mi: ss day mon '), to_char (trunc (sysdate, 'y'), 'yyyy-mm-dd hh24: mi: ss day mon ') from dual; execute a statement at any time of the month. The result is that the start time of the next month is to_char () trunc (add_months (sysdate, 1), 'mm ') trunc (last_day (sysdate) + 1) -------------------------------------------- type primary key of SQL constraints ------ unique primary key ------ unique non-null check ------ check foreign key ------ foreign key references table constraints: the last barrier of Data primary key ------ unique and non-empty, a table can only have one primary key at most Unique ----------- there cannot be repeated non-empty ----------- there cannot be NULL value check ------ the field must meet the check criteria foreign key ------------ involves two tables s_dept ----- id (master table) s_emp ----- dept_id (sub-table) The table that defines the foreign key is the child table s_dept ----- region_id s_region ---- id (primary key) foreign key: reference the fields of the main table, must have unique restrictions. The foreign key value can only be the value of the primary table field, or is NULL. This ensures the integrity of the sub-table data. If the foreign key constraint is violated, the Data Integrity Constraint is reported. Constraint Syntax: column-level constraints: constraints defined directly when defining columns in a table 1. primary Key ------- create table testconstraint_pk (id number, name varchar (30); insert into testconstraint_pk values (1, 'aobama '); drop table testconstraint_pk; create table testconstraint_pk1 (id number primary key, name varchar (30); insert into testconstraint_pk1 values (1, 'aobama '); if the constraint is invalid, the system constraint name create table testconstraint_pk2 (id number constraint testconstraint_pk2_id_pk p Rimary key, name varchar (30); insert into testconstraint_pk2 values (1, 'aobama '); requires that you create a table with two field IDs: numbername varchar2 (20) create table test_c_pkuk (id number constraint test_c_pk_id_pk primary key, name varchar (30) constraint test_c_pkuk_name_unique ); insert into test_c_pkuk values (211, 'abc'); 0001: violation of unique constraints (OPENLAB. TEST_C_PKUK_NAME_UK) the column-level constraint of not null is the same as the check constraint of the primary key language. You are required to create a table with two fields id numbersalary number. The id is required to set the column-level primary key constraint. salary is required to set the column-Level Check constraint. The salary is greater than 3500 create table test_c_pkck (id number constraint test_c_pkck_id, salary number constraint test_c_pkck_salary_ck check (salary> 3500); insert into test_c_pkck values (10087,3500); table-level constraints: why do table-level constraints apply to certain columns of the table after all column definitions are completed? Create a table with three fields fid number sid number name varchar2 (30) the combination of fid and sid is required for primary keys. The combination of fid and sid is required to uniquely solve the combination constraint not null. This constraint cannot appear. the syntax of Union non-null, not null, and table-level constraints is as follows: create table test_table_pk (id number, name varchar2 (30), salary number, constraint test_table_pk_id_pk primary key (id); create table fid numbersid numbername varchar2 (20) using table-level constraints) salary numberfid sid must be combined for primary key name unique salary must be greater than 8000 create table test_table_con1001 (fid number, sid number, name varchar2 (20), salary number, constraint test_table_con1001_fsid_pk primary key, sid), constraint test_table_con1001_name_uk unique (name), constraint test_table_con1001_salary_ck check (salary> 8000); foreign key: -- primary table: department table drop table myemp; drop table mydept; create table mydept (id number primary key, name varchar2 (30);/* sub-table: employee table */create table myemp (id number primary key, name varchar2 (30 ), fid number constraint myemp_fid_fk references mydept (id); 1. create a primary table and then a subtable 2. insert data into the primary table first unless the foreign key of the sub-Table uses NULL values insert into mydept values (1, 'test'); insert into myemp values (2, 'taoge ', 1); 3. delete data first, delete the child table, and then delete the parent table unless you have set cascade (cascade null cascade delete. delete A table first, delete the child table, and then delete the parent table drop table s_dept cascade constraints; (remove the primary and foreign key relationship before deleting the table) change the following foreign key relationship to the table-level foreign key constraint create table mmdept (id number primary key, name varchar2 (30);/* sub-table: employee table */create table mmemp (id number primary key, name varchar2 (30), fid number, constraint mmemp_fid_fk foreign key (fid) references mmdept (id )); cascade ----- cascade null cascade Delete drop table mmdept; drop table mmemp; create table mmdept (id number primary key, name varchar2 (30); insert into mmdept values (1, 'test'); commit;/* sub-table: employee table */create table mmemp (id number primary key, name varchar2 (30), fid number, constraint mmemp_fid_fk foreign key (fid) references mmdept (id) on delete cascade); insert into mmemp values (1, 'zs', 1); insert into mmemp values (2, 'zss', 1); insert into mmemp values (3, 'zss', 1); commit; when the primary table deletes data, the table Creates table mmemp (id number primary key, name varchar2 (30), fid number, constraint mmemp_fid_fk foreign key (fid) references mmdept (id) on delete set null); ------------------------------------------ constraints: class 5 constraints ---- a B c d e syntax ---- table-level and column-level not null check foreign key ---- cascading null deletion ---------------------------------------------- dmlinsert delete update 1 in the database. insert statement insertcreate table testdmla (id number primary key, fname varchar2 (10), sname char (10); insert into table name values (field value 1, field Value 2 ,....); insert into testdmla values (1, 'abc', 'abc'); select length (fname), length (sname) from testdmla; select * from testdmla where fname = 'abc'; select * from testdmla where sname = 'abc'; select * from testdmla where fname = 'abc '; select * from testdmla where sname = 'abc'; if the data length is not enough, fill in the space insert into testdmla (id, sname) values (100, 'baoge '); you must include all non-empty fields to register an account: insert into testdmla values (10086, 'quange ', 'chen'); insert into testdmla values (10087, 'moyan ', 'Mo '); commit; ------ confirm the transaction rollback; ------ cancel the transaction (transaction) General transaction who initiated and ended the drop table testdmla; create table testdmla (id number primary key, fname varchar2 (30), sname char (30); insert into testdmla select id, first_name, last_name from s_emp; insert into testdmla (id, fname) select id, first_name from s_emp; 2. delete data delete from table name where condition; delete from testdmla where id = 1; commit; rollback; 3. update Data update table name set field name = value, field name 2 = value where condition; update testdmla set fname = 'who 'where id = 10086; dml statements all have transaction characteristics. transactions are not automatically committed. ddl statements are automatically committed. select statements have no transaction characteristics.