Mysql杂记

-- SELECT * FROM tb_dept t,time tt WHERE tt.id=3 ;
1、SELECT id FROM tb_dept  WHERE id =3 UNION ALL SELECT id FROM time WHERE id=3;

CREATE TABLE `employee_tbl` (
  `id` int(11) NOT NULL,
  `name` char(10) NOT NULL DEFAULT '',
  `date` datetime NOT NULL,
  `singin` tinyint(4) NOT NULL DEFAULT '0' COMMENT '登录次数',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--2、 插入多条数据 INSERT INTO alter_test(name,created,updated) VALUES('Tina',SYSDATE(),SYSDATE()),('Jame',SYSDATE(),SYSDATE());

SELECT  * FROM employee_tbl;
3、-- 查询分组并统计总数用 with ROLLUP
SELECT name, SUM(singin) as singin_count FROM  employee_tbl GROUP BY name WITH ROLLUP;
--4、 mysql正则
-- 查找name字段中以'ok'为结尾的所有数据匹配是REGEXP 不匹配前面加上not

SELECT dept FROM tb_dept WHERE dept REGEXP 'R$';

-- 5、事务：
-- 原子性（Atomicity，或称不可分割性）、一致性（Consistency）、隔离性（Isolation，又称独立性）、持久性（Durability）


CREATE TABLE `aa` (
`id`  int NOT NULL AUTO_INCREMENT ,
`column_name`  varchar(20) NULL ,
PRIMARY KEY (`id`)
);
-- 6、alter
-- 重命名
ALTER TABLE aa RENAME TO alter_test;
-- 修改列名
ALTER TABLE alter_test CHANGE column_name name VARCHAR(10);
-- 显示表的状态
SHOW TABLE   STATUS LIKE 'alter_test';
-- 修改字段类型
ALTER TABLE alter_test MODIFY name datetime;

-- --7、 索引
-- 创建索引时，你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。
-- 
-- 实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。
-- 
-- 上面都在说使用索引的好处，但过多的使用索引将会造成滥用。因此索引也会有它的缺点：虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。
-- 
-- 建立索引会占用磁盘空间的索引文件。
-- 删除索引是drop
-- 索引失效：     1、.如果条件中有or，即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)；要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引
--                          2、对于多列索引，不是使用的第一部分(第一个)，则不会使用索引；
--                          3、like查询是以%开头；
--                          4、如果列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引；
--                          5、查询条件使用函数在索引列上，或者对索引列进行运算；
--                          6、not in ,not exist
--                        全值匹配我最爱，最左前缀要遵守；
--                        带头大哥不能死，中间兄弟不能断；
--                        索引列上少计算，范围之后全失效；
--                        LIKE百分写最右，覆盖索引不写星；
--                        不等空值还有or，索引失效要少用。
--                     left join（左连接）：右表创建索引。
--                     right join（右连接）：左表创建索引。
--                     简记：左右外连接，索引相反建（left：右表建，right：左表建）。
-- 8、复制表
CREATE TABLE test SELECT * FROM alter_test where 1=2; /*复制表结构*/

CREATE TABLE test1 SELECT * FROM alter_test /*复制表结构和数据*/
-- 9、获取服务器元数据
--             SELECT VERSION( )    服务器版本信息
--             SELECT DATABASE( )    当前数据库名 (或者返回空)
--             SELECT USER( )    当前用户名
--             SHOW STATUS    服务器状态
--             SHOW VARIABLES    服务器配置变量
-- 10、统计重复数据
SELECT * FROM test1 GROUP BY NAME HAVING COUNT(*) >1;

-- 如果你需要读取不重复的数据可以在 SELECT 语句中使用 DISTINCT 关键字来过滤重复数据。

-- 删除重复数据 复制表（结构和数据），然后删除原表，再修改临时表名称

CREATE TABLE temp SELECT *FROM test1 GROUP BY name;
 
DROP TABLE test1;

ALTER TABLE temp RENAME TO test1;
-- 11、导出数据 导出路径在mysql安装盘的data/数据库名/文件名
SELECT * FROM test1 INTO OUTFILE 'test.txt';
-- 12、 导入数据
--      mysql 命令导入；mysql -uroot -p123456 < sql的文件位置；
--      source命令导入：mysql> create database abc;      # 创建数据库
--                                           mysql> use abc;                  # 使用已创建的数据库 
--                                             mysql> set names utf8;           # 设置编码
--                                             mysql> source sql文件的位置  # 导入备份数据库
--             LOAD DATA 导入数据:LOAD DATA LOCAL INFILE 'D:/mysql/data/xiaojie/test.txt' INTO TABLE test;、
--      mysqlimport 导入数据
-- 13、sql函数
ASCII(str):返回字符串 s 的第一个字符的 ASCII 码。eg: SELECT ASCII(name) FROM test;
CHAR_LENGTH(str):返回字符数。eg :SELECT CHAR_LENGTH(name) FROM test;
CHARACTER_LENGTH(str):返回字符数. eg:SELECT CHARACTER_LENGTH(name) FROM test;
CONCAT(str1,str2,...) :字符串 s1,s2 等多个字符串合并为一个字符串。eg： SELECT CONCAT('xiaoming','xiaoli','xiaohua') as NAME;
CONCAT_WS(separator,str1,str2,...):字符串连接 但是中间有个分隔符 eg:SELECT CONCAT_WS('-','xiaoming','xiaoli','xiaohua') as NAME;
FIELD(str,str1,str2,str3,...) 返回第一个字符串 str 在字符串列表(str1,str2...)中的位置; SELECT FIELD('miss','miss','start','skssed','miss','miss');
FIND_IN_SET(s1,s2) 返回在字符串s2中与s1匹配到第一个字符串的位置;SELECT FIND_IN_SET('b','a,b,c,d,e,f,b');
FORMAT(X,D):函数将数字x格式化保留小数后d位，最后一位四舍五入;SELECT FORMAT('2541231.23549',2); 输出结果--------2,541,231.24;
INSERT(str,pos,len,newstr):用newstr替换字符串str从pos开始长度为len的字符串； SELECT INSERT("www.baidu.com",2,6,"sina");----------wsinadu.com;
LOCATE(s1,s):从s中获取s1第一次出现的位置： SELECT LOCATE('china','ilovechina');-----------6
LCASE(s)/LOWER(s):将 s转小写字母： SELECT LCASE('ILOVEYOU');
LEFT(str,len):选取字符串左侧的len个字符； SELECT LEFT('china','3');
LPAD(str,len,padstr):用padstr添加到字符串(前面添加)str是字符长度到len: SELECT LPAD('aa',5,'bb');-----bbbaa;
LTRIM(str):去掉字符串str开始处的空格;SELECT LTRIM('  study day day up');----------study day day up
MID(s,n,len)/SUBSTR(s, start, length)/SUBSTRING(s,n,len):从字符串 s 的 n 位置截取长度为 len 的子字符串，同 SUBSTRING(s,n,len);SELECT MID('ilove',2,4);-----------love;
POSITION(s1 IN s):从字符串 s 中获取 s1 的开始位置:SELECT POSITION('s' IN 'sabcdsdyu');
REPEAT(str,count): 将字符串str重复几次： SELECT REPEAT ('a',5);
REPLACE(s,s1,s2):将字符串 s2 替代字符串 s 中的字符串 s1: SELECT REPLACE('goodgoodstudy','o','a');--------gaadgaadstudy;
REVERSE(s):倒置字符串顺序; SELECT REVERSE('abcd');
`RIGHT`(str,len):返回字符串右边的len长度字符： SELECT RIGHT('good',1);
TRIM(s):去掉两端的空格： SELECT TRIM('   ssss   ');
UCASE(str)/UPPER(str):转字符为大写; SELECT UPPER('xioaming');
/*************************数字函数***********************************/
ABS(X)：绝对值
ACOS(X)等三角函数:求反余弦值; SELECT ACOS(1);
CEIL(X)/CEILING(X):向上取整 SELECT CEIL(-1.5);
DEGREES(X):将弧度数转为角度：SELECT DEGREES(3.14159365358979);
n DIV m :SELECT 10 DIV 2;----5;
EXP(X):求自然数e的x次幂的值；
FLOOR(X):向下取整； SELECT FLOOR(-1.5);
GREATEST(value1,value2,...): 返回最大值；
LEAST(value1,value2,...)：返回最小值；
POW(X,Y)/POWER(X,Y):返回x的y次幂；
RAND():返回0-1的随机数; SELECT RAND();
ROUND(X):返回距离x最近的整数： SELECT ROUND(-2.89);
SQRT(X):返回x的平发根： SELECT SQRT(4);
TRUNCATE(X,D) 对x保留小数 不会四舍五入 SELECT TRUNCATE(2.654523654,1);
/*************************日期函数***************************************************/
ADDDATE(d,n): 在当前日期增加几天 SELECT ADDDATE('2020-01-04',3);
ADDTIME(d,n):在当期时间加上几秒 SELECT ADDTIME('2020-01-07 16:04:25',35);
CURRENT_DATE()/CURDATE():返回当前日期 SELECT CURDATE();------2020-01-07;
`CURRENT_TIME`()/CURDATE();当前时间 SELECT CURRENT_TIME();
`CURRENT_TIMESTAMP`()；当前日期和时间 SELECT CURRENT_TIMESTAMP();
DATEDIFF(d1,d2):返回时间差天数d1-d2； SELECT DATEDIFF('2019-01-01','2020-01-01'); 
DATE_FORMAT(d,f);格式化时间；
DATE_SUB(date,INTERVAL expr type) 当前日期减去： SELECT DATE_SUB(NOW(),INTERVAL 2 DAY);
DAYNAME(date): 返回周几 ；
DAYOFMONTH(date)： 计算是本月的第几天； SELECT DAYOFMONTH(NOW());
`DAY`(date): 返回天数；
PERIOD_DIFF(P1,P2) 返回月份差： SELECT PERIOD_DIFF('2020-01-01','2020-01-02');
SEC_TO_TIME(seconds)将秒数改成时分秒： SELECT SEC_TO_TIME(120);
STR_TO_DATE(str,format):将字符串转为时间格式： SELECT STR_TO_DATE("August 10 2017", "%M %d %Y");
/*****************************高级函数***********************************/
BIN(N):放回二进制编码 ： SELECT BIN(3);
CONNECTION_ID():服务器的连接数: SELECT CONNECTION_ID();
IF(expr,v1,v2):如果满足表达式值为v1 不然值为v2 SELECT IF(1>2,1,2); ------2
IFNULL(v1,v2);如果为null v1不然v2 ;
NULLIF(v1,v2):比较两个字符串相等返回null 不然返回v1: SELECT NULLIF('a','b');
SESSION_USER();当前用户： SELECT SESSION_USER(); --------root@localhost;