SQL
(Redirected from SQL Database Syntax)
Jump to navigation
Jump to search
Contents
- 1 Examples
- 2 Reference
- 3 psql Interactive Commands
- 4 SQL Constraints
- 5 Database Normalization
- 6 Getting Started
- 7 Creating Database Tables
- 8 Inserting Data Into Tables
- 9 Retrieving Data From Tables
- 10 Sorting Retrieved Data
- 11 Data Filtering
- 12 Generating Calculated Fields
- 13 Manipulating Data
- 14 Grouping Table Data
- 15 Making Complex Queries
- 16 Joining Database Tables
- 17 Example Syntax
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 tablesSHOW 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_%';
- 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
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;