Mysql/oracle Database
- Oracle database management system is a computer software that manages database access, and is comprised of Oracle databases and Oracle instances
- Oracle database: A collection of related operating system files that are organized together to become a logical whole, that is, an Oracle database. Oracle databases must work with memory instances in order to provide data management services externally.
- Oracle instance: A data structure located in physical memory that consists of multiple background processes and a shared pool of memory in the operating system, which can be accessed by a process lock.
- Oracle uses them to manage database access
- Oracle instances are the usual database services (service)
- An instance can manipulate the database; At any time an instance can be associated with only one database, access a database, and the same database can be accessed by multiple instances (RAC)
- Oracle differs from MySQL in:
- In an Oracle database, use number to denote a numeric type, date to represent a type, and VARCHAR2 to represent a character type
- The null value differs from 0, and the result is null (NULL), the null value is invalid, the unspecified
- Creation of aliases: (Way three)
Use double quotes "", such as: display aliases intact (especially if the alias is made up of multiple words)
select employee_id "id",last_name "Name",12*salary "annual_sal"from employees;
Connector:
- String:
- The string can be a character, number, date in the select list
- Dates and characters can only appear in single quotation marks
- Each time a row is returned, the string is output once
Use distinct to filter out duplicate data, such as:
select distinct dept_id from empt;
- SQL statements with the Sql*plus command:
- SQL: A language, ANSI standard, keywords cannot be abbreviated, use statements to control the definition of tables in a database, and data in tables.
- Sql*plus: An environment, one of the characteristics of Oracle, Keywords can be abbreviated (such as: Edit (ed), describe (DESC)), the command can not change the value of data in the database, centralized operation
Database operation functions:
Single-line function
Single-line function classification: character, value, date, conversion, universal
单行函数:①操作数据对象; ②接收参数返回一个结果 ③只对一行进行变换 ④每行返回一个结果 ⑤可以嵌套与转换数据类型 ⑥参数可以是一列或一个值
Character functions:
Character size control functions, such as:
LOWER(‘HELLOWORLD‘) //helloworldUPPER(‘hello‘) //HELLOinitcap(‘helloWorld you‘) //Helloworld You,首个字母大写
Character control functions, such as:
CONCAT(‘Hello‘, ‘World‘) //HelloWorldSUBSTR(‘HelloWorld‘,1,5) //HelloLENGTH((‘HelloWorld‘) //10INSTR(‘HelloWorld‘,‘W‘) //6LPAD(24000,10,‘*‘) //*****24000RPAD(24000,10,‘*‘) //24000*****TRIM(‘H‘ FROM ‘HelloHWorldH‘) //elloHWorldREPLACE(‘abcd‘,‘b‘,‘m‘) //amcd
Numeric functions:
- Date function:
- Date-based data in Oracle actually contains two values: Date and time
- Date: function sysdate return
- Two date subtraction returns the number of days between dates (but does not allow addition operations)
- Months_between: Two months of difference between dates
- Add_months: Add a number of months to the specified date
- Next_day: The next week of the specified date * corresponds to the date
- Last_day: Last day of the month
- ROUND: Date Rounding
- TRUNC: Date Truncation
Conversion functions:
Implicit (Oracle automatically completes the following conversions)
VARCHAR2 or CHAR ---------------------> NUMBERVARCHAR2 or CHAR ---------------------> DATENUMBER ------------------------------> VARCHAR2DATE ------------------------------> VARCHAR2
-
Dominant
-
Data→to_char () →character→to_date () →date
Select To_char (sysdate, ' Yyyy-mm-dd ') from Dual;select to_date (' 1995-05-23 ', ' yyyy-mm-dd ') The from dual;//uses double quotes when using characters, such as: Select To_char (sysdate, '
yyyy "Year" MM "month" DD "Day" from dual;
-
Number→to_char () →character→to_number () →number
//1,234,567.890, ' $999,999,999,999.999 ' represents USD, ' l999,999,999,999.999 ', indicating local currency symbol (l = local) Select To_char (1234567.89, ' 999,999,999,999.999 ') from Dual;select to_
Number (' y001,234,567.89 ', ' l000,000,999.99 ') from dual;
In the conversion of numeric values, the To_char () function often uses a centralized format:
-9: number-0:0-$: Dollar sign-L: Local currency symbol-.: Denotes decimal point-,: denotes thousand character
-
Common functions:
- This class of functions applies to any data type and also to null values
-
NVL (EXPR1,EXPR2):
- converts null values to a known value
The
- data types you can use are date, character, value
The form of the
-
function:
NVL (commission_pct, 0)//is expressed as a value of 0 when commission_pct is null, otherwise equals COMMISSION_PCTNVL (hire_ Date, ' 01-jan-97 ')
- NVL2 (EXPR1,EXPR2,EXPR3): Expr1 NOT NULL, return EXPR2, NULL, return EXPR3
- Nullif (EXPR1,EXPR2): equals returns NULL, not equal to return EXPR1
- coalesce (expr1,expr2,..., exprn): If the first expression is empty, the next expression is returned, The other parameters are coalesce. The advantage of
- coalesce compared to NVL is that coalesce can handle alternating multiple values at the same time
Conditional expression:
- Using if-then-else logic in SQL statements
Two methods are used:
Case expression:
Format:
case expr when comparison_expr1 then return_expr1 [when comparison_expr2 then return_expr2 ...]
Such as:
select department_id,case department_id when 10 then salary*1.1 when 20 then salary*1.2 else salary*1.3endfrom employeeswhere department_id in(10,20,30);
Decode function:
Such as:
select department_id,decode(department_id, 10, 1.1*salary, 20,1.2*salary, 30,1.3*salary)from employees;
Nested group functions:
Oracle Database sub-query:
Sub-query syntax:
select select_listfrom tablewhere expr operator( select select_list2 from table2; //table与table2可以是同一个表);
- The subquery executes one time before the main query, and the result of the subquery is used by the main query
Attention:
1.子查询要包括在括号内2.将子查询放在比较条件的右侧3.子查询中可以使用组函数4.子查询中要留意空值
Single row comparison operators used by single-line subqueries:
=、>、>=、<、<=、<>(不等于)
Multiline operators used by multiple rows of subqueries:
如:in、any(和子查询返回的某一个值比较)、all(和子查询返回的所有值比较)
Composite subquery Nesting
//查询平均工资最低的部门信息select *from departmentswhere department_id = (select department_id from employees group by department_id having avg(salary) = (select min(avg(salary)) from employees group by department_id ) )
Create and manage Tables (DDL) (Create, Alter, DROP, rename, truncate) → Operations are not rolled back
Data processing (DML) (insert INTO, delete from, upddate, select) → actions can be rolled back
A special way to insert data: Copy data from other tables, such as:
insert into emp(employee_id,last_name,salary,hire_date)select employee_id,last_ame,salary,hire_datefrom employeeswhere employee_id < 20; //加上限制条件表示仅复制部分数据
Create script: Use & Variables in SQL statements to specify column values,& variables are placed in the values clause, such as:
//该插入方法是在执行语句后来添加数据的insert into emp(employee_id,last_name,salary,hire_date)values(&employee_id,‘&last_name‘,&salary,&hire_date);
-update:update the entire column is updated without limiting the condition
In update, you can use a subquery when you update data that is based on another table, such as:
upadate emp_copy set departmen_id = (select departmen_id from employees where employee_id = 20 ) where job_id = (select job_id from employees where employee_id = 10 );
- Data additions and deletions related operations
- Commit (Commit): Similar to File save
- Rollback (rollback): When the operation data is not at that time, it can be rolled back to the pre-modified data
Database transactions:
- It is best to perform a commit when the database operation (add and Scan) is completed.
Oracle Database Creation constraints:
Non-null constraints (NOT NULL), such as:
create table emp( id number(10) constraint emp_id_nn not null, //emp_id_nn表示约束名 name varchar(20), salary number(10,2))
Unique constraints (unique), such as:
create table emp( id number(10) constraint emp_id_uq unique, //列级约束,emp_id_uq约束名 name varchar(20) constraint, salary number(20), phone number(11), //表级约束 constraint emp_phone_uq unique(phone) //括号内指明约束列)
PRIMARY KEY constraint (primary key), such as:
create table emp( id number(5) constraint emp_id_pk primary key, name varchar(20), salary number(9,2) //或者使用表级约束 //constraint emp_id_pk primary key(id))
FOREIGN KEY constraint (foreign key): Connect two tables and associate two tables
- FOREIGN key: Specify columns in a child table at the table level
- References: Columns that are marked in the parent table
Such as:
create table empt( id number(5), name varchar(20), salary number(7,2), dept_id number(5), constraint emp_dept_fk foreign key(dept_id) references depts(dept_id) )create table depts( dept_id number(5), dept_name varchar(10), job_id number(3))
- When a column in the parent table is deleted when the data is deleted, the corresponding column in the child table (Foreign Key Association) is also deleted
-
Syntax for adding constraints:
-
Using the ALTER TABLE statement:
1. Add or remove constraints, but you cannot modify constraints 2. Constraint 3. Add not Null constraints to use the Modify statement
-
Format:
ALTER TABLE table_nameadd (drop ...)
[Constraint constraint_name] Type (column);
-
Such as:
//Add NOT null ALTER TABLE empmodify salary number (8,2) not null;//add additional constraint ALTER TABLE EMPADD constraint Emp_uq unique (name);//delete constraint ALTER TABLE EMPDROP constraint emp_uq;//invalid constraint ALTER TABLE empdisable constraint emp_uq;//effective (
Note that the prerequisite for enable is that there is no corresponding constraint problem with the column where the constraint resides) ALTER TABLE empenable constraint emp_uq;
-
Query constraint:
- user_constraints
-
Such as:
//query constraint name, constraint type, constraint select Constraint_name, Constraint_type,search_condition from User_constraintswhere table_name= ' employees '; The expression table name
-
Query defines the columns of the constraint:
-
View:
- view is a virtual table that is based on an existing table
- The advantages of a statement that provides data content to a view as a SELECT statement
- view:
- Control Data access
- simplify queries
- avoid repeated access to the same data
-
View rules for using DML
-
Sequence: database objects that can be used by multiple users to produce unique values
- automatically provide unique numeric values
- shared objects
- primarily used to provide primary key values
- loading sequence values into memory can improve access efficiency. /li>
-
Create format:
create sequence seq_name[increment by n]//value per increment [start wiht n]//starting from [{MAXVA Lue N |
Nomaxvalue}][{minvalue N | nominvalue}][{cycle | nocycle}]//Whether a loop is required [{Cache n | nocache}]//cache login
-
Such as:
Create sequence emp_seqincrement by 5start with 2maxvalue 50cyclenochche;
-
Sequence Execution method:
1.select emp_sql.nextval from dual;
2.select emp_sql.curval from dual;
- Note: When you first call a method, you need to use Nextval.
Modification of the
-
Sequence (cannot modify the initial value)
alter sequence seq_nameincrement by newvalnomaxvalue ...;
- Modify sequence Note:
- 1. Must be the owner of the sequence or have ALTER permission on the sequence
- 2. Only future sequence values will be changed
- 3. Change The ' initial value ' of the variable sequence can only be implemented by removing the sequence after the
- 4.rollback is unable to roll back the sequence
Base of the Mysql/oracle database (ii)