Как сделать таблицу в sql
Итак, мы познакомились с типами данных, теперь будем усовершенствовать таблицы для нашего форума. Сначала разберем их. И начнем с таблицы users (пользователи). В ней у нас 4 столбца:
id_user - целочисленные значения, значит будет тип int, ограничим его 10 символами - int (10).
name - строковое значение varchar, ограничим его 20 символами - varchar(20).
email - строковое значение varchar, ограничим его 50 символами - varchar(50).
password - строковое значение varchar, ограничим его 15 символами - varchar(15).
Все значения полей обязательны для заполнения, значит надо добавить тип NOT NULL.
id_user int (10) NOT NULL
name varchar(20) NOT NULL
email varchar(50) NOT NULL
password varchar(15) NOT NULL
Первый столбец, как вы помните из концептуальной модели нашей БД, является первичным ключом (т.е. его значения уникальны, и они однозначно идентифицируют запись). Следить за уникальностью самостоятельно можно, но не рационально. Для этого в SQL есть специальный атрибут - AUTO_INCREMENT, который при обращении к таблице на добавление данных высчитывает максимальное значение этого столбца, полученное значение увеличивает на 1 и заносит его в столбец. Таким образом, в этом столбце автоматически генерируется уникальный номер, а следовательно тип NOT NULL излишен. Итак, присвоим атрибут столбцу с первичным ключом:
id_user int (10) AUTO_INCREMENT
name varchar(20) NOT NULL
email varchar(50) NOT NULL
password varchar(15) NOT NULL
Теперь надо указать, что поле id_user является первичным ключом. Для этого в SQL используется ключевое слово PRIMARY KEY (), в скобочках указывается имя ключевого поля. Внесем изменения:
id_user int (10) AUTO_INCREMENT
name varchar(20) NOT NULL
email varchar(50) NOT NULL
password varchar(15) NOT NULL
PRIMARY KEY (id_user)
Итак, таблица готова, и ее окончательный вариант выглядит так:
create table users (
id_user int (10) AUTO_INCREMENT,
name varchar(20) NOT NULL,
email varchar(50) NOT NULL,
password varchar(15) NOT NULL,
PRIMARY KEY (id_user)
);
Теперь разберемся со второй таблицей - topics (темы). Рассуждая аналогично, имеем следующие поля:
id_topic int (10) AUTO_INCREMENT
topic_name varchar(100) NOT NULL
id_author int (10) NOT NULL
PRIMARY KEY (id_topic)
Но в модели нашей БД поле id_author является внешним ключом, т.е. оно может иметь только те значения, которые есть в поле id_user таблицы users. Для того, чтобы указать это в SQL есть ключевое слово FOREIGN KEY (), которое имеет следующий синтаксис:
FOREIGN KEY (имя_столбца_которое_является_внешним_ключом) REFERENCES имя_таблицы_родителя (имя_столбца_родителя);
Укажем, что id_author - внешний ключ:
id_topic int (10) AUTO_INCREMENT
topic_name varchar(100) NOT NULL
id_author int (10) NOT NULL
PRIMARY KEY (id_topic)
FOREIGN KEY (id_author) REFERENCES users (id_user)
Таблица готова, и ее окончательный вариант выглядит так:
create table topics (
id_topic int (10) AUTO_INCREMENT,
topic_name varchar(100) NOT NULL,
id_author int (10) NOT NULL,
PRIMARY KEY (id_topic),
FOREIGN KEY (id_author) REFERENCES users (id_user)
);
create table posts (
id_post int (10) AUTO_INCREMENT,
message text NOT NULL,
id_author int (10) NOT NULL,
id_topic int (10) NOT NULL,
PRIMARY KEY (id_post),
FOREIGN KEY (id_author) REFERENCES users (id_user),
FOREIGN KEY (id_topic) REFERENCES topics (id_topic)
);
Обратите внимание, внешних ключей у таблицы может быть несколько, а первичный ключ в MySQL может быть только один. В первом уроке мы удалили нашу БД forum, пришло время создать ее вновь.
Запускаем сервер MySQL (Пуск - Программы - MySQL - MySQL Server 5.1 - MySQL Command Line Client), вводим пароль, создаем БД forum (create database forum;), выбираем ее для использования (use forum;) и создаем три наших таблицы:
Обратите внимание, одну команду можно писать в несколько строк, используя клавишу Enter (MySQL автоматически подставляет символ новой строки ->), и только после разделителя (точки с запятой) нажатие клавиши Enter приводит к выполнению запроса.
Помните, если вы сделали что-то не так, всегда можно удалить таблицу или всю БД с помощью оператора DROP. Исправлять что-то в командной строке крайне неудобно, поэтому иногда (особенно на начальном этапе) проще писать запросы в каком-нибудь редакторе, например в Блокноте, а затем копировать и вставлять их в черное окошко.
Итак, таблицы созданы, чтобы убедиться в этом вспомним о команде show tables:
И, наконец, посмотрим структуру нашей последней таблицы posts:
Теперь становятся понятны значения всех полей структуры, кроме поля DEFAULT. Это поле значений по умолчанию. Мы могли бы для какого-нибудь столбца (или для всех) указать значение по умолчанию. Например, если бы у нас было поле с названием "Женаты\Замужем" и типом ENUM ('да', 'нет'), то было бы разумно сделать одно из значений значением по умолчанию. Синтаксис был бы следующий:
married enum ('да', 'нет') NOT NULL default('да')
Т.е. это ключевое слово пишется через пробел после указания типа данных, а в скобках указывается значение по умолчанию.
Но вернемся к нашим таблицам. Теперь нам необходимо внести данные в наши таблицы. На сайтах, вы обычно вводите информацию в какие-нибудь html-формы, затем сценарий на каком-либо языке (php, java. ) извлекает эти данные из формы и заносит их в БД. Делает он это посредством SQL-запроса на внесение данных в базу. Писать сценарии на php мы пока не умеем, а вот отправлять SQL-запросы на внесение данных сейчас научимся.
Для этого используется оператор INSERT. Синтаксис можно использовать двух видов. Первый вариант используется для внесения данных во все поля таблицы:
INSERT INTO имя_таблицы VALUES ('значение_первого_столбца','значение_второго_столбца', . 'значение_последнего_столбца');
Давайте попробуем внести в нашу таблицу users следующие значения:
Второй вариант используется для внесения данных в некоторые поля таблицы:
INSERT INTO имя_таблицы ('имя_столбца', 'имя_столбца') VALUES ('значение_первого_столбца','значение_второго_столбца');
В нашей таблице users все поля обязательны для заполнения, но наше первое поле имеет ключевое слово - AUTO_INCREMENT (т.е. оно заполняется автоматически), поэтому мы можем пропустить этот столбец:
Но прежде внесем информацию еще о нескольких пользователях. Чтобы добавить сразу несколько строк, надо просто перечислять скобки со значениями через запятую:
Теперь внесем данные во вторую таблицу - topics (темы). Все тоже самое, но надо помнить, что значения в поле id_author должны присутствовать в таблице users (пользователи):
Теперь давайте попробуем внести еще одну тему, но с id_author, которого в таблице users нет (т.к. мы внесли в таблицу users только 5 пользователей, то не существует):
Сервер выдает ошибку и говорит, что не может внести такую строку, т.к. в поле, являющемся внешним ключом, стоит значение, отсутствующее в связанной таблице users.
Итак, у нас есть 3 таблицы, в которых есть данные. Встает вопрос - как посмотреть, какие данные хранятся в таблицах. Этим мы и займемся на следующем уроке.
Видеоуроки php + mysql
Если этот сайт оказался вам полезен, пожалуйста, посмотрите другие наши статьи и разделы.
Для создания новой таблицы используется инструкция CREATE TABLE.
Синтаксис
Основной синтаксис инструкции CREATE TABLE выглядит следующим образом:
Бесплатный курс по PHP программированию
Освойте курс и узнайте, как создать динамичный сайт на PHP и MySQL с полного нуля, используя модель MVC
В курсе 39 уроков | 15 часов видео | исходники для каждого урока
CREATE TABLE — это ключевое слово, сообщающее системе баз данных, что вы хотите сделать. В данном случае мы хотим создать новую таблицу. После оператора CREATE TABLE указывается уникальное имя или идентификатор таблицы.
Затем в скобках указывается список, определяющий каждый столбец в таблице и тип данных для него. Чтобы вам было понятнее, давайте рассмотрим следующий пример.
Копию существующей таблицы можно создать с помощью комбинации инструкций CREATE TABLE и SELECT. По следующей ссылке вы можете найти полную информацию, как Создать таблицу используя другую таблицу
Пример
В следующем примере мы создаем таблицу CUSTOMERS с идентификатором в качестве первичного ключа, а NOT NULL — это ограничения, указывающие, что эти поля не могут содержать значение NULL при создании записей в этой таблице:
Для решения многих стандартных задач не требуется быть SQL-виртуозом, достаточно изучить азы работы с базами:
- создание и редактирование таблиц;
- сохранение и обновление записей;
- выборка и фильтрация данных;
- индексирование полей.
Этими азами мы и займемся: разберем синтаксис SQL-запросов в теории и на реальных примерах. К счастью, язык баз данных очень похож на простые английские предложения, так что вы легко с ним справитесь.
Чтобы учиться эффективнее, сразу же закрепляйте новые знания практикой. Поиграть с SQL можно на этом замечательном ресурсе. В левой панели вы должны ввести весь код, относящийся к структуре базы данных. После этого начинайте экспериментировать с SELECT’ами в правом поле.
* В примерах используется SQL-синтаксис для MySQL 5.6. Запросы, предназначенные для разных СУБД, могут различаться.
Терминология
База данных состоит из таблиц, а таблица – из колонок и строк.
Каждая колонка, или поле таблицы, представляет собой конкретный вид информации, например, имя студента (строка) или зарплата сотрудника (число).
Каждая строка, или запись таблицы, – это описание конкретного объекта, например, студента или сотрудника.
Уровень: Новичок
Создание и редактирование таблиц
CREATE
Несложно догадаться, что оператор CREATE создает новую таблицу в базе. Ему нужно передать описания всех полей таблицы в формате:
Создадим таблицу с данными о собаках и их рационе питания:
ALTER
Не всегда получается создать идеальную таблицу с первого раза. Не бойтесь вносить изменения, добавлять, удалять или изменять существующие поля:
DROP и TRUNCATE
Оператор DROP удаляет таблицу из базы целиком:
Если вам нужно удалить только записи, сохранив саму таблицу, воспользуйтесь оператором TRUNCATE:
Атрибуты и ограничения
Можно ограничить диапазон данных, которые попадают в поле, например, запретить устанавливать в качестве возраста или веса отрицательные числа.
Самые распространенные в SQL ограничения целостности (CONSTRAINTS):
- DEFAULT – устанавливает значение по умолчанию;
- AUTO_INCREMENT – автоматически инкрементирует значение поля для каждой следующей записи;
- NOT NULL – запрещает создавать запись с пустым значением поля;
- UNIQUE – следит, чтобы поле или комбинация полей оставались уникальны в пределах таблицы;
- PRIMARY KEY – UNIQUE + NOT NULL. Первичный ключ должен однозначно идентифицировать запись таблицы, поэтому он должен быть уникальным и не может оставаться пустым;
- CHECK – проверяет значение поля на соответствие некоторому условию.
Ограничения можно добавлять при создании таблицы, а затем при необходимости добавлять/изменять/удалять. Они могут действовать на одно поле или комбинацию полей.
Первичный ключ, автоматический инкремент, NOT NULL и значение по умолчанию мы уже использовали в примере с собаками.
Решим новую задачу – составление списка президентов:
Ограничение уникальности не позволит занести в таблицу одного и того же президента одной страны дважды. Кроме того, не попадут в список и слишком молодые политики.
Для добавления и удаления ограничений к существующим таблицам используйте оператор ALTER. Ограничениям можно давать имя, чтобы ссылаться на них впоследствии. Для этого предназначена конструкция CONSTRAINT.
Еще одно удобное ограничение в SQL – внешний ключ (FOREIGN KEY). Он позволяет связать поля двух разных таблиц.
Для примера возьмем базу данных организации с таблицами сотрудников и отделов:
Теперь в поле department таблицы employees нельзя будет указать произвольный отдел. Он обязательно должен содержаться в таблице departments.
Сохранение и обновление записей
INSERT
Добавить в таблицу новую запись (или даже сразу несколько) очень просто:
Вы даже можете скопировать записи из одной таблицы и вставить их в другую одним запросом. Для этого нужно скомбинировать операторы INSERT и SELECT:
UPDATE
Оператор UPDATE используется для изменения существующих записей таблицы.
Вот так легким движением руки мы обнулили зарплату сразу у всех сотрудников.
Запрос можно уточнить, добавив секцию WHERE с условием отбора записей.
С условиями мы подробно разберемся чуть позже, когда будем говорить о выборке данных из базы.
DELETE
Можно удалить из таблицы все записи сразу или только те, которые соответствуют некоторому условию:
Уровень: уверенный пользователь
Выборка и фильтрация данных
Для получения данных из базы служит оператор SELECT. В SQL есть множество способов отфильтровать именно те данные, которые вам нужны, а также отсортировать их и разбить по группам.
Вот небольшая демо-база, на которой вы можете попрактиковаться:
SELECT
Можно переименовывать поля для вывода:
Ограничение количества результатов:
Агрегатные функции и группировка
SQL позволяет привести несколько записей таблицы к некоторому единому значению:
Агрегатные функции могут работать со всеми записями таблицы разом, а могут и с отдельными группами. Чтобы эти группы сформировать, используйте оператор GROUP BY:
Полученные группы тоже можно отфильтровывать: для этого предназначена конструкция HAVING. Например, не будем учитывать в выборке отделы, в которых работает меньше трех человек:
Объединение таблиц
Очень часто нужная вам информация хранится в разных таблицах – это обусловлено законами нормализации. Поэтому важно уметь объединять их.
В запросе, захватывающем несколько таблиц, нужно указать следующее:
- все интересующие вас поля, которые могут принадлежать разным таблицам;
- тип соединения;
- правило, по которому поля одной таблицы будут поставлены в соответствие полям другой таблицы.
Соединение бывает внутреннее (INNER) и внешнее (OUTER).
Внутреннее соединение
При внутреннем соединении вы получите в результате только те записи, для которых нашлось соответствие во всех таблицах.
SQL просмотрит каждую запись из таблицы employees и попытается поставить ей в соответствие каждую запись из таблицы departments. Если это удастся (id отделов совпадают), запись будет включена в результат, иначе – не будет.
Таким образом, вы не увидите Kenny Washington, у которого отдел не указан, а также все отделы, в которых нет сотрудников.
Если не указано условие для соединения таблиц, SQL создаст все возможные комбинации сотрудников и отделов.
Внешнее соединение
При внешнем соединении в результат попадают также записи без соответствий. При этом вы можете регулировать, из какой таблицы такие записи берутся, а из какой – нет.
Например, чтобы увидеть в результате Kenny Washington, потребуется левое внешнее соединение. Слово OUTER можно не указывать – соединение по умолчанию внешнее:
Теперь в результате есть все данные из левой таблицы (employees), даже если для них нет соответствия.
Правое соединение соответственно проигнорирует Кенни, но выведет все пустые отделы:
И наконец, полное внешнее соединение выведет и соответствия, и пустые отделы, и сотрудников без отдела.
Декартово произведение
Оператор CROSS JOIN позволяет получить все возможные комбинации записей из двух таблиц:
Автосоединение
Кроме того, таблицу можно соединять с самой собой. Это пригодится, чтобы найти босса для каждого сотрудника. Сейчас в поле boss находится идентификатор другого сотрудника, необходимо вывести его имя:
Благодаря использованию левого соединения мы можем вывести также сотрудников, не имеющих руководителей.
Объединение выборок
SQL позволяет сделать две отдельные выборки, а затем объединить их результаты по определенному правилу:
UNION
Объединить штатных и внештатных сотрудников
INTERSECT
Найти всех сотрудников, которые участвуют в сборной предприятия по спортивной ходьбе
MINUS
Найти всех сотрудников, которые не участвуют в сборной предприятия по спортивной ходьбе и заставить участвовать :
Уровень: SQL-мастер
Представления
Views, или представления, в SQL – это SELECT-запрос, который вы можете сохранить для дальнейшего использования. Один раз написали, а потом можете пользоваться полученной таблицей, которая – внимание! – всегда остается актуальной в отличие от результата обычных запросов.
У представлений есть еще одна важная миссия: обеспечение безопасности. Под view вы легко можете скрыть бизнес-логику и архитектуру базы и защитить свое приложение от нежелательных вторжений.
Представление может извлекать данные из одной или нескольких таблиц. Кроме того, при соблюдении ряда условий представление может быть изменяемым, то есть совершая операции над ним, можно изменять базовые таблицы.
Все данные в БД SQL Server хранятся в таблицах. Таблицы состоят из колонок, объединяющих значения одного типа, и строк - записей в таблице. В одной БД может быть до 2 миллиардов таблиц, в таблице - 1024 колонки, в одной строке (записи) - 8060 байтов.
Таблицы можно создавать с помощью оператора CREATE TABLE языка Transact-SQL, а также с помощью Enterprise Manager. Рассмотрим сначала как это делается с помощью Transact-SQL.
Содание таблиц с помощью CREATE TABLE
- member_no - имеет тип int, значения NULL не допускаются
- lastname - имеет тип char(50) - 50 символов, значения NULL не допускаются
- firstname - аналогично lastname
- photo - имеет тип image (изображение), допускается значение NULL
Примечание
NULL - специальное обозначение того, что элемент данных не имеет значения. В описании типа колонки указывается, что элементы данных могут быть неинициализированы. При указании NOT NULL - "пустые" значения не допускаются. Если при вставке записи пропустить значение для такой колонки, вставка не произойдет, и SQL Server сгенерирует ошибку.
Попробуйте выполнить эту команду. Запустите Query Analyzer. Соединитесь с Вашим сервером. Из списка БД выберите sqlStep. Скопируйте в окно команд команду создания таблицы и выполните ее. (Если не забыли, надо нажать F5 или Ctrl-E).
Чтобы точно удостовериться, в том, что таблица была создана, наберите команду:
Выделите ее (как в обычном редакторе) и снова нажмите F5. В окно результатов будет выведена информация о таблице member.
На заметку!
sp_help - системная процедура, которая возвращает информацию об объектах БД (таблицах, хранимых процедурах и пр.).
Формат вызова таков:
Удалить таблицу проще простого. Там же, в запросчике (так у нас называют Query Analyzer), наберите:
Выделите эту строку и нажмите F5. Таблица будет удалена, о чем Вас и уведомят. В нашем случае эта процедура проста. На самом деле в большой БД просто так удалить таблицу не получится, поскольку она будет связана с другими таблицами, и для удаления потребуется эти связи оборвать. Как это сделать см. следующие шаги.
Как создать таблицу с помощью SQL Server Enterprise Manager
Раскройте последовательно: SQL Server Group, , Databases. Выберите БД (SqlStepByStep, я думаю :), нажмите правую кнопку мыши и выберите в контекстном меню пункт "New", а затем пункт "Table. ". Первым делом Вас спросят имя таблицы. Введите его и нажмите Enter. На экране появится окно, в котором можно вводить: имена колонок, тип, длину, размерность, точность (эти три колонки блокируются в зависимости от типа), флаг разрешения NULL, значение по умолчанию. Последние три колонки, пока не представляют интереса.
Введите названия колонок, их тип и длину также как в примере выше. Нажмите на иконку с дискетой для сохранения таблицы и можете закрыть окно. Раскройте вашу БД, щелкните на категории "Tables" и в списке таблиц увидите только что введенную таблицу. Для ее удаления выделите ее в списке, нажмите правую кнопку мыши и в контекстном меню выберите "Delete". Таблица будет удалена.
Читайте также: