Module 3 – MySQL Database Database Management System [PDF]

Example. % mysql -u username -p. Enter password: password. Welcome to the MySQL monitor. Commands end with ; or \g. Your

8 downloads 11 Views 406KB Size

Recommend Stories


PdF Database System Concepts
Love only grows by sharing. You can only have more for yourself by giving it away to others. Brian

Selecting a Database Management System
Never wish them pain. That's not who you are. If they caused you pain, they must have pain inside. Wish

BilVideo Video Database Management System
If you feel beautiful, then you are. Even if you don't, you still are. Terri Guillemets

Database Management
Your task is not to seek for love, but merely to seek and find all the barriers within yourself that

Database Management
It always seems impossible until it is done. Nelson Mandela

PDF Online Modern Database Management
In every community, there is work to be done. In every nation, there are wounds to heal. In every heart,

[PDF] Download Modern Database Management
Love only grows by sharing. You can only have more for yourself by giving it away to others. Brian

PDF Online Modern Database Management
The beauty of a living thing is not the atoms that go into it, but the way those atoms are put together.

Solution for Advanced Database Management system
Those who bring sunshine to the lives of others cannot keep it from themselves. J. M. Barrie

Chapter 3 Database Collection
And you? When will you begin that long journey into yourself? Rumi

Idea Transcript


Module 3 – MySQL Database • Module 3 Contains 2 components – Individual Assignment – Group Assignment

• BOTH are due on Mon, Feb 19th • Read the WIKI before attempting the lab

Networking Platform 1 Extensible - CSE 330 – Creative Programming and Rapid Prototyping

1

Database Management System • A database is simply a collection of data. In a relational database, data is organized into tables. Student_ID

Name

Major

Grade

101

Shannon

CSE

A

102

Mike

CHEM

A

103

Wang

BIO

A







• Database Management System (DBMS) is software to maintain and utilize the collections of data (Oracle, DB2, MySQL) Networking Platform 2 Extensible - CSE 330 – Creative Programming and Rapid Prototyping

2

Cells, Rows, Tables and Databases • Cell -- a single (scalar) value

12134

Networking Platform 3 Extensible - CSE 330 – Creative Programming and Rapid Prototyping

3

Cells, Rows, Tables and Databases • Row -- a group of scalar values representing a single instance of an object or event

12135

1310391314 Letter: July 23,1842

Networking Platform 4 Extensible - CSE 330 – Creative Programming and Rapid Prototyping

4

Cells, Rows, Tables and Databases • Table -- a series of rows describing separate objects or events

ID 1 21 34 1 21 35 1 21 36

METSID

LABEL

1 09031 331 3 Letter: Nov ember 1 8, 1 838 1 31 0391 31 4 Letter: July 23,1 842 1 31 302041 4 Waterloo at Sunset

Networking Platform 5 Extensible - CSE 330 – Creative Programming and Rapid Prototyping

5

Cells, Rows, Tables and Databases

• Database -- a collection of related tables describing various facets of a group of objects or events

OBJECTS ID METSID LABEL

CLINKS METSID COLID

Networking Platform 6 Extensible - CSE 330 – Creative Programming and Rapid Prototyping

COLS ID NAME URL ABSTRACT 6

Relationships in Databases • Databases are great for storing different types of data and managing relationships between them • When designing a DB it is important to understand the what types of relationships you need to create – These relationships are defined through referential integrity (keys and/or constraints)

• There are a few common types of DB relationships we will look at – One-to-One – One-to-Many – Many-to-Many

Networking Platform 7 Extensible - CSE 330 – Creative Programming and Rapid Prototyping

7

Relations -- One to One Table1 Record Record Record Record Record Record Record

Table2 Record Record Record Record Record Record Record

• Example: Table containing Social Security Number has a one-to-one relationship to table of Wash U Student IDs Networking Platform 8 Extensible - CSE 330 – Creative Programming and Rapid Prototyping

8

One to Many Table1 Record Record Record Record Record Record Record

Table2 Record Record Record Record Record Record Record

• Example: Table of Academic Advisors has a one to many relationship with a table containing students at WashU Networking Platform 9 Extensible - CSE 330 – Creative Programming and Rapid Prototyping

9

Many to Many Table1 Record Record Record Record Record Record Record

Table2 Record Record Record Record Record Record Record

• Example: Table of courses taught in the Fall semester has a many to many relationship with a table containing students at WashU Extensible Networking Platform 10 - CSE 330 – Creative Programming and Rapid Prototyping

10

Which DB to use? Why MySQL? • Free SQL (Structured Query Language) database server – licensed with the GNU General public license http://www.gnu.org/

• MySQL is a relational database management system (RDBMS) • MySQL is Open Source Software • Officially pronounced “my Ess Que Ell” Networking Platform 11Extensible - CSE 330 – Creative Programming and Rapid Prototyping

11

Basic MySQL Operations • • • • • • • • • • • •

Create table Insert records Load data Retrieve records Update records Delete records Modify table Join table Drop table Optimize table Count, Like, Order by, Group by More advanced ones (sub-queries, stored procedures, triggers, views …)

Extensible Networking Platform 12 - CSE 330 – Creative Programming and Rapid Prototyping

12

How MySQL stores data (by default) • A MySQL server can store several databases • Databases are stored as directories – Default is at /usr/local/mysql/var/

• Tables are stored as files inside each database (directory)

Extensible Networking Platform 13 - CSE 330 – Creative Programming and Rapid Prototyping

13

Login • mysql –h hostname –u username –p [password] • Example % mysql -u username -p Enter password: password 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. mysql>

Extensible Networking Platform 14 - CSE 330 – Creative Programming and Rapid Prototyping

14

Create User and Database •mysql>create user ‘test1’@’localhost’ identified by ‘mysecretpass’; – Create a new database user test1

•mysql>grant all on *.* to test1@’localhost’ with grant option; – Gives administrative privileges to user test1

• It is common to restrict users to a particular database with limited access, which we are NOT doing here

Extensible Networking Platform 15 - CSE 330 – Creative Programming and Rapid Prototyping

15

Create Database What are the current databases at the server? mysql> 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 mysql> create database MyDB; Select database to use mysql> use MyDB; Database changed What tables are currently stored in the MyDB database? mysql> show tables; Empty set (0.00 sec)

Extensible Networking Platform 16 - CSE 330 – Creative Programming and Rapid Prototyping

16

Create Table • CREATE TABLE Table_Name (column_specifications) • Example mysql> CREATE TABLE student -> ( -> student_ID INT UNSIGNED NOT NULL, -> name VARCHAR(20) NOT NULL, -> major VARCHAR(50), -> email VARCHAR(50) -> ); Query OK, 0 rows affected (0.00 sec)

Student_ID

Name

Major

Email

Extensible Networking Platform 17 - CSE 330 – Creative Programming and Rapid Prototyping

17

Domain Types in SQL Type

Description

CHAR(n)

Fixed length character string, with specified length n

VARCHAR(n)

Variable length character string, with specified maximum length n

INTEGER

Integer (a machine-dependent finite subset of the integers)

SMALLINT(n)

A small integer (a finite subset of INTEGER)

FLOAT(M,D)

Floating point number, with total number of digits M and number of digits following the decimal point D

DOUBLE(M,D)

Double-precision floating point number

• Similar to data types in classical programming languages

Extensible Networking Platform 18 - CSE 330 – Creative Programming and Rapid Prototyping

18

Display Table Structure mysql> show tables; +--------------------+ | Tables_in_MyDB | +--------------------+ | student | +--------------------+ 1 row in set (0.00 sec) mysql> describe student; +---------------+----------------------+------+------+----------+--------+ | Field | Type | Null | Key | Default | Extra | +---------------+----------------------+-------+-----+-----------+-------+ | student_ID | int(10) unsigned | | | 0 | | | name | varchar(20) | | | | | | major | varchar(50) |YES | | NULL | | | email | varchar(50) | YES | | NULL | | +---------------+----------------------+-------+------+----------+-------+ 4 rows in set (0.00 sec)

Extensible Networking Platform 19 - CSE 330 – Creative Programming and Rapid Prototyping

19

Demo

Extensible Networking Platform 20 - CSE 330 – Creative Programming and Rapid Prototyping

20

Primary and Foreign Keys • One mechanism to enforce “referential integrity” is through primary and foreign keys • Primary Keys are used in MySQL as unique identifiers for each row in a table – Consider a database of students

• The student ID could serve as a primary key

• A foreign key is a field in a table which is also the primary key of another table – Known as referential integrity – Consider a WashU Database with tables for students, courses and enrollment • What if the student tries to enroll in a course that does not exist?

Extensible Networking Platform 21 - CSE 330 – Creative Programming and Rapid Prototyping

21

Modify Table Structure • ALTER TABLE table_name Operations mysql> alter table student add primary key (student_ID); Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> describe student; +---------------+--------------------- +-------+------+----------+-------+ | Field | Type | Null | Key | Default | Extra | +---------------+----------------------+-------+------+----------+-------+ | student_ID | int(10) unsigned | | PRI | 0 | | | name | varchar(20) | | | | | | major | varchar(10) | YES | | NULL | | | email | varchar(50) | YES | | NULL | | +---------------+----------------------+-------+------+-----------+-------+ 4 rows in set (0.00 sec)

Extensible Networking Platform 22 - CSE 330 – Creative Programming and Rapid Prototyping

22

Insert Record • INSERT INTO table_name SET col_name1=value1, col_name2=value2, col_name3=value3, … • Example mysql> INSERT INTO student SET student_ID=101, name='Shannon', major=’CSE', email=‘[email protected]’; Query OK, 1 row affected (0.00 sec)

Student_ID

Name

Major

Email

101

Shannon

CSE

shannon@ yahoo.com

Extensible Networking Platform 23 - CSE 330 – Creative Programming and Rapid Prototyping

23

Retrieve Record • SELECT what_columns FROM table or tables WHERE condition • Example mysql> SELECT major, email FROM student WHERE name='Shannon'; +-------+------------------------+ | major| email | +-------+------------------------+ | CSE | [email protected]| +-------+------------------------+ 1 row in set (0.00 sec) mysql> SELECT * FROM student;

Extensible Networking Platform 24 - CSE 330 – Creative Programming and Rapid Prototyping

Student_ID

Name

Major

Email

101

Shannon

CSE

shannon@ yahoo.com

102

Mike

CHEM

mike@ gmail.com

103

Wang

BIO

wang@ wustl.edu







24

Update Record • UPDATE table_name SET which columns to change WHERE condition • Example mysql> UPDATE student SET email=‘[email protected]’ WHERE name='Shannon'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> SELECT * FROM student WHERE name=‘Shannon’; +------------+---------------+--------+-------------------------+ | name | student_ID | major | email | +------------+---------------+--------+-------------------------+ | Shannon | 101 | CSE | [email protected] | +------------+---------------+--------+-------------------------+ 1 row in set (0.00 sec)

Extensible Networking Platform 25 - CSE 330 – Creative Programming and Rapid Prototyping

25

Delete Record • DELETE FROM table_name WHERE condition • Example mysql> DELETE FROM student WHERE name='Shannon'; Query OK, 1 row affected (0.00 sec) Mysql> DELETE FROM student; Will delete ALL student records!

Extensible Networking Platform 26 - CSE 330 – Creative Programming and Rapid Prototyping

26

More complex queries • Queries are often interested in data from multiple tables • INNER JOIN (or just JOIN) • LEFT JOIN • RIGHT JOIN • The wiki has some great examples of using the JOIN operator Extensible Networking Platform 27 - CSE 330 – Creative Programming and Rapid Prototyping

27

phpMyAdmin

Extensible Networking Platform 28 - CSE 330 – Creative Programming and Rapid Prototyping

28

PHP and MYSQL • We will use PHP to communicate with our mySQL database • MySQL Improved Prepared Statements provide a clean way to issue queries • Refer to the wiki for additional syntax examples

Extensible Networking Platform 29 - CSE 330 – Creative Programming and Rapid Prototyping

29

MySQL and PHP Demo

Extensible Networking Platform 30 - CSE 330 – Creative Programming and Rapid Prototyping

30

Create database with user/password • Never store passwords in a database • Instead store password hash

– More importantly store a salted hash.

Extensible Networking Platform 31 - CSE 330 – Creative Programming and Rapid Prototyping

31

User accounts and passwords • The group portion of this module requires user accounts and passwords

– Never store passwords as plain text in a database – Instead use a salted password hash

• A password hash is a function that takes a password and maps it to a fixed size bit string

– Hashed passwords are also fixed length, so perfect for a char (not varchar) data type

Extensible Networking Platform 32 - CSE 330 – Creative Programming and Rapid Prototyping

32

Hashing • The MD5 Message Digest Algorithm is a widely used hash function for security applications • I could store my password as an MD5 hash in the mySQL… – But these passwords are trivially reversed

• So we add additional information to the string (called salt) to make it harder to accomplish • Hash(Password + Random characters)

• So can PHP help me with this?

– Fortunately PHP has a method called password_hash which takes of most of work

• Older versions of PHP used a less insecure function called crypt

– See the wiki for more information Extensible Networking Platform 33 - CSE 330 – Creative Programming and Rapid Prototyping

33

Smile Life

When life gives you a hundred reasons to cry, show life that you have a thousand reasons to smile

Get in touch

© Copyright 2015 - 2024 PDFFOX.COM - All rights reserved.