数据库SQL语句表记录操作教程

此篇主要内容是对表记录的查询，在mybatis及找工作笔试中会用到，基本可以解决所有问题。接着上一篇写。

三、表记录操作篇

预备知识

在进行sql操作之前我们壁纸知道常用的的数据类型，接下来以mysql中的数据类型为例子介绍。
- 字符串类型
包括 varchar（0~65535整数）、char(0~255整数)
- 大数据类型
blob(0~65535字节)、text(0~65535字节)
- 数值型
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 1970~01~01 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:
若想输出前几名，应该先按照从大到小或者别的方式排序。