MySQL入门与精通

MySQL，虽然功能未必很强大，但因为它的开源、广泛传播，很多人都了解到这个数据库。MySQL是一种开放源代码的关系型数据库管理系统（RDBMS），使用最常用的数据库管理语言--结构化查询语言（SQL）进行数据库管理。MySQL因为其速度、可靠性和适应性而备受关注。大多数人都认为在不需要事务化处理的情况下，MySQL是管理内容最好的选择。以下为mysql入门与精通，仅供参考。

数据库模式：
create database school2 ;

USE school2;
CREATE TABLE students
(
s_no VARCHAR(20) ,
s_name VARCHAR(20),
s_sex CHAR(2),
s_birth DATETIME,
s_address VARCHAR(30),
s_account decimal(9,2),
s_password VARCHAR(20)
);

CREATE TABLE courses
(
c_no CHAR(10),
c_name VARCHAR(30),
c_date DATETIME,
c_credit INT,
c_information VARCHAR(50)
);

CREATE TABLE enrollment
(
s_no VARCHAR(20),
c_no CHAR(10),
e_score INT,
e_date DATETIME,
e_register_state CHAR(1),
e_check_state CHAR(1),
e_test_state CHAR(1)
);

INSERT INTO students VALUES('200920001', '张三', '女', '1982-03-24', '北京市', 8200.0, '200920001');
INSERT INTO students VALUES('200920002', '李四', '男', '1976-07-23', '天津市', 3500.0, '200920002');
INSERT INTO students VALUES('200920003', '王五', '男', '1976-10-21', '天津市', 4500.0, '200920003');
INSERT INTO students VALUES('200920004', '赵六', '女', '1974-08-26', '长春市', 4000.0, '200920004');
INSERT INTO students VALUES('200920005', '钱七', '男', '1975-07-23', '天津市', 4300.0, '200920005');
INSERT INTO students VALUES('200920006', '吴八', '男', '1972-12-14', '天津市', 5500.0, '200920006');

INSERT INTO courses VALUES('22010401', '高等数学', '2009-03-01', 4, '基础课');
INSERT INTO courses VALUES('22010402', '高等代数', '2009-03-01', 4, '基础课');
INSERT INTO courses VALUES('22010403', '大学物理', '2009-03-01', 3, '基础课');
INSERT INTO courses VALUES('22010404', '计算机基础', '2009-03-01', 3, '基础课');
INSERT INTO courses VALUES('22010405', '程序设计', '2009-03-01', 2, '专业课');
INSERT INTO courses VALUES('22010406', '操作系统', '2009-03-01', 2, '专业课');
INSERT INTO courses VALUES('22010407', '数据库应用', '2009-03-01', 2, '专业课');
INSERT INTO courses VALUES('22010408', '数据结构', '2009-03-01', 3, '专业课');
INSERT INTO courses VALUES('22010409', '计算机网络', '2009-03-01', 2, '专业课');
INSERT INTO courses VALUES('22010410', '人工智能', '2009-03-01', 2, '专业课');

INSERT INTO enrollment VALUES('200920001', '22010409', 88, '2009-3-12', '1', '1', '1');
INSERT INTO enrollment VALUES('200920001', '22010410', 93, '2009-3-10', '1', '1', '1');
INSERT INTO enrollment VALUES('200920003', '22010407', 0, '2009-3-10', '0', '0', '0');
INSERT INTO enrollment VALUES('200920003', '22010409', 79, '2009-3-12', '1', '1', '1');
INSERT INTO enrollment VALUES('200920003', '22010410', 74, '2009-3-10', '1', '1', '1');
INSERT INTO enrollment VALUES('200920002', '22010409', 0, '2009-3-9', '0', '0', '0');
INSERT INTO enrollment VALUES('200920002', '22010407', 91, '2009-3-9', '1', '1', '1');
INSERT INTO enrollment VALUES('200920002', '22010410', 45, '2009-3-9', '1', '1', '1');
INSERT INTO enrollment VALUES('200920004', '22010407', 0, '2009-3-10', '1', '0', '0');
INSERT INTO enrollment VALUES('200920006', '22010406', 0, '2009-3-12', '1', '1', '0');

1查询选修了“操作系统”这门课的同学的姓名。
select students.s_name from students, courses, enrollment where students.s_no = enrollment.s_no and courses.c_no = enrollment.c_no and courses.c_name ='操作系统'
2将所有选修了"22010410"课程的学生的成绩加10分。
UPDATE enrollment SET e_score= e_score + 10 WHERE c_no = '22010410'
3查询哪些课程没有人选,要求列出课程号和课程名。

select distinct courses.c_no, courses.c_name from courses where not exists (
select * from enrollment where courses.c_no = c_no)

4查询来自“天津市”的同学所选的课程名。、

select courses.c_name from students, courses, enrollment where students.s_no = enrollment.s_no and courses.c_no = enrollment.c_no and s_address ='天津市'

5查询平均成绩高于80分的学生的姓名和他们的平均成绩。

select students.s_name, AVG(enrollment. e_score) from students, enrollment where students.s_no = enrollment.s_no group by enrollment.s_no having AVG(enrollment. e_score) > 80

6使用exists查询选修了“22010410”课程的学生号和姓名。

Select students.s_name, students.s_no from students where exists (
select * from enrollment where students.s_no = s_no and c_no = '22010410')

7查询选修了课程名称为“数据库应用”的学生号、姓名。
select students.s_name from students, courses, enrollment where students.s_no = enrollment.s_no and courses.c_no = enrollment.c_no and courses.c_name ='数据库应用'

8将students表和enrollment表作左外联接显示所有学生的信息，对于选课的同学同时显示所有选课信息。

Select * From
(
select enrollment.* , courses.c_name from enrollment, courses where enrollment.c_no = courses.c_no) as a
Left Join
students
On a.s_no = students.s_no

9查询年龄在30岁以上的选修了“计算机网络”课程的学生姓名。
select students.s_name from students, courses, enrollment where students.s_no = enrollment.s_no and courses.c_no = enrollment.c_no and students. s_birth > 30 and
courses.c_name ='计算机网络'

10查询没有选修“人工智能”这门课程的同学的姓名。
select distinct students.s_name from students, courses, enrollment where students.s_no = enrollment.s_no and courses.c_no = enrollment.c_no and courses.c_name !='人工智能'

11查询比“钱七”帐户高的而和他不是同一地址的学生姓名和年龄。

select students.s_name, students.s_birth from
(
select * from students where s_name ='钱七') as a , students where students. s_account > a. s_account and students.s_address != a.s_address

12删除修课成绩小于50分和成绩为空的学生的修课记录
DELETE FROM enrollment1 WHERE e_score< 50 or e_score = null
13使用in查询与“王五”选修至少同一种课程的学生号和课程号。

Select enrollment.s_no , enrollment.c_no from enrollment where enrollment.c_no in (
Select enrollment.c_no from students, enrollment where enrollment.s_no = students.s_no and students.s_name = '王五'
)
14查询选修了课程号为“22010410”的学生号和姓名，并以汉字标题显示。
select students.s_no , students.s_name from students, enrollment where students.s_no = enrollment.s_no and enrollment.c_no ='22010410'