此篇主要内容是对表记录的查询,在mybatis及找工作笔试中会用到,基本可以解决所有问题。接着上一篇写。
三、表记录操作篇
预备知识
在进行SQL操作之前我们壁纸知道常用的数据库的数据类型,接下来以MySQL中的数据类型为例子介绍。
- 字符串类型
包括 varchar(065535整数)、char(0255整数) - 大数据类型
blob(065535字节)、text(065535字节) - 数值型
tinyint(很小的整数)、smallint(小的整数)、mediumint(中等大小的整数)、int(普通大小的整数)、bigint(大的整数)、float(单精度浮点数)、double(双精度浮点数) - 逻辑型
bit - 日期型
year(YYYY 1901~2155)、time(HH:MM:SS -838:59:59~838:59:59)、date(YYYY-MM-DD 1000-01-01~9999-12-3)、datetime(YYYY-MM-DD HH:MM:SS 1000-01-01 00:00:00~ 9999-12-31 23:59:59)、timestamp(YYYY-MM-DD HH:MM:SS 19700101 00:00:01 UTC~2038-01-19 03:14:07UTC)
1、插入
1)向表中插入某些列(字段)的记录
格式:insert into 表名 (列名1,列名2,列名3...)
values (值1,值2,值3...);
2)向表中插入所有的列
格式;insert into 表名 values (值1,值2,值3...);
Note:
1. 插入的数据应与字段的数据类型相同
2. 数据的大小应在列的规定范围内
3. 在values中列出的数据位置必须与被加入的列的排列位置相对应
4. 除了数值类型外,其他字段类型的值必须用单引号引起来
5. 插入空值:不指定或insert into table value(null)
6. 对于自动增长的字段操作时,直接插入null
例如:
INSERT INTO sort(sid,sname) VALUES(‘s001’, ‘电器’);
INSERT INTO sort(sid,sname) VALUES(‘s002’, ‘服饰’);
INSERT INTO sort VALUES(‘s003’, ‘化妆品’);
INSERT INTO sort VALUES(‘s004’,‘书籍’);
2、更新
格式:update 表名 set 字段名=值,字段名=值;
格式:update 表名 set 字段名=值,字段名=值 where 条件;
Note:
1. 列名的类型与修改的值要一致
2. 修改值得时候不能超过最大长度
3. 值如果是字符串或者日期需要加’’
例如:
UPDATE sort SET sname=‘日用品’;
UPDATE sort SET sname=‘日用品’ WHERE sid=‘s002’;
UPDATE sort SET sname=‘日用品’ WHERE sid=‘s003’;
3、删除
格式:delete from 表名 [where 条件];
格式:truncate table 表名;
Note
delete 一条一条删除,不清空auto_increment记录数
truncate 直接将表删除,重新建表,auto_increment将置为零,从新开始
例如:
DELETE FROM sort WHERE sname=‘日用品’;
DELETE FROM sort;表数据清空
truncate table sort;
4、查询
以下以此表为例
CREATE TABLE zhangwu (
id INT PRIMARY KEY AUTO_INCREMENT, -- 账务ID
name VARCHAR(200), -- 账务名称
money DOUBLE, -- 金额
);
插入表记录:
INSERT INTO zhangwu(id,name,money) VALUES (1,'吃饭支出',247);
INSERT INTO zhangwu(id,name,money) VALUES (2,'工资收入',12345);
INSERT INTO zhangwu(id,name,money) VALUES (3,'服装支出',1000);
INSERT INTO zhangwu(id,name,money) VALUES (4,'吃饭支出',325);
INSERT INTO zhangwu(id,name,money) VALUES (5,'股票收入',8000);
INSERT INTO zhangwu(id,name,money) VALUES (6,打麻将支出,8000);
INSERT INTO zhangwu(id,name,money) VALUES (7,null,5000);
1、基本查询
1 查询指定字段
select 字段1,字段2,...from 表名;
例如:
select id,name from zhangwu;
2 查询表中所有字段
select * from 表名;
例如
select * from zhangwu;
3 去除重复记录
select distinct 字段 from 表名;
例如:
select distinct money from zhangwu;
4 别名查询,使用的as关键字,as可以省略的.别名可以给表中的字段,表设置别名
格式:
表别名格式:
select * from 表名 as 别名; 或
select * from 表名 别名;
列别名格式:
select 字段名 as 别名 from 表名;
或
select 字段名 别名 from 表名;
例如
表别名:
select * from zhangwu as zw;
列别名:
select money as m from zhangwu;
或
select money m from zhangwu;
5 sql语句的操作中,可以直接对列进行运算
例如:将所有账务的金额+10000元进行显示.
select pname,price+10000 from product;
2、条件查询
格式:select 字段 from 表名 where 条件;
接下来介绍以下where条件种类
比较运算符:
#> < <= >= = <>
大于、小于、大于(小于)等于、不等于
#BETWEEN …AND…
显示在某一区间的值(含头含尾)
#IN(set)
显示在in列表中的值,例:in(100,200)
#LIKE 通配符
% 用来匹配多个字符;例first_name like ‘a%’;
_ 用来匹配一个字符。例first_name like ‘a_’;
#IS NULL
判断是否为空 is null; 判断为空is not null; 判断不为空
逻辑运算符
and 多个条件同时成立
or 多个条件任一成立
not 不成立,例:where not(salary>100);
例如:
查询所有吃饭支出记录
SELECT * FROM zhangwu WHERE name = ‘吃饭支出’;
查询出金额大于1000的信息
SELECT * FROM zhangwu WHERE money >1000;
查询出金额在2000-5000之间的账务信息
SELECT * FROM zhangwu WHERE money >=2000 AND money <=5000;
或
SELECT * FROM zhangwu WHERE money BETWEEN 2000 AND 5000;
查询出金额是1000或5000或3500的商品信息
SELECT * FROM zhangwu WHERE money =1000 OR money =5000 OR money =3500;
或
SELECT * FROM zhangwu WHERE money IN(1000,5000,3500);
查询出账务名称包含”支出”的账务信息。
SELECT * FROM zhangwu WHERE name LIKE “%支出%”;
查询出账务名称中是无五个字的账务信息
SELECT * FROM gjp_ledger WHERE ldesc LIKE “_____”; – 五个下划线_
查询出账务名称不为null账务信息
SELECT * FROM zhangwu WHERE name IS NOT NULL;
SELECT * FROM zhangwu WHERE NOT (name IS NULL);
3、order by
格式:select 字段名1,字段名2,字段名3... from 表名
order by 字段名 asc(升序)或desc(降序);
4、聚集函数
count 返回某个字段记录总数(where可省略)
select count(*)或count(字段名) from 表名 where 条件;
sum 返回满足条件的和(where可省略,仅对数值有效)
select sum(字段名) from 表名 where 条件;
avg 求平均数(与上面格式一样)
max/min 求最大最小值(与上面格式一样)
5、group by
对记录进行分组
格式:select 字段1,字段2,字段3...
from 表名
where 条件
group by 字段名;
或
select 字段1,字段2,字段3...
from 表名
group by 字段名
having 条件;
Note:
Having和where均可实现过滤,但在having可以使用聚集函数,having通常跟在group by后,它作用于分组
例如:
select name,count(id)
...
group by name;
正确
select name,id
...
group by name;
错误,id为非聚集函数,任何没有出现在group by
子句中的属性若出现在select子句中,它只能出现在聚集函数
内部,否则错误。
6、exists ,not exists
直接以例子来说明
select id from sec as s
where sem = 'fall' and year = 2009
and exists ( select * from sec as t
where sem = 'spring '
and year = 2010);
7、限制输出的记录数
1、SQL server 和Access中
直接以例子来说明
select TOP 5 sname from pro1;
2、Oracle中中
select pname from pro1
where rownum <= 5;
3、MySQL中
select pname from pro1 limit 5;
Note:
若想输出前几名,应该先按照从大到小或者别的方式排序。