数据库mysql

1. 基本常识

1.查询该linux系统安装的数据库mysql包：rpm -aq | grep mysql

2.启动mysql服务：systemctl start mysqld

3.查看mysql的服务进程是否启动：ps -ef | grep mysqld

4.Oracle数据库和mysql数据库的区别

• Oracle只有有一个数据库(安装的时候指定的实例)，数据库名就叫orcl，该数据库下有多个用户，如scott、hr等，每个用户下又有很多表。
• Mysql只有一个用户，用户名就叫root，该用户下有很多数据库，每个库下又有很多表。

2. 基本操作

1. mysql服务

• 重启Mysql服务：systemctl restart mysqld
• 查看Mysql服务状态：systemctl status mysqld
• 停止Mysql服务：systemctl stop mysqld
• 启动Mysql服务：systemctl start mysqld

2. 操作

1、进入mysql

• 启动Mysql服务进程：systemctl start mysqld
• 进入mysql：mysql -uroot -pLxxwly@93

2、查看有哪些数据库

• 查看：show databases;

3、创建删除一个数据库firstdb

• 创建：create database firstdb;

• 删除：drop database blog;

4、操作数据库firstdb

(1)进入数据库firstdb：use firstdb;

(2)查看有多少张表：show tables;

(3)创建一张表：create table t1(表名) (id varchar(20),name varchar(50));

(4)展示创建的表：show create table t1;

(5)查看表的字段：desc t1;

(6)向表中插入数据：insert into t1 values (‘0001’,’lxx’);

(7)查看这张表中的所有数据：select * from t1;

(8)按条件查：select * from t1 where id=’0002’

• 还有条件的话，可以加and继续

(9)按条件查，满足包含l字母的：select * from t1 where name like “%l%”

• 默认打印出全部，如果后面加limit 1，表示只打印一行

(10)机器localhost可以通过用户名first、密码为123进入数据库firstdb，对器所有的表进行操作：grant all on firstdb.* to first@’localhost’ identified by “123”;

(11)在客户端(服务器)192.168.88.93上面可以通过用户名first、密码为123进入数据库firstdb，对器所有的表进行操作：grant all on firstdb.* to first@’192.168.88.93’ identified by “123”;

注意：10和11在mysql8.0版本以上就不可以了，需要先创建用户

(12)先创建用户，在赋予权限：

• create user first@’lxx’ identified by ‘Lxxwly@93’;

• grant all on firstdb.* to first@’lxx’; ——>all改为select就表示只赋予查找的权限

• mysql -hlxx -ufirst -pLxxwly@93 -P3306 ——–退出后，从linux终端进入

注意：flush privileges; —–>刷新权限，每次更改后，需要刷新权限

(13)删除数据库：drop database test_db;

(14)删除表：drop table test01;

(15)清空表内容：delete from test01;

(16)查看数据库字符集：show variables like '%char%';

(17)修改密码：mysqladmin -uroot -p password

3. 综合案例

1.创建一个学生表

create table student(id int, name varchar(20), chinese int, english int, math int);

2.插入数据

insert into student(id,name,chinese,english,math) values(1,'路飞',80,85,90);
insert into student(id,name,chinese,english,math) values(2,'索隆',90,95,95);
insert into student(id,name,chinese,english,math) values(3,'山治',80,96,96);
insert into student(id,name,chinese,english,math) values(4,'娜美',81,97,85);
insert into student(id,name,chinese,english,math) values(5,'乌索普',85,84,90);
insert into student(id,name,chinese,english,math) values(6,'罗宾',92,85,87);
insert into student(id,name,chinese,english,math) values(7,'乔巴',75,81,80);
insert into student(id,name,chinese,english,math) values(8,'甚平',77,80,79);

3.基础SQL

• 查询表中所有学生的信息：select * from student;
• 查询表中所有学生的姓名和对应的英语成绩：select name,english from student;
• 过滤表中英语成绩重复的数据：select distinct english from student;
• 计算每个人三科的总成绩，并使用别名：select english+chinese+math as 总分 from student;
• 在所有学生英语分数上加10分：select name,english+10 from student;
• 查询姓名为索隆的学生成绩：select * from student where name = '索隆';
• 查询英语成绩大于90分的同学：select * from student where english > 90;
• 查询总分大于250分的所有同学：select * from student where english+chinese+math> 250;
• 查询英语分数在85-95之间的同学：
  • select * from student where english>=85 and english<=95;
  • select * from student where english between 85 and 95;
• 查询数学分数为84，90，91的同学：
  • select * from student where math=84 or math=90 or math=91;
  • select * from student where math in(84,90,91);
• 查询所有姓索的学生成绩：select * from student where name like '索%';
• 对数学成绩排序后输出(升序)：select * from student order by math;
• 对总分进行排序输出(降序)：select * from student order by math+chinese+english desc;
• 对姓索的同学按总成绩排序输出(降序)：select * from student where name like '索%' order by math+chinese+english desc;
• 统计一个班级共有多少学生：select count(*) from student;
• 统计数学成绩大于90分的学生个数：select count(*) from student where math>90;
• 统计一个班级数学总成绩：select sum(math) from student;
• 统计一个班级语文、英语、数学各科的总成绩：select sum(math),sum(chinese),sum(english) from student;
• 统计一个班级语文、英语、数学的成绩总和：
  • select sum(math+chinese+english) from student;
  • select sum(math)+sum(chinese)+sum(english) from student;
• 一个班级的数学平均分：select avg(math) from student;
• 班级的最高分的最低分：select max(math+chinese+english),min(math+chinese+english) from student;

4. 日期时间函数

mysql里面时间分为三类：时间、日期、时间戳(含有时分秒的sysdate)

• 查看当前时间、当前年、当前月、当前日和当前天：select now(), year(now()), month(now()), day(now()), date(now());

  

• 查看当前天，当前时间，当前时间戳：select current_date(), current_time(), current_timestamp() from dual;

5. 相关函数

• 对多个字符串进行拼接：select concat('aaa','bbb', 'ccc') from dual;

• 将字符串转换为大写：select ucase('abc') from dual;

• 将字符串转换为小写：select lcase('ABC') from dual;

• 返回第二个参数字符串在第一个参数字符串中出现的位置：select instr('abc','bc') from dual;

• 从字符串abc中的左边起取2个字符：select left('abc',2) from dual;

• 返回字符串的长度：select length('abc') from dual;

• 将字符串abcdefg中的abc转换为lxx：select replace('abcdefg','abc','lxx') from dual;

• 按字符比较两个字符串的大小：select strcmp('abcdefg','abc') from dual;

• 日期转换为字符串显示：select date_format(now(), '%Y-%m-%d %h:%i:%s') from dual;

1.数字相关函数

函数	注释
abs(number)	绝对值
bin(number)	十进制转二进制
ceiling(number)	向上取整
conv(number,from,to)	进制转换
floor(number)	向下取整
format(number,decimal_places)	保留小数位数
hex(number)	转十六进制
least(number1,number2,…..)	求最小值
mod(numerator,denominator)	求余

6. Linux 操作系统里要使用 MySQL

1. 连接器

• 连接 MySQL 服务器：mysql -u$user -p
  • 连接的过程需要先经过 TCP 三次握手，因为 MySQL 是基于 TCP 协议进行传输的
  • 完成 TCP 连接的建立后，连接器就要开始验证用户名和密码
  • 如果用户密码都没有问题，连接器就会获取该用户的权限，然后保存起来，后续该用户在此连接里的任何操作，都会基于连接开始时读到的权限进行权限逻辑的判断

注意：如果一个用户已经建立了连接，即使管理员中途修改了该用户的权限，也不会影响已经存在连接的权限。修改完成后，只有再新建的连接才会使用新的权限设置

• 如何查看 MySQL 服务被多少个客户端连接了：show processlist;

  • 如果Command 列的状态为 Sleep ，这意味着该用户连接完 MySQL 服务就没有再执行过任何命令，也就是说这是一个空闲的连接
  • 空闲连接会一直占用着吗：不会，MySQL 定义了空闲连接的最大空闲时长，由 wait_timeout 参数控制的，默认值是 8 小时(28880秒)，如果空闲连接超过了这个时间，连接器就会自动将它断开
    • 查看最大空闲时长：show variables like 'wait_timeout';
    • 手动断开连接：kill connection +id;
    • 一个处于空闲状态的连接被服务端主动断开后，这个客户端并不会马上知道，等到客户端在发起下一个请求的时候，才会收到报错

• MySQL 的连接数有限制吗：有

  • MySQL 服务支持的最大连接数由 max_connections 参数控制，默认是 151 个,超过这个值，系统就会拒绝接下来的连接请求，并报错提示
    • 查看：show variables like 'max_connections';

注意：MySQL 的连接也跟 HTTP 一样，有短连接和长连接的概念

// 短连接
连接 mysql 服务(TCP 三次握手)
执行sql
断开 mysql 服务(TCP 四次挥手)

// 长连接
连接 mysql 服务(TCP 三次握手)
执行sql
执行sql
执行sql
....
断开 mysql 服务(TCP 四次挥手)

可以看到，使用长连接的好处就是可以减少建立连接和断开连接的过程，所以一般是推荐使用长连接。

但是，使用长连接后可能会占用内存增多，因为连接过程中产生的临时数据(如缓存、会话变量、临时表等)没有得到及时释放，那么这些数据将会持续占用内存直到连接断开。如果长连接累计很多，将导致 MySQL 服务占用内存太大，有可能会被系统强制杀掉，这样会发生 MySQL 服务异常重启的现象

• 怎么解决长连接占用内存的问题？
  • 定期断开长连接
  • 客户端主动重置连接：MySQL 5.7 版本实现了 mysql_reset_connection() 函数的接口，当客户端执行了一个很大的操作后，在代码里调用 mysql_reset_connection 函数来重置连接，达到释放内存的效果。这个过程不需要重连和重新做权限验证，但是会将连接恢复到刚刚创建完时的状态

2. 查询缓存

• 连接器完成工作后，客户端就可以向 MySQL 服务发送 SQL 语句了，MySQL 服务收到 SQL 语句后，就会解析出 SQL 语句的第一个字段，看看是什么类型的语句
• 如果 SQL 是查询语句(select 语句)，MySQL 就会先去查询缓存里查找缓存数据，看看之前有没有执行过这一条命令，这个查询缓存是以 key-value 形式保存在内存中的，key 为 SQL 查询语句，value 为 SQL 语句查询的结果
• 如果查询的语句命中查询缓存，那么就会直接返回 value 给客户端。如果查询的语句没有命中查询缓存中，那么就要往下继续执行，等执行完后，查询的结果就会被存入查询缓存中

缺点：对于更新比较频繁的表，查询缓存的命中率很低的，因为只要一个表有更新操作，那么这个表的查询缓存就会被清空。如果刚缓存了一个查询结果很大的数据，还没被使用的时候，刚好这个表有更新操作，查询缓冲就被清空了，相当于白白花费时间去缓存

所有在mysql的高版本中直接将查询缓存删掉了(执行一条 SQL 查询语句，不会再走到查询缓存这个阶段了)。低版本中想要关闭查询缓存，可以将参数 query_cache_type 设置成 DEMAND

注意：高版本移除的是 server 层的查询缓存，并不是 Innodb 存储引擎中的 buffer pool

3. 执行一条 SQL 查询语句，期间发生了什么

• 连接器：建立连接，管理连接、校验用户身份
• 查询缓存：查询语句如果命中查询缓存则直接返回，否则继续往下执行。MySQL 8.0 已删除该模块
• 解析 SQL，通过解析器对 SQL 查询语句进行词法分析、语法分析(表或字段不存在时，不会报错)；然后构建语法树，方便后续模块读取表名、字段、语句类型
• 执行 SQL：执行 SQL 共有三个阶段：
  • 预处理阶段：检查表或字段是否存在；将 select * 中的 * 符号扩展为表上的所有列
  • 优化阶段：基于查询成本的考虑， 选择查询成本最小的执行计划
  • 执行阶段：根据执行计划执行 SQL 查询语句，从存储引擎读取记录，返回给客户端