MySQL structure and syntax
MySQL is a relational database system, stores bits of information in separate areas and links those areas together.
Store virtually anything in a database example:- address book, product catalog.
MySQL Structure
MySQL allows separating information into tables or “areas of pertinent information.”
In non-relational database systems, all the information is stored in one big area, which makes it much more difficult and bulky to sort and extract required data.
In MySQL, each table consists of separate fields, which represent each bit of information.
For example, the field customer’s first name, and another field could contain his last name.
Fields can hold different types of data, such as text, numbers, dates, and so on.
Database tables based on what type of information to store in them.
The separate “areas” or tables of MySQL are then linked together with some common denominator, where the values of the common field are the same.
Example:- customer table that includes a customer’s name, address, and ID number, and another table that includes the customer’s ID number and past orders he has placed. The common field is the customer’s ID number, and the information stored in the two separate tables would be linked together via fields where this number is equal. This enables us to see all the information related to this customer at one time.
Field Types
When you create a table initially, need to tell the MySQL server what types of information will be stored
in each field.
The different types of fields and some examples are listed in the table that follows.
❑ Less than 255 characters, fixed-length: Use char.
❑ 1–255 characters, variable length: Use varchar if you want to delete any trailing spaces, or if want to set a default value. Use tiny text if don’t care about trailing spaces or a default value or if your text does not need to be case-sensitive. Use a tiny blob if don’t care about trailing spaces or a default value, but your text does need to be case-sensitive.
❑ 256–65536 characters: Use text if your text does not need to be case-sensitive in searches, sorts, or comparisons, use blob if the text is case-sensitive.
❑ 65537–1677215 characters: Use medium text if the text does not need to be case sensitive, use mediumblob if the text is case-sensitive.
❑ 1677216–4294967295 characters: Use long text if the text does not need to be case sensitive, use long blob if the text is case-sensitive.
❑ Yes. It may contain letters or numbers, but it must be one of a finite number of values. Use menu.
❑ No. It will consist of dates and/or times. Use timestamp for store the time and date the information was entered or updated. To store date and time both, use DateTime. Only the year, use year.
❑ No. consist only of numbers, and mathematical functions will be performed on this field.
❑ Integers from -127 to 127, use tinyint.
❑ Integers from -32768 to 32767, use smallint.
❑ Integers from -8388608 to 8388607, use mediumint.
❑ Integers from -2147483648 to 2147483647, use int.
❑ Integers from -9223372036854775808 to 9223372036854775807, use bigint.
❑ Integers from 0 to 255, use tinyint unsigned.
❑ Integers from 0 to 65535, use smallint unsigned.
❑ Integers from 0 to 16777215, use mediumint unsigned.
❑ Integers from 0 to 4294967295, use int unsigned.
❑ Integers from 0 to 18446744073709551615, use bigint unsigned.
❑ Decimals with fixed decimal places, use dec.
❑ No. It will consist of only numbers, but mathematical functions will not be performed on this field. Use the preceding guidelines for text/number mix in the field.
Null / not null
Null tells MySQL that it is okay if nothing is stored in the field, and not null tells MySQL to require something, anything, to be stored there.
If a field has been defined as not null and nothing is entered by the user, an error pops up.
A number zero is different from a null entry.
Indexes
MySQL uses indexes to expedite the process of searching for a particular row of information.
MySQL can jump to the approximate location of your data much more quickly. It does through the use of indexes, also known as keys.
MySQL requires at least one index on every table, use a primary key, or unique identifier that helps keep the data separate. This field must be “not null” and “unique”; an example would be a customer ID number to keep your customers separate.
MySQL also provides a feature that allows a value in a field to be automatically incremented by one.
This auto_increment parameter is useful for making sure your primary key is being populated with unique numbers.
Unique
MySQL makes sure that absolutely no duplicates exist for a particular field. Typically, this is used for only the primary key in your table, but it can be used with any field.
Auto Increment
A field that automatically increases by one whenever a new record is added. a useful function for assigning ID numbers.
0 Comments