Database Access using SQL An introduction to My

Database Access using SQL An introduction to My. SQL

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

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 Excel client Client side server controls access to database Server side

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

A Database Structure Database Schema A database contains schema, which describe the organization of the database. A schema can contain: Table field 1: t 1 field 2: t 2 field 3: t 3 indexes tables - containing data index files - for fast lookup of data stored procedures, constraints, triggers, and more

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. records (rows) +------------+-------+-----+ | ID | Name | CCode | District | Populatn +---------------+---------+ | 3320 | Bangkok | THA | Bangkok | 6320174 | | 3321 | Nonthaburi | THA | Nonthaburi | 292100 | | 3323 | Chiang Mai | THA | Chiang Mai | 171100 | +------------+-------+-----+ 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, Populatn int(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 | | | Country. Code | 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 | | | Country. Code | 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 n end each statement with a semi-colon ; sql> USE world; SQL statements end with semi-colon. database changed. sql> SHOW tables; sql> SHOW columns FROM city; sql> DECRIBE country;

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

DESCRIBE shows the structure of a table. n 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 | | | Country. Code | char(3) | NO | | | District | char(20) | NO | | | Population | int(11) | NO | | 0 | | +-------+------+-----+------------+

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

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

Qualifying Names § SQL uses ". " to qualify elements of a hierarchy n just like most O-O languages World. city "city" table in World db city. 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 field 1, field 2, field 3 FROM table ; § displays ALL rows from the table. § use LIMIT number to limit how many results. sql> SELECT account. Number, balance FROM accounts; +---------------+-----+-----+ | account. Number | account. Name | client. ID | 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; +---------------+-----+-----+ | account. Number | account. Name | client. ID | balance | +---------------+-----+-----+ | 11111113 | P. Watanapong | 00001001 | 300000 | | 11111114 | CPE Fund | 00001002 | 1840000 | +---------------+-----+-----+

Qualifying SELECT § Select columns from a table that match some criteria: SELECT field 1, field 2, field 3 FROM table WHERE condition ORDER BY field 1, . . . [ASC|DESC]; Example: cities with population > 5 M sql> SELECT * FROM City WHERE population > 5000000 ORDER BY population DESC;

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

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

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

WHERE conditions name = 'Bangkok' equality test name LIKE 'Bang%' pattern match population >= 100000 population < 500000 gnp <> 0 relations grade IN ('A', 'B', 'C', 'D', 'F') contained in set <> is not equals

Exercise with WHERE & ORDER 1. What is the most populous country in Europe? n use WHERE continent =. . . 2. What countries have name beginning with 'Z'? 3. 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 1. How many countries are in the database? 2. How many cities are in China? 3. 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(Surface. Area) FROM country; 1075400. 00 (sq. km. ) WRONG: This will NOT find the largest country! sql> SELECT MAX(Surface. Area), 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(Surface. Area) FROM Country WHERE continent='Asia'; § What is the version of My. SQL? SELECT version();

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

Exercise for Functions Harder: § What are total population and total GNP of each continent? n 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 person for each country sql> SELECT name, gnp/population FROM country ORDER BY capita_gnp DESC; AS capita_gnp Value of GNP is in millions of US Dollars. How can you show per capita GNP in dollars? ? ? alias

Exercise 1. What countries are the richest? Poorest? n Show the GNP per capita (in US dollars). n Order the results by GNP per capita. 2. 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 ( data 1, data 2, . . . ); sql> INSERT INTO Accounts VALUES ('2222', 'Ample Rich', '00000001' 10000000); Query OK, 1 row affected. +---------------+-----+-----+ | account. Number | account. Name | client. ID | balance | +---------------+-----+-----+ | 2222 | Ample Rich | 00000001 |10000000 | +---------------+-----+-----+

Exercise § Add your home town to the City table or, add another city to the City table. INSERT INTO city (name, country. Code, district, population) VALUES ('Bangsaen', 'THA', 'Chonburi', 30000); sql> Query OK, 1 row affected. The ID field has a qualifier "AUTO_INCREMENT". (see: "DESCRIBE City") This means My. SQL 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. n § unless you are using transactions Duplicate data is possible.

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

Copying Data Between Tables § Suppose we have another table named New. Accts § New. Accts has account. Number, account. Name, . . . INSERT INTO table (field 1, field 2, . . . ) SELECT field 1, field 2, field 3 FROM other_table WHERE condition; sql> INSERT INTO Accounts SELECT * FROM New. Accounts WHERE account. Number NOT NULL;

UPDATE multiple columns You can change multiple columns: UPDATE table SET field 1=value 1, field 2=value 2 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) Changed every country in the database!! Oops! I forgot "WHERE. . . "

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 Head. Of. State='Obama'; /* Oops! I forgot "WHERE. . . " */ +----------------+---------+ | Code | Name | Continent | Head. Of. State | +----------------+---------+ | AFG | Afghanistan | Asia | Obama | | NLD | Netherlands | Europe | Obama | | ALB | Albania | Europe | Obama | | DZA | Algeria | Africa | Obama | | ASM | American Samoa | Oceania | Obama rules! | AND | Andorra | Europe | Obama | | AGO | Angola | Africa | Obama |

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

Deleting Records § DELETE one or more records DELETE FROM tablename WHERE condition; 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 FROM tablename WHERE condition; 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;

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

Joining Tables § Relate or "join" tables using a condition. § Use "table. field" to qualify a field name: Country. code City ID (PK) Name Country. Code (FK) Population District * Country. name 1 Country Code (PK) Name Continent Capital. . . City. countrycode = Country. code

Example: Join Country and City SELECT Country. Name, City. Name FROM Country, City WHERE Country. Code = City. Country. Code AND Continent = 'South America'; Country Code Name Continent Region Surface. Area Population GNP Local. Name Capital City Country. Code = City. Country. Code ID Name Country. Code District Population

Use Aliases to Reduce Typing SELECT co. Name, c. Name FROM Country co, City c WHERE co. Code = c. Country. Code AND co. Continent = 'South America'; co is alias for Country c is alias for City

Exercise: Cities in Laos List the city names and city populations in Laos. SELECT co. Name, c. Population FROM Country co, City c WHERE. . . AND. . . ;

Exercise 1. How can we find the name of the capital city for each country? Country Code (PK) Name Continent Region Surface. Area Population GNP Local. Name Capital City ID (PK) Name Country. Code District Population

Exercise Solution List the country name and capital city name, for all countries in Asia. SELECT co. name, c. name AS Capital. City FROM Country co, City c WHERE. . . AND. . . ;

Exercise 1. How can we join the Country. Language table with the County table? Country Code (PK) Name Continent Region Surface. Area Population GNP Local. Name Capital Country. Language Country. Code Language is. Official Percentage FROM Country CO, Country. Language L WHERE. . .

Exercise 1. In what countries is the Thai language spoken? 2. By what percentage of the people? Example: SELECT CO. name, L. language, L. percentage FROM Country CO, Country. Language L WHERE. . . AND. . . ;

Answer using Aliases § In what countries is Chinese the official language? SELECT C. name, L. language, L. percentage FROM Country C, Country. Language L WHERE C. code = L. countrycode AND L. language LIKE '%Chinese' AND is. Official = 'T'; you can omit table name when there is no ambiguity alias for Country. Language

Exercise 1. What countries use English? n ORDER the results by percentage spoken, from largest to smallest %. 2. In how many countries is English the official language? Harder 3. In the world, approximately how many people speak English? 1. sum( C. population * L. percentage / 100 )

JOIN § Joins tables § Many forms: § n INNER JOIN (include only matching columns) n OUTER JOIN (include all columns) n LEFT OUTER JOIN n NATURAL JOIN n CONDITION JOIN "JOIN" means "INNER JOIN" in My. Sql.

Example of a Condition Join § JOIN the Country. Language and Language tables using the country code: SELECT CO. Name, L. language, L. percentage FROM Country CO JOIN Country. Language L ON CO. code = L. countrycode WHERE. . . ;

Exercise § JOIN the Country and Language tables. § View Country name and language with "SELECT. . . " § How many times is Thailand listed in the results? How can you order the results by language ?

Multiple Table Join § You can join many tables at one time: SELECT CO. name, C. *, L. language FROM Country CO JOIN Country. Language L ON CO. code = L. countrycode JOIN City C ON CO. code = C. countrycode WHERE. . . ; /* more conditions */

More SELECT Syntax

GROUP BY. . . is used when you want to apply a function (count, sum, avg) to a group of rows having a common characteristic. Example: How many countries are in each continent? SELECT continent, count(*) FROM country GROUP BY continent

GROUP BY Exercise What is the total population of each continent? § use sum(population) and GROUP BY SELECT continent, SUM(population) FROM. . . GROUP BY. . .

Logical operations § OR SELECT * FROM City WHERE District='Songkhla' OR District='Bangkok'; § AND SELECT Name, Surface. Area FROM Country WHERE Continent = 'Africa' AND Surface. Area > 1000000; § NOT SELECT * FROM Accounts WHERE NOT Available. Balance = 0;

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

Exercise for matching 1. How many countries have a government that is any form of monarchy? § § match any government containing 'Monarchy' How many are some form of monarchy, but not a Constitutional Monarchy (like Thailand)?

GROUP BY. . . HAVING. . . GROUP BY. . . used to apply a function to a group of rows having a characteristic. HAVING. . . is used to put a condition on the groups. Example: What countries have more than one official lanaguage? ? ? SELECT countrycode, count(language) FROM countrylanguage ? ? ?

GROUP BY. . . HAVING. . . (1) First, how to count official languages in each country? SELECT countrycode, count(language) FROM countrylanguage WHERE is. Official='T' GROUP BY countrycode

GROUP BY. . . HAVING. . . (2) add HAVING to restrict results to count( ) > 1 SELECT countrycode, count(language) FROM countrylanguage WHERE is. Official='T' GROUP BY countrycode HAVING count(language) > 1

Getting Help Online help for § HELP for the mysql command § HELP for SQL statements mysql> HELP SELECT If My. Sql doesn't have help on SQL commands, then load the "help tables" data onto your server. Download help table data from: http: //dev. mysql. com/downloads in the "Documentation" section.

Subqueries § Use the result of one query as part of another query. Example: Which country has the largest population? SELECT Name, Population FROM country WHERE Population = ( SELECT max(population) FROM country); Subquery To use subqueries in My. SQL you need version 4. 1 or newer.

Exercise § § In which country do people live the longest? How long to they live? SELECT Name, Life. Expectancy FROM country WHERE Life. Expectancy = ( insert subquery here ) ;

LIMIT instead of subquery Another way to get a "most" or "least" result: § ORDER results by what you want. Use ASC or DESC § use LIMIT 1 to limit number of results. SELECT Name, Population FROM country ORDER BY Population DESC LIMIT 1;

Exercise § Which nation is the most crowded? n n 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/surface. Area AS density WHERE. . . Alias: density : = population/surface. Area

Exercise Is Thailand richer than other countries in Southest Asia? n n List the name and GNP/population (=wealth) of countries in the same region as Thailand. use a subquery for "the region of Thailand": SELECT. . . FROM Country WHERE region = (SELECT region WHERE. . . ) ORDER BY. . . ; order the results by wealth

Exercise: increasing wealth Thailand has decided to annex (invade) either Cambodia, Laus, Vietnam, or Malaysia. The invaded country will become part of the new Thailand. The government wants the combined country to be wealthier than Thailand is now. "wealth" means GNP/population. What country should Thailand invade?

Data Definition Commands These commands alter the structure of a database CREATE create a Table, Index, or Database ALTER modify structure of a Database or Table DROP delete an entire Table, Index, or Database RENAME rename a Table

Creating a Table To add a new table to a database: CREATE TABLE tablename (field 1, field 2, . . . ) options ; sql> CREATE TABLE CUSTOMER ( account. Number VARCHAR(8) NOT NULL, client. ID VARCHAR(40) NOT NULL, balance DOUBLE DEFAULT '0', available. Balance DOUBLE DEFAULT '0' ) ; Query OK, 0 rows affected.

Productivity Hint § § Type the "CREATE TABLE" statement into a file. "source" the file in mysql: source filename; File: /temp/create-table. sql CREATE TABLE CUSTOMER ( account. Number CHAR(10) NOT NULL, client. ID VARCHAR(40) NOT NULL, balance DOUBLE DEFAULT '0', available. Balance DOUBLE DEFAULT '0', PRIMARY KEY( client. ID ) ) ; sql> SOURCE /temp/create-table. sql; Query OK, 0 rows affected.

Deleting Records a Table You must specify a "WHERE" clause for rows to delete. If there is no "WHERE", it deletes all rows !! DELETE FROM tablename WHERE condition ; -- first use SELECT to verify condition sql> SELECT * FROM city WHERE name="Bangsaen"; sql> DELETE FROM city WHERE name="Bangsaen"; Query OK, 1 row affected.

Exercise § Delete the city you added to the City table. § On a friend's machine, is it deleted immediately?

Deleting a Table Remove a table from the database DROP TABLE tablename ; sql> DROP TABLE CUSTOMER ;

Views § A View is like a "virtual table" containing selected data from one or more real tables. Country Name Continent Code. . . Country. Lan guage Language Percentage is. Official. . . CREATE VIEW My. View AS. . . My. View Name Language Percentage

View Example Create a view for country name, languages, and percentage. sql> CREATE VIEW lang AS SELECT name, language, percentage FROM Country C, Country. Language L WHERE C. code = L. countrycode ORDER BY language ASC; Query OK, 0 rows affected. Use the view to browse data: sql> SELECT * FROM lang WHERE language='Thai';

Productivity Hint § § Type the "CREATE VIEW" statement into a file. Read the file into mysql: source filename; File: langview. sql CREATE VIEW lang AS SELECT name, language, percentage FROM Country C, Country. Language L WHERE C. code = L. countrycode; sql> SOURCE langview. sql; Query OK, 0 rows affected.

Exercise § Create a view that shows these fields: City. name as name Country. name as country Region Population of the city Official language id of the city § Each person should use a different name for his view, to avoid interfering with each other. § List the tables in world ( show tables ).

Exercise § List the cities in Southest Asia where English is the official language and population is over 100, 000.

Exercise § Ask My. SQL to "describe" your view. § Delete your view: DROP VIEW viewname ;

Review What is the command to. . . 1. list all the databases that you have access to? 2. use the Bank database? 3. view a list of tables in Bank? 4. view the structure of the Accounts table?

SQL Quiz Database Game

Vocabulary § "largest" and "smallest" refer to size (surface. Area). § "most populous", "least populous" refer to population and population > 0. (exclude unpopulated nations) § "richest", "poorest" means GNP per capita not total GNP, and GNP > 0 (GNP = 0 means no data). § "most crowded" refers to population/surface. Area

Language Hints § "Fortran is an official language" means its an official language of the country § "Pascal is spoken unofficially" means it is spoken, but not an official language § "COBOL is spoken" means COBOL is a language and percentage > 0

What is the World's Smallest Nation? How big is it?

What is the Largest Country in Africa? § § Show the SQL How big is it?

What are the Poorest Countries in Asia? § must have GNP data (GNP > 0) § List 3 poorest nations. § What is the GNP person? NOTE: GNP is measured in $1, 000. Multiply your answer by 1, 000 to get US$.

What is the Richest Country in the Middle East (region)? § § § What is the GNP person? Show result is US$ (not million). Round the income to nearest dollar! n WRONG: 12345. 6789 NOTE: GNP is database is measured in $1, 000. Multiply your answer by 1, 000 to get US$.

In what countries is Thai spoken? SELECT. . . FROM Country C JOIN Country. Language L ON C. code = L. countrycode WHERE. . .

How many people speak English? 2 queries: § how many in each country § total for world SELECT FROM JOIN ON WHERE . . . Country C Country. Language L C. code = L. countrycode. . .

History of Empires through Language Empires are cultures that spread over many countries. We can detect past Empires by the spread of language. § What languages are spoken in greatest number of countries? § Can you name the Empire?

History of Empires through Language We can detect past Empires by the spread of language. § What languages are spoken in greatest number of countries? SELECT L. language, sum(L. percentage*C. population) AS sum FROM Country C JOIN Country. Language L ON C. code = L. countrycode GROUP BY L. language ORDER BY sum DESC

Where is Dari the Official Language? In what country is Dari the official language?

4 official languages? What country in Europe has 4 official languages? SELECT FROM JOIN ON WHERE GROUP HAVING . . . Country C Country. Language L C. code = L. countrycode. . . BY C. code -- group by country. . .

Resources My. SQL § http: //dev. mysql. com/tech-resources/articles/ Learning SQL § http: //www. w 3 schools. com/sql/ nice tutorial and command reference