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 <表名> (<列名1>, <列名2>, <列名3>, …)VALUES (<值1>, <值2>, <值3>, …);

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 <列名1> = <值1>, <列名2> = <值2>, ...WHERE <条件>;

清空表

DELETE FROM <表名> WHERE <条件>;

删除表

DROP TABLE <表名>;

实战中学习

1. 查询课程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

   

2. 查询平均成绩大于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中可以使用聚合函数。

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

   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表的记录数同    
   
   

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

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

   DISTINCT过滤掉了重复的值

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

   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进行筛选

6. 查询课程编号“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的话，则删除该行，最后将得到的结果返回。

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

   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进行分组

8. 按平均成绩从高到低显示所有学生的“语文”、“数学”、“英语”三门的课程成绩，按如下形式显示： 学生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) 

   

性能优化

1. 当只要一行数据时使用limit 1
   使用limit 1，Mysql数据库引擎找到一条结果就会停止搜索，而不需要继续查询下一条是否符合标准直到所有记录查询完毕
2. 选择正确的数据库引擎
   Mysql有两个引擎，MyISAM和InnoDB，较常用InnoDB
   MyISAM，适用一些大量的查询，对大量的写不是很友好，甚至update一个字段都会把整个表锁起来，可是对select count(*)这类的操作非常快
3. 用not exists 代替 not in
   not exists用到了连接，能够发挥已经建好索引的作用，not in 不能使用索引。not in是最慢的方式，要同每一条记录比较，在数据量大的操作不建议使用。

事务

未待续