mysql 语法笔记

基本语法

SELECT IFNULL(o.picture,121) FROM `people2` o;

SELECT * FROM people2 o WHERE o.`name` LIKE '_x_a%';

SELECT * FROM people2 o WHERE o.`name` LIKE '%x%w%b%';

SELECT * FROM people2 o WHERE o.`name` LIKE '%b';
SELECT * FROM people2 o WHERE o.`name` LIKE 'b%';

-- \ 转义字符
SELECT * FROM people2 o WHERE o.`name` LIKE '_\_%';

-- ESCAPE自定义转义符
SELECT * FROM people2 o WHERE o.`name` LIKE '_$_%' ESCAPE '$';

-- 效果等同
SELECT COUNT(1) FROM `people2` o WHERE ISNULL(o.picture);
SELECT COUNT(1) FROM `people2` o WHERE o.picture is null;
SELECT COUNT(1) FROM `people2` o WHERE o.picture <=>null;


SELECT * FROM people2 o WHERE o.id<=>123;


SELECT DISTINCT o.`name`,o.age FROM people2 o ;

-- 清表
TRUNCATE TABLE qwer;

-- 查看字符集类型
show VARIABLES LIKE '%char%';


-- 去除指定前后的字符
SELECT TRIM('a' FROM 'aaaaa哈哈哈aaaaaaa');
-- 成对去除
SELECT TRIM('aa' FROM 'aaaaa哈哈哈aaaaaaa');



-- 用指定字符左填充到指定长度
SELECT LPAD('哈1哈哈',10,'$');
SELECT LPAD('哈1哈哈',2,'$');
SELECT RPAD('哈1哈哈',10,'$');


-- 四舍五入
SELECT ROUND(1.55);
SELECT ROUND(1.55,1);


-- 向上取整
SELECT CEIL(1.45);
-- 向下取整
SELECT FLOOR(1.45);

-- 截断
SELECT TRUNCATE(1.44,1);
SELECT TRUNCATE(11,1);

-- 取余
SELECT MOD(10,3);
SELECT 10%3;


-- 当前系统日期时间
SELECT NOW();
-- 当前系统日期
SELECT CURDATE();
-- 当前系统时间
SELECT CURTIME();
-- 获取指定的年月日小时分钟秒
SELECT YEAR(NOW());
SELECT MONTH(NOW());
SELECT MONTHNAME(NOW());
SELECT DAY(NOW());
SELECT HOUR(NOW());
SELECT MINUTE(NOW());
SELECT SECOND(NOW());

-- 字符日期转换
SELECT STR_TO_DATE('1998_03_05','%Y_%m_%d');
SELECT DATE_FORMAT(NOW(),'%Y年%m月%d日');
SELECT DATE_FORMAT(NOW(),'%Y-%m-%d');

-- 等价于三元运算
SELECT IF(10>2,'大','小');

-- 查询日期相差天数
SELECT DATEDIFF('2019-09-18','2019-10-18');

分组查询

#分组查询第一条
SELECT s2.* FROM (SELECT o.class,MAX(o.age) age FROM `student` o GROUP BY o.class) s1  JOIN student s2 ON s1.class = s2.class and s1.age = s2.age;

SELECT * FROM (SELECT o.id,o.`name`,o.class,o.age age FROM `student` o ORDER BY o.age DESC ) s1  GROUP BY s1.class
#分组查询第一条5.7 必须加limit
SELECT * FROM (SELECT o.id,o.`name`,o.class,o.age age FROM `student` o ORDER BY o.age DESC LIMIT 100) s1  GROUP BY s1.class

#with as  mysql好像不支持
WITH max_age as(
SELECT o.class,MAX(o.age) age FROM `student` o GROUP BY o.class
)

SELECT s.* FROM max_age ma jion student s ON ma.age = s.age and ma.class = s.class

多字段子查询

-- 多字段子查询
SELECT * FROM `people` o WHERE (o.id,o.age)=(SELECT MAX(p.id),MIN(p.age) FROM people p);

复制表结构

-- 复制表结构
CREATE TABLE if NOT EXISTS copy_p LIKE people;
-- 复制表结构+数据
CREATE TABLE copy_p1 SELECT * FROM people;
-- 复制表部分结构
CREATE TABLE copy_p2 SELECT id,age FROM people WHERE 0;

表创建

-- 无符号int
DROP TABLE IF EXISTS tab_int;
CREATE TABLE if NOT EXISTS tab_int(
				id INT,
				id2 INT UNSIGNED
); 

INSERT INTO tab_int VALUES(-123,123); 
INSERT INTO tab_int VALUES(123,123); 

SELECT * FROM tab_int;
DESC tab_int;

-- ZEROFILL缺位补0
CREATE TABLE if NOT EXISTS tab_int(
				id INT(7) ZEROFILL,
				id2 INT(7) ZEROFILL
); 

-- 枚举类型
DROP TABLE tab_char;
CREATE TABLE IF NOT EXISTS tab_char(
			c1 ENUM('a','b','c'),
			c2 SET('a','b','c')
);

INSERT INTO tab_char VALUES('a','');
INSERT INTO tab_char VALUES('b','a,b');

时区设置

-- 时区设置
SHOW VARIABLES LIKE 'time_zone';
SET time_zone='+9:00';
SET time_zone='SYSTEM';

视图

CREATE VIEW p_v1 AS SELECT * FROM people;

-- 修改视图
CREATE OR REPLACE VIEW p_v1 AS SELECT * FROM people;
-- 修改视图
ALTER VIEW p_v1 as SELECT * FROM people;

-- 删除视图 DROP VIEW p_v1,p_v2
DROP VIEW p_v1;


-- 查看视图 
DESC  p_v1;

SHOW CREATE VIEW p_v1;

系统变量

-- 查看全部系统变量
show VARIABLES;

show VARIABLES LIKE '%auto%';
-- 查看全局变量
show GLOBAL VARIABLES;
-- 查看会话变量
show SESSION VARIABLES;


SET GLOBAL autocommit=1;

事务操作

SET autocommit=0;
START TRANSACTION;

DELETE FROM people WHERE id=1;

#COMMIT;
ROLLBACK;

SHOW VARIABLES LIKE '%autocommit%';


SET autocommit=0;
START TRANSACTION;

DELETE FROM people WHERE id=5;

SAVEPOINT a;

DELETE FROM people WHERE id=6;

#COMMIT;
ROLLBACK TO a; -- 回滚到节点

结构

DROP PROCEDURE test_case;
CREATE PROCEDURE test_case(IN bb INT(11))
BEGIN
	CASE
			WHEN bb>=90 and bb<=100 THEN SELECT 'A';
			WHEN bb>=80 THEN SELECT 'B';
			WHEN bb>=70 THEN SELECT 'C';
			ELSE SELECT 'D';
	END CASE;
END;


CALL test_case(77);


CREATE FUNCTION test_if(bb INT) RETURNS VARCHAR(25)
BEGIN
	IF bb>=90 AND bb<=100 THEN RETURN 'A';
	ELSEIF bb>=80 THEN RETURN 'B';
	ELSEIF bb>=70 THEN RETURN 'C';
	ELSE RETURN 'D';
	END IF;

END; 

SELECT test_if(80);



TRUNCATE copy_p;

DROP PROCEDURE test_while1;
CREATE PROCEDURE test_while1(IN inCount INT)
BEGIN
	DECLARE i INT DEFAULT 1;
	WHILE i<inCount DO
		INSERT INTO copy_p(name,age) VALUES('zs',3);
		SET i = i+1;
	END WHILE;

END;


CALL test_while1(20);




DROP PROCEDURE test_while2;
CREATE PROCEDURE test_while2(IN inCount INT)
BEGIN

	DECLARE i INT DEFAULT 1;
	a:WHILE 1 DO
		INSERT INTO copy_p(name,age) VALUES('zs',3);
			IF i>=20 THEN LEAVE a;
			END IF;
		SET i = i+1;
	END WHILE a;

END;

CALL test_while2(30);





-- LEAVE相当于break ITERATE相当于 countin
DROP PROCEDURE test_while3;
CREATE PROCEDURE test_while3(IN inCount INT)
BEGIN

	DECLARE i INT DEFAULT 1;
	a:WHILE 1 DO
		SET i = i+1;
		IF i>=20 THEN LEAVE a;
		ELSEIF MOD(i,2)!=0 THEN ITERATE a;
		END IF;
		INSERT INTO copy_p(name,age) VALUES(CONCAT('zs',i),3);
			
	END WHILE a;

END;

CALL test_while3(30);








-- 该循环先执行一次
DROP PROCEDURE test_repeat1;
CREATE PROCEDURE test_repeat1(IN inCount INT)
BEGIN

	DECLARE i INT DEFAULT 1;
	b:REPEAT
		SET i = i+1;
		INSERT INTO copy_p(name,age) VALUES(CONCAT('zs',i),3);
	UNTIL i>17	-- 结束条件不可加分号
	END REPEAT b;

END;

TRUNCATE copy_p;
CALL test_repeat1(30);



-- 死循环
DROP PROCEDURE test_loop1;
CREATE PROCEDURE test_loop1(IN inCount INT)
BEGIN

	DECLARE i INT DEFAULT 1;
	b:LOOP
		SET i = i+1;
		IF i>20 THEN LEAVE b;
		END IF;
		INSERT INTO copy_p(name,age) VALUES(CONCAT('zs',i),3);
	END LOOP b;

END;

TRUNCATE copy_p;
CALL test_loop1(30);

自定义变量

-- 自定义变量
-- 申明和赋值
SET @qq=1;

SELECT @qq;

SELECT COUNT(1) INTO @qq FROM people;



-- 局部变量 定义在begin end 中间 且定义在第一句话

BEGIN
DECLARE m INT DEFAULT 1;
DECLARE n INT DEFAULT 1;
DECLARE sum INT;

SET sum=m+n;

SELECT sum;
END;

存储过程

DROP PROCEDURE IF EXISTS search_people;
CREATE PROCEDURE search_people(IN page_index INT,IN page_size INT, OUT total_count INT, OUT total_page INT)

BEGIN
		DECLARE begin_no INT;
		SET begin_no = (page_index-1)*page_size;
 
		-- 分页查询列表
		SELECT * FROM people
		WHERE id >= (
			SELECT id FROM people
			ORDER BY id ASC
			LIMIT begin_no,1
		)
		ORDER BY id ASC
		LIMIT page_size;
 
		-- 计算数据总数
		SELECT COUNT(1) INTO total_count FROM people;
 
		-- 计算总页数
		SET total_page = FLOOR((total_count + page_size - 1) / page_size);
 
END

-- 调用
CALL search_people(3,10,@count,@page);

SELECT @count,@page;


delimiter $ -- 将默认的结束符号;替换
CREATE PROCEDURE insert_peo()
BEGIN
	INSERT INTO people(name,age) VALUES('qwer',44);
END $


CALL insert_peo();




CREATE PROCEDURE selectPeo(in name VARCHAR(20))
BEGIN
	SELECT * FROM people o WHERE o.`name`=name;
END 


CALL selectPeo('qwer');





CREATE PROCEDURE selectPeo2(in name VARCHAR(20),in age INT)
BEGIN
	DECLARE result INT DEFAULT 0;

	SELECT COUNT(1) INTO result FROM people o WHERE o.`name`=name and o.age=age;
	
	SELECT IF(result>0,'成功','失败') jg;
END 


CALL selectPeo2('qwer',44);



CREATE PROCEDURE doubleVal(INOUT m INT, INOUT n INT)
BEGIN
	set m=m*2;
	set n=n*2;
END 


SET @m=20;
SET @n=10;

CALL doubleVal(@m,@n);

SELECT @n,@m;

-- 删除存储过程
DROP PROCEDURE doubleVal;
-- 查看存储过程
SHOW CREATE PROCEDURE doubleVal;

函数

-- 函数
DROP FUNCTION total;
CREATE FUNCTION total() RETURNS INT
BEGIN
	
	DECLARE total INT DEFAULT 0;
	SELECT COUNT(*) INTO total FROM people;
	RETURN total;

END

SELECT total();


CREATE FUNCTION totalByName(name VARCHAR(20)) RETURNS INT
BEGIN
	
	DECLARE total INT DEFAULT 0;
	SELECT COUNT(*) INTO total FROM people p  WHERE p.name = name;
	RETURN total;

END

SELECT totalByName('ls');


-- 查看函数
SHOW CREATE FUNCTION totalByName;


CREATE FUNCTION doubleVal(m INT, n INT) RETURNS INT
BEGIN
	RETURN m+n;
END 


SELECT doubleVal(1,3);

查看优化语句

EXPLAIN EXTENDED 
	SELECT
		*
	FROM
		people p
SHOW WARNINGS;

执行顺序

索引优化

连表查询时如果是左外连接则在右表连接字段建立索引，右外连接反之；

1. ！= 和 <> 和 is null 和 is not null 会导致索引失效
2. varchar类型失去单引号会触发隐式转换从而导致索引失效

当必须使用%xx%作为条件查询时，查询列都为索引列时将会触发索引覆盖，从而查询用上索引优化