1stwebdesigner

---

MySQL Guide for Beginners: What You Must Know Posted: 19 Jul 2014 06:00 AM PDT A database is an essential part of most websites. From simple blogs to complex e-commerce websites, everything is being driven by a database. If you are a web developer, you definitely know a lot about MySQL. Is the most popular open source database system Uses SQL (Structured Query Language) to manipulate information inside the database It’s free, very dependable and fast It has several application programming interfaces (APIs) Easy to use to develop software or applications This article is a MySQL guide article that tackles how does the program works and what it can do for both users and programmers. What is MySQL? MySQL is an open source multi-threaded, relational database management system (RDBMS) created by Michael “Mondy” Widenius back in 1995. It was released under GNU GPL (General Public License) due to its popularity in 2000. MySQL AB is the company that owns and develops MySQL, which is presently a subsidiary of Sun Microsystems. The success of MySQL as a leading database is due to: Its cost effectiveness Open-source database It’s reliability, performance and features The following list shows the most important features of MySQL It is Relational Database System It is a client/server system Supports as its database language — as its name suggests – SQL (Structured Query Language) Remarkably scalable, and able to handle tens of thousands of tables and billions of rows of data Designed to be fully multi-threaded using kernel threads, to easily use multiple CPUs if they are available Executes very fast joins using an optimized nested-loop join A privilege and password system that is very flexible and secure, and enables host-based verification All data is saved in the chosen character set Tested with a broad range of different compilers Two Components of MySQL There are two main components to MySQL: 1. The MySQL database server, mysqlId - It runs all the time in the background, accepting connections from client programs and so on. 2. Various client and utility programs – Includes mysql command-line MySQL Monitor Client. Ways to install MySQL and its associated programs 1. Official MySQL installation package – MySQL official website has different prebuilt packages available for different platforms. Simply download the package file, extract it, and run the installer. 2. Linux Package Manager – Using Linux package manager like Ubuntu Sotware Centre comes with a installation of MySQL along with Apache and PHP and some other softwares. 3. XAMPP, LAMP, WAMP package – This is the easiest way to install a complete MySQL-base development setup on your computer. It includes Apache web server, MySQL, PHP and Perl on the installation. MySQL Statement and Clauses Below are the list of the basic MySQL statements that should prove useful for basic CRUD operations (create, replace, update, delete, select) and performing some user authentication process. ALTER DATABASE ALTER TABLE ALTER VIEW ANALYZE TABLE BACKUP TABLE CACHE INDEX CHANGE MASTER TO CHECK TABLE CHECKSUM TABLE COMMIT CREATE DATABASE CREATE INDEX CREATE TABLE CREATE VIEW DELETE DESCRIBE DO DROP DATABASE DROP INDEX DROP TABLE DROP USER DROP VIEW EXPLAIN FLUSH GRANT HANDLER INSERT JOIN KILL LOAD DATA FROM MASTER LOAD DATA INFILE LOAD INDEX INTO CACHE LOAD TABLE…FROM MASTER LOCK TABLES OPTIMIZE TABLE PURGE MASTER LOGS RENAME TABLE REPAIR TABLE REPLACE RESET RESET MASTER RESET SLAVE RESTORE TABLE REVOKE ROLLBACK ROLLBACK TO SAVEPOINT SAVEPOINT SELECT SET SET PASSWORD SET SQL_LOG_BIN SET TRANSACTION SHOW BINLOG EVENTS SHOW CHARACTER SET SHOW COLLATION SHOW COLUMNS SHOW CREATE DATABASE SHOW CREATE TABLE SHOW CREATE VIEW SHOW DATABASES SHOW ENGINES SHOW ERRORS SHOW GRANTS SHOW INDEX SHOW INNODB STATUS SHOW LOGS SHOW MASTER LOGS SHOW MASTER STATUS SHOW PRIVILEGES SHOW PROCESSLIST SHOW SLAVE HOSTS SHOW SLAVE STATUS SHOW STATUS SHOW TABLE STATUS SHOW TABLES SHOW VARIABLES SHOW WARNINGS START SLAVE START TRANSACTION STOP SLAVE TRUNCATE TABLE UNION UNLOCK TABLES USE MySQL String Functions Below are the list of MySQL String Functions that are mostly used to create conditional statements for strings and some other associate elements. AES_DECRYPT AES_ENCRYPT ASCII BIN BINARY BIT_LENGTH CHAR CHAR_LENGTH CHARACTER_LENGTH COMPRESS CONCAT CONCAT_WS CONV DECODE DES_DECRYPT DES_ENCRYPT ELT ENCODE ENCRYPT EXPORT_SET FIELD FIND_IN_SET HEX INET_ATON INET_NTOA INSERT INSTR LCASE LEFT LENGTH LOAD_FILE LOCATE LOWER LPAD LTRIM MAKE_SET MATCH AGAINST MD5 MID OCT OCTET_LENGTH OLD_PASSWORD ORD PASSWORD POSITION QUOTE REPEAT REPLACE REVERSE RIGHT RPAD RTRIM SHA SHA1 SOUNDEX SPACE STRCMP SUBSTRING SUBSTRING_INDEX TRIM UCASE UNCOMPRESS UNCOMPRESSED_LENGTH UNHEX UPPER MySQL Date and Time Functions Below are the list of MySQL Date and Time functions as the name suggest these commands are use for manipulating date and time. ADDDATE ADDTIME CONVERT_TZ CURDATE CURRENT_DATE CURRENT_TIME CURRENT_TIMESTAMP CURTIME DATE DATE_ADD DATE_FORMAT DATE_SUB DATEDIFF DAY DAYNAME DAYOFMONTH DAYOFWEEK DAYOFYEAR EXTRACT FROM_DAYS FROM_UNIXTIME GET_FORMAT HOUR LAST_DAY LOCALTIME LOCALTIMESTAMP MAKEDATE MAKETIME MICROSECOND MINUTE MONTH MONTHNAME NOW PERIOD_ADD PERIOD_DIFF QUARTER SEC_TO_TIME SECOND STR_TO_DATE SUBDATE SUBTIME SYSDATE TIME TIMEDIFF TIMESTAMP TIMESTAMPDIFF TIMESTAMPADD TIME_FORMAT TIME_TO_SEC TO_DAYS UNIX_TIMESTAMP UTC_DATE UTC_TIME UTC_TIMESTAMP WEEK WEEKDAY WEEKOFYEAR YEAR YEARWEEK Who Uses MySQL? Bank of Finland The Bank of Finland developed a MySQL-based versatile Payment and Settlement System Simulator for making payments and settlement system simulations. Running on Windows, the application has been widely adopted by Central Bank economists worldwide. The White House A programming overhaul of the White House’s web site has set the tech world abuzz. The White House website database operates using a database foundation like MySQL. Adobe Adobe Systems is one of the largest software companies and the leading provider of creative tools for print, web, interactive, mobile, video, and film. Adobe embeds MySQL into several Adobe Creative Suite 3 components including Adobe Acrobat CS3, Adobe® Bridge CS3, and Adobe® Version CueR CS3 so that workgroups can work more efficiently on complex projects. iStockphoto Istockphoto.com is the biggest royalty-free stock photo community in the world, and its sister company, istockpro.com, is home to a host of illustrious professional photographers. Every week, approximately 5,000 photographers upload more than 2,500 photos (2.5 GB) to MySQL® — the world’s most popular open source database — and approximately 1,250(1.25 GB) are accepted and posted to istock Web sites. Where to Learn MySQL? Although there are plenty of Websites where you can learn MySQL, I listed down some free and premium sites that can help you to have a strong foundation on this subject. 1. MySQL Documentation MySQL documentation contains all the full list of commands and updates you want to learn everything on this topic. 2. Lynda It is training library teaches computer skills in video format to members through monthly and annually. 3. Tizag A site designed to teach beginner web programmers how to use HTML, CSS, PHP and MySQL. It’s a great website to check out. MySQL Books 1. MySQL Cookbook by Paul DuBois. If you are a developer who has to store and retrieve data from MySQL database, this book is a must. This book explains the following: How to use mySQL client How to write MySQL program that connects to a DB, selects a DB, and queries data Explains table management, strings, date and time, query sorting, and summary report generation How to import and export data, and create stored procedures 2. MYSQL in a Nutshell by Russell Dyer. If you've used other "In a Nutshell" series from O'Reilly, you would love this book. Please keep in mind that this is a reference manual to MySQL statements, functions, and administrative utilities. This book contains the following: MySQL programming language API for PHP, Perl, and C. MySQL Explains replication, triggers, and stored procedures With different examples 3. MySQL Stored Procedure Programming, by Guy Harrison, Steven Feuerstein. Oracle developers never took MySQL seriously, until the stored procedures were implemented in MySQL 5. This book explains the following: How to implement stored procedures in MySQL Contains a lot of practical examples Covers the stored procedure fundamentals, including language fundamentals, blocks, conditional statements, and error handling Wrapping Up MySQL databases are a big topic to discuss. There are a lot more important areas to explore like normalization, grouping tables, updating and deleting records on the database. In this article, I introduce to you the basic information you need to learn about MySQL. I recommend exploring more books and video tutorials on the web to have a solid foundation on this topic. Hope you learned something from this article and see you again next time!

You are subscribed to email updates from 1stwebdesigner To stop receiving these emails, you may unsubscribe now.	Email delivery powered by Google
Google Inc., 20 West Kinzie, Chicago IL USA 60610