とらりもん - MySQL Diff
- Added parts are displayed like this.
- Deleted parts are displayed
like this.
!MySQLのインストール
!!<1> ソースコードより ※おすすめしない。メモ程度。
mysql-5.5.8.tar.gz をダウンロード。
# wget http://ftp.jaist.ac.jp/pub/mysql/Downloads/MySQL-5.5/mysql-5.5.8.tar.gz
# mv mysql-5.5.8.tar.gz /usr/local
# cd /usr/local
# tar zxvf mysql-5.5.8.tar.gz
# cd mysql-5.5.8
# apt-get install g++ cmake
sudo aptitude install paco
MYSQL
Download
http://dev.mysql.com/downloads/
$ tar xvf mysql-5.5.8.tar.gz
$ cd mysql-5.5.8
The mysql ver 5.5.8 doesn't have configure file.
Aternatively that has configure.cmake file.
So you have to install narrow packages.
$ sudo aptitude install cmake libncurses5-dev libncursesw5-dev sysv-rc-conf g++
$ sudo apt-get install bison
$ sudo cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci
$ sudo make
error が出る場合
$ vi config.h.cmake
#cmakedefine SOCKET_SIZE_TYPE @SOCKET_SIZE_TYPE@
を以下に書き換える。
#cmakedefine SOCKET_SIZE_TYPE size_t
もう一度cmakeを行い、makeしてみる。
$ sudo paco -D make install
paco is tool to manage software safly.
$ cd /usr/local/mysql
$ sudo scripts/mysql_install_db --user=mysql --no-defaults
Setting logfile
$ sudo mkdir /var/log/mysql/
$ sudo touch /var/log/mysql/error.log
$ sudo touch /var/log/mysql/query.log
$ sudo touch /var/log/mysql/slow.log
$ sudo chmod 755 /var/log/mysql/
$ sudo chmod 666 /var/log/mysql/*
$ sudo chown -R mysql. /var/log/mysql/
Setting value owner
$ sudo chown -R root:root /usr/local/mysql
$ sudo chown -R mysql:mysql /usr/local/mysql/var
$ vi .bashrc
export PATH=$PATH:/usr/local/mysql/bin
$ source .bashrc
!!<2>パッケージ
*make environment
sudo apt-get install mysql-server
sudo su
mysql -u root -p
enter : passwd
*start
/etc/init.d/mysql start
*stop mysql server
/etc/init.d/mysql stop
*show databases
SHOW DATABASES;
*select databese
mysql> use "NAME of database";
mysql> select database();
*create database
mysql> create database tesdb ;
*delete database
mysql> drop database "NAME of database " ;
*adduser
mysql> create user 'www-data'@'localhost' identified by 'passwd' ;
mysql> www-data, ryuiki
*setting user option
mysql> grant all on testdb.* to 'kentaro'@'localhost' ;
*if you use databases from other networks.
mysql> grant all on testdb.* to 'kentaro'@'%' ;
※In this case , you can not this database from localhost.
*if you want to change passwd.
mysql> set password for kentaro@localhost=password('newpasswd');
*delete user
if your version is 5.0.2 or before
mysql> remove all on testdb.* from 'kentaro'@'localhost'
mysql> drop user kentaro@localhost
* ユーザーのリストを表示
mysql> select Host, User, Password from mysql.user;
!2.MYSQLの使い方
!!基本操作
起動
$ mysql -u username -p
→ パスワードを入れる。
データベースのリストを表示
mysql> show databases;
データベースの選択選択決定
mysql> usedatabase<databasename>;
テーブルのリストを表示
show tables;
ひとつのテーブルのフィールド仕様を表示
show fields from <tablename>;
テーブル内の全データを表示
mysql> select * from <tablename>;
create table
mysql> create table "$NAME" (name1 type, name2 type , name3 type ,,,,);
#delete table
mysql> drop table "$NAME"
setting main key
mysql> create table "$NAME" (name1 type primary key, name2 type , name3 type ,,,,);
show tabel
mysql> desc staff;
setting NOTNULL
mysql> create table "$NAME" (name1 type primary key, name2 type not null, name3 type ,,,,);
add record
mysql> insert into "$NAME" values ('140.01', '36.55','4');
mysql> insert into "$NAME" ( east , landsurface) values ('140.01', '4');
show all record
mysql> select * from "$NAME";
search record
ex1)
mysql> select east,north from '$NAME';
ex2)
mysql> select east as '経度' , north as '緯度' from '$NAME';
ex3)
mysql> select * from '$NAME' where east <= 140;
*=,!=,IN(there are any word)
ex4)
mysql> select * from '$NAME' where landsurface link '1%' ;
*not link
make group
mysql> select * from test by landsurface
*only show one of avarege from landsurface
sort recode
mysql> select ***** order by east
*desc <--- reverce
output textfile
mysql> select * from test into outfile 'test.txt'
*default = /var/lib/mysql/test/
!!シェルスクリプトとMySQL
#!/bin/bash
mysql -u user_name -pxxxxxx << EOF
SQL文;
SQL文;
SQL文;
EOF
$ mysql -u user_name -pxxxxxx -e “"SQL文"
「;」で区切れば複数のSQL文発行も可能。
$ mysql -u user_name -pxxxxxx -e “SQL文;SQL文;SQL文”
$ echo “SQL文;” | mysql -u user_name -pxxxxxx
$ mysql -u user_name -pxxxxxx < file_name
$ mysql -u user_name -pxxxxxx db_name < file_name
$ mysql < file_name | less
$ mysql -u user_name -pxxxxxx -e “SQL文;” > outfile
!!<1> ソースコードより ※おすすめしない。メモ程度。
mysql-5.5.8.tar.gz をダウンロード。
# wget http://ftp.jaist.ac.jp/pub/mysql/Downloads/MySQL-5.5/mysql-5.5.8.tar.gz
# mv mysql-5.5.8.tar.gz /usr/local
# cd /usr/local
# tar zxvf mysql-5.5.8.tar.gz
# cd mysql-5.5.8
# apt-get install g++ cmake
sudo aptitude install paco
MYSQL
Download
http://dev.mysql.com/downloads/
$ tar xvf mysql-5.5.8.tar.gz
$ cd mysql-5.5.8
The mysql ver 5.5.8 doesn't have configure file.
Aternatively that has configure.cmake file.
So you have to install narrow packages.
$ sudo aptitude install cmake libncurses5-dev libncursesw5-dev sysv-rc-conf g++
$ sudo apt-get install bison
$ sudo cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci
$ sudo make
error が出る場合
$ vi config.h.cmake
#cmakedefine SOCKET_SIZE_TYPE @SOCKET_SIZE_TYPE@
を以下に書き換える。
#cmakedefine SOCKET_SIZE_TYPE size_t
もう一度cmakeを行い、makeしてみる。
$ sudo paco -D make install
paco is tool to manage software safly.
$ cd /usr/local/mysql
$ sudo scripts/mysql_install_db --user=mysql --no-defaults
Setting logfile
$ sudo mkdir /var/log/mysql/
$ sudo touch /var/log/mysql/error.log
$ sudo touch /var/log/mysql/query.log
$ sudo touch /var/log/mysql/slow.log
$ sudo chmod 755 /var/log/mysql/
$ sudo chmod 666 /var/log/mysql/*
$ sudo chown -R mysql. /var/log/mysql/
Setting value owner
$ sudo chown -R root:root /usr/local/mysql
$ sudo chown -R mysql:mysql /usr/local/mysql/var
$ vi .bashrc
export PATH=$PATH:/usr/local/mysql/bin
$ source .bashrc
!!<2>パッケージ
*make environment
sudo apt-get install mysql-server
sudo su
mysql -u root -p
enter : passwd
*start
/etc/init.d/mysql start
*stop mysql server
/etc/init.d/mysql stop
*show databases
SHOW DATABASES;
*select databese
mysql> use "NAME of database";
mysql> select database();
*create database
mysql> create database tesdb ;
*delete database
mysql> drop database "NAME of database " ;
*adduser
mysql> create user 'www-data'@'localhost' identified by 'passwd' ;
mysql> www-data, ryuiki
*setting user option
mysql> grant all on testdb.* to 'kentaro'@'localhost' ;
*if you use databases from other networks.
mysql> grant all on testdb.* to 'kentaro'@'%' ;
※In this case , you can not this database from localhost.
*if you want to change passwd.
mysql> set password for kentaro@localhost=password('newpasswd');
*delete user
if your version is 5.0.2 or before
mysql> remove all on testdb.* from 'kentaro'@'localhost'
mysql> drop user kentaro@localhost
* ユーザーのリストを表示
mysql> select Host, User, Password from mysql.user;
!2.MYSQLの使い方
!!基本操作
起動
$ mysql -u username -p
→ パスワードを入れる。
データベースのリストを表示
mysql> show databases;
データベースの
mysql> use
テーブルのリストを表示
show tables;
ひとつのテーブルのフィールド仕様を表示
show fields from <tablename>;
テーブル内の全データを表示
mysql> select * from <tablename>;
create table
mysql> create table "$NAME" (name1 type, name2 type , name3 type ,,,,);
#delete table
mysql> drop table "$NAME"
setting main key
mysql> create table "$NAME" (name1 type primary key, name2 type , name3 type ,,,,);
show tabel
mysql> desc staff;
setting NOTNULL
mysql> create table "$NAME" (name1 type primary key, name2 type not null, name3 type ,,,,);
add record
mysql> insert into "$NAME" values ('140.01', '36.55','4');
mysql> insert into "$NAME" ( east , landsurface) values ('140.01', '4');
show all record
mysql> select * from "$NAME";
search record
ex1)
mysql> select east,north from '$NAME';
ex2)
mysql> select east as '経度' , north as '緯度' from '$NAME';
ex3)
mysql> select * from '$NAME' where east <= 140;
*=,!=,IN(there are any word)
ex4)
mysql> select * from '$NAME' where landsurface link '1%' ;
*not link
make group
mysql> select * from test by landsurface
*only show one of avarege from landsurface
sort recode
mysql> select ***** order by east
*desc <--- reverce
output textfile
mysql> select * from test into outfile 'test.txt'
*default = /var/lib/mysql/test/
!!シェルスクリプトとMySQL
#!/bin/bash
mysql -u user_name -pxxxxxx << EOF
SQL文;
SQL文;
SQL文;
EOF
$ mysql -u user_name -pxxxxxx -e “"SQL文"
「;」で区切れば複数のSQL文発行も可能。
$ mysql -u user_name -pxxxxxx -e “SQL文;SQL文;SQL文”
$ echo “SQL文;” | mysql -u user_name -pxxxxxx
$ mysql -u user_name -pxxxxxx < file_name
$ mysql -u user_name -pxxxxxx db_name < file_name
$ mysql < file_name | less
$ mysql -u user_name -pxxxxxx -e “SQL文;” > outfile