Database Access using SQL

Database Access using SQL A basic introduction James Brucker

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

Install Client Software For this lab, you will access MySQL server on the network. All you need is a client application. You don't need to run a MySQL server on your computer. Client Tools mysql-workbench-gpl-5.x.y-win32.msi or use older GUI Tools: mysql-gui-tools-5.1.7-win32.msi From: se.cpe.ku.ac.th/download/mysql

Add MySQL "bin" to your Path This is so you can run the "mysql" command line. On Windows: 1. Right-click My Computer. 2. Choose Properties. 3. Click "Advanced". 4. Click "Environment Variables". 5. Edit PATH variable and add: C:\Windows\blah;C:\Program Files\MySql\bin

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

Exercise • Connect to MySQL server on host "se.cpe.ku.ac.th". • user: student password: student • What MySQL version is the server? dos> mysql -h se.cpe.ku.ac.th -u student -p Enter password: nisit mysql> SELECT 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)

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 • Connect to MySQL server on host "se.cpe.ku.ac.th". • user: student password: nisit • What databases are on the server? • What tables are in the world database? dos> mysql -h se.cpe.ku.ac.th -u student -p Enter password: nisit mysql> SHOW databases; mysql> USE world; mysql> SHOW tables;

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 For the world database: • what fields does the Country table have? • what information is in the fields? • which fields contain strings? (char or varchar) • which fields contain floating point values? • what is the primary key of the Country table?

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?

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!

Database Access using SQL