mysql 常用细节 时间 + 触发器+外键+查询结果创建新表

mysql 常用细节

查询当前日期是周几

select data_format(now, "%w") from test;

mysql 百度百科说 没有boolean类型，但是实际是支持的

create table test(
    is_return boolean
),

insert test values(false);   0
insert test values(true);    1

mysql 时间比较 date datetime可以直接比较

crate tablte test(
    date1 datetime,
    date2 date
);
insert test values("2021-3-6 10:00:00", "2021-2-7");
select * from test where date1 > "2021-3-5";

-- 时间相等比较
select * from test date1 = "2021-3-6"
-- 等价于
select * from test date1 = "2021-3-6 00:00:00"

时间比较2

-- 如果只想看日期是否相同
select * from test where Date(date1) = '2021-3-6'

时间比较3

-- 查询2021年1月的条目
select * from test where date(date1) between '2021-01-01' and '2021-01-31'
-- 等价于
select * from product where Year(date1) = 2021 and Month(date1) = 1

mysql 获得当前时间

-- 获取当前时间 格式：YYYY-MM-dd HH:mm:ss
now();
-- 格式转换    格式：YYYY-MM-dd
DATE_FORMAT(NOW(),'%Y-%m-%d')
-- 查询示例
SELECT * FROM 表名 WHERE 日期字段 >= DATE_FORMAT(NOW(),'%Y-%m-%d')

-- 每秒
select SECOND(t), count(*) from test group by SECOND(t);

-- 每分钟
select MINUTE(t), count(*) from test group by MINUTE(t);

-- 每小时
select HOUR(t), count(*) from test group by HOUR(t);

-- 查询每天的数据
select DATE_FORMAT(t,'%Y-%m-%d'), count(*) from test group by DATE_FORMAT(t,'%Y-%m-%d');

-- 查询每周的数据
select WEEK(t), count(*) from test group by WEEK(t);

-- 查询每月的数据
select MONTH(t), count(*) from test group by MONTH(t);

-- 查询每年的数据
select YEAR(t), count(*) from test group by YEAR(t);

-- 加一天
DATE_ADD(t,INTERVAL 1 DAY);

-- 减一天
DATE_SUB(t,INTERVAL 1 DAY);

-- 升序
select * from 表名 order by 字段 asc(MySQL中默认是升序排列，可不写)

-- 降序
select * from 表名 order by 字段 desc

-- 同时升降
 order by 升序字段 asc，降序字段 desc

mysql 触发器

创建触发器

CREATE TRIGGER trigger_name
trigger_time
trigger_event ON tbl_name
FOR EACH ROW
trigger_stmt

trigger_name：标识触发器名称，用户自行指定； trigger_time：标识触发时机，取值为 BEFORE 或 AFTER；
trigger_event：标识触发事件，取值为 INSERT、UPDATE 或 DELETE；
tbl_name：标识建立触发器的表名，即在哪张表上建立触发器；
trigger_stmt：触发器程序体，可以是一句SQL语句，或者用BEGIN 和 END 包含的多条语句。

由此可见，可以建立6种触发器，即：BEFORE INSERT、BEFORE UPDATE、BEFORE DELETE、AFTER INSERT、AFTER UPDATE、AFTER DELETE。

另外有一个限制是不能同时在一个表上建立2个相同类型的触发器，因此在一个表上最多建立6个触发器。

查看触发器

show triggers;
SELECT * FROM information_schema.`TRIGGERS`;

删除触发器

drop trigger lease_trigger;

MySQL创建触发器没有权限

解决如下

SET GLOBAL log_bin_trust_function_creators = 1;

外键

删除外键

alter table lease drop foreign key lease_ibfk_1;

添加外键

修改一下外键，下面是个例子：

alter table 表名
add constraint 外键名
foreign key(字段名) references 主表名(字段名)
on delete cascade

alter table lease
    add constraint lease_ibfk_1
        foreign key(crew_name) references crew(crew_name)
            on delete cascade
            on update cascade

语法：
Foreign Key
(column[,...n])
references referenced_table_name[(ref_column[,...n])]
[on delete cascade]
[on update cascade]
注释：
column:列名
referenced_table_name:外键参考的主键表名称
ref_name:外键要参考的表的主键列
on delete:删除级联
on update:更新级联

查询结果创建新表

CREATE TABLE school SELECT * FROM class

mysql 改表名

ALTER TABLE old RENAME TO new;

mysql 导出（备份）

mysqldump -h主机 -u用户名 -p密码 数据库 表1 表2 ... > 物理位置/文件名.sql

导入
登录
source 位置

未登录
mysql -uroot -p密码 库名 <  备份文件

MySQL 中止正在执行的SQL语句

show processlist;
kill id;