1. Connect to MySQL

mysql -u username -p password

2. Create a Database

CREATE DATABASE mydatabase;

3. Use a Database

USE mydatabase;

4. Create a Table

CREATE TABLE mytable (
  id INT PRIMARY KEY,
  name VARCHAR(255),
  age INT
);

5. Insert Data into a Table

INSERT INTO mytable (name, age) VALUES ('John Doe', 30);

6. Select Data from a Table

SELECT * FROM mytable;

7. Update Data in a Table

UPDATE mytable SET age = 31 WHERE name = 'John Doe';

8. Delete Data from a Table

DELETE FROM mytable WHERE name = 'John Doe';

9. Drop a Table

DROP TABLE mytable;

10. Drop a Database

DROP DATABASE mydatabase;

11. Show Databases

SHOW DATABASES;

12. Show Tables

SHOW TABLES;

13. Describe a Table

DESCRIBE mytable;

14. Create an Index

CREATE INDEX idx_name ON mytable (name);

15. Drop an Index

DROP INDEX idx_name ON mytable;

16. Create a View

CREATE VIEW myview AS SELECT * FROM mytable;

17. Drop a View

DROP VIEW myview;

18. Create a Stored Procedure

CREATE PROCEDURE myproc()
BEGIN
  SELECT * FROM mytable;
END;

19. Call a Stored Procedure

CALL myproc();

20. Drop a Stored Procedure

DROP PROCEDURE myproc;

21. Create a Function

CREATE FUNCTION myfunc(x INT) RETURNS INT
BEGIN
  RETURN x * 2;
END;

22. Call a Function

SELECT myfunc(5);

23. Drop a Function

DROP FUNCTION myfunc;

24. Create a Trigger

CREATE TRIGGER mytrigger
BEFORE INSERT ON mytable
FOR EACH ROW
BEGIN
  SET NEW.name = UPPER(NEW.name);
END;

25. Drop a Trigger

DROP TRIGGER mytrigger;

26. Create a User

CREATE USER 'myuser'@'%' IDENTIFIED BY 'mypassword';

27. Grant Privileges to a User

GRANT ALL PRIVILEGES ON mydatabase.* TO 'myuser'@'%';

28. Revoke Privileges from a User

REVOKE ALL PRIVILEGES ON mydatabase.* FROM 'myuser'@'%';

29. Show User Privileges

SHOW GRANTS FOR 'myuser'@'%';

30. Change User Password

SET PASSWORD FOR 'myuser'@'%' = PASSWORD('newpassword');

31. Drop a User

DROP USER 'myuser'@'%';

32. Show MySQL Version

SELECT VERSION();

33. Show MySQL Status

SHOW STATUS;

34. Show MySQL Variables

SHOW VARIABLES;

35. Set MySQL Variable

SET GLOBAL max_connections = 100;

36. Show MySQL Logs

SHOW BINARY LOGS;

37. Backup a Database

mysqldump -u username -p mydatabase > mydatabase_backup.sql

38. Restore a Database

mysql -u username -p mydatabase < mydatabase_backup.sql

39. Import Data from CSV

LOAD DATA INFILE 'data.csv' INTO TABLE mytable FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';

40. Export Data to CSV

SELECT * FROM mytable INTO OUTFILE 'data.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';

41. Use Transactions

START TRANSACTION;
UPDATE mytable SET age = 31 WHERE name = 'John Doe';
COMMIT;

42. Rollback a Transaction

ROLLBACK;

43. Create a Composite Key

ALTER TABLE mytable ADD PRIMARY KEY (name, age);

44. Create a Foreign Key

ALTER TABLE mytable ADD CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users(id);

45. Show Foreign Keys

SELECT * FROM information_schema.KEY_COLUMN_USAGE WHERE TABLE_NAME = 'mytable';

46. Optimize a Table

OPTIMIZE TABLE mytable;

47. Analyze a Table

ANALYZE TABLE mytable;

48. Check Table Status

CHECK TABLE mytable;

49. Repair a Table

REPAIR TABLE mytable;

50. Show Table Size

SELECT table_name AS "Table", 
       ROUND(((data_length + index_length) / 1024 / 1024), 2) AS "Size (MB)" 
FROM information_schema.TABLES 
WHERE table_schema = "mydatabase";

51. Create a Temporary Table

CREATE TEMPORARY TABLE temp_table AS SELECT * FROM mytable;

52. Show Current Database

SELECT DATABASE();

53. Show Current User

SELECT USER();

54. Show Current Connection ID

SELECT CONNECTION_ID();

55. Set SQL Mode

SET sql_mode = 'STRICT_ALL_TABLES';

56. Show SQL Modes

SELECT @@sql_mode;

57. Create a Sequence

CREATE SEQUENCE my_sequence START WITH 1 INCREMENT BY 1;

58. Get Next Value from a Sequence

SELECT NEXT VALUE FOR my_sequence;

59. Drop a Sequence

DROP SEQUENCE my_sequence;

60. Create a User-Defined Variable

SET @myvar = 10;

61. Use a User-Defined Variable

SELECT @myvar;

62. Create a Stored Function

CREATE FUNCTION add_numbers(a INT, b INT) RETURNS INT
BEGIN
  RETURN a + b;
END;

63. Call a Stored Function

SELECT add_numbers(5, 10);

64. Create a Cursor

DECLARE my_cursor CURSOR FOR SELECT name FROM mytable;

65. Open a Cursor

OPEN my_cursor;

66. Fetch from a Cursor

FETCH my_cursor INTO @name;

67. Close a Cursor

CLOSE my_cursor;

68. Create a View with Join

CREATE VIEW myview AS SELECT a.name, b.age FROM mytable a JOIN othert able b ON a.id = b.user_id;

69. Show View Definition

SHOW CREATE VIEW myview;

70. Refresh a Materialized View

CREATE OR REPLACE VIEW myview AS SELECT * FROM mytable;

71. Create a Composite Index

CREATE INDEX idx_name_age ON mytable (name, age);

72. Show Indexes

SHOW INDEX FROM mytable;

73. Use LIMIT with OFFSET

SELECT * FROM mytable LIMIT 10 OFFSET 5;

74. Use GROUP BY

SELECT age, COUNT(*) FROM mytable GROUP BY age;

75. Use HAVING Clause

SELECT age, COUNT(*) FROM mytable GROUP BY age HAVING COUNT(*) > 1;

76. Use UNION

SELECT name FROM mytable UNION SELECT name FROM othertable;

77. Use CASE Statement

SELECT name, 
       CASE 
           WHEN age < 18 THEN 'Minor' 
           ELSE 'Adult' 
       END AS age_group 
FROM mytable;

78. Use COALESCE Function

SELECT COALESCE(name, 'Unknown') FROM mytable;

79. Use IF Function

SELECT IF(age > 18, 'Adult', 'Minor') AS age_group FROM mytable;

80. Use CONCAT Function

SELECT CONCAT(name, ' is ', age, ' years old') AS description FROM mytable;

81. Use SUBSTRING Function

SELECT SUBSTRING(name, 1, 3) AS short_name FROM mytable;

82. Use LENGTH Function

SELECT LENGTH(name) AS name_length FROM mytable;

83. Use TRIM Function

SELECT TRIM(name) AS trimmed_name FROM mytable;

84. Use UPPER and LOWER Functions

SELECT UPPER(name) AS upper_name, LOWER(name) AS lower_name FROM mytable;

85. Use DATE Functions

SELECT CURDATE() AS current_date, NOW() AS current_datetime;

86. Use DATE_FORMAT Function

SELECT DATE_FORMAT(NOW(), '%Y-%m-%d') AS formatted_date;

87. Use DATEDIFF Function

SELECT DATEDIFF(NOW(), '2023-01-01') AS days_difference;

88. Use ADDDATE Function

SELECT ADDDATE(NOW(), INTERVAL 7 DAY) AS next_week;

89. Use SUBDATE Function

SELECT SUBDATE(NOW(), INTERVAL 7 DAY) AS last_week;

90. Use GROUP_CONCAT Function

SELECT GROUP_CONCAT(name) AS all_names FROM mytable;

91. Use JSON Functions

SELECT JSON_OBJECT('name', name, 'age', age) AS json_data FROM mytable;

92. Use REGEXP for Pattern Matching

SELECT * FROM mytable WHERE name REGEXP '^J';

93. Use EXPLAIN for Query Analysis

EXPLAIN SELECT * FROM mytable WHERE age > 30;

94. Use SHOW CREATE TABLE

SHOW CREATE TABLE mytable;

95. Use ALTER TABLE to Modify Structure

ALTER TABLE mytable ADD COLUMN email VARCHAR(255);

96. Use ALTER TABLE to Rename a Column

ALTER TABLE mytable CHANGE name full_name VARCHAR(255);

97. Use ALTER TABLE to Drop a Column

ALTER TABLE mytable DROP COLUMN email;

98. Use CREATE TABLE AS

CREATE TABLE new_table AS SELECT * FROM mytable WHERE age > 30;

99. Use INSERT IGNORE

INSERT IGNORE INTO mytable (name, age) VALUES ('John Doe', 30);

100. Use REPLACE INTO

REPLACE INTO mytable (name, age) VALUES ('John Doe', 30);