MySQL Tutorial Part 1
MYSQL:
- MySQL is a very popular open-source relational database management system.
- It is very easy to use.
- MySQL is one of the most straightforward database technology to learn and use.
- To add, access, and process data stored in a database.
- SQL stands for Structured Query Language
- SQL is used to communicate with the database.
- SQL lets you access and manipulate databases
- SQL keywords are NOT case sensitive: select is the same as SELECT
SQL VS MYSQL:
| SQL | MYSQL |
|---|---|
| SQL is a query programming language that manages RDBMS. | MySQL is a relational database management system that uses SQL. |
| SQL is primarily used to query and operate database systems. | MySQL allows you to handle, store, modify and delete data and store data in an organized way. |
- RDBMS stands for Relational Database Management System.
- RDBMS is a program used to maintain a relational database.
- RDBMS is the basis for all modern database systems such as MySQL, Microsoft SQL Server, Oracle, and Microsoft Access.
DATABASE TABLE:
- A table is a collection of related data entries, and it consists of columns and rows.
- A column holds specific information about every record in the table.
- A record (or row) is each individual entry that exists in a table.
RELATIONAL DATABASE:
A relational database defines database relationships in the form of tables. The tables are related to each other - based on data common to each.
IMPORTANT SQL COMMANDS:
• INSERT INTO - insert new data into a database
• UPDATE - update existing data in a database
• DELETE - delete data from a database
• SELECT - extract data from a database• CREATE DATABASE - create a new database
• ALTER DATABASE - modify a database
• CREATE TABLE - create a new table
• ALTER TABLE - modify a table
• DROP TABLE - delete a table
• CREATE INDEX - create an index (search key)
• DROP INDEX - delete an index
INSERT QUERY:
The INSERT INTO statement is used to insert new records in a table.
It is possible to write the INSERT INTO statement in two ways:
- Specify both the column names and the values to be inserted:
Syntax
INSERT INTO table_name (column1, column2, column3) VALUES (value1, value2, value3);
Example:
INSERT INTO Students (rollno, name, department, district, fees) VALUES(3, 'Vetri', 'CSE', 'Thanjavur', 50000); - If you are adding values for all the columns of the table, you do not need to specify the column names in the SQL query. However, make sure the order of the values is in the same order as the columns in the table. Here, the INSERT INTO syntax would be as follows:
Syntax
INSERT INTO table_name VALUES (value1, value2, value3);
Example:
INSERT INTO Students VALUES (4, 'Vishva', 'ECE', 'Trichy', 70000);
UPDATE QUERY:
The UPDATE statement is used to modify the existing records in a table.
Syntax
UPDATE table_name SET column1 = value1, column2 = value2 WHERE condition;
Example:
UPDATE Students SET fees= 40000 WHERE department = 'CSE'
Note: Be careful when updating records in a table! Notice the WHERE clause in the UPDATE statement. The WHERE clause specifies which record(s) that should be updated. If you omit the WHERE clause, all records in the table will be updated!
DELETE QUERY:
The DELETE statement is used to delete existing records in a table.
Syntax
DELETE FROM table_name WHERE condition;
Example:
DELETE FROM Students WHERE district='Thanjavur';
Note: Be careful when deleting records in a table! Notice the WHERE clause in the DELETE statement. The WHERE clause specifies which record(s) should be deleted. If you omit the WHERE clause, all records in the table will be deleted!
SELECT QUERY:
The SELECT statement is used to select data from a database.
Syntax
- Select query to retrieve all columns in the table
SELECT * FROM table_name;
Example:
SELECT * FROM Students; - Select query to retrieve particular columns in the table
SELECT column1, column2 FROM table_name;
Example:
SELECT name, department FROM Students;
SELECT DISTINCT QUERY:
The SELECT DISTINCT statement is used to return only distinct (different) values.
It removes the duplicate values and returns the different values.
Syntax
- To get the particular column data without the duplicate values.
SELECT DISTINCT column1, column2 FROM table_name;
Example:
SELECT DISTINCT district FROM Students; - To get the count of the data based on the particular column without the duplicate values.
SELECT COUNT(DISTINCT column1) FROM table_name;
Example:
SELECT COUNT(DISTINCT district) FROM Students;
INSERT INTO SELECT:
- The INSERT INTO SELECT statement copies data from one table and inserts it into another table.
- The INSERT INTO SELECT statement requires that the data types in the source and target tables match.
Note: The existing records in the target table are unaffected.
Syntax
Copy all columns from one table to another table:
INSERT INTO table2 SELECT * FROM table1 WHERE condition;
Copy only some columns from one table into another table:
INSERT INTO table2 (column1, column2) SELECT column1, column2 FROM table1 WHERE condition;
Example
INSERT INTO Students (department, fees) SELECT name, fees FROM Departments WHERE department='CSE';
CASE:
- The CASE statement goes through conditions and returns a value when the first condition is met (like an if-then-else statement). So, once a condition is true, it will stop reading and return the result. If no conditions are true, it returns the value in the ELSE clause.
- If there is no ELSE part and no conditions are true, it returns NULL.
Syntax
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
WHEN condition3 THEN result3
ELSE result
END;
Example
SELECT name, fees,
CASE
WHEN fees > 30000 THEN 'Fees greater than 30000'
WHEN fees = 30000 THEN 'Fees is 30000'
ELSE 'Fees is less than 30000'
END AS FeesQuote
FROM Students;
Comments
Post a Comment