数据库day2--练习

重庆城科李立川

已于 2023-07-15 09:48:20 修改

阅读量160

点赞数

文章标签：数据库

于 2023-07-08 21:30:51 首次发布

本文链接：https://blog.csdn.net/lilichuan132/article/details/131609708

版权

在这里插入图片描述

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

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

// 2、创建数据表customers，在c_num字段上添加主键约束和自增约束，在c_birth字段上添加非空约束
mysql> use MarKet;
Database changed
mysql> create table customers
    -> ( c_num int(11) primary key auto_increment,
    -> c_name varchar(50),
    -> c_city varchar(50),
    -> c_birth datetime not null);
Query OK, 0 rows affected (0.01 sec)

// 3、将c_contact字段插入c_birth字段后面
mysql> alter table customers add c_contact varchar(50)
    -> ;
Query OK, 0 rows affected (0.04 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.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> 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.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_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.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    |                |
| 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)

// 8、删除字段c_city
mysql> alter table customers_info drop c_city;
Query OK, 0 rows affected (0.00 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> 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_city` varchar(50) DEFAULT NULL,
  `c_birth` datetime NOT NULL,
  `c_phone` varchar(50) DEFAULT NULL,
  `c_contact` char(1) DEFAULT NULL,
  PRIMARY KEY (`c_num`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> alter table customers_info ENGINE=MyISAM;
Query OK, 0 rows affected (0.00 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_city` varchar(50) DEFAULT NULL,
  `c_birth` datetime NOT NULL,
  `c_phone` varchar(50) DEFAULT NULL,
  `c_contact` char(1) DEFAULT NULL,
  PRIMARY KEY (`c_num`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

在这里插入图片描述

// 1、创建数据表 orders，在o_num 字段上添加主键约束和自增约束，在c_id 字段上添加外键约束，关联customers 表中的主键c_num
mysql> create table orders
    -> (o_num INT(11) primary key auto_increment,
    -> o_date date,
    -> c_id VARCHAR(50));
Query OK, 0 rows affected (0.00 sec)

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   | varchar(50) | YES  |     | NULL    |                |
+--------+-------------+-

// 2、删除orders表的外键约束，然后删除表customers
mysql> alter table orders
    -> add constraint `orders_cid` foreign key (`c_id`) references `customers_info` (`c_num`);
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

在这里插入图片描述

//创建数据库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 PRIMARY KEY,
    -> playname VARCHAR(30) NOT NULL,
    -> teamnum INT NOT NULL UNIQUE,
    -> info VARCHAR(50));
Query OK, 0 rows affected (0.01 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)

在这里插入图片描述

// 1、创建一个新账户，用户名为accountl，该用户通过本地主机连接数据库，密码为oldpwd1。授权该用户对Team数据库中 player表的SELECT 和 INSERT权限，并且授权该用户对player表的info字段的UPDATE权限。
mysql> create user account1@localhost identified by 'oldpwd1';
Query OK, 0 rows affected (0.01 sec)

// 2、创建SQL 语句，更改account1用户的密码为newpwd2。
mysql> grant select,insert,update(info) on team.player to account1@localhost;
Query OK, 0 rows affected (0.01 sec)

// 3、创建SQL语句，使用FLUSH PRIVILEGES重新加载权限表。
mysql> alter user account1@localhost identified by 'oldpwd2';
Query OK, 0 rows affected (0.00 sec)

// 4、创建SQL语句，查看授权给account1用户的权限。
mysql> flush privileges;
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)

// 5、创建SQL语句,收回account1用户的权限。
mysql> revoke all on team.player from account1@localhost;
Query OK, 0 rows affected (0.00 sec)

// 6、创建SOL语句，将account1用户的账号信息从系统中删除。
mysql> drop user account1@localhost;
Query OK, 0 rows affected (0.00 sec)

重庆城科李立川

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
1
评论
数据库day2--练习

【代码】数据库day2--作业。
复制链接

扫一扫

数据库day2--练习

“相关推荐”对你有帮助么？