Oralce (2)

Oralce (2)

[SQL] // table join query // 1. select s. subscriberno, c. customerid from subscribern s, customer c where s. customerkey = c. customerkey and subscriberno = 'cxm'; // 2. join // select s. subscriberno, c. customerid from subscriber s inner join customer c on s. customerkey = c. customerkey where s. subscrinerno = 'cxm'; // naturally connects to select s. subscriberno, c. customerid from subscriber s natural join customer c on s. custom Erkey = c. customerkey where s. subscriberno = 'cxm'; // Outer Join (1) left outer link select s. subscriberno, c. customerid from subscriber s left outer join customer c on s. customerkey = c. customerkey where s. subscriberno = 'cxm'; (2) Right Outer Join select s. subscriberno, c. customerid from subscriber s rimo-outer join customer c on s. customerkey = c. customerkey where s. subscriberno = 'cxm'; (3) select s. subscriberno, c. custo Merid from subscriber s full outer join customer c on s. cusomterkey = c. cusomterkey where s. subscrinerno = 'cxm'; // set operation (1) union: You can accumulate the result row of the first query and the result row of the second query, in addition, it can remove duplicate rows select subscriberno from subscriber where last_name like 'C % 'or last_name like' B % 'union select subscriberno from subscriber where last_name like 'v %' or last_name like 'N % '; (2) union all: The result row of the first query and the result row of the second query can be accumulated, but duplicate row data cannot be excluded. Select subscriberno from subscriber where last_name like 'C % 'or last_name like' B % 'union all select subscriberno from subscriber where last_name like 'v %' or last_name like 'N % '; // subquery (1) in keyword select * from subscriber where customerkey in (select customerkey from customer where customerid = 'cxm'); (2) exists Keyword: in some cases, you only need to determine whether the data exists, whether the conditions are met, and do not care about the data itself. In this case, you can use exists for subquery select * from subscriber Where exists (select * from customer where customerid = 'cxm'); (3) comparison operator: when using a comparison operator to connect to a subquery, make sure that only one value is included in the returned results of the subquery. Otherwise, the entire query statement fails to select * from subscriber where subscriberkey = 'cxm' and salary> = (select avg (salary) from salary_table where salary_id = 'cx '); // data operation // Add (1) General insert statement insert into table_name (column_name1, column_name2, column_name3) values (value1, value2, value3); (2) Batch insert statement insert into table_name (c Explain, column_name2, column_name3) Subquery; (subquery is a Subquery statement); for example: insert into subscriber (column_name1, column_name2, column_name3) select column_name1, column_name2, column_name3 from subscriber1; this reminds me of A previous interview question. At that time, the interviewer's question was like this: Create A new table and put all the data in table? Now let's look at this method. step 1: Create Table B. Its table structure is the same as that of Table. Create table B (...); step 2: insert into B (...) select... from A; // modify (1) update table_name set column_name = ''where condition; (2) update table_name set column_name = (select Avg (salary) from salary where salaryid = 'cxm') where condition; for example, update subscriber set salary = (select avg (salary) from salary where salaryid = 'cxm ') where subscriberid = 'cxm'; note that s is provided through select Make sure that the values returned by the select statement are single. // Delete from table_name where condition; if you want to delete all records in the Table: We recommend that you use truncate; truncate is more efficient than delete because it does not generate rollback information, therefore, after truncate is executed, you cannot roll back the truncate table table_name; // Data Control (1) grant permission on object to user with grant option; (whether the with grant option allows users to transmit permissions) for example; grant select on table subscriber to user1; // grant the table query permission to user1 grant all privileges on table to user1; // grant all permissions of the table to user1; grant select on table subscriber to public; // grant the query permission of the table to all users using grant update (column_name ), select on table_name to user1; // grant the table's permission to modify a field and query to a user grant createtab on database database_name to user1; // grant the table creation permission to user1; (2) revoke the revoke permission on the object from user; for example, revoke select on table subscibre from user1;