MySQL小结

1. 数据库（DB）

数据库是长期存放在计算机内有组织，可共享的数据集合。数据库按照一定的数据规模组织进行描述，存储，具有较低的冗余度，较高的数据独立性和易扩展性，并可以为各种用户共享。所以数据库操作都是通过网络远程操作，很好的支持了并发，良好的结构组织和查询算法也使得对数据库的增删改查效率极高。而用户直接将数据存储在文件中，不利于共享，无法存储大数据，低并发，效率低。

• 关系型数据库（Relational Database）：表结构存储(Table Strutured Store)
• 非关系型数据库（Non-Relational Database）： key-value存储

2. 数据库管理系统（DataBase Management system DBMS）

科学地组织和存储数据，高校的获取和维护。

比较常见的管理关系型数据库的有MySQL, Oracle, SQLite, Access, MS SQL Server.

3. 数据库中表与记录的关系

数据库相当于文件夹用于组织文件，而表相当于文件，记录相当于文件中的一行内容

DB = Dictionary, Tables = Files, Records = One-line content

4 从文件管理系统到数据管理系统的发展

就像我们电脑中的一个个文件夹，一个应用程序只能路径相关的几个文件，但是如果不同的应用程序之间有相同的数据，就会造成数据冗余。其他的程序并不知道其他地方存储了一样的数据。也就是说，文件之间并没有相关性，每个文件都是独立存在的。而且文件和程序之间的耦合程度过高，无论哪一方改变，都需要重新改写。

数据库管理系统改善了之后：

• 数据结构化
• 数据共享
• 数据独立性
• 数据由DBMS统一调度：数据的安全性保护，数据完整性检查，并发控制，数据备份与恢复

5 SQL(Structured Query Language)语句

为什么要结构化？Why we want structure？

如果在文件中直接搜索的话，对于我们来说，文件中的内容是连续并且没有规则的，只能通过暴力搜索，但是一次暴力搜索之后并不会有任何的状态保存下来。而结构化会使得在暴力搜索之后，保存有用的状态信息。这样就会快很多了。

SQL语句分为三种类型:

• DDL语句 数据库定义语句：定义数据库DB，表table，视图View，索引index，存储过程store function
• DML语句 数据库操纵语句：增insert，删delete，改update， 查query
• DCL语句 数据库控制：控制用户的访问权限。

6 存储引擎Engine

MYSQL中的数据采用不用的技术存储在文件中。每一种技术都有不同的存储机制，索引技巧，锁定水平并且提供不同的功能。通过选择不同的技术，对应相应的结构特点，可以改善应用的整体功能。

Mysql采用了多层设计和独立模块，插件式存储引擎体系结构。允许存储引擎即插即用Mysql支持InnoDB,MYISAM,MEMORY,CSV,BLACKHOLE,FEDERATED,MRG_MYISAM,ARCHIVE,PERFORMANCE_SCHEMA.其中NDB和InnoDB。主要性能评判：

• 并发性（Concurrence）：某些应用程序比其他应用程序具有很多的颗粒级锁定要求（如行级锁定）。
• 事务支持：并非所有的应用程序都需要事务，但对的确需要事务的应用程序来说，有着定义良好的需求，如ACID兼容等。
• 引用完整性：通过DDL定义的外键，服务器需要强制保持关联数据库的引用完整性。
• 物理存储：它包括各种各样的事项，从表和索引的总的页大小，到存储数据所需的格式，到物理磁盘。
• 索引支持：不同的应用程序倾向于采用不同的索引策略，每种存储引擎通常有自己的编制索引方法，但某些索引方法（如B-tree索引）对几乎所有的存储引擎来说是共同的。
• 内存高速缓冲：与其他应用程序相比，不同的应用程序对某些内存高速缓冲策略的响应更好，因此，尽管某些内存高速缓冲对所有存储引擎来说是共同的（如用于用户连接的高速缓冲，MySQL的高速查询高速缓冲等），其他高速缓冲策略仅当使用特殊的存储引擎时才唯一定义。
• 性能帮助：包括针对并行操作的多I/O线程，线程并发性，数据库检查点，成批插入处理等。 其他目标特性：可能包括对地理空间操作的支持，对特定数据处理操作的安全限制等。

常用存储引擎介绍：

InnoDB

用于事务处理应用程序，支持外键和行级锁。如果应用对事物的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询之外，还包括很多更新和删除操作，那么InnoDB存储引擎是比较合适的。InnoDB除了有效的降低由删除和更新导致的锁定，还可以确保事务的完整提交和回滚，对于类似计费系统或者财务系统等对数据准确要求性比较高的系统都是合适的选择。

注：事物（transaction）：MySQL 事务主要用于处理操作量大，复杂度高的数据。比如说，在人员管理系统中，你删除一个人员，你既需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，这样，这些数据库操作语句就构成一个事务！事务是必须满足4个条件（ACID）：原子性（Atomicity，或称不可分割性，要么完成，要么失败回滚相当于没发生）、一致性（Consistency）、隔离性（Isolation，又称独立性，解决并行读写同一数据的冲突）、持久性（Durability，结果长久的存储）。

MyISAM

如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不高，那么可以选择这个存储引擎。

Memory

将所有的数据保存在内存中，在需要快速定位记录和其他类似数据的环境下，可以提供极快的访问。Memory的缺陷是对表的大小有限制，虽然数据库因为异常终止的话数据可以正常恢复，但是一旦数据库关闭，存储在内存中的数据都会丢失。

查看当前的存储引擎：

show variables like "default_storage_engine";

查看所支持的引擎：

show engines \G;

指定存储引擎：

建表时指定：
create table country(id int(4),cname varchar(50)) ENGINE=InnoDB;
也可以修改已经定义好的存储引擎：
alter table ai engine = innodb;
在配置文件中指定：
#my.ini文件
[mysqld]
default-storage-engine=INNODB

7 MySQL工作流程

最上层的服务并不是MySQL独有的，大多数给予网络的客户端/服务器的工具或者服务都有类似的架构。比如：连接处理、授权认证、安全等。

第二层的架构包括大多数的MySQL的核心服务。包括：查询解析、分析、优化、缓存以及所有的内置函数（例如：日期、时间、数学和加密函数）。同时，所有的跨存储引擎的功能都在这一层实现：存储过程、触发器、视图等。

第三层包含了存储引擎。存储引擎负责MySQL中数据的存储和提取。服务器通过API和存储引擎进行通信。这些接口屏蔽了不同存储引擎之间的差异，使得这些差异对上层的查询过程透明化。存储引擎API包含十几个底层函数，用于执行“开始一个事务”等操作。但存储引擎一般不会去解析SQL（InnoDB会解析外键定义，因为其本身没有实现该功能），不同存储引擎之间也不会相互通信，而只是简单的响应上层的服务器请求。

第四层包含了文件系统，所有的表结构和数据以及用户操作的日志最终还是以文件的形式存储在硬盘上

 8 MySQL表操作 Table operation

构建类似如下表：

id	name	age	sex	phone	job
1	Alex	83	female	13651054608	IT
2	Egon	26	male	13304320533	Teacher
3	nezha	25	male	13332353222	IT
4	boss_jin	40	male	13332353333	IT

id,name,age,sex,phone,job称为字段，其余的，一行内容称为一条记录 。

创建表：

#语法：
create table 表名(
字段名1 类型[(宽度) 约束条件],
字段名2 类型[(宽度) 约束条件],
字段名3 类型[(宽度) 约束条件]
);

#注意：
1. 在同一张表中，字段名是不能相同
2. 宽度和约束条件可选
3. 字段名和类型是必须的

前面已经说过表相当于个文件夹中, 那么就需要先建立存储表的地方。

1. 创建DB 
create database staff;
2. 切换DB
use staff;
3. 创建表
create table staff_info (id int,name varchar(50),age int(3),
sex enum('male','female'),phone bigint(11),job varchar(11));

注: enum不是约束条件，是数据类型，ENUM只允许从值集合中选取单个值，而不能一次取多个值。

注：数据类型和约束条件详解：

查看表：

三种方法:
1. describe [tablename];
2. desc [tablename];
3. show create table \G;     #\G 使得记录能够竖向排列，以便更好的显示内容较长的记录。
1和2的效果相同，3可以显示更多信息

 修改表结构：

语法：
1. 修改表名
      ALTER TABLE 表名 RENAME 新表名;

2. 增加字段
      ALTER TABLE 表名 ADD 字段名  数据类型 [完整性约束条件…],
                      ADD 字段名  数据类型 [完整性约束条件…];
                            
3. 删除字段
      ALTER TABLE 表名 DROP 字段名;

4. 修改字段
      Modify和Change最大区别是Modify只需要写一次字段名，而change无论是否对字段名修改都需要重复        
      写两次字段名，所以Modify不能修改字段名，只能修改其属性。

      ALTER TABLE 表名 MODIFY  字段名 数据类型 [完整性约束条件…];
      ALTER TABLE 表名 CHANGE 旧字段名 新字段名 旧数据类型 [完整性约束条件…];
      ALTER TABLE 表名 CHANGE 旧字段名 新字段名 新数据类型 [完整性约束条件…];

5.修改字段排列顺序/在增加的时候指定字段位置
    ALTER TABLE 表名 ADD 字段名  数据类型 [完整性约束条件…]  FIRST;
    ALTER TABLE 表名 ADD 字段名  数据类型 [完整性约束条件…]  AFTER 字段名;
    ALTER TABLE 表名 CHANGE 字段名  旧字段名 新字段名 新数据类型 [完整性约束条件…]  FIRST;
    ALTER TABLE 表名 MODIFY 字段名  数据类型 [完整性约束条件…]  AFTER 字段名;


Eg.

create table t(id int unique,name char(10) not null);
#去掉null约束
alter table t modify name char(10) null;
# 添加null约束
alter table t modify name char(10) not null;

# 去掉unique约束
alter table t drop index id;
# 添加unique约束
alter table t modify id int unique;

# 添加联合唯一，多个字段的combo不存在重复
alter table t add unique index(aa,bb);
alter操作非空和唯一
 

删除表：

DROP TABLE 表名;

9. 表的约束

主键：唯一标识，不能重复，不能为空。 

能够唯一标识表中某一行的属性或属性组。一个表只能有一个主键，但可以有多个候选索引。主键常常与外键构成参照完整性约束，防止出现数据不一致。主键可以保证记录的唯一和主键域非空,数据库管理系统对于主键自动生成唯一索引，所以主键也是一个特殊的索引。

# 每个表都必须设定唯一的主键，用于快速查找。该主键不能重复。
# 比如学生的名字存在可能存在重复，那么用班级+学生名字做主键

修改主键需要注意的是在增加主键之前,必须先把反复的id删除掉。
1、首先创建一个数据表table_test：
create table table_test(
`id` varchar(100) NOT NULL,
`name` varchar(100) NOT NULL,
PRIMARY KEY (`name`)
); 
2、如果发现主键设置错了,应该是id是主键,但如今表里已经有好多数据了,不能删除表再重建了，仅仅能在这基础上改动表结构。
先删除主键
alter table table_test drop primary key;
然后再增加主键
alter table table_test add primary key(id);


# 创建自增id主键
alter table staff modify id int(4) primary key auto_increment;
# 删除主键，可以看到删除一个自增主键会报错
alter table staff drop primary key;
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
# 需要先去掉主键的自增约束，然后再删除主键约束
mysql> alter table staff modify id int(11);


# 添加联合主键
alter table staff add primary key (sname,age);
# 删除主键
alter table staff drop primary key;
# 创建主键id
alter table staff add primary key (id);

外键：建立表之间的关系，用来约束信息的一致性

被引用的键，必须是唯一的。而且必须都是用innoDB引擎。

创建press表
CREATE TABLE `press` (
  `id` int(11) NOT NULL,
  `name` char(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ；

创建book表
CREATE TABLE `book` (
  `id` int(11) DEFAULT NULL,
  `bk_name` char(12) DEFAULT NULL,
  `press_id` int(11) NOT NULL,
  KEY `press_id` (`press_id`)
) ；

为book表添加外键
alter table book add constraint fk_id foreign key(press_id) references press(id);

删除外键
alter table book drop foreign key fk_id;

10. 如何找到两张表中的联系？

1. 先站在左表的角度去找 是否左表的多条记录可以对应右表的一条记录，如果是，则证明左表的一个字段foreign key 右表一个字

2. 再站在右表角度去找 是否右表的多条记录可以对应左表的一条记录，如果是，则证明右表的一个字段foreign key 左表一个字段

3. 情况总结:

• 多对一：1或2只有一种情况发生，比较容易理解，比如主机和机房，一个机房可以容纳多台主机。
• 多对多：1和2同时发生，此时需要第三张表来存放两张表的关系
• 一对一：1和2都没有发生，两个表无关或者是两个表一一对应，当一一对应的时候，只要把外键设置为unqiue就行。比如，一个管理员唯一对应一个用户

多对多的举例：

主要原因，省空间。可以用序号去替代大量的文本。只需要添加一张表去存储序号对应文本信息。

#多对多
三张表：出版社，作者信息，书
多对多：一个作者可以写多本书，一本书也可以有多个作者，双向的一对多，即多对多
关联方式：foreign key+一张新的表

create table author(
id int primary key auto_increment,
name varchar(20)
);


#这张表就存放作者表与书表的关系，即查询二者的关系查这表就可以了
create table author2book(
id int not null unique auto_increment,
author_id int not null,
book_id int not null,
constraint fk_author foreign key(author_id) references author(id)
on delete cascade
on update cascade,
constraint fk_book foreign key(book_id) references book(id)
on delete cascade
on update cascade,
primary key(author_id,book_id)
);


#插入四个作者，id依次排开
insert into author(name) values('egon'),('alex'),('yuanhao'),('wpq');

#每个作者与自己的代表作如下
egon: 
九阳神功
九阴真经
九阴白骨爪
独孤九剑
降龙十巴掌
葵花宝典
alex: 
九阳神功
葵花宝典
yuanhao:
独孤九剑
降龙十巴掌
葵花宝典
wpq:
九阳神功


insert into author2book(author_id,book_id) values
(1,1),
(1,2),
(1,3),
(1,4),
(1,5),
(1,6),
(2,1),
(2,6),
(3,4),
(3,5),
(3,6),
(4,1)
;

11 增删改查

增删改

增：可以多条插入
任意插入
INSERT INTO 表名(字段1,字段2,字段3…字段n) VALUES(值1,值2,值3…值n);
顺序插入：
INSERT INTO 表名 VALUES (值1,值2,值3…值n);       
插入查询结果
INSERT INTO 表名(字段1,字段2,字段3…字段n) 
SELECT (字段1,字段2,字段3…字段n) FROM 表2
WHERE …;

删除：
DELETE FROM 表名 WHERE CONITION;

改：
UPDATE 表名 SET 字段1=值1, 字段2=值2, WHERE CONDITION;

查询：

a. 单表查询：

SELECT DISTINCT 字段1,字段2... FROM 表名
                              WHERE 条件
                              GROUP BY field
                              HAVING 筛选
                              ORDER BY field
                              LIMIT 限制条数

关键字优先级：决定了运行顺序

from 》where 》group by 》select 》distinct 》having 》order by 》limit


按照一定规则批量提出相应的值，进行处理后拼接成新的表进行显示

1.找到表:from

2.拿着where指定的约束条件，去文件/表中取出一条条记录

3.将取出的一条条记录进行分组group by，如果没有group by，则整体作为一组

4.执行select， 选择字段，可以将选择的字段进行一些处理（系统提供的函数，或四则运算）

5.distinct用于去重

6.将分组的结果进行having过滤

7.将结果按条件排序：order by

8.限制结果的显示条数

简单查询，格式定义

#简单查询
    SELECT id,emp_name,sex,age,hire_date,post,post_comment,salary,office,depart_id 
    FROM employee;

    SELECT * FROM employee;

    SELECT emp_name,salary FROM employee;

#避免重复DISTINCT
    SELECT DISTINCT post FROM employee;    

#通过四则运算查询
    SELECT emp_name, salary*12 FROM employee;
    SELECT emp_name, salary*12 AS Annual_salary FROM employee;
    SELECT emp_name, salary*12 Annual_salary FROM employee;

#定义显示格式
   CONCAT() 函数用于连接字符串
   SELECT CONCAT('姓名: ',emp_name,'  年薪: ', salary*12)  AS Annual_salary 
   FROM employee;
   
   CONCAT_WS() 第一个参数为分隔符
   SELECT CONCAT_WS(':',emp_name,salary*12)  AS Annual_salary 
   FROM employee;

   结合CASE语句：
   SELECT
       (
           CASE
           WHEN emp_name = 'jingliyang' THEN
               emp_name
           WHEN emp_name = 'alex' THEN
               CONCAT(emp_name,'_BIGSB')
           ELSE
               concat(emp_name, 'SB')
           END
       ) as new_name
   FROM
       employee;

Where约束条件：

where字句中可以使用：

1. 比较运算符：> < >= <= <> !=
2. between 80 and 100 值在80到100之间
3. in(80,90,100) 值是80或90或100
4. 模糊查询
    1） like not like
        eg.  like 'e%'
        通配符可以是%或_，
        %表示任意多字符
        _表示一个字符
        ^ 表示取反
        [] 匹配括号内任一 
    2）正则查询 REGECXP， NOT REGEXP
5. 逻辑运算符：在多个条件直接可以使用逻辑运算符 and or not



例子     
#1:多条件查询
    SELECT emp_name,salary FROM employee
        WHERE post='teacher' AND salary>10000;

#2:关键字BETWEEN AND
    SELECT emp_name,salary FROM employee 
        WHERE salary BETWEEN 10000 AND 20000;
    
#3:关键字IS NULL(判断某个字段是否为NULL不能用等号，需要用IS)
    区分：
    SELECT emp_name,post_comment FROM employee 
        WHERE post_comment IS NULL;
       
    SELECT emp_name,post_comment FROM employee 
        WHERE post_comment=''; 注意''是空字符串，不是null

#4:关键字IN集合查询
    SELECT emp_name,salary FROM employee 
        WHERE salary=3000 OR salary=3500 OR salary=4000 OR salary=9000 ;
    等同于
    SELECT emp_name,salary FROM employee 
        WHERE salary IN (3000,3500,4000,9000) ;

#5:关键字LIKE模糊查询
    通配符’%’
    SELECT * FROM employee 
            WHERE emp_name LIKE 'eg%';

    通配符’_’
    SELECT * FROM employee 
            WHERE emp_name LIKE 'al__';

Group by：

单独使用GROUP BY关键字分组
    SELECT post FROM employee GROUP BY post;
    注意：我们按照post字段分组，那么select查询的字段只能是post，想要获取组内的其他相关信息，需要借助函数

GROUP BY关键字和GROUP_CONCAT()函数一起使用
    SELECT post,GROUP_CONCAT(emp_name) FROM employee GROUP BY post;#按照岗位分组，并查看组内成员名
    SELECT post,GROUP_CONCAT(emp_name) as emp_members FROM employee GROUP BY post;

GROUP BY与聚合函数一起使用
    select post,count(id) as count from employee group by post;#按照岗位分组，并查看每个组有多少人


聚合函数：
#强调：聚合函数聚合的是组的内容，若是没有分组，则默认一组

示例：
    SELECT COUNT(*) FROM employee;
    SELECT COUNT(*) FROM employee WHERE depart_id=1;
    SELECT MAX(salary) FROM employee;
    SELECT MIN(salary) FROM employee;
    SELECT AVG(salary) FROM employee;
    SELECT SUM(salary) FROM employee;
    SELECT SUM(salary) FROM employee WHERE depart_id=3;



注：如果我们用unique的字段作为分组的依据，则每一条记录自成一组，这种分组没有意义
多条记录之间的某个字段值相同，该字段通常用来作为分组的依据

Having 过滤：

#！！！执行优先级从高到低：where > group by > having 
#1. Where 发生在分组group by之前，因而Where中可以有任意字段，但是绝对不能使用聚合函数。
#2. Having发生在分组group by之后，因而Having中可以使用分组的字段，无法直接取到其他字段,可以使用聚合函数

 Order by排序：

按单列排序
    SELECT * FROM employee ORDER BY salary;
    SELECT * FROM employee ORDER BY salary ASC;
    SELECT * FROM employee ORDER BY salary DESC;

按多列排序:先按照age排序，如果年纪相同，则按照薪资排序
    SELECT * from employee
        ORDER BY age,
        salary DESC;

Limit 限制查询范围：

示例：
    SELECT * FROM employee ORDER BY salary DESC 
        LIMIT 3;                    #默认初始位置为0 
    
    SELECT * FROM employee ORDER BY salary DESC
        LIMIT 0,5; #从第0开始，即先查询出第一条，然后包含这一条在内往后查5条

    SELECT * FROM employee ORDER BY salary DESC
        LIMIT 5,5; #从第5开始，即先查询出第6条，然后包含这一条在内往后查5条

b. 多表查询：

SELECT 字段列表
    FROM 表1 INNER|LEFT|RIGHT JOIN 表2
    ON 表1.字段 = 表2.字段;

1 交叉连接：不适用任何匹配条件。生成笛卡尔积，假设表A，表B，a，b表示A，B中的一条记录，则A X B表示所有 (a, b) 的集合。简单的说就是A中每条记录和B中所有记录的组合，a1 b1, a1 b2 ... a2 b1 a2 b2.... 汇成一张表。

select * from employee,department;

2 内连接：只连接匹配的行，找两张表共有的部分

select 
employee.id,employee.name,employee.age,employee.sex,department.name 
from 
employee inner join department 
on employee.dep_id=department.id;
等价于
select
employee.id,employee.name,employee.age,employee.sex,department.name 
from 
employee,department 
where employee.dep_id=department.id;

3 外链接之左连接：优先显示左表全部记录

#以左表为准，即找出所有员工信息，当然包括没有部门的员工
#本质就是：在内连接的基础上增加左边有右边没有的结果
select 
employee.id,employee.name,department.name as depart_name 
from 
employee left join department 
on 
employee.dep_id=department.id;

4 外链接之右连接：优先显示右表全部记录

select 
employee.id,employee.name,department.name as depart_name 
from 
employee right join department 
on employee.dep_id=department.id;

5 全外连接：显示左右两个表全部记录

全外连接：在内连接的基础上增加左边有右边没有的和右边有左边没有的结果
#注意：mysql不支持全外连接 full JOIN
#强调：mysql可以使用此种方式间接实现全外连接
select * from employee left join department on employee.dep_id = department.id
union
select * from employee right join department on employee.dep_id = department.id
;

C. 嵌套查询

• 子查询是将一个查询语句嵌套在另一个查询语句中。
• 内层查询语句的查询结果，可以为外层查询语句提供查询条件。
• 子查询中可以包含：IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等关键字
• 还可以包含比较运算符：= 、 !=、> 、<等、

#查询平均年龄在25岁以上的部门名
select id,name from department
    where id in 
        (select dep_id from employee group by dep_id having avg(age) > 25);

#查看技术部员工姓名
select name from employee
    where dep_id in 
        (select id from department where name='技术');

#查看不足1人的部门名(子查询得到的是有人的部门id)
select name from department where id not in (select distinct dep_id from employee);

#查询大于所有人平均年龄的员工名与年龄
select name,age from emp where age > (select avg(age) from emp);

#查询大于部门内平均年龄的员工名、年龄
select t1.name,t1.age from emp t1
inner join 
(select dep_id,avg(age) avg_age from emp group by dep_id) t2
on t1.dep_id = t2.dep_id
where t1.age > t2.avg_age; 


EXISTS关字键字表示存在。在使用EXISTS关键字时，内层查询语句不返回查询的记录。
而是返回一个真假值。True或False
当返回True时，外层查询语句将进行查询；当返回值为False时，外层查询语句不进行查询

#department表中存在dept_id=203，Ture
select * from employee
where exists
(select id from department where id=200);

12 综合练习

数据导入（Navicat一个轻量级的MYSQL可视化工具）：

/*
 数据导入：
 Navicat Premium Data Transfer

 Source Server         : localhost
 Source Server Type    : MySQL
 Source Server Version : 50624
 Source Host           : localhost
 Source Database       : sqlexam

 Target Server Type    : MySQL
 Target Server Version : 50624
 File Encoding         : utf-8

 Date: 10/21/2016 06:46:46 AM
*/

SET NAMES utf8;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
--  Table structure for `class`
-- ----------------------------
DROP TABLE IF EXISTS `class`;
CREATE TABLE `class` (
  `cid` int(11) NOT NULL AUTO_INCREMENT,
  `caption` varchar(32) NOT NULL,
  PRIMARY KEY (`cid`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

-- ----------------------------
--  Records of `class`
-- ----------------------------
BEGIN;
INSERT INTO `class` VALUES ('1', '三年二班'), ('2', '三年三班'), ('3', '一年二班'), ('4', '二年九班');
COMMIT;

-- ----------------------------
--  Table structure for `course`
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
  `cid` int(11) NOT NULL AUTO_INCREMENT,
  `cname` varchar(32) NOT NULL,
  `teacher_id` int(11) NOT NULL,
  PRIMARY KEY (`cid`),
  KEY `fk_course_teacher` (`teacher_id`),
  CONSTRAINT `fk_course_teacher` FOREIGN KEY (`teacher_id`) REFERENCES `teacher` (`tid`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

-- ----------------------------
--  Records of `course`
-- ----------------------------
BEGIN;
INSERT INTO `course` VALUES ('1', '生物', '1'), ('2', '物理', '2'), ('3', '体育', '3'), ('4', '美术', '2');
COMMIT;

-- ----------------------------
--  Table structure for `score`
-- ----------------------------
DROP TABLE IF EXISTS `score`;
CREATE TABLE `score` (
  `sid` int(11) NOT NULL AUTO_INCREMENT,
  `student_id` int(11) NOT NULL,
  `course_id` int(11) NOT NULL,
  `num` int(11) NOT NULL,
  PRIMARY KEY (`sid`),
  KEY `fk_score_student` (`student_id`),
  KEY `fk_score_course` (`course_id`),
  CONSTRAINT `fk_score_course` FOREIGN KEY (`course_id`) REFERENCES `course` (`cid`),
  CONSTRAINT `fk_score_student` FOREIGN KEY (`student_id`) REFERENCES `student` (`sid`)
) ENGINE=InnoDB AUTO_INCREMENT=53 DEFAULT CHARSET=utf8;

-- ----------------------------
--  Records of `score`
-- ----------------------------
BEGIN;
INSERT INTO `score` VALUES ('1', '1', '1', '10'), ('2', '1', '2', '9'), ('5', '1', '4', '66'), ('6', '2', '1', '8'), ('8', '2', '3', '68'), ('9', '2', '4', '99'), ('10', '3', '1', '77'), ('11', '3', '2', '66'), ('12', '3', '3', '87'), ('13', '3', '4', '99'), ('14', '4', '1', '79'), ('15', '4', '2', '11'), ('16', '4', '3', '67'), ('17', '4', '4', '100'), ('18', '5', '1', '79'), ('19', '5', '2', '11'), ('20', '5', '3', '67'), ('21', '5', '4', '100'), ('22', '6', '1', '9'), ('23', '6', '2', '100'), ('24', '6', '3', '67'), ('25', '6', '4', '100'), ('26', '7', '1', '9'), ('27', '7', '2', '100'), ('28', '7', '3', '67'), ('29', '7', '4', '88'), ('30', '8', '1', '9'), ('31', '8', '2', '100'), ('32', '8', '3', '67'), ('33', '8', '4', '88'), ('34', '9', '1', '91'), ('35', '9', '2', '88'), ('36', '9', '3', '67'), ('37', '9', '4', '22'), ('38', '10', '1', '90'), ('39', '10', '2', '77'), ('40', '10', '3', '43'), ('41', '10', '4', '87'), ('42', '11', '1', '90'), ('43', '11', '2', '77'), ('44', '11', '3', '43'), ('45', '11', '4', '87'), ('46', '12', '1', '90'), ('47', '12', '2', '77'), ('48', '12', '3', '43'), ('49', '12', '4', '87'), ('52', '13', '3', '87');
COMMIT;

-- ----------------------------
--  Table structure for `student`
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `sid` int(11) NOT NULL AUTO_INCREMENT,
  `gender` char(1) NOT NULL,
  `class_id` int(11) NOT NULL,
  `sname` varchar(32) NOT NULL,
  PRIMARY KEY (`sid`),
  KEY `fk_class` (`class_id`),
  CONSTRAINT `fk_class` FOREIGN KEY (`class_id`) REFERENCES `class` (`cid`)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8;

-- ----------------------------
--  Records of `student`
-- ----------------------------
BEGIN;
INSERT INTO `student` VALUES ('1', '男', '1', '理解'), ('2', '女', '1', '钢蛋'), ('3', '男', '1', '张三'), ('4', '男', '1', '张一'), ('5', '女', '1', '张二'), ('6', '男', '1', '张四'), ('7', '女', '2', '铁锤'), ('8', '男', '2', '李三'), ('9', '男', '2', '李一'), ('10', '女', '2', '李二'), ('11', '男', '2', '李四'), ('12', '女', '3', '如花'), ('13', '男', '3', '刘三'), ('14', '男', '3', '刘一'), ('15', '女', '3', '刘二'), ('16', '男', '3', '刘四');
COMMIT;

-- ----------------------------
--  Table structure for `teacher`
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
  `tid` int(11) NOT NULL AUTO_INCREMENT,
  `tname` varchar(32) NOT NULL,
  PRIMARY KEY (`tid`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

-- ----------------------------
--  Records of `teacher`
-- ----------------------------
BEGIN;
INSERT INTO `teacher` VALUES ('1', '张磊老师'), ('2', '李平老师'), ('3', '刘海燕老师'), ('4', '朱云海老师'), ('5', '李杰老师');
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;

init.sql

问题 

1、查询男生、女生的人数；
2、查询姓“张”的学生名单；
3、课程平均分从高到低显示
4、查询有课程成绩小于60分的同学的学号、姓名；
5、查询至少有一门课与学号为1的同学所学课程相同的同学的学号和姓名；
6、查询出只选修了一门课程的全部学生的学号和姓名；
7、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分；
8、查询课程编号“2”的成绩比课程编号“1”课程低的所有同学的学号、姓名；
9、查询“生物”课程比“物理”课程成绩高的所有学生的学号；
10、查询平均成绩大于60分的同学的学号和平均成绩;
11、查询所有同学的学号、姓名、选课数、总成绩；
12、查询姓“李”的老师的个数；
13、查询没学过“张磊老师”课的同学的学号、姓名；
14、查询学过“1”并且也学过编号“2”课程的同学的学号、姓名；
15、查询学过“李平老师”所教的所有课的同学的学号、姓名；

更多练习
1、查询没有学全所有课的同学的学号、姓名；
2、查询和“002”号的同学学习的课程完全相同的其他同学学号和姓名；
3、删除学习“叶平”老师课的SC表记录；
4、向SC表中插入一些记录，这些记录要求符合以下条件：①没有上过编号“002”课程的同学学号；②插入“002”号课程的平均成绩； 
5、按平均成绩从低到高显示所有学生的“语文”、“数学”、“英语”三门的课程成绩，按如下形式显示： 学生ID,语文,数学,英语,有效课程数,有效平均分；
6、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分；
7、按各科平均成绩从低到高和及格率的百分数从高到低顺序；
8、查询各科成绩前三名的记录:(不考虑成绩并列情况) 
9、查询每门课程被选修的学生数；
10、查询同名同姓学生名单，并统计同名人数；
11、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列；
12、查询平均成绩大于85的所有学生的学号、姓名和平均成绩；
13、查询课程名称为“数学”，且分数低于60的学生姓名和分数；
14、查询课程编号为003且课程成绩在80分以上的学生的学号和姓名； 
15、求选了课程的学生人数
16、查询选修“杨艳”老师所授课程的学生中，成绩最高的学生姓名及其成绩；
17、查询各个课程及相应的选修人数；
18、查询不同课程但成绩相同的学生的学号、课程号、学生成绩；
19、查询每门课程成绩最好的前两名；
20、检索至少选修两门课程的学生学号；
21、查询全部学生都选修的课程的课程号和课程名；
22、查询没学过“叶平”老师讲授的任一门课程的学生姓名；
23、查询两门以上不及格课程的同学的学号及其平均成绩；
24、检索“004”课程分数小于60，按分数降序排列的同学学号；
25、删除“002”同学的“001”课程的成绩；

补充材料：

函数大全：https://blog.csdn.net/lzh_12345/article/details/88600135

MYSQL数据类型：https://www.cnblogs.com/Eva-J/articles/9683316.html#_label4

MYSQL完整性约束: https://www.cnblogs.com/Eva-J/articles/9687915.html