数据库的CUDR
一、create
create table tbl_name(
attribute1 int,
attribute2 varchar20,
)
二、update
update
三、delect
四、read
数据的读操作是重点
4.1 简单的数据记录查询
1
1、读取不重复数据 distinct关键字
2
select distinct va1 val2,,,valn from tbl_xxx # 这里面是值的val,val2...valn的并集不同就显示
3
4
select distinct house_title from tbl_house;
5
6
2、查询过程中实现四则运算
7
select ename sla*12 from tbl_xxx;
8
9
select ename, sla*12 as yearsalary from tbl_xxx;
10
11
3、设置显示数据的格式
12
select contact(ename,'雇员年薪为:',sla*12) yearsalary from tbl_xxx;
13
4.2 条件数据记录查询
1
select field1,field2 from tbl_xxx where condition
2
这里面的condition，可以有很多类型
3
1、带关系运算符和逻辑运算符的条件查询
4
select distinct title,location from house_price_list2 where location like '%麓山%' and field1>10;
5
2、带 between and 关键字的条件查询
6
select distinct title,location from house_price_list2 where location like '%麓山%' and field1>10 and field2 between 10 and 100;
7
3、带 is null 关键字的条件查询
8
select distinct title,location from house_price_list2 where location like '%麓山%' and field1>10 and field3 is null;
9
4、带 in 的关键字的条件查询
10
select distinct title,location from house_price_list2 where location like '%麓山%' and field1>10 and field4 in (12,12,3,4,5);
11
5、带 like 关键字的条件查询
12
select distinct title,location from house_price_list2 where location like '%麓山%';
13
14
4.3 排序数据记录查询
1
1、 单字段排序
2
select distinct title,location from house_price_list2 where location like '%麓山%' order by location;
3
select distinct title,location from house_price_list2 where location like '%麓山%' order by location desc; 降序
4
select distinct title,location from house_price_list2 where location like '%麓山%' order by location asc; 升序
5
6
2、 多字段排序
7
select distinct title,location from house_price_list2 where location like '%麓山%' order by location desc,title desc;
8
4.4 限制数据记录查询数量
1
select * from house_price_list2 limit 100;
2
select * from house_price_list2 limit 0,100; # 初始位置0，到100行
3
4.5 统计函数和分组数据记录查询
1
1、函数类型： count() avg() sum() max() min()
2
3
select count(*) from house_price_list2 where location like '%马%';
4
5
这里的*指的所有字段
6
一般形式如
7
select function(field1) from tbl_xxx where condition
8
9
2、简单分组查询
10
select * from house_price_list2 group by house_info; # 简单分组查询，对house_info分组，然后显示每个分组的一条记录
11
仅仅使用简单分组查询是没有什么实际意义，因为这个显示的一条数据是随机的
12
13
3、实现统计功能分组查询
14
select GROUP_CONCAT(house_price) FROM house_price_list2 GROUP BY house_info;
15
一般形式如下
16
select group_concat(field) from tbl_xxx where condition group by field;
17
这种分组查询，可以看到每个组中的字段值
18
19