Oracle and MySql knowledge Summary

Oracle and MySql knowledge Summary
How SQL plus connects to the database
1> enter sqlplus username/password @ database instance in cmd
For example, sqlplus system/lxr316 @ oracle,
Sqlplus sys/admin as sysdba
2> open sqlplus and enter the user name and password.
3> run the following command: connect sys/admin as sysdba,
Connect system/lxr316
4> super Administrator Logon: sys as sysdba
Disconnect the database: disconn (ect); MySql connection
Enter mysql [-h server address]-u username-p [Password] in cmd (BIND mysql database to environment variable must be configured)
For example, mysql-hlocalhost-uroot-proot
Mysql-uroot-p user operation
Oracle:
1> create a user
Create user name identified by password;
2> Change User Password
Alter user Username identified by new password;
3> account locking and unlocking
Alter user Username account lock | unlock;
4> when you log on for the first time, change the password directly-the password is invalid.
Alter user Username password expire;
MySql:
MySql:
1> create a user:
Create user 'username' @ 'host' identified by 'Password ';
Username-the username you will create, host-specifies the host on which the user can log on. If a local user can use localhost, if you want the user to log on from any remote host, wildcard characters % can be used. password-the user's login password. The password can be blank. If it is blank, the user can log on to the server without the password.
For example:
Create user 'dog '@ 'localhost' identified by '201312 ';
Create user 'pig' @ '192. 168.1.101 _ 'idendified BY '123 ';
Create user 'pig' @ '%' identified by '000000 ';
Create user 'pig' @ '%' identified ";
Create user 'pig' @ '% ';
2> set and change the User Password
Set password for 'username' @ 'host' = PASSWORD ('newpassword ');
For the current Login User, use: set password = PASSWORD ("newpassword ");
Example: set password for 'pig' @ '%' = PASSWORD ("123456 ");
3> delete a user
Drop user 'username' @ 'host'; permission management
Oracle:
1> authorization
Grant permission to user name;
Grant all on table name to user/role
2> revoke permissions
Revoke permission from user name;
Object permissions can be cascaded, but system permissions cannot be cascaded.
(1) system permission: A grants A system permission to B, and B grants C. Then A revokes the permission from B. After that, C still has this permission.
(2) object permission: A grants an object permission to B, and B grants C. Then A revokes the permission from B. After that, C also loses the permission.
3> common system Permissions
Create session-User Logon
Create table-create a table
Unlimited tablespace-unlimited tablespace
4> object permissions
Select on table name
Update on table name
Delete on table name
Insert on table name
5> cascading permission granting
System permission with admin option
Object permission with grant option
Grant permission to user name with admin option | with grant option;
MySql:
1> authorization:
GRANT privileges ON databasename. tablename TO 'username' @ 'host'
Privileges-user operation permissions, such as SELECT, INSERT, and UPDATE. use ALL .; databasename-Database Name, tablename-table name, available if you want to grant the user the corresponding operation permissions on all databases and tablesIndicates, such.*.
Example:
Grant select, insert on test. user TO 'pig' @ '% ';
GRANT ALL ON.TO 'pig' @ '% ';
Note: The user authorized with the preceding command cannot authorize other users. to authorize the user, run the following command:
GRANT privileges ON databasename. tablename TO 'username' @ 'host' with grant option;
2> revoke User Permissions
REVOKE privilege ON databasename. tablename FROM 'username' @ 'host ';
Note: privilege, databasename, tablename-same as the authorization section.
Example: REVOKE SELECT ON.FROM 'pig' @ '% ';
Note: If you authorize the user 'pig' @ '%' like this (or similar): grant select on test. user TO 'pig' @ '%', use REVOKE SELECT ON.FROM 'pig' @ '%'; the command does not allow the user to revoke the SELECT Operation ON the user table in the test database. ON the contrary, if the GRANT SELECT ON.TO 'pig' @ '%'; then revoke select on test. user FROM 'pig' @ '%'; the command cannot revoke this user's Select permission on the user table in the test database.
FOR more information, run the show grants for 'pig' @ '%' command. Oracle role
Role: a set of Permissions
View the role user_role_privs of the current user
View the role's system permissions role_sys_privs
View the role's object permissions role_tab_privs
1> Create a role
Create role role1;
2> authorize a role
Grant create any table, create procedure to role1;
3> grant/Revoke User Roles
Grant role1 to user1;
Revoke role1 from user1;
4> Create a role with a password (a password must be provided when a role with a password takes effect)
Create role role1 identified by password1;
5> modify role: Password required
Alter role role1 not identified;
Alter role role1 identified by password1;
6> set the role to take effect for the current user
Set role role1; // enable role1
Set role role1, role2; // make role1 and role2 take effect
// Use role1 with a password
Set role role1 identified by password1;
Set role all; // all roles used by this user take effect
Set role none; // set that all roles are invalid.
// All roles of this user except role1 take effect.
Set role all rule t role1;
Select * from SESSION_ROLES; // view the role of the current user.
7> modify a specified user and set its default role
Alter user user1 default role role1;
Alter user user1 default role all role t role1;
8> delete a role
Drop role role1;
After a role is deleted, the user who previously used this role no longer has this role, and the corresponding permissions are lost.
9> system roles
DBA: Has all privileges and is the highest privilege role in the system;
RESOURCE: users with Resource role permissions can only create objects;
CONNECT: users with the Connect role permission can only log on to Oracle;
For normal users: grant the connect and resource roles permissions.
For DBA management users: grant the connect, resource, and dba role permissions. Command for viewing table structure information
Oracle:
Desc (ribe) Table Name: desc lxr
MySql:
Desc (ribe) Table Name; displays the current logon User:
Oracle: show user
MySql: select user (); MySql: show databases;
View Database: show databases;
View table: show tables; Oracle user lock and unlock
Alter user username account lock;-LOCK a USER
Alter user username account unlock;-unlock user Sqlplus clear screen command
Clear screen short for: cl scr (dos clear screen command: cls) Oracle Change current user Date Format command
Alter session set nls_date_format = 'yyyy-mm-dd'; Basic Data Type in Oracle
Number (5, 2), int: 999.99
Char, varchar2 (4000)
Date
Image BLOB (binaryLargeObject) 4G
Text CLOB (Character LargeObject) 4GOracle table structure operation
1> rename a table
Alter table name rename to new table name;
2> Add comments to the table
Comment on table name is 'comment content ';
3> Add Constraints
A) alter table name add constraint name constraint type (field name of the constraint );
Constraint type:
Primary key
Unique
Check restrictions
Not null cannot be null
B) alter table name add constraint name foreign key (field name) references table name (field name );
Foreign key
You can insert duplicate records or duplicate null values by referring to the values in the primary key.
4> Delete Constraints
Alter table Name drop constraint name;
5> Delete table structure
Drop table name. (This operation is DDL and will be automatically submitted and cannot be rolled back)
6> Add fields to the table
Alter table name add field name type;
7> delete a field
Alter table Name drop (field name );
(When the system is not busy, delete unused fields. You can set the field to unused first:
Alter table test3 set unused column address;
Then execute delete:
Alter table test3 unused column ;)
8> rename a field
Alter table name rename column field name to new field name;
9> modify Fields
Alter table name modify Field name new type;
-Add not null
Alter table name modify Field name not null
-Delete not null
Alter table name modify Field name null; Oracle backup table
1> back up data in the current database
Create table Name (field) as select query statement
Data Movement
Insert into Table Name (Field List) select field list from Table Name
2> copy tables between database servers
Knowledge Point: connect the client to the server
Copy from system/hhl @ hhl create hhl_table using select * from scott. emp;
A) copy data from server A to your database
Copy from username/password @ host string create TABLE name using query statement;
B) copy data from your database to server
Copy to user/password @ host string create TABLE name using query statement;
C. Copy the table from server A to server B.
Copy from user/password @ host string to user/password @ host string create TABLE name using query statement; Oracle DBLINK database connection
Directly operate tables on other servers in the current database for addition, deletion, modification, and query. 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> Format
Truncate table name;-delete all records in the table s
Delete from table name;
2> Differences Between truncate and delete
Truncate is faster than delete.
Truncate cannot be rolled back, and delete can be rolled back.