数据库——命令

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数据库的一些命令

连接mysql的三种方法 在命令行输入：mysql -u root -p 回车，然后输入密码回车；在命令行输入：mysql -h localhost -P 3306 -u root -p 回车，然后输入密码回车；在mysql文件夹中找MySQL，Command Line Client 启动，然后输入密码。 基本的sql语句（sql语句以分号结尾，类比于常见的excel创建写入与存储） 显示所有的数据库文件（类似于打开excel文件目录）：show databases;创建数据库（类似于创建excel文件）：create database test;使用某个数据库（打开一个excel文件）：use test;查看数据表（相当于看看有哪些sheet）：show tables;删除库（相当于删除excel）:drop database test; 对数据表进行操作 创建表(相当于我们在 excel 中新建一个sheet，然后sheet的首行规定每一列该填什么，如姓名，年龄，性别) create table [if not exists] 表名称 ( 字段名1 列类型 [属性] [约束] [注释], 字段名2 列类型 [属性] [约束] [注释], ...... 字段名n 列类型 [属性] [约束] [注释] ); 删除表（相当于在excel中删除一个存在的sheet）：drop table ( 表名称);查看表结构：desc (表名称);查看所有数据列表：show tables;查看某一个数据表中的项：select * from (表名称); 数据类型（一般用在列类型上） 数值类型：整型：常用的有 int bigint；浮点型：常用的有 float double字符串类型（m是个长度数值）：char(m) varchar(20) 表示20个字符日期类型：date 2019-8-3 datetime 2019-8-3 10:05:30 属性与约束 null 空not null 不为空default 默认值 如：age int default 18unique key 唯一设置某个列的值，如身份证号等primary key 主键唯一标示（自带唯一、not null属性），是一个表中必须有的，一般都是数字自增auto_increment 自增长，必须给主键设置int，它的值是不会回退的foreign key 外键，减少冗余，用来与其他表连接 更改表结构 更改表名称：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 (参考表的表名)(列名称且这个列名称是有主键属性) 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。这样就可以把查询结果起别名

一个操作实例

创建school数据库 create database school; use school;创建四张表：学生表、课程表、教师表、分数表 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) );往表里插值 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’同学学过的任一门课程的人。