Cheatsheet

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. ...

PowerShell has evolved significantly over the years and introduced powerful language features that make scripting more efficient and readable. Whether you’re a system administrator, DevOps engineer, or developer - mastering these language constructs will dramatically improve your PowerShell productivity. Variables and Data Types Basic Variable Assignment # Simple assignment $name = "John Doe" $age = 30 $isActive = $true # Type constraints [string]$username = "admin" [int]$count = 0 [datetime]$startTime = Get-Date Arrays and Collections # Arrays $servers = @("web01", "web02", "db01") $numbers = 1..10 # Hash tables $configuration = @{ Server = "localhost" Port = 8080 SSL = $true } # Accessing elements Write-Host $servers[0] # web01 Write-Host $configuration.Server # localhost Write-Host $configuration["Port"] # 8080 Advanced Collections # ArrayList for dynamic arrays $list = [System.Collections.ArrayList]@() $list.Add("element1") | Out-Null $list.AddRange(@("element2", "element3")) # Ordered hash table $orderedConfig = [ordered]@{ First = "value1" Second = "value2" } Operators Comparison Operators # Basic comparisons $a -eq $b # Equal $a -ne $b # Not equal $a -gt $b # Greater than $a -lt $b # Less than $a -ge $b # Greater or equal $a -le $b # Less or equal # String operations $text -like "*pattern*" # Wildcard matching $text -match "regex" # Regular expression $text -contains "substring" # Contains check $text -in @("a", "b", "c") # Membership test Logical Operators # Logical operations $condition1 -and $condition2 $condition1 -or $condition2 -not $condition # Practical example if (($age -gt 18) -and ($hasLicense -eq $true)) { Write-Host "May drive car" } Modern Operators (PowerShell 7+) # Ternary operator $status = ($score -gt 80) ? "Passed" : "Failed" # Null coalescing operators $username = $env:USER ?? $env:USERNAME ?? "Unknown" # Null coalescing assignment $configuration.Timeout ??= 30 Conditional Logic If-Else Statements # Basic if-else if ($score -gt 90) { Write-Host "Excellent!" } elseif ($score -gt 70) { Write-Host "Good work!" } else { Write-Host "Keep practicing!" } # One-liner conditions if ($debug) { Write-Host "Debug mode activated" } Switch Statements # Basic switch switch ($status) { "Running" { Write-Host "Service is active" } "Stopped" { Write-Host "Service is inactive" } "Paused" { Write-Host "Service is paused" } default { Write-Host "Unknown status" } } # Advanced switch with regex switch -Regex ($logEntry) { "ERROR" { Write-Host "Error found" -ForegroundColor Red } "WARNING" { Write-Host "Warning found" -ForegroundColor Yellow } "INFO" { Write-Host "Information logged" } } Loops and Iteration ForEach Loops # Traditional foreach foreach ($server in $servers) { Test-Connection -ComputerName $server -Count 1 } # Pipeline foreach $servers | ForEach-Object { Write-Host "Processing $_" Test-Connection -ComputerName $_ -Count 1 } # Parallel processing (PowerShell 7+) $servers | ForEach-Object -Parallel { Test-Connection -ComputerName $_ -Count 1 } -ThrottleLimit 5 For and While Loops # For loop for ($i = 0; $i -lt 10; $i++) { Write-Host "Iteration $i" } # While loop $counter = 0 while ($counter -lt 5) { Write-Host "Counter: $counter" $counter++ } # Do-While loop do { $input = Read-Host "Enter 'quit' to exit" } while ($input -ne "quit") Functions and Parameters Basic Functions # Simple function function Get-SystemInfo { $os = Get-CimInstance -ClassName Win32_OperatingSystem return @{ ComputerName = $env:COMPUTERNAME OperatingSystem = $os.Caption FreeMemory = [math]::Round($os.FreePhysicalMemory / 1MB, 2) } } # Call function $info = Get-SystemInfo Write-Host "Computer: $($info.ComputerName)" Advanced Functions with Parameters function Test-ServiceStatus { [CmdletBinding()] param( [Parameter(Mandatory = $true, ValueFromPipeline = $true)] [string[]]$ServiceName, [ValidateSet("Running", "Stopped", "All")] [string]$Status = "All", [switch]$Detailed ) process { foreach ($service in $ServiceName) { $svc = Get-Service -Name $service -ErrorAction SilentlyContinue if ($svc) { if ($Status -eq "All" -or $svc.Status -eq $Status) { if ($Detailed) { $svc | Select-Object Name, Status, StartType, DisplayName } else { "$($svc.Name): $($svc.Status)" } } } else { Write-Warning "Service '$service' not found" } } } } # Usage examples Test-ServiceStatus -ServiceName "Spooler", "BITS" "Spooler", "BITS" | Test-ServiceStatus -Status Running -Detailed Error Handling Try-Catch-Finally try { $content = Get-Content -Path "C:\nonexistent.txt" -ErrorAction Stop Write-Host "File read successfully" } catch [System.IO.FileNotFoundException] { Write-Host "File not found - creating new file" New-Item -Path "C:\nonexistent.txt" -ItemType File } catch { Write-Host "An unexpected error occurred: $($_.Exception.Message)" } finally { Write-Host "Cleanup completed" } Error Action Settings # Set global error preference $ErrorActionPreference = "Stop" # Per-command error handling Get-Process -Name "NonExistent" -ErrorAction SilentlyContinue Get-Service -Name "Invalid" -ErrorAction Ignore # Capture errors Get-Process -Name "NonExistent" -ErrorAction SilentlyContinue -ErrorVariable myErrors if ($myErrors) { Write-Host "Errors occurred: $($myErrors.Count)" } Object Manipulation Creating Custom Objects # PSCustomObject (recommended) $server = [PSCustomObject]@{ Name = "WEB01" IP = "192.168.1.10" Role = "WebServer" LastReboot = (Get-Date).AddDays(-7) } # Add methods to objects $server | Add-Member -MemberType ScriptMethod -Name "GetUptime" -Value { (Get-Date) - $this.LastReboot } # Use method $uptime = $server.GetUptime() Write-Host "Server uptime: $($uptime.Days) days" Object Filtering and Selection # Get running services with specific properties $services = Get-Service | Where-Object { $_.Status -eq "Running" -and $_.Name -like "Win*" } | Select-Object Name, Status, StartType # Group and sort objects $processes = Get-Process | Group-Object ProcessName | Sort-Object Count -Descending | Select-Object -First 5 # Measure objects $memoryUsage = Get-Process | Measure-Object WorkingSet -Sum -Average Write-Host "Total memory: $([math]::Round($memoryUsage.Sum / 1MB, 2)) MB" Pipeline Operations Pipeline Basics # Chain commands with pipeline Get-Service | Where-Object Status -eq "Running" | Sort-Object Name | Select-Object Name, Status | Format-Table -AutoSize # Pipeline variable $_ Get-ChildItem -Path C:\Logs -Filter "*.log" | ForEach-Object { $size = [math]::Round($_.Length / 1KB, 2) Write-Host "$($_.Name): ${size} KB" } Advanced Pipeline Techniques # Tee-Object for pipeline branching Get-Process | Tee-Object -FilePath "processes.txt" | Where-Object CPU -gt 100 | Sort-Object CPU -Descending # Out-GridView for interactive filtering Get-EventLog -LogName System -Newest 100 | Out-GridView -Title "System Events" -PassThru | Export-Csv -Path "selected_events.csv" Classes and Methods Defining Classes class Server { [string]$Name [string]$IP [string]$Role [datetime]$LastReboot # Constructor Server([string]$name, [string]$ip, [string]$role) { $this.Name = $name $this.IP = $ip $this.Role = $role $this.LastReboot = Get-Date } # Methods [timespan]GetUptime() { return (Get-Date) - $this.LastReboot } [void]Restart() { Write-Host "Restarting server $($this.Name)..." $this.LastReboot = Get-Date } [string]ToString() { return "$($this.Name) ($($this.IP)) - $($this.Role)" } } # Use class $webServer = [Server]::new("WEB01", "192.168.1.10", "WebServer") Write-Host $webServer.ToString() Write-Host "Uptime: $($webServer.GetUptime().TotalHours) hours" Inheritance class DatabaseServer : Server { [string]$DatabaseEngine [int]$Port DatabaseServer([string]$name, [string]$ip, [string]$engine, [int]$port) : base($name, $ip, "Database") { $this.DatabaseEngine = $engine $this.Port = $port } [void]BackupDatabase() { Write-Host "Backing up $($this.DatabaseEngine) database on $($this.Name)" } } $dbServer = [DatabaseServer]::new("DB01", "192.168.1.20", "SQL Server", 1433) $dbServer.BackupDatabase() Modern Language Features Splatting # Parameter splatting $parameters = @{ Path = "C:\Logs" Filter = "*.log" Recurse = $true } Get-ChildItem @parameters # Array splatting $arguments = @("notepad.exe", "C:\temp\file.txt") Start-Process @arguments String Interpolation and Here-Strings # String interpolation $name = "John" $age = 30 Write-Host "Hello, $name! You are $age years old." # Subexpression operator Write-Host "Current time: $(Get-Date -Format 'yyyy-MM-dd HH:mm:ss')" # Here-strings for multiline text $script = @" Get-Service | Where-Object { $_.Status -eq "Running" } | Select-Object Name, Status "@ # Execute here-string as script block Invoke-Expression $script Regular Expressions # Basic regex matching $text = "Error: Connection failed at 2024-01-15 10:30:25" if ($text -match "(\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2})") { Write-Host "Timestamp found: $($Matches[1])" } # Replace with regex $logEntry = "ERROR: Database connection failed" $cleanedEntry = $logEntry -replace "ERROR:", "Warning:" # Split with regex $data = "apple,banana;orange:grape" $fruits = $data -split "[,;:]" Pipeline Chain Operators (PowerShell 7+) # Success chain (&&) Get-Service -Name "Spooler" && Write-Host "Spooler service exists" # Error chain (||) Get-Service -Name "NonExistent" 2>$null || Write-Host "Service not found" # Combine chains Test-Path "C:\temp" && Write-Host "Temp exists" || Write-Host "Temp missing" Practical Examples System Monitoring Script function Get-SystemHealth { [CmdletBinding()] param( [string[]]$ComputerName = $env:COMPUTERNAME, [int]$CPUThreshold = 80, [int]$MemoryThreshold = 90 ) $results = foreach ($computer in $ComputerName) { try { # Get system information $os = Get-CimInstance -ClassName Win32_OperatingSystem -ComputerName $computer $cpu = Get-CimInstance -ClassName Win32_Processor -ComputerName $computer # Calculate metrics $memoryUsed = [math]::Round((($os.TotalVisibleMemorySize - $os.FreePhysicalMemory) / $os.TotalVisibleMemorySize) * 100, 2) $cpuUsage = Get-Counter "\Processor(_Total)\% Processor Time" -ComputerName $computer -SampleInterval 1 -MaxSamples 1 | Select-Object -ExpandProperty CounterSamples | Select-Object -ExpandProperty CookedValue # Create result object [PSCustomObject]@{ ComputerName = $computer CPUUsage = [math]::Round($cpuUsage, 2) MemoryUsage = $memoryUsed Status = if ($cpuUsage -gt $CPUThreshold -or $memoryUsed -gt $MemoryThreshold) { "Warning" } else { "OK" } Timestamp = Get-Date } } catch { [PSCustomObject]@{ ComputerName = $computer CPUUsage = "Error" MemoryUsage = "Error" Status = "Unreachable" Timestamp = Get-Date } } } return $results } # Usage $healthCheck = Get-SystemHealth -ComputerName @("localhost", "server01") -CPUThreshold 70 $healthCheck | Format-Table -AutoSize Log Analysis Script function Analyze-Logs { [CmdletBinding()] param( [Parameter(Mandatory)] [string]$LogPath, [string]$Pattern = "ERROR|WARNING|CRITICAL", [int]$LastHours = 24 ) $cutoffTime = (Get-Date).AddHours(-$LastHours) Get-ChildItem -Path $LogPath -Filter "*.log" | ForEach-Object { $content = Get-Content -Path $_.FullName $matches = $content | Where-Object { $_ -match $Pattern } if ($matches) { [PSCustomObject]@{ LogFile = $_.Name ErrorCount = ($matches | Measure-Object).Count Errors = $matches LastModified = $_.LastWriteTime } } } | Where-Object { $_.LastModified -gt $cutoffTime } | Sort-Object ErrorCount -Descending } # Usage $logAnalysis = Analyze-Logs -LogPath "C:\Logs" -LastHours 8 $logAnalysis | Select-Object LogFile, ErrorCount, LastModified | Format-Table Best Practices and Tips Performance Tips # Use ArrayList instead of Array for frequent additions $list = [System.Collections.ArrayList]@() # Instead of: $array += $element (slow) $list.Add($element) | Out-Null # Much faster # Use -contains instead of -in for single element checks if ($servers -contains $targetServer) { } # Faster # Instead of: if ($targetServer -in $servers) { } # Pre-allocate arrays when size is known $results = New-Object System.Object[] 1000 Code Organization # Use regions for large scripts #region Helper Functions function Get-Configuration { # Implementation } #endregion #region Main Logic # Main script logic here #endregion Best Practices for Error Handling # Always use specific error types when possible try { # Risky operation } catch [System.UnauthorizedAccessException] { Write-Warning "Access denied - check permissions" } catch [System.IO.FileNotFoundException] { Write-Error "Required file not found" } catch { Write-Error "Unexpected error: $($_.Exception.Message)" throw # Re-throw if not recoverable } Conclusion PowerShell’s language features provide powerful tools for automation, system administration, and development tasks. Modern features like ternary operators, null coalescing operators, and parallel processing make PowerShell competitive with other scripting languages while maintaining its object-oriented nature and pipeline philosophy. ...