数据库——命令

mysql是什么

数据库是结构化信息或数据的有组织的集合，通常以电子方式存储在计算机系统中。数据库通常由数据库管理系统（DBMS）来控制。数据、DBMS及其关联应用一起被称为数据库系统，通常简称为数据库。——甲骨文官网

• 非结构化数据：例如文本数据、视频数据等；
• 半结构化数据：例如JSON格式的数据、html文档等；
• 结构化数据：例如mysql中存储的每行数据。
  也就是说，结构化数据是固定的字段，并且字段的数值类型也是固定的。而JSON之所以叫做半结构化数据，是因为它的字段数值类型不是固定的。
  Mysql 就是一款数据管理系统，即DBMS，再辅助上存储的数据和其生态应用，统称为 Mysql 数据库。再来看下 MySQL 的官网介绍：
  MySQL, the most popular Open Source SQL database management system

mysql是关系型数据库

通过将每张表看成是一个集合，也就是更抽象一层，我们就能够利用集合中的交并补等运算来进行操作，也就是将具体的数据表上升到了数学理论的高度。这个数学理论就是集合论，而关系型数据库的理论基础就是集合论。我们执行的各种增删改查操作，实际上都可以抽象的看成是对于集合（关系）的各种操作。
理论中的关系（即集合）对应的就是数据表，元组对应的就是每条记录，域对应的就是每列字段。基于这样理论基础的数据库就叫做关系型数据库。关系代表我们在操作这些数据的时候，把这些数据看成一个个集合，然后用集合之间的运算执行操作，只是这些操作或者这些数据与集合对应是有着坚实的数学理论基础。

**Mysql是数据库、关系型数据库的子集。**但是mysql有自己独有的特性：

• Uses multi-layered server design with independent modules. 同样是分层思想的体现
• Provides transactional and nontransactional storage engines. 两个关键词：事务、存储引擎
• Executes very fast joins using an optimized nested-loop join. 关键词：嵌套循环关联
• An EXPLAIN statement to show how the optimizer resolves a query. 关键词：explain语句
• The Connector/J interface provides MySQL support for Java client programs that use JDBC connections JDBC连接采用的是 Connector/J 接口
• These include both command-line programs such as mysqldump and mysqladmin, and graphical programs such as** MySQL Workbench**. 关键词：一些工具
• MySQL Server has built-in support for SQL statements to check, optimize, and repair tables. These statements are available from the command line through the mysqlcheck. 关键词：mysqlcheck 工具集
• MySQL programs can be invoked with the --help or -? option to obtain online assistance 可以使用 --help 来访问在线助手

mysql的逻辑结构和一些处理流程

服务器端的主要模块有：

• Connections/Thread handling：这就是处理连接的线程（池）
  客户端通过JDBC驱动向 Mysql 服务端发起连接，并携带上账号密码以及需要访问的数据库IP和名称，并且可以携带一些URL参数来传递其他信息，最终服务端将连接建立好以后，我们就获得了表示这个连接通路的 Connetction 对象。
  服务器端的Connection handling的核心功能有：
  1）建立并管理与客户端的连接；
  2）验证：验证账号密码，验证URL中的其他配置等，比如开启了SSL认证，则需要进行证书认证。
• Query Cache：查询缓存
  5.7.20之后的版本被废弃了。其原本的设计功能：对于 select 语句的查询结果进行缓存，当再次收到相同的 select 语句后，直接将结果返回，不进行下面的步骤。
  这个功能有诸多限制和缺点，分类如下：
  1）Queries must be exactly the same (byte for byte) to be seen as identical，也就是 字符串需要完全相同，并且语句中所有信息是确定的，比如包含了函数 NOW() 的语句其查询结果就不会被缓存下来 。下面这两个语句就会判断成两个查询语句：
  SELECT * FROM tbl_name
  select * from tbl_name
  2)另外一种限制是缓存的失效情景非常多，比如增删改等各种操作。这就导致了缓存命中率非常低，也就是程序花费了额外的时间来执行查询缓存，结果命中率低，导致性价比不高。
  3)查询缓存是存储在内存中的，所以对于内存的影响也很大；而且从存储、更新到删除都会消耗性能。
  其实，为什么一定要在服务端缓存数据呢？客户端照样可以，所以大家都会接触到比如 mybatis 的二级缓存、redis缓存等。
• Parser：解析器
  这里的解析器实际上起到了我们编程中编译器的作用。对于 SELECT * FROM tbl_name 这样一个字符串，需要经过下面的几个过程，程序才能完整的理解：
  1）词法分析：比如 SELECT 是关键字， tb1_name 是表名等
  2）语法分析：比如这个是查询语句，其格式满足 sql 格式
  3）语义分析：这里的语义分析实际上等同于 预处理器 ，这个预处理器也可归纳到解析器中。预处理器会检查数据列等是否存在，别名是否有歧义，并进行权限验证（是否有执行 select 的权限等）
  在 语法分析后，就会生成一棵解析树，类似于编译原理中的 AST（抽象语法树）。
• Optimizer：优化器
  优化器的最终目的是按照一定的指标（优化目标）生成它认为最佳的执行计划。这里的执行计划指的是，比如 A 表和 B 表关联查询，则先查询A表中符合条件的还是先查询B表中符合条件的，这就相当于两种不同的执行计划。这里面有三个关键点：优化指标如何考量？对什么进行优化？优化指标中的一些信息如何获取？
  mysql的优化策略也分为两种：
  1）编译时优化（静态优化）：就是对于解析树分析并优化；
  2）运行时优化（动态优化）：比如查询数据时索引的选择；
• Storage Engines：存储引擎
  首图中没有标明执行引擎，因为是逻辑结构，所以也可以将执行引擎和存储引擎归为一类，他们都属于执行阶段。
  在这个阶段，就非常简单了。好比设计师规划好了图纸和方案，工人们只需要按部就班执行即可。
  存储引擎对外提供了一些基本的接口，通过这些功能接口的调用组合，可以实现任何数据的增删改查等操作。而执行引擎就是根据优化器提供的执行计划来逐步执行。
• 其他的 cache/buffer 缓存

处理流程

其他过程不再赘述，只提一下数据返回。 Mysql 将结果集返回给客户端是增量、逐步返回的过程，并不是等到全部结果查好再一起返回。

mysql数据库的一些命令

1. 连接mysql的三种方法

• 在命令行输入：mysql -u root -p 回车，然后输入密码回车；
• 在命令行输入：mysql -h localhost -P 3306 -u root -p 回车，然后输入密码回车；
• 在mysql文件夹中找MySQL，Command Line Client 启动，然后输入密码。
  

2. 基本的sql语句（sql语句以分号结尾，类比于常见的excel创建写入与存储）

• 显示所有的数据库文件（类似于打开excel文件目录）：show databases;
• 创建数据库（类似于创建excel文件）：create database test;
• 使用某个数据库（打开一个excel文件）：use test;
• 查看数据表（相当于看看有哪些sheet）：show tables;
• 删除库（相当于删除excel）:drop database test;

3. 对数据表进行操作

• 创建表(相当于我们在 excel 中新建一个sheet，然后sheet的首行规定每一列该填什么，如姓名，年龄，性别)

  create table [if not exists] 表名称 (
    			字段名1  列类型 [属性] [约束] [注释],
    			字段名2  列类型 [属性] [约束] [注释],
    			......
    			字段名n  列类型 [属性] [约束] [注释]
    		);

• 删除表（相当于在excel中删除一个存在的sheet）：drop table ( 表名称);
• 查看表结构：desc (表名称);
• 查看所有数据列表：show tables;
• 查看某一个数据表中的项：select * from (表名称);

4. 数据类型（一般用在列类型上）

• 数值类型：整型：常用的有 int bigint；浮点型：常用的有 float double
• 字符串类型（m是个长度数值）：char(m) varchar(20) 表示20个字符
• 日期类型：date 2019-8-3 datetime 2019-8-3 10:05:30

5. 属性与约束

• null 空
• not null 不为空
• default 默认值 如：age int default 18
• unique key 唯一设置某个列的值，如身份证号等
• primary key 主键唯一标示（自带唯一、not null属性），是一个表中必须有的，一般都是数字自增
• auto_increment 自增长，必须给主键设置int，它的值是不会回退的
• foreign key 外键，减少冗余，用来与其他表连接

6. 更改表结构

• 更改表名称：alter table (旧表名) rename as (新表名)
  例如：alter table class1 rename as classOne;
• 添加字段：alter table (表名称) add (字段名)(列属性) [属性][约束]
  例如：alter table class2 add phone varchar(20);
• 删除字段：alter table (表名称) drop (字段名)
• 更改字段名称：alter table (表名称) change (旧字段名) (新字段名)(列属性) [属性][约束]
  例如：alter table class2 change name stu_name varchar(20) not null;
• 更改属性：alter table (表名称) modity (字段名) (列属性) [属性][约束]
  例如：alter table class2 modify stu_name varchar(50) not null;
• 增加外键：alter table (你要增加外键的表名) add constraint (你给外键取的名字) foreign key (你引用到外键的列名称) references (参考表的表名)(列名称且这个列名称是有主键属性)

7. select“字段”as是什么意思
   as可以理解为：用作、当成，作为：一般是重命名列名或表名。

• select column_1 as 列1,column_2 as 列2 from table as 表
  上面的语句就可以解释为，选择 column_1 作为 列1,column_2 作为 列2 从 table 当成 表
  SELECT * FROM Employee AS emp;
• 这句意思是查找所有Employee 表里面的数据，并把Employee表格命名为 emp。当你命名一个表之后，你可以在下面用 emp 代替 Employee。例如 SELECT * FROM emp;
• 把查询对象起个别名的作用。
  select ID as 用户ID，Name as 用户名 from Table_user 查出结果就以中文显示。
  select * from tb_user as mytableA join
  select * from Tb_UserGroup as mytableB
  on mytableA.ID=mytableB.ID。这样就可以把查询结果起别名

一个操作实例

1. 创建school数据库
   create database school;
   use school;
2. 创建四张表：学生表、课程表、教师表、分数表
   create table student(
   s_id varchar(10),
   s_name varchar(20),
   s_age date,
   s_sex varchar(10)
   );
   create table course(
   c_id varchar(10),
   c_name varchar(20),
   t_id varchar(10)
   );
   create table teacher(
   t_id varchar(10),
   t_name varchar(20)
   );
   create table score(
   s_id varchar(10),
   c_id varchar(10),
   score varchar(10)
   );
3. 往表里插值
   insert into student (s_id,s_name,s_age,s_sex)
   values (‘01’,‘赵磊’,‘1990-01-01’,‘男’),
   (‘02’,‘王默’,‘1990-12-21’,‘男’),
   (‘03’,‘李宇’,‘1990-01-03’,‘男’),
   (‘04’,‘王猛’,‘1990-08-06’,‘男’),
   (‘05’,‘张悦’,‘1991-12-01’,‘女’),
   (‘06’,‘吴梅’,‘1992-05-08’,‘女’),
   (‘07’,‘王菊’,‘1990-01-03’,‘女’),
   (‘08’,‘徐敏’,‘1990-04-23’,‘女’);

insert into course (c_id,c_name,t_id)
values (‘01’,‘语文’,‘02’),
(‘02’,‘数学’,‘01’),
(‘03’,‘英语’,‘03’);

insert into teacher (t_id,t_name)
values (‘01’,‘张维逸’),
(‘02’,‘许传科’),
(‘03’,‘王明’);

insert into score (s_id,c_id,score)
values (‘01’,‘01’,‘80’),
(‘01’,‘02’,‘90’),
(‘01’,‘03’,‘99’),
(‘02’,‘01’,‘70’),
(‘02’,‘02’,‘60’),
(‘02’,‘03’,‘80’),
(‘03’,‘01’,‘80’),
(‘03’,‘02’,‘80’),
(‘03’,‘03’,‘80’),
(‘04’,‘01’,‘60’),
(‘04’,‘02’,‘50’),
(‘04’,‘03’,‘40’),
(‘05’,‘01’,‘58’),
(‘05’,‘02’,‘47’),
(‘05’,‘03’,‘50’),
(‘06’,‘01’,‘99’),
(‘06’,‘02’,‘100’),
(‘06’,‘03’,‘95’),
(‘07’,‘01’,‘90’),
(‘07’,‘02’,‘98’),
(‘07’,‘03’,‘100’);
4. 创建一张总表
create table total(
select a.s_id as s_id,a.s_name as s_name,a.s_age as s_age,a.s_sex as s_sex,
b.c_id as c_id,b.score as score,c.t_id as t_id,d.t_name as t_name
from student a
left join
score b on a.s_id=b.s_id
left join
course c on b.c_id=c.c_id
left join
teacher d on c.t_id=d.t_id
);
select * from total;

SQL语句执行顺序：

1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数
select a.s_id as s_id,
score1,score2 from
(select s_id,
score as score1 from score where c_id=‘01’) a
inner join
(select s_id,
score as score2 from score where c_id=‘02’) b
on a.s_id=b.s_id
where score1>score2;

2、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
select student.s_id as s_id,student.s_name as s_name,b.avg_score as avg_score from student
right join
(select s_id,avg(score) as avg_score from score
group by s_id having avg_score>60) b
on student.s_id=b.s_id;

3、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
select s_id,s_name,count(c_id) as c_num,sum(score) as total_score
from total
group by s_id;

4、查询“李”姓老师的数量
select count(t_name) from teacher
where t_name like ‘李%’;

5、查询学过“王明”老师授课的同学的信息
select distinct s_id,s_name,s_age,s_sex
from total
where t_name=‘王明’;

6、查询没学过“王明”老师授课的同学的信息
select * from student
where s_id not in
(select distinct s_id
from total
where t_name=‘王明’);

7、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
select * from student
where s_id in
(select s_id from score where c_id=‘01’)
and s_id in
(select s_id from score where c_id=‘02’);

8、查询学过编号为"01"但没有学过编号为"02"的课程的同学的信息
select * from student
where s_id in
(select s_id from score where c_id=‘01’)
and s_id not in
(select s_id from score where c_id=‘02’);

9、查询没有学全所有课程的同学的信息
select s_id,s_name,s_age,s_sex from total
group by s_id having count(c_id)❤️;

10、查询至少有一门课与学号为"01"的同学所学相同的同学的信息
思路：先找出‘01’同学学过的c_id，再找出学过任一门的s_id，再根据s_id在student找学生信息。
select * from student
where s_id in
(select distinct s_id from score
where c_id in
(select c_id from score where s_id=‘01’));

11、查询和"01"号的同学学习的课程完全相同的其他同学的信息
思路：先找学过‘01’同学学过的课程的学生，然后通过group by找这些人里面学的课程数和‘01’相同的人。比如下面，表a是‘01’同学学过的课程，b则是所有学过‘01’同学学过的任一门课程的人。