表的基本操作

表结构变化之前要备份，因为数据是无法恢复的。
不是每张表都是需要主键的！
有外键关联的两张表必须使用相同的存储引擎。
自增属性默认从1开始。如果你插入的id是5，那么下次自增的id属性就是6。
主键，不允许为空。它能唯一的标识表中的一条记录
外键要么为空，要么等于另外一个表的已经存在的主键。
PRIMARY KEY 与 QNIQUE的区别：UNIQUE允许数据为空，而PRIMARY KEY不允许为空。

数据类型

DATETIME格式的时间，按实际输入的数据格式。
TIMESTAMP格式的时间，是按照UTC世界标准时间格式存储的。存储时，先把当地时区的时间换算成世界标准时间，取出的时候，再把世界标准时间换算成当地时区的时间。 当地时间 <=====> 世界标准时间

char是固定长度的字符串，字符串长度由定义的时候指明。如果插入的字符串没有达到定义的长度，那么就用空格补充。在取出的时候，会去掉尾部的所有空格。

varchar是可变长类型的字符串。字符串长度是其实际插入的字符串长度。varchar会保留住插入的字符串的空格。在取出数据的时候，也会把空格一并取出。

varchar比char节省空间，但是效率上比char要低。
如果一个字符串经常被修改，并且每次修改的长度都可能不一样，那么如果用varchar可能会造成‘行迁移’现象。这种情况下用char比较好。

行迁移：如果一块数据磁盘空间无法保存某个数据时(比如以前是1k,现在update到2k，而当前块的空闲空间不足1k),则会将新的数据保存到另外一个新的块里，然后在以前的块保存一个新位置的地址连接。

TEXT类型是可变长类型。一般用于文章内容、评论等较大文件的信息。

BLOB字段存储的是二进制数据。而TEXT存储的是非二进制字符串。

如何选择数据类型

整数和浮点数
不需要小数的情况，使用整数。需要小数的情况使用浮点数。
精度要求较高的时候，使用DOUBLE而不是FLOAT。

浮点类型有两种：单精度浮点类型FLOAT，双精度浮点类型DOUBLE。它们的区别仅在存储范围不同。分别占4,8字节。

定点类型有一种：DECIMAL。用法与FLOAT、DOUBLE一样。只不过它的占位是 M+2 字节。

浮点数与定点数
浮点数的优点在于能表示更大的数据范围，缺点是浮点数的计算容易产生误差。（有四舍五入）
浮点数 FLOAT(M,D)因为是非标准SQL定义，在数据库迁移的时候容易产生误差。

定点数因为内部使用的是字符串的形式存储的，所以适用于精度较高的场景，比如金额。另外，如果要进行数值比较，最好使用DECIMAL类型。

算术运算

除以的是0，那么返回的是NULL
与NULL运算的结果是NULL

几乎与'='运算一致，区别是 '<=>' 能用来对NULL进行判断，若两者均为NULL，则返回1，否则返回0。
<>不等于运算符，或者是 !=
语法： A IS NULL 、 B IS NOT NULL ，ISNULL(C)

SELECT
  sex,
  name,
  sex IS NULL,
  name IS NOT NULL,
  ISNULL(sex)
FROM t_char

image.png

函数

CONCAT_WS（X,S1,S2,S3），返回效果如 S1XS2XS3 。如果S1、S2、S3有NULL，那么会自动被忽略。

SELECT 
    CONCAT('%','mysql','%'), 
    CONCAT_WS('-','1996','05','19'),
    CONCAT('%',NULL,'%'),
    CONCAT_WS('-','1996','05',NULL,'19')
;

image.png

条件函数
IF(expr，V1，V2) 功能与Java的三目运算符一样，如果expr为真，返回V1，否则为V2
IFNULL(V1,V2); 如果V1是NULL，返回V2。V1不是NULL，返回V1。

SELECT 
    IF(1>0,2,3),
    IF(1<0,2,3),
    IFNULL(NULL,4),
    IFNULL(5,6)
;

image.png

CASE WHEN

     SELECT 
        CASE 1>0
            WHEN 1 THEN 'true'
            WHEN 0 THEN 'FALSE'
            ELSE        'MORE'
        END AS result;

image.png

CASE WHEN 另外一种变法。
如果没有表达式，那么将会顺序执行下面的WHEN语句，直到第一个为条件满足，返回后面的值。若没有符合的，则返回ELSE后面的值。

CASE 
                 WHEN v1 THEN r1
                 WHEN v2 THEN r2 
                ...
                 ELSE       rn
          END

 SELECT 
        CASE 
            WHEN 1>2 THEN 'A'
            WHEN 1<2 THEN 'B'
            WHEN 1<3 THEN 'C'
            ELSE          'D'
         END AS RESULT

image.png

SELECT 
USER_ACCOUNT,
(CASE 
  WHEN SEX = 1 THEN '男' 
  WHEN SEX = 2 THEN '女' 
  ELSE '未知'
END )sex
FROM m_user u 

SELECT CHAR_LENGTH('xiao da yu') AS length;   //10

有空为空
SELECT CONCAT('xiao ','da ','yu') AS myName; //xiao da yu

免疫空
SELECT CONCAT_WS("-", 'xiao', NULL, 'da', 'yu') AS myName; //xiao-da-yu

国际化金额函数。

SELECT FORMAT("123456.789",2);    
SELECT FORMAT(123456.789,2); 

image.png image.png

字符串替换。把给定字符串从【start，end】替换为给定字符串。
比如下面的例子中，从1到4，就是"XIAO"替换为"xiao"。

SELECT INSERT("XIAO da yu",1,4,"xiao") AS myName//xiao da yu

字符串定位。某个字符串首次出现的位置。有null运算返回null。

SELECT LOCATE("da",'xiao da yu') AS startIndex;

有个字符串叫："从头开始学mysql"，需求是这个字符串改为"从头开始学MySQL"。
首先要定位到"mysql"的开始位置，然后计算出"mysql"的长度。最后用"MySQL"替换"mysql"。

SELECT INSERT ('从头开始学mysql',
LOCATE('mysql','从头开始学mysql'),CHAR_LENGTH('mysql'),'MySQL')
AS blogName

大小写

SELECT LCASE('MYSQL') AS '小写' , UCASE('mysql') AS '大写'

返回前X位，后X位。

SELECT LEFT('mysql',2) AS '前两位' , RIGHT('mysql',2) AS '后两位'

image.png

去掉首位空格。LTRIM去掉左边空格，RTRIM去掉右边空格，TRIM去掉两边空格。

SELECT 
LTRIM('   mysql'),RTRIM('mysql    '), TRIM('  mysql  ')

替换

SELECT REPLACE('Mysql','sql','SQL') AS result

image.png

反转

SELECT REVERSE('LQSyM') AS result

截取

SELECT SUBSTRING("MySQL", 1, 3) AS result //MyS

连接分组下所有属性值 （NULL值会被忽略）

image.png

SELECT classId, GROUP_CONCAT(name) FROM t_student 
GROUP BY classId

image.png

MAX MIN SUM AVG ABS

提取日期时间表达式中

SELECT 
now(),
DATE(now()),
YEAR(now()),
MONTH(now()),
DAY(now()),
TIME(now())

image.png

以当前时间为基准，增加时间，减少时间

SELECT 
now(),
ADDDATE(now(), INTERVAL 1 second) '1秒后',
ADDDATE(now(), INTERVAL 1 minute) '1分后',
ADDDATE(now(), INTERVAL 1 hour)   '1小时后',
ADDDATE(now(), INTERVAL 1 day)    '1天后',
 
SUBDATE(now(), INTERVAL 2 day)    '2天前'

image.png

基于上述，来弄一个基本需求。获取前三个小时到当前时间的所有记录。

image.png

SELECT  t.* , now() AS '当前时间' FROM T_TIME_TEST t
WHERE t.TIME_STR BETWEEN SUBDATE(now(), INTERVAL 3 HOUR) AND now()

image.png

时间格式化

SELECT 
DATE_FORMAT(now(),'%Y-%m-%d-%r') AS '全',
DATE_FORMAT(now(),'%Y-%m-%d %H:%i:%s') '年月日 时分秒'

image.png

查询

DISTINCT控制的重复是DISTINCT后面所有字段！

升序与降序，究竟是哪个方向？
从第一条记录开始，往下比较指定的字段。如果越来越高，说明就是升序。
从第一条记录开始，往下比较指定的字段。越来越低，说明就是降序。
如果使用UNION ALL 可以连接多条查询语句，并将它们的结果集组合起来。
不加ALL的效果：返回结果中删除重复的记录。
将查询结果插入到表中

INSERT INTO t_person(name,address)
SELECT accountName , accountAddress FROM t_info;

-- articleId 默认值为 5
INSERT INTO t_tag(name,articleId) values ("a",null);
INSERT INTO t_tag(name) values ("a");
指明null则插入null，否则用默认值5

索引

索引是一个单独的、存储在磁盘上的数据库结构，它们包含着对数据库表里所有记录的引用指针。使用索引用于快速找出在某个或者多个列中有一特定值的行。对相关列使用索引是提高查询操作速度的最佳途径。
索引的优点：
（1）创建唯一索引，可以限制表的某列数据唯一，效果就像为那列数据加上了UNIQUE关键字一样。
（2）加快查询速度，这是创建索引最核心的原因。从4秒钟的查询时间变成了几毫秒，简直不可思议。
（3）在使用分组和排序子句进行数据查询的时候，显著减少查询中分组与排序的时间。

索引的缺点：
占空间，难维护。因为索引是存储在物理磁盘上的，所以占磁盘。在数据库中数据修改的时候，索引也会关联变动，降低了数据的维护速度，即维护索引较为耗时。

索引数量并非越多越好，因为索引占磁盘空间。且表中数据更改时，索引也会更新。就像目录一样。

对经常用于查询的字段设计索引。索引列尽可能少，避免添加不必要的字段。

数据量小的表不要用索引。就像正文没一两页，还去查目录一样。

不同值较多的列上建立索引。相反，如果列上值较少，比如“男、女”，加了索引只会降低数据更新速度。

在频繁进行排序与分组的表上建立索引。如果排序列有多个，可以建立组合索引。

当唯一性是某种数据的特征时，可以指定唯一索引。

尽量使用短索引。对字符串类型的字段进行索引，如果可能应该指定一个前缀长度。比如在CHAR(255)的列，如果在前10个或者30个字符，多数值是唯一的，则不需要对整个列进行索引。

分类
普通索引：MySQL的基本索引，索引列可以插入空值与重复值。

唯一索引：索引列的值必须唯一，但允许为空。效果就像为列加上了UNIQUE关键字。
主键是一种特殊的唯一索引，不允许为空值。

单列索引：一个索引只包含一个列。

组合索引：在多个字段组合上创建的索引，只有在查询条件中使用了这些字段的最左边字段时，索引才会被使用。这个原则称为"最左前缀"。

type字段类型为varchar，而SQL中是数字类型，所以不走索引。因此，大家也注意一下。需要把 2 修改为 字符串2："2"。

视图

视图是一个虚拟的表，它是把数据库中的一张或者多张表中的一些数据列，拼接起来的虚拟的表。
视图的好处：看到的就是需要的。另外，当视图表的某张数据来源表的表结构变化，只要那列数据不变，即使真实表的位置发生变化，也不影响视图。

视图就是已经编译好的SQL。它把多张表里面的一些数据抽取出来，拼接成一张新的虚拟的表，这张虚拟的表里只有我们最想要的数据，这样就很好的保护了基本表中的其它数据。

对视图增加或者删除记录，实际上是对基本表增加或者删除记录。
如果往视图中插入的数据不包括基本表中非空的数据，那么插入失败。

（1）视图是已经编译好的SQL语句，是基于SQL语句的结果集的可视化表。

（2）视图是逻辑上的存在，它是查看数据的一种办法，它不占磁盘。而表占物理磁盘。

（3）视图的建立和删除只影响视图本身，不影响对应的基本表。

（4）视图可以防止用户接触数据表，因为视图是虚拟的表，用户不知道基本表的表结构。

UPDATE stu_teac_info set stuIdCard = 320103 WHERE stuName = '大宇';

执行完毕后，查看视图，视图中的相关数据已经被修改了。 再查看基本表，基本表中的数据也被修改了。