MySQL
MySQL key and its main features:
The role of database in website development is different. The database is actually like a drugstore where different medicines are arranged in different directions, so that it is convenient to find them later, we can organize the information in the database as well. Let's talk about Facebook. Here the information of hundreds of crores of people including name, photo, date of birth, preferences etc. are stored in the database of Facebook, now if someone clicks on a name or searches a name or email etc. in the search box, then Facebook goes to the database inside it and finds that name or email. Extracts and presents its information to us. Now these tasks have to be done by creating the database, so that the information from here can be displayed on the web page. The software required for this is called RDBMS (Relational Database Management Software). Here we will see how to create database with MySQL database software. It is widely used in creating websites. Also there are ORACLE, SQL Server, MS ACCESS etc. If you can learn MySQL well, it is much easier to learn others. Eg- All commands of MySQL and ORACLE are almost same, 20-25% difference.
SQL = Standard Query Language (pronounced sequel or ess-cuell) is a powerful language for creating and querying databases. Any database system data can be manipulated and accessed with this language. For example, MySQL, SQL server, Oracle etc. It is 4th generation language. However, it is not a full-fledged programming language, as it does not support data structures such as loop, branch,
SQL statement:
Most of the database operations are done by SQL statements. For example, the following SQL statement will select all the records of the Friends table. SELECT * FROM friends
SQL is not case sensitive, ie select from friends and SELECT FROM friends both will have the same function and output.
Types of SQL and its features:
SQL can be divided into two--
DDL-Data Definition Language : It is used to create, delete database etc. index(keys) are defined, joins are made between two tables, constraints are fixed between tables, etc.
Important DDL statements are:
CREATE DATABASE - New database is created
ALTER DATABASE - The database is modified
CREATE TABLE - New table is created
ALTER TABLE - The table is modified
CREATE INDEX - INDEX is created
DROP INDEX - INDEX is deleted
Query and update are done with the DML part of SQL.
SELECT – retrieves data from the database
UPDATE - Updates the data
DELETE — Deletes data from the database
INSERT INTO - Inserts into the database
MySQL statement:
Before starting work with MySQL, a database must be created. A database is created with the CREATE DATABASE statement.
SQL CREATE DATABASE Syntax: CREATE DATABASE database-name Start MySQL in command mode and run the following statement. Here we will create a database called my-bd.
CREATE DATABASE my-db;
Selecting database: A server can have multiple databases, if you want to select one and use it, use the following statement.
USE databse name
Delete Database: To delete a redundant database use the following statement
DROP DATABASE database-name (Replace database name with the name of the database to be deleted.)
Once a database is deleted, all the data including its tables are gone and it is not possible to undo, so it must be done carefully, so as not to delete the necessary ones by mistake.
UPDATE Statement: Existing data in a table can be changed through the update command. The update command neither adds new records to a table nor deletes any records from the table. It can only modify existing records in that table. The update command is usually used to make changes to a column in a table. However, changes can be made to multiple columns at once. A single statement can change a single row of a table or many rows at once. The simplest use of the UPDATE statement is to modify a single column.
GROUP BY & ORDER BY, HAVING Clause, LIKE Operator and SQL JOIN :
Grouping data is the process of arranging data logically based on duplicate values in a column. For example, suppose a database contains information about all employees. Those employees may live in different cities. More than one person can live in the same city. Data grouping is the arrangement of displaying the list of employees living in the same city together. A query can also be run for a specific city and a report can be generated based on that query.
Example : Let's say, need to see total employee data by city. For this, use the aggregate function count on the id column and use the GROUP BY clause to show the output by city. Through the group by clause. You will be asked to group by the City column.
Group by--
The group by clause is used with the select statement to display similar data as a group. The group by clause is used after the where clause. But it will be used before the order by clause:
Group by syntax:
SELECT column_name (s)
FROM table_name
Group by column_name (s)
ORDER BY column_name (s) ASC| DESC
Order By:
The where clause is used with the SELECT statement to express similar data in hierarchical or hierarchical order of values. The group by clause is used after the where clause. But it is used before the ORDER BY clause. With the ORDER BY command, ASC is used for hierarchy of values or DSC for hierarchy-descendence. Usually default in any database management system.
Order by syntax :
SELECT column_name (s)
FROM table_name
Group by column_name (s)
ORDER BY column_name (s) ASC|DESC
DSC will be used with the ORDER BY command on previously created tables. Below is the code for __
SELECT city, COUNT(*) FROM employee
WHERE description IS NOT NULL
GROUP BY city,
Order by dsc;
HAVING clause:
Using the having clause with the group by clause in a select statement tells us that some groups will be shown in the output. Having does the same thing with group by that where does with select statements. In other words, the where clause imposes conditions on certain columns and the having clause imposes conditions on groups created by the group by clause.
Position of having clause in any query will be as below--
Select column_name (s)
FROM table_name
Where
Group by
Having
Order by
Using the command of having clause in the previously created table is shown--
SELECT FROM employee
-> GROUP BY first_name
-> HAVING first_name LIKE J%';
Like operator:
The Like operator is an important operator for simply using conditions in a query. Like operator is always used in where/having clause.
The command usage of the Like operator is shown in the previously created table—
SELECT FROM employee
-> WHERE (employee.first_name LIKE 'A%');
SQL JOIN:
Learned how to retrieve data from a table using SELECT statement. But in actual database programming, we don't just look at data from one table in a database because a table is divided into many smaller tables. So the structure of SQL JOIN with SELECT statement is as follows:
1.SELECT column_list
2. FROM table_1
3.[INNER |LEFT |RIGHT] table_2 ON conditions_2
4.[INNER |LEFT |RIGHT] table_3 ON conditions_3
5........
6. WHERE conditions
The JOIN keyword is used with SQL statements to query data from two or more tables based on the relationship between certain columns in those tables. Tables are related to each other in the database by keys (called foreign keys).
A primary key is a column (or set of columns) whose value is unique for each row.
Let's see some types of JOIN before the example: JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN
Comments
Post a Comment