All notes for mysql computer lecture.

mysql.md

MySql Notes

Data Types

CHAR(n) - all characters should be n size. VARCHAR(n) - all characters can less than n size. TEXT - unlimited size. INT - integer. BIGINT - big integer. FLOAT - float. DOUBLE - double. DECIMAL - decimal. DATE - date. DATETIME - date time. TIMESTAMP - timestamp. TIME - time. YEAR - year. BOOL - boolean.

Column Attributes

NOT NULL - column can not be null. UNSIGNED - column is unsigned. AUTO_INCREMENT - column is auto increment. PRIMARY KEY - column is primary key. UNIQUE - column is unique. DEFAULT - column has default value.

Note: here < > and [ ] are used for formatting, you should ignore them when you write your query.

Database Manuplation

Creating.

CREATE DATABASE <database_name>;

Deleting.

DROP DATABASE <database_name>;

Show all present database.

SHOW DATABASES;

Show all tables in database.

SHOW TABLES;

Table Manuplation

Creating.

CREATE TABLE <table_name> (
    <column_name> <data_type> [NOT NULL] [UNSIGNED] [AUTO_INCREMENT] [PRIMARY KEY] [UNIQUE] [DEFAULT <value>]
    ,
    <column_name> <data_type> [NOT NULL] [UNSIGNED] [AUTO_INCREMENT] [PRIMARY KEY] [UNIQUE] [DEFAULT <value>]
);

Delete table.

DROP TABLE <table_name>;

Update table Name.

ALTER TABLE <table_name> RENAME TO <new_table_name>;

Adding new column.

ALTER TABLE <table_name> ADD <column_name> <data_type> [NOT NULL] [UNSIGNED] [AUTO_INCREMENT] [PRIMARY KEY] [UNIQUE] [DEFAULT <value>];

after.

This is used when you want to add new column after or before existing column.

ALTER TABLE <table_name> ADD <column_name> <data_type> [NOT NULL] [UNSIGNED] [AUTO_INCREMENT] [PRIMARY KEY] [UNIQUE] [DEFAULT <value>] AFTER <column_name>;

before.

ALTER TABLE <table_name> ADD <column_name> <data_type> [NOT NULL] [UNSIGNED] [AUTO_INCREMENT] [PRIMARY KEY] [UNIQUE] [DEFAULT <value>] BEFORE <column_name>;

Update Column Name.

ALTER TABLE <table_name> CHANGE <column_name> <new_column_name> <data_type> [NOT NULL] [UNSIGNED] [AUTO_INCREMENT] [PRIMARY KEY] [UNIQUE] [DEFAULT <value>];

Update Column Data Type.

ALTER TABLE <table_name> MODIFY <column_name> <data_type> [NOT NULL] [UNSIGNED] [AUTO_INCREMENT] [PRIMARY KEY] [UNIQUE] [DEFAULT <value>];

Delete Column.

ALTER TABLE <table_name> DROP <column_name>;

Insert Data.

INSERT INTO <table_name> ( <column_name>, <column_name>, <column_name> ) VALUES ( <value>, <value>, <value> );

Multiple Insert.

INSERT INTO <table_name> ( <column_name>, <column_name>, <column_name> ) VALUES ( <value>, <value>, <value> ), ( <value>, <value>, <value> );

Multiple Insert with multiple values.

INSERT INTO <table_name> ( <column_name>, <column_name>, <column_name> ) VALUES ( <value>, <value>, <value> ), ( <value>, <value>, <value> ), ( <value>, <value>, <value> );

Adding to All Columns.

INSERT INTO <table_name> VALUES ( <value>, <value>, <value> );

Adding to All Columns with multiple values.

INSERT INTO <table_name> VALUES ( <value>, <value>, <value> ), ( <value>, <value>, <value> ), ( <value>, <value>, <value> );

Functions:

String Methods

Function	Use Case
Lower(str)	converts the string to lowercase.
UPPER(str)	convert the string to uppercase.
REVERSE(str)	reverse the string.
LENGTH	gives the length of string.
TRIM(str)	trim the string.
LTRIM(str)	trim the string from left.
RTRIM(str)	trim the string from right.
REPLACE(str,old,new)	replace the old string with new string.
SUBSTRING(str,start,length)	gives the substring from start to length.
LOCATE(str,substr)	gives the position of substring.
INSTR(str,substr)	gives the position of substring.
LEFT(str, n)	gives the left n characters of string.
RIGHT(str, n)	gives the right n characters of string.
MID(str, start, length)	gives the substring from start to length.
CONCAT(str1,str2)	concatenates two strings.

Date and Time Methods

Function	Use Case
CURDATE()	gives the current date.
CURTIME()	gives the current time.
NOW()	gives the current date and time.
DAY()	gives the day of the month.
MONTH()	gives the month.
YEAR()	gives the year.
HOUR()	gives the hour.
MINUTE()	gives the minute.
SECOND()	gives the second.
DATE(date)	gives the date.
TIME(time)	gives the time.
DATETIME(datetime)	gives the date and time.
FROM_DAYS(days)	gives the date from days.
FROM_UNIXTIME(unixtime)	gives the date and time from unixtime.
DAYNAME(date)	gives the day name.

|MONTHNAME(date)|gives the month name.|

Math Methods

Function	Use Case
ABS(num)	gives the absolute value of number.
CEIL(num)	gives the ceiling of number.
FLOOR(num)	gives the floor of number.
ROUND(num)	gives the round of number.
ROUND(num,decimal)	gives the round of number with decimal.
RAND()	gives the random number.
SQRT(num)	gives the square root of number.
POW(num,power)	gives the power of number.
MOD(num,mod)	gives the mod of number.

Operators

Function	Use Case
!=	not equalto.
>	greater than.
>=	greater than equal to.
<	less than.
<=	less than equal to.
=	equal to.
LIKE	for pattern matching
BETWEEN	from low to high
IN	for in operator

LIKE OPERATOR

Function	Use Case
LIKE	for pattern matching
%s%	does it contains the "s".
%s	does it ends with the "s".
s%	does it starts with the "s".
s	does it equals the "s".
_s	does the first character of the string is "s".
s_	does the last character of the string is "s".
_ _ _ _	it should contain 4 words
_ _ _ s _	4th character should be "s"

Data dealing with rows

SELECT * FROM <table_name>;

Display only specific columns.

SELECT <column_name>, <column_name>, <column_name> FROM <table_name>;

Display conditional data.

SELECT * FROM <table_name> WHERE <column_name> = <value>;

Display conditional data with multiple values.

SELECT * FROM <table_name> WHERE <column_name> = <value> OR <column_name> = <value>;

Mixing two coloumns.

SELECT <column_name>, <column_name_2>+ <column_name_3> "<New Column>" FROM <table_name>;

ORDER BY.

for sorting data.

SELECT * FROM <table_name> ORDER BY <column_name>;

ORDER BY with multiple columns.

SELECT * FROM <table_name> ORDER BY <column_name>, <column_name>;

ORDER BY Descending.

SELECT * FROM <table_name> ORDER BY <column_name> DESC;

LIMIT.

limint on showing number of rows.

SELECT * FROM <table_name> LIMIT <number>;

LIMIT with offset.

SELECT * FROM <table_name> LIMIT <number>, <number>;

DISINCT.

getting unique values from column.

SELECT DISTINCT <column_name> FROM <table_name>;

GROUP BY.

Group by clause is nothing by grouping rows whoes values are same with respect to same column where.

Having.

LIKE.