Oracle (III)

/**************************** table Space start****************************/--the role of table space/**1. Determining the spatial allocation of database Entities 2. Set the space share of the database User 3. Control the availability of some data in our database 4. Backing up and recovering data **/--How to create a file/*create tablespace tablespace name for the database that corresponds to the tablespace datafile Path name Size (default) autoextend on next size (increment size) maxsize size (maximum) *//**** create a tablespace jame_tablespace***/create tablespace The name of the jame--database is DataFile ' E:\oracle\oradata\orcl\jame01. DBF '--path name a separate path for the project's database size 20m;/*** Create a user and specify Tablespace Array ***/create user u_arrayidentified by U_arraydefault Tablespace jame;/** the Connect resource role to the U_array**/grant Connect,resource to u_array;/* Note: At work, when designing the database, The table must be placed with the user in a tablespace *//*** view the name and size of the tablespace ***//**dba_tablespaces the information view of the tablespace dba_data_files the file information relational database **/select T1 for the table space. Tablespace_name,t2. Bytesfrom dba_tablespaces T1, dba_data_files t2where t1. Tablespace_name = t2.tablespace_name;/*** View the name and size of the physical file ***/select * from dba_data_files/*** view tablespace usage ***/select * FROM dba_free_space;/** Adjust table space **/alter tablespace jameadd datafile ' E:\oracle\oradata\orcl\jame02. DBF ' size 20mautoextend on next 2mmaxsize 50m;--Modify the size of the data file ALTER DATABASE DataFile ' E:\ORACLE\ORADATA\ORCL\JAME02. DBF ' Resize 50m;--delete data file alter tablespace jamedrop datafile ' E:\oracle\oradata\orcl\jame02. DBF ';/**************************** table space end****************************//**************************** constraint start***** /--automates deployment automation on-line Automation Upgrade/*** constraint Type 1. PRIMARY KEY constraint (Primary) 2. Unique constraint (unique) 3. Check Constraint (check) 4. Non-null constraint (NOT NULL)-- belongs to the CHECK constraint 5. FOREIGN KEY constraint (foreign key) ***/create table T_student_con (f_id int,f_name varchar2 (+), F_sex char (2), F_age Int,f_birth Date default sysdate);--Add a field ALTER TABLE T_student_conadd f_email VARCHAR2 (30);--Adding a PRIMARY KEY constraint cannot be null automatically adds a UNIQUE constraint ALTER TABLE T_student_conadd constraint pk_std_id primary key (f_id); insert into T_student_con values (1, ' Jaydy ', ' M ', 23,to_date (' 1992-06-01 ', ' yyyy-mm-dd '), ' [email protected] ') insert into T_student_con values (1, ' Jaydy ', ' M ', 23,to_date (' 1992-06-01 ', ' yyyy-mm-dd '), ' [email protected] ')--add a UNIQUE constraint ALTER TABLE T_STUDENT_CON add constraint Uq_std_name Unique (f_name); INSERT INTO T_student_cOn values (2, ' Jaydy ', ' M ', 23,to_date (' 1992-06-01 ', ' yyyy-mm-dd '), ' [email protected] ')--Add a check constraint to customize the integrity of the data, Ensure that the data for the field does not exist beyond the constraints of the data ALTER TABLE T_STUDENT_CON add constraint ck_std_sex check (f_sex= ' M ' or f_sex= ' F ') insert into T_ Student_con values (2, ' Jaydy ', ' S ', 23,to_date (' 1992-06-01 ', ' yyyy-mm-dd '), ' [email protected] ')--add non-null constraint/* It's not very common. ALTER TABLE T_STUDENT_CONADD constraint nnull_std_email Check (F_email is not null), */--determines whether a field is null if it is null or not nul L is not nullalter table T_student_con Modify (F_email varchar2); insert into T_student_con values (3, ' Think path ', ' M ', 23,to_ Date (' 1992-06-01 ', ' yyyy-mm-dd '), NULL)---FOREIGN KEY constraint--Test information table 100.00 95.5 95.55create table T_student_exam (e_id int,--primary KEY E_ Subject VARCHAR2 (+),--account E_score number (5,2),--score f_id int--The foreign key for student information);--Add primary key ALTER TABLE T_STUDENT_EXAM ADD constraint pk _EXAM_ID primary KEY (E_ID);--Add foreign key ALTER TABLE T_STUDENT_EXAM ADD constraint fk_exam_student_id foreign key (f_id) References T_student_con (f_id) on DELETE cascade--cascade deletes the score information from the table when the student information is deleted; INSERT INTO T_Student_exam values (2, ' javase ', 100,1); select * from t_student_exam--delete t_student_con where f_id=1--delete constraint ALTER TABLE T_student_exam drop constraint fk_exam_student_id;/** in the table when you synchronize with **/drop table T_student_con1;create table T_student_con1 (f_id int Primary key,f_name VARCHAR2 (+), F_sex char (2) Check (f_sex= ' M ' OR f_sex= ' f '), F_age int,f_birth date default Sysda Te,constraint ck_std_age Check (f_age>=1 and f_age<=60));D ROP Table T_student_exam1;create table T_STUDENT_EXAM1 ( e_id int,--primary KEY e_subject varchar2 ($),--account E_score number (5,2),--score f_id int REFERENCES T_student_con (f_id) on DELETE cascade--foreign key for student information);/**************************** constraint End****************************/alter user Scott account unlock;/ * Simple query Start*/--1. Query Employee information named Smith-Note: content is case-sensitive in Oracle. Keywords, table names, field names are case-insensitive select * from emp where ename= ' SMITH ';--2. Query all employee information for 20 departments select * FROM EMP where deptno=20--3. Query the employee number, employee name, and department number for all employees who have job clerk. Select Empno,ename,deptno from emp where job= ' clerk ';--4. Inquire about employee information for bonuses (COMM) higher than salary (SAL). SELECT * FROM EMP WHEre comm>sal--5. Query the employee information for bonuses above 20% of the salary. SELECT * from emp where comm>sal*0.2--6. Find information about the job in department 10th for manager and 20 jobs in the Department clerk. --Combination Conditions SELECT * from emp where (job= ' manager ' and deptno=10) or (job= ' clerk ' and deptno=20)--7. Query all jobs are not MANAGER and clerk and Employee details with a salary greater than or equal to 2000. SELECT * from EMP where job!= ' manager ' and job!= ' clerk ' and Sal>=2000select * from EMP where JOB is not in (' manager ', ' CLE RK ') and sal>=2000--8. Query all job types are manager and clerk, and employees with a salary greater than or equal to 2000 are more information. SELECT * from EMP WHERE JOB in (' MANAGER ', ' Clerk ') and sal>=2000--10. Query employee whose name contains the letter "S". SELECT * from EMP WHERE ename like '%s% ';--11. Query employees whose names do not contain the letter "S". SELECT * from emp where ename '%s% ';--12. Query employee information for the second letter of the employee's name "M". --% represents any number of any character--_ represents any single character select * from emp where ename like ' _m% '--13. Receives the employee's name entered by the user and queries the employee information by name. SELECT * from emp where ename = ' & Employee name ' and sal= ' & Payroll ';--all job types in the statistics select distinct job from emp--14. Query the employee's name and salary, and press Pay from high to low sort order By field name [Desc|asc]select ename,sal from emp order by Sal Desc--15. Check the employee's name and entry date, and press the on-Job dayThe binate is sorted after the period. Select Ename,hiredate from emp order by HireDate asc--16. Displays the names, jobs, wages, and bonuses of all employees in descending order of job, sorted by salary in ascending order if the job is the same. Select Ename,job,sal,comm from emp order by job Desc,sal asc/* uses the EMP and dept tables under the Scott User 1. Query employee information with the name Smith (note case) 2. Query all employee information for 20 Departments 3. Query the employee number, employee name, and department number for all employees who are clerk. 4. Inquire about employee information for bonus (COMM) higher than salary (SAL). 5. Check the employee information for bonuses above 20% of the salary. 6. Check the information in department 10th of the job in the manager and 20 departments for job clerk employees. 7. Check the employee details for all jobs that are not manager and clerk with a salary greater than or equal to 2000. 8. Check that all types of jobs are manager and clerk, and employees with a salary greater than or equal to 2000 are more information. 9. Check the different job types of the employees who have the bonus. 10. Check employees whose names contain the letter "S". 11. Query employees whose names do not contain the letter "s". 12. Query employee information for the second letter of the employee's name "M". 13. Receive the employee's name entered by the user and query the employee information by name. 14. Query the employee's name and salary, and sort by the salary from high to low 15. Check the employee's name and entry date, and sort by the entry date binate. 16. Show all employees ' names, jobs, wages, and bonuses sorted in descending order of job, sorted in ascending order if job is the same.       *//* Simple Query end*/

　　

Oracle (III)