sql_syntax_reference

SQL Syntax Reference

This will cover some general SQL syntax for Oracle, MS-SQL, and MYSQL.

Requirement ASCII
Oracle: ASCII('A') is equal to 65
MS-SQL: ASCII('A') is equal to 65
MYSQL: ASCII('A') is equal to 65
Requirement SUBSTRING
Oracle: SUBSTR('ABCDE',2,3) is equal to BCD
MS-SQL: SUBSTR('ABCDE',2,3) is equal to BCD
MYSQL: SUBSTR('ABCDE',2,3) is equal to BCD
Requirement Retrieve current database user
Oracle: SELECT Sys.login_user from dual
Oracle: SELECT user from dual
Oracle: SYS CONTEXT('USERENV', 'SESSION USER')
MS-SQL: select user
MS-SQL: select suser sname()
MYSQL: SELECT user()
Requirement Cause a time delay
Oracle: Utl_Http.request('http://madeupserver.com')
MS-SQL: waitfor delay '0:0:10'
MS-SQL: exec master..xp_cmdshell 'ping localhost'
MYSQL: benchmark(50000, sha1('test')
Requirement Retrieve database version string
Oracle: select banner from v$version
MS-SQL: select @@version
MYSQL: select @@version
Requirement Retrieve current database
Oracle: SYS_CONTEXT('USERENV', 'DB_NAME')
MS-SQL: select db_name()
MS-SQL: The server name can be retrieved using: select @@servername
MYSQL: select database()
Requirement Retrieve current user's privileges
Oracle: select * from session_privs
MS-SQL: select grantee, table_name, privilege_type from INFORMATION SCHEMA.TABLE PRIVILEGES
MYSQL: SHOW GRANTS FOR CURRENT_USER()
Requirement Show user objects
Oracle: SELECT object_name, object_type from user_objects
MS-SQL: SELECT * from sysobjects
MYSQL: There is no database metadata in MySQL
Requirement Show user tables
Oracle: select object_name, object_type, from user_objects WHERE object_type='TABLE'
Oracle: To show all tables to which the user has access: SELECT table FROM all tables
MS-SQL: SELECT * FROM sysobjects WHERE xtype='U'
MYSQL: There is no database metadata in MySQL
Requirement Show column names for table foo
Oracle: SELECT column_name, Name from user_tab_columns where table_name = 'FOO' Use the ALL_TAB_COLUMNS table if the target data is not owned by the current application user.
MS-SQL: SELECT syscolumns.* FROM syscolumns JOIN sysobjects ON syscolumns.id=sysobjects.id WHERE sysobjects.name='FOO'
MYSQL: show columns from foo
Requirement Interact with the operating system (simplest ways)
Oracle: See the Oracle Hacker's Handbook, by David Litchfield
MS-SQL: exec xp_cmdshell 'dir c:\'
MYSQL: select load_file('/etc/passwd')
Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-Share Alike 2.5 License.