Mysql

Mysql

Useful commands

• Enable access from remote hosts.

Remove 

• backup all databases.

mysqldump --all-databases -p > /temp/mysql

• Create database.

mysql> create database puppetdb;

• Create table.

This creates table with thwo colums, id with datatype int, and data of the type varchar.
mysql> create table testtable (id int, data varchar(100));

• Connect to mysql database.

mysql -u root -p password -h mysqlhost

• Show databases.

mysql> show databases;

• Choose database to work with.

mysql> use puppetdb;

• Show tables.

mysql> show tables;

• Show columns in table.

mysql> show columns from hosts;

• Show which data is stored in a table in descending order limit to 100.

mysql> select * from hosts order by id desc limit 100;

• Show all data from table.

mysql> select * from hosts;

• Show values from one column.

mysql> select id from hosts;

• Show selected colums.

mysql> select name,ip from hosts;

• Show all ip in ascending order.

mysql> select * from hosts order by ip asc;

• Show column page_title in table page where page_counter is equal to 17.

mysql> select page_title from page where page_counter='17';
mysql> select page_title from page where page_counter='17' and page_is_new='1'; # and page_is_new equal to 1.

• Show column where page_counter is max in table page. Other values are min, avg, sum

mysql> select max(page_counter) from page;

• How many rows are on the table.

mysql> select count(page_counter) from page;
mysql> select count(*) from page;

• Commands.

mysql> exit;quit;                # exit mysql tool.
mysql> help;                     # Show help
mysql> source /file              # Run sql code. Offen used to setup database.
mysql> system command            # Run command in system.
mysql> status;                   # Shows status of database server.
mysql> desc table;               # Show information about table.
mysql> show create table table   # Show more information about table.

• Putt some data into a table.

mysql> INSERT INTO testtable (id,data) VALUES ('66','The beast whas here');

• Change id value 17 to 18 in testtable

mysql> UPDATE testtable set id=18 where id=17 ;

Data types

• Numerical, signed=could be negative,unsigned

type		Maxvalue
tinyint	255
smallint	65535
mediumint	16777215
int		4294967295
bigint		18446744073709551615

• Strings

type			Maxvalue
varchar		255
char			255
tinyblob,tinytext	256
blob,text		65536
mediumblob,mediumtext	16777216
longblob,longtext	4294967296

• Special

date
timestamp