SQL

From C3D2
Revision as of 16:27, 3 May 2010 by Sven (Talk | contribs)

(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to: navigation, search


Contents

Funktionen und Operatoren

ASCII-Code -> String

Befehl Ergebnis
Standard chr(65) 'A'
MySQL char(65) 'A'

String -> ASCII-Code

Befehl Ergebnis
Standard ascii('A') 65

Text verketten

Befehl Ergebnis
Standard 'chunky' || 'bacon' 'chunkybacon'
MySQL concat('chunky','bacon') 'chunkybacon'

Text ersetzen

Befehl Ergebnis
Standard replace('funky bacon','funky','chunky') 'chunky bacon'

Meta-Daten abfragen

Datenbanken

Beschreibung Befehl
MySQL SHOW DATABASES;
PostgreSQL SELECT * FROM pg_database;
SQLite PRAGMA database_list;

Tabellen

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

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

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

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

Beschreibung Befehl
Standard SELECT * FROM information_schema.routines;
Firebird SELECT * FROM RDB$PROCEDURES;
Oracle select * from user_objects where object_type = 'PROCEDURES';

Version

Beschreibung Befehl
MS SQL SELECT @@VERSION;
MySQL SELECT version();

SHOW VARIABLES LIKE 'version%';

PostgreSQL SELECT version();
Oracle SELECT * FROM V$VERSION;

Dokumentation

PostgreSQL 8.3

Personal tools
Namespaces

Variants
Actions
Navigation
Tools