1、Market数据库
1、创建数据库Market,以及创建customers表
#创建数据库
create database Market;
#选中此数据库
use Market;
#创建表
create table customers(
c_num int(11) AUTO_INCREMENT,
c_name varchar(50),
c_city varchar(50),
c_birth datetime not null,
Primary key (c_num)
);
#将c_contact字段插入c_birth后
ALTER TABLE customers MODIFY c_contact VARCHAR(50) AFTER c_birth;
#修改c_name的为varchar(70)
alter table customers modify c_name varchar(70);
#将字段c_contact改为c_phone
alter table customers change c_contact c_phone int(11);
#增加c_gander字段
alter table customers add c_gender char(1);
#删除字段c_city
alter table customers drop c_city;
#修改数据表的引擎为myisam
ALTER TABLE customers ENGINE=MyISAM;
2、创建orders表、因为外键的存在两个表的数据引擎要相同
#先修改customers的引擎
create table orders(
o_num int(11) primary key AUTO_INCREMENT,
o_date DATE,
c_id int(11),
CONSTRAINT waijian FOREIGN KEY (c_id) REFERENCES customers (c_num)
);
#删除外键、然后删除表
alter table orders drop foreign key waijian; //删除外键
drop table customers; //删除表
2、数据库Team
数据库,表的创建
create table player(
playid int primary key,
playname varchar(30) not null,
teamnum int not null unique,
info varchar(50)
);
#创建一个新账户为account1 ,密码为oldpwd1,给定权限
create user account1@'%' identified by 'oldpwd1'; //创建
grant select,insert on Team.player to account1@'%'; //给与表权限
grant select (info) on Team.player to account1@'%'; //给与info这列的权限
#更改account1的密码
UPDATE mysql.user SET PASSWORD = PASSWORD("newpwd2") WHERE User = 'account1' and host = 'localhost';
#加载权限表
FLUSH PRIVILEGES;
#查看账户的权限
show grants for account1@'%';
#收回权限
REVOKE SELECT,INSERT,UPDATE(info) ON player FROM 'account1'@'%';
#删除账户account1
drop user 'account1'@'%';