Memo# MySQL Essential
Contents:
--------------------------------------------------------------------
[1] BASIC
[2] CONTROL DB
[3] CREATE TABLE
[4] CREATE DATABASE
[5] MORE USE (CSV)
--------------------------------------------------------------------
[1] BASIC
● Status Check
st@ubuntu:~$ sudo service mysql status
mysql start/running, process 1xxx
● Stop/Start/Restart
st@ubuntu:~$ sudo service mysql stop
mysql stop/waiting
st@ubuntu:~$ sudo service mysql restart
stop: Unknown instance:
mysql start/running, process 2xxx
st@ubuntu:~$ sudo service mysql start
start: Job is already running: mysql
● Login MySQL Server
st@ubuntu:~$ mysql -u student -p
Enter password:
mysql>
● Help/Quit/Use DB
mysql> help or mysql> ?
mysql> quit
● Three Languages
1. DDL (Data Definition Language : データ定義言語)
Create Table, Drop Table, Create View, Drop View
2. DML (Data Manipuration Language : データ操作言語)
Select, Update, Insert, Delete
3. DCL (Data Control Language : データ制御言語)
Grant, Pevoke
●Use DataBase examples
(1) Simple statements samples
mysql> use sampledb
mysql> show tables;
mysql> select name,price from fruits;
mysql> select * from fruits;
mysql> select name as '名前' from fruits;
mysql> select * from fruits order by price;
mysql> select * from fruits order by price desc;
mysql> select * from fruits order by name;
(2) Practical statements samples
Search Simple Conditions：
mysql> select * from fruits where price <300;
mysql> select * from fruits where price <=300;
<>, >=, >, >, <=, <, =
mysql> select * from fruits where name= 'Apple';
mysql> select * from fruits where name like 'M%';
%・・0以上の文字列を表す特殊文字
mysql> select * from fruits where name not like 'M%';
Search Multiple Conditions：
mysql> select * from fruits where price=300 and name='cherry';
mysql> select * from fruits where price=100 or price=150;
mysql> select * from fruits where price in (100,150);
Basic Caliculation:合計・最大値・最小値・合計・平均・カウント
mysql> select sum(price) from fruits;
mysql> select avg(price) from fruits;
mysql> select max(price) from fruits;
mysql> select min(price) from fruits;
mysql> select count(*) from fruits;
mysql> select count(*) from fruits where price <=200;
Practical Caliculation:四則演算・四捨五入・切り捨て・別名
mysql> select * , price*number*1.08 from fruits2;
mysql> select * , round(price*number*1.08) from fruits2;
mysql> select * , truncate(price*number*1.08, 0) from fruits2;
mysql> select * , truncate(price*number*1.08,0) AS zeikomi from fruits2;
●Output Data
Members Table
mysql> select * from members;
mysql> select * from members group by address;
mysql> select address,avg(age) from members group by address;
mysql> select address,min(age) from members group by address;
mysql> select address,max(age) from members group by address;
mysql> select address,sum(age) from members group by address;
mysql> select address,count(name) from members group by address;
Refine results
GROUP BY name HAVING condition
mysql> select address,avg(age) from members group by address having AVG(age)<30;
●Connect tables 結合と紐ずけ
mysql> select class.classname, teachers.name from class, teachers
-> where class.t_id = teachers.id；
mysql> select class.classname, teachers.name from class, teachers
>where class.t_id = teachers.id
>order by classname;
mysql> select class.classname, teachers.name from class
>join teachers on class.t_id = teachers.id order by classname;
mysql> select staff.name, unit.u_name from staff
>join unit on staff.unit_id = unit.id;
内部結合は共通する列を紐づけてテーブルを作成できる。
外部結合は紐づけた列データが片方にない場合は、レコードは出力しない。
外部結合は、左外部結合（最初に指定したテーブルの全表示）と右外部結合（最後に指定したテーブルの全表示）がある。
mysql> select staff.name, unit.u_name from staff left join unit on staff.unit_id = unit.id;
mysql> select staff.name, unit.u_name from staff right join unit on staff.unit_id = unit.id;
●Subquery (副問い合わせ)
mysql> select * from members where age >= (select avg(age) from members);
mysql> select classname from class
>where t_id in
>(select id From teachers where age>=30);
[2] CONTROL DB
INSERT
mysql> insert into fruits value ('Peach',400);
mysql> insert into fruits value ('Grapefruits',200) , ('Plum',300);
mysql> insert into fruits (name) values ('Blueberry');
mysql> select * from fruits where price is null ;
mysql> insert into seiseki values ('Tanaka', 75 , 75 ,80 ,math + english + history) ;
UPDATE
mysql> update seiseki set english=80 where name ='Tanaka';
mysql> update seiseki set total = math + english + history where name = 'Tanaka';
Notice: No Where -> All changes
mysql> update seiseki set english = 100;
Must be check before updete!
mysql> select * from seiseki where name = 'Tanaka';
DELETE
mysql> delete from fruits where name = 'peach';
TRANSACTIOM and INTEGRITY
mysql> ALTER Table fruits engine = InnoDB;
mysql> start transaction;
mysql> insert into fruits value ('Beef',500);
mysql> rollback ;
mysql> insert into fruits value ('Lemon',100);
mysql> commit ;
[3] CREATE TABLE
mysql> DESC menbers;
https://dev.mysql.com/doc/refman/5.6/ja/data-types.html
参照整合性
mysql> select * from staff left join unit on staff.unit_id = unit.id;
NORMALIZATION：冗長性の除去
第１正規化：列に繰り返しがないようにする
第2正規化：主キーが決まれば主キー以外の項目が決まるようにする
第3正規化：主キー以外の列の値は主キーによってのみ確実にきまるようにする
CREAT TABLE
mysql> Create table weather (day DATE PRIMARY KEY, hi_temp INT,low_temp INT, precip INT);
mysql> DESC weather;
mysql> insert into weather values('2015-04-30', 23,15,0);
Query OK, 1 row affected (0.02 sec)
COPY TABLE
1．Structures only
mysql> create table weather_tokyo like weather;
2．Structiures with contents
mysql> create table weather2 select * from weather;
ADD COLUMN
mysql> alter table weather add id TINYINT first;
mysql> select * from weather;
DELETE(DROP) COLUMN
mysql> alter table weather drop id ;
RENAME TABLE
mysql> alter table weather rename tenki ;
mysql> select * from weather;
ERROR 1146 (42S02): Table 'sampledb.weather' doesn't exist
DELETE TABLE
mysql> drop table weather_tokyo;
CREAT VIEW
mysql> create view fruits_view
-> as select id,name,price from fruits2
-> where price >=200;
mysql> select * from fruits_view;
mysql> update fruits2 set price=150 where name='Grapes';
DELETE CREAT VIEW
mysql> Drop view fruits_view;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from fruits_view;
ERROR 1146 (42S02): Table 'sampledb.fruits_view' doesn't exist
[4] CREATE DATABASE
mysql> Create database newdb;
mysql> show databases;
Character Set
mysql> create database exampledb character set utf8;
DELETE DB
mysql> drop database newdb;
USER (CREATE DELETE) ON DB
mysql> quit
Bye
st@ubuntu:~$ mysql -u root -p
Enter password:
mysql> create user newuser@localhost identified by 'dbpass';
mysql> create user testuser@'%' identified by 'dbpass';
DBに対する全権
mysql> grant all privileges on sampledb.* to newuser@localhost;
全DBに対する全権
mysql> grant all privileges on *.* to newuser@localhost;
PASSWORD変更
mysql> set password for student@localhost=password('newpassword');
ユーザー検索
mysql> select User, Host from mysql.user ;
DELETE USER
mysql> Drop user testuser;
[5] MORE USE (CSV)
stt@ubuntu:~$ cat importtest.csv
8 Nakamura Kanagawa 25
st@ubuntu:~$ cp importtest.csv /tmp
st@ubuntu:~$ sudo chown mysql:mysql /tmp/importtest.csv
CSV
mysql> load data infile 'importtest.csv' into table members;
mysql> select * into outfile '/tmp/test.csv' from fruits2;
mysql> select * into outfile '/tmp/test2.csv' fields terminated by ',' from fruits2;
データベースバックアップ
st@ubuntu:/tmp$ mysqldump -u root -p sampledb > sample.txt
Enter password:
st@ubuntu:/tmp$
データベースリストア
mysql> create database newsampledb;
sｔ@ubuntu:~$ mysql -u root -p newsampledb < sampledb.txt;
--end--
参考図書；
中島 能和 (著) たった2日でわかるSQL MySQL対応
朝井 淳 (著)SQLポケットリファレンス (POCKET REFERENCE)