基本语法
新建数据库
CREATE DATABASE test1 DEFAULT CHARACTER SET utf8;
创建表
CREATE TABLE IF NOT EXISTS test_table
(id int(11) NOT NULL AUTO_INCREMENT, # `AUTO_INCREMENT`自增
name VARCHAR(20) DEFAULT NULL, #`DEFAULT`默认为null
fee decimal(10,2) UNSIGNED NULL, #`UNSIGNED` 不能为负
user_id int(11) comment '用户id', #`comment `备注
opt_id int(11) NOT NULL,
create_time datetime NULL ON UPDATE CURRENT_TIMESTAMP, #`ON UPDATE CURRENT_TIMESTAMP`根据系统时间来更新
PRIMARY KEY(id), # `PRIMARY KEY`主键
INDEX index_userId(user_id), # `INDEX `索引
CONSTRAINT `ref_optId` FOREIGN KEY (opt_id) REFERENCES `demo`(`id`) #`REFERENCES `外键约束
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='测试';
复制表
CREATE TABLE test2 like test;
#创建表test2跟test一样的结构
创建临时表
关闭连接,自动删除表
CREATE TEMPORARY TABLE tmp_table
(id int(11) not NULL AUTO_INCREMENT,
name VARCHAR(20) DEFAULT NULL,
user_id int(11) COMMENT '用户id',
opt_id int(11) NOT NULL,
fee decimal(10,2) UNSIGNED NULL,
create_time datetime NULL ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY(id),
INDEX index_userId(user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='测试';
创建表分区
DROP TABLE if EXISTS test_table;
CREATE TABLE IF NOT EXISTS test_table
(id int(11) not NULL AUTO_INCREMENT,
name VARCHAR(20) DEFAULT NULL,
ddate date NOT NULL,
PRIMARY KEY(id,ddate))
ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='测试'
/* RANGE:基于一个连续区间的列值,把多行分配给分区
LIST:列值匹配一个离散集合;
Hash:基于用户定义的表达式的返回值选择分区,表达式对要插入表中的列值进行计算。这个函数可以包含SQL中有效的,产生非负整数值的任何表达式
KEY:类似于HASH分区,区别在于KEY 分区的表达式可以是一列或多列,且MYSQL提供自身的HASH函数
*/
PARTITION BY RANGE COLUMNS (ddate)( #分区字段(ddate) 必须是主键字段
PARTITION p0 VALUES less than ('2018-01-01'),
PARTITION p1 VALUES less than ('2018-07-01'),
PARTITION p2 VALUES less than ('2019-01-01'),
PARTITION p3 VALUES less than ('2019-07-01'),
PARTITION p4 VALUES LESS THAN MAXVALUE);
创建视图
CREATE view v_test as
selct id,name from test;
创建自定义函数
DELIMITER ;;
CREATE FUNCTION f_test(name varchar(20))
RETURNS VARCHAR(20)
BEGIN
DECLARE result VARCHAR(20);
SET result =name;
RETURN result;
END
;;
创建自定义存储过程
DELIMITER ;;
CREATE PROCEDURE p_test(aId INT(11))
SQL SECURITY INVOKER #可以被其他调用
BEGIN
DECLARE result VARCHAR(20) DEFAULT 'null';
DECLARE is_error INTEGER;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET is_error=1;
START transaction; # 开始事务
update test_table SET name ='张三' where id =aId;
SELECT name into result from test_table where id =aId;
IF is_error =1 THEN
ROLLBACK; # 失败回滚
ELSE commit; # 提交
END IF;
select result;
END ;;
创建事件
CREATE EVENT `e_test`
ON SCHEDULE EVERY 1 DAY STARTS '2018-01-01 00:00:00' ENDS '2018-12-31 23:59:59'
ON COMPLETION NOT PRESERVE
ENABLE
DO
CALL p_test(s11); #调用存储过程
创建游标
DELIMITER ;;
CREATE PROCEDURE p_test(aId INT(11))
SQL SECURITY INVOKER
BEGIN
DECLARE Done INT DEFAULT 0;
DECLARE is_error INTEGER;
DECLARE name,addr,mail varchar(100);
DECLARE cur_test cursor for # 创建游标
SELECT name,address,mail
from test_table ;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET Done = 1;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET is_error=1;
start transaction;
OPEN cur_test; #打开游标
FETCH NEXT FROM cur_test into
name,addr,mail;
REPEAT if NOT Done THEN #循环
SELECT 'success';
END IF;
FETCH NEXT FROM cur_test INTO name,addr,mail;
UNTIL Done END REPEAT; #循环结束
CLOSE cur_test; #关闭游标
IF is_error =1 THEN
ROLLBACK; # 失败回滚
ELSE commit; # 提交
END IF;
END ;;
创建触发器
CREATE TRIGGER trig_test
AFTER # BEFORE、AFTER 触发时间
INSERT #INSERT、UPDATE 、DELETE 触发条件
ON demo #表名
FOR EACH ROW
BEGIN
INSERT INTO test_table(id, name) #执行语句
VALUES (112,'王炸');
END;