Mysql: Difference between revisions

From Halfface
Jump to navigation Jump to search
No edit summary
No edit summary
Line 4: Line 4:


=== Useful commands ===
=== Useful commands ===
*Enable access from remote hosts.
Remove
*backup all databases.
*backup all databases.
  mysqldump --all-databases -p > /temp/mysql
  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.
mysql>
*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


== Web resources ==
== Web resources ==

Revision as of 13:09, 25 October 2007

Introduction

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

; # Show information about table. mysql> show create table
# Show more information about table. mysql>
  • 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

Web resources