Common SQL commands @ oracle Data Type summary @ permissions, roles, User Creation and use @ pseudo columns and precautions

Common SQL commands @ oracle Data Type summary @ permission, role, User Creation and use @ pseudo column and precautions 1. user system permissions with different permissions: User name: sys/system (the default user name is sys, system); Password: Oracle11g (the password is your installed password, oracle11g password must be entered Oracle11g) reset the user password. Here we take scott user as an example: alter user scott identified by tiger; (when you forget the password, you can use sqlplus sys/aaa as sysdba; -- Log On As a database administrator; alter user identified by is a keyword) for SCOTT: alter user scott account unlock; scott can also use an incorrect password during logon, this will allow you to re-enter the password and set the password. 2. after the oracle database command is successfully installed, enter the DOS interface. When you perform the following operations, you must start the oracle service (oracleServiceoral ), if you want to connect to the database in the project, you also need to enable two services (oracleOraDb11g_home1 second, OracleDBConsoleorcl); it is best to set it to manual, and enable the service before each use, because oracle occupies a lot of memory, if you start a computer, it will not only lead to slow boot, but also lead to poor performance of the computer will also crash. 1) There are three methods to enter the oracle command window: A. Start -- run-cmd (or win + r Enter cmd) press Enter: Enter sqlplus press ENTER; prompt to enter the correct user name and password; b. Start> All Programs> oracle File> application development> sqlplus; C. Start> enter sqlplus and press Enter. The system prompts you to enter the correct user name and password. 2) switch user operation A, Exit first and then log on to Exit, quit: Exit the command window; crtl + c: Force Exit (you can Exit from the command in the input State ); the sqlplus window is closed and the cmd window is exited. Oracle; log on to the command cmd-> sqlplus press enter system/Oracle11g to log on directly or search sqlplus to directly enter the User Password B, or directly switch from the user without exiting the SQL> operation using the conn/user name/Password switch users; for example, conn scott/tiger. Note: No semicolon ends during logon. 3) tables in oracle are user-defined sys Super Users (with the highest permissions) system Database Administrators (who perform most management operations) to test users. 3. common oracle commands and knowledge point commonly used commands: select * from tabs: queries the tables of the current user (multiple queries to the system table); select table_name from user_tables: query all the table names under the current user. desc table name: view the table's columns (table structure) conn system/Oracle11g: connect to the user create user scott identified by tiger: create user conn scott/tiger: switch user (User Switching failure prompt Not logged on; prompt that the user has no session permission, should assign the session permission) user and password: sys/Oracle11g scott/tiger hr/h R dbshop/tiger set the column width: set linesize 120 set the specific column width: col ename for 9999 (only 9, less than the number of digits will use # to replace the current value) run the "host cls" command to clear the screen in the sqlplus command window to set deptno to show repeatedly: break on deptno skip 0; Set deptno to show repeatedly and different deptno fields are separated by two lines: break on deptno skip 2; backup table (create a table the same as the emp table): create table emps as select * from emp; assign permission: grant permission name to user name; SQL> conn system/Oracle11g; // The first step is to switch to the Administrator SQL> grant create session to scott; // prompt for user authorization: Grant succeeded // remarks for successful authorization when receiving Scott can log on to the table after the permission is granted. However, when a user creates a table, the table cannot be granted permissions. role (a role contains multiple permissions): connect create session, create table, resource (permission to use table space )...... Grant connect, resource to scott; Note: Only the administrator can perform operations on User Creation and permission allocation, which cannot be implemented by common users. create user: Switch to system administrator to Log On As system administrator. 1) create user username identified by password; (cannot be full number) // Create user 2) grant create session, create table, resource to user name; // assign permissions to users 3), start directory (D: \ emp. SQL); // note that the file cannot be placed on the desktop (because the directory cannot contain spaces or the following error occurs: SQL> start C: \ a \ emp. SQL; Error reading file) Case: SQL> create user redarmychen identified by redarmy; // create a user and set Password: redarmy SQL> grant connect, resource to redarmychen; // authorize connect, resource SQL> conn redarmychen/redarmy; // switch to SQL> start c: \ emp. SQL; // method of importing External SQL files SQL Plus Common commands: conn switching connection user desc display table structure host execute operating system command start execute file system SQL statement exit col format output/execute the latest SQL or new definition process startup start database instance (DBA) shutdown the database instance (DBA) startup shutdown must be performed by the sys system user. Other users cannot use it. this command can only be executed in sqlplus. cannot be used in client tools. Data is a digital representation of information. Information processing is carried out on the basis of a large amount of structured data. The core of the database management system is the database, and the main object of the database is the table, A table is a place where structured data is stored. the Oracle System also provides a large number of data types, including user-defined data types and set data types: a. The character data type can be used to declare fields that contain multiple letters and numbers. Fixed-length character type char: used to store fixed-length characters. If the declared length is fixed (no matter the actual size you store, but the size cannot exceed the declared length, use spaces. The default size of 1B. the maximum size is 2000B. Nchar and char are parsed in the same way, but ncarh stores Unicode character data. The variable-length character type varchar is similar to char, but it is used to store variable strings, while char is used to store fixed strings. Nvarchar and varchar have the same resolution, but nvarchar is used to store data in double bytes. B. date short date format (1990-10-10) Timestamp long date Format (1990-10-10 10:10:10) C. Number type Number (2), which represents a two-digit integer. Number (3, 2) indicates that it is declared as a three-digit Number, and there are two digits after the decimal point. D. Text-type Blob: it can store images, audio files, videos, and other files. Clob: a large object in character format. oracle Data is encoded in the unicode format Bfile: used to store binary files. E. The rowid type is used to store the physical address of each record in the oracle internal table. 4. create Table command and reproduce insert statement: insert a new (full field) record into the table insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) values (1111, 'test', 'test', '000000', to_date ('2017-12-12 ', 'yyyy-mm-dd'), 7369, 20 ); or insert into emp values (2222, 'test', 'test', '000000', to_date ('2017-12-12 ', 'yyyy-mm-dd '), 100,100, 20); insert a new (partial field) record SQL> insert into emp (empno, ename, hiredate, deptno) values (3333, 'test ', to_date ('2017-12-12 ', 'yyyy-mm-dd '), 30); Note: if there is an association during the insert operation, you must insert the values of related fields. note: After insertion, you must manually submit commit. SQL is the abbreviation of structured Query Language (structured Query Language. You can use SQL statements to create or delete database objects, insert, modify, and update database data, and perform various daily management operations on the database. It is the standard language for all relational database management systems. In other words, you can use SQL to operate all relational databases. 5. SQL is categorized by function: Data Definition Language (DDL): used to create, modify, and delete database objects. Database Operation Language DML (Data Manipulation Language) is used to query, add, modify, or delete Data in a database object. The Database Control Language DCL (Data Control Language) is used to Control the users who access specific objects in the database, and the SQL Language extensions in the grant revoke Oracle system are called PL/SQL languages. Data Query Language DQL: (Data Query Language) SELECT Data Query Language 6. set operators UNION and remove duplicate rows union all union set do not remove duplicate rows MINUS difference set INTERSECT intersection (oracle-specific) 7. pseudo column 1), rowNum pseudo column SQL> select ename, rownum from emp; // rownum is the query result with the serial number ename rownum ---------- SMITH 1 ALLEN 2 WARD 3 SQL> select ename, rownum from emp where rownum <= 5; // query the first five records of a record. ename rownum ------------ -------- SMITH 1 ALLEN 2 WARD 3 JONES 4 MARTIN 5 Note: If rownum is used as a comparison condition, it can only be used for values smaller than or equal. Do not use equal to, greater than, or greater than or equal to. But there is a special value that is greater than or equal to 1 or equal to 1. This is related to the generation principle of rownum, that is, when the result is obtained from the disk, the serial number is added. Test and analysis: SQL> select ename, rownum from emp where rownum> 5; // when the value is greater than the value, no results are found. ename rownum ---------- analysis: when the data read from the disk, plus the serial number is 1, 1> 5 is not true, read the data, plus the serial number is 1 (the key here?),> 5 not true. Read data ....... Therefore, 1 is a special value. SQL> select ename, sal, RowNUM from emp Order By sal; // observe the output result of rownum. ename sal rownum ---------- --------- ---------- test 100.00 15 SMITH 800.00 1 JAMES 950.00 12 ADAMS 1100.00 11 WARD 1250.00 3 and above indicate the sort of the first rownum to be executed. solution: SQL> select ename, sal, rownum from (select * from emp order by sal); // implement ENAME SAL ROWNUM ---------- --------- ---------- test 100.00 1 SM through subquery ITH 800.00 2 JAMES 950.00 3 ........ Rowid pseudo column: uniquely identifies a record in a database table (it can also be understood as the physical address of the record in a database table ). SQL> select ename, sal, rowid from emp; ENAME SAL ROWID ---------- --------- ------------------ SMITH 800.00 AAADVsAABAAAI1qAAA ALLEN 1600.00 AAADVsAABAAAI1qAAB ....... Note: When rowid is used for query, it is the fastest speed. SQL> select empno, rowid from emp where empno = 7369; EMPNO ROWID ---------- limit 7369 then select empno, rowid from emp where rowid = 'hangzhou'; EMPNO ROWID ---------- ------------ 7369 then