mysql数据库的增删改查详细--来自前端小白的总结

注: []代表的是可以不写,不是直接写[]格式,写的时候是要去掉[]

---

---

数据表的操作

增

create table 数据表名 (字段名1 类型 [字段属性...], 字段名2 类型 [字段属性...], ...) [charset=字符集] [engine=表类型];
// 如:
create table table1 (id int,name varchar(10),sex char(1) ) [charset=字符集] [engine=表类型];

删

drop table 表名;

改(修改表就是 对字段增删改查+改表名+改字符集)

改表名

alter table 表名 rename 新表名;

改字符集

alter table 表名 charset=新字符集;

字段增删改查

见下面

查

查所有表

show tables;

查看某表结构

desc 表名;

查看某表的创建语句

show create table 表名;

---

---

字段的操作(修改数据表)

增

alter table 表名 add 字段名 字段类型 [字段属性] [after 已有某字段名];   ('after 已有某字段名'可以是'first', 在某字段的后,在表最前)

删

alter table 表名 drop 删的字段名;

改

alter table 表名 change 旧字段名 新字段名 字段类型 [字段属性...];
// 若不修改名,只改其他内容:
alter table 表名 modify 旧字段名 字段类型 [字段属性...];

---

---

数据操作(初)

增

// 字段与数据要一一对应
insert into 表名 (字段1, 字段2, ...) values (数据1, 数据2, ...);
// 若数据与所有字段对应:
insert into 表名 values (数据1, 数据2, ...);

删

delete from 表名 [where 条件];
// 如:
delete from table1 where id>6 and order_id = 4;

改

update 表名 set 字段名1 = 新值1, 字段名2 = 新值2, ... [where 条件];
// 注: where条件不写将会所有数据都改掉, 一般不这么做
// 如:
update table1 set content = '新的内容' where id=3;

查

select 字段名1, 字段名2, ... from 表名 [where 条件];
// 注: * 代表所有字段名; where不写就是取出所有数据;
// 如:
select pro_id, pro_name from product where pro_id<10 and price>5000;

---

---

高级操作

高级查询(查)

语法概述

select 子句
    [from子句]
    [where子句]
    [group by子句]
    [having子句]
    [order by子句]
    [limit子句];

查询结果数据及select选项

查询’固定数据’

select 'abc';  // 就是以'abc'为表头查询

可计算

select 1+2, 1+round(); //round是系统函数

使用别别名 as

select user_name as un, user_pass as pwd from user;

消除重复行 disctinct (查出的结果两及以上数据完全一样时,只显示一个)

select disctinct 字段1, 字段2, ... from 表名;

where子句

select ... from 表名 where 条件;
// 如:
where id > 10;
where age - 18 >= 0;
where id < 10 and age >= 19;
where year % 4 = 0 and year % 5 != 0 || year % 400 = 0;

mysql运算符

// 算数运算符:
+ - * / %
// 比较运算符:
=   >  >=   <   <=
不相等: <> 或 !=
// 逻辑运算符:
与: && 或 and
或: || 或 or
非: ! 或 not

其他特殊运算符

like 模糊查找

% : 表示 任意个数 的 任意字符
where name like '余%';
_ : 表示 一个 的 任意字符
where name like '_志勇';

between 范围限定,(两值之间,包含两值)

where id between 2 and 4;
// 相当于:
where id >= 1 and id <= 4;

可选值 in

where id in (1, 3, 5); // id满足其中之一则找出;

是否存在 is

// 只有两种写法:
where content is null;
where content is not null;

group by 以给定的字段进行分组

group by 字段1, 字段2, ... ;
// 如:
select * from table1 group by pinpai; //按pinpai这个列进行分组
// 多条件分组
select * from table1 group by pinpai, chandi; // 按pinpai,chandi进行分组

聚合函数(分组不是会将重复的重叠吗, 这个就是重叠的规则)

count 计数值,原始数据的行数,一般count(*)
max  最大值
min  最小值
avg  平均值
sum  总和值

// 列: 以chandi为第一列以此为分组的,以chandi、数量、平均价为表头的表
select chandi, count(*) as 数量, avg(price) as 平均价 from table1 group by chandi; 
// 产地是北京的平均价
select avg(price) from table1 where chadi = '北京'

having对group by的结果进行筛选

// 平均价>5000
select chandi, count(*) as 数量, avg(price) as 平均价 from table1 group by chandi having 平均价 > 5000; 

order by排序

// asc升序(默认)  desc降序
order by 字段1 [asc或desc], 字段2 [asc 或 desc],...
// 如:
select chandi, count(*) as 数量, avg(price) as 平均价 from table1 group by chandi having 平均价 > 5000 order by 数量 asc 平均价 desc;

limit 取多少行(通常用于翻页)

// 注: 起始行号是从0开始不是id, 且默认为0
limit [起始行号,] 行数
// 用于翻页的公式,pageSize是每页条数,n表示'第n页':
limit (n-1)*pageSize, pageSize;
// 列,第一页,显示10条:
select chandi, count(*) as 数量, avg(price) as 平均价 from table1 group by chandi having 平均价 > 5000 order by 数量 asc 平均价 desc limit (1-1)*10, 10;

---

高级插入(增)

同时插入多行数据

insert into 表名 (字段1, 字段2, ...) values (数据1, 数据2, ...), (数据1, 数据2, ...), ...;

插入查询到的结果(插入的就是select语法)

insert into 表名 (字段1, 字段2, ...) select ...;

set语法插入,用 = 一一对应的插入

insert into 表名 set 字段1 = 值1, 字段2 = 值2, ...;

插入时主键冲突解决方法

忽略ignore (终止插入)

insert ignore into 表名 (字段...) values (值...);

替换replace (新的替换旧的)

insert replace into 表名 (字段...) values (值...);

更新

// 若冲突了,就只改规定的字段
insert into 表名 (字段...) values (值...) on duplicate key update 字段名 = 新的值;

---

高级删除(删)

指定顺序删除指定数量

delete from 表名 where ... [order by 字段名...] [limit 数量n];

清空truncate (恢复’初始状态’)

相对于delete from ,这个truncate的自增状态会重新开始

truncate 表名;

---

高级更新(改)

update 表名 set 字段1=值1, 字段2=值2, ... where ... [order by 字段名...] [limit 数量n];

---

---

联合查询union(纵向连接)

union distinct: 消除重复(默认,写union就是);
union all: 保留所有; 
// 如,注应有相同列数:
select id, a, b, c from table1 
union 
select id2, a2, b2, c2 from table2
...
[order by 字段...] [limit ...]; // 和正常查询的一样操作,只不过是两个表变成了一个

连接查询join(横向连接)

select ... from 表1 [连接方式] join 表2 [on连接条件] where ...;

连接方式分为:
交叉连接
内连接
外连接(左右外连接)

交叉连接 cross(通常价值不大)

select ... from 表1 [cross] join 表2;

内连接inner join(常用)

select ... from 表1 [inner] join 表2 on 连接条件;

// 如找出table12中id相同的并列出左右组合后的表:
select * from table1 inner join table2 on table1.id = table2.id;

左外连接left outer join(保证左边数据能够显示出,右边为添加的数据)

select tab1.name, tab2.age from 表1 as tab1 left [outer] join 表2 as tab2 on 连接条件;

右外连接right outer join(和左位置换下)

select ... from 表1 right [outer] join 表2 on连接条件;

自连接(自己和自己连接)

select ... from 表1 as a [连接形式] join 表1 as b on 字段名1 = 字段名2;

子查询(就是拿查询语句去对比语句)

select * from 表1 where price>=(一个子查询语句);

标量子查询(子查询的结果是 单个值)

// 查价格大于产地是北京的平均价的表
select * from table1 where price > (
 select avg(price) from table1 where chadi = '北京'
);

列子查询(子查询的结果是 一列)

// 如:
select * from table1 where chandi in(
 select pinpai as '品牌' from table1 where chandi = '北京'
);

行子查询(子查询结果是 一行)

where row(字段1, 字段2, ...) = (行子查询)

表子查询(子查询结果是 多行多列)

select ... from (表子查询) as tab1

exists子查询(有数据返回true,否则返回false)

where exists (任何子查询)

子查询的关键字

in: 其中每个都过一遍
any: 满足其中一个
all: 满足其中所有
// 如:
select * from table1 where chadi = '北京' and price > any(
 select price from table1 where chandi = '深圳'
)