mysql++使用_Mysql使用+使用实战

新建表student(学生表) 、course(课程表)、teacher(教师表)、score(成绩表)

CREATE TABLE student

(

id int NOT NULL AUTO_INCREMENT ,//自增

name nvarchar(32),

age int,

sex nvarchar(8),

PRIMARY KEY (`id`)//主键

) ;

CREATE TABLE course

(

id int NOT NULL AUTO_INCREMENT ,

name nvarchar(32),

teacherId int ,

PRIMARY KEY (`id`)

);

CREATE TABLE teacher

(

id int NOT NULL AUTO_INCREMENT ,

name nvarchar(16) ,

PRIMARY KEY (`id`)

);

CREATE TABLE score

(

id int NOT NULL AUTO_INCREMENT ,

studentId int,

courseId int ,

score int ,

PRIMARY KEY (`id`)

)

查看当前数据库中的表，SHOW TABLES;

插入数据

INSERT INTO (, , , …)VALUES (, , , …);

INSERT INTO student(name,age,sex) VALUES ("刘一",18,"男"),("蔡二",19,"女"),("张三",20,"男"),("李四",17,"女"),("王五",21,"男"),("赵六",22,"女");

INSERT INTO teacher(name) VALUES("叶平"),("贺高"),("杨艳"),("周磊");

INSERT INTO course(name,teacherId) VALUES ("语文",1),("数学",2),("英语",3),("物理",4);

INSERT INTO score(studentId,courseId,score) VALUES (1,1,56),(1,2,78),(1,3,67),(1,4,58),(2,1,79),(2,2,81),(2,3,92),(2,4,68),(3,1,91),(3,2,47),(3,3,88),

(3,4,56),(4,2,88),(4,3,90),(4,4,93),(5,1,46),(5,3,78),(5,4,53),(6,1,35),(6,2,68),(6,4,71)

更新数据

UPDATE SET = , = , ...WHERE ;

清空表

DELETE FROM WHERE ;

删除表

DROP TABLE ;

实战中学习

查询课程1比课程2成绩高的所有学生的学号；

SELECT s1.studentId FROM (SELECT studentId,score from score WHERE courseId = 1) s1,

(SELECT studentId,score from score WHERE courseId = 2) s2

WHERE s1.score>s2.score and s1.studentId = s2.studentId

查询平均成绩大于60分的同学的学号和平均成绩

SELECT studentId,AVG(score) FROM score GROUP BY studentId HAVING AVG(score)>60

GROUP BY语句可以分组结果，常用于COUNT、MAX、MIN、SUM、AVG等聚合函数(aggregate functions)

WHERE和HAVING的区别

“Where” 是一个约束声明，使用Where来约束来之数据库的数据，Where是在结果返回之前起作用的，且Where中不能使用聚合函数。

“Having”是一个过滤声明，是在查询返回结果集以后对查询结果进行的过滤操作，在Having中可以使用聚合函数。

查询所有同学的学号、姓名、选课数、总成绩；

SELECT student.id,name,COUNT(score.courseId),SUM(score) FROM

student LEFT JOIN score

on student.id = score.studentId

GROUP BY student.id;

三种连接方式LEFT JOIN、RIGHT JOIN、INNER JOIN

A left join B的连接的记录数与A表的记录数同

A right join B的连接的记录数与B表的记录数同

查询姓“李”的老师的个数；

SELECT COUNT(DISTINCT(name)) FROM teacher WHERE name like "李%"

DISTINCT过滤掉了重复的值

查询没学过“叶平”老师课的同学的学号、姓名；

SELECT id,name FROM student WHERE id not in (

SELECT DISTINCT(score.studentId) FROM score,course,teacher WHERE

teacher.`name` = "叶平" and teacher.id = course.teacherId and score.courseId = course.id)

逻辑思路：先查询出叶平老师的数据，根据id拿到对应的课程id，在根据课程学生关系表得到学过叶平老师的所有student.id，在用not in进行筛选

查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名；

SELECT st.id,st.name FROM student st,score s WHERE s.courseId = 1 and st.id = s.studentId and

EXISTS

(SELECT * FROM score s1 WHERE s1.courseId =2 and s1.studentId = s.studentId)

执行顺序：SELECT st.id,st.name FROM student st,score s WHERE s.courseId = 1 and st.id = s.studentId

再执行EXISTS后面的语句

如果成立则返回true，不成立则返回false。如果返回的是true的话，则该行结果保留，如果返回的是false的话，则删除该行，最后将得到的结果返回。

查询学过“叶平”老师所教的所有课的同学的学号、姓名；

select id,name from student

where id in (

select score.studentId from score ,course ,teacher where score.courseId=course.id and

teacher.id=course.teacherId and teacher.name='叶平'

group by

score.studentId having count(score.courseId)

=

(select count(course.id) from course,teacher where teacher.id=course.teacherId and teacher.name='叶平'));

思路逻辑：和第5类似，使用group by根据studentId进行分组

按平均成绩从高到低显示所有学生的“语文”、“数学”、“英语”三门的课程成绩，按如下形式显示： 学生ID,,数据库,企业管理,英语,有效课程数,有效平均分

SELECT studentId as 学生ID

,(SELECT score FROM score WHERE score.id=s.id AND score.courseId='001') AS 语文

,(SELECT score FROM score WHERE score.id=s.id AND score.courseId='002') AS 数学

,(SELECT score FROM score WHERE score.id=s.id AND score.courseId='003') AS 英语

,COUNT(*) AS 有效课程数, AVG(s.score) AS 平均成绩

FROM score AS s

GROUP BY studentId

ORDER BY avg(-s.score)

性能优化

当只要一行数据时使用limit 1

使用limit 1，Mysql数据库引擎找到一条结果就会停止搜索，而不需要继续查询下一条是否符合标准直到所有记录查询完毕

选择正确的数据库引擎

Mysql有两个引擎，MyISAM和InnoDB，较常用InnoDB

MyISAM，适用一些大量的查询，对大量的写不是很友好，甚至update一个字段都会把整个表锁起来，可是对select count(*)这类的操作非常快

用not exists 代替 not in

not exists用到了连接，能够发挥已经建好索引的作用，not in 不能使用索引。not in是最慢的方式，要同每一条记录比较，在数据量大的操作不建议使用。

事务

未待续