MySQL数据操作

关注

原创最新推荐文章于 2026-06-13 09:18:19 发布 246 阅读 AI 写同款· GEO 优化 ›

【MySQL】数据操作 一、INSERT1、介绍2、语法3、语法介绍4、注意事项5、示例二、插入否则更新1、介绍2、语法3、语法介绍4、示例三、ROW_COUNT1、介绍2、示例四、REPLACE1、介绍2、语法3、示例五、UPDATE1、介绍2、语法3、示例六、DELETE1、介绍2、语法3、语法介绍七、TRUNCATE1、介绍2、语法3、注意事项4、与DELETE的比较阅读详情

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条件

条件名称	详细信息
比较运算符	`>`、`<`、`=`、`>=`、`<=`、`<=>`、`!=`、`<>`
指定范围	`[not] between ··· and ···`
指定集合	`[not] in(value1,value2,···)`
逻辑运算符	`and`（与），`or`（或）
匹配字符	`[not] like ···`（模糊查询）

聚合函数

聚合函数	信息
`count()`	统计记录数
`sum()`	求和
`max()`	求最大值
`min()`	求最小值
`avg()`	求平均值

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};

外键约束参照

名称	详情
`cascade`	级联：当父表删除或更新时，子表也会删除和更新
`set null`	当父表删除和更新时，子表的外键列置为`null`
`no action`\|`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

	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()`	进一位取整
`floor()`	舍掉小数部分
`round()`	四舍五入
`truncate()`	截取小数后几位
`mod()`	取余数
`abs()`	取绝对值
`power()`	幂运算
`pi()`	圆周率
`rand(x)`	0~1之间随机数
`sign(x)`	得到数字的符号
`exp(x)`	计算e的x次方

-- 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(s)`	得到字符串的字符数
`lenght(s)`	得到字符串的长度
`concat(s1,s2,s3,...)`	合并多个字符串
`concat_ws(c,s1,s2,s3,...)`	以指定的分隔符c拼接字符串
`upper(s)`\|`ucase(s)`	将字符串转换为大写
`lower(s)`\|`lcase(s)`	将字符串转换为小写
`reverse(s)`	字符串反转
`left(s,n)`\|`right(s,n)`	得到字符串前\|后几个字符
`lpad(s,n,c)`\|`rpad(s,n,c)`	用字符c填充字符串s到指定长度n
`trim(s)`\|`ltrim(s)`\|`rtrim(s)`	去掉字符串两\|左\|右端的空格
`repeat(s,n)`	将字符串s重复n次
`replace(s,s1,s2)`	将s中的s1替换成s2
`substring(s,x,n)`	从x开始截取字符串s的n位
`strcmp(s1,s2)`	比较两个字符串的大小（字典序）

-- 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()`	反回当前日期
`curtime()`\|`current_time()`	返回当前时间
`now()`\|`current_timestamp()`\|`sysdate()`	返回当前的日期时间
`month(date)`\|`monthname(date)`	返回日期中的月份\|月份名称
`dayname(date)`	返回星期几
`dayofweek(date)`	返回一周中的第几天
`week(date)`	返回一年中的第几个星期
`year(date)`	返回是哪一年
`day(date)`	返回一个月中的第几天
`hour(time)`\|`minute(time)`\|`second(time)`	返回时\|分\|秒
`datediff(date1,date2)`	返回两个日期中相差的天数

-- 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');

其他常用函数

名称	信息
`version()`	版本信息
`connection_id()`	当前连接id
`database()\\|schema()`	当前数据库名
`user()`\|`current_user()`\|`system_user()`\|`session_user()`	当前用户名
`last_insert_id()`	最后一次插入操作产生的`auto_increment`的值
`md5()`	返回32位的md5码
`password()`	默认密码加密算法

注意事项

当插入数据不指定字段名时按照表中字段的顺序依次插入
更新数据表时如果不添加条件，整个表中的数据都会被更新
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是第一个日期减去第二个日期