Study with the several resources on Docsity
Earn points by helping other students or get them with a premium plan
Prepare for your exams
Study with the several resources on Docsity
Earn points to download
Earn points by helping other students or get them with a premium plan
THe notes for mysql.aaaaaaaaaaaaaaaaaaaaaaaaaaaaa
Typology: Cheat Sheet
1 / 63
This page cannot be seen from the preview
Don't miss anything!
Module: Database Design & Implementation Qualification: Advanced Certificate In Web Development
By the end of this tutorial you will be able to understand basics MySQL, table, constrains, view, procedures, backup & restore etc.
Contents S. No. Topic Description Required / Optional 01 MySQL Introduction Required 03 MySQL Installation Required 04 MySQL Clients Required 05 Data Types Required 06 Auto Increment Fields Required 07 Create, Select, Update, Delete Required 08 Constraints Required 09 Joins Required 10 Index Required 11 Views & Stored Procedures Required 12 Import & Export Required 13 Security & Privileges Required
MySQL Introduction Most popular Open Source RDBMS. Can be used for Web based applications. Runs on a server. Ideal for both small and large applications. Very fast, reliable, and easy to use. Uses standard SQL. Available for multiple platforms Free to download and use. Developed, distributed, and supported by Oracle Corporation.
Client Server Database Database Server is a separate process on a host. Clients can be on any machine. Many programs may be clients using a standard API. Server mysqld "mysql" utility PHP, python, .Net Java phpMyAdmin client Client side Server side server controls access to database
Install MySQL 5. Default Port for MySQL is 3306 Download 5.6.36 & install https://dev.mysql.com/downloads/mysql/5.6.html#downloads Refer to Module 1 Slides for Instructions
Install phpMyAdmin Free, open source platform for administering MySQL Download phpMyAdmin from https://www.phpmyadmin.net/downloads/ Download phpMyAdmin-4.8.1-English.zip
Running SQL Commands Run SQL Commands from mysql Command line tool phpMyAdmin tool using browser MySQL Workbench Run queries from libraries Administer Using mysql Command line tool mysqladmin Tool phpMyAdmin tool using browser MySQL Workbench
mysql Console Client MySQL is a simple SQL shell. Query results are presented in an ASCII-table format. To Invoke MySQL Open Command Prompt by Start -> Run -> cmd (type) Change Directory by running the command cd C:\Program Files\MySQL\MySQL Server 5.6\bin Type mysql – u root -p To exit MySQL type exit in Mysql> exit
MySQL phpMyAdmin Client Login to phpMyAdmin from http://localhost/phpmyadmin/ Use the Login Name root and the Password *** (Your password) Can do most tasks in phpMyAdmin as in mysql command line.
Database Tables A database consists of one or more tables. Table is made up of rows and columns.
Common Integer & Decimal Types INTEGER (or INT) and SMALLINT Float & Double FLOAT(8,5) will look like 999.99999. Use Decimal(8,5) for Fixed Point Types
Date & Time Type – DATETIME, DATE, and TIMESTAMP DATETIME, DATE, and TIMESTAMP Datetime uses 8 Bytes to store the value
String Types - BLOB and TEXT Types BLOB - binary large object that can hold a variable amount of data. TEXT – Store Text.
Auto Increment Field Unique number for new record while inserting data. Mostly Primary Key field. Example "ID" column to be an auto-increment Primary Key field CREATE TABLE Persons ( ID int NOT NULL AUTO_INCREMENT, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int, PRIMARY KEY (ID) );
