On this page of Tontuf, we will learn the basics of MySQL.
Let's start with what is MySQL
MySQL is an RDBMS(Relation Database Management System). MySQL is not alone as an RDBMS, there are many other RDBMS present too, like - Oracle, PostgreSQL, Microsoft SQL Server, LibreOffice Base, and much more. These RDBMS helps to create a database consisting of relations and to link one or more relations for efficient querying to store, retrieve and manipulate data on a database.
In the Database management system, there are special kinds of programing language, called Query Language and this Query language can be used to access data from the database. The Structured Query Language(SQL) is one of the most popular query languages. This query language is used in many major and bog relational database systems. Our MySQL(which basics we are learning right now!) also uses SQL(Structured Query Language).
Features of SQL -
- Easy to learn
- Words are not case sensitive
MySQL is an open-source RDBMS software. You can get more updates about MySQL from their official Dev website - Dev.mysql.com.
What we will have here?
- Creating Database
- Insert data into a Database
- Manipulate data
- and, Retrieve data from a Database
Make sure you install MySQL before using its commands. In order to install MySQL, you can visit - Dev.mysql.com/downloads, select the platform on which you wanna use your MySQL commands, and download MySQL accordingly. After you install and set up your MySQL in your platform, you will see "mysql>" in the prompt meaning that your MySQL is ready to use.
Rules of writing MySQL commands -
- SQL commands are not case-sensitive.
- All SQL commands always end with a semicolon(;)
- You can write multiple line commands in MySQL.
Data types in MySQL
- CHAR(n) - Character Data type where n is the length. n could have values between 0-255.
- VARCHAR(n) - Variable Data type where n is length. n could have values between 0-655535.
- INT - Integer Data type. INT value occupies 4 bytes.
- BIGINT - Integer Data type. BIGINT values occupy 8 bytes.
- FLOAT - Number with decimals points Data type FLOAT occupy 4 bytes
- DATE - Date Data type in the format of "YYYY-MM-DD". Supported range is '1000-01-01' to '9999-12-31'.
- And, much more...
Constraints in MySQL - These are certain types of restrictions on the data values that an attribute can have. It is not mandatory to have constraints for each attribute of a data table.
- NOT NULL - Column cannot have NULL values.
- UNIQUE - Columns must have unique values.
- DEFAULT - Column has no values then it has DEFAULT values.
- PRIMARY KEY - Column which can uniquely identify each row or record in the table.
- FOREIGN KEY -
Data Definition Language(DDL) - SQL provides commands for defining the relation schemas, modifying relation, and deleting relations.
Now let's learn about the commands of MySQL to create, add data, edit data and delete data in a database using MySQL commands.
- Creating Database in MySQL -
To create a database, we have to use the "CREATE DATABASE" command.
CREATE DATABASE yourdatabasename;
- Selecting Database in MySQL -
In order to use a database, we need to first select that database. Use the "USE" command to select your database.
USE yourdatabasename;
- Creating a Table in Database -
After creating and selecting your database, we need to create a table inside the database to store the data. To create a table inside the database using the "CREATE TABLE" command.
CREATE DATABASE yourtablename(attribute1 databtype constraint, attribute2 datatype constraint);
Important Points of creating a table in MySQL -
- An attribute name is the name of the column in the table of the database.
- Datatype is the type of data that the attribute will store.
- The constraint is the restrictions, that the attribute will have.
- It's necessary to give datatype in each attribute but not necessary to give constraint.
- View the created table -
In order to view the table which we created in a database, use the "DESCRIBE" or "DESC" command.
DESC yourtablename;
- List of all tables in the selected database -
In order to get the list of all tables in a database, use the "show tables;" command.
SHOW TABLES;
- Editing & Deleting the table in database -
Now that we have created the table, we can also edit/modify, add or delete data using the "ALTER TABLE" command.
Adding Constraint - ALTER TABLE yourtablename ADD constraint(attributename);
Adding new attribute in the existing table - ALTER TABLE yourtablename ADD attributename datatype;
Modifying datatype of the attribute - ALTER TABLE yourtablename MODIFY attributename new-datatype;
Modifying constraint of attribute - ALTER TABLE yourtablename MODIFY attributename datatype NOT NULL;
Adding default value to attribute - ALTER TABLE yourtablename MODIFY attributename datatype DEFAULT value;
Deleting database(deletes database and all the tables inside it) - DROP DATABASE yourdatabasename;
Deleting table(deletes table and all data stored in the table) - DROP TABLE yourtablename;
Deleting an attribute - ALTER TABLE yourtablename DROP attributename;
Deleting a constraint - ALTER TABLE yourtablename DROP constraint;
- Adding data into the table -
Now we learn how to add and populate data into our table which we created above. To add data into our table, use the "INSERT INTO" command.
INSERT INTO yourtablename VALuES(value1, value2, ...);
or
INSERT INTO youtablename (attribute1, attribute2, ...) VALUES(value1, value2, ...)
- SELECT Commands -
To view all the data inside the table - SELECT * FROM yourtablename;
To view data inside an attribute of the table - SELECT attributename FROM yourtablename;
To view data inside 2 attributes of the table - SELECT atrributename1, attributename2 FROM yourtablename;
To view data inside an attribute but with a different attributename in output - SELECT attributename AS new-attributename FROM yourtablename;
To view data without any duplicate values in the attribute - SELECT DISTINCT attributename FROM youtablename;
To view data with specific conditions in attribute - SELECT attributename FROM yourtablename WHERE attribute=(specify the value here);
In the above example, we have used the = operator in the WHERE clause. We can also use other relational operators (<, <=, >, >=, !=) to specify conditions.
To view data with more than one specific condition in the table - SELECT*FROM yourtablename WHERE attributename IN (specify the value here);
To view data with the specific condition in the table - SELECT*FROM yourtablename NOT IN(specify the value here);
Display details whose value name starts with "K" - SELECT*FROM yourtablename WHERE attributename LIKE "K%";
Display details whose value name ends with "k" - SELECT*FROM yourtablename WHERE attributename LIKE "%k";
Display details whose value name contains "kk" - SELECT*FROM yourtablename WHERE attributename LIKE "%kk%";
Display details whose value name contains 'k' as the second character - SELECT*FROM yourtablename WHERE attributename LIKE "_k%";
- ORDER BY Commands -
It is used to display data in arranged order with a specified column. By default its display data in ascending order
Ascending Order - SELECT*FROM yourtablename ORDER BY attributename;
Descending Order - SELECT*FROM yourtablename ORDER BY attrubutename DESC;
- NULL & NOT NULL Values -
NULL is a special value supported by SQL. NULL values are given in an attribute when the value of that attribute is missing or unknown.
Display details of all data that have a NULL value in the table - SELECT*FROM yourtablename WHERE attributename IS NULL;
Display details of all data which don't have a NULL value in the table - SELECT*FROM yourtablename WHERE attributename IS NOT NULL;
- Deleting Data from the table -
In order to delete any data from the table, use "DELETE" commands.
DELETE FROM yourtablename WHERE specifyyourvaluehere;
You can use the "WHERE" clause to specify the condition and values.
- Updating the data of the table -
In order to update the value from the table, use the "UPDATE" command. This command is used to modify the existing value or data in the table.
UPDATE yourtablename SET attributename1 = value1, attrubutename2 - value2, ...;
