Создание базы данных

Создание БД в mySQL

• CREATE DATABASE database-name;

Пример

CREATE DATABASE Music albums ;

Для использования созданной БД должны ссылаться на ее

USE database-name;

Пример

USE Music albums ;

В начале каждой работы должны указать с какой БД работать

Создание таблицы в mySQL

• CREATE TABLE table-name (
• col-name-1 col-def-1,
• col-name-2 col-def-2,
• :
• col-name-n col-def-n,
• constraint-1,
• :
• constraint-k

)

Типы строк

• col-name type
• [NULL | NOT NULL]
• [DEFAULT default_value]
• [NOT NULL | NULL]
• [AUTO_INCREMENT]
• [UNIQUE [KEY] |
• [PRIMARY] KEY]

Типы данных

• Numeric Types

TINYINT, SMALLINT, INT, MEDIUMINT, BIGINT

FLOAT, REAL, DOUBLE, DECIMAL

• Dates and Times

DATE, TIME, YEAR

• Strings

CHAR, VARCHAR

Пример создание таблицы

CONSTRAINT name

PRIMARY KEY

(col1, col2, …)

CONSTRAINT name

UNIQUE

(col1, col2, …)

CREATE TABLE Module (

mCode CHAR(6) NOT NULL,

mCredits TINYINT NOT NULL DEFAULT 10,

mTitle VARCHAR(100) NOT NULL,

CONSTRAINT mod_pk

PRIMARY KEY (mCode)

);

Foreign Key

CONSTRAINT name FOREIGN KEY

(col1, col2, ...) REFERENCES table-name (col1, col2, ...)

ON DELETE ref_opt

ON UPDATE ref_opt

CASCADE – вводить изменения

Таблица Enrolment

CREATE TABLE Enrolment (

sID INT NOT NULL,

mCode CHAR(6) NOT NULL,

CONSTRAINT en_pk

PRIMARY KEY (sID, mCode),

CONSTRAINT en_fk1

FOREIGN KEY (sID)

REFERENCES Student (sID)

ON DELETE CASCADE

ON UPDATE CASCADE,

CONSTRAINT en_fk2

FOREIGN KEY (mCode)

REFERENCES Module (mCode)

ON DELETE NO ACTION

ON UPDATE CASCADE

);

Создание таблицы библиотеки компакт – дисков

Создание таблиц Artist, CD

CREATE TABLE Artist (

artID INT NOT NULL AUTO_INCREMENT,

artName VARCHAR(255) NOT NULL,

CONSTRAINT pk_artist PRIMARY KEY (artID),

CONSTRAINT ck_artist UNIQUE (artName)

);

CREATE TABLE CD (

cdID INT NOT NULL AUTO_INCREMENT,

artID INT NOT NULL,

cdTitle VARCHAR(255) NOT NULL,

cdPrice REAL NOT NULL,

cdGenre VARCHAR(255) NULL,

cdNumTracks INT NULL,

CONSTRAINT pk_cd PRIMARY KEY (cdID),

CONSTRAINT fk_cd_art FOREIGN KEY (artID) REFERENCES Artist (artID) ON DELETE CASCADE ON UPDATE CASCADE

);

Создание таблиц Track

• CREATE TABLE Track (
• trID INT NOT NULL AUTO_INCREMENT,
• cdID INT NOT NULL,
• trTitle VARCHAR(255) NOT NULL,
• trTime INT NULL,
• CONSTRAINT pk_tr PRIMARY KEY (trID),
• CONSTRAINT fk_tr_cd FOREIGN KEY (cdID) REFERENCES CD (cdID) ON DELETE CASCADE ON UPDATE CASCADE
• );

Introduction

• SELECT * FROM Artist;
• SELECT * FROM CD;
• SELECT * FROM Track;

Для просмотра созданных таблиц

Manipulations

• INSERT добавляет новую строку (запись) в таблицу

INSERT INTO Artist (id, name) VALUES (1, 'Justin Bieber');

INSERT INTO Artist (id, name) VALUES (2, 'Beyonce Knowles');

INSERT INTO Artist (id, name) VALUES (3, 'Jeremy Lin');

INSERT INTO celebs (id, name) VALUES ( 4,'Taylor Swift');

SELECT name FROM Artist;

Select

• SELECT * name FROM Artist;

SELECT – это операция, указывающее, что оператор является запросом. Вы будете использовать SELECT каждый раз, когда вы запрашиваете данные из базы данных.

name определяет столбец БД по котором идет запрос

FROM Artist – определяет с кокой таблицей в БД нужно делать запрос

* - специальный характер который позволяет выбрать все столбцы в таблице не обращаясь к каждому индивидуально

UPDATE

UPDATE Artist

SET name = Enrique Iglesias WHERE id = 1;

UPDATE – позволяет редактировать записи в таблице

SET - указывает на редактируемый столбец

WHERE - определяет номер строки который нужно редактировать

Добавление нового столбца

ALTER TABLE Artist ADD COLUMN age INT;

ALTER TABLE – позволяет сделать определенные изменения

ADD COLUMN – позволяет создать столбец в таблице

Задание : Обновите таблицу, чтобы включить age для каждого артиста

Delete

• DELETE FROM Artist WHERE age IS NULL ;

DELETE FROM – позволяет удалить один или несколько строк в таблице

WHERE – идентифицирует какой столбец нужно удалить

Задание: удалить строки со столбца age , где они не пустые

6 часто используемых команд для создания и манипулирования данными в БД

• CREATE TABLE   создание таблицы .
• INSERT INTO   добавить новую строку .
• SELECT   запрос с БД .
• UPDATE   обновление или редактирование .
• ALTER TABLE   изменение существующей таблицы .
• DELETE FROM   удаление строк в таблице .

70; =  равно !=  не равно   больше чем =  больше чем или равно " width="640"

WHERE

• Утверждение которое фильтрует результат по условию

Пример

SELECT * FROM CD WHERE Price 70;

• =  равно
• !=  не равно
•   больше чем
• =  больше чем или равно

Like-I

• Является полезной командой, когда есть необходимость сравнивать похожие значения
• SELECT * FROM Artist WHERE name LIKE ‘J_n';

Like-II

• Является полезной командой, когда есть необходимость сравнивать похожие значения

1) SELECT * FROM movies WHERE name LIKE 'a%‘ ;

2) SELECT * FROM movies WHERE name LIKE '%man%‘ ;

Like-II

SELECT * FROM movies WHERE name LIKE 'a%';

Это выражние фильтрует все значения name в таблице movies которые начинаются с букв “А”

«%» – это символ который соответствует нулевому или более недостающих букв в шаблоне

SELECT * FROM movies WHERE name LIKE '%man%';

Можно использовать символ % до и после шаблона. Это означает что выражение будет фильтровать значения содержащие слово “man”

Between

• Это команда которая фильтрует результат в некотором отрезке. Это м/б текст, числа или даты

SELECT * FROM movies WHERE year BETWEEN 1990 AND 2000;

SELECT * FROM movies WHERE name BETWEEN 'A' AND 'J' ;

Найти треки стоимость которых между 100 и 300 тенге

And

• SELECT * FROM movies WHERE year BETWEEN 1990 AND 2000 AND genre = 'comedy' ;

AND – объединяет два условия

OR

• SELECT * FROM movies WHERE genre = 'comedy' OR year

• Оператор который фильтрует результат только для тех строк в которых условие справедливо

Результат

Order By

• Можно сортировать базу используя Order By . Сортировка результатов часто бывает полезной и проще анализируемой

• SELECT * FROM movies ORDER BY imdb_rating DESC ;
• ORDER BY означает что нужно сортировать базу по определенному столбцу или в алфавитном порядке.
• DESC зарезервированное слово которое используется вместе с ORDER BY для сортировки результата. Descending order  (high to low или Z-A)

• Также возможно сортировка по ascending order . ASC – это зарезервированное слово которое используется вместе с ORDER BY для сортировки результата. (low to high or A-Z).

Результат

• SELECT * FROM movies ORDER BY imdb_rating DESC ;
• select * from movies order by imdb_rating ASC LIMIT 3 ;

Limit

Определяет максимальное количество строк которое необходимо

Обобщение

• SELECT  is the clause you use every time you want to query information from a database.
• WHERE  is a popular command that lets you filter the results of the query based on conditions that you specify.
• LIKE  and  BETWEEN  are special operators that can be used in a  WHERE  clause
• AND  and  OR  are special operators that you can use with WHERE  to filter the query on two or more conditions.
• ORDER B Y lets you sort the results of the query in either ascending or descending order.
• LIMIT  lets you specify the maximum number of rows that the query will return. This is especially important in large tables that have thousands or even millions of rows.

Задание

Создать SQL запрос которая выполняет следующее:

1) находит из таблицы дисков всю информацию жанр которого является ‘Rock’

2) Выводит строки в таблице дисков, где цена меньше 100

3) Выводит строки где жанр не равно Pop

4) Выводит лист Cd цена которой меньше или равно 70

5) Выводит лист Cd цена которой больше или равно 100 или жанр равное ‘Rock’

6) Находит список названии дисков где цена должна быть между 80 и 120

7) Выводит список названии дисков где имя диска содержится фрагмент ‘eve’

8) Ввести список треков которые начинаются с буквы D

9) Сортировать столбец название Артиста по алфавитному порядку

10) Сортирует столбец цена по меньшей к большему и лимитирует до 2 строк