1.
第1题
mysql> create database Market charset 'utf8';
Query OK, 1 row affected (0.01 sec)
第二题
mysql> use Market
Database changed
mysql>
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);
Query OK, 0 rows affected (0.00 sec)
第3题
#ALTER TABLE your_table MODIFY COLUMN column1 datatype AFTER column2;
#ALTER TABLE your_table MODIFY COLUMN column3 datatype FIRST;
mysql> alter table customers modify column c_contact varchar(50) after c_birth ;
Query OK, 0 rows affected (0.08 sec)
Records: 0 Duplicates: 0 Warnings: 0
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)
第4题
mysql> alter table customers modify column c_name varchar(70);
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
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)
第5题
mysql> alter table customers change c_contact c_phone varchar(50);
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
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)
第6题
mysql> alter table customers add c_gender char(1);
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
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)
第7题
mysql> alter table customers rename customers_info;
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
+------------------+
| Tables_in_Market |
+------------------+
| customers_info |
+------------------+
1 row in set (0.00 sec)
第8题
mysql> alter table customers_info drop c_city;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
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 | |
+----------+-------------+------+-----+---------+----------------+
第9题
mysql> alter table customers_info engine=MyISAM;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
2.
ps: 这道题在创建外键的时候由于两个表的数据类型不同使用的引擎也不同,在创建外键时应把这些改一致.
mysql> create table orders( o_num int(11) primary key auto_increment, o_date date, o_id varchar(50) REFERENCES customers_info(c_num));
Query OK, 0 rows affected (0.04 sec)
mysql> desc orders;
+--------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+----------------+
| o_num | int(11) | NO | PRI | NULL | auto_increment |
| o_date | date | YES | | NULL | |
| o_id | varchar(50) | YES | | NULL | |
+--------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
mysql> create table orders( o_num int(11) primary key auto_increment, o_date date, c_id int(11) ,FOREIGN KEY(c_id) REFEREnces customers_info(c_num));
mysql> desc orders;
+--------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------+------+-----+---------+----------------+
| o_num | int(11) | 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> show create table orders;
+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| orders | CREATE TABLE `orders` (
`o_num` int(11) NOT NULL AUTO_INCREMENT,
`o_date` date DEFAULT NULL,
`c_id` int(11) DEFAULT NULL,
PRIMARY KEY (`o_num`),
KEY `c_id` (`c_id`),
CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`c_id`) REFERENCES `customers_info` (`c_num`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> alter table orders drop foreign key `orders_ibfk_1` ;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
3.
Create Database Team;
CREATE TABLE `player` (
`playid` int(11) NOT NULL,
`playname` varchar(30) NOT NULL,
`teamnum` int(11) NOT NULL,
`info` varchar(50) DEFAULT NULL,
PRIMARY KEY (`playid`),
UNIQUE KEY `teamnum` (`teamnum`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
- 创建用户并赋予权限
create user account1@localhost identified by 'oldpwd1'
grant select,insert,update(info) on Team.player to account1@localhost
mysql> select * from player;
+--------+----------+---------+------+
| playid | playname | teamnum | info |
+--------+----------+---------+------+
| 1 | 1 | 2 | 5 |
+--------+----------+---------+------+
1 row in set (0.00 sec)
mysql> update player set playid=5;
ERROR 1143 (42000): UPDATE command denied to user 'account1'@'localhost' for column 'playid' in table 'player'
- 修改密码
mysql> alter user account1@localhost identified by 'newpwd2';
Query OK, 0 rows affected (0.00 sec)
- 刷新 权限
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
-
查看权限
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' | +----------------------------------------------------------------------------------+ -
收回权限
mysql> revoke all on *.* from account1@localhost;
Query OK, 0 rows affected (0.00 sec)
- 删除账号
mysql> drop user account1@localhost;
Query OK, 0 rows affected (0.00 sec)
