MySQL is a widely used and widely used open-source relational database management system (RDBMS). In this article, we discuss MySQL commands and provide a cheat sheet of common MySQL queries to help users work with MySQL more efficiently and effectively.
What is a MySQL database?
MySQL is an open source RDBMS developed by Oracle Corporation. It was originally developed and released by Swedish company MySQL AB on May 23, 1995.
MySQL works well and reliably with business intelligence (BI) applications, especially read-heavy BI applications. MySQL and InnoDB together provide excellent read/write speeds for OLTP scenarios and work well with high concurrency scenarios. In addition, MySQL offers two different editions, open source MySQL community server and Proprietary enterprise server.
MySQL works on many system platforms including Linux, Windows, macOS and so on. It is also one of the most stable database management systems, and some cloud platforms offer it “as a service”. Cloud-based MySQL services include Oracle MySQL Cloud Service, Amazon Relational Database Service, and Azure Database for MySQL.
Read: Relational Database Management Systems (RDBMS): MSSQL vs. MySQL
MySQL commands
MySQL uses commands to communicate with the MySQL database, creating queries with data and performing specific tasks and functions. The commands are statements coded in SQL (Structured Query Language) statements. Writing a query requires a set of predefined codes that the database can understand.
MySQL supports all SQL standard types of data in multiple categories, including numeric, date and time, string, and spatial data types. The string data types include character string and byte string. MySQL also implements spatial extensions as a subset of SQL with Geometry Types environment according to the Open Geospatial Consortium (OGC) specification.
MySQL Cheat Sheet
Below are some of the most commonly used MySQL commands and statements that help users work with MySQL more easily and effectively. In this article, we briefly introduce the most commonly used commands – including MySQL command line client commands – and the commands for working with databases, tables, indexes, views, triggers, procedures, and functions.
Read: Best database software for developers
MySQL command line client commands
Below is a list of MySQL command line client commands:
mysql-u [username] -P; # Connect to MySQL server mysql -u [username] -P [database]; # Connect to MySQL server exit; # Exit the mysql command line client mysqldump -u [username] -P [database] > data_backup.sql; # export data with mysqldump tool mysql> system clear; # Clear MySQL on-screen console for Linux
The MySQL command line client commands are available on Linux to clear the MySQL screen console window, and no client command is available on Windows operating systems.
MySQL commands for working with databases
Below are MySQL commands used to work with databases:
CREATE DATABASE [IF NOT EXISTS] database name; # create a database on the server SHOW DATABASE; # show all available databases USE database_name; # Use a database with a specific name DROP DATABASE [IF EXISTS] database name; # Delete a database with a specific name
MySQL commands for working with tables
Here are MySQL commands for working with tables in a database:
CREATE TABLE [IF NOT EXISTS] tablename(columnlist, …); # create a new table SHOW TABLES; # Show all tables in the database DROP TABLE [IF EXISTS] table name; # Delete a table from the database
Commonly used MySQL commands
Below is a list of the most commonly used MySQL commands for database developers and database administrators using MySQL databases:
TO CHANGE
ALTER TABLE ADD tablename [COLUMN] column name; ALTER TABLE table-name DROP [COLUMN] column name; ALTER TABLE table-name MODIFY column-name type; ALTER TABLE table-name MODIFY column-name type NOT NULL …; ALTER TABLE table-name CHANGE old-column-name new-column-name type; ALTER TABLE table-name CHANGE old-column-name new-column-name type NOT NULL …; ALTER TABLE table-name MODIFY column-name type FIRST; ALTER TABLE table-name MODIFY column-name type AFTER other-column; ALTER TABLE table_name CHANGE old_column_name new_column_name type FIRST; ALTER TABLE table_name CHANGE old_column_name new_column_name type AFTER another_column; ALTER TABLE table-name ALTER column-name SET DEFAULT …; ALTER TABLE table-name ALTER column-name DROP DEFAULT; ALTER TABLE table-name ADD new-column-name type; ALTER TABLE table_name ADD new_column_name type FIRST; ALTER TABLE table_name ADD new_column_name type AFTER another_column; ALTER TABLE tablename INDEX ADD [name](Pillar, …); ALTER TABLE table-name ADD PRIMARY KEY (column-name, …); ALTER TABLE table-name DROP PRIMARY KEY;
SELECT
SELECT * FROM tablename; SELECT * FROM table1, table2, …; SELECT columnname FROM tablename; SELECT column1, column2, … FROM tablename; SELECT Column1, Column2, … FROM Table1, Table2, …; SELECT select_list FROM table_name WHERE condition; SELECT select_list FROM table GROUP BY column1, column2, …; SELECT select_list FROM table GROUP BY column_name HAVING condition; CHOOSE QUANTITY [DESC]FROM tablename; SELECT DISTINCT (column_name) FROM table_name; SELECT select_list FROM table ORDER BY column name; SELECT select_list FROM table ORDER BY column1 ASC [DESC]column2 ASC
; SELECT column-name AS alias, expression AS alias, … FROM table-name; SELECT select_list FROM table_name WHERE column LIKE ‘%pattern%’; SELECT select_list FROM table_name WHERE column RLIKE ‘regular_expression’;
CHOOSE – PARTICIPATE
SELECT select_list FROM table1 INNER JOIN table2 ON condition; SELECT select_list FROM table1 LEFT JOIN table2 ON condition; SELECT select_list FROM table1 RIGHT JOIN table2 ON condition; SELECT select_list FROM table1 CROSS JOIN table2;
DESCRIBE
DESCRIBE table-name; DESCRIBE table-name column-name;
INSERT IN
INSERT INTO table (column_list) VALUES(value_list); INSERT INTO table (column_list) VALUES(list1), (list2), …;
TO UPDATE
UPDATE tablename SET column1 = value1, …; UPDATE table_name SET column_1 = value_1, … WHERE condition; UPDATE table1, table2 INNER JOIN table1 ON table1.column1 = table2.column2 SET column1 = value1, WHERE condition;
CLEAR
DELETE FROM tablename; DELETE FROM table_name WHERE condition; DELETE table1, table2 FROM table1 INNER JOIN table2 ON table1.column1= table2.column2 WHERE condition;
INDEX
CREATE INDEX index-name ON table-name (column, …); DROP INDEX index_name; CREATE UNIQUE INDEX index_name ON table_name (column,…);
OUTLOOK [IF NOT EXISTS] CREATE VIEW [IF NOT EXISTS] view_name AS select_statement; CREATE VIEW [IF EXISTS] view-name AS select_statement WITH CHECK OPTION; CREATE OR REPLACE view_name AS select_statement; DROP VIEW [IF EXISTS] view name; DROP VIEW [{FROM | IN } database_name] view1, view2, …; RENAME TABLE view_name TO new_view_name; SHOW FULL TABLES
WHERE table_type=”VIEW”;
DEDUCTION [{FROM | IN} database_name] [LIKE ‘pattern’ | WHERE search_condition]CREATE TRIGGER trigger_name {BEFORE | AFTER} {INSERT | UPDATE| DELETE } ON table_name FOR EACH ROW trigger_body; SHOW TRIGGER [IF EXISTS] ; DROP TRIGGER
trigger_name;
PROCEEDINGS [IF EXISTS] DELIMITER $$ CREATE PROCEDURE procedure name (parameter list) BEGIN body; END $$ DELIMITER; DROP PROCEDURE [LIKE ‘pattern’ | WHERE search_condition]procedure name; SHOW PROCEDURE STATUS
;
FUNCTION [NOT] DELIMITER $$ CREATE FUNCTION function name (parameter list) RETURNS data type [IF EXISTS] DETERMINISTIC BEGIN — statements END $$ DELIMITER; DROP FUNCTION [LIKE ‘pattern’ | WHERE search_condition]function name; SHOW FUNCTIONAL STATUS
;
Users and Permissions
CREATE USER ‘user’@’localhost’; GRANT ALL PRIVILEGES ON base.* TO ‘user’@’localhost’ IDENTIFIED BY ‘password’; GRANT SELECT, INSERT, DELETE ON base.* TO ‘user’@’localhost’ IDENTIFIED BY ‘password’; ALL PRIVILEGES WILL BE BASED ON.* FROM ‘user’@’host’; REMOVE ALL PRIVILEGES, GRANT OPTION FROM ‘user’@’host’; FLUSH PRIVILEGES; SET PASSWORD = PASSWORD(‘new_pass’); SET PASSWORD FOR ‘user’@’host’ = PASSWORD(‘new_pass’); SET PASSWORD = OLD_PASSWORD(‘new_pass’); DROP USER ‘user’@’host’;
Conclusion of the MySQL cheat sheet
MySQL has a reputation for being an extremely fast database for read-intensive workloads, and it excels at read-intensive processes. The MySQL cheat sheet contains the most commonly used commands and instructions to help MySQL database users to manage it more effectively and easily. Continue readingDatabase administration and database programming tutorials
.
