MySQL is a widely-used open-source relational database management system (RDBMS) that provides a robust, scalable, and efficient way to manage databases. Understanding MySQL commands is essential for developers, database administrators, and data analysts. This guide will cover fundamental MySQL commands for database management, data manipulation, user administration, and more.
To connect to MySQL using the command-line interface (CLI), you use the following command: mysql -u username -p
. Replace username
with your MySQL username. You will be prompted to enter your password.
To create a new database, use the CREATE DATABASE
command: CREATE DATABASE dbname;
. This command creates a database named dbname
.
To list all the databases on the MySQL server, use the SHOW DATABASES
command: SHOW DATABASES;
. This command displays all available databases.
To select a database for subsequent operations, use the USE
command: USE dbname;
. This command switches to the specified database dbname
.
To delete a database, use the DROP DATABASE
command: DROP DATABASE dbname;
. This command permanently deletes the specified database dbname
.
To create a new table within a database, use the CREATE TABLE
command: CREATE TABLE tablename ( column1 datatype constraints, column2 datatype constraints, ... );
. This command creates a table named tablename
with specified columns, data types, and constraints.
To list all the tables in the current database, use the SHOW TABLES
command: SHOW TABLES;
. This command displays all tables in the selected database.
To view the structure of a table, use the DESCRIBE
command: DESCRIBE tablename;
. This command shows the structure of the table tablename
, including column names, data types, and constraints.
To delete a table from the database, use the DROP TABLE
command: DROP TABLE tablename;
. This command permanently deletes the table tablename
.
To insert new data into a table, use the INSERT INTO
command: INSERT INTO tablename (column1, column2, ...) VALUES (value1, value2, ...);
. This command adds a new row of data to the table tablename
.
To update existing data in a table, use the UPDATE
command: UPDATE tablename SET column1 = value1, column2 = value2 WHERE condition;
. This command modifies existing data in the table tablename
based on the specified condition.
To delete data from a table, use the DELETE FROM
command: DELETE FROM tablename WHERE condition;
. This command removes rows from the table tablename
based on the specified condition.
To retrieve data from a table, use the SELECT
command: SELECT column1, column2 FROM tablename WHERE condition;
. This command retrieves data from the table tablename
based on the specified condition.
To create a new MySQL user, use the CREATE USER
command: CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';
. This command creates a new user with the specified username and password.
To grant privileges to a user, use the GRANT
command: GRANT ALL PRIVILEGES ON dbname.* TO 'username'@'localhost';
. This command grants all privileges on the database dbname
to the user.
To revoke privileges from a user, use the REVOKE
command: REVOKE ALL PRIVILEGES ON dbname.* FROM 'username'@'localhost';
. This command removes all privileges on the database dbname
from the user.
To change a user's password, use the ALTER USER
command: ALTER USER 'username'@'localhost' IDENTIFIED BY 'new_password';
. This command changes the password for the specified user.
Transactions allow you to execute a series of SQL commands as a single unit of work. Use these commands for transaction management:
Start a transaction: START TRANSACTION;
Commit a transaction: COMMIT;
Rollback a transaction: ROLLBACK;
Indexes improve the speed of data retrieval operations on a table. Use these commands to manage indexes:
Create an index: CREATE INDEX indexname ON tablename (columnname);
Drop an index: DROP INDEX indexname ON tablename;
Views are virtual tables created based on the result set of an SQL query. Use these commands to manage views:
Create a view: CREATE VIEW viewname AS SELECT column1, column2 FROM tablename WHERE condition;
Alter a view: ALTER VIEW viewname AS SELECT column1, column2 FROM tablename WHERE condition;
Drop a view: DROP VIEW viewname;
Your email address will not be published. Required fields are marked *
Comments