Oracle_DAY 02 operation statement

Oracle_DAY 02 operation statement where clause ----- limit row return 1. if the data that meets the where condition is returned, if the where condition is not met, the first_name with the query salary greater than 1200 is filtered out. salary select first_name, salary from s_emp where salary> 1200; 2. comparison of strings and numbers query first_name select first_name, salary from s_emp where salary = 2500; query first_name is the salary of Carmen select first_name, salary from s_emp where first_name = 'carmen'; select first_name, salary from s_emp where first_name = 'carmen'; // no Rows selected Note: string values are case sensitive and string values are case sensitive. For example, 'A' a' is different. 3. calculation (comparison) symbol in SQL <1>. logical comparison operator number = <>>=< 2>. the SQL comparison operator number between low value and high value expresses a closed interval [1500, 2500] To Find The first_name, salary select first_name, salary from s_emp where salary between and; 4. in (list) List ----- A group of values separated by commas (,) to find the first_name, salary select first_name, salary from s_emp where id in (, 9) whose id is 1, 3, or 9 ); in: Related to the sequence and data composition in the list, put the probability high in front of 5.is null ---- judge whether a value is NULL to find out the first_name, sa whose percentage is NULL. Lary select first_name, salary from s_emp where commission_pct is null; 6. fuzzy query like 'string' dragon Jackie Chan Bruce Lee dragon child Dragon dragon find all first_name with, like 'wildcard string' the SQL wildcard % represents any character 0-N _ represents an arbitrary character 1 (can and can only be one) select first_name from s_emp where first_name like '% a % '; find the first_name select first_name from s_emp where first_name like '_ a %'; 1. data dictionary table: user_tables table_name table name s_emp s_dept select table_name from user_tables Select table_name from user_tables where table_name like's _ % '; 2. in the data field table, the values of all data are processed by default in the upper case: select table_name from user_tables where table_name like's _ % '; wildcard escape: select table_name from user_tables where table_name like's \ _ % 'escape '\'; 3. conditional connection symbols c and c ++ int a = 10; if (5 <a <9) {printf ("shenma shidao ");} and or not to find the first_name, salary select first_name, salary from s _ Emp where salary> = 1500 and salary <= 2500; find first_name, salary select first_name, salary from s_emp where salary> 1500 and salary between (2500; find the first_name, salary select first_name, salary from s_emp where id = 1 or id = 3 or id = 9; ><===! ==<> Between and not between and in not in like not like is null is not null to find the first_name, salary select first_name, salary from s_emp where manager_id is not null; 4. the SQL statement may find more results in order by clause 1. sort order by of data must appear in the form of sorting standard order at the end of the SQL statement (ascending asc, can be omitted, default order; descending desc, cannot be omitted, display id, first_name select id, first_name, salary from s_emp order by salary; equivalent to select id, first_name, salary from s_emp order by 3; equivalent to select id, first_name, salary from s_emp order by salary asc; 2. multi-field sorting the first sorting field the second sorting field shows first_name in wage sorting. If salary has the same salary, select id, first_name, salary from s_emp order by salary asc, first_name desc in descending order of first_name; different first-order fields are sorted by the first-order field. If the first-order field is the same, the second-order field rule is enabled. single-row function: number of rows affected by the SQL statement, returns a result for each row: select upper (first_name), first_name from s_emp; select upper (first_name) from s_emp where salary> 3500; group function: after processing a set of data, a result is obtained. count: select count (first_name) from s_emp; select count (first_name) from s_emp where salary> 1500; select count (first_name) from s_emp where salary> 3500; test when the row function ---- test table ----- dual this table is a single row single column table single row function: 1. string processing function upper ----- change data to uppercase lower ----- decrease data to write initcap ---- change the first letter of each word in the string to uppercase select initcap ('One world one Dream') from dual; concat ---- connect two strings select concat ('hello', 'World') from dual; substr (parameter 1, parameter 2, parameter 3) parameter 1: string parameter 2 from where the first one is 1 parameter 3: How long the select substr ('hello',) from dual; select substr ('hello ', 1, 3) from dual; the second parameter can be a negative number select substr ('hello',-3, 3) from dual; display the last three characters of first_name, use two different methods to complete length ------- to obtain the string length: select length ('hello') from dual; select first_name, upper (substr (first_name, 1, 3) from s_emp;