Database Access using SQL

Database Access using SQL An introduction to MySQL

Database Management System SELECT * FROM city WHERE name LIKE Ban% Database Manager Client Database: a structured, self-describing collection of data. • Control access to the database. • authentication • enforce permissions • data integrity • access services User Interface & communication protocol

Client - Server Databases • Database Server is a separate process on a host. • Clients can be on any machine. • Many programs may be clients using a standard API. "mysql" utility Server mysqld Java App +JDBC client server controls access to database Excel client Client side Server side

Exercise • Use the "mysql" command • What is the client version number? SHOW VARIABLES LIKE "%version%"

Table field1: t1 field2: t2 field3: t3 Table field1: t1 field2: t2 field3: t3 Table field1: t1 field2: t2 field3: t3 Table field1: t1 field2: t2 field3: t3 A Database Structure A database contains schema, which describe the organization of the database. A schema can contain: tables - containing data index files - for fast lookup of data stored procedures, constraints, triggers, and more Database Schema Schema indexes indexes indexes indexes

Contents of a Table • A table contains the actual data in records (rows). • A record is composed of fields (columns). • Each record contains one set of data values. +------+------------+-------+-------------+---------+ | ID | Name | CCode | District | Populatn +------+---------------+------------------+---------+ | 3320 | Bangkok | THA | Bangkok | 6320174 | | 3321 | Nonthaburi | THA | Nonthaburi | 292100 | | 3323 | Chiang Mai | THA | Chiang Mai | 171100 | +------+------------+-------+-------------+---------+ records(rows) fields (columns)

Create a Table CREATE TABLE City ( ID int NOT NULL AUTO_INCREMENT, Name char(35) NOT NULL, CCode char(3) NOT NULL, District char(20) NOT NULL, Populatnint(11) Default 0, PRIMARY KEY (ID) )

Key field for Identifying Rows • A table contains a primary key that uniquely identifies a row of data. • Each record must have a distinct value of primary key • The primary key is used to relate (join) tables. ID is the primary key in City table. +------+------------+-------+-------------+---------+ | ID | Name | CCode | District | Populatn +------+---------------+------------------+---------+ | 3320 | Bangkok | THA | Bangkok | 6320174 | | 3321 | Nonthaburi | THA | Nonthaburi | 292100 | | 3323 | Chiang Mai | THA | Chiang Mai | 171100 | +------+------------+-------+-------------+---------+

Structure of a Table Every field has: • a name • a data type and length To view the structure of a table use: DESCRIBE tablename sql> DESCRIBE City; +-------------+-----------+-----+-----+---------+----------------+ | Field | Type | Null| Key | Default | Extra | +-------------+-----------+-----+-----+---------+----------------+ | ID | int(11) | NO | PRI | | auto_increment | | Name | char(35) | NO | | | | | CountryCode | char(3) | NO | | | | | District | char(20) | NO | | | | | Population | int(11) | NO | | 0 | | +-------------+-----------+-----+-----+---------+----------------+

Structure of a Table "SHOW columns FROM tablename" shows the same information. sql> SHOW columns FROM City; +-------------+-----------+-----+-----+---------+----------------+ | Field | Type | Null| Key | Default | Extra | +-------------+-----------+-----+-----+---------+----------------+ | ID | int(11) | NO | PRI | | auto_increment | | Name | char(35) | NO | | | | | CountryCode | char(3) | NO | | | | | District | char(20) | NO | | | | | Population | int(11) | NO | | 0 | | +-------------+-----------+-----+-----+---------+----------------+ Fields may have a default value to use if a value is not assigned explicitly.

Structured Query Language • Structured Query Language (SQL) is the standard language for accessing information a database. • SQL is case-insensitive and free format. • Enter commands interactively or in a script file. • SQL statements can use multiple lines • end each statement with a semi-colon ; sql> USE world; database changed. sql> SHOW tables; sql> SHOW columns FROM city; sql> DECRIBE country; SQL statements end with semi-colon.

Exercise • Omit the semi-colon. What happens? • Enter a command on several lines mysql> SHOW tables FROM world ; No semi-colon.

DESCRIBE DESCRIBE shows the structure of a table. • same as "SHOW columns FROM tablename". sql> DESCRIBE city; +-------------+-----------+-----+-----+---------+----------------+ | Field | Type | Null| Key | Default | Extra | +-------------+-----------+-----+-----+---------+----------------+ | ID | int(11) | NO | PRI | | auto_increment | | Name | char(35) | NO | | | | | CountryCode | char(3) | NO | | | | | District | char(20) | NO | | | | | Population | int(11) | NO | | 0 | | +-------------+-----------+-----+-----+---------+----------------+

Exercise: Case Sensitivity • Is SQL case sensitive? • Are names of databases and tables case sensitive? mysql> DESCRIBE city; mysql> describe city; mysql> use world; mysql> use WORLD; mysql> describe city; mysql> describe City;

Exercise: O-O Analogy of a Table? DatabaseObject Oriented table __________________ record (row) __________________ fields (columns) __________________ +------+------------+--------------+---------+ | ID | Name | District | Popula..} +------+------------+--------------+---------+ | 3320 | Bangkok | Bangkok | 6320174 | | 3321 | Nonthaburi | Nonthaburi | 292100 | | 3323 | Chiang Mai | Chiang Mai | 171100 | +------+------------+--------------+---------+ records(rows) fields (columns)

Qualifying Names • SQL uses "." to qualify elements of a hierarchy • just like most O-O languages World.city "city" table in World db city.name name field in city table World.city.name fully qualified name sql> DESCRIBE World.country; ... sql> SELECT country.name from country;

4 Basic Database Operations The 4 most common operations: SELECT query (search) the data INSERT add new records to a table(s) UPDATE modify existing record(s) DELETE delete record(s) from a table What is CRUD? Programmers call these operations "CRUD". What does CRUD stand for? (create, read, update, delete)

Querying Data in a Table SELECT displays field values from a table: SELECT field1, field2, field3 FROM table ; • displays ALL rows from the table. • use LIMIT number to limit how many results. sql> SELECT accountNumber, balance FROM accounts; +---------------+---------------+----------+---------+ | accountNumber | accountName | clientID | balance | +---------------+---------------+----------+---------+ | 11111113 | P.Watanapong | 00001001 | 300000 | | 11111114 | CPE Fund | 00001002 | 1840000 | +---------------+---------------+----------+---------+

SELECT statement with * • Display values for all fields in table: SELECT * FROM tablename ; sql> SELECT * from accounts; +---------------+---------------+----------+---------+ | accountNumber | accountName | clientID | balance | +---------------+---------------+----------+---------+ | 11111113 | P.Watanapong | 00001001 | 300000 | | 11111114 | CPE Fund | 00001002 | 1840000 | +---------------+---------------+----------+---------+

Qualifying SELECT • Select columns from a table that match some criteria: SELECT field1, field2, field3 FROM table WHERE condition ORDERBY field1,... [ASC|DESC]; Example: cities with population > 5 M sql> SELECT * FROM City WHERE population > 5000000 ORDER BY population DESC;

Strings in SQL • Use single quotemark around String constants. SELECT * FROM Country WHERE name = 'Thailand'; SELECT * FROM City WHERE Name = 'Los Angeles';

Exercises • What are the first 3 cities in the database? • What are the 3 most populous countries in the world? • What is the smallest country in the world? How big?

Exercises for Thailand • What is the country code for Thailand? SELECT * from ... WHERE name = 'Thailand' • List the cities in Thailand, sorted by largestpopulation to smallest. Use "ORDER BY ..." • What languages are spoken in Thailand? • What countries speak Thai?

WHERE conditions

Exercise with WHERE & ORDER • What is the most populous country in Europe? • use WHERE continent = ... • What countries have name beginning with 'Z'? • In Thailand what cities have names like Ban______

Count Function Select can be used with functions, such as COUNT: SELECT COUNT(*) FROM accounts WHERE balance=0; sql> SELECT COUNT(*) from accounts; +----------+ | count(*) | +----------+ | 4 | +----------+ sql> SELECT COUNT(*) from accounts WHERE balance > 1000000;

Exercise • How manycountries are in the database? • How manycities are in China? • How many countries are in Europe?

Other Functions in SQL Functions can have arguments, just like C, Java, etc. SUM( expression ) MAX( expression ) MIN( expression ) COUNT( expression ) sql> SELECT MAX(SurfaceArea) FROM country; 1075400.00 (sq.km.) WRONG: This will NOT find the largest country! sql> SELECT MAX(SurfaceArea), Name FROM country; 1075400.00 Afghanistan

SELECT functions • How many people are in the world? SELECT SUM(Population) FROM Country; • How big is the largest country in Asia? SELECT MAX(SurfaceArea) FROM Country WHERE continent='Asia'; • What is the version of MySQL? SELECT version();

Exercise • What is the total GNP of the entire world? sql> SELECT sum(GNP) FROM country • What are the richest countries (GNP per person) in the world? sql> SELECT name, GNP/population FROM country ORDER BY GNP/population DESC LIMIT 20; • What are the most crowded countries (people per surface area) in Asia?

Exercise for Functions Harder: • What are totalpopulation and total GNP of each continent? • Hint: use GROUP BY continent

Expressions and Arithmetic • You can use expressions in SQL. Arithmetic: + - * / % sqrt() Grouping: ( ) String ops: substring( ), upper(), length( ) Example: display GNP per person for each country sql> SELECT name, gnp/population AS capita_gnp FROM country ORDER BY capita_gnp DESC; alias Value of GNP is in millions of US Dollars. How can you show per capita GNP in dollars???

Exercise • What countries are the richest? Poorest? • Show the GNP per capita (in US dollars). • Order the results by GNP per capita. • What countries are the most crowded? • Crowding refers to population per surface area.

Wildcards to match patterns • Pattern matches: field LIKE 'pattern' SELECT * FROM city WHERE name LIKE 'Ban%'; % means "match anything"

Adding New Records • INSERT adds a new record to a table INSERT INTO table VALUES ( data1, data2, ...); sql> INSERT INTO Accounts VALUES ('22222222', 'Ample Rich', '00000001' 10000000); Query OK, 1 row affected. +---------------+---------------+----------+---------+ | accountNumber | accountName | clientID | balance | +---------------+---------------+----------+---------+ | 22222222 | Ample Rich | 00000001 |10000000 | +---------------+---------------+----------+---------+

INSERT into columns by name INSERT INTO table (field1, field2, ...) VALUES ( data1, data2, ...); sql> INSERT INTO Accounts (accountNumber, balance, accountName) VALUES ('22222222', 10000000, 'Ample Rich'); Query OK, 1 row affected. +---------------+---------------+----------+---------+ | accountNumber | accountName | clientID | balance | +---------------+---------------+----------+---------+ | 20000000 | Ample Rich | |10000000 | +---------------+---------------+----------+---------+

Exercise • Add your home town to the City table or, add another city to the City table. sql> INSERT INTO city (name, countryCode, district, population) VALUES ('Bangsaen', 'THA', 'Chonburi', 30000); Query OK, 1 row affected. The ID field has a qualifier "AUTO_INCREMENT". (see: "DESCRIBE City") This meansMySQL will assign the ID value itself.

Exercise • View the City data that you just added! • Correct any errors using UPDATE sql> SELECT * FROM City WHERE City.name = 'Bangsaen'; sql> UPDATE City SET population = 33000 WHERE City.name = 'Bangsaen'; Query OK, 1 row affected.

Warning: INSERT is immediate • Change occurs immediately. • unless you are using transactions • Duplicate data is possible.

3 ways to add data to a table • INSERT command (boring). • Write INSERT commands in a text file and "source" the file (better). sql> SOURCE mydata.sql • IMPORT command (syntax depends on DBMS): sql> LOAD DATA INFILE'filename'INTOtable...

Copying Data Between Tables • Suppose we have another table named NewAccts • NewAccts has accountNumber, accountName, ... INSERT INTO table (field1, field2, ...) SELECT field1, field2, field3 FROM other_table WHERE condition; sql> INSERT INTO Accounts SELECT * FROM NewAccounts WHERE accountNumber NOT NULL;

UPDATE multiple columns You can change multiple columns: UPDATE table SET field1=value1, field2=value2 WHERE condition; Example: Update population and GNP of Thailand sql>UPDATE country SET population=68100000, gnp=345600 WHERE code='THA'; Query OK, 1 row affected (0.09 sec) Source: CIA World Factbook (on the web)

Warning: don't forget WHERE • UPDATE can change every row in a database • Make sure that your WHERE clause selects only records you want to change! sql> UPDATE country SET population=68100000, gnp=345600 ; Query OK, 240 rows affected (0.14 sec) Oops! I forgot "WHERE ..." Changed every country in the database!!

Warning: UPDATE is immediate! • Changes occur immediately. (Can't undo w/o trans.) Be Careful! If you forget the WHERE clause it will change all the rows in the table! sql> UPDATE country SET HeadOfState='Obama'; /* Oops! I forgot "WHERE ..."*/ +------+----------------+-------------+--------------+ | Code | Name | Continent | HeadOfState | +------+----------------+-------------+--------------+ | AFG | Afghanistan | Asia | Obama | | NLD | Netherlands | Europe | Obama | | ALB | Albania | Europe | Obama | | DZA | Algeria | Africa | Obama | | ASM | American Samoa | Oceania | Obama | | AND | Andorra | Europe | Obama | | AGO | Angola | Africa | Obama | Obama rules!

Exercise • Update the City you added to the database. • Change its population.

Deleting Records • DELETE one or more records DELETE FROMtablenameWHEREcondition; Example: Delete all cities with zero population sql> DELETE FROM City WHERE population <= 0; Query OK, 5 rows deleted.

Warning: DELETE can delete all • DELETE affects all rows that match. DELETE FROMtablenameWHEREcondition; Example: Delete all cities with zero population sql> DELETE FROM City WHERE population <= 0; Query OK, 5 rows deleted.

Safer Delete • First SELECT the key of the row you want sql> SELECT id FROM City WHERE name='Bangsaen'; 6402 • If only one match, then delete using primary key sql> DELETE FROM City WHERE id=6402;

Database Access using SQL