c3d2-wiki/SQL.mw

[[Kategorie:Wissen]][[Kategorie:SQL]]

=Funktionen und Operatoren=

==ASCII-Code -> String==

{| border="1" cellspacing="0" cellpadding="3" style="border-collapse: collapse;"
|---- bgcolor="lightblue"
!
!Befehl
!Ergebnis
|-
| Standard
| chr(65)
| 'A'
|-
| MySQL
| char(65)
| 'A'
|}

==String -> ASCII-Code==

{| border="1" cellspacing="0" cellpadding="3" style="border-collapse: collapse;"
|---- bgcolor="lightblue"
!
!Befehl
!Ergebnis
|-
| Standard
| ascii('A')
| 65
|}

==Text verketten==

{| border="1" cellspacing="0" cellpadding="3" style="border-collapse: collapse;"
|---- bgcolor="lightblue"
!
!Befehl
!Ergebnis
|-
| Standard
| 'chunky' || 'bacon'
| 'chunkybacon'
|-
| MySQL
| concat('chunky','bacon')
| 'chunkybacon'
|}

==Text ersetzen==

{| border="1" cellspacing="0" cellpadding="3" style="border-collapse: collapse;"
|---- bgcolor="lightblue"
!
!Befehl
!Ergebnis
|-
| Standard
| replace('funky bacon','funky','chunky')
| 'chunky bacon'
|}

=Meta-Daten abfragen=

==Datenbanken==

{| border="1" cellspacing="0" cellpadding="3" style="border-collapse: collapse;"
|---- bgcolor="lightblue"
!Beschreibung
!Befehl
|-
| MySQL
| SHOW DATABASES;
|-
| PostgreSQL
| SELECT * FROM pg_database;
|-
| SQLite
| PRAGMA database_list;
|}

==Tabellen==

{| border="1" cellspacing="0" cellpadding="3" style="border-collapse: collapse;"
|---- bgcolor="lightblue"
!Beschreibung
!Befehl
|-
| Standard ||
* SELECT * FROM information_schema.tables;
|-
| Firebird ||
* SELECT RDB$RELATION_NAME FROM RDB$RELATIONS;
|-
| MySQL (unterstützt Standard ab 5.0) ||
* SHOW TABLES;
|-
| SQLite ||
* SELECT * FROM SQLite_Master;
|-
| PostgreSQL (unterstützt Standard seit 7.4) ||
* SELECT * FROM pg_tables;
|-
| Oracle ||
* SELECT * FROM ALL_TABLES;
|}

==Spalten einer Tabelle==

{| border="1" cellspacing="0" cellpadding="3" style="border-collapse: collapse;"
|---- bgcolor="lightblue"
!Beschreibung
!Befehl
|-
| Standard ||
* SELECT * FROM information_schema.columns WHERE table_name = '<table_name>';
|-
| Firebird ||
*SELECT RDB$FIELD_NAME FROM RDB$RELATION_FIELDS WHERE RDB$RELATION_NAME='<table_name>'; 
|-
| MySQL ||
*SHOW COLUMNS FROM <table>;
*DESCRIBE <table>;
|-
| SQLite ||
*PRAGMA table_info(<table>);
|-
| Oracle ||
*DESCRIBE <table>;
|}

==User==

{| border="1" cellspacing="0" cellpadding="3" style="border-collapse: collapse;"
|---- bgcolor="lightblue"
!Beschreibung
!Befehl
|-
| Standard
| SELECT * FROM information_schema.enabled_roles;
|-
| Firebird
| SELECT DISTINCT RDB$USER FROM RDB$USER_PRIVILEGES;
|-
| MySQL
| SELECT * FROM mysql.user;
|-
| PostgreSQL
(unterstützt außerdem Standard)
| SELECT * FROM pg_user;
|-
| Oracle
| SELECT * FROM ALL_USERS;
|}

==User-Rechte==

{| border="1" cellspacing="0" cellpadding="3" style="border-collapse: collapse;"
|---- bgcolor="lightblue"
!Beschreibung
!Befehl
|-
| Standard || 
* SELECT * FROM information_schema.role_table_grants;
* SELECT * FROM information_schema.role_column_grants;
* SELECT * FROM information_schema.role_routine_grants;
* SELECT * FROM information_schema.role_usage_grants;
|-
| Firebird ||
* SELECT * FROM RDB$USER_PRIVILEGES;
|-
| MySQL ||
* SHOW GRANTS;
|}

==Prozeduren==

{| border="1" cellspacing="0" cellpadding="3" style="border-collapse: collapse;"
|---- bgcolor="lightblue"
!Beschreibung
!Befehl
|-
| Standard 
| SELECT * FROM information_schema.routines;
|-
| Firebird
| SELECT * FROM RDB$PROCEDURES;
|-
| Oracle
| select * from user_objects where object_type = 'PROCEDURES';
|}

==Version==
{| border="1" cellspacing="0" cellpadding="3" style="border-collapse: collapse;"
|---- bgcolor="lightblue"
!Beschreibung
!Befehl
|-
| MS SQL
| SELECT @@VERSION;
|-
| MySQL
| SELECT version();
SHOW VARIABLES LIKE 'version%';
|-
| PostgreSQL
| SELECT version();
|-
| Oracle
| SELECT * FROM V$VERSION;
|}

=Dokumentation=
*[http://www.firebirdsql.org/ Firebird]
**[http://www.ibphoenix.com/main.nfs?a=ibphoenix&s=1155387578:153600&page=ibp_60_sqlref Referenz-Dokumentation]

*[http://www-306.ibm.com/software/data/db2/ IBM DB2]
**[http://www-306.ibm.com/software/data/db2/udb/support/manualsv9.html IBM DB2 9 Manuals]

*[http://www.mimer.com/ Mimer SQL]
**[http://developer.mimer.com/documentation/html_92/Mimer_SQL_Engine_DocSet/Mimer_SQL_Engine.htm Mimer SQL 9.2]

*[http://www.microsoft.com/sql/default.mspx MSSQL]
**[http://msdn.microsoft.com/library/en-us/tsqlref/ts_tsqlcon_6lyk.asp SQL Server 2000]
**[http://msdn2.microsoft.com/en-us/library/ms189826.aspx SQL Server 2005]

*[http://www.mysql.com/products/database/ MySQL]
**[http://dev.mysql.com/doc/refman/4.1/en/sql-syntax.html MySQL 3.23, 4.0, 4.1], [http://dev.mysql.com/doc/refman/5.0/en/sql-syntax.html MySQL 5.0], [http://dev.mysql.com/doc/refman/5.1/en/sql-syntax.html MySQL 5.1], [http://dev.mysql.com/doc/refman/5.1/en/functions.html Funktions-Referenz]

*[http://www.oracle.com/database/index.html Oracle]
**[http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14200/toc.htm Oracle 10.2]

*[http://www.postgresql.org PostgreSQL]
**[http://www.postgresql.org/docs/7.4/static/sql-commands.html PostgreSQL 7.4], [http://www.postgresql.org/docs/8.0/static/sql-commands.html PostgreSQL 8.0], [http://www.postgresql.org/docs/8.1/static/sql-commands.html PostgreSQL 8.1], [http://www.postgresql.org/docs/8.2/static/sql-commands.html PostgreSQL 8.2],
[http://www.postgresql.org/docs/8.3/static/sql-commands.html PostgreSQL 8.3]

*[http://www.sqlite.org/ SQLite]
**[http://www.sqlite.org/lang.html Referenz-Dokumentation], [http://www.sqlite.org/pragma.html Pragma-Dokumentation]

*[http://www.sybase.com/products/informationmanagement Sybase]
**[http://infocenter.sybase.com/help/index.jsp Referenz-Dokumentation]