Oracle Database Learning Note 3

SELECT * from Stuent;
ALTER TABLE student Add Stu_card char (18)

--PRIMARY KEY constraint: Non-null, unique PRIMARY key
ALTER TABLE student ADD constraint
Pk_stuid primary KEY (STU_ID)

--Check the constrained check ck_xxx
ALTER TABLE student ADD constraint
Ck_stusex check (stu_sex= ' male ' or stu_sex= ' female ')

--Unique constraint: Do not repeat (only one is allowed to be empty) unique
ALTER TABLE student ADD constraint
Up_stucard Unique (Stu_card)

-Default constraint Defaults
ALTER TABLE student Modify
Stu_birth default Sysdate

--Non-null constraint not all
ALTER TABLE student Modify (stu_name NOT NULL)

-Primary FOREIGN KEY constraint

ALTER TABLE Stu_score add Constanint fk_stuid
Foreign KEY (stu_id) references student (stu_id);

CREATE TABLE Stu_score (
STU_ID number,
Stu_score Number (5,1)
);

--Delete the table (delete the foreign key table first, then delete the primary key table)
drop table student;

1. Each table must have a primary key and be an atomic column (each field cannot be divided)
2. Non-primary key columns fully dependent and primary key columns
3. Non-primary key columns cannot be dependent on and non-primary key columns

DML statement:
--Add data INSERT INTO table_name
INSERT INTO student
(Stu_id,stu_name,stu_birth,stu_card)
Values
(1, ' Zhang Sanfeng ', ' January-October-1990 ', ' 123 ');

INSERT INTO student
VALUES (2, ' MUI Wind ', default, ' 123456 ', ' Male ');

SELECT * from student;
Commit

--Modify Data Update table_name SET field name = value;
Update student Set stu_sex = ' female ' where stu_id = 2;

--Deleting data delete indicates
Delete from student where stu_id = 1;

--Query Data Select
Select
From
GROUP BY
Having
ORDER BY
SELECT * FROM emp
SELECT * FROM Dept

--Copy table
CREATE TABLE Emp_temp as SELECT * from EMP;
SELECT * FROM Emp_temp

--Query the employee's number, name, work position
Select Empno,ename,job from Emp_temp;
--Query the employee information of 20 Department door
SELECT * from emp_temp where deptno = 20;

Select Empno as employee number, ENAME employee name
From Emp_temp;

--Query employee information for employees with a salary greater than 3000
SELECT * from emp_temp where Sal > 3000;
--Search for employee information that is not part of the 30 portal
SELECT * from Emp_temp where Deptno <> 30;

--Query the employee information of section 20, display in the following format
XXX's salary is xxx
Selsect ename | | ' The salary is; ' | | Sal
From Emp_temp;

--Query monthly income for all employees
Select Empno,ename,sal,comm,
SAL+NVL (comm,0) Monthly income
From Emp_temp;

--Fuzzy query
SELECT * FROM Emp_temp
where ename like '%a% '; % can occur without appearing, can appear multiple

--Query The second character is a, _ is a placeholder, must appear
SELECT * FROM Emp_temp
where ename like ' _a% ';

--Check the salary of the employee between 3000-5000 []
SELECT * FROM Emp_temp
Where Sal between and 5000;

Not in 3000 and 5000.
SELECT * FROM Emp_temp
Where Sal not between and 5000;

--in
Selsect * from Emp_temp
where Deptno not in (20,30);

--Query those employees without bonuses
SELECT * FROM Emp_temp
where comm is null;

SELECT * FROM Emp_temp
Where Comm is not NULL

Oracle Database Learning Note 3