MySQL Overview

1
MySQL Overview

• Fast, free, stable database
• Syntax is similar to Oracle
• Many of the same features as Oracle
• Production version still missing subqueries,
  stored procedures, and triggers
• Frequently used in conjunction with Linux,
  Apache, and PHP

2
Login

• Login the MySQL server installed on your Hermes.
• Syntax mysql -p
• Enter password passowrd
• Welcome to the MySQL monitor. Commands end with
  or \g. Your MySQL connection id is 23 to server
  version 3.23.41.
• Type 'help' or '\h' for help. Type '\c' to clear
  the buffer.
• mysqlgt This is the mysql prompt and you enter
  query here!

3
How MySQL stores data (by default)

• A MySQL server can store several databases
• Databases are stored as directories
• Tables are stored as files inside each database
  (directory)
• For each table, it has three files
• tablename.frm file containing information about
  the table structure effectively, an internal
  representation of the CREATE TABLE statement.
• tablename.MYD file containing the row data
• tablename.MYI containing any indexes belonging
  with this table, as well as some statistics about
  the table.

4
Look Around in MySQL
What are the current databases at the server?
(what directories are out there?) mysqlgt show
databases -------------- Database
-------------- mysql
mysql is a database (stores users password )
used by system. test
-------------- Create a database (make a
directory) whose name is MyDB mysqlgt create
database MyDB Select database to use (change
your working directory) mysqlgt use MyDB Database
changed What tables are currently stored in bcb
database? (what files are in the
directory) mysqlgt show tables Empty set (0.00
sec)
5
Uppercase and Lowercase

• Traditionally SQL commands are written with
  uppercase.
• MYSQL commands are really case-insensitive
• But variable names in the commands are
  case-sensitive. I will therefore write them in
  lowercase.

6
Creating Databases

• CREATE DATABASE a mySQL command to create a new
  database.
• Example
• CREATE DATABASE newbase
• creates a database newbase

7
USE

• USE database tells mySQL to start working with
  the database database.
• If you have not issued a USE command, you can
  still address a table table by using
  database.table, where database is the name of
  your database and table is the name of your
  table. You are using the dot to link the two
  together.

8
Creating Tables

• before you do it, set up some examples on a sheet
  of paper.
• Here is an example
• CREATE TABLE customers (customer_id INT NOT
• NULL AUTO_INCREMENT PRIMARY KEY,
• name VARCHAR(50) NOT NULL,
• address VARCHAR(100) NOT NULL,
• email CHAR(40),
• state CHAR(2) NOT NULL)

9
Common MySQL Column Types
10
Column Data Types (Cont.)

• TINYINT can hold a number between -128 and 127 or
  between 0 to 255. BIT or BOOL are synonyms for
  the TINYINT.
• SMALLINT can hold a number between -32768 and
  32767 or 0 and 65535
• INT can hold a number between -231 and 231-1
  or between 0 and 232-1. INTEGER is a synonym
  for INT.
• BIGINT can hold a number between -263 and
  261-1 or between 0 and 264-1.

11
column data types float

• FLOAT is a floating number on 4 bytes
• DOUBLE is a floating number on 8 bytes
• DECIMAL(x,y) where x is the number of digits
  before the decimal point and y is the number of
  digits after the decimal point.

12
column data types dates

• DATE is a day from 1000-01-01 to 9999-12-31.
• TIME is a time from -8385959 to 8385959
• DATETIME is a date and time, usually displayed as
  YYYY-MM-DD HHMMSS
• TIMESTAMP is the number of seconds since
  1970-01-01 at 0 hours. This number may run out in
  2037.

13
Field Options

• PRIMARY KEY says that this column is a the
  primary key. There can be only one such column.
  Values in the column must be unique.
• AUTO_INCREMENT can be used on columns that
  contain integer values.
• NOT NULL requires the field not to be empty.

14
Creating Table Example 1

• CREATE TABLE books ( idNumber int primary key
• auto_increment, title varchar(30), author
  varchar(30))

15
Creating Table Example 2

• Lets create a table and name it as student!
• mysqlgt CREATE TABLE student
• (
• student_ID INT NOT NULL,
• name VARCHAR(20) NOT NULL,
• major VARCHAR(50),
• grade VARCHAR(5),
• Primary key (student_ID )
• )
• Query OK, 0 rows affected (0.00 sec)

16
Display Table Structure

• Now you should see the student table in the
  database.
• mysqlgt show tables
• --------------------
• Tables_in_bbsi
• --------------------
• student
• --------------------
• 1 row in set (0.00 sec)
• If you forgot what the student table structure
  is,
• mysqlgt describe student
• ----------------------------------------------
  ---------------------
• Field Type Null
  Key Default Extra
• ----------------------------------------------
  ---------------------
• student_ID int(10) unsigned
  0
• name varchar(20)
  
• major varchar(50) YES
  NULL
• grade varchar(5) YES
  NULL
• ----------------------------------------------
  ---------------------

17
Addressing Database Tables Columns

• Let there by a database database with a table
  table and some column column. Then it is
  addressed as database.table.column.
• Parts of this notation can be left out if it is
  clear what is meant, for example if you have
  issued USE database before, you can leave out the
  database part.

18
INSERT

• INSERT inserts new rows into a table. In its
  simples form
• INSERT INTO table VALUES (value1, value2, ..)
• Example
• INSERT INTO products VALUES (' ','Neufang
  Pils',1.23)
• Note that in the example, I insert the null
  string in the first column because it is an
  auto_increment.
• Another example
• INSERT INTO books (title,author) VALUES( Let
  Freedom Ring, Sean Hannity)

19
Changing values in a Row

• The general syntax is UPDATE LOW_PRIORITY
  IGNORE table SET column1expression1,
  column2expression2... WHERE condition ORDER
  BY order_criteria LIMIT number
• An example is
• UPDATE students SET email 'phpguru_at_gmail.com'
• WHERE name'Janice Insinga'
• IGNORE instructs to ignore errors.
• LOW_PRIORITY instructs to delay if the server is
  busy.

20
Deleting Rows

• The general syntax is
• DELETE LOW_PRIORITY QUICK IGNORE FROM
  table WHERE condition ORDER BY order_criteria
  LIMIT number
• Remove all rows in a table
• DELETE FROM customers
• Good example
• DELETE FROM customers WHERE
• customer.name'Thomas Krichel'

21
Replace Records

• REPLACE works like INSERT but also DELETE old
  record if exists.
• Insert your record WITH a grade
• mysqlgt replace student SET student_IDYourID,
  nameYourName', majorYourMajor', gradeA
• mysqlgt select from student where
  student_IDYourID
• What is your record now?
• Insert your record WITHOUT a grade
• mysqlgt replace student SET student_IDYourID,
  nameYourName', majorYourMajor'
• mysqlgt select from student where
  student_IDYourID
• What is your record now?

22
DROP Command

• DROP TABLE
• DROP SCHEMA DATABASE
• Optional IF EXISTS
• Example
• Drop table customer
• Drop table customer cascade // if there are
  foreign keys linked to another table

23
ALTER Command

• ALTER TABLE table_name alter_spec,
• alter_spec
• ADD COLUMN, CONSTRAINT
• Use the definition as in CREATE TABLE
• ALTER COLUMN name SET DEFAULT value
• RENAME new_table_name

24
ALTER TABLE (Adding and Dropping Columns)

• ALTER TABLE table_name ADD column_name datatype
• Adds a column to the table
• Ex Alter table employee add address
  varchar(40)
• ALTER TABLE table_name DROP COLUMN column_name
• Removes a column (and all its data) from the
  table
• Ex Alter table employee drop column address
• ALTER TABLE table_name MODIFY (column_name
  newType/length)
• Ex Alter table employee modify age varchar(15)

25
Removing rows and Dropping Tables

• Delete Syntax
• Delete from TableName,
• eg. Delete from employee
• Just deleting all the rows from a table leaves a
  blank table with column names and types
• Drop Syntax
• Drop TABLE TableName,
• eg. Drop table employee
• Remove the table completely from the database

26
Adding and Deleting Constraints

• Add a constraint ALTER TABLE tablename ADD
  CONSTRAINT constraint_name constraint_definition
• Remove a constraint ALTER TABLE tablename DROP
  CONSTRAINT constraint_name

27
Adding Primary keys and Foreign keys

• Add a primary key to NIN field in table employee
• ALTER TABLE employee ADD CONSTRAINT pk_NIN
  primary key(NIN)
• Add a foreign key to deptno field in table
  employee that refers to field deptno in
  department table
• ALTER TABLE employee ADD CONSTRAINT fk_deptno
  foreign key(deptno) references Department
  (deptno)

28
Buck load/Insert
Download student.txt and project.txt and buck
load into MySQL The following shows how to load
batch data instead of inserting records one by
one! mysqlgt LOAD DATA LOCAL INFILE "student.txt"
INTO TABLE student Query OK, 21 rows affected
(0.01 sec) Records 21 Deleted 0 Skipped 0
Warnings 0 mysqlgt select from student What
Project is Jerry on? NULL means data NOT
available mysqlgt load data local infile
"project.txt" into table project Query OK, 7
rows affected (0.00 sec) Records 7 Deleted 0
Skipped 0 Warnings 0 mysqlgt select from
project
29
Data Retrieval
Logical operator OR mysqlgt select name from
student where major 'BCB' OR major
'CS' Count query results mysqlgt select
count(name) from student where major 'BCB' OR
major 'CS' Sorting query results (ORDER
BY) mysqlgt select name from student where major
'BCB' OR major 'CS ORDER BY
name mysqlgt select name from student where major
'BCB' OR major 'CS ORDER BY
name DESC Pattern matching (LIKE) mysqlgt select
name from student where name LIKE "J" Remove
duplicates (DISTINCT) mysqlgt select major from
student mysqlgt select DISTINCT major from
student
30
MySQL Operators
MySQL Comparison Operators Operator
Meaning -----------------------------------
-------------------------- lt
less than lt
Less than or equal to
Equal to ! or ltgt Not
equal to gt Greater
than or equal to gt
Greater than MySQL Logical Operators Operator
Meaning -----------------------
--------------------------------------- AND
Logical AND OR
Logical OR NOT
Logical negation
31
Sorting

• select name from student ORDER BY name
• You may use also after ORDER BY clause DESC

32
Group By
Cluster your results based on different
groups. How many students from each major are
taking the class? mysqlgt select major, count()
from student GROUP BY major -------------------
major count() -------------------
BBMB 3 BCB 3
Chem 1 CS 5
IG 2 Math 2
MCDB 3 Stat 2
--------------------- 8 rows in set (0.00
sec)
33
NULL value
1. NULL means no value! 2. Can not use the
usual comparison operators (gt, , ! ). 3. Use
IS or IS NOT operators to compare with. Who has
NOT chosen any project yet? Wrong way to do
it mysqlgt select name from student where
project_ID NULL Empty set (0.00 sec) Correct
way to do it mysqlgt select name from student
where project_ID IS NULL -------
name ------- Jerry ------- 1 row in set
(0.00 sec)
34
Working with Dates

• to search for all DVDs rented in the last week,
  you would use the expression
• SELECT FROM LOAN WHERE DATEDIFF(CURDATE(),DATE
  HIRED) lt 7
• to calculate a persons age in years from their
  date of birth, you would use the expression
• SELECT (YEAR(CURDATE()) YEAR(DATE OF BIRTH)
  (RIGHT(CURDATE(),5)ltRIGHT(DATE OF BIRTH,5)) AS
  AGE

35
Insert Select
mysqlgt create table BCBstudent -gt (
student_ID int unsigned not null, -gt name
varchar(20) not null, -gt major
varchar(10), -gt project_ID int, -gt
primary key (student_ID)) Query OK, 0 rows
affected (0.00 sec) student where major'BCB''
at line 1 mysqlgt insert into BCBstudent select
from student where major'BCB' Query OK, 3 rows
affected (0.00 sec) Records 3 Duplicates 0
Warnings 0 mysqlgt select from
BCBstudent ----------------------------------
------ student_ID name major
project_ID ---------------------------------
------- 101 Shannon BCB
1 108 Troy BCB 3
113 Stephen BCB 4
---------------------------------------- 3
rows in set (0.01 sec)
36
Table Join
Retrieving Information from Multiple
Tables Which BCB students chose level-4 project?
mysqlgt select s.name from student s, project p
where s.project_ID p.project_ID
and s.major'BCB' and
p.level4 ------------ name
------------ Stephen ------------ 1 row
in set (0.00 sec)
37
Equi-join vs. Left join of multiple tables
Equi-join mysqlgt select s.name, p.category from
student s, project p where
s.project_ID p.project_ID and
s.major'MCDB' --------------------------------
---------- name category
----------------------------------------
-- Robert Phylogenetic Prediction
Tiffiny Sequence alignment
------------------------------------------ 2
rows in set (0.00 sec) Left join mysqlgt select
s.name, p.category from student s left join
project p on s.project_ID
p.project_ID where
s.major'MCDB' --------------------------------
----------- name category
--------------------------------------
----- Robert Phylogenetic Prediction
Jerry NULL
Tiffiny Sequence alignment
------------------------------------------- 3
rows in set (0.00 sec) A left join ALSO shows
rows in the LEFT table that do NOT have a match
in the right table!
38
PHP mySQL functions

• We are using here the new version of PHP mySQL
  function, starting with mysql_
• The interface is object-oriented, but can also be
  accessed in a non-object-oriented way. This is
  known as the procedural style, in the
  documentation.
• You should use the online documentation at
  http//php.net/mysql

39
mysql_connect()

• This is used to establish a connection to the
  mySQL server. It is typically of the form
  mysql_connect('host', 'user', 'password')
• Example
• link mysql_connect('localhost','boozer','heineke
  n')
• You can use localhost as the host name for wotan
  talking to itself, but you could also connect to
  other Internet hosts, if you have permission.
• The function returns a variable of type
  resource. If there is a mistake, it returns
  false.

40
mysql_error()

• This function return the error from the last
  mySQL command. It returns false if there was no
  error.
• errormysql_error()
• if(error)
• print "mySQL error errorltbr/gt"
• The value returned from that function is a simple
  string.
• It is a good idea to check out error messages.

41
mysql_select_db()

• This command has the syntax mysql_select_db('datab
  ase') where database is the name of a database.
• It returns a Boolean.
• This tells mySQL that you now want to use the
  database database.
• mysql_select_db('beer_shop')
• It has the same effect as issuing
• USE beer_shop
• within mySQL.

42
mysql_query()

• mysql_query(query) send the query query to mySQL.
  
• link mysql_connect("localhost", "shop_owner",
  "bruch") // you may then add some connection
  checks
• query"SELECT FROM beer_shop.customers"
• resultmysql_query(query)
• Note that the query itself does not require a
  terminating semicolon.
• The result is in result.

43
result of mysql_query()

• For SELECT, SHOW, DESCRIBE or EXPLAIN mySQL
  queries, mysql_query() returns a resource that
  can be further examined with mysql_fetch_array().
  
• For UPDATE, DELETE, DROP and others,
  mysql_query() returns a Boolean value.

44
examining resulting rows

• mysql_fetch_array(result) returns an array that
  is the result row for the resource resource
  representing the most recent, or NULL if it the
  last result is reached. Its results in an array
  that contains the columns requested both by
  number and by column name
• while(columnsmysql_fetch_array(result))
• print 'name '.columns'name'
• print 'first column columns0

45
examining a specific result

• mysql_data_seek(result, number) sets the array
  that is returned by mysql_fetch_array to a number
  number.
• while(rowmysql_fetch_array(result))
• print 'first column '.row0
• mysql_data_seek(result,0)
• // otherwise the second loop would not work
• while(rowmysql_fetch_array(result))
• print 'first column '.row0

46
mysql_real_escape_string()

• mysql_real_escape_string(string) returns a string
  escaped for the using in mySQL.
• name"John O'Guiness"
• s_namemysql_real_escape_string(name)
• print s_name // prints John O\'Guiness
• Note that this function makes a call to mySQL,
  therefore a connection must be established before
  the function can be used.
• This function guards against SQL injections.

47
mysql_close()

• This command connection. When it is invoked
  without an argument, it closes the current
  connection.
• This is the happiest command there is, because it
  means that we have finished.
• Unfortunately it is not used very often because
  the mySQL connection is closed automatically when
  the script finishes running.

48
extra sha1()

• This is a function that calculates a combination
  of 40 characters from a string.
• The result of sha1() can not be translated back
  into the original string.
• This makes it a good way to store password.
• s_passwordsha1(password)