Mysql: Difference between revisions
Jump to navigation
Jump to search
# Show more information about table.
mysql>
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- Putt some data into a table.
- Change id value 17 to 18 in testtable
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