mysql基本增删查改等

-- 创建数据库
CREATE DATABASE kangyu_test CHARACTER set = utf8;

-- 创建表
-- 如果存在就删除
DROP TABLE IF EXISTS `PETS_S`;
CREATE TABLE IF NOT EXISTS `PETS_S`(
ID INT UNSIGNED AUTO_INCREMENT,
love VARCHAR(20) NOT null,
 PRIMARY KEY ( ID )
)ENGINE=InnoDB DEFAULT CHARSET=utf8

-- 查询
SELECT * FROM PETS_S;
-- 新增
INSERT PETS_S(num) VALUES(40);
-- 修改
update pets_s t SET t.num = 20 WHERE t.id = 1;
-- 删除 
DELETE FROM pets_s WHERE id = 1;

-- 添加字段
ALTER TABLE PETS_S ADD color5 VARCHAR(5) NOT NULL;
ALTER TABLE PETS_S ADD color2 VARCHAR(10) NOT NULL;
ALTER TABLE PETS_S ADD num int NOT NULL;
-- 删除字段
ALTER TABLE PETS_S DROP colorr;
-- 修改字段
ALTER TABLE pets_s CHANGE color new_color VARCHAR(10);


-- 添加索引
# 唯一索引
ALTER TABLE pets_s ADD UNIQUE(color2,color3);
# 普通索引
ALTER TABLE pets_s ADD INDEX index_color(color);
-- 删除索引
ALTER TABLE pets_s DROP INDEX love;
--查看索引
SHOW INDEX from pets_s 

-- 创建存储过程
DELIMITER //
CREATE PROCEDURE PROC5(IN P_ID INTEGER)
BEGIN
	DECLARE ppid INT;
	SELECT P_ID;
	SET P_ID = 2;
	SELECT P_ID;
END //
DELIMITER;
-- 调用存储过程
CALL proc4(3);


-- 常用函数
	-- DISTINCT:  去重重复
	-- 1.聚合函数:AVG,COUNT,MAX，MIN，SUM
  -- 分组聚合函数： select [聚合函数] 字段名 from 表名 [where 查询条件] [group by 字段名] [having 过滤条件]
	SELECT COUNT(*)FROM pets_s;
	SELECT sum(num)FROM pets_s;
	SELECT SUM(NUM) as sumNum,t.id FROM pets_s T  GROUP BY id HAVING sumNum>=20 ORDER BY t.ID DESC;

	-- 2. 字符串函数
	-- CONCAT：连接函数
	SELECT CONCAT(num,',',id) FROM pets_s;
	-- FORMAT 将数字格式化为字符串。第二个参数为小数的位数。
	SELECT FORMAT(12345.67,2);
  -- REPLACE 替换
	SELECT REPLACE('abacbcc', 'cc', '');

	-- 3. 日期函数
	SELECT DATE();

	SELECT CURDATE();

	SELECT unix_timestamp();

	select date_format(DATE(now()), '%Y年%m月%d日%H时%i分%s秒');

	-- 日期格式化
	SELECT DATE_FORMAT(NOW(),'%m%s')


SELECT * FROM pets_s GROUP BY color;

SELECT * FROM pets_s t  WHERE not EXISTS(SELECT * FROM pets_s s where s.id = 3 AND s.id = t.ID);