🏠 | 💻 PC | Databases |
1MySQL
2MS SQL
3MS SQL Errors
4Postgres


How to connect to MySQL DB
Type in terminal
mysql -h your_DB_IP -u username -p
Press and enter the password
Enter
If you are connecting to DB of your site and you do not know the IP run in terminal
ping your_domain_name
For example site andreyolegovich.ru has IP 87.236.19.34
After you enter password MySQL greeting appears
To check all available databases run
show databases;
Pay attention to the semicolon at the end
To choose a specific DB run
use DB_name


How to figure out what tables are in the MySQL DB
To get the list of existing in DB tables
show tables;

🔝

How to check table's content in MySQL
To check what type of content is in specific table run
describe table_name;
To see all the table's contents run
SELECT * FROM table_name;
Sort output of table table_name by ID
SELECT * FROM table_name ORDER BY ID;
In reverse order:
SELECT * FROM table_name ORDER BY ID DESC;
Request that shows only Chinees manufacturers
SELECT * FROM table_manuf WHERE (country='China');
Request to show only countries with code 7 and 358
SELECT * FROM table_countries WHERE (code='7' OR code='358');


How to rename table
To rename table car to table auto

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


How to add a column to MySQL table
Добавить новый столбец типа TEXT с именем Body:

Типы столбцов (mysql.ru)
ALTER TABLE имя_таблицы ADD Body text;


Как добавить строку в таблицу БД
Добавляем запись в таблицу с названием Имя_таблицы
INSERT INTO Имя_таблицы VALUES(100, 'Компания', 'Страна', 'Что-то ещё');

🔝

Как удалить строку в таблице БД
Удалить строку с ID 1 в таблице с названием Имя_таблицы
DELETE FROM Имя_таблицы WHERE ID='1';


Как изменить значение в таблице MySQL
Изменить поле Country на Russia у записи с ID 7
UPDATE имя_таблицы SET Country = 'Russia' WHERE ID = '7';


Как обратиться к базе данных внутри сайта
<?php
$connection = mysql_connect("localhost", "Имя_БД" , "Пароль к БД для localhost подключения");
$db = mysql_select_db("Имя_БД");
if(!$connection || !$db)
{
mysql_error();
}
?>
<?php
mysql_connect('localhost','Имя_БД','Пароль к БД для localhost подключения');
mysql_select_db('Имя_БД');
$res = mysql_query("SELECT * FROM 'Название_таблицы'") 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>';



http://www.php.su/mysql_query

http://www.quizful.net/test
To establish a connection to Microsoft MS SQL you should install. https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-setup-tools#ubuntu

🔝

Errors

1MySQL
2MS SQL
3MS SQL Errors
4Postgres

MS SQL

habrahabr.ru



List all the MS SQL tables on screen
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 Errors

server management studio error 4064

If after attemp to connect ot your DB you get the following error :

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

Go to Options

Microsoft SQL Server Management Studio SQL Server Options

If in the field Connect to database: value is <default> replace it with your DB name

Microsoft SQL Server Management Studio Error 4064 SQL Server Connection Properties

In your case the DB name andreyolegovich.ru_db should be replaced by the name of DB you want to connect to.

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

🔝

Postgres
To get list of tables in Postgres run
select table_name from information_schema.tables;
To get list of tables and their colunms in Postgres
select table_name, column_name from information_schema.columns;
Get the list of public tables
select table_name from information_schema.tables where table_schema='public' ORDER BY table_name;
Choose a range of values fro the table
select * from table where (number > ‘111’ and number < ‘100500’) order by number;



Share in social media: