To create a new user within the MySQL shell use this sql syntax:

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.

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.

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

Your changes will now be in effect.

How To Grant Different User Permissions

Here is a short list of other common possible permissions that users can enjoy.

  • 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 framework:

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.

Each time you update or change a permission be sure to use the Flush Privileges command.

If you need to revoke a permission, the structure is almost identical to granting it:

Just as you can delete databases with DROP, you can use DROP to delete a user altogether:

To test out your new user, log out by typing

and log back in with this command in terminal:

MySql Free tutorials

Os tutoriais são projetados para mostrar-lhe como criar e excluir objetos do banco de dados, seleção de dados, filtragem de dados, classificação de dados, funções incorporadas, junte-se tabelas, agregação de dados e muito mais.
  É um bom começo para aprender programar SQL, você também aprenderá a instalar e configurar um banco de dados MySQL. Para iniciar a procura simplesmente tutoriais sobre o menu do lado esquerdo.

    The use of table aliases means to rename a table in a particular SQL statement. The renaming is a temporary change and the actual table name does not change in the database. The column aliases are used to rename a table’s columns for the purpose of a particular SQL query.

Let’s create the table we will use

Script:

Check the table to see the data and the structure.

Very good

Some examples using Aliases for columns:

Example 1 :

  • ok, we have in our Test table the column “DE” which we know by now that is a abbreviation for Department.
  • now imagine we need to make a report with all the departments we have in our Test table. For us is going to be ok to understand the “DE” abbreviation but for some other persons this will be confusing.
  • so now the “AS” to work , as he will replace the column name with some other name we chose:

Note :- thou we have printed the column name “DE” as Department the actual column name was not changed, is just the result that will receive the Department column header.

Example 2 :

 

  • we can use as well two strings as column header using the following syntax:
  • they have to be inside double quotes.

 

Example 3:

 

  • we can use alias as well like this :

 

 

  • see that we didn’t use the “AS” statement, it is possible to do so as well.

 

Examples using Aliases for tables:

 

  • in this case the alias will represent the entire table.

 

Aliases is more useful when

 

  • There are more than one tables involved in a query.
  • Functions are used in the query.
  • The column names are big or not readable.
  • More than one columns are combined together.

 

Example 1 :

 

  • we can see here the “a”(letter) and a “.”(dot) followed by the column name.This way “a” will represent out Test table .

 

  • the association of the Test table with “a” is done at the end of our query .Note: we can give any character values to our identifier “a”. it can be T , s, zx, etc We will use more of alias when we will get to join and union. For now try to understand the concepts of alias and practice using simple examples like above.

SQL wildcards can substitute for one or more characters when searching for data in a database. SQL wildcards must be used with the SQL LIKE operator.

Sintax :

Let’s create the table we will use for our examples:

Script:

Check the table to see the data and the structure.

Very good

Example:

-we will use the wildcard operator in front.

-we see that the query brings all the names that have the string ‘ve’ at the end of the word.

Example:

-we will use the wildcard operator at the end.

-we see that the query brings all the names that have the string ‘MI’ at the Front of the word.

Example :

-we will use the wildcard operator at the front and end.

-we see that the query brings all the names that have the string ‘k’ inside the word.

    The order by function is used to help us order our results, this could be in ascending order, in descending order, or could be based on either numerical value or text value.

In such cases, we can use the ORDER BY keyword to achieve our goal.

Let’s create the table we will use for our examples:

Script:

Check the table to see the data and the structure.

Very good

Ex 1 :

-we will order our result by alphabetical order.

-to do so we need to apply our ORDER BY function to a field that holds string values.

-we see that the id changed their order and the order manner is dictated by Name column, which is in alphabetical order now (from a to z)

We can also order them from z to a by using the DESC (descending) function at the end of our query.

We can observe that the order is descending now (from z-a).

Note : by default the order by function will sort the values in ascending way, still we have a ASC function we can use.

Example of ASC function

-we can see that the result is the same as above when not using the ASC function.

Still the ASC is often used .

Note : all of the above rules apply for numeric and other values as well.

 

The comparison operators in MySQL will test if two expressions are the same.

Comparison operators can be used on all expressions except expressions of the text, ntext, or image data types.

Table of comparison operators

Comparison Operator Value
= (Equals) Equal to
> (Greater Than) Greater than
< (Less Than) Less than
>= (Greater Than or Equal To) Greater than or equal to
<= (Less Than or Equal To) Less than or equal to
<> (Not Equal To) Not equal to
!= (Not Equal To) Not equal to (not ISO standard)
!< (Not Less Than) Not less than (not ISO standard)
!> (Not Greater Than) Not greater than (not ISO standard)

The BETWEEN operator allows us to select values within a range.

Syntax :

This will select all rows whose column has a value between ‘value1‘ and ‘value2‘.

Let’s create the table we will use for our examples:

Script:

Check the table to see the data and the structure.

Very good

Example 1:

  • say you want to get all the names that have ID values between 1 and 3.

Note: depending on the database you use the names that carry the values used in the condition may be listed or not, In our case they appear.

-this is when we use a numeric value.

Example 2 :

-now we will use string values:

  • see that we have 5 names listed and 6 names in our table

How does the BETWEEN operator works with strings ?

-well he will chose all the names that are between alphabetically.

  • so we see that Eve=E and Mike=M all names that are found in a,b,c,e…—… m…z will be selected.

-for use to understand more this concept we will create table that will have the alphabet letters, not all of them but in the same alphabetical order.

Here is the script

Ok now , after creating the table let’s use the between operator.

Example :

-we see in this query brings all the letters from a-b;

Example :

  • now in this case thou we do not have all the letter that might be between a-x the query will bring all the letter that GO between the declared values , in our case a-x;

We can also use date as values in our between operators.

The IN operator allows you to specify multiple values in a WHERE clause.

Sql IN Syntax:

Let’s create the table we will use

Script:

Check the table to see the data and the structure.

Very good

Now let us use the IN operator.

Example :

-let’s see all the names that have id equal to 1,2,3.

  • is pretty simple, you just put your option in the parentheses as the condition and here you go.

Note:

  • is not necessary that all the values that are in the IN operator to be true so that the query would work. We can throw values that don’t exist in our table but we won’t get any results.

Example :

  • we see that the query only shows two names that are related to first two values declared in the IN operator.

Example :

-we can write this query like this as well and it has the same action

The AND & OR & NOT operators(logical operators) are used to filter records based on more than one condition.

The AND operator displays a record if both the first condition and the second condition is true.

The OR operator displays a record if either the first condition or the second condition is true.

The NOT operator displays the records that are false to the specified value.

 

In the previous tutorials, we have learn that the WHERE keyword can be used to conditionally select data from a table.This are simple conditions.

With And & OR & NOT we will learn how to make compound conditions. Compound conditions are simple conditions combined connected using And & OR & NOT.

We can use as many simple conditions as we like into out query.

Sintax for a compound condition is :

Let’s create the table we will use

Script:

Check the table to see the data and the structure.

Very good

The AND condition

If you want to select rows that must satisfy all the given conditions, you can use the logical operator, AND.

Example

-we want to see the persons that have the id =1 and name= Eve.

– in this example the AND condition must be true for both cases (for both = conditions), if one of the condition is false then no result will be shownExample of wrong or false = condition.

Example 2

-an example with more than 2 AND conditions

-we can add as many conditions as we want .

The OR condition

If you want to select rows that satisfy at least one of the given conditions, you can use the logical operator, OR.

 

  • -when we use OR condition is enough that one of our condition to be true so that all of them to be true.
  • -let’s take the example 2 (where the AND condition returned no lines), but this time we will replace the AND condition with OR and see what happens:

 

– we can see that returned a row; why is that ? because of the OR condition.

AND & OR conditions are very important and they of great use in everyday work with SQL language ,for now we will stop here but in the future we will give more attention to our AND & OR conditions .

The NOT condition

If you want to find rows that do not satisfy a condition, you can use the logical operator, NOT. NOT results in the reverse of a condition. That is, if a condition is satisfied, then the row is not returned.

Using our test table we will want to find the name of the persons who are not working in the AD department:


-see that the result of the querys are the same, it will show all the users that are not in the AD department.

Returns the smallest value of the selected column.

Sql Syntax

Let’s create the table we will use

Script:

Check the table to see the data and the structure.

Very good

Example:

-we will select the smallest salary we have in our table.

-we can also use conditions with min() function.

Returns the largest value of the selected column.

Sql Syntax

Let’s create the table we will use

Script:

Check the table to see the data and the structure.

Very good

Example:

-we will select the bigger salary we have in our table .

-we can also use conditions with max() function.

The AVG function is a mathematical function that will calculate the average of some values that we indicate.

Sql Syntax:

-Let’s create the table we will use

Script:

Check the table to see the data and the structure.

Very good

Example 1:

-will try to find the average salary of all the persons on out table.

Example 2:

-or see the average salary of all the persons that work in a specific department.

Example 3:

-or a more complex example is if we want to see the names of the persons that earn more than the average salary .

-you can see here that we have used the result of this query (select avg(salary) from test)which is a “subquery” as a condition into our (select name from test).

In a table, some of the columns may contain duplicate values. This is not a problem, however, sometimes you will want to list only the different (distinct) values in a table.

The DISTINCT statement can be used to return only distinct (different/that don’trepeat) values.

Syntax :

Let’s create the table we will use

Script:

Check the table to see the data and the structure

Very good

Distinct() examples:

Example 1:

-let’s see how many distinct ID’s values we have:

Example 2:

-now let’s see how many distinct DE (departments) we have in our test table;

-so we can see that thaw we have 6 rows in our table the distinct() function only shows the distinct values of DE column.

We can use conditions and other functions in the same statement with distinct() function.

For more on this function in future tutorials.

The “count()”function allows us to count the number of row in a certain table or to count some values.

Syntax:

-before we do this create the table and add data to it using the following sql sintax:

Script:

Check the table to see the data and the structure.

Very good

Count() Examples:

Example 1:

-the count() function will count all the rows that the condition/clause will point out to , in our case all of the table rows as our * sign suggests.

-we can use condition/clause to be more specific in our count().

Example 2:

-this example will count all the persons that work in the IT department. In our case 2 of then do so .

Example 3:

This example will count all the persons that have salaries bigger then 2500 .

SQL aggregate functions return a single value, calculated from values in a column.

Useful aggregate functions:

  • AVG() – Returns the average value
  • COUNT() – Returns the number of rows
  • FIRST() – Returns the first value
  • LAST() – Returns the last value
  • MAX() – Returns the largest value
  • MIN() – Returns the smallest value
  • SUM() – Returns the sum
  • SQL Scalar functions

SQL scalar functions return a single value, based on the input value.

Useful scalar functions:

  • UCASE() – Converts a field to upper case
  • LCASE() – Converts a field to lower case
  • MID() – Extract characters from a text field
  • LEN() – Returns the length of a text field
  • ROUND() – Rounds a numeric field to the number of decimals specified
  • NOW() – Returns the current system date and time
  • FORMAT() – Formats how a field is to be displayed

Databases have many SQL Aggregate Functions that are built-in and they are used on performing calculation on data.

The SQL Aggregate Functions return a single value that was calculated using data from table columns.

We will demonstrate this functions in the next tutorials

Constraints are a way to limit the type of data that can go into a table and can be specified when a table iscreated (with the CREATE TABLE statement)or after the table is created (with the ALTER TABLE tatement).

We will focus on the following constraints:

  • NOT NULL
  • UNIQUE
  • PRIMARY KEY
  • FOREIGN KEY
  • CHECK
  • DEFAULT

We will explain and demonstrate the constraint listed above.

NOT NULL

Ensures that a column cannot have NULL value.

Example :

-when creating a table we can specify that a column cannot receive null values.

How do we do that?

-we have specified that NAME column can not receive null values

NULL values

What are null values ?

NULL means that data does not exist. NULL does not equal to 0 or an empty string.Both 0 and emptystring represents a value, while NULL has no value.

-aggregate functions such as SUM, COUNT, AVG, MAX, and MIN exclude NULL values.

UNIQUE

-the unique constraint ensures that there are only distinct values on the same column(no values are repeated).

Example :

-when creating a table we can specify that the column email will receive only oncea distinct values andthat values cannot be inserted more than once in the samecolumn .

How do we do that?

PRIMARY KEY

What are the primary keys ?

The PRIMARY KEY constraint uniquely identifies each record in a database table.

Primary keys must contain unique values.

A primary key column cannot contain NULL values.

Each table should have a primary key, and each table can have only ONE primary key.

-one primary key can contain more than one column

Example :

-we see that we give a name to the primary key and then tell which columns will be included.

-so now the primary key is made out of two column.

  • Primary keys can be specified either when the table is created or after using the alter table command.

Example :

-example of primary key creation after the table is created

FOREIGN KEY

The a foreign key is a field (or fields) that points to the primary key of another table.

The purpose of the foreign key is to ensure referential integrity of the data. In other words,only values that are supposed to appear in the database are permitted.

Database Constraint

-as we can see in the image the Table 1 has a primary key (Column 1) that will be a foreign key in Table 2 (Column 2)

-the Column 2 from Table 2 will be a reference of Column 1 from Table 1.

The Syntax to create a foreign key is :

-will use the image as our example

So we have a primary key created now.

Let’s create our Table 2 that will hold a Foreign Key referencing out Column 1 from Table 1.

-alright this is a bit complex for new comers I know, as it was for me as well at the beginning.

So let’s do more examples:

-I will show the General syntax and explain it :

Ok , now we will explain every part of it :

  • Foreign key Holder Column” it will be the column that will reference the “Column of Primary key” found in Table_Name_of_Primary_Key.

Note: – the data type of the Foreign Key Column must be the same as the Primary Key Column data type.

Example :

Database constraint data type

-the Column 2 (foreign key) from Table 2 must hold the same data type as Column1(primary key) from Table 1.

The foreign key can be added to the table as well after the table was created using the alter table :

-we will go more into Foreign Key Details in future tutorials.

CHECK

The CHECK constraint is used to limit the value range that can be placed in a column. CHECK constraint ensures that all values in a column satisfy certain conditions

If you define a CHECK constraint on a table it can limit the values in certain columnsbased on values in other columns in the row.

CHECK constraint is used to ensure data quality.

Create table example with column that hold CHECK constraints

Example :

Assume we have a site that only receives visitors that have more than 18 years.

And we need to create a table to hold them, but we want to make sure that only people with the age more than 18 can register.

-so for that we will create a CHECK constraint on the column that will receive the AGE values :

Great.

  • and now let us try to see the check constraint at work.

  • we see that we will revive an constraint violation error.

-ok now insert a value biggest then 18(which is the value that the CHECK constraint holds).

-we see that the insert statement worked.

Example :

-we can also use CHECK with a list of values

-so using the same criteria the values that we will insert into column CITY mustbe one of the values listed in the DEFAULT values .

DEFAULT

DEFAULT constraint provides a default value to a column when the INSERT INTO statement does not provide a specific value.

Example :

Even though we didn’t specify a value for the status column there will be a default values that will be inserted on the column every time we insert a row.

The default value will be added to all new records, if no other value is specified

To create a DEFAULT constraint after the table was created use:

Once we have created a table in our database we can use alter statement to modify it.

ALTER TABLE statement is used to add, delete, or modify columns in an existing table.

Syntax :

ALTER TABLE commands we can perform

  • 1-ALTER TABLE Add Column
  • 2-ALTER TABLE Drop Column
  • 3-ALTER TABLE Modify Column
  • 4-ALTER TABLE Rename Column
  • 5-ALTER TABLE Add Index
  • 6-ALTER TABLE Drop Index
  • 7-ALTER TABLE Add Constraint
  • 8-ALTER TABLE Drop Constraint

So let’s use our old TEST table script to show some examples of alter table :

Script:

Check the table to see the data and the structure.

Very good

1 -ALTER TABLE Add Column

Syntax:

  • this is used when we want to add a column to our table .Let’s add a column called AGE to our TEST table .

Example:

-so the AGE column should be int .

-and there you go , I have just added a column to our TEST table .

Now check the table :

-it has no values we can see that the column was added .

2- ALTER TABLE Drop Column

  • using the same table now we will drop the column we have just added (AGE column).

Sintax:

Example:

Nice.Check the table

Very good

See the column AGE is no longer in our table .

3- ALTER TABLE Modify Column

-this is used when we want to change the data type of a column

Sintax:

-so for this example will try alter the column DE from char(20) to char(10).

Example:

-firts we will see the table description do confirm that the column is char(20).

  • we can see that the column “de” is a char od 20 .

Now let’s change the type or the size of the column, make it char of 30 :

Ok, now check the table structure and see the change that we have made

4-ALTER TABLE Rename Column

  • this command will let use change the name of a column

Sintax:

Note: if you already have data in the table make sure the data types will apply to the data the column has in.

-also the constraints must be applied to the column.

Example:

Now check the table structure:

5-ALTER TABLE Add/Drop Index

-with the Add index statement one can add an index to the table.

Syntax:

Example:

-we will add an index to our table test.

-check the table if we any indexes created .

-we see that the column “id” has no index on it, so we will create one

-check our new index:

To get more info about your index use this command

Sintax:

-this command will bring data about all the indexes found on the table.

Syntax to drop an index:

Tables are the container for all the data that relational database guards and they are the basic structure of the databases.

Tables are divided in Column and Rows. Each row will represent a piece of data and it will be referenced by the column(header)

tab1
Example :

-we will create a basic table that will store a name and an email.

-so here it is we have created a table called TEST.

This is how we create a table using SQL language , in the next tutorial we will see how we can insert and store data into a table

Transaction Control Language statements are used to revoke transaction or to commit the data(they are basically what they mean and that is they control the transactions).

What are the statements used with TCL

  • 1-Rollback(is used for revoking the transactions until last commit).
  • 2-Commit(is used to commit the transaction to the database, ! very important once the transaction is committed we cannot “rollback it ” and once we rollback we cannot commit).
  • 3-Savepoint (identify a point in a transaction to which you can later roll back).
  • 4-Set transaction (Change transaction options like isolation level and what rollback segment to use).

We will look into TCL later on after we go thru our basic SQL knowledge;

We also have :

  • -Session Control Statements.
  • -System Control Statement.
  • -Embedded SQL Statements.

But this are more advanced topics which we will discuss in the advanced tutorials .

Data Query Language is used to extract data from the database. It doesn’t modify any data in the database. It describes only one query: SELECT.

The DQL statements are the most used statements in SQL language.

DQL statements

  • select

The select statement is the only DQL statement and is used to get data from existing tables

Examples of select statement use:

  • Select all the data from the table

  • Select only some data from the table

The select command is very important in SQL

 

SQL language commands/statements and are categorized into following five groups:

  • DQL: Data Query Language(SELECT)
  • DDL: Data Definition Language(CREATE, DROP, TRUNCATE, ALTER)
  • DML: Data Manipulation Language(DELETE, INSERT, UPDATE)
  • DCL: Data Control Language(GRANT, REVOKE)
  • TCL: Transaction Control Language(COMMIT,ROLLBACK, SAVEPOINT,SET TRANSACTION)

In the next tutorials we will go into more details about them.

This is a list of general SQL data types that may not be supported by all relational databases. I the future we will discuss more about all the data types. For now we will use the ones listed here.

Data type Syntax Description
Integer Integer
Smallint Smallint
Numeric Numeric(p,s) – p is the precision values .-s is the scale values.

For example numeric (6,2) is a number that has 4 digits before

the decimal and 2 digits after decimal(9999,99).

– Numeric (6,3) would be a number that that has 3 digits before

decimal and 3 after decimal (999,999).

Decimal Decimal(p,s) – p is the precision values .-s is the scale values.
Real Real -single precision floating number
Character Char(x) -where x will be the number of character allowed to store
Character Varying Varchar(x) -where x will be the number of character allowed to store
Float Float(p) – p is the precision values .
Bit Bit(x) -where x will be the number of bits allowed to store
Date Date -stores the month , year and day values
Time Time -stores hour , minute and second values
Timestamp Timestamp -stores year , month ,day , hour, min, and seconds

 

 

a

The UPDATE statement is used to update existing records in a table.

General syntax:

In this part of the tutorial we will use the table Test that has the following structure and data :

-we can see that Mike has no email address and Paul has no id and email address. -so for us to insert an email address that will be on the same row with Mike we will use the “update” command with the “where” clause.

-before we do this create the table and add data to it using the following sql sintax:

Script:(copy and paste this in your mysql terminal)

Now see the result :

So now let’s create the update statement that will add an email address to Mike

  • We can do this using as ‘where’ reference our ID column

Example:

Check the table

We can see that the table added an email address where ID column is equal to 3.

  • Now let us add an id and an email address to the row where Name is Paul:

Example:

Great, now check the table.

We can see that Paul got his ID and his EMAIL field in.

Note:

The “set” statement is the one responsible for pointing which columns will be updated and with what values they will receive and the “where” statement will point which is the row/rows will be updated.

Very important

If you omit the “where” clause, all records on that column will be updated!So always use a where clause and double check you update statements before running them.

Example of update statement with no “where ” clause:

And let’s check the changes made by this statement.

We can see the when we used the “update” statement with no “where” clause all the values of the column ID and EMAIL will receive the values stated in the update statement. Se be aware of updates with no condition clauses they can be dangerous.

There’s lot’s of update condition clauses types, but for now we will stick with this only. More advanced updates types in the future tutorials.

 

In this tutorial we will demonstrate the use of delete statement. The DELETE statement is used to delete records in a table.

General delete syntax

Before we do this create the table and add data to it using the following sql sintax:

Script:

This will be the initial table

Very well.

Now let’s start using delete statement on our Test table :

Example:

Check the table data now

We can see that the row that had the ID=1 is no longer in our table

We can also use any other column with our “where” statement.

 

Delete all rows from a table

Example:

All records will be deleted.

Check the table

No lines will show up .

Will produce the same result as above.

Very Important:
You cannot recover the data after a “delete from *” statement. If the delete command was committed all the records in the table will be deleted. We will talk about commit in future tutorials.
In some databases this is possible using (flashback, backups, etc), but this is an advanced topic which we will discuss in future tutorials. For now remember to always use where clause with you delete statements unless you want all the data to be deleted.
There are various types of conditions that we can combine in our delete statements which we will abort in future tutorials.

 

The this tutorial we will talk about the insert statement which is  statement is used to insert new records in a table.

We will use the Test table which has the following structure:

Insert general syntax :

In this case we will need to provide values for all the columns of the table.

Example:

As you can see there is no specification of the fields we want to insert data into, so we need to provide values for all the columns of the table.

In this case we chose witch columns we want to insert data into.

Example:

Notice we only provide values for columns that are provided in the statement.

Example:

As well we can insert only one value; like said in the upper example :we need to provide values for columns that are provided in the statement.

Wrong statements :

Example:

Reason : the table has 3 columns and we only provided values for 2 of them.

Correct statement:

Example:

Reason: we provided more values then columns;

Correct statement:

More complex insert statement we will learn later on our tutorials.

 

The SELECT statement is used to look for data, or to consult our tables.

The syntax for SELECT statement is:

Example 1

Tip: The asterisk (*) is a quick way of selecting all columns!

Or you can restrict the search by calling the rows you chose

Example 2

  • SELECT using condition/s

Sintax:

Tip: The (–) is a quick way of commenting a line !

Example:

Also is important to mention that when the values that will be used in the conditions a string we must place it in single quotes.

Integer values do not require single quotes.

Example:

Conditional selections used in the where clause:

= Equal

It will bring all the names that have the id equal to 1.

> Greater than

It will bring all the names that have the id bigger then 1.

< Less than

It will bring all the names that have the id smaller then 2.

>= Greater than or equal

<= Less than or equal

<> Not equal to

LIKE

The LIKE pattern matching operator can also be used in the conditional selection of the where clause. Like is a very powerful operator that allows you to select only rows that are “like” what you specify. The percent sign “%” can be used as a wild card to match any possible character that might appear before or after the characters specified.

Notice that the select returned all the names that end up in ‘ke’ , knowing that ‘%’ sign replaced the rest of the string.

We can use the ‘%’ wildcard as well at the end :

  • Example:

At the beginning and at the end.

Example:

See that the select returned all the names that have letter ‘i’ inside.

Remember that we can use more then one character between the wildcard operators.

Example:

We will learn more about the power of select after we go thrum the basics of Sql first.

A database contain one or more tables that contain data , they are uniquely identified by their name(no two tables can have the same name in one database).

Tables are organized in rows and columns.

Columns contain the column name, data type, and any other attributes for the column. Rows contain the records or data for the columns

Example

Table Test

Column 1 Column 2 Column 3
Data Row 1 Data Row 2 Data Row 3
Data Row 1 Data Row 2 Data Row 3
Data Row 1 Data Row 2 Data Row 3

Now that we have seen what a table is let’s learn how to create a table in the next tutorial.

  • 1- Install mysql-server mysql-client

  • 2- Start mysql-server

  • 3- Setup mysql-server to start at boot time

  • 4- Setup MySQL root user password using /usr/bin/mysql_secure_installation script(i recomend this if you plan to put it in production).

  • 5- Now to login

What is a Relational Database or RDBMS(Relational database management systems)?

  • -is an organized collection of data . And the data is organized depending on the ue of the RDBMS.
  • -is a set of tables containing data fitted into predefined categories.

Each table (which is sometimes called a relation) contains one or more data categories in columns.

Examples

Oracle Database , Sql Server Database (Microsoft), MySql, PostgreSql this are among the leading relational databases.

Here is the MySQL Database Architecture:

MySQL_diagram

SQL is a programming language designed for managing data in relational database management systems(RDBMS).
Originally based upon relational algebra and tuple relational calculus, its scope includes data insert, query, update and delete,schema creation and modification, and data access control. SQL was initially developed at IBM by Donald D. Chamberlin and Raymond F. Boyce in the early 1970s.
Some common relational database management systems that use SQL are: Oracle, Sybase, Microsoft SQL Server, Access, Ingres, etc.Although most database systems use SQL, most of them also have their own additional proprietary extensions that are usually only used on their system. However, the standard SQL commands such as “Select”,”Insert”,”Update”, “Delete”, “Create”, and “Drop” can be used to accomplish almost everything that one needs to do with a database.

The SQL language is subdivided into several language elements, including:

  • 1-Clauses: which are constituent components of statements and queries.(In some cases, these are optional) Expressions: which can produce either scalar values or tables consisting of columns and rows of data.
  • 2-Predicates: which specify conditions that can be evaluated to SQL three-valued logic (3VL) or Boolean(true/false/unknown)truth values and which are used to limit the effects of statements and queries,or to change program flow.
  • 3-Queries: which retrieve the data based on specific criteria. This is the most important element of SQL.
  • 4-Statements: which may have a persistent effect on schemata and data, or which may control transactions, program flow,connections, sessions, or diagnostics.

SQL statements also include the semicolon (“;“) statement terminator. Though not required on every platform, it is defined as a standard part of the SQL grammar.
Insignificant white space is generally ignored in SQL statements and queries, making it easier to format SQL code for readability. So the Conclusion is that SQL lets you access and manipulate databases.