SQL Basics+

1. SQL Basics+ Brandon Checketts

2. Why SQL? • Structured Query Language • Frees programmers from dealing with specifics of data persistence • Cross-platform, language independent • Indexing and data optimization • Data integrity

3. Some Pitfalls • Vendor-Specific features • Standardization is not great • Complexity? Additional Overhead?

4. SQL Engines • MySQL • PostgreSQL • Informix • Oracle • MSSQL • Many others

5. Database Organization • A database server may have multiple databases • Each database is made up of one or more tables • Queries can select from multiple databases and tables.

6. Accessing your Database • Command Line • Web / GUI Interfaces • Programmatically • Spreadsheets (Excel) • Reporting Applications (Crystal Reports)

7. INSERT and SELECT INSERT INTO kids SET name = ‘Noah’, status = ‘nice’; SELECT * FROM kids WHERE name = ‘Noah’

8. Table Manipulation • CREATE CREATE TABLE `christmas`.`kids` ( `name` VARCHAR( 40 ) NOT NULL , `status` VARCHAR( 7 ) NOT NULL ) ; • ALTER ALTER TABLE `kids` CHANGE `name` `first_name` VARCHAR( 40 ), ADD `last_name` VARCHAR( 40 ) NOT NULL AFTER `first_name` ; • DROP

9. Column Types • Char, varchar, text, longtext • Int, tinyint, smallint, mediumint, bigint • Float, double, decimal, • Blob (binary large objects) • Date, datetime, timestamp, year, • Enum, bool

10. Santa’s Database • Santa would like to move into the 21st century and start keeping all of his required information in a database. • Lets try developing it ourselves • Demonstrate creating a ‘christmas’ database using phpMyAdmin (including user/pass) • Create kids table • What columns might we need? What types?

11. Santa’s Christmas App • Santa decided that developing this entire application by himself is too complicated. • He found an open-source application that he wants to use to track his lists. We’ve installed it at: http://roundsphere.com/christmas/

12. Kids Table mysql> describe kids; +------------+-------------+------+-----+-------------------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+-------------+------+-----+-------------------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | first_name | varchar(40) | NO | | | | | last_name | varchar(40) | NO | | | | | status | varchar(7) | NO | | | | | zip | varchar(5) | NO | | | | | modified | timestamp | NO | | CURRENT_TIMESTAMP | | +------------+-------------+------+-----+-------------------+----------------+ Mysql> show create table kids; …… • CREATE TABLE `kids` ( • `id` int(11) NOT NULL auto_increment, • `first_name` varchar(40) NOT NULL, • `last_name` varchar(40) NOT NULL, • `status` varchar(7) NOT NULL, • `zip` varchar(5) NOT NULL, • `modified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, • PRIMARY KEY (`id`) • ) ENGINE=MyISAM;

13. Adding reports • Santa is very happy with his new application. Now he’d like to add some additional features • What reports might we want to add? • What have kids wished for? • Kids who have been naughty • Kids who have been nice • Kids who are avoiding being checked up on • http://roundsphere.com/christmas/reports.php

14. Gift Lists (Importing from CSV) • Santa Elves have compiled gift lists and have them available in a CSV format • We can create a table for them and load them directly from CSV mysql>CREATE TABLE `christmas`.`gifts` ( `kid_id` INT NOT NULL , `gift` VARCHAR( 255 ) NOT NULL ) ENGINE = MYISAM ; mysql> LOAD DATA local infile ‘gifts.csv' INTO TABLE gifts FIELDS TERMINATED BY ','ENCLOSED BY '"'LINES TERMINATED BY '\n'

15. Manufacturing Report • Santa is an optimist and hopes that all kids will be good and get what they asked for. He needs a report to pass on to his elves so that they know what to manufacture SELECT gift, COUNT(gift) AS count FROM gifts GROUP BY gift ORDER BY count DESC;

16. Date/Time Functions • SELECT * FROM sometable • WHERE timestamp > NOW() • WHERE timestamp > DATE_SUB( NOW(), INTERVAL 7 DAY) • http://roundsphere.com/christmas/report_by_date.php

17. Sleigh Loading Report • We only want to load gifts for kids that have been nice • We’ll introduce a JOIN on the kids table SELECT gift, COUNT(gift) AS count FROM gifts JOIN kids ON kids.id = gifts.kid_id WHERE kids.status = 'nice' GROUP BY gifts.gift ORDER BY count DESC

18. What is Santa’s sleight doesn’t have enough room for all toys? • He might have to reload his sleigh based on geography • We could query kids within a radius of a given location, that would be helpful • We have the kids zip codes. Maybe we could group those together?

19. Exporting and Importing • Mysqldump to export mysqldump db zipcode |gzip -c > zipcode.sql.gz • Import with: zcat zipcode.sql.gz| mysql christmas

20. SQL Arithmetic • SQL Can do semi-complicated arithmetic: Find all zip codes with in a distance of a lat/lon: SELECT zc_zip, 6371*acos(sin('$lat')*sin(zc_lat*pi()/180)+cos('$lat')*cos(zc_lat*pi()/180)*cos('$lon'-zc_lon*pi()/180))/1.6093 AS distance FROM zipcode WHERE 6371*acos(sin('$lat')*sin(zc_lat*pi()/180)+cos('$lat')*cos(zc_lat*pi()/180)*cos('$lon'-zc_lon*pi()/180))<$radius *1.6093

21. Complicated Queries • Now that we have a zip code database, we can figure out what toys to load for all kids who have been good and live within a given radius of some zip code • http://roundsphere.com/christmas/report_geo.php

22. SQL Injection Attacks The Grinch wants to stop Christmas from coming, and is attempting to delete Santa’s list. We have an SQL injection vulnerability in index.php This will select more ids than we intend to: http://roundsphere.com/christmas/index.php?status=bad%27+OR+1%3D1+--+ I’ve tried to construct something that will drop a table, but have been unsuccessful so far…. A good page about SQL injection that I found is at: http://unixwiz.net/techtips/sql-injection.html

23. Other Useful Features • Encryption • Full-Text search • Conditionals • String functions • Spacial functions (GIS) • Precision Math

24. Alternatives to SQL • MemCache • RRD