Shulou(Shulou.com)05/31 Report--

This article mainly explains the "Oracle and mysql database add, delete, change method is what", the article explains the content is simple and clear, easy to learn and understand, the following please follow the editor's ideas slowly in-depth, together to study and learn "Oracle and mysql database add, delete, change method is what" it!

I. Oracle architecture

1. Conceptual relationship

Database and instance: Oracle has only one database, but can have multiple instances

Oracle database and instance are one-to-many. In general, there is only one instance. What we usually call Oracle database name is actually the instance name of Oracle.

Instance and user one-to-many

Instance and tablespace one-to-many

Table space and user one-to-many

2. Oracle structure

Physical structure: data files (xxx.dbf, xxx.ora)

Logical structure: database tablespace segment Oracle data block

II. Oracle practical commands and sentences

Enter: sqlplus system/password@192.168.25.128:1521/orcl in the dos window of the physical machine

Query all tablespaces: select tablespace_name from dba_tablespaces

Query all roles: select * from dba_sys_privs

View all users: select * from all_users

View current user: show user

Logging in through scott can be tested: sqlplus scott/tiger@192.168.25.128:1521/orcl

Account lock error: ERROR:ORA-28000: the account is locked

Solution: log in with system again, execute alter user scott account unlock; to unlock the scott user

Password expiration error: ERROR:ORA-28001: the password has expired

Solution: just re-enter the password tiger

View all tables under the current user: select table_name from user_tables

View table structure: desc emp

Clear screen: host cls

Set line width: set linesize 150

Show line width: show linesize

Set column width: col ename for A8 (width of 8 characters)

Set the number of pieces of data per page: set pagesize 20

Show how many pieces of data per page: show pagesize

Writes the statements from the command line to the specified file under the specified directory:

Specify which file in which directory to write to: spool d:\ 1.text

Write completed: spool off

Edit the commands you entered earlier:

Enter ed, then enter enter

This command is mainly used when the current sql statement is miswritten and does not want to be rewritten. At this time, you can use the ed command to call up notepad.

Edit the previous command (if the command is used on a Linux system, the vi editor is called up)

Execute the previously cached command: /

Create tablespace + create user + system administrator authorization

1. Create a tablespace tablespace

Create tablespace waterbos

Datafile'c:\ waterbos.dbf'

Size 100m

Autoextend on

Next 10m

2. Create user user create user xuming identified by itcast default tablespace waterbos; 3 and authorize grant ORA-01045 to new users created: user lacks CREATE SESSION privilege; logon denied solution: system administrator authorizes new users: grant create session,resource to user name; equivalent to grant connect,resource to user name; grant dba to user name Zhang San Super Admin's addition to A Resource Li Si ordinary Administrator's deletion of A Resource Wang Wu Project Manager's Modification of A Resource Zhao Liu Product Manager's query on A Resource Qian Qi Technical consultant's query on B Resource

Create table + maintain table structure

DDL:Data Definition Language data definition languages such as create, alter, drop, truncate

DML:Data Manipulation Language data manipulation languages such as select, update, delete, insert

DCL:Data Control Language data control languages such as grant, revoke

1. Oracle common data types

Char,varchar2,long,number,date,clob,blob

2. Create table + constraint 1) basically create table statements create table myemp3 (id number (10), name varchar2 (10) constraint myemp3_name_nn not null,-- non-empty constraint gender varchar2 (4) default 'male',-- default values deptno NUMBER (2), email varchar2 (20), constraint myemp3_id_pk primary key (id) -- Primary key constraint constraint myemp3_gender_ck check (gender in ('male', 'female')),-- check constraint constraint myemp3_email_uk unique (email),-- unique constraint constraint myemp3_deptno_fk foreign key (deptno) references dept (deptno) on delete set null-- Foreign key constraint-- there is no such writing constraint myemp3_name_nn not null) 2) use subquery to create table create table emp2 as select * from emp where 1x2; because 1x2is false, the statement only copies the structure, but does not copy data 3, modify table 1) modify the table column oracle: alter table myemp3 modify ename vachar2 (20); cannot take column mysql: alter table myemp3 modify (column) ename varchar (20) 2) add oracle: alter table myemp3 add gender varchar2 (4); mysql: alter table myemp3 add (column) gender varchar (4); 3) delete the table column oracle: alter table myemp3 drop column gender; must take column mysql: alter table myemp3 drop (column) gender; 4) rename the table column oracle: alter table myemp3 rename column ename to myname Must take column mysql: alter table myemp3 change (column) ename myname varchar (20); 5) rename table name oracle: rename myemp3 to myemp4; mysql: rename table mysqltname3 to mysqltname4; must take table 4, delete table 1) delete table drop table myemp3 purge completely Without purge, the data is temporarily placed in the Recycle Bin. 2) the data after drop is put into the recyclebin Recycle Bin to view the Recycle Bin: show recyclebin; completely clears the Recycle Bin: purge recyclebin; deletes the table thoroughly: drop table myemp3 purge; views the Recycle Bin table data: select * from "BIN$cd/KYgV5RY6/RxGTjn2Skg==$0"; 3) Flash flashback table myemp3 to before drop

5. Operation on table data (insert,update,delete)

1) insert data into the table (insert into values)

Insert into t_owners values (2) insert into t_owners values' Zhang San', 1 'Magi' 3-3 '7895' sysdate1')

Insert into t_owners2 select * from t_owners

Insert into table name (id,name) values (xx,yy), (xx1,yy1) (this can be written in mysql, but not in oracle)

Insert data into multiple tables: insert all into table name (id,name) values (xx1,yy1) into table name (id,name) values (xx2,yy2)

2) modify the data in the table (update set) update table name set dateddd=dateadd-3,col1=col1+1; 3) Delete the data in the table using delete delete data delete from myemp where empno=7369; use truncate to delete the entire table data truncate table myemp; function is similar to delete from myemp The difference between the same delete and truncate deleted data: (1) delete can be rolled back Truncate cannot (2) truncate is more efficient than delete to delete data in actual enterprise development Note: (1) back up the data to be deleted first; (2) confirm that the sql statement for deletion is correct; (3) try to choose the way to roll back the data (4) drop > truncate > delete

VI. Import and export

1) Import and export of the whole library

Exp system/itcast full=y

Exp system/itcast full=y file=abc.dmp

Imp system/itcast full=y

Imp system/itcast full=y file=abc.dmp

2) Import and export by user exp system/itcast owner=xuming file=abc.dmp imp system/itcast file=abc.dmp fromuser=xuming 3) Import and export exp xuming/itcast file=a.dmp tables=t_owners; imp xuming/itcast file=a.dmp tables=t_owners; by table 4) mysql import export the first import data: mysql-uroot-proot mysql > create database crm; mysql > use crm Mysql > source d:/crm.sql the second way to import data is to create a database crm and execute mysql-uroot-proot crm.

D:\ crm1.sql

Summarize the differences in the use of oracle and mysql in projects

1. Different jar packages are imported.

The oracle:ojdbc14.jar version may change

The mysql:mysql-connector-java-5.1.7-bin.jar version may change

Note: jar package versions vary from project to project

2. Jdbc get connection and write method

Oracle:Connection connection = DriverManager.getConnection ("jdbc:oracle:thin:@192.168.25.128:1521:orcl", "xuming", "itcast")

Mysql:Connection connection = DriverManager.getConnection ("jdbc: mysql://localhost:3306/mybatis?characterEncoding=utf-8", "root", "root")

3. Configuration file

# oracle jdbc properties

Jdbc.url = jdbc:oracle:thin:@localhost:1521:xe

Jdbc.driver= oracle.jdbc.driver.OracleDriver

Jdbc.user = bos

Jdbc.password = bos

# mysql jdbc properties jdbc.driver=com.mysql.jdbc.Driver jdbc.url=jdbc:mysql://localhost:3306/bos?characterEncoding=utf-8 jdbc.user=root jdbc.password=root

=

1. Single table query

Complete statement: select from where group by having order by

1. Simple conditional query

=,! =, >, > =

RegisterOutParameter is required to set outgoing parameters

CallableStatement execute ()

The statement of CallableStatement calling stored procedures and stored functions {call mypro1 (?)} calls stored procedures {? = call myfn1 (?)} calls stored functions

7. Trigger

1. Front and rear

Pre-trigger: before the sql statement commit

Post trigger: after the sql statement commit

2. In terms of the number of rows affected

Insert into emp () values () inserted 3 records

Row-level triggers: triggered 3 times

Table-level trigger (or statement trigger): triggered once

3. Grammar

Create or replace trigger trigger name

Before | after

Delete | update | insert

On table name

For each row-- row-level trigger

Begin

Pl/sql statement

End

=

Oracle Summary:

1. Basic skills of database (ddl+dml (insert/update/delete) + single table + multiple tables)

Basic statement: select from where group by having order by

Multiple tables: internal connection of where connection + outer connection of on connection

Related to jdbc operation:

Statement/PreparedStatement/CallableStatement

Con.prepareCall ("stored functions and stored procedures")

Mysql connection syntax: jdbc: mysql://localhost:3306/mybatis?characterEncoding=utf-8

Oracle connection syntax: jdbc:oracle:thin:@192.168.25.128:1521:orcl

II. Database optimization

1. Sql statement optimization

1) try to use column names when querying, such as: select empno. All column names are written as from emp.

2) grouping queries have aggregate functions. If filtering is used, having is used.

Ordinary query with conditions, using where

3) choose between subquery and multi-table query, using multi-table query as much as possible

4) in set operations, if both union and union all are OK, consider using union all because union needs to remove duplicates

5) Don't use set operation if you don't use set operation.

6) for multiple conditional connections, you can put the false's on the far right of the and and the true's conditions on the far right of the or.

2. Index optimization indexing: indexing on a single column and indexing on multiple columns (complex or federated indexes): two scenarios of indexing need to be considered: 1) indexing: indexing on a column, it must be taken into account whether the value of the column is more extensive, and queries are frequent 2) do not index: a column is often changed, do not build an index

3. Commonly used in practice:

DML insert+update+delete+select

DDL create alter drop truncate

Thank you for your reading, the above is the "Oracle and mysql database add, delete, change method is what" the content, after the study of this article, I believe you on Oracle and mysql database add, delete, change method is what this problem has a deeper understanding, the specific use of the situation also needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.