Below is some basic MYSQL commands and concepts
Database-Level
DROP DATABASE databaseName -- Delete the database
DROP DATABASE IF EXISTS databaseName -- Delete if it exists
CREATE DATABASE databaseName -- Create a new database
CREATE DATABASE IF NOT EXISTS databaseName -- Create only if it does not exists
SHOW DATABASES -- Show all the databases in this server
USE databaseName -- Set the default (current) database
SELECT DATABASE() -- Show the default database
SHOW CREATE DATABASE databaseName -- Show the CREATE DATABASE statement
Table-Level
DROP TABLE [IF EXISTS] tableName, ...
CREATE TABLE [IF NOT EXISTS] tableName (
columnName columnType columnAttribute, ...
PRIMARY KEY(columnName),
FOREIGN KEY (columnNmae) REFERENCES tableName (columnNmae)
)
SHOW TABLES -- Show all the tables in the default database
DESCRIBE|DESC tableName -- Describe the details for a table
ALTER TABLE tableName ... -- Modify a table, e.g., ADD COLUMN and DROP COLUMN
ALTER TABLE tableName ADD columnDefinition
ALTER TABLE tableName DROP columnName
ALTER TABLE tableName ADD FOREIGN KEY (columnNmae) REFERENCES tableName (columnNmae)
ALTER TABLE tableName DROP FOREIGN KEY constraintName
SHOW CREATE TABLE tableName -- Show the CREATE TABLE statement for this tableName
Row-Level
INSERT INTO tableName
VALUES (column1Value, column2Value,...) -- Insert on all Columns
INSERT INTO tableName
VALUES (column1Value, column2Value,...), ... -- Insert multiple rows
INSERT INTO tableName (column1Name, ..., columnNName)
VALUES (column1Value, ..., columnNValue) -- Insert on selected Columns
DELETE FROM tableName WHERE criteria
UPDATE tableName SET columnName = expr, ... WHERE criteria
SELECT * | column1Name AS alias1, ..., columnNName AS aliasN
FROM tableName
WHERE criteria
GROUP BY columnName
ORDER BY columnName ASC|DESC, ...
HAVING groupConstraints
LIMIT count | offset count
-- Others
SHOW WARNINGS; -- Show the warnings of the previous statement
DDL, DML and DCL ?
DDL:- DDL stands for Data Definition Language.Its deals with database schemas and descriptions of
how the data should reside in the database.Example:- CREATE TABLE or ALTER TABLE,
DML:- DML stands for data manipulation Language.Its deals with SELECT, INSERT, UPDATE ,
DELETE etc.
DCL:- DCL stand for Data Control Language.Its contain commands like GRANT,Revoke etc.
Joins in MYSQL.
Joins are used in an MySql statement to query data from two or more tables, based on a relationship
between certain columns in these tables.
DISTINCT Statement in MySql.
In table some column may contain the duplicates value but you want to show only same value once
not again and again.Here DISTINCT keyword can be used to return only distinct (different) values not have
two same values.
Example:-
SELECT DISTINCT Column_name FROM table_name;
Difference between CHAR and VARCHAR.
CHAR column length is fixed to the length that is declared while creating table. The length value ranges
from 1 and 255.Varchar get space according to data entered in column.
How many columns can be used for creating Index.
Maximum of 16 indexed columns can be created for any table.
Can the database be renamed in MySQL?
No.
Default port for MySQL Server.
3306.
Common MySql Function.
NOW():- Returns the current date and time as one value.
CURRDATE() :- Returns the current date or time.
CURRTIME() :- Returns the current date or time.
CONCAT(X, Y) :- Concatenates two string values to create a single string output. Mostly used to
combine two or more fields into one single field.
DATEDIFF(X, Y) :- The difference between two dates.
Advantages of MyISAM over InnoDB.
Much more conservative approach to disk space management – each MyISAM table is stored in a
separate file, which could be compressed then with myisamchk if needed.
Each MyISAM table is stored on disk in three formats:-
1. The data file has a ‘.MYD’ (MYData) extension.
2. The ‘.frm’ file stores the table definition.
3. The index file has a ‘.MYI’ (MYIndex) extension.
Advantages of InnoDB over MyISAM.
Transactions
Row-level locking
Crash recovery
Foreign key constraints
What do _ and % mean inside LIKE statement?
_ is exactly one character.
% corresponds to 0 or more characters.
How can you see all indexes defined for a table.
SHOW INDEX FROM tablename;
Or you can use show create table tablename statement to see table structure along with indexes defined.
What are ENUMs used for in MySQL.
You can limit the possible values that go into the table.
e.g. :- CREATE TABLE months (month ENUM ‘January’, ‘February’, ‘March’,…);
Difference between NOW() and CURRENT_DATE().
NOW () command is used to show current year,month,date with hours,minutes and seconds.
CURRENT_DATE() shows current year,month and date only.
How would you change a table to InnoDB.
ALTER TABLE TABLENAME ENGINE = innodb;
How would you delete a column.
ALTER TABLE TABLENAME DROP COLUMNNAME.
Support us by sharing our content :-
