mysql增删改查基本操作

你的名字 2023-09-27 14:25 236阅读 0赞

mysql创建（用户)表

  1. DROP TABLE if `base_member` ;
  2. CREATE TABLE `base_member` (
  3.   `id` int(11) NOT NULL AUTO_INCREMENT,
  4.   `nickname` varchar(255) DEFAULT NULL COMMENT '昵称',
  5.   `avatar_url` varchar(255) DEFAULT NULL COMMENT '头像',
  6.   `gender` char(1) DEFAULT NULL COMMENT '性别',
  7.   `real_name` varchar(50) DEFAULT NULL COMMENT '姓名',
  8.   `mobile` varchar(50) DEFAULT NULL COMMENT '手机号码',
  9.   `login_name` varchar(50) DEFAULT NULL COMMENT '登录账号',
  10.   `password` varchar(100) DEFAULT NULL COMMENT '密码',
  11.   `user_type` int(11) DEFAULT NULL COMMENT '用户类型',
  12.   `school_id` int(11) DEFAULT NULL COMMENT '书院id',
  13.   `class_id` int(11) DEFAULT NULL COMMENT '班级id',
  14.   `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  15.   PRIMARY KEY (`id`) USING BTREE//指定id列为主键，使用B树作为索引。
  16. ) ENGINE=InnoDB AUTO_INCREMENT=90 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='学生用户';

要是不加索引

  1. DROP TABLE if `base_member`;
  2. CREATE TABLE  `base_member`(
  3.   `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT ,
  4.   `nickname` varchar(255) DEFAULT NULL COMMENT '昵称',
  5.   `avatar_url` varchar(255) DEFAULT NULL COMMENT '头像',
  6.   `gender` char(1) DEFAULT NULL COMMENT '性别',
  7.   `real_name` varchar(50) DEFAULT NULL COMMENT '姓名',
  8.   `mobile` varchar(50) DEFAULT NULL COMMENT '手机号码',
  9.   `login_name` varchar(50) DEFAULT NULL COMMENT '登录账号',
  10.   `password` varchar(100) DEFAULT NULL COMMENT '密码',
  11.   `user_type` int(11) DEFAULT NULL COMMENT '用户类型',
  12.   `school_id` int(11) DEFAULT NULL COMMENT '书院id',
  13.   `class_id` int(11) DEFAULT NULL COMMENT '班级id',
  14.   `create_time` datetime DEFAULT NULL COMMENT '创建时间'
  15. )ENGINE=InnoDB AUTO_INCREMENT=90 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='学生用户';

增删改查sql语句（base_member是要查询的表名）：

  1. 在表中插入/增加一条数据：

    insert into base_member(id,nickname,orderName) values( 1, ‘张三’,‘http://localhost:8080/img/avatar.png’，‘1’，'张小三‘ ，123456，123，1234，1，2，1，2023-01-24 11:45:09)；

  2. 在表中插入/增加多条数据：

    insert into base_member(id,nickname,orderName) values( 1, ‘张三’,‘http://localhost:8080/img/avatar.png’，‘1’，'张小三‘ ，123456，123，1234，1，2，1，2023-01-24 11:45:09)，
    (2, ‘李四’,‘ http://localhost:8080/img/avatar.png’，‘1’，'李小四‘ ，12345，12，123，10，2，1，2023-01-24 11:45:09)，(3, ‘王五’,‘http://localhost:8080/img/avatar.png’，‘1’，'王小五‘ ，1234，11，12，1，2，1，2023-01-24 11:45:09)；

  3. 删除数据：

    drop table base_member;删除表结构和数据
    truncate base_member; 只删除数据，不删除表结构，删除后不可恢复
    delete from base_member; 删除整个表的数据，不删除表结构

  4. 删除一条数据：（修改编号1的用户）

    delete from base_member where id=1;

  5. 修改一条数据：（修改编号2的学生名字为赵莹）

    update base_member set real_name =’赵莹’where Sid =’2’

  6. 查询一条数据：（查询名字为赵莹的用户表数据）

    select * from base_member where real_name=’赵莹’;

  7. 查询5条数据（可以查询任意多条，如果不选查询的是整个表的数据）：

select * from base_member limit 5;

  1. 查询第几条到第几条数据（至于第几条到第几条由自己决定）：

select * from base_member limit 1,5;

拓展：

846c8fb739ffb558b0ca86600f103137.png

7219a92343a771415dec5bf485d63be4.png

多表查询：两表联查

  1. 用inner join联查两个表
  3. select teacher.Tname from teacher
  4. inner join course
  5. where teacher.Tid = course.Tid and course.Tid = '1'
  7. 统计语文成绩大于70的学生信息
  9. select a.Sname,a.Sage,a.Ssex,b.score from Student a, SC b
  10. where a.Sid=b.Sid and b.Cid=1 and b.score >70
  12. 统计各科分数大于80的人
  14. select a.Sid,a.Sname
  15. from student a,
  16. (select Sid from sc
  17. group by Sid
  18. having min(score) > 80) as b
  19. where b.Sid = a.Sid;

聚合函数、group by 、having 。聚合函数是将“若干行数据”经过计算后聚合成“一行数据”

常用的聚合函数：

  1. 1.MAX：返回某列的最大值 
  2. 2.MIN(column)    返回某列的最高值 
  3. 3.COUNT(column)    返回某列的总行数
  4. 4.COUNT(*)    返回表的总行数
  5. 5.SUM(column)    返回某列的相加总和
  6. 6.AVG(column)    返回某列的平均值

下面我们简单使用一下这些聚合函数。

我们计算一下员工表中最大工资和最小工资。

  1. select Max(sal) , Min(sal) from emp;

计算一下工资总和和平均工资。

  1. select sum(sal),avg(sal) from emp;

count函数是计算总行数。count(*)是计算表中总行数。

count(列名)是计算某一列总行数(不包括null值)。

  1. select count(*),count(comm) from emp;

为什么count(*)和count（comm）的值不一样呢？

答案是：聚合函数只作用非null，因为null数据不参与运算。

请大家在使用聚合函数的时候一定要记住这一点，不然计算的结果可能不是你想要的。我们以奖金的平均值来做测试，代码如下：

select avg(comm),avg(ifnull(comm,0)) from emp;

为啥同样是计算comm表明平均值，但是计算出来值不一样呢？null在聚合函数中不参与计算。

avg(comm)只计算了4个人的奖金(关羽，张飞，貂蝉，吴用)取平均值。

而avg(ifnull(comm,0))却是计算了所有人的奖金，取平均值。所以值要小的多，这其中的关键就是用了ifnull() 函数（ifnull函数的作用就是发现值为null后将其值变为0）。

温馨提示：聚合函数使用时注意空值的情况，要配合ifnull函数使用哦~

GROUP BY 语句

下面我们有这样一个需求：

3224dd02057df7c983cc9defbd5d9650.png

  1. 求每个部门所有工资总和。

简简单单的一句话，光用where是很难做到的。但group by 就非常简单（部门号相同的分到一组)

  1. select deptno,sum(sal) from emp groupby  deptno;

通过deptno字段对表数据进行分组后，然后通过sum(sal)来计算每个分组的总和。

  1. 查询每个部门工资大于1500的的人数。
  3. 1.每个部门：按照deptno分组，select deptno from emp groupby deptno;
  4. 2.工资大于1500：where sal >15003.
  5. 人数：count(*)函数。
  7. select deptno,count(*) from emp where sal >1500 groupby deptno;

HAVING用于分组后的再次筛选,只能用于分组。(注意：分组后)

练习：求工资总和大于9000的部门,并按照工资总和排序。

这个问题是接上面：“求每个部门所有工资总和”，再加一条分组后的筛选：

  1. select deptno,sum(sal)  total  from emp groupby  deptno havingsum(sal) >9000orderbysum(sal) asc;

having和where区别：

  1. 1.having是分组后，where是分组前
  2. 2.where不用使用聚合函数，having可以使用聚合函数。
  3. 3.where在分组之前就会进行筛选，过滤掉的数据不会进入分组。

关键字的执行顺序总结

关键字的书写顺序如下：

  1. 1.select 
  2. 2.from    
  3. 3.where        
  4. 4.groupby        
  5. 5.having        
  6. 6.orderby        
  7. 7.limit

关键字的执行顺序如下：

  1. 1.from    //行过滤        
  2. 2.where        
  3. 3.groupby        
  4. 4.having        
  5. 5.select    //列过滤        
  6. 6.order by//排序        
  7. 7.limit//附加

还是以员工表为例，我们以下面这条语句为例子逐步解析一下。

  1. select deptno,sum(sal)  total  from emp where sal>1000groupby  deptno havingsum(sal) >9000orderbysum(sal) asc;

第一步：执行from关键字

  1. 等同于执行语句：select *  from emp;

第二步：在第一步的基础上执行where

  1. 等同于：select deptno from emp where sal >1000;

第三步：在第二步的基础上执行group by

  1. 等同于：select deptno from emp where sal >1000groupby deptno;

第四步：在第三步的基础上执行having

  1. 等同于：select deptno from emp where sal >1000groupby deptno havingsum(sal) >9000;

第五步：在第三步的基础上选择列。

  1. 等同于：select deptno,sum(sal)  total from emp where sal >1000groupby deptno havingsum(sal) >9000;

第六步：order by 排序（略）

第七步：limit（略）

发表评论

表情：
评论列表 （有 0 条评论，236人围观）

还没有评论，来说两句吧...

相关阅读