ORACLE---Unit01: database rationale, SQL (DDL, DML)

--sql statements are case-insensitive, but for readability, you can capitalize all the keywords, all lowercase for non-critical children.
SELECT sysdate from dual;

--sql statements are categorized according to function:
--DDL statement (data definition language)
--Used to manipulate database objects. Database objects include:
--tables, views, indexes, sequences

--->1. Create a table:
CREATE TABLE EMPLOYEE_RR (
ID Number (4),
Name VARCHAR2 (20),
Gender CHAR (1),
Birth DATE,
Salary number (6,2),
Job VARCHAR2 (30),
Deptno Number (2)
);

--->2. View the structure of a table: (two places will be used)
DESC EMPLOYEE_RR

--->3. Delete table:
DROP TABLE EMPLOYEE_RR;

---default
---Set the defaults
---in the database, regardless of what type the field is, the default value is NULL, but you can set the default value for the specified column individually by using the Defaults keyword when creating the table.
---in the database, strings use single quotes to denote literal literals, which is inconsistent with Java and needs attention!
---Note: For different data types, numbers do not need single quotes to write numbers directly, and the characters ' arguments write single quotes.
--      and Java do not, in the database, whether a single character or a multi-character argument, are single quotes. The
---can specify a default value (NULL) for a column by using the default clause
---Assign a default value of ' M ' to the gender column, and if there are no employees of the specified gender, the default is male.
---Delete the employee table first, and then create the table employee
---Note: The SQL statement is case-insensitive, but the string content is casing-sensitive.
CREATE TABLE employee_rr (
       ID number (4),
        name VARCHAR2,
       gender CHAR (1) DEFAULT ' M ',
        birth DATE,
       salary number (6,2),
        job VARCHAR2,
       deptno number (2)
); 
DROP TABLE employee_rr;

---NOT NULL constraint
---non-null constraint requires that the specified field in any case value is not allowed to be empty.
---Non-empty (not NULL) is a constraint that ensures that the field value is not empty
---by default, any column is allowed to have a null value (Note: Gender CHAR (1) Not Null default ' M ', for error, not allowed)
--- (Note: NOT NULL is visible.)
---When a field is set to a non-empty constraint, a valid value must exist in this field
---when you perform an operation that inserts data, you must provide data for the column
---you cannot set the value of this column to NULL when you perform an update operation
--->not A NULL constraint
--->not a null constraint ensures that the specified field does not allow null.
CREATE TABLE EMPLOYEE_RR (
       ID number (4),
        Name VARCHAR2 () not NULL,
       gender CHAR (1) DEFAULT ' M ',
        birth DATE,
       salary number (6,2),
        Job VARCHAR2 (+),
       deptno number (2)
);
DESC EMPLOYEE_RR

---two. Modify the table
---Modify a table to modify the table name and table structure
---2.1 Modify table name:
---RENAME old_name to New_name
---If you want to modify the table name after you build the table, you can use the Rename statement to implement
---syntax as follows, change the table name Old_name to new_name:
---RENAME old_name to new_name;
---Modify table name employee is Myemp
---RENAME employee to myemp;

RENAME EMPLOYEE_RR to MYEMPLOYEE_RR

DESC EMPLOYEE_RR
DESC MYEMPLOYEE_RR

---2.2: Modify table structure
---Add a new field, delete an existing field, modify an existing field
---is to change the table in the field, adding and removing changes. Add a new field, or several fields.
---add columns
---Add columns to the table can be implemented using ALTER TABLE's ADD clause.
---syntax:
---ALTER TABLE table_name ADD ();
---Columns can only be added at the back and cannot be inserted into an existing column
---Adds a column of hiredate to the table and sets the default value to the current date
---2.2.1: Add a new field
---Add fields to the Myemp table HireDate

ALTER TABLE myemployee_rr ADD (hiredate DATE DEFAULT sysdate);
DESC MYEMPLOYEE_RR

---2.2.2: Delete a field
---to remove hiredate from the Myemp table

ALTER TABLE myemployee_rr DROP (hiredate);
DESC MYEMPLOYEE_RR

---2.2.3: Modify an existing field
---Modify a field to modify the type of the field, add a default value or constrain the length.
---but modify the field as far as possible without data in the table, otherwise try not to modify the field type,
---the length of the change as much as possible, or the modification may fail.
---> Can modify the field type, length, default value, non-empty

--Modify Column MODIFY
--After the table is built, you can change the data type, length, and default values of the columns in the table
--Modification is only valid for data inserted later
-If the length is changed from big to small, it may not be successful
--Syntax:
--alter TABLE table_name MODIFY
--Modify the column job for table myemp and increase the default value setting
--alter TABLE Myemp
--modify (Job VARCHAR2 (+) DEFAULT ' clerk ');
Note: Try not to modify the table structure, modify the data is also empty table, otherwise it is insufficient analysis of the previous work.
1, the field type do not change, because the table already has data. If the string was previously changed to date, it will fail
2, the length as large as possible expansion, do not small shrinkage.
--alter TABLE myemployee_rr MODIFY (Job number (10,2) not NULL);

ALTER TABLE myemployee_rr MODIFY (Job VARCHAR2 (40))
DESC MYEMPLOYEE_RR

-----DML statements (data manipulation language)
---DML for adding, deleting, and modifying data in a table
---1.INSERT: inserting data
--insert into MYEMPLOYEE_RR (Id,name,job,deptno) VALUES (1, ' ROSE ', 11,10);
INSERT into MYEMPLOYEE_RR (Id,name,job,deptno) VALUES (1, ' ROSE ', ' clerk ', 10);
COMMIT
SELECT * from MYEMPLOYEE_RR

--commit--〉 has submitted execution button to the right of the fifth iron Drum hook is also a COMMIT (F11)//again to the right is rollback rollback
--rollback--〉 the fallback is complete.

The field names in the--insert statement can be ignored, but all columns are inserted after being ignored.
INSERT into Myemployee_rr VALUES (2, ' Rose ', ' M ', sysdate,5000, ' Cleak ', 20)
SELECT * from MYEMPLOYEE_RR

---inserting date type data
The---can be given a string directly, but the format must be: ' DD-MON-RR ', because the month is used for simple spelling,
---different language regions here:
---English regions are abbreviated in English, such as ' 01-sep-03 '
---and Chinese environment: ' January-September-03 '. Therefore, it is not recommended.
---can use the to_date function, which is a built-in function of the database, you can put a string
The---is converted to a date value in the format specified.

INSERT into MYEMPLOYEE_RR (Id,name,job,deptno,birth) VALUES
(3, ' Mark ', ' Clerk ', To_date (' 1990-09-13 ', ' yyyy-mm-dd '))

SELECT * from MYEMPLOYEE_RR

---UPDATE statement
---update records in a table
The---syntax is as follows:
---UPDATE table_name
---SET column= value ()
---if there is no WHERE clause, all data in the entire table will be updated and modified, be careful

---2.UPDATE, update the data, change Rose's gender to ' F ', update staff Rose's salary at 8600
The WHERE clause is usually used to add a filter condition when---modified, so that only records that satisfy the condition will be
---to modify, if you do not add the filter condition is the full table update, this situation is actually relatively small.
UPDATE myemployee_rr SET gender= ' F ', salary=8600 WHERE name= ' Rose '
SELECT * from MYEMPLOYEE_RR

---3.DELECT, DELETE statement
---delete Rose
DELETE from Myemployee_rr WHERE gender= ' M '
DELETE from MYEMPLOYEE_RR WHERE name= ' Rose '
SELECT * from MYEMPLOYEE_RR

---DELETE statement
---TRUNCATE statements in DDL statements also have the effect of deleting table data.
Differences between---and DELETE statements:
--delete can be conditionally deleted, truncate all table data is deleted
--delete is a DML statement that can be rolled back, truncate is a DDL statement that immediately takes effect and cannot be rolled back
Delete statements are less efficient than TRUNCATE statements if all table records are deleted and the amount of data is large.
--Delete all records
--delete from Myemp;
-OR
--truncate TABLE Myemp

*************************************************************************************************************** ***********************************************************

CREATE TABLE EMP_RR (
Empno number (4,0),
Ename VARCHAR2 (10),
Job VARCHAR2 (9),
Mgr Number (9),
HireDate DATE,
Sal Number (7,2),
Comm Number (7,2),
Deptno Number (2,0)
);

DESC EMP_RR

CREATE TABLE DEPT_RR (
Deptno number (2,0),
Dname VARCHAR2 (BYTE),
Loc VARCHAR2 (BYTE)
);

DESC DEPT_RR
DROP TABLE DEPT_RR;

INSERT into EMP_RR (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
VALUES (7369, ' SMITH ', ' Clerk ', 7902,to_date (' 1980-12-17 ', ' yyyy-mm-dd '), 800,null,20);
INSERT into Emp_rr VALUES (7499, ' ALLEN ', ' salesman ', 7698,to_date (' 1981-2-22 ', ' yyyy-mm-dd '), 1600,300,30);
INSERT into Emp_rr VALUES (7521, ' WARD ', ' salesman ', 7698,to_date (' 1981-2-22 ', ' yyyy-mm-dd '), 1250,500,30);
INSERT into Emp_rr VALUES (7566, ' JONES ', ' MANAGER ', 7839,to_date (' 1981-4-2 ', ' yyyy-mm-dd '), 2975,null,20);
INSERT into Emp_rr VALUES (7654, ' MARTIN ', ' salesman ', 7698,to_date (' 1981-9-28 ', ' yyyy-mm-dd '), 1250,1400,30);
INSERT into Emp_rr VALUES (7698, ' Blak ', ' MANAGER ', 7839,to_date (' 1981-5-1 ', ' yyyy-mm-dd '), 2850,null,30);
INSERT into Emp_rr VALUES (7782, ' CLARK ', ' MANAGER ', 7839,to_date (' 1981-6-9 ', ' yyyy-mm-dd '), 2450,null,10);
INSERT into Emp_rr VALUES (7788, ' SCOTT ', ' ANALYST ', 7566,to_date (' 1987-4-19 ', ' yyyy-mm-dd '), 3000,null,50);
INSERT into Emp_rr VALUES (7839, ' KING ', ' president ', null,to_date (' 1981-11-17 ', ' yyyy-mm-dd '), 5000,null,10);
INSERT into Emp_rr VALUES (7844, ' TURNER ', ' salesman ', 7698,to_date (' 1981-9-8 ', ' yyyy-mm-dd '), 1500,0,30);
INSERT into Emp_rr VALUES (7876, ' ADAMS ', ' Clerk ', 7788,to_date (' 1987-5-23 ', ' yyyy-mm-dd '), 1100,null,20);
INSERT into Emp_rr VALUES (7900, ' JAMES ', ' Clerk ', 7698,to_date (' 1981-12-3 ', ' yyyy-mm-dd '), 950,null,30);
INSERT into Emp_rr VALUES (7902, ' FORD ', ' ANALYST ', 7566,to_date (' 1981-12-3 ', ' yyyy-mm-dd '), 3000,null,20);
INSERT into Emp_rr VALUES (7934, ' MILLER ', ' Clerk ', 7782,to_date (' 1982-1-23 ', ' yyyy-mm-dd '), 1300,null,10);

SELECT * from EMP_RR;

INSERT into DEPT_RR (Deptno,dname,loc) VALUES (, ' ACCOUNTING ', ' NEW YORK ');
INSERT into Dept_rr VALUES ("DALLAS");
INSERT into Dept_rr VALUES (' SALES ', ' CHICAGO ');
INSERT into DEPT_RR VALUES (+, ' OPERATIONS ', ' BOSTON ');

SELECT * from DEPT_RR;

*************************************************************************************************************** ***********************************************************

ORACLE---Unit01: database rationale, SQL (DDL, DML)