mysql的基本的数据库的查询
学习一个数据库我们要学习哪些东西：
sql数据库的话，
curd.
对于查询，要注意表的关联的查询。
索引，触发器，对于控制连接量，脚本，
数据库的可视化工具，权限管理。
http://www.360doc.com/content/09/0722/10/116129_4382792.shtml
**********************************************************************
*********************************************************************
把大学时老师让我们练习一个实验搞出来的了：
一 目的与要求
（1）掌握数据库对象的操作过程，包括创建、修改、删除。
（2）熟悉表的各种操作，包括插入、修改、删除、查询。
（3）熟练掌握常用 SQL 语句的基本语法。
二 实验设备与环境
使用 SQL Server 数据库管理系统提供的 SSMS 和查询编辑器。
三 实验内容、实验记录及实验结果与分析
（1）实验内容
学生选课系统
要求如下：
建立一个数据库和相关的表、索引、视图等数据库对象，练习对表、索引和视图的各种操作。
要求认真进行实验，记录各实验用例及执行结果。
深入了解各个操作的功能。
（2）实验的具体要求
①数据定义
基本表的创建、修改及删除
索引的创建
视图的创建
②数据操作
插入数据
修改数据
删除数据
③ 数据查询
单表查询
分组统计
连接查询
嵌套查询
集合查询
④ 视图操作
创建视图
视图查询
（3）实验记录、结果
一、数据定义
创建学生选课数据库 ST，包括三个基本表，其中 Student 表保存学生基本信息，Course 表保存课程信息，SC 表保存学生选课信息，其结构如下表所示。
表 2-1 Student 表结构
|
列名称 |
用途 |
类型 |
长度 |
约束 |
备注 |
|
Sno |
学号 |
字符 |
8 |
主键 |
|
|
Sname |
姓名 |
字符 |
8 |
|
|
|
Ssex |
性别 |
字符 |
2 |
|
|
|
Sage |
年龄 |
整型 |
|
|
|
|
Sdept |
所在系 |
字符 |
20 |
|
|
|
Sclass |
班级 |
字符 |
4 |
|
|
表 2-2 Course 表结构
|
列名称 |
用途 |
类型 |
长度 |
约束 |
备注 |
|
Cno |
课程号 |
字符 |
4 |
主键 |
|
|
Cname |
课程名 |
字符 |
40 |
|
|
|
Cpno |
先修课程号 |
字符 |
4 |
|
|
|
Ccredit |
学分 |
整型 |
|
|
|
表 2-3 SC 表结构、
|
列名称 |
用途 |
类型 |
长度 |
约束 |
备注 |
|
Sno |
学号 |
字符 |
8 |
外键 |
|
|
Cno |
课程号 |
字符 |
4 |
|
|
|
Sage |
年龄 |
整型 |
|
|
|
1．创建、修改及删除基本表
（1）Student 表：
CREATE TABLE Student
( Sno CHAR(8) PRIMARY KEY,
Sname CHAR(8) ,
Ssex CHAR(2) NOT NULL,
Sage INT,
Sdept CHAR(20)
);
（2）Course 表：
CREATE TABLE Course
( Cno CHAR(4) PRIMARY KEY,
Cname CHAR(40) NOT NULL,
Cpno CHAR(4) ,
Ccredit SMALLINT,
);
（3）SC 表：
CREATE TABLE SC
( Sno CHAR(8) FOREIGN KEY (Sno) REFERENCES Student(Sno),
Cno CHAR(4),
Grade SMALLINT,
);
（4）检查表是否创建成功
SELECT * FROM Student;
SELECT * FROM Course;
SELECT * FROM SC;
（5）修改表结构及约束
增加班级列
ALTER TABLE Student ADD Sclass char(4);
修改年龄列
ALTER TABLE Student ALTER COLUMN Sage smallint;
增加约束
ALTER TABLE Course ADD UNIQUE(Cname);
2. 创建、删除索引
（1）为 Course 表按课程名称创建索引
CREATE INDEX iCname On Course(Cname);
（2）为 Student 表按学生姓名创建唯一索引
CREATE UNIQUE INDEX iSname ON Student(Sname);
（3）为 SC 表按学号和课程号创建聚集索引
CREATE CLUSTERED INDEX iSnoCno ON SC(Sno,Cno DESC);
（4）为 Course 表按课程号创建唯一索引
▼ CREATE UNIQUE INDEX iCno ON Course(Cno);
3．创建视图
建立信息系学生的视图：
CREATE VIEW IS_Student
AS
SELECT Sno,Sname,Sage FROM Student;
WHERE Sdept= 'IS';
二、数据操作
1．插入数据
(1)插入到Student表中：
INSERT INTO Student VALUES('20100001','李勇','男',20,'CS','1001')
INSERT INTO Student VALUES('20100002','刘晨','女',19,'CS','1001')
INSERT INTO Student(Sno,Sname,Ssex,Sage,Sdept,Sclass)
VALUES('20100021','王敏','女',18,'MA','1002')
INSERT INTO Student(Sno,Sname,Ssex,Sage,Sdept,Sclass)
VALUES('20100031','张立','男',19,'IS','1003')
INSERT INTO Student(Sno,Sname,Ssex,Sclass)
VALUES('20100003','刘洋','女','1001')
▼ INSERT INTO Student(Sno,Sname,Ssex,Sage,Sdept,Sclass)
VALUES('20100010','赵斌','男','19','IS','1005')
▼ INSERT INTO Student
VALUES('20100022','张明明','男’,'19','CS','1002')
（2）插入到Course表中：
INSERT INTO Course(Cno,Cname,Cpno,Ccredit)
VALUES('1','数据库系统原理','5',4)
INSERT INTO Course(Cno,Cname,Cpno,Ccredit)
VALUES('2','高等数学',null,2)
INSERT INTO Course(Cno,Cname,Cpno,Ccredit)
VALUES('3','管理信息系统','1',4)
▼ INSERT INTO Course(Cno,Cname,Cpno,Ccredit)
VALUES('4','操作系统系统原理','6',3)
▼ INSERT INTO Course(Cno,Cname,Cpno,Ccredit)
VALUES('5','数据结构','7',4)
▼ INSERT INTO Course(Cno,Cname,Cpno,Ccredit)
VALUES('6','数据处理',null,2)
▼ INSERT INTO Course(Cno,Cname,Cpno,Ccredit)
VALUES('7','C语言',null,4)
（3）插入到SC表中：
INSERT INTO SC VALUES('20100001','1',92)
▼ INSERT INTO SC VALUES('20100001','2',85)
▼ INSERT INTO SC VALUES('20100001','3',88)
▼ INSERT INTO SC VALUES('20100002','1',90)
INSERT INTO SC VALUES('20100002','2',80)
▼ INSERT INTO SC(Sno,Cno) VALUES('20100003','1')
INSERT INTO SC(Sno,Cno,Grade) VALUES('20100010','3',null)
（4）多行插入到表中
创建存一个表，保存学生的学号、姓名和年龄：
CREATE TABLE cs_Student
( 学号 char(8),
姓名 char(8),
年龄 smallint
);
插入数据行：
INSERT INTO cs_Student
SELECT Sno,Sname,Sage
FROM student Where Sdept='CS';
（5）检查插入到表中的数据
SELECT * FROM Student
SELECT * FROM Course
SELECT * FROM SC
2．修改数据
（1）将学生 20100001 的年龄改为 22 岁。
UPDATE student SET Sage = 22 WHERE Sno='20100001';
（2）将所有学生的年龄增加一岁：
UPDATE Student SET Sage = Sage +1
（3）填写赵斌同学的管理信息系统课程的成绩：
UPDATE SC SET Grade = 85
WHERE Sno='20100010' AND Cno='3'
（4）将计算机科学系全体学生的成绩加5分：
UPDATE sc SET Grade=Grade + 5
WHERE 'CS'=(select Sdept
from student
where student.Sno=sc.Sno);
（5）▼ 将刘晨同学的 2 号课程成绩修改为 80：
UPDATE SC SET Grade=80
WHERE Cno='2' AND Sno=(SELECT Sno FROM Student
WHERE SC.Sno=Student.Sno AND Sname='刘晨');
▼ 将“20100021”同学的学号修改为“20100025”：
UPDATE Student SET Sno='20100025'
WHERE Sno='20100021';
3. 删除数据
（1）删除学号为 201000022 的学生记录：
DELETE FROM Student WHERE Sno='20100022'
（2）删除学号 20100001 学生的 1 号课程选课记录
将选课信息复制到一个临时表 tmpSC 中：
SELECT * INTO tmpSC FROM SC
在 tmpSC 中执行删除操作：
DELETE FROM tmpSC WHERE Sno='20100001' and Cno='1'
（3）▼ 删除临时表中 20100002 学生的全部选课记录
SELECT * INTO tmpSC FROM SC
DELETE FROM tmpSC WHERE Sno='20100002'
（4）删除计算机科学系所有学生的选课记录
DELETE FROM tmpSC WHERE 'CS'=(select Sdept
from student where
student.Sno=tmpSC.Sno );
（5）删除全部选课记录：
DELETE FROM tmpSC
三、数据查询操作
1．单表查询
（1）按指定目标列查询
查询学生的详细记录：
SELECT * FROM Student;
查询学生的学号、姓名和年龄
SELECT Sno,Sname,Sage FROM Student;
（2）目标列包含表达式的查询
查询全体学生的姓名、出生年份和所有系，要求用小写字母表示所有系名。
SELECT Sname,'Year of Birth: ',2004-Sage,LOWER(Sdept)
FROM Student;
（3）查询结果集中修改列名称
查询全体学生的姓名、出生年份和所有系，要求用小写字母表示所有系名：
SELECT Sname, 'Year of Birth:' as BIRTH, 2000-Sage BIRTHDAY,
DEPARTMENT = LOWER(Sdept)
FROM Student;
（4）取消重复行
查询选修了课程的学生学号：比较ALL和DISTINCT的区别
SELECT Sno FROM SC;
SELECT DISTINCT Sno FROM SC;
（5）简单条件查询
查询计算机科学系全体学生的名单
SELECT Sname FROM Student WHERE Sdept='CS';
（6）按范围查询
查询年龄在20~23岁之间的学生的姓名、系别和年龄
SELECT Sname,Sdept,Sage
FROM Student
WHERE Sage BETWEEN 20 AND 23
（7）查询属性值属于指定集合的行
查询信息系（IS）、数学系（MA）和计算机科学系（CS）学生的姓名和性别：
SELECT Sname,Ssex
FROM Student
WHERE Sdept IN ('IS','MA','CS');
（8）模糊查询
查询所有姓刘学生的姓名、学号和性别
SELECT Sname,Sno,Ssex
FROM Student
WHERE Sname LIKE '刘%'
（9）查询空值
查询缺少成绩的学生的学号和相应的课程号
SELECT Sno,Cno
FROM sc
WHERE Grade is null;
（10）多重条件查询
查询计算机科学系年龄在 20 岁以下的学生姓名：
SELECT Sname
FROM student
WHERE Sdept='CS' and Sage<20;
（11）结果集排序
查询全体学生情况，查询结果按所在系的系号升序排列，同一系中的学 生按年龄降序排列。
SELECT * FROM Student ORDER BY Sdept,Sage DESC;
（12）▼ 查询学生基本信息，结果集属性名使用汉字：
SELECT Sno 学号,Sname 姓名,Ssex 性别,Sage 年龄, Sdept 系所,Sclass 班级 FROM Student;
▼ 查询信息系且年龄大于23岁同学的学号和姓名：
SELECT Sno,Sname
FROM student
WHERE Sdept='IS' and Sage>23;
▼ 查询年龄是17、18、20、23岁同学的学号、姓名、年龄和所在系：
SELECT Sno,Sname,Sage,Sdept
FROM student
WHERE Sage=17 OR Sage=18 OR Sage=20 OR Sage=23;
▼ 查询年龄不在21~24岁之间的学生的姓名、系别和年龄：
SELECT Sname,Sdept,Sage
FROM student
WHERE Sage NOT BETWEEN 21 AND 24;
2．分组统计
（1）聚集函数的使用
查询学生总人数：
SELECT COUNT(*) FROM Student;
查询选修了课程的学生人数：
SELECT COUNT(DISTINCT Sno) FROM SC
查询最高分：
SELECT MAX(Grade) FROM SC
（2）聚集函数作用于部分行
统计2号课程的总分、均分和最高分：
SELECT SUM(grade) 总分,AVG(grade) 均分,MAX(grade) 最高分
FROM sc WHERE Cno='2'
（3）分组统计
统计各门课程的选课人数、均分和最高分：
select cno 课程号,count(*) 人数,AVG(grade) 均分,MAX(grade) 最高分
from sc group by Cno
统计均分大于90的课程
select cno 课程号,count(*) 人数,AVG(grade) 均分,MAX(grade) 最高分
from sc group by Cno
having AVG(grade) > 90
（4）▼ 统计每个同学的学号、选课数、平均成绩和最高成绩
SELECT Student.Sno 学号,COUNT(distinct Course.Cno) 选课数,AVG(Sc.Grade) 平均成绩,MAX(Sc.Grade) 最高成绩
FROM SC
JOIN Student ON (SC.Sno = Student.Sno)
JOIN Course ON (SC.Cno = Course.Cno)
GROUP BY Student.Sno;
▼ 统计每个班的每门课的选课人数、平均成绩和最高成绩
SELECT Student.Sclass 班级,Course.Cname 课程名,COUNT(*) 选课人数,AVG(Sc.Grade) 平均成绩,MAX(Sc.Grade) 最高成绩
FROM SC
JOIN Student ON (SC.Sno = Student.Sno)
JOIN Course ON (SC.Cno = Course.Cno)
GROUP BY Student.Sclass,Course.Cname
3. 连接查询
（1）在 WHERE 中指定连接条件
查询每个参加选课的学生信息及其选修课程的情况：
SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
FROM Student,SC
WHERE Student.Sno = SC.Sno
查询每一门课的间接先修课：
SELECT * FROM course first,course second
WHERE first.Cpno=second.Cno;
SELECT first.Cno,second.Cpno FROM course first,course second
WHERE first.Cpno=second.Cno;
（2）在 FROM 中指定连接条件
查询每个参加选课的学生信息及其选修课程的情况：
SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
FROM Student JOIN SC ON (Student.Sno=SC.Sno)
（3）使用外连接查询
查询每个学生信息及其选修课程的情况：
SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
FROM Student LEFT OUTER JOIN SC ON (Student.Sno=SC.Sno)
（4）复合条件连接查询
查询选修2号课程且成绩在90分以上的所有学生：
SELECT Student.Sno, Sname
FROM Student join SC ON (Student.Sno = SC.Sno)
WHERE SC.Cno= '2' AND SC.Grade > 90;
（5）多表查询
查询每个学生的学号、姓名、选修的课程名及成绩：
SELECT Student.Sno,Sname,Cname,Grade
FROM Student,SC,Course
WHERE Student.Sno = SC.Sno AND SC.Cno = Course.Cno;
（6）▼ 查询选修了 2 号课程的同学的学号和姓名
SELECT Sno,Sname
FROM Student
WHERE EXISTS(SELECT *
FROM SC
WHERE Sno=Student.Sno AND Cno='2');
▼ 查询各门课程的课程号、课程名称以及选课学生的学号
SELECT Course.Cno,Cname,Student.Sno
FROM SC,Course,Student
WHERE Student.Sno=SC.Sno AND Course.Cno=SC.Cno;
▼ 查询选修了数据库系统原理课程的同学的学号和姓名和成绩
SELECT Student.Sno,Sname,Grade
FROM Student,Course,SC
WHERE Student.Sno=SC.Sno AND
Course.Cno=SC.Cno and Cname='数据库系统原理';
4. 嵌套查询
（1）由 In 引出的子查询
查询与“刘晨”在同一个系学习的学生：
SELECT Sno,Sname,Sdept FROM Student
WHERE Sdept IN ( SELECT Sdept FROM Student WHERE Sname= '刘晨');
（2）由比较运算符引出的子查询
找出每个学生超过他选修课程平均成绩的课程号。
SELECT Sno, Cno FROM SC x
WHERE Grade >= ( SELECT AVG(Grade)
FROM SC y
WHERE y.Sno=x.Sno);
（3）带修饰符的比较运算符引出的子查询
查询其他系中比计算机科学系所有学生年龄都小的学生姓名及年龄：
SELECT Sname,Sage FROM Student
WHERE Sage < ALL ( SELECT Sage FROM Student
WHERE Sdept= 'CS')
AND Sdept <> 'CS' ;
（4）由 EXISTS 引出的子查询：
查询所有选修了1号课程的学生姓名
SELECT Sname FROM Student
WHERE EXISTS (SELECT *
FROM SC
WHERE Sno=Student.Sno AND Cno= '1');
5. 集合查询
（1）集合并
查询计算机科学系的学生及年龄不大于19岁的学生：
SELECT * FROM Student WHERE Sdept= 'CS'
UNION
SELECT * FROM Student WHERE Sage<=19
（2）集合交
查询计算机科学系且年龄不大于 19 岁的学生：
SELECT * FROM Student WHERE Sdept='CS'
INTERSECT
SELECT * FROM Student WHERE Sage<=19
（3）集合差
查询计算机科学系且年龄大于19岁的学生
SELECT * FROM Student WHERE Sdept='CS'
EXCEPT
SELECT * FROM Student WHERE Sage <=19;
四、视图操作
建立视图并基于视图进行查询：
1. 创建视图
（1）建立学生基本信息视图
CREATE VIEW Student_VIEW(学号,姓名,性别,年龄,系,班级)
AS
SELECT Sno,Sname,Ssex,Sage,Sdept,Sclass
FROM Student;
（2）建立学生均分视图
CREATE VIEW S_G(Sno,Gavg)
AS
SELECT Sno,avg(Grade)
FROM SC GROUP BY Sno;
（3）建立选课信息视图
CREATE VIEW XK_VIEW
AS
SELECT Student.*,Course.*,Grade
FROM Student,SC,Course
WHERE Student.Sno = SC.Sno AND SC.Cno = Course.Cno;
2. 视图查询
（1）查询学生基本信息：
SELECT * FROM Student_VIEW
（2）找出每个学生超过他选修课程平均成绩的课程号：
SELECT SC.Sno,Cno,grade
FROM SC, S_G
WHERE SC.Sno = S_G.Sno and Grade >= S_G.Gavg
（3）查询每个学生的学号、姓名、选修的课程名及成绩
SELECT Sno,Sname,Cname,Grade
FROM XK_VIEW
（4）▼ 比较使用视图查询和直接从基表查询的优点
简单性。视图简化了用户对数据的理解和操作。那些被经常使用的查询可以被定义为视图，从而用户不必为以后的操作每次都指定全部的条件。
安全性。通过视图用户只能查询和修改他们所能见到的数据。数据库中的其他数据则既看不见也取不到。数据库授权命令使每个用户对数据库的检索限制到特定数据库对象，但不能授权到数据库特定行和特定的列上。通过视图，用户可以被限制在数据不同子集。
逻辑数据独立性。视图使应用程序和数据库表在一定程度上独立。如果没有视图则应用一定是建立在表上的。有了视图之后，程序可以建立在视图之上，从而程序与数据库表被视图分割开来。
四 实验遇到的问题和解决方法
1. 实验二的项目较多，花费了很长时间才全部完成。其中涉及到的有数据库语句操作，如表的创建、修改、删除，以及数据的插入、修改、删除和查询。实验课期间没有完成，之后又尝试编写出错和不会等问题。查询资料、请教同学之后顺利完成。
2. 实验虽多，却也是课本最基本知识的练习，不足为虑。实验中的多数问题是编写语言时候不细心所致，比如大小写转换，中英文标点的使用等。
五 实验心得
本次试验最大的收获就是，耐心和细心的锻炼。试验项目很多，必须专心致志和良好耐心之下，坚持完成。很高兴做到了这点，磨练了以后对待工作的态度和初步的数据库管理能力。在实验的基础之上，课本中的知识点也得到了很好的练习和巩固。
