找工作哪个网站好2022_适合做视频的自媒体平台_网络营销推广方案设计_优网营销

题目

1、建库并使用

mysql> create database mydb15_indexstu;
Query OK, 1 row affected (0.02 sec)mysql> use mydb15_indexstu;
Database changed

2、建表

mysql> create table student(-> sno int primary key auto_increment,-> sname varchar(30) not null unique,-> ssex varchar(2) check (ssex='男' or ssex='女') not null,-> sage int not null,-> sdept varchar(10) default '计算机' not null-> );
Query OK, 0 rows affected (0.05 sec)mysql> create table course(-> cno int primary key not null,-> cname varchar(20) not null-> );
Query OK, 0 rows affected (0.05 sec)mysql> create table sc(-> sno int not null,-> cno varchar(10) primary key not null,-> score int not null-> );
Query OK, 0 rows affected (0.05 sec)mysql> desc student;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| sno   | int         | NO   | PRI | NULL    | auto_increment |
| sname | varchar(30) | NO   | UNI | NULL    |                |
| ssex  | varchar(2)  | NO   |     | NULL    |                |
| sage  | int         | NO   |     | NULL    |                |
| sdept | varchar(10) | NO   |     | 计算机  |                |
+-------+-------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)mysql> desc course;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| cno   | int         | NO   | PRI | NULL    |       |
| cname | varchar(20) | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)mysql> desc sc;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| sno   | int         | NO   |     | NULL    |       |
| cno   | varchar(10) | NO   | PRI | NULL    |       |
| score | int         | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

3、处理要求

3.1 修改 Student 表中年龄（sage）字段属性，数据类型由 int 改变为 smallint

mysql> alter table student modify sage smallint;
Query OK, 0 rows affected (0.12 sec)
Records: 0  Duplicates: 0  Warnings: 0mysql> desc student;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| sno   | int         | NO   | PRI | NULL    | auto_increment |
| sname | varchar(30) | NO   | UNI | NULL    |                |
| ssex  | varchar(2)  | NO   |     | NULL    |                |
| sage  | smallint    | YES  |     | NULL    |                |
| sdept | varchar(10) | NO   |     | 计算机  |                |
+-------+-------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

3.2 为 Course 表中课程号字段设置索引，并查看索引

mysql> create index course_cno on course(cno);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0mysql> show index from course\G
*************************** 1. row ***************************Table: courseNon_unique: 0Key_name: PRIMARYSeq_in_index: 1Column_name: cnoCollation: ACardinality: 0Sub_part: NULLPacked: NULLNull:Index_type: BTREEComment:
Index_comment:Visible: YESExpression: NULL
*************************** 2. row ***************************Table: courseNon_unique: 1Key_name: course_cnoSeq_in_index: 1Column_name: cnoCollation: ACardinality: 0Sub_part: NULLPacked: NULLNull:Index_type: BTREEComment:
Index_comment:Visible: YESExpression: NULL
2 rows in set (0.00 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.04 sec)
Records: 0  Duplicates: 0  Warnings: 0mysql> show index from sc\G
*************************** 1. row ***************************Table: scNon_unique: 0Key_name: PRIMARYSeq_in_index: 1Column_name: cnoCollation: ACardinality: 0Sub_part: NULLPacked: NULLNull:Index_type: BTREEComment:
Index_comment:Visible: YESExpression: NULL
*************************** 2. row ***************************Table: scNon_unique: 0Key_name: SC_INDEXSeq_in_index: 1Column_name: snoCollation: ACardinality: 0Sub_part: NULLPacked: NULLNull:Index_type: BTREEComment:
Index_comment:Visible: YESExpression: NULL
*************************** 3. row ***************************Table: scNon_unique: 0Key_name: SC_INDEXSeq_in_index: 2Column_name: cnoCollation: ACardinality: 0Sub_part: NULLPacked: NULLNull:Index_type: BTREEComment:
Index_comment:Visible: YESExpression: NULL
3 rows in set (0.02 sec)

3.4 创建视图 stu_info，查询全体学生的姓名、性别、课程名、成绩

mysql> create view stu_info as-> select s.sname as '姓名', s.ssex as '性别', c.cname as '课程名', sc.s
core as '成绩'-> from student s-> join sc on s.sno = sc.sno-> join course c on sc.cno = c.cno;
Query OK, 0 rows affected (0.02 sec)mysql> create view stu_info as-> select student.sname as '姓名', student.ssex as '性别', course.cname as '课程名', sc.s
core as '成绩'-> from student,course,sc-> where student.sno=sc.sno and sc.cno= course.cno

3.5 删除所有索引

mysql> drop index course_cno on course;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0mysql> drop index SC_INDEX on sc;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0