MySQL Commands

0.0(0)
Studied by 0 people
call kaiCall Kai
learnLearn
examPractice Test
spaced repetitionSpaced Repetition
heart puzzleMatch
flashcardsFlashcards
GameKnowt Play
Card Sorting

1/114

encourage image

There's no tags or description

Looks like no tags are added yet.

115 Terms

1
New cards

ADD

Adds a column to an existing table. Used within ALTER TABLE to define the new column and its data type.

2
New cards

ADD CONSTRAINT

Adds a constraint (such as PRIMARY KEY, FOREIGN KEY, or UNIQUE) to an existing table. Used within ALTER TABLE to enforce data integrity rules.

3
New cards

ALTER

Modifies an existing database object such as a table, view, or procedure. Commonly used as ALTER TABLE or ALTER DATABASE.

4
New cards

ALTER DATABASE

Changes the global characteristics of a database, such as the default character set or collation. Requires ALTER privilege on the database.

5
New cards

ALTER TABLE

Modifies the structure of an existing table by adding, removing, or changing columns, indexes, and constraints. One of the most versatile DDL commands in MySQL.

6
New cards

ALTER USER

Modifies an existing MySQL user account, including password changes and account locking. Used for managing user authentication settings.

7
New cards

ANALYZE TABLE

Analyzes and stores key distribution statistics for a table to help the query optimizer. Improves query performance by keeping table statistics up to date.

8
New cards

AUTO_INCREMENT

Automatically generates a unique number for a column when a new row is inserted. Commonly used for primary key columns to ensure uniqueness.

9
New cards

BEGIN

Starts a new transaction block, allowing multiple SQL statements to be executed atomically. Used interchangeably with START TRANSACTION.

10
New cards

BETWEEN

Filters results within a specified inclusive range of values. Can be used with numbers, text, or dates in a WHERE clause.

11
New cards

CALL

Executes a stored procedure that has been previously defined in the database. Allows passing input, output, and input/output parameters.

12
New cards

CHANGE

Renames a column and changes its definition within an ALTER TABLE statement. Unlike MODIFY, CHANGE requires specifying both the old and new column names.

13
New cards

CHECK

Defines a constraint that limits the values that can be stored in a column. MySQL enforces CHECK constraints starting from version 8.0.16.

14
New cards

CHECK TABLE

Checks a table for errors, such as incorrect key ordering or invalid checksums. Useful for diagnosing and verifying table integrity.

15
New cards

CHECKSUM TABLE

Returns a live or cached checksum of a table's contents. Useful for comparing two tables to determine if they have the same data.

16
New cards

COLUMN

Refers to a column within a table; used in various DDL and DML statements such as ALTER TABLE … DROP COLUMN or ADD COLUMN. Not a standalone command but a keyword used in conjunction with other commands.

17
New cards

COMMIT

Permanently saves all changes made during the current transaction. Once committed, changes cannot be rolled back.

18
New cards

CONSTRAINT

Defines a rule applied to a column or set of columns to enforce data integrity. Common constraints include PRIMARY KEY, FOREIGN KEY, UNIQUE, and CHECK.

19
New cards

CREATE

Creates a new database object such as a database, table, index, view, or stored procedure. It is one of the core DDL commands in MySQL.

20
New cards

CREATE DATABASE

Creates a new database with an optional specified character set and collation. The new database is empty until tables or other objects are added.

21
New cards

CREATE EVENT

Creates a scheduled event that executes a SQL statement or block of statements at a defined time or interval. Requires the Event Scheduler to be enabled.

22
New cards

CREATE FUNCTION

Creates a user-defined function (UDF) that returns a single value and can be used in SQL expressions. Functions can be stored in the database for reuse.

23
New cards

CREATE INDEX

Creates an index on one or more columns of a table to speed up data retrieval. Indexes can be unique or non-unique.

24
New cards

CREATE PROCEDURE

Creates a stored procedure, which is a set of SQL statements that can be called with the CALL command. Stored procedures support logic such as loops and conditionals.

25
New cards

CREATE TABLE

Creates a new table in the database with defined columns, data types, and constraints. This is the primary way to define the structure for storing data.

26
New cards

CREATE TRIGGER

Creates a trigger that automatically executes a defined SQL action before or after INSERT, UPDATE, or DELETE operations on a table. Useful for enforcing business rules automatically.

27
New cards

CREATE USER

Creates a new MySQL user account with specified authentication credentials. The new user has no privileges until explicitly granted.

28
New cards

CREATE VIEW

Creates a virtual table based on the result of a SELECT query. Views simplify complex queries and can restrict access to specific data.

29
New cards

DATABASE

Refers to a named collection of tables and other objects; used as a keyword in commands like CREATE DATABASE, DROP DATABASE, or SHOW DATABASES. Can also be used with the USE command to select the active database.

30
New cards

DEFAULT

Specifies a default value for a column when no value is provided during an INSERT. If not defined, the default is NULL unless the column is NOT NULL.

31
New cards

DELETE

Removes one or more rows from a table based on a specified condition. Without a WHERE clause, all rows in the table are deleted.

32
New cards

DELIMITER

Changes the statement delimiter in the MySQL client, commonly used when defining stored procedures, functions, or triggers. Allows multi-statement blocks to be entered without prematurely ending the definition.

33
New cards

DESCRIBE

Displays the structure of a table, including column names, data types, and constraints. It is a shorthand for SHOW COLUMNS FROM.

34
New cards

DISTINCT

Removes duplicate rows from the result set of a SELECT query. Applied directly after SELECT to return only unique combinations of values.

35
New cards

DROP

Deletes a database object such as a table, database, index, view, or procedure. This operation is irreversible and removes both the definition and data.

36
New cards

DROP COLUMN

Removes a column from an existing table using ALTER TABLE. All data in the removed column is permanently deleted.

37
New cards

DROP CONSTRAINT

Removes a named constraint from a table using ALTER TABLE. The specific syntax may vary depending on the constraint type.

38
New cards

DROP DATABASE

Permanently deletes an entire database including all its tables and data. This operation cannot be undone and should be used with extreme caution.

39
New cards

DROP DEFAULT

Removes the default value assigned to a column using ALTER TABLE. After this, the column will have no default unless a new one is set.

40
New cards

DROP INDEX

Removes an index from a table to free up storage or reflect changes in query patterns. Removing an index may affect query performance.

41
New cards

DROP TABLE

Permanently deletes a table and all of its data from the database. Unlike DELETE, DROP TABLE also removes the table structure.

42
New cards

DROP TRIGGER

Removes an existing trigger from the database. Once dropped, the trigger will no longer fire on the associated table events.

43
New cards

DROP USER

Removes a MySQL user account from the server. All privileges associated with the user are also removed.

44
New cards

DROP VIEW

Deletes an existing view from the database. The underlying tables and their data are not affected.

45
New cards

ENUM

Defines a column that can hold one value from a predefined list of permitted values. Useful for columns with a fixed set of possible options such as status or category.

46
New cards

EVENTS

Refers to scheduled MySQL events created with CREATE EVENT. Can be listed using SHOW EVENTS.

47
New cards

EXCEPT

Returns the rows from the first SELECT statement that are not present in the result of the second SELECT. MySQL introduced EXCEPT support in version 8.0.31.

48
New cards

EXISTS

A subquery operator that returns TRUE if the subquery produces at least one row. Often used in WHERE clauses with correlated subqueries.

49
New cards

EXPLAIN

Displays the query execution plan that MySQL uses for a SELECT, INSERT, UPDATE, or DELETE statement. Useful for diagnosing and optimizing slow queries.

50
New cards

FLUSH

Clears or reloads internal caches, logs, or tables in the MySQL server. Common uses include FLUSH PRIVILEGES, FLUSH LOGS, and FLUSH TABLES.

51
New cards

FOREIGN KEY

Defines a referential constraint linking a column in one table to the primary key of another table. Enforces relational integrity between related tables.

52
New cards

FROM

Specifies the table or tables from which data is retrieved in a SELECT, DELETE, or UPDATE statement. Can include joins to combine multiple tables.

53
New cards

FULL OUTER JOIN

Returns all rows from both tables, matching rows where possible and filling NULLs where there is no match. MySQL does not natively support FULL OUTER JOIN but it can be emulated with a UNION of LEFT JOIN and RIGHT JOIN.

54
New cards

GRANT

Assigns privileges to a MySQL user account on databases, tables, or other objects. Used to control access and permissions across the server.

55
New cards

GROUP BY

Groups rows with the same values in specified columns into summary rows. Commonly used with aggregate functions like COUNT, SUM, AVG, MAX, and MIN.

56
New cards

HANDLER

Provides direct access to table storage using a low-level interface, bypassing the query optimizer. Useful for sequential scanning or key-based lookups in performance-critical scenarios.

57
New cards

HAVING

Filters groups produced by GROUP BY based on a condition involving aggregate functions. Similar to WHERE, but applied after grouping.

58
New cards

IGNORE

Instructs MySQL to suppress errors and continue processing when duplicate key violations or other non-critical errors occur. Used with INSERT, UPDATE, or LOAD DATA statements.

59
New cards

IN

Checks whether a value matches any value in a specified list or subquery result. A concise alternative to multiple OR conditions in a WHERE clause.

60
New cards

INDEX

A database object that improves the speed of data retrieval on a table at the cost of additional storage and write overhead. Can be created with CREATE INDEX or defined inline in CREATE TABLE.

61
New cards

INNER JOIN

Returns only the rows that have matching values in both joined tables. It is the most commonly used type of join in SQL.

62
New cards

INSERT

Adds one or more new rows into a table. Values can be specified explicitly or derived from a SELECT query.

63
New cards

INSERT IGNORE

Inserts rows into a table and silently ignores rows that would cause a duplicate key error. Useful for bulk inserts where some duplicates are expected.

64
New cards

INSERT INTO SELECT

Inserts rows into a table using the result set of a SELECT query from another table. Useful for copying or transforming data between tables.

65
New cards

INTERSECT

Returns only the rows that appear in the result sets of both SELECT statements. MySQL introduced INTERSECT support in version 8.0.31.

66
New cards

IS NULL

Tests whether a column's value is NULL. Used in WHERE clauses to filter rows that have no value.

67
New cards

IS NOT NULL

Tests whether a column's value is not NULL. Used in WHERE clauses to exclude rows with missing values.

68
New cards

JOIN

Combines rows from two or more tables based on a related column. The default JOIN type in MySQL is INNER JOIN.

69
New cards

KILL

Terminates a running query or connection identified by its process ID. Requires appropriate privileges to kill connections owned by other users.

70
New cards

LEFT JOIN

Returns all rows from the left table and the matching rows from the right table, with NULL for unmatched right-side columns. Used when you want to include all records from the primary table regardless of matches.

71
New cards

LIKE

Searches for a specified pattern in a column using wildcard characters % (any sequence) and _ (single character). Commonly used in WHERE clauses for partial text matching.

72
New cards

LIMIT

Restricts the number of rows returned by a SELECT query. Optionally used with OFFSET to paginate results.

73
New cards

LOAD DATA INFILE

Reads data from a text file and inserts it into a table at high speed. Significantly faster than individual INSERT statements for bulk loading.

74
New cards

LOCK TABLES

Explicitly locks one or more tables for the current session to prevent concurrent modifications. Requires the LOCK TABLES privilege and must be paired with UNLOCK TABLES.

75
New cards

MODIFY

Changes the data type or attributes of an existing column within ALTER TABLE. Unlike CHANGE, MODIFY does not rename the column.

76
New cards

NOT NULL

A column constraint that prevents NULL values from being stored in that column. Enforces that a value must always be provided for the column.

77
New cards

OPTIMIZE TABLE

Reorganizes the physical storage of table data and associated indexes to reduce fragmentation. Can improve performance and reclaim unused disk space.

78
New cards

OR

A logical operator that returns TRUE if at least one of the conditions is true. Used in WHERE clauses to combine multiple filter conditions.

79
New cards

ORDER BY

Sorts the result set of a SELECT query by one or more columns in ascending (ASC) or descending (DESC) order. By default, the sort order is ascending.

80
New cards

PRIMARY KEY

A constraint that uniquely identifies each row in a table and does not allow NULL values. A table can have only one primary key, which may consist of one or more columns.

81
New cards

PROCEDURE

A stored routine that can perform operations and accept parameters but does not return a value directly like a function. Executed using the CALL command.

82
New cards

RENAME TABLE

Changes the name of one or more existing tables. Can also be used to move a table between databases on the same server.

83
New cards

REPAIR TABLE

Attempts to repair a corrupted MyISAM or ARCHIVE table. Not supported for InnoDB tables, which rely on automatic crash recovery.

84
New cards

REPLACE

Inserts a new row or replaces an existing row if a duplicate primary key or unique key conflict is detected. Functionally similar to DELETE followed by INSERT.

85
New cards

REVOKE

Removes previously granted privileges from a MySQL user account. The change takes effect immediately without requiring a server restart.

86
New cards

RIGHT JOIN

Returns all rows from the right table and the matching rows from the left table, with NULL for unmatched left-side columns. Less commonly used than LEFT JOIN.

87
New cards

ROLLBACK

Undoes all changes made during the current transaction and ends the transaction. Only affects changes made since the last COMMIT or the start of the transaction.

88
New cards

SAVEPOINT

Sets a named point within a transaction to which you can later roll back without affecting the entire transaction. Useful for partial rollbacks in complex transaction logic.

89
New cards

SELECT

Retrieves data from one or more tables based on specified criteria. The most frequently used SQL command, forming the basis of all data queries.

90
New cards

SELECT DISTINCT

Retrieves unique rows by eliminating duplicates from the result set. Equivalent to SELECT with the DISTINCT keyword applied.

91
New cards

SELECT INTO

Not natively supported in MySQL; use INSERT INTO … SELECT instead to copy data between tables. In MySQL, SELECT … INTO is used to assign query results to variables.

92
New cards

SELECT TOP

Not supported in MySQL; use LIMIT instead to restrict the number of rows returned. SELECT TOP is a SQL Server/MS Access syntax.

93
New cards

SET

Assigns values to user-defined or system variables, or modifies session-level settings. Also used within UPDATE statements to specify new column values.

94
New cards

SHOW

Displays information about databases, tables, indexes, users, status, or other server objects. A versatile administrative command with many variants such as SHOW TABLES and SHOW DATABASES.

95
New cards

SHOW COLUMNS

Lists all columns in a specified table along with their data types, nullability, and key information. Equivalent to the DESCRIBE command.

96
New cards

SHOW CREATE TABLE

Displays the CREATE TABLE statement that would recreate the specified table, including all columns, constraints, and options. Useful for documentation and schema migration.

97
New cards

SHOW DATABASES

Lists all databases available on the MySQL server that the current user has access to. A quick way to see the available schemas.

98
New cards

SHOW INDEXES

Displays all indexes defined on a specified table, including index name, column, and cardinality. Useful for auditing index coverage and performance tuning.

99
New cards

SHOW PROCESSLIST

Shows all active threads and queries currently running on the MySQL server. Useful for identifying long-running or blocked queries.

100
New cards

SHOW STATUS

Returns server status variables and their current values for monitoring MySQL performance. Can be filtered by GLOBAL or SESSION scope.