MySQL

Connecting to MySQL Database

From the console, dial

mysql -h your_database_ip -u user_name -p

Press Enter and enter the password.

If you connect to the database of your site and do not know the ip do in the console.

ping your_site_name

For example, the site eth1.ru has IP 87.236.19.34

After entering the password, a MySQL greeting should appear.

All databases can be viewed using commands.

show databases;

Notice the semicolon at the end.

Select a specific DB

use database_name

How to find out which tables are in the MySQL database

View the list of tables contained in the database

show tables;

How to view the contents of the MySQL table

View the structure of a certain table

describe table_name;

View the content of a certain table

SELECT * FROM ;

Ordered by ID output table name table

SELECT * FROM table_name ORDER BY ID;

In reverse order:

SELECT * FROM table_name ORDER BY ID DESC;

Request with condition to show only Chinese manufacturers

SELECT * FROM table_manuf WHERE (country='China');

Request with the condition to show only countries with code 7 or 358

SELECT * FROM table_countries WHERE (code='7' OR code='358');

How to Rename a MySQL Table

Rename the table car to auto using ALTER TABLE (mysql.ru)

ALTER TABLE car RENAME auto;

How to Add a Column to MySQL Table

Add a new TEXT column named Body:

Types of columns (mysql.ru)

ALTER TABLE table_name ADD Body text;

How to add a row to the database table

Add an entry to the table with the name table

INSERT INTO table_name VALUES(100, 'Company', 'Country', 'Something else');

How to delete a row in the database table

Delete the line from ID 1 in the table called Name Table

DELETE FROM table_name WHERE ID='1';

How to change the value in the MySQL table

Change the Country to Russia field with ID 7

UPDATE table_name SET Country = 'Russia' WHERE ID = '7';

How to find all tables with a certain column

Search the entire database for tables that have a column named Name.

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = 'Name';

How to access a database using PHP

<?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>';

http://www.php.su/mysql_query

http://www.quizful.net/test

Examples of skeptics

It is understood that you are working in Linux or Bash under Windows How to install it - read it here

Create a script file

touch script

Edit it, for example, by an editor. vi

vi script

mysql -h XXX.XXX.XXX.XXX -u username -p <<MY_QUERY USE db_name; SHOW tables; MY_QUERY

Where's XXX. XXX. XXX. XXX - IP address of the server on which the database is installed

username should be replaced with the real username of the database

db name - replace the real name DB.

The username and db name are the same.

bash script

Enter password:

Enter the password

Tables_in_db_name
Table01
Table02

Article author: Andrei Olegovich