mysql语句使用记录

版本

查看版本

select version();

库

创建库

-- 创建名为test的库
CREATE DATABASE test;

删除库

DROP DATABASE test;

表

创建表

-- 创建表,id为主键
CREATE TABLE `test` (
  `id` bigint(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL DEFAULT '' COMMENT '备注内容',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP  COMMENT '更新时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='测试表';

删除表

-- 删除test表
drop table test;

字段

添加字段

-- 给test表添加code字段，字段类型为varchar，长度255
ALTER TABLE test ADD COLUMN  code VARCHAR(255);

-- NOT NULL DEFAULT ''：不能为null，默认空，可不加
-- COMMENT：设置备注，该参数必须在AFTER之前，可不加
-- AFTER：在某列后增加该字段，可不加
ALTER TABLE test ADD COLUMN  code VARCHAR(255) NOT NULL DEFAULT '' COMMENT '备注内容' AFTER name ;

-- 在第一行添加字段
ALTER TABLE test ADD COLUMN  code VARCHAR(255) FIRST;

-- 浮点类型
ALTER TABLE test ADD COLUMN  code FLOAT;

--时间类型,不自动生成时间
ALTER TABLE test ADD COLUMN start_time DATETIME;

--时间类型,自动生成时间
ALTER TABLE test ADD COLUMN start_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ;

-- 添加不为主键的自增字段
ALTER TABLE test ADD COLUMN test_id INT(11);
ALTER TABLE test ADD KEY id_index(test_id );
ALTER TABLE test MODIFY test_id INT(11) auto_increment NOT NULL;

修改字段

ALTER TABLE test MODIFY COLUMN code VARCHAR(255);

删除字段

ALTER TABLE test DROP COLUMN code;

索引

添加索引

-- 普通索引（允许出现相同的索引内容）
-- 给表名为test的name，code字段创建索引，索引名为idx_name
CREATE INDEX idx_name ON test(name，code);

-- UNIQUE索引（不可以出现相同的值，可以有NULL值）
CREATE UNIQUE INDEX idx_name ON test(name，code);

删除索引

-- 删除test表的idx_name索引
ALTER TABLE test DROP INDEX idx_name;

外键

添加外键

-- 在test表添加名为idx_code的外键，外键关联到test2表中的id字段，test2表中的id必须为主键
-- 默认RESTRICT：在外键关联的所有子表相关内容删除时，才能删除主表的该字段内容(默认设置，是安全的设置)
ALTER TABLE test ADD CONSTRAINT idx_code FOREIGN KEY (code) REFERENCES test2(id);

-- CASCADE：父表delete、update的时候，子表会delete、update掉相关内容
ALTER TABLE test ADD CONSTRAINT idx_code FOREIGN KEY (code) REFERENCES test2(id) ON DELETE CASCADE ON UPDATE CASCADE;

-- SET NULL：父表delete、update的时候，子表会将关联记录的外键字段所在列设为null，在设计子表时该字段不能设为not null
ALTER TABLE test ADD CONSTRAINT idx_code FOREIGN KEY (code) REFERENCES test2(id) ON DELETE SET NULL ON UPDATE SET NULL;

-- NO ACTION：与RESTRICT一样
ALTER TABLE test ADD CONSTRAINT idx_code FOREIGN KEY (code) REFERENCES test2(id) ON DELETE NO ACTION ON UPDATE NO ACTION;

删除外键

-- 删除test表的idx_code外键
ALTER TABLE test DROP FOREIGN KEY idx_code;

添加注释

-- test表增加注释，可用\n来换行
ALTER TABLE test COMMENT '测试表\n 用于测试';

其他sql

将某表或某几张中所有字段数据新增到另一张表

-- 字段跟位置需相同
-- 括号中sql可多表关联查询
INSERT INTO `table_test` (
	SELECT
		*
	FROM
		user_t
	WHERE
		age > 10
);

将某表或某几张表中部分字段数据更新到另一张表

-- 括号中sql可多表关联查询
UPDATE `table_test` t
INNER JOIN (
	SELECT
		`name`,
		`code`
	FROM
		user_t
	WHERE
		age > 10
) u ON t.id = u.test_id
SET t.`name` = u.`name`,
 t.`code` = u.`code`;

去重查询

-- 查询table_test表中type字段不重复内容
SELECT DISTINCT type FROM table_test;

-- 当查询字段为多个时，结果为多个字段组合的不重复数据，单个字段内容会重复
SELECT DISTINCT type,`name` FROM table_test;

查询重复数据

-- 查询table_test表中name字段重复的内容及个数
SELECT `name`,count(*) AS num FROM table_test GROUP BY `name` HAVING num>1;

-- 查询table_test表中name字段重复的所有内容
SELECT
	*
FROM
	table_test
WHERE
	`name` IN (
		SELECT
			`name`
		FROM
			table_test
		GROUP BY
			`name`
		HAVING
			count(*) > 1
	)

查询数据库中的相似表表名

-- 查询所有库中t_user的相似表表名
SELECT table_name FROM information_schema.`TABLES` WHERE table_name LIKE 't_user%';

--  查询此库中t_user的相似表
SHOW TABLES LIKE 't_user%';

数据截取

-- 截取name字段中第1个 “-”之前的所有字符，如：name="a-b-c", 结果为：a
SELECT substring_index(name,"-",1) FROM table_test;

-- 截取name字段中倒数第1个 “-”之后的所有字符，如：name="a-b-c", 结果为：c
SELECT substring_index(name,"-",-1) FROM table_test;

-- 截取name字段中第1个“-”到第2个“-”之间的所有字符，如：name="a-b-c", 结果为：b
SELECT substring(name,char_length(substring_index(name,"-",1))+2,char_length(substring_index(name,"-",2))-(name,char_length(substring_index(name,"-",1))+1)) FROM table_test;

-- 截取左边的1个字符，如：name="a-b-c", 结果为：a
SELECT left(name,1) FROM table_test;

-- 截取右边的1个字符，如：name="a-b-c", 结果为：c
SELECT right(name,1) FROM table_test;

-- 截取第2个字符后的所有字符，如：name="a-b-c", 结果为：-b-c
SELECT substring(name,2) FROM table_test;
SELECT substr(name,2) FROM table_test;
SELECT mid(name,2) FROM table_test;

-- 截取第2个字符后的两个字符，如：name="a-b-c", 结果为：-b
SELECT substring(name,2,2) FROM table_test;
SELECT substr(name,2,2) FROM table_test;
SELECT mid(name,2,2) FROM table_test;

-- 截取倒数第3个字符后的所有字符，如：name="a-b-c", 结果为：b-c
SELECT substring(name,-3) FROM table_test;
SELECT substr(name,-3) FROM table_test;
SELECT mid(name,-3) FROM table_test;

-- 截取倒数第3个字符后的两个字符，如：name="a-b-c", 结果为：b-
SELECT substring(name,-3,2) FROM table_test;
SELECT substr(name,-3,2) FROM table_test;
SELECT mid(name,-3,2) FROM table_test;

时间操作

-- 时间转字符串 结果：2020-10-11 10:11:12
select date_format(now(), '%Y-%m-%d %H:%i:%s');

%T 时间,24 小时(hh:mm:ss) 
%r 时间,12 小时(hh:mm:ss [AP]M) 
%j 一年中的天数(001……366) 
%p AM或PM 
%w 一个星期中的天数(0=Sunday ……6=Saturday ）
%y 年, 数字, 2 位 
%c 月, 数字(1……12) 
%b 缩写的月份名字(Jan……Dec) 
%M 月名字(January……December)
%W 星期名字(Sunday……Saturday) 
%a 缩写的星期名字(Sun……Sat) 

-- 时间戳转换成字符串 结果：2020-10-11 10:11:12
select from_unixtime(1602382272)

-- 时间戳转换成字符串 结果：2020-10-11 10:11:12
select from_unixtime(1602382272,'%Y-%m-%d %H:%i:%s')

-- 字符串转换成时间戳（秒）
select unix_timestamp("2020-10-11 10:11:12");

-- 时间转时间戳（秒）
select unix_timestamp(now());   

-- 字符串转时间，格式化  结果：2020-10-11
select str_to_date('2020-10-11','%Y-%m-%d')

-- 当前时间加1小时 结果：2020-10-11 11:11:12
-- 可加：year,month,day,hour,minute,second
select time, date_add(now(),interval 1 hour)

-- 当前时间减1小时 结果：2020-10-11 09:11:12
-- 可加：year,month,day,hour,minute,second
select time, DATE_SUB(now(),INTERVAL 1 hour);

批量自增

set @num=0;
update table_test SET seq_no = (select @num := @num +1 as num) where parentid="xxx";

计算

-- MAX(value) :最大值
-- MIN(value): 最小值
-- AVG(value): 平均值
-- SUM(value): 求和
-- VAR_SAMP(value) ： 求方差
select MAX(value) maxValue, MIN(value) minValue, AVG(value) avgValue, SUM(value) sumValue, VAR_SAMP(value) varValue from test ;

日期增加一天

update table_test set endTime = DATE_ADD(endTime, interval 1 day) where startTime > endTime;