Oracle database installation:

See article from Chuxus

User management in Oracle

Oracle has two built-in users: system and sys. Users can log in directly to the System user to create additional users, because system and sys have permission to create additional users.

  • Syntax [create user] : create user identified by password; Example: Create user Chuxus identified by PassWord;
  • Syntax [change user]: alter user user name identified by password [change password]; Example: alter user Chuxus identified by Angle;
  • Syntax [drop user]: drop user name; Example: drop user Chuxus;

Permission management in Oracle

  • Grant [connect,resource] to user name; grant [connect,resource] to user name; Example: Grant connect to Chuxus; Grant connect,resource to public;
  • Revoke connect, resource from user name; Revoke connect, resource from Chuxus; Revoke connect from public;

Create a new table

1. Use the PLSQL Developer tool to create 1.1 Right click on the login account location as shown in the figure to pop up the menu bar, and choose new – table





2. Use SQL PLUS to create the Oracle database

CREATE TABLE INFOS (STUID VARCHAR2(7) NOT NULL,-- STUID Id = 'S' + STUNAME VARCHAR2(10) NOT NULL,-- name GENDER VARCHAR2(2) NOT NULL,-- gender AGE NUMBER(2) NOT NULL,-- AGE SEAT NUMBER(2) NOT NULL,-- SEAT DATE DATE,-- enrollment time STUADDRESS VARCHAR2(50) DEFAULT'Address unknown'/I ALTER TABLE INFOS ADD CONSTRAINT PK_INFOS PRIMARY KEY(STUID)/II  ALTER TABLE INFOS ADD CONSTRAINT CK_INFOS_GENDER CHECK(GENDER ='male' OR GENDER = 'woman')/III

ALTER TABLE INFOS ADD CONSTRAINT CK_INFOS_SEAT 
CHECK(SEAT >=0 AND SEAT <=50)/IV

ALTER TABLE INFOS ADD CONSTRAINT CK_INFOS_AGE
CHECK(AGE >=0 AND AGE<=100)/V

ALTER TABLE INFOS ADD CONSTRAINT CK_INFOS_CLASSNO
CHECK((CLASSNO >='1001' AND
CLASSNO<='1999') OR (CLASSNO >='2001' AND CLASSNO<='2999')/VI ALTER TABLE INFOS ADD CONSTRAINTS UN_STUNAME UNIQUE(STUNAME)/VII In Oracle code, the "/" executes the statement in the cache, and since the buffer stores only one recently saved statement, each statement is followed by a separate line of "/" because it is not terminated by a semicolon, but is only stored in the buffer. II: Create a primary key constraint. III to VII Create check constraints together. Where ⑦ is the unique constraint, indicating that the value of the column is unique and cannot be repeated.Copy the code

To create a new table, type the above code into SQL PLUS.

Common Fields description

  • Char (length) : Stores a string of fixed length. The length argument specifies the length. If the stored string length is less than length, it is padded with Spaces. The default value is 1 and cannot exceed 2000 bytes.
  • Varchar2 (length) : Stores a string of variable length. Length specifies the maximum length of the string. The default value is 1 and cannot exceed 4000 characters.
  • NUMBER(p, s) : Stores both floating point and integer numbers. P indicates the maximum NUMBER of digits (including the integer part and the decimal point, p is 38 by default), and S indicates the NUMBER of decimal places.

Create a Java project to connect to the Oracle database

Write in front:

1. Check the port number of your Oracle database by opening the tnsnames.ora file in the following directory. D:\Oracle11g\product\11.2.0\dbhome_1\NETWORK\ADMIN: Oracle11g\product\11.2.0\dbhome_1\NETWORK\ADMIN





  • Create a project, import the JAR package, and configure environment variables.

  • Write an OracleDbutil file to get linked objects
public class OracleUtil {

	private static final String DRIVER = "oracle.jdbc.driver.OracleDriver"; // Driver String Private static final String URL ="jdbc:oracle:thin:@localhost:1522:orcl"; // Link String private static final String USER ="CHUXUS"; // User name private static final String PWD ="angle"; Public static Connection conn = null; publicOracleUtil() {}
	public static Connection getConnection() {
		
		try {
			Class.forName(DRIVER);
			conn = DriverManager.getConnection(URL, USER, PWD);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return conn;
	}
	public static void Close() { try { conn.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); }}}Copy the code
  • Test whether the link is normal
public static void main(String args[]) {
		Connection conn = OracleUtil.getConnection();
		System.out.println(conn);
	}
Copy the code

The final output result is as follows, it means that the link is successful, and then you can add, delete, change and check the database table

Details of the project go to the home page