Getting Started with SQL

Here i have created a list of basic commands using which you can operate or work with different databases like sqlite3 or PostgreSQL, MySQL etc.

1. SQLite3:

   • Create a database: sqlite3 database_name.db

   • Show all tables: .tables

   • View all table schema: .schema

   • Describe table structure: \headers

2. MySQL:

   • Show databases: SHOW DATABASES

   • Use a database: USE database_name

   • Show tables in current database: SHOW TABLES

   • Describe table structure: DESCRIBE table_name or SHOW COLUMNS FROM table_name

   • View schema for a specific table: SHOW CREATE TABLE table_name

3. PostgreSQL:

   • List all databases: \l+

   • Connect to a database: \c database_name

   • Show tables in the current database: \dt

   • Describe table structure: \d+ table_name

   • View schema for a specific table: \d table_name

4. Oracle SQL Developer:

   • Connect to a database: CONNECT username/password@database_name

   • Show all objects (tables, views, etc.): SELECT * FROM USER_OBJECTS

   • Describe table structure: DESCRIBE table_name or SELECT * FROM ALL_TAB_COLUMNS WHERE TABLE_NAME = 'table_name'

5. Microsoft SQL Server:

   • List all databases: SELECT name FROM sys.databases

   • Select a database: USE database_name

   • Show tables in the current database: SELECT * FROM information_schema.tables WHERE table_schema = 'database_name';

   • Describe table structure: SELECT * FROM information_schema.columns WHERE table_name = 'table_name';

6. Microsoft Access:

   • Show all tables: Show Table

   • Describe table structure: Open the table in Design view and check field properties.

These are some basic commands for managing databases, viewing tables, schemas, and other related operations for popular databases. Each database management system has its own set of specific commands and functionalities, so it's recommended to refer to their respective documentation for a comprehensive understanding.

Here are steps to import an external database into your MySQL server, if you had downloaded the database from somewhere else.

To import a database into MySQL on a Windows operating system using the command line, follow these steps:

Open Command Prompt: Open the Command Prompt as an administrator.

Navigate to MySQL's bin directory: You need to navigate to the directory where MySQL is installed.

mysql -u root -p

Replace root with your MySQL username if it's different. After executing, you’ll be prompted to enter your password.

Create a Database (Optional): If you want to import the database into a new one, you can create it first by running:

CREATE DATABASE my_database_name;

Import the SQL File: Use the source command to import the SQL file into the database you created. First, make sure you have your .sql file ready (e.g., my_database.sql). You can execute:

USE my_database_name;

SOURCE C:/path_to_your_sql_file/my_database.sql;

Replace C:/path_to_your_sql_file/my_database.sql with the actual path to your SQL file.

Note: Make sure to use forward slashes (/) instead of backslashes () in the file path.

Exit MySQL: After the import is complete, you can exit MySQL by typing:

EXIT;

Now here are some Google dorks for finding some random databases to work with or practise things on,

Google Dork for Finding Sample MySQL Databases on GitHub: You can use a Google Dork to find publicly available MySQL database samples on GitHub. Here's an example dork:

site:github.com "MySQL database" filetype:SQL

This query will help you find .sql files (which typically contain database dumps) related to MySQL on GitHub. You can refine this further by adding more keywords to narrow down your search, such as specific types of databases you're interested in.

For example, to find sample e-commerce databases:

site:github.com "MySQL e-commerce" filetype:SQL

This will show you repositories with MySQL database dumps related to e-commerce applications.

Do let me know if you got to learn something from this blog post!