general mysql-server infos

login to sql-server
mysql -u -p

overview
SHOW SCHEMAS;
SHOW DATABASES;
SHOW TABLES;

get server variables
SHOW VARIABLES;

get server version
SHOW VARIABLES LIKE "%version%";

select values without a from section
SELECT ‚blub‘;

table infos – table size

SELECT 
    TABLE_NAME AS `Table Name`, 
    TABLE_ROWS AS `Rows`,
    (FORMAT((DATA_LENGTH + INDEX_LENGTH) / POWER(1024,2),2)) AS `Size Mb`
FROM 
    information_schema.TABLES
WHERE 
    TABLES.TABLE_SCHEMA = ‚my_schema‘
    AND TABLES.TABLE_TYPE = ‚BASE TABLE‘
;



MySQL and the Command Line
Sample with the test.sql:
    SELECT COUNT(*) FROM a.testtable;

    mysql test.log

if you want to run scripts inside mysql do
    source


LOAD DATA
One problem can be importing from a data file but the output from the loader is:
Query OK, 0 rows affected (0.00 sec)
The solution can be to define the correct LINE TERMINATOR

LINES TERMINATED BY ‚\r‘ — CR – Line Terminator (OS X)
LINES TERMINATED BY ‚\n‘ — LF – Line Terminator (UNIX)
LINES TERMINATED BY ‚\r\n‘ — CR+LF – Line Terminator (WINDOWS)
To indicate the Line Terminator use eg Notepad++ 

sample:
use test;

create table test1
(
id int,
nr int
)
;

truncate table test1;

/* nano test1.csv
id,nr
2,1
1,2
3,3
*/

load data infile ‚/tmp/test1.csv‘
into table test.test1
fields terminated by ‚,‘ 
optionally enclosed by ‚“‚ 
lines terminated by ‚\n‘ ignore 1 lines
(
`id`
, `nr`
)
;

SELECT * FROM test1;
SELECT COUNT(*) FROM test1;

EXPORT DATA
To export data to CSV-files use:

select * 
INTO OUTFILE ‚/tmp/sqlout.csv‘ 
FIELDS TERMINATED BY ‚;‘ 
OPTIONALLY ENCLOSED BY ‚“‚ 
ESCAPED BY ‚\\‘ 
LINES TERMINATED BY ‚\n‘ 
FROM v_locationdata;

PYTHON
install python connector – first search the correct package name
sudo apt-cache search MySQLdb
then install the package
sudo apt-get install python_mysqldb

now go to python
import _mysql
create a connection to your mysql-server and database
db=_mysql.connect(db=“test“)
now set the query
db.query(„““SELECT * FROM test1″““)
temp save the result to r
r=db.store_result()
and now read the result
r.fetch_row()

Advertisements

Kommentar verfassen

Trage deine Daten unten ein oder klicke ein Icon um dich einzuloggen:

WordPress.com-Logo

Du kommentierst mit Deinem WordPress.com-Konto. Abmelden / Ändern )

Twitter-Bild

Du kommentierst mit Deinem Twitter-Konto. Abmelden / Ändern )

Facebook-Foto

Du kommentierst mit Deinem Facebook-Konto. Abmelden / Ändern )

Google+ Foto

Du kommentierst mit Deinem Google+-Konto. Abmelden / Ändern )

Verbinde mit %s