Comparison of database access

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:

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


This article is issued from Wikipedia - version of the Tuesday, November 24, 2015. The text is available under the Creative Commons Attribution/Share Alike but additional terms may apply for the media files.