Hits :
3892
MySQL References
Install
http://www.mysql.com
MySQL 5.1.39
# cd /opt
# wget -c http://ftp.ntua.gr/pub/databases/mysql/Downloads/MySQL-5.1/mysql-5.1.39-linux-i686-glibc23.tar.gz
# tar zxvf mysql-5.1.39-linux-i686-glibc23.tar.gz
# ln -s mysql-5.1.39-linux-i686-glibc23/ mysql
Formatter "highlight/html" not found
top
mysql recover root passwd
Formatter "highlight/html" not found
top
count rows
SELECT COUNT(*) FROM TABLE_NAME;
top
MySQL Version
mysql> SELECT version();
+-----------+
| version() |
+-----------+
| 5.0.22 |
+-----------+
1 ROW IN SET (0.00 sec)
top
MySQL Date – Time
mysql> SELECT CURRENT_DATE, CURRENT_TIME;
+--------------±-------------+
| CURRENT_DATE | CURRENT_TIME |
+--------------±-------------+
| 2007-10-15 | 10:36:16 |
+--------------±-------------+
1 ROW IN SET (0.00 sec)
mysql> SELECT NOW();
+---------------------+
| NOW() |
+---------------------+
| 2007-10-15 10:36:56 |
+---------------------+
1 ROW IN SET (0.00 sec)
top
MySQL Databases
mysql> SHOW DATABASES;
+--------------------+
| DATABASE |
+--------------------+
| information_schema |
| ebal |
+--------------------+
2 ROWS IN SET (0.03 sec)
mysql> USE ebal;
Reading TABLE information FOR completion OF TABLE AND COLUMN names
You can turn off this feature TO GET a quicker startup WITH -A
DATABASE changed
top
MySQL Change Password
mysql> UPDATE USER SET Password=OLD_PASSWORD('test') WHERE USER='root' AND Host='localhost';
Query OK, 0 ROWS affected (0.02 sec)
ROWS matched: 1 Changed: 0 Warnings: 0
mysql> FLUSH PRIVILEGES;
Query OK, 0 ROWS affected (0.00 sec)
top
Data Types
http://dev.mysql.com/doc/refman/5.1/en/data-types.html
Chapter 10. Data Types
Numeric
| Type | Bytes | Minimum Value | Maximum Value |
| | | (Signed/Unsigned) | (Signed/Unsigned) |
| TINYINT | 1 | -128 | 127 |
| | | 0 | 255 |
| SMALLINT | 2 | -32768 | 32767 |
| | | 0 | 65535 |
| MEDIUMINT | 3 | -8388608 | 8388607 |
| | | 0 | 16777215 |
| INT | 4 | -2147483648 | 2147483647 |
| | | 0 | 4294967295 |
| BIGINT | 8 | -9223372036854775808 | 9223372036854775807 |
| | | 0 | 18446744073709551615 |
http://en.wikipedia.org/wiki/S[..]mber_representations[link1]
String
| Value | CHAR(4) | Storage Required | VARCHAR(4) | Storage Required |
| '' | ' ' | 4 bytes | '' | 1 byte |
| 'ab' | 'ab ' | 4 bytes | 'ab' | 3 bytes |
| 'abcd' | 'abcd' | 4 bytes | 'abcd' | 5 bytes |
| 'abcdefgh' | 'abcd' | 4 bytes | 'abcd' | 5 bytes |
http://dev.mysql.com/doc/refma[..]ge-requirements.html[link2]
top
Ελληνικά
greek in mysql
Ελληνικά στην MySQL, δλδ ελληνικό όνομα στην βάση δεδομένων, στον πίνακα, στα πεδία και στα περιεχόμενα !!!
CREATE DATABASE `ελλας` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
CREATE TABLE IF NOT EXISTS `ελληνικά` (
`όνομα` varchar(25) NOT NULL,
`επώνυμο` varchar(50) NOT NULL,
`τηλ` char(10) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
INSERT INTO `ελληνικά` (`όνομα`, `επώνυμο`, `τηλ`) VALUES
('ευάγγελος', 'μπαλάσκας', '693'),
('βαγγέλης', 'μπαλάσκας', '697');
από την κονσόλα της MySQL:
για να μάθουμε τι έχει :
SHOW VARIABLES LIKE 'character_set%';
SHOW VARIABLES LIKE 'collation%';
για να θέσουμε :
SET NAMES utf8;
set character_set_client = utf8;
set character_set_connection = utf8;
set character_set_database = utf8;
set character_set_results = utf8;
set collation_connection = utf8_general_ci;
set collation_database = utf8_general_ci;
set collation_server = utf8_general_ci;
Διεργασίες
show full processlist;
top
create database
# mysqladmin CREATE DATABASE -p
# mysql -p
mysql> USE mysql;
mysql> CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';
mysql> GRANT ALL PRIVILEGES ON DATABASE.* TO 'pastebin'@'localhost' WITH GRANT OPTION;
Show Process List
SELECT * FROM information_schema.processlist ;
[link2] http://dev.mysql.com/doc/refman/5.0/en/storage-requirements.html