Examples
- CREATE OR REPLACE TEMP VIEW demo_tmp_vw(name, value) AS VALUES ("Yi", 1), ("Ali", 2), ("Selina", 3)
Referencepsql Interactive Commands
\conninfo
- Output: 'You are connected to database "postgres" as user "postgres" via socket in "/var/run/postgresql" at port "5432".'
SELECT session_user, current_user;
\?
- list all available commands
\h
- get help on a specific statement
\l
- list databases, i.e, show databases;
\dt
- list tables SHOW TABLES;
\d table-name
- EXPLAIN table-name;
\du
- list users and their roles
\q
- quit
SQL Constraints
NOT NULL Constraint
- Ensures that a column cannot have a NULL value
DEFAULT Constraint
- Provides a default value for a column when none is specified
UNIQUE Constraint
- Ensures that all the values in a column are different
PRIMARY Key
- Uniquely identifies each row/record in a database table
- A primary key is used to uniquely identify each record in the table
- Each table must have at least one primary key
- To define a primary key in SQL server, you would simply need to use the keywords ‘Primary Key’ after the column’s name
FOREIGN Key
- Uniquely identifies a row/record in any another database table.
- A foreign key is a key used to link two tables together
- Sometimes also called as a referencing key
CUSTOMER_ID INT references CUSTOMERS(ID)
CHECK Constraint
- The CHECK constraint ensures that all values in a column satisfy certain conditions.
INDEX
- Used to create and retrieve data from the database very quickly.
- Syntax:
CREATE INDEX index_name ON table_name ( column1, column2.....);
- To create an INDEX on the AGE column, to optimize the search on customers for a specific age, you can use the follow SQL syntax which is given below
Database Normalization
3NF
- The second advantage is data integrity. When duplicated data changes, there is a big risk of updating only some of the data, especially if it is spread out in many different places in the database.
Getting Started
- CREATE DATABASE database-name;
- CREATE DATABASE IF NOT EXISTS database-name;
- DROP DATABASE database-name;
- DROP DATABASE IF EXISTS database-name;
Creating Database Tables
- 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
|
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 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%";
SELECT * FROM glass_sets WHERE name REGEXP "^[ABC][A-K]hello[0-5]A$"
Generating Calculated FieldsManipulating DataGrouping 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
Example Syntax
- 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;