1, Chuangku
creating a database in oracle is usually called creating a tablespace, and users of the tablespace need to be created
– create tablespaces
create tablespace jwxt datafile 'jwxt.ora' size 500m autoextend on --Automatic extension next 50m maxsize 20480m --Each automatic expansion 50 M,Up to 20480 M extent management local;
– create tablespace default user
create user jwxt identified by 888 default tablespace jwxt quota 500m on jwxt;
– give it all permissions
grant all privileges to jwxt;
– users with connect permission can only log in to Oracle; Users with resource permission can only create entities, not database structures; Having all privileges is the highest privilege of the system. Only DBA s can create database structures.
2, Create table
oracle database uses sequence to complete the database autoincrement operation. Usually, a sequence is created for each table
create sequence USER_SEQ maxvalue 10000000
Create table
create table "user" ( id int constraint USER_PK primary key, name varchar2(5) )
3, Insert data
in oracle, the table name and field name cannot use the ~ (floating) above TAB, but use double quotation marks, and the value must use single quotation marks. At the same time, the next value method of the sequence is used to obtain the next value. Each time this method is used, the next value of the sequence will automatically increase in steps.
insert into "user"(ID,NAME) values (USER_SEQ.nextval,'Zhang San')
4, Paging query
Oracle paging query must use row number rownum. The row number of each query changes, so it is generally completed by sub query. The SQL is as follows:
select * from ( select rownum r_, row_.* from ( select * from student order by id ) row_ where rownum <=Y ) where r_>=X ;
10: Start index position.
Y: End index position.
5, jdbc operation
there are two driving methods before the url during jdbc operation, which are described below. The last sid is called the instance name. Generally, each database service will have an instance name. Each user has its own database. The query is also for the database.
At the same time, you need to import the corresponding dependencies:
<dependency> <groupId>com.oracle.database.jdbc</groupId> <artifactId>ojdbc8</artifactId> <version>12.2.0.1</version> </dependency>
Case:
Class.forName("oracle.jdbc.driver.OracleDriver"); Connection connection = DriverManager.getConnection("jdbc:oracle:thin:@192.168.75.128:1521:xe", "jwxt", "888"); Statement statement = connection.createStatement(); ResultSet resultSet = statement.executeQuery("select * from \"user\""); while (resultSet.next()) { System.out.println(resultSet.getString(2)); } resultSet.close(); statement.close(); connection.close();
Differences in url writing between thin and oci:
1) In terms of use, oci must install oracle client or on the client to connect, but thin is not required. Therefore, thin is more convenient to use, which is also a common reason for thin.
2) In principle, thin is a pure java implementation of tcp/ip c/s communication; In the oci mode, the client accesses the server by calling c library through the native java method, and this c library is the oci(oracle called interface), so this oci always needs to be installed with the oracle client (starting from oracle 10.1.0, OCI Instant Client is provided separately, and there is no need to install the client completely)
3) They are different types of drivers, oci is the second type of driver, thin is the fourth type of driver, but they have no difference in function.
4) Although many people say that oci is faster than thin, they haven't found the relevant test report for a long time.