没长正的技术专栏 勤动手、多思考

MySQL

2014-01-05

阅读:

DB
2020-03-27

基本信息及意义

MySQL 常用语句积累

表信息查询

(1)查询表名称信息 SQL 语句

SELECT
	table_name,
	TABLE_COMMENT
FROM
	information_schema. TABLES
WHERE
	table_schema = 'xxx'
AND LENGTH(TABLE_COMMENT) > 0;
-- and table_type='base table';

(2)统计表的数据量

SELECT
	TABLE_NAME,
	TABLE_ROWS
FROM
	`information_schema`.`tables`
WHERE
	`table_schema` = 'XXXX';

(3)查询数据库表列及列字段备注信息

SELECT
	t.TABLE_NAME,
	t.TABLE_COMMENT,
	c.COLUMN_NAME,
	c.COLUMN_TYPE,
	c.COLUMN_COMMENT
FROM
	information_schema. TABLES t,
	INFORMATION_SCHEMA. COLUMNS c
WHERE
	c.TABLE_NAME = t.TABLE_NAME
	AND t.`TABLE_SCHEMA` = 'xxx'

(4)数据库表操作


-- <1.修改表名  
alter table oldTableName rename newTableName; 
rename oldTableName to newTableName; 

-- <2.添加表列  
alter table tableName add  column columnName varchar(20); 

-- <3. 删除表列  
alter table tableName drop  column columnName; 

-- <4. 修改表列类型  
alter table tableName modify columnName varchar(20) 
alter table tableName change address address  varchar(20)  

-- <5. 修改表列名(**)
alter table tableName change  column oldColumnName newColumnName varchar(20)

(5) 查看进程、杀掉进程

-- 查询进程
SHOW PROCESSLIST;

-- 杀掉进程
kill pid;

函数

(1) 系统函数


-- <1.四舍五入

select ROUND(1.015,2) -- 保留两位小数  1.02

-- <2.非空 IFNULL(expr1,expr2)函数

select IFNULL(fieldName,0) from tableName;

-- <3. 时间函数 NOW()、DATE_FORMART(date,formart)  ...

select DATE_FORMAT(NOW(), "%Y-%m-%d %H:%i:%s")

-- <4. 一列多行数据转为一列一行数据 1,2,3

select GROUP_CONCAT(fieldName) from tableName;


(2) 自定义函数(使用)

-- ----------------------------
-- Function structure for getConversionRate
-- ----------------------------
DROP FUNCTION IF EXISTS `getConversionRate`;
DELIMITER ;;
CREATE FUNCTION `getConversionRate`(`reportCustomers` integer,`dealedCustomers` integer) RETURNS varchar(20) CHARSET utf8
BEGIN
	#转化率
	IF reportCustomers = 0 || dealedCustomers =0 THEN
		RETURN '';
	ELSE
		RETURN concat(ROUND((dealedCustomers*100)/reportCustomers,1),'%');
	END IF;
END
;;
DELIMITER ;

数据备份

(1)通过查询语句备份

create table newTableName select * from oldTableName;  -- 主键、唯一键等不会备份

(2) 插入数据

insert into newTableName(field1,field2) select field1,field2 from otherTabelName; -- insert into  select

(3) 插入或更新 INSERT INTO table ON DUPLICATE KEY UPDATE

-- 2020.05.14
INSERT INTO dc_sale_test ( upload_id, create_time, update_time, create_user, update_user, org_name, statistics_date, customer_visit, sign_amount )
VALUES
	( 60, '2020-05-14 16:33:56.284', '2020-05-14 16:33:56.284', 'XXX1', 'XXX1', '重庆', '2020-05-14', '20', '50' ),
	( 60, '2020-05-14 16:33:56.284', '2020-05-14 16:33:56.284', 'XXX1', 'XXX1', '重庆', '2020-05-14', '21', '51' )
	ON DUPLICATE KEY UPDATE
	customer_visit = VALUES( customer_visit ),
	sign_amount =VALUES( sign_amount ),
	upload_id =VALUES( upload_id ),
	update_time =VALUES( update_time ),
	update_user =VALUES( update_user )

# 分析: 如果存在主键或唯一键则根据其新增或更新;如果主键或唯一键都不存在,则插入

参考:

菜鸟教程

SQL 优化

时间函数 1

时间函数 2


欢迎拍砖,多多交流,转载请注明出处:[没长正的技术专栏](http://blog.meizhangzheng.com) 如涉及侵权问题,请发送邮件到xsj34567@163.com,如情况属实本人将会尽快删除。


上一篇 数据库概述

下一篇 DB性能简析

Comments

Content