Oracle database One, common SQL statements: 1. Connect to the System administrator account: Conn System/system (password on installation); 2. Create a new account: Creation user Leo (account name) identified by Leo (password) 3. Grant the new account: Grant Connect,resource to leo;4. Right to receive from an account: revoke resource from Leo; 5. Connect new account: Conn leo/leo;6. Display current customer: Show user;7. View table structure: DESC s_emp (table name); 8. No password to log in as DBA: Conn/as SYSDBA; Way of doing whatever it is, danger 9. Adding and removing changes to the basic syntax:add: INSERT INTO E_product (Id,name,price) VALUES (10001, ' Red rice phone ', 599);Delete : Delete from e_product where id = 10001;change: Update e_product set name= ' Xiaomi TV ', Price = 2999 where id=10001;check: Select Id,name,price from e_product where id = 10001; Database function 1.nvl (commission,0); This function can replace null values in a field (example: Commission) with 0 for easy operation. For example, when calculating commission, some employees do not have a commission. Case: List the total revenue for each employee for one year? (NVL) Select (NVL (commission_pct,0)/100+1) *12*salary from S_emp; 2.to_char (sysdate, ' yy '): Displays the date in a custom format ; case: Find out the entry time is all employee information for 90? Select First_name,start_date,to_char (start_date, ' yy ') from S_emp where To_char (start_date, ' yy ') =90; 3.lower (field /upper (field): Turns the value of a field into full-lowercase/full-capitalization to match unknown case characters; case: When you do not know ' Carmen ' when the database is case, find out ' Carmen ' wages? Select First_Name, salary from S_emp where lower (first_name) = ' Carmen '; 4.substr (attribute,indexfrom,length): The Intercept attribute value is a partial character of a string, starting with Indexfrom and intercepting length. Indexfrom is a negative representation of the penultimate number. Case: List the last two characters of each employee name (last_name)? Select Id,substr (last_name,-2, 2) from S_emp; 5.round (Attribute,index): The approximate value of a property is calculated, rounding the index bit. Index is negative when the decimal point to the left, the number of integers to the right. Case: List the approximate value of the employee's salary to 10 digits? Select Id,first_name, Round (salary,-1) from S_emp; 6.trunc (Attribute,index), the approximate value of an attribute is obtained, and the index position and the subsequent fraction are removed. Index is negative when the decimal point to the left, the number of integers to the right. Case: List the approximate value of the employee's salary, minus the fraction below 100 yuan? Select Id,first_name,trunc (Salary,-2) from S_emp; 7.sysdate: Gets the current system time. The format of the current system time is subject to localized strict effects, so the format you want to acquire yourself must be obtained through ToChar (sysdate, ' yy-mm-dd hh:mi:ss '); case: Find out the next day, the next minute, the next second. Select To_char ( Sysdate, ' Yy-mon-dd hh24:mi:ss ') as now, To_char (sysdate+1, ' Yy-mon-dd hh24:mi:ss ') as NextDay, To_char (1/(24*60) + Sysdate, ' Yy-mon-dd hh24:mi:ss ') as Nextmin,to_char (1/(24*60*60) +sysdate, ' Yy-mon-dd hh24:mi:ss ') as Nextsec from dual;& Nbsp;8.day: Represents the day of the week in a date. Case: Ask for a (today) day of the week select To_char (sysdate, ' days ') from Dual; 9.to_date (' 2015-11-09 ', ' yyyy-mm-dd '): Converts the date in string format to a date. 10.last_day (sysdate): Gets the date of the last day of the month for the given date. 9 and 10 cases: Find out the number of days of the Year Select last_day (to_date (' 1215 ', ' Mm/yy ')) +1-to_date (' 010115 ', ' dd/mm/yy ') from dual; 11. Avg (attribute): A field is worth an average. Case: List the average of commission? Select AVG (commission_pct) from S_emp; 12.count (attribute): Number of records in a field; case: Find out the number of employees with a commission? Select COUNT (*) from s_emp where commission_pct are not null; 13.group by: follow ... Group. By is followed by multiple fields, which are grouped by multiple fields. Case 1: Find out the average wage for each department? (single field) select Dept_id,avg (Salary) from S_emp Group by dept_id; Case 2: Find out the average of different positions in each departmentWages? (Multiple fields) select Dept_id,title,avg (Salary) from S_emp GROUP by dept_id,title; 14.having: filters out unwanted results after grouping. Case: Find out the average salary for different positions in each department and show only the results of the average wage above 2000? Select Dept_id,title,avg (Salary) from S_emp GROUP by Dept_id,title have avg (salary) >=2000; iii. database keyword 1. Distinct: Write in the Select field before the fields used to remove weight; case: SELECT distinct name from S_dept; 2.between ...: The value of a field is between a closed interval ""; Case: SELECT * From S_emp where salary between and 2000; 3.in: The value of a field is within an enumeration range; case: Select first_name,salary,dept_id from S_emp WH Ere dept_id in (31,42,43); 4.like: Fuzzy matching string, with _ and%; case: SELECT * from s_emp where first_name like ' _e% '; 5. Escape: Some of the symbols in the string have special meanings, and the following underscore _ indicates a single character in the fuzzy match, but you need to use escape to set the escape character if you want to represent the underscore only. Case: Select table_name from dba_tables where table_name like ' [email protected]_% ' escape ' @ '; 6. Is null: The record that gets the value of a field is empty; case: List which employees have no commission? Select Id,first_name from S_emp where commission_pct is null; 7.desc: in descending order of a field, with order by. Case: Display employee information in descending order of wages? SELECT * from S_emp ORDER by salary DESC; 8.ASC: ordered by a word orderby, withORDER BY. Case: Display employee information in ascending Commission order? SELECT * from S_emp ORDER by commission_pct ASC; NOTE: Null values are arranged in the "infinity" position; 9.order by: Sort. By can be followed by multiple fields, priority in order. Case: First wages descending, and then by the Commission in ascending order to show employees? SELECT * from S_emp ORDER by salary desc,commission_pct asc; four, multi-table query 1.join...on ...: Connect two tables together. On is followed by an association condition of two tables. Case: List employee name and department name? Method 1.select first_name| | ' ' | | last_name, name from S_emp e,s_dept d where e.dept_id = D.id; method 2.select e.first_name| | ' ' | | E.last_name, D.name from S_emp E joins s_dept d on e.dept_id = d.id; Note: Oracle | | Represents a plus sign, ' represents a string. 2. Three-Table Association query (two methods) Case: List ' Carmen ' in which area to work? --Method One: (Oracle, SQL Server syntax) Select R.name from s_emp e,s_region r,s_dept dwhere e.dept_id = d.id and d.region_id = r.i D and e.first_name = ' Carmen ';--Method Two: (Database general syntax) Select R.namefrom s_emp e join s_dept d on e.dept_id = D.id join S_region r o n d.region_id = r.idwhere e.first_name= ' Carmen '; v. DDL data Definition Language 1. Creating tables: Create TABLE e_category ( ID number ( 0) not null , t_category VARCHAR2 (+) , CONSTRAINT table1_pk PRIMARY key ( id ) enable ) 2. Insert data into the table insert into "SYSTEM". " E_category "(ID, T_category) VALUES (' 1 ', ' smart phone ') INSERT into" SYSTEM "." E_category "(ID, T_category) VALUES (' 2 ', ' digital camera ') INSERT into" SYSTEM "." E_category "(ID, T_category) VALUES (' 3 ', ' laptops ') INSERT into" SYSTEM "." E_category "(ID, T_category) VALUES (' 4 ', ' shirts ') INSERT into" SYSTEM "." E_category "(ID, T_category) VALUES (' 5 ', ' coats ') INSERT into" SYSTEM "." E_category "(ID, T_category) VALUES (' 6 ', ' slacks ') 3. Modify the table structure: add a field; ALTER TABLE e_category add Bigcate varchar2 ( 4. Modify table structure: Add a foreign key; ALTER TABLE E_PRODUCT add constraint fk_product_bigcate foreign key (P_category) References e_bigcate (ID); 5. Modify table Structure: Modify a field: ALTER TABLE e_product modify p_cdate timestamp (8); 6. Modify Table Structure: Modify the field name; ALTER TABLE e_product rename column p_name to name; 7. Modify Table structure: Delete foreign key: ALTER TABLE e_product drop constraint FK _product_category;8. Modify Table Structure: Modify FOREIGN key: idea: First delete after add view: Create view name as SELECT ... sequence (Sequence): Min:max:increm://sequenc:currval:nextval; index: Index: 1. Speed up Retrieval Logical Structure of the database: Database-table-segment-Block; Lock: For UPDATE---Row-level lock release lock when connection or commit is closed;
Common SQL statements and knowledge of Oracle database
