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/11/2021 PHP BGA 2
3 -Tier Architecture Web Browser (Client) 3/11/2021 Web Server BGA PHP Database Server 3
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 n n Windows GUI client: Heidi. SQL WEB GUI client: phpmyadmin 3/11/2021 BGA 4
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/11/2021 BGA 5
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/11/2021 BGA 6
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/11/2021 BGA 7
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/11/2021 BGA 8
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/11/2021 BGA 9
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/11/2021 BGA 10
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/11/2021 BGA 11
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/11/2021 BGA 12
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/11/2021 BGA 13
The CREATE Command 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/11/2021 BGA 14
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/11/2021 BGA 15
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/11/2021 BGA 16
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/11/2021 BGA 17
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/11/2021 BGA 18
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/11/2021 BGA 19
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/11/2021 BGA 20
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/11/2021 BGA 21
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/11/2021 BGA 22
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/11/2021 BGA 23
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/11/2021 BGA 24
- Slides: 24