sqlコマンドの基礎

## Basic command
### login 

``` 
■How to
mysql -u "user_name" -p "password"
```
``` 
mysql -u root       #no password
mysql -u root -p    #wtih password
```
### set password (for root)

``` 
■How to
set password for "user_name"@"host"=password("password")
``` 

``` 
mysql> set password for root@localhost=password("passwd");
Query OK, 0 rows affected, 2 warnings (0.06 sec)
```

## user
### create user

``` 
■How to
create user 'user_name'@'host' identified by 'password'
``` 
``` 
mysql> create user 'ogiman'@'localhost' identified by 'ogiman' ;
Query OK, 0 rows affected (0.12 sec)

mysql> select user,host from mysql.user ;
+---------------+-----------+
| user          | host      |
+---------------+-----------+
| ogino         | %         |
| mysql.session | localhost |
| mysql.sys     | localhost |
| ogiman        | localhost |
| root          | localhost |
+---------------+-----------+
5 rows in set (0.00 sec)

``` 

### delete user

``` 
■How to
drop user 'user_name'@'host'
``` 
``` 
mysql> select user,host from mysql.user ;
+---------------+-----------+
| user          | host      |
+---------------+-----------+
| ogino         | %         |
| mysql.session | localhost |
| mysql.sys     | localhost |
| ogiman        | localhost |
| root          | localhost |
+---------------+-----------+
5 rows in set (0.00 sec)

mysql> drop user 'ogiman'@'localhost' ;
Query OK, 0 rows affected (0.00 sec)

mysql> select user,host from mysql.user ;
+---------------+-----------+
| user          | host      |
+---------------+-----------+
| ogino         | %         |
| mysql.session | localhost |
| mysql.sys     | localhost |
| root          | localhost |
+---------------+-----------+

``` 

## database
### create database

``` 
■How to
create "database name"
``` 
``` 
mysql> create database ogiman;
Query OK, 1 row affected (0.03 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| ogiman             |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.03 sec)

``` 

### delete database

``` 
■How to
drop "database name"
``` 
``` 
mysql> drop database ogiman;
Query OK, 0 rows affected (0.04 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

``` 





### connect database

``` 
■How to
user "database_name"
``` 
``` 
mysql> use ogiman;
Database changed

``` 

## tables 
### show tables 

``` 
■How to
show tables from "database_name"
``` 
``` 
mysql> SHOW TABLES FROM ogiman;
+------------------+
| Tables_in_ogiman |
+------------------+
| addressbook      |
+------------------+
1 row in set (0.00 sec)

``` 

### show column details

``` 
■How to
show columns from "table_name";
``` 
``` 
mysql> show columns from addressbook;
+-----------+-------------+------+-----+---------+----------------+
| Field     | Type        | Null | Key | Default | Extra          |
+-----------+-------------+------+-----+---------+----------------+
| id        | int(3)      | NO   | PRI | NULL    | auto_increment |
| name      | varchar(20) | YES  |     | NULL    |                |
| address   | varchar(20) | YES  |     | NULL    |                |
| telephone | varchar(13) | YES  |     | NULL    |                |
+-----------+-------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)

``` 

### create tables

- [data format list](http://mysql.akarukutanoshiku.com/category5/entry21.html)

- [how to use option](http://uxmilk.jp/12725)

``` 
■How to
CREATE TABLE [table_name] (
    column1 ["data format"] ["option"],
    column2 ["data format"] ["option"],
    column3 ["data format"] ["option"],
    );
```
``` 
mysql> create table addressbook (
    -> id int(3),
    -> name varchar(20),
    -> address varchar(20),
    -> telephone varchar(13),
    -> primary key (id)
    -> );
Query OK, 0 rows affected (0.02 sec)

``` 

### delete table

``` 
■How to
drop tables "table_name" 
``` 
``` 
mysql> drop tables addressbook;
Query OK, 0 rows affected (0.02 sec)
```
### change column type of tables

``` 
■How to
ALTER TABLE "table_name" modify "column" "type"
``` 
``` 
mysql> show columns from test_DB;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(3)      | YES  |     | NULL    |       |
| test1 | varchar(20) | YES  |     | NULL    |       |
| test2 | varchar(20) | YES  |     | NULL    |       |
| test3 | varchar(20) | YES  |     | NULL    |       |
| test4 | varchar(20) | YES  |     | NULL    |       |
| test5 | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
mysql> ALTER TABLE test_DB modify test1 int(255);
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE test_DB modify test2 int(255);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE test_DB modify test3 int(255);
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE test_DB modify test4 int(255);
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE test_DB modify test5 int(255);
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show columns from test_DB;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int(3)   | YES  |     | NULL    |       |
| test1 | int(255) | YES  |     | NULL    |       |
| test2 | int(255) | YES  |     | NULL    |       |
| test3 | int(255) | YES  |     | NULL    |       |
| test4 | int(255) | YES  |     | NULL    |       |
| test5 | int(255) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
6 rows in set (0.00 sec)


```

### modify the name of table

``` 
■How to
ALTER TABLE "table_name" RENAME TO "new name"
``` 

```
ALTER TABLE tasttable RENAME TO testtable;
```

### insert data into table 

``` 
■How to
insert `table_name`(`column1`, `column2`, `column3`, `column3`) VALUES(input_data1, 'input_data2', 'input_data3', 'input_data4');
```

``` 
mysql> INSERT INTO `addressbook`(`id`, `name`, `address`, `telephone`) VALUES(1, 'nishide', 'Cebu', '080-xxxx-oooo');
Query OK, 1 row affected (0.00 sec)

mysql> select * from addressbook;
+----+---------+---------+---------------+
| id | name    | address | telephone     |
+----+---------+---------+---------------+
|  1 | nishide | Cebu    | 080-xxxx-oooo |
+----+---------+---------+---------------+
1 row in set (0.00 sec)
``` 

### delete data from table 

``` 
■How to
1 DELETE FROM "database_name" WHERE "column" = "value";
  =>if the column eqaul "value" , delete the lines

2 DELETE FROM "database_name" WHERE "column" < "value";
  =>if the column less than "value" , delete the lines

3 DELETE FROM "database_name";
  =>delete all data
```

```
mysql> select * from addressbook;
+----+---------+---------+---------------+
| id | name    | address | telephone     |
+----+---------+---------+---------------+
|  1 | nishide | Cebu    | 080-xxxx-oooo |
|  2 | ogino   | mituru  | 080-xxxx-oooo |
+----+---------+---------+---------------+
2 rows in set (0.00 sec)

mysql> DELETE FROM addressbook WHERE id = 1;
Query OK, 1 row affected (0.08 sec)

mysql> select * from addressbook;
+----+-------+---------+---------------+
| id | name  | address | telephone     |
+----+-------+---------+---------------+
|  2 | ogino | mituru  | 080-xxxx-oooo |
+----+-------+---------+---------------+
1 row in set (0.00 sec)
mysql> select * from addressbook;
+----+-------+---------+---------------+
| id | name  | address | telephone     |
+----+-------+---------+---------------+
|  2 | ogino | mituru  | 080-xxxx-oooo |
|  3 | gino  | mituru  | 080-xxxx-oo   |
+----+-------+---------+---------------+
2 rows in set (0.00 sec)

mysql> DELETE FROM addressbook WHERE id < 3;
Query OK, 1 row affected (0.03 sec)

mysql> select * from addressbook;
+----+------+---------+-------------+
| id | name | address | telephone   |
+----+------+---------+-------------+
|  3 | gino | mituru  | 080-xxxx-oo |
+----+------+---------+-------------+
1 row in set (0.00 sec)

mysql> DELETE FROM addressbook;
Query OK, 1 row affected (0.00 sec)

mysql> select * from addressbook;
Empty set (0.00 sec)

```
### any type of select(where)
####No.1

``` 
■How to
select "column1"[column2...] from "table_name" where "column_name" =(*) "specified_value"
*put operator(= != < > >= <=)
```

```
mysql> select name,address from addressbook where address = 'man' ;
+------+---------+
| name | address |
+------+---------+
| ogi  | man     |
| mitu | man     |
+------+---------+
2 rows in set (0.00 sec)

mysql> select name,telephone from addressbook where address = 'man' ;
+------+---------------+
| name | telephone     |
+------+---------------+
| ogi  | 080-xxxx-oooo |
| mitu | 080-xxxx-oooo |
+------+---------------+
2 rows in set (0.01 sec)

mysql> select name,telephone from addressbook where name != 'ogi' ;
+------+---------------+
| name | telephone     |
+------+---------------+
| mitu | 080-xxxx-oooo |
+------+---------------+
1 row in set (0.00 sec)
```

####No.2 conditions more than 2ways with "and" 

``` 
■How to
select "culumn1,culumn2[culumn3...]" from "tables_name"
where
"culumn1" = "value"
and
"culumn2" = "value"
;
```

``` 
mysql> select name,address from addressbook
    -> where
    -> address="man"
    -> and
    -> name="ogi"
    -> ;
+------+---------+
| name | address |
+------+---------+
| ogi  | man     |
+------+---------+
1 row in set (0.00 sec)
``` 
####No.3 conditions with "between" 

``` 
■How to
select "culumn1,culumn2[culumn3...]" from "tables_name"
where
"column" between "min_value" and "max_value";
;
```

``` 
mysql> select * from test_DB;
+------+-------+-------+-------+-------+-------+
| id   | test1 | test2 | test3 | test4 | test5 |
+------+-------+-------+-------+-------+-------+
|    1 |     1 |  NULL |  NULL |  NULL |  NULL |
|    2 |   100 |    20 |    23 |  NULL |  NULL |
|    3 |   100 |    20 |    23 |   255 |   135 |
|    4 |  1001 |     2 |   223 |    25 |     1 |
|    5 |   102 | 21111 |     2 |   225 |  1111 |
+------+-------+-------+-------+-------+-------+
5 rows in set (0.00 sec)

mysql> select * from test_DB
    -> where
    -> test2 between 2 and 30000;
+------+-------+-------+-------+-------+-------+
| id   | test1 | test2 | test3 | test4 | test5 |
+------+-------+-------+-------+-------+-------+
|    2 |   100 |    20 |    23 |  NULL |  NULL |
|    3 |   100 |    20 |    23 |   255 |   135 |
|    4 |  1001 |     2 |   223 |    25 |     1 |
|    5 |   102 | 21111 |     2 |   225 |  1111 |
+------+-------+-------+-------+-------+-------+
4 rows in set (0.00 sec)

``` 

####No.4 conditions with "in" 

``` 
■How to
select "culumn1,culumn2[culumn3...]" from "tables_name"
where
"column" in (value1,value2);
;
```

``` 
mysql> select * from test_DB;
+------+-------+-------+-------+-------+-------+
| id   | test1 | test2 | test3 | test4 | test5 |
+------+-------+-------+-------+-------+-------+
|    1 |     1 |  NULL |  NULL |  NULL |  NULL |
|    2 |   100 |    20 |    23 |  NULL |  NULL |
|    3 |   100 |    20 |    23 |   255 |   135 |
|    4 |  1001 |     2 |   223 |    25 |     1 |
|    5 |   102 | 21111 |     2 |   225 |  1111 |
+------+-------+-------+-------+-------+-------+
5 rows in set (0.00 sec)

mysql> select * from test_DB
    -> where
    -> test1 in (100,102)
    -> ;
+------+-------+-------+-------+-------+-------+
| id   | test1 | test2 | test3 | test4 | test5 |
+------+-------+-------+-------+-------+-------+
|    2 |   100 |    20 |    23 |  NULL |  NULL |
|    3 |   100 |    20 |    23 |   255 |   135 |
|    5 |   102 | 21111 |     2 |   225 |  1111 |
+------+-------+-------+-------+-------+-------+
3 rows in set (0.00 sec)

``` 
####No.5 conditions with "not in" 

``` 
■How to
select "culumn1,culumn2[culumn3...]" from "tables_name"
where
not "column" in (value1,value2);
;
```

``` 
mysql> select * from test_DB;
+------+-------+-------+-------+-------+-------+
| id   | test1 | test2 | test3 | test4 | test5 |
+------+-------+-------+-------+-------+-------+
|    1 |     1 |  NULL |  NULL |  NULL |  NULL |
|    2 |   100 |    20 |    23 |  NULL |  NULL |
|    3 |   100 |    20 |    23 |   255 |   135 |
|    4 |  1001 |     2 |   223 |    25 |     1 |
|    5 |   102 | 21111 |     2 |   225 |  1111 |
+------+-------+-------+-------+-------+-------+
5 rows in set (0.00 sec)

mysql> select * from test_DB
    -> where
    -> not test1 in (100)
    -> ;
+------+-------+-------+-------+-------+-------+
| id   | test1 | test2 | test3 | test4 | test5 |
+------+-------+-------+-------+-------+-------+
|    1 |     1 |  NULL |  NULL |  NULL |  NULL |
|    4 |  1001 |     2 |   223 |    25 |     1 |
|    5 |   102 | 21111 |     2 |   225 |  1111 |
+------+-------+-------+-------+-------+-------+
3 rows in set (0.00 sec)
``` 
####No.6 conditions with "search null" 

``` 
■How to
select "culumn1,culumn2[culumn3...]" from "tables_name"
where
WHERE "column" IS NULL;
;
```

``` 
mysql> select * from test_DB;
+------+-------+-------+-------+-------+-------+
| id   | test1 | test2 | test3 | test4 | test5 |
+------+-------+-------+-------+-------+-------+
|    1 |     1 |  NULL |  NULL |  NULL |  NULL |
|    2 |   100 |    20 |    23 |  NULL |  NULL |
|    3 |   100 |    20 |    23 |   255 |   135 |
|    4 |  1001 |     2 |   223 |    25 |     1 |
|    5 |   102 | 21111 |     2 |   225 |  1111 |
+------+-------+-------+-------+-------+-------+
5 rows in set (0.00 sec)

mysql> select * from test_DB
    -> WHERE test5 IS NULL;
+------+-------+-------+-------+-------+-------+
| id   | test1 | test2 | test3 | test4 | test5 |
+------+-------+-------+-------+-------+-------+
|    1 |     1 |  NULL |  NULL |  NULL |  NULL |
|    2 |   100 |    20 |    23 |  NULL |  NULL |
+------+-------+-------+-------+-------+-------+
2 rows in set (0.00 sec)
``` 

### any type of update

``` 
■How to
update "table_name" set "column" = "value" where "match_column = "match_value" ;
```

``` 
mysql> update test_DB set id = 2 where test4 = 255 ;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

mysql> select * from test_DB;
+------+-------+-------+-------+-------+-------+
| id   | test1 | test2 | test3 | test4 | test5 |
+------+-------+-------+-------+-------+-------+
|    1 |     1 |  NULL |  NULL |  NULL |  NULL |
|    2 |   100 |    20 |    23 |  NULL |  NULL |
|    2 |   100 |    20 |    23 |   255 |   135 |
+------+-------+-------+-------+-------+-------+
3 rows in set (0.00 sec)

mysql> update test_DB set id = 3 where test4 = 255 ;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from test_DB;
+------+-------+-------+-------+-------+-------+
| id   | test1 | test2 | test3 | test4 | test5 |
+------+-------+-------+-------+-------+-------+
|    1 |     1 |  NULL |  NULL |  NULL |  NULL |
|    2 |   100 |    20 |    23 |  NULL |  NULL |
|    3 |   100 |    20 |    23 |   255 |   135 |
+------+-------+-------+-------+-------+-------+
3 rows in set (0.00 sec)
``` 

## Remote control

``` 
■How to
mysql -u"user_name" -p"password" -D"DB_name" -e'sql_command;'

``` 
- Options 

| option   | explain | 
|:---------|:------------------|
| -u       | select username  |
| -p       | put password of username |
| -D       | select DBname |
| -e       | put sql command line|
| -h       | select hostname which you connect |
| -P       | select port number if the port number is not default|
| -S       | select socket which you connect |

```
root@4d4f3e43e5e3:/# mysql -u'root' -p'xxxxxx' 'ogiman' -e'select * from addressb
ook;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+------+---------+---------------+
| id | name | address | telephone     |
+----+------+---------+---------------+
|  1 | ogi  | man     | 080-xxxx-oooo |
|  2 | mitu | man     | 080-xxxx-oooo |
+----+------+---------+---------------+

root@4d4f3e43e5e3:/# mysql -u'root' -p'xxxxx' -e'show databases;' -S'/var/run/my
sqld/mysqld.sock'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| ogiman             |
| performance_schema |
| sys                |
+--------------------+
root@4d4f3e43e5e3:/#
```