MYSQL DATABASE

MySQL Database SystemInstallation Overview

SQL summary

04/22/23 BGA 2

2-Tier Architecture

WebBrowser(Client)

WebServer

PHP

04/22/23 BGA 3

3-Tier Architecture

WebBrowser(Client)

DatabaseServer

WebServer PHP

04/22/23 BGA 4

Command Line Client

The standard command line client is c:\mysql\bin\mysql.exe The command line client can be used

to send commands and SQL queries to the MySQL server

There are also GUI clients Windows GUI client: HeidiSQL WEB GUI client: phpmyadmin

04/22/23 BGA 5

Client-Server Interaction

MySQLServer

ClientProgram

Make a request(SQL query)

Get results

Client program can be a MySQL command line client,GUI client, or a program written in any language suchas C, Perl, PHP, Java that has an interface to theMySQL server.

04/22/23 BGA 6

Connecting to the Server

Use a command prompt that sets the path to c:\mysql\bin

The following command connects to the server: mysql -u root -p you are prompted for the root password. you can now send comands and SQL

statements to the server

04/22/23 BGA 7

Entering commands (1)

Show all the databases SHOW DATABASES;

mysql> SHOW DATABASES;+-------------+| Database |+-------------+| bookstore || employee_db || mysql || student_db || test || web_db |+-------------+

04/22/23 BGA 8

Entering commands (2)

Choosing a database and showing its tables USE test;SHOW tables;mysql> USE test;Database changedmysql> SHOW tables;+----------------+| Tables_in_test |+----------------+| books || name2 || names || test |+----------------+4 rows in set (0.00 sec)mysql>

04/22/23 BGA 9

Entering commands (3)

Show the structure of a table DESCRIBE names;

mysql> DESCRIBE names;+-----------+-------------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+-----------+-------------+------+-----+---------+----------------+| id | int(11) | | PRI | NULL | auto_increment || firstName | varchar(20) | | | | || lastName | varchar(20) | | | | |+-----------+-------------+------+-----+---------+----------------+3 rows in set (0.00 sec)

mysql>

04/22/23 BGA 10

Entering commands (4)

Show the rows of a table (all columns) SELECT * FROM names;

mysql> SELECT * FROM names;+----+-----------+------------+| id | firstName | lastName |+----+-----------+------------+| 1 | Fred | Flintstone || 2 | Barney | Rubble |+----+-----------+------------+2 rows in set (0.00 sec)

mysql>

04/22/23 BGA 11

Entering commands (5)

Inserting a new record INSERT INTO names (firstName,lastName) VALUES ('Rock','Quarry');

SELECT * FROM names;mysql> INSERT INTO names (firstName, lastName) VALUES ('Ralph', 'Quarry');Query OK, 1 row affected (0.02 sec)mysql> SELECT * FROM names;+----+-----------+------------+| id | firstName | lastName |+----+-----------+------------+| 1 | Fred | Flintstone || 2 | Barney | Rubble || 3 | Ralph | Quarry |+----+-----------+------------+3 rows in set (0.00 sec)mysql>

04/22/23 BGA 12

Entering commands (6)

Updating a record UPDATE names SET lastName = 'Stone'WHERE id=3;

SELECT * FROM names;mysql> UPDATE names SET lastName = 'Stone' WHERE id=3;Query OK, 1 row affected (0.28 sec)Rows matched: 1 Changed: 1 Warnings: 0mysql> SELECT * FROM names;+----+-----------+------------+| id | firstName | lastName |+----+-----------+------------+| 1 | Fred | Flintstone || 2 | Barney | Rubble || 3 | Ralph | Stone |+----+-----------+------------+3 rows in set (0.00 sec)mysql>

04/22/23 BGA 13

SQL commands SHOW, USE

SHOW Display databases or tables in current

database; Example (command line client): show databases; show tables;

USE Specify which database to use Example use bookstore;

04/22/23 BGA 14

The CREATE Command

Specifying primary keys

CREATE TABLE table_name( column_name1 column_type1 NOT NULL DEFAULT '0', column_name2 column_type2, ... column_nameN column_typeN, PRIMARY KEY (column_name1));

04/22/23 BGA 15

The CREATE Command

autoincrement primary integer keys

CREATE TABLE table_name( column_name1 column_type1 PRIMARY KEY NOT NULL DEFAULT '0' AUTO_INCREMENT, column_name2 column_type2, ... column_nameN column_typeN,);

04/22/23 BGA 16

The DROP Command

To delete databases and tables use the DROP command

Examples DROP DATABASE db_name; DROP DATABASE IF EXISTS db_name; DROP TABLE table_name; DROP TABLE IF EXISTS table_name;

Note: Don't confuse DROP with DELETE which deletes rowsof a table.

04/22/23 BGA 17

The INSERT Command

Inserting rows into a table

INSERT INTO table_name ( col_1, col_2, ..., col_N)VALUES ( val_1, val_2, ..., val_N);

String values are enclosed in single quotes by defaultbut double quotes are also allowed. Literal quotesneed to be escaped using \' and \"

04/22/23 BGA 18

The SELECT Command (1)

Selecting rows from a table Simplest form: select all columns

Select specified columns

Conditional selection of rows

SELECT column_list FROM table_name;

SELECT * FROM table_name;

SELECT column_list FROM table_nameWHERE condition;

04/22/23 BGA 19

The SELECT Command (2)

Specifying ascending row ordering

Specifying descending row ordering

SELECT column_list FROM table_nameWHERE conditionORDER by ASC;

SELECT column_list FROM table_nameWHERE conditionORDER by DESC;

04/22/23 BGA 20

The SELECT Command (3)

There are many other variations of the select command.

Example: finding the number of records in a table assuming a primary key called id:

Can also perform searching using the WHERE option

SELECT COUNT(id) FROM table_name

04/22/23 BGA 21

The UPDATE Command

Used to modify an existing record

Conditional update version

UPDATE table_nameSET col_1 = 'new_value1',..., col_n = 'new_value2';

UPDATE table_nameSET col_1 = 'new_value1',..., col_n = 'new_value2'WHERE condition;

04/22/23 BGA 22

marks.sql (1)

studentID first_name

USE test;CREATE TABLE marks ( studentID SMALLINT AUTO_INCREMENT NOT NULL, first_name VARCHAR(20) NOT NULL, last_name VARCHAR(20) NOT NULL, mark SMALLINT DEFAULT 0 NOT NULL, PRIMARY KEY (studentID));

markstable

last_name mark

04/22/23 BGA 23

marks.sql (2)

-- Insert some rows into marks table

INSERT INTO marks (first_name, last_name, mark) VALUES ('Fred', 'Jones', 78);INSERT INTO marks (first_name, last_name, mark) VALUES ('Bill', 'James', 67);INSERT INTO marks (first_name, last_name, mark) VALUES ('Carol', 'Smith', 82);INSERT INTO marks (first_name, last_name, mark) VALUES ('Bob', 'Duncan', 60);INSERT INTO marks (first_name, last_name, mark) VALUES ('Joan', 'Davis', 86);

04/22/23 BGA 24

The Marks Table

Selecting the complete table

SELECT * FROM marks;

+-----------+------------+-----------+------+| studentID | first_name | last_name | mark |+-----------+------------+-----------+------+| 1 | Fred | Jones | 78 || 2 | Bill | James | 67 || 3 | Carol | Smith | 82 || 4 | Bob | Duncan | 60 || 5 | Joan | Davis | 86 |+-----------+------------+-----------+------+5 rows in set (0.00 sec)