sql语句总结

叁歲伎倆 2022-04-17 01:46 311阅读 0赞

### 目录： ###

1.  sql
2.  语法
3.  数据类型
4.  数据库
5.  表
6.  数据
7.  进阶
8.  函数
    
    1.  字符串函数：[MySQL常用字符串函数 - GeaoZhang - 博客园][MySQL_ - GeaoZhang -]
        
        ![d414d15d6a8eeb6c005258326a9d5ca9.png][]
    2.  时间
        
        1.  sql函数 求两个日期的间隔 返回分钟数
            
                sql函数 求两个日期的间隔，返回分钟数
                如： '2013-05-30 11:00:00' 与 '2013-05-30 11:30:30'
                在mysql中可以用:
                round(TIME_TO_SEC(TIMEDIFF('2013-05-30 11:30:30', '2013-05-30 11:00:00')) /60)
                
                例：
                select round(TIME_TO_SEC(TIMEDIFF('2013-05-30 11:30:30', '2013-05-30 11:00:00')) /60);
                select round(TIME_TO_SEC(TIMEDIFF('11:30:30', '11:00:00')) /60);
                select round(TIME_TO_SEC(TIMEDIFF('11:30', '11:00')) /60);
        2.  待写

###  内容： ###

1.  SQL（结构化查询语言）是用于访问和操作数据库中的数据的标准数据库编程语言。 
2.  语法
    
    1.  SQL语句总是以关键字开始。
    2.  SQL语句以分号结尾。
    3.  SQL不区分大小写，意味着update与UPDATE相同
3.  通用数据类型：[MySQL 数据类型 | 菜鸟教程][MySQL _ _]
    
    1.  数值  
        整型、浮点型（n）,n表示固定长度
    2.  String类型  
        char、varchar
    3.  日期和时间  
        date、datetime、timestamp...  
        创建数据表的时候：时间用datetime、timestamp中的哪种存储呢？  
        花3分钟时间看这篇就明白：[MySQL 中 datetime 和 timestamp 的区别与选择][MySQL _ datetime _ timestamp]
4.  数据库
    
    1.  创建：create语句
    2.  删除：drop语句
    3.  选择：use语句
    4.  数据库列表：show databases;
    5.  数据表列表：show tables;
5.  表
    
    1.  MYSQL数据库类型与JAVA类型对应表：[Java数据类型和MySql数据类型对应表 - 向南君 - 博客园][Java_MySql_ - _ -]  
        mysql 中 tinyint 对应 java 中的数据类型byte，取值范围 -128~127，够用了。但是在 set 方法赋值的时候发现，用 byte 要强转一下，所以不建议在mysql中使用tinyint，推荐使用int 或 integer 。
    2.  创建：create
        
            1.创建表
            2.创建列名 列属性 列大小限制
            3.哪些需要自增 非空 默认default
            4.末尾添加约束（check、unique、主键、外键）
                如
                    检查    check(0<age<150 and/or ...)
                    唯一    unique(tel,name)
                    主键    primary key(id,column_name)
                    外键    foreign key(teacher_id) references teacher(tid)
                为约束命名
                    检查    constraint ch_name check  (sex='女' or sex='男'),
                    唯一    constraint un_name unique (tel,name),
                    主键    constraint pk_name primary key (P_id,lastName),
                    外键    constraint fk_name foreign key (T_id) references Teacher(Tid)
            5.哪些列可以设置为索引
                    索引    index index_name (column_name)
            6.非必要
                设置存储引擎     engine=innodb
                设置自增持       auto_increment=1
                设置默认字符类型  default charset=utf8[utf8与utf8mb4（utf8 most bytes 4）]以后尽量用utf8mb4
                设置数据库校对规则(常用的有3种)  utf8mb4_general_ci（默认） 
                    1.utf8mb4_bin的比较方法其实就是直接将所有字符看作二进制串，然后从最高位往最低位比对。所以很显然它是区分大小写的
                    2.utf8mb4_unicode_ci和utf8mb4_general_ci对于中文和英文来说，其实是没有任何区别的。对于我们开发的国内使用的系统来说，随便选哪个都行。
                    3.
                设置ROW_FORMAT = Dynamic：
                    在mysql中， 若一张表里面不存在varchar、text以及其变形、blob以及其变形的字段的话，那么张这个表其实也叫静态表，即该表的row_format是Fixed，就是说每条记录所占用的字节一样。其优点读取快，缺点浪费额外一部分空间。
                    若一张表里面存在varchar、text以及其变形、blob以及其变形的字段的话，那么张这个表其实也叫动态表，即该表的row_format是Dynamic，就是说每条记录所占用的字节是动态的。其优点节省空间，缺点增加读取的时间开销。所以，做搜索查询量大的表一般都以空间来换取时间，设计成静态表。
        
        [MYSQL中的COLLATE是什么？][MYSQL_COLLATE][https://www.cnblogs.com/qcloud1001/p/10033364.html例如：utf8\_bin 区分大小写][MYSQL_COLLATE]  
        ![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lpZ3VhbmdfODIw_size_16_color_FFFFFF_t_70][]  
        ![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lpZ3VhbmdfODIw_size_16_color_FFFFFF_t_70 1][]  
        [mysql 索引中的USING BTREE 的意义][mysql _USING BTREE]：[mysql 索引中的USING BTREE 的意义 - 浮.尘 - 博客园][mysql _USING BTREE]  
          
        模板1：int长度没有指定时，默认长度为11位，但是int最大的值为2 147 483 647，只有10位，所以不能设置默认（这是错误的 ，10并不是代表这个意思，不设置最好）
        
            CREATE TABLE `table_name` (
              `id` varchar(255) NOT NULL,
              `permission_id` varchar(255) DEFAULT NULL COMMENT '权限id',
              `icon_selected` varchar(255) DEFAULT NULL COMMENT '选中',
              `type` int DEFAULT '1' COMMENT '0：菜单 1:权限',
              `sort` int DEFAULT NULL COMMENT '排序',
              `is_delete` int DEFAULT NULL COMMENT '是否删除0:正常   1：删除',
              `create_time` datetime DEFAULT NULL COMMENT '创建时间',
              `update_time` datetime DEFAULT NULL COMMENT '修改时间',
              PRIMARY KEY (`id`) USING BTREE
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT "活动记录表";
        
        模板2：创建自增表：
        
            CREATE TABLE `table_name` (
              `id` int NOT NULL AUTO_INCREMENT,
              PRIMARY KEY (`id`)
            ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC  COMMENT "活动记录表";
        
        模板3：时间设置默认当前时间
        
            CREATE TABLE `user` (
              `id` BIGINT NOT NULL,
              `username` varchar(255) NOT NULL COMMENT '账号',
              `password` varchar(255) NOT NULL COMMENT '密码',
              `name` varchar(255) DEFAULT NULL COMMENT '姓名',
              `clinic_id` BIGINT DEFAULT NULL COMMENT '诊所id',
              `user_number` varchar(255) DEFAULT NULL COMMENT '员工号',
              `gender` int DEFAULT 0 COMMENT '性别 0未知、1男、2女',
              `birthday` date DEFAULT NULL COMMENT '出生日期',
              `post` int DEFAULT NULL COMMENT '岗位 0未知、1医生、2护士、3前台、4客服、5行政、6市场、7财务、8总经理',
              `department` int DEFAULT NULL COMMENT '科室 0未知、待写',
              `phone` varchar(255) DEFAULT NULL COMMENT '手机号',
              `id_card` varchar(255) DEFAULT NULL COMMENT '身份证号',
              `nickname` varchar(255) DEFAULT NULL COMMENT '昵称',
              `doctor_certificate_no` varchar(255) DEFAULT NULL COMMENT '医师执业证书号',
              `email` varchar(255) DEFAULT NULL COMMENT '邮箱',
              `home_address` varchar(255) DEFAULT NULL COMMENT '家庭地址',
              `work_status` varchar(255) DEFAULT NULL COMMENT '工作状态 1在职、2离职',
              `work_id` BIGINT default NULL COMMENT '工作诊所id',
              `roles` varchar(255) DEFAULT NULL COMMENT '角色',
              `unit` varchar(255) DEFAULT NULL COMMENT '单位',
              `unit_price` decimal(11,2) DEFAULT NULL COMMENT '单价',
              `num` int DEFAULT NULL COMMENT '数量',
              `price` decimal(11,2) DEFAULT NULL COMMENT '金额',
              `discount` varchar(255) DEFAULT NULL COMMENT '折扣',
              `is_delete` int DEFAULT NULL COMMENT '是否删除 0正常、1删除',
              `create_time` timestamp DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
              `update_time` timestamp DEFAULT CURRENT_TIMESTAMP COMMENT '修改时间',
              PRIMARY KEY (`id`) USING BTREE
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT "用户表";
        
        使用：  
        timestamp，默认为当前时间
        
            `post_time` timestamp NULL DEFAULT NULL COMMENT '上传时间', // 默认为null,和其他数据类型设置默认为null有点不同
              `create_time` timestamp DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', // 默认为当前时间
              `update_time` timestamp DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', // 默认为当前时间
        
        datetime，默认为当前时间
        
            `start_time` datetime DEFAULT NULL COMMENT '开始时间', // 默认为null
              `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '注册时间', // 默认为当前时间
              `update_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '修改时间', // 默认为当前时间
        
        说明：
        
        1.  CHARSET
        2.  COLLATE
        3.  ROW\_FORMAT=DYNAMIC
        4.  PRIMARY KEY (\`id\`) USING BTREE：分为两种b-tree和 hash，有序用b-tree，无序用hash
        5.  \`id\` unsign int NOT NUL  
            id int：id的存放是int类型id int unsigned：id的存放是int类型，但是不能为负数。auto\_increment：自动编码，从正数开始，不会有负数，使用其的列不能插入数据。
        6.  时间datetime、timestamp用哪个最好？  
            花3分钟时间看这篇就明白：[MySQL 中 datetime 和 timestamp 的区别与选择][MySQL _ datetime _ timestamp]  
            结果：数据库用timestamp，java用LocalDateTime
        7.  金额应该用哪种方式存储？  
            1.\`unit\_price\` decimal(11,2) DEFAULT NULL COMMENT '单价'  
            2.整型int，以分为单位  
            3.字符串  
            哪种好？
        8.  CURRENT\_TIMESTAMP 和 ON UPDATE CURRENT\_TIMESTAMP
            
                  create_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
                  update_time timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
                  update_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            
            ON UPDATE CURRENT\_TIMESTAMP 含义：  
            表示在数据库数据有更新的时候UPDATE\_TIME的时间会自动更新（如果数据库数据值没有变化的话，UPDATE\_TIME是不会自动更新的）。
        9.  ...
    3.  删除
        
        1.  表彻底删除
        2.  表数据删除，空间结构保留
            
                彻底删除：           drop table table_name;
                仅删除数据，保留空间： truncate table table_name;
    4.  修改：ALTER TABLE 语句用于在现有表中添加、删除或修改列。（修改列包括列名、数据类型、数据大小、约束、索引）
        
            1.已经创建的表：
            添加列：            alter table table_name add column column_name datatype(大小);
                                alter table table_name add column good_name varchar(500) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT '商品名称';
                                alter table table_name add column pay_price decimal(11,2) DEFAULT NULL COMMENT '支付原价';
            删除列：            alter table table_name drop column column_name;
            修改列:
                修改列名：       alter table table_name change column column_name new_name datatype(大小);
                修改数据类型：    alter table table_name modify column column_name datatype;
                修改数据大小：    alter table table_name modify column column_name datatype(大小);
                给已经存在的主键添加自增：（不是主键不能设置自增）
                    第一步：给 id 增加auto_increment 属性
                    alter table tablename modify id int(11)  auto_increment;
                    第二步：给自增值设置初始值
                    alter table bu_staff  auto_increment=10000; 
            
                为约束命名   
                        非空约束：   alter table table_name modify column column_name datatype not null;
                        default约束：alter table table_name modify column column_name datatype default 1;
                        check约束：  alter table table_name add check(column_name);
                        unique约束： alter table table_name add unique(column_name);
                        主键约束：    alter table table_name add primary key(column_name);
                        外键约束：    alter table table_name add foreign key(P_Id) references table_name(column_name);
            
                给多列添加约束：
                        check约束:    alter table table_name add constraint 约束名 check(condition1 and condition2);
                        unique约束：  alter table table_name add constraint 约束名 unique(column1, column2...); 
                        主键约束:     alter table table_name add constraint 约束名 primary key(column1, column2...);
            
                        
                给列撤销约束：
                        撤销check约束： alter table table_name drop check 约束名;
                        撤销unique约束：alter table table_name drop index 约束名;
                        撤销主键约束：  alter table table_name drop primary key;
                        撤销外键约束：  alter table table_name drop foreign key 外键名；
            
            2.修改表名：  alter table table_name rename new_name;
6.  数据：增删改查操作
    
    1.  批量插入
        
            INSERT INTO t 
            VALUES
              (1, 20, 'a'),
              (2, 26, 'b'),
              (3, 30, 'c');
    2.  增insert
        
        1.  INSERT INTO 语法
            
                第一种：insert into table1 values(null,123,"name","123456");
                第二种：insert into table1(id,number,name,password) values(null,123,"name","123456");
                注意：使用value和values有时候没有区别，有时候有区别，建议使用values
        2.  INSERT INTO SELECT 语法：可以将所有列从一个表中复制到另一个已经存在的表中：
            
                第一种：INSERT INTO table2 SELECT * FROM table1;    将所有列从一个表中复制到另一个已经存在的表中
                第二种：Insert into Table2(field1,field2,...) select value1,value2,... from Table1  把想要的列复制到另一个现有的表中
        3.  SELECT INTO 语句（mysql不支持）
            
                select * from table1 into table2;
    3.  删delete
        
        1.  delete from table\_name where 条件约束。
    4.  改update
        
        1.  update table\_name set 列名=“新值”  where 条件约束。
        2.  update cloud\_gift.goods t1,cloud\_gift.tb\_live\_apply\_goods t2 set t2.goods\_name=t1.goods\_name,t2.goods\_img=t1.goods\_img,t2.price=t1.price,t2.pay\_price=t1.pay\_price where t2.goods\_id=t1.id; 
    5.  查询子句
        
        1.  选择不同：SELECT DISTINCT语句用于仅返回不同的（different）值。
        2.  where
        3.  与或非：and、or、not
        4.  between...and...
        5.  按关键字排序：order by asc（升序）、desc（降序）  
            在SQL中order by null有什么用吗？这是我在一次面试时面试官问我的问题，当时我是懵的。他让我猜一下，我说不排序？没想到被我猜对了
            
            　　不排序你就别用order by啊！为什么要用order by null？
            
            我们看一下下面这条SQL分析
            
            explain select \* from sc group by sc.Sid
            
            在extra中我们看到 using filesort 外部排序，这肯定是影响SQL性能的。尤其是在用到group by时，即使你不需要排序，MySQL会默认根据分组字段order by。
            
            其实，你根本就不需要排序，无辜拉低了你的SQL性能。此时，就可以考虑一下order by null：
            
            explain select \* from sc group by sc.Sid order by null
            
            可以看到外部排序没有了。（即使select得到的数据只有一条，MySQL仍然会using filesort。）
        6.  limit（分页查询），limit n；(（表示返回n条数据），limit（m,n）;表示从第m条数据开始，返回n条数据。
        7.  in：在大数据的情况下效率低
            
            1.  IN运算符允许您在WHERE子句中指定多个值。
            2.  IN运算符是多个OR条件的简写。
        8.  like（模糊查询）和Wildcards通配符  
            有两个通配符与LIKE运算符一起使用：
            
            1.  `％` - 百分号表示零个，一个或多个字符
            2.  `_` - 下划线表示单个字符
            3.  通配符也可以组合使用
        9.  group by：GROUP BY 语句根据一个或多个列对结果集进行分组。
        10. having子句：WHERE 子句对被选择的列施加条件，而 HAVING 子句则对 GROUP BY 子句所产生的组施加条件。
            
                select from
                where 
                group by
                having
                order by
        11. Join连接
            
            1.  **INNER JOIN**：如果表中有至少一个匹配，则返回行
            2.  **LEFT JOIN**：即使右表中没有匹配，也从左表返回所有的行
            3.  **RIGHT JOIN**：即使左表中没有匹配，也从右表返回所有的行
            4.  **FULL JOIN**：只要其中一个表中存在匹配，则返回行
            5.  自连接：
                
                    SELECT column_name(s)
                    FROM table1 T1, table1 T2
                    WHERE condition;
7.  进阶
    
    1.  别名  
        mysql不同于oracle,不能使用关键字作为别名,建议更换为其他字段;  
        如果非要使用,请这样使用:
        
            select t.name as `key` from test t;
    2.  子查询
    3.  union：联合
        
        1.  UNION中的每个SELECT语句必须具有相同的列数
        2.  这些列也必须具有相似的数据类型
        3.  每个SELECT语句中的列也必须以相同的顺序排列
    4.  撤销数据库、表、列、约束、索引：drop
        
            撤销数据库：     drop database database_name;
            撤销表：         drop  table table_name;
            撤销列：         alter table table_name drop column_name;
            撤销约束（主键）：alter table table_name drop primary key;
            撤销索引：       alter table table_name drop index index_name;
    5.  null 和空值（也即是空字符串''），两者是互不冲突的
        
        1.  存储
            
            1.  空值是不占用空间的，注意空值的''之间是没有空格的。
            2.  mysql中的NULL其实是占用空间的，NULL 值代表遗漏的未知数据。下面是来自于MYSQL官方的解释  
                “NULL columns require additional space in the row to record whether their values are NULL. For MyISAM tables, each NULL column takes one bit extra, rounded up to the nearest byte.”  
                打个比方来说，你有一个杯子，空值代表杯子是真空的，NULL代表杯子中装满了空气，虽然杯子看起来都是空的，但是区别是很大的。
        2.  查询、插入
            
            1.  查询
                
                1.  null：null 不能使用任何运算符与其他字段或者变量（函数、存储过程）进行运算。若使用运算数据就可能会有问题。
                    
                    1.  **ISNULL:** 当列的值是 NULL,此运算符返回 true。
                        
                            //以下3种方式作用相同
                            select * from teacher where ISNULL(name);
                            select * from teacher where ISNULL(name)=0;
                            select * from teacher where ISNULL(name)=false;
                            
                            //以下2种方式作用相同
                            select * from teacher where ISNULL(name)=1;
                            select * from teacher where ISNULL(name)=true;
                    2.  **IS NOT NULL:** 当列的值不为 NULL, 运算符返回 true。
                    3.  **<=>:** 比较操作符（不同于 = 运算符），当比较的的两个值相等或者都为 NULL 时返回 true。
                        
                            select * from teacher where name is not null;
                            select * from teacher where ISNULL(name);
                            select * from teacher where name<=>null;
                    4.  当使用 IS NOT NULL 或者 IS NULL 时，只能查出字段中没有不为NULL的或者为 NULL 的，不能查出空值。
                    5.  注意：关于 NULL 的条件比较运算是比较特殊的。你不能使用 = NULL 或 != NULL 在列中查找 NULL 值 。因为在 MySQL 中，NULL 值与任何其它值的比较（即使是 NULL）永远返回 NULL，即 NULL = NULL 返回 NULL ，也就是false。  
                        MySQL 中处理 NULL 使用 IS NULL 和 IS NOT NULL 运算符。![20200917095907417.png][]
                        
                            select * from teacher where age = null;
                        
                        ![2020091710573222.png][]
                2.  空值使用 =，!=，<> 等算术运算符来判定
                3.  同时剔除null 和 空字符串 
                    
                    select \* from user where ISNULL(name)=0 and LENGTH(trim(name))>0;
                    
                    ![format_png][]
            2.  插入：字段类型是not null，为什么我可以插入空值
                
                    CREATE TABLE  `test` (  
                     `col1` VARCHAR( 10 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,  
                     `col2` VARCHAR( 10 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL  
                    ) ENGINE = MYISAM ;
                
                插入数据：
                
                    INSERT INTO `test` VALUES (null,1);
                
                mysql发生错误：
                
                    #1048 - Column 'col1' cannot be null
                
                再来一条
                
                    INSERT INTO `test` VALUES ('',1);
                
                成功插入数据，可见，NOT NULL 的字段是不能插入“NULL”的，只能插入“空值”。
        3.  COUNT 和 IFNULL函数
            
            1.  count函数  
                使用 COUNT(字段) 统计会过滤掉 NULL 值，但是不会过滤掉空值。  
                **例：**  
                ![20200917095907417.png][]  
                ![20200917095923745.png][]
            2.  `IFNULL`函数：IFNULL有两个参数。 如果第一个参数字段不是`NULL`，则返回第一个字段的值。 否则，`IFNULL`函数返回第二个参数的值（默认值）。
        4.  对于`MySQL`特殊的注意事项，对于`timestamp`数据类型  
            如果往这个数据类型插入的列插入`NULL`值，则出现的值是当前系统时间。  
            插入空值，则会出现 `0000-00-00 00:00:00`
        5.  数据库优化
            
            1.  Mysql难以优化引用可空列查询，它会使索引、索引统计和值更加复杂。可空列需要更多的存储空间（见上面的e文），还需要mysql内部进行特殊处理。可空列被索引后，每条记录都需要一个额外的字节，还能导致MYisam 中固定大小的索引变成可变大小的索引。
            2.  尽量避免NULL：应该指定列为NOT NULL，除非你想存储NULL。在mysql中，含有空值的列很难进行查询优化。因为它们使得索引、索引的统计信息以及比较运算更加复杂。你应该用0、一个特殊的值或者一个空串代替空值。
            3.  为什么not null的效率比null高  
                对于问题2，上面我们已经说过了，NULL 其实并不是空值，而是要占用空间，所以mysql在进行比较的时候，NULL 会参与字段比较，所以对效率有一部分影响，而且B树索引时不会存储NULL值的，所以如果索引的字段可以为NULL，索引的效率会下降很多。
            4.  实际到底是使用NULL值还是空值('')，根据实际业务来进行区分。个人建议在实际开发中如果没有特殊的业务场景，可以直接使用空值。
            5.  **索引字段说明**
                
                看到网上有一些人说： **MySql中如果某一列中含有NULL，那么包含该列的索引就无效了。**
                
                给`one` 和`two` 字段分别加上普通索引。之前有写过，在复习添加索引：[Mysql索引整理总结][Mysql]
                
                    -- ALTER TABLE table_name ADD INDEX index_name(col_name);
                    ALTER TABLE tb_test ADD INDEX index_oat (one, two);
                    ALTER TABLE tb_test add INDEX index_two(two);
                
                使用 `show keys from 表名;`或`show indexes from 表名;` ，查看这个表的所有索引信息。
                
                一个普通索引，一个复合索引。
                
                **复合索引遵守“最左前缀”原则**，**即在查询条件中使用了复合索引的第一个字段，索引才会被使用**。因此，在复合索引中索引列的顺序至关重要。
                
                ![format_png 1][]
                
                可以看到，创建了两个索引，并且`index_tow` NULL 那一列是 YES。
                
                使用`EXPLAIN` 来进行演示说明，`EXPLAIN` 的使用说明：[Mysql中explain用法和结果字段的含义介绍][Mysql_explain]
                
                `复合索引`
                
                ![format_png 2][]
                
                `普通索引`
                
                ![format_png 3][]
                
                发现查询`two`字段 是可以正常使用索引的。我使用的MYSQL 5.7 ,**InnoDB** 引擎。也看了一些网上的资料，[MySQL中NULL对索引的影响][MySQL_NULL] 这个文章中用例子验证，**MySQL可以在含有null的列上使用索引**。
                
                **备注**：可能是其他条件下不行，看网上资料说使用`空间索引`会失效，具体我没有去验证，空间索引没有用到过。查询官网[create-index-spatial][]，感兴趣的伙伴可以自行验证。
                
                ![format_png 4][]
                
                这里我想到一点，很多问题的答案都是在指定的条件和环境下才成立，多质疑，多验证。
                
                **小总结** ：在有NULL值得字段上使用常用的索引，如普通索引、复合索引、全文索引等不会使索引失效。在官网查看在空间索引的情况下，说明了 索引列必须为NOT NULL。
    6.  AUTO INCREMENT 字段
    7.  **drop、delete与truncate的区别**
        
        1.  **drop直接删掉表和空间； truncate会初始化表的大小，再插入时自增长id又从1开始 ；delete删除表中数据，不删除空间，可以加where字句。**
        2.  DELETE语句执行删除的过程是每次从表中删除一行，并且同时将该行的删除操作作为事务记录在日志中保存以便进行进行回滚操作。  
            TRUNCATE TABLE 则一次性地从表中删除所有的数据并不把单独的删除操作记录记入日志保存，删除行是不能恢复的。并且在删除的过程中不会激活与表有关的删除触发器。执行速度快。
        3.  delete语句为DML（data maintain Language),这个操作会被放到 rollback segment中,事务提交后才生效。如果有相应的 tigger,执行的时候将被触发。  
             truncate、drop是DLL（data define language),操作立即生效，原数据不放到 rollback segment中，不能回滚
        4.  一般而言，drop > truncate > delete  
            Truncate table 表名 速度快,而且效率高  
            truncate table 在功能上与不带 WHERE 子句的 DELETE 语句相同：二者均删除表中的全部行。但 TRUNCATE TABLE 比 DELETE 速度快，且使用的系统和事务日志资源少。DELETE 语句每次删除一行，并在事务日志中为所删除的每行记录一项。TRUNCATE TABLE 通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放。
        5.  应用范围。TRUNCATE 只能对TABLE；DELETE可以是table和view
        6.   注意：在没有备份情况下，谨慎使用 drop 与 truncate。要删除部分数据行采用delete且注意结合where来约束影响范围。回滚段要足够大。要删除表用drop;若想保留表而将表中数据删除，如果于事务无关，用truncate即可实现。如果和事务有关，或老是想触发trigger,还是用delete。
    8.  别名：SQL 别名（Aliases），表别名、列别名
        
        1.  SQL 别名用于为表或表中的列提供临时名称。 
        2.  SQL 别名通常用于使列名更具可读性。 
        3.  SQL 一个别名只存在于查询期间。
    9.  6个约束：创建表的时候创建约束、创建表之后为列添加约束或者重新添加一个列设置约束、撤销约束
        
        1.  not  null 约束
        2.  default 约束
        3.  unique 约束
        4.  check 约束
        5.  primary key 约束
        6.  foreign key 约束
    10. 克隆数据表
        
            第一步：获取表1的完整数据结构
                show create table table1_name;
            第二步：根据表1的数据结构创建表2
            第三步：将表1的数据插入到表2
                insert into table2_name select * from table1_name;
    11. 临时表：temporary    
        些关系型数据库管理系统支持临时表。临时表是一项很棒的特性，能够让你像操作普通的 SQL 数据表一样，使用 SELECT、UPDATE 和 JOIN 等功能来存储或者操作中间结果。  
        临时表有时候对于保存临时数据非常有用。有关临时表你需要知道的最重要的一点是，它们会在当前的终端会话结束后被删除。
        
        1.  创建  
            CREATE TEMPORARY TABLE SALESSUMMARY(....像创建普通表一样创建它)：
        2.  删除  
            DROP TABLE SALESSUMMARY;
    12. sql注入：所谓SQL注入，就是通过把SQL命令插入到Web表单递交或输入域名或页面请求的查询字符串，最终达到欺骗服务器执行恶意的SQL命令。  
        防止SQL注入，我们需要注意以下几个要点：
        
        1.  永远不要信任用户的输入。对用户的输入进行校验，可以通过正则表达式，或限制长度。
        2.  永远不要使用动态拼装sql，可以使用参数化的sql，比如在jdbc中可以使用prepareStatement.进行参数设置。
    13. 索引
        
        1.  作用：索引是为了提高查询效率。  
            索引就相当于字典中的拼音、笔画、偏旁部首等排序的目录（索引），可以快速查找到需要的字。拼音目录就是聚集索引，笔画、偏旁部首就是辅助索引。一个表只能有一个聚集索引，如果有主键，则主键列就是聚集索引。  
            创建索引时，你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。  
            实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。  
              
            上面都在说使用索引的好处，但过多的使用索引将会造成滥用。因此索引也会有它的缺点：虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。  
            建立索引会占用磁盘空间的索引文件。
        2.  使用：
            
            1.  创建索引需要使用 create index 语句，该语句允许对索引命名，指定要创建索引的表以及对哪些列进行索引，还可以指定索引按照升序或者降序排列。  
                同 UNIQUE 约束一样，索引可以是唯一的。这种情况下，索引会阻止列中（或者列的组合，其中某些列有索引）出现重复的条目。
            2.  删除索引：索引可以用 SQL **DROP** 命令删除。删除索引时应当特别小心，数据库的性能可能会因此而降低或者提高。  
                删除索引：alter table table\_name drop index index\_name;
        3.  分类：
            
            1.  单列索引：create index index\_name on table\_name(column\_name);
            2.  聚簇索引：create index index\_name on table\_name(column1\_name,column2\_name);
            3.  唯一索引：create unique index index\_name on table\_name(column\_name);
            4.  隐式索引：隐式索引由数据库服务器在创建某些对象的时候自动生成。例如，对于主键约束和唯一约束，数据库服务器就会自动创建索引。  
                  
                注意：alter table table\_name add index(column\_name);也可以创建索引
        4.  优点和缺点：索引能够提高 SELECT 查询和 WHERE 子句的速度，但是却降低了包含 UPDATE 语句或 INSERT 语句的数据输入过程的速度。索引的创建与删除不会对表中的数据产生影响。
        5.  注意：什么时候应当避免使用索引？  
            尽管创建索引的目的是提升数据库的性能，但是还是有一些情况应当避免使用索引。下面几条指导原则给出了何时应当重新考虑是否使用索引：
            
            1.  小的数据表不应当使用索引；
            2.  需要频繁进行大批量的更新或者插入操作的表；
            3.  如果列中包含大数或者 NULL 值，不宜创建索引；
            4.  频繁操作的列不宜创建索引。
    14. 序列
8.  函数
    
    1.  if函数
        
        1.  IFNULL() 函数用于判断第一个表达式是否为 NULL，如果为 NULL 则返回第二个参数的值，如果不为 NULL 则返回第一个参数的值。
            
                select id,IFNULL(batch_id,100) from tb_order where order_number='1598239861895259';
        2.  IF(expr1,expr2,expr3)，如果expr1的值为true，则返回expr2的值，如果expr1的值为false，则返回expr3的值。就是一个简单的三目表达式。
            
                select id,if(total_num>0,1,0) from tb_order where order_number='1598239861895259';
            
                select id,SUM(if(total_num>0,1,0)) num from tb_order where user_id='60712db214e9470e93469135113a5ff5';
            
            1.IF函数通常与SUM()函数组合
            
                SELECT
                SUM(IF(status = 'Shipped', 1, 0)) AS Shipped,
                SUM(IF(status = 'Cancelled', 1, 0)) AS Cancelled
                FROM orders;
            
            例： 有3张表购物表（购物车表有商品id，商品skuId，如果购物车中的记录没有skuId为空字符串，则使用商品表的会员价，否则使用商品sku表的会员价)、商品表(商品id）、商品sku表（商品skuId)，需求：每次将新商品添加进购物车都要显示购物车总价格，如图  
            ![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lpZ3VhbmdfODIw_size_16_color_FFFFFF_t_70 2][]  
             
            
                select count(sc.id) counts,sum(if(sc.sku_id='',gs.employee_price*sc.buy_num,gsk.employee_price*sc.buy_num)) prices 
                from cloud_gift.pc_shopping_cart sc 
                inner join cloud_gift.goods gs on sc.goods_id=gs.id 
                left join cloud_gift.goods_sku gsk on gsk.id=sc.sku_id
                where sc.user_id='1' and sc.admin_id='28';
            
            2.IF函数与COUNT()函数组合，因为COUNT函数不计算空值，所以如果状态不在选定状态，IF函数将返回NULL，否则将返回1。
            
                SELECT
                    COUNT(IF(status = 'Cancelled', 1, NULL)) Cancelled,
                    COUNT(IF(status = 'Disputed', 1, NULL)) Disputed,
                    COUNT(IF(status = 'In Process', 1, NULL)) 'In Process',
                    COUNT(IF(status = 'On Hold', 1, NULL)) 'On Hold',
                    COUNT(IF(status = 'Resolved', 1, NULL)) 'Resolved',
                    COUNT(IF(status = 'Shipped', 1, NULL)) 'Shipped'
                FROM orders;
    2.  concat函数 、concat\_ws 函数、group\_concat 函数、repeat()函数  
        ** **[MySQL 字符串连接CONCAT()函数\_w3cschool][MySQL _CONCAT_w3cschool]
    3.  concat 和 concat\_ws  
        **查询**
        
            select id,CONCAT(spec,';',stock_num,';',price) from goods_sku where id='16404dfbabf440d3aa85c3089bdf566c';
            select id,CONCAT_ws('#',stock_num,price,spec) from goods_sku where id='16404dfbabf440d3aa85c3089bdf566c';
        
        ![2020092509050084.png][]  
        ![20200925090530441.png][]  
        应用：mybatis使用模糊查询  like concat('%',\#\{name\},'%');  
         
        
        **更新**  
        1.mysql向表中某字段后追加一段字符串
        
            update table_name set field=CONCAT(field,'',str)
        
        2.mysql 向表中某字段前加字符串
        
            update table_name set field=CONCAT('str',field)
    4.  group\_concat函数  
        完整的语法如下：  
        group\_concat(\[DISTINCT\] 要连接的字段 \[Order BY ASC/DESC 排序字段\] \[Separator '分隔符'\])
        
        表结构及数据
        
            DROP TABLE IF EXISTS `course`;
            CREATE TABLE `course` (
            `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
            `course_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL
            ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
            
            -- ----------------------------
            -- Records of course
            -- ----------------------------
            INSERT INTO `course` VALUES ('李明', '阅读赏析');
            INSERT INTO `course` VALUES ('李明', '摄影');
            INSERT INTO `course` VALUES ('李明', '太极剑');
            INSERT INTO `course` VALUES ('司马光', '马克思主义基本原理');
            INSERT INTO `course` VALUES ('司马光', '摄影');
            INSERT INTO `course` VALUES ('叶问', '阅读赏析');
            INSERT INTO `course` VALUES ('叶问', '法语');
            
            SET FOREIGN_KEY_CHECKS = 1;
        
        选修课程表
        
            select t.name as '姓名',t.course_name as '课程' from course t  ;
        
        ![format_png 5][]
        
        1.需求： 将每个人的课程合并为一行数据，逗号分隔(默认)
        
            select t.name as '姓名',GROUP_CONCAT(t.course_name )  as '课程' from course t  group by t.name ;
        
        ![format_png 6][]
        
        2.修改分隔符及排序，以name排倒序
        
            select t.name as '姓名',GROUP_CONCAT(t.course_name  order by t.course_name desc SEPARATOR '#')  as '课程' from course t  group by t.name
        
        ![format_png 7][]
        
        注意：使用 GROUP\_CONCAT（）函数必须对源数据进行分组，否则所有数据会被合并成一行
        
        1.  
    5.  case when 用法
        
        MySQL 的 case when 的语法有两种：  
        ![watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAeWlnZw_size_20_color_FFFFFF_t_70_g_se_x_16][]
        
            简单函数 
            CASE [col_name] WHEN [value1] THEN [result1]…ELSE [default] END
            搜索函数 
            CASE WHEN [expr] THEN [result1]…ELSE [default] END
        
         例子：
        
            select product_name,CASE product_type when 1 then '保健项' else '护理项' end productType,status from project;
        
            case when gsu.pay_price IS NULL then w.pay_price else gsu.pay_price end as payPrice,
            case when gsu.price IS NULL then w.price else gsu.price end as price,
            case when gsu.employee_price IS NULL then w.employee_price else gsu.employee_price end as employeePrice
    6.  format函数    
        date\_format函数：时间格式化[MySQL DATE\_FORMAT() 函数][MySQL DATE_FORMAT_]  
        如：
        
            FORMAT(Now(),'YYYY-MM-DD') as PerDate
            DATE_FORMAT(mc.create_time,'%Y-%m-%d') createTime
            DATE_FORMAT(t1.operate_time,'%Y-%m-%d %H:%i:%s')operatTime
    7.  字符串函数：[MySQL常用字符串函数 - GeaoZhang - 博客园][MySQL_ - GeaoZhang -]
        
        1.  length函数
        2.  trim函数：和length函数一起用可以判断字段值不为空值的情况
            
                where length(trim(name))>0
    8.  模糊查询4种方式
        
        1.  like语句  
            `SELECT 'column' FROM 'table' where 'condition' like "%keyword%"`
        2.  LOCATE语句  
            `SELECT 'column' from 'table' where locate('keyword', 'condition')>0`
        3.  locate 的別名 position  
            `SELECT 'column' from 'table' where position('keyword' IN 'condition')`
        4.  INSTR语句  
            `SELECT 'column' from 'table' where instr('condition', 'keyword' )>0`
            
                select * from tableName a where name like '%helloworld%';
                select * from tableName a where instr(name,'helloworld')>0;  --这两条语句的效果是一样的
        5.  总结
            
            1.  `locate`、`position` 和 `instr` 的差別只是参数的位置不同，同时`locate` 多一个起始位置的参数外，两者是一样的。
            2.  有两种用法：一种是前面参数写变量，后面写列名；还有就是位置调换。两种有不同的效果，`instr（str1，str2）`的意思是`str2`在`str1`中，如果后面写变量前面写列名，则表示搜出表中所有`str1`列中值包含`str2`变量的数据，这时候跟（`列名 like '目标表里'`）的效果是一样的；
            3.  `instr`还有一个需要注意的地方，就是对该函数返回结果值的判断，不写时默认是判断 `"> 0"`;共有三种可能，每种对应情况如下：
                
                1.  `instr(title,'name')>0` 相当于 `title like '%name%'`
                2.  `instr(title,'name')=1` 相当于 `title like 'name%'`
                3.  `instr(title,'name')=0` 相当于 `title not like '%name%'`
9.  5个对象
    
    1.  视图view
    2.  触发器tigger
    3.  储存过程procedures
    4.  事件event
    5.  自定义函数

[MySQL_ - GeaoZhang -]: https://www.cnblogs.com/geaozhang/p/6739303.html
[d414d15d6a8eeb6c005258326a9d5ca9.png]: /images/20220417/bb9480fda32740a7866d2717788d9fda.png
[MySQL _ _]: https://www.runoob.com/mysql/mysql-data-types.html
[MySQL _ datetime _ timestamp]: https://www.cnblogs.com/sanduzxcvbnm/p/12937972.html
[Java_MySql_ - _ -]: https://www.cnblogs.com/jerrylz/p/5814460.html
[MYSQL_COLLATE]: https://www.cnblogs.com/qcloud1001/p/10033364.html
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lpZ3VhbmdfODIw_size_16_color_FFFFFF_t_70]: /images/20220417/9b855cbfcd874cd0b3bce8765b7d4933.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lpZ3VhbmdfODIw_size_16_color_FFFFFF_t_70 1]: /images/20220417/06070307fa5b4936bc85ae21ee6ca61e.png
[mysql _USING BTREE]: https://www.cnblogs.com/rxbook/p/10648796.html
[20200917095907417.png]: /images/20220417/3ec0572aa0134f48b0b903ed1ccfd9cb.png
[2020091710573222.png]: /images/20220417/716f19695e234e9cb7e70691665b9642.png
[format_png]: /images/20220417/7d81be322c9a431d8101801323dc597c.png
[20200917095923745.png]: /images/20220417/f14e86fc2c014f65912a0d838992cedc.png
[Mysql]: https://blog.csdn.net/u010648555/article/details/81102957#_87
[format_png 1]: /images/20220417/6ea666e203664b469fc4a3a740d316d0.png
[Mysql_explain]: https://blog.csdn.net/u010648555/article/details/81106983#explain_39
[format_png 2]: /images/20220417/67ec6da7a9eb4c3790edc0385ce889d4.png
[format_png 3]: /images/20220417/19cca0c346d947389c33dff39d9f7380.png
[MySQL_NULL]: https://www.jianshu.com/p/3cae3e364946
[create-index-spatial]: https://dev.mysql.com/doc/refman/5.7/en/create-index.html#create-index-spatial
[format_png 4]: /images/20220417/9d959ca25e5f4e39b7ccf84267c56b07.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lpZ3VhbmdfODIw_size_16_color_FFFFFF_t_70 2]: /images/20220417/49f11d55d82c42d892cbe57418c5303d.png
[MySQL _CONCAT_w3cschool]: https://www.w3cschool.cn/mysql/5nab1ptx.html
[2020092509050084.png]: /images/20220417/288370708ec547b8abfc96ebd0cc16ff.png
[20200925090530441.png]: /images/20220417/7b09ee66fb8d480b9ea9f00eaf509621.png
[format_png 5]: /images/20220417/c5cb35da55424b448c708362a0650704.png
[format_png 6]: /images/20220417/e608ac423b1345ebb2876e740126eb52.png
[format_png 7]: /images/20220417/02c0012ff3ed4b67ac391352f63e993b.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAeWlnZw_size_20_color_FFFFFF_t_70_g_se_x_16]: /images/20220417/fdfabf6e18d745dca22888363d097fb9.png
[MySQL DATE_FORMAT_]: https://www.w3school.com.cn/sql/func_date_format.asp