Mysql
Revision as of 13:13, 25 October 2007 by 217.10.60.85 (talk)
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 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