Mysql实用知识点总结

本文介绍MYSQL相关知识，方便日常使用查阅

目录

准备
MYSQL常用命令
语言结构
sql语句
外键
自然语言全文搜索

  1. 准备

    你可以使用 Navicat Premium 12 或者 MySQL Workbench 8.0 CE开发MYSQL

在C:mysql-8.0.12-winx64创建my.ini文件，文件编码ascll，输入如下内容
[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8 
[mysqld]
#设置3306端口
port = 3306 
# 设置mysql的安装目录
basedir=C:mysql-8.0.12-winx64
# 设置mysql数据库的数据的存放目录
datadir=C:mysql-8.0.12-winx64data
# 允许最大连接数
max_connections=200
# 服务端使用的字符集默认为8比特编码的latin1字符集
character-set-server=utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB

cmd进入mysql程序目录 cd C:mysql-8.0.12-winx64in

执行 mysqld --initialize-insecure 初始化data文件夹

运行：
	前台进程
		mysqld 开启mysql服务
		mysqld --console 开启mysql服务，带错误提示
	后台服务
		mysqld --install 管理员
		net start mysql 开启服务
        mysqld --remove mysql 卸载服务
登录：
    不同的机器 mysql -h host -u user -p
    相同的机器 mysql -u root -p
退出：
	QUIT

  2. MYSQL常用命令

    重新定义定界符
	delimiter //
取消当前sql语句	
    c 
显示所有数据库	
    SHOW DATABASES; 
显示所有表	
    SHOW TABLES;
开启用户访问权限	
    GRANT ALL ON yejiaweifirstdb.* TO 'root'@'localhost';
创建数据库	    
    create database test
使用数据库	
    USE test
	mysql -u root -p test
创建表格		
    CREATE TABLE pets (
		宠物名称 VARCHAR(20), 
		主人 VARCHAR(20),
		种类 VARCHAR(20), 
		性别 CHAR(1), 
		出生日期 DATE, 
		死亡日期 DATE
	);
	create table shop (
		article int(4) unsigned zerofill default '0000' not null,
		dealer char(20) default '' not null,
		price double(16,2) default '0.00' not null,
		primary key (article, dealer)
	);
查看字符编码
	SELECT CHARSET('abc');
查看表格属性	
    DESCRIBE pets;
查看创建表格的语句 	
    SHOW CREATE TABLE shirtG	
查看当前是何数据库   
    SELECT DATABASE();
查看索引		
    show index from event;
查看访问文件权限	
    SHOW VARIABLES LIKE 'local_infile';
设置访问文件权限	
    SET GLOBAL local_infile = 1;
导入数据		
    LOAD DATA LOCAL INFILE 'C:/Users/26401/Desktop/pets.txt' INTO TABLE pets LINES TERMINATED BY '
';
导入txt数据格式
	Fluffy	Harold	cat	f	1993-02-04	N
	Claws	Gwen	cat	m	1994-03-17	N
	Buffy	Harold	dog	f	1989-05-13	N
	Fang	Benny	dog	m	1990-08-27	N
	Bowser	Diane	dog	m	1979-08-31	1995-07-29
	Chirpy	Gwen	bird	f	1998-09-11	N
	Whistler	Gwen	bird	N	1997-12-09	N
	Slim	Benny	snake	m	1996-04-29	N
count字句不允许去掉groupby的设置
	SET sql_mode = 'ONLY_FULL_GROUP_BY';	
	SET sql_mode = '';
批处理
	执行文件 			
        mysql -u root -p < C:/Users/26401/Desktop/pets.txt 
	结果输出 			    
        mysql -u root -p < C:/Users/26401/Desktop/sql.txt >C:/Users/26401/Desktop/out.txt 
	结果格式输出		
        mysql -u root -p < C:/Users/26401/Desktop/sql.txt >C:/Users/26401/Desktop/out.txt -t 
	脚本和结果输出		
        mysql -u root -p < C:/Users/26401/Desktop/sql.txt >C:/Users/26401/Desktop/out.txt -v
	已处在mysql下执行文件	
        source C:/Users/26401/Desktop/sql.txt
		. C:/Users/26401/Desktop/sql.txt
使用用户定义变量
	SELECT @min_price:=MIN(price),@max_price:=MAX(price) FROM shop;
	SELECT * FROM shop WHERE price=@min_price OR price=@max_price;

运行localhost的数据库 
	mysql --user=root test
	mysql --host=localhost --user=root --password= test
	mysql --host=remote.example.com --port=13306
	mysql -u root -p --execute="use test;SELECT * FROM pets"; 执行sql并且退出

罗列mysql支持的所有参数 mysqladmin extended-status variables -u root -p

罗列帮助选项 mysqlshow --help

查看程序信息 mysqldump -u root personnel

可选的数字值 K, M, G, T, P, E 
	mysqladmin --count=1K --sleep=10 ping -u root -p 对server ping1024次，间隔10秒

查看是否使用了配置文件 mysqld --help

列出mysqld的所有参数 
	mysqld --verbose --help
	SHOW VARIABLES;
	SHOW STATUS;

垂直列出结果 select * from pets G

mysql使用技巧
	SET sql_safe_updates=1,  # 不允许执行sql除非指定了where或者limit
	sql_select_limit=1000, # 选择记录上限为1000条除非指定了limit
	max_join_size=1000000; # 多表选择记录上限1000000条
	mysql --safe-updates --select_limit=500 --max_join_size=10000 --skip-reconnect

mysql本地化
	SELECT @@lc_time_names; 查看当前本地语言
	set @@lc_time_names := 'zh_CN'; 设置本地语言

查看上一条sql语句的警告信息	
	SHOW WARNINGS;

  3. mysql语言结构

    • 字面量
    字符串 'a string' "another string"
Date  
	'YYYY-MM-DD' 或者 'YY-MM-DD' -> '2012-12-31', '2012/12/31', '2012^12^31', '2012@12@31' 任意分隔符
	'YYYYMMDD' 或者 'YYMMDD' -> '20150721', '150721'
	YYYYMMDD 或者 YYMMDD -> 20150721, 150721
DATETIME 和 TIMESTAMP
	'YYYY-MM-DD HH:MM:SS' 或者 'YY-MM-DD HH:MM:SS' -> '2012-12-31 11:30:45', '2012^12^31 11+30+45' '2012-12-31T11:30:45' 任意分隔符
	'YYYYMMDDHHMMSS' 或者 'YYMMDDHHMMSS' -> '20070523091528', '070523091528'
	YYYYMMDDHHMMSS 或者 YYMMDDHHMMSS -> 19830905132800, 830905132800
TIME
	D HH:MM:SS.fraction', 'D HH:MM:SS', 'HH:MM:SS', 'HH:MM', 'D HH:MM', 'D HH', 或者 'SS'
	'HHMMSS'
	HHMMSS 
Boolean
	TRUE, true, FALSE, false
    • 特殊的数据类型
    enum 
	create table myenum(test enum('a', 'b', 'c'));
	insert into myenum values('d');
	insert into myenum values(2);
set
	create table myset (test set('a', 'b', 'c'));
	insert into myset values('a');
	insert into myset values('a', 'b');
	select * from myset where find_in_set('a', test) = 1;
	select * from myset where test like '%a%';
json
	CREATE TABLE myjson (jdoc JSON);
	INSERT INTO myjson VALUES('{"key1": "value1", "key2": "value2"}');
	返回json数据类型
		SELECT JSON_TYPE('["a", "b", 1]');
		SELECT JSON_TYPE('"hello"');	
	返回json数据
		SELECT JSON_ARRAY('a', 1, NOW()); 返回json数组
		SELECT JSON_OBJECT('key1', 1, 'key2', 'abc'); 返回json对象
		SELECT JSON_MERGE_PRESERVE('["a", 1]', '{"key": "value"}'); 合并
	赋值给自定义变量
		SET @j = JSON_OBJECT('key', 'value');
		SELECT @j;
	取键值
		SELECT JSON_EXTRACT('{"id": 14, "name": "Aztalan"}', '$.name');
		SELECT JSON_EXTRACT('{"a": 1, "b": 2, "c": [3, 4, 5]}', '$.*'); 	# [1, 2, [3, 4, 5]]
		SELECT JSON_EXTRACT('{"a": 1, "b": 2, "c": [3, 4, 5]}', '$.c[*]');      # [3, 4, 5]
		SELECT JSON_EXTRACT('{"a": {"b": 1}, "c": {"b": 2}}', '$**.b');         # [1, 2]
		SELECT JSON_EXTRACT('[1, 2, 3, 4, 5]', '$[1 to 3]');                    # [2, 3, 4]
		SELECT JSON_EXTRACT('[1, 2, 3, 4, 5]', '$[last-3 to last-1]');          # [2, 3, 4]
	设置键值
		SELECT JSON_SET('[3, {"a": [5, 6], "b": 10}, [99, 100]]', '$[0]', 'a');
		SELECT JSON_SET('[3, {"a": [5, 6], "b": 10}, [99, 100]]', '$[0]', 'a', '$[1]', 'b');
		SELECT JSON_REPLACE('[1, 2, 3, 4, 5]', '$[last]', 10);                  # [1, 2, 3, 4, 10]
		SELECT JSON_INSERT('["a", {"b": [true, false]}, [10, 20]]', '$[1].b[0]', 1, '$[2][2]', 2); #  ["a", {"b": [true, false]}, [10, 20, 2]]
	移除键值
		SELECT JSON_REMOVE('["a", {"b": [true, false]}, [10, 20]]', '$[2]', '$[1].b[1]', '$[1].b[1]'); # ["a", {"b": [true]}]
    • 类型转换
    隐式类型转换
	SELECT 1+'1'; -> 2
	select '11' = 11; -> 1
	SELECT CONCAT(2,' test'); -> '2 test'
显示类型转换
	SELECT CONVERT('abc' USING utf8);
	SELECT CONVERT('abc', CHAR CHARACTER SET utf8);
	SELECT CONCAT(38.8); -> '38.8'
	select cast('11' as unsigned) = 11; -> 1
	SELECT CAST(38.8 AS CHAR); -> '38.8'
    • 比较操作符
    SELECT 1 <=> 1, NULL <=> NULL, 1 <=> NULL; 				-> 1, 1, 0
SELECT 1 = 1, NULL = NULL, 1 = NULL;       				-> 1, NULL, NULL
SELECT 'zapp' <> 'zappp';                  				-> 1
SELECT 0.1 <= 2;                           				-> 1
SELECT 2 < 2;                              				-> 0
SELECT 2 >= 2;                             				-> 1
SELECT 2 > 2;                              				-> 0
SELECT 1 IS TRUE, 0 IS FALSE, NULL IS UNKNOWN; 			-> 1, 1, 1
SELECT 1 IS NOT UNKNOWN, 0 IS NOT UNKNOWN, NULL IS NOT UNKNOWN; 	-> 1, 1, 0
SELECT 1 IS NULL, 0 IS NULL, NULL IS NULL; 				-> 0, 0, 1
SELECT 1 IS NOT NULL, 0 IS NOT NULL, NULL IS NOT NULL; 		-> 1, 1, 0
SELECT 1 BETWEEN 1 AND 3, 3 BETWEEN 3 and 1; 			-> 1, 0 (NOT BETWEEN min AND max)
SELECT COALESCE(NULL,1); 						-> 1 (返回第一个非NULL值)
SELECT GREATEST(100,20,30.1); 					-> 100.0 (返回最大值)
select least(1,0.1,2.2); 						-> 0.1 (返回最小值)
SELECT 'wefwf' IN ('wee','wefwf','weg'); 				-> 1 (NOT IN)
SELECT (3,4) IN ((1,2), (3,4)); 					-> 1
SELECT s1 FROM t1 WHERE s1 > ANY (SELECT s1 FROM t2); -> 是否有满足条件的
SELECT s1 FROM t1 WHERE s1 > ALL (SELECT s1 FROM t2); -> 是否全部满足条件
SELECT * FROM t1 WHERE ROW(col1,col2) = (SELECT col3, col4 FROM t2 WHERE id = 10); -> 同时比较多列
SELECT column1 FROM t1 WHERE EXISTS (SELECT * FROM t2); -> 是否有结果
SELECT ISNULL(1+1); 							-> 0
SELECT INTERVAL(23, 1, 15, 17, 30, 44, 200); 			-> 3 (查找23应该在顺序表中的索引)
    • 逻辑操作符
    SELECT NOT 10; -> 0 
SELECT !10; -> 0
SELECT NOT NULL; -> NULL

SELECT 1 AND 0; -> 0
SELECT 1 && 0; -> 0
SELECT 1 AND NULL; -> NULL

SELECT 1 OR 0; -> 1
SELECT 1 || 0; -> 1
SELECT 0 OR NULL; -> NULL

SELECT 1 XOR 1 XOR 1; -> 1 (两个操作数当且仅当有一个是true返回true)
SELECT 1 XOR NULL; -> NULL
    • 赋值运算符
    SELECT @var1 := 1; SELECT @var1; -> 1
    • 流程控制函数
    CASE
	SELECT CASE 3
	WHEN 1 THEN 'one'
	WHEN 2 THEN 'two'
	ELSE 'more' 
	END;

	SELECT CASE WHEN 
	1>0 THEN 'true' 
	ELSE 'false' 
	END;
IF	
	SELECT IF(1>2,2,3); -> 3
	SELECT IF(1<2,'yes','no'); -> 'yes'
IFNULL
	SELECT IFNULL(1,0); -> 1
	SELECT IFNULL(NULL,10); -> 10
NULLIF
	SELECT NULLIF(1,1); -> NULL (如果expr1=expr2，返回NULL，否则返回expr1)
	SELECT NULLIF(1,2); -> 1
LOOP
	CREATE PROCEDURE doiterate(p1 INT)
	BEGIN
		label1: LOOP
			SET p1 = p1 + 1;
			IF p1 < 10 THEN
			ITERATE label1;
			END IF;
			LEAVE label1;
		END LOOP label1;
		SET @x = p1;
	END;
REPEAT
	CREATE PROCEDURE dorepeat(p1 INT)
	BEGIN
		SET @x = 0;
		REPEAT
			SET @x = @x + 1;
		UNTIL @x > p1 END REPEAT;
	END
WHILE
	CREATE PROCEDURE dowhile()
	BEGIN
		DECLARE v1 INT DEFAULT 5;
		WHILE v1 > 0 DO
			...
			SET v1 = v1 - 1;
		END WHILE;
	END;
游标
	CREATE PROCEDURE curdemo()
	BEGIN
		DECLARE done INT DEFAULT FALSE;
		DECLARE a CHAR(16);
		DECLARE b, c INT;
		DECLARE cur1 CURSOR FOR SELECT id,data FROM test.t1;
		DECLARE cur2 CURSOR FOR SELECT i FROM test.t2;
		DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

		OPEN cur1;
		OPEN cur2;

		read_loop: LOOP
			FETCH cur1 INTO a, b;
			FETCH cur2 INTO c;
			IF done THEN
				LEAVE read_loop;
			END IF;
			IF b < c THEN
				INSERT INTO test.t3 VALUES (a,b);
			ELSE
				INSERT INTO test.t3 VALUES (a,c);
			END IF;
		END LOOP;

		CLOSE cur1;
		CLOSE cur2;
	END;
    • 字符串常用函数
    SELECT CONCAT('My', 'S', 'QL'); 									-> 'MySQL'
SELECT CONCAT_WS('|','First name','Second name','Last Name'); 		-> 'First name|Second name|Last Name'
SELECT FORMAT(11.11111111, 1); 										-> '11.1' (保留几位小数)
SELECT INSERT('Quadratic', 1, 3, 'What'); 							-> 'Whatdratic' (从原字符串的第1个字母开始，删掉连续的3个，替换成'what')
SELECT INSTR('foobarbar', 'f'); 									-> 1 (返回右字符串在左字符串中第一个匹配的索引，从1开始)
SELECT LOCATE('f','foobarbar'); 									-> 1 (返回左字符串在右字符串中第一个匹配的索引，从1开始 SELECT LOCATE('f','foobarbar', 1); )
SELECT LEFT('foobarbar', 5); 										-> 'fooba' (取左边五个字符)
SELECT RIGHT('foobarbar', 5); 										-> 'arbar'
SELECT LENGTH('abc'); 												-> 3 (返回字节数 SELECT LENGTH('叶家伟'); -> 6)
SELECT LOWER('YEJIAWEI'); 											-> 'yejiawei' (大写转小写)
SELECT LPAD('||',4,'??'); 											-> '??||' (左填充，一共4个字符)
SELECT RPAD('||',4,'??'); 											-> ||??
SELECT LTRIM('  ||'); 												-> '||' (去掉左空格)
SELECT RTRIM('||  '); 												-> '||'
SELECT TRIM('  ||   '); 											-> '||'
SELECT TRIM(LEADING 'x' FROM 'xxx||xxx'); 							-> '||xxx'
SELECT TRIM(BOTH 'x' FROM 'xxx||xxx'); 								-> '||'
SELECT TRIM(TRAILING 'x' FROM 'xxx||xxx'); 							-> 'xxx||'
SELECT REPEAT('MySQL', 3); 											-> 'MySQLMySQLMySQL'
SELECT REPLACE('abcd', 'b', 'fg');	 								-> 'afgcd'
SELECT REVERSE('abc'); 												-> 'cba'
SELECT SPACE(6); 													-> '      '
SELECT SUBSTRING('abcdefg', 2);										-> 'bcdefg' 等价于 SELECT SUBSTRING('abcdefg' from 2);
SELECT SUBSTRING('abcdefg',2,2); 									-> 'bc'
SELECT SUBSTRING('abcdefg',-1); 									-> 'g'
SELECT SUBSTRING('abcdefg' FROM -2 FOR 2); 							-> 'fg'
SELECT SUBSTRING_INDEX('a.a.a.b.b', '.', 2); 						-> 'a.a' 按分隔符截取
SELECT SUBSTRING_INDEX('a.a.a.b.b', '.', -2); 						-> 'b.b'
SELECT UPPER('aaa');												-> 'AAA'
LIKE(同理有NOT LIKE)
	% 代表任意数量的字符
	_ 代表任意一个字符
	SELECT 'a' LIKE 'a ', 'a' = 'a '; 								-> 0 1
	SELECT 'abc' LIKE 'ab_';										-> 1
	SELECT 'abcd' LIKE '%b%';										-> 1
	SELECT 'abc' LIKE 'abdc' ESCAPE 'd';							-> 1
	SELECT 'abc' LIKE 'ABC';										-> 1
	SELECT 'abc' LIKE _utf8mb4 'ABC' COLLATE utf8mb4_0900_as_cs;	-> 1
	SELECT 100000 LIKE '1%';										-> 1
STRCMP(比较字符串大小)
	SELECT STRCMP('ab', 'ab'); 										-> 0 (相等)
	SELECT STRCMP('ab', 'ac'); 										-> -1 (前者比后者小)
	SELECT STRCMP('ac', 'ab');										-> 1 (后者比前者小)
正则表达式
	. 	表示任意单个字符
	+ 	表示至少一个字符
	[abc] [a-z] [0-9]	多个选一个
	* 	表示任意个之前的字符 [0-9]*任意个数字  .*任意个任意值
	^ 	表示开头
	$ 	表示结尾
	? 	0个或者1个字符
	| 	或者
	{2,3} 	2个到3个字符
	SELECT 'aaaaa' REGEXP 'a*';										-> 1
	SELECT REGEXP_INSTR('abcdefg', 'bc');  							-> 2
	SELECT REGEXP_INSTR('abcdefg', 'b.*');  						-> 2
	SELECT REGEXP_INSTR('abcdefg', 'bc', 3); 						-> 0
	SELECT REGEXP_LIKE('abc', 'ABC', 'c');							-> 0 (区分大小写)
	SELECT REGEXP_LIKE('abc', 'ABC');								-> 1
	SELECT REGEXP_REPLACE('abc', 'b.*', '|');						-> 'a|'
	SELECT REGEXP_REPLACE('abcbcd', 'b.*', '|', 4, 1);				-> 'abc|' (从第4个位置开始，替换1个满足匹配的子字符串)
	SELECT REGEXP_SUBSTR('123, 456', '[1-9]+');						-> '123'
    • 数字函数
    SELECT ABS(-1); 													-> 1
SELECT CEILING(1.23); 												-> 2
SELECT FLOOR(1.23);													-> 1
SELECT MOD(3, 2);													-> 1
SELECT POW(2,2);													-> 4 (次方)
SELECT SQRT(4);														-> 2 (开方)
SELECT RAND();														-> 返回0到1之间的随机数
SELECT RAND(3);														-> 指定相同的seed，随机数不变
SELECT ROUND(1.298);												-> 1 (四舍五入)
SELECT ROUND(1.298, 2);												-> 1.30 (指定小数位数)
SELECT TRUNCATE(1.223,1);											-> 1.2 (截取小数位数)
    • 日期和时间函数
    SELECT DATE_ADD('2000-01-01', INTERVAL 1 MONTH); 					-> 2000-02-01
SELECT ADDDATE('2000-01-01', INTERVAL 1 MONTH);						-> 2000-02-01
SELECT ADDDATE('2000-01-01', -1);									-> 1999-12-31

SELECT DATE_SUB('2000-01-01', INTERVAL 1 SECOND);					-> 1999-12-31 23:59:59
SELECT SUBDATE('2008-01-02 12:00:00', 31);							-> 2007-12-02 12:00:00

SELECT DATE('2000-01-01 00:00:00'); 								-> 2000-01-01 提取日期部分
SELECT DATEDIFF('2000-01-01 00:00:00', '2000-02-01 00:00:00'); 		-> -31

SELECT CURDATE();													-> 2000-01-01 获取当前日期
SELECT CURDATE() + 0;												-> 20000101

SELECT DATE_FORMAT('2009-10-04 22:23:00', '%Y\%m\%d');				-> 2009104
SELECT DATE_FORMAT('2000-01-01',GET_FORMAT(DATETIME,'ISO'));  		-> 2000-01-01 00:00:00 格式化
SELECT DAY('2000-01-10');											-> 10

SELECT NOW();														-> 2000-01-02 00:00:00获取当前日期和时间
SELECT NOW() + 0;													-> 20000102000000


SELECT TIME('2003-12-31 01:02:03');									-> 01:02:03 提取时间部分
SELECT TIMEDIFF('2000:01:01 00:00:00','2000:01:01 00:00:00.000001');-> 46:58:57.999999

SELECT CURTIME();													-> 00:00:00
SELECT CURTIME() + 0;												-> 000000

SELECT ADDTIME('2000-01-01 00:00:00.000000', '1 1:1:1.000001');		-> 2000-01-02 01:01:01.000001
SELECT SUBTIME('2007-12-31 23:59:59.999999','1 1:1:1.000002');		-> '2007-12-30 22:58:58.999997'

SELECT TIMESTAMP('2003-12-31 12:00:00','2:00:00');					-> 2003-12-31 14:00:00
SELECT TIMESTAMPADD(MINUTE,1,'2003-01-02');							-> 2003-01-02 00:01:00
SELECT TIMESTAMPDIFF(MONTH,'2003-02-01','2003-05-01');				-> 3
SELECT TIME_FORMAT('100:00:00', '%H %k %h %I %l');					-> 100 100 04 04 4
SELECT TIME_TO_SEC('22:23:00');										-> 80580


SELECT PERIOD_ADD(200801,2);										-> 200803
SELECT PERIOD_DIFF(200802,200703);									-> 11


SELECT MONTHNAME('2008-02-03');										-> February 获取月份名称
SELECT DAYNAME('2000-01-01');										-> Saturday
SELECT DAYOFWEEK('2000-01-01');										-> 7 同上
SELECT DAYOFMONTH('2000-01-01');									-> 1
SELECT DAYOFYEAR('2000-10-01');										-> 275 返回一年中的第几天
SELECT HOUR('2000-01-01 10:11:12');									-> 10
SELECT MINUTE('2008-02-03 10:05:03');								-> 5
SELECT MICROSECOND('12:00:00.123456');								-> 123456 获取微秒
SELECT QUARTER('2008-04-01');										-> 2 获取季度
SELECT SECOND('10:05:03');											-> 3 获取秒
SELECT WEEK('2008-02-20');											-> 7
SELECT YEAR('2000-01-01');											-> 2000


SELECT EXTRACT(YEAR_MONTH FROM '2009-07-02 01:02:03');				-> 200907 截取
SELECT LAST_DAY('2000-01-01');										-> 2000-01-31 当月最后一天的日期

SELECT MAKEDATE(2000,200);											-> 2000-07-18 获取当年第200天的日期
SELECT MAKETIME(12,15,30);											-> 12:15:30

SELECT SEC_TO_TIME(2378);											-> 00:39:38 将秒转化成time
SELECT STR_TO_DATE('01,5,2013','%d,%m,%Y');							-> 2013-05-01 转化成日期

SELECT NOW(), SLEEP(2), NOW();										-> 2000-01-01 00:00:00 |        0 | 2000-01-01 00:00:00
SELECT SYSDATE(), SLEEP(2), SYSDATE();								-> 2000-01-01 00:00:00 |        0 | 2000-01-01 00:00:02
    • 聚合函数
    select avg(num) from mynumber group by num; 						-> 取平均值
select count(num) from mynumber group by num;						-> 计数
select count(distinct num) from mynumber group by num;
select group_concat(num) from mynumber1 group by id;				-> 1,2 将分组的列值拼接成一个字符串
select group_concat(
	distinct num order by num desc separator '|'
) from mynumber group by id;										-> 2|1  对分组的记录去重，排序，设置分隔符
select json_arrayagg(num) from mynumber1 group by id;				-> [1, 2] 将结果聚合成json数组
select json_objectagg(id,num) from mynumber1 group by id;			-> {"1": 2} 将结果聚合成json对象
select min(num), max(num) from mynumber1 group by id;				-> 取最值
select sum(num) from mynumber1 group by id with rollup;				-> 计算分组的和，并且通过with rollup得到总的值

select 
	id, num,sum(num), GROUPING(id),GROUPING(num) 
from 
	mynumber1 
group by 
	id, num 
with rollup;														-> with rollup 用在多个聚合条件的情况下，会按照每一个条件的分组做汇总
	结果如下：
	+------+------+----------+--------------+---------------+
	| id   | num  | sum(num) | GROUPING(id) | GROUPING(num) |
	+------+------+----------+--------------+---------------+
	|    1 |    1 |        1 |            0 |             0 |
	|    1 |    2 |        2 |            0 |             0 |
	|    1 | NULL |        3 |            0 |             1 |
	|    2 |    3 |        3 |            0 |             0 |
	|    2 |    4 |        4 |            0 |             0 |
	|    2 | NULL |        7 |            0 |             1 |
	|    3 |    5 |        5 |            0 |             0 |
	|    3 | NULL |        5 |            0 |             1 |
	| NULL | NULL |       15 |            1 |             1 |
	+------+------+----------+--------------+---------------+
	GROUPING 可以用来指示哪一个聚合条件的数据参与汇总了，如上表中的NULL，GROUPING就会返回1，反之返回0
	如果你不想要NULL，可以自定义显示内容，sql如下
select 
	if(grouping(id), '所有的id', id), 
	if(grouping(num), '所有的num', num), 
	sum(num) 
from 
	mynumber1 
group by 
	id, num 
with rollup;
	结果如下
	+----------------------------------+-------------------------------------+----------+
	| if(grouping(id), '所有的id', id) | if(grouping(num), '所有的num', num) | sum(num) |
	+----------------------------------+-------------------------------------+----------+
	| 1                                | 1                                   |        1 |
	| 1                                | 2                                   |        2 |
	| 1                                | 所有的num                           |        3 |
	| 2                                | 3                                   |        3 |
	| 2                                | 4                                   |        4 |
	| 2                                | 所有的num                           |        7 |
	| 3                                | 5                                   |        5 |
	| 3                                | 所有的num                           |        5 |
	| 所有的id                         | 所有的num                           |       15 |
	+----------------------------------+-------------------------------------+----------+

select 
	*, ROW_NUMBER() over w AS 'row_number', 
from 
	mynumber1 														
window w as (order by id);											-> mysql8的新特性window函数	
上面讲过的聚合函数都是对最后的结果做一个汇总操作，而window能够对每一条记录做操作
select 
	id, num , 
	sum(num) over(), 												-> 不带参数的over就是整个记录
	sum(num) over(partition by id) 									-> 通过partition可以指定分组依据
	ROW_NUMBER() OVER(PARTITION BY id) 
from 
	mynumber1 order by id, num;	
	结果如下
	+------+------+-----------------+--------------------------------+------------------------------------+
	| id   | num  | sum(num) over() | sum(num) over(partition by id) | ROW_NUMBER() OVER(PARTITION BY id) |
	+------+------+-----------------+--------------------------------+------------------------------------+
	|    1 |    1 |              15 |                              3 |                                  1 |
	|    1 |    2 |              15 |                              3 |                                  2 |
	|    2 |    3 |              15 |                              7 |                                  1 |
	|    2 |    4 |              15 |                              7 |                                  2 |
	|    3 |    5 |              15 |                              5 |                                  1 |
	+------+------+-----------------+--------------------------------+------------------------------------+

  4. sql语句（不区分大小写）

    • select语句
        SELECT VERSION(), USER(), CURRENT_DATE; 
	SELECT SIN(PI()/4), (4+1)*5;
	where字句的使用
		select * from pets where 主人='Gwen'
		select * from pets where 出生日期 >= '1998-09-11';
		select * from pets where 种类='dog' and 性别 = 'f';
		select * from pets where 种类='dog' or 种类 = 'snake';
		select * from pets where (种类 = 'cat' and 性别 = 'm') or (种类 = 'dog' and 性别 = 'f');
	指定列
		select 宠物名称, 出生日期 from pets;
		select 宠物名称, 种类, 出生日期 from pets where 种类='dog' or 种类 = 'cat';
	去重
		select distinct 主人 from pets;
	排序不区分大小写 
		select 宠物名称, 出生日期 from pets order by 出生日期;
	排序区分大小写 
		select * from pets order by binary 宠物名称;
	降序
		select * from pets order by 出生日期 desc;
	多列排序
		select 宠物名称, 种类, 出生日期 from pets order by 种类, 出生日期 desc;
	时间间隔
		select 宠物名称, 出生日期, curdate(), timestampdiff(year, 出生日期, curdate()) as age from pets;
		select 宠物名称, 出生日期, 死亡日期, timestampdiff(year, 出生日期, 死亡日期) as age from pets where 死亡日期 is not null order by age;
		select 宠物名称, 出生日期,year(出生日期), month(出生日期),dayofmonth(出生日期) from pets;
		月份加一月 
			select 宠物名称, 出生日期 from pets where month(出生日期) = month(date_add(curdate(), interval 1 month));
			select 宠物名称, 出生日期 from pets where month(出生日期) = mod(month(curdate()), 12) + 1;
	空值
		select 1 is null, 1 is not null;
	标准模式匹配
		_	代表任意单个字符
		% 	代表任意多个字符
		select * from pets where 宠物名称 like 'b%';
		select * from pets where 宠物名称 like '_____';
	扩展模式匹配
		. 		表示任意单个字符
		[abc] [a-z] [0-9]	多个选一个
		* 		表示任意个之前的字符 [0-9]*任意个数字  	.*任意个任意值
		^ 		表示开头
		$ 		表示结尾
		select * from pets where regexp_like(宠物名称, '^b');
		select * from pets where regexp_like(宠物名称, '^.{5}$');
		大小写敏感
			select * from pets where regexp_like(宠物名称, binary '^B');	
			select * from pets where regexp_like(宠物名称,'^B','c');
	获取个数
		select count(*) from pets;
		select 主人, count(*) from pets group by 主人;
		select 种类, 性别, count(*) from pets group by 种类, 性别;
	inner join的使用
		select pets.宠物名称, timestampdiff(year, 出生日期, eventdate) as age, remark 
		from pets inner join event on 
		pets.宠物名称 = event.petname 
		where event.eventtype = 'litter';

		select p1.宠物名称, p1.性别, p2.宠物名称, p2.性别, p1.种类 
		from pets as p1 inner join pets as p2 on 
		p1.种类 = p2.种类 and p1.性别 = 'f' and p2.性别 = 'm';
	获取最大值
		select Max(article) as article from shop;
		select * from shop where price = (select Max(price) from shop);
		select article, max(price) as price from shop group by article;

		可以使用如下语句代替

		SELECT s1.article, s1.dealer, s1.price
		FROM shop s1
		LEFT JOIN shop s2 ON s1.price < s2.price
		WHERE s2.article IS NULL;

		select * from shop order by price desc limit 1;
    • insert语句
    INSERT INTO pets VALUES ('Puffball','Diane','hamster','f','1999-03-30',NULL);
    • delete语句
    清空表中数据	DELETE FROM pets; <=> TRUNCATE TABLE pets;
    • update语句
    update pets set 出生日期='1989-08-31' where 宠物名称='Bowser';
    • union 语句
    select * from shirt where id = '2'
union
select * from shirt where id = '3'
    • event
    创建一个event
	create event 
		myevent 
	on schedule
		every 1 second 
	comment 
		'A sample comment.' 
	do 
		insert into test.mynumber values(100);
修改event 
	alter event 
	 	myevent 
	on schedule 
		every 1 second starts current_timestamp + interval 1 minute 
	do 
		update test.mynumber set num = SECOND(current_timestamp);
禁用event
	ALTER EVENT myevent DISABLE; 
修改event名称
	ALTER EVENT myevent RENAME TO mynewevent;
移动event
	ALTER EVENT olddb.myevent RENAME TO newdb.myevent;
    • alter表格
    删除列	alter table mynumber1 drop column id;
重置自增起始值	alter table mynumber1 AUTO_INCREMENT = 10;
修改表编码
	show create table mynumber1;
	ALTER TABLE mynumber1 CHARACTER SET = utf8;
    • 存储过程
    创建存储过程
	CREATE PROCEDURE p (OUT ver_param VARCHAR(25), INOUT incr_param INT)
	BEGIN
		# Set value of OUT parameter
		SELECT VERSION() INTO ver_param;
		# Increment value of INOUT parameter
		SET incr_param = incr_param + 1;
	END;
	INOUT的变量需要初始化
	OUT的变量不需要初始化

调用存储过程
	SET @increment = 10;
	CALL p(@version, @increment); 
	SELECT @version, @increment;

	SET @increment = 10;
	PREPARE s FROM 'CALL p(?, ?)';
	EXECUTE s USING @version, @increment;
	SELECT @version, @increment;
    • with语句
    合并集合
WITH cte (col1, col2) AS
(
	SELECT 1, 2
	UNION ALL
	SELECT 3, 4
)
SELECT col1, col2 FROM cte;
    • 事务
    start transaction
select @A:=sum(num) from mynumber;
insert into mynumber values(@A);
commit;
    • 触发器
    创建触发器
	CREATE TRIGGER 
		triggername 
	BEFORE 																	# AFTER
		INSERT 																# DELETE and UPDATE
	ON 
		tablename
	FOR EACH ROW SET @sum = @sum + NEW.tablecolumn;

如果触发器同名，可以指定先后顺序
	FOR EACH ROW PRECEDES
	FOR EACH ROW FOLLOWS 

使用触发器
	SET @sum = 0;
	INSERT INTO ...
	SELECT @sum;

删除触发器
	DROP TRIGGER triggername;
    • 视图
    创建视图
	CREATE TABLE t (qty INT, price INT);
	INSERT INTO t VALUES(3, 50), (5, 60);
	CREATE VIEW v AS SELECT qty, price, qty*price AS value FROM t;
	SELECT * FROM v;

  5. 外键（只是一个memo）

    外键不会创建index，只是一个标记而已
CREATE TABLE person (
	id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
	name CHAR(60) NOT NULL,
	PRIMARY KEY (id)
);

# ALTER TABLE person AUTO_INCREMENT = 100;

CREATE TABLE shirt (
	id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
	style ENUM('t-shirt', 'polo', 'dress') NOT NULL,
	color ENUM('red', 'blue', 'orange', 'white', 'black') NOT NULL,
	owner SMALLINT UNSIGNED NOT NULL REFERENCES person(id),
	PRIMARY KEY (id)
);

INSERT INTO person VALUES (NULL, 'Antonio Paz');

SELECT @last := LAST_INSERT_ID();

INSERT INTO shirt VALUES
(NULL, 'polo', 'blue', @last),
(NULL, 'dress', 'white', @last),
(NULL, 't-shirt', 'blue', @last);

INSERT INTO person VALUES (NULL, 'Lilliana Angelovska');

SELECT @last := LAST_INSERT_ID();

INSERT INTO shirt VALUES
(NULL, 'dress', 'orange', @last),
(NULL, 'polo', 'red', @last),
(NULL, 'dress', 'blue', @last),
(NULL, 't-shirt', 'white', @last);

  6. 全文搜索

    CREATE TABLE articles (
    id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
    title VARCHAR(200),
    body TEXT,
    FULLTEXT (title,body)
)

INSERT INTO articles (title,body) VALUES
('MySQL Tutorial','DBMS stands for DataBase ...'),
('How To Use MySQL Well','After you went through a ...'),
('Optimizing MySQL','In this tutorial we will show ...'),
('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
('MySQL vs. YourSQL','In the following database comparison ...'),
('MySQL Security','When configured properly, MySQL ...');

自然语言全文搜索

	SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('database' IN NATURAL LANGUAGE MODE);

	其中MATCH的参数和建表时的FULLTEXT参数一致，全文搜索起到一个快速检索和相关性排序的功能

	SELECT COUNT(IF(MATCH (title,body) AGAINST ('database' IN NATURAL LANGUAGE MODE), 1, NULL)) AS count FROM articles;

	上面这条语句，避免了相关性排序的操作
Boolean 全文搜索

	SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('+MySQL -YourSQL' IN BOOLEAN MODE); 查找记录中包含MySQL但是不包含YourSQL
【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/ye-hcj/p/9530396.html