Java:oracle (Table of additions and deletions, data and other changes)

1. Oracle Naming conventions: Similar    to Java naming Conventions 1. Strictly case-sensitive    2. All SQL statements must be '; ' End    3. All SQL is separated by a space: Sqlplus space/space as Space sysdba carriage return    4. Either the table name or the column names must start with a letter the class in Java must begin with an uppercase letter, followed by the Hump naming method    5. Cannot use Oracle's reserved word    6. Cannot be the same name as other user-defined objects    7. Number, $ #_, letter (can only contain a-z,a-z,0-9,_,$,#) Letter plus _   
    The EMP table has a column called EMP_NO (employee's number)

 2. There are two ways to connect to Oracle: cleanup command line: CLS Oracle cleanup command line: Host CLS 1.pl/sql connection 
 2. Native command line connection (CMD) sqlplus Enter user name: Scoot Please enter the password (the password entered is not visible on the command line): Enter User Scott locked! (the user is locked, unable to login!) )        

 Lock Command: First login to Admin user sqlplus/as SYSDBA: Use Super (System) Administrator login alter user Oracle username                Account Locked Sysdbs:system Database administator (super Admin) unlock command: First login to Admin user        Sqlplus/as SYSDBA ALTER user Oracle Username account unlock view current user login sql: show user; Modify the user password: (modified is the password!)            first of all to login to the administrator user in Oracle, in the future as long as the account of the word, it must be used to modify some of the user's information but only need to change the password when you do not need to use the key word        Alter user Oracle username identified by 123; Unlock and modify user passwords: Oracle has its own error code ORA-00922: Option missing or invalid alter user Scott account unlock identified by new        Code        View Current user status: select Username,account_status from Dba_status where username= ' Scott '; How to switch users after signing in to Oracle: conn user name; (for black box)　　　　　
Table  3.oracle: database is the need to store data, the table in the database to store the data stored in accordance with the provisions. Employee (EMP): The information department that holds the employee dept: Information about the Storage Department table: table name, column, data type of the defined column, row
　　
type of column:Three most common types: 1.varchar2 2.number 3.date/timestap char and VARCHAR2 are string types: Cha R is a fixed-length string.                    char (10), which must have 10 char type characters such as: Male, female, province (Henan province) VARCHAR2 is a string of non-fixed length.                It is recommended to use the VARCHAR2 Clob type (string): Character Set according to: a long, long, long, long string, with a maximum of 4 g of data stored. Decimal (in fact, is a long type of number: Larger than the amount of data stored in the number type):d ecimal (100,3): 100-bit integer, 3-bit decimalUnique identification of table data:            PRIMARY key: is to constrain the uniqueness of each row of data. Follow the features: not empty, and unique.to add a primary key when building a table:CREATE TABLE table name (type primary key NOT NULL for column names);after the table is established, specify the column as the primary keyThe premise is that there is no null data && no duplicate data. ALTER TABLE name add constraint (constraint, limit) pk_ table name _ specified column name primary key (the column specified by the primary key);table Creation (table name, column name, column type):CREATE table name (column name and column type, column name and column type) to be created;Create a student table:CREATE TABLE Stus (stu_no number (5), Stu_name varchar2 (,........));to add a primary key while creating a table:CREATE TABLE table name (the type of column primary key not NULL, the type of column name, the type of column name ...);deletion of the table:The name of the table that the drop table needs to delete; Only the data in the table is emptied and the table is not deleted: TRUNCATE table name;changes to the table:        Modify Table name:ALTER TABLE name rename to new table before modificationTo Modify a column name:ALTER TABLE name REANME column name to new column nameTo Add a column:ALTER TABLE name Add (the type of column list that needs to be added);To delete a column name:ALTER TABLE name drop column name to delete;to modify the type of a column:ALTER TABLE name modify (the type of column modified by the column name that needs to be modified);
 changes to the data:
　　 

1. Add Data
Insert into table name (column name) values (column name corresponding value);
　　　　　　2. Delete data
Delete from teacher where name= ' hahaha ';
Delete from teacher where id=8;
　　　　　　3. Modify the data
Update teacher set age=29 where id=7;
　　　　　　4. Filtering query data through the WHERE keyword
SELECT * FROM teacher where id = 1;

 View:
1. Create
　　　　　　　CREATE view name as (SELECT * from which table) (the table can be created as a view of the query)
2. Create alternate rename settings permission Read Only　　　　　 
Create or Replace view name as (select St.id from STU2 St) with Read only
3. Delete
　　　　　　　Drop View Name
4. Add Data
　　　　　　　Insert into view name (column name) values (column name corresponding value);　
 5. Modify the data 　　　　　
Update view name s set s.age=99,s.salary=100,s.name= ' Zhang San ' where s.id=7
6. Enquiry　　　　　
SELECT * from view name
　　　　　　　　
    4 . pl/        SQL: PL/SQL is one of the Oracle's connectivity tools users: All Oracle User (in SYSDBA user login mode, to complete the user name, login password, permission configuration) tab            Les: Contains a simple query statement for all tables of the current user: SELECT * from table name; Query out all the data in the table comments in Oracle:--
　　　　　
Data

 1. Inserting a single piece of data through an SQL statement
--  insert into table name (column name) values (column name corresponding value); 　　

-- when using the INSERT INTO statement, if it is a string type of data, you must add the single quotation mark '----> Chinese single quotation mark '----> Single quotation mark '--when using the INSERT INTO statement, if a non-empty field is set,　　Be sure to insert the value, otherwise you will get an error. --and insert INTO teacher (column name) VALUES (the column corresponds to the value);---> to each insert into teacher (id,name,age,description) VALUES (5, ' Tianqi　　', 36, ' I am Tianqi ');　　INSERT into teacher (ID, name, age) VALUES (6, ' Zhao VIII ', 34);　　--insert into teacher (id,name,description) VALUES (7, ' Wangqiwang ', ' I am Wangqiwang '); Insert into teacher (name, ID, age, description) VALUES (' hahaha ', 7, 30, ' I am a hahaha ');

2. Delete data via SQL--In the process of deletion, try to use a unique key to delete, or use the primary key to delete the delete from teacher where name= ' hahaha '; Delete from teacher where id=8;3. Modifying a single piece of data through an SQL statement---modify/update---Modify in Oracle for column type modification, which is the modification of the table--The UPDATE keyword applies to modifications to the data in Oracle--When you use the UPDATE keyword to modify data,  Be sure to modify with a unique key or primary key--when modifying data, if you modify multiple data, separated by commas, string with single quotation mark update teacher set id=7, name= ' I am hahaha ', age=35, description= ' I am the test data '　　where id=10;　　Update teacher set age=29 where id=7; --where to speak in, =, <,, between and,! = (<>), >=, <=, or, is null, was not NULL, not in, like "_l%", like " %_a% "Escape ' a '4. Filter queries by the WHERE keyword =Key SymbolsSELECT * FROM teacher where id = 1;　greater than and less thanSELECT * from teacher where ID > 1; SELECT * FROM teacher where ID < 9;greater than equals and less than equalsSELECT * from teacher where ID >= 5; SELECT * from teacher where ID <= 1;implementing interval (range) queriesSELECT * from teacher where ID >= 2 and ID <= 10;between and is the limit data containing both sides (closed interval)SELECT * from teacher where ID between 2 and 10;!=(not equal to)SELECT * FROM teacher where id! = 5; SELECT * from teacher where ID <> 5;or keyword (or)SELECT * from teacher where ID =1 or id = 2;and keyword (and)SELECT * FROM teacher where id = 6 and name = ' Zhao Ba ';in keyword (main function: bulk operation)SELECT * from teacher where ID in (1, 5, 10);
   is null filters out all NULL data select * from teacher where description is null; is not null　　filter out all the non-null data SELECT * from teacher where description are not null;  not in (filter out all data not within range) Select ID from teacher where ID > 5 only the specified ID is queried for select * from teacher where ID not in (select I　　D from teacher where ID > 5);　　SELECT * FROM teacher where ID not in (1,5,7);　　SELECT * from teacher where name is not in (' Zhangsan ', ' Lisi ', ' Wangwu '); fuzzy query --like generally to and% together with,% in fact a placeholder, if the% is written in front, matching to Zhao end all the names, and vice-versa to match all the names beginning with Zhao if you need to query the fields before and after adding%,　　As long as the query field is included, it's all found . Select * from teacher where name is like '%a% ';   　

 when _a% a fuzzy query, it matches the string with the second bit of ' a '  --_ that represents any one character select * from teacher where name is like ' _a% '; --Query all data starting with _--need to remove the---match rule: When using escape, if _ is written in front of the required query field, Oracle will automatically transfer _ to any one character--only the underscore is written after the query field is required to use ESCA　　　　The PE keyword removes extra fields, leaving only the underscore. if '%_x% ' , when you use the Escape keyword, it is removed along with the underscore.　　　　SELECT * from teacher where name like '%x_d% ' escape ' x '; 　　　　

 * Query --in SQL, you can use simple operators (+,-, *,/)--Query all data for a table select * from teacher;　　　　--Query the specified data of a table select name, age from teacher;　　　　--Query the specified data (calculation) of a table select name, age from teacher where id=1;　　　　-How big is three after 10 years?　　　　Select name, age+10 from teacher where id=1; 　　　

 alias the Age field-aliases the field: Aliases are used to better describe the field, meaning that an alias is not arbitrary, and that it should be applied to the field's meaning select name, age+10 as, and from TEAC　　　　her where id=1;　　　　-Typically, the AS keyword here can omit the select name, Age+10 age from teacher where id=1;　　　　　　　　--You can also give the table an alias (nickname) Select T.name from teacher T; 
 changed column name with a space in it
　　　　　　　　

　　　　If there is a space in the middle of the alias, it will be an error (cannot find the FROM keyword)
　　　　
Just add a double quotation mark to the alias (must be double quotes)

select name, age+10 ' Zhangsan age ' from teacher where id=1; 
 　　　　 changed column name, data content
--Name: Zhangsan Age:33<---\--Name: also need to enclose quotation marks (be sure to use single quotation marks) SELECT ' Insert field in data ' | | Name ' self-starting column name ' from teacher;find out all the teacher information and arrange it by age from small to largeOrder by--The order by default is ascending--->asc ascending, if you arrange in ascending order typically ASC omits the SELECT * from Teacher order by age ASC;Check out all the teacher information and sort---->desc descending from the oldest to the smallestSELECT * FROM teacher order BY age Desc; --If two teachers are of the same age, the initials are sorted by the first letter of the name select * FROM teacher order BY age DESC, name;Go weight (keyword: distinct)Select distinct age from teacher; 　　　  Select distinct T.age, t.name from teacher T; Select distinct name from teacher; multi-table queryDual,Calculate 1+1 equals a few? Select the from dual;--dual is actually a table, this table is virtual, no columns and data, when the query will be assigned to columns and data, for user-friendly test use-Query the current Oracle login users select User from Du　　　　Al --sysdate,sysdate-,months_netween (,), add_months (,), Next_day (, ' Day of the Week '), Last_day,to_day5. Time function 　　　　 get current time: SysdateSelect Sysdate from dual;　sysdate-time, the number of days that the specified date is from the current dateSelect T.hire_date, T.name, Sysdate, sysdate-hire_date from teacher T;add_months: Add and subtract calculations for a specified date monthSelect Add_months (sysdate,3) from dual; Select Add_months (sysdate,-3) from dual;Months_between: Specifies the month of the date from the current dateSelect T.hire_date, Sysdate, Months_between (Sysdate, hire_date) from teacher T;Next_day: The date of the specified date and the specified day of the week is queried: can not forwardSelect Next_day (sysdate, ' Monday ') from dual;Last_day: Check out the last day of the monthSelect Last_day (sysdate) from dual; Select T.hire_date, Last_day (hire_date) from teacher T;

 to_date: Time conversion function --The default conversion to month day, not with time division seconds--but the Oracle query current date will take time division--When using the To_date function for conversion, the queried data still follow the Oracle's date specification ---To_date: can only convert the date and minute of the month to the day of the month or month, or the day of the year is converted to a minute or so, cannot convert the Oracle format specification-none of the functions in Oracle can modify the Oracle Date format specification Select To_date (' 1987　　　　/08/21 19:18:02 ', ' yyyy-mm-dd hh24:MI:ss ') from dual; --lower (), Upper (), Initcat (), Length (), replace (replace the column name, ' z ', ' z ') (replace small z with large z), substr (the column name to intercept, the position to start the intercept, and the last intercept position), concat (, ), Ceil,float,round,trunc　　　　

To_char: Convert dates to Strings　　  　
Extract Year　　　　　　
Select To_char (sysdate, ' yyyy ') from dual
Extract Month　　　　　　
Select To_char (sysdate, ' mm ') from dual 　　　 
Extract Day　　　　　　

Select To_char (sysdate, ' DD ') from dual

　　　　　　Extraction Hour (HH is 12, Hh24 is 24 binary)

Select To_char (sysdate, ' hh ') from dual

　　　　　　　　　　Extract the exact time
Select To_char (sysdate, ' Yyyy-mm-dd hh24:mi:ss ') from dual

　　　　　To_number: Converting a string to a numeric type (must be a string of numeric type)

Select To_number (' 123456 ') from dual

COUNT (*) or count (any number): Calculate the amount of data, in fact, the data is converted into parentheses in the number, and then statistics, the query is more than a count (? Column

Be sure to use COUNT (1) In actual development do not use COUNT (*)!

　　　　　　

functions in 6.oracle 　　　　 --Lower--Convert all teacher names (English) to lowercase select lower (name) from teacher;--Upper--Convert all teacher names (English) to uppercase select upper (name) from teacher;--Initcap: First letter capitalizedSelect Initcap (' Oracle ') from dual; Select Initcap (name) from teacher;--Length : Gets the lengths of the stringsSelect Length (name) from teacher where id = 1;　--Replace: There are three parameters--First parameter: Column name (field), second argument: a character in the field that needs to be replaced; the third parameter: the character to replace the select * from teacher where id = 1; Select replace (name, ' Z ', ' Q ') from teacher where id = 1;--substr: Similar to Java's substring--SUBSTR has two parameters: first parameter: Column name, second argument: from which one to start interception (including the last number intercepted): Name, to intercept the first two bits: SUBTR (' name ', 2);---> Get ame Select substr (name,　　　　3) from teacher where id = 1; --If the second argument is a negative number, it is backwards from the last one to intercept select substr (' Zhangsan ',-3) from dual;--substr:SELECT * from teacher; --SUBTR: There are three parameters: the first parameter: The column name, the second argument: from which one to intercept (closed interval), the third parameter: How many bits are intercepted (will be intercepted from the intercept position of the second parameter)-in Oracle, the intercepted position will start at 0 or 1 (　　　　The position of 0 and 1 is the same) Select substr (' Zhangsan ', 0,5) from teacher where id = 1; Select substr (' Zhangsan ', 1,5) from teacher where id = 1;--ceil: Rounding upSelect Ceil (' 12345.1 ') from dual;--Floor : Rounding downSelect Floor (' 12345.1 ') from dual;--Round: RoundingSelect round (' 12345.1 ') from dual;　　　　Select round (' 12345.7 ') from dual; Select round (' 12845.6 ',-3) from dual;--trunc: Truncation: integer is taken--The second parameter: Reserved decimal places-if there are no decimals, and the second argument is a positive number: it is displayed as is, not added. XX Select Trunc (' 123444.87978787 ', 2) from dual; --if there are no decimals, and the second argument is negative: the integer digits are written from forward to 0 select trunc (' 123456 ',-3) from dual;--concat: stitching Strings---> instead of | |Select Concat (' Zhang ', ' San ') from dual; Select Concat (' name is: ', name ') from teacher;

Java:oracle (Table of additions and deletions, data and other changes)