Mysql使用+使用实战

PumpkinDylan 发布于2019-08-01 14:08 / 3227人阅读

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

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

事务

未待续

文章版权归作者所有，未经允许请勿转载,若此文章存在违规行为，您可以联系管理员删除。

转载请注明本文地址：https://www.ucloud.cn/yun/49422.html

NodeJs+Express+Mysql + Vuejs 项目实战 - 大纲

摘要：多一个技能多一条出路，祝你在自学道路上越走越好，掌握自己的核心技能，不只是优秀，还要成为不可替代的人 NodeJs+Express+Mysql + Vuejs 项目实战最近准备写一系列文章，全面讲述如何基于NodeJs + Express + Mysql + Vuejs 从零开发前后端完全分离项目；文笔及技术可能在某些方面欠佳，请您指正，共同学习进步前端：Vuejs全家桶后端：...

noONE 2019-08-23 14:55 评论0 收藏0
NodeJs+Express+Mysql + Vuejs 项目实战 - 大纲

摘要：多一个技能多一条出路，祝你在自学道路上越走越好，掌握自己的核心技能，不只是优秀，还要成为不可替代的人 NodeJs+Express+Mysql + Vuejs 项目实战最近准备写一系列文章，全面讲述如何基于NodeJs + Express + Mysql + Vuejs 从零开发前后端完全分离项目；文笔及技术可能在某些方面欠佳，请您指正，共同学习进步前端：Vuejs全家桶后端：...

Miracle_lihb 2019-08-01 10:25 评论0 收藏0
Docker实战指南(三)：Docker镜像

摘要：原文地址实战指南三镜像在实战指南二在中搭建服务器中，我们已经介绍了如何快速地搭建一个实用的服务器。这次我们将围绕镜像，介绍其使用方法。此外，你也可以通过访问，通过来搜索你需要的镜像。通过此实战，你应该对镜像有了更深入的体会。原文地址：Docker实战指南(三)：Docker镜像 Introduction 在Docker实战指南(二)：在Docker中搭建Nginx服务器中，我们已经介...

Donne 2019-06-28 15:24 评论0 收藏0
Python3网络爬虫实战---4、数据库的安装：MySQL、MongoDB、Redis

摘要：的安装是一个轻量级的关系型数据库，以表的形式来存储数据，本节我们来了解下它的安装方式。相关链接官方网站下载地址中文教程下的安装推荐使用安装，执行命令即可。上一篇文章网络爬虫实战解析库的安装下一篇文章网络爬虫实战存储库的安装上一篇文章：Python3网络爬虫实战---3、解析库的安装：LXML、BeautifulSoup、PyQuery、Tesserocr下一篇文章：Python3网络...

winterdawn 2019-07-31 10:34 评论0 收藏0
1.3 Mysql 安装与使用-基础配置-NodeJs+Express+Mysql实战

摘要：下载及安装下载及安装中并没有集成，需要手动安装。到这里已经配置完成，并且可以运行。相关命令设置为了更加方便使用，最好再设置一下系统环境变量，也就是让这个命令在任何路径都可以直接启动不需要输入一长串的准确路径。下载 MySQL 及安装 1、下载 MySQL 及安装 OS X Mavericks 中并没有集成 Mysql，需要手动安装。官网下载： http://dev.mysql.co...

王岩威 2019-08-23 17:15 评论0 收藏0
python3.6+scrapy+mysql 爬虫实战

摘要：最近闲着，把之前写的小爬虫分享一下，才疏学浅，仅当参考。项目地址爬虫实战最近闲着，把之前写的小爬虫分享一下，才疏学浅，仅当参考。 [介绍文档] python版本：python3.6 scrapy: 1.5.0 需要安装pymysql包支持访问mysql数据库可以使用pip安装： pip install pymysql 重要提示 *或者按照下述方法执行...

instein 2019-07-31 18:02 评论0 收藏0