表纪录操作
同样, 表纪录也有增删改查的操作, 但是一般我们大概 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)
- );
创建好之后, 我们开始增加数据, 增加数据的方式有很多:
单条插入:
- 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);
单条插入的时候, 我们可以和键一一对应的写进去, 也可以不写键名 (默认全部键) 直接插入对应顺序的值, 也可以指定键名写入对应的值, 这里 id 设为自增, 不用填写也会自己增加.
多条插入:
- insert into emp_new values
- (4,'pyq1','1992-06-20',8000),
- (5,'pyq2','1994-06-20',9000);
现在我们已经插入了五条数据, 可以通过 select * from emp_new 先看看有没有插入成功:
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 的工资
现在看看有没有修改成功:
其实用 set 也可以增加一条纪录:
insert into emp_new set id=12,name="pyq3",salary=8800;
3. 删除一条表纪录
- delete from tab_name [where ....]
- /* 如果不跟 where 语句则删除整张表中的数据
- delete 只能用来删除一行记录
- delete 语句只能删除表中的内容, 不能删除表本身, 想要删除表, 用 drop
- TRUNCATE TABLE 也可以删除表中的所有数据, 词语句首先摧毁表, 再新建表. 此种方式删除的数据不能在
- 事务中恢复.*/
删除表纪录也很简单, 按照条件删除就行了:
我们先把最后 set 增加的名字为 pyq3 的删除掉:
delete from emp_new where name='pyq3';
也可以删除表中所有纪录, 删除表中所有纪录有两种方法:
-- 删除表中所有记录.
delete from emp_new;
-- 使用 truncate 删除表中记录.
truncate table emp_new;
那这二者的区别在哪里? 一起看一下:
两份表的数据完全一样, 唯一的区别在于 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 不区分大小写, 但这样可读性更强.
- 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);
表单创建好之后, 我们就开始查询了:
1. 普通方式查询
select [distinct] *|field1,field2,...... from tab_name
-- 其中 from 指定从哪张表筛选,* 表示查找所有列, 也可以指定一个列
-- 表明确指定要查找的列, distinct 用来剔除重复行.
我们上面再增删改里面也说过一条 select * from table_name, 就是查询表中所有信息, 就不用演示了. 现在我们来查询所有学生的姓名和对应的英语成绩:
select name,english from result;
从图片看到, 只显示了我们需要的名字和英语成绩, 而且表头的名字大小写也和我们 select 命令中的显示一致. 接下来我们再试一下过滤功能, 我们插入两行和 pengfy 一样的内容, 然后过滤一下:
- insert into result values (6,'pengfy',100,99,98),
- (7,'pengfy',100,99,98);
- select distinct chinese,name from result;
上面演示的是过滤掉了语文成绩和名字一样的信息, 如果单独过滤语文成绩不一样的, 那么 80 也会只保留从上往下的第一个, 可以试一试.
2. 表达式查询
select 也可以使用表达式, 并且可以使用: 字段 as 别名或者: 字段 别名
现在我要给每个学生的成绩加上 10 分, 就可以这样写:
select name,chinese+10,english+10,math+10 from result;
要注意的是, 这仅仅是显示的给我们看的时候加上了 10 分, 实际的成绩是没有变的, 我们查看实际成绩还是 select * from result. 我们再看看学生的总分:
select name,chinese+english+math from result;
根据表达式所说的, 我们可以使用别名来显示, 比如我把 name 和 chinese+english+math 显示成中文:
select name as 姓名, chinese+english+math as 总成绩 from result;
再次强调, 我们改变的是我们查看的方式, 并没有影响数据本身, 否则那就不要查看, 叫修改喽. 还有需要注意的是记得在查找不容的内容之间加上逗号, 你可以试试不加逗号是怎样的.
3. 使用 where 子句, 进行过滤查询
我们在修改表纪录的时候也简单使用了 where 子句, 那这里同样可以, 首先我们查一下名字是 pengfy 的成绩:
select * from result where name='pengfy';
这就把名字为 pengfy 的成绩全找到了, 当然, 我们使用 id 或者成绩也可以找到, 比如 where Chinese=100, 因为只有 pengfy 是 10 嘛, 但我还是习惯用 name, 因为条件是找名字是 pengfy 的信息. 除了这种, 我们还可以做判断, 比如找到英语成绩大于 90 分的人:
select name,english from result where english>90;
也可以配合上面的表达式使用, 比如找到总分大于 260 分的人, 只显示名字和总分:
select name,chinese+english+math from result where chinese+english+math>260;
这时候你可能有疑问, 为什么不把 chinese+english+math 换一个别名, 然后 where 就不用写那么长一大串了, 先告诉你这样会报错的, 具体为什么, 就是运行的优先级问题, 后面再讲. 除了这些, 在 where 中还可以使用这些:
比较运算符:
> <>= <= <> !=
between 80 and 100 值在 10 到 20 之间
in(80,90,100) 值是 10 或 20 或 30
- like 'pengfy%'
- /*
- pattern 可以是 % 或者_,
- 如果是 % 则表示任意多字符, 此例如唐僧, 唐国强
- 如果是_则表示一个字符唐_, 只有唐僧符合. 两个_则表示两个字符:__
- */
逻辑运算符
在多个条件直接可以使用逻辑运算符 and or not
这里可能有点问题的是 like, 这里就举一个例子, 现在要找到名字是 p 开头的所有信息:
select * from result where name like 'p%';
这里可以看到 % 可以代表任意长度的字符, 但如果你要定长的字符, 就要使用下划线_, 比如要查找名字 p 开头且 4 个字节的所有信息:
select * from result where name like 'p___';
看着这个下划线有点长, 其实是三个, 一个就代表一个字符, 加上 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;
可以看到默认的是从小到大来显示的, 如果要从大到小, 只需要在最后加上一个 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;
细心的朋友就发现了, 怎么这里就可以弄个别名放在 order 后面用呢, where 确不可以, 还是优先级的事. 不过这里先看一个问题, Lily 是没有成绩的, 但成绩应该是一个数字, 放一个 null 算怎么回事, 怎么办? 在排序前先做一个判断, 看是不是空, 是空就变为 0:
select name,(ifnull(chinese,0)+ifnull(english,0)+ifnull(math,0)) as 总成绩 from result order by 总成绩 desc;
这还没完, 如果我只要名字 p 开头的总成绩排名怎么办? 怎么放置 where 和 order 呢?
select name,(ifnull(chinese,0)+ifnull(english,0)+ifnull(math,0)) as 总成绩
-> from result where name like 'p%'
-> order by 总成绩 desc;
搞定了, order 放在后边. 查询的方式可不止这些, 下一篇继续查看表单的其他方式.
来源: https://www.cnblogs.com/pengfy/p/10898668.html