6 篇博文 含有标签「数据库」

背景：我们对MongoDB采用的逻辑删除的方案，与MySQL完全不同。 得益于MongoDB擅长储存非结构化数据的优点，即使业务数据结构发生，也不会影响原来的数据，还能保证业务表查询效率。 若MySQL采用此方案，则有业务数据库表结构变动导致数据迁移失败的风险，甚至影响正常业务流程。 综合考虑，关系型数据库适合通过“删除标记”实现逻辑删除，非关系型数据库更适合将“已删除”的数据迁移至回收表中。

举个小demo

如果改变表字段：​

​ 首先导入了 MongoDB 驱动，然后创建了一个 MongoDB 客户端（MongoClient），并连接到了本地的 MongoDB 服务器（"mongodb://localhost:27017"）。通过客户端获取了数据库 "test" 中的集合 "collection"。在逻辑删除代码中，首先通过 ObjectId 创建了一个 ObjectId 对象，其值为 "5f36f47a06c5a722497f37b5"。然后，通过调用 updateOne 方法对该文档进行了逻辑删除操作，即在该文档中添加/更新了一个 "deleted" 字段，该字段的值为 true。

<dependency>
    <groupId>org.mongodb</groupId>
    <artifactId>mongodb-driver-sync</artifactId>
    <version>4.0.5</version>
</dependency>

import com.mongodb.client.MongoClient;
import com.mongodb.client.MongoClients;
import com.mongodb.client.MongoCollection;
import com.mongodb.client.model.Filters;
import com.mongodb.client.model.Updates;
import org.bson.Document;
import org.bson.types.ObjectId;

public class MongoDBLogicDeleteExample {
    public static void main(String[] args) {
        // 创建 MongoDB 客户端
        MongoClient mongoClient = MongoClients.create("mongodb://localhost:27017");

        // 获取数据库和集合
        MongoCollection<Document> collection = mongoClient.getDatabase("test").getCollection("collection");

        // 逻辑删除
        ObjectId id = new ObjectId("5f36f47a06c5a722497f37b5");
        collection.updateOne(Filters.eq("_id", id), Updates.set("deleted", true));

        // 关闭 MongoDB 客户端
        mongoClient.close();
    }
}

​ 然后在查询代码中，通过调用 find 方法查询 "deleted" 字段不等于 true 的文档，即查询未被逻辑删除的文档。然后，将查询结果存入 documents 集合中。最后，通过循环打印出了查询结果中的每一个文档。最后，关闭了 MongoDB 客户端。

import com.mongodb.client.MongoClient;
import com.mongodb.client.MongoClients;
import com.mongodb.client.MongoCollection;
import com.mongodb.client.model.Filters;
import org.bson.Document;

import java.util.ArrayList;
import java.util.List;

public class MongoDBQueryExample {
    public static void main(String[] args) {
        // 创建 MongoDB 客户端
        MongoClient mongoClient = MongoClients.create("mongodb://localhost:27017");

        // 获取数据库和集合
        MongoCollection<Document> collection = mongoClient.getDatabase("test").getCollection("collection");

        // 查询
        List<Document> documents = new ArrayList<>();
        collection.find(Filters.ne("deleted", true)).into(documents);

        // 打印结果
        for (Document document : documents) {
            System.out.println(document);
        }

        // 关闭 MongoDB 客户端
       

不改变表字段​

如果不想添加字段，可以使用 MongoDB 的另一种实现逻辑删除的方法，即使用软删除。软删除的思想是将文档移动到另一个集合中，而不是真正删除文档。

import com.mongodb.MongoClient;
import com.mongodb.client.MongoCollection;
import com.mongodb.client.MongoDatabase;
import org.bson.Document;
import org.bson.types.ObjectId;

import java.util.ArrayList;
import java.util.List;

public class LogicalDeletionExample {
    public static void main(String[] args) {
        // 创建 MongoDB 客户端
        MongoClient mongoClient = new MongoClient("mongodb://localhost:27017");

        // 获取数据库
        MongoDatabase database = mongoClient.getDatabase("test");

        // 获取原始集合
        MongoCollection<Document> collection = database.getCollection("collection");

        // 获取新集合
        MongoCollection<Document> deletedCollection = database.getCollection("deleted_collection");

        // 逻辑删除
        ObjectId id = new ObjectId("5f36f47a06c5a722497f37b5");
        Document deletedDocument = collection.find(new Document("_id", id)).first();
        deletedCollection.insertOne(deletedDocument);
        collection.deleteOne(new Document("_id", id));

        // 查询
        List<Document> documents = new ArrayList<>();
        collection.find().into(documents);

        // 打印结果
        for (Document document : documents) {
            System.out.println(document);
        }

        // 关闭客户端
        mongoClient.close();
    }
}

以上代码通过将文档移动到新集合中，实现了 MongoDB 的逻辑删除，并且不添加字段。

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

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

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

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;