Transcript database-sql - CyberInfrastructure and Geospatial Information

Geog 480: Principles of GIS
Guofeng Cao
CyberInfrastructure and Geospatial Information Laboratory
Department of Geography
National Center for Supercomputing Applications (NCSA)
University of Illinois at Urbana-Champaign
Introduction to SQL
SQL Overview
• Structured Query Language
• The standard for relational database
management systems (RDBMS)
• SQL-92 and SQL-99 Standards – Purpose:
o Specify syntax/semantics for data definition and
manipulation
o Define data structures
o Enable portability
o Specify minimal (level 1) and complete (level 2)
standards
o Allow for later growth/enhancement to standard
3
• Catalog
SQL Environment
o A set of schemas that constitute the description of a
database
• Schema
o The structure that contains descriptions of objects created
by a user (base tables, views, constraints)
• Data Definition Language (DDL)
o Commands that define a database, including creating,
altering, and dropping tables and establishing constraints
• Data Manipulation Language (DML)
o Commands that maintain and query a database
• Data Control Language (DCL)
o Commands that control a database, including administering
privileges and committing data
4
5
SQL Data types
• String types
o CHAR(n) – fixed-length character data, n characters long
Maximum length = 2000 bytes
o VARCHAR(n) – variable length character data, maximum 4000
bytes
• Numeric types
o NUMBER(p,q) – general purpose numeric data type
o INTEGER(p) – signed integer, p digits wide
o FLOAT(p) – floating point in scientific notation with p binary
digits precision
• Binary Large Objects(BLOB)
o Byte Array
• Geometry types
o Box2d, box3d, point, linestring, polygon
• Date/time type
6
Hands-on
key
Item
count
key
Item
price
Connecting to Geog480 server
Connecting to Database
• psql -U username -d database_name
o username = geog480
o database_name = tutorial
o Enter passwd when prompted (same as username)
• Postgres Commands
o
o
o
o
\l List all accessible databases
\dt List all the tables in current DB
\? Help
\q Quite
Table Creation
• SQL CREATE TABLE Syntax
o CREATE TABLE table_name
(
column_name1 data_type,
column_name2 data_type,
column_name3 data_type,
....
)
• Table Creation Example:
o create table count (key int, item varchar(20),count int);
o create table price (key int, item varchar(20),price float);
• Copy from a CSV file (postgres specific)
o \COPY count FROM ‘/srv/cigi/code/test.csv' with CSV HEADER
Table Insertion
• SQL CREATE TABLE Syntax
o INSERT INTO table_name
VALUES (value1, value2, value3,...)
• Insertion Example:
o insert into count values(1, ‘item0', 100);
o insert into count values(2, ‘item1', 101);
o insert into count values(3, ‘item2, 102);
• Copy from a CSV file (postgres specific)
o \COPY count FROM ‘/srv/cigi/code/test.csv' with CSV HEADER
Select Table Syntax
• Used for queries on single or multiple tables
• Clauses of the SELECT statement: (Sequence!!)
o SELECT
• List the columns (and expressions) that should be returned from the
query
o FROM
• Indicate the table(s) or view(s) from which data will be obtained
o WHERE
• Indicate the conditions under which a row will be included in the result
o GROUP BY
• Indicate categorization of results
o HAVING
• Indicate the conditions under which a category (group) will be included
o ORDER BY
• Sorts the result according to specified criteria
Select Table Examples
• List contents of table
o
o
o
o
select * from count;
select * from count where item =‘item1';
Select * from count order by key asc;
select key, count from count limit 5;
• Counting
o select count(*) from count;
o select count(*) from count where item like '%1';
• Max/Min/Avg
o select max(count) from count;
o select avg(count) from count where item like '%1%';
Update Tables
• SQL UPDATE Syntax
o UPDATE table_name
SET column1=value, column2=value2,...
WHERE some_column=some_value
• Update table Examples:
o update count set item =‘item1' where key =1;
o update count set count =105 where key=1;
• Delete Rows
o delete from count where key=1;
Join Tables
• SQL JOIN Syntax
o SELECT column_name(s)
FROM table_name1
JOIN table_name2
ON table_name1.column_name=table_name2.column_name
• Example: Join
o select count.key, count.item, count.count, price.price
from
count join price
on
count.item = price.item;
Using and Defining Views
• Views provide users controlled access to tables
• Base Table–table containing the raw data
• Concepts of View
o A “virtual table” created dynamically upon request by a user
o No data actually stored; instead data from base table made available to user
o Based on SQL SELECT statement on base tables or other views
Create View
• SQL Syntax
o CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition
• Example: Create view:
o Create view stock as
select count.key, count.item, count.count, price.price
from
count join price
on
count.item = price.item;
View Operations
• Very similar to table operations
• Multiply two columns
o Select count*price as value from stock
• Drop view
o Drop view stock
• End of this topic