Mysql

From Halfface
Revision as of 13:09, 25 October 2007 by 217.10.60.85 (talk)
Jump to navigation Jump to search

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