- Sqlplus How to connect to a database
1> cmd input: sqlplus user name/password @ DB instance
such as: Sqlplus System/[email protected],
Sqlplus Sys/admin as Sysdba
2> Open Sqlplus Enter your username and password directly
3> use command: Connect sys/admin as SYSDBA,
Connect system/lxr316
4> Super Admin login: SYS as SYSDBA
Disconnect database: Disconn (ECT);
- MySQL Connection
CMD input: MySQL [–h server address]–u user name –p[password] (need to configure the bin of the MySQL database to the environment variable)
such as: Mysql-hlocalhost–uroot–proot
Mysql–uroot-p
- User Action
Oracle:
1> Creating user
Create username identified by password
2> Modify user password
Alter username user name identified by New password;
3> account lockout, unlock
Alter user username accounts lock|unlock;
4> user changes password directly at first logon-password invalidation
Alter user username password expire;
MySQL:
MySQL:
1> created by:
Create user ' username ' @ ' host ' identified by ' password ';
Username-the user name that you will create, host-Specifies which host the user can log on to, and if localhost is available to the local user, you can use the wildcard% if you want the user to be able to log on from any remote host. Password-the user's login password, The password can be empty, and if it is empty, the user can log on to the server without a password.
For example:
CREATE USER ' dog ' @ ' localhost ' identified by ' 123456 ';
CREATE USER ' pig ' @ ' 192.168.1.101_ ' idendified by ' 123456 ';
CREATE USER ' pig ' @ '% ' identified by ' 123456 ';
CREATE USER ' pig ' @ '% ' identified by ';
CREATE USER ' pig ' @ '% ';
2> Set and change user password
Set PASSWORD for ' username ' @ ' host ' =password (' newpassword ');
If the current user is logged in: SET PASSWORD = PASSWORD ("NewPassword");
Example: SET PASSWORD for ' pig ' @ '% ' = PASSWORD ("123456");
3> Delete users
Drop user ' username ' @ ' host ';
- Rights Management
Oracle:
1> Authorization
Grant permission to user name;
Grant all on table name to user/role
2> Revoke Permissions
Revoke permissions from user name;
Object permissions can be cascade retracted, but system permissions may not be cascaded back.
(1) System permissions: A give a system permission to b,b again Grant C. Then a reclaims the permission from B. Thereafter, C still has this permission.
(2) Object permissions: A assigns an object permission to B,b and then Grant C. Then a reclaims the permission from B. Since then, C has also lost this permission
3> Common system permissions
Create session– User Login
Create table– creating a table
Unlimited tablespace– Infinite table space
4> Object Permissions
Select on table name
Update on table name
Delete on table name
Insert on table name
5> Permissions for cascading grants
System permissions with admin option
Object permissions with GRANT option
Grant permission to username with admin option|with grant option;
Mysql:
1> Authorization:
GRANT privileges on Databasename.tablename to ' username ' @ ' host '
Privileges-user's operation permissions, such as SELECT, INSERT, UPDATE, etc. use all if you want to grant the permission. DatabaseName-database name, tablename-table name, available if you want to grant the user appropriate permissions for all databases and tablesindicate, such as.*。
Example:
GRANT SELECT, INSERT on Test.user-' pig ' @ '% ';
GRANT all on.To ' pig ' @ '% ';
Note: A user authorized with the above command cannot authorize another user, and if you want the user to be authorized to do so, use the following command:
GRANT privileges on Databasename.tablename to ' username ' @ ' host ' with GRANT OPTION;
2> Revoke user Rights
REVOKE privilege on Databasename.tablename from ' username ' @ ' host ';
Description: Privilege, DatabaseName, TableName-With the authorization section.
Example: REVOKE SELECT on.From ' pig ' @ '% ';
Note: If you are authorizing the user ' pig ' @ '% ' (or similar): Grant SELECT on Test.user to ' pig ' @ '% ', then use revoke select on.From ' pig ' @ '% '; the command does not revoke the user's SELECT operation on the Users table in the test database. Conversely, if authorization is using the grant Select on.To ' pig ' @ '% '; then revoke select on Test.user from ' Pig '; the command cannot revoke the user's SELECT permission to the Users table in the test database.
Specific information can be used with the command show GRANTS for ' pig ' @ '% '; View.
- Oracle Roles Role
Role: Collection of permissions
View the roles in the current user User_role_privs
View system permissions in a role Role_sys_privs
View object permissions in a role Role_tab_privs
1> to build a character
Create role Role1;
2> authorization to the role
Grant create any table,create procedure to role1;
3> Grant/Reclaim user role
Grant Role1 to User1;
Revoke role1 from User1;
4> Create a password with a role (password must be provided when the role with password is in effect)
Create role Role1 identified by Password1;
5> Modify Role: password is required
Alter role ROLE1 not identified;
Alter role ROLE1 identified by Password1;
6> set the role that the current user is going to take effect
Set role role1;//make role1 effective
Set role role1,role2;//make role1,role2 effective
Use Role1 with password to take effect
Set role Role1 identified by Password1;
Set role all;//takes effect with all roles of the user
Set role none;//sets all roles to fail
All other roles for the user except Role1 are in effect.
Set role all except role1;
SELECT * from session_roles;//View the roles in effect for the current user.
7> Modify a specified user, set its default role
Alter user User1 default role role1;
Alter user User1 default role all except Role1;
8> Deleting a role
Drop role Role1;
After the role is deleted, the user who owns the role will no longer have the role, and the corresponding permissions will not be available.
9> system Roles
DBA: Having full privileges, is the highest privilege role of the system;
RESOURCE: A user with RESOURCE role privileges can only create entities;
Connect: Users with Connect role permissions can only log in to Oracle;
For normal users: Grant Connect, Resource role permissions.
For DBA administration users: Grant Connect,resource, DBA role permissions.
- View table Structure Information command
Oracle:
Desc (ribe) table name such as: Desc LXR
Mysql:
Desc (ribe) table name;
- Show currently logged on User:
Oracle:show User
Mysql:select user ();
- MySQL View database under User: show databases;
View database: show databases;
View table: Show tables;
- Oracle User Lock and Unlock
ALTER user Username account lock;– Lock user
ALTER user Username account unlock;– Unlock user
- Sqlplus Clear Screen Command
Clear screen Shorthand: Cl SCR (dos clear command: CLS)
- Oracle Change Current User Date format command
Alter session set nls_date_format= ' YYYY-MM-DD ';
- Basic data types in Oracle
Numeric Number (5,2), int:999.99
Character Char,varchar2 (4000)
Dates Date
Picture BLOB (Binarylargeobject) 4G
Text CLOB (Character LargeObject) 4G
- Oracle Table structure Operations
1> table rename
ALTER TABLE table name rename to new name;
2> add comment to table
Comment on table name is ' comment content ';
3> Add constraint
a) ALTER TABLE name ADD CONSTRAINT constraint name constraint type (field name of constraint);
Constraint constraint type:
PRIMARY key primary key
Unique unique
Check restriction
NOT NULL cannot be NULL
B) ALTER TABLE table name add constrain T constraint name foreign key (field name) References table name (field name);
Foreign key Foreign key
reference the value that exists in the primary key, you can insert a duplicate record, you can insert a duplicate null value
4> delete constraint
ALTER TABLE name DROP CONSTRAINT constraint name;
5> Delete Table structure
drop table name, (This operation is DDL, automatically commits and cannot be rolled back)
6> table add field
ALTER TABLE name add field name type;
7> Delete field
ALTER TABLE name drop (field name);
(usually when the system is not busy deleting unused fields, you can first set the field unused:
ALTER TABLE TEST3 set unused column address;
to perform the deletion:
ALTER TABLE test3 unused column;)
8> field rename
ALTER TABLE name rename column field name to new field name;
9> modify field
ALTER TABLE name modify field name new type;
– Add NOT NULL
ALTER TABLE name modify field name NOT NULL
– Delete NOT NULL
ALTER TABLE table name modify field name null;
- Oracle Backup Tables
1> backup within the current database
CREATE TABLE table name (field) as SELECT query statement
Movement of data
Insert into Table name (field list) Select field List from table name
2> copy tables between database servers
Knowledge Point: Client Connection server
Copy from System/[email protected] Create hhl_table using SELECT * from Scott.emp;
A) Copy from a server to your own database
Copy from user name/password @ host string CREATE TABLE name using query statement;
b) Copy from your own database to a server
Copy to user/password @ host string CREATE TABLE name using query statement;
c) Copy table from a server to B server
Copy from user/password @ host string to user/password @ host string CREATE TABLE name using query statement;
- Oracle DBLINK Database connection
In the current database directly manipulate other servers in the table to do additions and deletions, the format is as follows:
Create database link name connect to user name identified by password using ' host string ';
- MySQL View database character set:
Show variables like ' character% ';
Show variables like '%collation% ';
- Truncate and delete
1> using formats
TRUNCATE table name; – Delete all records in table S
Delete from table name;
The difference between 2> truncate and delete
Truncate removal speed faster than delete delete
Truncate cannot be rolled back, and delete can be rolled back.
Oracle and MySQL Knowledge summary
