• 函数类

DISTINCT #过滤重复数据

SUBSTRING_INDEX(us_name,'你好',1) #寻找替换文本 参数1 准备要替换的字段 参数2 要搜寻的文本 参数3 要替换的字符个数

select SUBDATE(now(),interval 60 second)  取当前时间的前一分钟

select ADDDATE(now(),interval 60 second)  取当前时间的下一分钟

SUBDATE() 参数1 时间类型 参数2减去参数1 得出结果 day = 天、hour = 小时、minute = 分钟、week = 周、month = 月、year = 年

ADDDATE()  参数1 时间类型 参数2加上参数1 得出结果 day = 天、hour = 小时、minute = 分钟、week = 周、month = 月、year = 年。、

 

  • 示例

SELECT * FROM design_sift WHERE path NOT IN (SELECT path FROM design_copy) 子表与总表数据作比对 筛选的作用 逻辑条件: NOT IN = 不在 IN = 在 万能的数据库很强大

  • #创建表

create table [ if not exists ] 表名(

  字段名称 字段类型  [not null | null ] [ default value] [primary key | unique [key]]  [auto_increment]  [comment ‘字段描述’]

)engine = 存储引擎名称 DEFAULT CHARSET=utf8  ROW_FORMAT=DYNAMIC comment  '表的备注信息' ;

show tables;  # 查看当前库中的所有表,前提进入库中

show tables from 库名;  # 查看库中有哪些表

show create table 表名;  # 查看创建表结构语句

DESC 表名 / DESCRIBE 表名 / EXPLAIN 表名 / SHOW COLUMNS FROM 表名;  # 查看表中的字段信息

SHOW TABLE STATUS [FROM db_name];  # 查看表信息,如存储引擎,字符编码等。

rename table 原表名 to 新表名;     # 表的重复名

alter table 旧表名 rename to 新表名;  #表的重复名

rename table 原表名 to  库名.表名;  # 将表移动到另一个数据库

// 添加修改字段结构

alter table 表名 add 列名  类型;  # 给表添加一列

alter table 表名 drop 列名;  # 删除列

alter table 表名 change 原列名 新列名 类型;  # 修改列的名称和类型

alter table 表名 modify 列类型;  # 修改列名的类型

alter table 表名 add constraint 主键名称  primary key(列名);  #设置主键

alter table 表名 drop primary key;  # 删除主键

alter table 子表名 add constraint 外键名称foreign key(子表列名) references 主表(主表列名);  #设置外键

alter table 子表 drop foreign key 外键名称;   # 删除外键

alter table 表名 add constraint 唯一索引名称 unique(列名);  #设置唯一索引

alter table 表名 add index 索引名称  (字段名称);  # 添加索引

alter table 表名 drop index 索引名称;  #删除索引

drop table [ if exists ] 表名 ...;   #删除表

truncate table 表名;   #清空表数据。自增主键会清空从起始1开始

delete table 表名 [where 列名=条件];  #可全删或指定范围删除数据

create table 新表名 like 复制表名;  #复制表结构

create table 新表名 as select * from 要复制的表名;  #复制表结构和数据

  •  数据操作

insert into 表名(字段列表)  values(值列表),(值列表),(值列表)......;  # 添加数据

insert into 表名 set 字段名=值 [, 字段名=值 ...];# 不常用的添加数据语句

insert ignore 表名(字段列表) values(值列表),.....;  #添加数据,如果已存在,忽略

replace into 表名(字段列表) values(值列表);  # 替换新增数据

replace into 表名 set 字段名=值 [, 字段名=值 ...];# 不常用

select 字段列表 from 表名;  #查询

delete from 表名 [删除条件子句];  #删除

update 表名 set 字段名=新值 [, 字段名=新值] [更新条件]; #修改数据

 

DQL语句的顺序(单表)

select [ all|distinct ] select_ecpr from -> where -> group by [ 合计函数 ] -> having -> order by -> limit

select:查什么

from:哪个表

where:条件是?

group by:按什么分组

group by 字段/别名

having:补充不足的条件

order by:排序  升序:ASC,降序:DESC

limit:分页

limit 起始位置, 获取条数
省略第一个参数,表示从索引0开始。limit 获取条数

 

/* UNION*/

将多个select查询的结果组合成一个结果集合。

SELECT ... UNION  SELECT ...

需要各select查询的字段数量一样。

每个select查询的字段列表(数量、类型)应一致,因为结果中的字段名第一条select语句为准

/* 子查询 */

子查询需要括号包裹

-- fromfrom后要求是一个表,必须给子查询结果取个别名。
    - 简化每个查询内的条件。
    - from型需将结果生成一个临时表格,可用以原表的锁定的释放。
    - 子查询返回一个表,表型子查询。
    select * from (select * from tb where id>0) as subfrom where id>1;
-- where型
    - 子查询返回一个值,标量子查询。
    - 不需要给子查询取别名。
    - where子查询内的表,不能直接用以更新。
    select * from tb where money = (select max(money) from tb);
-- 列子查询
    如果子查询结果返回的是一列。
    使用 in not in 完成查询
    existsnot exists 条件
    如果子查询返回数据,则返回1或0。常用于判断条件。
    select column1 from t1 where exists (select * from t2);
-- 行子查询
    查询条件是一个行。
    select * from t1 where (id, gender) in (select id, gender from t2);
    行构造符:(col1, col2, ...)ROW(col1, col2, ...)
    行构造符通常用于与对能返回两个或两个以上列的子查询进行比较。

几个高级查询运算词

A:UNION运算符

UNION运算符通过组合其他两个结果表(例如 TABLE1 TABLE2)并消去表中任何重复行而派生出一个结果表。当ALL UNION 一起使用时(即UNIONALL),不消除重复行。两种情况下,派生表的每一行不是来自 TABLE1 就是来自 TABLE2。

B:EXCEPT运算符

EXCEPT运算符通过包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重复行而派生出一个结果表。当ALL EXCEPT 一起使用时 (EXCEPTALL),不消除重复行。

C:INTERSECT运算符

INTERSECT运算符通过只包括 TABLE1TABLE2 中都有的行并消除所有重复行而派生出一个结果表。当ALL INTERSECT 一起使用时 (INTERSECTALL),不消除重复行。

注:使用运算词的几个查询结果行必须是一致的。
 
内置函数
-- 数值函数 abs(x) -- 绝对值 abs(-10.9) = 10 format(x, d) -- 格式化千分位数值 format(1234567.456, 2) = 1,234,567.46 ceil(x) -- 向上取整 ceil(10.1) = 11 floor(x) -- 向下取整 floor (10.1) = 10 round(x) -- 四舍五入去整 mod(m, n) -- m%n m mod n 求余 10%3=1 pi() -- 获得圆周率 pow(m, n) -- m^n sqrt(x) -- 算术平方根 rand() -- 随机数 truncate(x, d) -- 截取d位小数 -- 时间日期函数 now(), current_timestamp(); -- 当前日期时间 current_date(); -- 当前日期 current_time(); -- 当前时间 date('yyyy-mm-dd hh:ii:ss'); -- 获取日期部分 time('yyyy-mm-dd hh:ii:ss'); -- 获取时间部分 date_format('yyyy-mm-dd hh:ii:ss', '%d %y %a %d %m %b %j'); -- 格式化时间 unix_timestamp(); -- 获得unix时间戳 from_unixtime(); -- 从时间戳获得时间 -- 字符串函数 length(string) -- string长度,字节 char_length(string) -- string的字符个数 substring(str, position [,length]) -- 从str的position开始,取length个字符 replace(str ,search_str ,replace_str) -- 在str中用replace_str替换search_str instr(string ,substring) -- 返回substring首次在string中出现的位置 concat(string [,...]) -- 连接字串
group_concat(name separator ';') -- 分组连接字符串,用;分隔。 默认是逗号“,” charset(str) -- 返回字串字符集 lcase(string) -- 转换成小写 left(string, length) -- 从string2中的左边起取length个字符 load_file(file_name) -- 从文件读取内容 locate(substring, string [,start_position]) -- 同instr,但可指定开始位置 lpad(string, length, pad) -- 重复用pad加在string开头,直到字串长度为length ltrim(string) -- 去除前端空格 repeat(string, count) -- 重复count次 rpad(string, length, pad) --在str后用pad补充,直到长度为length rtrim(string) -- 去除后端空格 strcmp(string1 ,string2) -- 逐字符比较两字串大小 -- 流程函数 case when [condition] then result [when [condition] then result ...] [else result] end 多分支 if(expr1,expr2,expr3) 双分支。 -- 聚合函数 count() sum(); max(); min(); avg(); group_concat() -- 其他常用函数 md5(); default();