MySQL 的一些常用 SQL 集合

# 简单描述表结构,字段类型

显示表结构,字段类型,主键,是否为空等属性,但不显示外键。

desc tabl_name;
1

# 查询数据库的外键关系

你可以通过 INFORMATION_SCHEMA.KEY_COLUMN_USAGE 表来查看。

select * from INFORMATION_SCHEMA.KEY_COLUMN_USAGE where REFERENCED_TABLE_NAME = 't_stu'
1

# 查询表中列的注释信息

select * from information_schema.columns
where table_schema = 'db' -- 表所在数据库
and table_name = 'tablename' ; -- 你要查的表
1
2
3

# 只查询列名和注释

select column_name, column_comment from information_schema.columns where table_schema ='db' and table_name = 'tablename' ;
1

# 查看表的注释

select table_name,table_comment from information_schema.tables where table_schema = 'db' and table_name = 'tablename'
1

# 查看表生成的 DDL

show create table table_name;
1

# 复制数据

insert into table1 select * from table
1

# 机器授权

grant select on *.* to 'reader'@'%' identified by '123456' WITH GRANT OPTION
flush privileges
1
2

# 查询当前这周的数据

SELECT name,submittime FROM enterprise WHERE YEARWEEK(date_format(submittime,'%Y-%m-%d')) = YEARWEEK(now());
1

# 查询上周的数据

SELECT name,submittime FROM enterprise WHERE YEARWEEK(date_format(submittime,'%Y-%m-%d')) = YEARWEEK(now())-1;
1

# 查询当前月份的数据

select name,submittime from enterprise where date_format(submittime,'%Y-%m')=date_format(now(),'%Y-%m')
1

# 查询距离当前现在 6 个月的数据

select name,submittime from enterprise where submittime between date_sub(now(),interval 6 month) and now();
1

# 查询上个月的数据

select name,submittime from enterprise where date_format(submittime,'%Y-%m')=date_format(DATE_SUB(curdate(), INTERVAL 1 MONTH),'%Y-%m')

select * from `user` where DATE_FORMAT(pudate,'%Y%m') = DATE_FORMAT(CURDATE(),'%Y%m') ;

select * from user where WEEKOFYEAR(FROM_UNIXTIME(pudate,'%y-%m-%d')) = WEEKOFYEAR(now())

select *
from user
where MONTH(FROM_UNIXTIME(pudate,'%y-%m-%d')) = MONTH(now())

select *
from [user]
where YEAR(FROM_UNIXTIME(pudate,'%y-%m-%d')) = YEAR(now())
and MONTH(FROM_UNIXTIME(pudate,'%y-%m-%d')) = MONTH(now())

select *
from [user]
where pudate between 上月最后一天
and 下月第一天
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

# 获取前一天时间的方法

SELECT DATE_SUB(current_dateINTERVAL 1 DAY);

SELECT DATE_SUB(curdate(), INTERVAL 1 DAY);
1
2
3
mysql> select date_sub(curdate(),interval 1 day);
+------------------------------------+
| date_sub(curdate(),interval 1 day) |
+------------------------------------+
| 2006-06-19                         |
+------------------------------------+
1 row in set (0.09 sec)

mysql> select date_sub('2006-06-04',interval 1 day);
+---------------------------------------+
| date_sub('2006-06-04',interval 1 day) |
+---------------------------------------+
| 2006-06-03                            |
+---------------------------------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

# 替换部分字段内容

UPDATE imicro_messages set ms_content = REPLACE(ms_content, ' ', '</span>')
1