Comparison of database access

From HandWiki
Short description: None
Programming language comparisons

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

Filtering expressions

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

Managing users and permissions

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





Retrieved from "https://handwiki.org/wiki/index.php?title=Comparison_of_database_access&oldid=3371562"