mysql> create database market;
mysql> use market
Database changed
mysql> create table customers(
-> c_num int(11) primary key auto_increment,
-> c_name varchar(50),
-> c_contact varchar(50) ,
-> c_city varchar(50) ,
-> c_birth datetime not null);
mysql> mysql> alter table customers modify c_contact varchar(50) after c_birth;
mysql> desc customers;
+-----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+----------------+
| c_num | int(11) | NO | PRI | NULL | auto_increment |
| c_name | varchar(50) | YES | | NULL | |
| c_city | varchar(50) | YES | | NULL | |
| c_birth | datetime | NO | | NULL | |
| c_contact | varchar(50) | YES | | NULL | |
+-----------+-------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
mysql> alter table customers modify c_name varchar(70);
mysql> desc customers;
+-----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+----------------+
| c_num | int(11) | NO | PRI | NULL | auto_increment |
| c_name | varchar(70) | YES | | NULL | |
| c_city | varchar(50) | YES | | NULL | |
| c_birth | datetime | NO | | NULL | |
| c_contact | varchar(50) | YES | | NULL | |
+-----------+-------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
mysql> alter table customers change c_contact c_phone varchar(50);
mysql> desc customers;
+---------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+----------------+
| c_num | int(11) | NO | PRI | NULL | auto_increment |
| c_name | varchar(70) | YES | | NULL | |
| c_city | varchar(50) | YES | | NULL | |
| c_birth | datetime | NO | | NULL | |
| c_phone | varchar(50) | YES | | NULL | |
+---------+-------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
mysql> alter table customers add c_gender char(1);
mysql> desc customers;
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| c_num | int(11) | NO | PRI | NULL | auto_increment |
| c_name | varchar(70) | YES | | NULL | |
| c_city | varchar(50) | YES | | NULL | |
| c_birth | datetime | NO | | NULL | |
| c_phone | varchar(50) | YES | | NULL | |
| c_gender | char(1) | YES | | NULL | |
+----------+-------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)
mysql> alter table customers drop c_city;
mysql> desc customers;
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| c_num | int(11) | NO | PRI | NULL | auto_increment |
| c_name | varchar(70) | YES | | NULL | |
| c_birth | datetime | NO | | NULL | |
| c_phone | varchar(50) | YES | | NULL | |
| c_gender | char(1) | YES | | NULL | |
+----------+-------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
mysql> alter table customers engine=myisam;
| customers | CREATE TABLE `customers` (
`c_num` int(11) NOT NULL AUTO_INCREMENT,
`c_name` varchar(70) DEFAULT NULL,
`c_birth` datetime NOT NULL,
`c_phone` varchar(50) DEFAULT NULL,
`c_gender` char(1) DEFAULT NULL,
PRIMARY KEY (`c_num`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 |
mysql> alter table customers rename to customers_info;
Query OK, 0 rows affected (0.00 sec)
mysql> desc customers_info ;
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| c_num | int(11) | NO | PRI | NULL | auto_increment |
| c_name | varchar(70) | YES | | NULL | |
| c_birth | datetime | NO | | NULL | |
| c_phone | varchar(50) | YES | | NULL | |
| c_gender | char(1) | YES | | NULL | |
+----------+-------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
2.
mysql> desc orders;
+--------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------+------+-----+---------+----------------+
| o_num | int(1) | NO | PRI | NULL | auto_increment |
| o_date | date | YES | | NULL | |
| c_id | int(11) | YES | | NULL | |
+--------+---------+------+-----+---------+----------------+
3 rows in set (0.01 sec)
mysql> alter table orders add constraint fk_id foreign key (c_id) references customers_info (c_num);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc orders;
+--------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------+------+-----+---------+----------------+
| o_num | int(1) | NO | PRI | NULL | auto_increment |
| o_date | date | YES | | NULL | |
| c_id | int(11) | YES | MUL | NULL | |
+--------+---------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
mysql> alter table orders drop foreign key fk_id;
mysql> drop table customers_info;
Query OK, 0 rows affected (0.00 sec)
3.
mysql> create database team;
mysql> use team;
mysql> create user account1@'localhost' identified by 'oldpwd1';
mysql> grant select ,insert on team.player to account1@'localhost' ;
mysql> grant update(info) on team.player to account1@'localhost';
mysql> set password for account1@'localhost' = password('newpwd2');
mysql> flush privileges;
mysql> show grants for account1@'localhost';
+----------------------------------------------------------------------------------+
| Grants for account1@localhost |
+----------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'account1'@'localhost' |
| GRANT SELECT, INSERT, UPDATE (info) ON `team`.`player` TO 'account1'@'localhost' |
+----------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> revoke all on team.player from account1@'localhost';
mysql> drop user account1@localhost;