MYSQL DATABASE My SQL Database System Installation Overview

MYSQL DATABASE My. SQL Database System Installation Overview SQL summary

2 -Tier Architecture Web Server Web Browser (Client) 3/2/2021 PHP BGA 2

3 -Tier Architecture Web Browser (Client) 3/2/2021 Web Server BGA PHP Database Server 3

SQL links n Tutorials n n n http: //www. w 3 schools. com/sql/ http: //www. sqlzoo. net http: //sqlcourse. com (part 2) http: //sqlcourse 2/com (part 1) My. SQL online reference manual n http: //dev. mysql. com/doc/mysql/en/Reference. h tml 3/2/2021 BGA 4

Command Line Client n n The standard command line client is c: mysqlbinmysql. exe The command line client can be used to send commands and SQL queries to the My. SQL server There also GUI clients such as My. CC 3/2/2021 BGA 5

Client-Server Interaction Make a request (SQL query) My. SQL Server Get results Client Program Client program can be a My. SQL command line client, GUI client, or a program written in any language such as C, Perl, PHP, Java that has an interface to the My. SQL server. 3/2/2021 BGA 6

Connecting to the Server n n Use a command prompt that sets the path to c: mysqlbin The following command connects to the server: n n n mysql -u root -p you are prompted for the root password. you can now send comands and SQL statements to the server 3/2/2021 BGA 7

Entering commands (1) n Show all the databases n SHOW DATABASES; mysql> SHOW DATABASES; +-------+ | Database | +-------+ | bookstore | | employee_db | | mysql | | student_db | | test | | web_db | +-------+ 3/2/2021 BGA 8

Entering commands (2) n Choosing a database and showing its tables n USE test; SHOW tables; mysql> USE test; Database changed mysql> SHOW tables; +--------+ | Tables_in_test | +--------+ | books | | name 2 | | names | | test | +--------+ 4 rows in set (0. 00 sec) mysql> 3/2/2021 BGA 9

Entering commands (3) n Show the structure of a table n DESCRIBE names; mysql> DESCRIBE names; +-------------+-----+------------+ | Field | Type | Null | Key | Default | Extra | +-------------+-----+------------+ | id | int(11) | | PRI | NULL | auto_increment | | first. Name | varchar(20) | | | last. Name | varchar(20) | | | +-------------+-----+------------+ 3 rows in set (0. 00 sec) mysql> 3/2/2021 BGA 10

Entering commands (4) n Show the rows of a table (all columns) n SELECT * FROM names; mysql> SELECT * FROM names; +-----------+------+ | id | first. Name | last. Name | +-----------+------+ | 1 | Fred | Flintstone | | 2 | Barney | Rubble | +-----------+------+ 2 rows in set (0. 00 sec) mysql> 3/2/2021 BGA 11

Entering commands (5) n Inserting a new record n n INSERT INTO names (first. Name, last. Name) VALUES ('Rock', 'Quarry'); SELECT * FROM names; mysql> INSERT INTO names (first. Name, last. Name) VALUES ('Ralph', 'Quarry'); Query OK, 1 row affected (0. 02 sec) mysql> SELECT * FROM names; +-----------+------+ | id | first. Name | last. Name | +-----------+------+ | 1 | Fred | Flintstone | | 2 | Barney | Rubble | | 3 | Ralph | Quarry | +-----------+------+ 3 rows in set (0. 00 sec) mysql> 3/2/2021 BGA 12

Entering commands (6) n Updating a record n n UPDATE names SET last. Name = 'Stone' WHERE id=3; SELECT * FROM names; mysql> UPDATE names SET last. Name = 'Stone' WHERE id=3; Query OK, 1 row affected (0. 28 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> SELECT * FROM names; +-----------+------+ | id | first. Name | last. Name | +-----------+------+ | 1 | Fred | Flintstone | | 2 | Barney | Rubble | | 3 | Ralph | Stone | +-----------+------+ 3 rows in set (0. 00 sec) mysql> 3/2/2021 BGA 13

Executing SQL files n n It is usually better to use an editor to write an SQL script and send it to the server. A file of SQL commands such as books. sql can be executed by the server by using a command such as n n C: mysqlbinmysql -u root -p < books. sql This assumes that books. sql is in your current directory. Otherwise the complete path to books. sql must be supplied 3/2/2021 BGA 14

Database concepts (1) n n n A relational database management system consists of a number of databases. Each database consists of a number of tables. Example table column headings isbn title author pub books table 3/2/2021 year price rows (records) BGA 15

Some SQL data types (1) n n Each entry in a row has a type specified by the column. Numeric data types n n n TINYINT, SMALLINT, MEDIUMINT, BIGINT FLOAT(display_length, decimals) DOUBLE(display_length, decimals) DECIMAL(display_length, decimals) n 3/2/2021 NUMERIC is the same as DECIMAL BGA 16

Some SQL data types (2) n Date and time types n DATE n n DATETIME n n format YYYYMMDDHHMMSS TIME n n format YYYY-MM-DD HH: MM: SS TIMESTAMP n n format is YYYY-MM-DD format HH: MM: SS YEAR n 3/2/2021 default length is 4 BGA 17

SQL data types (3) n String types n CHAR n n VARCHAR n n variable length string, e. g. , VARCHAR(20) BLOB, TINYBLOB, MEDIUMBLOB, LONGBLOB n n fixed length string, e. g. , CHAR(20) same as TEXT, TINYTEXT. . . ENUM n 3/2/2021 list of items from which value is selected BGA 18

SQL commands SHOW, USE n SHOW n n n Display databases or tables in current database; Example (command line client): show databases; show tables; USE n n n Specify which database to use Example use bookstore; 3/2/2021 BGA 19

The CREATE Command (1) n CREATE creates a database table CREATE TABLE table_name ( column_name 1 column_type 1, column_name 2 column_type 2, . . . column_name. N column_type. N ); Note: To create a database use the statement CREATE db_name; 3/2/2021 BGA 20

The CREATE Command (2) n Specifying primary keys CREATE TABLE table_name ( column_name 1 column_type 1 NOT NULL DEFAULT '0', column_name 2 column_type 2, . . . column_name. N column_type. N, PRIMARY KEY (column_name 1) ); 3/2/2021 BGA 21

The CREATE Command (3) n autoincrement primary integer keys CREATE TABLE table_name ( column_name 1 column_type 1 PRIMARY KEY NOT NULL DEFAULT '0' AUTO_INCREMENT, column_name 2 column_type 2, . . . column_name. N column_type. N, ); 3/2/2021 BGA 22

Conditional Creation n Conditional database creation n n CREATE DATABASE IF NOT EXISTS db_name; Conditional table creation n CREATE TABLE IF NOT EXISTS table_name; 3/2/2021 BGA 23

The DROP Command n n To delete databases and tables use the DROP command Examples n n DROP DATABASE db_name; DATABASE IF EXISTS db_name; TABLE table_name; TABLE IF EXISTS table_name; Note: Don't confuse DROP with DELETE which deletes rows of a table. 3/2/2021 BGA 24

The INSERT Command n 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 default but double quotes are also allowed. Literal quotes need to be escaped using ' and " 3/2/2021 BGA 25

The SELECT Command (1) n n Selecting rows from a table Simplest form: select all columns SELECT * FROM table_name; n Select specified columns SELECT column_list FROM table_name; n Conditional selection of rows SELECT column_list FROM table_name WHERE condition; 3/2/2021 BGA 26

The SELECT Command (2) n Specifying ascending row ordering SELECT column_list FROM table_name WHERE condition ORDER by ASC; n Specifying descending row ordering SELECT column_list FROM table_name WHERE condition ORDER by DESC; 3/2/2021 BGA 27

The SELECT Command (3) n n There are many other variations of the select command. Example: finding the number of records in a table assuming a primary key called id: SELECT COUNT(id) FROM table_name n Can also perform searching using the WHERE option 3/2/2021 BGA 28

The UPDATE Command n Used to modify an existing record UPDATE table_name SET col_1 = 'new_value 1', . . . , col_n = 'new_value 2'; n Conditional update version UPDATE table_name SET col_1 = 'new_value 1', . . . , col_n = 'new_value 2' WHERE condition; 3/2/2021 BGA 29

marks. sql (1) student. ID first_name last_name marks table USE test; CREATE TABLE marks ( student. ID 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 (student. ID) ); 3/2/2021 BGA 30

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); 3/2/2021 BGA 31

Executing The Script n within My. SQL use a command such as n n source c: /. . . /marks. sql This adds the marks table to the test database 3/2/2021 BGA 32

The Marks Table n Selecting the complete table SELECT * FROM marks; +------------+------+------+ | student. ID | 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) 3/2/2021 BGA 33