数据的基本操作,增,删,改,查
image.png

表结构

image.png

1.增加 create

  • 普通增加 ``sql INSERT INTOstudent` (name,age,sex,school,stuno,phone,classid) values (‘蓝瞳’,18,1,’牧业经济学院’,1001,17788132456,202201);

// 如果确保表里所有的字段,都有值,可以省略字段 INSERT INTO student values (‘蓝瞳’,18,1,’牧业经济学院’,1001,17788132456,202201);

  2. - 使用默认值
  3. ```sql
  4. // 使用默认值,设置了默认值的字段,会使用默认值作为字段值
  5. INSERT INTO `student`  (name,age,sex,school,stuno,phone,classid) values ('蓝瞳',18,DEFAULT,'牧业经济学院',1001,17788132456,202201);
  7. // 有默认值字段,可以不设置值,有个问题就是自增列的值,也需要插入才行,不推荐这种写法
  8. INSERT INTO `student`  (name,age,school,stuno,phone,classid) values (10,'蓝瞳',18,'牧业经济学院',1001,17788132456,202201);
  • 一次增加多条数据
    1. INSERT INTO `student`  (name,age,school,stuno,phone,classid) 
    2. values
    3. ('蓝瞳-01',18,'牧业经济学院-01',1001,17788132456,202201),
    4. ('蓝瞳-02',19,'牧业经济学院-02',1001,17788132456,202201),
    5. ('蓝瞳-03',20,'牧业经济学院-03',1001,17788132456,202201);

    2. 删除

    1. DELETE FROM `student` where  id=10;
    把表student中,id是1的这条数据的name字段修改为’蓝火之瞳’,

    如果没有where id=1,这个条件限制,会删除表中所有数据

3. 修改

  1. UPDATE `student` SET  name='蓝火之瞳' where id=1

把表student中,id是1的这条数据的name字段修改为’蓝火之瞳’,

如果没有where id=1,这个条件限制,会把表中所有的数据的name字段都修改为’蓝火之瞳’

image.png

4. 查询

image.png

单表查询

image.png

  1. 查询一个字段

    1. select id from `user`

    从user表中,查出所有的id的数据

  2. 查询多个字段

image.png

  1. select id,loginId from `user`

每个字段名字之间用逗号隔开

  1. 查询所有字段

image.png

  1. select * from `user`

查询user表中的所有字段

  1. 别名

image.png

  1. select  ismale as '性别' from employee

查询employee表中的ismale字段,别名修改为性别,as 可以省略
image.png

  1. case 

    1. select id,name,
    2. case ismale when 1 then '男' else '女' end as sex,
    3. salary from employee

    对查出来的某一列的值做单独的处理,上述代码,判断ismale的值,当值是1的时候,显示’男’,其他情况显示’女’,最后修改别名为sex
    image.png
    另一种写法

    1. select id,name,
    2. case ismale when 1 then '男' else '女' end as sex,
    3. case 
    4. when salary >=10000 then '高'
    5. when salary >=5000 then '中'
    6. else '低'
    7. end as '薪资水平',
    8. salary from employee

    当salary >=10000 显示为’高’,当salary >=5000显示为’中’,其他显示为’低’,别名为薪资水平

  2. where

image.png

  1. =

    1. select * FROM employee where ismale =1

    条件查询,查询记录表 employee 字段ismale=1的记录

  2. in

    1. select * FROM department where companyId in(1,2)

    条件查询,查询记录表 department 字段companyId等于1,或者等于2,的记录
    image.png

  3. is 

    1. select * FROM employee where  location   is null

    查询employee表中 location中字段的值是null的数据,mysql中不支持location=null这种写法
    image.png

  4. is not 

    1. select * FROM employee where  location   is not  null

    查询employee表中 location中字段的不是null的数据,mysql中不支持location!=null这种写法
    image.png

  5. , <,>=,<=

    1. select * FROM employee where  salary > 5000

    查询employee表中 salary字段的值大于5000的数据,其他的关系都一样,不一一列举
    image.png

  6. between and 

    1. select * FROM employee where  salary between 10000 and 12000

    查询employee表中 salary字段的值大于10000,并且小于12000的数据
    image.png

  7. like 

    1. select * FROM employee where  name  like  '%袁%'

    查询employee表中 name字段的值中包含’袁’字的数据,'%'表示匹配任意字符,可以'&袁'这样来表示袁在结尾的'袁&',袁在前面, 还可以用下划线表示一个字符'袁_'表示,两个字符,第一个是袁字

  8. and 

    1. select * FROM employee where  name  like  '张%' and ismale=0 and salary>12000

    多个条件并列,查询employee表中,name字段开头是张字的,ismale字段是0的,salary字段大于12000的
    image.png

  9. or

    1. select * FROM employee where  name  like  '张%' and ismale=0 and salary>1200
    2. or 
    3. birthday > '1998-01-02'

    多个条件并列,查询employee表中,name字段开头是张字的,ismale字段是0的,salary字段大于12000,或者 birthday的值大于'1998-01-02'的值
    image.png

  10. order by

    1. select * FROM employee where  name  like  '张%' and ismale=0 and salary>12000
    2. or 
    3. birthday > '1998-01-02'
    4. order by salary asc

    多个条件并列,查询employee表中,name字段开头是张字的,ismale字段是0的,salary字段大于12000,或者 birthday的值大于'1998-01-02'的值,最后,数据按照salary的asc升序排列
    image.png

    1. select * FROM employee where  name  like  '张%' and ismale=0 and salary>12000
    2. or 
    3. birthday > '1998-01-02'
    4. order by salary desc

    多个条件并列,查询employee表中,name字段开头是张字的,ismale字段是0的,salary字段大于12000,或者 birthday的值大于'1998-01-02'的值,最后,数据按照salary的desc降序排列
    image.png

    1. 由于order by出现在select之后,所以可以使用创建的列或者别名排序
    2. order by 可以跟多个值,第一个值相等的情况下,使用第二个值进行比较

  11. limit ```sql select * FROM employee where name like ‘张%’ and ismale=0 and salary>12000 or birthday > ‘1998-01-02’ order by salary desc limit 0,3

  1. 多个条件并列,查询`employee`表中,`name`字段开头是张字的,`ismale`字段是0的,`salary`字段大于12000,**或者** `birthday`的值大于`'1998-01-02'`的值,**然后**,数据按照`salary`的`desc`降序排列,最后从结果中第一个数据开始取,取三条数据<br />![image.png](https://cdn.nlark.com/yuque/0/2022/png/22357012/1644853487215-f1cc5b67-f66b-4615-bb81-26f50cd90302.png#clientId=ufb887f62-fcba-4&crop=0&crop=0&crop=1&crop=1&from=paste&height=165&id=udb113d24&margin=%5Bobject%20Object%5D&name=image.png&originHeight=330&originWidth=1176&originalType=binary&ratio=1&rotation=0&showTitle=false&size=45360&status=done&style=none&taskId=u3c9756fd-a4b3-45d7-9ae9-cc14d3fd10e&title=&width=588)
  3. 11. DISTINCT
  4. ```sql
  5. select DISTINCT location FROM employee  order by location desc

查询表employee 中location中的不重复字段,一般用来的对某一个字段进行去重,多个字段的话,需要每一个字段的值都相同,才会去重

多表联合查询

image.png

  1. 全连接(笛卡尔积)

    1. select * from user,company

    image.png
    user表中的每一个行,跟company整个表做一个集合

  2. 左链接,左外链接,left join

    1. select * from department as d left join employee as e on d.id=e.deptId;

    image.png
    以左表为基准,表中的每一行,通过条件d.id=e.deptId,在右表中进行查询,匹配到的数据,拼接为一个表

    当左表的中的数据,在右边中匹配不到的数据时候,左表中至少有一条数据,右边的所有数据字段为null

右链接,右外链接,right join和左链接相反

  1. select * from employee as e  right join department as d  on d.id=e.deptId;

image.png

  1. 内连接
    1. select * from department as d inner join employee as e on d.id=e.deptId;
    image.png当左表的数据和右边的数据完全满足条件d.id=e.deptId时候,才会被匹配到

demo

  1. 显示所有员工的姓名,性别(使用男或女显示),入职时间,薪水,所属部门(显示部门名称),所属公司(公司名称) ```sql select e.name as ‘员工姓名’, case e.ismale when 1 then ‘男’ else ‘女’ end as ‘性别’, e.joinDate as ‘入职时间’,e.salary as ‘薪水’,d.name as ‘所属部门’,c.name as ‘所属公司’ from employee as e inner join department as d on e.deptId=d.id inner join company as c on d.companyId=c.id
  1. ![image.png](https://cdn.nlark.com/yuque/0/2022/png/22357012/1644931580115-198eafa4-f5aa-4e1f-b465-790712a0e2d9.png#clientId=uaa837798-3e2e-4&crop=0&crop=0&crop=1&crop=1&from=paste&height=244&id=u73ed53b9&margin=%5Bobject%20Object%5D&name=image.png&originHeight=488&originWidth=840&originalType=binary&ratio=1&rotation=0&showTitle=false&size=138160&status=done&style=none&taskId=u1810992f-7c12-4ff3-a15f-e5fa2fef752&title=&width=420)
  3. 2. 查询腾讯和蚂蚁金服的所有员工的姓名,性别,入职事件,部门名,公司名
  4. ```sql
  5. select e.name as '员工姓名',
  6. case  e.ismale
  7.  when 1 then '男'
  8.  else '女'
  9.  end as '性别',
  10.  e.joinDate as '入职时间',e.salary as '薪水',d.name as '所属部门',c.name as '所属公司' from employee as e inner join  department as d 
  11.  on e.deptId=d.id  inner join company as c
  12.  on d.companyId=c.id
  13.  where  c.`name` in('腾讯科技','蚂蚁金服')

image.png

  1. 查询渡一教学部的,所有的员工姓名,性别,入职时间,部门名,公司名 ``sql select e.name as '员工姓名', case e.ismale when 1 then '男' else '女' end as '性别', e.joinDate as '入职时间',e.salary as '薪水',d.name as '所属部门',c.name as '所属公司' from employee as e inner join department as d on e.deptId=d.id inner join company as c on d.companyId=c.id where d.name='教学部' and c.name` like ‘%渡一%’

``` image.png