Mbbt. Sql tili

BETWEEN

SELECT firstName, lastName, officeCode

FROM employees

WHERE

officeCode BETWEEN 1 AND 3

ORDER BY officeCode;

LIKE

SELECT firstName, lastName

FROM employees

WHERE

lastName LIKE '%son’

ORDER BY firstName;

IN

SELECT firstName, lastName, officeCode

FROM employees

WHERE

officeCode IN (1 , 2, 3)

ORDER BY

officeCode;

IS NULL

SELECT lastName, firstName, reportsTo

FROM employees

WHERE

reportsTo IS NULL;

Solishtirish amallari

Operator	Description
=	Equal to. You can use it with almost any data types.
<> or !=	Not equal to
<	Less than. You typically use it with numeric and date/time data types.
>	Greater than.
<=	Less than or equal to
>=	Greater than or equal to

SELECT lastname, firstname, jobtitle

FROM employees

WHERE jobtitle <> 'Sales Rep';

SELECT lastname, firstname, officeCode

FROM employees

WHERE officecode > 5;

DISTINCT

SELECT

DISTINCT lastname

FROM employees

ORDER BY lastname;

SELECT

DISTINCT state, city

FROM customers

WHERE state IS NOT NULL

ORDER BY state, city;

DISTINCT AGGRIGATE

SELECT COUNT(DISTINCT state)

FROM customers

WHERE country = 'USA';

LIMIT

SELECT

DISTINCT state

FROM customers

WHERE

state IS NOT NULL

LIMIT 5;

MySql alias

SELECT

[column_1 | expression] AS descriptive_name

FROM table_name;

SELECT

[column_1 | expression] AS `descriptive name`

FROM table_name;

MySql table alias

table_name AS table_alias

SELECT * FROM employees e;

SELECT

e.firstName, e.lastName

FROM employees e

ORDER BY e.firstName;

JOIN

Realsion ma’lumotlar bazasida jadvallar umumiy ustun (FOREIGN KEY) orqali o’zaro bog’langan bo’ladi. Bitta jadvalda unga bog’langan boshqa jadvaldagi ma’lumotlarni to’g’ridan to’g’ri olib bo’lmaydi, buning uchun maxsus JOIN komandasidan foydalanish zarur.

SELECT column_list

FROM table_1

JOIN table_2 ON join_condition;

SELECT column_list

FROM table_1

JOIN table_2 USING (column_name);

JOIN turlari

INNER JOIN – ikkala jadvalda ham mavjud qatorlarni oladi

LEFT JOIN -

RIGHT JOIN

CROSS JOIN

INNER JOIN

Faqatgina ikkala jadvalda ham mavjud bo’lgan qatorlarni oladi. Birinchi jadval FK ustunini ikkinchi jadvalning barcha ustunlari bilan solishtiradi va mosini oladi.

SELECT

productCode,

productName,

textDescription

FROM products t1

INNER JOIN productlines t2

ON t1.productline = t2.productline;

INNER JOIN (2)

SELECT

orderNumber,

orderDate,

orderLineNumber,

productName,

quantityOrdered,

priceEach

FROM orders

INNER JOIN orderdetails USING (orderNumber)

INNER JOIN products USING (productCode)

ORDER BY

orderNumber,

orderLineNumber;

INNER JOIN (3)

?

INNER JOIN (4)

SELECT

orderNumber, productName, msrp, priceEach

FROM products p

INNER JOIN orderdetails o

ON p.productcode = o.productcode AND p.msrp > o.priceEach

WHERE

p.productcode = 'S10_1678';

LEFT JOIN

Bunda ikkita jadval qatnashadi va ular mos ravishda left-chap (t1) hamda right-o’ng (t2) jadval deb nomlanadi.

Bu bog’lanishda chap (t1) jadvaldagi barcha qatorlar olinadi, o’ng (t2) jadvalda esa faqatgina shartni qanoatlantiruvchi qatorlar olinadi, shart qanoatlantirmagan qatorlarga NULL qo’yiladi.

SELECT select_list

FROM

t1

LEFT JOIN

t2

ON join_condition;

USING (column_name);

LEFT JOIN (2)

SELECT

customers.customerNumber,

customerName,

orderNumber,

status

FROM customers

LEFT JOIN orders

ON orders.customerNumber = customers.customerNumber;

LEFT JOIN (3)

RIGHT, SELF, CROSS JOIN

Agrigat funksiyalar

• AVG
• COUNT
• SUM
• MAX
• MIN
• GROUP_CONCAT

Solishtirish funksiyalari

• COALESCE
• GREATEST & LEAST
• ISNULL

Sana bilan ishlash funksiyalari

Function	Description
CURDATE	Returns the current date.
DATEDIFF	Calculates the number of days between two DATE values.
DAY	Gets the day of the month of a specified date.
DATE_ADD	Adds a time value to date value.
DATE_SUB	Subtracts a time value from a date value.
DATE_FORMAT	Formats a date value based on a specified date format.
DAYNAME	Gets the name of a weekday for a specified date.
DAYOFWEEK	Returns the weekday index for a date.
EXTRACT	Extracts a part of a date.

LAST_DAY	Returns the last day of the month of a specified date
NOW	Returns the current date and time at which the statement executed.
MONTH	Returns an integer that represents a month of a specified date.
STR_TO_DATE	Converts a string into a date and time value based on a specified format.
SYSDATE	Returns the current date.
TIMEDIFF	Calculates the difference between two TIME or DATETIME values.
TIMESTAMPDIFF	Calculates the difference between two DATE or DATETIME values.
WEEK	Returns a week number of a date.
WEEKDAY	Returns a weekday index for a date.
YEAR	Return the year for a specified date

Matn bilan ishlash funksiyalari

Name	Description
CONCAT	Concatenate two or more strings into a single string
INSTR	Return the position of the first occurrence of a substring in a string
LENGTH	Get the length of a string in bytes and in characters
LEFT	Get a specified number of leftmost characters from a string
LOWER	Convert a string to lowercase
LTRIM	Remove all leading spaces from a string
REPLACE	Search and replace a substring in a string

RIGHT	Get a specified number of rightmost characters from a string
RTRIM	Remove all trailing spaces from a string
SUBSTRING	Extract a substring starting from a position with a specific length.
SUBSTRING_INDEX	Return a substring from a string before a specified number of occurrences of a delimiter
TRIM	Remove unwanted characters from a string.
FIND_IN_SET	Find a string within a comma-separated list of strings
FORMAT	Format a number with a specific locale, rounded to the number of decimals
UPPER	Convert a string to uppercase

Matematik funksiyalar

Name	Description
ABS()	Returns the absolute value of a number
CEIL()	Returns the smallest integer value greater than or equal to the input number (n).
FLOOR()	Returns the largest integer value not greater than the argument
MOD()	Returns the remainder of a number divided by another
ROUND()	Rounds a number to a specified number of decimal places.
TRUNCATE()	Truncates a number to a specified number of decimal places
ACOS(n)	Returns the arc cosine of n or null if n is not in the range -1 and 1.
ASIN(n)	Returns the arcsine of n which is the value whose sine is n. It returns null if n is not in the range -1 to 1.
ATAN()	Returns the arctangent of n.
ATAN2(n,m), ATAN(m,n)	Returns the arctangent of the two variables n and m
CONV(n,from_base,to_base)	Converts a number between different number bases
COS(n)	Returns the cosine of n, where n is in radians
COT(n)	Returns the cotangent of n.
SQRT(n)	Returns the square root of n
TAN(n)	Returns the tangent of n

CRC32()	Computes a cyclic redundancy check value and returns a 32-bit unsigned value
DEGREES(n)	Converts radians to degrees of the argument n
EXP(n)	Raises to the power of e raised to the power of n
LN(n)	Returns the natural logarithm of n
LOG(n)	Returns the natural logarithm of the first argument
LOG10()	Returns the base-10 logarithm of the argument
LOG2()	Returns the base-2 logarithm of the argument
PI()	Returns the value of PI
POW()	Returns the argument raised to the specified power
POWER()	Returns the argument raised to the specified power
RADIANS()	Returns argument converted to radians
RAND()	Returns a random floating-point value
SIGN(n)	Returns the sign of n that can be -1, 0, or 1 depending on whether n is negative, zero, or positive.
SIN(n)	Returns the sine of n