mysql数据库基本操作（四)

左手的ㄟ右手 2021-09-19 13:12 438阅读 0赞

# 表纪录操作 #

同样，表纪录也有增删改查的操作，但是一般我们大概70%的操作都是在查数据，所以查的操作需要单独说清楚，先简单看看增删改。

## 表纪录的增、删、改 ##

###      1.增加一条纪录的方式： ###

insert ［into］ tab_name (field1,filed2,.......) values (value1,value2,.......);

上一篇我们已经创建了一张员工表，现在简单一点，就留下名字和年龄信息,重新重建一张员工表：

create table emp_new(
                     id int primary key auto_increment,
                     name varchar(20) not null unique,
                     birth varchar(20),
                     salary float(7,2)
                                 );

创建好之后，我们开始增加数据，增加数据的方式有很多：

单条插入：

![复制代码][copycode.gif]

insert into emp_new (id,name,birth,salary) values
                         (1,'pengfy','1993-01-01',20000);
    
    insert into emp_new values
           (2,'pyq','1992-12-12',10000);
    
    insert into emp_new(name,salary) values
           ('xiaojiang',3000);

![复制代码][copycode.gif]

单条插入的时候，我们可以和键一一对应的写进去，也可以不写键名（默认全部键）直接插入对应顺序的值，也可以指定键名写入对应的值，这里id设为自增，不用填写也会自己增加。

多条插入：

insert into emp_new values
           (4,'pyq1','1992-06-20',8000),
           (5,'pyq2','1994-06-20',9000);

现在我们已经插入了五条数据，可以通过select \* from emp\_new先看看有没有插入成功：

![1388201-20190521110035296-600730648.png][]

###        2.修改一条表纪录： ###

修改表记录  update tab_name set field1=value1,field2=value2,......[where 语句]
    
        /*      UPDATE语法可以用新值更新原有表行中的各列。
                SET子句指示要修改哪些列和要给予哪些值。
                WHERE子句指定应更新哪些行。如没有WHERE子句，则更新所有的行。*/

现在发现pyq的生日写错了，就可以通过where单独修改pyq的生日，pengfy 的工资也太低了，老板加了6000工资：

update emp_new set barth='1992-9-11'where id=2;  #修改pyq的生日
    update emp_new set salary=26000 where name='pengfy';  #修改pengfy的工资

现在看看有没有修改成功：

![1388201-20190521111134231-1030619418.png][]

其实用set也可以增加一条纪录：

insert into emp_new set id=12,name="pyq3",salary=8800;

###       3.删除一条表纪录 ###

![复制代码][copycode.gif]

delete from tab_name [where ....]
    
            /*    如果不跟where语句则删除整张表中的数据
                delete只能用来删除一行记录
                delete语句只能删除表中的内容，不能删除表本身，想要删除表，用drop
                TRUNCATE TABLE也可以删除表中的所有数据，词语句首先摧毁表，再新建表。此种方式删除的数据不能在
                事务中恢复。*/

![复制代码][copycode.gif]

删除表纪录也很简单，按照条件删除就行了：

我们先把最后set增加的名字为pyq3的删除掉：

delete from emp_new where name='pyq3';

也可以删除表中所有纪录，删除表中所有纪录有两种方法:

-- 删除表中所有记录。
     delete from emp_new;                
    -- 使用truncate删除表中记录。
      truncate table emp_new;

那这二者的区别在哪里？一起看一下：

![1388201-20190521112845166-1731679503.png][]       ![1388201-20190521115619790-105066415.png][]

![1388201-20190521112903568-1547891211.png][]       ![1388201-20190521115647146-1408884969.png][]

![1388201-20190521112924368-2070954166.png][]        ![1388201-20190521115717667-718108837.png][]

两份表的数据完全一样，唯一的区别在于delete删除是一条一条删除的，所以在第一张图里面可以看到5行被影响（5 rows affected，而truncate在删除的时候，是直接把整个表删除后再重新建一张名字一样的空表，所以在数据很大的时候，我们用右边的truncate去做删除表的动作，时间会更短。还有一点需要注意的是：之前设置id里的**auto\_increment没有被重置:alter table employee auto\_increment=1;**所以表中如果有数据三条，id分别为1，2，3，突然插入一个id＝7，那么下次作为主键的字增长的id会从7开始增加。

## 表纪录之查(单表查询) ##

表查询表达式

SELECT *|field1,filed2 ...   FROM tab_name
                      WHERE 条件
                      GROUP BY field
                      HAVING 筛选
                      ORDER BY field
                      LIMIT 限制条数

我们重新创建一张表，是一张学校的成绩单,然后输入一些数据，有一个好习惯就是把sql命令都写成大写的，虽然sql不区分大小写，但这样可读性更强。

![复制代码][copycode.gif]

CREATE TABLE Result(
    
       id INT PRIMARY KEY  auto_increment,
       name VARCHAR (20),
       Chinese DOUBLE ,
       English DOUBLE ,
       Math DOUBLE
    );
    
    
    INSERT INTO Result VALUES  (1,"pengfy",100,99,98),
                                   (2,"pyq",80,95,80),
                                   (3,"xiaojiang",43,59,22),
                                   (4,"pyq2",80,86,82),
                                   (5,"pyq3",82,90,90);

![复制代码][copycode.gif]

表单创建好之后，我们就开始查询了：

**1.普通方式查询**

select [distinct] *|field1，field2，......   from tab_name
                -- 其中from指定从哪张表筛选，*表示查找所有列，也可以指定一个列
                -- 表明确指定要查找的列，distinct用来剔除重复行。

我们上面再增删改里面也说过一条select \* from table\_name，就是查询表中所有信息，就不用演示了。现在我们来查询所有学生的姓名和对应的英语成绩：

select name,english from result;

![1388201-20190521134422886-128478710.png][]

从图片看到，只显示了我们需要的名字和英语成绩，而且表头的名字大小写也和我们select命令中的显示一致。接下来我们再试一下过滤功能，我们插入两行和pengfy一样的内容，然后过滤一下：

insert into result values (6,'pengfy',100,99,98)，
                                      (7,'pengfy',100,99,98);
    
    select distinct chinese,name from result;

![1388201-20190521135721924-745276289.png][]

上面演示的是过滤掉了语文成绩和名字一样的信息，如果单独过滤语文成绩不一样的，那么80也会只保留从上往下的第一个，可以试一试。

**2.表达式查询**

select 也可以使用表达式，并且可以使用: 字段 as 别名或者:字段 别名

现在我要给每个学生的成绩加上10分，就可以这样写：

select name,chinese+10,english+10,math+10 from result;

![1388201-20190521140320453-1664980150.png][]

**要注意的是，这仅仅是显示的给我们看的时候加上了10分，实际的成绩是没有变的**，我们查看实际成绩还是select \* from result。我们再看看学生的总分：

select name,chinese+english+math from result;

![1388201-20190521140702299-1112388482.png][]

根据表达式所说的，我们可以使用别名来显示，比如我把name和chinese+english+math显示成中文：

select name as 姓名,chinese+english+math as 总成绩 from result;

![1388201-20190521141042033-1251392123.png][]

再次强调，我们改变的是我们查看的方式，并没有影响数据本身，否则那就不要查看，叫修改喽。还有需要注意的是记得在查找不容的内容之间加上逗号，你可以试试不加逗号是怎样的。

**3.使用where子句，进行过滤查询**

**      **我们在修改表纪录的时候也简单使用了where子句，那这里同样可以，首先我们查一下名字是pengfy 的成绩：

select * from result where name='pengfy';

![1388201-20190521141555594-1884188193.png][]

这就把名字为pengfy的成绩全找到了，当然，我们使用id或者成绩也可以找到，比如where Chinese=100，因为只有pengfy是10嘛，但我还是习惯用name，因为条件是找名字是pengfy的信息。除了这种，我们还可以做判断，比如找到英语成绩大于90分的人：

select name,english from result where english>90;

![1388201-20190521142043559-692352501.png][]

也可以配合上面的表达式使用， 比如找到总分大于260分的人，只显示名字和总分：

select name,chinese+english+math from result where chinese+english+math>260;

![1388201-20190521142442829-191530362.png][]

这时候你可能有疑问，为什么不把chinese+english+math换一个别名，然后where就不用写那么长一大串了，先告诉你这样会报错的，具体为什么，就是运行的优先级问题，后面再讲。除了这些，在where中还可以使用这些：

![复制代码][copycode.gif]

比较运算符：
                  > < >= <= <> !=
                  between 80 and 100 值在10到20之间
                  in(80,90,100) 值是10或20或30
                  like 'pengfy%'
    
                            /*
                            pattern可以是%或者_，
                            如果是%则表示任意多字符，此例如唐僧,唐国强
                            如果是_则表示一个字符唐_，只有唐僧符合。两个_则表示两个字符：__
                            */
    
    逻辑运算符
                  在多个条件直接可以使用逻辑运算符 and or not

![复制代码][copycode.gif]

这里可能有点问题的是like，这里就举一个例子，现在要找到名字是p开头的所有信息：

select * from result where name like 'p%';

![1388201-20190521143120130-1710048330.png][]

这里可以看到%可以代表任意长度的字符，但如果你要定长的字符，就要使用下划线\_，比如要查找名字p开头且4个字节的所有信息：

select * from result where name like 'p___';

![1388201-20190521143336057-1311838264.png][]

看着这个下划线有点长，其实是三个，一个就代表一个字符，加上p就一共4个字符。

**4.Order by 指定排序的列****  **

Order by 指定排序的列，排序的列即可是表中的列名，也可以是select 语句后指定的别名。
    
                  -- select *|field1,field2... from tab_name order by field [Asc|Desc]
    
                  -- Asc 升序、Desc 降序，其中asc为默认值 ORDER BY 子句应位于SELECT语句的结尾。

除了上面的操作，我们在现实中遇到的情况也有很多是排序显示的，现在我们按语文成绩排序来显示：

select * from result order by chinese;

![1388201-20190521144509291-1357373065.png][]

可以看到默认的是从小到大来显示的，如果要从大到小，只需要在最后加上一个desc即可：

select * from result order by chinese desc;

我们再按总成绩排个名看看，在这之前先加入一个插班生lily，Lily这个时候还没有考试，所以还没有成绩：

insert into result (id,name) values (8,'lily');
    
    select name,chinese+english+math as 总成绩 from result order by 总成绩 desc;

![1388201-20190521150028967-1636401594.png][]

细心的朋友就发现了，怎么这里就可以弄个别名放在order后面用呢，where确不可以，还是优先级的事。不过这里先看一个问题，Lily是没有成绩的，但成绩应该是一个数字，放一个null算怎么回事，怎么办？在排序前先做一个判断，看是不是空，是空就变为0：

select name,(ifnull(chinese,0)+ifnull(english,0)+ifnull(math,0)) as 总成绩 from result order by 总成绩 desc;

![1388201-20190521150512687-2069921708.png][]

这还没完，如果我只要名字p开头的总成绩排名怎么办？怎么放置where和order呢？

select name,(ifnull(chinese,0)+ifnull(english,0)+ifnull(math,0)) as 总成绩
        -> from result where name like 'p%'
        -> order by 总成绩 desc;

![1388201-20190521150758188-1583549141.png][]

搞定了，order放在后边。查询的方式可不止这些，下一篇继续查看表单的其他方式。

如果你对编程感兴趣或者想往编程方向发展，可以关注微信公众号**【筑梦编程】**，大家一起交流讨论！小编也会每天定时更新既有趣又有用的编程知识！

[copycode.gif]: /images/20210812/7dd65b6a4f2144028bb2ca2bd2f2662e.png
[1388201-20190521110035296-600730648.png]: /images/20210812/c5fa03417c0b419bbf49a3815b20e5d1.png
[1388201-20190521111134231-1030619418.png]: /images/20210812/81850dbe961d4fecb553ac619d134489.png
[1388201-20190521112845166-1731679503.png]: https://img2018.cnblogs.com/blog/1388201/201905/1388201-20190521112845166-1731679503.png
[1388201-20190521115619790-105066415.png]: /images/20210812/16156ad4e2ca46e1997b12c7b630c958.png
[1388201-20190521112903568-1547891211.png]: https://img2018.cnblogs.com/blog/1388201/201905/1388201-20190521112903568-1547891211.png
[1388201-20190521115647146-1408884969.png]: /images/20210812/10d6d17da2e04a268b0847509b89c7aa.png
[1388201-20190521112924368-2070954166.png]: https://img2018.cnblogs.com/blog/1388201/201905/1388201-20190521112924368-2070954166.png
[1388201-20190521115717667-718108837.png]: /images/20210812/e76b52d46fe4417ca4ba7337c1169a7c.png
[1388201-20190521134422886-128478710.png]: /images/20210812/f25ed7d7f71e4141a78b156696de4236.png
[1388201-20190521135721924-745276289.png]: /images/20210812/4f5ad9e1cf2e45c29250a280abd8e530.png
[1388201-20190521140320453-1664980150.png]: /images/20210812/2f2eee26464748c0902b81752b02b28a.png
[1388201-20190521140702299-1112388482.png]: /images/20210812/5c89accc7d6e4c089247cbd3dadf0070.png
[1388201-20190521141042033-1251392123.png]: /images/20210812/3a471c92dc7549168008c677e7881e93.png
[1388201-20190521141555594-1884188193.png]: /images/20210812/26528a3dc4864927900fabda81719f09.png
[1388201-20190521142043559-692352501.png]: /images/20210812/db857860f75f475e9ba08f33307c2a06.png
[1388201-20190521142442829-191530362.png]: /images/20210812/d3f178cf67ec4375ade4787c5fc8c5a3.png
[1388201-20190521143120130-1710048330.png]: /images/20210812/9f50e151b41f416ba1cdb957e5e0fc16.png
[1388201-20190521143336057-1311838264.png]: /images/20210812/62017cb21695462ea5f7229af8beaa6a.png
[1388201-20190521144509291-1357373065.png]: /images/20210812/c12cf23b36244a79a186834177f84845.png
[1388201-20190521150028967-1636401594.png]: /images/20210812/f51734f780ee428fbc4ca38b51c30508.png
[1388201-20190521150512687-2069921708.png]: /images/20210812/6fe2c4b6ac564f84adeffdd53613a60d.png
[1388201-20190521150758188-1583549141.png]: /images/20210812/234356b7d83147bb99cc02d9a4eb83f8.png