MySQL Queries and MySQL Connection (Class 12)
All MySQL Queries and Connection Code (Full List + Reference Table)
1. MySQL Connection using Python:
----------------------------------
import mysql.connector
mycon = mysql.connector.connect(
host="localhost",
user="root",
password="yourpassword",
database="school"
cursor = mycon.cursor()
print("Connection Successful")
2. Create Database:
--------------------
CREATE DATABASE school;
3. Use Database:
----------------
USE school;
4. Create Table:
-----------------
MySQL Queries and MySQL Connection (Class 12)
CREATE TABLE student (
roll INT PRIMARY KEY,
name VARCHAR(50),
class INT,
marks INT,
phone BIGINT
);
5. Insert Values:
------------------
INSERT INTO student VALUES (1, 'Aman', 12, 85, 9876543210);
INSERT INTO student VALUES (2, 'Riya', 12, 90, 9988776655);
INSERT INTO student VALUES (3, 'Ankit', 11, 78, 9123456789);
6. Display Table Data:
------------------------
SELECT * FROM student;
7. Select Specific Columns:
----------------------------
SELECT name, class FROM student;
8. Use WHERE Clause:
---------------------
MySQL Queries and MySQL Connection (Class 12)
SELECT * FROM student WHERE class = 12;
9. WHERE with AND/OR:
----------------------
SELECT * FROM student WHERE class = 12 AND marks > 80;
10. LIKE Clause (Pattern Matching):
------------------------------------
SELECT * FROM student WHERE name LIKE 'A%'; -- Names starting with A
SELECT * FROM student WHERE name LIKE '%a'; -- Names ending with a
SELECT * FROM student WHERE name LIKE '%an%'; -- Names containing 'an'
11. Aggregate Functions:
-------------------------
SELECT MAX(marks) FROM student;
SELECT MIN(marks) FROM student;
SELECT SUM(marks) FROM student;
SELECT AVG(marks) FROM student;
SELECT COUNT(*) FROM student;
12. GROUP BY Clause:
---------------------
SELECT class, AVG(marks) FROM student GROUP BY class;
MySQL Queries and MySQL Connection (Class 12)
13. HAVING Clause:
-------------------
SELECT class, AVG(marks) FROM student GROUP BY class HAVING AVG(marks) > 80;
14. ORDER BY Clause:
---------------------
SELECT * FROM student ORDER BY marks DESC;
15. UPDATE Data:
-----------------
UPDATE student SET marks = 95 WHERE name = 'Aman';
16. DELETE Data:
-----------------
DELETE FROM student WHERE roll = 3;
17. ALTER TABLE:
-----------------
ALTER TABLE student ADD email VARCHAR(100);
ALTER TABLE student DROP COLUMN email;
18. DROP Table:
----------------
DROP TABLE student;
MySQL Queries and MySQL Connection (Class 12)
19. BETWEEN Clause:
--------------------
SELECT * FROM student WHERE marks BETWEEN 80 AND 90;
20. IN Clause:
---------------
SELECT * FROM student WHERE class IN (11, 12);
21. IS NULL / IS NOT NULL:
----------------------------
SELECT * FROM student WHERE email IS NULL;
SELECT * FROM student WHERE email IS NOT NULL;
Reference Table (MySQL Clauses and Functions)
22. Reference Table (MySQL Clauses and Functions):
--------------------------------------------------
| Keyword / Clause | Description |
|------------------|-------------|
| SELECT | Used to select data from a database |
| FROM | Specifies the table to select or delete data from |
| WHERE | Filters records that fulfill a specified condition |
MySQL Queries and MySQL Connection (Class 12)
| AND, OR, NOT | Combine multiple conditions in WHERE clause |
| LIKE | Search for a specified pattern in a column |
| IN | Specifies multiple values in a WHERE clause |
| BETWEEN | Selects values within a range |
| IS NULL | Checks for NULL values |
| ORDER BY | Sorts the result set in ascending or descending order |
| GROUP BY | Groups rows that have the same values |
| HAVING | Used to filter groups based on condition (like WHERE) |
| COUNT() | Returns the number of rows |
| SUM() | Returns the total sum of a column |
| AVG() | Returns the average value of a column |
| MAX() | Returns the largest value |
| MIN() | Returns the smallest value |
| INSERT INTO | Inserts new data into a table |
| UPDATE | Modifies existing records |
| DELETE | Deletes existing records |
| CREATE TABLE | Creates a new table |
| DROP TABLE | Deletes a table completely |
| ALTER TABLE | Adds, deletes, or modifies columns in a table |
| PRIMARY KEY | Uniquely identifies each row in a table |
