Sql

Basic Commands Database Connection # Connect to MySQL console mysql -u username -p # Connect to specific database mysql -u username -p database_name # Connect to remote server mysql -h hostname -u username -p database_name Database Operations -- Show all databases SHOW DATABASES; /* +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | my_database | | test_db | +--------------------+ */ -- Create database CREATE DATABASE database_name; CREATE DATABASE database_name CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; -- Query OK, 1 row affected (0.01 sec) -- Use database USE database_name; -- Database changed -- Show current database SELECT DATABASE(); /* +-------------+ | DATABASE() | +-------------+ | my_database | +-------------+ */ -- Drop database DROP DATABASE database_name; -- Query OK, 0 rows affected (0.05 sec) Table Management Creating Tables -- Basic table creation CREATE TABLE users ( id INT AUTO_INCREMENT PRIMARY KEY, username VARCHAR(50) NOT NULL UNIQUE, email VARCHAR(100) NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ); -- Table with foreign key CREATE TABLE posts ( id INT AUTO_INCREMENT PRIMARY KEY, user_id INT, title VARCHAR(200) NOT NULL, content TEXT, FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE ); Table Information -- Show all tables SHOW TABLES; /* +---------------------+ | Tables_in_my_database | +---------------------+ | users | | posts | | categories | +---------------------+ */ -- Describe table structure DESCRIBE table_name; DESC table_name; /* +------------+--------------+------+-----+-------------------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+--------------+------+-----+-------------------+----------------+ | id | int | NO | PRI | NULL | auto_increment | | username | varchar(50) | NO | UNI | NULL | | | email | varchar(100) | NO | | NULL | | | created_at | timestamp | YES | | CURRENT_TIMESTAMP | DEFAULT_GENERATED | +------------+--------------+------+-----+-------------------+----------------+ */ -- Detailed table information SHOW CREATE TABLE table_name; -- Show columns of a table SHOW COLUMNS FROM table_name; Modifying Tables -- Add column ALTER TABLE table_name ADD COLUMN column_name datatype; -- Modify column ALTER TABLE table_name MODIFY COLUMN column_name new_datatype; ALTER TABLE table_name CHANGE old_column_name new_column_name datatype; -- Drop column ALTER TABLE table_name DROP COLUMN column_name; -- Add index ALTER TABLE table_name ADD INDEX idx_column_name (column_name); -- Rename table RENAME TABLE old_name TO new_name; -- Drop table DROP TABLE table_name; CRUD Operations CREATE (Insert) -- Insert single record INSERT INTO users (username, email) VALUES ('john_doe', '[email protected]'); -- Insert multiple records INSERT INTO users (username, email) VALUES ('user1', '[email protected]'), ('user2', '[email protected]'), ('user3', '[email protected]'); -- With ON DUPLICATE KEY UPDATE (since MySQL 4.1) INSERT INTO users (username, email) VALUES ('john_doe', '[email protected]') ON DUPLICATE KEY UPDATE email = VALUES(email); READ (Select) -- Select all records SELECT * FROM users; /* +----+----------+-------------------+---------------------+ | id | username | email | created_at | +----+----------+-------------------+---------------------+ | 1 | john_doe | [email protected] | 2024-01-15 10:30:00 | | 2 | jane_doe | [email protected] | 2024-01-16 14:22:00 | | 3 | bob_user | [email protected] | 2024-01-17 09:15:00 | +----+----------+-------------------+---------------------+ */ -- Select specific columns SELECT username, email FROM users; /* +----------+-------------------+ | username | email | +----------+-------------------+ | john_doe | [email protected] | | jane_doe | [email protected] | | bob_user | [email protected] | +----------+-------------------+ */ -- With WHERE condition SELECT * FROM users WHERE id = 1; SELECT * FROM users WHERE username LIKE 'john%'; -- With ORDER BY SELECT * FROM users ORDER BY created_at DESC; SELECT * FROM users ORDER BY username ASC, created_at DESC; -- With LIMIT SELECT * FROM users LIMIT 10; SELECT * FROM users LIMIT 10 OFFSET 20; -- With COUNT, SUM, AVG, MAX, MIN SELECT COUNT(*) FROM users; /* +----------+ | COUNT(*) | +----------+ | 3 | +----------+ */ SELECT AVG(age) FROM users; SELECT MAX(created_at) FROM users; /* +---------------------+ | MAX(created_at) | +---------------------+ | 2024-01-17 09:15:00 | +---------------------+ */ UPDATE (Update) -- Update single record UPDATE users SET email = '[email protected]' WHERE id = 1; -- Update multiple columns UPDATE users SET username = 'new_username', email = '[email protected]' WHERE id = 1; -- With conditions UPDATE users SET status = 'active' WHERE created_at > '2024-01-01'; DELETE (Delete) -- Delete specific records DELETE FROM users WHERE id = 1; DELETE FROM users WHERE created_at < '2024-01-01'; -- Delete all records (structure remains) DELETE FROM users; -- Empty table (faster than DELETE) TRUNCATE TABLE users; Understanding Database Relations 1:1 Relationship (One-to-One) Concept: One record in table A belongs to exactly one record in table B and vice versa. ...