Showing posts with label Database and SQL. Show all posts
Showing posts with label Database and SQL. Show all posts

MySql get full name from last first mid name

Using CONCAT_WS to extract full name from first , middle and last name. 

It also handles the case that the mid_name can be empty or null or even have multiple spaces!


SELECT id, email,
    CASE WHEN mid_name IS NULL OR TRIM(mid_name) ='' THEN
        CONCAT_WS( " ", first_name, last_name )
    ELSE
        CONCAT_WS( " ", first_name, mid_name, last_name )
    END AS full_name
FROM USER;

mysql hibernate unicode support - character set, collate

I just did following configurations to achieve Unicode support in my Java+Hibernate+MySQL project.

Configuring MySQL to support Unicode - set Character Set and Collate as :
CREATE TABLE YOUR_DB_NAME
 CHARACTER SET "UTF8"
 COLLATE "utf8_general_ci";

NOTE : You Need to do "ALTER TABLE" instead of "CREATE TABLE", 
      if you are going to modify existing DB.

Hibernate JDBC connection string :
jdbc.url=jdbc:mysql://localhost:3306/YOUR_DB_NAME?useUnicode=true&characterEncoding=UTF-8

Hibernate Configuration:
<hibernate-configuration>
<session-factory>
    ...
    <property name="hibernate.connection.charSet">UTF-8</property>
    <property name="hibernate.connection.characterEncoding">UTF-8</property>
    <property name="hibernate.connection.useUnicode">true</property>
    ...
</session-factory>
</hibernate-configuration>

What's the difference between a primary key and a unique key?

Both primary key and unique enforce uniqueness of the column on which they are defined. But by default primary key creates a clustered index on the column, where unique creates a non-clustered index by default. Another major difference is that, primary key does not allow NULLs, but unique key allows one NULL only.

What is de-normalization and when would you go for it?

As the name indicates, de-normalization is the reverse process of normalization. It is the controlled introduction of redundancy in to the database design. It helps improve the query performance as the number of joins could be reduced.

What are clustered index and non clustered index


With a clustered index the rows are stored physically on the disk in the same order as the index. There can therefore be only one clustered index.
With a non clustered index there is a second list that has pointers to the physical rows. You can have many non clustered indexes, although each new index will increase the time it takes to write new records.
It is generally faster to read from a clustered index if you want to get back all the columns. You do not have to go first to the index and then to the table.
Writing to a table with a clustered index can be slower, if there is a need to rearrange the data.
In other words,
A clustered index means you are telling the database to store close values actually close to one another on the disk. This has the benefit of rapid scan / retrieval of records falling into some range of clustered index values.


From Wikipedia :
database index is a data structure that improves the speed of data retrieval operations on a database table at the cost of slower writes and increased storage space

SQL basics- complete reference guide - part8 SQL System Commands

Part8: SQL System Commands Reference guide

TOPICTEXTSYNTAXExample
ARRAY_GETReturns one element of an array.ARRAY_GET(arrayExpression, indexExpression)
ARRAY_LENGTHReturns the length of an array.ARRAY_GET(arrayExpression)
AUTOCOMMITReturns true if auto commit is switched on for this session.AUTOCOMMIT()
CANCEL_SESSIONCancels the currently executing statement of another session.CANCEL_SESSION(sessionInt)
CASEWHEN FunctionReturns 'a' if the boolean expression is true, otherwise 'b'.CASEWHEN(boolean, aValue, bValue)
CASTConverts a value to another data type.CAST(value AS dataType)
COALESCEReturns the first value that is not null.COALESCE(aValue, bValue [,...])
CONVERTConverts a value to another data type.CONVERT(value, dataType)
CURRVALReturns the current (last) value of the sequence, independent of the session.CURRVAL( [ schemaName, ] sequenceString )
CSVREADReturns the result set of reading the CSV (comma separated values) file.CSVREAD(fileNameString [, columnsString [, csvOptions ] ] )
CSVWRITEWrites a CSV (comma separated values).CSVWRITE ( fileNameString, queryString [, csvOptions [, lineSepString] ] )
DATABASEReturns the name of the database.DATABASE()
DATABASE_PATHReturns the directory of the database files and the database name, if it is file
based.
DATABASE_PATH()
FILE_READReturns the contents of a file.FILE_READ(fileNameString [,encodingString])
GREATESTReturns the largest value that is not NULL, or NULL if all values are NULL.GREATEST(aValue, bValue [,...])
IDENTITYReturns the last inserted identity value for this session.IDENTITY()
IFNULLReturns the value of 'a' if it is not null, otherwise 'b'.IFNULL(aValue, bValue)
LEASTReturns the smallest value that is not NULL, or NULL if all values are NULL.LEAST(aValue, bValue [,...])
LOCK_MODEReturns the current lock mode.LOCK_MODE()
LOCK_TIMEOUTReturns the lock timeout of the current session (in milliseconds).LOCK_TIMEOUT()
LINK_SCHEMACreates table links for all tables in a schema.LINK_SCHEMA(targetSchemaString, driverString, urlString,
userString, passwordString, sourceSchemaString)
MEMORY_FREEReturns the free memory in KB (where 1024 bytes is a KB).MEMORY_FREE()
MEMORY_USEDReturns the used memory in KB (where 1024 bytes is a KB).MEMORY_USED()
NEXTVALReturns the next value of the sequence.NEXTVAL ( [ schemaName, ] sequenceString )
NULLIFReturns NULL if 'a' is equals to 'b', otherwise 'a'.NULLIF(aValue, bValue)
READONLYReturns true if the database is read-only.READONLY()
ROWNUMReturns the number of the current row.ROWNUM()
SCHEMAReturns the name of the default  schema for this session.SCHEMA()
SCOPE_IDENTITYReturns the last inserted identity value for this session for the current scope.SCOPE_IDENTITY()
SESSION_IDReturns the unique session id number for the current database connection.SESSION_ID()
SETUpdates a variable with the given value.SET(@variableName, value)
TABLEReturns the result set.{ TABLE | TABLE_DISTINCT } ( { name dataType = expression } [,...] )
TRANSACTION_IDReturns the current transaction id for this session.TRANSACTION_ID()
USERReturns the name of the current user of this session.{ USER | CURRENT_USER } ()Select User();

SQL basics- complete reference guide - part7 - Date Time Functions

Part7: SQL Date Time Functions Reference

TOPICTEXTSYNTAXExample
CURRENT_DATEReturns the current date.{ CURRENT_DATE [ () ] | CURDATE() | SYSDATE | TODAY }
CURRENT_TIMEReturns the current time.{ CURRENT_TIME [ () ] | CURTIME() }
CURRENT_TIMESTAMPReturns the current timestamp.{ CURRENT_TIMESTAMP [ ( [ int ] ) ] | NOW( [ int ] ) }
DATEADDAdds units to a timestamp.DATEADD(unitString, addInt, timestamp)
DATEDIFFReturns the the number of crossed unit boundaries between two timestamps.{ DATEDIFF | TIMESTAMPDIFF } (unitString, aTimestamp, bTimestamp)
DAYNAMEReturns the name of the day (in English).DAYNAME(date)
DAY_OF_MONTHReturns the day of the month (1-31).DAY_OF_MONTH(date)
DAY_OF_WEEKReturns the day of the week (1 means Sunday).DAY_OF_WEEK(date)
DAY_OF_YEARReturns the day of the year (1-366).DAY_OF_YEAR(date)
EXTRACTReturns a specific value from a timestamps.EXTRACT ( { YEAR | YY | MONTH | MM | DAY | DD | DAY_OF_YEAR
    | DOY | HOUR | HH | MINUTE | MI | SECOND | SS | MILLISECOND | MS }
    FROM timestamp )
FORMATDATETIMEFormats a date, time or timestamp as a string.FORMATDATETIME ( timestamp, formatString
[ , localeString [ , timeZoneString ] ] )
HOURReturns the hour (0-23) from a timestamp.HOUR(timestamp)
MINUTEReturns the minute (0-59) from a timestamp.MINUTE(timestamp)
MONTHReturns the month (1-12) from a timestamp.MONTH(timestamp)
MONTHNAMEReturns the name of the month (in English).MONTHNAME(date)
PARSEDATETIMEParses a string and returns a timestamp.PARSEDATETIME(string, formatString
[, localeString [, timeZoneString]])
QUARTERReturns the quarter (1-4) from a timestamp.QUARTER(timestamp)
SECONDReturns the second (0-59) from a timestamp.SECOND(timestamp)
WEEKReturns the week (1-53) from a timestamp.WEEK(timestamp)
YEARReturns the year from a timestamp.YEAR(timestamp)

SQL basics- complete reference guide - part6 - String functions

Part6: SQL String Functions Complete Reference

TOPICTEXTSYNTAXExample
ASCIIReturns the ASCII value of the first character in the string.ASCII(string)
BIT_LENGTHReturns the number of bits in a string.BIT_LENGTH(string)
LENGTHReturns the number of characters in a string.{ LENGTH | CHAR_LENGTH | CHARACTER_LENGTH } ( string )
OCTET_LENGTHReturns the number of bytes in a string.OCTET_LENGTH(string)
CHARReturns the character that represents the ASCII value.{ CHAR | CHR } ( int )
CONCATCombines strings.CONCAT(string, string [,...])
DIFFERENCEReturns the difference between the sounds of two strings.DIFFERENCE(string, string)
HEXTORAWConverts a hex representation of a string to a string.HEXTORAW(string)
RAWTOHEXConverts a string to the hex representation.RAWTOHEX(string)
INSTRReturns the location of a search string in a string.INSTR(string, searchString, [, startInt])
INSERT FunctionInserts a additional string into the original string at a specified start position.INSERT(originalString, startInt, lengthInt, addString)
LOWERConverts a string to lowercase.{ LOWER | LCASE } ( string )
UPPERConverts a string to uppercase.{ UPPER | UCASE } ( string )
LEFTReturns the leftmost number of characters.LEFT(string, int)
RIGHTReturns the rightmost number of characters.RIGHT(string, int)
LOCATEReturns the location of a search string in a string.LOCATE(searchString, string [, startInt])
POSITIONReturns the location of a search string in a string.POSITION(searchString, string)
LPADLeft pad the string to the specified length.LPAD(string, int[, paddingString])
RPADRight pad the string to the specified length.RPAD(string, int[, paddingString])
LTRIMRemoves all leading spaces from a string.LTRIM(string)
RTRIMRemoves all trailing spaces from a string.RTRIM(string)
TRIMRemoves all leading spaces, trailing spaces, or spaces at both ends, from a string.TRIM ( [ { LEADING | TRAILING | BOTH } [ string ] FROM ] string )
REGEXP_REPLACEReplaces each substring that matches a regular expression.REGEXP_REPLACE(inputString, regexString, replacementString)
REPEATReturns a string repeated some number of times.REPEAT(string, int)
REPLACEReplaces all occurrences of a search string in a text with another string.REPLACE(string, searchString [, replacementString])
SOUNDEXReturns a four character code representing the sound of a string.SOUNDEX(string)
SPACEReturns a string consisting of a number of spaces.SPACE(int)
STRINGDECODEConverts a encoded string using the Java string literal encoding format.STRINGDECODE(string)
STRINGENCODEEncodes special characters in a string using the Java string literal encoding format.STRINGENCODE(string)
STRINGTOUTF8Encodes a string to a byte array using the UTF8 encoding format.STRINGTOUTF8(string)
SUBSTRINGReturns a substring of a string starting at a position.{ SUBSTRING | SUBSTR } ( string, startInt [, lengthInt ] )
UTF8TOSTRINGDecodes a byte array in the UTF8 format to a string.UTF8TOSTRING(bytes)
XMLATTRCreates an XML attribute element of the form "name=value".XMLATTR(nameString, valueString)
XMLNODECreate an XML node element.XMLNODE(elementString [, attributesString [, contentString]])
XMLCOMMENTCreates an XML comment.XMLCOMMENT(commentString)
XMLCDATACreates an XML CDATA element.XMLCDATA(valueString)
XMLSTARTDOCThe string "XMLSTARTDOC()
XMLTEXTCreates an XML text element.XMLTEXT(valueString)

SQL basics- complete reference guide - part5 - Mathematical Functions

Part5: Mathematical Functions in SQL- Complete Reference

FunctionDescriptionSYNTAXExample
ABSABS ( { int | long | decimal | double } )
ACOSACOS(double)
ASINASIN(double)
ATANATAN(double)
COSCOS(double)
COTCOT(double)
SINSIN(double)
TANTAN(double)
ATAN2ATAN2(double, double)
BITANDThe bitwise AND operation.BITAND(long, long)
BITORThe bitwise OR operation.BITOR(long, long)
BITXORThe bitwise XOR operation.BITXOR(long, long)
MODThe modulo operation.MOD(long, long)
CEILINGCEILING(double)
DEGREESDEGREES(double)
EXPEXP(double)
FLOORFLOOR(double)
LOGLOG(double)
LOG10LOG10(double)
RADIANSRADIANS(double)
SQRTSQRT(double)
PIPI()
POWERPOWER(double, double)
RANDCalling the function without parameter returns the next a pseudo random number.RAND( [ int ] )
RANDOM_UUIDReturns a new UUID with 122 pseudo random bits.RANDOM_UUID()
ROUNDRounds to a number of digits.ROUND(double, digitsInt)
ROUNDMAGICThis function rounds numbers in a good way, but it is slow.ROUNDMAGIC(double)
SECURE_RANDGenerates a number of cryptographically secure random numbers.SECURE_RAND(int)
SIGNReturns -1 if the value is smaller 0, 0 if zero, and otherwise 1.SIGN ( { int | long | decimal | double } )
ENCRYPTEncrypts data using a key.ENCRYPT(algorithmString, keyBytes, dataBytes)
DECRYPTDecrypts data using a key.DECRYPT(algorithmString, keyBytes, dataBytes)
HASHCalculate the hash value using an algorithm, and repeat this process for a number of iterations.HASH(algorithmString, dataBytes, iterationInt)
TRUNCATETruncates to a number of digits (to the next value closer to 0).TRUNCATE(double, digitsInt)
COMPRESSCompresses the data using the specified compression algorithm.COMPRESS(dataBytes [, algorithmString])
EXPANDExpands data that was compressed using the COMPRESS function.EXPAND(bytes)
ZEROReturns the value 0.ZERO()

SQL basics- complete reference guide - part4 - Aggregate Functions in SQL

Part4: Aggregate Functions in SQL- complete reference sheet

Command/FunctionDescriptionSYNTAXExample
AVGThe average (mean) value.AVG ( [ DISTINCT ] { int | long | decimal | double } )
BOOL_ANDReturns true if all expressions are true.BOOL_AND(boolean)
BOOL_ORReturns true if any expression is true.BOOL_OR(boolean)
COUNTThe count of all row, or of the non-null values.COUNT( { * | { [ DISTINCT ] expression } } )
GROUP_CONCATConcatenates strings with a separator.GROUP_CONCAT ( [ DISTINCT ] string
[ ORDER BY { expression [ ASC | DESC ] } [,...] ]
[ SEPARATOR expression ] )
MAXThe highest value.MAX(value)
MINThe lowest value.MIN(value)
SUMThe sum of all values.SUM( [ DISTINCT ] { int | long | decimal | double } )
SELECTIVITYEstimates the selectivity (0-100) of a value.SELECTIVITY(value)
STDDEV_POPThe population standard deviation.STDDEV_POP( [ DISTINCT ] double )
STDDEV_SAMPThe sample standard deviation.STDDEV_SAMP( [ DISTINCT ] double )
VAR_POPThe population variance (square of the population standard deviation).VAR_POP( [ DISTINCT ] double )
VAR_SAMPThe sample variance (square of the sample standard deviation).VAR_SAMP( [ DISTINCT ] double )

SQL basics- complete reference guide - part3 - Data Types in SQL

Part3: SQL Data Types Reference

TOPICTEXTSYNTAXExample
INT TypePossible values: -2147483648 to 2147483647.INT | INTEGER | MEDIUMINT | INT4 | SIGNED
BOOLEAN TypePossible values: TRUE and FALSE.BOOLEAN | BIT | BOOL
TINYINT TypePossible values are: -128 to 127.TINYINT
SMALLINT TypePossible values: -32768 to 32767.SMALLINT | INT2 | YEAR
BIGINT TypePossible values: -9223372036854775808 to 9223372036854775807.BIGINT | INT8
IDENTITY TypeAuto-Increment value.IDENTITY
DECIMAL TypeData type with fixed precision and scale.{ DECIMAL | NUMBER | DEC | NUMERIC } ( precisionInt [ , scaleInt ] )
DOUBLE TypeFloating point number.{ DOUBLE [ PRECISION ] | FLOAT | FLOAT4 | FLOAT8 }
REAL TypeSingle precision floating point number.REAL
TIME TypeThe format is hh:mm:ss.TIME
DATE TypeThe format is yyyy-MM-dd.DATE
TIMESTAMP TypeThe format is yyyy-MM-dd hh:mm:ss[.{ TIMESTAMP | DATETIME | SMALLDATETIME }
BINARY TypeRepresents a byte array.{ BINARY | VARBINARY | LONGVARBINARY | RAW | BYTEA } [ ( precisionInt ) ]
OTHER TypeThis type allows storing serialized Java objects.OTHER
VARCHAR TypeUnicode String.{ VARCHAR | LONGVARCHAR | VARCHAR2 | NVARCHAR
    | NVARCHAR2 | VARCHAR_CASESENSITIVE}  [ ( precisionInt ) ]
VARCHAR_IGNORECASE TypeSame as VARCHAR, but not case sensitive when comparing.VARCHAR_IGNORECASE [ ( precisionInt ) ]
CHAR TypeThis type is supported for compatibility with other databases and older
applications.
{ CHAR | CHARACTER | NCHAR } [ ( precisionInt ) ]
BLOB TypeLike BINARY, but intended for very large values such as files or images.{ BLOB | TINYBLOB | MEDIUMBLOB | LONGBLOB | IMAGE | OID } [ ( precisionInt ) ]
CLOB TypeCLOB is like VARCHAR, but intended for very large values.{ CLOB | TINYTEXT | TEXT | MEDIUMTEXT | LONGTEXT | NTEXT | NCLOB } [ ( precisionInt ) ]
UUID TypeUniversally unique identifier.UUID
ARRAY TypeAn array of values.ARRAY

SQL basics- complete reference guide - part2 - DDL

Part2: DDL -Data Definition Language Reference

CommandDescriptionSYNTAXExample
ALTER INDEX RENAMERenames an index.ALTER INDEX indexName RENAME TO newIndexName
ALTER SCHEMA RENAMERenames a schema.ALTER SCHEMA schema RENAME TO newSchemaName
ALTER SEQUENCEChanges the next value and the increment of a sequence.ALTER SEQUENCE sequenceName [ RESTART WITH long ] [ INCREMENT BY long ]
ALTER TABLE ADDAdds a new column to a table.ALTER TABLE tableName ADD name dataType [ DEFAULT expression ]
[ [ NOT ] NULL ] [ AUTO_INCREMENT | IDENTITY ] [ BEFORE columnName ]
ALTER TABLE ADD CONSTRAINTAdds a constraint to a table.ALTER TABLE tableName ADD constraint [ CHECK | NOCHECK ]
ALTER TABLE ALTERChanges the data type of a column, rename a column,
change the identity value, or change the selectivity.
ALTER TABLE tableName ALTER COLUMN columnName
{ { dataType [ DEFAULT expression ] [ [ NOT ] NULL ] [ AUTO_INCREMENT | IDENTITY ] }
    | { RENAME TO name }
    | { RESTART WITH long }
    | { SELECTIVITY int }
    | { SET DEFAULT expression }
    | { SET NULL }
    | { SET NOT NULL } }
ALTER TABLE DROP COLUMNRemoves a column from a table.ALTER TABLE tableName DROP COLUMN columnName
ALTER TABLE DROP CONSTRAINTRemoves a constraint or a primary key from a table.ALTER TABLE tableName DROP { CONSTRAINT [ IF EXISTS ] constraintName | PRIMARY KEY }
ALTER TABLE SETDisables or enables referential integrity checking for a table.ALTER TABLE tableName SET REFERENTIAL_INTEGRITY
    { FALSE | TRUE [ CHECK | NOCHECK ] }
ALTER TABLE RENAMERenames a table.ALTER TABLE tableName RENAME TO newName
ALTER USER ADMINSwitches the admin flag of a user on or off.ALTER USER userName ADMIN { TRUE | FALSE }
ALTER USER RENAMERenames a user.ALTER USER userName RENAME TO newUserName
ALTER USER SET PASSWORDChanges the password of a user.ALTER USER userName SET { PASSWORD string | SALT bytes HASH bytes }
ALTER VIEWRecompiles a view after the underlying tables have been changed or created.ALTER VIEW viewName RECOMPILE
ANALYZEUpdates the selectivity statistics of all tables.ANALYZE [ SAMPLE_SIZE rowCountInt ]
COMMENTSets the comment of a database object.COMMENT ON
{ { COLUMN [ schemaName. ] tableName.columnName }
    | { { TABLE | VIEW | CONSTANT | CONSTRAINT | ALIAS | INDEX | ROLE
    | SCHEMA | SEQUENCE | TRIGGER | USER | DOMAIN } [ schemaName. ] objectName } }
IS expression
CREATE AGGREGATECreates a new user-defined aggregate function.CREATE AGGREGATE [ IF NOT EXISTS ] newAggregateName FOR className
CREATE ALIASCreates a new function alias.CREATE ALIAS [ IF NOT EXISTS ] newFunctionAliasName [ DETERMINISTIC ]
{ FOR classAndMethodName | AS sourceCodeString }
CREATE CONSTANTCreates a new constant.CREATE CONSTANT [ IF NOT EXISTS ] newConstantName VALUE expression
CREATE DOMAINCreates a new data type (domain).CREATE DOMAIN [ IF NOT EXISTS ] newDomainName AS dataType
[ DEFAULT expression ] [ [ NOT ] NULL ] [ SELECTIVITY selectivity ]
[ CHECK condition ]
CREATE INDEXCreates a new index.CREATE { [ UNIQUE ] [ HASH ] INDEX [ IF NOT EXISTS ] newIndexName
    | PRIMARY KEY [ HASH ] }
ON tableName ( indexColumn [,...] )
CREATE LINKED TABLECreates a table link to an external table.CREATE [ [ GLOBAL | LOCAL ] TEMPORARY ] LINKED TABLE [ IF NOT EXISTS ]
name ( driverString, urlString, userString, passwordString,
[ originalSchemaString, ] originalTableString ) [ EMIT UPDATES | READONLY ]
CREATE ROLECreates a new role.CREATE ROLE [ IF NOT EXISTS ] newRoleName
CREATE SCHEMACreates a new schema.CREATE SCHEMA [ IF NOT EXISTS ] name [ AUTHORIZATION ownerUserName ]
CREATE SEQUENCECreates a new sequence.CREATE SEQUENCE [ IF NOT EXISTS ] newSequenceName [ START WITH long ]
[ INCREMENT BY long ] [ CACHE long ]
CREATE TABLECreates a new table.CREATE [ CACHED | MEMORY ] [ TEMP | [ GLOBAL | LOCAL ] TEMPORARY ]
TABLE [ IF NOT EXISTS ] name
{ { ( { columnDefinition | constraint } [,...] ) [ AS select ] }
    | { AS select } }
[ ENGINE tableEngineName ] [ TRANSACTIONAL ] [ NOT PERSISTENT ]
CREATE TRIGGERCreates a new trigger.CREATE TRIGGER [ IF NOT EXISTS ] newTriggerName { BEFORE | AFTER | INSTEAD OF }
{ INSERT | UPDATE | DELETE | SELECT | ROLLBACK } [,...] ON tableName [ FOR EACH ROW ]
[ QUEUE int ] [ NOWAIT ] CALL triggeredClassName
CREATE USERCreates a new user.CREATE USER [ IF NOT EXISTS ] newUserName
{ PASSWORD string | SALT bytes HASH bytes } [ ADMIN ]
CREATE VIEWCreates a new view.CREATE [ OR REPLACE ] [ FORCE ] VIEW [ IF NOT EXISTS ] newViewName
[ ( columnName [,...] ) ] AS select
DROP AGGREGATEDrops an existing user-defined aggregate function.DROP AGGREGATE [ IF EXISTS ] aggregateName
DROP ALIASDrops an existing function alias.DROP ALIAS [ IF EXISTS ] existingFunctionAliasName
DROP ALL OBJECTSDrops all existing views, tables, sequences, schemas, function aliases, roles,
user-defined aggregate functions, domains, and users (except the current user).
DROP ALL OBJECTS [ DELETE FILES ]
DROP CONSTANTDrops a constant.DROP CONSTANT [ IF EXISTS ] constantName
DROP DOMAINDrops a data type (domain).DROP DOMAIN [ IF EXISTS ] domainName
DROP INDEXDrops an index.DROP INDEX [ IF EXISTS ] indexName
DROP ROLEDrops a role.DROP ROLE [ IF EXISTS ] roleName
DROP SCHEMADrops a schema.DROP SCHEMA [ IF EXISTS ] schemaName
DROP SEQUENCEDrops a sequence.DROP SEQUENCE [ IF EXISTS ] sequenceName
DROP TABLEDrops an existing table, or a list of tables.DROP TABLE [ IF EXISTS ] tableName [,...] [ CASCADE | RESTRICT ]
DROP TRIGGERDrops an existing trigger.DROP TRIGGER [ IF EXISTS ] triggerName
DROP USERDrops a user.DROP USER [ IF EXISTS ] userName
DROP VIEWDrops an existing view.DROP VIEW [ IF EXISTS ] viewName [ RESTRICT | CASCADE ]
TRUNCATE TABLERemoves all rows from a table.TRUNCATE TABLE tableName

SQL basics- complete reference guide - part1 - DML

Part1: DML-Data Manipulation Language

CommandDescriptionSYNTAXExample
SELECTSelects data from a table or multiple tables.SELECT [ TOP term ] [ DISTINCT | ALL ] selectExpression [,...]
FROM tableExpression [,...] [ WHERE expression ]
[ GROUP BY expression [,...] ] [ HAVING expression ]
[ { UNION [ ALL ] | MINUS | EXCEPT | INTERSECT } select ] [ ORDER BY order [,...] ]
[ LIMIT expression [ OFFSET expression ] [ SAMPLE_SIZE rowCountInt ] ]
[ FOR UPDATE ]
INSERTInserts a new row / new rows into a table.INSERT INTO tableName [ ( columnName [,...] ) ]
{ VALUES { ( { DEFAULT | expression } [,...] ) } [,...] | [ DIRECT ] [ SORTED ] select }
UPDATEUpdates data in a table.UPDATE tableName [ [ AS ] newTableAlias ]
SET { columnName= { DEFAULT | expression } } [,...]
[ WHERE expression ]
DELETEDeletes rows form a table.DELETE FROM tableName [ WHERE expression ]
BACKUPBacks up the database files to a .BACKUP TO fileNameString
CALLCalculates a simple expression.CALL expression
EXPLAINShows the execution plan for a statement.EXPLAIN { [ PLAN FOR ] | ANALYZE } { select | insert | update | delete | merge }
MERGEUpdates existing rows, and insert rows that don't exist.MERGE INTO tableName [ ( columnName [,...] ) ]
[ KEY ( columnName [,...] ) ]
{ VALUES { ( { DEFAULT | expression } [,...] ) } [,...] | select }
RUNSCRIPTRuns a SQL script from a file.RUNSCRIPT FROM fileNameString [ scriptCompression ]
[ CIPHER cipher PASSWORD string ] [ CHARSET charsetString ]
SCRIPTCreates a SQL script from the database.SCRIPT [ SIMPLE ] [ NODATA ] [ NOPASSWORDS ] [ NOSETTINGS ]
[ DROP ] [ BLOCKSIZE blockSizeInt ]
[ TO fileNameString [ scriptCompression ]
    [ CIPHER cipher PASSWORD string ] ]
[ CHARSET charsetString ]
SHOWLists the schemas, tables, or the columns of a table.SHOW { SCHEMAS | TABLES [ FROM schemaName ] |
    COLUMNS FROM tableName [ FROM schemaName ] }