SQL Database Syntax

From Colettapedia
Jump to: navigation, search

Nice MediaWiki Commands

  • See all pages - select page_id, page_title, page_latest, page_touched from mw_page order by page_touched;
  • Get one page - select page_id, page_title, page_latest, page_touched from mw_page where page_title='Maryland_Safe_Boating_Course' order by page_touched;
  • Get latest revision of page - select rev_text_id from mw_revision where rev_id=4053;
  • Get text of latest revision - select old_text from mw_text where old_id=4021;

list of keywords not in sqlite3

  • show
  • databases
  • can't create table without specifying schema at creation time

Postgres bash shell commands

# Start the PostgreSQL server.
initdb /usr/local/var/postgres
brew services start postgresql
pg_ctl -D /usr/local/var/postgres/ -l /usr/local/var/postgres/server.log start

#Create a user, a database and add the PL/pgSQL language to your database.

createuser -P -D -R -S db_user
Enter password for new role:       # db_password
Enter it again:       # db_password
createdb -O db_user omero_database
createlang plpgsql omero_database
dropdb omero_database


Getting Started

  • SHOW DATABASES;
  • CREATE DATABASE database-name;
  • CREATE DATABASE IF NOT EXISTS database-name;
  • DROP DATABASE database-name;
  • DROP DATABASE IF EXISTS database-name;

Creating Database Tables

  • USE database-name;
  • SHOW TABLES;
  • EXPLAIN table-name;
  • CREATE TABLE table-name;
    • In SQLite, you have to specify data types, as in the next item below.
  • CREATE TABLE IF NOT EXISTS table-name( id INT, name TEXT, color TEXT );
  • DROP TABLE table-name;
  • DROP TABLE IF EXISTS table-name1, table-name2;
  • Drop all tables in this database whose name begins with a certain prefix:
    • Have MySQL generate the statement for you SELECT CONCAT( 'DROP TABLE ', GROUP_CONCAT(table_name) , ';' ) AS statement FROM information_schema.tables WHERE table_name LIKE 'myprefix_%';
  • some data types:
INT integer on order of 2 billion
DECIMAL() DECIMAL(3,2) specifies range of values of -999.99 to 999.99
DATE YYY-MM-DD
TIME HH:MM:SS
DATETIME YYY-MM-DD HH:MM:SS
YEAR 1901-2155, either YY or YYYY
TIMESTAMP Automatic Date and Time of last record entry
CHAR() A string of defined fixed length up to 255 characters long. CHAR(100) pads a smaller string to make it 100 characters long.
VARCHAR() Same as CHAR w/o padding, VARCHAR(50) specifies maximum length of 50 characters.
varchar (SQLite) SQLite seems not to care about varchar lengths, it'll hold the string however long it is no matter how you specified the length should be.
TEXT string up to 65,535 chars long
BLOB A binary type for variable data
ENUM A single string value from a defined list. For Example, ENUM("red", "green", "blue") allows any one of these three colors only.
SET Same as ENUM except allows other entries
  • Table field modifiers
NOT NULL Insists that each record must include a data value in this column.
UNIQUE Insists that records may not duplicate any entry in this column.
AUTO_INCREMENT Available for numeric columns, to automatically generate a number that is one more than the previous value in that column.
DEFAULT Specifies a value to be used where no value is stated for this column when a record is inserted.
PRIMARY KEY Specifies Primary Key
  • ALTER TABLE table-name ADD COLUMN name data-type optional-modifiers;
  • ALTER TABLE table-name ADD PRIMARY KEY( column-name );
  • ALTER TABLE table-name CHANGE old-column-name new-column-name data-type optional-modifiers;
  • ALTER TABLE table-name DROP COLUMN column-name;

Inserting Data Into Tables

  • INSERT INTO table-name VALUES ( val-for-col-1, ... , val-for-col-n );
  • SELECT * FROM table-name; /* Select all the data in all the columns in table-name */
  • INSERT INTO table-name ( column-n, column-m ) VALUES ( val-n, val-m ); /* to allow for default functionality to take over */
  • INSERT INTO destination-table ( column-m, ... , column-n ) SELECT * FROM source-table;
  • UPDATE table-name SET column-name = value;
  • UPDATE table-name SET column-name = value WHERE other-column-name = other-value;
  • DELETE FROM table-name; /* Delete all rows */
  • DELETE FROM table-name WHERE column = value;

Retrieving Data From Tables

  • SELECT column-name FROM table-name /* retrieve a column */
  • SELECT column-name1, column-name2, column-name3 FROM table-name; /* retrieve multiple columns */
  • SELECT * FROM table-name WHERE column-name = value; /* retrieve a row */
  • SQLITE: select * from table-name order by column-name LIMIT 1 OFFSET 2;
    • return only one match, the third one, since you've offset 2.
  • Copying retrieved data
 CREATE TABLE IF NOT EXISTS 800w_microwaves SELECT * FROM microwaves WHERE power = 800;
  • INSERT INTO destination-table ( column, column ) SELECT column, column FROM source-table WHERE column = value; /* Inserting multiple selected fields */
INSERT INTO sharp_ovens ( serial_no, power ) SELECT model, power FROM microwaves WHERE maker = "Sharp";

Sorting Retrieved Data

  • SELECT * FROM animals ORDER BY id;
  • SELECT name FROM animals ORDER BY name;
  • Sort by multiple columns
 SELECT first_name, last_name FROM employees ORDER BY last_name, first_name;
  • Sort by column position of retrieved data, not actual table position.
SELECT model, style, price FROM watches ORDER BY 3;
  • Setting the sort direction
 SELECT * FROM top_5_films ORDER BY position DESC;
 SELECT * FROM top_5_films ORDER BY year ASC;

Data Filtering

  • Let the DBMS system filter the results rather than the client application because that's what the DBMS is optimized for.
  • operators: =, !=, <, >, <=, >=, BETWEEN min AND max, IS NULL, IS NOT NULL
  • you can use AND, OR
  • specifying multiple
SELECT * FROM coffee_makers WHERE make IN ("Starbucks", "DeLonghi", "Dunkin Donuts") and model NOT IN ("arabica", "robusta");
  • use parentheses to define order of operations
  • Match strings using LIKE. '%' and '_' are wildcard character. Do not use equal sign.
SELECT * FROM shredders WHERE type LIKE "%cr_ss%";
  • Regular Expression
SELECT * FROM glass_sets WHERE name REGEXP "^[ABC][A-K]hello[0-5]A$"

Generating Calculated Fields

  • CONCAT
  • TRIM
  • AS

Manipulating Data

  • functions

Grouping Table Data

  • summary values
  • counting rows

Making Complex Queries

Joining Database Tables

  • The ability to dynamically join together multiple tables with a single SELECT query is one of SQL's most powerful feaures. This allows related data to be efficiently stored in numerous tables rather than just one large table