Oracle Summary ____oracle

Create a table space

Create tablespace l_l
datafile ' D:luo. DBF '
size 5m
autoextend on next 4m
MaxSize Unlimited;

Create user default table space

Create user Luo identified by 123456 default Tablespace l_l;

Modify user Default space

Alter user Luo default tablespace ts_hp;

Modify Password

Alter user Luo identified by 123;

Delete User

Drop user Luo [cascade];

The right to confer a conversation

(Create Session,create table,create index,create view,create sequence,create trriger) various permissions

Grant create session to Luo;

Assigning roles (Dba,connect,resource)

Grant resource to Luo;

Reclaim Permissions

Revoke create session from Luo;

Reclaim Permissions

Revoke resource from Luo;

Toggle User Name

Conn system/123456;

Create a table

CREATE table users (ID number);

Add Data

Insert into users values (1);

Query

Select*from users;

Delete Table

drop table users;

Create a table

CREATE TABLE students (
        ID number,
        name VARCHAR2 (),
        sex char (2),
        birthday date,
        score number ( 3,1),
        resume Clob);

Add

ALTER TABLE students Add (class_id number);

Modify

ALTER TABLE students modify (name varchar (30));

Remove

ALTER TABLE student drop column score;
ALTER TABLE students drop (score);
ALTER TABLE students drop (SCORE,ID);

Modify Table name

Rename students to Stu;

Modifying column names

ALTER TABLE Stu Rename column resume to intro;

View Table Structure

Desc Stu;

Add Data

Insert into Stu values ();

constraint

CREATE table class (ID number primary key);
CREATE TABLE STU1 (ID number references class (ID),
                    name varchar2 NOT NULL, age number
                    unique,
                    add varchar 2 Check (add in (' Luoyang ', ' Jiaozuo '));

Increase NOT NULL

ALTER TABLE STU modify name not NULL;

Add or modify

ALTER TABLE STU Add constraint Pk_stu primary key (ID);

Delete Constraint

ALTER TABLE STU DROP CONSTRAINT constraint name
ALTER TABLE STU DROP constraint primary key cascade;

Constraint Naming conventions

Nn_ Table Name _ Column name: Non-empty
Un_ Table Name _ Column name: Unique
Fk_ Table Name _ Column name: Foreign key
Ck_ Table Name _ Column name: Condition
Pk_ table name: PRIMARY KEY display constraint information

Select constraint_name,constraint_type,status,validated from
user_constraints
WHERE table_name = ' class ';

Update

Syntax: Update table name set column name = expression [, column name = expression, ...] [Where condition];

  Update stu Set s_name = "QQ" where s_id = 1002;
  -Note: There is no where condition. All row
  Update stu Set f_ship = ten where f_ship is null will be modified;

Delete

Syntax: delete from table name [where condition expression];
Delete from Stu where s_id = 1002;– Deletes a row
– Note: There is no where condition to delete all records;
Delete from table name, delete data, and structure
Delete table name; Delete structure and data
trancate table name; Settings Save point cannot be retrieved

SavePoint Save Point Name
Rolling back
Rollback to save point name
With delete used, retrieve delete deleted data, and Trancate cannot retrieve the query

Select*from stu;– Query All
Select s_name,s_id from stu;– query specific columns

Select distinct S_name from Stu; – Delete duplicate data
Select s_name,s_id from stu where s_n = "QQ"; – Query for specific criteria columns using arithmetic expressions

Null to calculate all null, with NVL (field, 0), NVL function to handle null problem
With as if the quote must be added "" can only use as without quotes, as can not give the table alias How to connect strings (| |)

In the query when you want to connect multiple columns into a column to return to use | |

To_date
To_char
and
Between and how to use the LIKE operator

%: Any 0 or more characters
_: Represents any single character order by sort words

ASC: Ascending, default.
Desc: Descending can use the alias of a column to sort grouped queries

The function you use Max, min, avg, SUM, count. GROUP BY and having

GROUP BY: Used to group the results of a query
Having: used to limit the results of a query