MySQL数据操作
添加记录
insert[ into] tb_name[(col_name1,col_name2···)] values
(row1_value1,row1_value2,···)[,
(row2_value1,row2_value2,···),
···
];
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 分组
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;
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 限制结果条数
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);
select * from tb1_name where col_name [not] in(select col_name from tb2_name where conditions);
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次方 |
select ceil(1.2);
select floor(1.3);
select round(1.5);
select truncate(1.234,2);
select mod(5,3);
select 5 mod 3;
select abs(-3);
select pow(3,2);
select power(3,2);
select pi();
select rand(5);
select sign(-2);
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) |
比较两个字符串的大小（字典序） |
select char_length('你好');
select length('你好');
select concat('he','ll','o');
select concat_ws('-','who','are','we');
select upper('hello'),ucase('world');
select lower('HELLO'),lcase('WORLD');
select reverse('reverse');
select left('helloworld',5),right('helloworld',5);
select lpad('why',5,'!'),rpad('why',6,'?');
select trim(' hello '),ltrim(' hello '),rtrim(' hello ');
select repeat('ha',3);
select replace('hello world !','world','china');
select substring('iloveyou',2,4);
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) |
返回两个日期中相差的天数 |
select curdate(),current_date();
select curtime(),current_time();
select now(),current_timestamp(),sysdate();
select month('1997-02-07'),month(curdate()),monthname(current_date());
select dayname(now());
select dayofweek(now());
select week(now());
select year(now());
select day(now());
select hour(now()),minute(now()),second(now());
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是第一个日期减去第二个日期
打开App，阅读手记
随时随地看视频
