创建数据库Market、Team,按要求完成指定操作

这篇具有很好参考价值的文章主要介绍了创建数据库Market、Team,按要求完成指定操作。希望对大家有所帮助。如果存在错误或未考虑完全的地方,请大家不吝赐教,您也可以点击"举报违法"按钮提交疑问。

  • 创建数据库Market,在Market中创建数据表customers,customers表结构如表4.6所示,按要求进行操作。

创建数据库Market、Team,按要求完成指定操作,MySQL,数据库,mysql

 代码如下:

#(1)创建数据库Market
mysql> create database Market;
Query OK, 1 row affected (0.00 sec)

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

mysql> use Market
Database changed
mysql> show tables;
Empty set (0.00 sec)

#(2)创建数据表customers,在c_num字段上添加主键约束和自增约束,在c_birth字段上添加非空约束
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.04 sec)

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_contact | varchar(50) | YES  |     | NULL    |                |
| c_city    | varchar(50) | YES  |     | NULL    |                |
| c_birth   | datetime    | NO   |     | NULL    |                |
+-----------+-------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

#(3)将c_contack字段插入到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> 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.01 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> 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)将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> 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)增加字段c_gender,数据类型为CHAR(1)
mysql> alter table customers add c_gender char(1);
Query OK, 0 rows affected (0.03 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)将表名修改为customers_info
mysql> alter table customers rename customers_info;
Query OK, 0 rows affected (0.02 sec)

mysql> desc customers;
ERROR 1146 (42S02): Table 'Market.customers' doesn't exist
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_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.03 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)


#(9)修改数据表的存储引擎为MyISAM
mysql> alter table customers_info engine=MyISAM;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

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

创建数据库Market、Team,按要求完成指定操作,MySQL,数据库,mysql

代码如下:

在关联customers_info 表中的主键c_num时,orders表中的c_id和customers_info表中的c_num

的类型必须相同才能关联,并且customers_info表的存储引擎需要改回InnoDB才可以。

#修改customers_info表中的存储引擎为InnoDB
mysql> alter table customers_info engine=InnoDB;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

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) #添加外键约束
    -> );
Query OK, 0 rows affected (0.02 sec)

#创建orders表成功
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=latin1 |
+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

#删除外键约束
mysql> alter table orders drop foreign key orders_ibfk_1;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

#删除表custormers_info
mysql> drop table if exists customers_info;
Query OK, 0 rows affected (0.01 sec)

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

#删除表customers_info
  • 创建数据库Team,定义数据player,语句如下

创建数据库Market、Team,按要求完成指定操作,MySQL,数据库,mysql

创建数据库Market、Team,按要求完成指定操作,MySQL,数据库,mysql

题目要求密码oldpwd1不满足默认策略要求,所以我们需要更改密码策略(临时的)

mysql> show variables like 'validate_password%';
+--------------------------------------+--------+
| Variable_name                        | Value  |
+--------------------------------------+--------+
| validate_password_check_user_name    | OFF    |
| validate_password_dictionary_file    |        |
| validate_password_length             | 8      |
| validate_password_mixed_case_count   | 1      |
| validate_password_number_count       | 1      |
| validate_password_policy             | MEDIUM |
| validate_password_special_char_count | 1      |
+--------------------------------------+--------+
7 rows in set (0.00 sec)

mysql> set global validate_password_policy='LOW';
Query OK, 0 rows affected (0.00 sec)

mysql> set global validate_password_length=0;
Query OK, 0 rows affected (0.00 sec)

mysql> set global validate_password_special_char_count=0;
Query OK, 0 rows affected (0.00 sec)

mysql> set global validate_password_mixed_case_count=0;
Query OK, 0 rows affected (0.00 sec)

mysql> set global validate_password_number_count=0;
Query OK, 0 rows affected (0.00 sec)

mysql> set global validate_password_length=0;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'validate_password%';
+--------------------------------------+-------+
| Variable_name                        | Value |
+--------------------------------------+-------+
| validate_password_check_user_name    | OFF   |
| validate_password_dictionary_file    |       |
| validate_password_length             | 0     |
| validate_password_mixed_case_count   | 0     |
| validate_password_number_count       | 0     |
| validate_password_policy             | LOW   |
| validate_password_special_char_count | 0     |
+--------------------------------------+-------+
7 rows in set (0.01 sec)

(1)创建一个新账户,用户名为account1,该用户通过本地主机连接数据库,密码为oldpwd。授权该用户对Team数据库中的player表中的select和insert权限,并且授权该用户对player表中的info字段的update权限。

#创建用户
mysql> create user account1@localhost identified by 'oldpwd1';
Query OK, 0 rows affected (0.01 sec)
#授权查、写
mysql> grant select,insert on Team.player to account1@'localhost';
Query OK, 0 rows affected (0.00 sec)
#授权更新info字段
mysql> grant update(info) on Team.player to 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'                                     |
| GRANT SELECT, INSERT, UPDATE (info) ON `Team`.`player` TO 'account1'@'localhost' |
+----------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

新启动一个本地连接的会话进行验证,并验证成功。

#成功登录该用户
[root@localhost ~]# mysql -uaccount1 -poldpwd1 
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 19
Server version: 5.7.18 MySQL Community Server (GPL)

Copyright (c) 2000, 2017, 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数据库
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| Team               |
+--------------------+
2 rows in set (0.00 sec)

mysql> use Team
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+----------------+
| Tables_in_Team |
+----------------+
| player         |
+----------------+
1 row in set (0.00 sec)

mysql> desc player;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| playid   | int(11)     | NO   | PRI | NULL    |       |
| playname | varchar(30) | NO   |     | NULL    |       |
| teamnum  | int(11)     | NO   | UNI | NULL    |       |
| info     | varchar(50) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

#当前playere表中没有任何数据
mysql> select * from player;
Empty set (0.00 sec)

#插入数据
mysql> insert into player values(1,'a',100,'GOOD'),(2,'b',120,'BAD');
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

#查询数据
mysql> select * from player;
+--------+----------+---------+------+
| playid | playname | teamnum | info |
+--------+----------+---------+------+
|      1 | a        |     100 | GOOD |
|      2 | b        |     120 | BAD  |
+--------+----------+---------+------+
2 rows in set (0.00 sec)

#由于没有给account1用户授予对player数据表删除的权限,所以失败。
mysql> delete from player where playid=1;
ERROR 1142 (42000): DELETE command denied to user 'account1'@'localhost' for table 'player'
mysql> 

(2)创建SQL语句,更改account1用户的密码为newpwd2

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

mysql> 

我们再次去之前启动的那个本地连接会话中进行验证,并验证成功

[root@localhost ~]# mysql -uaccount1 -poldpwd1   #旧密码登录报错
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'account1'@'localhost' (using password: YES)

[root@localhost ~]# mysql -uaccount1 -pnewpwd2   #新密码成功登录
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 21
Server version: 5.7.18 MySQL Community Server (GPL)

Copyright (c) 2000, 2017, 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> 

(3)创建SQL语句,使用FLUSH PRIVILEGES重新加载权限表

mysql> flush privileges;
Query OK, 0 rows affected (0.01 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)

mysql> 

(5)创建SQL语句,收回account1用户的权限

mysql> revoke all on Team.player from account1@localhost;
Query OK, 0 rows affected (0.00 sec)

mysql> 

(6)创建SQL语句,将account1用户的账号信息从系统中删除

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

mysql> 

我们再次去之前启动的那个本地连接会话中进行验证,并验证成功文章来源地址https://www.toymoban.com/news/detail-536322.html

[root@localhost ~]# mysql -uaccount1 -pnewpwd2   #已删除无法登录
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'account1'@'localhost' (using password: YES)
[root@localhost ~]# 

到了这里,关于创建数据库Market、Team,按要求完成指定操作的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

原文地址:https://blog.csdn.net/Liu_Cheng_H/article/details/131579859

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用

相关文章

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包