KEMBAR78
mysql-Tutorial with Query presentation.ppt
Uploaded byaptechaligarh
PPT, PDF852 views

mysql-Tutorial with Query presentation.ppt

This document provides an introduction to MySQL. It outlines connecting to MySQL, entering basic queries, and creating and using databases and tables. Examples show how to select data from tables, sort results, and load sample data. The document is intended as a crash course to help students understand fundamental MySQL concepts.

Education◦
In this document
Powered by AI

Overview of MySQL, its popularity, and documentation sources.

Fundamentals of connecting to MySQL, using the interactive shell, and logging in.

Introduction to simple queries, SQL command syntax, multi-line queries, and command cancellation.

Steps for using and creating databases in MySQL, including checking existing databases.

Instructions on creating tables, observing structures, and deleting tables.

Methods for inserting data into tables, including individual records and bulk loading from files.

Using SELECT statements to retrieve all data, specific rows, and columns from tables.

Techniques for sorting query results in ascending or descending order.

Definition of NULL values in MySQL and how to test for them.

Using SQL pattern matching techniques, including LIKE and wildcards.

Extended regular expression matching features in MySQL.

Using COUNT() function to count the number of rows in a table.

Introduction to running MySQL commands in batch mode from a file.

Summary of what was covered and the importance of continual practice with MySQL.

Download to read offline
Introduction to MySQL
2 Road Map  Introduction to MySQL  Connecting and Disconnecting  Entering Basic Queries  Creating and Using a Database
3 Attribution  Most of these slides are based directly on the MySQL Documentation.  Most information comes from Chapter 3, MySQL Tutorial:  http://www.mysql.com/documentation/m ysql/bychapter/manual_Tutorial.html#Tu torial
4 MySQL  MySQL is a very popular, open source database.  Officially pronounced “my Ess Que Ell” (not my sequel).  Handles very large databases; very fast performance.  Why are we using MySQL?  Free (much cheaper than Oracle!)  Each student can install MySQL locally.  Easy to use Shell for creating tables, querying tables, etc.  Easy to use with Java JDBC
5 Crash Course Fundamentals  In order to use JDBC, you need:  a database.  basic understand of SQL (Structured Query Language)  Some students may have database backgrounds; others may not.  The purpose of this lecture is to get all students up to speed on database fundamentals.
6 Connecting to MySQL  MySQL provides an interactive shell for creating tables, inserting data, etc.  On Windows, just go to c:mysqlbin, and type:  mysql  Or, click on the Windows icon
7 Sample Session  For example: Enter password: ***** Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 241 to server version: 3.23.49 Type 'help;' or 'h' for help. Type 'c' to clear the buffer. mysql>  To exit the MySQL Shell, just type QUIT or EXIT: mysql> QUIT mysql> exit
8 Basic Queries  Once logged in, you can try some simple queries.  For example: mysql> SELECT VERSION(), CURRENT_DATE; +-----------+--------------+ | VERSION() | CURRENT_DATE | +-----------+--------------+ | 3.23.49 | 2002-05-26 | +-----------+--------------+ 1 row in set (0.00 sec)  Note that most MySQL commands end with a semicolon (;)  MySQL returns the total number of rows found, and the total time to execute the query.
9 Basic Queries  Keywords may be entered in any lettercase.  The following queries are equivalent: mysql> SELECT VERSION(), CURRENT_DATE; mysql> select version(), current_date; mysql> SeLeCt vErSiOn(), current_DATE;
10 Basic Queries  Here's another query. It demonstrates that you can use mysql as a simple calculator: mysql> SELECT SIN(PI()/4), (4+1)*5; +-------------+---------+ | SIN(PI()/4) | (4+1)*5 | +-------------+---------+ | 0.707107 | 25 | +-------------+---------+
11 Basic Queries  You can also enter multiple statements on a single line. Just end each one with a semicolon: mysql> SELECT VERSION(); SELECT NOW(); +--------------+ | VERSION() | +--------------+ | 3.22.20a-log | +--------------+ +---------------------+ | NOW() | +---------------------+ | 2004 00:15:33 | +---------------------+
12 Multi-Line Commands  mysql determines where your statement ends by looking for the terminating semicolon, not by looking for the end of the input line.  Here's a simple multiple-line statement: mysql> SELECT -> USER() -> , -> CURRENT_DATE; +--------------------+--------------+ | USER() | CURRENT_DATE | +--------------------+--------------+ | joesmith@localhost | 1999-03-18 | +--------------------+--------------+
13 Canceling a Command  If you decide you don't want to execute a command that you are in the process of entering, cancel it by typing c mysql> SELECT -> USER() -> c mysql>
14 Using a Database  To get started on your own database, first check which databases currently exist.  Use the SHOW statement to find out which databases currently exist on the server: mysql> show databases; +----------+ | Database | +----------+ | mysql | | test | +----------+ 2 rows in set (0.01 sec)
15 Using a Database  To create a new database, issue the “create database” command:  mysql> create database webdb;  To the select a database, issue the “use” command:  mysql> use webdb;
16 Creating a Table  Once you have selected a database, you can view all database tables: mysql> show tables; Empty set (0.02 sec)  An empty set indicates that I have not created any tables yet.
17 Creating a Table  Let’s create a table for storing pets.  Table: pets name: VARCHAR(20) owner: VARCHAR(20) species: VARCHAR(20) sex: CHAR(1) birth: DATE date: DATE VARCHAR is usually used to store string data.
18 Creating a Table  To create a table, use the CREATE TABLE command: mysql> CREATE TABLE pet ( -> name VARCHAR(20), -> owner VARCHAR(20), -> species VARCHAR(20), -> sex CHAR(1), -> birth DATE, death DATE); Query OK, 0 rows affected (0.04 sec)
19 Showing Tables  To verify that the table has been created: mysql> show tables; +------------------+ | Tables_in_test | +------------------+ | pet | +------------------+ 1 row in set (0.01 sec)
20 Describing Tables  To view a table structure, use the DESCRIBE command: mysql> describe pet; +---------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------+-------+ | name | varchar(20) | YES | | NULL | | | owner | varchar(20) | YES | | NULL | | | species | varchar(20) | YES | | NULL | | | sex | char(1) | YES | | NULL | | | birth | date | YES | | NULL | | | death | date | YES | | NULL | | +---------+-------------+------+-----+---------+-------+ 6 rows in set (0.02 sec)
21 Deleting a Table  To delete an entire table, use the DROP TABLE command: mysql> drop table pet; Query OK, 0 rows affected (0.02 sec)
22 Loading Data  Use the INSERT statement to enter data into a table.  For example: INSERT INTO pet VALUES ('Fluffy','Harold','cat','f', '1999-02-04',NULL);  The next slide shows a full set of sample data.
23 More data… name owner species sex birth death Fluffy Harold cat f 1993-02-04 Claws Gwen cat m 1994-03-17 Buffy Harold dog f 1989-05-13 Fang Benny dog m 1990-08-27 Bowser Diane dog m 1998-08-31 1995-07-29 Chirpy Gwen bird f 1998-09-11 Whistler Gwen bird 1997-12-09 Slim Benny snake m 1996-04-29
24 Loading Sample Data  You could create a text file `pet.txt' containing one record per line.  Values must be separated by tabs, and given in the order in which the columns were listed in the CREATE TABLE statement.  Then load the data via the LOAD DATA Command.
25 Sample Data File Fluffy Harold cat f 1993-02-04 N Claws Gwen cat m 1994-03-17 N Buffy Harold dog f 1989-05-13 N Fang Benny dog m 1990-08-27 N Bowser Diane dog m 1979-08-31 1995-07-29 Chirpy Gwen bird f 1998-09-11 N WhistlerGwen bird N 1997-12-09 N Slim Benny snake m 1996-04-29 N To Load pet.txt: mysql> LOAD DATA LOCAL INFILE "pet.txt" INTO TABLE pet;
26 For each of the examples, assume the following set of data. name owner species sex birth death Fluffy Harold cat f 1993-02-04 Claws Gwen cat m 1994-03-17 Buffy Harold dog f 1989-05-13 Fang Benny dog m 1990-08-27 Bowser Diane dog m 1998-08-31 1995-07-29 Chirpy Gwen bird f 1998-09-11 Whistler Gwen bird 1997-12-09 Slim Benny snake m 1996-04-29
27 SQL Select  The SELECT statement is used to pull information from a table.  The general format is: SELECT what_to_select FROM which_table WHERE conditions_to_satisfy
28 Selecting All Data  The simplest form of SELECT retrieves everything from a table mysql> select * from pet; +----------+--------+---------+------+------------+------------+ | name | owner | species | sex | birth | death | +----------+--------+---------+------+------------+------------+ | Fluffy | Harold | cat | f | 1999-02-04 | NULL | | Claws | Gwen | cat | f | 1994-03-17 | NULL | | Buffy | Harold | dog | f | 1989-05-13 | NULL | | Fang | Benny | dog | m | 1999-08-27 | NULL | | Bowser | Diane | dog | m | 1998-08-31 | 1995-07-29 | | Chirpy | Gwen | bird | f | 1998-09-11 | NULL | | Whistler | Gwen | bird | | 1997-12-09 | NULL | | Slim | Benny | snake | m | 1996-04-29 | NULL | +----------+--------+---------+------+------------+------------+ 8 rows in set (0.00 sec)
29 Selecting Particular Rows  You can select only particular rows from your table.  For example, if you want to verify the change that you made to Bowser's birth date, select Bowser's record like this: mysql> SELECT * FROM pet WHERE name = "Bowser"; +--------+-------+---------+------+------------+------------+ | name | owner | species | sex | birth | death | +--------+-------+---------+------+------------+------------+ | Bowser | Diane | dog | m | 1998-08-31 | 1995-07-29 | +--------+-------+---------+------+------------+------------+ 1 row in set (0.00 sec)
30 Selecting Particular Rows  To find all animals born after 1998 SELECT * FROM pet WHERE birth >= "1998-1-1";  To find all female dogs, use a logical AND SELECT * FROM pet WHERE species = "dog" AND sex = "f";  To find all snakes or birds, use a logical OR SELECT * FROM pet WHERE species = "snake" OR species = "bird";
31 Selecting Particular Columns  If you don’t want to see entire rows from your table, just name the columns in which you are interested, separated by commas.  For example, if you want to know when your pets were born, select the name and birth columns.  (see example next slide.)
32 Selecting Particular Columns mysql> select name, birth from pet; +----------+------------+ | name | birth | +----------+------------+ | Fluffy | 1999-02-04 | | Claws | 1994-03-17 | | Buffy | 1989-05-13 | | Fang | 1999-08-27 | | Bowser | 1998-08-31 | | Chirpy | 1998-09-11 | | Whistler | 1997-12-09 | | Slim | 1996-04-29 | +----------+------------+ 8 rows in set (0.01 sec)
33 Sorting Data  To sort a result, use an ORDER BY clause.  For example, to view animal birthdays, sorted by date: mysql> SELECT name, birth FROM pet ORDER BY birth; +----------+------------+ | name | birth | +----------+------------+ | Buffy | 1989-05-13 | | Claws | 1994-03-17 | | Slim | 1996-04-29 | | Whistler | 1997-12-09 | | Bowser | 1998-08-31 | | Chirpy | 1998-09-11 | | Fluffy | 1999-02-04 | | Fang | 1999-08-27 | +----------+------------+ 8 rows in set (0.02 sec)
34 Sorting Data  To sort in reverse order, add the DESC (descending keyword) mysql> SELECT name, birth FROM pet ORDER BY birth DESC; +----------+------------+ | name | birth | +----------+------------+ | Fang | 1999-08-27 | | Fluffy | 1999-02-04 | | Chirpy | 1998-09-11 | | Bowser | 1998-08-31 | | Whistler | 1997-12-09 | | Slim | 1996-04-29 | | Claws | 1994-03-17 | | Buffy | 1989-05-13 | +----------+------------+ 8 rows in set (0.02 sec)
35 Working with NULLs  NULL means missing value or unknown value.  To test for NULL, you cannot use the arithmetic comparison operators, such as =, < or <>.  Rather, you must use the IS NULL and IS NOT NULL operators instead.
36 Working with NULLs  For example, to find all your dead pets (what a morbid example!) mysql> select name from pet where death >IS NOT NULL; +--------+ | name | +--------+ | Bowser | +--------+ 1 row in set (0.01 sec)
37 Pattern Matching  MySQL provides:  standard SQL pattern matching; and  regular expression pattern matching, similar to those used by Unix utilities such as vi, grep and sed.  SQL Pattern matching:  To perform pattern matching, use the LIKE or NOT LIKE comparison operators  By default, patterns are case insensitive.  Special Characters:  _ Used to match any single character.  % Used to match an arbitrary number of characters.
38 Pattern Matching Example  To find names beginning with ‘b’: mysql> SELECT * FROM pet WHERE name LIKE "b%"; +--------+--------+---------+------+------------+------------+ | name | owner | species | sex | birth | death | +--------+--------+---------+------+------------+------------+ | Buffy | Harold | dog | f | 1989-05-13 | NULL | | Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 | +--------+--------+---------+------+------------+------------+
39 Pattern Matching Example  To find names ending with `fy': mysql> SELECT * FROM pet WHERE name LIKE "%fy"; +--------+--------+---------+------+------------+-------+ | name | owner | species | sex | birth | death | +--------+--------+---------+------+------------+-------+ | Fluffy | Harold | cat | f | 1993-02-04 | NULL | | Buffy | Harold | dog | f | 1989-05-13 | NULL | +--------+--------+---------+------+------------+-------+
40 Pattern Matching Example  To find names containing a ‘w’: mysql> SELECT * FROM pet WHERE name LIKE "%w%"; +----------+-------+---------+------+------------+------------+ | name | owner | species | sex | birth | death | +----------+-------+---------+------+------------+------------+ | Claws | Gwen | cat | m | 1994-03-17 | NULL | | Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 | | Whistler | Gwen | bird | NULL | 1997-12-09 | NULL | +----------+-------+---------+------+------------+------------+
41 Pattern Matching Example  To find names containing exactly five characters, use the _ pattern character: mysql> SELECT * FROM pet WHERE name LIKE "_____"; +-------+--------+---------+------+------------+-------+ | name | owner | species | sex | birth | death | +-------+--------+---------+------+------------+-------+ | Claws | Gwen | cat | m | 1994-03-17 | NULL | | Buffy | Harold | dog | f | 1989-05-13 | NULL | +-------+--------+---------+------+------------+-------+
42 Regular Expression Matching  The other type of pattern matching provided by MySQL uses extended regular expressions.  When you test for a match for this type of pattern, use the REGEXP and NOT REGEXP operators (or RLIKE and NOT RLIKE, which are synonyms).
43 Regular Expressions  Some characteristics of extended regular expressions are:  . matches any single character.  A character class [...] matches any character within the brackets. For example, [abc] matches a, b, or c. To name a range of characters, use a dash. [a-z] matches any lowercase letter, whereas [0-9] matches any digit.  * matches zero or more instances of the thing preceding it. For example, x* matches any number of x characters, [0-9]* matches any number of digits, and .* matches any number of anything.  To anchor a pattern so that it must match the beginning or end of the value being tested, use ^ at the beginning or $ at the end of the pattern.
44 Reg Ex Example  To find names beginning with b, use ^ to match the beginning of the name: mysql> SELECT * FROM pet WHERE name REGEXP "^b"; +--------+--------+---------+------+------------+------------+ | name | owner | species | sex | birth | death | +--------+--------+---------+------+------------+------------+ | Buffy | Harold | dog | f | 1989-05-13 | NULL | | Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 | +--------+--------+---------+------+------------+------------+
45 Reg Ex Example  To find names ending with `fy', use `$' to match the end of the name: mysql> SELECT * FROM pet WHERE name REGEXP "fy$"; +--------+--------+---------+------+------------+-------+ | name | owner | species | sex | birth | death | +--------+--------+---------+------+------------+-------+ | Fluffy | Harold | cat | f | 1993-02-04 | NULL | | Buffy | Harold | dog | f | 1989-05-13 | NULL | +--------+--------+---------+------+------------+-------+
46 Counting Rows  Databases are often used to answer the question, "How often does a certain type of data occur in a table?"  For example, you might want to know how many pets you have, or how many pets each owner has.  Counting the total number of animals you have is the same question as “How many rows are in the pet table?” because there is one record per pet.  The COUNT() function counts the number of non- NULL results.
47 Counting Rows Example  A query to determine total number of pets: mysql> SELECT COUNT(*) FROM pet; +----------+ | COUNT(*) | +----------+ | 9 | +----------+
48 Batch Mode  In the previous sections, you used mysql interactively to enter queries and view the results.  You can also run mysql in batch mode. To do this, put the commands you want to run in a file, then tell mysql to read its input from the file:  shell> mysql < batch-file
49 Is that all there is to MySQL?  Of course not!  Understanding databases and MySQL could take us several weeks (perhaps months!)  For now, focus on:  using the MySQL shell  creating tables  creating basic SQL queries
50 Summary  SQL provides a structured language for querying/updating multiple databases.  The more you know SQL, the better.  The most important part of SQL is learning to retrieve data.  selecting rows, columns, boolean operators, pattern matching, etc.  Keep playing around in the MySQL Shell.

More Related Content

PPT
Working with Databases and MySQL
byNicole Ryan
 
PPTX
Mysql Crud, Php Mysql, php, sql
byAimal Miakhel
 
PPTX
Sessions in php
byMudasir Syed
 
PPTX
PHP and MySQL.pptx
bynatesanp1234
 
PPT
MySql slides (ppt)
bywebhostingguy
 
PPTX
MySQL Database with phpMyAdmin
byKarwan Mustafa Kareem
 
PPT
Mysql
byTSUBHASHRI
 
PPTX
Mysql-overview.pptx
byTamilHunt
 
Working with Databases and MySQL
byNicole Ryan
 
Mysql Crud, Php Mysql, php, sql
byAimal Miakhel
 
Sessions in php
byMudasir Syed
 
PHP and MySQL.pptx
bynatesanp1234
 
MySql slides (ppt)
bywebhostingguy
 
MySQL Database with phpMyAdmin
byKarwan Mustafa Kareem
 
Mysql
byTSUBHASHRI
 
Mysql-overview.pptx
byTamilHunt
 

What's hot

PPTX
Relational Database Design
byArchit Saxena
 
PPT
Normalization
bymomo2187
 
PPT
Java collection
byArati Gadgil
 
PDF
Object Based Databases
byFarzad Nozarian
 
PDF
Normalization in DBMS
byHitesh Mohapatra
 
ODP
Datatype in JavaScript
byRajat Saxena
 
PPTX
Lab2 ddl commands
byBalqees Al.Mubarak
 
PDF
Triggers and Stored Procedures
byTharindu Weerasinghe
 
PPT
MYSQL - PHP Database Connectivity
byV.V.Vanniaperumal College for Women
 
PPTX
database Normalization
byHarsiddhi Thakkar
 
PPT
MySQL and its basic commands
byBwsrang Basumatary
 
PPTX
DML, DDL, DCL ,DRL/DQL and TCL Statements in SQL with Examples
byLGS, GBHS&IC, University Of South-Asia, TARA-Technologies
 
PDF
View & index in SQL
bySwapnali Pawar
 
PPTX
SQL - DML and DDL Commands
byShrija Madhu
 
PPTX
SQL
byReimuel Bisnar
 
PPT
MySQL
byGouthaman V
 
ODP
OOP java
byxball977
 
PPT
SQL Queries
byNilt1234
 
PPT
Databases: Normalisation
byDamian T. Gordon
 
PPTX
PHP FUNCTIONS
byZeeshan Ahmed
 
Relational Database Design
byArchit Saxena
 
Normalization
bymomo2187
 
Java collection
byArati Gadgil
 
Object Based Databases
byFarzad Nozarian
 
Normalization in DBMS
byHitesh Mohapatra
 
Datatype in JavaScript
byRajat Saxena
 
Lab2 ddl commands
byBalqees Al.Mubarak
 
Triggers and Stored Procedures
byTharindu Weerasinghe
 
MYSQL - PHP Database Connectivity
byV.V.Vanniaperumal College for Women
 
database Normalization
byHarsiddhi Thakkar
 
MySQL and its basic commands
byBwsrang Basumatary
 
DML, DDL, DCL ,DRL/DQL and TCL Statements in SQL with Examples
byLGS, GBHS&IC, University Of South-Asia, TARA-Technologies
 
View & index in SQL
bySwapnali Pawar
 
SQL - DML and DDL Commands
byShrija Madhu
 
SQL
byReimuel Bisnar
 
MySQL
byGouthaman V
 
OOP java
byxball977
 
SQL Queries
byNilt1234
 
Databases: Normalisation
byDamian T. Gordon
 
PHP FUNCTIONS
byZeeshan Ahmed
 

Similar to mysql-Tutorial with Query presentation.ppt

PPT
Intro to my sql
byMinhaj Kazi
 
PPT
Intro to my sql
bysagaroceanic11
 
PPT
Mysql Database and query formation using
byrkmvedant
 
PPT
My sql statements by okello erick
byokelloerick
 
PPTX
Intro to my sql
byAlamgir Bhuyan
 
PPTX
Lecture5 my sql statements by okello erick
byokelloerick
 
PPTX
Lecture2 mysql by okello erick
byokelloerick
 
PPTX
Lecture3 mysql gui by okello erick
byokelloerick
 
PPT
Basic Commands using Structured Query Langauage(SQL)
byJyotiNandimath
 
PPT
Intro to my sql
byMusTufa Nullwala
 
PPTX
Introduction databases and MYSQL
byNaeem Junejo
 
PPTX
PHP mysql Introduction database
byMudasir Syed
 
PPTX
DATA BASE || INTRODUCTION OF DATABASE \\ SQL 2018
byteachersduniya.com
 
PPTX
Mysqlppt
byNEERAJ KUMAR
 
PDF
mysqlanditsbasiccommands-150226033905-conversion-gate02.pdf
bypradnyamulay
 
PPTX
Getting Started with MySQL I
bySankhya_Analytics
 
PPT
My sql1
byNV Chandra Sekhar Nittala
 
PDF
Introduction to MySQL and introduction to basic queries
byanishacotta2
 
PPTX
Using Mysql.pptx
byStephenEfange3
 
PPTX
Chapter-9-MySQL.pptxxzrtyryrydfdsfdsfdsrter
byzmulani8
 
Intro to my sql
byMinhaj Kazi
 
Intro to my sql
bysagaroceanic11
 
Mysql Database and query formation using
byrkmvedant
 
My sql statements by okello erick
byokelloerick
 
Intro to my sql
byAlamgir Bhuyan
 
Lecture5 my sql statements by okello erick
byokelloerick
 
Lecture2 mysql by okello erick
byokelloerick
 
Lecture3 mysql gui by okello erick
byokelloerick
 
Basic Commands using Structured Query Langauage(SQL)
byJyotiNandimath
 
Intro to my sql
byMusTufa Nullwala
 
Introduction databases and MYSQL
byNaeem Junejo
 
PHP mysql Introduction database
byMudasir Syed
 
DATA BASE || INTRODUCTION OF DATABASE \\ SQL 2018
byteachersduniya.com
 
Mysqlppt
byNEERAJ KUMAR
 
mysqlanditsbasiccommands-150226033905-conversion-gate02.pdf
bypradnyamulay
 
Getting Started with MySQL I
bySankhya_Analytics
 
My sql1
byNV Chandra Sekhar Nittala
 
Introduction to MySQL and introduction to basic queries
byanishacotta2
 
Using Mysql.pptx
byStephenEfange3
 
Chapter-9-MySQL.pptxxzrtyryrydfdsfdsfdsrter
byzmulani8
 

Recently uploaded

DOCX
Extension Education: From theory to practice by Dr Subin K Mohan.docx
byDrSubinKMohan
 
PPTX
Essay Type Answer Writing Analysis Workshop.pptx
byDhatriParmar
 
PPTX
Capitol Webinar October 2025 Dr. Jha.pptx
byCapitolTechU
 
PDF
Umlando kaMufi. IsiZulu Ulimi Lwebele...
byNokwandaNzuza2
 
PPTX
Single entry System Vs Double entry System.pptx
bylavanyafranklin0804
 
PDF
Admin Slides for Oct'25 semester (Progression)
byGreat Files
 
PPTX
DPSM-BITDA Introduction Presentation Slides
byGreat Files
 
PPTX
GAS chromatography ppt (Presentation) by Jatin Chauhan
byjatinchauhan1618
 
DOCX
PRESS STATEMENT - Response to Minority_ Press Ready.docx
bynservice241
 
PDF
Who Owns the Narrative? Data, Disinformation, and the Missing Voice of Academia
byIsmail Fahmi
 
PDF
Phase Equilibria and Colligative Properties.pdf
byMithil Fal Desai
 
PDF
BUSINESS ETHICS – UNIT V: Ethical Insights from Thirukkural and Modern Thought
byD NANEE
 
PDF
Admin Slides for Oct'25 semester - PBO - MC1.pdf
byMark Kor
 
PPTX
English Home Language & First Additional Language P2 Preparation.pptx
byMasingita Maswanganye
 
PPTX
How can education build trust in a polarised world_Jonathan James_OECD .pptx
byEduSkills OECD
 
PDF
The Minority Caucus in Parliament has called on government to take immediate ...
bynservice241
 
PPTX
Exploring Entrepreneurial Qualities: A Curated Presentation for Grade 11 Busi...
bySamanthaNkoana
 
PPTX
Common problems or challenges faced by Indian adolescents
byDr. Harpal Kaur
 
PDF
Nernst Distribution Law and factors affecting distribution constant
byMithil Fal Desai
 
PDF
BD1TL - TNTEU - Teaching and Learning - Unit - 2.pdf
byDr Raja Mohammed T
 
Extension Education: From theory to practice by Dr Subin K Mohan.docx
byDrSubinKMohan
 
Essay Type Answer Writing Analysis Workshop.pptx
byDhatriParmar
 
Capitol Webinar October 2025 Dr. Jha.pptx
byCapitolTechU
 
Umlando kaMufi. IsiZulu Ulimi Lwebele...
byNokwandaNzuza2
 
Single entry System Vs Double entry System.pptx
bylavanyafranklin0804
 
Admin Slides for Oct'25 semester (Progression)
byGreat Files
 
DPSM-BITDA Introduction Presentation Slides
byGreat Files
 
GAS chromatography ppt (Presentation) by Jatin Chauhan
byjatinchauhan1618
 
PRESS STATEMENT - Response to Minority_ Press Ready.docx
bynservice241
 
Who Owns the Narrative? Data, Disinformation, and the Missing Voice of Academia
byIsmail Fahmi
 
Phase Equilibria and Colligative Properties.pdf
byMithil Fal Desai
 
BUSINESS ETHICS – UNIT V: Ethical Insights from Thirukkural and Modern Thought
byD NANEE
 
Admin Slides for Oct'25 semester - PBO - MC1.pdf
byMark Kor
 
English Home Language & First Additional Language P2 Preparation.pptx
byMasingita Maswanganye
 
How can education build trust in a polarised world_Jonathan James_OECD .pptx
byEduSkills OECD
 
The Minority Caucus in Parliament has called on government to take immediate ...
bynservice241
 
Exploring Entrepreneurial Qualities: A Curated Presentation for Grade 11 Busi...
bySamanthaNkoana
 
Common problems or challenges faced by Indian adolescents
byDr. Harpal Kaur
 
Nernst Distribution Law and factors affecting distribution constant
byMithil Fal Desai
 
BD1TL - TNTEU - Teaching and Learning - Unit - 2.pdf
byDr Raja Mohammed T
 

mysql-Tutorial with Query presentation.ppt

  • 1.
  • 2.
    2 Road Map  Introductionto MySQL  Connecting and Disconnecting  Entering Basic Queries  Creating and Using a Database
  • 3.
    3 Attribution  Most ofthese slides are based directly on the MySQL Documentation.  Most information comes from Chapter 3, MySQL Tutorial:  http://www.mysql.com/documentation/m ysql/bychapter/manual_Tutorial.html#Tu torial
  • 4.
    4 MySQL  MySQL isa very popular, open source database.  Officially pronounced “my Ess Que Ell” (not my sequel).  Handles very large databases; very fast performance.  Why are we using MySQL?  Free (much cheaper than Oracle!)  Each student can install MySQL locally.  Easy to use Shell for creating tables, querying tables, etc.  Easy to use with Java JDBC
  • 5.
    5 Crash Course Fundamentals In order to use JDBC, you need:  a database.  basic understand of SQL (Structured Query Language)  Some students may have database backgrounds; others may not.  The purpose of this lecture is to get all students up to speed on database fundamentals.
  • 6.
    6 Connecting to MySQL MySQL provides an interactive shell for creating tables, inserting data, etc.  On Windows, just go to c:mysqlbin, and type:  mysql  Or, click on the Windows icon
  • 7.
    7 Sample Session  Forexample: Enter password: ***** Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 241 to server version: 3.23.49 Type 'help;' or 'h' for help. Type 'c' to clear the buffer. mysql>  To exit the MySQL Shell, just type QUIT or EXIT: mysql> QUIT mysql> exit
  • 8.
    8 Basic Queries  Oncelogged in, you can try some simple queries.  For example: mysql> SELECT VERSION(), CURRENT_DATE; +-----------+--------------+ | VERSION() | CURRENT_DATE | +-----------+--------------+ | 3.23.49 | 2002-05-26 | +-----------+--------------+ 1 row in set (0.00 sec)  Note that most MySQL commands end with a semicolon (;)  MySQL returns the total number of rows found, and the total time to execute the query.
  • 9.
    9 Basic Queries  Keywordsmay be entered in any lettercase.  The following queries are equivalent: mysql> SELECT VERSION(), CURRENT_DATE; mysql> select version(), current_date; mysql> SeLeCt vErSiOn(), current_DATE;
  • 10.
    10 Basic Queries  Here'sanother query. It demonstrates that you can use mysql as a simple calculator: mysql> SELECT SIN(PI()/4), (4+1)*5; +-------------+---------+ | SIN(PI()/4) | (4+1)*5 | +-------------+---------+ | 0.707107 | 25 | +-------------+---------+
  • 11.
    11 Basic Queries  Youcan also enter multiple statements on a single line. Just end each one with a semicolon: mysql> SELECT VERSION(); SELECT NOW(); +--------------+ | VERSION() | +--------------+ | 3.22.20a-log | +--------------+ +---------------------+ | NOW() | +---------------------+ | 2004 00:15:33 | +---------------------+
  • 12.
    12 Multi-Line Commands  mysqldetermines where your statement ends by looking for the terminating semicolon, not by looking for the end of the input line.  Here's a simple multiple-line statement: mysql> SELECT -> USER() -> , -> CURRENT_DATE; +--------------------+--------------+ | USER() | CURRENT_DATE | +--------------------+--------------+ | joesmith@localhost | 1999-03-18 | +--------------------+--------------+
  • 13.
    13 Canceling a Command If you decide you don't want to execute a command that you are in the process of entering, cancel it by typing c mysql> SELECT -> USER() -> c mysql>
  • 14.
    14 Using a Database To get started on your own database, first check which databases currently exist.  Use the SHOW statement to find out which databases currently exist on the server: mysql> show databases; +----------+ | Database | +----------+ | mysql | | test | +----------+ 2 rows in set (0.01 sec)
  • 15.
    15 Using a Database To create a new database, issue the “create database” command:  mysql> create database webdb;  To the select a database, issue the “use” command:  mysql> use webdb;
  • 16.
    16 Creating a Table Once you have selected a database, you can view all database tables: mysql> show tables; Empty set (0.02 sec)  An empty set indicates that I have not created any tables yet.
  • 17.
    17 Creating a Table Let’s create a table for storing pets.  Table: pets name: VARCHAR(20) owner: VARCHAR(20) species: VARCHAR(20) sex: CHAR(1) birth: DATE date: DATE VARCHAR is usually used to store string data.
  • 18.
    18 Creating a Table To create a table, use the CREATE TABLE command: mysql> CREATE TABLE pet ( -> name VARCHAR(20), -> owner VARCHAR(20), -> species VARCHAR(20), -> sex CHAR(1), -> birth DATE, death DATE); Query OK, 0 rows affected (0.04 sec)
  • 19.
    19 Showing Tables  Toverify that the table has been created: mysql> show tables; +------------------+ | Tables_in_test | +------------------+ | pet | +------------------+ 1 row in set (0.01 sec)
  • 20.
    20 Describing Tables  Toview a table structure, use the DESCRIBE command: mysql> describe pet; +---------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------+-------+ | name | varchar(20) | YES | | NULL | | | owner | varchar(20) | YES | | NULL | | | species | varchar(20) | YES | | NULL | | | sex | char(1) | YES | | NULL | | | birth | date | YES | | NULL | | | death | date | YES | | NULL | | +---------+-------------+------+-----+---------+-------+ 6 rows in set (0.02 sec)
  • 21.
    21 Deleting a Table To delete an entire table, use the DROP TABLE command: mysql> drop table pet; Query OK, 0 rows affected (0.02 sec)
  • 22.
    22 Loading Data  Usethe INSERT statement to enter data into a table.  For example: INSERT INTO pet VALUES ('Fluffy','Harold','cat','f', '1999-02-04',NULL);  The next slide shows a full set of sample data.
  • 23.
    23 More data… name ownerspecies sex birth death Fluffy Harold cat f 1993-02-04 Claws Gwen cat m 1994-03-17 Buffy Harold dog f 1989-05-13 Fang Benny dog m 1990-08-27 Bowser Diane dog m 1998-08-31 1995-07-29 Chirpy Gwen bird f 1998-09-11 Whistler Gwen bird 1997-12-09 Slim Benny snake m 1996-04-29
  • 24.
    24 Loading Sample Data You could create a text file `pet.txt' containing one record per line.  Values must be separated by tabs, and given in the order in which the columns were listed in the CREATE TABLE statement.  Then load the data via the LOAD DATA Command.
  • 25.
    25 Sample Data File FluffyHarold cat f 1993-02-04 N Claws Gwen cat m 1994-03-17 N Buffy Harold dog f 1989-05-13 N Fang Benny dog m 1990-08-27 N Bowser Diane dog m 1979-08-31 1995-07-29 Chirpy Gwen bird f 1998-09-11 N WhistlerGwen bird N 1997-12-09 N Slim Benny snake m 1996-04-29 N To Load pet.txt: mysql> LOAD DATA LOCAL INFILE "pet.txt" INTO TABLE pet;
  • 26.
    26 For each ofthe examples, assume the following set of data. name owner species sex birth death Fluffy Harold cat f 1993-02-04 Claws Gwen cat m 1994-03-17 Buffy Harold dog f 1989-05-13 Fang Benny dog m 1990-08-27 Bowser Diane dog m 1998-08-31 1995-07-29 Chirpy Gwen bird f 1998-09-11 Whistler Gwen bird 1997-12-09 Slim Benny snake m 1996-04-29
  • 27.
    27 SQL Select  TheSELECT statement is used to pull information from a table.  The general format is: SELECT what_to_select FROM which_table WHERE conditions_to_satisfy
  • 28.
    28 Selecting All Data The simplest form of SELECT retrieves everything from a table mysql> select * from pet; +----------+--------+---------+------+------------+------------+ | name | owner | species | sex | birth | death | +----------+--------+---------+------+------------+------------+ | Fluffy | Harold | cat | f | 1999-02-04 | NULL | | Claws | Gwen | cat | f | 1994-03-17 | NULL | | Buffy | Harold | dog | f | 1989-05-13 | NULL | | Fang | Benny | dog | m | 1999-08-27 | NULL | | Bowser | Diane | dog | m | 1998-08-31 | 1995-07-29 | | Chirpy | Gwen | bird | f | 1998-09-11 | NULL | | Whistler | Gwen | bird | | 1997-12-09 | NULL | | Slim | Benny | snake | m | 1996-04-29 | NULL | +----------+--------+---------+------+------------+------------+ 8 rows in set (0.00 sec)
  • 29.
    29 Selecting Particular Rows You can select only particular rows from your table.  For example, if you want to verify the change that you made to Bowser's birth date, select Bowser's record like this: mysql> SELECT * FROM pet WHERE name = "Bowser"; +--------+-------+---------+------+------------+------------+ | name | owner | species | sex | birth | death | +--------+-------+---------+------+------------+------------+ | Bowser | Diane | dog | m | 1998-08-31 | 1995-07-29 | +--------+-------+---------+------+------------+------------+ 1 row in set (0.00 sec)
  • 30.
    30 Selecting Particular Rows To find all animals born after 1998 SELECT * FROM pet WHERE birth >= "1998-1-1";  To find all female dogs, use a logical AND SELECT * FROM pet WHERE species = "dog" AND sex = "f";  To find all snakes or birds, use a logical OR SELECT * FROM pet WHERE species = "snake" OR species = "bird";
  • 31.
    31 Selecting Particular Columns If you don’t want to see entire rows from your table, just name the columns in which you are interested, separated by commas.  For example, if you want to know when your pets were born, select the name and birth columns.  (see example next slide.)
  • 32.
    32 Selecting Particular Columns mysql>select name, birth from pet; +----------+------------+ | name | birth | +----------+------------+ | Fluffy | 1999-02-04 | | Claws | 1994-03-17 | | Buffy | 1989-05-13 | | Fang | 1999-08-27 | | Bowser | 1998-08-31 | | Chirpy | 1998-09-11 | | Whistler | 1997-12-09 | | Slim | 1996-04-29 | +----------+------------+ 8 rows in set (0.01 sec)
  • 33.
    33 Sorting Data  Tosort a result, use an ORDER BY clause.  For example, to view animal birthdays, sorted by date: mysql> SELECT name, birth FROM pet ORDER BY birth; +----------+------------+ | name | birth | +----------+------------+ | Buffy | 1989-05-13 | | Claws | 1994-03-17 | | Slim | 1996-04-29 | | Whistler | 1997-12-09 | | Bowser | 1998-08-31 | | Chirpy | 1998-09-11 | | Fluffy | 1999-02-04 | | Fang | 1999-08-27 | +----------+------------+ 8 rows in set (0.02 sec)
  • 34.
    34 Sorting Data  Tosort in reverse order, add the DESC (descending keyword) mysql> SELECT name, birth FROM pet ORDER BY birth DESC; +----------+------------+ | name | birth | +----------+------------+ | Fang | 1999-08-27 | | Fluffy | 1999-02-04 | | Chirpy | 1998-09-11 | | Bowser | 1998-08-31 | | Whistler | 1997-12-09 | | Slim | 1996-04-29 | | Claws | 1994-03-17 | | Buffy | 1989-05-13 | +----------+------------+ 8 rows in set (0.02 sec)
  • 35.
    35 Working with NULLs NULL means missing value or unknown value.  To test for NULL, you cannot use the arithmetic comparison operators, such as =, < or <>.  Rather, you must use the IS NULL and IS NOT NULL operators instead.
  • 36.
    36 Working with NULLs For example, to find all your dead pets (what a morbid example!) mysql> select name from pet where death >IS NOT NULL; +--------+ | name | +--------+ | Bowser | +--------+ 1 row in set (0.01 sec)
  • 37.
    37 Pattern Matching  MySQLprovides:  standard SQL pattern matching; and  regular expression pattern matching, similar to those used by Unix utilities such as vi, grep and sed.  SQL Pattern matching:  To perform pattern matching, use the LIKE or NOT LIKE comparison operators  By default, patterns are case insensitive.  Special Characters:  _ Used to match any single character.  % Used to match an arbitrary number of characters.
  • 38.
    38 Pattern Matching Example To find names beginning with ‘b’: mysql> SELECT * FROM pet WHERE name LIKE "b%"; +--------+--------+---------+------+------------+------------+ | name | owner | species | sex | birth | death | +--------+--------+---------+------+------------+------------+ | Buffy | Harold | dog | f | 1989-05-13 | NULL | | Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 | +--------+--------+---------+------+------------+------------+
  • 39.
    39 Pattern Matching Example To find names ending with `fy': mysql> SELECT * FROM pet WHERE name LIKE "%fy"; +--------+--------+---------+------+------------+-------+ | name | owner | species | sex | birth | death | +--------+--------+---------+------+------------+-------+ | Fluffy | Harold | cat | f | 1993-02-04 | NULL | | Buffy | Harold | dog | f | 1989-05-13 | NULL | +--------+--------+---------+------+------------+-------+
  • 40.
    40 Pattern Matching Example To find names containing a ‘w’: mysql> SELECT * FROM pet WHERE name LIKE "%w%"; +----------+-------+---------+------+------------+------------+ | name | owner | species | sex | birth | death | +----------+-------+---------+------+------------+------------+ | Claws | Gwen | cat | m | 1994-03-17 | NULL | | Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 | | Whistler | Gwen | bird | NULL | 1997-12-09 | NULL | +----------+-------+---------+------+------------+------------+
  • 41.
    41 Pattern Matching Example To find names containing exactly five characters, use the _ pattern character: mysql> SELECT * FROM pet WHERE name LIKE "_____"; +-------+--------+---------+------+------------+-------+ | name | owner | species | sex | birth | death | +-------+--------+---------+------+------------+-------+ | Claws | Gwen | cat | m | 1994-03-17 | NULL | | Buffy | Harold | dog | f | 1989-05-13 | NULL | +-------+--------+---------+------+------------+-------+
  • 42.
    42 Regular Expression Matching The other type of pattern matching provided by MySQL uses extended regular expressions.  When you test for a match for this type of pattern, use the REGEXP and NOT REGEXP operators (or RLIKE and NOT RLIKE, which are synonyms).
  • 43.
    43 Regular Expressions  Somecharacteristics of extended regular expressions are:  . matches any single character.  A character class [...] matches any character within the brackets. For example, [abc] matches a, b, or c. To name a range of characters, use a dash. [a-z] matches any lowercase letter, whereas [0-9] matches any digit.  * matches zero or more instances of the thing preceding it. For example, x* matches any number of x characters, [0-9]* matches any number of digits, and .* matches any number of anything.  To anchor a pattern so that it must match the beginning or end of the value being tested, use ^ at the beginning or $ at the end of the pattern.
  • 44.
    44 Reg Ex Example To find names beginning with b, use ^ to match the beginning of the name: mysql> SELECT * FROM pet WHERE name REGEXP "^b"; +--------+--------+---------+------+------------+------------+ | name | owner | species | sex | birth | death | +--------+--------+---------+------+------------+------------+ | Buffy | Harold | dog | f | 1989-05-13 | NULL | | Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 | +--------+--------+---------+------+------------+------------+
  • 45.
    45 Reg Ex Example To find names ending with `fy', use `$' to match the end of the name: mysql> SELECT * FROM pet WHERE name REGEXP "fy$"; +--------+--------+---------+------+------------+-------+ | name | owner | species | sex | birth | death | +--------+--------+---------+------+------------+-------+ | Fluffy | Harold | cat | f | 1993-02-04 | NULL | | Buffy | Harold | dog | f | 1989-05-13 | NULL | +--------+--------+---------+------+------------+-------+
  • 46.
    46 Counting Rows  Databasesare often used to answer the question, "How often does a certain type of data occur in a table?"  For example, you might want to know how many pets you have, or how many pets each owner has.  Counting the total number of animals you have is the same question as “How many rows are in the pet table?” because there is one record per pet.  The COUNT() function counts the number of non- NULL results.
  • 47.
    47 Counting Rows Example A query to determine total number of pets: mysql> SELECT COUNT(*) FROM pet; +----------+ | COUNT(*) | +----------+ | 9 | +----------+
  • 48.
    48 Batch Mode  Inthe previous sections, you used mysql interactively to enter queries and view the results.  You can also run mysql in batch mode. To do this, put the commands you want to run in a file, then tell mysql to read its input from the file:  shell> mysql < batch-file
  • 49.
    49 Is that allthere is to MySQL?  Of course not!  Understanding databases and MySQL could take us several weeks (perhaps months!)  For now, focus on:  using the MySQL shell  creating tables  creating basic SQL queries
  • 50.
    50 Summary  SQL providesa structured language for querying/updating multiple databases.  The more you know SQL, the better.  The most important part of SQL is learning to retrieve data.  selecting rows, columns, boolean operators, pattern matching, etc.  Keep playing around in the MySQL Shell.