SQL

From Colettapedia
(Redirected from SQL Database Syntax)
Jump to navigation Jump to search

Examples

  • CREATE OR REPLACE TEMP VIEW demo_tmp_vw(name, value) AS VALUES ("Yi", 1), ("Ali", 2), ("Selina", 3)


Reference

psql 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

  • USE database-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

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;