MySQL对库对表操作

1.创建数据库Market，在Market中创建数据表customes，customes表结构如表4.6所示，按要求进行操作。

（1）.创建数据库Market。

mysql> create database Market;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| Market             |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

（2）.创建数据表customes，在c_num字段上添加主键约束和自增约束，在c_birth字段上添加

mysql> use Market
Database changed
mysql> create table customers(
    -> c_num INT(11) not null unique primary key auto_increment,
    -> c_name varchar(50),
    -> c_contact varchar(50),
    -> c_city varchar(50),
    -> c_birth datetime not null
    -> );

mysql> show tables
    -> ;
+------------------+
| Tables_in_Market |
+------------------+
| customers        |
+------------------+
1 row in set (0.00 sec)

mysql> show columns from customers;
+-----------+-------------+------+-----+---------+----------------+
| Field     | Type        | Null | Key | Default | Extra          |
+-----------+-------------+------+-----+---------+----------------+
| c_num     | int(11)     | NO   | PRI | NULL    | auto_increment |
| c_name    | varchar(50) | YES  |     | NULL    |                |
| c_contact | varchar(50) | YES  |     | NULL    |                |
| c_city    | varchar(50) | YES  |     | NULL    |                |
| c_birth   | datetime    | NO   |     | NULL    |                |
+-----------+-------------+------+-----+---------+----------------+
5 rows in set (0.01 sec)

（3）.将c_contact插入到c_birth后面

mysql> alter table customers modify c_contact varchar(50) after c_birth;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show columns from 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）.将c_name数据类型改为varchar（70）

mysql> alter table customers modify c_name varchar(70);
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show columns from 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）.将c_contact字段改名为c_phone

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> show columns from 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）.增加c_gender字段，数据类型为char（1）

mysql> alter table customers add c_gender char(1);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show columns from 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）.将表名修改为customers_info

mysql> rename table customers to 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)

mysql> show columns from customers_info;
+----------+-------------+------+-----+---------+----------------+
| 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)

（8）.删除字段c_city。

mysql> alter table customers_info drop c_city;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show columns from 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)

（9）.修改数据表的存储引擎为MyISAM

mysql> alter table customers_info 
    -> engine=myisam;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

2.

mysql> create table orders( 
    -> o_num int(11) primary key not null unique auto_increment,
    -> o_date date,
    -> c_id varchar(50)
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> show tables;
+------------------+
| Tables_in_Market |
+------------------+
| customers_info   |
| orders           |
+------------------+
2 rows in set (0.00 sec)

mysql> show columns from orders;
+--------+-------------+------+-----+---------+----------------+
| Field  | Type        | Null | Key | Default | Extra          |
+--------+-------------+------+-----+---------+----------------+
| o_num  | int(11)     | NO   | PRI | NULL    | auto_increment |
| o_date | date        | YES  |     | NULL    |                |
| c_id   | varchar(50) | YES  |     | NULL    |                |
+--------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> 
mysql> alter table orders drop Foreign key fk_order_custom;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> 
mysql>  drop table customers_info;
Query OK, 0 rows affected (0.00 sec)

3.创建数据库Team，定义数据表player并执行操作

（1）创建一个新用户，用户名为account1，该用户通过本地主机连接数据库，密码为oldowd1。

mysql> create user account1@localhost identified by 'oldpwd1';
Query OK, 0 rows affected (0.00 sec)

授权用户对Team数据库中player表的select和insert权限，并且授权对player表的info字段的UPDATE权限。

mysql> grant select,insert,update(info) on Team.player TO 'account1'@localhost identified by'oldpwd1';
Query OK, 0 rows affected, 1 warning (0.00 sec)

（2）创建SQL语句，更改account1用户的密码为newpwd2。

mysql> alter user account1@localhost identified by 'newpwd2';
Query OK, 0 rows affected (0.00 sec)

（3）创建SQL语句，使用FLUSH PRIVILEGES 重新加载权限表。

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

（4）创建SQL语句，查看授权给account1用户的权限。

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)

（5）创建SQL语句，收回account1用户的权限。

mysql> revoke all privileges,grant option from'account1'@localhost;
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for 'account1'@localhost;
+----------------------------------------------+
| Grants for account1@localhost                |
+----------------------------------------------+
| GRANT USAGE ON *.* TO 'account1'@'localhost' |
+----------------------------------------------+
1 row in set (0.00 sec)

（6）创建SQL语句，将account1用户的账号信息从系统中删除。

mysql> drop user 'account1'@localhost;
Query OK, 0 rows affected (0.00 sec)