SQL= Structured Query Language It is a powerful language for managing relational databases. With this language you can manage and access the data of any database system such as MySQL, SQL Server, Oracle etc. It is a 4th generation language, but it is not a full-fledged programming language because it does not support data structures such as loop, branch.
I will discuss how to manage MySQL databases with SQL.
What can be done with SQL (MySQL)?
- A new database can be created
- New tables can be created
- Query can be done
- Data can be retrieved from the database
- New records can be inserted into the database
- Records can be updated
- Can be deleted etc.
An example of an equation statement
Query says this whole above statement survives in SQL. In a query, the name of the database table, column can be written with backtick (`) but it will work even if it is not given. I could write the above query as below
How to start writing a SQL command
After launching Apache and MySQL from XAMPP Control Panel, open the browser and type in its address bar http://localhost/xampp/ then the result will look like below.
Now you can do everything from creating a MySQL database to the window that came up. After coming to this window, click on phpMyAdmin at the bottom. You can do everything, but the suggestion is that you do this by typing the command as discussed below, it will be useful to write the script in PHP later.
Now to see the window that came up like below – click on the SQL tab here.
After clicking on the SQL tab, a box will appear. Here you have to type all the SQL commands.
Most of the work in the database can be done with the sequel statement. For example, with the following sequel statement, all the records in the friends table will be selected.
SELECT * FROM friends
SQL but not case sensitive i.e. ‘select * from friends’ and ‘SELECT * FROM friends’ will both have the same function and output.
SQL DDL and DML
SQL can be divided into two ways
DDL-Data Definition Language: It is used to create databases, delete, etc. Index (keys) are added, connections are made between two tables, constraints are fixed in the table, etc. Important DDL statements are
- CREATE DATABASE – A new database is created
- ALTER DATABASE- The database is executed
- CREATE TABLE – A new table is created
- ALTER TABLE- The table is edited
- CREATE INDEX- INDEX is created
- DROP INDEX- INDEX is deleted
DML-Data Manipulation Language
- Query and update is 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 information into the database
CREATE DATABASE :
Before starting work on MySQL you need to create a database. CREATE DATABASE Create a database with this statement. Have you started XAMPP? If not, open it and open the browser and type http://localhost/ in the address bar. Now enter phpMyadmin under Tools on the bottom left here and finally click on the SQL tab.
Write here as below (CREATE DATABASE designer) and click on go button.
Now look at the left side, a database called designer is coming.
** You can create a new database directly from the “Databases” (left of the SQL tab) tab without going to the SQL tab. Go to the Databases tab and click on the “Create” button with the desired name of the database in the “Create database” field. Like the picture below
Select the database :
A server can have more than one database.
USE database_name (Replace database_name with the database you want to use.
Delete database :
The following statement to delete an unnecessary database
DROP DATABASE database_name (Replace database_name with the database you want to delete.) Once a database is deleted, all data, including its table, is lost and cannot be undo, so be careful