Hits : 4325

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[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 ;


Links
[link1] http://en.wikipedia.org/wiki/Signed_number_representations

[link2] http://dev.mysql.com/doc/refman/5.0/en/storage-requirements.html