MySQL 命令行操作语句

在SQL SERVER 创建一个”学校”数据库
mysql> create database school;

在学校数据库中创建三张表:学生,课程,选课
学生
mysql> use school;
Database changed
mysql> CREATE TABLE student(sno VARCHAR(50),s_name VARCHAR(50) NOT NULL, s_age INT(30) NOT NULL,PRIMARY KEY (s_id));

课程
mysql> CREATE TABLE course (cno INT(10)NOT NULL,c_name VARCHAR(50) NOT NULL,teacher VARCHAR(50) NOT NULL,PRIMARY KEY (cno));

选课
mysql> CREATE TABLE studentcourse (sno INT(10)NOT NULL,cno VARCHAR(50) NOT NULL,PRIMARY KEY (sno,cno),grade INT(20));

分别为这三张表在主键上创建一个索引
学生
CREATE INDEX index_student ON student(Sno);
课程
CREATE INDEX index_Cno ON course(Cno);
选课
CREATE INDEX index_studentcourse ON studentcourse(Sno);
CREATE INDEX Cno ON studentcourse(Cno);

分别向这三张表中插入三条数据,要求选课表中出现的学号和课程号必须在学生和课程表中出现
学生
INSERT INTO student VALUE(1,'lilei',23);
INSERT INTO student VALUE(2,'hanmeimei',22);
INSERT INTO student VALUE(3,'yaoming',44);
课程
INSERT INTO course VALUE(1,'navigation','laowang');
INSERT INTO course VALUE(2,'English','teacher_chen');
INSERT INTO course VALUE(3,'CS','pan');
选课
INSERT INTO studentcourse VALUE(1,1,88);
INSERT INTO studentcourse VALUE(1,2,90);
INSERT INTO studentcourse VALUE(1,3,66);
INSERT INTO studentcourse VALUE(2,1,99);
INSERT INTO studentcourse VALUE(2,2,70);
INSERT INTO studentcourse VALUE(2,3,75);
INSERT INTO studentcourse VALUE(3,1,55);
INSERT INTO studentcourse VALUE(3,2,35);
INSERT INTO studentcourse VALUE(3,3,85);

检索学习某一具体课程号课程的学生学号和成绩
mysql> SELECT Cno,Sno,grade FROM studentcourse WHERE Cno=2;

检索学习某一具体课程号课程的学生学号和姓名
mysql> SELECT s_name,student.sno,cno FROM student,studentcourse WHERE studentcourse.Sno=student.Sno AND studentcourse.Cno=1;

检索学习某一具体课程名的学生学号和姓名
SELECT c_name,student.Sno,S_name FROM student,course,studentcourse WHERE student.Sno=studentcourse.Sno AND studentcourse.Cno=course.Cno AND c_name='CS';

在学生,选课和课程三张表上创建一个视图,视图可显示选修某一门课程学生数.按课程名称分组,按学生数排降序.
CREATE VIEW coursecount(cname,studentnumber) AS SELECT c_name,COUNT(Sno) AS studentnumber FROM course,studentcourse WHERE course.cno=studentcourse.cno GROUP BY c_name ORDER BY studentnumber DESC;

在学生,选课和课程三张表上创建视图,视图可显示选修某一门课程学生姓名,按照姓名升序. (CS课)
mysql> CREATE VIEW course_s_name(cname,sname) AS SELECT c_name,s_name FROM course,student,studentcourse WHERE student.Sno=studentcourse.Sno AND studentcourse.Cno=course.Cno AND c_name= 'cs' ORDER BY s_name;

删除两个视图
mysql> DROP VIEW coursecount;
mysql> DROP VIEW course_s_name;

从学生能表里删除一条记录，并查看删除的结果
DELETE FROM student WHERE Sno=2;
SELECT * FROM student;

分别删除三张表的索引
DROP INDEX index_Cno ON course;
DROP INDEX index_student ON student;
DROP INDEX index_studentcourse ON studentcourse;

删除三张表格
DROP TABLE student,course,studentcourse;

删除数据库
DROP DATABASE school;