How to Use MySQL

1
How to Use MySQL

• CS430
• March 18, 2003

2
SQL Structured Query Languagethe most common
standardized language used to access
databases. SQL has several parts DDL Data
Definition Language Defining, Deleting,
Modifying relation schemas DML Data
Manipulation Language Inserting, Deleting,
Modifying tuples in database Embedded SQL
defines how SQL statements can be used with
general-purposed programming
3

• MySQL, the most popular Open Source SQL database,
  is developed, distributed and supported by MySQL
  AB.
• MySQL is a relational database management system.
  
• MySQL software is Open Source.
• Written in C and C. Tested with a broad range
  of different compilers.
• Works on many different platforms.
• APIs for C, C, Eiffel, Java, Perl, PHP, Python,
  Ruby, and Tcl.
• You can find MySQl manual and documentation at
• http//www.mysql.com/documentation/

4
MySQL To see a list of options provided by
mysql, invoke it with the --help option shellgt
mysql --help Using SQL On any solaris/linux
you have to use this to log on to MySQL shellgt
/usr/local/mysql/bin/mysql -h faure -D loginname
-p shellgt mysql -h host -u user -p Enter
password The represents your
password enter it when mysql displays the Enter
password prompt.
5

• Basic Query
• select A1, A2,,An
• from r1, r2, ,rm
• where P
• A1, A2,,An represent attributes
• r1, r2, rm represent relations
• P represents predicate (guard condition)
• Keywords may be entered in any letter case
• mysqlgt SELECT VERSION(), CURRENT_DATE
• mysqlgt select version(), current_date
• mysqlgt SeLeCt vErSiOn(), current_DATE

6
Prompt Meaning mysqlgt Ready for new command.
-gt Waiting for next line of
multiple-line command. gt Waiting for
next line, collecting a string that begins
with a single quote ( ). gt
Waiting for next line, collecting a string that
begins with a double quote ( ). mysqlgt
SELECT -gt FROM my_table -gt
WHERE name Smith AND age lt 30 mysqlgt
SELECT FROM my_table WHERE name "Smith AND
age lt 30 "gt "\c mysqlgt \c to cancel the
execution of a command
7

• Basic Database Operation
• Create a database
• Create a table
• Load data into the table
• Retrieve data from the table in various ways
• Use multiple tables
• Suppose you have several pets in your home (your
  menagerie) and you'd like to keep track of
  various types of information about them. You can
  do so by creating tables to hold your data and
  loading them with the desired information. Then
  you can answer different sorts of questions about
  your animals by retrieving data from the tables.

8
Creating and Using a Database mysqlgt SHOW
DATABASES SHOW statement can be used to find
out the databases currently existing on the
server mysqlgt USE testdb testdb is a database
name. USE command does not need a semi colon and
must be given in a single line. Database needs to
be invoked in order to use it. mysqlgt CREATE
DATABASE example Database names are
case-sensitive unlike keywords Same applies for
table names So example ! Example ! EXAMPLE or
some other variant
9
Creating a Table mysqlgt SHOW TABLES Displays
the current list of tables mysqlgt CREATE TABLE
pet (name VARCHAR(20), owner VARCHAR(20),
-gt species VARCHAR(20), sex CHAR(1), birth DATE,
death DATE) mysqlgt SHOW TABLES Will display
the table with the table name pet Verification
of the table can be done with DESCRIBE
command mysqlgt 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 ---------------- ----------
------ ---------------- ---------------- ------
---------- ----------------
10
Loading Data into a Table LOAD DATA uses a text
file with single record in a line that match the
attributes in the table. Useful for inserting
when multiple records are involved. Example
pet.txt is a text file with a single
record Name owner species sex birth death
Whistler Gwen bird \N 1997-12-09 \N
mysqlgt LOAD DATA LOCAL INFILE "pet.txt" INTO
TABLE pet INSERT command can be used when
records needs to be inserted one at a time. NULL
can be directly inserted in the field
column Example mysqlgt INSERT INTO pet
-gt VALUES ('Puffball','Diane','hamster','f','1999-
03-30',NULL)
11

• Retrieving Information from a Table
• The SELECT statement is used to pull information
  from a table. The general form of the statement
  is
• SELECT what_to_select
• FROM which_table
• WHERE conditions_to_satisfy
• The simplest form of SELECT retrieves everything
  from a table
• mysqlgt SELECT FROM pet
• You can select only particular rows from your
  table.
• mysqlgt SELECT FROM pet WHERE name "Bowser"
• You can specify conditions on any column, not
  just name. For example, if you want to know which
  animals were born after 1998, test the birth
  column
• mysqlgt SELECT FROM pet WHERE birth gt
  "1998-1-1"
• You can combine conditions, for example, to
  locate female dogs
• mysqlgt SELECT FROM pet WHERE species "dog"
  AND sex "f"

12

• Selecting Particular Columns
• If you don't want to see entire rows from your
  table, just name the columns in which you're
  interested, separated by commas.
• For example, if you want to know when your
  animals were born, select
• the name and birth columns
• mysqlgt SELECT name, birth FROM pet
• To find out who owns pets, use this query
• mysqlgt SELECT owner FROM pet
• mysqlgt SELECT DISTINCT owner FROM pet
• You can use a WHERE clause to combine row
  selection with column selection. For example, to
  get birth dates for dogs and cats only, use this
  query
• mysqlgt SELECT name, species, birth FROM pet
• -gt WHERE species "dog" OR species "cat"

13

• Sorting Rows
• To sort a result, use an ORDER BY clause.
• Here are animal birthdays, sorted by date
• mysqlgt SELECT name, birth FROM pet ORDER BY
  birth
• To sort in reverse order, add the DESC
  (descending) keyword to the name of the column
  you are sorting by
• mysqlgt SELECT name, birth FROM pet ORDER BY birth
  DESC
• You can sort on multiple columns. For example, to
  sort by type of animal, then by birth date within
  animal type with youngest animals first, use the
  following query
• mysqlgt SELECT name, species, birth FROM pet ORDER
  BY species, birth DESC

14

• Pattern Matching
• MySQL provides standard SQL pattern matching as
  well as a form of pattern matching based on
  extended regular expressions similar to those
  used by Unix utilities such as grep.
• SQL pattern matching allows you to use _' to
  match any single character and ' to match an
  arbitrary number of characters (including zero
  characters). In MySQL, SQL patterns are
  case-insensitive by default. Some examples are
  shown here. Note that you do not use or ltgt when
  you use SQL patterns use the LIKE or NOT LIKE
  comparison operators instead.
• To find names beginning with b'
• mysqlgt SELECT FROM pet WHERE name LIKE "b"
• To find names containing exactly five characters,
  use the _' pattern character
• mysqlgt SELECT FROM pet WHERE name LIKE _____

15

• Counting Rows
• For example, you might want to know 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?
• The COUNT() function counts the number of
  non-NULL results, so the query to count your
  animals looks like this
• mysqlgt SELECT COUNT() FROM pet
• You can use COUNT() if you want to find out how
  many pets each owner has
• mysqlgt SELECT owner, COUNT() FROM pet GROUP BY
  owner
• ------------------------
• owner COUNT()
• ------------------------
• Benny 2
• Diane 2
• Gwen 3
• Harold 2
• ------------------------

16
Examples of some common queries CREATE TABLE
shop ( article INT(4) UNSIGNED ZEROFILL
DEFAULT 0000' NOT NULL, dealer CHAR(20)
DEFAULT NOT NULL, price DOUBLE(16,2)
DEFAULT '0.00 NOT NULL, PRIMARY
KEY(article, dealer)) INSERT INTO shop VALUES
(1,'A',3.45),(1,'B',3.99),(2,'A',10.99),(3,'B',1.4
5),(3,'C',1.69), (3,'D',1.25),(4,'D',19.95)
mysqlgt SELECT FROM shop ------------------
------------- article dealer price
-------------------------------
0001 A 3.45 0001
B 3.99 0002
A 10.99 0003 B
1.45 0003 C 1.69
0003 D 1.25
0004 D 19.95 ----------------
--------------- The maximum value for a
column The row holding the maximum of a certain
column Maximum of column per group The rows
holding the group-wise maximum of a certain field
17

• What's the highest price?
• SELECT MAX(price) AS price FROM shop
• ---------
• price
• ---------
• 19.95
• ---------
• Find number, dealer, and price of the most
  expensive article.
• In ANSI SQL (and MySQL Version 4.1) this is
  easily done with a subquery
• SELECT article, dealer, price FROM shop
• WHERE price (SELECT MAX(price) FROM shop)
• In MySQL versions prior to 4.1, you have to do it
  in two steps
• Get the maximum price value from the table with a
  SELECT statement.
• Using this value compile the actual query
• SELECT article, dealer, price FROM shop WHERE
  price19.95

18

• Maximum of Column per Group
• What's the highest price per article?
• SELECT article, MAX(price) AS price
• FROM shop
• GROUP BY article
• ------------------
• article price
• ------------------
• 0001 3.99
• 0002 10.99
• 0003 1.69
• 0004 19.95
• ------------------

19

• The Rows Holding the Group-wise Maximum of a
  Certain Field
• For each article, find the dealer(s) with the
  most expensive price.
• In ANSI SQL (MySQL Version 4.1 or greater), do it
  with a subquery
• SELECT article, dealer, price
• FROM shop s1
• WHERE price(SELECT MAX(s2.price)
• FROM shop s2
• WHERE s1.article
  s2.article)

20

• But, In MySQL versions prior to 4.1, it has to be
  done in several steps, with a temporary table (It
  doesnt support nested-query \subquery).
• CREATE TEMPORARY TABLE tmp
• ( article INT(4) UNSIGNED ZEROFILL DEFAULT
  '0000' NOT NULL,
• price DOUBLE(16,2) DEFAULT '0.00' NOT NULL)
• LOCK TABLES shop read
• INSERT INTO tmp
• SELECT article, MAX(price) FROM shop GROUP BY
  article
• SELECT shop.article, dealer, shop.price
• FROM shop, tmp
• WHERE shop.articletmp.article AND
  shop.pricetmp.price
• UNLOCK TABLES DROP TABLE tmp