第四次作业

1 mysq1>创建数据库 mydb15 indexstu;

mysql> CREATE DATABASE mydb15_indexstu;
Query OK, 1 row affected (0.01 sec)
 
mysql> USE mydb15_indexstu;
Database changed

一、新建表
1、学生表学生，定义主键，姓名不能重名，性别只能输入男或女，所在系的默认值是“计算机”’，结构如下:2学生(Sno学号，Sname姓名，Ssex性别，Sage年龄，Sdept所在系)sno为主键3 mysq1>创建表学生(主键自动_递增)，学生名(30)不空唯一，学生名(2)检查(学生='男’或学生='女’)不空学生名(30)不空，学生名(10)默认计算机'不是零1);

mysql> CREATE TABLE STUDENT (
    -> Sno INT AUTO_INCREMENT PRIMARY KEY,
    -> Sname VARCHAR(30) NOT NULL UNIQUE,
    -> Ssex VARCHAR(2) CHECK (Ssex IN ('男', '女')) NOT NULL,
    -> Sage INT,
    -> Sdept VARCHAR(30) DEFAULT '计算机' NOT NULL
    -> );
Query OK, 0 rows affected (0.05 sec)

2、用SQL语句创建课程表
课程(Cno课程号，Cname课程名)cno为主键
mysq1>创建表Course(Cno int主键不为空，Cnamevarchar(20)不为num11);

mysql> CREATE TABLE Course (
    -> Cno INT PRIMARY KEY NOT NULL,
    -> Cname VARCHAR(20) NOT NULL
    -> );
Query OK, 0 rows affected (0.39 sec)

3、用SQL语句创建选课表
(Sno学号，Cno课程号，Score成绩)sno,cno为主键2部
9 mysq1>创建表sc (Sno int not null, Cno varchar (10)primary key not null, score int not nul1);

mysql> CREATE TABLE sc (
    -> Sno INT NOT NULL,
    -> Cno INT NOT NULL,
    -> Score INT NOT NULL,
    -> PRIMARY KEY (Sno, Cno)
    -> );
Query OK, 0 rows affected (0.03 sec)

二、处理表
mysq1
11.修改学生表中年龄(sage)字段属性，数据类型由int改变为小int

mysql> ALTER TABLE STUDENT
    -> MODIFY COLUMN Sage SMALLINT;
Query OK, 0 rows affected (0.22 sec)
Records: 0  Duplicates: 0  Warnings: 0
2 2.为课程表中课程号字段设置索引，并查看索引

mysql> CREATE INDEX idx_Course_Cno ON Course (Cno);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql> SHOW INDEX FROM Course;
+--------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table  | Non_unique | Key_name       | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+--------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| course |          0 | PRIMARY        |            1 | Cno         | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| course |          1 | idx_Course_Cno |            1 | Cno         | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+--------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
2 rows in set (0.02 sec)

3 3.为SC表建立按学号(sno)和课程号(cno)组合的升序的主键索引，索引名为SC_INDEX

mysql> CREATE UNIQUE INDEX SC_INDEX ON sc (Sno ASC, Cno ASC);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0
4 4.创建一视图 stu info,查询全体学生的姓名，性别，课程名，成绩

mysql> CREATE VIEW stu_info AS
    -> SELECT STUDENT.Sname, STUDENT.Ssex, Course.Cname, sc.Score
    -> FROM STUDENT
    -> JOIN sc ON STUDENT.Sno = sc.Sno
    -> JOIN Course ON sc.Cno = Course.Cno;
Query OK, 0 rows affected (0.01 sec)

5 5.删除所有索引

mysql> DROP INDEX idx_Course_Cno ON Course;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql> DROP INDEX SC_INDEX ON sc;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql>
————————————————