mysql常用增删改查命令总结

深碍√TFBOYSˉ_ 2022-11-26 09:48 1858阅读 0赞

**目录**

一、增：insert

二、删：delete

三、改：update

四、查：select

--------------------

# 一、增：insert #

基础语法

注意

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

<table> 
 <thead> 
  <tr> 
   <th> <p>id</p> </th> 
   <th> <p>name</p> </th> 
   <th> <p>age</p> </th> 
   <th> <p>phone</p> </th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>王麻子</td> 
   <td>32</td> 
   <td>13141098899</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>九筒</td> 
   <td>32</td> 
   <td>13721096541</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>六子</td> 
   <td>18</td> 
   <td>13139192657</td> 
  </tr> 
  <tr> 
   <td>4</td> 
   <td>马邦德</td> 
   <td>37</td> 
   <td>15855996688</td> 
  </tr> 
 </tbody> 
</table>

**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][http_baijiahao.baidu.com_s_id_1649513276188155038_wfr_spider_for_pc]

[https://www.jianshu.com/p/0427b1f97899][https_www.jianshu.com_p_0427b1f97899]

[http_baijiahao.baidu.com_s_id_1649513276188155038_wfr_spider_for_pc]: http://baijiahao.baidu.com/s?id=1649513276188155038&wfr=spider&for=pc
[https_www.jianshu.com_p_0427b1f97899]: https://www.jianshu.com/p/0427b1f97899