mySql常用sql语句

/*
utf-8编码可能2个字节、3个字节、4个字节的字符，但是MySQL的utf8编码只支持3字节的数据，而移动端的表情数据是4个字节的字符。如果直接往采用utf-8编码的数据库中插入表情数据则会报错
可以对4字节的字符进行编码存储( 存之前先用base64格式化例 base64_encode($teststr) )，然后取出来的时候，再进行解码( base64_decode ($teststr) )。但是这样做会使得任何使用该字符的地方都要进行编码与解码。
utf8mb4编码是utf8编码的超集，兼容utf8，并且能存储4字节的表情字符。
采用utf8mb4编码的好处是：存储与获取数据的时候，不用再考虑表情字符的编码与解码问题。
utf8mb4的最低mysql版本支持版本为5.5.3+，若不是，请升级到较新版本。
*/
-- 更改数据库编码，测试数据库为 testdb
ALTER DATABASE `testdb` CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
-- 更改表编码
ALTER TABLE `table1` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
-- 更改表中字段 oldfieldname，将原字段名 oldfieldname 改为 新字段名 newfieldname，并改编码为 utf8mb4
ALTER TABLE `table1` CHANGE `oldfieldname` `newfieldname` VARCHAR(255) CHARSET utf8mb4 COLLATE utf8mb4_general_ci; 
-- 如不需改表名只需改编码，则两个字段名写成一致即可
ALTER TABLE `table1` CHANGE `oldfieldname` `oldfieldname` VARCHAR(255) CHARSET utf8mb4 COLLATE utf8mb4_general_ci; 


-- 查询结果的总条数和查询的字段数量及字段的内容大小都影响查询速度，即查询内容的总体积越大查询速度越慢，反之总体积越小查询速度越快
SELECT * FROM table1

SELECT * FROM table1 WHERE id >= 1 AND id<=20 AND id IN(1,2,3,4,5,6,7,8,9) OR (id BETWEEN 1 AND 10)

SELECT * FROM table3 WHERE t3Date >= '2011-08-10'

SELECT * FROM table1 WHERE id IS NOT NULL GROUP BY id,t1Name ORDER BY id,t1Name

SELECT * FROM (SELECT a.*,b.t2Value FROM table1 a LEFT JOIN table2 b ON a.id = b.t2Table1Id) a

SELECT a.*,b.* FROM table1 a LEFT JOIN table2 b ON a.id = b.t2Table1Id/*显示a表中所有数据与b表中所有符合关联条件的数据，b表中有多条满足a表条件的行，则b表所有满足的行都显示，结果中对应a表显示部分的内容相同。如果a表中某条数据在b表中没有关联数据，则查询结果该条记录对应b表列为null空*/

SELECT a.*,b.* FROM table1 a RIGHT JOIN table2 b ON a.id = b.t2Table1Id/*显示b表中所有数据与a表中所有符合关联条件的数据。如果b表中某条数据在a表中没有关联数据，则查询结果该条记录对应a表列为null空*/

SELECT a.*,b.* FROM table1 a INNER JOIN table2 b ON a.id = b.t2Table1Id/*只显示a表b表中所有与对方有对应的数据*/

-- 把 UNION 前后两语句查询的结果累加起来，并且结果中字段值完全一样的多条记录只保留一条，再返回结果。UNION前后俩语句查询的字段名及顺序必须一致
SELECT field1 FROM table1 WHERE id <10
UNION
SELECT field1 FROM table1 WHERE id >20

-- 返回数据库最后插入的id值
SELECT @@IDENTITY AS id;
SELECT LAST_INSERT_ID() AS id;

UPDATE table4 a LEFT JOIN table5 b ON a.table5_id = b.id SET a.name = IF(b.id IS NOT NULL,CONCAT(a.name, '.html', b.id), '') WHERE b.id = 1;-- CONCAT(a.name, '.html')CONCAT()将两字符串连接后返回
-- 批插入
INSERT INTO table1(id, t1Name) VALUES(1, 'name1'), (2, 'name2'), (3, 'name3');

CREATE TABLE table6 AS  SELECT a.id,b.name  FROM table4 a LEFT JOIN table5 b ON a.table5_id = b.id WHERE b.id = 1;-- 用查询结果创建新表，可用来复制表，不成功则全回滚

INSERT INTO table6 SELECT a.id,b.name  FROM table4 a LEFT JOIN table5 b ON a.table5_id = b.id WHERE b.id = 1;-- 用查询结果插入表数据，也可用来复制表，不成功则全回滚

-- 为输出结果加排序号
SELECT (@i:=@i+1) AS num,b.test_field2 FROM test_table a
LEFT JOIN test_table2 b ON a.id = b.test_table_id
,(SELECT @i:=0) AS num_table

-- =========================================常用函数，其中参数均可用字段代替
-- 汇总函数
SELECT SUM(id) FROM table1 -- 获取括号内字段的和
;
SELECT COUNT(*) FROM table1 -- 获取括号内参数的个数
;
-- 逻辑函数
SELECT IF(2>1, '2大于1', '2不大于1') -- 判断第一个参数，真返回第二个参数，假返回第三个参数
;
SELECT IFNULL(NULL,0) -- 判断第一个参数，不是NULL返回第一个参数，是NULL返回第二个参数
;
SELECT CASE 1 WHEN 1 THEN '这是1' WHEN 2 THEN '这是2' WHEN 3 THEN '这是3' ELSE '都不是' END AS a -- 判断CASE后面的参数值与哪个WHEN的值相等，就返回这个WHEN对应的THEN的值，不限制WHEN与THEN的个数
;
SELECT CONCAT('a','b','c') -- 将括号内参数值连接起来再返回，参数个数无限制
;
-- 取整函数
SELECT CEIL(1.2) -- 向上取整，获取大于参数的最小整数，输出2
;
SELECT FLOOR(1.2) -- 向下取整，获取小于参数的最大整数，输出1
;
SELECT ROUND(1.2),ROUND(1.6) -- 四舍五入取整
;
-- 时间函数
SELECT NOW() -- 获取当前格式化时间 输出 2014-10-16 00:26:04
;
SELECT UNIX_TIMESTAMP(NOW()) -- 获取括号内格式化时间的时间戳，括号为空则返回当前时间戳
;
SELECT UNIX_TIMESTAMP(UTC_DATE()) -- 当天0点时间戳
;
SELECT FROM_UNIXTIME(1413388191, '%Y-%m-%d %H:%i:%s') -- 根据第二参数格式化时间，第二参数可以不写，输出2014-10-15 23:49:51
;
-- 混合应用
SELECT IF(1 IS NOT NULL,CONCAT('1','不是','NULL'),'是NULL') AS a
;


-- 导入数据时有时会有 MySQL server has gone away 报错
-- 查看max_allowed_packet值，可以看到是1048576 即 1MB，官方解释是适当增大 max_allowed_packet 参数可以使client端到server端传递大数据时，系统能够分配更多的扩展内存来处理。
SHOW GLOBAL VARIABLES LIKE 'max_allowed_packet'
;
-- 然后调大为256M(1024*1024*256)
SET GLOBAL max_allowed_packet=268435456
;
-- 修改后再执行导入，一切正常