mysql tutorial for beginners (4/8) : MySQL Syntax and Datatype

Показать описание

mysql tutorial for beginners (4/8) : MySQL Syntax and Datatype

The commands you’ll use most often are a lot, I’ll cover most of these as we proceed, but first, you need to remember a couple of points about MySQL commands:

1- SQL commands and keywords are case-insensitive. CREATE in capital case, create in small case, and CrEaTe in random case, all mean the same thing. However, for the sake of clarity, the recommended style is to use uppercase.

2- Table names are case-sensitive on Linux and OS X, but case-insensitive on Windows. So for portability purposes, you should always choose a case and stick to it. The recommended style is to use lowercase for tables.

MySQL Data Types
-------------------------------
When creating a table in MySQL it is important to know the type you wish to use for each attribute.

The most common used data types:
----------------------------------------------------------
INT UNSIGNED : Makes the column take an integer,
large enough for you to store more than 4 billion records in the table.

NOT NULL : Ensures that every column has a value.
Many programmers use NULL in a field to indicate that it doesn’t have any value.
But that would allow duplicates, which would violate the whole reason for this column’s existence. So we disallow NULL values.

AUTO_INCREMENT: Causes MySQL to set a unique value for this column in every row. We don’t really have control over the value that this column will take in each row, but we don’t care: all we care about is that we are guaranteed a unique value.

KEY : An auto-increment column is useful as a key, because you will tend to search for rows based on this column.
Each entry in the column Assigned Auto-Increment data type will now have a unique number, with the first starting at 1 and the others counting upward from there.

UNIQUE (column_name, ...)-used to specify which fields are unique

Primary keys
----------------------
The importance of having a key with a unique value for each row will come up when we start to combine data from different tables. “The AUTO_INCREMENT data type introduced the idea of a primary key when creating the auto-incrementing column, which could have been used as a primary key

Creating a database:
----------------------------------
To create new database, use the following command:

CREATE DATABASE the database name is examSystem;

Now that you’ve created the database, you want to work with it, so issue the following command:

USE examSystem;

Creating users:
------------------------
So let’s create a user who can access just the new examSystem database and all its objects, by entering the following command:

GRANT ALL ON examSystem.* TO 'Safaa'@'localhost'
IDENTIFIED BY ‘mypassword';

What this does is allow the user Safaa full access to the examSystem database using the password mypasswd.

Creating a table:
--------------------------
At this point, you should now be logged into MySQL with ALL privileges granted for the database examSystem, so you’re ready to create your first table.
Make sure the correct database is in use by typing the following:

USE examSystem;

Let’s create students table, by typing the following command:
CREATE TABLE students,

Open parentheses,

The first column in the table students,
'id_student' specify the data type for this field to be SMALLINT UNSIGNED NOT NULL
AUTO_INCREMENT, to add new column, you need to separate them by coma,
'name' VARCHAR(lO) NOT NULL,
'surname' VARCHAR(20) NOT NULL,
'email' VARCHAR(30) NOT NULL,
You need to make one column primary key, and that will be id_student
PRIMARY KEY ('id_student'),
Email values must be unique values, then you need to use Unique data type for email field:
UNIQUE ('email'))
engine = InnoDB COMMENT = 'Super table';

The engine field at the end is not really necessary, but you should include it to force the type of table you are creating.
The COMMENT field is just an example of how you would insert a comment for a created table.

To check whether your new table has been created, type the following:
DESCRIBE student;
You can also use DESCRIBE command to remind yourself about a table’s field and the types of data in each one.

Table students has been created! But, it’s empty, so, on the next tutorial we will learn how to insert, update and delete data.

Subscribe for more:
----------------------------------------------------------------------------

SWE.Safaa Al-Hayali - saf3al2a