ddl
创建表:
CREATE TABLE ` table_name` (
` id` bigint ( 20 ) NOT NULL AUTO_INCREMENT COMMENT '主键id' ,
` xxx1` varchar ( 255 ) NOT NULL COMMENT 'comment1' ,
` xxx2` int ( 11 ) DEFAULT 0 COMMENT 'comment2' ,
` xxx3` decimal ( 20 , 2 ) DEFAULT '0.00' COMMENT 'comment3' ,
` is_deleted` int ( 1 ) DEFAULT 0 COMMENT '删除标示 0表示未删除 1表示删除' ,
` created_by` varchar ( 255 ) NOT NULL COMMENT '创建人' ,
` created_date` datetime NOT NULL COMMENT '创建时间' ,
` last_updated_by` varchar ( 255 ) NOT NULL COMMENT '最后更新人' ,
` last_updated_date` datetime NOT NULL COMMENT '最后更新时间' ,
PRIMARY KEY ( ` id` ) USING BTREE ,
UNIQUE KEY ` uni_key_xxx1` ( ` xxx1` ) USING BTREE COMMENT 'comment1'
) ENGINE = InnoDB DEFAULT CHARSET = utf8 ROW_FORMAT= DYNAMIC COMMENT = 'table comment' ;
创建视图:
CREATE VIEW ` view_name` AS select * from ` table_name` ;
删除视图:
drop view if exists view_name;
删除表:
drop table if exists table_name;
清空表数据:
truncate table table_name;
新增字段:
alter table table_name add COLUMN new_column_name varchar ( 255 ) COMMENT 'new comment' ;
删除字段:
alter table table_name drop COLUMN new_column_name;
修改字段:
ALTER TABLE table_name MODIFY cloumn_name VARCHAR ( 1000 ) ;
创建存储过程:
DELIMITER
CREATE PROCEDURE procedure_name( IN param1 VARCHAR ( 255 ) )
BEGIN
END
DELIMITER ;
删除存储过程:
drop procedure IF EXISTS procedure_name;
dml
插入:
INSERT INTO ` schemaName` . ` tableName` ( ` id` , ` xxx1` , ` xxx2` ) VALUES ( 1 , 'xxx1' , 'xxx2' ) ;
更新:
UPDATE ` schemaName` . ` tableName` set column_name = 'xxx' where id = 'xxx' ;
删除:
DELETE FROM ` schemaName` . ` tableName` where id = 'xxx' ;
dql
查询:
SELECT * FROM ` schemaName` . ` tableName` where id = 'xxx' ;
查询数量:
SELECT COUNT ( * ) FROM ` schemaName` . ` tableName` where id = 'xxx' ;
SELECT COUNT ( 1 ) FROM ` schemaName` . ` tableName` where id = 'xxx' ;
其他
查询表相关信息:
select * from information_schema. ` TABLES` where TABLE_SCHEMA = 'schemaName' and TABLE_NAME = 'tableName' ;
查询视图相关信息:
select * from information_schema. ` VIEWS` where TABLE_SCHEMA = 'schemaName' and TABLE_NAME = 'tableName' ;
查询字段相关信息:
select * from information_schema. ` COLUMNS` where TABLE_SCHEMA = 'schemaName' and TABLE_NAME = 'tableName' ;
查询索引相关信息:
select * from information_schema. ` STATISTICS` where TABLE_SCHEMA = 'schemaName' and TABLE_NAME = 'tableName' ;
查询时区:
show variables like '%time_zone%' ;
查询字符集:
SHOW VARIABLES LIKE 'character_set%' ;
查询连接超时时间:
SHOW VARIABLES LIKE 'wait_timeout' ;
查询最大连接数:
SHOW VARIABLES LIKE 'max_connections' ;
设置超时和最大连接数:
SET GLOBAL wait_timeout = 300 ;
SET GLOBAL max_connections = 500 ;
查询mysql版本号:
select version( ) ;
查询当前时间:
select sysdate( ) ;
select now ( ) ;
查询所有表名:
SHOW TABLES ;
查询建表语句:
SHOW CREATE TABLE ` schemaName` . ` tableName` ;
查询建视图语句:
SHOW CREATE VIEW ` schemaName` . ` viewName` ;