Huntersg814

Mysql>

This prompt indicates ready for a new query.

->

Next line of a multi-line query

`>

Continues to wait for identifier completion beginning with.`

‘>

Continues to wait to the end of the string that was begun with ‘

“>

Continues to wait to the end of the string that was begun with “

/*>

Waits for the completion of a comment begun with /*

VARCHAR

A string of variable length up to 65535.

CHAR

A fixed-length character variable. T is declared with the number of characters. Ex. CHAR(15)

TEXT

Used to store long-form text strings. Generally used to store article bodies.

TINY TEXTTINYTEXT

It is used to store short strings of information. It stores up to 255 bytes or 255 characters along with 1 byte as overhead.

MEDIUMTEXT

It is useful for storing larger text strings like whitepapers, etc. These data objects can be of 16MB size.

LONGTEXT

It is used for storing extreme long text strings. This can be of 4GB size.

BLOB

These are binary strings which are treated as numeric values. They are used to store datafiles like images and videos.

BIT

Stores bit values.

BOOL | BOOLEAN

It holds either a 0 or a nonzero value. The value 0 is considered false, a non-zero value is considered TRUE.

TINYINT

It holds an integer value.

The range of a signed small integer is -128 to 127,

The unsigned field is 0-255.

SMALLINT

It holds an integer value.

The range of a signed small integer is -32768 to 32767,

The unsigned range is 0-65535

MEDIUMINT

It holds an integer value.

The range of a signed small integer is -8388608 to 8388607,

The unsigned range is 0-16777215

INT

Used to store an integer value.

The range of a signed small integer is -2147483648 to 2147483647,

The unsigned range is 0-4294967295

BIGINT

It is used to hold a big integer.

Its signed range is -2E63 to 2E63 -1.

The unsigned range is 0 to 2E64-1.

FLOAT

It is a single precision floating-point number. Its permissible values are -3.402823466E+38 to -1.175494351E-38, 0,

and 1.175494351E-38 to 3.402823466E+38.

DOUBLE

It is a double precision floating-point number. Its permissible values are -1.7976931348623157E+308 to -2.2250738585072014E-308, 0, and 2.2250738585072014E-308 to 1.7976931348623157E+308.

DECIMAL

Used to store an exact fixed-point decimal value. The maximum number of digits is 65, while the maximum number of the decimal is 30.

ENUM

It is a string object. Its value is selected from a list of benefitsvalues. It uses numeric indexes to represent string values.

How to convert aae to jpg. JSON

Defines JSON datatype to store JSON documents.

SET

It is a string object that enables us to store zero or more values from a list of pre-specified values when the table is created.

DATE

Renders a datatype as a date value. – YYYY-MM-DD

DATETIME

It is a combination of datetime. It displays datetime values in YYYY-MM-DD hh:mm:ss format.

TIMESTAMP

This datatype holds a combination date and time within the range 1970-01-01 00:00:00 UTC to 2038-01-19 03:14:07 UTC

TIME

Returns the current time.

TIMEDIFF()

Calculates the difference between two in terms of time between two time or datetime values.

TIMESTAMPDIFF()

Returns the difference between two date or date-time values.

CURDATE()

Gives the current date.

NOW()

Returns the date and time of statement execution.

DATADIFF()

Returns the number of days between the two dates

DATE_FORMAT()

Reformats a date value based on the specified format.

DAY()

Returns the day of the specified date.

DAYNAME()

Returns the day name for the specified date.

DAYOFWEEK()

Returns the day of the week index for a specified date.

MONTH()

Returns the month number of the specified date.

STR_TO_DATE()

Converts a string into date-time based on a specified format.

SYSDATE()

Returns the system configured date.

WEEK()

It returns the week number of a specified date.

WEEKDAY()

Returns the index of the weekday of a specified date.

YEAR()

Returns the year from a specified date.

CREATE

Used to create a new table

INSERT

This command is used to insert one or more rows of data into the table.

UPDATE

Used to modify existing data in a table.

The WHERE command here is optional here.

SELECT

Used to select list values from a table.

SELECT DISTINCT

Used to select only unique values from a list.

WHERE

The WHERE clause allows to select values based on specified conditions.

ORDER BY

The ORDER BY clause is used to sort the queried result set in either ascending or Descending Order. By default, it will be Ascending Order.

AND

This is a logical operator. It is mainly used with the WHERE clause. Here, the query statement is executed only when both the expressions are true.

OR

This is a logical operator. It is mainly used with the WHERE clause. Here, the query statement is executed only when one or both the expressions are true.

IN

The IN operator is used with the WHERE clause. It enables to determine if a specified value in a list matches in another set of values.

BETWEEN

This operator is also used with the WHERE clause. It is used to specify whether a value is in a specified range.

LIMIT

This clause is used with SELECT statement to specify the number of rows to be returned.

IS NULL

It is used to test if a value is NULL or not. If it is NULL, the expression returns true, else false.

INNER JOIN

This is a filter clause that matches each row in one table with every row in the other table thus enabling to query only those rows that have corresponding columns from both tables.

LEFT JOIN

It allows you to query data from multiple tables. It matches each row from the first table to each row in the second table on the join_condition

Type vietnamese on mac. RIGHT JOIN

Same as LEFT_JOIN except that the table manipulation is in reverse order.

It matches each row from the second table to each row in the first table on the join_condition

CROSS JOIN

It returns the cartesian product of rows from the joined tables.

GROUP BY

This command enables to group rows into subgroups based on column or expression values.

HAVING

It is generally used with the GROUP BY clause. It is used to specify filter conditions for group of rows.

ROLL UP

Used to generate the subtotals as well as grandtotals of fieldvalues.

EXISTS

It is a Boolean operator that returns either true or false. It is generally used to determine if a query/subquery has returned any number of rows.

INTERSECT

This is a set operator which returns only distinct rows of two or more queries.

UNION

This command combines two or more result sets from multiple SELECT queries and returns a single result set.

UPDATE JOIN

JOIN clauses when used with the UPDATE statement is called as UPDATE JOIN.

DELETE

This command is used to delete data from table.

DELETE JOIN

This command is used to delete data from multiple tables using the JOIN statement.

ON DELETE CASCADE

This command enables deleting data from child table automatically when data from master table is deleted.

REPLACE

This command is used to inert or update data in a table.

ASCII()

Returns the ASCII value of the left most character of the string passed as an argument.

BIN()

Returns the string representation of the binary value of the number passed as an argument.

CHARACTER_LENGTH() | CHAR_LENGTH()

Returns the length of the string passed as an argument.

LOWER()

Changes all the characters of the argument string into lower case.

UPPER()

Changes all the characters of the argument string into lower case.

CONCAT()

Appends one string at the end of the other string. Any number of string’s can be passed as an argument. If any argument is NULL, then the function will return NULL.

LENGTH()

Returns the length of the string.

LEFT()

Returns a specified number of characters from left most side of the argument string.

RIGHT()

Returns a specified number of characters from right most side of the argument string.

TRIM()

Removes all leading and trailing spaces from a string.

LTRIM()

Removes all leading space from a character string.

RTRIM()

Removes all trailing space from a character string.

FORMAT()

Formats a number as ‘#,###,###.##’, rounded to a specified number of decimal places and returns the result as a string.

SUBSTRING() | SUBSTR()

Returns a substring from an argument string, starting from a specified position, of a specified length.

SUBSTRING_INDEX()

Returns a substring from an argument string before a ‘count’ number of occurrences of a specified delimiter. If the ‘count’ specified in the argument is positive, then all characters left of the string is returned, if it is negative, then all characters right of the string is returned. Further, it performs a case sensitive search for the specified delimiter.

STRCMP()

It compares two strings passed in the argument and returns 0 if both are equal, -1 if the first string is smaller than the second and 1 if the first string is greater than the second.

LIKE()

This command is used to match a string with a pattern on a per character basis. It returns 1 if there is a pattern, and 0 if there is no pattern.

POSITION() |LOCATE()

Returns the position of a substring in a string.

REVERSE()

Reverses the order of the string argument.

REGEXP()

Returns 1 if the string argument matches a specified pattern, else returns 0.

LOAD_FILE()

Returns the contents of a file as a string.

ABS()

Returns the absolute value of a number passed to it as an argument

TRUNCATE()

TRUNCATE(number_to be truncated, number of decimal places to be truncated to)

It trims a number to a specified number of decimal places passed to it as an argument.

ROUND()

It rounds a number to a specified number of decimal places passed to it as an argument.

MOD()

Returns the remainder of a number after dividing it with another number.

Geto boys allmusic. CEIL() | CEILING()

It returns the integer value which is equal to or is the next greater integer value of the specified number. Ex CEIL(3.5) – Returns 4.

CONV()

Converts a number of one base to a number of another base.

FLOOR()

It returns the integer value which is equal to or is the next smaller integer value of the specified number.

Ex FLOOR(3.15) – Returns 3.

FLOOR(-3.15) – Returns -4.

PI()

Returns the value of PI.

RAND()

Returns a random floating-point number within the range 0 - 1.

SIGN()

Returns the sign of a number passed as an argument.

SQRT()

Returns the square root of a number passed as an argument.

RADIANS()

Returns an argument number in terms of radians

MIN()

Returns the minimum value in a set of values.

Here, ‘marks’ refers to the field name.

MAX()

Returns the maximum value in a set of values.

Here, ‘marks’ refers to the field name.

CONNECTION_ID()

Returns the Connection ID for a particular connection

CURRENT_USER()

It returns the combination of the username and the hostname used by the MySQL account server to authenticate the current user.

DATABASE()

Returns the default database name.

SCHEMA()

Same as DATABASE()

FOUND_ROWS()

Returns the number of rows found by a query without actually running the query. In order to refer to this value, later on, it needs to be saved.

ROW_COUNT()

It returns the number of affected rows following a statement execution.

LAST_INSERT_ID()

It returns a 64-bit value which is an automatically generated value that has been successfully inserted for an AUTO_INCREMENT column. This value remains unchanged if no new rows are inserted successfully.

USER(), SESSION_USER()

SYSTEM_USER

Returns the MySQL username and Hostname.

VERSION()

Returns the MySQL Version number.

CHARSET()

Returns the character set of the string argument.

ENCRYPT()

Returns a binary string.

DECODE()

Decodes or decrypts an encrypted string.

MD5()

It calculates an MD5 128-bit checksum for the string and returns a string of 32 hexadecimal digits.

PASSWORD()

Uses a cleartext password str to return a hashed password string.

COMPRESS()

This command is used to compress a string and returns a binary string.

UNCOMPRESS()

This command is used to uncompress a string that is compressed using the COMPRESS() command.

SLEEP()

This command is used to pause for a specified number of seconds in the argument.

DEFAULT()

Returns the default value for a field. If no default value is specified, it returns an error.

Show databases;

To list all the existing databases

Create Database <database_name>

Creates a database of name <database_name>

Use <database_name>

Selects the <database_name> to be used.

Grant all on <database_name> to <mysql_user> @ <client_host>

Used to grant all permission on <database_name> to the mysql_user and the host_server

Drop <database_name>

Delete database_name

mysql -h host_name -u user_name -p

Here the host_name refers to the name of the host where MySQL server is running.

User_name is the user name of the MySQL account.

QUIT | q

Disconnects from the server.

Included in the dbconfig.php file.

It contains all the configured parameters for database configuration.

This file can be named as phpmysqlconnect.php. Here the code for establishing database connection has been included.

More code can be included here in order to handle exceptions in case the connection is not successful.

Pip

Enables installation of MySQL Python connector on any Operating System. This includes Linux, Unix, macOS & Windows.

This command will enable you to uninstall the current MySQL Connector/Python.

Connect()

Here the mysql.connector and Error objects are first imported from the Python package.

The connect() function is used to connect to the MySQL server.

The connect() function needs specification for 4 parameters. They are:

• Host
• Database
• User
• Password

The is_connected() function is used to check if the connection has been established successfully.

Close() function is used to close the database connection.

Imports the mysql module

Connects to the MySQL Database by calling the creatconnection() method.

The end() method allows the execution of all remaining queries before closing the database connection.

Connection.destroy()

This method is used to close the connection immediately. It further does not allow any triggers for the connection.

The three classes Connection, Driver Manager and SQL Exception need no be imported into the from the java.sql.*package.

The getConnection() method is required to get the Connection Object.