-
Notifications
You must be signed in to change notification settings - Fork 0
SQL
A database is a structured collection of data that is organized and stored in a way that allows for efficient retrieval and manipulation of that data.
They are used to persist data beyond the lifespan of an application or server, and offer features such as atomicity, consistency, isolation, and durability (ACID) to ensure data integrity.
The ACID properties of a database refer to four key features that ensure data consistency and reliability:
-
Atomicity: Transactions are treated as a single, indivisible unit of work, so if any part of the transaction fails, the entire transaction is rolled back to its original state. -
Consistency: Data is subject to predefined rules and constraints, and any attempted modification must meet these criteria. If not, the transaction is rolled back. -
Isolation: Transactions are isolated from each other, so that concurrent transactions do not interfere with one another. Each transaction operates as if it were the only one executing. -
Durability: Once a transaction is committed, it is guaranteed to persist, even in the face of system failures or power outages.
In addition to the ACID properties, a solid database also provides strong performance and scalability. Since databases deal with input/output (I/O) operations, which can be a bottleneck, their performance can significantly affect the overall performance of an application. A good database is designed to handle large volumes of data and many concurrent requests efficiently, while also being able to scale to handle increasing demand over time. This means that inserting one user in a small collection of data should take about the same time as inserting one user in a very large collection of data, without causing any significant slowdowns or performance issues.
CRUD operations refer to the four basic operations that can be performed on data in a database:
- Create: Adding new data to the database
- Read: Retrieving data from the database
- Update: Modifying existing data in the database
- Delete: Removing data from the database
A reliable and efficient database should allow for all four of these operations to be performed, as they are essential for managing and manipulating data in a persistent and secure manner.
A Database Management System (DBMS) is a software system that is used to manage and control access to a database. It provides an interface for users to interact with the database, allowing them to perform tasks such as inserting, updating, deleting, and querying data. The DBMS is responsible for managing the storage and retrieval of data, ensuring the data is consistent, and enforcing data security and access controls.
There are several types of Database Management Systems (DBMS), including:
-
Relational DBMS (RDBMS): These systems store and manage data in tables that are related to each other through keys or relationships. Examples include MySQL, Oracle, Microsoft SQL Server, and PostgreSQL. -
NoSQL DBMS: These systems store and manage data in non-tabular structures, such as key-value, document, column-family, and graph databases. Examples include MongoDB, Couchbase, Cassandra, and Neo4j. -
Object-oriented DBMS (OODBMS): These systems store and manage data in objects, which can include attributes and methods. Examples include Objectivity/DB and ObjectStore. -
Hierarchical DBMS: These systems store and manage data in a tree- like structure, with parent-child relationships. Examples include IBM's Information Management System (IMS) and Windows Registry. -
Network DBMS: These systems store and manage data in a network- like structure, with records linked by pointers. Examples include Integrated Data Store (IDS) and CA-IDMS. -
Cloud-based DBMS: These systems store and manage data in the cloud, allowing for scalability and flexibility. Examples include Amazon Web Services (AWS), Relational Database Service (RDS), Microsoft Azure SQL Database, and Google Cloud SQL.
SQL (Structured Query Language) is a programming language used to manage and manipulate relational databases. It is commonly used for tasks such as creating, updating, and querying data in databases. SQL is used by many relational database management systems (RDBMS) such as MySQL, Oracle, and Microsoft SQL Server.
MySQL is a free, open-source Relational Database Management System (RDBMS) that uses Structured Query Language (SQL) to manage and store data. It is one of the most popular databases in use today and is commonly used for web applications, data warehousing, and e-commerce. MySQL is known for its scalability, reliability, and ease of use, and it supports a variety of programming languages and platforms. It is developed and maintained by Oracle Corporation and is available for Windows, Linux, and macOS. Some popular applications that use MySQL include WordPress, Drupal, Joomla, and Magento.
- Open a terminal window
- Update the package index and upgrade the system with the command:
sudo apt update
sudo apt upgrade
- Install the MySQL server package with the command:
sudo apt install mysql-server
- After the installation is complete, start the MySQL service and enable it to start automatically on boot with the command:
sudo systemctl start mysql or sudo service mysql start
sudo systemctl enable mysql
- Run the security script to secure the installation by setting the root password and removing insecure default settings:
sudo mysql_secure_installation
Follow the on-screen prompts to answer the questions and set the root password.
MySQL is now installed on your Ubuntu 20.04 system. You can access it by running the mysql command from the terminal.
Upon installation, MySQL creates a root user account which you can use to manage your database. This user has full privileges over the MySQL server, meaning it has complete control over every database, table, user, and so on. Because of this, it’s best to avoid using this account outside of administrative functions. This step outlines how to use the root MySQL user to create a new user account and grant it privileges.
You must invoke mysql with sudo privileges to gain access to the root MySQL user:
sudo mysql
Once you have access to the MySQL prompt, you can create a new user with a CREATE USER statement. These follow this general syntax:
CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';
After creating your new user, you can grant them the appropriate privileges. The general syntax for granting user privileges is as follows:
GRANT PRIVILEGE ON 'database'.'table' TO 'username'@'host';
The PRIVILEGE value in this example syntax defines what actions the user is allowed to perform on the specified database and table.
You can grant multiple privileges to the same user in one command by separating each with a comma.
You can also grant a user privileges globally by entering asterisks (*) in place of the database and table names. In SQL, asterisks are special characters used to represent “all” databases or tables.
GRANT CREATE, ALTER, DROP, INSERT, UPDATE, INDEX, DELETE, SELECT, REFERENCES, RELOAD on *.* TO 'sammy'@'localhost' WITH GRANT OPTION;
Note that this statement also includes WITH GRANT OPTION. This will allow your MySQL user to grant any permissions that it has to other users on the system.
The above command grants a user global privileges to CREATE, ALTER, and DROP databases, tables, and users, as well as the power to INSERT, UPDATE, and DELETE data from any table on the server. It also grants the user the ability to query data with SELECT, create foreign keys with the REFERENCES keyword, and perform FLUSH operations with the RELOAD privilege. However, you should only grant users the permissions they need.
GRANT ALL PRIVILEGES ON *.* TO 'sammy'@'localhost' WITH GRANT OPTION;
The above command grants superuser privileges to the user on all databases and tables.
It’s good practice to run the FLUSH PRIVILEGES command. This will free up any memory that the server cached as a result of the preceding CREATE USER and GRANT statements:
FLUSH PRIVILEGES;
Then you can exit the MySQL client:
exit
In the future, to log in as your new MySQL user, you’d use a command like the following:
mysql -u sammy -p
The -p flag will cause the MySQL client to prompt you for your MySQL user’s password in order to authenticate.
check its status.
systemctl status mysql.service
or
sudo service mysql status
This will show you the current status of the MySQL service, whether it is running or not.
If the MySQL service is not running, you can start it with the following command:
sudo systemctl start mysql
or
sudo service mysql start
If the MySQL service is running, but you want to stop it, you can use the following command:
sudo service mysql stop
For an additional check, you can try connecting to the database using the mysqladmin tool, which is a client that lets you run administrative commands. For example, this command says to connect as a MySQL user named sammy (-u sammy), prompt for a password (-p), and return the version.
sudo mysqladmin -p -u sammy version