Test 1 - My SQL

examples of databases

An online store database that stores products, customer details and orders A database for a web forum that stores members, forums, topics and posts A database for a blog system, such as WordPress, that stores users, blog posts, categories, tags, and comments

MySQL advantages for web developers compared to some other systems:

It's open source, which means it's free for anyone to use and modify. It's widely available. MySQL can be installed on many different platforms, and it usually comes standard with most web hosting setups. It's easy to use. Setting up and working with MySQL databases is relatively straightforward. It works well with PHP. As of version 5.3, PHP has a native MySQL driver that is tightly coupled with the PHP engine, making it a good choice for PHP coders.

What Can SQL do?

SQL can execute queries against a database SQL can retrieve data from a database SQL can insert records in a database SQL can update records in a database SQL can delete records from a database SQL can create new databases SQL can create new tables in a database SQL can create stored procedures in a database SQL can create views in a database SQL can set permissions on tables, procedures, and views

Here are some reasons to use a database instead of text files:

They're fast. Databases use indexes, or keys, to find records of data extremely quickly. They're reliable. A DBMS ensures that the data in the database is read and written reliably, without corrupting the data. They let you link records together.

Text types:

*CHAR(size)-Holds a fixed length string. Can store up to 255 characters *VARCHAR(size)-Holds a variable length string. Can store up to 255 characters *TINYTEXT-Holds a string with a maximum length of 255 characters *TEXT-Holds a string with a maximum length of 65,535 characters *BLOB *MEDIUMTEXT *MEDIUMBLOB *LONGTEXT *LONGBLOB *ENUM(x,y,z,etc.) *SET

Date types:

*DATE() YYYY-MM-DD *DATETIME() Format: YYYY-MM-DD HH:MI:SSNote: The supported range is from '1000-01-01 00:00:00' to '9999-12-31 23:59:59' *TIMESTAMP() Format: YYYY-MM-DD HH:MI:SS Note: The supported range is from '1970-01-01 00:00:01' UTC to '2038-01-09 03:14:07' UTC *TIME() Format: HH:MI:SS Note: The supported range is from '-838:59:59' to '838:59:59' *YEAR() A year in two-digit or four-digit format. Note: Values allowed in four-digit format: 1901 to 2155. Values allowed in two-digit format: 70 to 69, representing years from 1970 to 2069

How to create a database with MySQL workbench

*Login to your MySQL server using your root user and password *Click on the New Schema icon in the menu, and then enter a name (name your database in ALL LOWERCASE) for your new database in the field as shown. *Click the Apply button to generate the SQL script. *Click the Apply button again to execute the create database statement, and create your new database

How to create a database with MySQL workbench(PT3)

*Select the Selected Schema radio option, and choose your database schema from the list. *Select the appropriate privileges to allow the user access to the selected database. Most modern website software will only require the permissions listed below. *Click Save Changes to complete your new user setup. (Select, Insert, Update, Delete, Create, Alter, Index, Drop, Create Temporary Tables, Lock Tables)

Number types:

*TINYINT(size) *SMALLINT(size) *MEDIUMINT(size) *INT(size *BIGINT(size) *FLOAT(size,d) *DOUBLE(size,d) *DECIMAL(size,d)

How to create a database with MySQL workbench(PT2)

*Under the Management section we will create a new database user and assign privileges to your new database. *Click on Users and Privileges. (you may have to use your root password here) *Then click on Add Account. *Enter a login name for the new user, type localhost and a new password as shown. *Click Apply to create the new user account. *To assign privileges for this user to access a specific database, click on the Schema Privileges tab. *Click the user account from the list of users on the left. *Click the Add Entry button.

SQL is an

ANSI (American National Standards Institute) standard, there are different versions of the SQL language. However, to be compliant with the ANSI standard, they all support at least the major commands (such as SELECT, UPDATE, DELETE, INSERT, WHERE) in a similar manner.

SQL stands for

Structured Query Language

database management system, or DBMS

The software that manages databases

MySQL is known as

a relational database management system, or RDBMS.

SQL is

a standard language for accessing databases.

A database is

a structured collection of data

If you haven't used a database for your website before, you may have stored data in text files such as

comma-separated value (CSV) files

A database

is a collection of information that is organized so that it can easily be accessed, managed, and updated.

One of the most popular database models is

is the relational model

a database manager

provides users the capabilities of controlling read/write access, specifying report generation, and analyzing usage.

Relational databases let you store different types of data in groups known as

tables

MySQL Data Types

text, number, and Date/Time types.

Here's an example of a CSV file:

username,password,emailAddress,firstName,lastName "johnsmith","brickwall53","[email protected]","John","Smith" "maryjones","garden37","[email protected]","Mary","Jones"

Database model

ways to organize data in a database