Mysql Command

为什么80%的码农都做不了架构师？>>>   

很久没有使用过mysql，慢慢找回吧，持续更新。

基础知识

数据库管理系统（database management system, DBMS）

由一个相互关联的数据集合（数据库）和一组访问这些数据的程序组成，它是位于用户和计算机操作系统之间的数据管理软件，主要功能：
1、数据定义（提供数据定义语言，data definition language, DDL）；
2、数据操纵（提供数据操纵语言，data manipulation language, DML）；
3、事务管理和运行管理；
4、数据存储和查询处理；
5、数据库的建立和维护；
6、其他功能，如DBMS于其他软件通信，异构数据库之间数据转换和互操作。

数据库、数据库模式和数据库系统

数据库database，DB是持久存储在计算机中有组织的，可共享的（目的）大量数据的集合。 数据库中的数据按一定的数据模型组织、描述和储存，可以被各种用户共享，具有较小的冗余度、较高的数据独立性，并易于扩展。
数据库模式是在数据库中，使用数据模型对数据建模，所产生的设计结果。
1、 数据库模式描述数据库的数据结构（型），具有相对稳定性。
2、 特定时刻数据库中的数据为数据库的实例（值）。数据库的值是随时间而不断变化的。
数据库系统由数据库、DBMS（及其开发工具）、应用系统和数据库管理员（DBA）组成。

数据管理基本操作

操作主体分为两类：数据定义和数据操纵。

• 数据定义（使用DDL）
• 数据操纵（使用DML）
  • 数据查询
  • 数据更新（几乎所有的更新操作都涉及隐含的查询）
    • 插入
    • 删除
    • 修改（删除+插入）

实际应用对数据操作的要求

实际应用是在并发、充满故障和错误的环境下运行的。
1、 并发访问：允许多个用户同时对数据库中的数据进行访问。
2、 面临故障：各种故障都可能发生，必须确保数据在任何情况下都不会丢失或被破坏。
3、 数据安全：防止用户对数据进行未经授权的访问，包括两部分，不允许登录，或不允许某种操作。
4、 数据完整性：防止不符合语义的数据进行数据库。
5、 数据一致性：防止数据库进入不一致状态，有些操作必须作为一个整体（原子性），要么都做，要么都不做。

数据模型

数据模型是一种形式机制，用于数据建模，描述数据、数据之间的联系、数据的语义、数据上的操作和数据的完整性约束条件。

• 数据建模分为两个层次：概念模型和数据模型。
  • 1、 概念模型，也称信息模型，按用户的观点来对现实世界进行数据建模。
  • 2、 数据模型，按计算机系统的观点对信息世界进行数据建模。
• 数据模型三要素：
  • 1、 数据结构：描述数据库中的对象和对象之间的联系，是对系统静态特性的描述。
  • 2、 数据操作：定义数据库中各种数据对象的实例上允许执行的操作和操作规则，是对系统动态特性的描述。
  • 3、 完整性约束：数据的完整性约束是一组条件，用以限定符合数据模型的数据库状态和状态的变化，保证数据的正确、有效和相容。
    • 3.1 通用完整性约束条件：该模型下所有数据库都必须满足的约束条件，如在关系模型中，所有关系都必须满足实体完整性和参照完整性，该条件由数据模型明确规定。
    • 3.2 专用完整性约束条件：负责具体应用，这取决于实际问题的语义，如库存必须为非负整数。该条件由DBMS提供定义和检查机制。

关系模型

关系模型只有一种数据结构——关系。现实世界中的对象和对象之间的联系都用关系表示，关系是元组的集合。从用户角度看，关系是一张二维表。

• 在E-R模型中，实体集合联系集都用关系表示。
  • 1、 关系表示实体集，则表中每一行代表一个实体，表的每一列代表实体的一个属性。
  • 2、 关系表示联系集，则表中每一行代表实体之间的一个联系，而表的每一列表示参与联系的实体的码属性和联系的属性。
• 关系模型要求关系必须是规范化的：关系的每个属性只能取原子值（不可再分的值），即，表中不能有子表。
• 关系模型中，定义数据操作的方法有两种：关系代数和关系演算。
  • 1、 关系代数显式地定义了一些关系运算，如并、交、差、笛卡尔积、选择、投影和连接等。关系运算的运算对象和运算结果都是关系，所以上一步的运算结果可以作为下一步的运算对象。
    • 1.1 查询用关系代数表达式表示；
    • 1.2 更新用关系代数表达式向关系向量的赋值表示。
  • 2、 关系演算并不显式定义基本运算，而用一个逻辑公式表示查询结果必须满足的条件。
    • 2.1 元组关系演算：公式中的变元为元组变量；
    • 2.2 域关系演算：公式中的变元为域变量。

数据库系统的三级模式结构

数据库系统内部广泛采用三级模式和两级映像结构。数据库系统的三级模式提供了三个层次的数据抽象，这样可以隐蔽数据存储细节，隐蔽系统内部的复杂性，简化系统的用户界面和支持数据独立性。

• 1、 外模式（external schema）也称子模式或用户模式。
  • 外模式介于模式与应用之间，是特定数据库用户的数据视图，是与某一具体应用相关的数据局部逻辑结构的描述。
  • 外模式面向具体的应用程序，反映不同的用户的应用需求和看待数据的方式。定义在模式之上，独立于存储模式和存储设备。通常，外模式是模式的子集，但是，在外模式中，同一数据对象的结构、类型、长度等都可以不同于模式。
  • 一个数据库可以用多个外模式，一个外模式可以被多个应用程序使用，但一个应用程序只能用一个外模式，理想情况下，所有应用都建立在外模式上，但实际上，DBMS也允许应用程序直接访问模式。
  • 外模式与授权配合，限制用户只能访问所对应的外模式中的数据，可以一定程度上保证数据库安全性；外模式与外模式-模式映像配合，可以实现一定程度的数据的逻辑独立性。
  • 外模式使用DBMS中的子模式定义语言定义。
• 2、 模式（schema）也称逻辑模式。
  • 模式是数据库中全体数据的总体逻辑结构描述，是所有用户的公共数据视图。
  • 一个数据库只有一个模式。模式处于数据库系统模式结构的中间层，与数据的无力存储细节和硬件环境无关，与具体的应用程序、开发工具及高级程序设计语言无关。
  • 设计数据库结构时应首先确定数据库的模式，模式的定义不仅包括数据的逻辑结构（数据项的名字、类型、取值范围等），还包括数据之间的关系、数据有关的安全性和完整性要求。
• 3、 内模式（internal schema）也称存储模式或物理模式。
  • 内模式是数据物理结构和存储方式的描述，定义数据在数据库内部的表示方式。如：文件记录的存储方式（顺序存储、B树结构存储或按hash方法存储）、索引的组织方式、数据是否压缩存储、数据是否加密和记录是否跨页等。
  • 一个数据库只有一个内模式。它依赖于全局逻辑结构，独立于外模式（数据库的用户视图）和具体的存储设备。
  • 内模式到物理存储器的映射可以由操作系统实现，或DBMS实现。

数据库系统的两级映像

两级映像负责实现三层数据抽象之间的转换，分别是：外模式-模式映像映像和模式-内模式映像。

• 1、 外模式-模式映像定义外模式与模式之间的对应关系。
  • 每一个外模式都有一个对应的外模式-模式映像，建立外模式中的数据对象和模式中的数据对象之间的对应关系。
  • 该映像可以保证外模式的相对稳定性。模式改变时，DBA只需要修改相关的外模式-模式映像，不需要更改外模式，从而为数据的逻辑独立性提供保证。
  • 通常，该映像定义包含在每个外模式的定义中。
• 2、 模式-内模式映像定义数据全局逻辑结构与存储结构之间的对应关系。如：说明逻辑记录和字段在内部是如何表示的。
  • 该映像是唯一的，一个数据库也只有一个模式和一个内模式。
  • 该模式可以保证模式的相对稳定性，从而保证外模式的相对稳定性。当内模式（数据的存储结构）发生改变时（如选用另一种存储结构），DBA可以修改该映像，使得模式保持不变，为数据的物理独立性提供保证。
  • 通常，该映像定义包含在模式的定义中。

数据独立性

数据独立性即数据与应用程序相互独立，分数据的逻辑独立性和物理独立性两种。数据的独立性靠三级模式和两级映像实现。数据独立性使得数据的定义和描述可以从应用程序中分离出去，减少了数据逻辑结构和物理结构的变化对程序的影响。
1、 数据的逻辑独立性指应用程序与数据库的逻辑结构之间的相互独立性。当数据的逻辑结构改变时，通过修改外模式-模式映像，保持外模式不变，从而建立在外模式上的应用程序也可以不变。
2、 数据的物理独立性指应用程序与存储在磁盘上的数据库中数据之间的相互独立性。当数据的物理存储结构改变时，通过修改模式-内模式映像，保持模式不变，那么外模式也不变，应用程序也可以不变。

SQL基础

数据查询

• select的一般形式

select [all | distinct] <选择序列>
    from <表引用>, ..., <表引用>
    [where <查询条件>]
    [group by <分组列>, ..., <分组列>[having <分组选择条件>]]
    [order by <排序列> [asc | desc], ..., <排序列> [asc | desc]]

- select子句  
    select字句相当于关系代数的投影运算（广义投影），用来列出查询结果表的诸列。

- from子句  
    from子句相当于关系代数的笛卡尔积运算，用来列出查询需要扫描的基本表或导出表。

- where子句  
    where子句相当于关系代数中的选择运算，<查询条件>是作用于from子句中的表和视图的选择谓词。

- select执行  
    首先求from子句指定的基本表或导出表的笛卡尔积，然后根据where子句的查询条件从中选择满足查询条件的元组，最后投影到select子句的结果列上，得到查询的答案。

• 带where子句的查询
  一般来说，查询条件是一个布尔表达式。布尔表达式 = ( 基本布尔表达式 ) <NOT|AND|OR> ( 基本布尔表达式 )， 基本布尔表达式：逻辑常量（true和false）、比较表达式、between表达式、in表达式、like表达式、null表达式等。 对于表别名：可以将表别名看做元组变量，也可以将表名看做该表的元组变量。

  • 比较表达式：<值表达式1><比较运算符><值表达式2>
    其中比较运算符：<、 <=、 >、 >=、 <>或!=
    eg：

    -- 查询职称（title）为讲师的全体教师的姓名和性别
    select tname, sex from teachers where title = '讲师';
    -- 查询考试成绩不及格的学生的学号
    select distinct sno from sc where grade < 60; 
    

  • between表达式：<值表达式>[not] between <下界> and <上界>
    判定一个给定的值是否在给定的闭区间，其中<值表达式>、<下界>和<上界>都是可求值得表达式，其值是序数类型。
    eg:

    -- 查询出生年份在1987-1990年的学生的姓名和专业。
    select sname, speciality from students where year(birthday) between 1987 and 1990;
    -- 上面的between-and可以用and连接多重比较表示
    select sname, speciality from students where year(birthday) >= 1987 and year(birthday) <= 1990;
    -- 查询出生年份不在1987-1990年的学生的姓名和专业。
    select sname, speciality from students where year(birthday) not between 1987 and 1990;
    -- 上面的not between-and可以用or连接多重比较表示
    select sname, speciality from students where year(birthday) < 1987 or year(birthday) > 1990;
    

  • in表达式：<值表达式> [not] in (<值表达式列表>) 或 <元组> | [not] in <子查询> 判定一个给定的元素是否存在于给定的集合中。
    eg:

    -- 查询计算机科学与技术和软件工程专业的学生的学号和姓名。
    select sno, sname from students where speciality in ('计算机科学与技术','软件工程');
    -- 查询既不是计算机科学与技术，也不是软件工程专业的学生的学号和姓名。
    select sno, sname from students where speciality not in ('计算机科学与技术', '软件工程');
    -- 查询和王丽丽在同一个专业学习的女同学的学号和姓名。
    select sno, sname from students where sex = '女' and speciality in
        (select speciality from students where sname = '王丽丽');
    
    

  • like表达式：<匹配值> [not] like <模式> [escape '<换码字符>'] 可以进行模糊查询，其中<匹配值>和<模式>都是字符串表达式，它们的值是可比较的。<模式>中允许使用通配符：'_'匹配任意单个字符；'%'匹配零个或多个任意字符。
    eg:

    -- 查询所有以“数据”开头的课程名。
    select cname from courses where cname like '数据%';
    -- 查询姓李并且姓名只有两个汉字的学生的学号和姓名。
    select sno, sname from students where sname like '李__'; -- 一个汉字占两个字符位置
    -- 查询以C_打头的课程的详细信息。
    select * from courses where cname like 'C\_%' escape '\'; -- 'C_'中的'_'需要转义
    

  • null表达式：<值表达式>|<子查询> is [not] null
    sql 允许元组在某些属性上取空值（null），空值代表未知的值，不能与其他值进行比较。
    eg:

    -- 查询成绩为空的学生的学号和课程号。
    select sno, cno from sc where grade is null;
    
    

• 排序和分组

  • 将查询结果排序：order by <排序列> [asc | desc] {, <排序列> [asc | desc]}
    eg:

  -- 查询每位学生的每门课程的成绩，并将查询结果按课程号升序、成绩降序排列。
  select * from sc order by cno asc, grade desc;
  

  • 聚集函数：<聚集函数> ([all | distinct] <值表达式>)
    <聚集函数>可以是count（计数）、sum（和）、avg（平均值）、min（最小值）和max（最大值），聚集函数不允许复合，不允许形如max(avg(...))等写法。
    eg:

  -- 查询CS302课程成绩最低分、平均分和最高分。
  select min(grade), avg(grade), max(grade) from sc where cno = 'CS302';
  

  • 分组： group by <分组列> {,<分组列>} [having <分组选择条件>]
    可选的having子句用来过滤掉不满足<分组选择条件>的分组，缺省时等价于having true，同时<分组选择条件>类似where子句中的查询条件，但允许有聚集函数。对于带group by子句的select语句，select子句中的结果列必须是group by子句中的<分组列>或聚集函数，也就是说分组中要把select中的所有内容（除聚集函数）写到group by中去。
    eg:

  -- 查询每个学生的平均成绩，并输出平均成绩大于85的学生学号和平均成绩。
  select sno, avg(grade) from sc group by sno having avg(grade) > 85;
  

  • 连接查询：当from子句中包含多个表时，相当于求这些表的笛卡尔积。
    当某些属性同时属于多个表时，需要属性前加表名前缀。
    eg:

  -- 查询每个学生的平均成绩，并输出平均成绩大于85的学生学号、姓名和平均成绩。
  select students.sno, sname, avg(grade) 
  from sc, students where students.sno = sc.sno 
  group by students.sno, sname having avg(grade) > 85;
  
  -- 查询和王丽丽出生年月相同的学生的姓名。-- 自连接
  select s2.sname from students s1, students s2 
  where s1.birthday = s2.birthday and s1.sname = '王丽丽' and s2.sname <> '王丽丽';
  
  

• 嵌套查询 最常见的嵌套是将子查询嵌套在where子句或having短语的条件中。将一个查询嵌套在另一个查询中的查询称为嵌套查询，并将前者称为子查询（内层查询），后者称为父查询（外层查询）。子查询中不能使用order by子句。嵌套查询分为两类：1、不相关子查询的子查询条件不依赖与父查询；2、相关子查询的子查询的查询条件依赖于父查询。可以引进子查询的表达式包括in表达式、存在表达式、null表达式和唯一表达式等。

  • in引出的子查询：<元组>[not] in <子查询>
    上面已经提到该点，这里不再赘述。

  • 集合比较引出的子查询：<值表达式><比较运算符> all | some | any <子查询>
    some和any含义相同，因易与英语中的any混淆，所以推荐使用some。当<子查询>的结果为单个值时，all和some可以省略。设v是<值表达式>的值，S是<子查询>的查询结果，它是元祖（值）的集合。v<值表达式>all S 为真，当且仅当v与S中的每个值都满足比较关系；v<值表达式>some S为真，当且仅当v与S中的某个值满足比较关系。注意：=some等价于in，但是<>some并不等价于not in，具体见下表。
    eg:

  -- 查询比软件工程专业所有学生年龄都小的其他专业的学生的学号、姓名、专业和出生日期。
  select sno, sname, speciality, birthday from students 
  where speciality <> '软件工程' and 
  birthday > all (select birthday from students where speciality = '软件工程');
  -- 用聚集函数实现
  select sno, sname, speciality, birthday from students where speciality <> '软件工程' and birthday > (select max(birthday) from students where speciality = '软件工程'); 
  
  -- 查询平均成绩最高的课程的课程号和平均成绩
  select cno, avg(grade) from sc group by cno 
  having 
      avg(grade) >= all(select avg(grade) from sc group by con);
  

  • 存在量词引出的子查询：exists <子查询>
    eg:

  -- 查询至少选修了学号为200515122的学生选修的全部课程的学生的学号和姓名
  -- 也就是不存在这样一条选课记录sc1，学号为200515122的学生选了，而s没有选
  select sno, sname from students s where not exists
      (select * from sc sc1 where sc1.sno = '200515122' and 
          not exists 
          (select * from sc sc2 where sc2.sno = s.sno and sc2.cno = sc1.cno));
  

  • 检测子查询结果中的重复元组：unique <子查询>
    SQL允许使用该形式的唯一表达式检查子查询结果是否包含重复元组：该表达式为真，当且仅当<子查询>的结果中不存在两个完全相同的元组。
    eg:

  -- 查询只讲授一门课程的教师的姓名和职称。
  select tname, title from teaches t where unique
      (select tno from teaches tc where t.tno = tc.tno);
  

  • 集合运算：<元组集表达式> <集合运算符> [all] <元组集表达式>
    SQL也支持传统的集合运算，包括：并（union）、交（intersect）和差（except）。与select语句不同，集合运算会自动删除结果中的重复元组，可选的all可以用来保留运算结果中的重复元组。与关系代数一样，SQL的集合运算要求参与运算的元组集的列数必须相同，对应列的数据类型也必须相同。
    eg:

  -- 查询选修了CS301号课程或选修了CS306号课程的学生的学号。
  select sno from sc where cno = 'CS301'
  union
  select sno from sc where cno = 'CS306';
  -- 与如下等价
  select distinct sno from sc where cno = 'CS301' or cno = 'CS306';
  
  -- 查询既选修了CS301号课程，又选修了CS306号课程的学生的学号。
  select sno from sc where cno = 'CS301'
  intersect
  select sno from sc where cno = 'CS306';
  -- 与如下等价
  select distinct sno from sc where cno = 'CS301' and 
      sno in (select sno from sc where cno = 'CS306');
  
  -- 查询选修了CS301号课程，但未选修CS306号课程的学生的学号。
  select sno from sc where cno = 'CS301'
  except
  select sno from sc where cno = 'CS306';
  -- 与如下等价
  select distinct sno from sc where cno = 'CS301' and 
      sno not in (select sno from sc where cno = 'CS306');
  
  

some、all谓词与聚集函数及in谓词的等价转换关系

| | = | != 或<> | < | <= | > | >= | |:-|:-|:-|:-|:-|:-|:-| | some | in | -- | < max | <= max | > min | >= min | | all | -- | not in | < min | <= min | > max | >= max |

SQL练习

查询训练

-- 对所有学生的成绩按升序排列，显示学生学号、成绩、课程号
select stu.sno, sc1.grade, sc1.cno from students stu, sc sc1 where stu.sno = sc1.sno order by grade asc;

-- 对所有学生的成绩按升序排列，并且课程号按降序排列，显示学号、成绩、课程号
select stu.sno, sc1.grade, sc1.cno from students stu, sc sc1 where stu.sno = sc1.sno order by sc1.grade asc, sc1.cno desc;

-- 对所有学生按学号和课程号升序，并且成绩按降序
select * from students stu, sc sc1 where stu.sno = sc1.sno order by stu.sno asc, sc1.cno asc, sc1.grade desc;

-- 显示所有课程的平均成绩，要求显示课程号、平均成绩
select avg(grade), cno from sc group by cno;
-- select avg(grade), cno from sc; -- group by的含义

-- 显示所有学生的选课数目
select count(grade), sno from sc group by sno;

-- 显示所有学生的选课数目和课程的平均成绩，并对平均成绩升序排列
select count(grade), sno, avg(grade) from sc group by sno order by avg(grade) asc;

-- 显示所有学生的选课数目和课程的平均成绩，并且平均成绩大于80分，并对平均成绩升序排列
select count(grade), sno, avg(grade) from sc group by sno having avg(grade) > 80 order by avg(grade) asc;

-- 查询选修课程号为5001并且成绩低于学生号为20071201的所有学生的成绩
select * from sc where cno = '5001' and grade < (select grade from sc where sno = '20071201' and cno = '5001');

 -- 逐步分解问题：首先写子查询：查询学号为20071201，课程号为5001的成绩
 select grade from sc where sno = '20071201' and cno = '5001'
 -- 然后查询课程号为5001的，成绩低于子查询的
 select * from sc where cno = '5001' and grade < 子查询
 -- 组合：
 select * from sc where cno = '5001' and grade < (select grade from sc where sno = '20071201' and cno = '5001');

-- 查询选修了全部课程的学生的学号和姓名
select sno, sname from students s where not exists
    (select * from courses c 
        where not exists
            (select * from sc where sc.sno = s.sno and sc.cno = c.cno));
-- 理解：不存在这样一门课c，学生s没有选修
-- select sno, sname from students s; 第一句的students中的第一条记录拿到下面去执行，如果exists的结果为true，则显示出来，也就是说记录一条一条执行，相当于for循环中，每一个i就是一条记录

-- 查询女生选修的课程的最高分、学生号
select cno, max(grade) from sc where sno in (select sno from students where sex = '女') group by cno order by cno;

查询测试

-- students 学生表（sid：学号；sname：学生姓名；sage：学生年龄；ssex：学生性别）
-- courses 课程表（cid：课程编号；cname：课程名字；tid：教师编号）
-- sc 成绩表（sid：学号；cid：课程编号；score：成绩）
-- teachers 教师表（tid：教师编号；tname：教师名字）

-- 问题：
-- 1. 查询“001”课程比“002”课程成绩高的所有学生的学生学号；
-- 2. 查询平均成绩大于60分的同学的学号和平均成绩；
-- 3. 查询所有同学的学号、姓名、选修数、总成绩；
-- 4. 查询姓“李”的老师的个数；
-- 5. 查询没学过“叶平”老师课的同学的学号、姓名；
-- 6. 查询学过“001”并且也学过编号“002”课程的同学的学号、姓名；
-- 7. 查询所有课程成绩小于60分的同学的学号、姓名；
-- 8. 查询没有学全所有课的同学的学号、姓名；
-- 9. 查询各科成绩最高和最低分：如如下形式显示：课程ID，最高分、最低分
-- 10. 查询每门课程被选修的学生数；
-- 11. 查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列。

-- 1
select * from students c where  
(select score from sc a where cid = '001' and c.sid = a.sid)  
>  
(select score from sc b where cid = '002' and c.sid = b.sid)

-- 2
select sid, avg(score) from sc group by sid having avg(score) > 60

-- 3
select a.sid, a.sname, count(b.score), sum(b.score) from students a, sc b where a.sid = b.sid group by a.sid

-- 4
select count(*) from teachers where tname like '李%'

-- 5
select sid, sname from students a
    where not exists 
    (select * from sc b where b.sid = a.sid and b.cid in
        (select cid from courses where tid in
            (select tid from teachers where tname='叶平')))

-- 6
select sid from sc where cid in ('001', '002') group by sid having count(*) = 2

-- 7 
select sid from sc group by sid having max(score) < 60

-- 8
select a.sid, sname from student a, sc b where a.sid = b.sid group by a.sid, sname having count(b.score) < (select count(*) from courses)

-- 9 
select cid, max(score), min(score) from sc group by cid

-- 10
select cid, count(*) from sc group by cid

-- 11
select cid, avg(score) from sc group by cid order by avg(score) asc, cid desc

报表相关

一些说明：

• 以下数据及题目来源于《本科数据库大作业》，使用数据库：SQLite，在这里主要关注解决的思路。
• 为了方便说明，变量使用如下形式：${}
• 表结构及完成之后的说明文档见附件。大作业要求 大作业使用手册

-- 1. 职位名称，以表格得形式列出该职位下的所有员工。
select empno, ename, job, mgr, strftime('%Y-%m-%d', hiredate) hiredate, sal, comm, deptno from emp where job = ${job} order by empno asc;

-- 2. 薪水统计：输入一个百分数X%，以表格形式列出佣金（奖金）高于薪水（基本工资+奖金）X%的所有员工。
select empno, ename, job, mgr, strftime('%Y-%m-%d', hiredate) hiredate, sal, comm, deptno from emp group by empno having comm > sum(comm + sal) * 0.01 * ${num} order by empno asc;

-- 3. 经理与办事员：输入两个部门编号（假设为A和B部门），以表格形式列A部门中所有经理（MANAGER)和B部门中所有办事员（CLERK)的详细资料。
select empno, ename, job, mgr, strftime('%Y-%m-%d', hiredate) hiredate, sal, comm, deptno from emp where job = 'MANAGER' and deptno = ${deptno_man};

select empno, ename, job, mgr, strftime('%Y-%m-%d', hiredate) hiredate, sal, comm, deptno from emp where job = 'CLERK' and deptno = ${deptno_clerk};

-- 4. 特殊员工：以表格形式列出，即不是经理又不是办事员但其薪金（基本工资+奖金）大于或等于2000的所有雇员的详细资料。
select empno, ename, job, mgr, strftime('%Y-%m-%d', hiredate) hiredate, sal, comm, deptno from emp group by empno, ename, job, mgr, hiredate, sal, comm, deptno having sum(comm + sal) >= 2000 and job not in ('CLERK', 'MANAGER') order by deptno asc;

-- 5. 职位统计：以表格形式统计所有职位的员工数。
select job, count(empno) num from emp group by job;

-- 6. 统计老员工：输入一个数字X，列出工龄超过X年的雇员信息。
select empno, ename, job, mgr, strftime('%Y-%m-%d', hiredate) hiredate, sal, comm, deptno from emp group by empno, ename, job, mgr, hiredate, sal, comm, deptno having (date('now') - hiredate) > ${num} order by deptno asc;

-- 7. 入职日期：输入一个数字X，列出入职日期为每月倒数第X天的员工。(mysql不兼容该语法)
select empno, ename, job, mgr, strftime('%Y-%m-%d', hiredate) hiredate, sal, comm, deptno from emp where date(hiredate, 'start of month', '+1 month', '-${num} day') = hiredate order by deptno asc;

-- 8. 姓名统计：输入一个数字X，列出姓名长度为X的所有员工，并按姓名升序、入职时间降序排序。
select empno, job, mgr, strftime('%Y-%m-%d', hiredate) hiredate, sal, comm, deptno from emp where length(ename) > ${num} order by ename asc, hiredate desc;

-- 9. 员工工薪详细：列出所有员工的姓名、工资和工资级别。
select ename, sal, grade, losal, hisal from emp, salgrade where sal >= losal and sal <= hisal order by grade asc;

-- 10. 同部门员工：输入一个员工姓名，列出与该员工同部门的所有员工。
select empno, ename, job, mgr, strftime('%Y-%m-%d', hiredate) hiredate, sal, comm, deptno from emp where deptno in (select deptno from emp where ename='${name}') order by deptno asc;

-- 11. 高薪员工：列出高于自己部门平均工薪的员工，并按薪水降序排列。
select empno, ename, job, mgr, strftime('%Y-%m-%d', hiredate) hiredate, sal, comm, deptno from emp a where a.sal > (select avg(b.sal) from emp b where a.deptno = b.deptno) order by sal desc;

-- 12. 低薪员工：输入一个部门编号X，列出比X部门所有员工工薪都低的员工。
select empno, ename, job, mgr, strftime('%Y-%m-%d', hiredate) hiredate, sal, comm, deptno from emp where sal < (select min(sal) from emp where deptno = ${num}) order by deptno asc;

-- 13. 高薪部门：显示平均工薪最高的部门，并显示平均工资，平均工薪保留两位小数。
select deptno, round(max(avg_sal)) sal, loc, dname from (select avg(sal) avg_sal, b.deptno deptno, b.dname dname, b.loc loc from emp a, dept b where a.deptno=b.deptno group by b.deptno, b.dname, b.loc);

-- 14. 工薪级别：输入一个部门编号X，列出X部门所有员工的工薪级别。
select empno, ename, sal, comm, deptno, grade, losal, hisal from salgrade a, emp b where b.sal >= a.losal and b.sal <= a.hisal and b.deptno in (${num});

-- 15. 部门员工统计：列出所有部门，以及该部门下所有雇员的姓名和工作。
select ename, job, dname, loc, b.deptno deptno from dept b left join emp a on a.detpno = b.deptno order by b.deptno;

常用命令

# 连接本地mysql
mysql -u<your username> -p<your password>

# 导入数据库
use database_name
source <database_name.sql>;

#---------主从数据库相关-----------#
# 授权用户
$ GRANT REPLICATION SLAVE ON *.* to '用户名'@'%' identified by '密码';
# 查看主库状态
show master status;
# 查看从库同步状态
show slave status;
# 在从库中配置主库信息
mysql> change master to master_host='主库ip',master_user='主库用于配置的同步的用户', master_password='密码', master_log_file='主库中的日志文件',  master_log_pos=<日志文件同步坐标>, master_port=3306;

# 在slave节点中启动该slave节点
mysql> start slave
# 在slave节点中停止该slave节点
mysql> stop slave;
#---------主从数据库相关-----------#

#---------远程连接相关-----------#
# 开启远程访问
# my.ini或my.conf配置文件中，配置文件可能位置：/etc/my.cnf
bind-address=127.0.0.1
# 修改为如下（若没有该参数，则手动添加在[mysqld]目录下）
bind-address=0.0.0.0
# 为用户授权，添加远程连接的能力
mysql> GRANT ALL PRIVILEGES ON *.* TO '用户名'@"%" IDENTIFIED BY "密码";
# 在5.6版本中，可能需要再次设置密码
mysql> update user set Password = password('密码') where User='用户名';
# 刷新
mysql> flush privileges;
# 重启mysql服务
sudo service mysql restart
#---------远程连接相关-----------#

常见问题

1. 连接docker中的mysql，使用navicat软件可以连接成功，但是shell中连接失败，错误原因：ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

使用命令： mysql -h localhost -P <your container port> --protocol=tcp -uroot -p <your password>

更多

qt5使用mysql
win10卸载原mysql安装64位mysql5.7并修改root密码
mysql 按树结构查询父子节点
mysql function和limit的使用

参考

mysql docker主从配置
mysql 主从数据库
mysql 5.6 允许远程连接

转载于:https://my.oschina.net/htzy/blog/851964