Introduction
Why do we need to use SQL? SQL provides a number of security and performance capabilities. For example, transactions ensure that several operations are performed as a whole, so if any operation fails, the entire transaction will be pumped out. In addition, indexes can significantly speed up query execution by allowing the database system to use an access path other than a linear search for all records in the table.
Finally, SQL allows users with almost no knowledge of programming languages to perform complex operations on data. For example, it is very easy for a person who knows nothing about programming languages to write a SELECT statement that extracts only surnames and first names from a table with information about employees. However, if he tried to do it in a procedural language like C or Java, it would be much more difficult, since the programmer would have to use loops and other constructs to get the data in the desired format.
For that reason we will consider common command for improve your navigate skills in the DBMS system!
How to see users?
First of all we need login into the system via localhost or remote type connection, for the second way you need to replace localhost to the IP-address of needed MySQL server and indicate name of user for your case! For example, we want to connect via local:
mysql -u root -h localhost -p
Highlight! Don't forget to change username and port optionally!
mysql -u jhon -h 76.56.32.1 -pAlright we are into the system, now we can lookup users in our database, system accounts and settings also saved into the database then access we can get through commonly SQL-query:
SELECT user,host FROM mysql.user;
In the picture you can see table which formed from selected column user and host, which we looked for in the user table! Operators SELECT indicate column where we will search information and FROM in which table.
How to create user?
But if we don't have any needed user or want to create new, then we need to type command below:
CREATE USER 'kk'@'localhost'
IDENTIFIED by 'password';Highlight! Using operators for command don't sensitive to the case of letter, but name and label of database, table and other object are. In that command you can see creation account with name kk for localhost, by the next row you can see operator IDENTIFIED that provide access control in the DBMS, instead of password insert your value!
Let's check result of our manipulation with user:
SELECT user,host FROM mysql.user;
But also every user need to have rights in the MySQL system user by default don't have them. That very significant point, because of rule in the main concept of security we need to restrict access for another:
GRANT ALL PRIVILEGES
on mysql.user
to 'kk'@'localhost';
In that point we grant privileges for kk user on the table user and give ALL privileges, that significant point which determine list of rights for user. We can grant REVOKE, DROP or CREATE list of needed rights due to type of account.
But if we accidentally gave privileges? That we can fix by the command REVOKE:
REVOKE ALL PRIVILEGES
on mysql.user
from 'kk'@'localhost';
The REVOKE means deprive rights from the user and form of usage almost matches with GRANT, excluding syntax from.
How create database?
For that purpose we will use familiar command:
CREATE DATABASE store;And then you can type for selecting database as default:
USE store
Which syntax mean creation db with name store. Maybe you have noticed after every command sign semicolon. We use them when we need to indicate end of sequences of command. But, some syntax such as USE operator we don't mark by that way. How to check result of our manipulation? Just type command SHOW below:
SHOW DATABASE;And we will see the result!
How to create table?
Now we can create column by the command:
CREATE TABLE clothes (
id INT NOT NULL,name VARCHAR(30) NOT NULL);Where CREATE TABLE immutable operator, clothes it's name of the table, id — first column with integers INT type of data and NOT NULL option, for column name the same situation. And check our result by the command:
SHOW TABLES;Or we can see structure of created table by the next command:
DESCRIBE clothes;
In the picture we can see result of our manipulations!
Conclusion
The understanding and utilizing SQL is crucial for enhancing the security and performance of database management systems. SQL's support for transactions ensures the integrity of operations, while features like indexes contribute to efficient query execution. Moreover, SQL empowers individuals with limited programming knowledge to perform complex data operations effortlessly.
