Marketing Analytics: Database Query with My. SQL Disclaimer: • All logos, photos, etc. used in this presentation are the property of their respective copyright owners and are used here for educational purposes only © Stephan Sorger 2015. www. stephansorger. com; Database Query with My. SQL; SQL. 1
Outline Category Description Introduction Overview of database query languages RDBMS Example of Relational Database Management System Hosting Obtaining hosting server for My. SQL (free or paid) Setup Setting up the My. SQL Server on hosting platforms Importing databases into My. SQL Server Structure Data structure; Showing tables within databases SHOW Commands to display data SELECT Commands to choose certain pieces of data Sorting Commands to sort data Filtering Commands to filter data Logical Using logical commands (AND, OR) in commands Creating, Dropping, and Altering databases and tables © Stephan Sorger 2015. www. stephansorger. com; Database Query with My. SQL; SQL. 2
Database Query: Introduction Category Description SQL Structured Query Language Programming language for accessing RDBMS Relational Database Management System SQL Consists of two items: Data Definition Language: Syntax for defining data structures Data Manipulation Language: Syntax for selecting, inserting deleting, and updating data in databases Top 3 Database engines: Oracle My. SQL Microsoft SQL Server My. SQL Open source; Free; Available for Mac and PC Manual: See dev. mysql. com/doc/index. html © Stephan Sorger 2015. www. stephansorger. com; Database Query with My. SQL; SQL. 3
RDBMS Database Example Primary Key: Column with 100% unique entries similar to Social Security Number (‘Engine’ column will not work; duplicate entries) Row: Record © Stephan Sorger 2015. www. stephansorger. com; Database Query with My. SQL; SQL. 4
My. SQL: Hosting Go to 000 webhost. com (or other similar Web hosting service) and get an account © Stephan Sorger 2015. www. stephansorger. com; Database Query with My. SQL; SQL. 5
My. SQL Server: Setup Creating My. SQL Databases for hosting accounts on Go. Daddy. com © Stephan Sorger 2015. www. stephansorger. com; Database Query with My. SQL; SQL. 6
My. SQL: Server Setup For Go. Daddy, launch hosting account to see c. Panel (shown). Click on My. SQL Database Wizard © Stephan Sorger 2015. www. stephansorger. com; Database Query with My. SQL; SQL. 7
My. SQL Server: Setup Enter name for database © Stephan Sorger 2015. www. stephansorger. com; Database Query with My. SQL; SQL. 8
My. SQL Server: Setup Enter username and password © Stephan Sorger 2015. www. stephansorger. com; Database Query with My. SQL; SQL. 9
My. SQL Server: Setup Select privileges for user © Stephan Sorger 2015. www. stephansorger. com; Database Query with My. SQL; SQL. 10
My. SQL Server: Setup Select php. My. Admin © Stephan Sorger 2015. www. stephansorger. com; Database Query with My. SQL; SQL. 11
My. SQL: Importing Databases Select your Database (Database 1) © Stephan Sorger 2015. www. stephansorger. com; Database Query with My. SQL; SQL. 12
My. SQL: Importing Databases Select Import © Stephan Sorger 2015. www. stephansorger. com; Database Query with My. SQL; SQL. 13
My. SQL: Importing Databases My. SQL Sample Databases: dev. mysql. com/doc/index-other. html © Stephan Sorger 2015. www. stephansorger. com; Database Query with My. SQL; SQL. 14
My. SQL: Importing Databases Choose File: Dataset: world. sql Click “Go” (bottom of page) © Stephan Sorger 2015. www. stephansorger. com; Database Query with My. SQL; SQL. 15
My. SQL: Importing Databases Imported OK; Click “Database 1” © Stephan Sorger 2015. www. stephansorger. com; Database Query with My. SQL; SQL. 16
My. SQL: Data Structure Check “City” Table within World database Click on SQL tab © Stephan Sorger 2015. www. stephansorger. com; Database Query with My. SQL; SQL. 17
My. SQL: Data Structure In SQL tab, enter “SHOW DATABASES” and hit “Go” SQL commands also called “queries” are in ALL CAPS Table entries are in lower case or Mixed Case © Stephan Sorger 2015. www. stephansorger. com; Database Query with My. SQL; SQL. 18
My. SQL: Data Structure Shows databases: information_schema (already in SQL server) Database 1 (we created) © Stephan Sorger 2015. www. stephansorger. com; Database Query with My. SQL; SQL. 19
My. SQL: Data Structure In SQL tab, enter “SHOW TABLES” and hit “Go” © Stephan Sorger 2015. www. stephansorger. com; Database Query with My. SQL; SQL. 20
My. SQL: Data Structure Tables: City Country Language © Stephan Sorger 2015. www. stephansorger. com; Database Query with My. SQL; SQL. 21
My. SQL: SHOW Commands View columns of table Click on SQL tab Syntax: SHOW COLUMNS FROM (table) Here: SHOW COLUMNS FROM City Then click Go © Stephan Sorger 2015. www. stephansorger. com; Database Query with My. SQL; SQL. 22
My. SQL: SHOW Commands Results: See columns from table Note PRI (primary) key Here, PRI = ID field © Stephan Sorger 2015. www. stephansorger. com; Database Query with My. SQL; SQL. 23
My. SQL: SHOW Commands Click on file structure list in left column to see table Here, click on “City” © Stephan Sorger 2015. www. stephansorger. com; Database Query with My. SQL; SQL. 24
My. SQL: SELECT Commands SELECT Name FROM City and click Go Note column names on right Note commands on bottom © Stephan Sorger 2015. www. stephansorger. com; Database Query with My. SQL; SQL. 25
My. SQL: SELECT Commands Result: Names of cities © Stephan Sorger 2015. www. stephansorger. com; Database Query with My. SQL; SQL. 26
My. SQL: SELECT Commands Select multiple columns from tables using comma (, ) between column labels Note semicolon at end of line (most compilers will run command without semicolon if command is only 1 line) SELECT Name, Country. Code FROM City; © Stephan Sorger 2015. www. stephansorger. com; Database Query with My. SQL; SQL. 27
My. SQL: SELECT Commands Result: Names of cities AND Names of Country Codes © Stephan Sorger 2015. www. stephansorger. com; Database Query with My. SQL; SQL. 28
My. SQL: SELECT Commands Insert wildcard character (*) to retrieve all information SELECT * FROM City © Stephan Sorger 2015. www. stephansorger. com; Database Query with My. SQL; SQL. 29
My. SQL: SELECT Commands Result: Entire dataset © Stephan Sorger 2015. www. stephansorger. com; Database Query with My. SQL; SQL. 30
My. SQL: SELECT with Distinct and LIMIT DISTINCT: To show only one instance of a particular value SELECT DISTINCT Country. Code FROM City © Stephan Sorger 2015. www. stephansorger. com; Database Query with My. SQL; SQL. 31
My. SQL: SELECT with Distinct and LIMIT Result: Distinct country codes © Stephan Sorger 2015. www. stephansorger. com; Database Query with My. SQL; SQL. 32
My. SQL: SELECT with Distinct and LIMIT Show only first 5 using LIMIT command SELECT Name, Country. Code FROM City LIMIT 5 © Stephan Sorger 2015. www. stephansorger. com; Database Query with My. SQL; SQL. 33
My. SQL: SELECT with Distinct and LIMIT Result: First 5 entries © Stephan Sorger 2015. www. stephansorger. com; Database Query with My. SQL; SQL. 34
My. SQL: SELECT with Distinct and LIMIT Select only limited set of entries From start point (say, 5) To a certain number of data points (say, 10) (Computers start counting at 0, not at 1, so results will show “ 6”, not “ 5” SELECT Name, Country. Code FROM City LIMIT 5, 10 © Stephan Sorger 2015. www. stephansorger. com; Database Query with My. SQL; SQL. 35
My. SQL: SELECT with Distinct and LIMIT Result: Results 5 - 15 © Stephan Sorger 2015. www. stephansorger. com; Database Query with My. SQL; SQL. 36
My. SQL: SELECT with Distinct and LIMIT Fully Qualified Names: “dataset. label” Handy if working on multiple databases with the same column SELECT City. Name FROM City © Stephan Sorger 2015. www. stephansorger. com; Database Query with My. SQL; SQL. 37
My. SQL: SELECT with Distinct and LIMIT Results: Same as before Note names in order as found in the original table; Can be confusing. Alternative: Sort. © Stephan Sorger 2015. www. stephansorger. com; Database Query with My. SQL; SQL. 38
My. SQL: Sorting Sort results using ORDER BY command SELECT Name FROM City ORDER BY Name © Stephan Sorger 2015. www. stephansorger. com; Database Query with My. SQL; SQL. 39
My. SQL: Sorting Results are now in alphabetic order © Stephan Sorger 2015. www. stephansorger. com; Database Query with My. SQL; SQL. 40
My. SQL: Sorting multiple columns, specifying order parameter SELECT Name, Country. Code, District FROM City ORDER BY Name © Stephan Sorger 2015. www. stephansorger. com; Database Query with My. SQL; SQL. 41
My. SQL: Sorting Results: Multiple columns, sorted by Name © Stephan Sorger 2015. www. stephansorger. com; Database Query with My. SQL; SQL. 42
My. SQL: Sorting Sequential Sort: Sort by x, then by y SELECT Name, Country. Code, District from City ORDER BY name, Country. Code © Stephan Sorger 2015. www. stephansorger. com; Database Query with My. SQL; SQL. 43
My. SQL: Sorting Result: Sort by Name, then by Country Code (not very interesting in this case, because data is unique for each area) © Stephan Sorger 2015. www. stephansorger. com; Database Query with My. SQL; SQL. 44
My. SQL: Sorting in reverse direction; from high to low DESC = Descending ASC = Ascending SELECT Name, Country. Code FROM City ORDER BY Name DESC © Stephan Sorger 2015. www. stephansorger. com; Database Query with My. SQL; SQL. 45
My. SQL: Sorting Result: Names from Z – A BUT: Algorithm lists special characters, such as umlauted vowels, AFTER Z © Stephan Sorger 2015. www. stephansorger. com; Database Query with My. SQL; SQL. 46
My. SQL: Sorting Select highest value (or lowest value) SELECT Name, Population FROM City ORDER BY Population LIMIT 1 © Stephan Sorger 2015. www. stephansorger. com; Database Query with My. SQL; SQL. 47
My. SQL: Sorting Results: Adamstown has the smallest population, with only 42 people © Stephan Sorger 2015. www. stephansorger. com; Database Query with My. SQL; SQL. 48
My. SQL: Sorting Select highest value (or lowest value) SELECT Name, Population FROM City ORDER BY Population DESC LIMIT 1 © Stephan Sorger 2015. www. stephansorger. com; Database Query with My. SQL; SQL. 49
My. SQL: Sorting Results: Mubai has the largest population, with 10, 500, 000 people © Stephan Sorger 2015. www. stephansorger. com; Database Query with My. SQL; SQL. 50
My. SQL: Filtering: Want only areas within country code ‘AFG’ SELECT Name, Country. Code FROM City WHERE Country. Code=‘AFG’ © Stephan Sorger 2015. www. stephansorger. com; Database Query with My. SQL; SQL. 51
My. SQL: Filtering Results © Stephan Sorger 2015. www. stephansorger. com; Database Query with My. SQL; SQL. 52
My. SQL: Filtering: Want to list “small cities”, i. e. areas with population under 10, 000 SELECT Name, Population FROM City WHERE Population <= 10000 (could also show “large cities”) : SELECT Name, Population FROM City WHERE Population >= 10000 © Stephan Sorger 2015. www. stephansorger. com; Database Query with My. SQL; SQL. 53
My. SQL: Filtering Result: Listing of “small” cities in ID order (could also sort if desired) © Stephan Sorger 2015. www. stephansorger. com; Database Query with My. SQL; SQL. 54
My. SQL: Filtering: Want to list “medium cities” i. e. areas with population between 10, 000 and 50, 000: SELECT Name, Population FROM City WHERE Population BETWEEN 10000 AND 50000 OR SELECT Name, Population FROM City WHERE Population >= 10000 && Population <= 50000 © Stephan Sorger 2015. www. stephansorger. com; Database Query with My. SQL; SQL. 55
My. SQL: Filtering Result: “Sweet spot” cities between 10, 000 and 50, 000 © Stephan Sorger 2015. www. stephansorger. com; Database Query with My. SQL; SQL. 56
My. SQL: Logical Operators Find name of city within AFG with a population greater than 200, 000 SELECT Name, Country. Code FROM City WHERE Country. Code=‘AFG’ AND Population>200000 © Stephan Sorger 2015. www. stephansorger. com; Database Query with My. SQL; SQL. 57
My. SQL: Logical Operators Result: Two cities met both criteria © Stephan Sorger 2015. www. stephansorger. com; Database Query with My. SQL; SQL. 58
My. SQL: Logical Operators Find name of city within NLD OR with a population greater than 400, 000 SELECT Name, Country. Code, Population FROM City WHERE Country. Code=‘NLD’ AND Population>400000 © Stephan Sorger 2015. www. stephansorger. com; Database Query with My. SQL; SQL. 59
My. SQL Server Result: Three cities met at least one of the criteria © Stephan Sorger 2015. www. stephansorger. com; Database Query with My. SQL; SQL. 60
My. SQL: Searching Use REGEXP (regular expression) to search for item. Here, we want to search for any district with the word “Holland” in it. SELECT Name, District FROM City WHERE District REGEXP 'Holland' © Stephan Sorger 2015. www. stephansorger. com; Database Query with My. SQL; SQL. 61
My. SQL: Searching Results: Many districts have the word “Holland” in them © Stephan Sorger 2015. www. stephansorger. com; Database Query with My. SQL; SQL. 62
My. SQL: Special Columns Create special columns using math: +, -, *, / For example, what if population were to grow by 10%? SELECT Name, Population*1. 1 AS Population_Growth FROM City © Stephan Sorger 2015. www. stephansorger. com; Database Query with My. SQL; SQL. 63
My. SQL: Special Columns Result: Special Column temporarily inserted in database: Population Growth Shows population + 10% SELECT Name, Population*1. 1 AS Population_Growth FROM City © Stephan Sorger 2015. www. stephansorger. com; Database Query with My. SQL; SQL. 64
My. SQL: Create Database Create new database called “database 2” CREATE DATABASE database 2 To delete database: DROP DATABASE database 2 © Stephan Sorger 2015. www. stephansorger. com; Database Query with My. SQL; SQL. 65
My. SQL: Create Table Create new table through php. My. Admin By clicking on Database © Stephan Sorger 2015. www. stephansorger. com; Database Query with My. SQL; SQL. 66
My. SQL: Create Table Within php. My. Admin: Create table: Enter Name Enter number of columns © Stephan Sorger 2015. www. stephansorger. com; Database Query with My. SQL; SQL. 67
My. SQL: Create Table Within My. SQL: Create new table: CREATE Drop or rename table: DROP TABLE signup RENAME TABLE signup TO users Create new table for website where people can sign up for emails CREATE TABLE signup ( id int NOT NULL AUTO_INCREMENT name varchar (30) NOT NULL email varchar (30) NOT NULL PRIMARY KEY (id) ) Open bracket; Create new lines First variable: id; data type: Integer; Automatically add 1 Second variable: name; alphanumeric text; Required Third variable: email; max length: 30 char. ; Required Declare primary key; in this case, “id” Close bracket © Stephan Sorger 2015. www. stephansorger. com; Database Query with My. SQL; SQL. 68
My. SQL: Create Table Click on Structure to see table structure id underlined PRIMARY KEY © Stephan Sorger 2015. www. stephansorger. com; Database Query with My. SQL; SQL. 69
My. SQL: Create Table Manually enter data by clicking Insert tab, then click Go © Stephan Sorger 2015. www. stephansorger. com; Database Query with My. SQL; SQL. 70
My. SQL Server Results: -Data inserted -ID incremented To Delete a Row: DELETE FROM signup WHERE name = ‘Kathy Kar’ © Stephan Sorger 2015. www. stephansorger. com; Database Query with My. SQL; SQL. 71
My. SQL: Alter Table Add column: phone (phone number) To add a column: ALTER TABLE signup ADD phone varchar(10) NOT NULL To delete a column: ALTER TABLE signup DROP COLUMN phone © Stephan Sorger 2015. www. stephansorger. com; Database Query with My. SQL; SQL. 72
My. SQL Server Results: New column inserted: “phone” © Stephan Sorger 2015. www. stephansorger. com; Database Query with My. SQL; SQL. 73
My. SQL: Reference Manuals To learn all commands, functions, etc. available, go to: dev. mysql. com/doc/index. html © Stephan Sorger 2015. www. stephansorger. com; Database Query with My. SQL; SQL. 74
Скачать презентацию Marketing Analytics Database Query with My SQL Disclaimer
d9ed389282c8faaa0cdfbb1a37b41b5b.ppt