MYSQL 基础实验一

1.查看当前系统有哪些数据库？

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

2.创建图书馆数据库db_library，给数据库db_library创建以下数据表：

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

mysql> use db_library;
Database changed

3.创建读者数据表：

mysql> create table t_reader(reader_id char(6),reader_name varchar(50),reader_se
x char(2),reader_borrowtotal float);
Query OK, 0 rows affected (0.03 sec)

4.查看t_reader表结构

mysql> desc t_reader;
+--------------------+-------------+------+-----+---------+-------+
| Field              | Type        | Null | Key | Default | Extra |
+--------------------+-------------+------+-----+---------+-------+
| reader_id          | char(6)     | YES  |     | NULL    |       |
| reader_name        | varchar(50) | YES  |     | NULL    |       |
| reader_sex         | char(2)     | YES  |     | NULL    |       |
| reader_borrowtotal | float       | YES  |     | NULL    |       |
+--------------------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

5.创建图书数据表：

mysql> create table t_book(ISBN char(17),book_name varchar(50),book_author varch
ar(20),book_price dec(6.1),press_id char(3));
Query OK, 0 rows affected (0.04 sec)

6.查看图书数据表结构

mysql>desc t_book;

7.创建出版社数据表：

mysql> create table t_press(press_id char(3),press_name varchar(50),website varc
har(50),postcode char(6),press_telephone varchar(50),press_email varchar(50),pre
ss_adress varchar(100));
Query OK, 0 rows affected (0.03 sec)

8.查看出版社数据表结构

mysql> desc t_press;
+-----------------+--------------+------+-----+---------+-------+
| Field           | Type         | Null | Key | Default | Extra |
+-----------------+--------------+------+-----+---------+-------+
| press_id        | char(3)      | YES  |     | NULL    |       |
| press_name      | varchar(50)  | YES  |     | NULL    |       |
| website         | varchar(50)  | YES  |     | NULL    |       |
| postcode        | char(6)      | YES  |     | NULL    |       |
| press_telephone | varchar(50)  | YES  |     | NULL    |       |
| press_email     | varchar(50)  | YES  |     | NULL    |       |
| press_adress    | varchar(100) | YES  |     | NULL    |       |
+-----------------+--------------+------+-----+---------+-------+
7 rows in set (0.01 sec)

9.创建归还记录数据表：

mysql> create table t_return_record(return_id char(9),borrow_id char(6),return_d
ate date,ISBN char(17));
Query OK, 0 rows affected (0.03 sec)

10.查看归还记录数据表结构

mysql> desc t_return_record;
+-------------+----------+------+-----+---------+-------+
| Field       | Type     | Null | Key | Default | Extra |
+-------------+----------+------+-----+---------+-------+
| return_id   | char(9)  | YES  |     | NULL    |       |
| borrow_id   | char(6)  | YES  |     | NULL    |       |
| return_date | date     | YES  |     | NULL    |       |
| ISBN        | char(17) | YES  |     | NULL    |       |
+-------------+----------+------+-----+---------+-------+
4 rows in set (0.00 sec)

11.创建借阅记录数据表：

mysql> create table t_borrow_record(borrow_id char(6),reader_id char(6),ISBN cha
r(17),borrow_date date);
Query OK, 0 rows affected (0.03 sec)

12.查看借阅记录数据表结构

mysql> desc t_borrow_record;
+-------------+----------+------+-----+---------+-------+
| Field       | Type     | Null | Key | Default | Extra |
+-------------+----------+------+-----+---------+-------+
| borrow_id   | char(6)  | YES  |     | NULL    |       |
| reader_id   | char(6)  | YES  |     | NULL    |       |
| ISBN        | char(17) | YES  |     | NULL    |       |
| borrow_date | date     | YES  |     | NULL    |       |
+-------------+----------+------+-----+---------+-------+
4 rows in set (0.01 sec)

13.给归还记录数据表的”借阅编号”字段后面增加一个新字段：”借阅日期”。

mysql> alter table t_return_record add borrow_date date after borrow_id;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

14：创建t_temp其结构和”借阅记录”数据表的结构相同

mysql> create table t_temp like t_borrow_record;
Query OK, 0 rows affected (0.04 sec)

15: 将表t_temp重命名为t_temp1。

mysql> alter table t_temp rename t_temp1;
Query OK, 0 rows affected (0.03 sec)

16: 在t_temp表下添加两个字段：book_copy（复本量），book_ inventory（库存量），数据类型均为INT。

mysql> alter table t_temp1 add book_copy int;
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table t_temp1 add book_inventory int;
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

17: 删除t_return_record表下的两个字段ISBN（图书编号），borrow_date（借阅日期）。

mysql> alter table t_return_record drop ISBN;
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table t_return_record drop borrow_date;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

18.将表t_reader中的字段reader_borrowtotal的数据类型改为INT；将表t_press中的字段website改名为press_website，字段postcode改名为press_postcode。

mysql> alter table t_reader modify reader_borrowtotal int;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> alter table t_press change website  press_website varchar(50);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table t_press change postcode press_postcode char(6);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

19.将表t_press中字段press_postcode调整到字段press_address之后。

mysql> alter table t_press modify press_postcode char(6) after press_adress;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0