create database tables

Creating database and assigning username/password, for handy


mysql>  create database abc;
mysql> use abc;
mysql> create table tab1 (i int, c char(3));
mysql> insert into tab1 values (1, 'A');
mysql> insert into tab1 values (2, 'B'), (3, 'C'), (4, 'D');


mysql> create user 'def'@'localhost' identified by 'def';
mysql> grant all on abc.* to 'def'@'localhost' with grant option;
mysql> flush_privileges;
mysql> flush privileges;

mysql> show grants for  'def'@'localhost';

+------------------------------------------------------------------------------------------------------------+
| Grants for def@localhost                                                                                   |
+------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'def'@'localhost' IDENTIFIED BY PASSWORD '*EFA28F5E94885AA962E1AFC1912CE376EE374F65' |
| GRANT ALL PRIVILEGES ON `abc`.* TO 'def'@'localhost' WITH GRANT OPTION                                     |
+------------------------------------------------------------------------------------------------------------+


$ mysql -u def -p  abc
Enter password:


mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| abc                |
+--------------------+

mysql> use abc;
Database changed
mysql> show tables;
+---------------+
| Tables_in_abc |
+---------------+
| tab1          |
+---------------+


mysql> create index i_idx on tab1 (i);

mysql> show indexes from tab1;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| tab1  |          1 | i_idx    |            1 | i           | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

mysql> select * from tab1;

+------+------+

| i    | c    |

+------+------+

|    1 | A    |

|    2 | B    |

|    3 | C    |

|    4 | D    |

+------+------+

mysql> create table tab2 like tab1;

mysql> desc tab2;

+-------+---------+------+-----+---------+-------+

| Field | Type    | Null | Key | Default | Extra |

+-------+---------+------+-----+---------+-------+

| i     | int(11) | YES  | MUL | NULL    |       |

| c     | char(3) | YES  |     | NULL    |       |

+-------+---------+------+-----+---------+-------+

mysql> show indexes from tab2;

+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |

+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

| tab2  |          1 | i_idx    |            1 | i           | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         |

+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

mysql> select * from tab2;

Empty set (0.00 sec)

mysql> create table tab3 as select * from tab1;

mysql> desc tab3;

+-------+---------+------+-----+---------+-------+

| Field | Type    | Null | Key | Default | Extra |

+-------+---------+------+-----+---------+-------+

| i     | int(11) | YES  |     | NULL    |       |

| c     | char(3) | YES  |     | NULL    |       |

+-------+---------+------+-----+---------+-------+

mysql> show indexes from tab3;

mysql> select * from tab3;

+------+------+

| i    | c    |

+------+------+

|    1 | A    |

|    2 | B    |

|    3 | C    |

|    4 | D    |

+------+------+

mysql> insert into tab2 select * from tab1 where mod(i,2)=0;

Query OK, 2 rows affected (0.00 sec)

mysql> select * from tab2;

+------+------+

| i    | c    |

+------+------+

|    2 | B    |

|    4 | D    |

+------+------+

 drop user 'def'@'localhost' ;

 drop table tab1;

 drop database abc;