This is an article comparing database programming languages.
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 | | | | | | | | |
| 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); | 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.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
