Oracle study Note 3

// Data integrity, business and logic rules can be implemented using three methods: Constraints, triggers, and applications (processes and functions)
// The constraints include not null, unique, primary key, foreign key, and check.
// A table can have multiple unique tables, but only one primary key can exist.

// Item table. The item number is the primary key and the unit price is greater than 0,
Create table goods (goodId, char (8) primary key, goodsName varchar2 (30), unitprice number (10, 2) check (unitprice> 0 ),
Category varchar (8), provider varchar (20 ));
// Customer table,
Create table customer (customerId char (8) primary key, name varchar2 (50) not null -- not empty, address varchar2 (50 ),
Email varchar2 (50) unique -- unique email address,
Sex char (2) default 'male' check (sex in ('male', 'female ') -- default is male, either male or female
CardId char (18 ));
// Purchase table
Create table purchase (customerId char (8) references customer (customerId) -- foreign key points to customerId
GoodId char (8) references goods (goodId) -- foreign key
Nums number (5) check (nums between 1 and 30 ));

// The not null constraint must be added using modify, And the add constraint must be added.
Alter table goods modify goodsName not null; // The goodsName field for updating the goods table is not empty.
Alter table customer add constraint cardUnique unique (cardId) // The ID card cannot be duplicated. cardUnique is the name of the constraint.
Alter table customer add constraint addressConstrian check (address in ('haidian ', 'chaoyang', 'tongzhou '); // increase the customer's address, it must be 'haidian ', 'chaoyang', 'tongzhou ', and addressConstrian is the constraint name.
Alter table Name drop constraint name; // delete a constraint
// Deletion of primary key constraints may result in errors. Therefore, the cascade option is required.

// Display Constraints
Select * from user_constraints where table_name = 'table name ';
Select * from user_cons_columns where constraint_name = 'constraint name'; // display constraint Columns

// Column-level definition, defining both column-level definition Constraints
// Table-level definition. After defining all columns, define constraints again. Note that not null can only be column-level definition.
Create table xxx (empId number (2), name varchar2 (15), dept_id number (2), constraint pk_employee primary key empId,
Constraint fk_department foreign key (dept_id) references department (dept_id); // table-Level Definition

// Index
// Single-Column Index
Create index nameIndex on customer (name); // create an index on the name field of the customer table
// Composite Index
Create index nameSexIndex on customer (sex, name); // create a composite index on name and sex. sex indexes are more effective.

// Principle 1. On a large table 2. Use the where statement to query a large number of Columns 3. The index level cannot exceed four layers, and the multi-level index
// Disadvantage 1, 1.2 times of hard disk and memory space 2. Additional system time for updating data to update the index, maintain data and index consistency, and improper indexes will reduce system performance
Do not create an index: there are few or uncommon fields, logical fields, such as sex and 'male' and female'

// Display all indexes of the table
Dba_indexs displays all index information of the database
User_indexs: displays all index information of the current user.
Select * from
// If the field is unique, the index is automatically added.

// Permission,
// System permissions (for databases), system_privilege_map (create table \ conn \)
// Object permission (select \ alter \ delete \ update \ insert \ references \ execute)
// Role to simplify permission management
Select * from dba_roles;
// Grant system permissions to dba users or grant any privilege permissions to other users.
// Create a ken and a tom user
Create user ken identified by m123;
Create user tom identified by m123;
Grant ken create session, create table to ken with admin option;
Grant create view to ken;
Grant create session, create table to tom with admin option; // use the ken user to grant permissions to the tom user
// Revoke system permissions, which are generally completed by the dba user, or the option with admin option with corresponding system permissions and system transfer Permissions
Revoke create session from ken; // revoke the logon permission of the ken, but tom can still log on, proving that the system-level permission is not revoked
// Grant the object permission. the dba user or has the with grant option permission. This permission can be assigned to the user but cannot be granted to the role.
Grant select on emp to monkey; // The scott user assigns the query permission of the emp table to the monkey user.
Grant update on emp (ename, sal) to monkey; // you can only access the ename and sal columns of the emp table.
Grant alter/index/execute permission
Option with grant option indicates that permissions can be transferred to other users. This option can only be assigned to users, but cannot be assigned to role.
After the cascade assignment of object permissions (with grant option), after the first user's permissions are rovoke, the object permissions of other users are also revoked.

// A role is a set of commands for related permissions (predefine and custom)
Common predefined roles: connect, resource, and dba
Connect role (create session, create cluster, alter session, create database link, create sequence, create table, create view)
Resource role ()
The dba role () has no permission to start or close the database.

// Custom role, generally created by dba, or has the system permission to create a role
Do not verify the create role name not identified;
Verify the password of the create role name identified by. // provide the password when modifying the role
// Authorize a role
Conn system/manager;
Grant create session to role name with admin option;
Conn scott/tiger @ database;
Grant select on emp to role name;
Grant update on emp to role name;
Grant delete on emp to role name;
// Assign a role to the user. The dba user should be used.
Conn system/manager;
Create user xxx identified by password;
Grant role name to user name with admin option;
// Delete a role
Conn system/manager;
Drop role name; // After the role is deleted,
// Display all roles
Select * from dba_roles;
// Display the system permissions of the role
Select privilege, admin_option from role_sys_privs where role = 'Role name'
// Display all object permissions of a role
Dba_tab_privs
// Display all user roles and default roles
Select grant_role, default_role from dba_role_privs where gurantee = 'username'

// Fine-grained access control
Policy, function, when different clients query, oracle will automatically add the where statement after the query

Dba, Database Administrator
// Install and upgrade, create databases, tables, tablespaces, views, and indexes, specify and implement backup and recovery plans, manage permissions, optimize, and troubleshoot, project development, writing SQL statements, stored procedures, triggers, rules, constraints, packages, and database security
Sys Chairman and system General Manager
Sys: The base table and dynamic view of all oracle databases, which cannot be manually modified by any user and has the roles or permissions of dba, sysdba, and sysoper.
You must Log On As sysdba or sysoper. You cannot log on as normal.
System: Sub-user data, with dba and sysdba roles or permissions. You cannot create or delete databases or view user data.
Dba users can only perform operations after starting the database instance
Sysdba> sysoper> dba
Shutdown; // shut down the database
Startup; // start the database
Show parameter; // database initialization parameters, more than 200

Logical backup/
Physical backup/database shutdown status
Export tables, export schemes, and export Databases
Export Scheme: export all the items of the user
Export the entire database: export all user and system solutions
// Perform operations in the bin directory of oracle during import and export.
Exp userid = scott/tiger @ myOra1 tables = (emp) file = d: e1.dmp; // export scott's table, password tiger, myOra1 database table emp to d disk, exp is a program
// Exporting tables of other users requires dba permission or exp_full_database
Exp userid = system/manager @ myOral1 table = (scott. emp, scott. dept) file = d:/e1.dmp;
Exp userid = scott/tiger @ myOra1 tables = (emp) file = d: e1.dmp rows = n // rows = n export only the table structure
Exp userid = scott/tiger @ myOra1 tables = (emp) file = d: e1.dmp direct = y // direct = y for direct export, faster

Exp userid = scott/tiger @ myOra1 owner = scott file = d: e1.dmp // owner = scott Export Scheme
System can export solutions for any user except sys

// Export the database, full = y all export, inctype = complete incremental
Exp userid = system/manager @ myOral1 full = y inctype = complete file = D:/xx. dmp

Import tables, solutions, databases
Imp userid = scott/tiger @ myOral1 tables (emp) file = d:/dd. dmp // import table
Imp userid = scott/tiger @ myOral1 tables (emp) file = d:/dd. dmp rows = n // rows = n only import table structure
// Import database, full = y all import, inctype = complete incremental
Imp userid = system/manager @ myOral1 full = y inctype = complete file = D:/xx. dmp

Data Dictionary: records the system information of the database. The owner is a sys user, a combination of read-only tables and views. Users can only perform the select Operation, and other operations are automatically completed by the system.
It consists of a data dictionary base table and a data dictionary view. You can only query views, including user_xxx, all_xxx, and dba_xxx.
Select table_name form user_tables; // The table owned by the current user
Select table_name from all_tables; // All Tables accessible to the current user
Select table_name from dba_tables; // table of all schemes, which must be a dba user or have select_any_table system Permissions

When creating a user, the user permissions and role information are stored in the data dictionary,
Select * from dba_users; // detailed information of all database users
Dba_sys_privs // system permissions of all users, data dictionary View
Dba_table_privs // object permissions of the user, data dictionary View
Dba_col_privs // column permissions of the user, data dictionary View
Dba_roll_privs // role of all users, data dictionary View

Select * from dab_roll_privs; // query all roles
// How many permissions a role has, including system permissions and object permissions?
Select * from system_privilege_map order by name; // All system Permissions
Select dinstinct privilege from dba_tab_privs; // all object permissions
Select * from dba_sys_privs where grantee = 'connection'; // system permission of the CONNECT role
Select * from dba_tab_privs where grantee = 'connection'; // The object permission of the CONNECT role
Select * from role_sys_privs where role = 'connect '; // the data stored in the role_sys_privs and dba_sys_privs of the CONNECT role has the same point.
Select * from dba_role_privs where gurantee = 'Scott '; // What permissions does the SCOTT user have?

Select * from global_name; // myoral.us.oracle.com displays the full name of the current database.
Select * from dict where comments like '% grant %'; // view of all data dictionaries accessible to the current user

The dynamic performance view starts with v _ $, which is generally owned by sys

Physically, databases are stored in data files. Logically, databases are stored in tablespaces, which are composed of one or more data files.
The logical structure includes the database, tablespace, segment, partition, and block. The database is composed of tablespaces, which are composed of segments and segments.
The following table space can be used:
Control the disk space occupied by the database
Dba deploys different data types to different locations to improve I/O performance and facilitate recovery and backup

// Create a tablespace
Create tablespace // dba user or have the create tablespace permission
Create tablespace data01 datafile d:/data01.dbf size 20 m uniform size 128 K; // create a table space (up to 500 m) with a size of 128 K
Create table mypart (deptno number (4), deptname varchar2 (10), loc varchar2 (14) tablespace data01; // create a table mypart in the tablespace of data01, if no tablespace is specified, it is in the system tablespace by default.

// Change the tablespace status
Alter tablespace data01 offline; // take the tablespace data01 offline
Alter tablespace data01 online; // bring the tablespace data01 online
Alter tablespace data01 read only; // read-only table space data01
Alter tablespace data01 read write; // make the tablespace data01 readable and writable

Select * from all_tables where tablespace = "data01"; // query all tables in the data01 tablespace
Select tablespace_name, table_name from user_tables where table_name = 'emp'; // query the tablespace that EMP belongs.

Drop tablespace data01 including contents and datafiles; // delete a tablespace

// Three methods for expanding the tablespace: 1. Adding data files 2. Increasing the size of data files 3. Setting Automatic Growth
Alter tablespace data01 datafile 'd:/data02.dbf '100 MB; // Add another data file data02.dbf, 300 MB for the tablespace data01
Alter tablespace data01 'd:/data01.dbf' resize 100 M; // change the size of the original data file to M
Alter tablespace data01 'd:/data01.dbf 'autoextend on next 10 M maxsize 500 M; // set the automatic growth of the tablespace data01 file,

// Move the data file
1. Determine the tablespace.
2. Convert the tablespace to offline
Alter tablespace data01 offline;
3. Use commands to move to a specified location
Host move d:/data01.dbf c:/data01.dbf
4. Execute the alter tablespace command
Alter tablespace data01 rename datafile 'd:/data01.dbf 'to 'C:/data01.dbf'
5. Bring the tablespace online
Alter tablespace data01 online;
// Table space status, online, offline, read only, read write
// Index tablespaces, undo tablespaces, temporary tablespaces, and non-standard tablespaces

Date functions:
Sysdate // system time
Select sysdate from dual; // obtain the system date
Select ename form emp where sysdate> add_months (hiredate, 300); //
Select floor (sysdate-hiredate) "days of employment" from emp; // calculates the number of days of employment
Select hiredate last_day (hireday) from emp;
Select hiredate ename from emp where last_day (hireday)-2 = hireday; // The last three days of the employee

Conversion functions:
Oracle is an invisible Conversion
Select ename to_char (hiredate, 'yyyy-mm-dd h24: mi: ss') from emp; // display the specified date format
Select ename to_char (sal, 'l99999. 99 ') from emp; // display the currency. L indicates the local currency. 9 indicates the number. The dot and comma indicate the original intention. $ indicates the dollar. 0 indicates that the number is less than 0.
Select ename hiredate from emp where to_char (hiredate, 'yyyy') = 1980; // 1980 employee

System functions:
Select sys_context ('userenv', 'db _ name') from dual; // obtain the current database
Select sys_context ('userenv', 'language') form dual; // obtain the current language
Select sys_context ('userenv', 'session _ user') from dual; // obtain the current user
Select sys_context ('userenv', 'current _ scheme') from dual; // obtain the current scheme
Oracle manages data objects in the form of solutions, including tables, stored procedures, and triggers.