Примеры MySQL запросов

Приведу ниже пример некоторых стандартных MySQL запросов.

Подключение к базе данных:

<?php
$link = mysql_connect("localhost", "mysql_user", "mysql_password") or die("Could not connect: " . mysql_error());
mysql_close($link);
?>

Выбор базы данных:

<?php
 mysql_select_db ("auth", $link);
?>

Вывод количества записей в таблице:

SELECT count(*) FROM таблица1;

Удаление таблицы:

DROP TABLE таблица1;

Удаление строки с id=25 в таблица1:

DELETE from таблица1 where id = '25';

Изменение кодировки базы данных:

ALTER DATABASE `characters` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

Создание таблицы:

CREATE TABLE таблица1 (
id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
поле2 CHAR(60) NOT NULL,
PRIMARY KEY (id)
);

Сортировка (ORDER BY):

SELECT `поле1`, `поле2`, `поле3` FROM `таблица1` ORDER BY `поле2`; //выбирает поля из таблицы1 и сортирует их по полю2.
SELECT `поле1` FROM `таблица1` WHERE поле1 = 'а%' ORDER BY `поле2` ASC LIMIT 20; //выбирает 20 строк которые начинаются с буквы а и сортирует их по полю2 по возрастанию.

ASC – по возрастанию и DESC – по убыванию. По-умолчанию данные сортируются по возрастанию.

Изменение данных:

UPDATE таблица1 set поле1 = 'текст' where id = '25'

Смотрите также мои статьи:
SQL запросы для ABillS
SQL запросы для АСРМ «Ника»

Как вытащить данные из одного столбца mysql таблицы

Чтобы увидеть данные из одно столбца, достаточно выполнить SQL запрос (где «abcd» — имя столбца в таблице):

SELECT abcd FROM table;

Для экспорта в файл достаточно выполнить команду в Linux:

mysql -u root -e "SELECT abcd FROM database;" -s -N > file.txt

Приведу пример экспорта email адресов из mysql таблицы на http страницу используя PHP.
Мысль сразу пришла такого плана (создаем php файл и открываем его через браузер):

<?php
// Соединяемся с mysql сервером
mysql_connect("localhost", "USER", "PASSWORD") or die (mysql_error ());
// Выбираем базу данных
mysql_select_db("users") or die(mysql_error());
// SQL-запрос
$rows = "SELECT * FROM account";
// Выполняем этот SQL-запрос
$d = mysql_query($rows);
// Каждый ряд становится массивом ($row) с помощью функции mysql_fetch_array
while($row = mysql_fetch_array($d)) {
// Выводим значения столбца email
echo $row['email'] . "<br />";
}
// Закрываем соединение с базой данных
mysql_close();
?>

online.pl скрипт количества пользователей онлайн

Приведу пример скрипта выполняющего sql запрос в mysql базу с выводом результата. Можно прицепить его к графикам Zabbix, Cacti и т.д. чтобы мониторить например количество игроков онлайн на каком нибудь игровом сервере.

#!/usr/bin/perl
use DBI;
my $host = "localhost";
my $port = "3306";
my $user = "root";
my $pass = "";
my $db = "test";
$dbh = DBI->connect("DBI:mysql:$db:$host:$port",$user,$pass);
$sth = $dbh->prepare("SELECT count(id) FROM `online`");
$sth->execute;
while ($ref = $sth->fetchrow_arrayref) {
print "$$ref[0]\n";
}
$rc = $sth->finish;
$rc = $dbh->disconnect;

Установка и настройка MySQL сервера на Ubuntu

Установка сервера и клиента чтобы можно было через консоль заливать файлы:

sudo apt-get install mysql-server mysql-client

Директория с файлами конфигурации находится по адресу:

/etc/mysql

Директория хранения баз данных:

/var/lib/mysql

Чтобы разрешить удаленный доступ к mysql серверу, в конфигурационном файле /etc/mysql/my.cnf необходимо закомментировать строку bind-address = 127.0.0.1 (поставить перед ней символ #) и выполнить перезапуск mysql сервера чтобы изменения применились:

sudo service mysql restart

Можно установить пароль root, удалить базу test, запретить удаленный доступ root пользователю и настроить прочие параметры безопасности выполнив команду:

sudo mysql_secure_installation

Покажу некоторые примеры команд:
Подключение к mysql через командную строку:

mysql -u имяпользователя -p

Просмотр доступных баз данных:

show databases;

Открываем нужную базу данных:

use имябазы;

Смотрим какие в базе есть таблицы:

show tables;

Либо создаем новую базу:

create database имябазы;

Удаление базы:

drop database имябазы;

Создание пользователя:

CREATE USER 'имяпользователя'@'localhost' IDENTIFIED BY 'пароль';

Даем пользователю полные привилегии над нужной базой:

GRANT ALL PRIVILEGES ON имябазы.* to 'имяпользователя'@'localhost';

Создание дампа базы данных (будет сохранен в домашнюю папку пользователя):

mysqldump -u имяпользователя -p имябазы > имядампа.sql

Заливаем дамп в нужную базу:

mysql -u имяпользователя -p имябазы < имядампа.sql

Команда справки — help

Для безопасности подключаемся и удаляем стандартную базу test:

mysql -uroot -p
DROP DATABASE test;

Смотрим привилегии для пустого пользователя и если он существует, то удаляем:

SHOW GRANTS FOR ''@'localhost'
DROP USER "";
quit

Смотрим какие существуют пользователи и какие у них привилегии:

use mysql;
select * from user;

Проверяем запущен ли сервер:

sudo netstat -tap | grep mysql

Проверяем соединения на порте 3306:

netstat -na | grep 3306

Можно посмотреть пакеты на порт 3306, когда возникают проблемы с подключением к mysql из вне:

tcpdump port 3306
tcpdump port 3306 -i eth0

Смотрите также:
IPTables правила для MySQL
Восстановление MySQL таблиц
Прочее

Исправление ошибки MySQL: Can’t create table ‘tablename’ (errno: 150)

Ошибка MySQL: Can’t create table ‘tablename’ (errno: 150) может возникать когда вы например заливаете бекап и MySQL питается создать таблицу 1 которая ссылается на таблицу 2, которой по сути еще нету. Решить можно вставив в бекап строки:
в начале
SET FOREIGN_KEY_CHECKS = 0;
и в самом конце
SET FOREIGN_KEY_CHECKS = 1;