SQL
Jump to navigation
Jump to search
Contents
- 1 Nice MediaWiki Commands
- 2 list of keywords not in sqlite3
- 3 Postgres bash shell commands
- 4 Getting Started
- 5 Creating Database Tables
- 6 Inserting Data Into Tables
- 7 Retrieving Data From Tables
- 8 Sorting Retrieved Data
- 9 Data Filtering
- 10 Generating Calculated Fields
- 11 Manipulating Data
- 12 Grouping Table Data
- 13 Making Complex Queries
- 14 Joining Database Tables
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_%';
- Have MySQL generate the statement for you
- 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;
- ex:
UPDATE wp_options SET option_value='http://ec2-184-73-8-87.compute-1.amazonaws.com' WHERE option_name='home';
- ex:
- 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