mysql第二次作业

Shanhai15849

于 2023-07-07 21:40:02 发布

阅读量126

点赞数

文章标签： mysql 数据库

本文链接：https://blog.csdn.net/Shanhai15849/article/details/131604662

版权

创建数据库Market，在Market中创建数据表customers，customers 表结构如下图所示，按要求进行操作

操作1

mysql> create database Market; #该行为执行操作
Query OK, 1 row affected (0.00 sec)

mysql> create table customers( c_num int(11)primary key not null UNIQUE AUTO_INCREMENT,c_name varchar(50),c_contact varchar(50),c_birth datetime not null);
Query OK, 0 rows affected (0.01 sec)

创建数据库Market。
在操作1已经完成
创建数据表customers，在c_num字段上添加主键约束和自增约
束，在c_birth字段上添加非空约束。
在操作1已经完成
将c_contact字段插入到c_birth字段后面。

mysql> alter table customers modify c_contact varchar(50) after c_birth;
Query OK, 0 rows affected (0.02 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_birth   | datetime    | NO   |     | NULL    |                |
| c_contact | varchar(50) | YES  |     | NULL    |                |
+-----------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

将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> 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_contact | varchar(50) | YES  |     | NULL    |                |
+-----------+-------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)

将c_contact字段名改为c_phone,数据类型保持不变

mysql> alter table customers CHANGE c_contact c_phone varchar(50);
Query OK, 0 rows affected (0.01 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_birth | datetime    | NO   |     | NULL    |                |
| c_phone | varchar(50) | YES  |     | NULL    |                |
+---------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

增加c_gender字段，数据类型为CHAR(1)。

mysql> alter table customers add c_gender char(1);
Query OK, 0 rows affected (0.02 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_birth  | datetime    | NO   |     | NULL    |                |
| c_phone  | varchar(50) | YES  |     | NULL    |                |
| c_gender | char(1)     | YES  |     | NULL    |                |
+----------+-------------+------+-----+---------+----------------+
5 rows in set (0.01 sec)

将表名修改为customers_info。

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)

创建数据类型为varchar(50)的c_city字段后删除该字段。

mysql> alter table customers_info add c_city varchar(50); #添加字段操作
Query OK, 0 rows affected (0.02 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    |                |
| c_city   | varchar(50) | YES  |     | NULL    |                |
+----------+-------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

mysql> alter table customers_info drop c_city; #删除字段操作
Query OK, 0 rows affected (0.02 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    |                |
+----------+-------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

修改数据表的存储引擎为MyISAM

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

mysql> show create table customers_info\G;# \G为规范化显示
*************************** 1. row ***************************
       Table: customers_info
Create Table: CREATE TABLE `customers_info` (
  `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`),
  UNIQUE KEY `c_num` (`c_num`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 #可以看到engine已经变成了MyISAM，查看数据库支持的所有引擎可通过show engines\G；来查
1 row in set (0.00 sec)

在Market中创建数据表orders，orders表结构如下表所示，按要求进行操作。

⑴创建数据表orders，在c_num字段上添加主键约束和自增约束，在c_id字段上添加外键约束，关联customers表中的主键c_num。

#必须先把表引擎都设为Innodb
mysql> alter table customers_info engine=innodb
    -> ;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table customers_info\G #规范化结束输出显示
*************************** 1. row ***************************
		Table: customers_info
Create Table: CREATE TABLE `customers_info` (
  `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`),
  UNIQUE KEY `c_num` (`c_num`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> create table orders(c_num int(11) primary key not null unique AUTO_INCREMENT,o_date date,c_id int(11),CONSTRAINT xxxxxx FOREIGN KEY(c_id)REFERENCES customers_info(c_num))ENGINE=InnoDB DEFAULT CHARSET=laatin1;
Query OK, 0 rows affected (0.01 sec)

mysql> show create table orders\G
*************************** 1. row ***************************
       Table: orders
Create Table: CREATE TABLE `orders` (
  `c_num` int(11) NOT NULL AUTO_INCREMENT,
  `o_date` date DEFAULT NULL,
  `c_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`c_num`),
  UNIQUE KEY `c_num` (`c_num`),
  KEY `xxxxxx` (`c_id`),
  CONSTRAINT `xxxxxx` FOREIGN KEY (`c_id`) REFERENCES `customers_info` (`c_num`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
# 如果两个表都是MyISAM 引擎的，即使设定外键也不会产生外键)

⑵删除orders表的外键约束，然后删除表customers_info。

mysql> drop table customers_info
    -> ;
ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails
#当有外键关联本(父)表的时候，本(父)表无法被删除

mysql> alter table orders drop foreign key xxxxxx;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> show create table orders\G
*************************** 1. row ***************************
       Table: orders
Create Table: CREATE TABLE `orders` (
  `c_num` int(11) NOT NULL AUTO_INCREMENT,
  `o_date` date DEFAULT NULL,
  `c_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`c_num`),
  UNIQUE KEY `c_num` (`c_num`),
  KEY `xxxxxx` (`c_id`) #可以看到外键约束被删除
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql>  drop table customers_info;
Query OK, 0 rows affected (0.00 sec) #外键约束被删除后即可成功删除父表。

1.创建team库，在库内创建一个表player

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

mysql> use team
Database changed
mysql> create table player(
    -> playid int(11)  primary key not null,
    -> playname varchar(30) not null,
    -> teamnum int(11) not null unique,
    -> info varchar(50));
Query OK, 0 rows affected (0.09 sec)

1、创建一个新账户，用户名为account1，该用户通过本地主机连接数据库，密码为oldpwd1

mysql> CREATE USER 'account1'@'localhost' IDENTIFIED BY 'oldpwd1';
Query OK, 0 rows affected (0.00 sec)

mysql> Ctrl-C -- exit!
Aborted
[root@bogon ~]# mysql -u account1 -poldpwd1
[root@bogon ~]# mysql -u account1 -poldpwd1
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.5.22-log Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

授权该用户对team数据库中player表的select和insert权限，并且授权该用户对player表的info字段的update权限

切换回root用户登录
mysql> grant select,insert,update(info) on team.player to 'account1'@'localhost' identified by 'oldpwd1';
Query OK, 0 rows affected (0.00 sec)

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

mysql> set password for  'account1'@'localhost'=password('newpwd2');
Query OK, 0 rows affected (0.00 sec)

重新加载权限表

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

查看权限给account1用户的权限

mysql> show grants for    'account1'@'localhost'\G
*************************** 1. row ***************************
Grants for account1@localhost: GRANT USAGE ON *.* TO 'account1'@'localhost' IDENTIFIED BY PASSWORD '*6FE6C49810423D44E33DD9EAA61C4D419AF07278'
*************************** 2. row ***************************
Grants for account1@localhost: GRANT SELECT, INSERT, UPDATE (info) ON `team`.`player` TO 'account1'@'localhost'
2 rows in set (0.00 sec)

收回account1 用户的权限

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



mysql> show grants for    'account1'@'localhost'\G
*************************** 1. row ***************************
Grants for account1@localhost: GRANT USAGE ON *.* TO 'account1'@'localhost' IDENTIFIED BY PASSWORD '*6FE6C49810423D44E33DD9EAA61C4D419AF07278'
1 row in set (0.00 sec)

将account1 用户的账户信息从系统中删除

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

Shanhai15849

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql第二次作业

创建数据库Market，在Market中customers，customers 表结构如下图所示，按要求进行操作在操作1已经完成在操作1已经完成将c_name字段数据类型改为VARCHAR(70)将c_contact字段名改为c_phone,数据类型保持不变增加c_gender字段，数据类型为CHAR(1)。将表名修改为customers_info。创建数据类型为varchar(50)的c_city字段后删除该字段。修改数据表的存储引擎为MyISAM。
复制链接

扫一扫