↑↑↑

↓↓↓
Flag of Russia Flag of UK
🏠 | 💻 PC | Databases |
1MySQL
2MS SQL
3MS SQL Errors
4Postgres


How to connect to you MySQL Database
Type in console
mysql -h ip_Data_Base_Name -u user_name -p
Press Enter and type in your password
Enter
If you are connectin to a remote db, e.g. of your website and you do not know the IP - ping it by domain name
ping your_domain_name
For example website AskQA.ru has IP 87.236.19.34
After entering the password you shoul see the MySQL welcome window MySQL welcome window image from www.AskQA.ru
To list all the databases run
show databases;
Do not forget to put semicolon at the end
Choose the DB
use DB_name


Get lis of tables in MySQL DB
Run
show tables;

🔝

Check MySQL table content
To check table structure run
describe table_name;
To check table content run
SELECT * FROM table_name;
To list rows by ID run
SELECT * FROM table_name ORDER BY ID;
Reverse order by ID:
SELECT * FROM table_name ORDER BY ID DESC;
Example of request when we want to list only Chineese manufacturers
SELECT * FROM table_manuf WHERE (country='China');
Request that lists only countries with 7 or 358 code
SELECT * FROM table_countries WHERE (code='7' OR code='358');


How to rename table in MySQL
Rename table car to auto

ALTER TABLE (mysql.ru)
ALTER TABLE car RENAME auto;


Add column to MySQL table
Add new TEXT-type column with name «Body»:

Further reading - Table types in (mysql.ru)
ALTER TABLE table_name ADD Body text;


Add line to DB table
To add line to the table «Table_name_AAA»
INSERT INTO Table_name_AAA VALUES(100, 'Company', 'Country', 'Code');

🔝

Delete line from DB table
Delete line with ID 1 in table Table_name_AAA
DELETE FROM Table_name_AAA WHERE ID='1';


Change value in MySQL table
Change Country to Russia for the line with ID 7
UPDATE Table_name_AAA SET Country = 'Russia' WHERE ID = '7';


How to make request to the DB from the website
<?php
$connection = mysql_connect("localhost", "DB_name" , "DB_password for localhost connection");
$db = mysql_select_db("DB_name");
if(!$connection || !$db)
{
mysql_error();
}
?>
<?php
mysql_connect('localhost','DB_name','DB_password for localhost connection');
mysql_select_db('DB_name');
$res = mysql_query("SELECT * FROM `Table_name`") or die(mysql_error());
echo
'<table border="1">
<tr>
<td>ID</td>
<td>Company</td>
<td>Country</td>
</tr>';
while($row = mysql_fetch_assoc($res)) {
echo '
<tr>
<td>'.$row['ID'].'</td>
<td>'.$row['Company'].'</td>
<td>'.$row['Country'].'
</tr>';
}
echo '
</table>';
echo'<form enctype="multipart/form-data" action="php_scripts/sort_01_mysql.php" method="get"></br> <input type="submit" value="Sort"></form>'; echo'<form enctype="multipart/form-data" action="php_scripts/clear_filter_01_mysql.php" method="get"></br> <input type="submit" value="Clear Filtering"></form>';



www.php.su

www.quizful.net
To connect to Microsoft MS SQL DB you need to install software docs.microsoft.com

🔝

Errors

1MySQL
2MS SQL
3MS SQL Errors
4Postgres

MS SQL

habrahabr.ru



Вывести на экран список таблиц MS SQL
SELECT TABLE_NAME FROM information_schema.tables

select table_name, column_name from information_schema.columns;

🔝

1MySQL
2MS SQL
3MS SQL Errors
4Postgres

Ошибки MS SQL

server management studio error 4064

Если при попытке соединения с базой данных Вы получаете следующую ошибку:

Cannot open user default database. Login failed.
Login failed for user ‘UserName’. (Microsoft SQL Server, Error: 4064)
Microsoft SQL Server Management Studio Error 4064

Перейдите в Options

Microsoft SQL Server Management Studio SQL Server Options

Если в поле Connect to database: стоит <default> замените его на имя Вашей базы данных.

Microsoft SQL Server Management Studio Error 4064 SQL Server Connection Properties

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

Microsoft SQL Server Management Studio Error 4064 SQL Server Connection Properties
1MySQL
2MS SQL
3MS SQL Errors
4Postgres

🔝

Postgres
Получить список таблиц Postgres
select table_name from information_schema.tables;
Получить список таблиц и столбцов Postgres
select table_name, column_name from information_schema.columns;
Получить список таблиц исключая служебные и отсортировать по имени таблицы
select table_name from information_schema.tables where table_schema='public' ORDER BY table_name;
Выбрать из таблицы диапазон значений
select * from table where (number > ‘111’ and number < ‘100500’) order by number;



Share in social media: