Hits : 3871

MySQL References


Contents

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


groupadd mysql
useradd -g mysql mysql
cd /usr/local/mysql
./scripts/mysql_install_db --user=mysql
chown -Rv root  .
chown -Rv mysql data
chgrp -Rv mysql .
./bin/mysqld_safe --user=mysql &
./bin/mysql
 
use mysql
UPDATE  `mysql`.`user` SET `Password` = OLD_PASSWORD( 'your password here' ) WHERE  `user`.`User` =  'root';
FLUSH PRIVILEGES;
exit
 
./bin/mysql -p

top

mysql recover root passwd


/etc/init.d/mysqld stop
/usr/bin/mysqld_safe --skip-grant-tables --skip-networking &
mysql -u root
use mysql;
UPDATE user SET Password=PASSWORD("your password here") WHERE User="root";
exit
# kill all the mysql processes
/etc/init.d/mysqld stop
# Start MySQL again
/etc/init.d/mysqld start

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


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


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 ;