Comparison of database access

From HandWiki
The printable version is no longer supported and may have rendering errors. Please update your browser bookmarks and please use the default browser print function instead.
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"