Comparison of database access

Short description: None

Database input commands

Database and table management

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

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";

Altering tables

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";

MySQL databases

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

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

Databases in object-relational mapping systems

There are ORMs to manage a database:

• django (web framework)
• SQLObject
• others

Defining the object

Django

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

	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

• ODBC
• JDBC
• MySQL
• PostgreSQL