MySQL数据操作

添加记录

insert[ into] tb_name[(col_name1,col_name2···)] values
(row1_value1,row1_value2,···)[,
(row2_value1,row2_value2,···),
···
];
-- 使用set添加单条记录
insert tb_name set col_name1=value1,col_name2=value2,···;
-- 添加查询结果
insert tb_name[(col_name1,col_name2,···)]
select other_col_name1,other_col_name2,···
from other_tb_name
[ where conditions];

删除数据

delete from tb_name[ where conditions];
-- 重置数据表
truncate[ table] tb_name;

修改记录

update tb_name set col_name1=value1,clo_name2=value2,···
[ where conditions];

查询记录

select select_expr1,select_expr2,··· from tb_name
[where conditions]
[group by {col_name|position} having other_conditions]
[order by{col_name|position|expr} [asc|desc]]
[limit num];
-- 设置别名
select col_name1 [as] alias1,col_name2 [as] alias2,··· from tb_name [as] tb_alias;
-- 过滤重复数据
select distinct(col_name) from tb_name;

WHERE条件

聚合函数

GROUP BY 分组

-- 分组查询使用group_concat()显示详细信息
select group_concat(col_name1)，group_concat(col_name2),··· from tb_name
group by a_col_name;
-- 统计分组中记录的总数
select col_name,count(*) from tb_name group by col_name;
-- 根据字段位置进行分组
select * from tb_name group by col_num;
-- 使用having子句对分组查询结果二次筛选
select col_name,count(*) [as] count from tb_name 
group by col_name having count_expr;

ORDER BY 排序

-- 将查询结果按照字段依次排序
select * from tb_name 
group by col_name1 asc,col_name2 desc,···;
-- 随机排序
select * from tb_name group by rand();

LIMIT 限制结果条数

-- 从offset开始显示row_count条数据
select * from tb_name limit offset row_count;

连接查询

-- 笛卡尔积查询
select * from tb1_name tb2_name; 
-- 内连接查询
select * from tb1_name 
[inner] join tb2_name on conditions1   
[[inner] join tb3_name on conditions2]
···;
-- 外连接查询
select * from tb1_name {left|right} [outer] join tb2_name
on conditions;

外键约束

-- 建表时创建外键
create table tb_name(
col_name col_type,
···
[constraint fk_name] foreign key (col_name) reference mian_tb_name(key_col_name) on {cascade|set null|restrict}
);
-- 删除外键
alter table tb_name drop foreign key fk_name;
-- 动态添加外键
alter table tb_name add [constraint fk_name] foreign key col_name reference mian_tb_name(key_col_name) on {cascade|set null|restrict};

外键约束参照

子查询语句

-- 由比较运算符引发的子查询
select * from tb1_name where col_name {>|>=|<|<=|=|<=>|!=|<>} {any|some|all} (select col_name from tb2_name where conditions);
-- 由in引发的子查询
select * from tb1_name where col_name [not] in(select col_name from tb2_name where conditions);
-- 由exists引发的子查询
select * from tb1_name where exists(select * from tb2_name where conditions);
-- 用查询结果创建表
create table tb_name(col_name col_type,···)select col_name,··· from other_tb_name;
-- 将查询结果插入其它表
insert tb_name(col_name,···) select col_name,··· from other_tb_name;

子查询中的ANY、SOME和ALL

联合查询

select * from tb1_name {union|union all} select * from tb2_name ···

自身连接查询

select s.* from tb_name as s left join tb_name as p on s.col1_name=p.col2_name;

数学函数

-- ceil
select ceil(1.2);
-- floor
select floor(1.3);
-- round
select round(1.5);
-- truncate
select truncate(1.234,2);
-- mod
select mod(5,3);
select 5 mod 3;
-- abs
select abs(-3);
-- power
select pow(3,2);
select power(3,2);
-- pi
select pi();
-- rand
select rand(5);
-- sign
select sign(-2);
-- exp
select exp(3);

字符串函数

-- char_length
select char_length('你好');
-- length
select length('你好');
-- concat
select concat('he','ll','o');
-- concat_ws
select concat_ws('-','who','are','we');
-- upper|ucase
select upper('hello'),ucase('world');
-- lower|lcase
select lower('HELLO'),lcase('WORLD');
-- reverse
select reverse('reverse');
-- left|right
select left('helloworld',5),right('helloworld',5);
-- lpad|rpad
select lpad('why',5,'!'),rpad('why',6,'?');
-- trim|ltrim|rtrim
select trim('  hello  '),ltrim('  hello  '),rtrim('  hello  ');
-- repeat
select repeat('ha',3);
-- replace
select replace('hello world !','world','china');
-- substring
select substring('iloveyou',2,4);
-- strcmp
select strcmp('love','peace');

日期时间函数

-- curdate|current_date
select curdate(),current_date();
-- curtime|current_time
select curtime(),current_time();
-- now|current_timestamp|sysdate
select now(),current_timestamp(),sysdate();
-- month|monthname
select month('1997-02-07'),month(curdate()),monthname(current_date());
-- dayname
select dayname(now());
-- dayofweek
select dayofweek(now());
-- week
select week(now());
-- year
select year(now());
-- day
select day(now());
-- hour|minute|second
select hour(now()),minute(now()),second(now());
-- datediff
select datediff(now(),'1997-02-07');

其他常用函数

注意事项

当插入数据不指定字段名时按照表中字段的顺序依次插入
更新数据表时如果不添加条件，整个表中的数据都会被更新
delete删除数据表如不添加条件，表中所有数据都会被删除
delete删除数据不会改变auto_increment，可以用alter table tb_name auto_increment=num;将其设置为num的值
truncate会清除表中所有信息，并重置auto_increment
可以使用db_name.tb_name的形式查询指定数据库下的数据表，而不用先打开数据表
可以使用tb_name.col_name查询特定表下的字段
等号<=>可以检测null而=则不可以
可以使用is [not] null检测是否为空或非空
between后面的值应当小于and的值，且between ··· and ···包含边界值
like匹配的字符有两种：%代表任意长度字符串，_代表任意单个字符
group by把值相同的放到一个组中，最终查询结果指挥显示组中的一条记录
count(col_name)不统计null，count(*)会统计null
可以在查询语句的最后加上with rollup，会在记录末尾添加一条记录，是上面所有记录的总和
排序有两种asc（升序）和desc（降序），默认asc
使用limit时，offset从0开始，当未设置offset时，默认为0
内连接查询两表中共同存在的数据
左外连接查询以左表为主，查询右表中对应的数据，不存在则为null，右外连接则相反
只有innodb支持外键，外键必须关联主表的主键
子表的外键字段，和主表的主键字段为数值型，要求类型一致，有无符号也要一致，字符型，要求类型相同，长度可以不同
外键字段没有索引，会自动创建
动态添加外键前表中的数据必须合法
外键必须关联主表的主键
union会自动删除两个表中重复的数据，union all则不会
rand(x)中的x为标识，相同的标识产生相同的随机数，也可不设标识,每次产生不同的随机数
使用length()时utf-8下一个中文字符的长度为3
concat()和concat_ws()拼接的字符串中如果含有null则拼接的结果为null
strcmp(s1,s2)当s1>s2返回1，s1<s2返回-1，s1=s2返回0
星期天是一周的第1天，依次类推，星期六是第7天
datediff是第一个日期减去第二个日期