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.
SQLite3:
Create a database:
sqlite3 database_name.db
Show all tables:
.tables
View all table schema:
.schema
Describe table structure:
\headers
MySQL:
Show databases:
SHOW DATABASES
Use a database:
USE database_name
Show tables in current database:
SHOW TABLES
Describe table structure:
DESCRIBE table_name
orSHOW COLUMNS FROM table_name
View schema for a specific table:
SHOW CREATE TABLE table_name
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
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
orSELECT * FROM ALL_TAB_COLUMNS WHERE TABLE_NAME = 'table_name'
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';
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!
Subscribe to my newsletter
Read articles from Pulkit Garg directly inside your inbox. Subscribe to the newsletter, and don't miss out.
Written by
