usage-examples.md•11.9 kB
# MCP MySQL Server - Usage Examples
Comprehensive examples for using the MCP MySQL Server with AI assistants.
## Table of Contents
- [Basic Queries](#basic-queries)
- [Data Analysis](#data-analysis)
- [Schema Operations](#schema-operations)
- [Reporting](#reporting)
- [Data Validation](#data-validation)
- [Advanced Queries](#advanced-queries)
## Basic Queries
### Listing Tables
**Prompt:**
```
What tables are in my database?
```
**Tool Used:** `mysql_list_tables`
**Expected Response:**
```json
{
"success": true,
"tables": [
{ "Tables_in_db": "users" },
{ "Tables_in_db": "orders" },
{ "Tables_in_db": "products" }
]
}
```
### Describing Table Structure
**Prompt:**
```
Show me the structure of the users table
```
**Tool Used:** `mysql_describe_table`
**Parameters:**
```json
{
"table": "users"
}
```
**Expected Response:**
```json
{
"success": true,
"table": "users",
"columns": [
{
"Field": "id",
"Type": "int",
"Null": "NO",
"Key": "PRI",
"Default": null,
"Extra": "auto_increment"
},
{
"Field": "email",
"Type": "varchar(255)",
"Null": "NO",
"Key": "UNI",
"Default": null,
"Extra": ""
},
{
"Field": "created_at",
"Type": "timestamp",
"Null": "YES",
"Key": "",
"Default": "CURRENT_TIMESTAMP",
"Extra": ""
}
]
}
```
### Simple SELECT Query
**Prompt:**
```
Get the first 10 users from the database
```
**Tool Used:** `mysql_query`
**Parameters:**
```json
{
"query": "SELECT * FROM users LIMIT 10"
}
```
### Parameterized Query
**Prompt:**
```
Find the user with email 'john@example.com'
```
**Tool Used:** `mysql_query`
**Parameters:**
```json
{
"query": "SELECT * FROM users WHERE email = ?",
"params": ["john@example.com"]
}
```
## Data Analysis
### Counting Records
**Prompt:**
```
How many users do we have?
```
**Query:**
```sql
SELECT COUNT(*) as total_users FROM users
```
### Aggregations
**Prompt:**
```
What's the average order value?
```
**Query:**
```sql
SELECT AVG(total_amount) as average_order_value FROM orders
```
### Grouping Data
**Prompt:**
```
Show me the number of orders per customer
```
**Query:**
```sql
SELECT
customer_id,
COUNT(*) as order_count,
SUM(total_amount) as total_spent
FROM orders
GROUP BY customer_id
ORDER BY total_spent DESC
LIMIT 20
```
### Date-Based Analysis
**Prompt:**
```
Show me monthly revenue for the last 6 months
```
**Query:**
```sql
SELECT
DATE_FORMAT(order_date, '%Y-%m') as month,
SUM(total_amount) as monthly_revenue,
COUNT(*) as order_count
FROM orders
WHERE order_date >= DATE_SUB(NOW(), INTERVAL 6 MONTH)
GROUP BY DATE_FORMAT(order_date, '%Y-%m')
ORDER BY month DESC
```
### Finding Patterns
**Prompt:**
```
Find all users who registered in the last week
```
**Query:**
```sql
SELECT
id,
email,
created_at
FROM users
WHERE created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)
ORDER BY created_at DESC
```
## Schema Operations
### Getting Database Info
**Prompt:**
```
What's my current database connection info?
```
**Tool Used:** `mysql_get_database_info`
**Expected Response:**
```json
{
"success": true,
"connection": {
"host": "localhost",
"port": 3306,
"user": "app_user",
"database": "myapp_db"
},
"permissions": {
"INSERT": false,
"UPDATE": false,
"DELETE": false,
"SELECT": true,
"DROP": false,
"TRUNCATE": false,
"ALTER": false
}
}
```
### Exploring Relationships
**Prompt:**
```
Show me tables that might be related to the users table
```
**Query:**
```sql
SELECT
TABLE_NAME,
COLUMN_NAME,
REFERENCED_TABLE_NAME,
REFERENCED_COLUMN_NAME
FROM information_schema.KEY_COLUMN_USAGE
WHERE
REFERENCED_TABLE_NAME = 'users'
AND TABLE_SCHEMA = DATABASE()
```
### Checking Indexes
**Prompt:**
```
What indexes exist on the orders table?
```
**Query:**
```sql
SHOW INDEX FROM orders
```
## Reporting
### User Activity Report
**Prompt:**
```
Generate a user activity report showing registration trends
```
**Query:**
```sql
SELECT
DATE(created_at) as registration_date,
COUNT(*) as new_users,
COUNT(*) * 100.0 / SUM(COUNT(*)) OVER() as percentage
FROM users
WHERE created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
GROUP BY DATE(created_at)
ORDER BY registration_date DESC
```
### Top Products Report
**Prompt:**
```
What are the top 10 best-selling products?
```
**Query:**
```sql
SELECT
p.id,
p.name,
p.category,
COUNT(oi.id) as times_ordered,
SUM(oi.quantity) as total_quantity,
SUM(oi.price * oi.quantity) as total_revenue
FROM products p
INNER JOIN order_items oi ON p.id = oi.product_id
GROUP BY p.id, p.name, p.category
ORDER BY total_revenue DESC
LIMIT 10
```
### Customer Segmentation
**Prompt:**
```
Segment customers by their spending behavior
```
**Query:**
```sql
SELECT
CASE
WHEN total_spent >= 1000 THEN 'High Value'
WHEN total_spent >= 500 THEN 'Medium Value'
ELSE 'Low Value'
END as customer_segment,
COUNT(*) as customer_count,
AVG(total_spent) as avg_spent
FROM (
SELECT
customer_id,
SUM(total_amount) as total_spent
FROM orders
GROUP BY customer_id
) customer_totals
GROUP BY customer_segment
```
## Data Validation
### Finding Nulls
**Prompt:**
```
Check for users with missing email addresses
```
**Query:**
```sql
SELECT
COUNT(*) as users_without_email
FROM users
WHERE email IS NULL OR email = ''
```
### Duplicate Detection
**Prompt:**
```
Find duplicate email addresses in the users table
```
**Query:**
```sql
SELECT
email,
COUNT(*) as occurrence_count
FROM users
GROUP BY email
HAVING COUNT(*) > 1
ORDER BY occurrence_count DESC
```
### Data Integrity Checks
**Prompt:**
```
Find orders without associated customers
```
**Query:**
```sql
SELECT
o.id,
o.customer_id,
o.order_date
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.id
WHERE c.id IS NULL
```
### Range Validation
**Prompt:**
```
Find products with invalid prices
```
**Query:**
```sql
SELECT
id,
name,
price
FROM products
WHERE price <= 0 OR price > 1000000
```
## Advanced Queries
### Complex Joins
**Prompt:**
```
Show me order details with customer and product information
```
**Query:**
```sql
SELECT
o.id as order_id,
o.order_date,
c.name as customer_name,
c.email as customer_email,
p.name as product_name,
oi.quantity,
oi.price,
(oi.quantity * oi.price) as line_total
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id
INNER JOIN order_items oi ON o.id = oi.order_id
INNER JOIN products p ON oi.product_id = p.id
WHERE o.order_date >= DATE_SUB(NOW(), INTERVAL 7 DAY)
ORDER BY o.order_date DESC, o.id, oi.id
```
### Subqueries
**Prompt:**
```
Find customers who have spent more than the average
```
**Query:**
```sql
SELECT
c.id,
c.name,
c.email,
SUM(o.total_amount) as total_spent
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id
GROUP BY c.id, c.name, c.email
HAVING SUM(o.total_amount) > (
SELECT AVG(customer_total)
FROM (
SELECT SUM(total_amount) as customer_total
FROM orders
GROUP BY customer_id
) customer_totals
)
ORDER BY total_spent DESC
```
### Window Functions
**Prompt:**
```
Rank products by sales within each category
```
**Query:**
```sql
SELECT
category,
name,
total_sales,
RANK() OVER (PARTITION BY category ORDER BY total_sales DESC) as category_rank
FROM (
SELECT
p.category,
p.name,
SUM(oi.quantity * oi.price) as total_sales
FROM products p
INNER JOIN order_items oi ON p.id = oi.product_id
GROUP BY p.category, p.name
) product_sales
ORDER BY category, category_rank
```
### Time Series Analysis
**Prompt:**
```
Calculate 7-day moving average of daily sales
```
**Query:**
```sql
SELECT
order_date,
daily_sales,
AVG(daily_sales) OVER (
ORDER BY order_date
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
) as moving_avg_7day
FROM (
SELECT
DATE(order_date) as order_date,
SUM(total_amount) as daily_sales
FROM orders
WHERE order_date >= DATE_SUB(NOW(), INTERVAL 30 DAY)
GROUP BY DATE(order_date)
) daily_totals
ORDER BY order_date DESC
```
### Cohort Analysis
**Prompt:**
```
Analyze user retention by registration cohort
```
**Query:**
```sql
SELECT
DATE_FORMAT(first_order_date, '%Y-%m') as cohort_month,
COUNT(DISTINCT customer_id) as cohort_size,
COUNT(DISTINCT CASE
WHEN TIMESTAMPDIFF(MONTH, first_order_date, order_date) = 1
THEN customer_id
END) as month_1_retained,
COUNT(DISTINCT CASE
WHEN TIMESTAMPDIFF(MONTH, first_order_date, order_date) = 2
THEN customer_id
END) as month_2_retained
FROM (
SELECT
customer_id,
order_date,
MIN(order_date) OVER (PARTITION BY customer_id) as first_order_date
FROM orders
) customer_orders
GROUP BY DATE_FORMAT(first_order_date, '%Y-%m')
ORDER BY cohort_month DESC
```
## Safety Features Examples
### Blocked INSERT (Default)
**Prompt:**
```
Add a new user with email 'test@example.com'
```
**Query Attempted:**
```sql
INSERT INTO users (email, name) VALUES ('test@example.com', 'Test User')
```
**Response:**
```json
{
"error": "INSERT operations are disabled. Set ALLOW_INSERT_OPERATION=true to enable.",
"blocked": true
}
```
### Blocked UPDATE (Default)
**Prompt:**
```
Update user email to 'newemail@example.com' where id = 1
```
**Query Attempted:**
```sql
UPDATE users SET email = 'newemail@example.com' WHERE id = 1
```
**Response:**
```json
{
"error": "UPDATE operations are disabled. Set ALLOW_UPDATE_OPERATION=true to enable.",
"blocked": true
}
```
### Always Blocked DROP
**Prompt:**
```
Drop the test_table
```
**Query Attempted:**
```sql
DROP TABLE test_table
```
**Response:**
```json
{
"error": "DROP operations are not allowed",
"blocked": true
}
```
## SQL Injection Prevention
### Safe Parameterized Query
**Prompt:**
```
Find users with email from user input
```
**Unsafe (Don't do this):**
```javascript
// DO NOT USE STRING CONCATENATION
query: "SELECT * FROM users WHERE email = '" + userInput + "'"
```
**Safe (Always use params):**
```json
{
"query": "SELECT * FROM users WHERE email = ?",
"params": ["user@example.com"]
}
```
### Multiple Parameters
**Query with Multiple Params:**
```json
{
"query": "SELECT * FROM products WHERE category = ? AND price < ?",
"params": ["electronics", 500]
}
```
### Array Parameters
**Query with IN clause:**
```json
{
"query": "SELECT * FROM users WHERE id IN (?, ?, ?)",
"params": [1, 2, 3]
}
```
## Tips for Better Results
1. **Be Specific**: "Show me the last 10 orders" is better than "show orders"
2. **Mention Limits**: Always specify LIMIT for potentially large result sets
3. **Use Filters**: Add WHERE clauses to narrow down results
4. **Ask for Formatting**: "Format as a table" or "format as JSON"
5. **Request Explanations**: "Explain this query" to understand what's happening
6. **Safety First**: Test queries in development before production
## Common Patterns
### Pagination
```sql
-- Page 1 (first 20 records)
SELECT * FROM users ORDER BY id LIMIT 20 OFFSET 0
-- Page 2 (next 20 records)
SELECT * FROM users ORDER BY id LIMIT 20 OFFSET 20
```
### Search
```sql
-- Case-insensitive search
SELECT * FROM products
WHERE LOWER(name) LIKE LOWER('%search term%')
LIMIT 50
```
### Date Ranges
```sql
-- Last 30 days
SELECT * FROM orders
WHERE order_date >= DATE_SUB(NOW(), INTERVAL 30 DAY)
-- Specific date range
SELECT * FROM orders
WHERE order_date BETWEEN ? AND ?
```
### Exists Check
```sql
-- Check if record exists
SELECT EXISTS(
SELECT 1 FROM users WHERE email = ?
) as user_exists
```
## Next Steps
- Try these examples in your own database
- Modify queries to match your schema
- Combine patterns for complex analysis
- Always test in development first
- Check out the setup guides for [Claude Code](./claude-code-setup.md) or [Codex CLI](./codex-cli-setup.md)