Shulou(Shulou.com)06/01 Report--

The following brings you about oracle commonly used related sql sentences and mysql commonly used command Daquan content, I believe you must have read similar articles. What's the difference between what we bring to everyone? Let's take a look at the body. I believe you will gain something after reading the sql sentences commonly used in oracle and the Compendium of mysql commands.

1. Related sql sentences commonly used in oracle

1. Connect to the database

Su-oracle-c "sqlsplus user / password Note: sqlplus / as sysdba for first login

Note: close the database: note: shutdown can add the shutdown option, from the mildest to the most violent behavior options are (shutdown, shutdown transactional, shutdown immediate, shutdown abort)

Shutdown: close, wait for each user to exit the system is canceled, exit and shut down the database.

Shutdown transactional: transactional shutdown, waiting for each user to commit the transaction to roll back the current transaction, then oracle cancels the conversation and performs the shutdown after all users exit the system.

Shutdown immediate: close directly, cancel all user conversations (cause fallback), and execute the normal shutdown procedure.

After shutting down the database, you need to turn off listening, lsnrctl stop

two。 View current user

Show user

3. Create a tablespace

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 tablespace

ALTER TABLESPACE tablespace 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 tablespace name temporary tablespace temp

6. Assign permissions

Grant permissions to user

7. Reset password

Alter user user identified by password

8. View the data file in the table space of the current user

Select * from v$datafile

9. View the data file corresponding to the tablespace in which the table is located

Select t1.name from v$tablespace T1 where t1.ts=value vicidatafile T2

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'

twelve。 Know the tablespace and display all the tables included in the tablespace

Select * from all_tables where tablespace_name=' table space name'

13. Know the table name and show the tablespace to which the table belongs

Select tablespace_name,table_name from user_tables where table_name=' table name'

14. Rename tablespace

When the tablespace is ONLINE

ALTER TABLESPACE tablespace_name RENAME TO new_tablespace_name

15. Delete tablespace

DROP TABLESPACE tablespace_name INCLUDING CONTENTS AND DATAFILES

16. View tablespace

SELECT t.tablespace_name, round (SUM (bytes / (1024 * 1024)), 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 file

SELECT NAME FROM v$controlfile

18. View log files

SELECT MEMBER FROM v$logfile

19. View the date and method of database creation

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. Query user session

Select username,serial#,sid from v$session

Alter system kill session 'serial#,sid';-deletes related user sessions

twenty-two。 Query the number of connections in oracle

Select count (*) from v$session

23. Query the number of concurrent connections to oracle

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

24. View the version of oracle

Select banner from sys.v_$version

Mysql common commands

1: use the show statement to find out what databases currently exist on the cloud 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

It indicates that the operation is successful!) 4: view the current function

1: use the show statement to find out what databases currently exist on the CVM:

Mysql > SHOW DATABASES

2:2, create a database MYSQLDATA

Mysql > CREATE DATABASE MYSQLDATA

3: select the database you created

Mysql > USE MYSQLDATA; (if Database changed appears by pressing enter key, 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 (20), sex CHAR (1))

6: display the structure of the table:

Mysql > DESCRIBE MYTABLE

7: add records to the table

Mysql > insert into MYTABLE values ("hyq", "M")

8: load data into database tables in text (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 the table

Mysql > drop TABLE MYTABLE

11: clear the table

Mysql > delete from MYTABLE

12: update data in the table

Mysql > update MYTABLE set sex= "f" where name='hyq'

The following is the management experience of using MySql seen inadvertently on the network.

MySql exists as a service in windows. Before using it, make sure that the service has been started and that the net start mysql command is not available to start it. The "/ etc/rc.d/init.d/mysqld start" command can be used when starting in Linux. Note that the initiator should have administrator privileges.

The newly installed MySql contains a root account with an empty password and an anonymous account, which is a big security risk. For some important applications, we should improve the security as much as possible. Here, delete the anonymous account and set the password for the root account. Use the following command:

Use mysql

Delete from User where User= ""

Update User set Password=PASSWORD ('newpassword') where User='root'

If you want to restrict the login terminal used by the user, you can update the Host field of the corresponding user in the User table, and restart the database service after making the above changes. When logging in, you can use the following command:

Mysql-uroot-p

Mysql-uroot-pnewpassword

Mysql mydb-uroot-p

Mysql mydb-uroot-pnewpassword

The above command parameters are part of the commonly used parameters. Please refer to the documentation for details. Mydb here is the name of the database to be logged in.

In the development and practical application, users should not only use root users to connect to the database, although the use of root users for testing is very convenient, but it will bring major security risks to the system, and is not conducive to the improvement of management skills. We give the most appropriate database permissions to the users used in an application. Such as one who only enters

Users who insert row data should not be given permission to delete data. The user management of MySql is realized through the User table. There are two common ways to add new users: one is to insert the corresponding data row in the User table and set the corresponding permissions at the same time; the other is to create users with certain permissions through the GRANT command. Among them, GRANT

The common uses are as follows:

Grant all on mydb.* to NewUserName@HostName identified by "password"

Grant usage on *. * to NewUserName@HostName identified by "password"

Grant select,insert,update on mydb.* to NewUserName@HostName identified by "password"

Grant update,delete on mydb.TestTable to NewUserName@HostName identified by "password"

To give this user the administrative ability to give him permissions on the corresponding object, add the WITH GRANT OPTION option after GRANT. For users who are added by inserting the User table, the Password field is updated and encrypted using the PASSWORD function to prevent unscrupulous people from reading the password. Should be given to those users who are no longer in use

To be cleared, users whose permissions are out of bounds should reclaim their permissions in a timely manner, which can be done by updating the corresponding fields in the User table or by using REVOKE operation.

The following is an explanation of the common permissions I have obtained from other materials (www.cn-java.com):

Global administrative permissions:

FILE: read and write files on MySQL CVM.

PROCESS: displays or kills service threads that belong to other users.

RELOAD: reload access control tables, refresh logs, etc.

SHUTDOWN: turn off the MySQL service.

Database / data Table / data column permissions:

ALTER: modify existing data tables (such as adding / deleting columns) and indexes.

CREATE: create a new database or data table.

DELETE: deletes the record of the table.

DROP: delete a data table or database.

INDEX: create or delete an index.

INSERT: add the record of the table.

SELECT: show / search the records of the table.

UPDATE: modifies records that already exist in the table.

Special permissions:

ALL: allow to do anything (like root).

USAGE: only login is allowed-nothing else is allowed.

For the above about oracle commonly used related sql sentences and mysql commonly used command Daquan, do you think it is what you want? If you want to know more about it, you can continue to follow our industry information section.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.