SQL — урок 1

Базы данных SQL — урок 1

Помечено: ,

  • В этой теме 0 ответов, 1 участник, последнее обновление 1 год, 7 месяцев назад сделано Васильев Владимир Сергеевич.
Просмотр 0 веток ответов
  • Автор
    Сообщения
    • #5167
      @admin
      StudLance.ru

      Введение

      SQL (Structured Query Language) — язык разработанный специально для обслуживания реляционных баз данных. Главная цель SQL предоставить интерфейс с базой данных, поэтому он может использоваться всеми видами пользователей:

      • разработчики приложений;
      • администраторы базы данных;
      • простые конечные пользователи (редко).

      Очень часто SQL любят называть языком четвертого поколения, в котором программист указывает что ему нужно, а не как что-то сделать.

      Внутри SQL часто выделяют два языка или две группы команд, которые рассмотрены ниже. Также SQL не отвергает процедурное программирование, позволяя создавать процедуры, функции и триггеры. Но эти возможности как и реализация ООП зависят от конкретной СУБД. В принципе стандарты языка разрабатываются как SQL99 и SQL2003, и возможно в будущем эти различия не станут столь значимыми.

      DDL (Data Definition Language)

      Команды этой группы управляют структурой базой данных:

      • создавать, изменять и уничтожать БД и ее объекты (CREATE, ALTER,
        DROP);
      • переименовать объекты (RENAME);
      • стереть все данные в объектах без удаления объектной структуры (TRUNCATE);
      • добавлять комментарий к объектам БД. (COMMENT).

      DML (Data Manipulation Language)

      Команды этой группы позволяют запрашивать и обрабатывать данные в существующих объектах:

      • получить данные из одной и более таблиц (SELECT);
      • добавлять новые записи в таблицу (INSERT);
      • изменять данные в таблицах (UPDATE);
      • изменять или вставлять данные (MERGE);
      • удалять данные (DELETE).

      Команды управления транзакциями позволяют управлять изменениями сделанные DML командами, другими словами подтвердить или отменить изменение:

      • применить изменения, т.е. совершить транзакцию (COMMIT);
      • отменить последние изменения, т.е. сделать откат базы данных с точки сохранения
        (ROLLBACK);
      • сохранить текущее состояние базы данных (SAVEPOINT).

      прочие команды

      Остальные команды, например управляющие привилегиями (GRANT, REVOKE), можно отнести к административным.

      Структура базы данных

      Большинство современных баз данных (хранилища) являются объектно-реляционными, т.е. множеством объектов, как двухмерные таблицы данных, связанных между собой. Комплекс программ управляющих объектами БД и обеспечивающих ее целостность обычно называют системой управления базой данных, или сокращенно СУБД. Как правило, конечный пользователь БД работает не с СУБД, а со специально разработанными программами с дружественным интерфейсом, скрывающих структуру БД и SQL запросы. Сравнивая с объектно-ориентированным программированием, можно сказать, что каждая таблица задает тип объекта данных, где столбец — свойство объекта. А каждая строка представляет непосредственно экземпляр объекта.

      Строку таблицы иногда называют записью таблицы, а ячейку записи полем.

      ключи, ограничения


      Для различных служебных целей в таблице можно создать ключи:

      • первичный ключ (primary key) — множество столбцов, по которым можно уникально идентифицировать запись в таблице. Поля таких столбцов обязательно должны иметь значение и не повторяться. В нашем примере столбец id во всех таблицах может использоваться для первичного ключа;
      • уникальный ключ (unical key) — множество столбцов, чье общее значение должно быть уникально. Например, если в уникальный ключ входит два столбца, то две записи могут иметь одинаковое значение в первом столбце только в том случае, если значения во втором столбце у них различны;
      • внешний ключ (foreign key) — определяет ссылку на запись в другой таблице. Значения полей столбца входящего во внешней ключ одной таблицы идентичны значениям полей столбца другой таблицы (обычно это первичный ключ), на которую ссылается ключ. Пустое значение (null) для внешних ключей разрешается, что означает отсутствие ссылки на данный момент. Использование внешних ключей устраняет избыточность информации в базе данных, позволяя не вводить одни и те же данные несколько раз. В нашем примере столбцы fk_author и fk_janr являются внешними ключами, ссылающиеся на первичные ключи таблиц авторов и жанров соответственно. Таблицы без внешних ключей, как таблицы жанров и авторов называют словарями или справочниками.

      Кроме ключей обычно позволяется наложить ограничения (constraints) на значения столбцов таблицы. За правильностью обычно следит СУБД. Например, если попытаться вставить в таблицу две записи с одинаковым значением первичного ключа, то сразу возникнет ошибка. То же самое произойдет, если в качестве значения внешнего ключа указать ссылку на не существующую запись или значение поля не удовлетворяет условию ограничения.

      индексы

      Если предполагается частый поиск записей или сортировка, то используемые при этом столбцы индексируют. Для этого создается специальный объект БД — индекс, содержащий ссылки (индексы) на все записи таблицы отсортированных по одному и более столбцов. Для внутренней организации индексов могут использоваться различные структуры данных как деревья. Столбцы, входящие в первичный или уникальный ключ, обычно индексируются автоматически.

      представления

      Конечной целью создания БД являются запросы данных от пользователей. Если запрос используется клиентскими программами, например для генерации отчета или очень сложен (код запроса занимает страницы), то имеет смысл сохранить его. Для хранения запросов используются специальные объекты — представления или на сленге вьюхи (от view), содержащие в себе код запроса данных на языке SQL. Представления также называют виртуальными таблицами, которые отличаются от обычных таблиц тем, что доступны только для чтения. В результате код новых запросов с подзапросами становится более понятным. А также упрощается работа создателей отчетов, которые могут даже не знать структуру БД, ведь им достаточно знать имя представления.

      процедуры и функции

      Серьезные СУБД поддерживают в различной степени динамический SQL, с которым связаны дополнительные типы объектов. В первую очередь это хранимые функции и процедуры. Их аргументы могут быть как входными, так и выходными, т.е. в них можно сохранить результат работы процедуры. В Oracle множество функций, процедур, различные пользовательские типы и переменные можно объединить в специальный объект — пакет (package), выполняющий роль модуля или библиотеки обычных языков программирования. Например, пакет DBMS_OUTPUT служит для вывода данных из хранимых процедур. Помимо обычных функций, СУБД поддерживают агрегатные функции, работающие сразу со множеством записей. К таким функциям относятся подсчет записей в выборке, поиск записи с минимальным или максимальным значением в указанном поле.

      триггеры

      Для обработки событий вставки, изменения и удаления записей в таблице могут быть назначены триггеры — процедурные объекты автоматически выполняющиеся при наступлении указанного события.

      схема

      Объект схема (schema) позволяет логически сгруппировать множество других объектов БД для определенной цели. Например, одна схема может служить для управления книжным фондом библиотеки, а другая ее бухгалтерией. Таким образом, несколько клиентских программ могут работать с одной СУБД не мешая друг другу.

      пользователи и сессии

      Информация о пользователях также хранится в БД. Каждый пользователь характеризуется именем, паролем для входа, правами доступа к объектам БД и числом одновременных подключений (сессий). Для облегчения права доступа могут быть сгруппированы в роли. Ограничение в правах разумно не только с точки зрения безопасности целостности БД (можно случайно уничтожить таблицу с данными) и сокрытия данных (например, адреса клиентов). Клиентские программы могут анализировать доступные права и выстраивать соответствующий пользовательский интерфейс, например, убрать лишние пункты меню.

      В каждой СУБД свой перечень используемых типов объектов. Выше обозначены лишь более-менее стандартные объекты.

      Инструментарий

      скачать СУБД

      В первую очередь необходимо достать какую-либо СУБД:

      • MySQL является самой популярной СУБД с открытым кодом заточенной для web-приложений. Скачать можно c оффициального сайта
        dev.mysql.com/downloads
      • СУБД Oracle является популярной среди разработчиков производственных приложений. Если осилите регистрацию, то сможете скачать дистрибутив бесплатно на сайте
        http://www.oracle.com
      • PostgreSQL также является СУБД с открытым кодом. Используется как для web-приложений, так и для производственных приложений. Реализуются практически все возможности SQL стандарта, с другой стороны по синтаксису близка Oracle. Скачать можно на сайте http://www.postgresql.org

      Инсталляция под windows

      Инсталляция MySQL и СУБД Oracle под Windows для учебных целей выполняется не сложно — просто следуем шагам инсталлятора и установщика. При этом просим создать базу по умолчанию и запоминаем пароли системных пользователей: root для MySQL, system для Oracle.

      Тем не менее, рассмотрим процесс настройки (выполняется после установки) MySQL Server:
      1. Выбираем стандартную конфигурацию

      2. Устанавливаем параметры работы службы MySQL

      Параметр Install As Windows Service означает, что сервер MySQL будет работать как служба Windows. При необходимости можно выбрать другой Service Name, по умолчанию MySQL — он и будет использован. Включенный параметр Launch the MySQL Server automatically заставит службу MySQL стартовать автоматически при запуске операционной системы. А также параметр Include Bin Directory in Windows PATH приводит к добавлению пути MySQL в переменной PATH операционной системы.

      3. Устанавливаем пароль для пользователя root. Также можно выставить галочку Enable root access from remote machines, если предполагается подключаться к серверу MySQL с других машин в локальной сети. Рекомендую сразу поставить, даже если на данный момент нет сетевых компьютеров. Жмем «Next» и переходим к завершению установки.

      Далее настраивать уже ничего не нужно. В последующем окне просто нажимаем Execute, что приведет к применению всех настроек и запуску службы MySQL. Сразу после выполнения Execute появится окно в котором будет показано состояние выполненных действий. Бывает что появляются некоторые ошибки, но обычно они не мешают работе и помогает простая перезагрузка компьютера.

      Для PostgreSQL скачиваем инсталлятор postgresql-8.3.3-1.zip и устанавливаем СУБД, либо в
      виде программы (далее инструкции для этого типа инсталляции), либо в виде службы. Все утилиты находятся в поддиректории bin. Делаем инициализацию БД, с указанием директории, где будут хранится данные:
      initdb -D "D:/PostgreSQL/8.3/data"
      В результате будет создана база данных postgres с одной общедоступной схемой. Запустить СУБД можно командой:

      pg_ctl start -D "D:/PostgreSQL/8.3/data" -l logfile
      Соответственно остановить можно командой:

      pg_ctl stop -D "D:/PostgreSQL/8.3/data" -l logfile

      По умолчанию создается пользователь с системными правами с именем пользователя на компьютере. Если оно не устраивает, нового пользователя (например, root) при работающей СУБД можно создать утилитой createuser.exe.

      инсталляция mysql под linux

      aptitude install mysql-server
      aptitude install mysql-client

      Сбрасываем начальные настройки, задаем пароли:

      /usr/bin/mysql_secure_installation

      Зайти в mySQL можно так:

      mysql -u root -p
      
      (show databases; show tables from ...; - просмотр баз и таблиц в mysql) Меняем стандартную latin на utf8!!!
      
      sudo nano /etc/mysql/my.cnf

      Находим по тексту [mysqld] и добавляем строчки:

      # это вранних версиях? default-character-set = utf8
      # сейчас нужно так: 
      character-set-server = utf8#cp1251
      character-sets-dir = /usr/share/mysql/charsets
      #skip-character-set-client-handshake [это не обязательно, если кому надо]
      init_connect='SET collation_connection = utf8_general_ci'
      init_connect='SET NAMES utf8'

      Здесь же можно подправить язык по умолчанию на русский:

      language = /usr/share/mysql/russian

      Теперь находим по тексту [client] и добавляем:

      default-character-set = utf8
      character-sets-dir = /usr/share/mysql/charsets

      Сохраняем, перезапускаем

      $sudo /etc/init.d/mysql restart

      выполнение запросов

      В поставку Oracle входит программа SQL plus, для входа запрашиваются следующие
      параметры:

      username: system  
      password: ваш пароль   
      database: orcl 

      Под database понимается строка связи, известная как tns имя.
      По умолчанию создается строка связи orcl. Посмотреть и исправить имена можно в файле

      ORACLE_HOME/network/admin/tnsnames.ora

      Консоль SQL plus не так удобна. Если есть возможность, то лучше купить программу по типу PL/SQL Developer.

      В поставку PostgreSQL входит утилита pgAdmin3.exe. Несмотря на название, онапозволяет выполнять любые sql запросы. Добавте в ней новый сервер,например, со следующими параметрами:

      name: pg (любое имя для сохранения подключения)
      host: localhost
      user: root
      password: вашпароль (можно оставить пустым)

      С оффициального сайта MySQL можно скачать визуальные утилиты, среди которых есть
      MySQLQueryBrowser. Для подключения к ней можно использовать, например, следующие параметры:

      server host: localhost
      port: 3306 (по умолчанию)
      username: root
      password: ваш пароль
      default schema: test (создается по умолчанию)

      использование примеров

      В приведенных в этом руководстве примерах используется схема по умолчанию. В MySQL она задается при соединении (схема test создается автоматически), в Oracle для каждого пользователя своя одноименная схема, в PostgreSQL это схема public. В противном случае перед именами объектов (таблиц, хранимых процедур и т.п.) необходимо через точку указывать имя схемы, а пользователь должен иметь соответствующие права. Более подробно можно посмотреть в соответствующих пунктах.

      -- используем схему по умолчанию
      select * from tblname;
      
      -- используем схему sсhemaname 
      select * from sсhemaname.tblname;

      Комментарий

      В SQL коде комментарий оформляется следующим образом.

      /* много
      строчный комментарий
      */
      
      -- однострочный комментарий

      Для MySQL после начала однострочного комментария пробел обязателен.

      Идентификаторы

      Идентификатор — последовательность символов и цифр, служащая для именавания различных объектов как таблицы, имена столбцов и т.д.

      Допустимыми символами являются: ‘A’-‘Z’, ‘a’-‘z’,’_’, ‘0’-‘9’. Цифры не могут быть первым символом. Регистр букв обычно значения не имеет.

      По станадарту строка, заключенная в двойные кавычки, также является идентификатором. Причем в строку могут входить прочие символы, например, буквы русского алфавита и пробелы. В MysQL дополнительно поддерживаются идентификаторы в обратных одинарных кавычках « (не апостроф, а кавычка где ~ или русская Ё, перед клавишей 1). А двойные кавычки могут использоваться только в режиме ANSI_QUOTES.

      Обзор типов данных

      Ниже приведен список основных типов данных для столбцов таблиц:

      • BOOLEAN — логический тип;
      • TINYINT — целочисленный тип размером в один байт;
      • SMALLINT — целочисленный тип размером в два байта;
      • INT,INTEGER — целочисленный тип размером в четыре байта;
      • BIGINT — целочисленный тип размером в восемь байт;
      • DEC, NUMERIC, DECIMAL — тип для вещественных чисел с фиксированной точкой;
      • FLOAT — тип для вещественных чисел с плавающей точкой малой точности;
      • DOUBLE PRECISION — тип для вещественных чисел с плавающей точкой двойной точности;
      • CHAR — строка фиксированного размера;
      • NCHAR — строка фиксированного размера с поддержкой национальных символов;
      • VARCHAR — строка переменного размера;
      • NVARCHAR — строка переменного размера с поддержкой национальных символов;
      • DATE — дата;
      • TIME — время;
      • TIMESTAMP — временная отметка;
      • INTERVAL — интервал времени;
      • BINARY LARGE OBJECT — двоичные данные больших размеров. В большинстве СУБД называется BLOB;
      • CHARACTER LARGE OBJECT — символьные данные больших размеров. Может называться как CLOB или TEXT.

      В Oracle все числовые типы реализованы через собственный встроенный тип NUMBER. В некоторых базах как MySQL DOUBLE PRECISION можно заменить на просто DOUBLE. При определении вещественных типов возможны два параметра, определяющих общее число знаков в числе и число знаков после запятой, например DOUBLE PRECISION(10,2).

      Строки также имеют параметр определяющие размер строки или максимальный размер строки.

      Вместо ANSII типов VARCHAR и NVARCHAR в Oracle рекомендуется использовать типы VARCHAR2 и NVARCHAR2. Для символьных типов приставка N в Oracle подразумевает использование Unicode символов. MS Access всегда использует только Unicode символы (т.е. VARCHAR и NVARCHAR являются синонимами). В MySQL тип символов задается настройкой базы, или явным указанием используемой кодировки и таблицы сравнения строк без учета регистра.

      В некоторых базах нет строгого разграничения между временными типами, и все имена типов являются синонимами.

      StudLance.ru

Просмотр 0 веток ответов
  • Для ответа в этой теме необходимо авторизоваться.
×