Меню

Sql как посмотреть структуру таблиц



Основные команды SQL, которые должен знать каждый программист

Основные команды SQL, которые должен знать каждый программист

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

Обратите внимание, что в примерах используется MySQL, поэтому в конце каждого оператора ставится точка с запятой.

Примечание Вы читаете улучшенную версию некогда выпущенной нами статьи.

Настройка базы данных

Перед началом создайте БД с тестовыми данными. Для работы вам понадобится скачать два файла: DLL.sql и InsertStatements.sql. После установите MySQL, откройте терминал и войдите в консоль MySQL с помощью команды:

Затем введите пароль и выполните следующую команду. Назовём базу данных «university»:

SHOW DATABASES

SQL-команда, которая отвечает за просмотр доступных баз данных.

CREATE DATABASE

Команда для создания новой базы данных.

С помощью этой SQL-команды USE выбирается база данных, необходимая для дальнейшей работы с ней.

SOURCE

А SOURCE позволит выполнить сразу несколько SQL-команд, содержащихся в файле с расширением .sql.

DROP DATABASE

Стандартная SQL-команда для удаления целой базы данных.

SHOW TABLES

С помощью этой несложной команды можно увидеть все таблицы, которые доступны в базе данных.

CREATE TABLE

SQL-команда для создания новой таблицы:

Ограничения целостности при использовании CREATE TABLE

Может понадобиться создать ограничения для определённых столбцов в таблице. При создании таблицы можно задать следующие ограничения:

  • ячейка таблицы не может иметь значение NULL ;
  • первичный ключ — PRIMARY KEY(col_name1, col_name2, …) ;
  • внешний ключ — FOREIGN KEY(col_namex1, …, col_namexn) REFERENCES table_name(col_namex1, …, col_namexn) .

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

Пример

Создайте таблицу «instructor»:

DESCRIBE

С помощью DESCRIBE

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

INSERT

Команда INSERT INTO

в SQL отвечает за добавление данных в таблицу:

При добавлении данных в каждый столбец таблицы не требуется указывать названия столбцов.

UPDATE

SQL-команда для обновления данных таблицы:

DELETE

SQL-команда DELETE FROM

используется для удаления данных из таблицы.

DROP TABLE

А так можно удалить всю таблицу целиком.

SELECT

Далее мы рассмотрим основные команды SQL, которые позволяют работать непосредственно с данными. К одной из таких SQL-команд относится SELECT для получения данных из выбранной таблицы:

Следующей командой можно вывести все данные из таблицы:

SELECT DISTINCT

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

WHERE

Можно использовать ключевое слово WHERE в SELECT для указания условий в запросе:

В запросе можно задавать следующие условия:

  • сравнение текста;
  • сравнение численных значений;
  • логические операции AND (и), OR (или) и NOT (отрицание).

Пример

Попробуйте выполнить следующие команды. Обратите внимание на условия, заданные в WHERE :

SQL-команды: пример вывода с WHERE

GROUP BY

Оператор GROUP BY часто используется с агрегатными функциями, такими как COUNT , MAX , MIN , SUM и AVG , для группировки выходных значений.

Пример

Выведем количество курсов для каждого факультета:

SQL-команды: пример вывода с GROUP BY

HAVING

Ключевое слово HAVING было добавлено в SQL по той причине, что WHERE не может использоваться для работы с агрегатными функциями.

Пример

Выведем список факультетов, у которых более одного курса:

SQL-команды: пример вывода с HAVING

ORDER BY

ORDER BY используется для сортировки результатов запроса по убыванию или возрастанию. ORDER BY отсортирует по возрастанию, если не будет указан способ сортировки ASC или DESC .

Пример

Выведем список курсов по возрастанию и убыванию количества кредитов:

BETWEEN

BETWEEN используется для выбора значений данных из определённого промежутка. Могут быть использованы числовые и текстовые значения, а также даты.

Пример

Выведем список инструкторов, чья зарплата больше 50 000, но меньше 100 000:

Читайте также:  Физического развития взрослые центильные таблицы

Оператор LIKE используется в WHERE , чтобы задать шаблон поиска похожего значения.

Есть два свободных оператора, которые используются в LIKE :

  • % (ни одного, один или несколько символов);
  • _ (один символ).

Пример

Выведем список курсов, в имени которых содержится «to» , и список курсов, название которых начинается с «CS-» :

SQL-команды: пример вывода с LIKE

С помощью IN можно указать несколько значений для оператора WHERE :

Пример

Выведем список студентов с направлений Comp. Sci., Physics и Elec. Eng.:

JOIN используется для связи двух или более таблиц с помощью общих атрибутов внутри них. На изображении ниже показаны различные способы объединения в SQL. Обратите внимание на разницу между левым внешним объединением и правым внешним объединением:

SQL-команды: схема использования JOIN

Пример

Выведем список всех обязательных курсов и детали о них:

SQL-команды: пример вывода с JOIN

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

Создание

Удаление

Агрегатные функции

Это не совсем основные команды SQL, однако знать их тоже желательно. Агрегатные функции используются для получения совокупного результата, относящегося к рассматриваемым данным:

  • COUNT(col_name) — возвращает количество строк;
  • SUM(col_name) — возвращает сумму значений в данном столбце;
  • AVG(col_name) — возвращает среднее значение данного столбца;
  • MIN(col_name) — возвращает наименьшее значение данного столбца;
  • MAX(col_name) — возвращает наибольшее значение данного столбца.

Вложенные подзапросы

Вложенные подзапросы — это SQL-запросы, которые включают выражения SELECT , FROM и WHERE , вложенные в другой запрос.

Пример

Найдём курсы, которые преподавались осенью 2009 и весной 2010 годов:

Источник

MySQL список таблиц и их структура

Команды для работы со списком таблиц и их структурой.

список баз данных

список таблиц в базе

список столбцов в таблице

показать структуру таблицы в формате «CREATE TABLE»

привилегии для пользователя.

значения системных переменных

статистика по mysqld процессам

статистика по всем таблицам в базе

статистика по всем таблицам wp_ в базе

Проверка настроек кодировки

Проверка настроек кодировки

Проверка кодировки текущей базы данных

Для настройки кодировки utf8 по умолчанию
Необходимо внести следующие изменения в файл my.cnf (my.ini):

[mysqld]

[client]
default-character-set = utf8

Информация об установленной версии pkg_info | grep mysql (FreeBSD)

добавить столбец в таблице в базе mysql

переименование столбцов в таблице

внести изменения в столбцы таблицы

В будущем, если включите innodb_file_per_table, сможете уменьшать объем tablespace путем
пересоздания таблиц сразу в InnoDB (т.к. в режиме файл-на-таблицу файл при удалении таблицы
стирается) командой

Определение размера таблицы

Показывает обьем и количество строк в таблицах MySQL.

Перемещение таблицы MySQL в другую базу

Копирование таблицы MySQL в другую базу

Копирование таблицы MySQL в другую базу с условием

Копирование необходимых столбцов таблицы MySQL в другую базу

database1— база из которой копируем

database2 — база в которую копируем
table — название таблицы

Текущее состояние кэша

Посмотреть состояние кэша можно с помощью запроса:

  • Qcache_free_memory — объем свободной памяти, отведенной под кэш.
  • Qcache_hits — количество запросов, отработанных из кэша.
  • Qcache_inserts — количество вставок запросов в кэш.
  • Qcache_lowmem_prunes — количество высвобождений памяти из-за наполненности кэша.
  • Qcache_not_cached — количество запросов, не подлежащих кэшированию.
  • Qcache_queries_in_cache — количество запросов, находящихся в кэше в настоящее время.

Мерой эффективности кэша может служить отношение Qcache_hits / (Qcache_inserts + Qcache_not_cached).

Читайте другие интересные статьи

Понравилась статья, расскажи о ней друзьям, нажми кнопку!

Источник

Sql как посмотреть структуру таблиц

Команды для работы со списком таблиц и их структурой.

список баз данных

список таблиц в базе

список столбцов в таблице

показать структуру таблицы в формате «CREATE TABLE»

Читайте также:  Разрывы таблицы ворд как устранить

привилегии для пользователя.

значения системных переменных

статистика по mysqld процессам

статистика по всем таблицам в базе

статистика по всем таблицам wp_ в базе

Проверка настроек кодировки

Проверка настроек кодировки

Проверка кодировки текущей базы данных

Для настройки кодировки utf8 по умолчанию
Необходимо внести следующие изменения в файл my.cnf (my.ini):

[mysqld]

[client]
default-character-set = utf8

Информация об установленной версии pkg_info | grep mysql (FreeBSD)

добавить столбец в таблице в базе mysql

переименование столбцов в таблице

внести изменения в столбцы таблицы

В будущем, если включите innodb_file_per_table, сможете уменьшать объем tablespace путем
пересоздания таблиц сразу в InnoDB (т.к. в режиме файл-на-таблицу файл при удалении таблицы
стирается) командой

Определение размера таблицы

Показывает обьем и количество строк в таблицах MySQL.

Перемещение таблицы MySQL в другую базу

Копирование таблицы MySQL в другую базу

Копирование таблицы MySQL в другую базу с условием

Копирование необходимых столбцов таблицы MySQL в другую базу

database1— база из которой копируем

database2 — база в которую копируем
table — название таблицы

Текущее состояние кэша

Посмотреть состояние кэша можно с помощью запроса:

  • Qcache_free_memory — объем свободной памяти, отведенной под кэш.
  • Qcache_hits — количество запросов, отработанных из кэша.
  • Qcache_inserts — количество вставок запросов в кэш.
  • Qcache_lowmem_prunes — количество высвобождений памяти из-за наполненности кэша.
  • Qcache_not_cached — количество запросов, не подлежащих кэшированию.
  • Qcache_queries_in_cache — количество запросов, находящихся в кэше в настоящее время.

Мерой эффективности кэша может служить отношение Qcache_hits / (Qcache_inserts + Qcache_not_cached).

Читайте другие интересные статьи

Понравилась статья, расскажи о ней друзьям, нажми кнопку!

Источник

Полезные команды MySQL

Авторизация на сервере (из консоли), -h при необходимости авторизации на удалённом сервере

mysql -h hostname -u root -p

Создание БД

mysql> create database `databasename`;

Создание БД с указанием необходимой кодировки

mysql> create database `databasename` default character set ‘utf8’ collate ‘utf8_unicode_ci’;

Получить список всех БД на сервере

mysql> show databases;

Переключится на БД

mysql> use `db name`;

Получить список таблиц в базе

mysql> show tables;

Посмотреть структуру таблицы

mysql> describe `table name`;

Ещё один вариант

mysql> show columns from `table name`;

Удалить БД

mysql> drop database `database name`;

Удалить таблицу

mysql> drop table `table name`;

Показать все данные в таблице

mysql> SELECT * FROM `table name`;

Показать строки, где поле `field name` имеет значение «whatever».

mysql> SELECT * FROM `table name` WHERE `field name` = ‘whatever’;

Показать строки с именем «Bob» и номеном «3444444»

mysql> SELECT * FROM `table name` WHERE name = ‘Bob’ AND phone_number = 3444444;

Показать строки с номером «3444444» не содержащие имени «Bob» отсортированные по номеру.

mysql> SELECT * FROM `table name` WHERE name != ‘Bob’ AND phone_number = 3444444 order by phone_number;

Показать записи с именем, начинающимся на «bob» и номером 3444444

mysql> SELECT * FROM `table name` WHERE name like ‘Bob%’ AND phone_number = 3444444;

Верннуть все данные с именем, начинающемся на «bob» и номером 3444444 ограничить вывод пятью первыми строками

mysql> SELECT * FROM `table name` WHERE name like ‘Bob%’ AND phone_number = 3444444 limit 0,5;

Используем регулярное выражение. Для регистрозависимого выбора используйте «REGEXP BINARY». Данный запрос найдёт все записи, начинающиеся на «a»

mysql> SELECT * FROM `table name` WHERE rec RLIKE ‘^a’;

Показать уникальные записи

mysql> SELECT DISTINCT `column name` FROM `table name`;

Показать выбранные колонки отсортированные от а до я (ASC) или от я до а (DESC)

mysql> SELECT `col1`,`col2` FROM `table name` ORDER BY `col2` DESC;

Вернуть количество строк в таблице.

mysql> SELECT COUNT(*) FROM `table name`;

Читайте также:  Помощь с контрольной работой по БЖД ИОП
Просуммировать все числовые поля таблицы

mysql> SELECT SUM(*) FROM `table name`;

Объединение таблиц. Как работает JOIN (в картинках)

mysql> select lookup.illustrationid, lookup.personid,person.birthday from lookup left join person on lookup.personid=person.personid=statement to join birthday in person table with primary illustration id;

Создание пользователя. Вход под root. Переключение на БД mysql. Создание пользователя и обновление привилегий.

mysql -u root -p mysql> use mysql;
mysql> INSERT INTO user (Host,User,Password) VALUES(‘%’,’username’,PASSWORD(‘password’));
mysql> flush privileges;

Смена пароля пользователя из консоли

mysqladmin -u username -h hostname -p password ‘new-password’

Смена пароля пользователя из консоли MySQL. Вход как root. Смена пароля. Обновление привелегий.

mysql -u root -p mysql> SET PASSWORD FOR ‘user’@’hostname’ = PASSWORD(‘passwordhere’);
mysql> flush privileges;

Восстановление пароля root пользователя. Остановить MySQL сервер. Запустить с пониженной безопасностью. Залогинится на MySQL как root. Установить новый пароль. Разлогинится и перезапустить MySQL сервер.

/etc/init.d/mysql stop
mysqld_safe —skip-grant-tables &
mysql -u root mysql> use mysql;
mysql> update user set password=PASSWORD(‘newrootpassword’) where User=’root’;
mysql> flush privileges;
mysql> quit /etc/init.d/mysql stop
/etc/init.d/mysql start

Установка пароля root если он ещё не задавался ранее

mysqladmin -u root password newpassword

Смена пароля root

mysqladmin -u root -p oldpassword newpassword

Разрешить пользователю «Bob» подключаться к серверу c локального адреса с паролем «passwd». Войти как root. Переключиться на БД mysql. Дать привилегии. Обновить привелегии.

mysql -u root -p mysql> use mysql;
mysql> grant usage on *.* to bob@localhost identified by ‘passwd’;
mysql> flush privileges;

Предоставить пользователю привилегии на БД. Авторизоваться как root. Переключиться на БД mysql. Предоставить привилегии. Обновить кеш привилегий.

mysql -u root -p mysql> use mysql;
mysql> INSERT INTO user (Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv) VALUES (‘%’,’databasename’,’username’,’Y’,’Y’,’Y’,’Y’,’Y’,’N’);
mysql> flush privileges;

mysql> grant all privileges on databasename.* to username@localhost;
mysql> flush privileges;

Обновить информацию для существующего пользователя

mysql> use mysql;
mysql> UPDATE `user` SET Select_priv = ‘Y’,Insert_priv = ‘Y’,Update_priv = ‘Y’ where `User` = ‘user’;
flush privileges;

Удалить строки из таблицы

mysql> DELETE from `table name` where `field_name` = ‘whatever’;

Обновить кеш привилегий

mysql> flush privileges;

Удалить колонку из таблицы

mysql> alter table `table name` drop column `column name`;

Добавить колонку в таблицу

mysql> alter table `table name` add column `new column name` varchar (20);

Переименовать колонку

mysql> alter table `table name` change `old column name` `new column name` varchar (50);

Сделать данные в колоке уникальными (если дублирующиеся уже есть — будет ошибка)

mysql> alter table `table name` add unique (`column name`);

Модифицировать колонку

mysql> alter table `table name` modify `column name` VARCHAR(3);

Удалить индекс

mysql> alter table `table name` drop index `colmn name`;

Загрузить данные в БД из CSV файла.

mysql> LOAD DATA INFILE ‘/tmp/filename.csv’ replace INTO TABLE `table name` FIELDS TERMINATED BY ‘,’ LINES TERMINATED BY ‘\n’ (field1,field2,field3);

Сделать дамп всех БД для бэкапа. Бэкап это файл с SQL командами для воссоздания всех баз.

mysqldump -u root -p —opt > /tmp/alldatabases.sql

Сделать дамп одной базы.

mysqldump -u username -p —databases databasename > /tmp/databasename.sql

Сделать дамп одной таблицы

mysqldump -c -u username -p databasename tablename > /tmp/databasename.tablename.sql

Восстановить БД (или таблицу) из бэкапа

mysql -u username -p databasename CREATE TABLE `table name` (
`firstname` VARCHAR(20),
`middleinitial` VARCHAR(3),
`lastname` VARCHAR(35),
`suffix` VARCHAR(3),
`officeid` VARCHAR(10),
`userid` VARCHAR(15),
`username` VARCHAR(8),
`email` VARCHAR(35),
`phone` VARCHAR(25),
`groups` VARCHAR(15),
`datestamp` DATE,
`timestamp` time,
`pgpemail` VARCHAR(255)
);

Создание таблицы, пример 2.

mysql> CREATE TABLE `table name` (
personid int(50) not null auto_increment primary key,
firstname VARCHAR(35),
middlename VARCHAR(50),
lastname VARCHAR(50) default ‘bato’
);

Источник