1 mysql自增为AUTO_INCREMENT
mssql自增为identity
2 mysql获取日期为NOW()
mssql获取日期为getdate()
3 mysql支持LIMIT m,n(从m位置开始取n位),当ORDER BY和LIMIT同时使用时,有可能出错,解决方法,使用子查询,内部ORDER BY外面LIMIT(建议)
mssql不支持LIMIT
4 mysql调用存储过程为CALL procName(…)
mssql可以直接调用存储过程名
5 mysql存储的内容部区分大小写,但是表名和字段名区分大小写
mssql都不区分
6 mysql每个语句都以 ; 结束
7 mysql存储过程的声明如下:
DELIMITER //
CREATE PROCEDURE procName(...)
BEGIN
......
END;
//
注意前面的DELIMITER //和后面的//部分,因为mysql默认是以 ; 作为语句的结束,所以mysql遇到 ; 就会提交并执行前面的SQL语句。但是,存储过程中会有多个SQL语句,这时就不能使用 ; 作为存储过程的结束符了,这时我们就通过DELIMITER来声明单独的结束符,我们用的是//也可以是##、$$等等,我们在存储过程的开始就告诉mysql结束符变为了//,然后再在存储过程的结尾使用//告诉存储过程结束了,而不是以 ; 结束。这样,mysql就知道这是存储过程还是普通的sql语句了。
8 mysql的变量
8.1 局部变量,只在代码段中有效,代码段结束则变量失效,需要提前申明,例:
DECLARE _num INT DEFAULT 100;
8.2 用户变量,在用户状态的整个生命周期有效,即一个用户的会话周期内有效,需要使用@符号,不需要提前申明,可以直接使用,例
SET @num=999;
8.3 系统变量,对整个系统可见,需要使用@@符号,例:SET @@num=888;
8.4 全局变量,对整个系统可见,需要使用@@GLOBAL符号,例:SET @@global.num=999;
9 Mysql在SELECT中给变量赋值使用INTO关键字,例:
SELECT MAX(id) INTO @maxId FROM tab
Mssql在SELECT中给变量赋值有=号,例:
SELECT MAX(id)=@maxId FROM tab
10 Mysql可以给代码块命名,并用LEAVE跳出代码块,代码块以BEGIN关键字开始,以END关键字结束,代码块名和BEGIN之间有 : 号,例:
_code:BEGIN
SET @num=101;
IF(@num>100) THEN
LEAVE _code;
END IF;
SELECT @num; #不会执行
END _code;
11 Mysql的IF语句,例:
IF (@num>0) THEN
...
ELSEIF(@num>1) THEN
...
ELSE
...
END IF;
12 Mysql判断空为IFNULL()
Mssql判断空为ISNULL()
13 Mysql常用时间函数,例:
ADDDATE(NOW(), 3); #返回当前时间加三天后的时间
ADDTIME(NOW(),'yyyy-mm-dd hh:mm:ss'); #返回当前时间加后面的时间后的时间
DATEDIFF('2001-1-1', '2002-1-1'); #返回两个时间相差的天数
TIMESTAMPDIFF(YEAR/MONTH/DAY/HOUR/MINUTE/SECOND/..., '2001-1-1', '2002-1-1'); #返回两个时间的时间差,单位可选
14 Mysql循环,有WHILE——先判断后做,DO…WHILE——先做后判断,LOOP——循环没有判断需要自己写LEAVE,例:
_code:LOOP
IF(@num>100) THEN
LEAVE _code; #跳出循环
END IF;
SELECT @num;
END LOOP _code;
15 Mysql游标,mysql中使用LOOP循环游标比较方便,建议使用,例:
DECLARE _id INT;
DECLARE _done BOOL DEFAULT FALSE; #初始化游标标识
#声明游标
DECLARE cs CURSOR FOR
SELECT uid FROM users;
#声明游标事件在发生not found事件时的处理代码
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET _done=TRUE;
OPEN cs;
_cs:LOOP
#读取游标
FETCH cs INTO _id;
#根据游标标识判断是否跳出游标
IF(_done) THEN
LEAVE _cs;
END IF;
BEGIN
... #游标中的处理代码
END;
#重置游标标识,因为循环中有select时,not found事件会被激发
#但是这不是真的是游标终止的事件,而是select发生的事件
#所以,需要重置循环标识
SET _done=FALSE;
END LOOP _cs;
CLOSE cs; #关闭游标
16 Mysql字符串查找函数,例:
POSITION(SUBSTR IN Str);——返回SUBSTR在str中第一个出现的位置,位置从1开始
17 Mysql存储过程中的注意事项
17.1 所有的DECLARE语句必须在所有的执行语句之前,游标除外,例:
SELECT * FROM tab;
DECLARE _id INT; #这样就不行
17.2 嵌套游标使用时,嵌套的游标的标识需在嵌套游标之前重置,例:
DECLARE _id INT;
DECLARE _done BOOL DEFAULT FALSE; #初始化游标标识
#声明游标
DECLARE cs CURSOR FOR
SELECT uid FROM users;
#声明游标事件在发生not found事件时的处理代码
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET _done=TRUE;
OPEN cs;
_cs:LOOP
#读取游标
FETCH cs INTO _id;
#根据游标标识判断是否跳出游标
IF(_done) THEN
LEAVE _cs;
END IF;
#重置子游标标识
SET _doneA=FALSE;
#子游标
BEGIN
DECLARE csA CURSOR FOR
SELECT pid FROM ptab;
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET _doneA=TRUE;
OPEN csA;
....
CLOSE csA;
END;
#重置游标标识,因为循环中有select时,not found事件会被激发
#但是这不是真的是游标终止的事件,而是select发生的事件
SET _done=FALSE;
END LOOP _cs;
CLOSE cs; #关闭游标
18 数据类型转
18.1 CAST(EXP AS TYPE)
18.2 CONVERT(EXP TYPE)
18.3 TYPE可以为CHAR、DATE、TIME、DATETIME、DECIMAL、SIGNED、UNSIGNED,其它类型不支持
19 Mysql事务,START trasaction … COMMIT;
Mssql事务,BEGIN TRANSACTION…COMMIT;
20 Mysql作业未event
20.1 Event要起作用,GLOBAL event_scheduler必须为开启
20.2 查看是否开启,SHOW VARIABLES LIKE ‘event_schedeler’
20.3 设置为开启,SET GLOBAL event_schedulter=1;
20.4 建立作业,例:
DELIMITER //
CREATE event eventName #作业名
ON SCHEDULE EVERY 3 DAY|HOUR|MINUTE|.... #每3时间段(可选)执行一次
DO
BEGIN
...作业内容
END;
//
21 Mysql索引
21.1 Mysql的AUTO_INCREMENT字段必须是主键
21.2 Mysql的主键就是唯一的聚集索引,聚集索引就是主键,主键就是聚集索引。这跟mssql不一样,mssql的聚集索引不一定是主键
21.3 聚集索引决定了数据的物理存储排序,它对查询和排序非常有用,所以非常重要
21.4 索引以B+树形式存储
21.5 组合索引的列顺序非常重要,通常是查询越多的列就应该放在前面,并且第一列被称为引导列,查询时必须包括引导列索引才会被使用(这点不太确定)
21.6 联合主键时,pid为AUTO_INCREMENT,当PRIMARY KEY(pid, ptime)时,pid不会重复;当PRIMARY KEY(ptime, pid)时,pid会重复;总之,联合主键时,联合主键整体才是唯一的。
22 Mysql分区
22.1 分区类型
22.1.1 RANGE——范围分区
22.1.2 LIST——列表分区
22.1.3 HASH——哈希分区
22.1.4 KEY——键分区(特殊的HASH分区,可以直接使用列,不用管列的类型)
22.2 分区编号从0开始,必须依次递增,最多1024个分区,且分区名必须相同(区分大小写)
22.3 分区键必须非空,不能有NULL
22.4 分区表操作
22.4.1 创建分区表,例:
CREATE TABLE PartitionTest___(
pid INT AUTO_INCREMENT,
pname VARCHAR(100),
ptime DATETIME,
PRIMARY KEY(pid, ptime)
)
#分区键,必须在主键里面,auto_increment列,必须在主键里面
PARTITION BY RANGE(pid)(
PARTITION p0 VALUES LESS THAN(100),
PARTITION p1 VALUES LESS THAN(1000),
PARTITION p2 VALUES LESS THAN maxvalue #最大值
);
22.4.2 修改分区表,例:
ALTER TABLE PartitionTest___
PARTITION BY RANGE COLUMNS(ptime)( #此处有关键字columns
PARTITION p0 VALUES LESS THAN('2010-1-1'),
PARTITION p1 VALUES LESS THAN('2011-1-1')
);
22.4.3 添加分区,RANGE分区的表,只可以在分区的顶端增加分区,不能在已分区的分区内增加分区,例:
ALTER TABLE PartitionTest___
ADD PARTITION(
PARTITION p1 VALUES LESS THAN('2012-1-1')
);
22.4.4 删除分区,删除分区会删除分区中的数据,例:
ALTER TABLE PartitionTest___
DROP PARTITION p0;
22.4.5 合并相邻分区,例:
ALTER TABLE PartitionTest___
REORGANIZE PARTITION p0, p1 INTO(
PARTITION p0 VALUES LESS THAN(1000)
);
22.4.6 获取分区表信息在information_schma.partitions表中
23 Mysql的转义字符为\
24 Mysql通过PREPARE关键字执行拼接的SQL,这时存储SQL字符串的变量必须为用户变量,不能用局部变量,例:
SET @s='select * from Meta where metaId=?;';
SET @id=100;
#声明预编译sql
PREPARE ps FROM @s;
#执行预编译sql
EXECUTE ps USING @id; #using调用参数
#销毁预编译sql
DEALLOCATE PREPARE ps;
25 Mysql表的存储引擎
25.1 ENGINE=MYISAM类型,不支持事物,速度快
25.2 ENGINE=INNODB类型,支持事物,安全性高
25.3 设置表存储引擎,例:
CREATE TABLE AAA() ENGINE=INNODB;
26.1 查看执行计划,EXPLAIN 查询语句,例:
EXPLAIN SELECT * FROM Meta WHERE metaId=1;
26.2.1 Id,执行顺序,越大优先级越高,相同则从上到下排列
26.2.2 selectType,查询类型
26.2.2.1 SIMPLE,不包含子查询或UNION
26.2.2.2 PRIMARY,包含子查询或UNION
26.2.2.3 Subquery,子查询(SELECT或WHERE中的)
26.2.2.4 Derived,子查询(FROM中的)
26.2.2.5 UNION,从UNION表获取结果
26.2.3 TYPE,访问类型
26.2.3.1 ALL,全表扫描
26.2.3.2 INDEX,索引扫描
26.2.3.3 RANGE,范围扫描
26.2.3.4 Ref,非唯一索引扫描
26.2.3.5 Eq_ref,唯一索引扫描
26.2.3.6 Const,优化的
26.2.3.7 Possiblekey,可能使用的键索引
26.2.3.8 KEY,确定使用的键索引
26.2.4 Extra,额外的很重要的信息
26.2.4.1 USING INDEX,使用了覆盖索引
26.2.4.2 USING WHERE,使用了后置过滤
26.2.4.3 等等
27 Mysql锁
27.1 锁是针对不同请求对同一资源的操作而进行的限定
27.2 使用锁,GET_LOCK('锁名', 等待的时间),1、成功开启;0、等待超时;NULL、发生错误
例:SELECT GET_LOCK('mylock', 10); ——开启一个名为mylock的锁,开启的超时时间为10秒
27.3 判断锁是否被释放,IS_FREE_LOCK('锁名'),1、已经被释放;0、未被释放
例:SELECT IS_FREE_LOCK('mylock'); ——判断锁mylock释放已经被释放
27.4 释放锁,RELEASE_LOCK('锁名'),1、成功释放;0、释放不成功;NULL、不存在该锁
例:SELECT RELEASE_LOCK('mylock'); ——释放锁名为mylock的锁
Mysql边用边学学习笔记及Mysql与Mssql的区别
最新推荐文章于 2023-12-13 03:21:27 发布