1 / 24

MYSQL DATABASE

MYSQL DATABASE. MySQL Database System Installation Overview SQL summary. 2-Tier Architecture. Web Server. Web Browser (Client). PHP. 3-Tier Architecture. Web Browser (Client). Web Server. Database Server. PHP. Command Line Client. The standard command line client is

msteed
Download Presentation

MYSQL DATABASE

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. MYSQL DATABASE MySQL Database System Installation Overview SQL summary

  2. 2-Tier Architecture WebServer WebBrowser(Client) PHP BGA

  3. 3-Tier Architecture WebBrowser(Client) WebServer DatabaseServer PHP 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 BGA

  5. Client-Server Interaction Make a request(SQL query) MySQLServer ClientProgram 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. 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 BGA

  7. Entering commands (1) • Show all the databases • SHOW DATABASES; mysql> SHOW DATABASES;+-------------+| Database |+-------------+| bookstore || employee_db || mysql || student_db || test || web_db |+-------------+ 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> 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> 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> 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> 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> 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; 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)); BGA

  15. The CREATE Command • autoincrement primary integer keys CREATE TABLE table_name( column_name1 column_type1 PRIMARYKEY NOT NULL DEFAULT '0' AUTO_INCREMENT, column_name2 column_type2, ... column_nameN column_typeN,); 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. 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 \" BGA

  18. The SELECT Command (1) • Selecting rows from a table • Simplest form: select all columns • Select specified columns • Conditional selection of rows SELECT * FROM table_name; SELECT column_list FROM table_name; SELECT column_list FROM table_nameWHERE condition; 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; 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 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; BGA

  22. marks.sql (1) mark studentID first_name last_name markstable 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)); 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); 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) BGA

More Related