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
■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:/#