Oracle MySQL Statement

First, Oracle common related SQL statements

1. Connect to the database
Su-oracle-c "Sqlsplus User/Password Note: First time login with Sqlplus/as SYSDBA

Note: Close the database: Note: Shutdown can be turned off option, from the mildest to the most brutal behavior options (shutdown, shutdown transactional, shutdown immediate, shutdown abort)
Shutdown: Shut down, wait for each user to exit the system war be canceled after exiting to close the database.
Shutdown transactional: Transactional shutdown, waiting for each user to submit a war fallback to the current transaction, and Oracle cancels the conversation and shuts down after all users have exited the system.
Shutdown immediate: Directly close, cancel all user conversations (prompting fallback), and perform a normal shutdown program.
After you close the database, you need to turn off monitoring, lsnrctl stop

2. View Current User

Show user;

3. Create a table space
Create tablespace table name datafile '/db/oracle11g/oradata/table name/table name 01.dbf ' size 500m autoextend on next 500m
MaxSize 31g Logging Online permanent extent management local;

4. Add Table Space
ALTER tablespace table space name ADD datafile '/db/oracle11g/oradata/table name/table name 02.dbf ' SIZE 500m autoextend on NEXT 500m
MAXSIZE 31g;

5. Create a user
Create user username identified by password default Tablespace table space name temporary tablespace temp;

6. Assigning Permissions

Grant permissions to the user;

7. Reset Password
Alter user identified by password;

8. View the data file for the current user's tablespace

SELECT * FROM V$datafile

9. View the data file corresponding to the table space where the table resides

Select T1.name from V$tablespace t1,v$datafile T2 where T1.ts=value

10. View the table space name of the current user

SELECT * from V$tablespace;

11. See what role a user has?

SELECT * from Dba_role_privs where grantee= ' SYS '

12. Know the tablespace, showing all tables included in the table space;
SELECT * from all_tables where tablespace_name= ' table space name '

13. Know the table name and show the table space that the table belongs to
Select Tablespace_name,table_name from user_tables where table_name= ' table name '

14. Renaming table Spaces
In the case of a table space of online
ALTER tablespace tablespace_name RENAME to New_tablespace_name;

15. Delete Table spaces

DROP tablespace tablespace_name including CONTENTS and datafiles

16. View Table Space

SELECT T.tablespace_name, round (SUM (Bytes/(1024x768)), 0) ts_size
From Dba_tablespaces T, Dba_data_files D
WHERE T.tablespace_name = D.tablespace_name
GROUP by T.tablespace_name;

17. View Control Files

SELECT NAME from V$controlfile;

18. View Log files

SELECT MEMBER from V$logfile;

19. View the date and manner in which the database was created

SELECT created, Log_mode, log_mode from V$database;

20. View the number of hosts currently connected to the database:

Col Machine for A20
Set Linesize 150
Select distinct machine,username from V$session order by Username,machine;

21. Querying User Sessions

Select Username,serial#,sid from V$session;
Alter system kill session ' Serial#,sid '; --Delete related user sessions

22. Query the number of connections to Oracle

Select COUNT (*) from v$session

23. Querying the number of concurrent connections for Oracle

Select COUNT (*) from v$session where status= ' ACTIVE ';

24. View versions of Oracle

Select banner from Sys.v_$version;




MySQL Common commands
1: Use the SHOW statement to find out what database currently exists on the server: MySQL show DATABASES; 2:2, create a database mysqldata mysql create databases mysqldata; 3: Select the database you created MySQL use Mysqldata; (press ENTER to appear when database changed

Description Operation Successful! ) 4: View the current number


1: Use the show statement to find out what database currently exists on the server:
Mysql> SHOW DATABASES;
2:2. Create a database Mysqldata
mysql> CREATE DATABASE Mysqldata;
3: Select the database you created
mysql> use Mysqldata; (press ENTER to appear database changed the operation is successful!) )
4: See what tables exist in the current database
Mysql> SHOW TABLES;
5: Create a database table
Mysql> CREATE TABLE MYTABLE (name VARCHAR), sex CHAR (1));
6: Show the structure of the table:
Mysql> DESCRIBE MYTABLE;
7: Add a record to the table
mysql> INSERT INTO MYTABLE values ("HyQ", "M");
8: Loading data into a database table in text mode (for example, D:/mysql.txt)
mysql> LOAD DATA LOCAL INFILE "D:/mysql.txt" into TABLE MYTABLE;
9: Import. sql File command (for example, D:/mysql.sql)
Mysql>use database;
Mysql>source D:/mysql.sql;
10: Delete Table
Mysql>drop TABLE MYTABLE;
11: Clear the table
Mysql>delete from MYTABLE;
12: Update data in table
Mysql>update MYTABLE set sex= "F" where name= ' HyQ ';

Here are some of the management tips that you can inadvertently see on the web using MySQL,
In Windows, MySQL exists as a service and you should ensure that the service is started before use, and that the available net start MySQL command is not started. While Linux starts with the "/etc/rc.d/init.d/mysqld start" command, note that the initiator should have administrator privileges.
The newly installed MySQL contains a root account with a blank password and an anonymous account, which is a great security risk, for some important applications we should improve security as far as possible, the anonymous account should be deleted, the root account password, the following commands can be used:
Use MySQL;
Delete from User where user= "";
Update User set Password=password (' NewPassword ') where user= ' root ';
If you want to restrict the logon terminal used by users, you can update the user's host field in the user table, and you should restart the database service when you make the above changes, and you will be able to log in with a command like this:
Mysql-uroot-p;
Mysql-uroot-pnewpassword;
MySQL mydb-uroot-p;
MySQL Mydb-uroot-pnewpassword;
The above command parameters are part of the common parameters, which can be referenced in detail in the documentation. The mydb here is the name of the database to log in to.
In the development and the actual application, the user should not only use the root user to connect the database, although uses the root user to carry on the test to be convenient, but will bring the system the significant security hidden danger, also is not advantageous to the management technology enhancement. We give the most appropriate database permissions to the users used in an application. such as a only in

The user who inserted the row data should not be given permission to delete the data. The user management of MySQL is implemented through the users table, there are two common methods for adding new users, one is to insert the corresponding data row in the user table, set the appropriate permissions, and the other is to create a user with some kind of permission through the grant command. where Grant

The common usage of this is as follows:
Grant all on mydb.* to [e-mail protected] identified by "password";
Grant Usage on * * to [e-mail protected] identified by "password";
Grant Select,insert,update on mydb.* to [e-mail protected] identified by "password";
Grant Update,delete on MyDB. TestTable to [e-mail protected] identified by "password";
To give this user the ability to manage the permissions on the object, you can add the WITH GRANT option after Grant. For users added with the Insert User table, the password field applies the password function to update the encryption to prevent the malicious person from stealing the password. For those who have not used the user should give

To be cleared, the user who has permission to pass through the bounds should reclaim the permission in time, and reclaim the permission by updating the user table corresponding field or using the revoke operation.
The following is an explanation of the common permissions I have obtained from other sources (www.cn-java.com):
Global Administrative permissions:
File: Read and write files on the MySQL server.
PROCESS: Displays or kills service threads belonging to other users.
RELOAD: Overloads the Access Control table, refreshes the log, and so on.
SHUTDOWN: Turn off the MySQL service.
Database/data Table/Data column permissions:
Alter: Modifies an existing data table (for example, add/Remove Columns) and index.
Create: Create a new database or data table.
Delete: Deletes the record for the table.
Drop: Deletes a data table or database.
Index: Establish or delete the indexes.
INSERT: Adds a table record.
SELECT: Displays/searches the table's records.
UPDATE: Modifies a record that already exists in the table.
Special permissions:
All: Allow to do anything (as root).
USAGE: Allow login only – nothing else is allowed.

This article is from the "lake and Laughter" blog, please make sure to keep this source http://hashlinux.blog.51cto.com/9647696/1793751

Oracle MySQL Statement