6 篇博文 含有标签「MySQL」

背景：因为日常工作和学习中难免会与数据库打交道，其中如何快速的从庞大的数据库中精准的查找到我们想要的信息一直是很热的话题，所以写下此篇笔记，亦在不断地积累有关数据库查询优化方面的经验，从而能高效的使数据传递给外界，给用户更好的体验，这篇文章会一直更新下去。

or与union的执行效率比较​

stackoverflow链接: https://stackoverflow.com/questions/13750475/sql-performance-union-vs-or）

当SQL语句有多个or语句时，可以考虑使用union或者union all代替来提高速度。使用or的SQL语句往往无法进行优化，导致速度变慢。但这不是固定的，有时候使用or速度会更快些。具体情况还要经过测试为准。如果加索引的话，也可能实现速度优化。

实验表格如下,实际数据有2,000,000条，从里面返回大约最多1000行左右的数据。

or语句（部分节选）

SELECT * FROM tablename where (cdp= 300 and inline=301) or (cdp= 301 and inline=301) or (cdp= 302 and inline=301) or (cdp= 303 and inline=301) or (cdp= 304 and inline=301) or (cdp= 305 and inline=301) or (cdp= 306 and inline=301) or (cdp= 307 and inline=301)

union all语句（部分节选）

SELECT * FROM tablename where (inline= 300 and cdp=300) union all SELECT * FROM tablename where (inline= 301 and cdp=300) union all SELECT * FROM tablename where (inline= 302 and cdp=300) union all SELECT * FROM tablename where (inline= 303 and cdp=300) 返回不规则的900条数据，前者用了60多秒，后者用了8秒左右。

总结：

1. Union：对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序； 即：去重+排序
2. Union All：对两个结果集进行并集操作，包括重复行，不进行排序； 即：不去重+不排序
3. 对于单列来说，用or是没有任何问题的，但是or涉及到多个列的时候，每次select只能选取一个index，如果选择了area，population就需要进行table-scan，即全部扫描一遍，但是使用union就可以解决这个问题，分别使用area和population上面的index进行查询。 但是这里还会有一个问题就是，UNION会对结果进行排序去重，可能会降低一些performance(这有可能是方法一比方法二快的原因），所以最佳的选择应该是两种方法都进行尝试比较。

2023/02/09

shell脚本批量插入mock数据​

shell脚本

#!/bin/bash
echo "请输入字段servnumber的值："
read serber
echo "请输入创建sql语句的数量："
read number
# char=`head /dev/urandom | tr -dc 0-9 | head -c 11`
for (( i=0;i<$number;i++ ))
        do
        pass=`head /dev/urandom | tr -dc a-z | head -c 8`
        let serber=serber+1
        echo "insert into test(id,username,servnumber,password,createtime) values('$i','user${i}','${serber}','$pass',now());" >>sql1.txt
   
     done

尽量避免使用select *from ，尽量精确到想要的结果字段。 尽量避免条件使用or。 记得加上limit 限制行数，避免数据量过大消耗性能。 使用模糊查询时，%放在前面是会使索引失效。 要小心条件字段类型的转换。

2023/02/11

线上调优-慢查询日志配置​

最好 nginxi访问日志，流量重放在测试环境中；迫不得已再线上调

• 第一步：查看是否已经开启了慢查询日志

mysql> show variables like 'slow%';
+---------------------+--------------------------------------+
| Variable_name       | Value                                |
+---------------------+--------------------------------------+
| slow_launch_time    | 2                                    |
| slow_query_log      | OFF                                  |
| slow_query_log_file | /data/mydata/jachen-public-slow.log |
+---------------------+--------------------------------------+

• 第二步：开启慢查询日志

set global slow_query_log = on ;

日志路径也可以自定义：

set global slow_query_log_file = '路径';

• 第三步：查看慢查询的时间临界值

show variables like '%long%';

• 第四步：设置慢查询的时间标准

set long_query_time=0.4;

• 注意：假如运行时间正好等于long_query_time的情况，并不会被记录下来。也就是说，在mysql源码里是判断大于long_query_time，而非大于等于。

永久生效的设置方法：修改配置文件 vi /etc/my.cnf
[mysqld]
slow_query_log = 1
long_query_time = 0.1
slow_query_log_file =/usr/local/mysql/mysql_slow.log

最后重新连接才能生效，不必重启服务器！

执行耗时sql：

SELECT * FROM emp;
SELECT * FROM emp WHERE deptid > 1;

查询慢查询记录数：

SHOW GLOBAL STATUS LIKE '%Slow_queries%'; 

查询日志：

vim /var/lib/mysql/bogon-slow.log

mysqldumpslow分析工具：

在生产环境中，如果要手工分析日志，查找、分析SQL，显然是个体力活，MySQL提供了日志分析工具mysqldumpslow。退出mysql命令行，执行以下命令：

-- 查看mysqldumpslow的帮助信息
mysqldumpslow --help

-- 工作常用参考
-- 1.得到返回记录集最多的10个SQL
mysqldumpslow -s r -t 10 /var/lib/mysql/bogon-slow.log
-- 2.得到访问次数最多的10个SQL
mysqldumpslow -s c -t 10 /var/lib/mysql/bogon-slow.log
-- 3.得到按照时间排序的前10条里面含有左连接的查询语句
mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/bogon-slow.log
-- 4.另外建议在使用这些命令时结合 | 和more 使用 ，否则有可能出现爆屏情况
mysqldumpslow -s r -t 10 /var/lib/mysql/bogon-slow.log | more

• -a: 将数字抽象成N，字符串抽象成S

• -s: 是表示按照何种方式排序

• • c: 访问次数
    • l: 锁定时间
    • r: 返回记录
• • t: 查询时间
  • al:平均锁定时间
  • ar:平均返回记录数
  • at:平均查询时间

• -t: 即为返回前面多少条的数据

• -g: 后边搭配一个正则匹配模式，大小写不敏感的

2023/02/15

sql语句执行过程解析​

介绍如何开启性能详情

• 第一步：查看性能详情是否开启

mysql> show variables like '%profiling%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| have_profiling         | YES   |
| profiling              | OFF   |
| profiling_history_size | 15    |
+------------------------+-------+

• 第二步：开启性能记录功能

set profiling = on ;

• 第三步：查看性能的记录

mysql> show profiles;
+----------+------------+---------------------------------------------------+
| Query_ID | Duration   | Query                                             |
+----------+------------+---------------------------------------------------+
|        1 | 0.00177775 | show variables like '%profiling%'                 |
|        2 | 0.00037900 | select * from test where id='087878'              |
|        3 | 0.34618025 | select * from test where servnumber='1367008787'  |
|        4 | 0.31986825 | select * from test where servnumber='13670087879' |
+----------+------------+---------------------------------------------------+

• 第四步：查看语句的执行性能详情

mysql> show profile for query 4;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000100 |
| checking permissions | 0.000010 |
| Opening tables       | 0.000023 |
| init                 | 0.000045 |
| System lock          | 0.000015 |
| optimizing           | 0.000016 |
| statistics           | 0.000028 |
| preparing            | 0.000020 |
| executing            | 0.000006 |
| Sending data         | 0.319489 |
| end                  | 0.000037 |
| query end            | 0.000012 |
| closing tables       | 0.000012 |
| freeing items        | 0.000040 |
| cleaning up          | 0.000017 |
+----------------------+----------+

性能线程的详细解释官方文档链接：https://dev.mysql.com/doc/refman/5.7/en/general-thread-states.html

2023/02/16

设计表的NULL值问题​

1、字段不添加null 都是not null，默认值

2、把字段都设置为not null ，添加特殊字段，把数据存储j为JSON格式

2023/02/18

关联查询优化​

1、保证被驱动表的JOIN字段已经创建了索引 2、需要JOIN 的字段，数据类型保持绝对一致。 3、LEFT JOIN 时，选择小表作为驱动表，大表作为被驱动表 。减少外层循环的次数。 4、INNER JOIN 时，MySQL会自动将小结果集的表选为驱动表 。选择相信MySQL优化策略。 5、能够直接多表关联的尽量直接关联，不用子查询。(减少查询的趟数) 6、不建议使用子查询，建议将子查询SQL拆开结合程序多次查询，或使用 JOIN 来代替子查询。 7、衍生表建不了索引

2023/02/19

子查询优化​

使用连接（JOIN）查询来替代子查询。**连接查询不需要建立临时表 ，其速度比子查询 要快 ，如果查询中使用索引的话，性能就会更好. 例如：尽量不要使用NOT IN 或者 NOT EXISTS，用LEFT JOIN xxx ON xx WHERE xx IS NULL替代

2023/02/22

数据库表设计​

• 字段长度：能使用int就不要使用varchar、char，能用varchar(16)就不要使用varchar(256)
• 字段选择：固定长度的类型最好使用char,能使用tinyinti就不要使用int
• 默认值：最好给每个字段个默认值，最好不能为null,即not null default
• 适当索引：为每个表创建合理的索引，如唯一索引组合索引的场景以及普通索引的场景

2023/02/28

背景：我们通常理解的mysql5.7过渡到8.0是由于查询缓存很鸡肋，要保证每次查询都是相同的sql查询语句，命中率自然会很低。但是真的只是因为这个原因吗？那么我要是在高并发下请求同一sql的场景这显然也是存在的，那为什么还要去掉这层缓存呢？

官方描述​

MySQL 8.0 在缓存方面也进行了一些改变，以下是一些主要变化：

1. 自适应哈希索引：MySQL 8.0 引入了自适应哈希索引，用于在内存中缓存查询结果。这种索引可以根据查询的频率和模式自动调整大小，从而提高查询性能。
2. 持久化内存引擎：MySQL 8.0 引入了 InnoDB 持久化内存引擎，这是一种将数据存储在内存中的引擎。与传统的内存引擎不同，持久化内存引擎可以将数据持久化到磁盘上，以避免数据丢失。
3. 更好的查询缓存：MySQL 8.0 引入了更好的查询缓存机制，用于缓存查询结果。与之前的版本不同，MySQL 8.0 不再使用全局查询缓存，而是改为使用基于查询语句的缓存。
4. 更好的内存管理：MySQL 8.0 改进了内存管理，使得内存使用更加高效。这包括改进了内存分配器、提供了更好的内存监控和警告功能等。

其中第三点提到了 MySQL 8.0 引入了更好的查询缓存机制，用于缓存查询结果，这与之前的版本不同，MySQL 8.0 不再使用全局查询缓存，而是改为使用基于查询语句的缓存。这里我们具体展开来看看这个变化。

在之前的 MySQL 版本中，查询缓存是一种可用于缓存 SELECT 语句的结果集的机制。这个机制通过在内存中缓存查询结果集，以便在以后执行相同的查询时可以直接从缓存中读取结果集，而无需执行查询。这种机制可以大大提高查询性能，特别是在有大量重复查询的情况下。

然而，MySQL 8.0 中的查询缓存与之前版本不同，它不再使用全局查询缓存，而是改为使用基于查询语句的缓存。具体来说，每个查询都会被单独缓存，并且只有相同的查询（包括查询语句和参数）才能从缓存中获取结果集。这种机制可以避免之前版本中遇到的一些问题，比如全局查询缓存锁和内存分配问题。

另外，MySQL 8.0 还支持对查询缓存进行更细粒度的控制，包括可以对某些查询禁用查询缓存，可以在查询语句中指定查询结果集是否需要被缓存等。

虽然 MySQL 8.0 引入了基于查询语句的缓存机制，但是需要注意的是，查询缓存并不总是对性能有益。对于一些查询频率低、数据更新频繁的情况，缓存可能会浪费内存，而不是提高性能。因此，需要根据具体情况来评估是否使用查询缓存。

探究去掉查询缓存的原因​

我们先来看看mysql5.7服务端获取客户端请求的基本流程，查询缓存是在解析与优化模块的开始位置，也就意味着它是以全局查询缓存存在的，那么这样有什么弊端呢？它能否保证高并发写的读写一致呢？

管理策略层面的缺失​

在高并发下查询同一个sql语句select *from user where id = 1;那么存在查询缓存的确可以提高查询的效率，这是我们普遍认为的正常情况。但是在不正常的情况下是会出大问题的，如果我们已经在内存中的数据在磁盘中进行了update修改，我们就必须要使我们缓存中的数据更新，保证数据的一致性，但是在mysql5.7中，对内存是没有管理策略的，内存中数据的生效、失效、过期都没有进行标记。那么如果不能保证缓存数据的一致性就会查到脏数据，如果我们是在对数据一致性要求不高的场景下的程序，那么使用查询缓存是不影响的，总不能正在玩着游戏，你停机游戏对数据进行更新？

底层有完善的缓存机制​

我们的排序，大数据量的查找，order by ，join操作等，join是左表去匹配右表，左表拿出一行数据到右表一一对比，这个比的过程是绝对不会从磁盘中一一拿出来比的，它底层会把数据加载到内存的缓冲区buffer中，再进行比较。

处理连接的瓶颈中并发连接数会有限制，比如500个线程，虽然它从缓存内存中取很快，但是对于select语句，只要做好优化，比如创建好索引，在磁盘中取也是不慢的，而且mysql底层的磁盘是有机的组合的，并且在高频的访问中，它也会有读buffer，甚至还存在写buffer，如果读buffer中的数据没有被修改，它每次也是在内存中读的，也不会在磁盘中读，所以就没必要再加查询缓存了。

查询缓存容量小，存在瓶颈，且命中率也很低​

这个缓存机制是由一系列小缓存组成的。比如表缓存，记录缓存，key缓存，权限缓存等 。只有相同的SQL语句才会命中查询缓存。两个查询请求在任何字符上的不同（例如：空格、注释、大小写），都会导致缓存不会命中。在两条查询之间 有 INSERT 、 UPDATE 、 DELETE 、 TRUNCATE TABLE 、 ALTER TABLE 、 DROP TABLE 或 DROP DATABASE 语句也会导致缓存失效，所以在MySQL 8之后就抛弃了这个功能。

如果一张表中有8000万条数据，查询缓存显然就会力不从心，如果只存热点数据呢？即便是使用lru算法，我们也无法确定已经存在mysql的缓存数据，即便我们缓存了10w条数据，但是我们不知道是8000w条中的哪一个，就会发生缓存穿透，无法命中。加缓存集群更是无从谈起，所以mysql8.0就直接废弃了。

替代方案​

应用层组织缓存，最简单的是使用redis,ehcached等

总结​

缓存的意义在于快速查询提升系统性能，可以灵活控制缓存的一致性 8.0之前让DBA一直禁用的mysql缓存的限制：

1. MySQL基本没有手段灵活的管理缓存失效和生效，尤其对于频繁更新的表；
2. SQL必须完全一致才会导致cache命中；
3. 为了节省内存空间，太大的result set不会被cache(query_cache_limit)；
4. MySQL缓存在分库分表环境下是不起作用的；
5. 执行SQL里有触发器，自定义函数时，MySQL缓存也是不起作用的；
6. 在表的结构或数据发生改变时，基于该表相关cache.立即全部失效。

背景：今天在复习事务的隔离级别的时候总结了一些新的想法，之前只知道事务的隔离级别分为读未提交、读已提交、可重复读、序列化四个隔离级别，而在并发运行中，出现的隔离级别中的脏读、幻读和不可重复读的区别，以及他们的触发条件不是很清，经过了一番折腾，这里总结了一些想法。

1、那么究竟什么是脏读、幻读和不可重复读呢？​

• 脏读：一个事务读取了另一个事务未提交数据；
• 不可重复读：同一个事务中前后两次读取同一条记录不一样。因为被其他事务修改了并且提交了。
• 幻读：一个事务读取了另一个事务新增、删除的记录情况，记录数不一样，像是出现幻觉。

2、什么是事务的隔离级别呢？如何查看隔离级别？(dos 命令)​

事务隔离是数据库处理的基础之一。隔离是首字母缩略词 ACID中的 I ；隔离级别是在多个事务同时进行更改和执行查询时微调性能与结果的可靠性、一致性和可再现性之间的平衡的设置。

/*
mysql支持四个隔离级别：
read-uncommitted：会出现脏读、不可重复读、幻读
read-committed：可以避免脏读，会出现不可重复读、幻读
repeatable-read：可以避免脏读、不可重复读、幻读。但是两个事务不能操作（写update,delete）同一个行。
serializable：可以避免脏读、不可重复读、幻读。但是两个事务不能操作（写update,delete）同一个表。

修改隔离级别：
set transaction_isolation='隔离级别';  
#mysql8之前 transaction_isolation变量名是 tx_isolation

查看隔离级别：
select @@transaction_isolation; 默认为：repeatable-read可重复读
*/

数据库提供的 4 种事务隔离级别：

总结：

1. 脏读：是在读未提交时发生的，事务A读取了事务B还未提交的数据，是一种错误。
2. 幻读：发生在读未提交、读已提交和可重复读级别都会出现，它强调的是记录数不一样，即增删的情况。事务A读取了事务B已提交的数据，发生了幻读，它不是错误。
3. 不可重复读：发生在读未提交和读已提交的隔离级别，它强调的是数据内容发生改变，即修改的情况。事务A读取了事务B已提交的数据，事务A前后两次读取同一条记录不一样，它也不是错误。

总结了数据查询相关的主要方法，在工作中经常用到下面先引入我们的数据：

/*创建部门表*/
CREATE TABLE dept(
    deptnu      INT  PRIMARY KEY comment '部门编号',
    dname       VARCHAR(50) comment '部门名称',
    addr        VARCHAR(50) comment '部门地址'
);
某个公司的员工表
CREATE TABLE employee(
    empno       INT  PRIMARY KEY comment '雇员编号',
    ename       VARCHAR(50) comment '雇员姓名',
    job         VARCHAR(50) comment '雇员职位',
    mgr         INT comment '雇员上级编号',
    hiredate    DATE comment '雇佣日期',
    sal         DECIMAL(7,2) comment '薪资',
    deptnu      INT comment '部门编号'
)ENGINE=MyISAM DEFAULT CHARSET=utf8;
/*创建工资等级表*/
CREATE TABLE salgrade(
5.1 mysql查询子句之一where条件查询 
简介：详解where条件下的各种查询
简单查询
精确条件查询
模糊条件查询
    grade       INT  PRIMARY KEY comment '等级',
    lowsal      INT comment '最低薪资',
    higsal      INT comment '最高薪资'
);
/*插入dept表数据*/
INSERT INTO dept VALUES (10, '研发部', '北京');
INSERT INTO dept VALUES (20, '工程部', '上海');
INSERT INTO dept VALUES (30, '销售部', '广州');
INSERT INTO dept VALUES (40, '财务部', '深圳');
/*插入emp表数据*/
INSERT INTO employee VALUES (1009, '唐僧', '董事长', NULL, '2010-11-17', 50000,  10);
INSERT INTO employee VALUES (1004, '猪八戒', '经理', 1009, '2001-04-02', 29750, 20);
INSERT INTO employee VALUES (1006, '猴子', '经理', 1009, '2011-05-01', 28500, 30);
INSERT INTO employee VALUES (1007, '张飞', '经理', 1009, '2011-09-01', 24500,10);
INSERT INTO employee VALUES (1008, '诸葛亮', '分析师', 1004, '2017-04-19', 30000, 20);
INSERT INTO employee VALUES (1013, '林俊杰', '分析师', 1004, '2011-12-03', 30000, 20);
INSERT INTO employee VALUES (1002, '牛魔王', '销售员', 1006, '2018-02-20', 16000, 30);
INSERT INTO employee VALUES (1003, '程咬金', '销售员', 1006, '2017-02-22', 12500, 30);
INSERT INTO employee VALUES (1005, '后裔', '销售员', 1006, '2011-09-28', 12500, 30);
INSERT INTO employee VALUES (1010, '韩信', '销售员', 1006, '2018-09-08', 15000,30);
INSERT INTO employee VALUES (1012, '安琪拉', '文员', 1006, '2011-12-03', 9500,  30);
INSERT INTO employee VALUES (1014, '甄姬', '文员', 1007, '2019-01-23', 7500, 10);
INSERT INTO employee VALUES (1011, '妲己', '文员', 1008, '2018-05-23', 11000, 20);
INSERT INTO employee VALUES (1001, '小乔', '文员', 1013, '2018-12-17', 8000, 20);
/*插入salgrade表数据*/
INSERT INTO salgrade VALUES (1, 7000, 12000);
INSERT INTO salgrade VALUES (2, 12010, 14000);
INSERT INTO salgrade VALUES (3, 14010, 20000);
INSERT INTO salgrade VALUES (4, 20010, 30000);
INSERT INTO salgrade VALUES (5, 30010, 99990);

一、where条件查询​

1、简单查询​

select * from employee;
select empno,ename,job as ename_job from employee;

2、精确条件查询​

select * from employee where ename='后裔';
select * from employee where sal != 50000;
select * from employee where sal <> 50000;
select * from employee where sal > 10000;

3、模糊条件查询​

show variables like '%aracter%'; 
select * from employee  where ename like '林%';

4、范围查询​

select * from employee where sal between 10000 and 30000; 
select * from employee where hiredate between '2011-01-01' and '2017-12-1';

5、离散查询​

select * from employee where ename in ('猴子','林俊杰','小红','小胡');  

6、消除重复值​

select distinct(job) from employee;

7、统计查询（聚合函数）​

count(code)或者count(*)
select count(*) from employee;
select count(ename) from employee;
        
sum()  计算总和 
select sum(sal) from employee;
        
max()    计算最大值
select * from employee where sal= (select  max(sal) from employee);
        
avg()   计算平均值
select avg(sal) from employee;
        
min()   计算最低值
select * from employee where sal= (select  min(sal) from employee);
        
concat函数： 起到连接作用
select concat(ename,' 是 ',job) as aaaa from employee;

二、group by分组查询（分组）​

1、作用：把行 按 字段 分组

2、语法：group by 列1，列2....列n

3、适用场合：常用于统计场合，一般和聚合函数连用

eg：
select deptnu,count(*) from employee group by deptnu;

select deptnu,job,count(*)from employee group by deptnu,job;

select job,count(*) from employee group by job;

三、having条件查询（筛选）​

1、作用：对查询的结果进行筛选操作

2、语法：having 条件 或者 having 聚合函数 条件

3、适用场合：一般跟在group by之后

eg:
select job,count(*) from employee group by job having job ='文员';

select  deptnu,job,count(*) from employee group by deptnu,job having count(*)>=2;

select  deptnu,job,count(*) as 总数 from employee group by deptnu,job having 总数>=2

四、order by排序查询（排序）​

1、作用：对查询的结果进行排序操作

2、语法：order by 字段1,字段2 .....

3、适用场合：一般用在查询结果的排序

eg:
select * from employee order by sal;

select * from employee order by hiredate;

# 倒序	
select  deptnu,job,count(*) as 总数 from employee group by deptnu,job having 总数>=2 order by deptnu desc;

# 正序
select  deptnu,job,count(*) as 总数 from employee group by deptnu,job having 总数>=2 order by deptnu asc;

select  deptnu,job,count(*) as 总数 from employee group by deptnu,job having 总数>=2 order by deptnu;

顺序：where ---- group by ----- having ------ order by

五、limit限制查询（限制）​

1、作用：对查询结果起到限制条数的作用

2、语法：limit n，m n:代表起始条数值，不写默认为0；m代表：取出的条数

3、适用场合：数据量过多时，可以起到限制作用

eg:
    select * from XD.employee limit 4,5;

六、exist型子查询​

用法： 1、exists型子查询后面是一个受限的select查询语句 2、exists子查询，如果exists后的内层查询能查出数据，则返回 TRUE 表示存在；为空则返回 FLASE则不存在。

分为俩种：exists跟 not exists

select 1 from employee where 1=1;
select * from 表名 a where exists (select 1 from 表名2 where 条件);

eg:查询出公司有员工的部门的详细信息

select * from dept a where exists (select 1 from employee b where a.deptnu=b.deptnu);

select * from dept a where not exists (select 1 from employee b where a.deptnu=b.deptnu);

七、左连接查询与右连接查询​

用法和应用场景： 1、左连接称之为左外连接 右连接称之为右外连接 这俩个连接都是属于外连接 2、左连接关键字：left join 表名 on 条件 / left outer 表名 join on 条件 右连接关键字：right join 表名 on 条件/right outer 表名 join on 条件 3、左连接说明： left join 是left outer join的简写，左(外)连接，左表(a_table)的记录将会全部表示出来， 而右表(b_table)只会显示符合搜索条件的记录。右表记录不足的地方均为NULL。 4、右连接说明：right join是right outer join的简写，与左(外)连接相反，右(外)连接，左表(a_table)只会显示符合搜索条件的记录，而右表(b_table)的记录将会全部表示出来。左表记录不足的地方均为NULL。

eg:列出部门名称和这些部门的员工信息，同时列出那些没有的员工的部门
dept，employee

select a.dname,b.* from dept a  left join employee b on a.deptnu=b.deptnu;

select b.dname,a.* from employee a  right join  dept b on b.deptnu=a.deptnu;

八、内连接查询与联合查询​

语法和应用场景： 1、内连接：获取两个表中字段匹配关系的记录 2、主要语法：INNER JOIN 表名 ON 条件;

eg:想查出员工张飞的所在部门的地址

select a.addr  from dept a inner join employee b on a.deptnu=b.deptnu and b.ename='张飞';

select a.addr from dept a,employee b where a.deptnu=b.deptnu and b.ename='张飞';

3、联合查询：就是把多个查询语句的查询结果结合在一起 主要语法1：... UNION ... （去除重复） 主要语法2：... UNION ALL ...（不去重复） 4、union查询的注意事项：

(1)两个select语句的查询结果的“字段数”必须一致；
(2)通常，也应该让两个查询语句的字段类型具有一致性；
(3)也可以联合更多的查询结果；
(4)用到order by排序时，需要加上limit（加上最大条数就行），需要对子句用括号括起来

eg:对销售员的工资从低到高排序，而文员的工资从高到低排序
(select * from employee a where a.job = '销售员'  order by a.sal limit 999999 ) union  (select * from employee b where b.job = '文员' order by b.sal desc limit 999999);

主要介绍了对库、表的基本操作和mysql基本数据类型

1、mysql库的创建、查看以及使用/切换​

• 创建数据库space1

  create database space1;
  

• 查看当前在哪个数据库

  select database;
  

• 进入数据库

  use 库名;
  

• 判断是否存在，如果不存在则创建数据库space2

  create database if not exists space2;
  

• 创建数据库并指定字符集为GBK

  create database space3 default character set gbk; 
  

• 查看某个库是什么字符集

  show create database space1; 
  

• 查看当前mysql使用的字符集

  show variables like 'character%';
  

2、mysql表的数据类型​

<1>整数型
 类型     大小    范围（有符号）     范围（无符号unsigned）    用途
TINYINT  1字节   (-128，127)       (0，255)            小整数值
SMALLINT 2字节   (-32768，32767)   (0，65535)          大整数值
MEDIUMINT 3字节  (-8388608，8388607)  (0，16777215)    大整数值
INT   4字节  (-2147483648，2147483647) (0，4294967295) 大整数值
BIGINT    8字节    （）             (0，2的64次方减1)   极大整数值

<2>浮点型
 FLOAT(m,d）  4字节   单精度浮点型  备注：m代表总个数，d代表小数位个数
 DOUBLE(m,d） 8 字节  双精度浮点型  备注：m代表总个数，d代表小数位个数
 
 <3>定点型
 DECIMAL(m,d）   依赖于M和D的值    备注：m代表总个数，d代表小数位个数
 
 <4>字符串类型 
 类型          大小              用途
 CHAR          0-255字节         定长字符串
 VARCHAR       0-65535字节       变长字符串
 TINYTEXT      0-255字节         短文本字符串
 TEXT          0-65535字节       长文本数据
 MEDIUMTEXT    0-16777215字节    中等长度文本数据
 LONGTEXT      0-4294967295字节  极大文本数据
 
char的优缺点：存取速度比varchar更快，但是比varchar更占用空间
varchar的优缺点：比char省空间。但是存取速度没有char快
 
 <5>时间型
 数据类型    字节数     格式                  备注
 date        3     yyyy-MM-dd            存储日期值
 time        3      HH:mm:ss             存储时分秒
 year        1       yyyy                存储年
 datetime    8    yyyy-MM-dd HH:mm:ss    存储日期+时间
 timestamp   4  yyyy-MM-dd HH:mm:ss     存储日期+时间，可作时间戳
         
 create table test_time (
 date_value date,
 time_value time,
 year_value year,
 datetime_value datetime,
 timestamp_value timestamp
 ) engine=innodb charset=utf8;
 
insert into test_time values(now(),now(),now(),now(),now());

3、mysql表的创建​

• 语法：（注意空格位）

  CREATE TABLE 表名 (    
  字段名1 字段类型1 约束条件1 说明1,   
  字段名2 字段类型2 约束条件2 说明2,
  字段名3 字段类型3 约束条件3 说明3
  );
                  
  create table 新表名 as select * from 旧表名 where 1=2;(注意：建议这种创建表的方式用于日常测试，因为可能索引什么的会复制不过来)
  
  create table 新表名 like 旧表名;
  

• 约束条件：

  comment         ----说明解释
  not null        ----不为空
  default         ----默认值
  unsigned        ----无符号（即正数）
  auto_increment  ----自增
  zerofill        ----自动填充
  unique key      ----唯一值
  

• 创建sql：

  CREATE TABLE student (
  
  id tinyint(5) zerofill auto_increment not null comment '学生学号',
  
  name varchar(20) default null comment '学生姓名',
  
  age  tinyint  default null comment '学生年龄',
  
  class varchar(20) default null comment '学生班级',
  
  sex char(5) not null comment '学生性别',
  
  unique key (id)
  
  )engine=innodb charset=utf8;;
                      
  
  
  CREATE TABLE student (
  
  id tinyint(5)  auto_increment default null comment '学生学号',
  
  name varchar(20) default null comment '学生姓名',
  
  age  tinyint  default null comment '学生年龄',
  
  class varchar(20) default null comment '学生班级',
  
  sex char(5) not null comment '学生性别',
  
  unique key (id)
                      
  )engine=innodb charset=utf8;;
  

4、mysql表的查看​

• 查看数据库中的所有表：show tables；
• 查看表结构：desc 表名;
• 查看创建表的sql语句：show create table 表名;
• \G :有结束sql语句的作用，还有把显示的数据纵向旋转90度
• \g :有结束sql语句的作用
• 复制A表结构创建e表： create table e as select *from A;或者写成create table e like A;（不包含表数据）

5、mysql表的维护和删除​

• 修改表名

   rename table 旧表名 to 新表名;
   
   rename table student to user;
  

• 添加列

  给表添加一列：alter table 表名 add 列名 类型;
  
  alter table user add addr varchar(50);
  
  alter table add 列名 类型 comment '说明';
  
  alter table user add famliy varchar(50) comment '学生父母';
  
  给表最前面添加一列：alter table 表名 add 列名 类型 first;
  
  alter table user add job varchar(10) first;
  
  给表某个字段后添加一列：alter table 表名 add 列名 类型 after 字段名;
  
  alter table user add servnumber int(11)  after id;
  
  注意：没有给表某个字段前添加一列的说法。
  

• 修改列类型

  alter table 表名 modify 列名 新类型;
  
  alter table user modify servnumber varchar(20);
  

• 修改列名

  alter table 表名 change 旧列名 新列名 类型;
  
  alter table user change servnumber telephone varchar(20);
  

• 删除列

  alter table 表名 drop 列名;
  
  alter table user drop famliy;
  

• 修改字符集

  alter table 表名 character set 字符集;
  
  alter table user character  set GBK;
  

• 表的删除

  drop table 表名；
  drop table user;
  
  看表是否存在，若存在则删除表：drop table if exists 表名;
  drop table  if exists teacher;
  

mysql表数据的插入、删除、修改以及中文编码问题

1、mysql新增表数据​

普通的插入表数据

insert into 表名（字段名） values（字段对应值）;

insert into employee (empno,ename,job,mgr,hiredate,sal,deptnu) values ('1000','小明','经理','10001','2019-03-03','12345.23','10');
insert into 表名 values（所有字段对应值）;

# 一次性插入多个数据
insert into employee  values ('1001','小明','经理','10001','2019-03-03','12345.23','10');

蠕虫复制（将一张表的数据复制到另一张表中）

insert into 表名1 select * from 表名2;

insert into 表名1（字段名1，字段名2） select 字段名1，字段名2 from 表名2;

insert into emp (empno,ename) select empno,ename from employee;

建表复制

create table 表名1 as select 字段名1，字段名2 from 表名2;

create table emp as select empno ,ename from employee;

创建sql

某个公司的员工表
CREATE TABLE employee(
empno INT PRIMARY KEY comment '雇员编号',
ename VARCHAR(20) comment '雇员姓名',
job VARCHAR(20) comment '雇员职位',
mgr INT comment '雇员上级编号',
hiredate DATE comment '雇佣日期',
sal DECIMAL(7,2) comment '薪资',
deptnu INT comment '部门编号'
);

2、mysql表数据的修改和删除​

表数据的修改/更新：

update 表名 set 字段名1=值1 where 字段名=值;

update 表名 set 字段名1=值1,字段名2=值2 where 字段名=值;

删除：

delete from 表名 where 字段名=值;
 truncate table 表名;
 delete from 表名;
 drop table 表名;
 
面试时：面试官问在删改数据之前，你会怎么做？
答案：会对数据进行备份操作，以防万一，可以进行数据回退

面试时：面试官会问，delete与truncate与drop 这三种删除数据的共同点都是删除数据，他们的不同点是什么?
delele 会把删除的操作记录给记录起来，以便数据回退，不会释放空间，而且不会删除定义。
truncate不会记录删除操作，会把表占用的空间恢复到最初，不会删除定义
drop会删除整张表，释放表占用的空间。

删除速度：
drop > truncate > delete

3、乱码问题​

查看当前mysql使用的字符集：show variables like 'character%';

character_set_client：客户端请求数据的字符集 character_set_connection：客户端与服务器连接的字符集 character_set_database：数据库服务器中某个库使用的字符集设定，如果建库时没有指明，将默认使用配置 上的字符集 character_set_results：返回给客户端的字符集(从数据库读取到的数据是什么编码的) character_set_server：为服务器安装时指定的默认字符集设定。 character_set_system：系统字符集(修改不了的，就是utf8) character_sets_dir：mysql字符集文件的保存路径

临时：set names gbk;

永久：修改配置文件my.cnf里边的

[client]
default-character-set=gbk
作用于外部的显示
[mysqld]
character_set_server=gbk
作用于内部，会作用于创建库表时默认字符集

修改库的字符集编码

alter database xiaoxiao default character set gbk;

修改表的字符集编码

alter table employee default character set utf8;