数据库表
建表
create table user(
id int(19) primary key auto_increment comment '主键',
name varchar(300) comment '姓名',
create_time date comment '创建时间'
)comment = '用户表';表添加新的字段
--table_name：表名
--column_name：添加的字段名称
--old_column：新增加的字段是添加在old_column字段的后面
ALTER TABLE table_name ADD COLUMN column_name VARCHAR(100) DEFAULT NULL COMMENT '新加字段' AFTER old_column;修改注释
修改表注释
alter table user comment = '用户信息表';修改字段注释
alter table user modify column name varchar(300) comment '名字';删除表
drop table user ; --直接将表删除，无法找回。
truncate （table） user ; --删除表中所有数据，但不能与where一起使用；
delete from tb (where); --删除表中数据，但可以与where连用，删除特定行；
区别：truncate和delete的区别
1、事务：truncate是不可以rollback的，但是delete是可以rollback的；
原因：truncate删除整表数据(ddl语句,隐式提交)，delete是一行一行的删除，可以rollback
2、效果：truncate删除后将重新水平线和索引(id从零开始) ,delete不会删除索引
3、 truncate 不能触发任何Delete触发器。
4、delete 删除可以返回行数
索引
添加索引
ALTER table tableName ADD INDEX indexName(columnName)
--创建普通索引
CREATE INDEX index_name ON table_name(col_name);
--创建唯一索引
CREATE UNIQUE INDEX index_name ON table_name(col_name);
--创建普通组合索引
CREATE INDEX index_name ON table_name(col_name_1,col_name_2);
--创建唯一组合索引
CREATE UNIQUE INDEX index_name ON table_name(col_name_1,col_name_2);
删除索引
DROP INDEX [indexName] ON mytable; 显示表索引
SHOW INDEX FROM table_name; 插入语句
insert into tt values (null,3,-3),(null,4,-4);
value与values都是正确的，只是两者对不同语句插入数量的执行效率各不相同。在插入单行的时候使用values，在插入多行的时候使用value。
数据更新（有时更新，没有时插入）
ON DUPLICATE KEY UPDATE
insert时指定了ON DUPLICATE KEY UPDATE，并且insert后会导致在一个UNIQUE索引（唯一索引）或PRIMARY KEY（主键）中出现重复值，则执行UPDATE
语法1（批量操作）：
INSERT INTO 表名 (ID, 列1, 列2, ...)
VALUES (值1, 值2, ...),
(值3, 值4, ...),
...
ON DUPLICATE KEY UPDATE 列1=VALUES(列1), 列2=VALUES(列2), ...;写法2：
INSERT INTO `upms_resource`(line_no,id,create_time,update_time,version,resource_name,resource_type,authority_identifier,url,application_identifier,parent_resource_id,application_id)
VALUES (null, 1151669296037425196,NOW(), NOW(), 0,'userList', 'BUTTON', 'open-api:user:lis','/auth/open-api/user/list' , 'finance-system', 1151669296037425154, 1)
on duplicate key
update resource_name=VALUES(resource_name),
resource_type=VALUES(resource_type),
url=VALUES(ur),
parent_resource_id=VALUES(parent_resource_id);语法2：
START TRANSACTION;
INSERT INTO 表名 (ID, 列1, 列2, ...)
VALUES (值1, 值2, ...)
ON DUPLICATE KEY UPDATE 列1=新值1, 列2=新值2, ...;
COMMIT;
-----------------------------
INSERT INTO 表名 (ID, 列1, 列2, ...)
SELECT 值1, 值2, ...
FROM dual
ON DUPLICATE KEY UPDATE 列1=新值1, 列2=新值2, ...;写法2：
insert into user(userid,username,userage,usersex) values(1,"qyf",22,"nv")
on duplicate key update username="qinyufeng",userage=24,usersex="女";REPLACE
表在一个字段上建立了唯一索引，使用REPLACE insert 时，系统返回了所影响的行数
如果返回1，说明在表中并没有重复的记录，REPLACE就和INSERT的功能一样。
如果返回2，说明有一条重复记录，系统自动先调用了 DELETE删除这条记录，然后再记录用INSERT来insert这条记录。
如果返回的值大于2，那说明有多个唯一索引，有多条记录被删除和insert。
上面曾提到REPLACE可能影响3条以上的记录，这是因为在表中有超过一个的唯一索引。在这种情况下，REPLACE将考虑每一个唯一索引，并对每一个索引对应的重复记录都删除，然后insert这条新记录。
在高并发情况下，使用 REPLACE INTO 语句可能会导致锁冲突。这是因为 REPLACE INTO 语句实际上是先删除原有行，然后插入新的行，这涉及到对数据表的写操作，可能会引发锁定问题。
语法：
REPLACE INTO 表名 (ID, 列1, 列2, ...)
VALUES (值1, 值2, ...);两种写法：
replace into users set id = 123, name = '赵本山', age = 50;REPLACE INTO users(id, name, age) VALUES(123, '赵本山', 50), (134,'Mary',15);多表联合更新
UPDATE
使用“UPDATE table1 t1,table2,...,table n”的方式来多表更新
UPDATE table1 t1, table2 t2
SET
t2.name = 1,
t1.name = 1
WHERE
t1.id= t2.id
AND
t1.id= 89;INNER JOIN
UPDATE table1 t1
INNER JOIN table2 t2
ON t1.id= t2.id
SET
t2.name = 1,
t1.name = 1;
WHERE
t1.id = 89;LEFT JOIN
UPDATE table1 t1
LEFT JOIN table2 t2
ON t1.id= t2.id
SET
t2.name = 1,
t1.name = 1;
WHERE
t1.id = 89;子查询
UPDATE table1 t1
SET t1.COMM_STATUS=(SELECT COMM_STATUS FROM table2 WHERE id = t1.id)函数
取整函数
ROUND函数
ROUND(X) -- 表示将值 X 四舍五入为整数，无小数位
ROUND(X,D) -- 表示将值 X 四舍五入为小数点后 D 位的数值，D为小数点后小数位数。若要保留 X 值小数点左边的 D 位，可将 D 设为负值。
FLOOR函数
FLOOR(X)表示向下取整，只返回值X的整数部分，小数部分舍弃。
CEILING函数
CEILING(X) 表示向上取整，只返回值X的整数部分，小数部分舍弃。
向上取整 不管四舍五入的规则 只要后面有小数前面的整数就加1
向下取整 不管四舍五入的规则 只要后面有小数忽略小数
日期函数
时间格式化：DATE_FORMAT(date,format)
--日期转换为字符串
str_to_date('2016-09-09 15:43:28','%Y-%m-%d %H:%i:%s')字符串转换为日期：str_to_date(str, format)
--字符串转换为日期
date_format(now(), '%Y-%m-%d %h:%i:%s')format参数的格式
| %a | 缩写星期名 |
| %b | 缩写月名 |
| %c | 月，数值 |
| %D | 带有英文前缀的月中的天 |
| %d | 月的天，数值(00-31) |
| %e | 月的天，数值(0-31) |
| %f | 微秒 |
| %H | 小时 (00-23) |
| %h | 小时 (01-12) |
| %I | 小时 (01-12) |
| %i | 分钟，数值(00-59) |
| %j | 年的天 (001-366) |
| %k | 小时 (0-23) |
| %l | 小时 (1-12) |
| %M | 月名 |
| %m | 月，数值(00-12) |
| %p | AM 或 PM |
| %r | 时间，12-小时（hh:mm:ss AM 或 PM） |
| %S | 秒(00-59) |
| %s | 秒(00-59) |
| %T | 时间, 24-小时 (hh:mm:ss) |
| %U | 周 (00-53) 星期日是一周的第一天 |
| %u | 周 (00-53) 星期一是一周的第一天 |
| %V | 周 (01-53) 星期日是一周的第一天，与 %X 使用 |
| %v | 周 (01-53) 星期一是一周的第一天，与 %x 使用 |
| %W | 星期名 |
| %w | 周的天 （0=星期日, 6=星期六） |
| %X | 年，其中的星期日是周的第一天，4 位，与 %V 使用 |
| %x | 年，其中的星期一是周的第一天，4 位，与 %v 使用 |
| %Y | 年，4 位 |
| %y | 年，2 位 |
日期增加：date_add()
date_add(now(), interval 1 day); -- 加1天
date_add(now(), interval 1 hour); --加1小时
date_add(now(), interval 1 minute); -- 加1分钟
date_add(now(), interval 1 second); --加1秒
date_add(now(), interval 1 microsecond); --加1毫秒
date_add(now(), interval 1 week); --加1周
date_add(now(), interval 1 month); --加1月
date_add(now(), interval 1 quarter); --加1季
date_add(now(), interval 1 year); --加1年
adddate(),addtime()函数，可以用date_add()来替代
日期减少：date_sub()
subdate(),subtime()函数，可以用date_sub()来替代
日期、时间相减函数
period_add(P,N)：日期加/减去N月，P：YYYYMM”或者“YYMM”；N：增加或减去 N month(月)。
period_diff(P1,P2)：日期 P1-P2，返回 N 个月。
datediff(date1,date2)：两个日期相减 date1 date2，返回天数。
timediff(time1,time2)：两个日期相减 time1 time2，返回time差值。
获取当前时间的函数
NOW(); 当前系统日期时间
sysdate(); 当前系统日期时间
CURDATE(); 当前系统年月日
current_date; 当前系统年月日
CURTIME(); 当前系统时间
year(current_date); 当前系统年
month(current_date); 当前系统月
day(current_date); 当前系统日
time(sysdate()); 当前系统时间
hour(sysdate()); 当前系统小时
minute(sysdate()); 当前系统分钟
second(sysdate()); 当前系统秒
microsecond(sysdate()); 当前系统毫秒
筛选日期
查询本月数据：date_format(时间字段名,'%y%m') = date_format(curdate(),'%y%m')
查询上月数据：period_diff(date_format(now(),'%y%m'),date_format(时间字段名,'%y%m')) = 1
查询本季度数据：quarter(时间字段名)=quarter(now())
查询上季度数据：quarter(时间字段名)=quarter(date_sub(now(),interval 1 quarter))
查询本年数据：year(时间字段名)=year(now())
查询上年数据：year(时间字段名)=year(date_sub(now(),interval 1 year))
查询本周的数据：yearweek(date_format(时间字段名,'%y-%m-%d')) = yearweek(now())
查询上周的数据：yearweek(date_format(时间字段名,'%y-%m-%d')) = yearweek(now())-1
DATE(date); 提取日期或日期/时间表达式的日期部分
TO_DAYS(date); 返回一个天数(从 0 年开始的天数)
week(date,[mode]); 查询日期属于哪个周
date是要获取周数的日期。mode是一个可选参数，用于确定周数计算的逻辑。
它允许您指定本周是从星期一还是星期日开始，返回的周数应在0到52之间或0到53之间
参考：mysql中如何获取年、月、日及本周是一年中的第几周？？_mysql求4月是一年中的那几周_树上的疯子^的博客-CSDN博客
秒数与时分秒互相转换
SEC_TO_TIME()函数
将秒数转换成时分秒
注意：此函数是指将传入的秒数转换成距离当天00:00:00的时间，00:00:00为基数，为 0 秒。
select sec_to_time(3600);
-- 01:00:00TIME_TO_SEC()函数：将指定的时间值转为秒数。
SEC_TO_TIME 与TIME_TO_SEC 互为反函数。
数学方式
转时分秒
SELECT
CONCAT(
FLOOR((duration / (60 * 60 * 1000))) % 24,
':',
LPAD(FLOOR((duration / (60 * 1000))) % 60, 2, '0'),
':',
LPAD(FLOOR((duration / 1000)) % 60, 2, '0')
) AS formatted_duration
FROM
your_table;转时分
CONCAT(
FLOOR((d.run_time / (3600000))) ,
'h',
round((d.run_time / (60000)) % 60),
'min'
)计算两个日期之间相差
TIMESTAMPDIFF
timestampdiff(差值单位，开始时间，结束时间)差值单位的下参数有：
MICROSECOND（微秒）
SECOND（秒）
MINUTE（分）
HOUR（小时）
DAY（天）
WEEK（周）
MONTH（月）
QUARTER（季度）
YEAR（年）
timediff()函数
返回两个TIME或DATETIME相同类型的值之间的差值。极限值是838:59:59，相差在极限值以内的可以用此函数
SELECT TIMEDIFF('12:00:00','10:00:00') diff;
-- 差值为：02:00:00。组合date和time为datetime
CONCAT( date, ' ', time);连贯字符串
update 字段=字段+字符串
MySQL连贯字符串不能利用加号(+)，而利用concat。
--在aa表的name字段前加字符'x'
update aa set name=concat('x',name)concat()函数
（1）功能：将多个字符串连接成一个字符串。
（2）语法：concat(str1, str2,...)
返回结果为连接参数产生的字符串，如果有任何一个参数为null，则返回值为null。
concat_ws()函数
（1）功能：和concat()一样，将多个字符串连接成一个字符串。
但是可以一次性指定分隔符（concat_ws就是concat with separator）
（2）语法：concat_ws(separator, str1, str2, ...)
说明：str1，str2代表的是字符串，而separator代表的是连接其他参数的分隔符，可以是符号，也可以是字符串。如果分隔符为NULL，则结果为NULL。此方法参数可以为NULL。
group_concat()函数
（1）功能：将分组中括号里对应的字符串进行连接.如果分组中括号里的参数xxx有多行，那么就会将这多行的字符串连接，每个字符串之间会有特定的符号进行分隔。
（2）语法：group_concat( [要连接的字段] ,[order by asc/desc 排序字段] ,['分隔符']）
group_concat(type separator '#'); group_concat(type order by type desc); group_concat(distinct type order by type desc);
截取字符串
1）left(str, length)
即：left(被截取字符串， 截取长度)
2）right(str, length)
即：right(被截取字符串， 截取长度)
3）substr() 等价于 substring() 函数
substring(str, pos)，即：substring(被截取字符串， 从第几位开始截取)
substring(str, pos, length)，即：substring(被截取字符串，从第几位开始截取，截取长度)
4）substring_index(str, delim, count)
即substring_index(被截取字符串，关键字，关键字出现的次数)
--去掉字符后5位
SELECT SUBSTR('1234567', 1, LENGTH('1234567')-5) from dual5）REPLACE函数
删除最后一个逗号及其后面的所有内容
SELECT REPLACE('1,2,3,4,5', concat( ',', SUBSTRING_INDEX('1,2,3,4,5', ',', -1)), '');MySQL 拼音排序
如果字符集采用的是 gbk(汉字编码字符集)，直接在查询语句后边添加 ORDER BY。
如果字符集采用的是 utf8(万国码)，需要先对字段进行转码然后排序：ORDER BY CONVERT(字段 using gbk)。
函数（存储过程）
完成多个sql语句的操作，并且可以定义参数传值
create procedure TimedDownLine()
begin
insert into table (name,created_at,updated_at) values ('hello',now(),now());
end查看存储过程
show create procedure 存储过程名;
删除存储过程
DROP PROCEDURE 存储过程名;调用存储过程
CALL 存储过程名(实参列表);事件（定时任务）
开启事件
查看是否开启：
show variables like 'event_scheduler';如果显示OFF,则输入以下语句开启：
set global event_scheduler = on;永久修改：更改配置文件 /etc/my.cnf ，[mysqld]部分加入一段event_scheduler=on， 在重启mysql
创建定时事件
#每天凌晨开始执行
CREATE EVENT IF NOT EXISTS TimeDownlines_event
ON SCHEDULE EVERY 1 DAY STARTS DATE_ADD(DATE_ADD(CURDATE(), INTERVAL 1 DAY), INTERVAL 0 HOUR)
ON COMPLETION PRESERVE ENABLE #到点开启定时任务
DO CALL TimedDownLine();#调用函数查看定时任务
SHOW EVENTS;
SELECT * FROM information_schema.events; 删除事件的语法
DROP EVENT [IF EXISTS] event_name数据备份
insert into select
需要创建临时表，设置字段与数据类型。基本语法：insert into table_Name1 select * from tableName
select into
不需要创建临时表，在运行过程中自动创建。基本语法：select * into table_Name from tableName
mysql 数据库是不支持 select into ，另外一种变通的方法：Create table Table2 (Select * from Table1);
触发器
创建触发器
一个执行语句
CREATE TRIGGER 触发器名 BEFORE|AFTER 触发事件 ON 表名 FOR EACH ROW 执行语句;多个执行语句
CREATE TRIGGER 触发器名 BEFORE|AFTER 触发事件
ON 表名 FOR EACH ROW
BEGIN
执行语句列表
END;例如
CREATE TRIGGER `tri_insert_user` AFTER INSERT
ON `user` FOR EACH ROW
begin
INSERT INTO user_history(user_id, operatetype, operatetime) VALUES (new.id, 'add a user', now());
endBEFORE和AFTER参数指定了触发执行的时间，在事件之前或是之后。
FOR EACH ROW表示任何一条记录上的操作满足触发事件都会触发该触发器，也就是说触发器的触发频率是针对每一行数据触发一次。
触发事件：
①INSERT型触发器：插入某一行时激活触发器，可能通过INSERT、LOAD DATA、REPLACE 语句触发(LOAD DAT语句用于将一个文件装入到一个数据表中，相当与一系列的INSERT操作)；
②UPDATE型触发器：更改某一行时激活触发器，可能通过UPDATE语句触发；
③DELETE型触发器：删除某一行时激活触发器，可能通过DELETE、REPLACE语句触发。NEW与OLD详解
new：当触发插入和更新事件时可用，指向的是被操作的记录
old： 当触发删除和更新事件时可用，指向的是被操作的记录①在INSERT型触发器中，NEW用来表示将要（BEFORE）或已经（AFTER）插入的新数据；
②在UPDATE型触发器中，OLD用来表示将要或已经被修改的原数据，NEW用来表示将要或已经修改为的新数据；
③在DELETE型触发器中，OLD用来表示将要或已经被删除的原数据；
参考：
https://www.cnblogs.com/fps2tao/p/10400936.html
https://www.cnblogs.com/geaozhang/p/6819648.html
查看触发器
SHOW TRIGGERS下面方法可以查询制定触发器的详细信息。
SELECT * FROM information_schema.triggers删除触发器
DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name连接
join
inner join（同join）
保留两张表中完全匹配的结果集。
select * from a,b where a.id = b.id ，等价于：select * from a inner join b on a.id = b.id。即就是内连接。
但是这种写法并不符合规范，可能只对某些数据库管用，如sqlserver。推荐最好不要这样写。最好写成inner join的写法。
注意：单纯的select * from a,b是笛卡尔乘积。
比如a表有5条数据，b表有3条数据，那么最后的结果有5*3=15条数据。
left join
返回左表所有的行，即使在右表中没有匹配的记录（没匹配的以 null 值取代）。
right join
返回右表所有的行，即使在左表中没有匹配的记录（没匹配的以 null 值取代）。
full join（mysql是不支持full join）
完全连接返回左表和右表中的所有记录，包括连接字段相等的记录和不相等的记录。如果左表的某行在右表中没有匹配行，将返回空值(NULL），反之亦然。
union
union主要是合并行，上下合并，首列字段保持不变，其中union去重，union all不去重。 join是合并列，左右关联。
示例：
1、三表关联查询，条件： a,b,c三张表，a表里面有b、c表的主键
select
a.uid,a.uname,a.upsw,a.urealname,a.utel,a.uremark,
b.rid,b.rname,b.rremark,
c.deptid,c.deptname,c.deptremark
from table1 a
left join table2 b on a.sems_role_rid=b.rid
left join table3 c on a.udeptid=c.deptid ;参考：mysql三表关联查询_鱼sama的博客-CSDN博客
联表查询，条件： 订单表里保存的买家、卖家id都只是对应的用户表user里的id
select
o.*,
u1.nick as buyer,
u2.nick as seller
from hy_orderid o
LEFT JOIN hy_user u1 ON o.uid=u1.id
LEFT JOIN hy_user u2 ON o.shid=u2.id
参考：联表查询，一个表的两个字段是另一个表的一个字段的值，如何去查_Leo_林同学的博客-CSDN博客
sql查询提高查询效率
1、对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。
2、应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描
3、应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。
4、应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描
5、in 和 not in 也要慎用，否则会导致全表扫描
6、应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。
7、很多时候用 exists 代替 in 是一个好的选择：
8、索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率， 因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。 一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。
扫一扫
2万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
