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;
