 |
Урок 8: Изменяем поля ALTER. Урок 9: Изменяем поля – строковые функции
|
|
|
|
Урок 8: Изменяем поля ALTER
В этом уроке мы поговорим о том, как же нам изменять структуру нашей таблицы. Достаточно просто это делается. Есть несколько путей. О втором пути, который задействует функции и еще к тому же влияет на записи мы поговорим в следующем уроке, а в этом уроке мы поговорим о командах ALTER TABLE.
Итак, если мы взглянем на общий вид синтаксиса нашего простейшего запроса, то он выглядит так:
ALTER TABLE имя таблицы. После этого я должен произвести действия, заканчивая запрос точкой с запятой.
В прошлом уроке мы познакомились с ключевым словом ADD COLUMN, ADD PRIMARY KEY.
Я буду использовать БД users_db;
| mysql> USE users_db; | |
| mysql> SELECT * FROM users; |
| name |
| Peter |
| Kate |
| Joe |
| Erik |
| Tomas |
Я думаю, что вы поняли, что для содержания пользователей на сайте, только одних имен не требуется. Поэтому нам нужно прокачать нашу таблицу.
Итак, давайте сначала добавим самое важное – это добавим поле с первичным ключом. Для этого мы вводим команду ALTER TABLE, выбираем таблицу users и теперь можем добавить либо колонку, либо первичный ключ.
| mysql> ALTER TABLE users ADD COLUMN id INT AUTO_INCREMENT FIRST, ADD PRIMARY KEY(id); | Таким образом, если мы сейчас выберем все столбцы из табл. users, то как вы видите на 1-м месте у нас появился столбец с названием id, и соответственно он все наши записи пронумеровал. Кстати помимо FIRST, также можно писать SECOND, THIRD. А можно так: AFTER имя_поля за которым будет расположен первый стоблец. |
| id | name |
| Peter | |
| Kate | |
| Joe | |
| Erik | |
| Tomas |
| mysql> ALTER TABLE users ADD COLUMN test VARCHAR(10) AFTER id; | Теперь предположим, что у нас есть лишний столбец. Давайте его создадим. И теперь проверяем. |
| id | test | name |
| NULL | Peter | |
| NULL | Kate | |
| NULL | Joe | |
| NULL | Erik | |
| NULL | Tomas |
|
|
|
Как видим, у нас test заполняется значениями NULL, т. е. значениями буквально хранящими «ничего».
Теперь нам поле test не нужно. Мы хотим его удалить. Для этого используются ключевые слова DROP COLUMN.
| mysql> ALTER TABLE users DROP COLUMN test; |
Смотрим на таблицу…
| id | name |
| Peter | |
| Kate | |
| Joe | |
| Erik | |
| Tomas |
Идем дальше… Следующее ключевое слово – это RENAME TO.
| mysql> ALTER TABLE users RENAME TO testUsers; | RENAME TO изменяет название нашей таблицы. |
| mysql> SHOW TABLES; | Выведем таблицы в нашей БД |
| Tables in user_db |
| testUsers |
Теперь переименуем все обратно, как было.
| mysql> ALTER TABLE testUsers RENAME TO users; | Выведем таблицы в нашей БД |
Следующее – это CHANGE COLUMN (изменить колонку). Здесь мы можем изменить как имя, так и его тип. Попробуем расширить количество доступных символов у нас в поле name. Сейчас доступно 20 символов.
| mysql> DESC users; |
| Field | Type | NULL | KEY | Default | Extra |
| id | INT(11) | NO | PRI | NULL | auto_increment |
| name | VARCHAR(20) | NO | NULL |
Сократим количество символов, выделяемых под имя до 15.
Для этого мы пишем:
| mysql> ALTER TABLE users CHANGE COLUMN name names_list VARCHAR(15) NOT NULL; |
Выведем таблицу users.
| id | names_list |
| Peter | |
| Kate | |
| Joe | |
| Erik | |
| Tomas |
Также, если мы выведем описание нашей таблицы, то VARCHAR также изменится до 15.
Вопрос: Почему мы должны писать имя для нашего поля, если мы не хотим добавлять новое имя в наше поле? Мы хотим изменить его тип, а еще хорошо бы изменить его позицию! Для этого мы используем ключевые слова MODIFY COLUMN.
| mysql> ALTER TABLE users MODIFY COLUMN names_list VARCHAR(20); | |
| mysql> DESC users; | Вызвав описание таблицы, мы увидим, что VARCHAR стал разрешать не более 20. |
| mysql> ALTER TABLE users DROP COLUMN id, CHANGE COLUMN names_list name VARCHAR(15); | Вернем команду в исходный вариант |
| mysql> SELECT * FROM users; | И снова выведем все. |
| name |
| Peter |
| Kate |
| Joe |
| Erik |
| Tomas |
|
|
|
А теперь давайте одной командой прокачаем эту таблицу.
| mysql> ALTER TABLE users ADD COLUMN id INT AUTO_INCREMENT FIRST, ADD PRIMARY KEY(id), CHANGE COLUMN name names_list VARCHAR(20), ADD COLUMN lname_list VARCHAR(20); | Вернем команду в исходный вариант - добавили первичный ключ - изменили название и тип нашего столбца с именами - добавили новый пустой столбец с фамилией |
| id | names_list | lname_list |
| Peter | NULL | |
| Kate | NULL | |
| Joe | NULL | |
| Erik | NULL | |
| Tomas | NULL |
Урок 9: Изменяем поля – строковые функции
SQL-функция – блок действий с названием. Функция может производить действия, а также возвращать значения на место вызова.
Я буду использовать БД users_db
| mysql> USE users_db; | |
| mysql> SELECT * FROM users; |
| id | names_list |
| Peter Yan | |
| Lola Vin | |
| Jimmy Voode |
В прошлом уроке мы научились добавлять новые столбцы, удалять поля, модифицировать их типы, переименовывать. Но в каких-то случаях, этого может быть недостаточно. И поэтому здесь мы будем использовать строковые функции. Почему строковые?
Функции, которые я покажу – они работают со строками.
- RIGHT/LEFT(поле, количество_символов) – функция возвращает символы в заданном количестве слева / справа от значения в поле.
- SUBSTRING_INDEX(поле, 'разделитель', номер_разделителя) – функция возвращает все символы до разделителя
- UPPER/LOWER(поле)
- RTRIM/LTRIM(поле) – убрать пробелы справа / слева
Например, когда мы создаем таблицу, мы также открываем скобочки CREATE TABLE(). Тоже самое и с функцией. У нас есть функция UPPER (и сюда мы можем ввести столбец). Следовательно, все значения этого поля сделаются с заглавных букв.
NB: Строковые функции в SQL не изменяют данные непосредственно в вашей таблице, а лишь выводят результат. Они берут значение какой-то записи, предположим Peter Yan, увеличивают его, и не вставляют его обратно на место, а выводят его в то место, где они вызывались.
#Пример:
| mysql> SELECT UPPER(names_list) FROM users; | //нажимаем Enter и смотрим
| ||||||||
| mysql> SELECT * FROM users; |
|
Идем дальше… У меня есть задача. Дело в том, что мне не очень удобно использовать имена вместе. Мне хотелось бы как-то по отдельности иметь имя и фамилию. Почему? Потому что иногда мне приходится для некоторых людей брать только имена, для некоторых только фамилии. А когда у тебя и имя и фамилия, достаточно сложные запросы получаются. И это на самом деле не очень круто. Поэтому, лучше сразу на небольших этапах роста таблицы нашей БД все сделать по атомарности данных. Мне удобно, чтобы были отдельно имена и отдельно фамилии. Что для этого сделаем? Для этого сначала мы используем тему прошлого урока…
|
|
|
| mysql> ALTER TABLE users ADD COLUMN fnames_list VARCHAR(20) NOT NULL, ADD COLUMN lnames_list VARCHAR(20) NOT NULL; | |
| mysql> SELECT * FROM users; | Теперь выведем таблицу. |
| id | names_list | fnames_list | lnames_list |
| Peter Yan | |||
| Lola Vin | |||
| Jimmy Voode |
Теперь SUBSTRING_INDEX – позволяет вам выбрать весь текст до, либо после разделителя. Давайте посмотрим на структуру нашего поля. Как мы видим, во всех трех записях у нас сохраняется следующая структура имя, пробел, фамилия. И это позволяет нам выбрать, предположим, пробел в качестве разделителя. И когда у нас так все красиво оформлено, выбрав пробел в качестве разделителя, мы можем выделить только имена, либо только фамилию. И сейчас вы увидите, как это делается.
| mysql> UPDATE users SET fnames_list = SUBSTRING_INDEX(names_list, ' ', 1); | где 1 – если номер положительный, то берется текст слева от него, если отрицательный, то справа от него |
| mysql> UPDATE users SET lnames_list = SUBSTRING_INDEX(names_list, ' ', -1); |
| id | names_list | fnames_list | lnames_list |
| Peter Yan | Peter | Yan | |
| Lola Vin | Lola | Vin | |
| Jimmy Voode | Jimmy | Voode |
Идем дальше…
mysql> SELECT RIGHT(names_list, 2) FROM users;
| mysql> SELECT LEFT(names_list, 3) FROM users;
|
| mysql> UPDATE users SET names_list = RIGHT(names_list, 2); | Если мы хотим заполнить все это поле, какими-то символами либо слева, либо справа – это пишется таким образом. |
| id | names_list | fnames_list | lnames_list |
| an | Peter | Yan | |
| in | Lola | Vin | |
| de | Jimmy | Voode |
А теперь давайте поиграемся с LOWER.
| mysql> UPDATE users SET names_list = UPPER(names_list) WHERE id = 1; | Давайте в верхний регистр возведем только то значение, где id = 1 |
|
|
|
| id | names_list | fnames_list | lnames_list |
| AN | Peter | Yan | |
| in | Lola | Vin | |
| de | Jimmy | Voode |
По поводу RTRIM / LTRIM
Допустим у нас есть таблица где хранится логин и пароль. Предположим, что в логине у нас встречаются имена с пробелами, стоящими как перед, так и после слова. Нам нужно их удалить.
Пример, символ нижнего подчеркивания «_» за пробел.
А теперь удалим ненужное поле names_list
| mysql> ALTER TABLE users DROP COLUMN names_list; |
| id | fnames_list | lnames_list |
| Peter | Yan | |
| Lola | Vin | |
| Jimmy | Voode |
|
|
|
