Learning SQL Everyday: Day 2

12:25 PM Unknown 1 Comments



 Introduction to the CREATE SQL statement:

The CREATE statement is one of the DDL (Data Definition Language) in that it is used to define/create database objects like, database, table, user etc.

We will use the CREATE statement to do the following things:

•   To create a database
•   To create a table structure
•   To create a database user

Today, We will see the how the CREATE statement is used to achieve above points in MySQL.


The CREATE DATABASE Statement:

Before creating a database, we need to understand what a database is. A database is a collection of related tables and serves as a repository. For instance you can think of a school as a database and the teachers, students, courses etc as a table. The interaction among the entity (objects) teacher, student and course etc. is what we call relationship among them. You know that a student takes a course and a course is tutored (given) by a teacher. So when you want to know who is teaching who? Who is taking what course etc, you have to trace the bits and pieces of information across each related table. Hence you will get full information when you combine the related records across each related tables. As you can see, the database contains a full information by keeping the data across related tables.

The CREATE DATABASE statement is used to create a database in a DBMS. As you we have discussed in Day 1, a DBMS can have a number of databases in it. So if you want to create a new database in your DBMS, you will have to use the CREATE DATABASE statement. The syntax is given as follows:

CREATE DATABASE dbname;

N.B: dbname is the name of your database. Remember that you need to end the statement with a semi-colon (;). This is important when you are writing statements in a MySQL console window.

Eg.

CREATE DATABASE db_school;

This statement will create a blank database (no tables in it) named db_school in your DBMS. Unless you select the newly created database and execute a CREATE TABLE statement, the database will contain no tables in it.


The CREATE TABLE Statement:

The CREATE TABLE Statement is used to create table in the selected database. This statement is also one of the DDL of SQL in that we use the CREATE TABLE statement to create a new database structure, in this case a TABLE.

A database table is a collection of rows/tuples. Each row represents a record/an entity we are persisting (saving) to the database. In turn, a row is a collection of fields/columns. Continuing from our first example, if you take the object/instance Student, you know that a student object should have name, id_number, sex, department etc.

Each attributes we have listed as name, id_number, sex etc. are known as fields/columns. When you actually merge this together, they will form a row/record. A record is one instance/object of the table Student in our example.



If you consider this example:

STUDENT

Name
Id_Number
Sex
Department
Selam Alemu
SC/2345/00
Female
Math
Abebe Kebede
SC/1234/00
Male
Physics

As you can see from the above table each column represents fields of the object student.

If you view horizontally (records/tuples) it represents a single student object.

It might be note worthy to remember this relationship:

Table = Class = File
Tuple/Record = Object
Column = Field/attribute

These points will be used when we do mapping later on in our journey.

The CREATE TABLE Syntax:

CREATE TABLE table_name
(
column_name1 data_type(size),
column_name2 data_type(size),
column_name3 data_type(size),
....
);

The column name parameters specify the names of the columns of the table.
The data_type parameter specifies what type of data the column can hold (e.g. varchar, integer, decimal, date, etc.).
The size parameter specifies the maximum length of the column of the table.
Example:

CRATE DATABASE db_school;

USE db_school;

CREATE TABLE tbl_student(
   Name varchar(50) not null,
   Id_Number varchar(20) not null,
   Sex varchar(10) not null,
   Department varchar(50) not null,
   PRIMARY KEY(Id_Number)
);

Assuming you created the database db_school, you need to select the specific database you wanted the new table to reside upon. Remember we have said a DBMS can contain many databases. For selecting which database to use, we use the ‘USE’ statement as listed in the example.

The above SQL code creates a table named ‘tbl_student’. The table has four columns with their data type and size specified. For eg. You can see that name is given only 50 characters. If you try to give a name value exceeding this size, the data value will be truncated. The ‘Not Null’ value in each column shows that empty value /null value for that filed/column is not allowed and will have to always contain a data value. Finally we have added the PRIMARY KEY information in the table.

It is not a must that a table shall have a primary key. But it is always a good idea to have a primary key defined for your tables because that is the only way you can create relationship b/n tables. Of course you can have flat table (a table with no relationship at all).

So how do you determine which column of a table should be a PK (Primary key)? The following are the criteria you should check:

·      The field/attribute MUST NOT be NULL
·      It must be UNIQUE for a single record in the table

If your attribute (Candidate key) happens to fulfill the above two points, then it can be taken as a Primary key, and hence you let your DBMS know that this particular column is a primary key. To do that you can use the PRIMARY KEY statement as used in the example.

The CREATE USER Statement:

Since we are using MySQL DBMS, we will see how to create and grant a privilege to a user. By default MySQL has a user called ‘root’. This user has all the privilege so you can consider this as a super user.

Using this account, we can create our own users with different level of privilege.

The syntax for creating a user is:

CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';

N.B:
‘newuser’  is the username that you want it to be. It should be a valid variable name.
‘password’ is the password of the new user you are just creating.

Sadly, at this point newuser has no permissions to do anything with the databases. In fact, if newuser even tries to login (with the password, password), they will not be able to reach the MySQL shell. 

Therefore, the first thing to do is to provide the user with access to the information they will need.

GRANT ALL PRIVILEGES ON * . * TO 'newuser'@'localhost';
The asterisks in this command refer to the database and table (respectively) that they can access—this specific command allows to the user to read, edit, execute and perform all tasks across all the databases and tables in the DBMS.




Once you have finalized the permissions that you want to set up for your new users, always be sure to reload all the privileges.

FLUSH PRIVILEGES;
How To Grant Different User Permissions

Here is a short list of other common possible permissions

·       ALL PRIVILEGES- as we saw previously, this would allow    a MySQL user all access to a designated database (or if no database is selected, across the system)
  • CREATE- allows them to create new tables or databases
  • DROP- allows them to them to delete tables or databases
  • DELETE- allows them to delete rows from tables
  • INSERT- allows them to insert rows into tables
  • SELECT- allows them to use the Select command to read through databases
  • UPDATE- allow them to update table rows
  • GRANT OPTION- allows them to grant or remove other users' privileges

To provide a specific user with a permission, you can use this syntax:

GRANT [type of permission] ON [database name].[table name] TO ‘[username]’@'localhost’;

If you want to give them access to any database or to any table, make sure to put an asterisk (*) in the place of the database name or table name and each time you update or change a permission be sure to use the Flush Privileges command to make the changes.
If you need to revoke permission, the structure is almost identical to granting it:

REVOKE [type of permission] ON [database name].[table name] FROM ‘[username]’@‘localhost’;
Just as you can delete databases with DROP, you can use DROP to delete a user altogether:

DROP USER ‘newuser@‘localhost’;




1 comment:

Subscribe to: Post Comments (Atom)