Mbbt. Sql tili

SQL operatorlari

• DDL(Data Definition Language) - Bu guruhga ma'lumotlar bazasida obyektlar(jadvallar, indekslar)ni hosil qiluvchi operatorlar kiradi.
• CREATE, ALTER, DROP
• DQL(Data Query Language) – Ma’lumotlarni bazadan olish uchun ishlatiladi.
• SELECT
• DML(Data Manipulation Language) – ma'lumotlarni manipulyatsiya qiluvchi operatorlar yig'indisi guruhi.
• INSERT, UPDATE, DELETE
• DCL(Data Control Language) – ma'lumotlarni boshqaruvchi operatorlar.
• GRANT, REVOKE

SQL ma’lumotlar toifalari

TINYINT	DATE
BIT, BOOL	DATETIME
SMALLINT	TIMESTAMP
MEDIUMINT	TIME
INT	YEAR
INTEGER	CHAR
BIGINT	BLOB, TEXT
FLOAT	ENUM
DOUBLE	SET
DECIMAL

Baza yaratish

• CREATE DATABASE my_db;
• USE my_db;
• mysql -h host -u user -p my_db

Jadval yaratish

• CREATE TABLE table_name (column_name column_type);
• create table tutorials_tbl(
• tutorial_id INT NOT NULL AUTO_INCREMENT,
• tutorial_title VARCHAR(100) NOT NULL,
• tutorial_author VARCHAR(40) NOT NULL,
• submission_date DATE,
• PRIMARY KEY ( tutorial_id )
• );

Jadvalni o’chirish

• DROP TABLE table_name ;

Jadvalga ma’lumot yozish

• INSERT INTO
• table_name ( field1, field2, ... fieldN )
• VALUES ( value1, value2, ... valueN );

Ma’lumotlarni o’qish

• SELECT field1, field2,...fieldN
• FROM table_name1, table_name2...
• [WHERE Clause]
• [OFFSET M ][LIMIT N]

Ma’lumotlarni o’zgartirish

• UPDATE table_name
• SET
• field1 = new-value1,
• field2 = new-value2
• [WHERE Clause]

Jadvaldan ma’lumot o’chirish

• DELETE FROM table_name
• [WHERE Clause]

Mavjud jadvalga yangi ustun qo’shish

ALTER TABLE table_name

ADD new_column_name column_definition

[ FIRST | AFTER column_name ];

ALTER TABLE table_name

ADD new_column_name column_definition

[ FIRST | AFTER column_name ],

ADD new_column_name column_definition

[ FIRST | AFTER column_name ],

...

;

Mavjud jadval ustunini o’zgartirish

ALTER TABLE table_name

MODIFY column_name column_definition

[ FIRST | AFTER column_name ];

ALTER TABLE table_name

MODIFY column_name column_definition

[ FIRST | AFTER column_name ],

MODIFY column_name column_definition

[ FIRST | AFTER column_name ],

...

;

Jadval ustunini o’chirish

ALTER TABLE table_name

DROP COLUMN column_name;

Jadval ustunining nomini o’zgartirish

ALTER TABLE table_name

CHANGE COLUMN old_name new_name column_definition

[ FIRST | AFTER column_name ]

Mavjud jadval nomini o’zgartirish

ALTER TABLE table_name

RENAME TO new_table_name;

Jadvalni o’chirish

DROP TABLE [ IF EXISTS ]

table_name1, table_name2, ...

[ RESTRICT | CASCADE ];

Jadvallarni bog’lash

FOREIGN KEY

[CONSTRAINT [symbol]] FOREIGN KEY

[index_name] (col_name, ...)

REFERENCES tbl_name (col_name,...)

[ON DELETE reference_option]

[ON UPDATE reference_option]

reference_option:

RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT

FOREIGN KEY yaratish

CREATE TABLE Orders (

OrderID int NOT NULL,

OrderNumber int NOT NULL,

PersonID int,

PRIMARY KEY (OrderID),

CONSTRAINT FK_PersonOrder

FOREIGN KEY (PersonID) REFERENCES Persons(PersonID)

);

ALTER TABLE Orders

ADD CONSTRAINT FK_PersonOrder

FOREIGN KEY (PersonID)

REFERENCES Persons(PersonID);

ALTER TABLE Orders DROP FOREIGN KEY FK_PersonOrder;