mysql常用增删改查命令总结

目录

一、增：insert

二、删：delete

三、改：update

四、查：select

---

一、增：insert

基础语法

注意

• 按“行”插入
• “字段”和“值”之间，一一对应
• 值的形式：数字直接写，字符串和时间加单引号，但如果是函数值，则不能加引号
• auto_increment， timestamp等字段无需插入

id	name	age	phone
1	王麻子	32	13141098899
2	九筒	32	13721096541
3	六子	18	13139192657
4	马邦德	37	15855996688

Example

insert into student (name, age, phone) values('王麻子', 32, '13141098899');
insert into student (id,name, age, phone) values(2,'九筒', 32, '13721096541'),(3,'六子', 18, '13139192657'),(4,'马邦德', 37, '15855996688');

 

二、删：delete

基础语法

delete from 表名 [where  条件] [order by 排序的字段 [desc 取反]] [limit  限定的行数];

drop table  表名     /*整个表将不复存在*/

注意

• “以行为单位”删除
• 删除语句中，where条件如果不写，则就删除了所有数据
• order排序子句用于设定删除数据的先后顺序
• limit限定子句用于限定在设定的顺序情况下删除指定的某些行
   

三、改：update

基本语法

修改

update 表名 set 字段名1=值1，字段名2=值2，....[where条件] [order排序] [limit限定];

注意

• 以“行”为单位进行的，可以指定只更新其中的部分字段
• 其他限定遵循insert语法

四、查：select

4.1实例表创建

班级表

CREATE TABLE class (
  id int  NOT NULL AUTO_INCREMENT,
  name varchar(10) ,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 

老师表

CREATE TABLE teacher (
  id int  NOT NULL AUTO_INCREMENT,
  name varchar(10) ,
  age int ,
  phone char(11) ,
  PRIMARY KEY (id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

student学生表

create table  student( 
id int auto_increment primary key, 
name varchar(10), 
age  int, 
class_id int, 
foreign key(class_id) references class(id) 
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 

多对多关系表

create table class2teacher( 
id int auto_increment primary key, 
class_id int,
teacher_id int, 
foreign key(class_id) REFERENCES class(id),
foreign key(teacher_id) references teacher(id) 
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 

4.2实例表数据添加

class(班级表)

insert into class(name) values
("云计算1810"),
("云计算1901"),
("云计算1902");

teacher(老师表)

insert into teacher(name, age, phone) values
("奇哥", 18, "13733878989"),
("强哥", 28, "15633878989"),
("磊哥", 30, "13933878989"),
("闫老师", 18, "13633878989");

student(学生表)

insert into student(name, age, class_id) values
("黛玉", 18, 3), ("钦文", 19, 3),("马邦德", 30, 1),
("九筒", 48, 1),("六子", 36, 2),("汤师爷", 18, 2),
("麻匪", 18,2),
("黛玉", 18,2);

class2teacher(班级到老师多对多关系表)

insert  into class2teacher(class_id,teacher_id) values
(1,1),(1,2),(2,1),(2,2),(2,3),(3,1),(3,3);

4.3单表查询

4.3.1基础查询

select * 或者[字段名] from 表名;

select * from 表;
select * from 表 where id > 2;
select id,name,age as gg from 表 where id > 2;

4.3.2高级查询

a、条件
    SELECT * 或 [字段名] FROM 表名 WHERE 条件;

    select * from 表 where id > 1 and name != '王麻子' and age = 18;
    select * from 表 where id between 5 and 16;
    select * from 表 where id in (11,22,33);
    select * from 表 where id not in (11,22,33);
    select * from 表 where id in (select id from 表);


b、去重
    SELECT DISTINCT [字段名] FROM 表名;

    select distinct name from 表;
 

c、通配符
    SELECT * 或 [字段名] FROM 表名 WHERE 字段名 LIKE 'xx%'或 'xx_';  --%匹配多个字符；_匹配单个字符

    select * from 表 where name like 'sha%';  -- sha开头的所有（多个字符串）
    select * from 表 where name like 'shar_';  -- sha开头的所有（一个字符）
 

d、限制
    SELECT * 或 [字段名] FROM 表名 LIMIT [N];

    select * from 表 limit 5;            - 获取前 5 行
    select * from 表 limit 0,2;          - 从第 1 行开始, 取出 2 行, 包含第 1 行
    select * from 表 limit 2 offset 0;   - 从第 1 行开始, 取出 2 行, 包含第 1 行
 

e、排序
    SELECT * 或 [字段名] FROM 表名 ORDER BY 字段名 [ASC | DESC];

    select * from 表 order by 列 asc;              - 根据 “列” 从小到大排列
    select * from 表 order by 列 desc;             - 根据 “列” 从大到小排列
    select * from 表 order by 列1 desc,列2 asc;    - 根据 “列1” 从大到小排列，如果相同则按列2从小到大排序

 
f、分组
    SELECT 字段名 [聚合函数] FROM 表名 GROUP BY 字段名 [ASC | DESC]

    select age from 表 group by age;
    select age,id from 表 group by age,id;
    select age,id from 表  where id > 10 group by age,id order by id desc;
    select age,id,count(*),sum(age),max(age),min(age) from 表 group by age,id;
    特别的：group by 必须在where之后，order by之前


g、having设置条件
    SELECT 字段名 [聚合函数] FROM 表名 GROUP BY 字段名 HAVING 条件;

    select age from 表 group by age having max(id) > 10;

h、嵌套查询
    select * from  (select name from t1 where age>18 and age < 25 order by id desc limit 2 ) as tt  order by id;

4.3.3多表查询

4.3.3.1子查询--企业通用方法

-- 查询到 每个班级的所有学员的姓名
select class.name, student.name
from class, student
where student.class_id=class.id;


-- 查询到 云计算1901班级的所有学员的姓名
select class.name, student.name
from class, student
where class.name="云计算1901" 
and student.class_id=class.id;


-- 查询到 马邦德 是哪个班级的
select student.name as 学员姓名,class.name as 班级姓名
from student,class
where student.name='马邦德'
and student.class_id=class.id;


-- 查询老师 奇哥 都负责哪些班级
select t.name,c.name 
from teacher as t, class as c ,class2teacher c2t
where t.name='奇哥' 
and c2t.teacher_id=t.id  
and c2t.class_id=c.id;

4.3.3.2连表查询：join

/*无对应关系则不显示*/
select  A.class_name, B.name
from class as A, teacher as B
Where A.id = B.class_id
 

/* 内连接   无对应关系则不显示*/
SELECT 字段集 FROM 表1 INNER JOIN 表2 ON 连接条件;
select A.class_name, B.name
from class as A inner join teacher as B
on A.id = B.class_id
 

/* 左连接   左边的表(A)所有显示，如果右边的表(B)中无对应关系，则值为null*/
SELECT 字段集 FROM 表1 LEFT JOIN 表2 ON 连接条件;
select A.class_name, B.name
from class as A left join teacher as B
on A.id = B.class_id
 

/* 右连接 右边的(B)表所有显示，如果左边的表(A)中无对应关系，则值为 NULL*/
SELECT 字段集 FROM 表1 RIGHT JOIN 表2 ON 连接条件;
select A.name, B.name
from class as A right join teacher as B
on A.id = B.class_id

Example

select class.name, student.name
from class join student
on student.class_id=class.id;


select class.name,student.name
from class inner join student
on student.class_id=class.id
where class.name="云计算1901";


select teacher.name, class.name
from class, teacher, class2teacher
where teacher.name = "奇哥"
and class2teacher.teacher_id=teacher.id
and class2teacher.class_id = class.id;


select teacher.name, class.name
from class join teacher join class2teacher
on class2teacher.teacher_id=teacher.id and class2teacher.class_id = class.id
where teacher.name = "奇哥";

参考链接：

http://baijiahao.baidu.com/s?id=1649513276188155038&wfr=spider&for=pc

https://www.jianshu.com/p/0427b1f97899