一、数据库Market中创建表customers
1、创建数据库
mysql> create database Market;
mysql> use Market;
2、创建数据表
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
-> );
3、将c_contact字段插入到c_birth字段后面
mysql> alter table customers modify column c_contact varchar(50) after c_birth;
4、 将c_name字段数据类型改为varchar(70)
mysql> alter table customers modify c_name varchar(70);
5 、将c_contact字段改为c_phone
mysql> alter table customers change c_contact c_phone varchar(50);
6、增加c_gender字段,数据类型为char(1)
mysql> alter table customers add c_gender char(1);
7、将表名修改为customers_info
mysql> rename table customers to customers_info;
8、删除字段c_city
mysql> alter table customers_info drop c_city;
9、修改数据表的存储引擎为MyISAM。
mysql> alter table customers_info engine=MyISAM;
mysql> SHOW TABLE STATUS LIKE 'customers_info';
二、Market中创建表orders
1、创建数据表
mysql> create table orders(
-> o_name int(11) primary key auto_increment,
-> o_date date,
-> c_id int(50)
-> );
mysql> alter table customers_info engine=InnoDB;
mysql> alter table orders add foreign key (c_id) references customers_info(c_num);
2、删除orders表的外键约束,然后删除表customers_info。
mysql> show create table orders;
mysql> alter table orders drop foreign key orders_ibfk_1;
mysql> drop table customers_info;
3、数据库Team
1、创建一个新账户,用户名为account1,该用户通过本地主机连接数据库,密码为oldpwd1。授权该用户对Team 数据库中 player表的SELECT和 INSERT权限,并且授权该用户对player表的info字段的UPDATE权限。
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> FLUSH PRIVILEGES;
2、创建SQL 语句,更改account1用户的密码为newpwd2。
mysql> alter user account1@localhost identified by 'Newpwd2.';
3、创建SQL语句,使用FLUSH PRIVILEGES重新加载权限表。
mysql> FLUSH PRIVILEGES;
4、创建SQL语句,查看授权给account1用户的权限。
mysql> show grants for account1@localhost;
5、创建SQL语句,收回account1用户的权限。
mysql> revoke all privileges on *.* from account1@localhost;
6、创建SQL语句,将account1用户的账号信息从系统中删除。
mysql> drop user account1@localhost;