Comparison of database access

From Wikipedia, the free encyclopedia
Jump to: navigation, search

A comprehensive comparison of current:database Management Systems, and corresponding Programming Languages;

              including: Command, Semantic, and Syntax references. 

Database input commands[edit]

Database and table management[edit]

Database Create database Delete database List databases Use a db Create table Delete table List tables List table fields
MySQL create database dbname; drop database dbname; show databases; use dbname; create table tbname; drop table tbname; show tables; describe tbname;
PostgreSQL create database dbname; drop database dbname; \l (in psql) \c dbname (in psql)
For schemas (a similar feature):
set search_path = schema_name;
create table tbname (field1 value_type1, field2 value_type2, ... ); drop table tbname; \d (in psql) \d tbname (in psql)
Sqlite ver 3 ? ? ? $ sqlite3 filename create table tbname (field1 value_type1, field2 value_type2, ... ) drop table tbname .tables .schema

Using data in a table[edit]

Database show all data in all fields show all data in some fields show filtered data in all fields
MySQL select * from "tbname"; select "fields" from "tbname"; select * from "tbname" where "filter_expr";

Filtering expressions[edit]

Altering tables[edit]

Database Add new column Delete column Change column Add element Delete element
MySQL
alter table "tbname"
add column "column_name";
alter table "tbname"
drop column "column_name";
alter table "tbname"
change "old" "new" "newtype";
insert into "tbname" ("fields")
values ("values");
delete from "tbname"
where "expr";

Managing users and permissions[edit]

MySQL databases[edit]

Prog Language Access server Use database List databases List tables
raw MySQL access use db; show databases; show tables;
PHP $link = mysql_connect(host, user, password);

or

$db = new mysqli(host, user, password);

or

$db = new PDO('mysql:host=hostname;dbname=dbname', 'username', 'password');

mysql_select_db(''name'', $link);

or

$db = new mysqli(host, user, password, ''name'');

or

$db->select_db(''name'');

Python
db = MySQLdb.connect(
      host="hostname",
      user="username",
      passwd="password",
      db="dbname",
      connect_timeout=5)
import MySQLdb
c = db.cursor()
c.execute("show databases")
c.fetchall()
c = db.cursor()
c.execute("show tables")
c.fetchall()

Reading from tables[edit]

Prog Language Describe List table content Search
raw MySQL access describe tablename; select field from tablename;
PHP

Databases in Object-Relational mapping systems[edit]

There are ORMs to manage a database:

Defining the object[edit]

Django[edit]

This class represents a row of a table named "Table"

class Table(models.Model):
     field1_name = models."field_name"()
     field2_name = models."field_name"()
 
     # More explicit examples
     integer_field = models.IntegerField()
     char_field = models.CharField(max_length=1000)

Using a table[edit]

Read value Change value Search Inserting
django
table_var."field_name"
table_var."field_name" = \
"value"
table_var = \
Table.objects.filter( "expr" )
table_var = Table()
table_var.save()

See also[edit]