博客

一、实验目的：

1．掌握创建、修改及删除数据库、数据表的方法；

2．掌握SQL语言增、删、改操作。

二、实验内容和步骤：

1．查看数据库系统中已存在的数据库。

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

2．查看该数据库系统支持的存储引擎的类型。

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| ndbinfo            | NO      | MySQL Cluster system information storage engine                | NULL         | NULL | NULL       |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| ndbcluster         | NO      | Clustered, fault-tolerant tables                               | NULL         | NULL | NULL       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
11 rows in set (0.00 sec)

3．创建student数据库和teaching数据库。

mysql> create database student; 
Query OK, 1 row affected (0.01 sec)

mysql> create database teaching;
Query OK, 1 row affected (0.01 sec)

4. 再次查看数据库系统中已经存在的数据库，确保student和teaching数据库已经存在。

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

5．删除student数据库。

mysql> drop database student;
Query OK, 0 rows affected (0.01 sec)

6．再次查看数据库系统中已经存在的数据库，确保student数据库已经删除。

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

7．创建一个studentcourse数据库。

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

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

8．在数据库studentcourse中创建一个表S，它由sno char(11)、sname char(8)、ssex char(2)、sage smallint、sdept char(20) (含义分别是学号、姓名、性别、年龄、系)组成，其中sno为主键。

mysql> use studentcourse;
Database changed
mysql> create table S(
    -> sno char(11),
    -> sname char(8),
    -> ssex char(2),
    -> sage smallint,
    -> sdept char(20),
    -> primary key(sno));
Query OK, 0 rows affected (0.02 sec)
mysql> describe s;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| sno   | char(11) | NO   | PRI | NULL    |       |
| sname | char(8)  | YES  |     | NULL    |       |
| ssex  | char(2)  | YES  |     | NULL    |       |
| sage  | smallint | YES  |     | NULL    |       |
| sdept | char(20) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
5 rows in set (0.00 sec)

9．在数据库studentcourse中创建一个表C，包括 cno char(10)、cname char(20)、ccredit int 属性 (含义是课程号、课程名、学分)，要求建立cno为主键，cnam非空。

mysql> create table C(
    -> cno char(10) primary key,
    -> cname char(20) not null,
    -> ccredit int);
Query OK, 0 rows affected (0.01 sec)

mysql> describe c;
+---------+----------+------+-----+---------+-------+
| Field   | Type     | Null | Key | Default | Extra |
+---------+----------+------+-----+---------+-------+
| cno     | char(10) | NO   | PRI | NULL    |       |
| cname   | char(20) | NO   |     | NULL    |       |
| ccredit | int      | YES  |     | NULL    |       |
+---------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)

10．在数据库studentcourse中建立表SC，包括 sno char(11)、cno char(10)、grade smallint三个属性，要求建立主键（sno,cno）。

mysql> create table SC(
    -> sno char(11),
    -> cno char(10),
    -> grade smallint,
    -> primary key(sno,cno));
Query OK, 0 rows affected (0.02 sec)

11．在C表中增加一列 teacher char(8)。

mysql> alter table c add column teacher char(8);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> describe c;
+---------+----------+------+-----+---------+-------+
| Field   | Type     | Null | Key | Default | Extra |
+---------+----------+------+-----+---------+-------+
| cno     | char(10) | NO   | PRI | NULL    |       |
| cname   | char(20) | NO   |     | NULL    |       |
| ccredit | int      | YES  |     | NULL    |       |
| teacher | char(8)  | YES  |     | NULL    |       |
+---------+----------+------+-----+---------+-------+
4 rows in set (0.00 sec)

12．删除C表中teacher一列。

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

mysql> describe c;
+---------+----------+------+-----+---------+-------+
| Field   | Type     | Null | Key | Default | Extra |
+---------+----------+------+-----+---------+-------+
| cno     | char(10) | NO   | PRI | NULL    |       |
| cname   | char(20) | NO   |     | NULL    |       |
| ccredit | int      | YES  |     | NULL    |       |
+---------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)

13．修改S表中sname 为非空。

mysql> alter table s change sname sname char(8) not null;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> describe s;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| sno   | char(11) | NO   | PRI | NULL    |       |
| sname | char(8)  | NO   |     | NULL    |       |
| ssex  | char(2)  | YES  |     | NULL    |       |
| sage  | smallint | YES  |     | NULL    |       |
| sdept | char(20) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
5 rows in set (0.00 sec)

14．将S表的主键删除。

mysql> alter table s drop primary key;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc s;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| sno   | char(11) | NO   |     | NULL    |       |
| sname | char(8)  | NO   |     | NULL    |       |
| ssex  | char(2)  | YES  |     | NULL    |       |
| sage  | smallint | YES  |     | NULL    |       |
| sdept | char(20) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
5 rows in set (0.00 sec)

15．建立S表的主键sno。

mysql> alter table s change column sno sno char(11) primary key;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc s;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| sno   | char(11) | NO   | PRI | NULL    |       |
| sname | char(8)  | NO   |     | NULL    |       |
| ssex  | char(2)  | YES  |     | NULL    |       |
| sage  | smallint | YES  |     | NULL    |       |
| sdept | char(20) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
5 rows in set (0.00 sec)

16．对SC表建立与S及C表联接的外键。

mysql> alter table sc add foreign key(sno) references s(sno);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table sc add foreign key(cno) references c(cno);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc sc;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| sno   | char(11) | NO   | PRI | NULL    |       |
| cno   | char(10) | NO   | PRI | NULL    |       |
| grade | smallint | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)

删除外键：

show create table

表名

;

alter table

表名

drop foreign key

外键名

;

17．增加一条学生记录“S10，自己姓名，自己性别，自己年龄，计算机”

mysql> insert into s values('S10','小明','男','22','计算机');
Query OK, 1 row affected (0.00 sec)

mysql> select * from s;
+-----+-----------+------+------+-----------+
| sno | sname     | ssex | sage | sdept     |
+-----+-----------+------+------+-----------+
| S10 | 小明    | 男   |   22 | 计算机    |
+-----+-----------+------+------+-----------+
1 row in set (0.00 sec)

18. 将“C10”、“数据库原理及应用”、4；“C11”、“离散数学”4；“C12”、“操作系统原理”、4 ；加入C 表。

mysql> insert into c values('C10','数据库原理及应用',4);
Query OK, 1 row affected (0.00 sec)

mysql> insert into c values('C11','离散数学',4);
Query OK, 1 row affected (0.00 sec)

mysql> insert into c values('C12','操作系统原理',4);
Query OK, 1 row affected (0.00 sec)

mysql> select * from c;
+-----+--------------------------+---------+
| cno | cname                    | ccredit |
+-----+--------------------------+---------+
| C10 | 数据库原理及应用         |       4 |
| C11 | 离散数学                 |       4 |
| C12 | 操作系统原理             |       4 |
+-----+--------------------------+---------+
3 rows in set (0.00 sec)

19．向 SC表增加6条记录。

mysql> insert into sc values('S10','C10',100);
Query OK, 1 row affected (0.00 sec)

mysql> insert into sc values('S10','C11',100);
Query OK, 1 row affected (0.00 sec)

mysql> insert into sc values('S10','C12',100);
Query OK, 1 row affected (0.00 sec)

mysql> insert into s values('S11','帅哥','男','22','物理系');
Query OK, 1 row affected (0.00 sec)

mysql> insert into sc values('S11','C10',60);
Query OK, 1 row affected (0.00 sec)

mysql> insert into sc values('S11','C11',60);
Query OK, 1 row affected (0.00 sec)

mysql> insert into sc values('S11','C12',60);
Query OK, 1 row affected (0.00 sec)

mysql> select * from sc;
+-----+-----+-------+
| sno | cno | grade |
+-----+-----+-------+
| S10 | C10 |   100 |
| S10 | C11 |   100 |
| S10 | C12 |   100 |
| S11 | C10 |    60 |
| S11 | C11 |    60 |
| S11 | C12 |    60 |
+-----+-----+-------+
6 rows in set (0.00 sec)

mysql> select * from sc;
+-----+-----+-------+
| sno | cno | grade |
+-----+-----+-------+
| S10 | C10 |   100 |
| S10 | C11 |   100 |
| S10 | C12 |   100 |
| S11 | C10 |    60 |
| S11 | C11 |    60 |
| S11 | C12 |    60 |
+-----+-----+-------+
6 rows in set (0.00 sec)

20．删除“物理系”的学生记录。（无物理系的需要添加）

mysql> select * from s;
+-----+-----------+------+------+-----------+
| sno | sname     | ssex | sage | sdept     |
+-----+-----------+------+------+-----------+
| S10 | 小明   | 男   |   22 | 计算机    |
| S11 | 帅哥      | 男   |   22 | 物理系    |
+-----+-----------+------+------+-----------+
2 rows in set (0.00 sec)

mysql> delete from s where sdept='物理系';
Query OK, 1 row affected (0.00 sec)

mysql> select * from s;
+-----+-----------+------+------+-----------+
| sno | sname     | ssex | sage | sdept     |
+-----+-----------+------+------+-----------+
| S10 | 小明    | 男   |   22 | 计算机    |
+-----+-----------+------+------+-----------+
1 row in set (0.00 sec)

21．删除选修“C10”的学生的选修记录。

mysql> select * from sc;
+-----+-----+-------+
| sno | cno | grade |
+-----+-----+-------+
| S10 | C10 |   100 |
| S10 | C11 |   100 |
| S10 | C12 |   100 |
| S11 | C10 |    60 |
| S11 | C11 |    60 |
| S11 | C12 |    60 |
+-----+-----+-------+
6 rows in set (0.00 sec)

mysql> delete from sc where cno='C10';
Query OK, 2 rows affected (0.00 sec)

mysql> select * from sc;
+-----+-----+-------+
| sno | cno | grade |
+-----+-----+-------+
| S10 | C11 |   100 |
| S10 | C12 |   100 |
| S11 | C11 |    60 |
| S11 | C12 |    60 |
+-----+-----+-------+
4 rows in set (0.00 sec)

22．把学生S3的院系改为商学院。（无S3的需要添加）

mysql> insert into s values('S3','美女','女','22','计算机');
Query OK, 1 row affected (0.00 sec)

mysql> select * from s;
+-----+-----------+------+------+-----------+
| sno | sname     | ssex | sage | sdept     |
+-----+-----------+------+------+-----------+
| S10 | 小明    | 男   |   22 | 计算机    |
| S3  | 美女      | 女   |   22 | 计算机    |
+-----+-----------+------+------+-----------+
2 rows in set (0.00 sec)

mysql> update s set sdept='商学院' where sno='S3';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from s;
+-----+-----------+------+------+-----------+
| sno | sname     | ssex | sage | sdept     |
+-----+-----------+------+------+-----------+
| S10 | 牛鑫科    | 男   |   22 | 计算机    |
| S3  | 美女      | 女   |   22 | 商学院    |
+-----+-----------+------+------+-----------+
2 rows in set (0.00 sec)

23．将选修C11课程学生的成绩都增加5分。

mysql> select * from sc;
+-----+-----+-------+
| sno | cno | grade |
+-----+-----+-------+
| S10 | C11 |   100 |
| S10 | C12 |   100 |
| S11 | C11 |    60 |
| S11 | C12 |    60 |
+-----+-----+-------+
4 rows in set (0.00 sec)

mysql> update sc set grade=grade+5 where cno='C11';
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> select * from sc;
+-----+-----+-------+
| sno | cno | grade |
+-----+-----+-------+
| S10 | C11 |   105 |
| S10 | C12 |   100 |
| S11 | C11 |    65 |
| S11 | C12 |    60 |
+-----+-----+-------+
4 rows in set (0.00 sec)

三、实验总结体会

1．掌握数据库和表的操作。



2．定义和管理数据完整性方法。



3. 熟悉存储引擎InnoDB的特点。