Анатолий Мотев СанктПетербург бхвпетербург 2006 удк 681 06 ббк 32. 973. 26018. 2 М85
 Скачать 4.25 Mb.
|
|
CREATE TABLE book ( id_book mediumint(5) unsigned NOT NULL auto_increment, title varchar(50) default NULL, year_issue year(4) default NULL, id_author smallint(4) unsigned default NULL, id_publisher smallint(3) unsigned default NULL, id_section tinyint(2) default NULL, PRIMARY KEY (id_book) ) TYPE=MyISAM; -- -- Dumping data for table 'book' -- -- -- Table structure for table 'publisher' -- DROP TABLE IF EXISTS publisher; CREATE TABLE publisher ( id_publisher smallint(3) unsigned NOT NULL auto_increment, ×àñòü III. Ôîðìèðîâàíèå çàïðîñîâ ê ÁÄ. ßçûê SQL 96 publisher varchar(30) default NULL, PRIMARY KEY (id_publisher) ) TYPE=MyISAM; -- -- Dumping data for table 'publisher' -- -- -- Table structure for table 'reader' -- DROP TABLE IF EXISTS reader; CREATE TABLE reader ( id_reader mediumint(5) unsigned NOT NULL auto_increment, reader varchar(60) default NULL, id_addr mediumint(5) default NULL, PRIMARY KEY (id_reader) ) TYPE=MyISAM; -- -- Dumping data for table 'reader' -- -- -- Table structure for table 'section' -- DROP TABLE IF EXISTS section; CREATE TABLE section ( id_section tinyint(2) unsigned NOT NULL auto_increment, section varchar(30) default NULL, PRIMARY KEY (id_section) ) TYPE=MyISAM; -- -- Dumping data for table 'section' -- -- -- Table structure for table 'unit' -- DROP TABLE IF EXISTS unit; CREATE TABLE unit ( id_unit mediumint(6) unsigned NOT NULL auto_increment, Óðîê 8. Èçìåíåíèå ñòðóêòóðû òàáëèöû 97 id_book mediumint(5) default NULL, PRIMARY KEY (id_unit) ) TYPE=MyISAM; -- -- Dumping data for table 'unit' -- Как видите, СУБД MySQL установила для полей значение по умолчанию ( NULL ), проставила тип таблицы MyISAM , а также, благодаря установленной оп- ции –-add-drop-table , перед каждой таблицей появилась команда DROP TABLE IF EXISTS имя_таблицы . Если в дальнейшем вы измените содержимое файла и выполните его, то MySQL удалит таблицу с таким именем и заменит ее той, что указана в файле. То есть вы можете вносить любые изменения в структу- ру таблиц и заносить их в БД, выполняя этот файл при помощи утилиты MYSQL.EXE. Напоминаю, что необходимость в опции –-add-drop-table отпадает при рабо- те с MySQL версии 4.xx и выше. Мы научились работать с таблицами, но база данных с пустыми таблицами вряд ли кому-то нужна. Пришло время узнать, как заносить данные в табли- цу. Мы рассмотрим это в следующем уроке. ÂÎÏÐÎÑÛ ÄËß ÑÀÌÎÊÎÍÒÐÎËß 1. Какая опция оператора ALTER TABLE позволяет удалить поле таблицы? 2. Какие ключевые слова используются для указания позиции добавляемого поля? ÓÐÎÊ 9 Äîáàâëåíèå äàííûõ â òàáëèöó Добавлять записи в таблицу можно с помощью команд LOAD DATA и INSERT Команда INSERT позволяет добавить в таблицу одну запись, а команда LOAD DATA — сразу несколько записей, загружаемых из текстового файла. Рассмот- рим эти команды подробнее. Общий синтаксис команды INSERT : INSERT [INTO] [IGNORE] [LOW PRIORITY | DELAYED] имя_таблицы [(имя_поля,...)] VALUES (выражение,...); Параметры: имя_таблицы — таблица, в которую добавляется запись; ( выраже- ние,...) — список добавляемых значений (значения перечисляются в том же порядке, что и поля таблицы); ( имя_поля,...) — список полей, в которые вносятся данные. Эта часть параметров указана в квадратных скобках ( [] ), что говорит о необязательности ее присутствия в команде. Список полей ука- зывается только тогда, когда таблица заполняется не полностью, в ином слу- чае эта часть не присутствует. То есть если у нас есть таблица myuser : myuser id_user login password и мы хотим заполнить только два поля из трех ( id_user и login ), то команда будет выглядеть так: mysql> INSERT INTO myuser(id_user,login) VALUES(1,'login1'); В поля таблицы, не указанные в списке, вставляется значение по умолчанию (свое для каждого поля). Если заполняются все поля, то перечислять их имена совсем не обязательно: mysql> INSERT INTO myuser VALUES(1,'login1','password1'); Óðîê 9. Äîáàâëåíèå äàííûõ â òàáëèöó 99 Ключевое слово INTO не является обязательным (его необходимо использо- вать только в MySQL 3.22.5). Если указать ключевое слово LOW PRIORITY , то оператор не выполняется, пока хотя бы один пользователь читает данные из таблицы. При задании ключевого слова DELAYED строки будут помещены в очередь для более поздней вставки, и оператор прекратит выполнение, что позволит клиенту продолжить работу. Если при заполнении таблицы явно не задается значение какого-либо поля, то в него будет занесено значение по умолчанию, определенное вами или MySQL при создании таблицы. В том случае, если вносятся дублирующие значения в поля PRIMARY KEY или UNIQUE , действие команды прекращается и выдается предупреждение (рис. 9.1). Ключевое слово IGNORE позволит проигнорировать вставку дубли- рующих значений. Рис. 9.1. Ошибка при дублировании значений Кроме этого, предупреждения выдаются в следующих ситуациях: внесение значения NULL в поле, которое было объявлено как NOT NULL ; в данное поле будет установлено значение, заданное по умолчанию; установка числового поля в значение, которое находится за пределами его допустимого диапазона; данное значение будет усечено до соответствую- щей конечной точки этого диапазона; внесение в числовое поле такой величины, как '32 ba' ; конечные данные удаляются, и вносится только оставшаяся числовая часть; если вводимую величину невозможно интерпретировать как число, то значение поля уста- навливается в 0; внесение в поле типа CHAR , VARCHAR , TEXT или BLOB строки, длина которой превышает максимальную длину поля; данная величина усекается до мак- симальной длины поля; внесение в поле даты или времени строки, недопустимой для данного типа столбца; это поле будет установлено в нулевое значение, соответствующее данному типу. ×àñòü III. Ôîðìèðîâàíèå çàïðîñîâ ê ÁÄ. ßçûê SQL 100 Для внесения данных в определенные поля также можно использовать син- таксис: INSERT INTO имя_таблицы SET имя_поля=значение[, имя_поля=значение,...]; Здесь оператор INSERT вставляет в поля, определенные в предложении SET , заданные значения. Если поля не определены, в них вставляется значение по умолчанию. Рис. 9.2. Использование синтаксиса INSERT ... SET Выполнив команду SELECT , вы сможете просмотреть результаты внесения данных в таблицу: mysql> SELECT * FROM reader; Команда LOAD DATA актуальна, если требуется внести в таблицу большое ко- личество записей: LOAD DATA [LOCAL] INFILE ' имя_файла.txt' [IGNORE | REPLACE] INTO TABLE имя_таблицы Эта команда читает строки из текстового файла и вставляет их в таблицу с очень высокой скоростью. Файл должен находиться в каталоге данных серве- ра (в нашем случае это C:\MYSQL\DATA\LIBRARY). Без ключевого слова LOCAL файл считывается с того компьютера, где установлен сервер MySQL. Обычно возможность использования ключевого слова LOCAL на стороне сер- вера отключена. По умолчанию команда работает так: 1. Ищет конец строки в виде символов '\n' 2. Разбивает строки на поля по символу табуляции. Например, создадим в указанном каталоге файл 1.TXT, записав в него не- сколько строк со значениями, разделенными табуляцией: 1 Denis 1 2 Oleg 2 3 Nina 3 Óðîê 9. Äîáàâëåíèå äàííûõ â òàáëèöó 101 Можно сразу поместить их в таблицу с помощью команды LOAD DATA (рис. 9.3): mysql> LOAD DATA INFILE '1.txt' INTO TABLE reader; Рис. 9.3. Выполнение команды LOAD DATA Если вам нужно пропустить несколько строк из файла, используйте предло- жение IGNORE n LINES . Например, это понадобится, если наш файл выглядит так: id_reader reader id_addr 1 Denis 1 2 Oleg 2 3 Nina 3 После выполнения команды mysql> LOAD DATA INFILE '1.txt' INTO TABLE reader IGNORE 1 LINES; все данные из текстового файла будут помещены в таблицу. При указании ключевых слов IGNORE и REPLACE дублирующие значения для уникальных ин- дексов либо игнорируются, либо заменяют уже существующие. В режиме консоли также можно заносить в таблицу (например, section ) дан- ные следующим образом (рис. 9.4): mysql> INSERT INTO section VALUES -> (1,'Детектив'), -> (2,'Фантастика'), -> (3,'Сказка'); Теперь вы знаете, как можно заполнить таблицы, находящиеся в БД. Необходимо заполнить нашу учебную БД, внеся хотя бы по несколько запи- сей в каждую таблицу, и тогда мы сможем с ней работать. На сопроводитель- ном компакт-диске книги есть файл DATA_FOR_LIBRARY.TXT с коман- дами INSERT для каждой таблицы. Текст файла записан в кодировке Win- ×àñòü III. Ôîðìèðîâàíèå çàïðîñîâ ê ÁÄ. ßçûê SQL 102 dows-1251, поэтому при просмотре содержимого таблиц посредством ко- мандной строки кириллица может отображаться некорректно (в дальнейшем, при выводе данных в браузере все будет отображаться верно). Поэтому для тестирования операторов языка SQL ( DELETE , UPDATE , SELECT ) вводите данные непосредственно в командной строке. Напоминаю, что для выполнения ко- манд из файла необходимо ввести в командной строке такую команду: C:\mysql\bin> mysql –uroot library –uroot не нужен, если вы работаете с правами администратора, но если вы его укажете, хуже не будет. Я рекомендую, конечно же, заполнить таблицы самостоятельно, закрепив пройденный материал. Рис. 9.4. Занесение данных в таблицу section ÓÐÎÊ 10 Óäàëåíèå äàííûõ Если вы допустили какие-то ошибки при занесении данных или занесли в таблицу то, что в ней не должно находиться, то потребуется удалить эти не- верные данные. В этом уроке мы рассмотрим удаление записей из таблицы. Также эти знания могут понадобиться в дальнейшем при написании прило- жений на языке PHP, обслуживающих БД. Для удаления записей из таблицы предназначен оператор DELETE : DELETE FROM имя_таблицы [WHERE условие]; Оператор DELETE удаляет из таблицы имя_таблицы строки, которые удовлетво- ряют заданным в параметре WHERE условиям. Условия WHERE не являются обя- зательными, но используются в большинстве запросов на удаление. Если, на- пример, ввести команду mysql> DELETE FROM reader; то будут удалены все записи таблицы reader ("Читатель"), что вряд ли нам нужно. Какие же условия мы можем задавать при удалении записей? Давайте рассмотрим это подробнее. Параметр условие задается как имя_поля='значение' . Например, запрос mysql> DELETE FROM reader WHERE id_reader=1; означает "удалить из таблицы reader ту запись, где значение поля id_reader равно 1 ". Этим запросом мы удалим запись только для одного читателя с оп- ределенным номером (номер уникальный, т. к. id_reader является первичным ключом — PRIMARY KEY ). При описании условия кроме знака равенства ( = ) можно использовать сле- дующие операции: ×àñòü III. Ôîðìèðîâàíèå çàïðîñîâ ê ÁÄ. ßçûê SQL 104 > ("больше, чем"): mysql> DELETE FROM book WHERE year_issue>2000; После выполнения этого запроса будут удалены записи книг начиная с 2001 года выпуска (рис. 10.1); Рис. 10.1. Удаление записей из таблицы book >= ("больше или равно"): mysql> DELETE FROM reader WHERE id_reader>=25; После выполнения этого запроса будут удалены записи читателей с номе- рами, больше или равными 25; < ("меньше, чем"); <= ("меньше или равно"); <> или != ("не равно): mysql> DELETE FROM author WHERE id_author<>5; В этом случае удаляются все записи авторов, номера которых не равны 5 (у нас осталась только одна запись, рис. 10.2); IS NULL (поле содержит значение NULL ) (рис. 10.3): mysql> DELETE FROM reader WHERE reader IS NULL; Значение NULL можно установить в поле, если на момент внесения записи вам неизвестно, какие данные будут храниться в этом поле. По сути, NULL обозначает отсутствующее или неизвестное значение; Нельзя проверять значение на равенство NULL при помощи обычных операций сравнения (=, > или !=). Óðîê 10. Óäàëåíèå äàííûõ 105 Рис. 10.2. Удаление записей из таблицы author Рис. 10.3. Удаление записей с полем, содержащим значение NULL Рис. 10.4. Удаление записей с полем reader , значение которого отлично от NULL ×àñòü III. Ôîðìèðîâàíèå çàïðîñîâ ê ÁÄ. ßçûê SQL 106 IS NOT NULL (поле содержит значение, отличное от NULL ) (рис. 10.4): mysql> DELETE FROM reader WHERE reader IS NOT NULL; Ñðàâíåíèå ïî øàáëîíó В MySQL реализовано сравнение по шаблонам: LIKE 'шаблон' — значение поля соответствует шаблону; NOT LIKE 'шаблон' — значение поля не соответствует шаблону. При описании можно использовать символы шаблона: '_' — соответствует любому одиночному символу; '%' — соответствует любому количеству символов. В MySQL шаблоны по умолчанию не чувствительны к регистру символов. Например, если вы хотите удалить записи всех авторов, фамилии которых начинаются с буквы 'Л' , то введите такой запрос (рис. 10.5): mysql> DELETE FROM author WHERE author LIKE 'Л%'; Постарайтесь по возможности избегать шаблонов, начинающихся с символа '%'. СУБД тратит довольно много времени на поиск значений, соответствую- щих такому шаблону. Примеры запросов с таким шаблоном представлены на рис. 10.6 и 10.7. Рис. 10.5. Удаление по шаблону 'Л%' Óðîê 10. Óäàëåíèå äàííûõ 107 Если нужно удалить записи авторов, фамилии которых заканчиваются на 'й' , введите запрос (рис. 10.6): mysql> DELETE FROM author WHERE author LIKE '%й'; Рис. 10.6. Удаление по шаблону '%й' А если нужно удалить записи авторов, в фамилиях которых есть буква 'о' , введите такой запрос (рис. 10.7): mysql> DELETE FROM author WHERE author LIKE '%о%'; Рис. 10.7. Удаление по шаблону '%о%' ×àñòü III. Ôîðìèðîâàíèå çàïðîñîâ ê ÁÄ. ßçûê SQL 108 Удалить записи авторов, фамилии которых состоят из шести букв, можно с помощью следующего запроса: mysql> DELETE FROM author WHERE author LIKE '_ _ _ _ _ _ '; Здесь шаблон состоит из шести символов '_' . Поскольку значения 'Пушкин' и 'Гоголь' совпадают с этим шаблоном, система удаляет их из таблицы (рис. 10.8). Рис. 10.8. Удаление по шаблону '_ _ _ _ _ _ ' Ðàñøèðåííûå ðåãóëÿðíûå âûðàæåíèÿ Кроме описанных ранее, вы можете применять шаблоны другого вида, осно- ванные на расширенных регулярных выражениях. В этом случае для проверки значений на соответствие или несоответствие шаблону используются опера- торы REGEXP и NOT REGEXP соответственно. Также можно применять их сино- нимы RLIKE и NOT RLIKE При составлении шаблона допустимы следующие выражения: (точка) — любой символ; [...] (квадратные скобки) — любой из символов, перечисленных в скоб- ках (класс символов). Например, '[ab2]' обозначает букву 'a' или 'b' или цифру '2' ; Класс символов — это набор символов, обозначающий один символ из пере- численного набора. В классе можно задавать диапазоны, например, класс [a-z] Óðîê 10. Óäàëåíèå äàííûõ 109 обозначает любую букву в нижнем регистре, а класс [0-9] — любую цифру. Есть возможность комбинировать диапазоны в классе, например, класс [a-z0-9] обозначает любую букву или цифру. + — одно или более вхождений символа. То есть шаблон '[0-9]+' обозна- чает одно или более вхождений любой цифры, шаблон 'a+' — одно или более вхождений буквы 'a' , а шаблон '.+' — одно или более вхождений любого символа; * — ноль или больше вхождений символа. Отсюда следует, что шаблон '[a-z]*' — это любое количество букв; ? — ноль или одно вхождение символа; {n} — определенное число вхождений символа. Например, шаблон '[0-9]{4}' обозначает четыре любых цифры; {n,} — n и больше вхождений символа; {n,m} — от n до m вхождений символа (т. е. задаются минимальное и мак- симальное количества вхождений символа); ^ — привязка к началу строки. Например, шаблон '^[0-9]{4}' будет ис- кать наличие четырех цифр в начале строки; $ — привязка к концу строки. Шаблон '[a-zA-Z]{2}$' будет искать нали- чие двух букв в верхнем или нижнем регистре, начиная с конца строки. В MySQL версий до 3.23.4 оператор REGEXP учитывает регистр при сравнении по шаблону. Более поздние версии не учитывают регистр символов. Но можно "заставить" REGEXP учитывать регистр, если добавить ключевое слово BINARY. Давайте теперь рассмотрим на примерах, как все это работает. Составим не- сколько запросов, использующих при удалении сравнение значений на соот- ветствие шаблону. mysql> DELETE FROM book WHERE title REGEXP BINARY '^[а-я]'; Этот запрос удалил все записи всех книг, названия которых начинаются с буквы в нижнем регистре, — мы лишились 'буратино' (рис. 10.9). mysql> DELETE FROM book WHERE title RLIKE '^.{3}$'; Этот запрос удалил записи книг, названия которых состоят из трех символов ( 'Нос' ) (рис. 10.10). mysql> DELETE FROM book WHERE title RLIKE '[0-9]+'; Этот запрос позволил нам удалить записи книг, в названиях которых встре- чаются цифры (рис. 10.11). ×àñòü III. Ôîðìèðîâàíèå çàïðîñîâ ê ÁÄ. ßçûê SQL 110 Рис. 10.9. Удаление по шаблону с учетом регистра символов Рис. 10.10. Удаление по шаблону '^.{3}$' mysql> DELETE FROM book WHERE title RLIKE '[а-я]+( [а-я]+)+'; А здесь мы удалили записи книг, название которых состоит более чем из од- ного слова. Первая часть шаблона '[а-я]+' описывает слово (одну или не- сколько букв, идущих подряд), далее следуют пробел и еще одно слово '( [а-я]+)+' |