MySQL数据操作

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是第一个日期减去第二个日期