Database Access using SQL

Database Access using SQL Very basic introduction James Brucker

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

Client - Server Structure • The database server is a separate process running on a host. • The database client can be on a different machine. • Many programs may be client using standard API. mysqld(server) client using "mysql" utility client using Java+JDBC client using Excel

Exercise • Use the "mysql" command • if machine doesn't have "mysql" then use MySQL Query Browser GUI. • What is the version number? • Look at help message: how do you connect to server? dos> mysql --version mysql Ver 14.12 Distrib 5.0.16, for Win32 dos> mysql --help displays a long help message: very useful

Exercise • Connect to MySQL server on host "se.cpe.ku.ac.th". • user: student password: student • What MySQL version is the server running? dos> mysql -h se.cpe.ku.ac.th -u student -p Enter password: student mysql> SELECT version(); mysql>

Structured Query Language • The standard language for manipulating a database is Structured Query Language (SQL). • SQL is case-insensitive and free format. • Commands are entered interactively or in a script file. • SQL statements can span multiple lines: • end a statement with a semi-colon ; sql> USE Bank; database changed. sql> SHOW tables; sql> SHOW columns FROM accounts; sql> SELECT accountNumber, accountName, clientID, balance FROM accounts; SQL statements end with a semi-colon.

Exercise • Connect to MySQL server on host "se.cpe.ku.ac.th". • user: student password: student • What databases are there? • What tables are in the world database? dos> mysql -h se.cpe.ku.ac.th -u student -p Enter password: student mysql> SHOW databases; mysql> SHOW tables from world; mysql> USE world; mysql> SHOW tables;

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

Structure of a Database Table • A table contains records (rows) of data. • A record is composed of several columns (fields). • A database schema can contain index files, views, and other information in addition to tables. sql> SELECT * FROM Accounts; +---------------+---------------+----------+---------+ | accountNumber | accountName | clientID | balance | +---------------+---------------+----------+---------+ | 11111111 | J.Brucker | 11111111 | 35000 | | 11111112 | Vacation Fund | 11111111 | 22500 | | 11111113 | P.Watanapong | 11111120 | 300000 | | 11111114 | CPE Fund | 11111120 | 1840000 | +---------------+---------------+----------+---------+

Exercise: For the world database: • what columns does each table have? • what information do you suppose is in the columns? • What are the names of some data types in SQL? mysql> describe city; +-------------+----------+------+-----+---------+-----------+ | Field | Type | Null | Key | Default | Extra | +-------------+----------+------+-----+---------+-----------+ | ID | int(11) | NO | PRI | NULL | auto_incr | | Name | char(35) | NO | | | | | CountryCode | char(3) | NO | | | | | District | char(20) | NO | | | | | Population | int(11) | NO | | 0 | | +-------------+----------+------+-----+---------+-----------+

Exercise: O-O Analogy of a Table? DatabaseObject Oriented table __________________ record (row) __________________ fields (columns) __________________ sql> SELECT * FROM Accounts; +---------------+---------------+----------+---------+ | accountNumber | accountName | clientID | balance | +---------------+---------------+----------+---------+ | 11111111 | J.Brucker | 11111111 | 35000 | | 11111112 | Vacation Fund | 11111111 | 22500 | | 11111113 | P.Watanapong | 11111120 | 300000 | | 11111114 | CPE Fund | 11111120 | 1840000 | +---------------+---------------+----------+---------+

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 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 Diagram of Database Structure Database Catalog Catalog Schema Schema Schema Schema indexes indexes indexes indexes indexes indexes indexes indexes In MySQL the words "database" and "schema" are used inconsistently.

Database Operations • Operations you can perform on a database include: USE choose a database SELECT query (search) the data INSERT add new records to a table(s) UPDATE modify information in existing records DELETE delete records from a table sql> USE bank; sql> SELECT * FROM clients WHERE firstname = 'james'; | clientID | firstName | lastName | email | +----------+-----------+----------+--------------------+ | 11111111 | James | Brucker | jbrucker@yahoo.com | sql> UPDATE accounts SET balance=100000 WHERE clientID='11111111'; Query OK, 1 row affected (0.09 sec)

SQL SELECT statement • Select columns from a table and display them: SELECT field1, field2, field3 FROM tablename ; • This displays ALL rows from the table. sql> SELECT * from accounts; +---------------+---------------+----------+---------+ | accountNumber | accountName | clientID | balance | +---------------+---------------+----------+---------+ | 11111111 | J.Brucker | 11111111 | 35000 | | 11111112 | Vacation Fund | 11111111 | 22500 | | 11111113 | P.Watanapong | 11111120 | 300000 | | 11111114 | CPE Fund | 11111120 | 1840000 | +---------------+---------------+----------+---------+

Qualifying SELECT • Select columns from a table that match some criteria: SELECT field1, field2, field3 FROM table WHERE condition ORDER BY field1,... [ASC|DESC]; sql> SELECT accountNumber, ... FROM accounts WHERE balance > 100000 ORDER by balance DESC; +---------------+---------------+----------+---------+ | accountNumber | accountName | clientID | balance | +---------------+---------------+----------+---------+ | 11111114 | CPE Fund | 11111120 | 1840000 | | 11111113 | P.Watanapong | 11111120 | 300000 | +---------------+---------------+----------+---------+ SQL statements and field names are case insensitive, but the field values may not be! It depends on the data type of the field.

Counting with SELECT • Select can be used with functions, such as COUNT: SELECT COUNT(*) from Accounts WHERE condition; sql> SELECT count(*) from accounts; +----------+ | count(*) | +----------+ | 4 | +----------+ sql> SELECT count(*) from accounts WHERE balance > 100000;

Exercise: Use the world database to answer these questions: • How many countries and cities are in the database? • What is the country code for China? • use the condition WHERE name = 'China' • How many cities are in China? • What languages are spoken in China? • what is the official language? • List the cities in China, sorted by population (largest to smallest). Use "ORDER BY ..."

Exercise: • What is the country code for Thailand? • What is the population of Thailand? • How many cities are in Thailand? • what are the names of the cities? • What languages are used in Thailand?

Strings and wildcards in SELECT • Use single quote marks for String data. • For exact matches usefield = 'value' SELECT * FROM city WHERE CountryCode = 'THA'; • For pattern matches, use: field LIKE 'pattern' SELECT * FROM city WHERE name LIKE 'Ba%'; • In SQL, '%' means "match anything". SELECT * FROM clients WHERE firstName LIKE 'J%' ORDER BY lastName; SQL statements and field names are case insensitive, but the fieldvalues may not be! It depends on the data type of the field.

Logical operations • OR SELECT * FROM City WHERE District='Songkhla' OR District='Bangkok'; • AND SELECT Name, SurfaceArea FROM Country WHERE Continent = 'Africa' AND SurfaceArea > 1000000; • NOT SELECT * FROM Accounts WHERE NOT AvailableBalance = 0;

Set operations • IN SELECT * FROM City WHERE District IN ('Songkhla', 'Bangkok');

Exercise: who lives longest? • How many countries are in Asia? • In which countries in Asia do people live longest? • List the countries sorted by life expectancy. • Where can you expect to live > 80 years? • What country in Asia has the shortest life expectancy? • How does Thailand rank for life expectancy? • COUNT the countries in Asia with life expectancy less than in Thailand. • COUNT the countries in Asia with life expectancy greater than in Thailand.

Exercise • Find all cities in Thailand, ranked by Population • use: "SELECT ... ORDERBY Population DESC" • do you see any errors in the data?

Using Functions in SELECT • How many people are in the world? SELECT SUM(population) FROM Country; • What is the largest surface area of any country? SELECT MAX(SurfaceArea) FROM Country; • How many cities are from Thailand? SELECT COUNT(*) FROM City WHERE countrycode = 'THA'; • What is the version of MySQL? SELECT version();

Exercise • What is the total population of the world? • What is the total population of Asia? • What is the average and maximum population per sq.km. of the countries in the world? • Country.SurfaceArea is the area in sq. km.

Getting Help Built-in help for • how to use the mysql command • SQL statements mysql> HELP mysql> HELP SELECT If mysql doesn't have help on SQL commands, then load the "help tables" data onto the server. Download help table data from: http://dev.mysql.com/downloads in the "Documentation" section.

Using Subqueries • Use the result of one query as part of another query. • Which account has the greatest balance? SELECT AccountNumber, AccountName FROM Accounts WHERE balance = ( SELECT max(balance) FROM Accounts ); • Where do people live the longest? SELECT Name, LifeExpectancy FROM Country WHERE LifeExpectancy = ( SELECT max(LifeExpectancy) FROM Country ); To use SQL subqueries in MySQL you need version 4.1 or newer.

Exercise • Where do people live longer than in Thailand? • ORDER the results by DESCending life expectancy. SELECT Name, LifeExpectancy FROM Country WHERE LifeExpectancy > ( insert subquery here ) ORDER BY ... ;

Exercise • Which nation is the most crowded? • Find the country with maximum population density (population per sq. km.) • Show the name and the population density • Hint: create an alias for a computed field: sql> SELECT name, population/surfaceArea AS density WHERE ... Alias:density := population/surfaceArea

Exercise • Is Thailand more crowded than neighbor countries? • List the name and population density of all countries in the same region as Thailand. • use a subquery for "same region as Thailand": SELECT ... FROM CountryWHERE Region = (SELECT Region ... Code=...) ORDER ...; • Order the results by population density.

Exercise • How does Thailand's economic output per capita compare with other countries in Asia? • Compare 1,000,000 * GNP / population.

Modify data with UPDATE • UPDATE changes data in one or more tables: UPDATE accounts SET balance=100000 WHERE clientID='11111111'; sql> UPDATE accounts SET balance=100000 WHERE clientID='11111111'; +---------------+---------------+----------+---------+ | accountNumber | accountName | clientID | balance | +---------------+---------------+----------+---------+ | 11111111 | J.Brucker | 11111111 | 1000000 | | 11111112 | Vacation Fund | 11111111 | 1000000 | | 11111113 | P.Watanapong | 11111120 | 300000 | | 11111114 | CPE Fund | 11111120 | 1840000 | +---------------+---------------+----------+---------+

Warning: UPDATE is immediate! • There is no "undo". Changes take effect immediately. • Be Careful! If you forget the WHERE clause it will change all the rows in the table! sql> UPDATE accounts SET balance=100000 ; /* oops! */ +---------------+---------------+----------+---------+ | accountNumber | accountName | clientID | balance | +---------------+---------------+----------+---------+ | 11111111 | J.Brucker | 11111111 | 1000000 | | 11111112 | Vacation Fund | 11111111 | 1000000 | | 11111113 | P.Watanapong | 11111120 | 1000000 | | 11111114 | CPE Fund | 11111120 | 1000000 | +---------------+---------------+----------+---------+

Exercise • Get the current population for a city in Thailand. • Search the web or www.ttt.go.th • Update the population in the world database. Question: what happens if two people change the same city data (same record) at the same time?

Inserting new records • INSERT adds a new record to a table INSERT INTO tablename VALUES ( value1, value2, ...); sql> INSERT INTO Accounts VALUES ('22222222', 'Ample Rich', '00000000' 10000000); Query OK, 1 row affected. +---------------+---------------+----------+---------+ | accountNumber | accountName | clientID | balance | +---------------+---------------+----------+---------+ | 22222222 | Ample Rich | 00000000 |10000000 | +---------------+---------------+----------+---------+

Insert into columns by name You can specify which columns will receive the values: INSERT INTO tablename (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', 100000); Query OK, 1 row affected. The ID field has a qualifier of "AUTO_INCREMENT". (use "DESCRIBE City") This means MySQL will assign the ID value itself.

Exercise • View the city data that you just added! • Use UPDATE to change the population of the city you added. sql> UPDATE City SET population = 95000 WHERE city.name = 'Bangsaen'; Query OK, 1 row affected. sql> SELECT * FROM City WHERE City.name = 'Bangsaen';

How do you get data into a table? • Use INSERT commands (boring). • Put INSERT commands in a Script (text file) and "source" the file (better). • Import command (may depend on DBMS): • LOAD DATA INFILE'filename'INTOtable... • BCP ("bulk copy" - MS SQL server)

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

Relating Tables • The power of a relational database is the ability to selectively combine data from tables. You can use: • select data from multiple tables by matching a field • relations can be • 1-to-1 student -> photograph • 1-to-many country -> city • many-to-1 city -> country • many-to-many language -> country

Joining Tables • The power of a database comes from the ability to relate or "join" tables using a condition. • Use "table.field" to qualify a field name: Accounts.balance, Clients.clientID, ... • The clientID joins the Accounts table and Clients table. Accounts accountNumber accountName clientID balance availableBalance Clients clientID (KEY) firstName lastName email 1 * Accounts.clientID = Clients.clientID

Joining Tables (2) • Show the balance of all accounts owned by J. Brucker. • the account balance is in the Accounts table. • the client name is the Clients table. • relate the tables using clientID field. SELECT accountNumber, firstName, balance FROM Accounts, Clients WHERE Accounts.clientID = Clients.clientID AND clients.firstName = 'James'; +---------------+---------------+---------+ | accountNumber | firstName | balance | +---------------+---------------+---------+ | 11111111 | James | 100000 | | 11111112 | James | 22500 |

Exercise • What fields can we use to relate tables in the world database? City ID Name CountryCode District Population Country Code Name Continent Region SurfaceArea Population GNP LocalName Capital CountryLanguage CountryCode Language isOfficial Percentage

Database Access using SQL