SQL is the standard for managing structured data in industry. There is a debate as to whether SQL or NoSQL databases are better but it is wise to be able to work with both. SQL serves as:
- a Data Definition Language (DDL) - used to define and manage the structure of database objects
- a Data Manipulation Language (DML) - used to manage the data living within database objects
Creating a database
We can have multiple databases in SQL. You can think about each database as corresponding to a different app. For example, we can have $5$ databases if we are working with $5$ separate web apps. The value in this is that we can isolate the information to be managed by an app to the app’s own database.
CREATE DATABASE mydatabase;
SQL itself is not case sensitive so we can type the keywords (CREATE, DATABASE, etc.) in common letters but for readability purposes we keep these keywords in all caps.
Creating a user
There is value in having multiple users in SQL. We can thus manage who can/cannot access particular databases. We do not simply use a database, we identify as a user that has permission to use that database and perform operations as that user.
CREATE USER 'supercooluserdude'@'localhost' IDENTIFIED BY '@@123456789BerryGood';
Granting privileges
Being recognized as a user is not enough for us to use a database. Our user must also be granted privileges to do certain operations. Of course, by default it is assumed we will grant all privileges but there will be circumstances where only having limited privileges is desirable.
GRANT ALL PRIVILEGES ON mydatabase.* TO 'supercooluserdude'@'localhost';
Run the following to apply the changes and verify that the user has the right permissions:
FLUSH PRIVILEGES;
SHOW GRANTS FOR 'supercooluserdude'@'localhost';
Reflection
We’re all set to do amazing operations as our new supercooluserdude user. We looked at:
- Creating a database
- Creating a user to use that database
- Granting privileges to the user so they can perform operations on our new database