Mysql使用使用实战

234次阅读

共计 3583 个字符，预计需要花费 9 分钟才能阅读完成。

新建表 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 是最慢的方式，要同每一条记录比较，在数据量大的操作不建议使用。

事务

未待续

正文完
mysql
发表至：无分类
2019-07-29
 0