MySQL
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> use <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
Keyword(s):
References:[SQL] [とらりもんHOME]