Oracle Study Notes (3)

Oracle Study Notes (3) filter grouping results select from where group by having order by -- for group function filtering, use having select deptno, avg (sal) from emp group by deptno having avg (sal)> 2000; where-> group by-> having select deptno, count (*) from emp group by deptno having count (*)> = 5 order by count (*); select sal from emp where ename = 'Ford '; select ename from emp where sal> 3000; -- subquery select ename from emp where sal> (select sal from emp where ename = 'Smith '); select ename, sal from emp where sal = (select min (sal) from emp); -- if the subquery returns a data, you can use the =,>, <, >=, <= single row comparison operators. -- If the subquery returns multiple rows of data, the multi-row comparison operator must be used: in select ename, job from emp where deptno in (select deptno from emp where job = 'cler '); -- If the subquery returns a null value and uses the not in operator, no result is returned. select ename from emp where empno not in (select mgr from emp); -- select ename, sal from emp where sal = (select max (sal) from emp); -- select deptno, ename, sal from emp where (deptno, sal) in (select deptno, max (sal) from emp group by deptno); -- select deptno, avg (sal) from emp group by deptno having avg (sal)> (select avg (sal) from emp where deptno = 20); associated subquery -- which employees have higher salaries than the average salaries of the department select ename, sal, deptno from emp outer where sal> (select avg (sal) from emp where deptno = outer. deptno); select ename, job, deptno from emp outer where exists (select 'A' from emp where mgr = outer. empno); -- select deptno, dname from dept outer where not exists (select 'x' from emp where deptno = outer. deptno); -- set operation A = {1, 2, 3, 4, 5, 6} B = {2, 4, 6, 8} A union B = {1, 2, 3, 4, 5, 6, 8} A union all B = {1, 2, 3, 4, 5, 6, 6} A intersect B = {2, 4, 6} A minus B = {1, 3, 5} -- union all select worker. ename, manager. ename from emp worker join emp manager on worker. mgr = manager. empno union all select ename, 'boss' from emp where mgr is null; -- replace union in SQL with -- union all/intersect/minus to try the select job from emp where deptno = 10 union select job from emp where deptno = 20; constraint condition primary key: primary key, PK = unique + not null foreign key: foreign key, FK unique: unique non-null: not null check: check F: female/M: male Major: major -- create table student_ning (id number primary key, name varchar2 (20), email char (40), registtime date default sysdate); -- add record insert into student_ning (id, name, email, registtime) VALUES (1, 'Peter ', 'Peter @ 123.com', sysdate); -- if you add all the fields, you can omit the field name insert into student_ning VALUES (3, 'chris ', 'chris @ 1.com', sysdate); -- if not all fields are added, you must provide the field list insert into student_ning (id, name) VALUES (13, 'Bono'); -- add multiple records at a time -- replace values insert into student_ning (id, name) select empno, ename from emp where deptno = 10; -- Adjust the format in sqlplus, taking column width as an example -- special emphasis: sqlplus command, non-SQL statement. -- Adjust the specified columns to a 10-character column name format a10 column numeric column name format 9999 column sal for 9999 column ename for a8 column job for a9 column mgr for 9999 col comm for 9999 select * from emp; -- create a constraint -- if the created constraint is not named, it is automatically named by the system. SYS_Cn. -- 1. create a primary key constraint: -- 1) create a table at the column level or table level. -- 2) create a table after creating the table and create it at the table level. example 1. -- Take student_ning as an example -- create a primary key constraint. It is named by the system and is column-level. create table student_ning (id number primary key, name varchar2 (20), email char (40), registtime date default sysdate); example 2. -- CREATE a primary key constraint and name it by yourself. It is created at the TABLE level. -- the constraint is named stuning1_id_pk create table student_ning1 (id NUMBER, name VARCHAR2 (20 ), CONSTRAINT stuningincluid_pk primary key (id); example 3. -- create a primary key constraint after the table is created -- the constraint name is customized (stuning2_id_pk). It is best to make sense. create table student_ning2 (id number, name varchar2 (20); alter table student_ning2 add constraint stuning2_id_pk primary key (id); -- view the primary key constraint select constraint_name, constraint_type from user_constraints where table_name = 'student _ ning2'; -- understand the role of primary key constraints. insert into student_ning (id, name) values (1234, 'Peter '); -- repeat the previous statement and try to insert a duplicate id value of 1234, returns the ORA-00001 that violates the unique constraint -- does not provide a primary key value, returns the "id cannot be blank" error insert into student_ning (name) values ('Peter '); -- drop table student_ning; -- 2. create non-null constraint: Only create table student_ning1 (id number primary key, name varchar2 (20) not null); select constraint_name, constraint_type from user_constraints where table_name = 'student _ NING1 '; -- this column must have a value. incorrect SQL: insert into student_ning1 (id) values (1); -- 3. create unique constraint -- the unique constraint of email is created at the column level -- the unique constraint of nickname is created at the table level create table student_ning1 (id number primary key, name varchar2 (20 ), nickname varchar2 (20), email char (30) unique, constraint stu_ning1_nickname_uk1 unique (nickname); -- the unique constraint can be empty but cannot be repeated. -- 4. check constraints create table student_ning2 (id number primary key, name varchar2 (20), sex char, constraint stuning2_sex_ck check (sex in ('M', 'F '))); -- correct example: insert into student_ning2 values (1234, 'Peter ', 'M'); -- incorrect example: insert into student_ning2 values (1235, 'chris ', 'A ');